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