Dogcows Code - chaz/chatty/blob - db/schema.sql

   1
   2 PRAGMA foreign_keys = ON;
   3
   4 CREATE TABLE room (
   5         id              INTEGER PRIMARY KEY,
   6         name            TEXT UNIQUE,
   7         created         TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   8 );
   9
  10 CREATE TABLE account (
  11         id              INTEGER PRIMARY KEY,
  12         email           TEXT,
  13         username        TEXT UNIQUE,
  14         password        TEXT NOT NULL,
  15         status          TEXT DEFAULT 'active',
  16         current_room    INTEGER REFERENCES room(id)
  17 );
  18
  19 CREATE TABLE message (
  20         id              INTEGER PRIMARY KEY,
  21         posted          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  22         author          INTEGER REFERENCES account(id),
  23         room            INTEGER REFERENCES room(id),
  24         content         TEXT
  25 );
  26
  27 INSERT INTO account (username, password) VALUES ('chaz', 'mypass');
  28 INSERT INTO account (username, password) VALUES ('jdoe', 'foobar');
  29