1 | -- Really Simple CalDAV Store - Database Schema |
---|
2 | -- |
---|
3 | |
---|
4 | -- Use the usr, group and schema management stufffrom libawl-php |
---|
5 | \i awl-tables.sql |
---|
6 | \i schema-management.sql |
---|
7 | |
---|
8 | -- The main event. Where we store the things the calendar throws at us. |
---|
9 | CREATE TABLE caldav_data ( |
---|
10 | user_no INT references usr(user_no), |
---|
11 | dav_name TEXT, |
---|
12 | dav_etag TEXT, |
---|
13 | created TIMESTAMP WITH TIME ZONE, |
---|
14 | modified TIMESTAMP WITH TIME ZONE, |
---|
15 | caldav_data TEXT, |
---|
16 | caldav_type TEXT, |
---|
17 | logged_user INT references usr(user_no), |
---|
18 | |
---|
19 | PRIMARY KEY ( user_no, dav_name ) |
---|
20 | ); |
---|
21 | |
---|
22 | GRANT SELECT,INSERT,UPDATE,DELETE ON caldav_data TO general; |
---|
23 | |
---|
24 | -- Not particularly needed, perhaps, except as a way to collect |
---|
25 | -- a bunch of valid iCalendar time zone specifications... :-) |
---|
26 | CREATE TABLE time_zone ( |
---|
27 | tz_id TEXT PRIMARY KEY, |
---|
28 | tz_locn TEXT, |
---|
29 | tz_spec TEXT |
---|
30 | ); |
---|
31 | GRANT SELECT,INSERT ON time_zone TO general; |
---|
32 | |
---|
33 | -- The parsed calendar item. Here we have pulled those events/todos/journals apart somewhat. |
---|
34 | CREATE TABLE calendar_item ( |
---|
35 | user_no INT references usr(user_no), |
---|
36 | dav_name TEXT, |
---|
37 | dav_etag TEXT, |
---|
38 | |
---|
39 | -- Extracted vEvent/vTodo data |
---|
40 | uid TEXT, |
---|
41 | created TIMESTAMP, |
---|
42 | last_modified TIMESTAMP, |
---|
43 | dtstamp TIMESTAMP, |
---|
44 | dtstart TIMESTAMP WITH TIME ZONE, |
---|
45 | dtend TIMESTAMP WITH TIME ZONE, |
---|
46 | due TIMESTAMP WITH TIME ZONE, |
---|
47 | summary TEXT, |
---|
48 | location TEXT, |
---|
49 | description TEXT, |
---|
50 | priority INT, |
---|
51 | class TEXT, |
---|
52 | transp TEXT, |
---|
53 | rrule TEXT, |
---|
54 | url TEXT, |
---|
55 | percent_complete NUMERIC(7,2), |
---|
56 | tz_id TEXT REFERENCES time_zone( tz_id ), |
---|
57 | status TEXT, |
---|
58 | |
---|
59 | -- Cascade updates / deletes from the caldav_data table |
---|
60 | CONSTRAINT caldav_exists FOREIGN KEY ( user_no, dav_name ) |
---|
61 | REFERENCES caldav_data ( user_no, dav_name ) |
---|
62 | MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE |
---|
63 | ); |
---|
64 | |
---|
65 | GRANT SELECT,INSERT,UPDATE,DELETE ON calendar_item TO general; |
---|
66 | |
---|
67 | |
---|
68 | -- Something that can look like a filesystem hierarchy where we store stuff |
---|
69 | CREATE TABLE collection ( |
---|
70 | user_no INT references usr(user_no), |
---|
71 | parent_container TEXT, |
---|
72 | dav_name TEXT, |
---|
73 | dav_etag TEXT, |
---|
74 | dav_displayname TEXT, |
---|
75 | is_calendar BOOLEAN, |
---|
76 | created TIMESTAMP WITH TIME ZONE, |
---|
77 | modified TIMESTAMP WITH TIME ZONE, |
---|
78 | |
---|
79 | PRIMARY KEY ( user_no, dav_name ) |
---|
80 | ); |
---|
81 | |
---|
82 | GRANT SELECT,INSERT,UPDATE,DELETE ON collection TO general; |
---|
83 | |
---|
84 | -- Each user can be related to each other user. This mechanism can also |
---|
85 | -- be used to define groups of users, since some relationships are transitive. |
---|
86 | CREATE TABLE relationship_type ( |
---|
87 | rt_id SERIAL PRIMARY KEY, |
---|
88 | rt_name TEXT, |
---|
89 | rt_togroup BOOLEAN, |
---|
90 | confers TEXT DEFAULT 'RW', |
---|
91 | rt_fromgroup BOOLEAN |
---|
92 | ); |
---|
93 | |
---|
94 | GRANT SELECT,INSERT,UPDATE,DELETE ON relationship_type TO general; |
---|
95 | GRANT SELECT,UPDATE ON relationship_type_rt_id_seq TO general; |
---|
96 | |
---|
97 | CREATE TABLE relationship ( |
---|
98 | from_user INT REFERENCES usr (user_no) ON UPDATE CASCADE, |
---|
99 | to_user INT REFERENCES usr (user_no) ON UPDATE CASCADE, |
---|
100 | rt_id INT REFERENCES relationship_type (rt_id) ON UPDATE CASCADE, |
---|
101 | |
---|
102 | PRIMARY KEY ( from_user, to_user, rt_id ) |
---|
103 | ); |
---|
104 | |
---|
105 | GRANT SELECT,INSERT,UPDATE,DELETE ON relationship TO general; |
---|
106 | |
---|
107 | |
---|
108 | CREATE TABLE locks ( |
---|
109 | dav_name TEXT, |
---|
110 | opaquelocktoken TEXT UNIQUE NOT NULL, |
---|
111 | type TEXT, |
---|
112 | scope TEXT, |
---|
113 | depth INT, |
---|
114 | owner TEXT, |
---|
115 | timeout INTERVAL, |
---|
116 | start TIMESTAMP DEFAULT current_timestamp |
---|
117 | ); |
---|
118 | |
---|
119 | CREATE INDEX locks_dav_name_idx ON locks(dav_name); |
---|
120 | GRANT SELECT,INSERT,UPDATE,DELETE ON locks TO general; |
---|
121 | |
---|
122 | CREATE TABLE property ( |
---|
123 | dav_name TEXT, |
---|
124 | property_name TEXT, |
---|
125 | property_value TEXT, |
---|
126 | changed_on TIMESTAMP DEFAULT current_timestamp, |
---|
127 | changed_by INT REFERENCES usr ( user_no ), |
---|
128 | PRIMARY KEY ( dav_name, property_name ) |
---|
129 | ); |
---|
130 | |
---|
131 | CREATE INDEX properties_dav_name_idx ON property(dav_name); |
---|
132 | GRANT SELECT,INSERT,UPDATE,DELETE ON property TO general; |
---|
133 | |
---|
134 | |
---|
135 | SELECT new_db_revision(1,1,7, 'July' ); |
---|