db_stats.sql 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332
  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. c_id int unsigned default NULL,
  81. session_id int unsigned default 0,
  82. PRIMARY KEY (default_id)
  83. );
  84. DROP TABLE IF EXISTS track_e_downloads;
  85. CREATE TABLE track_e_downloads (
  86. down_id int NOT NULL auto_increment,
  87. down_user_id int unsigned default NULL,
  88. down_date datetime NOT NULL default '0000-00-00 00:00:00',
  89. down_cours_id varchar(40) NOT NULL default '',
  90. down_doc_path varchar(255) NOT NULL default '',
  91. down_session_id INT NOT NULL DEFAULT 0,
  92. PRIMARY KEY (down_id),
  93. KEY down_user_id (down_user_id),
  94. KEY down_cours_id (down_cours_id)
  95. );
  96. DROP TABLE IF EXISTS track_e_exercices;
  97. CREATE TABLE track_e_exercices (
  98. exe_id int NOT NULL auto_increment,
  99. exe_user_id int unsigned default NULL,
  100. exe_date datetime NOT NULL default '0000-00-00 00:00:00',
  101. exe_cours_id varchar(40) NOT NULL default '',
  102. exe_exo_id int unsigned NOT NULL default 0,
  103. exe_result float(6,2) NOT NULL default 0,
  104. exe_weighting float(6,2) NOT NULL default 0,
  105. PRIMARY KEY (exe_id),
  106. KEY exe_user_id (exe_user_id),
  107. KEY exe_cours_id (exe_cours_id)
  108. );
  109. ALTER TABLE track_e_exercices ADD status varchar(20) NOT NULL default '';
  110. ALTER TABLE track_e_exercices ADD data_tracking text NOT NULL default '';
  111. ALTER TABLE track_e_exercices ADD start_date datetime NOT NULL default '0000-00-00 00:00:00';
  112. ALTER TABLE track_e_exercices ADD steps_counter SMALLINT UNSIGNED NOT NULL default 0;
  113. ALTER TABLE track_e_exercices ADD session_id INT UNSIGNED NOT NULL default 0;
  114. ALTER TABLE track_e_exercices ADD INDEX ( session_id ) ;
  115. ALTER TABLE track_e_exercices ADD orig_lp_id int NOT NULL default 0;
  116. ALTER TABLE track_e_exercices ADD orig_lp_item_id int NOT NULL default 0;
  117. ALTER TABLE track_e_exercices ADD exe_duration int UNSIGNED NOT NULL default 0;
  118. ALTER TABLE track_e_exercices ADD COLUMN expired_time_control datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
  119. ALTER TABLE track_e_exercices ADD COLUMN orig_lp_item_view_id INT NOT NULL DEFAULT 0;
  120. ALTER TABLE track_e_exercices ADD COLUMN questions_to_check TEXT NOT NULL DEFAULT '';
  121. DROP TABLE IF EXISTS track_e_attempt;
  122. CREATE TABLE track_e_attempt (
  123. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  124. exe_id int default NULL,
  125. user_id int NOT NULL default 0,
  126. question_id int NOT NULL default 0,
  127. answer text NOT NULL,
  128. teacher_comment text NOT NULL,
  129. marks float(6,2) NOT NULL default 0,
  130. course_code varchar(40) NOT NULL default '',
  131. position int default 0,
  132. tms datetime NOT NULL default '0000-00-00 00:00:00',
  133. session_id INT NOT NULL DEFAULT 0,
  134. filename VARCHAR(255) DEFAULT NULL
  135. );
  136. ALTER TABLE track_e_attempt ADD INDEX (exe_id);
  137. ALTER TABLE track_e_attempt ADD INDEX (user_id);
  138. ALTER TABLE track_e_attempt ADD INDEX (question_id);
  139. ALTER TABLE track_e_attempt ADD INDEX (session_id);
  140. DROP TABLE IF EXISTS track_e_attempt_recording;
  141. CREATE TABLE track_e_attempt_recording (
  142. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  143. exe_id int unsigned NOT NULL,
  144. question_id int unsigned NOT NULL,
  145. marks int NOT NULL,
  146. insert_date datetime NOT NULL default '0000-00-00 00:00:00',
  147. author int unsigned NOT NULL,
  148. teacher_comment text NOT NULL,
  149. session_id INT NOT NULL DEFAULT 0
  150. );
  151. ALTER TABLE track_e_attempt_recording ADD INDEX (exe_id);
  152. ALTER TABLE track_e_attempt_recording ADD INDEX (question_id);
  153. ALTER TABLE track_e_attempt_recording ADD INDEX (session_id);
  154. DROP TABLE IF EXISTS track_e_hotpotatoes;
  155. CREATE TABLE track_e_hotpotatoes (
  156. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  157. exe_name VARCHAR( 255 ) NOT NULL ,
  158. exe_user_id int unsigned DEFAULT NULL ,
  159. exe_date DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL ,
  160. exe_cours_id varchar(40) NOT NULL ,
  161. exe_result smallint default 0 NOT NULL ,
  162. exe_weighting smallint default 0 NOT NULL,
  163. KEY exe_user_id (exe_user_id),
  164. KEY exe_cours_id (exe_cours_id)
  165. );
  166. DROP TABLE IF EXISTS track_e_links;
  167. CREATE TABLE track_e_links (
  168. links_id int NOT NULL auto_increment,
  169. links_user_id int unsigned default NULL,
  170. links_date datetime NOT NULL default '0000-00-00 00:00:00',
  171. links_cours_id varchar(40) NOT NULL default '' ,
  172. links_link_id int NOT NULL default 0,
  173. links_session_id INT NOT NULL DEFAULT 0,
  174. PRIMARY KEY (links_id),
  175. KEY links_cours_id (links_cours_id),
  176. KEY links_user_id (links_user_id)
  177. );
  178. DROP TABLE IF EXISTS track_e_login;
  179. CREATE TABLE track_e_login (
  180. login_id int NOT NULL auto_increment,
  181. login_user_id int unsigned NOT NULL default 0,
  182. login_date datetime NOT NULL default '0000-00-00 00:00:00',
  183. login_ip varchar(39) NOT NULL default '',
  184. logout_date datetime NULL default NULL,
  185. PRIMARY KEY (login_id),
  186. KEY login_user_id (login_user_id)
  187. );
  188. DROP TABLE IF EXISTS track_e_online;
  189. CREATE TABLE track_e_online (
  190. login_id int NOT NULL auto_increment,
  191. login_user_id int unsigned NOT NULL default 0,
  192. login_date datetime NOT NULL default '0000-00-00 00:00:00',
  193. login_ip varchar(39) NOT NULL default '',
  194. course varchar(40) default NULL,
  195. session_id INT NOT NULL DEFAULT 0,
  196. access_url_id INT NOT NULL DEFAULT 1,
  197. PRIMARY KEY (login_id),
  198. KEY login_user_id (login_user_id)
  199. );
  200. DROP TABLE IF EXISTS track_e_open;
  201. CREATE TABLE track_e_open (
  202. open_id int NOT NULL auto_increment,
  203. open_remote_host tinytext NOT NULL,
  204. open_agent tinytext NOT NULL,
  205. open_referer tinytext NOT NULL,
  206. open_date datetime NOT NULL default '0000-00-00 00:00:00',
  207. PRIMARY KEY (open_id)
  208. );
  209. DROP TABLE IF EXISTS track_e_uploads;
  210. CREATE TABLE track_e_uploads (
  211. upload_id int NOT NULL auto_increment,
  212. upload_user_id int unsigned default NULL,
  213. upload_date datetime NOT NULL default '0000-00-00 00:00:00',
  214. upload_cours_id varchar(40) NOT NULL default '',
  215. upload_work_id int NOT NULL default 0,
  216. upload_session_id INT NOT NULL DEFAULT 0,
  217. PRIMARY KEY (upload_id),
  218. KEY upload_user_id (upload_user_id),
  219. KEY upload_cours_id (upload_cours_id)
  220. );
  221. DROP TABLE IF EXISTS track_e_course_access;
  222. CREATE TABLE track_e_course_access (
  223. course_access_id int NOT NULL auto_increment,
  224. course_code varchar(40) NOT NULL,
  225. user_id int NOT NULL,
  226. login_course_date datetime NOT NULL default '0000-00-00 00:00:00',
  227. logout_course_date datetime default NULL,
  228. counter int NOT NULL,
  229. session_id int NOT NULL default 0,
  230. c_id int NOT NULL default 0,
  231. PRIMARY KEY (course_access_id)
  232. );
  233. DROP TABLE IF EXISTS track_e_hotspot;
  234. CREATE TABLE track_e_hotspot (
  235. hotspot_id int NOT NULL auto_increment,
  236. hotspot_user_id int NOT NULL,
  237. hotspot_course_code varchar(50) NOT NULL,
  238. hotspot_exe_id int NOT NULL,
  239. hotspot_question_id int NOT NULL,
  240. hotspot_answer_id int NOT NULL,
  241. hotspot_correct tinyint(3) unsigned NOT NULL,
  242. hotspot_coordinate text NOT NULL,
  243. PRIMARY KEY (hotspot_id),
  244. KEY hotspot_course_code (hotspot_course_code),
  245. KEY hotspot_user_id (hotspot_user_id),
  246. KEY hotspot_exe_id (hotspot_exe_id),
  247. KEY hotspot_question_id (hotspot_question_id)
  248. );
  249. DROP TABLE IF EXISTS track_e_item_property;
  250. CREATE TABLE track_e_item_property (
  251. id int NOT NULL auto_increment PRIMARY KEY,
  252. course_id int NOT NULL,
  253. item_property_id int NOT NULL,
  254. title varchar(255),
  255. content text,
  256. progress int NOT NULL default 0,
  257. lastedit_date datetime NOT NULL default '0000-00-00 00:00:00',
  258. lastedit_user_id int NOT NULL,
  259. session_id int NOT NULL default 0
  260. );
  261. ALTER TABLE track_e_course_access ADD INDEX (user_id);
  262. ALTER TABLE track_e_course_access ADD INDEX (login_course_date);
  263. ALTER TABLE track_e_course_access ADD INDEX (course_code);
  264. ALTER TABLE track_e_course_access ADD INDEX (session_id);
  265. ALTER TABLE track_e_access ADD INDEX (access_session_id);
  266. ALTER TABLE track_e_online ADD INDEX (course);
  267. ALTER TABLE track_e_online ADD INDEX (session_id);
  268. ALTER TABLE track_e_online ADD INDEX idx_trackonline_uat (login_user_id, access_url_id, login_date);
  269. ALTER TABLE track_e_item_property ADD INDEX (course_id, item_property_id, session_id);
  270. ALTER TABLE track_e_downloads ADD INDEX (down_session_id);
  271. ALTER TABLE track_e_links ADD INDEX (links_session_id);
  272. ALTER TABLE track_e_uploads ADD INDEX (upload_session_id);
  273. --
  274. -- Table structure for LP custom storage API
  275. --
  276. DROP TABLE IF EXISTS track_stored_values;
  277. CREATE TABLE IF NOT EXISTS track_stored_values (
  278. id int unsigned not null AUTO_INCREMENT PRIMARY KEY,
  279. user_id INT NOT NULL,
  280. sco_id INT NOT NULL,
  281. course_id CHAR(40) NOT NULL,
  282. sv_key CHAR(64) NOT NULL,
  283. sv_value TEXT NOT NULL
  284. );
  285. ALTER TABLE track_stored_values ADD KEY (user_id, sco_id, course_id, sv_key);
  286. ALTER TABLE track_stored_values ADD UNIQUE (user_id, sco_id, course_id, sv_key);
  287. DROP TABLE IF EXISTS track_stored_value_stack;
  288. CREATE TABLE IF NOT EXISTS track_stored_values_stack (
  289. id int unsigned not null AUTO_INCREMENT PRIMARY KEY,
  290. user_id INT NOT NULL,
  291. sco_id INT NOT NULL,
  292. stack_order INT NOT NULL,
  293. course_id CHAR(40) NOT NULL,
  294. sv_key CHAR(64) NOT NULL,
  295. sv_value TEXT NOT NULL
  296. );
  297. ALTER TABLE track_stored_values_stack ADD KEY (user_id, sco_id, course_id, sv_key, stack_order);
  298. ALTER TABLE track_stored_values_stack ADD UNIQUE (user_id, sco_id, course_id, sv_key, stack_order);
  299. DROP TABLE IF EXISTS track_e_attempt_coeff;
  300. CREATE TABLE track_e_attempt_coeff (
  301. id int unsigned not null auto_increment primary key,
  302. attempt_id INT NOT NULL,
  303. marks_coeff float(6,2)
  304. );