8d347a33 |
1 | CREATE TABLE Balance( |
2 | id INTEGER PRIMARY KEY, |
3 | name TEXT NOT NULL, |
4 | amount REAL NOT NULL); |
5 | |
6 | CREATE SEQUENCE BalanceSeq START 1; |
7 | |
8 | INSERT INTO Balance |
9 | (id, name, amount) VALUES |
10 | (0, 'adam', 0.0); |
11 | |
12 | CREATE TABLE WebUser( |
13 | id INTEGER PRIMARY KEY, |
14 | name TEXT NOT NULL, |
15 | rname TEXT NOT NULL, |
16 | bal INTEGER NOT NULL, |
17 | joined TIMESTAMP NOT NULL, |
18 | FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE); |
19 | |
20 | CREATE SEQUENCE WebUserSeq START 1; |
21 | |
22 | INSERT INTO WebUser |
23 | (id, name, bal) VALUES |
24 | (0, 'adam', 0); |
25 | |
26 | CREATE TABLE WebGroup( |
27 | id INTEGER PRIMARY KEY, |
28 | name TEXT NOT NULL); |
29 | |
57c305c1 |
30 | CREATE SEQUENCE WebGroupSeq START 4; |
8d347a33 |
31 | |
32 | INSERT INTO WebGroup |
33 | (id, name) VALUES |
34 | (0, 'root'); |
35 | |
36 | CREATE TABLE Membership( |
37 | grp INTEGER NOT NULL, |
38 | usr INTEGER NOT NULL, |
39 | PRIMARY KEY (grp, usr), |
40 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE, |
41 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
42 | |
43 | INSERT INTO Membership |
44 | (grp, usr) VALUES |
45 | (0, 0); |
46 | |
47 | CREATE TABLE Transaction( |
48 | id INTEGER PRIMARY KEY, |
49 | descr TEXT NOT NULL, |
50 | amount REAL NOT NULL, |
51 | d DATE NOT NULL, |
52 | stamp TIMESTAMP NOT NULL); |
53 | |
54 | CREATE SEQUENCE TransactionSeq START 1; |
55 | |
56 | CREATE TABLE Charge( |
57 | trn INTEGER NOT NULL, |
58 | usr INTEGER NOT NULL, |
59 | amount REAL NOT NULL, |
60 | PRIMARY KEY (trn, usr), |
61 | FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE, |
62 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
63 | |
57c305c1 |
64 | CREATE TABLE Poll( |
65 | id INTEGER PRIMARY KEY, |
66 | usr INTEGER NOT NULL, |
67 | title TEXT NOT NULL, |
68 | descr TEXT NOT NULL, |
69 | starts DATE NOT NULL, |
70 | ends DATE NOT NULL, |
71 | votes INTEGER NOT NULL, |
72 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
73 | |
74 | CREATE SEQUENCE PollSeq START 1; |
75 | |
76 | CREATE TABLE PollChoice( |
77 | id INTEGER PRIMARY KEY, |
78 | pol INTEGER NOT NULL, |
79 | seq REAL NOT NULL, |
80 | descr TEXT NOT NULL, |
81 | FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE); |
82 | |
83 | CREATE SEQUENCE PollChoiceSeq START 1; |
84 | |
85 | CREATE TABLE Vote( |
86 | usr INTEGER NOT NULL, |
87 | cho INTEGER NOT NULL, |
88 | PRIMARY KEY (usr, cho), |
89 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
90 | FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE); |
91 | |
46b0c2df |
92 | CREATE TABLE ContactKind( |
93 | id INTEGER PRIMARY KEY, |
94 | name TEXT NOT NULL, |
95 | url BOOLEAN NOT NULL, |
96 | urlPrefix TEXT, |
97 | urlPostfix TEXT); |
57c305c1 |
98 | |
46b0c2df |
99 | CREATE SEQUENCE ContactKindSeq START 1; |
8d347a33 |
100 | |
46b0c2df |
101 | CREATE TABLE Contact( |
369e1577 |
102 | id INTEGER PRIMARY KEY, |
46b0c2df |
103 | usr INTEGER NOT NULL, |
104 | knd INTEGER NOT NULL, |
105 | v TEXT NOT NULL, |
106 | priv INTEGER NOT NULL, |
46b0c2df |
107 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
108 | FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE); |
369e1577 |
109 | |
110 | CREATE SEQUENCE ContactSeq START 1; |
51520441 |
111 | |
112 | CREATE TABLE Location( |
113 | id INTEGER PRIMARY KEY, |
114 | parent INTEGER, |
115 | name TEXT NOT NULL, |
116 | FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE); |
117 | |
118 | CREATE SEQUENCE LocationSeq START 1; |
119 | |
120 | CREATE TABLE Lives( |
121 | usr INTEGER NOT NULL, |
122 | loc INTEGER NOT NULL, |
123 | PRIMARY KEY (usr, loc), |
124 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
125 | FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE); |
29c3cc58 |
126 | |
127 | CREATE TABLE Link( |
128 | id INTEGER PRIMARY KEY, |
129 | usr INTEGER NOT NULL, |
130 | title TEXT NOT NULL, |
131 | url TEXT NOT NULL, |
132 | descr TEXT NOT NULL, |
133 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
134 | |
135 | CREATE SEQUENCE LinkSeq START 1; |
136 | |
c7311141 |
137 | CREATE TABLE SupCategory( |
138 | id INTEGER PRIMARY KEY, |
139 | grp INTEGER NOT NULL, |
140 | name TEXT NOT NULL, |
141 | descr TEXT NOT NULL, |
142 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE); |
143 | |
144 | CREATE SEQUENCE SupCategorySeq START 1; |
145 | |
146 | CREATE TABLE SupIssue( |
147 | id INTEGER PRIMARY KEY, |
148 | usr INTEGER NOT NULL, |
149 | cat INTEGER NOT NULL, |
150 | title TEXT NOT NULL, |
151 | priv BOOLEAN NOT NULL, |
152 | status INTEGER NOT NULL, |
153 | stamp TIMESTAMP NOT NULL, |
154 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
155 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); |
156 | |
157 | CREATE SEQUENCE SupIssueSeq START 1; |
158 | |
159 | CREATE TABLE SupPost( |
160 | id INTEGER PRIMARY KEY, |
161 | usr INTEGER NOT NULL, |
162 | iss INTEGER NOT NULL, |
163 | body TEXT NOT NULL, |
164 | stamp TIMESTAMP NOT NULL, |
165 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
166 | FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE); |
167 | |
168 | CREATE SEQUENCE SupPostSeq START 1; |
169 | |
170 | CREATE TABLE SupSubscription( |
171 | usr INTEGER NOT NULL, |
172 | cat INTEGER NOT NULL, |
173 | PRIMARY KEY (usr, cat), |
174 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
175 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); |
176 | |
36d5f176 |
177 | CREATE TABLE Apt( |
178 | id INTEGER PRIMARY KEY, |
179 | usr INTEGER NOT NULL, |
ff2b7604 |
180 | data TEXT NOT NULL, |
36d5f176 |
181 | msg TEXT NOT NULL, |
182 | status INTEGER NOT NULL, |
183 | stamp TIMESTAMP NOT NULL, |
184 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
c7311141 |
185 | |
36d5f176 |
186 | CREATE SEQUENCE AptSeq START 1; |
ff2b7604 |
187 | |
188 | CREATE TABLE Domain( |
189 | id INTEGER PRIMARY KEY, |
190 | usr INTEGER NOT NULL, |
191 | data TEXT NOT NULL, |
192 | msg TEXT NOT NULL, |
193 | status INTEGER NOT NULL, |
194 | stamp TIMESTAMP NOT NULL, |
195 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
196 | |
197 | CREATE SEQUENCE DomainSeq START 1; |