Calendar database

(→DateStart)
m (removed section edit links with __NOEDITSECTION__ magic word to prettify the page a bit)
 
(40 intermediate revisions not shown)
Line 1: Line 1:
-
The calendar database can be found in /home/user/.calendar/calendardb. The goal of this wiki is to provide enough information on the calendar database in order to implement mass modifying/adding/deleting events verifying some criterias (string match in title, locations, comments for example). It could be easy then to write a small shell script using zenity and sqlite3 to perform this tasks.
+
The [[Open development/Maemo roadmap/Fremantle|Maemo 5]] calendar database can be found in <code>/home/user/.calendar/calendardb</code>. The goal of this article is to provide enough information on the calendar database in order to implement mass modifying/adding/deleting events verifying some criterias (string match in title, locations, comments for example). It could be easy then to write a small shell script using zenity and sqlite3 to perform this tasks.
-
If you want to help editing this wiki and writing useful informations on fields or new scripts, feel free to copy your own database on your computer and analyze it with some tools like "SQLite Database Browser" (Linux/Debian).
+
If you want to help editing this article and writing useful informations on fields or new scripts, feel free to copy your own database on your computer and analyze it with some tools like "SQLite Database Browser" (Linux/Debian). The datatypes are those listed in the [http://www.sqlite.org/datatype3.html SQLite documentation].
 +
__NOEDITSECTION__<!-- removes section edit links as they look quite ugly on this page. -ashley on 8 June 2013 -->
 +
== [[#toc|Structure]] ==
 +
=== [[#toc|Tables & fields]] ===
-
= Scripts =
+
==== [[#toc|ALARM]] ====
-
= Structure =
+
===== Id =====
-
== Tables & fields ==
+
===== Trigger =====
 +
===== Repeat =====
 +
===== Duration =====
 +
===== Action =====
 +
===== CookieId =====
 +
===== Attach =====
-
=== ALARM ===
+
==== [[#toc|Birthdays]] ====
-
==== Id ====
+
-
==== Trigger ====
+
-
==== Repeat ====
+
-
==== Duration ====
+
-
==== Action ====
+
-
==== CookieId ====
+
-
==== Attach ====
+
-
=== Birthdays ===
+
{| class="wikitable"
-
==== Id ====
+
|+ Birthdays table
-
==== DayMonth ====
+
|-
-
==== Year ====
+
! Field !! Type !! Description
 +
|-
 +
| Id || integer || primary key
 +
|-
 +
| DayMonth || integer || Day and month as a combined integer. Calculated as: month*100+day (day with a leading zero). 1102 means November 2
 +
|-
 +
| Year || integer || Year as a number.
 +
|}
 +
 
 +
==== [[#toc|Calendars]] ====
-
=== Calendars ===
+
{| class="wikitable"
-
==== CalendarId ====
+
|+ Calendars table
-
==== Name ====
+
-
==== Colour ====
+
-
:''Type''
+
-
::INTEGER
+
-
:''Description''
+
-
::Colour of a calendar
+
-
:''Mappings''
+
-
::{| class="wikitable"
+
|-
|-
-
| 0 || Dark Blue
+
! Field !! Type !! Description
|-
|-
-
| 1 || Green
+
| CalendarId || integer || primary key
|-
|-
-
| 2 || Dark Pink
+
| Name || text || name of the calendar
|-
|-
-
| 3 || Orange
+
| Colour || integer || 0 = dark blue, 1 = green, 2 = dark pink, 3 = orange, 4 = purple, 5 = yellow, 6 = white, 7 = light blue, 8 = light pink, 9 = yellow-green
|-
|-
-
| 4 || Purple
+
| isVisible || integer || 0 = not visible, 1 = visible
|-
|-
-
| 5 || Yellow
+
| isReadonly || integer || 0 = writeable, 1 = not writeable
|-
|-
-
| 6 || White
+
| CalendarType || integer || 0 = local, 1 = intelligent, 2 = ?, 3 = private, 4 = synchronized
|-
|-
-
| 7 || Light Blue
+
| CalendarTune || text || Path to the calendar alarm sound. Default path: <code>/usr/share/sounds/ui-calendar_alarm_default.aac</code>
|-
|-
-
| 8 || Light Pink
+
| CalendarVersion || ||
|-
|-
-
| 9 || Yellow-Green
+
| CalendarProfile || ||
|}
|}
-
==== IsVisible ====
+
==== [[#toc|ComponentDetails]] ====
-
:''Type''
+
 
-
:: INTEGER
+
===== Id =====
-
:''Description''
+
===== ComponentType =====
-
::
+
===== Class =====
-
:''Mappings''
+
===== Geo =====
-
::{| class="wikitable"
+
===== Priority =====
 +
===== DateStamp =====
 +
===== Sequence =====
 +
===== Transparency =====
 +
===== Uid =====
 +
===== Url =====
 +
===== Attach =====
 +
===== Category =====
 +
===== Comment =====
 +
===== Contact =====
 +
===== Related =====
 +
===== Resources =====
 +
===== Percent =====
 +
===== Completed =====
 +
 
 +
==== [[#toc|Components]] ====
 +
 
 +
{| class="wikitable"
 +
|+ Components table
|-
|-
-
| 0 || not visible
+
! Field !! Type !! Description
|-
|-
-
| 1 || visible
+
| Id || integer || primary key
|-
|-
-
|}
+
| CalendarId || integer || association with the corresponding [[#Calendar|calender]]
-
 
+
-
==== IsReadonly ====
+
-
:''Type''
+
-
:: INTEGER
+
-
:''Description''
+
-
:: Indicates if the calendar is writeable or not.
+
-
:''Mappings''
+
-
::{| class="wikitable"
+
|-
|-
-
| 0 || writeable
+
| ComponentType || integer || 1 = event, 2 = task, 3 = note, 4 = birthday
|-
|-
-
| 1 || not writeable
+
| Flags || integer || 3 = has recursive entry, 4 = has alarm, 5 = has both
|-
|-
-
|}
+
| DateStart || integer || UTC start date in Unix Time Format (number of seconds since 01/01/1970)
-
 
+
-
==== CalendarType ====
+
-
:''Type''
+
-
:: INTEGER
+
-
:''Description''
+
-
::
+
-
:''Mappings''
+
-
::{| class="wikitable"
+
|-
|-
-
| 0 || local
+
| DateEnd || integer || -1 = undefined, otherwise UTC end date in Unix Time Format (number of seconds since 01/01/1970)
|-
|-
-
| 1 || intelligent
+
| Summary || text || title [event, task, note, birthday]
|-
|-
-
| 2 ||  
+
| Location || text || location [event]
|-
|-
-
| 3 || private
+
| Description || text || comments [event, task, note]
|-
|-
-
| 4 || synchronized
+
| Status || integer || -1 = undefined [event, note]. 0 = incomplete [task], 1 = complete [task], yyyy = year [birthday]
|-
|-
-
|}
+
| Uid || ||
-
 
+
-
==== CalendarTune ====
+
-
==== CalendarVersion ====
+
-
==== CalendarProfile ====
+
-
 
+
-
=== ComponentDetails ===
+
-
==== Id ====
+
-
==== ComponentType ====
+
-
:''Type''
+
-
::INTEGER
+
-
:''Description''
+
-
:: Defines the type of a calendar component.
+
-
:''Mappings''
+
-
::{| class="wikitable"
+
|-
|-
-
| 1 || Event
+
| Until || integer || -1 = none [event, tastk, note], 2145913199 = end of calendar / limit of the Unix Time Format, other integer = end date of a recurrence [event, birthday]
|-
|-
-
| 2 || Task
+
| AllDay || integer || 0 = in limited time [event, task, note], 1 = all day [event, birthday]
|-
|-
-
| 3 || Note
+
| CreatedTime || integer || date of creation
|-
|-
-
| 4 || Birthday
+
| ModifiedTime || integer || last date of modification
|-
|-
 +
| Tzid || text || time zone identifier, for example "<code>Europe/Paris</code>"
 +
|-
 +
| TzOffset || text || time zone offset in seconds compared to UTC, for example "<code>7200</code>" (2 hours)
|}
|}
-
==== Class ====
+
==== [[#toc|IDMAP]] ====
-
==== Geo ====
+
-
==== Priority ====
+
-
==== DateStamp ====
+
-
==== Sequence ====
+
-
==== Transparency ====
+
-
==== Uid ====
+
-
==== Url ====
+
-
==== Attach ====
+
-
==== Category ====
+
-
==== Comment ====
+
-
==== Contact ====
+
-
==== Related ====
+
-
==== Resources ====
+
-
==== Percent ====
+
-
==== Completed ====
+
-
=== Components ===
+
===== LocalId =====
-
==== Id ====
+
===== ExternalId =====
-
==== CalendarId ====
+
 
-
==== ComponentType ====
+
==== [[#toc|Instances]] ====
-
==== Flags ====
+
 
-
==== DateStart ====
+
===== Id =====
-
::{| class="wikitable"
+
===== DateStart =====
-
|-
+
===== DateEnd =====
-
| <INTEGER> || start date of an event in the Unix Time Format (number of seconds since 01/01/1970).
+
 
-
|-
+
==== [[#toc|Parameters]] ====
-
|}
+
 
 +
===== ComponentId =====
 +
===== PropertyType =====
 +
===== PropertyValue =====
 +
===== ParamName =====
 +
===== ParamValue =====
 +
 
 +
==== [[#toc|RECURSIVE]] ====
 +
 
 +
===== Id =====
 +
===== Rrule =====
 +
===== Rdates =====
 +
===== Exdates =====
 +
===== Exrule =====
 +
===== RecurrenceId =====
 +
===== Rtype =====
-
==== DateEnd ====
+
==== [[#toc|TIMEZONE]] ====
-
==== Summary ====
+
-
==== Location ====
+
-
==== Description ====
+
-
==== Status ====
+
-
==== Uid ====
+
-
==== Until ====
+
-
==== AllDay ====
+
-
==== CreatedTime ====
+
-
==== ModifiedTime ====
+
-
==== Tzid ====
+
-
==== TzOffset ====
+
-
=== IDMAP ===
+
===== Location =====
-
==== LocalId ====
+
===== Tzid =====
-
==== ExternalId ====
+
===== DtStartStd =====
 +
===== DtStartDst =====
 +
===== TzOffsetStd =====
 +
===== TzOffsetDst =====
 +
===== RRuleStd =====
 +
===== RRuleDst =====
 +
===== TzName =====
 +
===== DSTFlag =====
-
=== Instances ===
 
-
==== Id ====
 
-
==== DateStart ====
 
-
==== DateEnd ====
 
-
=== Parameters ===
+
==== [[#toc|Trash]] ====
-
==== ComponentId ====
+
-
==== PropertyType ====
+
-
==== PropertyValue ====
+
-
==== ParamName ====
+
-
==== ParamValue ====
+
-
=== RECURSIVE ===
+
===== ComponentId =====
-
==== Id ====
+
===== CalendarId =====
-
==== Rrule ====
+
===== ComponentType =====
-
==== Rdates ====
+
===== DeletedTime =====
-
==== Exdates ====
+
===== CreatedTime =====
-
==== Exrule ====
+
-
==== RecurrenceId ====
+
-
==== Rtype ====
+
-
=== TIMEZONE ===
+
==== [[#toc|XProperties]] ====
-
==== Location ====
+
-
==== Tzid ====
+
-
==== DtStartStd ====
+
-
==== DtStartDst ====
+
-
==== TzOffsetStd ====
+
-
==== TzOffsetDst ====
+
-
==== RRuleStd ====
+
-
==== RRuleDst ====
+
-
==== TzName ====
+
-
==== DSTFlag ====
+
-
=== Trash ===
+
===== ComponentId =====
-
==== ComponentId ====
+
===== XPropertyName =====
-
==== CalendarId ====
+
===== XPropertyValue =====
-
==== ComponentType ====
+
-
==== DeletedTime ====
+
-
==== CreatedTime ====
+
-
=== XProperties ===
+
==== [[#toc|sqlite_sequence]] ====
-
==== ComponentId ====
+
-
==== XPropertyName ====
+
-
==== XPropertyValue ====
+
-
=== sqlite_sequence ===
+
===== name =====
-
==== name ====
+
===== seq =====
-
==== seq ====
+
 +
=== [[#toc|Indices]] ===
 +
==== IDX_ALARM ====
 +
==== IDX_BIRTHDAYS_DATE ====
 +
==== IDX_BIRTHDAYS_IDS ====
 +
==== IDX_CALENDAR ====
 +
==== IDX_COMPONENT ====
 +
==== IDX_DETAILS ====
 +
==== IDX_INST ====
 +
==== IDX_RECURSIVE ====
 +
==== IDX_TIMEZONE ====
 +
==== IDX_TRASH ====
-
== Indices ==
+
[[Category:Fremantle]]
-
=== IDX_ALARM ===
+
-
=== IDX_BIRTHDAYS_DATE ===
+
-
=== IDX_BIRTHDAYS_IDS ===
+
-
=== IDX_CALENDAR ===
+
-
=== IDX_COMPONENT ===
+
-
=== IDX_DETAILS ===
+
-
=== IDX_INST ===
+
-
=== IDX_RECURSIVE ===
+
-
=== IDX_TIMEZONE ===
+
-
=== IDX_TRASH ===
+
[[Category:Calendar]]
[[Category:Calendar]]

Latest revision as of 12:14, 8 June 2013

The Maemo 5 calendar database can be found in /home/user/.calendar/calendardb. The goal of this article is to provide enough information on the calendar database in order to implement mass modifying/adding/deleting events verifying some criterias (string match in title, locations, comments for example). It could be easy then to write a small shell script using zenity and sqlite3 to perform this tasks.

If you want to help editing this article and writing useful informations on fields or new scripts, feel free to copy your own database on your computer and analyze it with some tools like "SQLite Database Browser" (Linux/Debian). The datatypes are those listed in the SQLite documentation.

Contents

Structure

Tables & fields

ALARM

Id
Trigger
Repeat
Duration
Action
CookieId
Attach

Birthdays

Birthdays table
Field Type Description
Id integer primary key
DayMonth integer Day and month as a combined integer. Calculated as: month*100+day (day with a leading zero). 1102 means November 2
Year integer Year as a number.

Calendars

Calendars table
Field Type Description
CalendarId integer primary key
Name text name of the calendar
Colour integer 0 = dark blue, 1 = green, 2 = dark pink, 3 = orange, 4 = purple, 5 = yellow, 6 = white, 7 = light blue, 8 = light pink, 9 = yellow-green
isVisible integer 0 = not visible, 1 = visible
isReadonly integer 0 = writeable, 1 = not writeable
CalendarType integer 0 = local, 1 = intelligent, 2 = ?, 3 = private, 4 = synchronized
CalendarTune text Path to the calendar alarm sound. Default path: /usr/share/sounds/ui-calendar_alarm_default.aac
CalendarVersion
CalendarProfile

ComponentDetails

Id
ComponentType
Class
Geo
Priority
DateStamp
Sequence
Transparency
Uid
Url
Attach
Category
Comment
Contact
Related
Resources
Percent
Completed

Components

Components table
Field Type Description
Id integer primary key
CalendarId integer association with the corresponding calender
ComponentType integer 1 = event, 2 = task, 3 = note, 4 = birthday
Flags integer 3 = has recursive entry, 4 = has alarm, 5 = has both
DateStart integer UTC start date in Unix Time Format (number of seconds since 01/01/1970)
DateEnd integer -1 = undefined, otherwise UTC end date in Unix Time Format (number of seconds since 01/01/1970)
Summary text title [event, task, note, birthday]
Location text location [event]
Description text comments [event, task, note]
Status integer -1 = undefined [event, note]. 0 = incomplete [task], 1 = complete [task], yyyy = year [birthday]
Uid
Until integer -1 = none [event, tastk, note], 2145913199 = end of calendar / limit of the Unix Time Format, other integer = end date of a recurrence [event, birthday]
AllDay integer 0 = in limited time [event, task, note], 1 = all day [event, birthday]
CreatedTime integer date of creation
ModifiedTime integer last date of modification
Tzid text time zone identifier, for example "Europe/Paris"
TzOffset text time zone offset in seconds compared to UTC, for example "7200" (2 hours)

IDMAP

LocalId
ExternalId

Instances

Id
DateStart
DateEnd

Parameters

ComponentId
PropertyType
PropertyValue
ParamName
ParamValue

RECURSIVE

Id
Rrule
Rdates
Exdates
Exrule
RecurrenceId
Rtype

TIMEZONE

Location
Tzid
DtStartStd
DtStartDst
TzOffsetStd
TzOffsetDst
RRuleStd
RRuleDst
TzName
DSTFlag

Trash

ComponentId
CalendarId
ComponentType
DeletedTime
CreatedTime

XProperties

ComponentId
XPropertyName
XPropertyValue

sqlite_sequence

name
seq

Indices

IDX_ALARM

IDX_BIRTHDAYS_DATE

IDX_BIRTHDAYS_IDS

IDX_CALENDAR

IDX_COMPONENT

IDX_DETAILS

IDX_INST

IDX_RECURSIVE

IDX_TIMEZONE

IDX_TRASH

Retrieved from "http://bugs.maemo.org/Calendar_database"