source: contrib/davical/dba/windows/rscds.sql @ 3733

Revision 3733, 3.6 KB checked in by gabriel.malheiros, 13 years ago (diff)

Ticket #1541 - <Davical customizado para o Expresso.Utiliza Caldav e CardDav?>

Line 
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.
9CREATE 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
22GRANT 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... :-)
26CREATE TABLE time_zone (
27  tz_id TEXT PRIMARY KEY,
28  tz_locn TEXT,
29  tz_spec TEXT
30);
31GRANT SELECT,INSERT ON time_zone TO general;
32
33-- The parsed calendar item.  Here we have pulled those events/todos/journals apart somewhat.
34CREATE 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
65GRANT SELECT,INSERT,UPDATE,DELETE ON calendar_item TO general;
66
67
68-- Something that can look like a filesystem hierarchy where we store stuff
69CREATE 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
82GRANT 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.
86CREATE 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
94GRANT SELECT,INSERT,UPDATE,DELETE ON relationship_type TO general;
95GRANT SELECT,UPDATE ON relationship_type_rt_id_seq TO general;
96
97CREATE 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
105GRANT SELECT,INSERT,UPDATE,DELETE ON relationship TO general;
106
107
108CREATE 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
119CREATE INDEX locks_dav_name_idx ON locks(dav_name);
120GRANT SELECT,INSERT,UPDATE,DELETE ON locks TO general;
121
122CREATE 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
131CREATE INDEX properties_dav_name_idx ON property(dav_name);
132GRANT SELECT,INSERT,UPDATE,DELETE ON property TO general;
133
134
135SELECT new_db_revision(1,1,7, 'July' );
Note: See TracBrowser for help on using the repository browser.