[3733] | 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' ); |
---|