schema.sql

   1 drop table if exists user_choice;
   2 drop table if exists choices;
   3 drop table if exists attachments;
   4 drop table if exists votes;
   5 drop table if exists user_group;
   6 drop table if exists groups;
   7 drop table if exists users;
   8
   9 PRAGMA foreign_keys = ON;
  10
  11 create table users (
  12     id INTEGER PRIMARY KEY AUTOINCREMENT,
  13     email TEXT UNIQUE NOT NULL,
  14     password TEXT NOT NULL,
  15     openid TEXT NOT NULL,
  16     name TEXT UNIQUE NOT NULL,
  17     organization TEXT,
  18     is_admin BOOLEAN DEFAULT 0 NOT NULL,
  19     key TEXT,
  20     CHECK (is_admin IN (0, 1))
  21 );
  22
  23 create table groups (
  24     id INTEGER PRIMARY KEY AUTOINCREMENT,
  25     name TEXT UNIQUE NOT NULL,
  26     system BOOLEAN DEFAULT 0 NOT NULL,
  27     CHECK (system IN (0, 1))
  28 );
  29
  30 create table user_group (
  31     id_user INTEGER,
  32     id_group INTEGER,
  33     FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
  34     FOREIGN KEY(id_group) REFERENCES groups (id) ON DELETE CASCADE,
  35     PRIMARY KEY(id_user, id_group)
  36 );
  37
  38 create table votes (
  39     id INTEGER PRIMARY KEY AUTOINCREMENT,
  40     title TEXT NOT NULL,
  41     description TEXT,
  42     category TEXT,
  43     date_begin INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
  44     date_end INTEGER NOT NULL,
  45     is_transparent BOOLEAN DEFAULT 1 NOT NULL,
  46     is_public BOOLEAN DEFAULT 1 NOT NULL,
  47     is_multiplechoice BOOLEAN DEFAULT 1 NOT NULL,
  48     is_weighted BOOLEAN DEFAULT 0 NOT NULL,
  49     is_open BOOLEAN DEFAULT 0 NOT NULL,
  50     is_terminated BOOLEAN DEFAULT 0 NOT NULL,
  51     id_author INTEGER DEFAULT 1 NOT NULL,
  52     id_group INTEGER DEFAULT 1 NOT NULL,
  53     FOREIGN KEY(id_author) REFERENCES users (id) ON DELETE SET DEFAULT,
  54     FOREIGN KEY(id_group) REFERENCES groups (id),
  55     CHECK (is_transparent IN (0, 1)),
  56     CHECK (is_public IN (0, 1)),
  57     CHECK (is_weighted IN (0, 1)),
  58     CHECK (is_open IN (0, 1)),
  59     CHECK (is_terminated IN (0, 1))
  60 );
  61
  62 create table attachments (
  63     id INTEGER PRIMARY KEY AUTOINCREMENT,
  64     url TEXT NOT NULL,
  65     id_vote INTEGER NOT NULL,
  66     FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE
  67 );
  68
  69 create table choices (
  70     id INTEGER PRIMARY KEY AUTOINCREMENT,
  71     name TEXT NOT NULL,
  72     id_vote INTEGER NOT NULL,
  73     FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE
  74 );
  75
  76 create table user_choice (
  77     id_user INTEGER,
  78     id_choice INTEGER,
  79     weight INTEGER,
  80     FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
  81     FOREIGN KEY(id_choice) REFERENCES choices (id) ON DELETE CASCADE,
  82     PRIMARY KEY(id_user, id_choice)
  83 );
  84
  85 -- Test data
  86
  87 INSERT INTO users (id, email, password, openid, name, organization, is_admin, key)
  88 VALUES (1, "admin@admin.fr", "", "", "Toto (admin) Tata", "World corp", 1, "victory");
  89 -- to login, go to /login/1/victory
  90 INSERT INTO groups (id, name, system) VALUES (1, "Tous", 1);
  91 INSERT INTO groups (name) VALUES ("CA");
  92 INSERT INTO groups (name) VALUES ("Membres");
  93 INSERT INTO user_group (id_user, id_group) VALUES(1, 1);