schema.sql

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