Updated to use new logging format.
[moodle.git] / lib / db / mysql.sql
CommitLineData
6fa8326d 1# Database : `moodle`\r
2# --------------------------------------------------------\r
3\r
4#\r
5# Table structure for table `course`\r
6#\r
7\r
8CREATE TABLE course (\r
9 id int(10) unsigned NOT NULL auto_increment,\r
10 category int(10) unsigned NOT NULL default '0',\r
11 password varchar(50) NOT NULL default '',\r
12 fullname varchar(254) NOT NULL default '',\r
13 shortname varchar(15) NOT NULL default '',\r
14 summary text NOT NULL,\r
15 format tinyint(4) NOT NULL default '1',\r
16 teacher varchar(100) NOT NULL default 'Teacher',\r
17 startdate int(10) unsigned NOT NULL default '0',\r
18 enddate int(10) unsigned NOT NULL default '0',\r
19 timemodified int(10) unsigned NOT NULL default '0',\r
20 PRIMARY KEY (id)\r
21);\r
22# --------------------------------------------------------\r
23\r
24#\r
25# Table structure for table `course_categories`\r
26#\r
27\r
28CREATE TABLE course_categories (\r
29 id int(10) unsigned NOT NULL auto_increment,\r
30 name varchar(255) NOT NULL default '',\r
31 PRIMARY KEY (id),\r
32 UNIQUE KEY id (id)\r
33);\r
34# --------------------------------------------------------\r
35\r
36#\r
37# Table structure for table `course_modules`\r
38#\r
39\r
40CREATE TABLE course_modules (\r
41 id int(10) unsigned NOT NULL auto_increment,\r
42 course int(10) unsigned NOT NULL default '0',\r
43 module int(10) unsigned NOT NULL default '0',\r
44 instance int(10) unsigned NOT NULL default '0',\r
45 week int(10) unsigned NOT NULL default '0',\r
46 added int(10) unsigned NOT NULL default '0',\r
47 deleted tinyint(1) unsigned NOT NULL default '0',\r
48 score tinyint(4) NOT NULL default '0',\r
49 PRIMARY KEY (id),\r
50 UNIQUE KEY id (id)\r
51) ;\r
52# --------------------------------------------------------\r
53\r
54#\r
55# Table structure for table `course_weeks`\r
56#\r
57\r
58CREATE TABLE course_weeks (\r
59 id int(10) unsigned NOT NULL auto_increment,\r
60 course int(10) unsigned NOT NULL default '0',\r
61 week int(10) unsigned NOT NULL default '0',\r
62 summary varchar(255) NOT NULL default '',\r
63 sequence varchar(255) NOT NULL default '',\r
64 PRIMARY KEY (id)\r
65) ;\r
66# --------------------------------------------------------\r
67\r
68#\r
69# Table structure for table `logs`\r
70#\r
71\r
72CREATE TABLE logs (\r
73 id int(10) unsigned NOT NULL auto_increment,\r
74 time int(10) unsigned NOT NULL default '0',\r
75 user int(10) unsigned NOT NULL default '0',\r
76 course int(10) unsigned NOT NULL default '0',\r
77 ip varchar(15) NOT NULL default '',\r
78 url varchar(200) NOT NULL default '',\r
79 message varchar(255) NOT NULL default '',\r
80 PRIMARY KEY (id)\r
81) ;\r
82# --------------------------------------------------------\r
83\r
84#\r
85# Table structure for table `modules`\r
86#\r
87\r
88CREATE TABLE modules (\r
89 id int(10) unsigned NOT NULL auto_increment,\r
90 name varchar(20) NOT NULL default '',\r
91 fullname varchar(255) NOT NULL default '',\r
92 version int(10) NOT NULL default '0',\r
93 cron int(10) unsigned NOT NULL default '0',\r
94 lastcron int(10) unsigned NOT NULL default '0',\r
95 search varchar(255) NOT NULL default '',\r
96 PRIMARY KEY (id),\r
97 UNIQUE KEY id (id)\r
98) ;\r
99# --------------------------------------------------------\r
100\r
101#\r
102# Table structure for table `user`\r
103#\r
104\r
105CREATE TABLE user (\r
106 id int(10) unsigned NOT NULL auto_increment,\r
107 confirmed tinyint(1) NOT NULL default '0',\r
108 username varchar(100) NOT NULL default '',\r
109 password varchar(32) NOT NULL default '',\r
110 idnumber varchar(12) default NULL,\r
111 firstname varchar(20) NOT NULL default '',\r
112 lastname varchar(20) NOT NULL default '',\r
113 email varchar(100) NOT NULL default '',\r
114 icq varchar(15) default NULL,\r
115 phone1 varchar(20) default NULL,\r
116 phone2 varchar(20) default NULL,\r
117 institution varchar(40) default NULL,\r
118 department varchar(30) default NULL,\r
119 address varchar(70) default NULL,\r
120 city varchar(20) default NULL,\r
121 country char(2) default NULL,\r
122 firstaccess int(10) unsigned NOT NULL default '0',\r
123 lastaccess int(10) unsigned NOT NULL default '0',\r
124 lastlogin int(10) unsigned NOT NULL default '0',\r
125 currentlogin int(10) unsigned NOT NULL default '0',\r
126 lastIP varchar(15) default NULL,\r
127 personality varchar(5) default NULL,\r
128 picture tinyint(1) default NULL,\r
129 url varchar(255) default NULL,\r
130 description text,\r
131 research tinyint(1) unsigned NOT NULL default '0',\r
132 forwardmail tinyint(1) unsigned NOT NULL default '0',\r
133 timemodified int(10) unsigned NOT NULL default '0',\r
134 PRIMARY KEY (id),\r
135 UNIQUE KEY username (username),\r
136 UNIQUE KEY id (id)\r
137) ;\r
138# --------------------------------------------------------\r
139\r
140#\r
141# Table structure for table `user_admins`\r
142#\r
143\r
144CREATE TABLE user_admins (\r
145 id int(10) unsigned NOT NULL auto_increment,\r
146 user int(10) unsigned NOT NULL default '0',\r
147 PRIMARY KEY (id),\r
148 UNIQUE KEY id (id)\r
149) ;\r
150# --------------------------------------------------------\r
151\r
152#\r
153# Table structure for table `user_students`\r
154#\r
155\r
156CREATE TABLE user_students (\r
157 id int(10) unsigned NOT NULL auto_increment,\r
158 user int(10) unsigned NOT NULL default '0',\r
159 course int(10) unsigned NOT NULL default '0',\r
160 start int(10) unsigned NOT NULL default '0',\r
161 end int(10) unsigned NOT NULL default '0',\r
162 time int(10) unsigned NOT NULL default '0',\r
163 PRIMARY KEY (id),\r
164 UNIQUE KEY id (id)\r
165) ;\r
166# --------------------------------------------------------\r
167\r
168#\r
169# Table structure for table `user_teachers`\r
170#\r
171\r
172CREATE TABLE user_teachers (\r
173 id int(10) unsigned NOT NULL auto_increment,\r
174 user int(10) unsigned NOT NULL default '0',\r
175 course int(10) unsigned NOT NULL default '0',\r
176 authority varchar(10) default NULL,\r
177 PRIMARY KEY (id),\r
178 UNIQUE KEY id (id)\r
179) ;\r
180\r