schema.sql

   1 drop table if exists user_choice;
   2 drop table if exists user_choice_buffer_anonymous;
   3 drop table if exists user_vote;
   4 drop table if exists choices;
   5 drop table if exists cardinals;
   6 drop table if exists values_;
   7 drop table if exists attachments;
   8 drop table if exists votes;
   9 drop table if exists user_group;
  10 drop table if exists groups;
  11 drop table if exists users;
  12
  13 PRAGMA foreign_keys = ON;
  14
  15 create table users (
  16     id INTEGER PRIMARY KEY AUTOINCREMENT,
  17     email TEXT UNIQUE NOT NULL,
  18     password TEXT NOT NULL,
  19     openid TEXT NOT NULL,
  20     name TEXT UNIQUE NOT NULL,
  21     organization TEXT,
  22     is_admin BOOLEAN DEFAULT 0 NOT NULL,
  23     key TEXT,
  24     CHECK (is_admin IN (0, 1))
  25 );
  26
  27 create table groups (
  28     id INTEGER PRIMARY KEY AUTOINCREMENT,
  29     name TEXT UNIQUE NOT NULL,
  30     system BOOLEAN DEFAULT 0 NOT NULL,
  31     CHECK (system IN (0, 1))
  32 );
  33
  34 create table user_group (
  35     id_user INTEGER,
  36     id_group INTEGER,
  37     FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
  38     FOREIGN KEY(id_group) REFERENCES groups (id) ON DELETE CASCADE,
  39     PRIMARY KEY(id_user, id_group)
  40 );
  41
  42 create table cardinals (
  43     id INTEGER UNIQUE NOT NULL PRIMARY KEY,
  44     name TEXT UNIQUE NOT NULL,
  45     first INTEGER
  46 );
  47
  48 create table values_ (
  49     name TEXT NOT NULL,
  50     weight INTEGER NOT NULL,
  51     id_cardinal INTEGER NOT NULL,
  52     FOREIGN KEY(id_cardinal) REFERENCES cardinals (id) ON DELETE CASCADE,
  53     PRIMARY KEY(id_cardinal, weight)
  54 );
  55
  56 create table votes (
  57     id INTEGER PRIMARY KEY AUTOINCREMENT,
  58     title TEXT NOT NULL,
  59     description TEXT,
  60     category TEXT,
  61     date_begin INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
  62     date_end INTEGER NOT NULL,
  63     quorum FLOAT DEFAULT 1,
  64     reminder_last_days INTEGER DEFAULT 3 NOT NULL,
  65     is_transparent BOOLEAN DEFAULT 1 NOT NULL,
  66     is_public BOOLEAN DEFAULT 1 NOT NULL,
  67     is_anonymous BOOLEAN DEFAULT 1 NOT NULL,
  68     is_open BOOLEAN DEFAULT 0 NOT NULL,
  69     is_terminated BOOLEAN DEFAULT 0 NOT NULL,
  70     is_hidden BOOLEAN DEFAULT 0 NOT NULL,
  71     id_author INTEGER DEFAULT 1 NOT NULL,
  72     id_group INTEGER DEFAULT 1 NOT NULL,
  73     id_cardinal INTEGER NOT NULL,
  74     FOREIGN KEY(id_author) REFERENCES users (id) ON DELETE SET DEFAULT,
  75     FOREIGN KEY(id_group) REFERENCES groups (id),
  76     FOREIGN KEY(id_cardinal) REFERENCES cardinals (id),
  77     CHECK (is_transparent IN (0, 1)),
  78     CHECK (is_public IN (0, 1)),
  79     CHECK (is_open IN (0, 1)),
  80     CHECK (is_terminated IN (0, 1)),
  81     CHECK (is_hidden IN (0, 1))
  82 );
  83
  84 create table attachments (
  85     id INTEGER PRIMARY KEY AUTOINCREMENT,
  86     url TEXT NOT NULL,
  87     id_vote INTEGER NOT NULL,
  88     FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE
  89 );
  90
  91 create table choices (
  92     id INTEGER PRIMARY KEY AUTOINCREMENT,
  93     name TEXT NOT NULL,
  94     id_vote INTEGER NOT NULL,
  95     FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE
  96 );
  97
  98 create table user_choice (
  99     id_user INTEGER,
 100     id_choice INTEGER NOT NULL,
 101     id_cardinal INTEGER,
 102     weight INTEGER,
 103     FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
 104     FOREIGN KEY(id_choice) REFERENCES choices (id) ON DELETE CASCADE,
 105     FOREIGN KEY(id_cardinal, weight) REFERENCES values_ (id_cardinal, weight) ON DELETE CASCADE,
 106     PRIMARY KEY(id_user, id_choice)
 107 );
 108
 109 create table user_choice_buffer_anonymous (
 110  -- NOTE: same table structure as user_choice, used to randomize insertion per vote
 111     id_user INTEGER,
 112     id_choice INTEGER NOT NULL,
 113     id_cardinal INTEGER,
 114     weight INTEGER,
 115     FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
 116     FOREIGN KEY(id_choice) REFERENCES choices (id) ON DELETE CASCADE,
 117     FOREIGN KEY(id_cardinal, weight) REFERENCES values_ (id_cardinal, weight) ON DELETE CASCADE,
 118     PRIMARY KEY(id_user, id_choice)
 119 );
 120
 121 create table user_vote (
 122     date INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
 123     id_user INTEGER NOT NULL,
 124     id_vote INTEGER NOT NULL,
 125     comment TEXT,
 126     FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
 127     FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE,
 128     PRIMARY KEY(id_user, id_vote)
 129 );