Some blank index.html files to prevent browsing
[moodle.git] / lib / db / postgres7.sql
CommitLineData
a3eea09c 1CREATE TABLE prefix_config (
fd790dfb 2 id SERIAL PRIMARY KEY,
3 name varchar(255) NOT NULL default '',
ba39fa10 4 value text NOT NULL default '',
fd790dfb 5 CONSTRAINT config_name_uk UNIQUE (name)
6);
7
a3eea09c 8CREATE TABLE prefix_course (
fd790dfb 9 id SERIAL PRIMARY KEY,
10 category integer NOT NULL default '0',
d2b6ba70 11 sortorder integer NOT NULL default '0',
fd790dfb 12 password varchar(50) NOT NULL default '',
13 fullname varchar(254) NOT NULL default '',
14 shortname varchar(15) NOT NULL default '',
15 summary text NOT NULL default '',
50b5487c 16 format varchar(10) NOT NULL default 'topics',
fff79722 17 showgrades integer NOT NULL default '1',
fd790dfb 18 modinfo text NOT NULL default '',
19 newsitems integer NOT NULL default '1',
20 teacher varchar(100) NOT NULL default 'Teacher',
21 teachers varchar(100) NOT NULL default 'Teachers',
22 student varchar(100) NOT NULL default 'Student',
23 students varchar(100) NOT NULL default 'Students',
24 guest integer NOT NULL default '0',
25 startdate integer NOT NULL default '0',
26 numsections integer NOT NULL default '1',
e71f132d 27 showrecent integer NOT NULL default '1',
fd790dfb 28 marker integer NOT NULL default '0',
4909e176 29 maxbytes integer NOT NULL default '0',
3f125001 30 showreports integer NOT NULL default '0',
a6d82c3f 31 visible integer NOT NULL default '1',
f374fb10 32 groupmode integer NOT NULL default '0',
33 groupmodeforce integer NOT NULL default '0',
b3153e4b 34 lang varchar(5) NOT NULL default '',
fd790dfb 35 timecreated integer NOT NULL default '0',
36 timemodified integer NOT NULL default '0'
37);
38
3052e775 39CREATE INDEX prefix_course_category_idx ON prefix_course (category);
40
a3eea09c 41CREATE TABLE prefix_course_categories (
fd790dfb 42 id SERIAL PRIMARY KEY,
577cf899 43 name varchar(255) NOT NULL default '',
c2cb4545 44 description text NOT NULL default '',
45 parent integer NOT NULL default '0',
46 sortorder integer NOT NULL default '0',
9936fe81 47 coursecount integer NOT NULL default '0',
c2cb4545 48 visible integer NOT NULL default '1',
577cf899 49 timemodified integer NOT NULL default '0'
fd790dfb 50);
51
b86fc0e2 52CREATE TABLE prefix_course_display (
53 id SERIAL PRIMARY KEY,
54 course integer NOT NULL default '0',
55 userid integer NOT NULL default '0',
56 display integer NOT NULL default '0'
57);
58
ea563f8f 59CREATE INDEX prefix_course_display_courseuserid_idx ON prefix_course_display (course,userid);
b86fc0e2 60
a3eea09c 61CREATE TABLE prefix_course_modules (
fd790dfb 62 id SERIAL PRIMARY KEY,
63 course integer NOT NULL default '0',
64 module integer NOT NULL default '0',
65 instance integer NOT NULL default '0',
66 section integer NOT NULL default '0',
67 added integer NOT NULL default '0',
68 deleted integer NOT NULL default '0',
1acfbce5 69 score integer NOT NULL default '0',
aac94fd0 70 indent integer NOT NULL default '0',
ddc7afad 71 visible integer NOT NULL default '1',
72 groupmode integer NOT NULL default '0'
fd790dfb 73);
74
a3eea09c 75CREATE TABLE prefix_course_sections (
fd790dfb 76 id SERIAL PRIMARY KEY,
77 course integer NOT NULL default '0',
78 section integer NOT NULL default '0',
79 summary text NOT NULL default '',
dd0bd508 80 sequence text NOT NULL default '',
7d99d695 81 visible integer NOT NULL default '1'
fd790dfb 82);
83
5fba04fb 84CREATE TABLE prefix_event (
85 id SERIAL PRIMARY KEY,
86 name varchar(255) NOT NULL default '',
87 description text,
88 courseid integer NOT NULL default '0',
89 groupid integer NOT NULL default '0',
90 userid integer NOT NULL default '0',
91 modulename varchar(20) NOT NULL default '',
92 instance integer NOT NULL default '0',
93 eventtype varchar(20) NOT NULL default '',
94 timestart integer NOT NULL default '0',
95 timeduration integer NOT NULL default '0',
96 timemodified integer NOT NULL default '0'
97);
98
99CREATE INDEX prefix_event_courseid_idx ON prefix_event (courseid);
100CREATE INDEX prefix_event_userid_idx ON prefix_event (userid);
101
0da33e07 102CREATE TABLE prefix_groups (
f374fb10 103 id SERIAL PRIMARY KEY,
104 courseid integer NOT NULL default '0',
105 name varchar(255) NOT NULL default '',
106 description text,
107 lang varchar(10) NOT NULL default '',
108 picture integer NOT NULL default '0',
3c0561cf 109 hidepicture integer NOT NULL default '0',
f374fb10 110 timecreated integer NOT NULL default '0',
111 timemodified integer NOT NULL default '0'
112);
113
0da33e07 114CREATE INDEX prefix_groups_idx ON prefix_groups (courseid);
f374fb10 115
0da33e07 116CREATE TABLE prefix_groups_members (
f374fb10 117 id SERIAL PRIMARY KEY,
118 groupid integer NOT NULL default '0',
119 userid integer NOT NULL default '0',
120 timeadded integer NOT NULL default '0'
121);
122
0da33e07 123CREATE INDEX prefix_groups_members_idx ON prefix_groups_members (groupid);
f374fb10 124
a3eea09c 125CREATE TABLE prefix_log (
fd790dfb 126 id SERIAL PRIMARY KEY,
127 time integer NOT NULL default '0',
ebc3bd2b 128 userid integer NOT NULL default '0',
fd790dfb 129 ip varchar(15) NOT NULL default '',
130 course integer NOT NULL default '0',
8af3e379 131 module varchar(20) NOT NULL default '',
69d79bc3 132 cmid integer NOT NULL default '0',
8af3e379 133 action varchar(20) NOT NULL default '',
fd790dfb 134 url varchar(100) NOT NULL default '',
135 info varchar(255) NOT NULL default ''
136);
137
ea563f8f 138CREATE INDEX prefix_log_coursemoduleaction_idx ON prefix_log (course,module,action);
a8fa25d1 139CREATE INDEX prefix_log_timecoursemoduleaction_idx ON prefix_log (time,course,module,action);
ea563f8f 140CREATE INDEX prefix_log_courseuserid_idx ON prefix_log (course,userid);
e209e0b0 141
a3eea09c 142CREATE TABLE prefix_log_display (
fd790dfb 143 module varchar(20) NOT NULL default '',
144 action varchar(20) NOT NULL default '',
145 mtable varchar(20) NOT NULL default '',
146 field varchar(40) NOT NULL default ''
147);
148
a3eea09c 149CREATE TABLE prefix_modules (
fd790dfb 150 id SERIAL PRIMARY KEY,
151 name varchar(20) NOT NULL default '',
152 version integer NOT NULL default '0',
153 cron integer NOT NULL default '0',
154 lastcron integer NOT NULL default '0',
7e6b0b3b 155 search varchar(255) NOT NULL default '',
156 visible integer NOT NULL default '1'
fd790dfb 157);
158
a142b3b4 159CREATE TABLE prefix_scale (
160 id SERIAL PRIMARY KEY,
161 courseid integer NOT NULL default '0',
162 userid integer NOT NULL default '0',
163 name varchar(255) NOT NULL default '',
164 scale text,
165 description text,
166 timemodified integer NOT NULL default '0'
167);
168
f1d604cb 169
170CREATE TABLE prefix_cache_filters (
171 id SERIAL PRIMARY KEY,
172 filter varchar(32) NOT NULL default '',
173 version integer NOT NULL default '0',
174 md5key varchar(32) NOT NULL default '',
175 rawtext text,
176 timemodified integer NOT NULL default '0'
177);
178
179CREATE INDEX prefix_cache_filters_filtermd5key_idx ON prefix_cache_filters (filter,md5key);
180
181
45121ffb 182CREATE TABLE prefix_cache_text (
d363047e 183 id SERIAL PRIMARY KEY,
184 md5key varchar(32) NOT NULL default '',
185 formattedtext text,
186 timemodified integer NOT NULL default '0'
187);
188
f1d604cb 189CREATE INDEX prefix_cache_text_md5key_idx ON prefix_cache_text (md5key);
190
191
a3eea09c 192CREATE TABLE prefix_user (
fd790dfb 193 id SERIAL PRIMARY KEY,
194 confirmed integer NOT NULL default '0',
195 deleted integer NOT NULL default '0',
196 username varchar(100) NOT NULL default '',
197 password varchar(32) NOT NULL default '',
198 idnumber varchar(12) default NULL,
199 firstname varchar(20) NOT NULL default '',
200 lastname varchar(20) NOT NULL default '',
201 email varchar(100) NOT NULL default '',
8199e3f0 202 emailstop integer NOT NULL default '0',
fd790dfb 203 icq varchar(15) default NULL,
204 phone1 varchar(20) default NULL,
205 phone2 varchar(20) default NULL,
206 institution varchar(40) default NULL,
207 department varchar(30) default NULL,
208 address varchar(70) default NULL,
209 city varchar(20) default NULL,
210 country char(2) default NULL,
465fd00e 211 lang varchar(5) NOT NULL default '',
fd790dfb 212 timezone float NOT NULL default '99',
213 firstaccess integer NOT NULL default '0',
214 lastaccess integer NOT NULL default '0',
215 lastlogin integer NOT NULL default '0',
216 currentlogin integer NOT NULL default '0',
217 lastIP varchar(15) default NULL,
218 secret varchar(15) default NULL,
219 picture integer default NULL,
220 url varchar(255) default NULL,
221 description text,
222 mailformat integer NOT NULL default '1',
223 maildisplay integer NOT NULL default '2',
7f2a3e67 224 htmleditor integer NOT NULL default '1',
225 autosubscribe integer NOT NULL default '1',
fd790dfb 226 timemodified integer NOT NULL default '0',
227 CONSTRAINT user_username_uk UNIQUE (username)
228);
229
a3eea09c 230CREATE TABLE prefix_user_admins (
fd790dfb 231 id SERIAL PRIMARY KEY,
ebc3bd2b 232 userid integer NOT NULL default '0'
fd790dfb 233);
234
70812e39 235CREATE TABLE prefix_user_preferences (
236 id SERIAL PRIMARY KEY,
237 userid integer NOT NULL default '0',
238 name varchar(50) NOT NULL default '',
239 value varchar(255) NOT NULL default ''
240);
241
242CREATE INDEX prefix_user_preferences_useridname_idx ON prefix_user_preferences (userid,name);
243
a3eea09c 244CREATE TABLE prefix_user_students (
fd790dfb 245 id SERIAL PRIMARY KEY,
ebc3bd2b 246 userid integer NOT NULL default '0',
fd790dfb 247 course integer NOT NULL default '0',
ebc3bd2b 248 timestart integer NOT NULL default '0',
249 timeend integer NOT NULL default '0',
4d744a22 250 time integer NOT NULL default '0',
251 timeaccess integer NOT NULL default '0'
fd790dfb 252);
253
a8fa25d1 254CREATE INDEX prefix_user_students_courseuserid_idx ON prefix_user_students (course,userid);
255
a3eea09c 256CREATE TABLE prefix_user_teachers (
fd790dfb 257 id SERIAL PRIMARY KEY,
ebc3bd2b 258 userid integer NOT NULL default '0',
fd790dfb 259 course integer NOT NULL default '0',
260 authority integer NOT NULL default '3',
73047f2f 261 role varchar(40) NOT NULL default '',
262 editall integer NOT NULL default '1',
4d744a22 263 timemodified integer NOT NULL default '0',
264 timeaccess integer NOT NULL default '0'
fd790dfb 265);
a3eea09c 266
a8fa25d1 267CREATE INDEX prefix_user_teachers_courseuserid_idx ON prefix_user_teachers (course,userid);
268
269CREATE TABLE prefix_user_coursecreators (
1924074c 270 id SERIAL8 PRIMARY KEY,
271 userid int8 NOT NULL default '0'
272);
a3eea09c 273
274INSERT INTO prefix_log_display VALUES ('user', 'view', 'user', 'CONCAT(firstname," ",lastname)');
d0117715 275INSERT INTO prefix_log_display VALUES ('course', 'user report', 'user', 'CONCAT(firstname," ",lastname)');
db70b54b 276INSERT INTO prefix_log_display VALUES ('course', 'view', 'course', 'fullname');
277INSERT INTO prefix_log_display VALUES ('course', 'update', 'course', 'fullname');
278INSERT INTO prefix_log_display VALUES ('course', 'enrol', 'course', 'fullname');
279INSERT INTO prefix_log_display VALUES ('course', 'update', 'course', 'fullname');