|
3
|
1 /**
|
|
|
2 * RoundCube Calendar
|
|
|
3 *
|
|
|
4 * Plugin to add a calendar to RoundCube.
|
|
|
5 *
|
|
|
6 * @author Lazlo Westerhof
|
|
|
7 * @author Albert Lee
|
|
|
8 * @author Aleksander Machniak <machniak@kolabsys.com>
|
|
|
9 * @licence GNU AGPL
|
|
|
10 * @copyright (c) 2010 Lazlo Westerhof - Netherlands
|
|
|
11 * @copyright (c) 2014 Kolab Systems AG
|
|
|
12 *
|
|
|
13 **/
|
|
|
14
|
|
|
15
|
|
|
16 CREATE SEQUENCE calendars_seq
|
|
|
17 INCREMENT BY 1
|
|
|
18 NO MAXVALUE
|
|
|
19 NO MINVALUE
|
|
|
20 CACHE 1;
|
|
|
21
|
|
|
22 CREATE TABLE calendars (
|
|
|
23 calendar_id integer DEFAULT nextval('calendars_seq'::regclass) NOT NULL,
|
|
|
24 user_id integer NOT NULL
|
|
|
25 REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
|
26 name varchar(255) NOT NULL,
|
|
|
27 color varchar(8) NOT NULL,
|
|
|
28 showalarms smallint NOT NULL DEFAULT 1,
|
|
|
29 PRIMARY KEY (calendar_id)
|
|
|
30 );
|
|
|
31
|
|
|
32 CREATE INDEX calendars_user_id_idx ON calendars (user_id, name);
|
|
|
33
|
|
|
34
|
|
|
35 CREATE SEQUENCE events_seq
|
|
|
36 INCREMENT BY 1
|
|
|
37 NO MAXVALUE
|
|
|
38 NO MINVALUE
|
|
|
39 CACHE 1;
|
|
|
40
|
|
|
41 CREATE TABLE events (
|
|
|
42 event_id integer DEFAULT nextval('events_seq'::regclass) NOT NULL,
|
|
|
43 calendar_id integer NOT NULL
|
|
|
44 REFERENCES calendars (calendar_id) ON UPDATE CASCADE ON DELETE CASCADE,
|
|
|
45 recurrence_id integer NOT NULL DEFAULT 0,
|
|
|
46 uid varchar(255) NOT NULL DEFAULT '',
|
|
|
47 instance varchar(16) NOT NULL DEFAULT '',
|
|
|
48 isexception smallint NOT NULL DEFAULT '0',
|
|
|
49 created timestamp without time zone DEFAULT now() NOT NULL,
|
|
|
50 changed timestamp without time zone DEFAULT now(),
|
|
|
51 sequence integer NOT NULL DEFAULT 0,
|
|
|
52 "start" timestamp without time zone DEFAULT now() NOT NULL,
|
|
|
53 "end" timestamp without time zone DEFAULT now() NOT NULL,
|
|
|
54 recurrence varchar(255) DEFAULT NULL,
|
|
|
55 title character varying(255) NOT NULL DEFAULT '',
|
|
|
56 description text NOT NULL DEFAULT '',
|
|
|
57 location character varying(255) NOT NULL DEFAULT '',
|
|
|
58 categories character varying(255) NOT NULL DEFAULT '',
|
|
|
59 url character varying(255) NOT NULL DEFAULT '',
|
|
|
60 all_day smallint NOT NULL DEFAULT 0,
|
|
|
61 free_busy smallint NOT NULL DEFAULT 0,
|
|
|
62 priority smallint NOT NULL DEFAULT 0,
|
|
|
63 sensitivity smallint NOT NULL DEFAULT 0,
|
|
|
64 status character varying(32) NOT NULL DEFAULT '',
|
|
|
65 alarms text DEFAULT NULL,
|
|
|
66 attendees text DEFAULT NULL,
|
|
|
67 notifyat timestamp without time zone DEFAULT NULL,
|
|
|
68 PRIMARY KEY (event_id)
|
|
|
69 );
|
|
|
70
|
|
|
71 CREATE INDEX events_calendar_id_notifyat_idx ON events (calendar_id, notifyat);
|
|
|
72 CREATE INDEX events_uid_idx ON events (uid);
|
|
|
73 CREATE INDEX events_recurrence_id_idx ON events (recurrence_id);
|
|
|
74
|
|
|
75
|
|
|
76 CREATE SEQUENCE attachments_seq
|
|
|
77 INCREMENT BY 1
|
|
|
78 NO MAXVALUE
|
|
|
79 NO MINVALUE
|
|
|
80 CACHE 1;
|
|
|
81
|
|
|
82 CREATE TABLE attachments (
|
|
|
83 attachment_id integer DEFAULT nextval('attachments_seq'::regclass) NOT NULL,
|
|
|
84 event_id integer NOT NULL
|
|
|
85 REFERENCES events (event_id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
86 filename varchar(255) NOT NULL DEFAULT '',
|
|
|
87 mimetype varchar(255) NOT NULL DEFAULT '',
|
|
|
88 size integer NOT NULL DEFAULT 0,
|
|
|
89 data text NOT NULL DEFAULT '',
|
|
|
90 PRIMARY KEY (attachment_id)
|
|
|
91 );
|
|
|
92
|
|
|
93 CREATE INDEX attachments_user_id_idx ON attachments (event_id);
|
|
|
94
|
|
|
95
|
|
|
96 CREATE TABLE itipinvitations (
|
|
|
97 token varchar(64) NOT NULL,
|
|
|
98 event_uid varchar(255) NOT NULL,
|
|
|
99 user_id integer NOT NULL
|
|
|
100 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
101 event TEXT NOT NULL,
|
|
|
102 expires timestamp without time zone DEFAULT NULL,
|
|
|
103 cancelled smallint NOT NULL DEFAULT 0,
|
|
|
104 PRIMARY KEY (token)
|
|
|
105 );
|
|
|
106
|
|
|
107 CREATE INDEX itipinvitations_user_id_event_uid_idx ON itipinvitations (user_id, event_uid);
|
|
|
108
|
|
|
109 INSERT INTO system (name, value) VALUES ('calendar-database-version', '2015022700');
|
