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 create table users (
  10     id INTEGER primary key autoincrement,
  11     email TEXT unique not null,
  12     password TEXT not null,
  13     name TEXT unique,
  14     organization TEXT,
  15     is_admin INTEGER default 0 not null,
  16     key TEXT
  17 );
  18
  19 create table groups (
  20     id INTEGER primary key autoincrement,
  21     name TEXT,
  22     system INTEGER default 0 not null
  23 );
  24
  25 create table user_group (
  26     id_user INTEGER,
  27     id_group INTEGER,
  28     FOREIGN KEY(id_user) REFERENCES users(id),
  29     FOREIGN KEY(id_group) REFERENCES groups(id),
  30     PRIMARY KEY(id_user, id_group)
  31 );
  32
  33 create table votes (
  34     id INTEGER primary key autoincrement,
  35     title TEXT not null,
  36     description TEXT,
  37     category TEXT,
  38     date_begin INTEGER default CURRENT_TIMESTAMP not null,
  39     date_end INTEGER not null,
  40     is_transparent INTEGER default 1 not null,
  41     is_public INTEGER default 1 not null,
  42     is_multiplechoice INTEGER default 1 not null,
  43     is_weighted INTEGER default 0 not null,
  44     is_open INTEGER default 0 not null,
  45     id_author INTEGER, -- :COMMENT:maethor:120528: not null ?
  46     id_group INTEGER default 1 not null,
  47     FOREIGN KEY(id_author) REFERENCES users(id)
  48     FOREIGN KEY(id_group) REFERENCES groups(id)
  49 );
  50
  51 create table attachments (
  52     id INTEGER primary key autoincrement,
  53     url TEXT not null,
  54     id_vote INTEGER not null,
  55     FOREIGN KEY(id_vote) REFERENCES vote(id)
  56 );
  57
  58 create table choices (
  59     id INTEGER primary key autoincrement,
  60     name TEXT not null,
  61     id_vote INTEGER not null,
  62     FOREIGN KEY(id_vote) REFERENCES vote(id)
  63 );
  64
  65 create table user_choice (
  66     id_user INTEGER,
  67     id_choice INTEGER,
  68     weight INTEGER,
  69     FOREIGN KEY(id_user) REFERENCES users(id),
  70     FOREIGN KEY(id_choice) REFERENCES choices(id),
  71     PRIMARY KEY(id_user, id_choice)
  72 );
  73
  74 -- Test data
  75
  76 insert into users (email, password, name, organization, is_admin, key) values ("admin@admin.fr", "d033e22ae348aeb5660fc2140aec35850c4da997", "Toto (admin) Tata", "World corp", 1, "test"); -- mdp = admin
  77 insert into groups (id, name, system) values (1, "Tous", 1);
  78 insert into groups (name) values ("CA");
  79 insert into groups (name) values ("Members");
  80