db_stats.sql 12 KB

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