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,
15 name TEXT UNIQUE NOT NULL,
17 is_admin
BOOLEAN DEFAULT 0 NOT NULL,
19 CHECK (is_admin
IN (0, 1))
23 id INTEGER PRIMARY KEY AUTOINCREMENT
,
24 name TEXT UNIQUE NOT NULL,
25 system BOOLEAN DEFAULT 0 NOT NULL,
26 CHECK (system IN (0, 1))
29 create table user_group (
32 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
33 FOREIGN KEY(id_group
) REFERENCES groups (id) ON DELETE CASCADE,
34 PRIMARY KEY(id_user
, id_group
)
38 id INTEGER PRIMARY KEY AUTOINCREMENT
,
42 date_begin
INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
43 date_end
INTEGER NOT NULL,
44 is_transparent
BOOLEAN DEFAULT 1 NOT NULL,
45 is_public
BOOLEAN DEFAULT 1 NOT NULL,
46 is_multiplechoice
BOOLEAN DEFAULT 1 NOT NULL,
47 is_weighted
BOOLEAN DEFAULT 0 NOT NULL,
48 is_open
BOOLEAN DEFAULT 0 NOT NULL,
49 is_terminated
BOOLEAN DEFAULT 0 NOT NULL,
50 id_author
INTEGER DEFAULT 1 NOT NULL,
51 id_group
INTEGER DEFAULT 1 NOT NULL,
52 FOREIGN KEY(id_author
) REFERENCES users (id) ON DELETE SET DEFAULT,
53 FOREIGN KEY(id_group
) REFERENCES groups (id),
54 CHECK (is_transparent
IN (0, 1)),
55 CHECK (is_public
IN (0, 1)),
56 CHECK (is_weighted
IN (0, 1)),
57 CHECK (is_open
IN (0, 1)),
58 CHECK (is_terminated
IN (0, 1))
61 create table attachments (
62 id INTEGER PRIMARY KEY AUTOINCREMENT
,
64 id_vote
INTEGER NOT NULL,
65 FOREIGN KEY(id_vote
) REFERENCES votes (id) ON DELETE CASCADE
68 create table choices (
69 id INTEGER PRIMARY KEY AUTOINCREMENT
,
71 id_vote
INTEGER NOT NULL,
72 FOREIGN KEY(id_vote
) REFERENCES votes (id) ON DELETE CASCADE
75 create table user_choice (
79 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
80 FOREIGN KEY(id_choice
) REFERENCES choices (id) ON DELETE CASCADE,
81 PRIMARY KEY(id_user
, id_choice
)
86 INSERT INTO users (id, email
, password, name, organization
, is_admin
, key)
87 VALUES (1, "admin@admin.fr", "", "Toto (admin) Tata", "World corp", 1, "victory");
88 -- to login, go to /login/1/victory
89 INSERT INTO groups (id, name, system) VALUES (1, "Tous", 1);
90 INSERT INTO groups (name) VALUES ("CA");
91 INSERT INTO groups (name) VALUES ("Membres");
92 INSERT INTO user_group (id_user
, id_group
) VALUES(1, 1);