Mercurial > hg > rc1

comparison plugins/calendar/drivers/database/SQL/postgres.initial.sql @ 3:f6fe4b6ae66a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
calendar plugin nearly as distributed
author Charlie Root
date Sat, 13 Jan 2018 08:56:12 -0500
parents
children
comparison
equal deleted inserted replaced
2:c828b0fd4a6e 3:f6fe4b6ae66a
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');