Merge lp:~thomir-deactivatedaccount/sloecode/fix-dbpatches into lp:sloecode

Proposed by Thomi Richards
Status: Merged
Merged at revision: 150
Proposed branch: lp:~thomir-deactivatedaccount/sloecode/fix-dbpatches
Merge into: lp:sloecode
Diff against target: 621 lines (+191/-309)
11 files modified
dbpatches/README.txt (+13/-7)
dbpatches/patches/mysql/1 (+0/-6)
dbpatches/patches/mysql/1.1 (+33/-0)
dbpatches/patches/mysql/2 (+0/-16)
dbpatches/patches/mysql/3 (+0/-9)
dbpatches/patches/sqlite3/1 (+0/-27)
dbpatches/patches/sqlite3/1.1 (+88/-0)
dbpatches/patches/sqlite3/2 (+0/-50)
dbpatches/patches/sqlite3/3 (+0/-9)
dbpatches/schema/mysql (+27/-151)
dbpatches/schema/sqlite3 (+30/-34)
To merge this branch: bzr merge lp:~thomir-deactivatedaccount/sloecode/fix-dbpatches
Reviewer Review Type Date Requested Status
sloecode Pending
Review via email: mp+100315@code.launchpad.net

Description of the change

Fixes the database patches, so dbconfig-common works correctly.

