function db_table($tablename, $fields = "", $long_alias = "")
{
global $db_table_last;
global $db_data_model_create;
$db_table_last = $tablename;
if ($db_data_model_create) {
if (mysql) {
sql("\r\n CREATE TABLE `{$tablename}` (\r\n `id` int(11) NOT NULL auto_increment,\r\n PRIMARY KEY (`id`)\r\n ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8");
} else {
if (sqlite2) {
sql("CREATE TABLE {$tablename} (id INTEGER PRIMARY KEY NOT NULL)");
} else {
if (sqlite3 || pdo_sqlite) {
sql("CREATE TABLE {$tablename} (id INTEGER PRIMARY KEY NOT NULL)");
}
}
}
} else {
global $tables;
if ($long_alias == "") {
$long_alias = $tablename;
}
$tables[$tablename]['long_alias'] = $long_alias;
}
if (strlen($fields)) {
$fields = explode(",", $fields);
foreach ($fields as $field) {
db_field(trim($field));
}
}
}
function check_url()
{
global $hotspot_network;
$uamsecret = $hotspot_network[db_field('uamsecret')];
$md = $_GET['md'];
$check = (empty($_SERVER['HTTPS']) ? 'http' : 'https') . '://' . $_SERVER['SERVER_NAME'] . preg_replace('/&md=[^&=]+$/', '', $_SERVER['REQUEST_URI']);
$match = strtoupper(md5($check . $uamsecret));
if ($md == $match) {
return;
}
echo "Reply-Message: bad url or uamsecret [{$check} {$uamsecret}]\n";
exit;
}
$tables = [];
$tables[] = ['table_name' => '{db_prefix}characters', 'columns' => [db_field('id_character', 'int', 0, true, true), db_field('id_member', 'mediumint'), db_field('character_name', 'varchar', 255), db_field('avatar', 'varchar', 255), db_field('signature', 'text'), db_field('id_theme', 'tinyint'), db_field('posts', 'mediumint'), db_field('age', 'varchar', 255), db_field('date_created', 'int'), db_field('last_active', 'int'), db_field('is_main', 'tinyint'), db_field('main_char_group', 'smallint'), db_field('char_groups', 'varchar', 255), db_field('char_sheet', 'int'), db_field('char_title', 'varchar', 255), db_field('retired', 'tinyint', 3)], 'indexes' => [['columns' => ['id_character'], 'type' => 'primary'], ['columns' => ['id_member'], 'type' => 'index']]];
$tables[] = ['table_name' => '{db_prefix}character_sheet_comments', 'columns' => [db_field('id_comment', 'int', 0, true, true), db_field('id_character', 'int'), db_field('id_author', 'mediumint'), db_field('time_posted', 'int'), db_field('sheet_comment', 'text')], 'indexes' => [['columns' => ['id_comment'], 'type' => 'primary'], ['columns' => ['id_character', 'time_posted'], 'index']]];
$tables[] = ['table_name' => '{db_prefix}character_sheet_versions', 'columns' => [db_field('id_version', 'int', 0, true, true), db_field('sheet_text', 'mediumtext'), db_field('id_character', 'int'), db_field('id_member', 'mediumint'), db_field('created_time', 'int'), db_field('id_approver', 'mediumint'), db_field('approved_time', 'int'), db_field('approval_state', 'tinyint')], 'indexes' => [['columns' => ['id_version'], 'type' => 'primary'], ['columns' => ['id_character', 'id_approver'], 'type' => 'index']]];
$tables[] = ['table_name' => '{db_prefix}character_sheet_templates', 'columns' => [db_field('id_template', 'smallint', 0, true, true), db_field('template_name', 'varchar', 100), db_field('template', 'text'), db_field('position', 'smallint')], 'indexes' => [['columns' => ['id_template'], 'type' => 'primary']]];
// Oh joy, we've now made it to extra rows...
$rows = [];
// Now we can add a new column to an existing table
$columns = [];
$columns[] = ['table_name' => '{db_prefix}messages', 'column_info' => db_field('id_character', 'int'), 'parameters' => [], 'if_exists' => 'ignore', 'error' => 'fatal'];
$columns[] = ['table_name' => '{db_prefix}members', 'column_info' => db_field('current_character', 'int'), 'parameters' => [], 'if_exists' => 'ignore', 'error' => 'fatal'];
$columns[] = ['table_name' => '{db_prefix}members', 'column_info' => db_field('immersive_mode', 'tinyint', 3, true, false, 1), 'parameters' => [], 'if_exists' => 'ignore', 'error' => 'fatal'];
$columns[] = ['table_name' => '{db_prefix}log_online', 'column_info' => db_field('id_character', 'int'), 'parameters' => [], 'if_exists' => 'ignore', 'error' => 'fatal'];
$columns[] = ['table_name' => '{db_prefix}boards', 'column_info' => db_field('in_character', 'tinyint'), 'parameters' => [], 'if_exists' => 'ignore', 'error' => 'fatal'];
$columns[] = ['table_name' => '{db_prefix}membergroups', 'column_info' => db_field('is_character', 'tinyint'), 'parameters' => [], 'if_exists' => 'ignore', 'error' => 'fatal'];
$columns[] = ['table_name' => '{db_prefix}membergroups', 'column_info' => db_field('badge_order', 'smallint'), 'parameters' => [], 'if_exists' => 'ignore', 'error' => 'fatal'];
// Update mod settings if applicable
updateSettings($mod_settings);
// Create new tables, if any
foreach ($tables as $table) {
if (!isset($table['if_exists'])) {
$table['if_exists'] = 'ignore';
}
if (!isset($table['error'])) {
$table['error'] = 'fatal';
}
if (!isset($table['parameters'])) {
$table['parameters'] = [];
}
$smcFunc['db_create_table']($table['table_name'], $table['columns'], $table['indexes'], $table['parameters'], $table['if_exists'], $table['error']);
// Because of issues with SMF in 2.0 RC5 onwards, users coming from older installs may not have all columns as if_exists => update doesn't appear to work.
$tables[] = array('table_name' => '{db_prefix}helpdesk_relationships', 'columns' => array(db_field('primary_ticket', 'mediumint'), db_field('secondary_ticket', 'mediumint'), db_field('rel_type', 'tinyint')), 'indexes' => array(array('columns' => array('primary_ticket', 'secondary_ticket'), 'type' => 'primary'), array('columns' => array('primary_ticket', 'rel_type'))), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_custom_fields', 'columns' => array(db_field('id_field', 'smallint', 0, true, true), db_field('active', 'tinyint'), db_field('field_order', 'smallint'), db_field('field_name', 'varchar', 40), db_field('field_desc', 'text'), db_field('field_loc', 'tinyint'), db_field('icon', 'varchar', 20), db_field('field_type', 'tinyint'), db_field('field_length', 'smallint', 5, 255), db_field('field_options', 'text'), db_field('bbc', 'tinyint'), db_field('default_value', 'varchar', 255), db_field('can_see', 'varchar', 3, '0,0'), db_field('can_edit', 'varchar', 3, '0,0'), db_field('display_empty', 'tinyint'), db_field('placement', 'tinyint', 0, 1)), 'indexes' => array(array('columns' => array('id_field', 'active'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_custom_fields_values', 'columns' => array(db_field('id_post', 'int', 0, true, true), db_field('id_field', 'smallint'), db_field('value', 'text'), db_field('post_type', 'int')), 'indexes' => array(array('columns' => array('id_post', 'id_field'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_custom_fields_depts', 'columns' => array(db_field('id_field', 'smallint'), db_field('id_dept', 'smallint'), db_field('required', 'tinyint')), 'indexes' => array(array('columns' => array('id_field', 'id_dept'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_roles', 'columns' => array(db_field('id_role', 'smallint', 0, true, true), db_field('template', 'tinyint'), db_field('role_name', 'varchar', 80)), 'indexes' => array(array('columns' => array('id_role'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_role_groups', 'columns' => array(db_field('id_role', 'smallint'), db_field('id_group', 'smallint')), 'indexes' => array(array('columns' => array('id_role', 'id_group'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_role_permissions', 'columns' => array(db_field('id_role', 'smallint'), db_field('permission', 'varchar', 40), db_field('add_type', 'tinyint')), 'indexes' => array(array('columns' => array('id_role', 'permission'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_preferences', 'columns' => array(db_field('id_member', 'mediumint'), db_field('variable', 'varchar', 30), db_field('value', 'text')), 'indexes' => array(array('columns' => array('id_member', 'variable'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_depts', 'columns' => array(db_field('id_dept', 'smallint', 0, true, true), db_field('dept_name', 'varchar', 50), db_field('description', 'text'), db_field('board_cat', 'smallint'), db_field('before_after', 'tinyint'), db_field('dept_order', 'smallint'), db_field('dept_theme', 'tinyint'), db_field('autoclose_days', 'smallint')), 'indexes' => array(array('columns' => array('id_dept'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_dept_roles', 'columns' => array(db_field('id_role', 'smallint'), db_field('id_dept', 'smallint')), 'indexes' => array(array('columns' => array('id_role', 'id_dept'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_cannedreplies', 'columns' => array(db_field('id_reply', 'smallint', 0, true, true), db_field('id_cat', 'smallint'), db_field('title', 'varchar', 80), db_field('body', 'text'), db_field('vis_user', 'tinyint'), db_field('vis_staff', 'tinyint'), db_field('reply_order', 'smallint'), db_field('active', 'tinyint')), 'indexes' => array(array('columns' => array('id_reply'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_cannedreplies_cats', 'columns' => array(db_field('id_cat', 'smallint', 0, true, true), db_field('cat_name', 'varchar', 80), db_field('cat_order', 'smallint')), 'indexes' => array(array('columns' => array('id_cat'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_cannedreplies_depts', 'columns' => array(db_field('id_dept', 'smallint'), db_field('id_reply', 'smallint')), 'indexes' => array(array('columns' => array('id_dept', 'id_reply'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_notify_override', 'columns' => array(db_field('id_member', 'mediumint'), db_field('id_ticket', 'mediumint'), db_field('notify_state', 'tinyint')), 'indexes' => array(array('columns' => array('id_member', 'id_ticket'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_search_ticket_words', 'columns' => array(db_field('id_word', 'bigint'), db_field('id_msg', 'int')), 'indexes' => array(array('columns' => array('id_word', 'id_msg'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
$tables[] = array('table_name' => '{db_prefix}helpdesk_search_subject_words', 'columns' => array(db_field('id_word', 'bigint'), db_field('id_ticket', 'int')), 'indexes' => array(array('columns' => array('id_word', 'id_ticket'), 'type' => 'primary')), 'if_exists' => 'ignore', 'error' => 'fatal', 'parameters' => array());
// Oh joy, we've now made it to extra rows...
$rows = array();
$rows[] = array('method' => 'replace', 'table_name' => '{db_prefix}scheduled_tasks', 'columns' => array('next_time' => 'int', 'time_offset' => 'int', 'time_regularity' => 'int', 'time_unit' => 'string', 'disabled' => 'int', 'task' => 'string'), 'data' => array(strtotime('tomorrow'), 0, 1, 'd', 0, 'simpledesk'), 'keys' => array('task'));
// Another row we might want to add is package server. Except we may have to remove a pre-existing plugins one, because the version may be wrong.
$query = $smcFunc['db_query']('', '
DELETE FROM {db_prefix}package_servers
WHERE url LIKE {string:plugins}', array('plugins' => 'http://www.simpledesk.net/download%'));
$rows[] = array('method' => 'insert', 'table_name' => '{db_prefix}package_servers', 'columns' => array('name' => 'string', 'url' => 'string'), 'data' => array('SimpleDesk Plugins', 'http://www.simpledesk.net/download/plugins/2.0'), 'keys' => array('id_server'));
// Now we can add a new column to an existing table
$columns = array();
// Update mod settings if applicable
foreach ($mod_settings as $new_setting => $new_value) {
if (empty($modSettings[$new_setting])) {
updateSettings(array($new_setting => $new_value));
}
/**
* Parse query and replace placeholders with data
*
* @param string $query unparsed query
* @param array $data data for placeholders
* @return parsed query
*/
function db_process($pattern, $data = array(), $replace = true)
{
static $session_vars_updated = false;
$command = 'get';
$group_concat_len = 3000;
// 3Kb
// Check if query updates data in the database
if (preg_match("/^(UPDATE|INSERT INTO|REPLACE INTO|DELETE FROM) \\?\\:(\\w+) /", $pattern, $m)) {
$table_name = $m[2];
//str_replace(TABLE_PREFIX, '', $m[2]);
Registry::set_changed_tables($table_name);
Registry::register_cache('cached_queries', array(), CACHE_LEVEL_STATIC, true);
$cached_queries = Registry::if_get('cached_queries', array());
if (!empty($cached_queries)) {
foreach ($cached_queries as $cquery => $ctables) {
if (in_array($table_name, $ctables)) {
unset($cached_queries[$cquery]);
}
}
Registry::set('cached_queries', empty($cached_queries) ? array(0 => array()) : $cached_queries);
}
$command = $m[1] == 'DELETE FROM' ? 'delete' : 'set';
}
if (strpos($pattern, 'GROUP_CONCAT(') !== false && $session_vars_updated == false) {
db_query('SET SESSION group_concat_max_len = ?i', $group_concat_len);
$session_vars_updated = true;
}
if (!empty($data) && preg_match_all("/\\?(i|s|l|d|a|n|u|e|p|w|f)+/", $pattern, $m)) {
$offset = 0;
foreach ($m[0] as $k => $ph) {
if ($ph == '?u' || $ph == '?e') {
$data[$k] = fn_check_table_fields($data[$k], $table_name);
if (empty($data[$k])) {
return false;
}
}
if ($ph == '?i') {
// integer
$pattern = db_str_replace($ph, db_intval($data[$k]), $pattern, $offset);
// Trick to convert int's and longint's
} elseif ($ph == '?s') {
// string
$pattern = db_str_replace($ph, "'" . addslashes($data[$k]) . "'", $pattern, $offset);
} elseif ($ph == '?l') {
// string for LIKE operator
$pattern = db_str_replace($ph, "'" . addslashes(str_replace("\\", "\\\\", $data[$k])) . "'", $pattern, $offset);
} elseif ($ph == '?d') {
// float
$pattern = db_str_replace($ph, sprintf('%01.2f', $data[$k]), $pattern, $offset);
} elseif ($ph == '?a') {
// array FIXME: add trim
$data[$k] = !is_array($data[$k]) ? array($data[$k]) : $data[$k];
$pattern = db_str_replace($ph, "'" . implode("', '", array_map('addslashes', $data[$k])) . "'", $pattern, $offset);
} elseif ($ph == '?n') {
// array of integer FIXME: add trim
$data[$k] = !is_array($data[$k]) ? array($data[$k]) : $data[$k];
$pattern = db_str_replace($ph, !empty($data[$k]) ? implode(', ', array_map('db_intval', $data[$k])) : "''", $pattern, $offset);
} elseif ($ph == '?u' || $ph == '?w') {
// update/condition with and
$q = '';
$clue = $ph == '?u' ? ', ' : ' AND ';
foreach ($data[$k] as $field => $value) {
$q .= ($q ? $clue : '') . '`' . db_field($field) . "` = '" . addslashes($value) . "'";
}
$pattern = db_str_replace($ph, $q, $pattern, $offset);
} elseif ($ph == '?e') {
// insert
$pattern = db_str_replace($ph, '(`' . implode('`, `', array_map('addslashes', array_keys($data[$k]))) . "`) VALUES ('" . implode("', '", array_map('addslashes', array_values($data[$k]))) . "')", $pattern, $offset);
} elseif ($ph == '?f') {
// field/table/database name
$pattern = db_str_replace($ph, db_field($data[$k]), $pattern, $offset);
} elseif ($ph == '?p') {
// prepared statement
// $pattern = db_str_replace($ph, str_replace('?:', TABLE_PREFIX, $data[$k]), $pattern, $offset);
$pattern = db_str_replace($ph, $data[$k], $pattern, $offset);
}
}
}
if ($replace) {
if (Registry::is_exist('revisions') && !Registry::get('revisions.working')) {
if (strpos($pattern, 'SELECT') === 0) {
fn_revisions_process_select($pattern);
}
if (strpos($pattern, 'UPDATE') === 0) {
fn_revisions_process_update($pattern);
}
if (strpos($pattern, 'INSERT') === 0 || strpos($pattern, 'REPLACE') === 0) {
Registry::set('revisions.db_insert_id', 0);
fn_revisions_process_insert($pattern);
}
if (strpos($pattern, 'DELETE') === 0) {
fn_revisions_process_delete($pattern);
}
}
// Replace table prefixes
$pattern = str_replace('?:', TABLE_PREFIX, $pattern);
}
return $pattern;
}
