db_stats.sql 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321
  1. -- MySQL dump
  2. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  3. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  4. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  5. /*!40101 SET NAMES utf8 */;
  6. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  7. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  8. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  9. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  10. CREATE TABLE track_c_browsers (
  11. id int NOT NULL auto_increment,
  12. browser varchar(255) NOT NULL default '',
  13. counter int NOT NULL default 0,
  14. PRIMARY KEY (id)
  15. );
  16. CREATE TABLE track_c_countries (
  17. id int NOT NULL auto_increment,
  18. code varchar(40) NOT NULL default '',
  19. country varchar(50) NOT NULL default '',
  20. counter int NOT NULL default 0,
  21. PRIMARY KEY (id)
  22. );
  23. CREATE TABLE track_c_os (
  24. id int NOT NULL auto_increment,
  25. os varchar(255) NOT NULL default '',
  26. counter int NOT NULL default 0,
  27. PRIMARY KEY (id)
  28. );
  29. CREATE TABLE track_c_providers (
  30. id int NOT NULL auto_increment,
  31. provider varchar(255) NOT NULL default '',
  32. counter int NOT NULL default 0,
  33. PRIMARY KEY (id)
  34. );
  35. CREATE TABLE track_c_referers (
  36. id int NOT NULL auto_increment,
  37. referer varchar(255) NOT NULL default '',
  38. counter int NOT NULL default 0,
  39. PRIMARY KEY (id)
  40. );
  41. CREATE TABLE track_e_access (
  42. access_id int NOT NULL auto_increment,
  43. access_user_id int unsigned default NULL,
  44. access_date datetime NOT NULL default '0000-00-00 00:00:00',
  45. access_cours_code varchar(40) NOT NULL default '',
  46. access_tool varchar(30) default NULL,
  47. access_session_id int NOT NULL default 0,
  48. PRIMARY KEY (access_id),
  49. KEY access_user_id (access_user_id),
  50. KEY access_cours_code (access_cours_code)
  51. );
  52. CREATE TABLE track_e_lastaccess (
  53. access_id bigint NOT NULL auto_increment,
  54. access_user_id int unsigned default NULL,
  55. access_date datetime NOT NULL default '0000-00-00 00:00:00',
  56. access_cours_code varchar(40) NOT NULL,
  57. access_tool varchar(30) default NULL,
  58. access_session_id int unsigned default NULL,
  59. PRIMARY KEY (access_id),
  60. KEY access_user_id (access_user_id),
  61. KEY access_cours_code (access_cours_code),
  62. KEY access_session_id (access_session_id)
  63. );
  64. CREATE TABLE track_e_default (
  65. default_id int NOT NULL auto_increment,
  66. default_user_id int unsigned NOT NULL default 0,
  67. default_cours_code varchar(40) NOT NULL default '',
  68. default_date datetime NOT NULL default '0000-00-00 00:00:00',
  69. default_event_type varchar(20) NOT NULL default '',
  70. default_value_type varchar(20) NOT NULL default '',
  71. default_value tinytext NOT NULL,
  72. PRIMARY KEY (default_id)
  73. );
  74. CREATE TABLE track_e_downloads (
  75. down_id int NOT NULL auto_increment,
  76. down_user_id int unsigned default NULL,
  77. down_date datetime NOT NULL default '0000-00-00 00:00:00',
  78. down_cours_id varchar(40) NOT NULL default '',
  79. down_doc_path varchar(255) NOT NULL default '',
  80. down_session_id INT NOT NULL DEFAULT 0,
  81. PRIMARY KEY (down_id),
  82. KEY down_user_id (down_user_id),
  83. KEY down_cours_id (down_cours_id)
  84. );
  85. CREATE TABLE track_e_exercices (
  86. exe_id int NOT NULL auto_increment,
  87. exe_user_id int unsigned default NULL,
  88. exe_date datetime NOT NULL default '0000-00-00 00:00:00',
  89. exe_cours_id varchar(40) NOT NULL default '',
  90. exe_exo_id mediumint unsigned NOT NULL default 0,
  91. exe_result float(6,2) NOT NULL default 0,
  92. exe_weighting float(6,2) NOT NULL default 0,
  93. PRIMARY KEY (exe_id),
  94. KEY exe_user_id (exe_user_id),
  95. KEY exe_cours_id (exe_cours_id)
  96. );
  97. ALTER TABLE track_e_exercices ADD status varchar(20) NOT NULL default '';
  98. ALTER TABLE track_e_exercices ADD data_tracking text NOT NULL default '';
  99. ALTER TABLE track_e_exercices ADD start_date datetime NOT NULL default '0000-00-00 00:00:00';
  100. ALTER TABLE track_e_exercices ADD steps_counter SMALLINT UNSIGNED NOT NULL default 0;
  101. ALTER TABLE track_e_exercices ADD session_id SMALLINT UNSIGNED NOT NULL default 0;
  102. ALTER TABLE track_e_exercices ADD INDEX ( session_id ) ;
  103. ALTER TABLE track_e_exercices ADD orig_lp_id int NOT NULL default 0;
  104. ALTER TABLE track_e_exercices ADD orig_lp_item_id int NOT NULL default 0;
  105. ALTER TABLE track_e_exercices ADD exe_duration int UNSIGNED NOT NULL default 0;
  106. ALTER TABLE track_e_exercices ADD COLUMN expired_time_control datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
  107. ALTER TABLE track_e_exercices ADD COLUMN orig_lp_item_view_id INT NOT NULL DEFAULT 0;
  108. ALTER TABLE track_e_exercices ADD COLUMN questions_to_check TEXT NOT NULL DEFAULT '';
  109. CREATE TABLE track_e_attempt (
  110. exe_id int default NULL,
  111. user_id int NOT NULL default 0,
  112. question_id int NOT NULL default 0,
  113. answer text NOT NULL,
  114. teacher_comment text NOT NULL,
  115. marks float(6,2) NOT NULL default 0,
  116. course_code varchar(40) NOT NULL default '',
  117. position int default 0,
  118. tms datetime NOT NULL default '0000-00-00 00:00:00',
  119. session_id INT NOT NULL DEFAULT 0
  120. );
  121. ALTER TABLE track_e_attempt ADD INDEX (exe_id);
  122. ALTER TABLE track_e_attempt ADD INDEX (user_id);
  123. ALTER TABLE track_e_attempt ADD INDEX (question_id);
  124. ALTER TABLE track_e_attempt ADD INDEX (session_id);
  125. CREATE TABLE track_e_attempt_recording (
  126. exe_id int unsigned NOT NULL,
  127. question_id int unsigned NOT NULL,
  128. marks int NOT NULL,
  129. insert_date datetime NOT NULL default '0000-00-00 00:00:00',
  130. author int unsigned NOT NULL,
  131. teacher_comment text NOT NULL,
  132. session_id INT NOT NULL DEFAULT 0
  133. );
  134. ALTER TABLE track_e_attempt_recording ADD INDEX (exe_id);
  135. ALTER TABLE track_e_attempt_recording ADD INDEX (question_id);
  136. ALTER TABLE track_e_attempt_recording ADD INDEX (session_id);
  137. CREATE TABLE track_e_hotpotatoes (
  138. exe_name VARCHAR( 255 ) NOT NULL ,
  139. exe_user_id int unsigned DEFAULT NULL ,
  140. exe_date DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL ,
  141. exe_cours_id varchar(40) NOT NULL ,
  142. exe_result smallint default 0 NOT NULL ,
  143. exe_weighting smallint default 0 NOT NULL,
  144. KEY exe_user_id (exe_user_id),
  145. KEY exe_cours_id (exe_cours_id)
  146. );
  147. CREATE TABLE track_e_links (
  148. links_id int NOT NULL auto_increment,
  149. links_user_id int unsigned default NULL,
  150. links_date datetime NOT NULL default '0000-00-00 00:00:00',
  151. links_cours_id varchar(40) NOT NULL default '' ,
  152. links_link_id int NOT NULL default 0,
  153. links_session_id INT NOT NULL DEFAULT 0,
  154. PRIMARY KEY (links_id),
  155. KEY links_cours_id (links_cours_id),
  156. KEY links_user_id (links_user_id)
  157. );
  158. CREATE TABLE track_e_login (
  159. login_id int NOT NULL auto_increment,
  160. login_user_id int unsigned NOT NULL default 0,
  161. login_date datetime NOT NULL default '0000-00-00 00:00:00',
  162. login_ip varchar(39) NOT NULL default '',
  163. logout_date datetime NULL default NULL,
  164. PRIMARY KEY (login_id),
  165. KEY login_user_id (login_user_id)
  166. );
  167. CREATE TABLE track_e_online (
  168. login_id int NOT NULL auto_increment,
  169. login_user_id int unsigned NOT NULL default 0,
  170. login_date datetime NOT NULL default '0000-00-00 00:00:00',
  171. login_ip varchar(39) NOT NULL default '',
  172. course varchar(40) default NULL,
  173. session_id INT NOT NULL DEFAULT 0,
  174. access_url_id INT NOT NULL DEFAULT 1,
  175. PRIMARY KEY (login_id),
  176. KEY login_user_id (login_user_id)
  177. );
  178. CREATE TABLE track_e_open (
  179. open_id int NOT NULL auto_increment,
  180. open_remote_host tinytext NOT NULL,
  181. open_agent tinytext NOT NULL,
  182. open_referer tinytext NOT NULL,
  183. open_date datetime NOT NULL default '0000-00-00 00:00:00',
  184. PRIMARY KEY (open_id)
  185. );
  186. CREATE TABLE track_e_uploads (
  187. upload_id int NOT NULL auto_increment,
  188. upload_user_id int unsigned default NULL,
  189. upload_date datetime NOT NULL default '0000-00-00 00:00:00',
  190. upload_cours_id varchar(40) NOT NULL default '',
  191. upload_work_id int NOT NULL default 0,
  192. upload_session_id INT NOT NULL DEFAULT 0,
  193. PRIMARY KEY (upload_id),
  194. KEY upload_user_id (upload_user_id),
  195. KEY upload_cours_id (upload_cours_id)
  196. );
  197. CREATE TABLE track_e_course_access (
  198. course_access_id int NOT NULL auto_increment,
  199. course_code varchar(40) NOT NULL,
  200. user_id int NOT NULL,
  201. login_course_date datetime NOT NULL default '0000-00-00 00:00:00',
  202. logout_course_date datetime default NULL,
  203. counter int NOT NULL,
  204. session_id int NOT NULL default 0,
  205. PRIMARY KEY (course_access_id)
  206. );
  207. CREATE TABLE track_e_hotspot (
  208. hotspot_id int NOT NULL auto_increment,
  209. hotspot_user_id int NOT NULL,
  210. hotspot_course_code varchar(50) NOT NULL,
  211. hotspot_exe_id int NOT NULL,
  212. hotspot_question_id int NOT NULL,
  213. hotspot_answer_id int NOT NULL,
  214. hotspot_correct tinyint(3) unsigned NOT NULL,
  215. hotspot_coordinate text NOT NULL,
  216. PRIMARY KEY (hotspot_id),
  217. KEY hotspot_course_code (hotspot_course_code),
  218. KEY hotspot_user_id (hotspot_user_id),
  219. KEY hotspot_exe_id (hotspot_exe_id),
  220. KEY hotspot_question_id (hotspot_question_id)
  221. );
  222. CREATE TABLE track_e_item_property (
  223. id int NOT NULL auto_increment PRIMARY KEY,
  224. course_id int NOT NULL,
  225. item_property_id int NOT NULL,
  226. title varchar(255),
  227. content text,
  228. progress int NOT NULL default 0,
  229. lastedit_date datetime NOT NULL default '0000-00-00 00:00:00',
  230. lastedit_user_id int NOT NULL,
  231. session_id int NOT NULL default 0
  232. );
  233. ALTER TABLE track_e_course_access ADD INDEX (user_id);
  234. ALTER TABLE track_e_course_access ADD INDEX (login_course_date);
  235. ALTER TABLE track_e_course_access ADD INDEX (course_code);
  236. ALTER TABLE track_e_course_access ADD INDEX (session_id);
  237. ALTER TABLE track_e_access ADD INDEX (access_session_id);
  238. ALTER TABLE track_e_online ADD INDEX (course);
  239. ALTER TABLE track_e_online ADD INDEX (session_id);
  240. ALTER TABLE track_e_item_property ADD INDEX (course_id, item_property_id, session_id);
  241. ALTER TABLE track_e_downloads ADD INDEX (down_session_id);
  242. ALTER TABLE track_e_links ADD INDEX (links_session_id);
  243. ALTER TABLE track_e_uploads ADD INDEX (upload_session_id);
  244. CREATE TABLE reports_keys (
  245. id int unsigned NOT NULL AUTO_INCREMENT,
  246. course_id int unsigned DEFAULT NULL,
  247. tool_id int DEFAULT NULL,
  248. child_id int DEFAULT NULL,
  249. child_name varchar(64) DEFAULT NULL,
  250. subchild_id int DEFAULT NULL,
  251. subchild_name varchar(64) DEFAULT NULL,
  252. subsubchild_id int DEFAULT NULL,
  253. subsubchild_name varchar(64) DEFAULT NULL,
  254. link varchar(256) DEFAULT NULL,
  255. PRIMARY KEY (id),
  256. KEY course_id (course_id),
  257. KEY course_id_2 (course_id,tool_id,child_id,subchild_id,subsubchild_id));
  258. CREATE TABLE reports_values (
  259. key_id int unsigned NOT NULL,
  260. user_id int unsigned DEFAULT NULL,
  261. session_id int DEFAULT NULL,
  262. attempt int DEFAULT NULL,
  263. score decimal(5,3) DEFAULT NULL,
  264. progress int DEFAULT NULL,
  265. report_time int DEFAULT NULL,
  266. KEY user_id (user_id),
  267. PRIMARY KEY (key_id,user_id,session_id,attempt));
  268. --
  269. -- Table structure for LP custom storage API
  270. --
  271. CREATE TABLE stored_values (
  272. user_id INT NOT NULL,
  273. sco_id INT NOT NULL,
  274. course_id CHAR(40) NOT NULL,
  275. sv_key CHAR(64) NOT NULL,
  276. sv_value TEXT NOT NULL
  277. );
  278. ALTER TABLE stored_values ADD KEY (user_id, sco_id, course_id, sv_key);
  279. ALTER TABLE stored_values ADD UNIQUE (user_id, sco_id, course_id, sv_key);
  280. CREATE TABLE stored_values_stack (
  281. user_id INT NOT NULL,
  282. sco_id INT NOT NULL,
  283. stack_order INT NOT NULL,
  284. course_id CHAR(40) NOT NULL,
  285. sv_key CHAR(64) NOT NULL,
  286. sv_value TEXT NOT NULL
  287. );
  288. ALTER TABLE stored_values_stack ADD KEY (user_id, sco_id, course_id, sv_key, stack_order);
  289. ALTER TABLE stored_values_stack ADD UNIQUE (user_id, sco_id, course_id, sv_key, stack_order);