To post a comment you must log in.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'dbpatches/README.txt'
2--- dbpatches/README.txt 2011-06-27 07:31:27 +0000
3+++ dbpatches/README.txt 2012-04-01 00:02:18 +0000
4@@ -4,14 +4,20 @@
5
6 Scripts in this folder are broken into two categories:
7
8- * Schema definition scripts are used to create the initial database and MUST
9- NOT BE CHANGED, unless you really know what you are doing, and are also
10- going to change the debian packaging scripts. These scripts are named after
11- the database type, and are stored in the schema directory.
12+ * Schema definition scripts are used to create the initial database at install
13+ time. These scripts must be kept up-to-date. If a schema change is
14+ introduced, you must make sure that the schema files match the current
15+ database schema. These scripts are named after the database type, and are
16+ stored in the schema directory.
17+
18+ Note that the schema file may in fact be applied to an existing database. It
19+ must not alter any existing data. So for example, create table statements
20+ must be 'CREATE TABLE IF NOT EXISTS'.
21
22 * Schema patches change the original schema definition over time. They are
23 stored in the patches directory, and are then further separated into
24- separate folders for database type. Patch scripts are numbered, and must be
25- numbered consecutively. For this reason, if you need to make a change to the
26- database schema, you MUST get a patch number from the code sloecode developers.
27+ separate folders for database type. Patch scripts are numbered according to
28+ the version of the package they will be installed in. If you are creating
29+ the first patch of a new release, you will need to create a new patch file,
30+ otherwise append your patch to an existing file.
31
32
33=== removed file 'dbpatches/patches/mysql/1'
34--- dbpatches/patches/mysql/1 2011-06-27 07:31:27 +0000
35+++ dbpatches/patches/mysql/1 1970-01-01 00:00:00 +0000
36@@ -1,6 +0,0 @@
37-/*
38-This patch alters the projects table so the displayname column no longer
39-has a unique constraint.
40-*/
41-
42-ALTER TABLE project DROP INDEX displayname;
43\ No newline at end of file
44
45=== added file 'dbpatches/patches/mysql/1.1'
46--- dbpatches/patches/mysql/1.1 1970-01-01 00:00:00 +0000
47+++ dbpatches/patches/mysql/1.1 2012-04-01 00:02:18 +0000
48@@ -0,0 +1,33 @@
49+/*
50+This patch alters the projects table so the displayname column no longer
51+has a unique constraint.
52+*/
53+
54+ALTER TABLE project DROP INDEX displayname;
55+
56+/*
57+Alter the project table to reduce the size of the name and displayname columns
58+to sensible lengths.
59+*/
60+
61+ALTER TABLE project
62+ MODIFY COLUMN `name` varchar(24) DEFAULT NULL,
63+ MODIFY COLUMN `displayname` varchar(32) DEFAULT NULL;
64+
65+/*
66+Similar treatment for the user table:
67+*/
68+
69+ALTER TABLE person
70+ MODIFY COLUMN `login` varchar(24) DEFAULT NULL,
71+ MODIFY COLUMN `name` varchar(32) DEFAULT NULL;
72+
73+/*
74+This patch add the public column to the projects table, defaulting all projects to private.
75+*/
76+
77+-- add public column:
78+ALTER TABLE project ADD public BOOLEAN CHECK (public IN (0, 1));
79+
80+- default to private:
81+UPDATE project SET public=0;
82
83=== removed file 'dbpatches/patches/mysql/2'
84--- dbpatches/patches/mysql/2 2011-07-02 07:13:32 +0000
85+++ dbpatches/patches/mysql/2 1970-01-01 00:00:00 +0000
86@@ -1,16 +0,0 @@
87-/*
88-Alter the project table to reduce the size of the name and displayname columns
89-to sensible lengths.
90-*/
91-
92-ALTER TABLE project
93- MODIFY COLUMN `name` varchar(24) DEFAULT NULL,
94- MODIFY COLUMN `displayname` varchar(32) DEFAULT NULL;
95-
96-/*
97-Similar treatment for the user table:
98-*/
99-
100-ALTER TABLE person
101- MODIFY COLUMN `login` varchar(24) DEFAULT NULL,
102- MODIFY COLUMN `name` varchar(32) DEFAULT NULL;
103\ No newline at end of file
104
105=== removed file 'dbpatches/patches/mysql/3'
106--- dbpatches/patches/mysql/3 2011-10-23 19:12:09 +0000
107+++ dbpatches/patches/mysql/3 1970-01-01 00:00:00 +0000
108@@ -1,9 +0,0 @@
109-/*
110-This patch add the public column to the projects table, defaulting all projects to private.
111-*/
112-
113--- add public column:
114-ALTER TABLE project ADD public BOOLEAN CHECK (public IN (0, 1));
115-
116-- default to private:
117-UPDATE project SET public=0;
118
119=== removed file 'dbpatches/patches/sqlite3/1'
120--- dbpatches/patches/sqlite3/1 2011-06-27 07:31:27 +0000
121+++ dbpatches/patches/sqlite3/1 1970-01-01 00:00:00 +0000
122@@ -1,27 +0,0 @@
123-/*
124-This patch alters the projects table so the displayname column no longer
125-has a unique constraint. Unfortunately, SQLite does not allow you to remove
126-column constraint with the ALTER TABLE command, so we need to rename the table,
127-create the new table without the UNIQUE constraint on the displayname column,
128-insert all the data from the renamed table, and finally delete the old, renamed
129-table.
130-*/
131-
132--- rename old table:
133-ALTER TABLE project RENAME TO project_old;
134-
135--- create new table:
136-CREATE TABLE project (
137- id INTEGER NOT NULL,
138- name VARCHAR(200),
139- displayname VARCHAR(200),
140- description TEXT,
141- PRIMARY KEY (id),
142- UNIQUE (name)
143-);
144-
145--- insert data:
146-INSERT INTO project SELECT * FROM project_old;
147-
148--- remove old table:
149-DROP TABLE project_old;
150\ No newline at end of file
151
152=== added file 'dbpatches/patches/sqlite3/1.1'
153--- dbpatches/patches/sqlite3/1.1 1970-01-01 00:00:00 +0000
154+++ dbpatches/patches/sqlite3/1.1 2012-04-01 00:02:18 +0000
155@@ -0,0 +1,88 @@
156+/*
157+This patch alters the projects table so the displayname column no longer
158+has a unique constraint. Unfortunately, SQLite does not allow you to remove
159+column constraint with the ALTER TABLE command, so we need to rename the table,
160+create the new table without the UNIQUE constraint on the displayname column,
161+insert all the data from the renamed table, and finally delete the old, renamed
162+table.
163+*/
164+
165+-- rename old table:
166+ALTER TABLE project RENAME TO project_old;
167+
168+-- create new table:
169+CREATE TABLE project (
170+ id INTEGER NOT NULL,
171+ name VARCHAR(200),
172+ displayname VARCHAR(200),
173+ description TEXT,
174+ PRIMARY KEY (id),
175+ UNIQUE (name)
176+);
177+
178+-- insert data:
179+INSERT INTO project SELECT * FROM project_old;
180+
181+-- remove old table:
182+DROP TABLE project_old;
183+
184+/*
185+This patch alters the length of the name and displayname columns in the projects
186+table to more sensible values. Unfortunately the SQLAlchemy ALTER TABLE command
187+is pretty crap, and doesn't allow you to alter any existing table columns, so we
188+need to rename the table, create the new table, and then copy all the data across.
189+*/
190+
191+-- rename old table:
192+ALTER TABLE project RENAME TO project_old;
193+
194+-- create new table:
195+CREATE TABLE project (
196+ id INTEGER NOT NULL,
197+ name VARCHAR(24),
198+ displayname VARCHAR(32),
199+ description TEXT,
200+ PRIMARY KEY (id),
201+ UNIQUE (name)
202+);
203+
204+-- insert data:
205+INSERT INTO project SELECT * FROM project_old;
206+
207+-- remove old table:
208+DROP TABLE project_old;
209+
210+/*
211+Similar treatment for the user table:
212+*/
213+
214+-- rename old table:
215+ALTER TABLE person RENAME TO person_old;
216+
217+-- create new table:
218+CREATE TABLE person (
219+ id INTEGER NOT NULL,
220+ login VARCHAR(24),
221+ password VARCHAR(200),
222+ name VARCHAR(32),
223+ email VARCHAR(200),
224+ PRIMARY KEY (id),
225+ UNIQUE (login)
226+);
227+
228+
229+-- insert data:
230+INSERT INTO person SELECT * FROM person_old;
231+
232+-- remove old table:
233+DROP TABLE person_old;
234+
235+/*
236+This patch add the public column to the projects table, defaulting all projects to private.
237+*/
238+
239+-- add public column:
240+ALTER TABLE project ADD public BOOLEAN CHECK (public IN (0, 1));
241+
242+-- default to private:
243+UPDATE project SET public=0;
244
245=== removed file 'dbpatches/patches/sqlite3/2'
246--- dbpatches/patches/sqlite3/2 2011-07-02 07:13:32 +0000
247+++ dbpatches/patches/sqlite3/2 1970-01-01 00:00:00 +0000
248@@ -1,50 +0,0 @@
249-/*
250-This patch alters the length of the name and displayname columns in the projects
251-table to more sensible values. Unfortunately the SQLAlchemy ALTER TABLE command
252-is pretty crap, and doesn't allow you to alter any existing table columns, so we
253-need to rename the table, create the new table, and then copy all the data across.
254-*/
255-
256--- rename old table:
257-ALTER TABLE project RENAME TO project_old;
258-
259--- create new table:
260-CREATE TABLE project (
261- id INTEGER NOT NULL,
262- name VARCHAR(24),
263- displayname VARCHAR(32),
264- description TEXT,
265- PRIMARY KEY (id),
266- UNIQUE (name)
267-);
268-
269--- insert data:
270-INSERT INTO project SELECT * FROM project_old;
271-
272--- remove old table:
273-DROP TABLE project_old;
274-
275-/*
276-Similar treatment for the user table:
277-*/
278-
279--- rename old table:
280-ALTER TABLE person RENAME TO person_old;
281-
282--- create new table:
283-CREATE TABLE person (
284- id INTEGER NOT NULL,
285- login VARCHAR(24),
286- password VARCHAR(200),
287- name VARCHAR(32),
288- email VARCHAR(200),
289- PRIMARY KEY (id),
290- UNIQUE (login)
291-);
292-
293-
294--- insert data:
295-INSERT INTO person SELECT * FROM person_old;
296-
297--- remove old table:
298-DROP TABLE person_old;
299\ No newline at end of file
300
301=== removed file 'dbpatches/patches/sqlite3/3'
302--- dbpatches/patches/sqlite3/3 2011-10-25 07:04:37 +0000
303+++ dbpatches/patches/sqlite3/3 1970-01-01 00:00:00 +0000
304@@ -1,9 +0,0 @@
305-/*
306-This patch add the public column to the projects table, defaulting all projects to private.
307-*/
308-
309--- add public column:
310-ALTER TABLE project ADD public BOOLEAN CHECK (public IN (0, 1));
311-
312--- default to private:
313-UPDATE project SET public=0;
314
315=== modified file 'dbpatches/schema/mysql'
316--- dbpatches/schema/mysql 2011-06-27 07:31:27 +0000
317+++ dbpatches/schema/mysql 2012-04-01 00:02:18 +0000
318@@ -1,79 +1,20 @@
319--- MySQL dump 10.13 Distrib 5.1.54, for debian-linux-gnu (x86_64)
320---
321--- Host: localhost Database: sloecode
322--- ------------------------------------------------------
323--- Server version 5.1.54-1ubuntu4
324-
325-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
326-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
327-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
328-/*!40101 SET NAMES utf8 */;
329-/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
330-/*!40103 SET TIME_ZONE='+00:00' */;
331-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
332-/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
333-/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
334-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
335-
336---
337--- Table structure for table `database_version`
338---
339-
340-DROP TABLE IF EXISTS `database_version`;
341-/*!40101 SET @saved_cs_client = @@character_set_client */;
342-/*!40101 SET character_set_client = utf8 */;
343-CREATE TABLE `database_version` (
344- `version` int(11) NOT NULL AUTO_INCREMENT,
345- PRIMARY KEY (`version`)
346-) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
347-/*!40101 SET character_set_client = @saved_cs_client */;
348-
349---
350--- Dumping data for table `database_version`
351---
352-
353-LOCK TABLES `database_version` WRITE;
354-/*!40000 ALTER TABLE `database_version` DISABLE KEYS */;
355-INSERT INTO `database_version` VALUES (1);
356-/*!40000 ALTER TABLE `database_version` ENABLE KEYS */;
357-UNLOCK TABLES;
358-
359---
360--- Table structure for table `person`
361---
362-
363-DROP TABLE IF EXISTS `person`;
364-/*!40101 SET @saved_cs_client = @@character_set_client */;
365-/*!40101 SET character_set_client = utf8 */;
366-CREATE TABLE `person` (
367+
368+CREATE TABLE IF NOT EXISTS `person` (
369 `id` int(11) NOT NULL AUTO_INCREMENT,
370- `login` varchar(200) DEFAULT NULL,
371+ `login` varchar(24) DEFAULT NULL,
372 `password` varchar(200) DEFAULT NULL,
373- `name` varchar(200) DEFAULT NULL,
374+ `name` varchar(32) DEFAULT NULL,
375 `email` varchar(200) DEFAULT NULL,
376 PRIMARY KEY (`id`),
377 UNIQUE KEY `login` (`login`)
378-) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
379-/*!40101 SET character_set_client = @saved_cs_client */;
380-
381---
382--- Dumping data for table `person`
383---
384+);
385
386 LOCK TABLES `person` WRITE;
387-/*!40000 ALTER TABLE `person` DISABLE KEYS */;
388 INSERT INTO `person` VALUES (1,'admin','8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918','Default Administrator',NULL);
389-/*!40000 ALTER TABLE `person` ENABLE KEYS */;
390 UNLOCK TABLES;
391
392---
393--- Table structure for table `person_keys`
394---
395
396-DROP TABLE IF EXISTS `person_keys`;
397-/*!40101 SET @saved_cs_client = @@character_set_client */;
398-/*!40101 SET character_set_client = utf8 */;
399-CREATE TABLE `person_keys` (
400+CREATE TABLE IF NOT EXISTS `person_keys` (
401 `id` int(11) NOT NULL AUTO_INCREMENT,
402 `person_id` int(11) DEFAULT NULL,
403 `key_name` text,
404@@ -81,106 +22,41 @@
405 `key_data` text,
406 `key_comment` text,
407 PRIMARY KEY (`id`),
408- KEY `person_id` (`person_id`)
409-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
410-/*!40101 SET character_set_client = @saved_cs_client */;
411-
412---
413--- Dumping data for table `person_keys`
414---
415-
416-LOCK TABLES `person_keys` WRITE;
417-/*!40000 ALTER TABLE `person_keys` DISABLE KEYS */;
418-/*!40000 ALTER TABLE `person_keys` ENABLE KEYS */;
419-UNLOCK TABLES;
420-
421---
422--- Table structure for table `person_project_membership`
423---
424-
425-DROP TABLE IF EXISTS `person_project_membership`;
426-/*!40101 SET @saved_cs_client = @@character_set_client */;
427-/*!40101 SET character_set_client = utf8 */;
428-CREATE TABLE `person_project_membership` (
429+ KEY `person_id` (`person_id`),
430+ CONSTRAINT `person_keys_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
431+);
432+
433+CREATE TABLE IF NOT EXISTS `person_project_membership` (
434 `id` int(11) NOT NULL AUTO_INCREMENT,
435 `person_id` int(11) DEFAULT NULL,
436 `project_id` int(11) DEFAULT NULL,
437 `role` enum('Observer','Developer','Manager') DEFAULT NULL,
438 PRIMARY KEY (`id`),
439+ KEY `person_id` (`person_id`),
440 KEY `project_id` (`project_id`),
441- KEY `person_id` (`person_id`)
442-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
443-/*!40101 SET character_set_client = @saved_cs_client */;
444-
445---
446--- Dumping data for table `person_project_membership`
447---
448-
449-LOCK TABLES `person_project_membership` WRITE;
450-/*!40000 ALTER TABLE `person_project_membership` DISABLE KEYS */;
451-/*!40000 ALTER TABLE `person_project_membership` ENABLE KEYS */;
452-UNLOCK TABLES;
453-
454---
455--- Table structure for table `project`
456---
457-
458-DROP TABLE IF EXISTS `project`;
459-/*!40101 SET @saved_cs_client = @@character_set_client */;
460-/*!40101 SET character_set_client = utf8 */;
461-CREATE TABLE `project` (
462+ CONSTRAINT `person_project_membership_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
463+ CONSTRAINT `person_project_membership_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)
464+);
465+
466+CREATE TABLE IF NOT EXISTS `project` (
467 `id` int(11) NOT NULL AUTO_INCREMENT,
468- `name` varchar(200) DEFAULT NULL,
469- `displayname` varchar(200) DEFAULT NULL,
470+ `name` varchar(24) DEFAULT NULL,
471+ `public` tinyint(1) DEFAULT NULL,
472+ `displayname` varchar(32) DEFAULT NULL,
473 `description` text,
474 PRIMARY KEY (`id`),
475- UNIQUE KEY `displayname` (`displayname`),
476 UNIQUE KEY `name` (`name`)
477-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
478-/*!40101 SET character_set_client = @saved_cs_client */;
479-
480---
481--- Dumping data for table `project`
482---
483-
484-LOCK TABLES `project` WRITE;
485-/*!40000 ALTER TABLE `project` DISABLE KEYS */;
486-/*!40000 ALTER TABLE `project` ENABLE KEYS */;
487-UNLOCK TABLES;
488-
489---
490--- Table structure for table `site_role`
491---
492-
493-DROP TABLE IF EXISTS `site_role`;
494-/*!40101 SET @saved_cs_client = @@character_set_client */;
495-/*!40101 SET character_set_client = utf8 */;
496-CREATE TABLE `site_role` (
497+);
498+
499+CREATE TABLE IF NOT EXISTS `site_role` (
500 `id` int(11) NOT NULL AUTO_INCREMENT,
501 `person_id` int(11) DEFAULT NULL,
502 `role` enum('User Admin','Project Admin') DEFAULT NULL,
503 PRIMARY KEY (`id`),
504- KEY `person_id` (`person_id`)
505-) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
506-/*!40101 SET character_set_client = @saved_cs_client */;
507-
508---
509--- Dumping data for table `site_role`
510---
511+ KEY `person_id` (`person_id`),
512+ CONSTRAINT `site_role_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
513+);
514
515 LOCK TABLES `site_role` WRITE;
516-/*!40000 ALTER TABLE `site_role` DISABLE KEYS */;
517-INSERT INTO `site_role` VALUES (1,1,'User Admin'),(2,1,'Project Admin');
518-/*!40000 ALTER TABLE `site_role` ENABLE KEYS */;
519+REPLACE INTO `site_role` VALUES (1,1,'User Admin'),(2,1,'Project Admin');
520 UNLOCK TABLES;
521-/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
522-
523-/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
524-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
525-/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
526-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
527-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
528-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
529-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
530-
531--- Dump completed on 2011-06-04 15:36:54
532
533=== modified file 'dbpatches/schema/sqlite3'
534--- dbpatches/schema/sqlite3 2011-06-27 07:31:27 +0000
535+++ dbpatches/schema/sqlite3 2012-04-01 00:02:18 +0000
536@@ -1,50 +1,36 @@
537 PRAGMA foreign_keys=OFF;
538 BEGIN TRANSACTION;
539-CREATE TABLE project (
540- id INTEGER NOT NULL,
541- name VARCHAR(200),
542- displayname VARCHAR(200),
543- description TEXT,
544- PRIMARY KEY (id),
545- UNIQUE (displayname),
546- UNIQUE (name)
547-);
548-CREATE TABLE database_version (
549- version INTEGER NOT NULL,
550- PRIMARY KEY (version)
551-);
552-INSERT INTO "database_version" VALUES(1);
553-CREATE TABLE person (
554- id INTEGER NOT NULL,
555- login VARCHAR(200),
556+CREATE TABLE IF NOT EXISTS person (
557+ id INTEGER NOT NULL,
558+ login VARCHAR(24),
559 password VARCHAR(200),
560- name VARCHAR(200),
561+ name VARCHAR(32),
562 email VARCHAR(200),
563 PRIMARY KEY (id),
564 UNIQUE (login)
565 );
566-INSERT INTO "person" VALUES(1,'admin','8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918','Default Administrator',NULL);
567-CREATE TABLE person_project_membership (
568+INSERT OR REPLACE INTO "person" VALUES(1,'admin','8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918','Default Administrator',NULL);
569+CREATE TABLE IF NOT EXISTS project (
570+ id INTEGER NOT NULL,
571+ name VARCHAR(24),
572+ public BOOLEAN,
573+ displayname VARCHAR(32),
574+ description TEXT,
575+ PRIMARY KEY (id),
576+ UNIQUE (name),
577+ CHECK (public IN (0, 1))
578+);
579+CREATE TABLE IF NOT EXISTS person_project_membership (
580 id INTEGER NOT NULL,
581 person_id INTEGER,
582 project_id INTEGER,
583 role VARCHAR(9),
584 PRIMARY KEY (id),
585- CHECK (role IN ('Observer', 'Developer', 'Manager')),
586+ FOREIGN KEY(person_id) REFERENCES person (id),
587 FOREIGN KEY(project_id) REFERENCES project (id),
588- FOREIGN KEY(person_id) REFERENCES person (id)
589-);
590-CREATE TABLE site_role (
591- id INTEGER NOT NULL,
592- person_id INTEGER,
593- role VARCHAR(13),
594- PRIMARY KEY (id),
595- CHECK (role IN ('User Admin', 'Project Admin')),
596- FOREIGN KEY(person_id) REFERENCES person (id)
597-);
598-INSERT INTO "site_role" VALUES(1,1,'User Admin');
599-INSERT INTO "site_role" VALUES(2,1,'Project Admin');
600-CREATE TABLE person_keys (
601+ CHECK (role IN ('Observer', 'Developer', 'Manager'))
602+);
603+CREATE TABLE IF NOT EXISTS person_keys (
604 id INTEGER NOT NULL,
605 person_id INTEGER,
606 key_name TEXT,
607@@ -54,4 +40,14 @@
608 PRIMARY KEY (id),
609 FOREIGN KEY(person_id) REFERENCES person (id)
610 );
611+CREATE TABLE IF NOT EXISTS site_role (
612+ id INTEGER NOT NULL,
613+ person_id INTEGER,
614+ role VARCHAR(13),
615+ PRIMARY KEY (id),
616+ FOREIGN KEY(person_id) REFERENCES person (id),
617+ CHECK (role IN ('User Admin', 'Project Admin'))
618+);
619+INSERT OR REPLACE INTO "site_role" VALUES(1,1,'User Admin');
620+INSERT OR REPLACE INTO "site_role" VALUES(2,1,'Project Admin');
621 COMMIT;

Subscribers

People subscribed via source and target branches