Merge lp:~thomir-deactivatedaccount/sloecode/fix-dbpatches into lp:sloecode
- fix-dbpatches
- Merge into trunk
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 |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
sloecode | Pending | ||
Review via email:
|
Commit message
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; |