aboutsummaryrefslogtreecommitdiffstats
path: root/Zotlabs/Update/_1191.php
blob: 015b9a2ca376428661d3b65a139ddc19b861f50a (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
<?php

namespace Zotlabs\Update;

class _1191 {
function run() {

	$r = q("SELECT 1 FROM principals LIMIT 1");

	if($r !== false) {
		return UPDATE_SUCCESS;
	}
	else {
		if(ACTIVE_DBTYPE == DBTYPE_POSTGRES) {
			$r1 = q("CREATE TABLE addressbooks (
					id SERIAL NOT NULL,
					principaluri VARCHAR(255),
					displayname VARCHAR(255),
					uri VARCHAR(200),
					description TEXT,
					synctoken INTEGER NOT NULL DEFAULT 1
				);"
			);

			$r2 = q("ALTER TABLE ONLY addressbooks ADD CONSTRAINT addressbooks_pkey PRIMARY KEY (id);");

			$r3 = q("CREATE UNIQUE INDEX addressbooks_ukey ON addressbooks USING btree (principaluri, uri);");

			$r4 = q("CREATE TABLE cards (
					id SERIAL NOT NULL,
					addressbookid INTEGER NOT NULL,
					carddata BYTEA,
					uri VARCHAR(200),
					lastmodified INTEGER,
					etag VARCHAR(32),
					size INTEGER NOT NULL
				);"
			);

			$r5 = q("ALTER TABLE ONLY cards ADD CONSTRAINT cards_pkey PRIMARY KEY (id);");

			$r6 = q("CREATE UNIQUE INDEX cards_ukey ON cards USING btree (addressbookid, uri);");

			$r7 = q("CREATE TABLE addressbookchanges (
					id SERIAL NOT NULL,
					uri VARCHAR(200) NOT NULL,
					synctoken INTEGER NOT NULL,
					addressbookid INTEGER NOT NULL,
					operation SMALLINT NOT NULL
				);"
			);

			$r8 = q("ALTER TABLE ONLY addressbookchanges ADD CONSTRAINT addressbookchanges_pkey PRIMARY KEY (id);");

			$r9 = q("CREATE INDEX addressbookchanges_addressbookid_synctoken_ix ON addressbookchanges USING btree (addressbookid, synctoken);");

			$r10 = q("CREATE TABLE calendarobjects (
					id SERIAL NOT NULL,
					calendardata BYTEA,
					uri VARCHAR(200),
					calendarid INTEGER NOT NULL,
					lastmodified INTEGER,
					etag VARCHAR(32),
					size INTEGER NOT NULL,
					componenttype VARCHAR(8),
					firstoccurence INTEGER,
					lastoccurence INTEGER,
					uid VARCHAR(200)
				);"
			);

			$r11 = q("ALTER TABLE ONLY calendarobjects ADD CONSTRAINT calendarobjects_pkey PRIMARY KEY (id);");

			$r12 = q("CREATE UNIQUE INDEX calendarobjects_ukey ON calendarobjects USING btree (calendarid, uri);");

			$r13 = q("CREATE TABLE calendars (
					id SERIAL NOT NULL,
					synctoken INTEGER NOT NULL DEFAULT 1,
					components VARCHAR(21)
				);"
			);

			$r14 = q("ALTER TABLE ONLY calendars ADD CONSTRAINT calendars_pkey PRIMARY KEY (id);");

			$r15 = q("CREATE TABLE calendarinstances (
					id SERIAL NOT NULL,
					calendarid INTEGER NOT NULL,
					principaluri VARCHAR(100),
					access SMALLINT NOT NULL DEFAULT '1', -- '1 = owner, 2 = read, 3 = readwrite'
					displayname VARCHAR(100),
					uri VARCHAR(200),
					description TEXT,
					calendarorder INTEGER NOT NULL DEFAULT 0,
					calendarcolor VARCHAR(10),
					timezone TEXT,
					transparent SMALLINT NOT NULL DEFAULT '0',
					share_href VARCHAR(100),
					share_displayname VARCHAR(100),
					share_invitestatus SMALLINT NOT NULL DEFAULT '2' --  '1 = noresponse, 2 = accepted, 3 = declined, 4 = invalid'
				);"
			);

			$r16 = q("ALTER TABLE ONLY calendarinstances ADD CONSTRAINT calendarinstances_pkey PRIMARY KEY (id);");

			$r17 = q("CREATE UNIQUE INDEX calendarinstances_principaluri_uri ON calendarinstances USING btree (principaluri, uri);");

			$r18 = q("CREATE UNIQUE INDEX calendarinstances_principaluri_calendarid ON calendarinstances USING btree (principaluri, calendarid);");

			$r19 = q("CREATE UNIQUE INDEX calendarinstances_principaluri_share_href ON calendarinstances USING btree (principaluri, share_href);");

			$r20 = q("CREATE TABLE calendarsubscriptions (
					id SERIAL NOT NULL,
					uri VARCHAR(200) NOT NULL,
					principaluri VARCHAR(100) NOT NULL,
					source TEXT,
					displayname VARCHAR(100),
					refreshrate VARCHAR(10),
					calendarorder INTEGER NOT NULL DEFAULT 0,
					calendarcolor VARCHAR(10),
					striptodos SMALLINT NULL,
					stripalarms SMALLINT NULL,
					stripattachments SMALLINT NULL,
					lastmodified INTEGER
				);"
			);

			$r21 = q("ALTER TABLE ONLY calendarsubscriptions ADD CONSTRAINT calendarsubscriptions_pkey PRIMARY KEY (id);");

			$r22 = q("CREATE UNIQUE INDEX calendarsubscriptions_ukey ON calendarsubscriptions USING btree (principaluri, uri);");

			$r23 = q("CREATE TABLE calendarchanges (
					id SERIAL NOT NULL,
					uri VARCHAR(200) NOT NULL,
					synctoken INTEGER NOT NULL,
					calendarid INTEGER NOT NULL,
					operation SMALLINT NOT NULL DEFAULT 0
				);"
			);

			$r24 = q("ALTER TABLE ONLY calendarchanges ADD CONSTRAINT calendarchanges_pkey PRIMARY KEY (id);");

			$r25 = q("CREATE INDEX calendarchanges_calendarid_synctoken_ix ON calendarchanges USING btree (calendarid, synctoken);");

			$r26 = q("CREATE TABLE schedulingobjects (
					id SERIAL NOT NULL,
					principaluri VARCHAR(255),
					calendardata BYTEA,
					uri VARCHAR(200),
					lastmodified INTEGER,
					etag VARCHAR(32),
					size INTEGER NOT NULL
				);"
			);

			$r27 = q("CREATE TABLE locks (
					id SERIAL NOT NULL,
					owner VARCHAR(100),
					timeout INTEGER,
					created INTEGER,
					token VARCHAR(100),
					scope SMALLINT,
					depth SMALLINT,
					uri TEXT
				);"
			);

			$r28 = q("ALTER TABLE ONLY locks ADD CONSTRAINT locks_pkey PRIMARY KEY (id);");

			$r29 = q("CREATE INDEX locks_token_ix ON locks USING btree (token);");

			$r30 = q("CREATE INDEX locks_uri_ix ON locks USING btree (uri);");

			$r31 = q("CREATE TABLE principals (
					id SERIAL NOT NULL,
					uri VARCHAR(200) NOT NULL,
					email VARCHAR(80),
					displayname VARCHAR(80)
				);"
			);

			$r32 = q("ALTER TABLE ONLY principals ADD CONSTRAINT principals_pkey PRIMARY KEY (id);");

			$r33 = q("CREATE UNIQUE INDEX principals_ukey ON principals USING btree (uri);");

			$r34 = q("CREATE TABLE groupmembers (
					id SERIAL NOT NULL,
					principal_id INTEGER NOT NULL,
					member_id INTEGER NOT NULL
				);"
			);

			$r35 = q("ALTER TABLE ONLY groupmembers ADD CONSTRAINT groupmembers_pkey PRIMARY KEY (id);");

			$r36 = q("CREATE UNIQUE INDEX groupmembers_ukey ON groupmembers USING btree (principal_id, member_id);");

			$r37 = q("CREATE TABLE propertystorage (
					id SERIAL NOT NULL,
					path VARCHAR(1024) NOT NULL,
					name VARCHAR(100) NOT NULL,
					valuetype INT,
					value BYTEA
				);"
			);

			$r38 = q("ALTER TABLE ONLY propertystorage ADD CONSTRAINT propertystorage_pkey PRIMARY KEY (id);");

			$r39 = q("CREATE UNIQUE INDEX propertystorage_ukey ON propertystorage (path, name);");

			$r40 = q("CREATE TABLE users (
					id SERIAL NOT NULL,
					username VARCHAR(50),
					digesta1 VARCHAR(32)
				);"
			);

			$r41 = q("ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);");

			$r42 = q("CREATE UNIQUE INDEX users_ukey ON users USING btree (username);");

			if(
				$r1 && $r2 && $r3 && $r4 && $r5 && $r6 && $r7 && $r8 && $r9 && $r10
				&& $r11 && $r12 && $r13 && $r14 && $r15 && $r16 && $r17 && $r18 && $r19 && $r20
				&& $r21 && $r22 && $r23 && $r24 && $r25 && $r26 && $r27 && $r28 && $r29 && $r30
				&& $r31 && $r32 && $r33 && $r34 && $r35 && $r36 && $r37 && $r38 && $r39 && $r40
				&& $r41 && $r42
			)
				return UPDATE_SUCCESS;
			return UPDATE_FAILED;
		}
		else {
			$r1 = q("CREATE TABLE if not exists addressbooks (
					id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					principaluri VARBINARY(255),
					displayname VARCHAR(255),
					uri VARBINARY(200),
					description TEXT,
					synctoken INT(11) UNSIGNED NOT NULL DEFAULT '1',
					UNIQUE(principaluri(100), uri(100))
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r2 = q("CREATE TABLE if not exists cards (
					id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					addressbookid INT(11) UNSIGNED NOT NULL,
					carddata MEDIUMBLOB,
					uri VARBINARY(200),
					lastmodified INT(11) UNSIGNED,
					etag VARBINARY(32),
					size INT(11) UNSIGNED NOT NULL
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r3 = q("CREATE TABLE if not exists addressbookchanges (
					id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					uri VARBINARY(200) NOT NULL,
					synctoken INT(11) UNSIGNED NOT NULL,
					addressbookid INT(11) UNSIGNED NOT NULL,
					operation TINYINT(1) NOT NULL,
					INDEX addressbookid_synctoken (addressbookid, synctoken)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r4 = q("CREATE TABLE if not exists calendarobjects (
					id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					calendardata MEDIUMBLOB,
					uri VARBINARY(200),
					calendarid INTEGER UNSIGNED NOT NULL,
					lastmodified INT(11) UNSIGNED,
					etag VARBINARY(32),
					size INT(11) UNSIGNED NOT NULL,
					componenttype VARBINARY(8),
					firstoccurence INT(11) UNSIGNED,
					lastoccurence INT(11) UNSIGNED,
					uid VARBINARY(200),
					UNIQUE(calendarid, uri),
					INDEX calendarid_time (calendarid, firstoccurence)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r5 = q("CREATE TABLE if not exists calendars (
					id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					synctoken INTEGER UNSIGNED NOT NULL DEFAULT '1',
					components VARBINARY(21)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r6 = q("CREATE TABLE if not exists calendarinstances (
					id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					calendarid INTEGER UNSIGNED NOT NULL,
					principaluri VARBINARY(100),
					access TINYINT(1) NOT NULL DEFAULT '1' COMMENT '1 = owner, 2 = read, 3 = readwrite',
		 			displayname VARCHAR(100),
					uri VARBINARY(200),
					description TEXT,
		 			calendarorder INT(11) UNSIGNED NOT NULL DEFAULT '0',
					calendarcolor VARBINARY(10),
					timezone TEXT,
					transparent TINYINT(1) NOT NULL DEFAULT '0',
					share_href VARBINARY(100),
					share_displayname VARCHAR(100),
					share_invitestatus TINYINT(1) NOT NULL DEFAULT '2' COMMENT '1 = noresponse, 2 = accepted, 3 = declined, 4 = invalid',
					UNIQUE(principaluri, uri),
					UNIQUE(calendarid, principaluri),
					UNIQUE(calendarid, share_href)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r7 = q("CREATE TABLE if not exists calendarchanges (
					id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					uri VARBINARY(200) NOT NULL,
					synctoken INT(11) UNSIGNED NOT NULL,
					calendarid INT(11) UNSIGNED NOT NULL,
					operation TINYINT(1) NOT NULL,
					INDEX calendarid_synctoken (calendarid, synctoken)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r8 = q("CREATE TABLE if not exists calendarsubscriptions (
					id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					uri VARBINARY(200) NOT NULL,
					principaluri VARBINARY(100) NOT NULL,
					source TEXT,
					displayname VARCHAR(100),
					refreshrate VARCHAR(10),
					calendarorder INT(11) UNSIGNED NOT NULL DEFAULT '0',
					calendarcolor VARBINARY(10),
					striptodos TINYINT(1) NULL,
					stripalarms TINYINT(1) NULL,
					stripattachments TINYINT(1) NULL,
					lastmodified INT(11) UNSIGNED,
					UNIQUE(principaluri, uri)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r9 = q("CREATE TABLE if not exists schedulingobjects (
					id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					principaluri VARBINARY(255),
					calendardata MEDIUMBLOB,
					uri VARBINARY(200),
					lastmodified INT(11) UNSIGNED,
					etag VARBINARY(32),
					size INT(11) UNSIGNED NOT NULL
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r10 = q("CREATE TABLE if not exists locks (
					id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					owner VARCHAR(100),
					timeout INTEGER UNSIGNED,
					created INTEGER,
					token VARBINARY(100),
					scope TINYINT,
					depth TINYINT,
					uri VARBINARY(1000),
					INDEX(token),
					INDEX(uri(100))
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r11 = q("CREATE TABLE if not exists principals (
					id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					uri VARBINARY(200) NOT NULL,
					email VARBINARY(80),
					displayname VARCHAR(80),
					UNIQUE(uri)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r12 = q("CREATE TABLE if not exists groupmembers (
					id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					principal_id INTEGER UNSIGNED NOT NULL,
					member_id INTEGER UNSIGNED NOT NULL,
					UNIQUE(principal_id, member_id)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r13 = q("CREATE TABLE if not exists propertystorage (
					id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					path VARBINARY(1024) NOT NULL,
					name VARBINARY(100) NOT NULL,
					valuetype INT UNSIGNED,
					value MEDIUMBLOB
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r14 = q("CREATE UNIQUE INDEX path_property ON propertystorage (path(600), name(100));");

			$r15 = q("CREATE TABLE if not exists users (
					id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					username VARBINARY(50),
					digesta1 VARBINARY(32),
					UNIQUE(username)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			$r16 = q("CREATE TABLE if not exists calendarinstances (
					id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					calendarid INTEGER UNSIGNED NOT NULL,
					principaluri VARBINARY(100),
					access TINYINT(1) NOT NULL DEFAULT '1' COMMENT '1 = owner, 2 = read, 3 = readwrite',
					displayname VARCHAR(100),
					uri VARBINARY(200),
					description TEXT,
					calendarorder INT(11) UNSIGNED NOT NULL DEFAULT '0',
					calendarcolor VARBINARY(10),
					timezone TEXT,
					transparent TINYINT(1) NOT NULL DEFAULT '0',
					share_href VARBINARY(100),
					share_displayname VARCHAR(100),
					share_invitestatus TINYINT(1) NOT NULL DEFAULT '2' COMMENT '1 = noresponse, 2 = accepted, 3 = declined, 4 = invalid',
					UNIQUE(principaluri, uri),
					UNIQUE(calendarid, principaluri),
					UNIQUE(calendarid, share_href)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
			);

			if($r1 && $r2 && $r3 && $r4 && $r5 && $r6 && $r7 && $r8 && $r9 && $r10 && $r11 && $r12 && $r13 && $r14 && $r15 && $r16)
				return UPDATE_SUCCESS;
			return UPDATE_FAILED;
		}
	}
}


}