Commits

Yang Zhang  committed 3ac8046

Make db schema--> sqlite

  • Participants
  • Parent commits 85484ba

Comments (0)

Files changed (2)

+syntax: glob
+db
+--special one
+CREATE TABLE admin(
+    email               VARCHAR,
+    password            VARCHAR
+);
+
+--user
+CREATE TABLE user(
+    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
+    nickname            VARCHAR,
+    email               VARCHAR,
+    password            VARCHAR,
+    avatar              BLOB,-------- need to reduce
+    create_date         TIMESTAMP DEFAULT (datetime('now','localtime')),
+    reputation          INTEGER,
+    UNIQUE(nickname),
+    UNIQUE(email)
+);
+
+--topic
+CREATE TABLE topic(
+    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
+    title              VARCHAR,
+    content            TEXT,
+    create_date        TIMESTAMP DEFAULT (datetime('now','localtime')),
+    posterid           INTEGER,
+    FOREIGN KEY(posterid) REFERENCES user(id)
+);
+
+--comment for topic, user cannot comment for comments
+--reply --> comment on topic, but the system will notify the replied one
+CREATE TABLE comment(
+    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
+    content            TEXT,
+    topicid            INTEGER,
+    userid             INTEGER,
+    create_date        TIMESTAMP DEFAULT (datetime('now','localtime')),
+    FOREIGN KEY(topicid) REFERENCES topic(id),
+    FOREIGN KEY(userid) REFERENCES user(id)
+);
+
+-- user can star his favorate topic,
+-- when star a topic, whenever topic changed, it will notify the user
+-- unless he refuse recieve it.
+CREATE TABLE star(
+    userid              INTEGER,
+    topicid             INTEGER,
+    recieve             BOOLEAN,
+    FOREIGN KEY(userid) REFERENCES user(id),
+    FOREIGN KEY(topicid) REFERENCES topic(id),
+    PRIMARY KEY(userid, topicid)
+);
+
+CREATE TABLE message(
+    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
+    userid             INTEGER,
+    commentid          INTEGER,
+    readed             BOOLEAN,
+    create_date         TIMESTAMP DEFAULT (datetime('now','localtime')),
+    FOREIGN KEY(userid) REFERENCES user(id),
+    FOREIGN KEY(commentid) REFERENCES comment(id)
+);
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+