schema.sql

   1 drop table if exists choices;
   2 drop table if exists attachments;
   3 drop table if exists votes;
   4 drop table if exists roles;
   5 drop table if exists users;
   6 drop table if exists user_role;
   7
   8 create table users (
   9     id INTEGER primary key autoincrement,
  10     email TEXT unique not null,
  11     password TEXT not null,
  12     name TEXT unique,
  13     organization TEXT,
  14     is_admin INTEGER default 0 not null,
  15     key TEXT
  16 );
  17
  18 create table roles (
  19     id INTEGER primary key autoincrement,
  20     name TEXT,
  21     system INTEGER default 0 not null
  22 );
  23
  24 create table user_role (
  25     id_user INTEGER,
  26     id_role INTEGER,
  27     FOREIGN KEY(id_user) REFERENCES users(id),
  28     FOREIGN KEY(id_role) REFERENCES roles(id),
  29     PRIMARY KEY(id_user, id_role)
  30 );
  31
  32 create table votes (
  33     id INTEGER primary key autoincrement,
  34     title TEXT not null,
  35     description TEXT,
  36     category TEXT,
  37     date_begin INTEGER default CURRENT_TIMESTAMP not null,
  38     date_end INTEGER not null,
  39     is_transparent INTEGER default 1 not null,
  40     is_public INTEGER default 1 not null,
  41     is_multiplechoice INTEGER default 1 not null,
  42     is_weighted INTEGER default 0 not null,
  43     is_open INTEGER default 0 not null,
  44     id_author INTEGER, -- :COMMENT:maethor:120528: not null ?
  45     id_role INTEGER default 1 not null,
  46     FOREIGN KEY(id_author) REFERENCES users(id)
  47     FOREIGN KEY(id_role) REFERENCES roles(id)
  48 );
  49
  50 create table attachments (
  51     url TEXT not null,
  52     id_vote INTEGER not null,
  53     FOREIGN KEY(id_vote) REFERENCES vote(id),
  54     PRIMARY KEY(url, id_vote)
  55 );
  56
  57 create table choices (
  58     id INTEGER primary key autoincrement,
  59     name TEXT not null,
  60     id_vote INTEGER not null,
  61     FOREIGN KEY(id_vote) REFERENCES vote(id)
  62 );
  63
  64 -- Test data
  65
  66 insert into users (email, password, name, organization, is_admin, key) values ("admin@admin.fr", "d033e22ae348aeb5660fc2140aec35850c4da997", "Toto (admin) Tata", "World corp", 1, "test"); -- mdp = admin
  67 insert into roles (id, name, system) values (1, "Tous", 1);
  68 insert into roles (name) values ("CA");
  69 insert into roles (name) values ("Members");
  70