summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--schema.sql44
1 files changed, 44 insertions, 0 deletions
diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..b005380
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,44 @@
+--*- mode: sql; coding: utf-8 -*--
+
+BEGIN TRANSACTION;
+
+CREATE TABLE users(
+ id SERIAL NOT NULL,
+ first_name VARCHAR,
+ middle_names VARCHAR,
+ last_name VARCHAR,
+ email VARCHAR,
+ website VARCHAR,
+ status VARCHAR,
+ PRIMARY KEY(id),
+ CHECK (status IN ('admin', 'approved', 'visitor', 'disabled'))
+);
+
+CREATE TABLE openids(
+ "user" INTEGER NOT NULL,
+ openid VARCHAR NOT NULL,
+ PRIMARY KEY(openid),
+ FOREIGN KEY("user") REFERENCES users
+);
+
+CREATE TABLE wiki_pages(
+ id SERIAL NOT NULL,
+ PRIMARY KEY(id)
+);
+
+CREATE TABLE wiki_page_revisions(
+ id SERIAL NOT NULL,
+ page INTEGER NOT NULL,
+ date TIMESTAMP DEFAULT now(),
+ title VARCHAR,
+ content VARCHAR,
+ author INTEGER,
+ format VARCHAR,
+ PRIMARY KEY(id),
+ FOREIGN KEY(page) REFERENCES wiki_pages,
+ FOREIGN KEY(author) REFERENCES users,
+ CHECK (format IN ('mulkwiki', 'html5', 'xhtml5', 'markdown', 'textile', 'muse', 'bbcode'))
+);
+
+ROLLBACK;
+--COMMIT;