?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
let's see if i can break something. :o
[phpbb.git] / phpBB / develop / create_schema_files.php
blob1261d4f57e3b38f8f9541bd747bc2bdfccab36db
1 <?php
2 /**
3 *
4 * @package phpBB3
5 * @version $Id$
6 * @copyright (c) 2006 phpBB Group
7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
8 *
9 * This file creates new schema files for every database.
10 * The filenames will be prefixed with an underscore to not overwrite the current schema files.
11 *
12 * If you overwrite the original schema files please make sure you save the file with UNIX linefeeds.
13 */
14
15 die("Please read the first lines of this script for instructions on how to enable it");
16
17 set_time_limit(0);
18
19 $schema_path = './../install/schemas/';
20
21 if (!is_writable($schema_path))
22 {
23 die('Schema path not writable');
24 }
25
26 $schema_data = get_schema_struct();
27 $dbms_type_map = array(
28 'mysql_41' => array(
29 'INT:' => 'int(%d)',
30 'BINT' => 'bigint(20)',
31 'UINT' => 'mediumint(8) UNSIGNED',
32 'UINT:' => 'int(%d) UNSIGNED',
33 'TINT:' => 'tinyint(%d)',
34 'USINT' => 'smallint(4) UNSIGNED',
35 'BOOL' => 'tinyint(1) UNSIGNED',
36 'VCHAR' => 'varchar(255)',
37 'VCHAR:' => 'varchar(%d)',
38 'CHAR:' => 'char(%d)',
39 'XSTEXT' => 'text',
40 'XSTEXT_UNI'=> 'varchar(100)',
41 'STEXT' => 'text',
42 'STEXT_UNI' => 'varchar(255)',
43 'TEXT' => 'text',
44 'TEXT_UNI' => 'text',
45 'MTEXT' => 'mediumtext',
46 'MTEXT_UNI' => 'mediumtext',
47 'TIMESTAMP' => 'int(11) UNSIGNED',
48 'DECIMAL' => 'decimal(5,2)',
49 'VCHAR_UNI' => 'varchar(255)',
50 'VCHAR_UNI:'=> 'varchar(%d)',
51 'VCHAR_CI' => 'varchar(255)',
52 'VARBINARY' => 'varbinary(255)',
53 ),
54
55 'mysql_40' => array(
56 'INT:' => 'int(%d)',
57 'BINT' => 'bigint(20)',
58 'UINT' => 'mediumint(8) UNSIGNED',
59 'UINT:' => 'int(%d) UNSIGNED',
60 'TINT:' => 'tinyint(%d)',
61 'USINT' => 'smallint(4) UNSIGNED',
62 'BOOL' => 'tinyint(1) UNSIGNED',
63 'VCHAR' => 'varbinary(255)',
64 'VCHAR:' => 'varbinary(%d)',
65 'CHAR:' => 'binary(%d)',
66 'XSTEXT' => 'blob',
67 'XSTEXT_UNI'=> 'blob',
68 'STEXT' => 'blob',
69 'STEXT_UNI' => 'blob',
70 'TEXT' => 'blob',
71 'TEXT_UNI' => 'blob',
72 'MTEXT' => 'mediumblob',
73 'MTEXT_UNI' => 'mediumblob',
74 'TIMESTAMP' => 'int(11) UNSIGNED',
75 'DECIMAL' => 'decimal(5,2)',
76 'VCHAR_UNI' => 'blob',
77 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
78 'VCHAR_CI' => 'blob',
79 'VARBINARY' => 'varbinary(255)',
80 ),
81
82 'firebird' => array(
83 'INT:' => 'INTEGER',
84 'BINT' => 'DOUBLE PRECISION',
85 'UINT' => 'INTEGER',
86 'UINT:' => 'INTEGER',
87 'TINT:' => 'INTEGER',
88 'USINT' => 'INTEGER',
89 'BOOL' => 'INTEGER',
90 'VCHAR' => 'VARCHAR(255) CHARACTER SET NONE',
91 'VCHAR:' => 'VARCHAR(%d) CHARACTER SET NONE',
92 'CHAR:' => 'CHAR(%d) CHARACTER SET NONE',
93 'XSTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
94 'STEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
95 'TEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
96 'MTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
97 'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
98 'STEXT_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
99 'TEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
100 'MTEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
101 'TIMESTAMP' => 'INTEGER',
102 'DECIMAL' => 'DOUBLE PRECISION',
103 'VCHAR_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
104 'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
105 'VCHAR_CI' => 'VARCHAR(255) CHARACTER SET UTF8',
106 'VARBINARY' => 'CHAR(255) CHARACTER SET NONE',
107 ),
108
109 'mssql' => array(
110 'INT:' => '[int]',
111 'BINT' => '[float]',
112 'UINT' => '[int]',
113 'UINT:' => '[int]',
114 'TINT:' => '[int]',
115 'USINT' => '[int]',
116 'BOOL' => '[int]',
117 'VCHAR' => '[varchar] (255)',
118 'VCHAR:' => '[varchar] (%d)',
119 'CHAR:' => '[char] (%d)',
120 'XSTEXT' => '[varchar] (1000)',
121 'STEXT' => '[varchar] (3000)',
122 'TEXT' => '[varchar] (8000)',
123 'MTEXT' => '[text]',
124 'XSTEXT_UNI'=> '[varchar] (100)',
125 'STEXT_UNI' => '[varchar] (255)',
126 'TEXT_UNI' => '[varchar] (4000)',
127 'MTEXT_UNI' => '[text]',
128 'TIMESTAMP' => '[int]',
129 'DECIMAL' => '[float]',
130 'VCHAR_UNI' => '[varchar] (255)',
131 'VCHAR_UNI:'=> '[varchar] (%d)',
132 'VCHAR_CI' => '[varchar] (255)',
133 'VARBINARY' => '[varchar] (255)',
134 ),
135
136 'oracle' => array(
137 'INT:' => 'number(%d)',
138 'BINT' => 'number(20)',
139 'UINT' => 'number(8)',
140 'UINT:' => 'number(%d)',
141 'TINT:' => 'number(%d)',
142 'USINT' => 'number(4)',
143 'BOOL' => 'number(1)',
144 'VCHAR' => 'varchar2(255)',
145 'VCHAR:' => 'varchar2(%d)',
146 'CHAR:' => 'char(%d)',
147 'XSTEXT' => 'varchar2(1000)',
148 'STEXT' => 'varchar2(3000)',
149 'TEXT' => 'clob',
150 'MTEXT' => 'clob',
151 'XSTEXT_UNI'=> 'varchar2(300)',
152 'STEXT_UNI' => 'varchar2(765)',
153 'TEXT_UNI' => 'clob',
154 'MTEXT_UNI' => 'clob',
155 'TIMESTAMP' => 'number(11)',
156 'DECIMAL' => 'number(5, 2)',
157 'VCHAR_UNI' => 'varchar2(765)',
158 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
159 'VCHAR_CI' => 'varchar2(255)',
160 'VARBINARY' => 'raw(255)',
161 ),
162
163 'sqlite' => array(
164 'INT:' => 'int(%d)',
165 'BINT' => 'bigint(20)',
166 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
167 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
168 'TINT:' => 'tinyint(%d)',
169 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
170 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
171 'VCHAR' => 'varchar(255)',
172 'VCHAR:' => 'varchar(%d)',
173 'CHAR:' => 'char(%d)',
174 'XSTEXT' => 'text(65535)',
175 'STEXT' => 'text(65535)',
176 'TEXT' => 'text(65535)',
177 'MTEXT' => 'mediumtext(16777215)',
178 'XSTEXT_UNI'=> 'text(65535)',
179 'STEXT_UNI' => 'text(65535)',
180 'TEXT_UNI' => 'text(65535)',
181 'MTEXT_UNI' => 'mediumtext(16777215)',
182 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
183 'DECIMAL' => 'decimal(5,2)',
184 'VCHAR_UNI' => 'varchar(255)',
185 'VCHAR_UNI:'=> 'varchar(%d)',
186 'VCHAR_CI' => 'varchar(255)',
187 'VARBINARY' => 'blob',
188 ),
189
190 'postgres' => array(
191 'INT:' => 'INT4',
192 'BINT' => 'INT8',
193 'UINT' => 'INT4', // unsigned
194 'UINT:' => 'INT4', // unsigned
195 'USINT' => 'INT2', // unsigned
196 'BOOL' => 'INT2', // unsigned
197 'TINT:' => 'INT2',
198 'VCHAR' => 'varchar(255)',
199 'VCHAR:' => 'varchar(%d)',
200 'CHAR:' => 'char(%d)',
201 'XSTEXT' => 'varchar(1000)',
202 'STEXT' => 'varchar(3000)',
203 'TEXT' => 'varchar(8000)',
204 'MTEXT' => 'TEXT',
205 'XSTEXT_UNI'=> 'varchar(100)',
206 'STEXT_UNI' => 'varchar(255)',
207 'TEXT_UNI' => 'varchar(4000)',
208 'MTEXT_UNI' => 'TEXT',
209 'TIMESTAMP' => 'INT4', // unsigned
210 'DECIMAL' => 'decimal(5,2)',
211 'VCHAR_UNI' => 'varchar(255)',
212 'VCHAR_UNI:'=> 'varchar(%d)',
213 'VCHAR_CI' => 'varchar_ci',
214 'VARBINARY' => 'bytea',
215 ),
216 );
217
218 // A list of types being unsigned for better reference in some db's
219 $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
220 $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
221
222 foreach ($supported_dbms as $dbms)
223 {
224 $fp = fopen($schema_path . '_' . $dbms . '_schema.sql', 'wt');
225
226 $line = '';
227
228 // Write Header
229 switch ($dbms)
230 {
231 case 'mysql_40':
232 case 'mysql_41':
233 $line = "#\n# \$I" . "d: $\n#\n\n";
234 break;
235
236 case 'firebird':
237 $line = "#\n# \$I" . "d: $\n#\n\n";
238 $line .= custom_data('firebird') . "\n";
239 break;
240
241 case 'sqlite':
242 $line = "#\n# \$I" . "d: $\n#\n\n";
243 $line .= "BEGIN TRANSACTION;\n\n";
244 break;
245
246 case 'mssql':
247 $line = "/*\n\n \$I" . "d: $\n\n*/\n\n";
248 $line .= "BEGIN TRANSACTION\nGO\n\n";
249 break;
250
251 case 'oracle':
252 $line = "/*\n\n \$I" . "d: $\n\n*/\n\n";
253 $line .= custom_data('oracle') . "\n";
254 break;
255
256 case 'postgres':
257 $line = "/*\n\n \$I" . "d: $\n\n*/\n\n";
258 $line .= "BEGIN;\n\n";
259 $line .= custom_data('postgres') . "\n";
260 break;
261 }
262
263 fwrite($fp, $line);
264
265 foreach ($schema_data as $table_name => $table_data)
266 {
267 // Write comment about table
268 switch ($dbms)
269 {
270 case 'mysql_40':
271 case 'mysql_41':
272 case 'firebird':
273 case 'sqlite':
274 fwrite($fp, "# Table: '{$table_name}'\n");
275 break;
276
277 case 'mssql':
278 case 'oracle':
279 case 'postgres':
280 fwrite($fp, "/*\n\tTable: '{$table_name}'\n*/\n");
281 break;
282 }
283
284 // Create Table statement
285 $generator = $textimage = false;
286 $line = '';
287
288 switch ($dbms)
289 {
290 case 'mysql_40':
291 case 'mysql_41':
292 case 'firebird':
293 case 'oracle':
294 case 'sqlite':
295 case 'postgres':
296 $line = "CREATE TABLE {$table_name} (\n";
297 break;
298
299 case 'mssql':
300 $line = "CREATE TABLE [{$table_name}] (\n";
301 break;
302 }
303
304 // Table specific so we don't get overlap
305 $modded_array = array();
306
307 // Write columns one by one...
308 foreach ($table_data['COLUMNS'] as $column_name => $column_data)
309 {
310 // Get type
311 if (strpos($column_data[0], ':') !== false)
312 {
313 list($orig_column_type, $column_length) = explode(':', $column_data[0]);
314 if (!is_array($dbms_type_map[$dbms][$orig_column_type . ':']))
315 {
316 $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'], $column_length);
317 }
318 else
319 {
320 if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['rule']))
321 {
322 switch ($dbms_type_map[$dbms][$orig_column_type . ':']['rule'][0])
323 {
324 case 'div':
325 $column_length /= $dbms_type_map[$dbms][$orig_column_type . ':']['rule'][1];
326 $column_length = ceil($column_length);
327 $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
328 break;
329 }
330 }
331
332 if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['limit']))
333 {
334 switch ($dbms_type_map[$dbms][$orig_column_type . ':']['limit'][0])
335 {
336 case 'mult':
337 $column_length *= $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][1];
338 if ($column_length > $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][2])
339 {
340 $column_type = $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][3];
341 $modded_array[$column_name] = $column_type;
342 }
343 else
344 {
345 $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
346 }
347 break;
348 }
349 }
350 }
351 $orig_column_type .= ':';
352 }
353 else
354 {
355 $orig_column_type = $column_data[0];
356 $column_type = $dbms_type_map[$dbms][$column_data[0]];
357 if ($column_type == 'text' || $column_type == 'blob')
358 {
359 $modded_array[$column_name] = $column_type;
360 }
361 }
362
363 // Adjust default value if db-dependant specified
364 if (is_array($column_data[1]))
365 {
366 $column_data[1] = (isset($column_data[1][$dbms])) ? $column_data[1][$dbms] : $column_data[1]['default'];
367 }
368
369 switch ($dbms)
370 {
371 case 'mysql_40':
372 case 'mysql_41':
373 $line .= "\t{$column_name} {$column_type} ";
374
375 // For hexadecimal values do not use single quotes
376 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
377 {
378 $line .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
379 }
380 $line .= 'NOT NULL';
381
382 if (isset($column_data[2]))
383 {
384 if ($column_data[2] == 'auto_increment')
385 {
386 $line .= ' auto_increment';
387 }
388 else if ($dbms === 'mysql_41' && $column_data[2] == 'true_sort')
389 {
390 $line .= ' COLLATE utf8_unicode_ci';
391 }
392 }
393
394 $line .= ",\n";
395 break;
396
397 case 'sqlite':
398 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
399 {
400 $line .= "\t{$column_name} INTEGER PRIMARY KEY ";
401 $generator = $column_name;
402 }
403 else
404 {
405 $line .= "\t{$column_name} {$column_type} ";
406 }
407
408 $line .= 'NOT NULL ';
409 $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
410 $line .= ",\n";
411 break;
412
413 case 'firebird':
414 $line .= "\t{$column_name} {$column_type} ";
415
416 if (!is_null($column_data[1]))
417 {
418 $line .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
419 }
420
421 $line .= 'NOT NULL';
422
423 // This is a UNICODE column and thus should be given it's fair share
424 if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
425 {
426 $line .= ' COLLATE UNICODE';
427 }
428
429 $line .= ",\n";
430
431 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
432 {
433 $generator = $column_name;
434 }
435 break;
436
437 case 'mssql':
438 if ($column_type == '[text]')
439 {
440 $textimage = true;
441 }
442
443 $line .= "\t[{$column_name}] {$column_type} ";
444
445 if (!is_null($column_data[1]))
446 {
447 // For hexadecimal values do not use single quotes
448 if (strpos($column_data[1], '0x') === 0)
449 {
450 $line .= 'DEFAULT (' . $column_data[1] . ') ';
451 }
452 else
453 {
454 $line .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
455 }
456 }
457
458 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
459 {
460 $line .= 'IDENTITY (1, 1) ';
461 }
462
463 $line .= 'NOT NULL';
464 $line .= " ,\n";
465 break;
466
467 case 'oracle':
468 $line .= "\t{$column_name} {$column_type} ";
469 $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
470
471 // In Oracle empty strings ('') are treated as NULL.
472 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
473 $line .= ($column_data[1] === '') ? ",\n" : "NOT NULL,\n";
474
475 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
476 {
477 $generator = $column_name;
478 }
479 break;
480
481 case 'postgres':
482 $line .= "\t{$column_name} {$column_type} ";
483
484 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
485 {
486 $line .= "DEFAULT nextval('{$table_name}_seq'),\n";
487
488 // Make sure the sequence will be created before creating the table
489 $line = "CREATE SEQUENCE {$table_name}_seq;\n\n" . $line;
490 }
491 else
492 {
493 $line .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
494 $line .= "NOT NULL";
495
496 // Unsigned? Then add a CHECK contraint
497 if (in_array($orig_column_type, $unsigned_types))
498 {
499 $line .= " CHECK ({$column_name} >= 0)";
500 }
501
502 $line .= ",\n";
503 }
504 break;
505 }
506 }
507
508 switch ($dbms)
509 {
510 case 'firebird':
511 // Remove last line delimiter...
512 $line = substr($line, 0, -2);
513 $line .= "\n);;\n\n";
514 break;
515
516 case 'mssql':
517 $line = substr($line, 0, -2);
518 $line .= "\n) ON [PRIMARY]" . (($textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '') . "\n";
519 $line .= "GO\n\n";
520 break;
521 }
522
523 // Write primary key
524 if (isset($table_data['PRIMARY_KEY']))
525 {
526 if (!is_array($table_data['PRIMARY_KEY']))
527 {
528 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
529 }
530
531 switch ($dbms)
532 {
533 case 'mysql_40':
534 case 'mysql_41':
535 case 'postgres':
536 $line .= "\tPRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . "),\n";
537 break;
538
539 case 'firebird':
540 $line .= "ALTER TABLE {$table_name} ADD PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ");;\n\n";
541 break;
542
543 case 'sqlite':
544 if ($generator === false || !in_array($generator, $table_data['PRIMARY_KEY']))
545 {
546 $line .= "\tPRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . "),\n";
547 }
548 break;
549
550 case 'mssql':
551 $line .= "ALTER TABLE [{$table_name}] WITH NOCHECK ADD \n";
552 $line .= "\tCONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED \n";
553 $line .= "\t(\n";
554 $line .= "\t\t[" . implode("],\n\t\t[", $table_data['PRIMARY_KEY']) . "]\n";
555 $line .= "\t) ON [PRIMARY] \n";
556 $line .= "GO\n\n";
557 break;
558
559 case 'oracle':
560 $line .= "\tCONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . "),\n";
561 break;
562 }
563 }
564
565 switch ($dbms)
566 {
567 case 'oracle':
568 // UNIQUE contrains to be added?
569 if (isset($table_data['KEYS']))
570 {
571 foreach ($table_data['KEYS'] as $key_name => $key_data)
572 {
573 if (!is_array($key_data[1]))
574 {
575 $key_data[1] = array($key_data[1]);
576 }
577
578 if ($key_data[0] == 'UNIQUE')
579 {
580 $line .= "\tCONSTRAINT u_phpbb_{$key_name} UNIQUE (" . implode(', ', $key_data[1]) . "),\n";
581 }
582 }
583 }
584
585 // Remove last line delimiter...
586 $line = substr($line, 0, -2);
587 $line .= "\n)\n/\n\n";
588 break;
589
590 case 'postgres':
591 // Remove last line delimiter...
592 $line = substr($line, 0, -2);
593 $line .= "\n);\n\n";
594 break;
595
596 case 'sqlite':
597 // Remove last line delimiter...
598 $line = substr($line, 0, -2);
599 $line .= "\n);\n\n";
600 break;
601 }
602
603 // Write Keys
604 if (isset($table_data['KEYS']))
605 {
606 foreach ($table_data['KEYS'] as $key_name => $key_data)
607 {
608 if (!is_array($key_data[1]))
609 {
610 $key_data[1] = array($key_data[1]);
611 }
612
613 switch ($dbms)
614 {
615 case 'mysql_40':
616 case 'mysql_41':
617 $line .= ($key_data[0] == 'INDEX') ? "\tKEY" : '';
618 $line .= ($key_data[0] == 'UNIQUE') ? "\tUNIQUE" : '';
619 foreach ($key_data[1] as $key => $col_name)
620 {
621 if (isset($modded_array[$col_name]))
622 {
623 switch ($modded_array[$col_name])
624 {
625 case 'text':
626 case 'blob':
627 $key_data[1][$key] = $col_name . '(255)';
628 break;
629 }
630 }
631 }
632 $line .= ' ' . $key_name . ' (' . implode(', ', $key_data[1]) . "),\n";
633 break;
634
635 case 'firebird':
636 $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : '';
637 $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : '';
638
639 $line .= ' ' . $table_name . '_' . $key_name . ' ON ' . $table_name . '(' . implode(', ', $key_data[1]) . ");;\n";
640 break;
641
642 case 'mssql':
643 $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : '';
644 $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : '';
645 $line .= " [{$key_name}] ON [{$table_name}]([" . implode('], [', $key_data[1]) . "]) ON [PRIMARY]\n";
646 $line .= "GO\n\n";
647 break;
648
649 case 'oracle':
650 if ($key_data[0] == 'UNIQUE')
651 {
652 continue;
653 }
654
655 $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : '';
656
657 $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ")\n";
658 $line .= "/\n";
659 break;
660
661 case 'sqlite':
662 $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : '';
663 $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : '';
664
665 $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");\n";
666 break;
667
668 case 'postgres':
669 $line .= ($key_data[0] == 'INDEX') ? 'CREATE INDEX' : '';
670 $line .= ($key_data[0] == 'UNIQUE') ? 'CREATE UNIQUE INDEX' : '';
671
672 $line .= " {$table_name}_{$key_name} ON {$table_name} (" . implode(', ', $key_data[1]) . ");\n";
673 break;
674 }
675 }
676 }
677
678 switch ($dbms)
679 {
680 case 'mysql_40':
681 // Remove last line delimiter...
682 $line = substr($line, 0, -2);
683 $line .= "\n);\n\n";
684 break;
685
686 case 'mysql_41':
687 // Remove last line delimiter...
688 $line = substr($line, 0, -2);
689 $line .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;\n\n";
690 break;
691
692 // Create Generator
693 case 'firebird':
694 if ($generator !== false)
695 {
696 $line .= "\nCREATE GENERATOR {$table_name}_gen;;\n";
697 $line .= 'SET GENERATOR ' . $table_name . "_gen TO 0;;\n\n";
698
699 $line .= 'CREATE TRIGGER t_' . $table_name . ' FOR ' . $table_name . "\n";
700 $line .= "BEFORE INSERT\nAS\nBEGIN\n";
701 $line .= "\tNEW.{$generator} = GEN_ID({$table_name}_gen, 1);\nEND;;\n\n";
702 }
703 break;
704
705 case 'oracle':
706 if ($generator !== false)
707 {
708 $line .= "\nCREATE SEQUENCE {$table_name}_seq\n/\n\n";
709
710 $line .= "CREATE OR REPLACE TRIGGER t_{$table_name}\n";
711 $line .= "BEFORE INSERT ON {$table_name}\n";
712 $line .= "FOR EACH ROW WHEN (\n";
713 $line .= "\tnew.{$generator} IS NULL OR new.{$generator} = 0\n";
714 $line .= ")\nBEGIN\n";
715 $line .= "\tSELECT {$table_name}_seq.nextval\n";
716 $line .= "\tINTO :new.{$generator}\n";
717 $line .= "\tFROM dual;\nEND;\n/\n\n";
718 }
719 break;
720 }
721
722 fwrite($fp, $line . "\n");
723 }
724
725 $line = '';
726
727 // Write custom function at the end for some db's
728 switch ($dbms)
729 {
730 case 'mssql':
731 $line = "\nCOMMIT\nGO\n\n";
732 break;
733
734 case 'sqlite':
735 $line = "\nCOMMIT;";
736 break;
737
738 case 'postgres':
739 $line = "\nCOMMIT;";
740 break;
741 }
742
743 fwrite($fp, $line);
744 fclose($fp);
745 }
746
747
748 /**
749 * Define the basic structure
750 * The format:
751 * array('{TABLE_NAME}' => {TABLE_DATA})
752 * {TABLE_DATA}:
753 * COLUMNS = array({column_name} = array({column_type}, {default}, {auto_increment}))
754 * PRIMARY_KEY = {column_name(s)}
755 * KEYS = array({key_name} = array({key_type}, {column_name(s)})),
756 *
757 * Column Types:
758 * INT:x => SIGNED int(x)
759 * BINT => BIGINT
760 * UINT => mediumint(8) UNSIGNED
761 * UINT:x => int(x) UNSIGNED
762 * TINT:x => tinyint(x)
763 * USINT => smallint(4) UNSIGNED (for _order columns)
764 * BOOL => tinyint(1) UNSIGNED
765 * VCHAR => varchar(255)
766 * CHAR:x => char(x)
767 * XSTEXT_UNI => text for storing 100 characters (topic_title for example)
768 * STEXT_UNI => text for storing 255 characters (normal input field with a max of 255 single-byte chars) - same as VCHAR_UNI
769 * TEXT_UNI => text for storing 3000 characters (short text, descriptions, comments, etc.)
770 * MTEXT_UNI => mediumtext (post text, large text)
771 * VCHAR:x => varchar(x)
772 * TIMESTAMP => int(11) UNSIGNED
773 * DECIMAL => decimal number (5,2)
774 * VCHAR_UNI => varchar(255) BINARY
775 * VCHAR_CI => varchar_ci for postgresql, others VCHAR
776 */
777 function get_schema_struct()
778 {
779 $schema_data = array();
780
781 $schema_data['phpbb_attachments'] = array(
782 'COLUMNS' => array(
783 'attach_id' => array('UINT', NULL, 'auto_increment'),
784 'post_msg_id' => array('UINT', 0),
785 'topic_id' => array('UINT', 0),
786 'in_message' => array('BOOL', 0),
787 'poster_id' => array('UINT', 0),
788 'is_orphan' => array('BOOL', 1),
789 'physical_filename' => array('VCHAR', ''),
790 'real_filename' => array('VCHAR', ''),
791 'download_count' => array('UINT', 0),
792 'attach_comment' => array('TEXT_UNI', ''),
793 'extension' => array('VCHAR:100', ''),
794 'mimetype' => array('VCHAR:100', ''),
795 'filesize' => array('UINT:20', 0),
796 'filetime' => array('TIMESTAMP', 0),
797 'thumbnail' => array('BOOL', 0),
798 ),
799 'PRIMARY_KEY' => 'attach_id',
800 'KEYS' => array(
801 'filetime' => array('INDEX', 'filetime'),
802 'post_msg_id' => array('INDEX', 'post_msg_id'),
803 'topic_id' => array('INDEX', 'topic_id'),
804 'poster_id' => array('INDEX', 'poster_id'),
805 'is_orphan' => array('INDEX', 'is_orphan'),
806 ),
807 );
808
809 $schema_data['phpbb_acl_groups'] = array(
810 'COLUMNS' => array(
811 'group_id' => array('UINT', 0),
812 'forum_id' => array('UINT', 0),
813 'auth_option_id' => array('UINT', 0),
814 'auth_role_id' => array('UINT', 0),
815 'auth_setting' => array('TINT:2', 0),
816 ),
817 'KEYS' => array(
818 'group_id' => array('INDEX', 'group_id'),
819 'auth_opt_id' => array('INDEX', 'auth_option_id'),
820 'auth_role_id' => array('INDEX', 'auth_role_id'),
821 ),
822 );
823
824 $schema_data['phpbb_acl_options'] = array(
825 'COLUMNS' => array(
826 'auth_option_id' => array('UINT', NULL, 'auto_increment'),
827 'auth_option' => array('VCHAR:50', ''),
828 'is_global' => array('BOOL', 0),
829 'is_local' => array('BOOL', 0),
830 'founder_only' => array('BOOL', 0),
831 ),
832 'PRIMARY_KEY' => 'auth_option_id',
833 'KEYS' => array(
834 'auth_option' => array('INDEX', 'auth_option'),
835 ),
836 );
837
838 $schema_data['phpbb_acl_roles'] = array(
839 'COLUMNS' => array(
840 'role_id' => array('UINT', NULL, 'auto_increment'),
841 'role_name' => array('VCHAR_UNI', ''),
842 'role_description' => array('TEXT_UNI', ''),
843 'role_type' => array('VCHAR:10', ''),
844 'role_order' => array('USINT', 0),
845 ),
846 'PRIMARY_KEY' => 'role_id',
847 'KEYS' => array(
848 'role_type' => array('INDEX', 'role_type'),
849 'role_order' => array('INDEX', 'role_order'),
850 ),
851 );
852
853 $schema_data['phpbb_acl_roles_data'] = array(
854 'COLUMNS' => array(
855 'role_id' => array('UINT', 0),
856 'auth_option_id' => array('UINT', 0),
857 'auth_setting' => array('TINT:2', 0),
858 ),
859 'PRIMARY_KEY' => array('role_id', 'auth_option_id'),
860 'KEYS' => array(
861 'ath_op_id' => array('INDEX', 'auth_option_id'),
862 ),
863 );
864
865 $schema_data['phpbb_acl_users'] = array(
866 'COLUMNS' => array(
867 'user_id' => array('UINT', 0),
868 'forum_id' => array('UINT', 0),
869 'auth_option_id' => array('UINT', 0),
870 'auth_role_id' => array('UINT', 0),
871 'auth_setting' => array('TINT:2', 0),
872 ),
873 'KEYS' => array(
874 'user_id' => array('INDEX', 'user_id'),
875 'auth_option_id' => array('INDEX', 'auth_option_id'),
876 'auth_role_id' => array('INDEX', 'auth_role_id'),
877 ),
878 );
879
880 $schema_data['phpbb_banlist'] = array(
881 'COLUMNS' => array(
882 'ban_id' => array('UINT', NULL, 'auto_increment'),
883 'ban_userid' => array('UINT', 0),
884 'ban_ip' => array('VCHAR:40', ''),
885 'ban_email' => array('VCHAR_UNI:100', ''),
886 'ban_start' => array('TIMESTAMP', 0),
887 'ban_end' => array('TIMESTAMP', 0),
888 'ban_exclude' => array('BOOL', 0),
889 'ban_reason' => array('VCHAR_UNI', ''),
890 'ban_give_reason' => array('VCHAR_UNI', ''),
891 ),
892 'PRIMARY_KEY' => 'ban_id',
893 'KEYS' => array(
894 'ban_end' => array('INDEX', 'ban_end'),
895 'ban_user' => array('INDEX', array('ban_userid', 'ban_exclude')),
896 'ban_email' => array('INDEX', array('ban_email', 'ban_exclude')),
897 'ban_ip' => array('INDEX', array('ban_ip', 'ban_exclude')),
898 ),
899 );
900
901 $schema_data['phpbb_bbcodes'] = array(
902 'COLUMNS' => array(
903 'bbcode_id' => array('TINT:3', 0),
904 'bbcode_tag' => array('VCHAR:16', ''),
905 'bbcode_helpline' => array('VCHAR_UNI', ''),
906 'display_on_posting' => array('BOOL', 0),
907 'bbcode_match' => array('TEXT_UNI', ''),
908 'bbcode_tpl' => array('MTEXT_UNI', ''),
909 'first_pass_match' => array('MTEXT_UNI', ''),
910 'first_pass_replace' => array('MTEXT_UNI', ''),
911 'second_pass_match' => array('MTEXT_UNI', ''),
912 'second_pass_replace' => array('MTEXT_UNI', ''),
913 ),
914 'PRIMARY_KEY' => 'bbcode_id',
915 'KEYS' => array(
916 'display_on_post' => array('INDEX', 'display_on_posting'),
917 ),
918 );
919
920 $schema_data['phpbb_bookmarks'] = array(
921 'COLUMNS' => array(
922 'topic_id' => array('UINT', 0),
923 'user_id' => array('UINT', 0),
924 ),
925 'PRIMARY_KEY' => array('topic_id', 'user_id'),
926 );
927
928 $schema_data['phpbb_bots'] = array(
929 'COLUMNS' => array(
930 'bot_id' => array('UINT', NULL, 'auto_increment'),
931 'bot_active' => array('BOOL', 1),
932 'bot_name' => array('STEXT_UNI', ''),
933 'user_id' => array('UINT', 0),
934 'bot_agent' => array('VCHAR', ''),
935 'bot_ip' => array('VCHAR', ''),
936 ),
937 'PRIMARY_KEY' => 'bot_id',
938 'KEYS' => array(
939 'bot_active' => array('INDEX', 'bot_active'),
940 ),
941 );
942
943 $schema_data['phpbb_config'] = array(
944 'COLUMNS' => array(
945 'config_name' => array('VCHAR', ''),
946 'config_value' => array('VCHAR_UNI', ''),
947 'is_dynamic' => array('BOOL', 0),
948 ),
949 'PRIMARY_KEY' => 'config_name',
950 'KEYS' => array(
951 'is_dynamic' => array('INDEX', 'is_dynamic'),
952 ),
953 );
954
955 $schema_data['phpbb_confirm'] = array(
956 'COLUMNS' => array(
957 'confirm_id' => array('CHAR:32', ''),
958 'session_id' => array('CHAR:32', ''),
959 'confirm_type' => array('TINT:3', 0),
960 'code' => array('VCHAR:8', ''),
961 'seed' => array('UINT:10', 0),
962 ),
963 'PRIMARY_KEY' => array('session_id', 'confirm_id'),
964 'KEYS' => array(
965 'confirm_type' => array('INDEX', 'confirm_type'),
966 ),
967 );
968
969 $schema_data['phpbb_disallow'] = array(
970 'COLUMNS' => array(
971 'disallow_id' => array('UINT', NULL, 'auto_increment'),
972 'disallow_username' => array('VCHAR_UNI:255', ''),
973 ),
974 'PRIMARY_KEY' => 'disallow_id',
975 );
976
977 $schema_data['phpbb_drafts'] = array(
978 'COLUMNS' => array(
979 'draft_id' => array('UINT', NULL, 'auto_increment'),
980 'user_id' => array('UINT', 0),
981 'topic_id' => array('UINT', 0),
982 'forum_id' => array('UINT', 0),
983 'save_time' => array('TIMESTAMP', 0),
984 'draft_subject' => array('XSTEXT_UNI', ''),
985 'draft_message' => array('MTEXT_UNI', ''),
986 ),
987 'PRIMARY_KEY' => 'draft_id',
988 'KEYS' => array(
989 'save_time' => array('INDEX', 'save_time'),
990 ),
991 );
992
993 $schema_data['phpbb_extensions'] = array(
994 'COLUMNS' => array(
995 'extension_id' => array('UINT', NULL, 'auto_increment'),
996 'group_id' => array('UINT', 0),
997 'extension' => array('VCHAR:100', ''),
998 ),
999 'PRIMARY_KEY' => 'extension_id',
1000 );
1001
1002 $schema_data['phpbb_extension_groups'] = array(
1003 'COLUMNS' => array(
1004 'group_id' => array('UINT', NULL, 'auto_increment'),
1005 'group_name' => array('VCHAR_UNI', ''),
1006 'cat_id' => array('TINT:2', 0),
1007 'allow_group' => array('BOOL', 0),
1008 'download_mode' => array('BOOL', 1),
1009 'upload_icon' => array('VCHAR', ''),
1010 'max_filesize' => array('UINT:20', 0),
1011 'allowed_forums' => array('TEXT', ''),
1012 'allow_in_pm' => array('BOOL', 0),
1013 ),
1014 'PRIMARY_KEY' => 'group_id',
1015 );
1016
1017 $schema_data['phpbb_forums'] = array(
1018 'COLUMNS' => array(
1019 'forum_id' => array('UINT', NULL, 'auto_increment'),
1020 'parent_id' => array('UINT', 0),
1021 'left_id' => array('UINT', 0),
1022 'right_id' => array('UINT', 0),
1023 'forum_parents' => array('MTEXT', ''),
1024 'forum_name' => array('STEXT_UNI', ''),
1025 'forum_desc' => array('TEXT_UNI', ''),
1026 'forum_desc_bitfield' => array('VCHAR:255', ''),
1027 'forum_desc_options' => array('UINT:11', 7),
1028 'forum_desc_uid' => array('VCHAR:5', ''),
1029 'forum_link' => array('VCHAR_UNI', ''),
1030 'forum_password' => array('VCHAR_UNI:40', ''),
1031 'forum_style' => array('TINT:4', 0),
1032 'forum_image' => array('VCHAR', ''),
1033 'forum_rules' => array('TEXT_UNI', ''),
1034 'forum_rules_link' => array('VCHAR_UNI', ''),
1035 'forum_rules_bitfield' => array('VCHAR:255', ''),
1036 'forum_rules_options' => array('UINT:11', 7),
1037 'forum_rules_uid' => array('VCHAR:5', ''),
1038 'forum_topics_per_page' => array('TINT:4', 0),
1039 'forum_type' => array('TINT:4', 0),
1040 'forum_status' => array('TINT:4', 0),
1041 'forum_posts' => array('UINT', 0),
1042 'forum_topics' => array('UINT', 0),
1043 'forum_topics_real' => array('UINT', 0),
1044 'forum_last_post_id' => array('UINT', 0),
1045 'forum_last_poster_id' => array('UINT', 0),
1046 'forum_last_post_subject' => array('XSTEXT_UNI', ''),
1047 'forum_last_post_time' => array('TIMESTAMP', 0),
1048 'forum_last_poster_name'=> array('VCHAR_UNI', ''),
1049 'forum_last_poster_colour'=> array('VCHAR:6', ''),
1050 'forum_flags' => array('TINT:4', 32),
1051 'display_on_index' => array('BOOL', 1),
1052 'enable_indexing' => array('BOOL', 1),
1053 'enable_icons' => array('BOOL', 1),
1054 'enable_prune' => array('BOOL', 0),
1055 'prune_next' => array('TIMESTAMP', 0),
1056 'prune_days' => array('UINT', 0),
1057 'prune_viewed' => array('UINT', 0),
1058 'prune_freq' => array('UINT', 0),
1059 ),
1060 'PRIMARY_KEY' => 'forum_id',
1061 'KEYS' => array(
1062 'left_right_id' => array('INDEX', array('left_id', 'right_id')),
1063 'forum_lastpost_id' => array('INDEX', 'forum_last_post_id'),
1064 ),
1065 );
1066
1067 $schema_data['phpbb_forums_access'] = array(
1068 'COLUMNS' => array(
1069 'forum_id' => array('UINT', 0),
1070 'user_id' => array('UINT', 0),
1071 'session_id' => array('CHAR:32', ''),
1072 ),
1073 'PRIMARY_KEY' => array('forum_id', 'user_id', 'session_id'),
1074 );
1075
1076 $schema_data['phpbb_forums_track'] = array(
1077 'COLUMNS' => array(
1078 'user_id' => array('UINT', 0),
1079 'forum_id' => array('UINT', 0),
1080 'mark_time' => array('TIMESTAMP', 0),
1081 ),
1082 'PRIMARY_KEY' => array('user_id', 'forum_id'),
1083 );
1084
1085 $schema_data['phpbb_forums_watch'] = array(
1086 'COLUMNS' => array(
1087 'forum_id' => array('UINT', 0),
1088 'user_id' => array('UINT', 0),
1089 'notify_status' => array('BOOL', 0),
1090 ),
1091 'KEYS' => array(
1092 'forum_id' => array('INDEX', 'forum_id'),
1093 'user_id' => array('INDEX', 'user_id'),
1094 'notify_stat' => array('INDEX', 'notify_status'),
1095 ),
1096 );
1097
1098 $schema_data['phpbb_groups'] = array(
1099 'COLUMNS' => array(
1100 'group_id' => array('UINT', NULL, 'auto_increment'),
1101 'group_type' => array('TINT:4', 1),
1102 'group_founder_manage' => array('BOOL', 0),
1103 'group_name' => array('VCHAR_CI', ''),
1104 'group_desc' => array('TEXT_UNI', ''),
1105 'group_desc_bitfield' => array('VCHAR:255', ''),
1106 'group_desc_options' => array('UINT:11', 7),
1107 'group_desc_uid' => array('VCHAR:5', ''),
1108 'group_display' => array('BOOL', 0),
1109 'group_avatar' => array('VCHAR', ''),
1110 'group_avatar_type' => array('TINT:4', 0),
1111 'group_avatar_width' => array('TINT:4', 0),
1112 'group_avatar_height' => array('TINT:4', 0),
1113 'group_rank' => array('UINT', 0),
1114 'group_colour' => array('VCHAR:6', ''),
1115 'group_sig_chars' => array('UINT', 0),
1116 'group_receive_pm' => array('BOOL', 0),
1117 'group_message_limit' => array('UINT', 0),
1118 'group_legend' => array('BOOL', 1),
1119 ),
1120 'PRIMARY_KEY' => 'group_id',
1121 'KEYS' => array(
1122 'group_legend' => array('INDEX', 'group_legend'),
1123 ),
1124 );
1125
1126 $schema_data['phpbb_icons'] = array(
1127 'COLUMNS' => array(
1128 'icons_id' => array('UINT', NULL, 'auto_increment'),
1129 'icons_url' => array('VCHAR', ''),
1130 'icons_width' => array('TINT:4', 0),
1131 'icons_height' => array('TINT:4', 0),
1132 'icons_order' => array('UINT', 0),
1133 'display_on_posting' => array('BOOL', 1),
1134 ),
1135 'PRIMARY_KEY' => 'icons_id',
1136 'KEYS' => array(
1137 'display_on_posting' => array('INDEX', 'display_on_posting'),
1138 ),
1139 );
1140
1141 $schema_data['phpbb_lang'] = array(
1142 'COLUMNS' => array(
1143 'lang_id' => array('TINT:4', NULL, 'auto_increment'),
1144 'lang_iso' => array('VCHAR:30', ''),
1145 'lang_dir' => array('VCHAR:30', ''),
1146 'lang_english_name' => array('VCHAR_UNI:100', ''),
1147 'lang_local_name' => array('VCHAR_UNI:255', ''),
1148 'lang_author' => array('VCHAR_UNI:255', ''),
1149 ),
1150 'PRIMARY_KEY' => 'lang_id',
1151 'KEYS' => array(
1152 'lang_iso' => array('INDEX', 'lang_iso'),
1153 ),
1154 );
1155
1156 $schema_data['phpbb_log'] = array(
1157 'COLUMNS' => array(
1158 'log_id' => array('UINT', NULL, 'auto_increment'),
1159 'log_type' => array('TINT:4', 0),
1160 'user_id' => array('UINT', 0),
1161 'forum_id' => array('UINT', 0),
1162 'topic_id' => array('UINT', 0),
1163 'reportee_id' => array('UINT', 0),
1164 'log_ip' => array('VCHAR:40', ''),
1165 'log_time' => array('TIMESTAMP', 0),
1166 'log_operation' => array('TEXT_UNI', ''),
1167 'log_data' => array('MTEXT_UNI', ''),
1168 ),
1169 'PRIMARY_KEY' => 'log_id',
1170 'KEYS' => array(
1171 'log_type' => array('INDEX', 'log_type'),
1172 'forum_id' => array('INDEX', 'forum_id'),
1173 'topic_id' => array('INDEX', 'topic_id'),
1174 'reportee_id' => array('INDEX', 'reportee_id'),
1175 'user_id' => array('INDEX', 'user_id'),
1176 ),
1177 );
1178
1179 $schema_data['phpbb_moderator_cache'] = array(
1180 'COLUMNS' => array(
1181 'forum_id' => array('UINT', 0),
1182 'user_id' => array('UINT', 0),
1183 'username' => array('VCHAR_UNI:255', ''),
1184 'group_id' => array('UINT', 0),
1185 'group_name' => array('VCHAR_UNI', ''),
1186 'display_on_index' => array('BOOL', 1),
1187 ),
1188 'KEYS' => array(
1189 'disp_idx' => array('INDEX', 'display_on_index'),
1190 'forum_id' => array('INDEX', 'forum_id'),
1191 ),
1192 );
1193
1194 $schema_data['phpbb_modules'] = array(
1195 'COLUMNS' => array(
1196 'module_id' => array('UINT', NULL, 'auto_increment'),
1197 'module_enabled' => array('BOOL', 1),
1198 'module_display' => array('BOOL', 1),
1199 'module_basename' => array('VCHAR', ''),
1200 'module_class' => array('VCHAR:10', ''),
1201 'parent_id' => array('UINT', 0),
1202 'left_id' => array('UINT', 0),
1203 'right_id' => array('UINT', 0),
1204 'module_langname' => array('VCHAR', ''),
1205 'module_mode' => array('VCHAR', ''),
1206 'module_auth' => array('VCHAR', ''),
1207 ),
1208 'PRIMARY_KEY' => 'module_id',
1209 'KEYS' => array(
1210 'left_right_id' => array('INDEX', array('left_id', 'right_id')),
1211 'module_enabled' => array('INDEX', 'module_enabled'),
1212 'class_left_id' => array('INDEX', array('module_class', 'left_id')),
1213 ),
1214 );
1215
1216 $schema_data['phpbb_poll_options'] = array(
1217 'COLUMNS' => array(
1218 'poll_option_id' => array('TINT:4', 0),
1219 'topic_id' => array('UINT', 0),
1220 'poll_option_text' => array('TEXT_UNI', ''),
1221 'poll_option_total' => array('UINT', 0),
1222 ),
1223 'KEYS' => array(
1224 'poll_opt_id' => array('INDEX', 'poll_option_id'),
1225 'topic_id' => array('INDEX', 'topic_id'),
1226 ),
1227 );
1228
1229 $schema_data['phpbb_poll_votes'] = array(
1230 'COLUMNS' => array(
1231 'topic_id' => array('UINT', 0),
1232 'poll_option_id' => array('TINT:4', 0),
1233 'vote_user_id' => array('UINT', 0),
1234 'vote_user_ip' => array('VCHAR:40', ''),
1235 ),
1236 'KEYS' => array(
1237 'topic_id' => array('INDEX', 'topic_id'),
1238 'vote_user_id' => array('INDEX', 'vote_user_id'),
1239 'vote_user_ip' => array('INDEX', 'vote_user_ip'),
1240 ),
1241 );
1242
1243 $schema_data['phpbb_posts'] = array(
1244 'COLUMNS' => array(
1245 'post_id' => array('UINT', NULL, 'auto_increment'),
1246 'topic_id' => array('UINT', 0),
1247 'forum_id' => array('UINT', 0),
1248 'poster_id' => array('UINT', 0),
1249 'icon_id' => array('UINT', 0),
1250 'poster_ip' => array('VCHAR:40', ''),
1251 'post_time' => array('TIMESTAMP', 0),
1252 'post_approved' => array('BOOL', 1),
1253 'post_reported' => array('BOOL', 0),
1254 'enable_bbcode' => array('BOOL', 1),
1255 'enable_smilies' => array('BOOL', 1),
1256 'enable_magic_url' => array('BOOL', 1),
1257 'enable_sig' => array('BOOL', 1),
1258 'post_username' => array('VCHAR_UNI:255', ''),
1259 'post_subject' => array('XSTEXT_UNI', '', 'true_sort'),
1260 'post_text' => array('MTEXT_UNI', ''),
1261 'post_checksum' => array('VCHAR:32', ''),
1262 'post_attachment' => array('BOOL', 0),
1263 'bbcode_bitfield' => array('VCHAR:255', ''),
1264 'bbcode_uid' => array('VCHAR:5', ''),
1265 'post_postcount' => array('BOOL', 1),
1266 'post_edit_time' => array('TIMESTAMP', 0),
1267 'post_edit_reason' => array('STEXT_UNI', ''),
1268 'post_edit_user' => array('UINT', 0),
1269 'post_edit_count' => array('USINT', 0),
1270 'post_edit_locked' => array('BOOL', 0),
1271 ),
1272 'PRIMARY_KEY' => 'post_id',
1273 'KEYS' => array(
1274 'forum_id' => array('INDEX', 'forum_id'),
1275 'topic_id' => array('INDEX', 'topic_id'),
1276 'poster_ip' => array('INDEX', 'poster_ip'),
1277 'poster_id' => array('INDEX', 'poster_id'),
1278 'post_approved' => array('INDEX', 'post_approved'),
1279 'tid_post_time' => array('INDEX', array('topic_id', 'post_time')),
1280 ),
1281 );
1282
1283 $schema_data['phpbb_privmsgs'] = array(
1284 'COLUMNS' => array(
1285 'msg_id' => array('UINT', NULL, 'auto_increment'),
1286 'root_level' => array('UINT', 0),
1287 'author_id' => array('UINT', 0),
1288 'icon_id' => array('UINT', 0),
1289 'author_ip' => array('VCHAR:40', ''),
1290 'message_time' => array('TIMESTAMP', 0),
1291 'enable_bbcode' => array('BOOL', 1),
1292 'enable_smilies' => array('BOOL', 1),
1293 'enable_magic_url' => array('BOOL', 1),
1294 'enable_sig' => array('BOOL', 1),
1295 'message_subject' => array('XSTEXT_UNI', ''),
1296 'message_text' => array('MTEXT_UNI', ''),
1297 'message_edit_reason' => array('STEXT_UNI', ''),
1298 'message_edit_user' => array('UINT', 0),
1299 'message_attachment' => array('BOOL', 0),
1300 'bbcode_bitfield' => array('VCHAR:255', ''),
1301 'bbcode_uid' => array('VCHAR:5', ''),
1302 'message_edit_time' => array('TIMESTAMP', 0),
1303 'message_edit_count' => array('USINT', 0),
1304 'to_address' => array('TEXT_UNI', ''),
1305 'bcc_address' => array('TEXT_UNI', ''),
1306 ),
1307 'PRIMARY_KEY' => 'msg_id',
1308 'KEYS' => array(
1309 'author_ip' => array('INDEX', 'author_ip'),
1310 'message_time' => array('INDEX', 'message_time'),
1311 'author_id' => array('INDEX', 'author_id'),
1312 'root_level' => array('INDEX', 'root_level'),
1313 ),
1314 );
1315
1316 $schema_data['phpbb_privmsgs_folder'] = array(
1317 'COLUMNS' => array(
1318 'folder_id' => array('UINT', NULL, 'auto_increment'),
1319 'user_id' => array('UINT', 0),
1320 'folder_name' => array('VCHAR_UNI', ''),
1321 'pm_count' => array('UINT', 0),
1322 ),
1323 'PRIMARY_KEY' => 'folder_id',
1324 'KEYS' => array(
1325 'user_id' => array('INDEX', 'user_id'),
1326 ),
1327 );
1328
1329 $schema_data['phpbb_privmsgs_rules'] = array(
1330 'COLUMNS' => array(
1331 'rule_id' => array('UINT', NULL, 'auto_increment'),
1332 'user_id' => array('UINT', 0),
1333 'rule_check' => array('UINT', 0),
1334 'rule_connection' => array('UINT', 0),
1335 'rule_string' => array('VCHAR_UNI', ''),
1336 'rule_user_id' => array('UINT', 0),
1337 'rule_group_id' => array('UINT', 0),
1338 'rule_action' => array('UINT', 0),
1339 'rule_folder_id' => array('INT:11', 0),
1340 ),
1341 'PRIMARY_KEY' => 'rule_id',
1342 'KEYS' => array(
1343 'user_id' => array('INDEX', 'user_id'),
1344 ),
1345 );
1346
1347 $schema_data['phpbb_privmsgs_to'] = array(
1348 'COLUMNS' => array(
1349 'msg_id' => array('UINT', 0),
1350 'user_id' => array('UINT', 0),
1351 'author_id' => array('UINT', 0),
1352 'pm_deleted' => array('BOOL', 0),
1353 'pm_new' => array('BOOL', 1),
1354 'pm_unread' => array('BOOL', 1),
1355 'pm_replied' => array('BOOL', 0),
1356 'pm_marked' => array('BOOL', 0),
1357 'pm_forwarded' => array('BOOL', 0),
1358 'folder_id' => array('INT:11', 0),
1359 ),
1360 'KEYS' => array(
1361 'msg_id' => array('INDEX', 'msg_id'),
1362 'author_id' => array('INDEX', 'author_id'),
1363 'usr_flder_id' => array('INDEX', array('user_id', 'folder_id')),
1364 ),
1365 );
1366
1367 $schema_data['phpbb_profile_fields'] = array(
1368 'COLUMNS' => array(
1369 'field_id' => array('UINT', NULL, 'auto_increment'),
1370 'field_name' => array('VCHAR_UNI', ''),
1371 'field_type' => array('TINT:4', 0),
1372 'field_ident' => array('VCHAR:20', ''),
1373 'field_length' => array('VCHAR:20', ''),
1374 'field_minlen' => array('VCHAR', ''),
1375 'field_maxlen' => array('VCHAR', ''),
1376 'field_novalue' => array('VCHAR_UNI', ''),
1377 'field_default_value' => array('VCHAR_UNI', ''),
1378 'field_validation' => array('VCHAR_UNI:20', ''),
1379 'field_required' => array('BOOL', 0),
1380 'field_show_on_reg' => array('BOOL', 0),
1381 'field_hide' => array('BOOL', 0),
1382 'field_no_view' => array('BOOL', 0),
1383 'field_active' => array('BOOL', 0),
1384 'field_order' => array('UINT', 0),
1385 ),
1386 'PRIMARY_KEY' => 'field_id',
1387 'KEYS' => array(
1388 'fld_type' => array('INDEX', 'field_type'),
1389 'fld_ordr' => array('INDEX', 'field_order'),
1390 ),
1391 );
1392
1393 $schema_data['phpbb_profile_fields_data'] = array(
1394 'COLUMNS' => array(
1395 'user_id' => array('UINT', 0),
1396 ),
1397 'PRIMARY_KEY' => 'user_id',
1398 );
1399
1400 $schema_data['phpbb_profile_fields_lang'] = array(
1401 'COLUMNS' => array(
1402 'field_id' => array('UINT', 0),
1403 'lang_id' => array('UINT', 0),
1404 'option_id' => array('UINT', 0),
1405 'field_type' => array('TINT:4', 0),
1406 'lang_value' => array('VCHAR_UNI', ''),
1407 ),
1408 'PRIMARY_KEY' => array('field_id', 'lang_id', 'option_id'),
1409 );
1410
1411 $schema_data['phpbb_profile_lang'] = array(
1412 'COLUMNS' => array(
1413 'field_id' => array('UINT', 0),
1414 'lang_id' => array('UINT', 0),
1415 'lang_name' => array('VCHAR_UNI', ''),
1416 'lang_explain' => array('TEXT_UNI', ''),
1417 'lang_default_value' => array('VCHAR_UNI', ''),
1418 ),
1419 'PRIMARY_KEY' => array('field_id', 'lang_id'),
1420 );
1421
1422 $schema_data['phpbb_ranks'] = array(
1423 'COLUMNS' => array(
1424 'rank_id' => array('UINT', NULL, 'auto_increment'),
1425 'rank_title' => array('VCHAR_UNI', ''),
1426 'rank_min' => array('UINT', 0),
1427 'rank_special' => array('BOOL', 0),
1428 'rank_image' => array('VCHAR', ''),
1429 ),
1430 'PRIMARY_KEY' => 'rank_id',
1431 );
1432
1433 $schema_data['phpbb_reports'] = array(
1434 'COLUMNS' => array(
1435 'report_id' => array('UINT', NULL, 'auto_increment'),
1436 'reason_id' => array('USINT', 0),
1437 'post_id' => array('UINT', 0),
1438 'user_id' => array('UINT', 0),
1439 'user_notify' => array('BOOL', 0),
1440 'report_closed' => array('BOOL', 0),
1441 'report_time' => array('TIMESTAMP', 0),
1442 'report_text' => array('MTEXT_UNI', ''),
1443 ),
1444 'PRIMARY_KEY' => 'report_id',
1445 );
1446
1447 $schema_data['phpbb_reports_reasons'] = array(
1448 'COLUMNS' => array(
1449 'reason_id' => array('USINT', NULL, 'auto_increment'),
1450 'reason_title' => array('VCHAR_UNI', ''),
1451 'reason_description' => array('MTEXT_UNI', ''),
1452 'reason_order' => array('USINT', 0),
1453 ),
1454 'PRIMARY_KEY' => 'reason_id',
1455 );
1456
1457 $schema_data['phpbb_search_results'] = array(
1458 'COLUMNS' => array(
1459 'search_key' => array('VCHAR:32', ''),
1460 'search_time' => array('TIMESTAMP', 0),
1461 'search_keywords' => array('MTEXT_UNI', ''),
1462 'search_authors' => array('MTEXT', ''),
1463 ),
1464 'PRIMARY_KEY' => 'search_key',
1465 );
1466
1467 $schema_data['phpbb_search_wordlist'] = array(
1468 'COLUMNS' => array(
1469 'word_id' => array('UINT', NULL, 'auto_increment'),
1470 'word_text' => array('VCHAR_UNI', ''),
1471 'word_common' => array('BOOL', 0),
1472 'word_count' => array('UINT', 0),
1473 ),
1474 'PRIMARY_KEY' => 'word_id',
1475 'KEYS' => array(
1476 'wrd_txt' => array('UNIQUE', 'word_text'),
1477 'wrd_cnt' => array('INDEX', 'word_count'),
1478 ),
1479 );
1480
1481 $schema_data['phpbb_search_wordmatch'] = array(
1482 'COLUMNS' => array(
1483 'post_id' => array('UINT', 0),
1484 'word_id' => array('UINT', 0),
1485 'title_match' => array('BOOL', 0),
1486 ),
1487 'KEYS' => array(
1488 'unq_mtch' => array('UNIQUE', array('word_id', 'post_id', 'title_match')),
1489 'word_id' => array('INDEX', 'word_id'),
1490 'post_id' => array('INDEX', 'post_id'),
1491 ),
1492 );
1493
1494 $schema_data['phpbb_sessions'] = array(
1495 'COLUMNS' => array(
1496 'session_id' => array('CHAR:32', ''),
1497 'session_user_id' => array('UINT', 0),
1498 'session_last_visit' => array('TIMESTAMP', 0),
1499 'session_start' => array('TIMESTAMP', 0),
1500 'session_time' => array('TIMESTAMP', 0),
1501 'session_ip' => array('VCHAR:40', ''),
1502 'session_browser' => array('VCHAR:150', ''),
1503 'session_forwarded_for' => array('VCHAR:255', ''),
1504 'session_page' => array('VCHAR_UNI', ''),
1505 'session_viewonline' => array('BOOL', 1),
1506 'session_autologin' => array('BOOL', 0),
1507 'session_admin' => array('BOOL', 0),
1508 ),
1509 'PRIMARY_KEY' => 'session_id',
1510 'KEYS' => array(
1511 'session_time' => array('INDEX', 'session_time'),
1512 'session_user_id' => array('INDEX', 'session_user_id'),
1513 ),
1514 );
1515
1516 $schema_data['phpbb_sessions_keys'] = array(
1517 'COLUMNS' => array(
1518 'key_id' => array('CHAR:32', ''),
1519 'user_id' => array('UINT', 0),
1520 'last_ip' => array('VCHAR:40', ''),
1521 'last_login' => array('TIMESTAMP', 0),
1522 ),
1523 'PRIMARY_KEY' => array('key_id', 'user_id'),
1524 'KEYS' => array(
1525 'last_login' => array('INDEX', 'last_login'),
1526 ),
1527 );
1528
1529 $schema_data['phpbb_sitelist'] = array(
1530 'COLUMNS' => array(
1531 'site_id' => array('UINT', NULL, 'auto_increment'),
1532 'site_ip' => array('VCHAR:40', ''),
1533 'site_hostname' => array('VCHAR', ''),
1534 'ip_exclude' => array('BOOL', 0),
1535 ),
1536 'PRIMARY_KEY' => 'site_id',
1537 );
1538
1539 $schema_data['phpbb_smilies'] = array(
1540 'COLUMNS' => array(
1541 'smiley_id' => array('UINT', NULL, 'auto_increment'),
1542 // We may want to set 'code' to VCHAR:50 or check if unicode support is possible... at the moment only ASCII characters are allowed.
1543 'code' => array('VCHAR_UNI:50', ''),
1544 'emotion' => array('VCHAR_UNI:50', ''),
1545 'smiley_url' => array('VCHAR:50', ''),
1546 'smiley_width' => array('USINT', 0),
1547 'smiley_height' => array('USINT', 0),
1548 'smiley_order' => array('UINT', 0),
1549 'display_on_posting'=> array('BOOL', 1),
1550 ),
1551 'PRIMARY_KEY' => 'smiley_id',
1552 'KEYS' => array(
1553 'display_on_post' => array('INDEX', 'display_on_posting'),
1554 ),
1555 );
1556
1557 $schema_data['phpbb_styles'] = array(
1558 'COLUMNS' => array(
1559 'style_id' => array('TINT:4', NULL, 'auto_increment'),
1560 'style_name' => array('VCHAR_UNI:255', ''),
1561 'style_copyright' => array('VCHAR_UNI', ''),
1562 'style_active' => array('BOOL', 1),
1563 'template_id' => array('TINT:4', 0),
1564 'theme_id' => array('TINT:4', 0),
1565 'imageset_id' => array('TINT:4', 0),
1566 ),
1567 'PRIMARY_KEY' => 'style_id',
1568 'KEYS' => array(
1569 'style_name' => array('UNIQUE', 'style_name'),
1570 'template_id' => array('INDEX', 'template_id'),
1571 'theme_id' => array('INDEX', 'theme_id'),
1572 'imageset_id' => array('INDEX', 'imageset_id'),
1573 ),
1574 );
1575
1576 $schema_data['phpbb_styles_template'] = array(
1577 'COLUMNS' => array(
1578 'template_id' => array('TINT:4', NULL, 'auto_increment'),
1579 'template_name' => array('VCHAR_UNI:255', ''),
1580 'template_copyright' => array('VCHAR_UNI', ''),
1581 'template_path' => array('VCHAR:100', ''),
1582 'bbcode_bitfield' => array('VCHAR:255', 'kNg='),
1583 'template_storedb' => array('BOOL', 0),
1584 ),
1585 'PRIMARY_KEY' => 'template_id',
1586 'KEYS' => array(
1587 'tmplte_nm' => array('UNIQUE', 'template_name'),
1588 ),
1589 );
1590
1591 $schema_data['phpbb_styles_template_data'] = array(
1592 'COLUMNS' => array(
1593 'template_id' => array('TINT:4', NULL, 'auto_increment'),
1594 'template_filename' => array('VCHAR:100', ''),
1595 'template_included' => array('TEXT', ''),
1596 'template_mtime' => array('TIMESTAMP', 0),
1597 'template_data' => array('MTEXT_UNI', ''),
1598 ),
1599 'KEYS' => array(
1600 'tid' => array('INDEX', 'template_id'),
1601 'tfn' => array('INDEX', 'template_filename'),
1602 ),
1603 );
1604
1605 $schema_data['phpbb_styles_theme'] = array(
1606 'COLUMNS' => array(
1607 'theme_id' => array('TINT:4', NULL, 'auto_increment'),
1608 'theme_name' => array('VCHAR_UNI:255', ''),
1609 'theme_copyright' => array('VCHAR_UNI', ''),
1610 'theme_path' => array('VCHAR:100', ''),
1611 'theme_storedb' => array('BOOL', 0),
1612 'theme_mtime' => array('TIMESTAMP', 0),
1613 'theme_data' => array('MTEXT_UNI', ''),
1614 ),
1615 'PRIMARY_KEY' => 'theme_id',
1616 'KEYS' => array(
1617 'theme_name' => array('UNIQUE', 'theme_name'),
1618 ),
1619 );
1620
1621 $schema_data['phpbb_styles_imageset'] = array(
1622 'COLUMNS' => array(
1623 'imageset_id' => array('TINT:4', NULL, 'auto_increment'),
1624 'imageset_name' => array('VCHAR_UNI:255', ''),
1625 'imageset_copyright' => array('VCHAR_UNI', ''),
1626 'imageset_path' => array('VCHAR:100', ''),
1627 ),
1628 'PRIMARY_KEY' => 'imageset_id',
1629 'KEYS' => array(
1630 'imgset_nm' => array('UNIQUE', 'imageset_name'),
1631 ),
1632 );
1633
1634 $schema_data['phpbb_styles_imageset_data'] = array(
1635 'COLUMNS' => array(
1636 'image_id' => array('USINT', NULL, 'auto_increment'),
1637 'image_name' => array('VCHAR:200', ''),
1638 'image_filename' => array('VCHAR:200', ''),
1639 'image_lang' => array('VCHAR:30', ''),
1640 'image_height' => array('USINT', 0),
1641 'image_width' => array('USINT', 0),
1642 'imageset_id' => array('TINT:4', 0),
1643 ),
1644 'PRIMARY_KEY' => 'image_id',
1645 'KEYS' => array(
1646 'i_d' => array('INDEX', 'imageset_id'),
1647 ),
1648 );
1649
1650 $schema_data['phpbb_topics'] = array(
1651 'COLUMNS' => array(
1652 'topic_id' => array('UINT', NULL, 'auto_increment'),
1653 'forum_id' => array('UINT', 0),
1654 'icon_id' => array('UINT', 0),
1655 'topic_attachment' => array('BOOL', 0),
1656 'topic_approved' => array('BOOL', 1),
1657 'topic_reported' => array('BOOL', 0),
1658 'topic_title' => array('XSTEXT_UNI', '', 'true_sort'),
1659 'topic_poster' => array('UINT', 0),
1660 'topic_time' => array('TIMESTAMP', 0),
1661 'topic_time_limit' => array('TIMESTAMP', 0),
1662 'topic_views' => array('UINT', 0),
1663 'topic_replies' => array('UINT', 0),
1664 'topic_replies_real' => array('UINT', 0),
1665 'topic_status' => array('TINT:3', 0),
1666 'topic_type' => array('TINT:3', 0),
1667 'topic_first_post_id' => array('UINT', 0),
1668 'topic_first_poster_name' => array('VCHAR_UNI', ''),
1669 'topic_first_poster_colour' => array('VCHAR:6', ''),
1670 'topic_last_post_id' => array('UINT', 0),
1671 'topic_last_poster_id' => array('UINT', 0),
1672 'topic_last_poster_name' => array('VCHAR_UNI', ''),
1673 'topic_last_poster_colour' => array('VCHAR:6', ''),
1674 'topic_last_post_subject' => array('XSTEXT_UNI', ''),
1675 'topic_last_post_time' => array('TIMESTAMP', 0),
1676 'topic_last_view_time' => array('TIMESTAMP', 0),
1677 'topic_moved_id' => array('UINT', 0),
1678 'topic_bumped' => array('BOOL', 0),
1679 'topic_bumper' => array('UINT', 0),
1680 'poll_title' => array('STEXT_UNI', ''),
1681 'poll_start' => array('TIMESTAMP', 0),
1682 'poll_length' => array('TIMESTAMP', 0),
1683 'poll_max_options' => array('TINT:4', 1),
1684 'poll_last_vote' => array('TIMESTAMP', 0),
1685 'poll_vote_change' => array('BOOL', 0),
1686 ),
1687 'PRIMARY_KEY' => 'topic_id',
1688 'KEYS' => array(
1689 'forum_id' => array('INDEX', 'forum_id'),
1690 'forum_id_type' => array('INDEX', array('forum_id', 'topic_type')),
1691 'last_post_time' => array('INDEX', 'topic_last_post_time'),
1692 'topic_approved' => array('INDEX', 'topic_approved'),
1693 'forum_appr_last' => array('INDEX', array('forum_id', 'topic_approved', 'topic_last_post_id')),
1694 'fid_time_moved' => array('INDEX', array('forum_id', 'topic_last_post_time', 'topic_moved_id')),
1695 ),
1696 );
1697
1698 $schema_data['phpbb_topics_track'] = array(
1699 'COLUMNS' => array(
1700 'user_id' => array('UINT', 0),
1701 'topic_id' => array('UINT', 0),
1702 'forum_id' => array('UINT', 0),
1703 'mark_time' => array('TIMESTAMP', 0),
1704 ),
1705 'PRIMARY_KEY' => array('user_id', 'topic_id'),
1706 'KEYS' => array(
1707 'forum_id' => array('INDEX', 'forum_id'),
1708 ),
1709 );
1710
1711 $schema_data['phpbb_topics_posted'] = array(
1712 'COLUMNS' => array(
1713 'user_id' => array('UINT', 0),
1714 'topic_id' => array('UINT', 0),
1715 'topic_posted' => array('BOOL', 0),
1716 ),
1717 'PRIMARY_KEY' => array('user_id', 'topic_id'),
1718 );
1719
1720 $schema_data['phpbb_topics_watch'] = array(
1721 'COLUMNS' => array(
1722 'topic_id' => array('UINT', 0),
1723 'user_id' => array('UINT', 0),
1724 'notify_status' => array('BOOL', 0),
1725 ),
1726 'KEYS' => array(
1727 'topic_id' => array('INDEX', 'topic_id'),
1728 'user_id' => array('INDEX', 'user_id'),
1729 'notify_stat' => array('INDEX', 'notify_status'),
1730 ),
1731 );
1732
1733 $schema_data['phpbb_user_group'] = array(
1734 'COLUMNS' => array(
1735 'group_id' => array('UINT', 0),
1736 'user_id' => array('UINT', 0),
1737 'group_leader' => array('BOOL', 0),
1738 'user_pending' => array('BOOL', 1),
1739 ),
1740 'KEYS' => array(
1741 'group_id' => array('INDEX', 'group_id'),
1742 'user_id' => array('INDEX', 'user_id'),
1743 'group_leader' => array('INDEX', 'group_leader'),
1744 ),
1745 );
1746
1747 $schema_data['phpbb_users'] = array(
1748 'COLUMNS' => array(
1749 'user_id' => array('UINT', NULL, 'auto_increment'),
1750 'user_type' => array('TINT:2', 0),
1751 'group_id' => array('UINT', 3),
1752 'user_permissions' => array('MTEXT', ''),
1753 'user_perm_from' => array('UINT', 0),
1754 'user_ip' => array('VCHAR:40', ''),
1755 'user_regdate' => array('TIMESTAMP', 0),
1756 'username' => array('VCHAR_CI', ''),
1757 'username_clean' => array('VCHAR_CI', ''),
1758 'user_password' => array('VCHAR_UNI:40', ''),
1759 'user_passchg' => array('TIMESTAMP', 0),
1760 'user_pass_convert' => array('BOOL', 0),
1761 'user_email' => array('VCHAR_UNI:100', ''),
1762 'user_email_hash' => array('BINT', 0),
1763 'user_birthday' => array('VCHAR:10', ''),
1764 'user_lastvisit' => array('TIMESTAMP', 0),
1765 'user_lastmark' => array('TIMESTAMP', 0),
1766 'user_lastpost_time' => array('TIMESTAMP', 0),
1767 'user_lastpage' => array('VCHAR_UNI:200', ''),
1768 'user_last_confirm_key' => array('VCHAR:10', ''),
1769 'user_last_search' => array('TIMESTAMP', 0),
1770 'user_warnings' => array('TINT:4', 0),
1771 'user_last_warning' => array('TIMESTAMP', 0),
1772 'user_login_attempts' => array('TINT:4', 0),
1773 'user_inactive_reason' => array('TINT:2', 0),
1774 'user_inactive_time' => array('TIMESTAMP', 0),
1775 'user_posts' => array('UINT', 0),
1776 'user_lang' => array('VCHAR:30', ''),
1777 'user_timezone' => array('DECIMAL', 0),
1778 'user_dst' => array('BOOL', 0),
1779 'user_dateformat' => array('VCHAR_UNI:30', 'd M Y H:i'),
1780 'user_style' => array('TINT:4', 0),
1781 'user_rank' => array('UINT', 0),
1782 'user_colour' => array('VCHAR:6', ''),
1783 'user_new_privmsg' => array('TINT:4', 0),
1784 'user_unread_privmsg' => array('TINT:4', 0),
1785 'user_last_privmsg' => array('TIMESTAMP', 0),
1786 'user_message_rules' => array('BOOL', 0),
1787 'user_full_folder' => array('INT:11', -3),
1788 'user_emailtime' => array('TIMESTAMP', 0),
1789 'user_topic_show_days' => array('USINT', 0),
1790 'user_topic_sortby_type' => array('VCHAR:1', 't'),
1791 'user_topic_sortby_dir' => array('VCHAR:1', 'd'),
1792 'user_post_show_days' => array('USINT', 0),
1793 'user_post_sortby_type' => array('VCHAR:1', 't'),
1794 'user_post_sortby_dir' => array('VCHAR:1', 'a'),
1795 'user_notify' => array('BOOL', 0),
1796 'user_notify_pm' => array('BOOL', 1),
1797 'user_notify_type' => array('TINT:4', 0),
1798 'user_allow_pm' => array('BOOL', 1),
1799 'user_allow_viewonline' => array('BOOL', 1),
1800 'user_allow_viewemail' => array('BOOL', 1),
1801 'user_allow_massemail' => array('BOOL', 1),
1802 'user_options' => array('UINT:11', 895),
1803 'user_avatar' => array('VCHAR', ''),
1804 'user_avatar_type' => array('TINT:2', 0),
1805 'user_avatar_width' => array('USINT', 0),
1806 'user_avatar_height' => array('USINT', 0),
1807 'user_sig' => array('MTEXT_UNI', ''),
1808 'user_sig_bbcode_uid' => array('VCHAR:5', ''),
1809 'user_sig_bbcode_bitfield' => array('VCHAR:255', ''),
1810 'user_from' => array('VCHAR_UNI:100', ''),
1811 'user_icq' => array('VCHAR:15', ''),
1812 'user_aim' => array('VCHAR_UNI', ''),
1813 'user_yim' => array('VCHAR_UNI', ''),
1814 'user_msnm' => array('VCHAR_UNI', ''),
1815 'user_jabber' => array('VCHAR_UNI', ''),
1816 'user_website' => array('VCHAR_UNI:200', ''),
1817 'user_occ' => array('TEXT_UNI', ''),
1818 'user_interests' => array('TEXT_UNI', ''),
1819 'user_actkey' => array('VCHAR:32', ''),
1820 'user_newpasswd' => array('VCHAR_UNI:32', ''),
1821 ),
1822 'PRIMARY_KEY' => 'user_id',
1823 'KEYS' => array(
1824 'user_birthday' => array('INDEX', 'user_birthday'),
1825 'user_email_hash' => array('INDEX', 'user_email_hash'),
1826 'user_type' => array('INDEX', 'user_type'),
1827 'username_clean' => array('UNIQUE', 'username_clean'),
1828 ),
1829 );
1830
1831 $schema_data['phpbb_warnings'] = array(
1832 'COLUMNS' => array(
1833 'warning_id' => array('UINT', NULL, 'auto_increment'),
1834 'user_id' => array('UINT', 0),
1835 'post_id' => array('UINT', 0),
1836 'log_id' => array('UINT', 0),
1837 'warning_time' => array('TIMESTAMP', 0),
1838 ),
1839 'PRIMARY_KEY' => 'warning_id',
1840 );
1841
1842 $schema_data['phpbb_words'] = array(
1843 'COLUMNS' => array(
1844 'word_id' => array('UINT', NULL, 'auto_increment'),
1845 'word' => array('VCHAR_UNI', ''),
1846 'replacement' => array('VCHAR_UNI', ''),
1847 ),
1848 'PRIMARY_KEY' => 'word_id',
1849 );
1850
1851 $schema_data['phpbb_zebra'] = array(
1852 'COLUMNS' => array(
1853 'user_id' => array('UINT', 0),
1854 'zebra_id' => array('UINT', 0),
1855 'friend' => array('BOOL', 0),
1856 'foe' => array('BOOL', 0),
1857 ),
1858 'PRIMARY_KEY' => array('user_id', 'zebra_id'),
1859 );
1860
1861 return $schema_data;
1862 }
1863
1864
1865 /**
1866 * Data put into the header for various dbms
1867 */
1868 function custom_data($dbms)
1869 {
1870 switch ($dbms)
1871 {
1872 case 'oracle':
1873 return <<<EOF
1874 /*
1875 This first section is optional, however its probably the best method
1876 of running phpBB on Oracle. If you already have a tablespace and user created
1877 for phpBB you can leave this section commented out!
1878
1879 The first set of statements create a phpBB tablespace and a phpBB user,
1880 make sure you change the password of the phpBB user before you run this script!!
1881 */
1882
1883 /*
1884 CREATE TABLESPACE "PHPBB"
1885 LOGGING
1886 DATAFILE 'E:\ORACLE\ORADATA\LOCAL\PHPBB.ora'
1887 SIZE 10M
1888 AUTOEXTEND ON NEXT 10M
1889 MAXSIZE 100M;
1890
1891 CREATE USER "PHPBB"
1892 PROFILE "DEFAULT"
1893 IDENTIFIED BY "phpbb_password"
1894 DEFAULT TABLESPACE "PHPBB"
1895 QUOTA UNLIMITED ON "PHPBB"
1896 ACCOUNT UNLOCK;
1897
1898 GRANT ANALYZE ANY TO "PHPBB";
1899 GRANT CREATE SEQUENCE TO "PHPBB";
1900 GRANT CREATE SESSION TO "PHPBB";
1901 GRANT CREATE TABLE TO "PHPBB";
1902 GRANT CREATE TRIGGER TO "PHPBB";
1903 GRANT CREATE VIEW TO "PHPBB";
1904 GRANT "CONNECT" TO "PHPBB";
1905
1906 COMMIT;
1907 DISCONNECT;
1908
1909 CONNECT phpbb/phpbb_password;
1910 */
1911 EOF;
1912
1913 break;
1914
1915 case 'postgres':
1916 return <<<EOF
1917 /*
1918 Domain definition
1919 */
1920 CREATE DOMAIN varchar_ci AS varchar(255) NOT NULL DEFAULT ''::character varying;
1921
1922 /*
1923 Operation Functions
1924 */
1925 CREATE FUNCTION _varchar_ci_equal(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) = LOWER($2)' LANGUAGE SQL STRICT;
1926 CREATE FUNCTION _varchar_ci_not_equal(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) != LOWER($2)' LANGUAGE SQL STRICT;
1927 CREATE FUNCTION _varchar_ci_less_than(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) < LOWER($2)' LANGUAGE SQL STRICT;
1928 CREATE FUNCTION _varchar_ci_less_equal(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) <= LOWER($2)' LANGUAGE SQL STRICT;
1929 CREATE FUNCTION _varchar_ci_greater_than(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) > LOWER($2)' LANGUAGE SQL STRICT;
1930 CREATE FUNCTION _varchar_ci_greater_equals(varchar_ci, varchar_ci) RETURNS boolean AS 'SELECT LOWER($1) >= LOWER($2)' LANGUAGE SQL STRICT;
1931
1932 /*
1933 Operators
1934 */
1935 CREATE OPERATOR <(
1936 PROCEDURE = _varchar_ci_less_than,
1937 LEFTARG = varchar_ci,
1938 RIGHTARG = varchar_ci,
1939 COMMUTATOR = >,
1940 NEGATOR = >=,
1941 RESTRICT = scalarltsel,
1942 JOIN = scalarltjoinsel);
1943
1944 CREATE OPERATOR <=(
1945 PROCEDURE = _varchar_ci_less_equal,
1946 LEFTARG = varchar_ci,
1947 RIGHTARG = varchar_ci,
1948 COMMUTATOR = >=,
1949 NEGATOR = >,
1950 RESTRICT = scalarltsel,
1951 JOIN = scalarltjoinsel);
1952
1953 CREATE OPERATOR >(
1954 PROCEDURE = _varchar_ci_greater_than,
1955 LEFTARG = varchar_ci,
1956 RIGHTARG = varchar_ci,
1957 COMMUTATOR = <,
1958 NEGATOR = <=,
1959 RESTRICT = scalargtsel,
1960 JOIN = scalargtjoinsel);
1961
1962 CREATE OPERATOR >=(
1963 PROCEDURE = _varchar_ci_greater_equals,
1964 LEFTARG = varchar_ci,
1965 RIGHTARG = varchar_ci,
1966 COMMUTATOR = <=,
1967 NEGATOR = <,
1968 RESTRICT = scalargtsel,
1969 JOIN = scalargtjoinsel);
1970
1971 CREATE OPERATOR <>(
1972 PROCEDURE = _varchar_ci_not_equal,
1973 LEFTARG = varchar_ci,
1974 RIGHTARG = varchar_ci,
1975 COMMUTATOR = <>,
1976 NEGATOR = =,
1977 RESTRICT = neqsel,
1978 JOIN = neqjoinsel);
1979
1980 CREATE OPERATOR =(
1981 PROCEDURE = _varchar_ci_equal,
1982 LEFTARG = varchar_ci,
1983 RIGHTARG = varchar_ci,
1984 COMMUTATOR = =,
1985 NEGATOR = <>,
1986 RESTRICT = eqsel,
1987 JOIN = eqjoinsel,
1988 HASHES,
1989 MERGES,
1990 SORT1= <);
1991
1992 EOF;
1993 break;
1994 }
1995
1996 return '';
1997 }
1998
1999 ?>
phpbb is a php-based BBS (buletin board system)