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
;
9 PRAGMA foreign_keys
= ON;
12 id INTEGER PRIMARY KEY AUTOINCREMENT
,
13 email
TEXT UNIQUE NOT NULL,
14 password TEXT NOT NULL,
16 name TEXT UNIQUE NOT NULL,
18 is_admin
BOOLEAN DEFAULT 0 NOT NULL,
20 CHECK (is_admin
IN (0, 1))
24 id INTEGER PRIMARY KEY AUTOINCREMENT
,
25 name TEXT UNIQUE NOT NULL,
26 system BOOLEAN DEFAULT 0 NOT NULL,
27 CHECK (system IN (0, 1))
30 create table user_group (
33 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
34 FOREIGN KEY(id_group
) REFERENCES groups (id) ON DELETE CASCADE,
35 PRIMARY KEY(id_user
, id_group
)
39 id INTEGER PRIMARY KEY AUTOINCREMENT
,
43 date_begin
INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
44 date_end
INTEGER NOT NULL,
45 is_transparent
BOOLEAN DEFAULT 1 NOT NULL,
46 is_public
BOOLEAN DEFAULT 1 NOT NULL,
47 is_multiplechoice
BOOLEAN DEFAULT 1 NOT NULL,
48 is_weighted
BOOLEAN DEFAULT 0 NOT NULL,
49 is_open
BOOLEAN DEFAULT 0 NOT NULL,
50 is_terminated
BOOLEAN DEFAULT 0 NOT NULL,
51 id_author
INTEGER DEFAULT 1 NOT NULL,
52 id_group
INTEGER DEFAULT 1 NOT NULL,
53 FOREIGN KEY(id_author
) REFERENCES users (id) ON DELETE SET DEFAULT,
54 FOREIGN KEY(id_group
) REFERENCES groups (id),
55 CHECK (is_transparent
IN (0, 1)),
56 CHECK (is_public
IN (0, 1)),
57 CHECK (is_weighted
IN (0, 1)),
58 CHECK (is_open
IN (0, 1)),
59 CHECK (is_terminated
IN (0, 1))
62 create table attachments (
63 id INTEGER PRIMARY KEY AUTOINCREMENT
,
65 id_vote
INTEGER NOT NULL,
66 FOREIGN KEY(id_vote
) REFERENCES votes (id) ON DELETE CASCADE
69 create table choices (
70 id INTEGER PRIMARY KEY AUTOINCREMENT
,
72 id_vote
INTEGER NOT NULL,
73 FOREIGN KEY(id_vote
) REFERENCES votes (id) ON DELETE CASCADE
76 create table user_choice (
80 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
81 FOREIGN KEY(id_choice
) REFERENCES choices (id) ON DELETE CASCADE,
82 PRIMARY KEY(id_user
, id_choice
)
87 INSERT INTO users (id, email
, password, openid
, name, organization
, is_admin
, key)
88 VALUES (1, "admin@admin.fr", "", "", "Toto (admin) Tata", "World corp", 1, "victory");
89 -- to login, go to /login/1/victory
90 INSERT INTO groups (id, name, system) VALUES (1, "Tous", 1);
91 INSERT INTO groups (name) VALUES ("CA");
92 INSERT INTO groups (name) VALUES ("Membres");
93 INSERT INTO user_group (id_user
, id_group
) VALUES(1, 1);