Diff of /trunk/schema.sql [r176] .. [r177]  Maximize  Restore

Switch to side-by-side view

--- a/trunk/schema.sql
+++ b/trunk/schema.sql
@@ -1,3 +1,4 @@
+-- holds boards
 CREATE TABLE IF NOT EXISTS Boards (
     id          INTEGER PRIMARY KEY, -- board id
     parent      INTEGER,             -- parent id, if applicable
@@ -8,6 +9,7 @@
     img         TEXT                 -- url to image to display w/ desc.
 );
 
+-- holds posts
 CREATE TABLE IF NOT EXISTS Posts (
     id              INTEGER PRIMARY KEY, -- post id
     thread          INTEGER,             -- NULL if this is the first post in a
@@ -29,11 +31,89 @@
     -- other things I haven't thought of yet
     -- I like bit fields, but is it the right answer?
 );
+CREATE INDEX threads ON Posts (thread);
 
+-- holds attachements
 CREATE TABLE IF NOT EXISTS Attachments (
-    id          -- file id
-    postid      -- post it is attached to
-    filename    -- name of the file in
-    location    -- where it's stored rel. to attachments directory
-    filesize    -- 
+    id         INTEGER PRIMARY KEY, -- file id
+    postid     INTEGER,             -- post it is attached to
+    filename   VARCHAR(255),        -- name of the file when uploaded
+    location   VARCHAR(255),        -- path & name within attachments directory
+    filesize   INTEGER,             -- KB, usually a TB/attachment is sufficent
+    downloads  INTEGER              -- # of times attchment has been d/l
 );
+CREATE INDEX attchment_posts ON Attachments (postid);
+
+-- holds poll questions
+CREATE TABLE IF NOT EXISTS Polls (
+    id          INTEGER PRIMARY KEY, -- ...
+    postid      INTEGER,             -- post this poll is in
+    question    TEXT,                -- 
+    numchoices  INTEGER              -- how many choices the user can vote for
+    expires     NUMERIC(10,0)        -- when the poll is closed
+    flags       INTEGER,             -- 
+    -- POSSIBLE FLAGS:
+    -- SHOW_AFTER_VOTE  - 0x01 - 0001 - default is to show after expiration
+    -- NEVER_SHOW       - 0x02 - 0010
+    -- CAN_CHANGE   - do we want to allow this?
+);
+CREATE INDEX poll_posts ON Polls (postid);
+
+-- holds poll answers
+CREATE TABLE IF NOT EXISTS PollAnswers (
+    id      INTEGER PRIMARY KEY, --
+    pollid  INTEGER,             --
+    answer  TEXT,                --
+    votes   INTEGER              -- votes for this choice
+);
+CREATE INDEX poll_answer ON PollAnswers (pollid);
+
+-- TODO
+-- pollvotes table needed so votes can be changed/audited
+
+-- user data
+CREATE TABLE IF NOT EXISTS Users (
+    id          INTEGER PRIMARY KEY, -- 
+    username    VARCHAR(255),        --
+    password    VARCHAR(64),         -- the hash representation of the pw
+    name        VARCHAR(255),        --
+    email       VARCHAR(255),        --
+    signature   TEXT,                --
+    registered  NUMERIC(10,0)        -- registration date
+);
+
+-- other data that isn't critical, nice for extending profile
+CREATE TABLE IF NOT EXISTS UsersExtendedInfo (
+    ufid       VARCHAR(45) PRIMARY KEY, -- since we don't support compound pk
+                                        -- this is 'userid'.'fieldname'
+    userid     INTEGER,                 --
+    fieldname  VARCHAR(30),             -- the name of the field ie 'birthdate' 
+    fieldvalue VARCHAR(255),            -- the value of the field ie 1986-08-10
+);
+CREATE INDEX user_info ON UsersExtendedInfo (userid);
+
+CREATE TABLE IF NOT EXISTS BoardTracking (
+    ubid    VARCHAR(30) PRIMARY KEY, -- since we don't support compound pk
+                                     -- this is 'userid'.'boardid'
+    userid  INTEGER,
+    boardid INTEGER,
+    time    NUMERIC(10,0)
+);
+CREATE INDEX user_board_tracking ON BoardTracking (userid);
+
+CREATE TABLE IF NOT EXISTS ThreadTracking (
+    utid    VARCHAR(30) PRIMARY KEY, -- since we don't support compound pk
+                                     -- this is 'userid'.'threadid'
+    userid   INTEGER,
+    threadid INTEGER,
+    time     NUMERIC(10,0)
+);
+CREATE INDEX user_thread_tracking ON ThreadTracking (userid);
+
+--CREATE TABLE IF NOT EXISTS PMFolders ();
+
+--CREATE TABLE IF NOT EXISTS PMs ();
+
+--CREATE TABLE IF NOT EXISTS PMsUser ();
+
+--CREATE TABLE IF NOT EXISTS Bans ();

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks