Context Navigation

Back to Ticket #13

Ticket #13: 01quiz_schema.sql

File 01quiz_schema.sql, 10.8 KB (added by me egan, 17 months ago)
Snap shot of SQL at time of dot graph generation

Line
1
2	CREATE TABLE user (
3	id INTEGER NOT NULL,
4	username VARCHAR(80) NOT NULL,
5	password VARCHAR(80) NOT NULL,
6	email VARCHAR(120) NOT NULL,
7	PRIMARY KEY (id),
8	UNIQUE (username),
9	UNIQUE (email)
10	);
11	CREATE TABLE IF NOT EXISTS "quiz_questions" (
12	"id" INTEGER UNIQUE,
13	"question_id" INTEGER,
14	"quiz_id" INTEGER,
15	PRIMARY KEY("id" AUTOINCREMENT)
16	);
17	--CREATE TABLE sqlite_sequence(name,seq);
18	CREATE TABLE IF NOT EXISTS "quiz_types" (
19	"id" INTEGER UNIQUE,
20	"name" INTEGER,
21	PRIMARY KEY("id" AUTOINCREMENT)
22	);
23	CREATE TABLE IF NOT EXISTS "question_files" (
24	"id" INTEGER UNIQUE,
25	"quiz_id" INTEGER,
26	"file_name" TEXT,
27	PRIMARY KEY("id" AUTOINCREMENT)
28	);
29	CREATE TABLE IF NOT EXISTS "subjects" (
30	"id" INTEGER UNIQUE,
31	"name" TEXT,
32	PRIMARY KEY("id" AUTOINCREMENT)
33	);
34	CREATE TABLE IF NOT EXISTS "quiz_results" (
35	"id" INTEGER UNIQUE,
36	"file_name" TEXT,
37	"start_time" REAL,
38	"end_time" REAL,
39	"total_questions" INTEGER,
40	"score" REAL,
41	"timed" INTEGER,
42	"student_id" INTEGER,
43	"duration" INTEGER,
44	PRIMARY KEY("id" AUTOINCREMENT)
45	);
46	CREATE TABLE IF NOT EXISTS "config" (
47	"data_path" INTEGER
48	);
49	CREATE TABLE IF NOT EXISTS "question_choices" (
50	"id" INTEGER UNIQUE,
51	"question_id" INTEGER,
52	"choice" TEXT,
53	PRIMARY KEY("id" AUTOINCREMENT)
54	);
55	CREATE TABLE IF NOT EXISTS "quizes" (
56	"id" INTEGER UNIQUE,
57	"name" TEXT,
58	"description" TEXT,
59	PRIMARY KEY("id" AUTOINCREMENT)
60	);
61	CREATE TABLE IF NOT EXISTS "choice_types" (
62	"id" INTEGER UNIQUE,
63	"name" TEXT,
64	"description" TEXT,
65	PRIMARY KEY("id" AUTOINCREMENT)
66	);
67	CREATE TABLE IF NOT EXISTS "choice_type_lookup" (
68	"id" INTEGER NOT NULL UNIQUE,
69	"choice_type_id" INTEGER,
70	"name" INTEGER,
71	PRIMARY KEY("id")
72	);
73	CREATE TABLE IF NOT EXISTS "question_updated" (
74	"id" INTEGER UNIQUE,
75	"question_id" INTEGER,
76	"description" INTEGER,
77	"user_id" INTEGER,
78	"time_stamp" NUMERIC,
79	PRIMARY KEY("id" AUTOINCREMENT)
80	);
81	CREATE TABLE IF NOT EXISTS "question_review" (
82	"id" INTEGER UNIQUE,
83	"nec_version_id" INTEGER NOT NULL,
84	"notes" TEXT NOT NULL,
85	"user_id" INTEGER NOT NULL,
86	"time_stamp" REAL NOT NULL,
87	"question_id" INTEGER NOT NULL,
88	"action_type_id" INTEGER NOT NULL,
89	PRIMARY KEY("id" AUTOINCREMENT)
90	);
91	CREATE TABLE IF NOT EXISTS "question_review_action_types" (
92	"id" INTEGER UNIQUE,
93	"name" TEXT,
94	"description" TEXT,
95	PRIMARY KEY("id" AUTOINCREMENT)
96	);
97	CREATE TABLE IF NOT EXISTS "state_rc" (
98	"id" INTEGER NOT NULL UNIQUE,
99	"section" TEXT NOT NULL,
100	"title" INTEGER NOT NULL,
101	"state_id" INTEGER NOT NULL,
102	"rc_version_id" INTEGER NOT NULL,
103	PRIMARY KEY("id" AUTOINCREMENT)
104	);
105	CREATE TABLE IF NOT EXISTS "state_ac" (
106	"id" INTEGER NOT NULL UNIQUE,
107	"article" TEXT NOT NULL,
108	"title" TEXT NOT NULL,
109	"state_id" INTEGER NOT NULL,
110	"ac_version_id" INTEGER NOT NULL,
111	PRIMARY KEY("id" AUTOINCREMENT)
112	);
113	CREATE TABLE IF NOT EXISTS "glossary_term_refs" (
114	"id" INTEGER NOT NULL UNIQUE,
115	"type_id" INTEGER NOT NULL,
116	"term_id" INTEGER NOT NULL,
117	PRIMARY KEY("id" AUTOINCREMENT)
118	);
119	CREATE TABLE IF NOT EXISTS "nec_language_fail" (
120	"id" INTEGER NOT NULL UNIQUE,
121	"notes" INTEGER NOT NULL,
122	"nec_ref" INTEGER NOT NULL,
123	"nec_article_section_id" INTEGER NOT NULL,
124	"nec_version_id" INTEGER NOT NULL,
125	PRIMARY KEY("id" AUTOINCREMENT)
126	);
127	CREATE TABLE IF NOT EXISTS "nec_glossary" (
128	"id" INTEGER NOT NULL UNIQUE,
129	"term" TEXT NOT NULL,
130	"def" TEXT,
131	"nec_version_id" INTEGER,
132	"refs" TEXT,
133	PRIMARY KEY("id" AUTOINCREMENT)
134	);
135	CREATE TABLE IF NOT EXISTS "nec_versions" (
136	"id" INTEGER UNIQUE,
137	"year" TEXT,
138	"title" TEXT,
139	PRIMARY KEY("id" AUTOINCREMENT)
140	);
141	CREATE TABLE IF NOT EXISTS "nec_tables" (
142	"id" INTEGER NOT NULL UNIQUE,
143	"Name" INTEGER NOT NULL,
144	"description" TEXT NOT NULL,
145	"nec_version_id" INTEGER NOT NULL,
146	"nec_article_id" INTEGER NOT NULL,
147	"nec_chapter_id" INTEGER NOT NULL,
148	PRIMARY KEY("id" AUTOINCREMENT)
149	);
150	CREATE TABLE IF NOT EXISTS "nec_chapters" (
151	"id" INTEGER NOT NULL UNIQUE,
152	"number" INTEGER NOT NULL,
153	"title" TEXT NOT NULL,
154	"description" TEXT NOT NULL,
155	"nec_version_id" INTEGER NOT NULL,
156	PRIMARY KEY("id" AUTOINCREMENT)
157	);
158	CREATE TABLE IF NOT EXISTS "nec_article_sections" (
159	"id" INTEGER NOT NULL UNIQUE,
160	"number" REAL NOT NULL,
161	"description" TEXT NOT NULL,
162	"article_id" INTEGER NOT NULL,
163	"chapter_id" INTEGER NOT NULL,
164	"nec_version_id" INTEGER NOT NULL,
165	PRIMARY KEY("id" AUTOINCREMENT)
166	);
167	CREATE TABLE IF NOT EXISTS "quiz_results_questions" (
168	"id" INTEGER UNIQUE,
169	"q_id" INTEGER,
170	"quiz_result_id" INTEGER,
171	"wrong" INTEGER DEFAULT 1,
172	"marked" INTEGER DEFAULT 0,
173	"review_nbr" INTEGER,
174	PRIMARY KEY("id" AUTOINCREMENT)
175	);
176	CREATE TABLE IF NOT EXISTS "quiz_result_questions_time_intervals" (
177	"id" INTEGER NOT NULL UNIQUE,
178	"quiz_id" INTEGER,
179	"qid" INTEGER,
180	"start_t" REAL,
181	"end_t" REAL,
182	"correct" INTEGER,
183	"user_id" INTEGER,
184	PRIMARY KEY("id" AUTOINCREMENT)
185	);
186	CREATE TABLE IF NOT EXISTS "questions" (
187	"id" INTEGER UNIQUE,
188	"subject_id" TEXT,
189	"question" TEXT,
190	"answer" TEXT,
191	"notes" TEXT,
192	"refs" TEXT,
193	"choice_type" INTEGER,
194	"type_id" INTEGER,
195	PRIMARY KEY("id" AUTOINCREMENT)
196	);
197	CREATE TABLE IF NOT EXISTS "question_type" (
198	"id" INTEGER UNIQUE,
199	"name" TEXT,
200	"description" TEXT,
201	PRIMARY KEY("id" AUTOINCREMENT)
202	);
203	CREATE TABLE IF NOT EXISTS "question_type_ids" (
204	"id" INTEGER NOT NULL UNIQUE,
205	"qid" INTEGER,
206	"qid_type_id" INTEGER,
207	PRIMARY KEY("id" AUTOINCREMENT)
208	);
209	CREATE TABLE IF NOT EXISTS "code_question_of_the_day" (
210	"id" INTEGER NOT NULL UNIQUE,
211	"qid" INTEGER NOT NULL,
212	PRIMARY KEY("id" AUTOINCREMENT)
213	);
214	CREATE TABLE IF NOT EXISTS "quiz_question_state" (
215	"id" INTEGER NOT NULL UNIQUE,
216	"quiz_state_id" INTEGER NOT NULL,
217	"qid" INTEGER NOT NULL,
218	"q_choice" INTEGER,
219	"correct" INTEGER,
220	PRIMARY KEY("id" AUTOINCREMENT)
221	);
222	CREATE TABLE IF NOT EXISTS "quiz_state" (
223	"id" INTEGER NOT NULL UNIQUE,
224	"user_id" INTEGER NOT NULL,
225	"quiz_id" INTEGER NOT NULL,
226	"start_datetime" INTEGER NOT NULL,
227	"end_datetime" INTEGER NOT NULL,
228	"quiz_question_state_id" INTEGER NOT NULL,
229	PRIMARY KEY("id" AUTOINCREMENT)
230	);
231	CREATE TABLE IF NOT EXISTS "users" (
232	"id" INTEGER DEFAULT 1000 UNIQUE,
233	"user_name" TEXT NOT NULL UNIQUE,
234	"admin" INTEGER,
235	"password" BLOB,
236	"salt" BLOB,
237	PRIMARY KEY("id" AUTOINCREMENT)
238	);
239	CREATE TABLE IF NOT EXISTS "tmp_additional_nec_glossary" (
240	"id" INTEGER NOT NULL UNIQUE,
241	"term" TEXT,
242	"definition" TEXT,
243	"nec_ver" TEXT DEFAULT 'NEC_2011_HANDBOOK',
244	PRIMARY KEY("id" AUTOINCREMENT)
245	);
246	CREATE TABLE IF NOT EXISTS "nec_articles" (
247	"id" INTEGER NOT NULL UNIQUE,
248	"number" INTEGER NOT NULL,
249	"title" TEXT NOT NULL,
250	"definition" TEXT,
251	"chapter_id" INTEGER,
252	"nec_version_id" INTEGER NOT NULL,
253	PRIMARY KEY("id" AUTOINCREMENT)
254	);
255	CREATE TABLE IF NOT EXISTS "wac_glossary" (
256	"id" INTEGER NOT NULL UNIQUE,
257	"term" TEXT NOT NULL,
258	"definition" TEXT NOT NULL,
259	"term_referance_id" INTEGER,
260	"refs" TEXT,
261	"wac_version_id" INTEGER,
262	PRIMARY KEY("id" AUTOINCREMENT)
263	);
264	CREATE TABLE IF NOT EXISTS "rcw_glossary" (
265	"id" INTEGER NOT NULL UNIQUE,
266	"term" TEXT NOT NULL,
267	"definition" TEXT,
268	"refs" INTEGER,
269	"rcw_version_id" INTEGER,
270	PRIMARY KEY("id" AUTOINCREMENT)
271	);
272	CREATE TABLE IF NOT EXISTS "quiz_state_types" (
273	"id" INTEGER NOT NULL UNIQUE,
274	"name" TEXT,
275	PRIMARY KEY("id" AUTOINCREMENT)
276	);
277	CREATE TABLE IF NOT EXISTS "quiz_in_progress_state_times" (
278	"id" INTEGER NOT NULL UNIQUE,
279	"quiz_id" INTEGER,
280	"in_progress_id" INTEGER,
281	"datetime" STRING(19),
282	"quiz_state_type_id" INTEGER,
283	PRIMARY KEY("id" AUTOINCREMENT)
284	);
285	CREATE TABLE role (
286	id INTEGER UNIQUE,
287	name VARCHAR(80),
288	description VARCHAR(255),
289	UNIQUE (name),
290	PRIMARY KEY(id AUTOINCREMENT)
291	);
292	CREATE TABLE roles_users (
293	id INTEGER UNIQUE,
294	user_id INTEGER,
295	role_id INTEGER,
296	PRIMARY KEY(id AUTOINCREMENT)
297	);
298	CREATE TABLE IF NOT EXISTS "quiz_in_progress_tmp" (
299	"id" INTEGER NOT NULL UNIQUE,
300	"user_id" INTEGER NOT NULL,
301	"quiz_id" INTEGER NOT NULL,
302	"state_id" INTEGER NOT NULL DEFAULT 1,
303	PRIMARY KEY("id" AUTOINCREMENT)
304	);
305	CREATE TABLE IF NOT EXISTS "quiz_in_progress" (
306	"id" INTEGER NOT NULL UNIQUE,
307	"user_id" INTEGER NOT NULL,
308	"quiz_id" INTEGER NOT NULL,
309	"state_id" INTEGER NOT NULL DEFAULT 1,
310	"quiz_hh" INTEGER,
311	"quiz_mm"INTEGER,
312	"quiz_ss" INTEGER,
313	PRIMARY KEY("id" AUTOINCREMENT)
314	);
315	CREATE TABLE IF NOT EXISTS "quiz_in_progress_questions_answered" (
316	"id"INTEGER NOT NULL UNIQUE,
317	"question_id"INTEGER NOT NULL,
318	"latest_start_time"INTEGER,
319	"duration_time"INTEGER,
320	"choice"INTEGER NOT NULL,
321	"in_progress_id"INTEGER,
322	"visit_cnt"TEGER DEFAULT 1,
323	"marked"INTEGER DEFAULT 0,
324	PRIMARY KEY("id" AUTOINCREMENT)
325	);
326	CREATE TABLE promo_codes (
327	id INTEGER NOT NULL,
328	code VARCHAR(64) NOT NULL,
329	discount REAL NOT NULL,
330	partner VARCHAR(128),
331	description VARCHAR(512) NOT NULL,
332	PRIMARY KEY (id),
333	UNIQUE (code)
334	);
335	--CREATE VIEW "test_results" AS select id, file_name, (end_time - start_time)/60 as mins_taken_for_test, student_id, total_questions as nbr_of_q, (end_time -start_time)/total_questions as sec_per_q, ((end_time -start_time)/total_questions)/60 as mins_per_q, score from quiz_results
336	/* test_results(id,file_name,mins_taken_for_test,student_id,nbr_of_q,sec_per_q,mins_per_q,score) */;
337	CREATE TABLE quiz_in_progress_types (
338	id INTEGER NOT NULL,
339	name VARCHAR(64) NOT NULL,
340	PRIMARY KEY (id),
341	UNIQUE (name)
342	);
343	CREATE TABLE package_types (
344	id INTEGER NOT NULL,
345	name VARCHAR(64) NOT NULL,
346	PRIMARY KEY (id),
347	UNIQUE (name)
348	);
349	CREATE TABLE package_quizes (
350	id INTEGER NOT NULL,
351	p_id INTEGER NOT NULL, /* foreign key to packages */
352	quiz_id INTEGER,
353	PRIMARY KEY (id)
354	);
355	CREATE TABLE user_quizes (
356	id INTEGER NOT NULL,
357	user_id INTEGER,
358	quiz_id INTEGER, /* packages_id */
359	PRIMARY KEY (id)
360	);
361	CREATE TABLE user_packages (
362	id INTEGER NOT NULL,
363	user_id INTEGER,
364	package_id INTEGER, /* packages_id */
365	PRIMARY KEY (id)
366	);
367	CREATE TABLE IF NOT EXISTS "quiz_time_limits" (
368	"id" INTEGER NOT NULL UNIQUE,
369	"quiz_id" INTEGER NOT NULL,
370	"quiz_hh" INTEGER,
371	"quiz_mm" INTEGER,
372	"quiz_ss" INTEGER,
373	PRIMARY KEY("id" AUTOINCREMENT)
374	);
375	CREATE TABLE packages (
376	id INTEGER NOT NULL,
377	package_type_id INTEGER NOT NULL, /foreignkey to package_types /
378	name VARCHAR(64) NOT NULL,
379	description VARCHAR(512) NOT NULL,
380	PRIMARY KEY (id)
381	);
382
383	;; ------ test string --------
384
385	-- CREATE TABLE packages (
386	-- id INTEGER NOT NULL,
387	-- package_type_id INTEGER NOT NULL, /foreignkey to package_types /
388	-- name VARCHAR(64) NOT NULL,
389	-- description VARCHAR(512) NOT NULL,
390	-- PRIMARY KEY (id)
391	-- );
392	-- CREATE TABLE promo_codes (
393	-- id INTEGER NOT NULL,
394	-- code VARCHAR(64) NOT NULL,
395	-- discount REAL NOT NULL,
396	-- partner VARCHAR(128),
397	-- description VARCHAR(512) NOT NULL,
398	-- PRIMARY KEY (id),
399	-- UNIQUE (code)
400	-- );

Download in other formats:

Original Format