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         username        TEXT UNIQUE,
  13         password        TEXT NOT NULL,
  14         status          TEXT DEFAULT 'active',
  15         current_room    INTEGER REFERENCES room(id)
  16 );
  17
  18 CREATE TABLE message (
  19         id              INTEGER PRIMARY KEY,
  20         posted          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  21         author          INTEGER REFERENCES account(id),
  22         room            INTEGER REFERENCES room(id),
  23         content         TEXT
  24 );
  25
  26 INSERT INTO account (username, password) VALUES ('chaz', 'mypass');
  27 INSERT INTO account (username, password) VALUES ('jdoe', 'foobar');
  28