Convert the text cache to use a blob, so that there are no character set
[moodle.git] / lib / db / mysql.sql
CommitLineData
93777df6 1# phpMyAdmin MySQL-Dump
2# version 2.3.0-dev
3# http://phpwizard.net/phpMyAdmin/
4# http://www.phpmyadmin.net/ (download page)
5#
6# Host: localhost
b5fe4c93 7# Generation Time: Jun 25, 2002 at 05:04 PM
93777df6 8# Server version: 3.23.49
9# PHP Version: 4.1.2
10# Database : `moodle`
11# --------------------------------------------------------
12
dafa318b 13#
14# Table structure for table `config`
15#
16
9fa49e22 17CREATE TABLE `prefix_config` (
dafa318b 18 `id` int(10) unsigned NOT NULL auto_increment,
19 `name` varchar(255) NOT NULL default '',
ba39fa10 20 `value` text NOT NULL default '',
dafa318b 21 PRIMARY KEY (`id`),
22 UNIQUE KEY `name` (`name`)
23) TYPE=MyISAM COMMENT='Moodle configuration variables';
24# --------------------------------------------------------
25
a13e99bd 26#
27# Table structure for table `config_plugins`
28#
29
30CREATE TABLE `prefix_config_plugins` (
31 `id` int(10) unsigned NOT NULL auto_increment,
ee708245 32 `plugin` varchar(100) NOT NULL default 'core',
33 `name` varchar(100) NOT NULL default '',
a13e99bd 34 `value` text NOT NULL default '',
35 PRIMARY KEY (`id`),
36 UNIQUE KEY `plugin_name` (`plugin`, `name`)
37) TYPE=MyISAM COMMENT='Moodle modules and plugins configuration variables';
38# --------------------------------------------------------
39
40
93777df6 41#
42# Table structure for table `course`
43#
44
9fa49e22 45CREATE TABLE `prefix_course` (
da5c172a 46 `id` int(10) unsigned NOT NULL auto_increment,
47 `category` int(10) unsigned NOT NULL default '0',
d2b6ba70 48 `sortorder` int(10) unsigned NOT NULL default '0',
da5c172a 49 `password` varchar(50) NOT NULL default '',
50 `fullname` varchar(254) NOT NULL default '',
51 `shortname` varchar(15) NOT NULL default '',
e89ff6c6 52 `idnumber` varchar(100) NOT NULL default '',
5d7a2722 53 `summary` text NOT NULL default '',
50b5487c 54 `format` varchar(10) NOT NULL default 'topics',
fff79722 55 `showgrades` smallint(2) unsigned NOT NULL default '1',
0afb2f2f 56 `modinfo` longtext NOT NULL default '',
b5fe4c93 57 `newsitems` smallint(5) unsigned NOT NULL default '1',
da5c172a 58 `teacher` varchar(100) NOT NULL default 'Teacher',
b4d7002e 59 `teachers` varchar(100) NOT NULL default 'Teachers',
da5c172a 60 `student` varchar(100) NOT NULL default 'Student',
b4d7002e 61 `students` varchar(100) NOT NULL default 'Students',
bd2ad32f 62 `guest` tinyint(2) unsigned NOT NULL default '0',
da5c172a 63 `startdate` int(10) unsigned NOT NULL default '0',
18763fd4 64 `enrolperiod` int(10) unsigned NOT NULL default '0',
e3f0a6a7 65 `numsections` smallint(5) unsigned NOT NULL default '1',
2f21e988 66 `marker` int(10) unsigned NOT NULL default '0',
4909e176 67 `maxbytes` int(10) unsigned NOT NULL default '0',
3f125001 68 `showreports` int(4) unsigned NOT NULL default '0',
009cc726 69 `visible` int(1) unsigned NOT NULL default '1',
464fd803 70 `hiddensections` int(2) unsigned NOT NULL default '0',
f374fb10 71 `groupmode` int(4) unsigned NOT NULL default '0',
72 `groupmodeforce` int(4) unsigned NOT NULL default '0',
37aaf074 73 `lang` varchar(10) NOT NULL default '',
915602ea 74 `theme` varchar(50) NOT NULL default '',
8c5c6133 75 `cost` varchar(10) NOT NULL default '',
cd9f55be 76 `currency` char(3) NOT NULL default 'USD',
f13b00d3 77 `timecreated` int(10) unsigned NOT NULL default '0',
da5c172a 78 `timemodified` int(10) unsigned NOT NULL default '0',
5f37b628 79 `metacourse` int(1) unsigned NOT NULL default '0',
0705ff84 80 `requested` int(1) unsigned NOT NULL default '0',
81 `restrictmodules` int(1) unsigned NOT NULL default '0',
7246c2d1 82 `expirynotify` tinyint(1) unsigned NOT NULL default '0',
83 `expirythreshold` int(10) unsigned NOT NULL default '0',
84 `notifystudents` tinyint(1) unsigned NOT NULL default '0',
f89033b1 85 `enrollable` tinyint(1) unsigned NOT NULL default '1',
86 `enrolstartdate` int(10) unsigned NOT NULL default '0',
87 `enrolenddate` int(10) unsigned NOT NULL default '0',
f9667a5a 88 `enrol` varchar(20) NOT NULL default '',
3052e775 89 PRIMARY KEY (`id`),
17f3e7d0 90 KEY `category` (`category`),
91 KEY `idnumber` (`idnumber`),
92 KEY `shortname` (`shortname`)
93777df6 93) TYPE=MyISAM;
94# --------------------------------------------------------
95
96#
97# Table structure for table `course_categories`
98#
99
9fa49e22 100CREATE TABLE `prefix_course_categories` (
b5fe4c93 101 `id` int(10) unsigned NOT NULL auto_increment,
102 `name` varchar(255) NOT NULL default '',
0afb2f2f 103 `description` text NOT NULL default '',
c2cb4545 104 `parent` int(10) unsigned NOT NULL default '0',
105 `sortorder` int(10) unsigned NOT NULL default '0',
9936fe81 106 `coursecount` int(10) unsigned NOT NULL default '0',
c2cb4545 107 `visible` tinyint(1) NOT NULL default '1',
108 `timemodified` int(10) unsigned NOT NULL default '0',
f41ef63e 109 `depth` int(10) unsigned NOT NULL default '0',
110 `path` varchar(255) NOT NULL default '',
b5fe4c93 111 PRIMARY KEY (`id`),
112 UNIQUE KEY `id` (`id`)
93777df6 113) TYPE=MyISAM COMMENT='Course categories';
114# --------------------------------------------------------
115
b86fc0e2 116
117#
118# Table structure for table `course_display`
119#
120
121CREATE TABLE `prefix_course_display` (
122 `id` int(10) unsigned NOT NULL auto_increment,
123 `course` int(10) unsigned NOT NULL default '0',
124 `userid` int(10) unsigned NOT NULL default '0',
125 `display` int(10) NOT NULL default '0',
126 PRIMARY KEY (`id`),
127 UNIQUE KEY `id` (`id`),
128 KEY `courseuserid` (course,userid)
129) TYPE=MyISAM COMMENT='Stores info about how to display the course';
130# --------------------------------------------------------
131
132
ceff9307 133#
134# Table structure for table `course_meta`
135#
136
137CREATE TABLE `prefix_course_meta` (
138 `id` int(10) unsigned NOT NULL auto_increment,
139 `parent_course` int(10) NOT NULL default 0,
140 `child_course` int(10) NOT NULL default 0,
141 PRIMARY KEY (`id`),
142 KEY `parent_course` (parent_course),
143 KEY `child_course` (child_course)
144);
145# --------------------------------------------------------
146
147
93777df6 148#
149# Table structure for table `course_modules`
150#
151
9fa49e22 152CREATE TABLE `prefix_course_modules` (
b5fe4c93 153 `id` int(10) unsigned NOT NULL auto_increment,
154 `course` int(10) unsigned NOT NULL default '0',
155 `module` int(10) unsigned NOT NULL default '0',
156 `instance` int(10) unsigned NOT NULL default '0',
157 `section` int(10) unsigned NOT NULL default '0',
158 `added` int(10) unsigned NOT NULL default '0',
b5fe4c93 159 `score` tinyint(4) NOT NULL default '0',
aac94fd0 160 `indent` int(5) unsigned NOT NULL default '0',
1acfbce5 161 `visible` tinyint(1) NOT NULL default '1',
ddc7afad 162 `groupmode` tinyint(4) NOT NULL default '0',
b5fe4c93 163 PRIMARY KEY (`id`),
17f3e7d0 164 UNIQUE KEY `id` (`id`),
165 KEY `visible` (`visible`),
166 KEY `course` (`course`),
167 KEY `module` (`module`),
594ceedd 168 KEY `instance` (`instance`)
93777df6 169) TYPE=MyISAM;
170# --------------------------------------------------------
171
172#
b5fe4c93 173# Table structure for table `course_sections`
93777df6 174#
175
9fa49e22 176CREATE TABLE `prefix_course_sections` (
b5fe4c93 177 `id` int(10) unsigned NOT NULL auto_increment,
178 `course` int(10) unsigned NOT NULL default '0',
179 `section` int(10) unsigned NOT NULL default '0',
98332898 180 `summary` text NOT NULL default '',
dd0bd508 181 `sequence` text NOT NULL default '',
7d99d695 182 `visible` tinyint(1) NOT NULL default '1',
f374fb10 183 PRIMARY KEY (`id`),
184 KEY `coursesection` (course,section)
93777df6 185) TYPE=MyISAM;
186# --------------------------------------------------------
187
0705ff84 188#
189# Table structure for table `course_request`
190#
191
192CREATE TABLE `prefix_course_request` (
193 `id` int(10) unsigned NOT NULL auto_increment,
194 `fullname` varchar(254) NOT NULL default '',
195 `shortname` varchar(15) NOT NULL default '',
98332898 196 `summary` text NOT NULL default '',
197 `reason` text NOT NULL default '',
0705ff84 198 `requester` int(10) NOT NULL default 0,
e19db110 199 `password` varchar(50) NOT NULL default '',
0705ff84 200 PRIMARY KEY (`id`),
201 KEY `shortname` (`shortname`)
202) TYPE=MyISAM;
203# ---------------------------------------------------------
204
205#
206# Table structure for table `coursre_allowed_modules`
207#
208
209CREATE TABLE `prefix_course_allowed_modules` (
210 `id` int(10) unsigned NOT NULL auto_increment,
211 `course` int(10) unsigned NOT NULL default 0,
212 `module` int(10) unsigned NOT NULL default 0,
213 PRIMARY KEY (`id`),
214 KEY `course` (`course`),
215 KEY `module` (`module`)
216) TYPE=MyISAM;
217
218------------------------------------------------------------
219
5fba04fb 220#
221# Table structure for table `event`
222#
223
224CREATE TABLE `prefix_event` (
225 `id` int(10) unsigned NOT NULL auto_increment,
226 `name` varchar(255) NOT NULL default '',
0afb2f2f 227 `description` text NOT NULL default '',
cda21d48 228 `format` int(4) unsigned NOT NULL default '0',
5fba04fb 229 `courseid` int(10) unsigned NOT NULL default '0',
230 `groupid` int(10) unsigned NOT NULL default '0',
231 `userid` int(10) unsigned NOT NULL default '0',
8180c859 232 `repeatid` int(10) unsigned NOT NULL default '0',
5fba04fb 233 `modulename` varchar(20) NOT NULL default '',
234 `instance` int(10) unsigned NOT NULL default '0',
235 `eventtype` varchar(20) NOT NULL default '',
236 `timestart` int(10) unsigned NOT NULL default '0',
237 `timeduration` int(10) unsigned NOT NULL default '0',
dcd338ff 238 `visible` tinyint(4) NOT NULL default '1',
98f8f9c2 239 `uuid` char(36) NOT NULL default '',
240 `sequence` int(10) unsigned NOT NULL default '1',
5fba04fb 241 `timemodified` int(10) unsigned NOT NULL default '0',
242 PRIMARY KEY (`id`),
243 UNIQUE KEY `id` (`id`),
244 KEY `courseid` (`courseid`),
17f3e7d0 245 KEY `userid` (`userid`),
246 KEY `timestart` (`timestart`),
247 KEY `timeduration` (`timeduration`)
5fba04fb 248) TYPE=MyISAM COMMENT='For everything with a time associated to it';
d363047e 249# --------------------------------------------------------
250
f1d604cb 251#
252# Table structure for table `cache_filters`
253#
254
255CREATE TABLE `prefix_cache_filters` (
256 `id` int(10) unsigned NOT NULL auto_increment,
257 `filter` varchar(32) NOT NULL default '',
258 `version` int(10) unsigned NOT NULL default '0',
259 `md5key` varchar(32) NOT NULL default '',
98332898 260 `rawtext` text NOT NULL default '',
f1d604cb 261 `timemodified` int(10) unsigned NOT NULL default '0',
262 PRIMARY KEY (`id`),
263 KEY `filtermd5key` (filter,md5key)
264) TYPE=MyISAM COMMENT='For keeping information about cached data';
265# --------------------------------------------------------
266
267
d363047e 268#
45121ffb 269# Table structure for table `cache_text`
d363047e 270#
271
45121ffb 272CREATE TABLE `prefix_cache_text` (
d363047e 273 `id` int(10) unsigned NOT NULL auto_increment,
274 `md5key` varchar(32) NOT NULL default '',
71470b3b 275 `formattedtext` longblob NOT NULL default '',
d363047e 276 `timemodified` int(10) unsigned NOT NULL default '0',
277 PRIMARY KEY (`id`),
278 KEY `md5key` (`md5key`)
279) TYPE=MyISAM COMMENT='For storing temporary copies of processed texts';
280# --------------------------------------------------------
281
282
4eb25d43 283#
284# Table structure for table `grade_category`
285#
286
287CREATE TABLE `prefix_grade_category` (
288 `id` int(10) unsigned NOT NULL auto_increment,
289 `name` varchar(64) default NULL,
290 `courseid` int(10) unsigned NOT NULL default '0',
291 `drop_x_lowest` int(10) unsigned NOT NULL default '0',
292 `bonus_points` int(10) unsigned NOT NULL default '0',
293 `hidden` int(10) unsigned NOT NULL default '0',
53f6a21e 294 `weight` decimal(5,2) default '0.00',
4eb25d43 295 PRIMARY KEY (`id`),
296 KEY `courseid` (`courseid`)
297) TYPE=MyISAM ;
298
299# --------------------------------------------------------
300
301#
302# Table structure for table `grade_exceptions`
303#
304
305CREATE TABLE `prefix_grade_exceptions` (
306 `id` int(10) unsigned NOT NULL auto_increment,
307 `courseid` int(10) unsigned NOT NULL default '0',
308 `grade_itemid` int(10) unsigned NOT NULL default '0',
309 `userid` int(10) unsigned NOT NULL default '0',
310 PRIMARY KEY (`id`),
311 KEY `courseid` (`courseid`)
312) TYPE=MyISAM ;
313
314# --------------------------------------------------------
315
316#
317# Table structure for table `grade_item`
318#
319
320CREATE TABLE `prefix_grade_item` (
321 `id` int(10) unsigned NOT NULL auto_increment,
322 `courseid` int(10) unsigned default NULL,
323 `category` int(10) unsigned default NULL,
324 `modid` int(10) unsigned default NULL,
325 `cminstance` int(10) unsigned default NULL,
326 `scale_grade` float(11,10) default '1.0000000000',
327 `extra_credit` int(10) unsigned NOT NULL default '0',
328 `sort_order` int(10) unsigned NOT NULL default '0',
329 PRIMARY KEY (`id`),
330 KEY `courseid` (`courseid`)
331) TYPE=MyISAM ;
332
333# --------------------------------------------------------
334
335#
336# Table structure for table `grade_letter`
337#
338
339CREATE TABLE `prefix_grade_letter` (
340 `id` int(10) unsigned NOT NULL auto_increment,
341 `courseid` int(10) unsigned NOT NULL default '0',
342 `letter` varchar(8) NOT NULL default 'NA',
343 `grade_high` decimal(4,2) NOT NULL default '100.00',
344 `grade_low` decimal(4,2) NOT NULL default '0.00',
345 PRIMARY KEY (`id`),
346 KEY `courseid` (`courseid`)
347) TYPE=MyISAM ;
348
349# --------------------------------------------------------
350
351#
352# Table structure for table `grade_preferences`
353#
354
355CREATE TABLE `prefix_grade_preferences` (
356 `id` int(10) unsigned NOT NULL auto_increment,
357 `courseid` int(10) unsigned default NULL,
358 `preference` int(10) NOT NULL default '0',
359 `value` int(10) NOT NULL default '0',
360 PRIMARY KEY (`id`),
361 UNIQUE KEY `courseidpreference` (`courseid`,`preference`)
362) TYPE=MyISAM;
363
364# --------------------------------------------------------
5fba04fb 365
f374fb10 366#
367# Table structure for table `group`
368#
369
0da33e07 370CREATE TABLE `prefix_groups` (
f374fb10 371 `id` int(10) unsigned NOT NULL auto_increment,
372 `courseid` int(10) unsigned NOT NULL default '0',
373 `name` varchar(254) NOT NULL default '',
0afb2f2f 374 `description` text NOT NULL default '',
1a5bc046 375 `password` varchar(50) NOT NULL default '',
f374fb10 376 `lang` varchar(10) NOT NULL default 'en',
915602ea 377 `theme` varchar(50) NOT NULL default '',
f374fb10 378 `picture` int(10) unsigned NOT NULL default '0',
3c0561cf 379 `hidepicture` int(2) unsigned NOT NULL default '0',
f374fb10 380 `timecreated` int(10) unsigned NOT NULL default '0',
381 `timemodified` int(10) unsigned NOT NULL default '0',
382 PRIMARY KEY (`id`),
5fba04fb 383 UNIQUE KEY `id` (`id`),
f374fb10 384 KEY `courseid` (`courseid`)
385) TYPE=MyISAM COMMENT='Each record is a group in a course.';
386# --------------------------------------------------------
387
388#
389# Table structure for table `group_members`
390#
391
0da33e07 392CREATE TABLE `prefix_groups_members` (
f374fb10 393 `id` int(10) unsigned NOT NULL auto_increment,
394 `groupid` int(10) unsigned NOT NULL default '0',
395 `userid` int(10) unsigned NOT NULL default '0',
396 `timeadded` int(10) unsigned NOT NULL default '0',
397 PRIMARY KEY (`id`),
5fba04fb 398 UNIQUE KEY `id` (`id`),
17f3e7d0 399 KEY `groupid` (`groupid`),
400 KEY `userid` (`userid`)
f374fb10 401) TYPE=MyISAM COMMENT='Lists memberships of users to groups';
402# --------------------------------------------------------
403
404
93777df6 405#
406# Table structure for table `log`
407#
408
9fa49e22 409CREATE TABLE `prefix_log` (
b5fe4c93 410 `id` int(10) unsigned NOT NULL auto_increment,
411 `time` int(10) unsigned NOT NULL default '0',
ebc3bd2b 412 `userid` int(10) unsigned NOT NULL default '0',
b5fe4c93 413 `ip` varchar(15) NOT NULL default '',
414 `course` int(10) unsigned NOT NULL default '0',
78345312 415 `module` varchar(20) NOT NULL default '',
69d79bc3 416 `cmid` int(10) unsigned NOT NULL default '0',
b5fe4c93 417 `action` varchar(15) NOT NULL default '',
418 `url` varchar(100) NOT NULL default '',
419 `info` varchar(255) NOT NULL default '',
264d4e0b 420 PRIMARY KEY (`id`),
a8fa25d1 421 KEY `timecoursemoduleaction` (time,course,module,action),
f9ce68ee 422 KEY `coursemoduleaction` (course,module,action),
65070e3c 423 KEY `courseuserid` (course,userid),
424 KEY `userid` (userid),
425 KEY `info` (info)
93777df6 426) TYPE=MyISAM COMMENT='Every action is logged as far as possible.';
427# --------------------------------------------------------
428
429#
430# Table structure for table `log_display`
431#
432
9fa49e22 433CREATE TABLE `prefix_log_display` (
65adacc4 434 `module` varchar(20) NOT NULL default '',
435 `action` varchar(20) NOT NULL default '',
565f7a95 436 `mtable` varchar(20) NOT NULL default '',
65adacc4 437 `field` varchar(40) NOT NULL default ''
565f7a95 438) TYPE=MyISAM COMMENT='For a particular module/action, specifies a moodle table/field.';
93777df6 439# --------------------------------------------------------
440
ffce79c0 441#
442# Table structure for table `message`
443#
444
445CREATE TABLE `prefix_message` (
446 `id` int(10) unsigned NOT NULL auto_increment,
447 `useridfrom` int(10) NOT NULL default '0',
448 `useridto` int(10) NOT NULL default '0',
98332898 449 `message` text NOT NULL default '',
e53c030b 450 `format` int(4) unsigned NOT NULL default '0',
0eacb23c 451 `timecreated` int(10) NOT NULL default '0',
ffce79c0 452 `messagetype` varchar(50) NOT NULL default '',
453 PRIMARY KEY (`id`),
454 KEY `useridfrom` (`useridfrom`),
455 KEY `useridto` (`useridto`)
456) TYPE=MyISAM COMMENT='Stores all unread messages';
457# --------------------------------------------------------
458
459#
460# Table structure for table `message_read`
461#
462
463CREATE TABLE `prefix_message_read` (
464 `id` int(10) unsigned NOT NULL auto_increment,
465 `useridfrom` int(10) NOT NULL default '0',
466 `useridto` int(10) NOT NULL default '0',
98332898 467 `message` text NOT NULL default '',
e53c030b 468 `format` int(4) unsigned NOT NULL default '0',
0eacb23c 469 `timecreated` int(10) NOT NULL default '0',
470 `timeread` int(10) NOT NULL default '0',
ffce79c0 471 `messagetype` varchar(50) NOT NULL default '',
472 `mailed` tinyint(1) NOT NULL default '0',
473 PRIMARY KEY (`id`),
474 KEY `useridfrom` (`useridfrom`),
475 KEY `useridto` (`useridto`)
476) TYPE=MyISAM COMMENT='Stores all messages that have been read';
477# --------------------------------------------------------
478
0eacb23c 479#
480# Table structure for table `message_contacts`
481#
482
483CREATE TABLE `prefix_message_contacts` (
484 `id` int(10) unsigned NOT NULL auto_increment,
485 `userid` int(10) unsigned NOT NULL default '0',
486 `contactid` int(10) unsigned NOT NULL default '0',
487 `blocked` tinyint(1) unsigned NOT NULL default '0',
488 PRIMARY KEY (`id`),
489 UNIQUE KEY `usercontact` (`userid`,`contactid`)
490) TYPE=MyISAM COMMENT='Maintains lists of relationships between users';
491# --------------------------------------------------------
492
93777df6 493#
494# Table structure for table `modules`
495#
496
9fa49e22 497CREATE TABLE `prefix_modules` (
b5fe4c93 498 `id` int(10) unsigned NOT NULL auto_increment,
499 `name` varchar(20) NOT NULL default '',
b5fe4c93 500 `version` int(10) NOT NULL default '0',
501 `cron` int(10) unsigned NOT NULL default '0',
502 `lastcron` int(10) unsigned NOT NULL default '0',
503 `search` varchar(255) NOT NULL default '',
7e6b0b3b 504 `visible` tinyint(1) NOT NULL default '1',
b5fe4c93 505 PRIMARY KEY (`id`),
17f3e7d0 506 UNIQUE KEY `id` (`id`),
507 KEY `name` (`name`)
93777df6 508) TYPE=MyISAM;
509# --------------------------------------------------------
510
37eef3ed 511
512#
513# Table structure for table `scale`
514#
515
516CREATE TABLE `prefix_scale` (
517 `id` int(10) unsigned NOT NULL auto_increment,
518 `courseid` int(10) unsigned NOT NULL default '0',
519 `userid` int(10) unsigned NOT NULL default '0',
520 `name` varchar(255) NOT NULL default '',
98332898 521 `scale` text NOT NULL default '',
0afb2f2f 522 `description` text NOT NULL default '',
37eef3ed 523 `timemodified` int(10) unsigned NOT NULL default '0',
17f3e7d0 524 PRIMARY KEY (id),
525 KEY `courseid` (`courseid`)
37eef3ed 526) TYPE=MyISAM COMMENT='Defines grading scales';
527# --------------------------------------------------------
528
529
42b90599 530#
531# Table structure for table `sessions`
532#
533
534CREATE TABLE `prefix_sessions` (
98332898 535 `sesskey` char(32) NOT null default '',
536 `expiry` int(11) unsigned NOT null default '0',
537 `expireref` varchar(64) default '',
538 `data` text NOT null default '',
42b90599 539 PRIMARY KEY (`sesskey`),
540 KEY (`expiry`)
541) TYPE=MyISAM COMMENT='Optional database session storage, not used by default';
542# --------------------------------------------------------
543
b41c4f7e 544
545#
546# Table structure for table `timezone`
547#
548
549CREATE TABLE `prefix_timezone` (
550 `id` int(10) NOT NULL auto_increment,
551 `name` varchar(100) NOT NULL default '',
552 `year` int(11) NOT NULL default '0',
553 `rule` varchar(20) NOT NULL default '',
554 `gmtoff` int(11) NOT NULL default '0',
555 `dstoff` int(11) NOT NULL default '0',
556 `dst_month` tinyint(2) NOT NULL default '0',
557 `dst_startday` tinyint(3) NOT NULL default '0',
558 `dst_weekday` tinyint(3) NOT NULL default '0',
559 `dst_skipweeks` tinyint(3) NOT NULL default '0',
560 `dst_time` varchar(5) NOT NULL default '00:00',
561 `std_month` tinyint(2) NOT NULL default '0',
562 `std_startday` tinyint(3) NOT NULL default '0',
563 `std_weekday` tinyint(3) NOT NULL default '0',
564 `std_skipweeks` tinyint(3) NOT NULL default '0',
565 `std_time` varchar(5) NOT NULL default '00:00',
566 PRIMARY KEY (`id`)
567) TYPE=MyISAM COMMENT='Rules for calculating local wall clock time for users';
568
569
93777df6 570#
571# Table structure for table `user`
572#
1c66bf59 573# When changing prefix_user, you may need to update
574# truncate_userinfo() in moodlelib.php
575#
9fa49e22 576CREATE TABLE `prefix_user` (
b5fe4c93 577 `id` int(10) unsigned NOT NULL auto_increment,
4e11ad4f 578 `auth` varchar(20) NOT NULL default 'manual',
b5fe4c93 579 `confirmed` tinyint(1) NOT NULL default '0',
027a1604 580 `policyagreed` tinyint(1) NOT NULL default '0',
01a086c3 581 `deleted` tinyint(1) NOT NULL default '0',
b5fe4c93 582 `username` varchar(100) NOT NULL default '',
583 `password` varchar(32) NOT NULL default '',
d35757eb 584 `idnumber` varchar(64) default NULL,
b5fe4c93 585 `firstname` varchar(20) NOT NULL default '',
586 `lastname` varchar(20) NOT NULL default '',
587 `email` varchar(100) NOT NULL default '',
8199e3f0 588 `emailstop` tinyint(1) unsigned NOT NULL default '0',
b5fe4c93 589 `icq` varchar(15) default NULL,
766d2bf3 590 `skype` varchar(50) default NULL,
591 `yahoo` varchar(50) default NULL,
592 `aim` varchar(50) default NULL,
593 `msn` varchar(50) default NULL,
b5fe4c93 594 `phone1` varchar(20) default NULL,
595 `phone2` varchar(20) default NULL,
596 `institution` varchar(40) default NULL,
597 `department` varchar(30) default NULL,
598 `address` varchar(70) default NULL,
599 `city` varchar(20) default NULL,
600 `country` char(2) default NULL,
37aaf074 601 `lang` varchar(10) default 'en',
915602ea 602 `theme` varchar(50) NOT NULL default '',
68c0264e 603 `timezone` varchar(100) NOT NULL default '99',
b5fe4c93 604 `firstaccess` int(10) unsigned NOT NULL default '0',
605 `lastaccess` int(10) unsigned NOT NULL default '0',
606 `lastlogin` int(10) unsigned NOT NULL default '0',
607 `currentlogin` int(10) unsigned NOT NULL default '0',
608 `lastIP` varchar(15) default NULL,
a789fb73 609 `secret` varchar(15) default NULL,
b5fe4c93 610 `picture` tinyint(1) default NULL,
611 `url` varchar(255) default NULL,
27998b9e 612 `description` text NOT NULL default '',
b5fe4c93 613 `mailformat` tinyint(1) unsigned NOT NULL default '1',
cc21211e 614 `maildigest` tinyint(1) unsigned NOT NULL default '0',
bd2ad32f 615 `maildisplay` tinyint(2) unsigned NOT NULL default '2',
0095d5cd 616 `htmleditor` tinyint(1) unsigned NOT NULL default '1',
7f2a3e67 617 `autosubscribe` tinyint(1) unsigned NOT NULL default '1',
9e33ce2c 618 `trackforums` tinyint(1) unsigned NOT NULL default '0',
b5fe4c93 619 `timemodified` int(10) unsigned NOT NULL default '0',
620 PRIMARY KEY (`id`),
621 UNIQUE KEY `id` (`id`),
ffa5f71b 622 UNIQUE KEY `username` (`username`),
623 KEY `user_deleted` (`deleted`),
624 KEY `user_confirmed` (`confirmed`),
625 KEY `user_firstname` (`firstname`),
626 KEY `user_lastname` (`lastname`),
627 KEY `user_city` (`city`),
628 KEY `user_country` (`country`),
629 KEY `user_lastaccess` (`lastaccess`),
630 KEY `user_email` (`email`)
93777df6 631) TYPE=MyISAM COMMENT='One record for each person';
01e2ea5f 632
d35757eb 633ALTER TABLE `prefix_user` ADD INDEX `auth` (`auth`);
634ALTER TABLE `prefix_user` ADD INDEX `idnumber` (`idnumber`);
93777df6 635# --------------------------------------------------------
636
637#
638# Table structure for table `user_admins`
639#
640
9fa49e22 641CREATE TABLE `prefix_user_admins` (
da5c172a 642 `id` int(10) unsigned NOT NULL auto_increment,
ebc3bd2b 643 `userid` int(10) unsigned NOT NULL default '0',
da5c172a 644 PRIMARY KEY (`id`),
17f3e7d0 645 UNIQUE KEY `id` (`id`),
646 KEY `userid` (`userid`)
93777df6 647) TYPE=MyISAM COMMENT='One record per administrator user';
648# --------------------------------------------------------
649
70812e39 650
651
652#
653# Table structure for table `user_preferences`
654#
655
656CREATE TABLE `prefix_user_preferences` (
657 `id` int(10) unsigned NOT NULL auto_increment,
658 `userid` int(10) unsigned NOT NULL default '0',
659 `name` varchar(50) NOT NULL default '',
660 `value` varchar(255) NOT NULL default '',
661 PRIMARY KEY (`id`),
662 UNIQUE KEY `id` (`id`),
663 KEY `useridname` (userid,name)
664) TYPE=MyISAM COMMENT='Allows modules to store arbitrary user preferences';
665# --------------------------------------------------------
666
667
668
93777df6 669#
670# Table structure for table `user_students`
671#
672
9fa49e22 673CREATE TABLE `prefix_user_students` (
b5fe4c93 674 `id` int(10) unsigned NOT NULL auto_increment,
ebc3bd2b 675 `userid` int(10) unsigned NOT NULL default '0',
b5fe4c93 676 `course` int(10) unsigned NOT NULL default '0',
ebc3bd2b 677 `timestart` int(10) unsigned NOT NULL default '0',
678 `timeend` int(10) unsigned NOT NULL default '0',
b5fe4c93 679 `time` int(10) unsigned NOT NULL default '0',
4d744a22 680 `timeaccess` int(10) unsigned NOT NULL default '0',
5cd79686 681 `enrol` varchar(20) NOT NULL default '',
b5fe4c93 682 PRIMARY KEY (`id`),
a8fa25d1 683 UNIQUE KEY `id` (`id`),
b110348b 684 UNIQUE KEY `courseuserid` (course,userid),
17f3e7d0 685 KEY `userid` (userid),
5cd79686 686 KEY `enrol` (enrol),
17f3e7d0 687 KEY `timeaccess` (timeaccess)
93777df6 688) TYPE=MyISAM;
689# --------------------------------------------------------
690
691#
692# Table structure for table `user_teachers`
693#
694
9fa49e22 695CREATE TABLE `prefix_user_teachers` (
b5fe4c93 696 `id` int(10) unsigned NOT NULL auto_increment,
ebc3bd2b 697 `userid` int(10) unsigned NOT NULL default '0',
b5fe4c93 698 `course` int(10) unsigned NOT NULL default '0',
b4d7002e 699 `authority` int(10) NOT NULL default '3',
700 `role` varchar(40) NOT NULL default '',
73047f2f 701 `editall` int(1) unsigned NOT NULL default '1',
faef9f7b 702 `timestart` int(10) unsigned NOT NULL default '0',
703 `timeend` int(10) unsigned NOT NULL default '0',
73047f2f 704 `timemodified` int(10) unsigned NOT NULL default '0',
4d744a22 705 `timeaccess` int(10) unsigned NOT NULL default '0',
13706b52 706 `enrol` varchar(20) NOT NULL default '',
b5fe4c93 707 PRIMARY KEY (`id`),
a8fa25d1 708 UNIQUE KEY `id` (`id`),
f3c9e7f9 709 UNIQUE KEY `courseuserid` (course,userid),
90ccfd19 710 KEY `userid` (userid),
711 KEY `enrol` (enrol)
93777df6 712) TYPE=MyISAM COMMENT='One record per teacher per course';
713
1924074c 714#
715# Table structure for table `user_admins`
716#
717
718CREATE TABLE `prefix_user_coursecreators` (
719 `id` int(10) unsigned NOT NULL auto_increment,
720 `userid` int(10) unsigned NOT NULL default '0',
721 PRIMARY KEY (`id`),
17f3e7d0 722 UNIQUE KEY `id` (`id`),
723 KEY `userid` (`userid`)
1924074c 724) TYPE=MyISAM COMMENT='One record per course creator';
1924074c 725
b61efafb 726
ceff9307 727#
728# For debugging puposes, see admin/dbperformance.php
729#
730
731CREATE TABLE `adodb_logsql` (
98332898 732 `created` datetime NOT NULL default '0000-00-00 00:00:00',
733 `sql0` varchar(250) NOT NULL default '',
734 `sql1` text NOT NULL default '',
735 `params` text NOT NULL default '',
736 `tracer` text NOT NULL default '',
737 `timer` decimal(16,6) NOT NULL default '0'
b61efafb 738);
739
f3221af9 740CREATE TABLE `prefix_stats_daily` (
741 `id` int(10) unsigned NOT NULL auto_increment,
742 `courseid` int(10) unsigned NOT NULL default 0,
743 `timeend` int(10) unsigned NOT NULL default 0,
744 `students` int(10) unsigned NOT NULL default 0,
745 `teachers` int(10) unsigned NOT NULL default 0,
746 `activestudents` int(10) unsigned NOT NULL default 0,
747 `activeteachers` int(10) unsigned NOT NULL default 0,
748 `studentreads` int(10) unsigned NOT NULL default 0,
749 `studentwrites` int(10) unsigned NOT NULL default 0,
750 `teacherreads` int(10) unsigned NOT NULL default 0,
751 `teacherwrites` int(10) unsigned NOT NULL default 0,
752 `logins` int(10) unsigned NOT NULL default 0,
753 `uniquelogins` int(10) unsigned NOT NULL default 0,
754 PRIMARY KEY (`id`),
755 KEY `courseid` (`courseid`),
98332898 756 KEY `timeend` (`timeend`)
f3221af9 757);
758
759CREATE TABLE prefix_stats_weekly (
760 `id` int(10) unsigned NOT NULL auto_increment,
761 `courseid` int(10) unsigned NOT NULL default 0,
762 `timeend` int(10) unsigned NOT NULL default 0,
763 `students` int(10) unsigned NOT NULL default 0,
764 `teachers` int(10) unsigned NOT NULL default 0,
765 `activestudents` int(10) unsigned NOT NULL default 0,
766 `activeteachers` int(10) unsigned NOT NULL default 0,
767 `studentreads` int(10) unsigned NOT NULL default 0,
768 `studentwrites` int(10) unsigned NOT NULL default 0,
769 `teacherreads` int(10) unsigned NOT NULL default 0,
770 `teacherwrites` int(10) unsigned NOT NULL default 0,
771 `logins` int(10) unsigned NOT NULL default 0,
772 `uniquelogins` int(10) unsigned NOT NULL default 0,
773 PRIMARY KEY (`id`),
774 KEY `courseid` (`courseid`),
775 KEY `timeend` (`timeend`)
776);
777
778CREATE TABLE prefix_stats_monthly (
779 `id` int(10) unsigned NOT NULL auto_increment,
780 `courseid` int(10) unsigned NOT NULL default 0,
781 `timeend` int(10) unsigned NOT NULL default 0,
782 `students` int(10) unsigned NOT NULL default 0,
783 `teachers` int(10) unsigned NOT NULL default 0,
784 `activestudents` int(10) unsigned NOT NULL default 0,
785 `activeteachers` int(10) unsigned NOT NULL default 0,
786 `studentreads` int(10) unsigned NOT NULL default 0,
787 `studentwrites` int(10) unsigned NOT NULL default 0,
788 `teacherreads` int(10) unsigned NOT NULL default 0,
789 `teacherwrites` int(10) unsigned NOT NULL default 0,
790 `logins` int(10) unsigned NOT NULL default 0,
791 `uniquelogins` int(10) unsigned NOT NULL default 0,
792 PRIMARY KEY (`id`),
793 KEY `courseid` (`courseid`),
794 KEY `timeend` (`timeend`)
795);
796
797CREATE TABLE prefix_stats_user_daily (
798 `id` int(10) unsigned NOT NULL auto_increment,
799 `courseid` int(10) unsigned NOT NULL default 0,
800 `userid` int(10) unsigned NOT NULL default 0,
801 `roleid` int(10) unsigned NOT NULL default 0,
802 `timeend` int(10) unsigned NOT NULL default 0,
803 `reads` int(10) unsigned NOT NULL default 0,
804 `writes` int(10) unsigned NOT NULL default 0,
805 `stattype` varchar(30) NOT NULL default '',
806 PRIMARY KEY (`id`),
807 KEY `courseid` (`courseid`),
808 KEY `userid` (`userid`),
809 KEY `roleid` (`roleid`),
810 KEY `timeend` (`timeend`)
811);
812
813CREATE TABLE prefix_stats_user_weekly (
814 `id` int(10) unsigned NOT NULL auto_increment,
815 `courseid` int(10) unsigned NOT NULL default 0,
816 `userid` int(10) unsigned NOT NULL default 0,
817 `roleid` int(10) unsigned NOT NULL default 0,
818 `timeend` int(10) unsigned NOT NULL default 0,
819 `reads` int(10) unsigned NOT NULL default 0,
820 `writes` int(10) unsigned NOT NULL default 0,
821 `stattype` varchar(30) NOT NULL default '',
822 PRIMARY KEY (`id`),
823 KEY `courseid` (`courseid`),
824 KEY `userid` (`userid`),
825 KEY `roleid` (`roleid`),
826 KEY `timeend` (`timeend`)
827);
828
829CREATE TABLE prefix_stats_user_monthly (
830 `id` int(10) unsigned NOT NULL auto_increment,
831 `courseid` int(10) unsigned NOT NULL default 0,
832 `userid` int(10) unsigned NOT NULL default 0,
833 `roleid` int(10) unsigned NOT NULL default 0,
834 `timeend` int(10) unsigned NOT NULL default 0,
835 `reads` int(10) unsigned NOT NULL default 0,
836 `writes` int(10) unsigned NOT NULL default 0,
837 `stattype` varchar(30) NOT NULL default '',
838 PRIMARY KEY (`id`),
839 KEY `courseid` (`courseid`),
840 KEY `userid` (`userid`),
841 KEY `roleid` (`roleid`),
842 KEY `timeend` (`timeend`)
843);
844
89d38fdd 845#
112d0ebf 846# Table structure for table `prefix_post`
89d38fdd 847#
848CREATE TABLE prefix_post (
dffaf682 849 `id` int(10) NOT NULL auto_increment,
112d0ebf 850 `module` varchar(20) NOT NULL default '',
dffaf682 851 `userid` int(10) NOT NULL default '0',
852 `courseid` int(10) NOT NULL default '0',
853 `groupid` int(10) NOT NULL default '0',
854 `moduleid` int(10) NOT NULL default '0',
855 `coursemoduleid` int(10) NOT NULL default '0',
89d38fdd 856 `subject` varchar(128) NOT NULL default '',
857 `summary` longtext,
858 `content` longtext,
859 `uniquehash` varchar(128) NOT NULL default '',
dffaf682 860 `rating` int(10) NOT NULL default '0',
861 `format` int(10) NOT NULL default '0',
89d38fdd 862 `publishstate` enum('draft','site','public') NOT NULL default 'draft',
863 `lastmodified` int(10) NOT NULL default '0',
864 `created` int(10) NOT NULL default '0',
865 PRIMARY KEY (`id`),
866 UNIQUE KEY `id_user_idx` (`id`, `userid`),
867 KEY `post_lastmodified_idx` (`lastmodified`),
112d0ebf 868 KEY `post_module_idx` (`module`),
89d38fdd 869 KEY `post_subject_idx` (`subject`)
112d0ebf 870) TYPE=MyISAM COMMENT='Generic post table to hold data blog entries etc in different modules.';
871
89d38fdd 872
873# tags are not limited to blogs
874CREATE TABLE prefix_tags (
dffaf682 875 `id` int(10) NOT NULL auto_increment,
89d38fdd 876 `type` varchar(255) NOT NULL default 'official',
dffaf682 877 `userid` int(10) NOT NULL default '0',
89d38fdd 878 `text` varchar(255) NOT NULL default '',
879 PRIMARY KEY (`id`)
880) TYPE=MyISAM COMMENT ='tags structure for moodle.';
881
882# instance of a tag for a blog
883CREATE TABLE prefix_blog_tag_instance (
dffaf682 884 `id` int(10) NOT NULL auto_increment,
885 `entryid` int(10) NOT NULL default '0',
886 `tagid` int(10) NOT NULL default '0',
887 `groupid` int(10) NOT NULL default '0',
888 `courseid` int(10) NOT NULL default '0',
889 `userid` int(10) NOT NULL default '0',
6a30c073 890 `timemodified` int(10) unsigned NOT NULL default '0',
891 KEY `bti_entryid_idx` (`entryid`),
892 KEY `bti_tagid_idx` (`tagid`),
89d38fdd 893 PRIMARY KEY (`id`)
894) TYPE=MyISAM COMMENT ='tag instance for blogs.';
895
f3221af9 896
db70b54b 897INSERT INTO prefix_log_display VALUES ('user', 'view', 'user', 'CONCAT(firstname," ",lastname)');
d0117715 898INSERT INTO prefix_log_display VALUES ('course', 'user report', 'user', 'CONCAT(firstname," ",lastname)');
14e4a496 899INSERT INTO prefix_log_display VALUES ('course', 'view', 'course', 'fullname');
900INSERT INTO prefix_log_display VALUES ('course', 'update', 'course', 'fullname');
db70b54b 901INSERT INTO prefix_log_display VALUES ('course', 'enrol', 'course', 'fullname');
0eacb23c 902INSERT INTO prefix_log_display VALUES ('message', 'write', 'user', 'CONCAT(firstname," ",lastname)');
903INSERT INTO prefix_log_display VALUES ('message', 'read', 'user', 'CONCAT(firstname," ",lastname)');
36f01e7b 904INSERT INTO prefix_log_display VALUES ('message', 'add contact', 'user', 'CONCAT(firstname," ",lastname)');
905INSERT INTO prefix_log_display VALUES ('message', 'remove contact', 'user', 'CONCAT(firstname," ",lastname)');
906INSERT INTO prefix_log_display VALUES ('message', 'block contact', 'user', 'CONCAT(firstname," ",lastname)');
907INSERT INTO prefix_log_display VALUES ('message', 'unblock contact', 'user', 'CONCAT(firstname," ",lastname)');