CREATE SEQUENCE WebUserSeq START 1;
INSERT INTO WebUser
- (id, name, bal) VALUES
- (0, 'adam', 0);
+ (id, name, rname, bal, joined) VALUES
+ (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
CREATE TABLE WebGroup(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL);
-CREATE SEQUENCE WebGroupSeq START 4;
+CREATE SEQUENCE WebGroupSeq START 2;
INSERT INTO WebGroup
(id, name) VALUES
INSERT INTO WebGroup
(id, name) VALUES
- (1, 'money');
-
-INSERT INTO WebGroup
- (id, name) VALUES
- (2, 'paying');
-
-INSERT INTO WebGroup
- (id, name) VALUES
- (3, 'poll');
+ (1, 'paying');
CREATE TABLE Membership(
grp INTEGER NOT NULL,
(grp, usr) VALUES
(0, 0);
+INSERT INTO Membership
+ (grp, usr) VALUES
+ (1, 0);
+
CREATE TABLE Transaction(
id INTEGER PRIMARY KEY,
descr TEXT NOT NULL,
PRIMARY KEY (usr, loc),
FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
+
+CREATE TABLE Link(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ title TEXT NOT NULL,
+ url TEXT NOT NULL,
+ descr TEXT NOT NULL,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE LinkSeq START 1;
+
+CREATE TABLE SupCategory(
+ id INTEGER PRIMARY KEY,
+ grp INTEGER NOT NULL,
+ name TEXT NOT NULL,
+ descr TEXT NOT NULL,
+ FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE SupCategorySeq START 1;
+
+CREATE TABLE SupIssue(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ cat INTEGER NOT NULL,
+ title TEXT NOT NULL,
+ priv BOOLEAN NOT NULL,
+ status INTEGER NOT NULL,
+ stamp TIMESTAMP NOT NULL,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
+ FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE SupIssueSeq START 1;
+
+CREATE TABLE SupPost(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ iss INTEGER NOT NULL,
+ body TEXT NOT NULL,
+ stamp TIMESTAMP NOT NULL,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
+ FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE SupPostSeq START 1;
+
+CREATE TABLE SupSubscription(
+ usr INTEGER NOT NULL,
+ cat INTEGER NOT NULL,
+ PRIMARY KEY (usr, cat),
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
+ FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
+
+CREATE TABLE Apt(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ data TEXT NOT NULL,
+ msg TEXT NOT NULL,
+ status INTEGER NOT NULL,
+ stamp TIMESTAMP NOT NULL,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE AptSeq START 1;
+
+CREATE TABLE Domain(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ data TEXT NOT NULL,
+ msg TEXT NOT NULL,
+ status INTEGER NOT NULL,
+ stamp TIMESTAMP NOT NULL,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE DomainSeq START 1;
+
+CREATE TABLE MailingList(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ data TEXT NOT NULL,
+ msg TEXT NOT NULL,
+ status INTEGER NOT NULL,
+ stamp TIMESTAMP NOT NULL,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE MailingListSeq START 1;
+
+CREATE TABLE DirectoryPref(
+ usr INTEGER PRIMARY KEY,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+CREATE TABLE MemberApp(
+ id INTEGER PRIMARY KEY,
+ name TEXT NOT NULL,
+ rname TEXT NOT NULL,
+ email TEXT NOT NULL,
+ forward BOOLEAN NOT NULL,
+ uses TEXT NOT NULL,
+ other TEXT NOT NULL,
+ passwd TEXT NOT NULL,
+ status INTEGER NOT NULL,
+ applied TIMESTAMP NOT NULL,
+ confirmed TIMESTAMP,
+ decided TIMESTAMP,
+ msg TEXT NOT NULL);
+
+CREATE SEQUENCE MemberAppSeq START 1;
+
+CREATE TABLE AppVote(
+ app INTEGER NOT NULL,
+ usr INTEGER NOT NULL,
+ PRIMARY KEY (app, usr),
+ FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+