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