slists01.sql

   1 --
   2 -- Create a very simple database to hold lists information
   3 --
   4 PRAGMA foreign_keys = ON;
   5
   6 CREATE TABLE users (
   7   id            INTEGER PRIMARY KEY,
   8   username      TEXT,
   9   password      TEXT,
  10   email_address TEXT,
  11   first_name    TEXT,
  12   last_name     TEXT,
  13   active        INTEGER
  14 );
  15
  16 create table lists (
  17  id INTEGER PRIMARY KEY,
  18  user_id integer,
  19  title text,
  20  FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE
  21 );
  22
  23 create table list_items (
  24  id INTEGER PRIMARY KEY,
  25  product_quantity INTEGER,
  26  product_id INTEGER,
  27  list_id int,
  28  FOREIGN KEY (product_id) REFERENCES products(id) ON UPDATE CASCADE,
  29  FOREIGN KEY (list_id) REFERENCES lists(id) ON UPDATE CASCADE
  30 );
  31
  32 create table products (
  33  id INTEGER PRIMARY KEY,
  34  name TEXT,
  35  weight REAL, --kg
  36  cost REAL --XXX rename to price $
  37 );
  38
  39 --
  40 -- Write two users, one of them (#1) has a list that contains
  41 --  4kg potatoes cost $5
  42 --
  43 insert into users values (NULL, 'user1','user1','user1@localhost',
  44                           'userf','userl',1);
  45 insert into users values (NULL, 'user2','user2','user2@localhost',
  46                           'userf2','userl2',1);
  47 insert into users values (NULL, 'user3','user3','user3@localhost',
  48                           'userf3','userl3',1);
  49 insert into lists values (NULL, 1, "A first list");
  50 insert into products values(NULL, 'potatoes', 4, 5);
  51 insert into list_items values (NULL, 1, 1,1); -- insert 1 product #1 into list 1
  52
  53 ALTER TABLE lists ADD created TIMESTAMP;
  54 ALTER TABLE lists ADD updated TIMESTAMP;
  55 UPDATE lists SET created = DATETIME('NOW'), updated = DATETIME('NOW');
  56 SELECT * FROM lists;