db_stats.sql 11 KB

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