]>
Commit | Line | Data |
---|---|---|
a5eae6b7 MR |
1 | <?php |
2 | /* $Id$ */ | |
3 | ||
4 | function make_sql_stm() { | |
5 | $db = array(); | |
6 | ||
7 | array_push($db, "drop table if exists roles"); | |
8 | array_push($db, "CREATE TABLE roles ( | |
9 | id integer primary key autoincrement, | |
10 | name text not null | |
11 | )"); | |
12 | array_push($db, "INSERT INTO roles VALUES(0,'administrator')"); | |
13 | array_push($db, "INSERT INTO roles VALUES(1,'power user')"); | |
14 | array_push($db, "INSERT INTO roles VALUES(2,'user')"); | |
15 | ||
16 | array_push($db, "drop table if exists user"); | |
17 | array_push($db, "CREATE TABLE user ( | |
18 | id integer primary key autoincrement, | |
19 | uid text not null, | |
20 | pwd text not null, | |
21 | userrole integer not null, | |
22 | timeout integer default 3600, | |
23 | userview text default 'week', | |
24 | weekstart integer default 0, | |
25 | daystart real default 8.00, | |
26 | dayend real default 17.00, | |
27 | timezone text default 'Etc/UTC', | |
28 | seckey text default '', | |
29 | pubkey text default '', | |
30 | constraint uid_index unique (uid) | |
31 | )"); | |
32 | ||
33 | array_push($db, "drop table if exists calendar"); | |
34 | array_push($db, "CREATE TABLE calendar ( | |
35 | id integer default -1, | |
36 | uid integer not null, | |
37 | name text not null, | |
38 | color text default 'navy', | |
39 | config text default '', | |
40 | constraint calendar_pk primary key (id, uid) | |
41 | )"); | |
42 | array_push($db, "CREATE TRIGGER compute_id after insert on calendar | |
43 | begin | |
44 | update calendar set id = (select max(id) + 1 from calendar) where id = -1; | |
45 | end;"); | |
46 | array_push($db, "CREATE TRIGGER genfkey2_insert_referencing BEFORE INSERT ON calendar WHEN | |
47 | new.uid IS NOT NULL AND NOT EXISTS (SELECT 1 FROM user WHERE new.uid == id) | |
48 | BEGIN | |
49 | SELECT RAISE(ABORT, 'constraint failed'); | |
50 | END"); | |
51 | array_push($db, "CREATE TRIGGER genfkey2_update_referencing BEFORE | |
52 | UPDATE OF uid ON calendar WHEN | |
53 | new.uid IS NOT NULL AND | |
54 | NOT EXISTS (SELECT 1 FROM user WHERE new.uid == id) | |
55 | BEGIN | |
56 | SELECT RAISE(ABORT, 'constraint failed'); | |
57 | END"); | |
58 | array_push($db, "CREATE TRIGGER genfkey2_delete_referenced BEFORE DELETE ON | |
59 | user WHEN EXISTS (SELECT 1 FROM calendar WHERE old.id == uid) | |
60 | BEGIN | |
61 | DELETE FROM calendar WHERE uid = old.id; | |
62 | END"); | |
63 | array_push($db, "CREATE TRIGGER genfkey2_update_referenced AFTER | |
64 | UPDATE OF id ON user WHEN | |
65 | EXISTS (SELECT 1 FROM calendar WHERE old.id == uid) | |
66 | BEGIN | |
67 | UPDATE calendar SET uid = new.id WHERE uid = old.id; | |
68 | END"); | |
69 | array_push($db, "CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON | |
70 | user WHEN new.userrole IS NOT NULL AND NOT EXISTS (SELECT 1 FROM roles WHERE | |
71 | new.userrole == id) | |
72 | BEGIN | |
73 | SELECT RAISE(ABORT, 'constraint failed'); | |
74 | END"); | |
75 | array_push($db, "CREATE TRIGGER genfkey1_update_referencing BEFORE | |
76 | UPDATE OF userrole ON user WHEN new.userrole IS NOT NULL AND | |
77 | NOT EXISTS (SELECT 1 FROM roles WHERE new.userrole == id) | |
78 | BEGIN | |
79 | SELECT RAISE(ABORT, 'constraint failed'); | |
80 | END"); | |
81 | array_push($db, "CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON | |
82 | roles WHEN EXISTS (SELECT 1 FROM user WHERE old.id == userrole) | |
83 | BEGIN | |
84 | SELECT RAISE(ABORT, 'constraint failed'); | |
85 | END"); | |
86 | array_push($db, "CREATE TRIGGER genfkey1_update_referenced AFTER | |
87 | UPDATE OF id ON roles WHEN | |
88 | EXISTS (SELECT 1 FROM user WHERE old.id == userrole) | |
89 | BEGIN | |
90 | UPDATE user SET userrole = new.id WHERE userrole = old.id; | |
91 | END"); | |
92 | array_push($db, "drop table if exists about"); | |
93 | array_push($db, "create table about ( | |
94 | id integer default 1, | |
95 | version text default '')"); | |
96 | array_push($db, "drop table if exists ldap"); | |
97 | array_push($db, "create table ldap ( | |
98 | enable integer default 0, | |
99 | dns text default '', | |
100 | tls integer default 0, | |
101 | base_dn text default '', | |
102 | user_attr text default 'uid')"); | |
103 | ||
104 | return $db; | |
105 | } |