Submitted by This article will explain how to use that file to create your own schema files.
Preparations
First of all, you need to download the script from here. Place it in a folder named "develop", in your phpbb root path. Before we can work with it, we need to do three little changes.
First, find this line, and add "//" before:
- Code: Select all
die("Please read the first lines of this script for instructions on how to enable it");
Then, find this line and adjust it if needed. Also make sure that folder exists.
- Code: Select all
$schema_path = './../install/schemas/';
Finally, find this line:
- Code: Select all
$fp = fopen($schema_path . '_' . $dbms . '_schema.sql', 'wt');
And replace it with:
- Code: Select all
$fp = fopen($schema_path . '' . $dbms . '_schema.sql', 'wt');
Modify the tables
If you scroll down a bit more, you find a function called get_schema_struct(). You can see the format of the tables and then remove them. Now we can add our own tables for our MOD.
Here is an example of a table for a pastebin.
- Code: Select all
$schema_data['phpbb_pastebin'] = array(
'COLUMNS' => array(
'paste_id' => array('UINT', NULL, 'auto_increment'),
'user_id' => array('UINT', 0),
'paste_time' => array('TIMESTAMP', 0),
'paste_text' => array('MTEXT_UNI', ''),
'paste_approved' => array('BOOL', 1),
),
'PRIMARY_KEY' => 'paste_id',
'KEYS' => array(
'user_id' => array('INDEX', 'user_id'),
),
);
$schema_data['phpbb_pastebin'] here you specify the table name, always use phpbb_ here, it will later be replaced by the $table_prefix.
'COLUMNS' contains an array. The keys are the column names, the value is an array of options. The first argument is the column type, the second is the default value, the third is 'auto_increment' for primary keys, 'true_sort' for utf8 sortable fields (topic_title) or else void.
'PRIMARY_KEY' is a column name or an array of column names to be used as primary key.
'KEYS' is an array with indexes and unique keys. The array key is the key name, the value is an array. The first entry of this array is either 'INDEX' or 'UNIQUE'. The second entry is the column name to be indexed.
The types are documented above the get_schema_struct() function. The most used types are:
- UINT - used for all types of ids
- BOOL - true/false
- VCHAR - used for simple names, filenames
- XSTEXT_UNI - 100 chars, topic_title
- STEXT_UNI - 255 chars, normal input type
- TEXT_UNI - 3000 chars, descriptions, comments
- MTEXT_UNI - large text, post_text
- TIMESTAMP - unix timestamp
Generating the SQL files
To generate the SQL files, first make sure the folder you specified for $schema_path exists. Now open up a webbrowser and go to develop/generate_schema_files.php of your phpbb installation. Your files should now be in $schema_path, for example install/schemas/mysql_41_schema.sql, install/schemas/oracle_schema.sql and so on.
Using the files in the MOD
Now we have the files, but how do we use them? For our MOD we need an installation script. This installation script will then load our newly created schema files, here is a function that can load these files. Simply pass the path of the schema folder as the first argument. You can specify the dbms optionally, by default the one from config.php is used.
- Code: Select all
/**
* Load a schema (and execute)
*
* @param string $install_path Path to folder containing schema files
* @param mixed $install_dbms Alternative database system than $dbms
*/
function load_schema($install_path = '', $install_dbms = false)
{
global $db;
global $table_prefix;
if ($install_dbms === false)
{
global $dbms;
$install_dbms = $dbms;
}
static $available_dbms = false;
if (!$available_dbms)
{
if (!function_exists('get_available_dbms'))
{
global $phpbb_root_path, $phpEx;
include($phpbb_root_path . 'includes/functions_install.' . $phpEx);
}
$available_dbms = get_available_dbms($install_dbms);
if ($install_dbms == 'mysql')
{
if (version_compare($db->sql_server_info(true), '4.1.3', '>='))
{
$available_dbms[$install_dbms]['SCHEMA'] .= '_41';
}
else
{
$available_dbms[$install_dbms]['SCHEMA'] .= '_40';
}
}
}
$remove_remarks = $available_dbms[$install_dbms]['COMMENTS'];
$delimiter = $available_dbms[$install_dbms]['DELIM'];
$dbms_schema = $install_path . $available_dbms[$install_dbms]['SCHEMA'] . '_schema.sql';
if (file_exists($dbms_schema))
{
$sql_query = @file_get_contents($dbms_schema);
$sql_query = preg_replace('#phpbb_#i', $table_prefix, $sql_query);
$remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, $delimiter);
foreach ($sql_query as $sql)
{
$db->sql_query($sql);
}
unset($sql_query);
}
if (file_exists($install_path . 'schema_data.sql'))
{
$sql_query = file_get_contents($install_path . 'schema_data.sql');
switch ($install_dbms)
{
case 'mssql':
case 'mssql_odbc':
$sql_query = preg_replace('#\# MSSQL IDENTITY (phpbb_[a-z_]+) (ON|OFF) \##s', 'SET IDENTITY_INSERT \1 \2;', $sql_query);
break;
case 'postgres':
$sql_query = preg_replace('#\# POSTGRES (BEGIN|COMMIT) \##s', '\1; ', $sql_query);
break;
}
$sql_query = preg_replace('#phpbb_#i', $table_prefix, $sql_query);
$sql_query = preg_replace_callback('#\{L_([A-Z0-9\-_]*)\}#s', 'adjust_language_keys_callback', $sql_query);
remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, ';');
foreach ($sql_query as $sql)
{
$db->sql_query($sql);
}
unset($sql_query);
}
}
You're done; you now have a MOD that supports multiple dbms for creating it's database tables.
