123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456 |
- <?php
- if (!defined('SMF'))
- die('No direct access...');
- function db_extra_init()
- {
- global $smcFunc;
- if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
- $smcFunc += array(
- 'db_backup_table' => 'smf_db_backup_table',
- 'db_optimize_table' => 'smf_db_optimize_table',
- 'db_insert_sql' => 'smf_db_insert_sql',
- 'db_table_sql' => 'smf_db_table_sql',
- 'db_list_tables' => 'smf_db_list_tables',
- 'db_get_version' => 'smf_db_get_version',
- );
- }
- function smf_db_backup_table($table, $backup_table)
- {
- global $smcFunc, $db_prefix;
- $table = str_replace('{db_prefix}', $db_prefix, $table);
-
- $smcFunc['db_query']('', '
- DROP TABLE IF EXISTS {raw:backup_table}',
- array(
- 'backup_table' => $backup_table,
- )
- );
-
- $result = $smcFunc['db_query']('', '
- CREATE TABLE {raw:backup_table} LIKE {raw:table}',
- array(
- 'backup_table' => $backup_table,
- 'table' => $table
- ));
-
- if ($result)
- {
- $request = $smcFunc['db_query']('', '
- INSERT INTO {raw:backup_table}
- SELECT *
- FROM {raw:table}',
- array(
- 'backup_table' => $backup_table,
- 'table' => $table
- ));
-
- if ($request)
- return $request;
- }
-
- $result = $smcFunc['db_query']('', '
- SHOW CREATE TABLE {raw:table}',
- array(
- 'table' => $table,
- )
- );
- list (, $create) = $smcFunc['db_fetch_row']($result);
- $smcFunc['db_free_result']($result);
- $create = preg_split('/[\n\r]/', $create);
- $auto_inc = '';
-
- $engine = 'MyISAM';
- $charset = '';
- $collate = '';
- foreach ($create as $k => $l)
- {
-
- if (strpos($l, 'auto_increment'))
- $auto_inc = trim($l);
-
- if (strpos($l, 'ENGINE') !== false || strpos($l, 'TYPE') !== false)
- {
-
- preg_match('~(ENGINE|TYPE)=(\w+)(\sDEFAULT)?(\sCHARSET=(\w+))?(\sCOLLATE=(\w+))?~', $l, $match);
- if (!empty($match[1]))
- $engine = $match[1];
- if (!empty($match[2]))
- $engine = $match[2];
- if (!empty($match[5]))
- $charset = $match[5];
- if (!empty($match[7]))
- $collate = $match[7];
- }
-
- if (strpos($l, 'KEY') === false)
- unset($create[$k]);
- }
- if (!empty($create))
- $create = '(
- ' . implode('
- ', $create) . ')';
- else
- $create = '';
- $request = $smcFunc['db_query']('', '
- CREATE TABLE {raw:backup_table} {raw:create}
- ENGINE={raw:engine}' . (empty($charset) ? '' : ' CHARACTER SET {raw:charset}' . (empty($collate) ? '' : ' COLLATE {raw:collate}')) . '
- SELECT *
- FROM {raw:table}',
- array(
- 'backup_table' => $backup_table,
- 'table' => $table,
- 'create' => $create,
- 'engine' => $engine,
- 'charset' => empty($charset) ? '' : $charset,
- 'collate' => empty($collate) ? '' : $collate,
- )
- );
- if ($auto_inc != '')
- {
- if (preg_match('~\`(.+?)\`\s~', $auto_inc, $match) != 0 && substr($auto_inc, -1, 1) == ',')
- $auto_inc = substr($auto_inc, 0, -1);
- $smcFunc['db_query']('', '
- ALTER TABLE {raw:backup_table}
- CHANGE COLUMN {raw:column_detail} {raw:auto_inc}',
- array(
- 'backup_table' => $backup_table,
- 'column_detail' => $match[1],
- 'auto_inc' => $auto_inc,
- )
- );
- }
- return $request;
- }
- function smf_db_optimize_table($table)
- {
- global $smcFunc, $db_prefix;
- $table = str_replace('{db_prefix}', $db_prefix, $table);
-
- $request = $smcFunc['db_query']('', '
- SHOW TABLE STATUS LIKE {string:table_name}',
- array(
- 'table_name' => str_replace('_', '\_', $table),
- )
- );
- $row = $smcFunc['db_fetch_assoc']($request);
- $smcFunc['db_free_result']($request);
- $data_before = isset($row['Data_free']) ? $row['Data_free'] : 0;
- $request = $smcFunc['db_query']('', '
- OPTIMIZE TABLE `{raw:table}`',
- array(
- 'table' => $table,
- )
- );
- if (!$request)
- return -1;
-
- $request = $smcFunc['db_query']('', '
- SHOW TABLE STATUS LIKE {string:table}',
- array(
- 'table' => str_replace('_', '\_', $table),
- )
- );
- $row = $smcFunc['db_fetch_assoc']($request);
- $smcFunc['db_free_result']($request);
- $total_change = isset($row['Data_free']) && $data_before > $row['Data_free'] ? $data_before / 1024 : 0;
- return $total_change;
- }
- function smf_db_list_tables($db = false, $filter = false)
- {
- global $db_name, $smcFunc;
- $db = $db == false ? $db_name : $db;
- $db = trim($db);
- $filter = $filter == false ? '' : ' LIKE \'' . $filter . '\'';
- $request = $smcFunc['db_query']('', '
- SHOW TABLES
- FROM `{raw:db}`
- {raw:filter}',
- array(
- 'db' => $db[0] == '`' ? strtr($db, array('`' => '')) : $db,
- 'filter' => $filter,
- )
- );
- $tables = array();
- while ($row = $smcFunc['db_fetch_row']($request))
- $tables[] = $row[0];
- $smcFunc['db_free_result']($request);
- return $tables;
- }
- function smf_db_insert_sql($tableName, $new_table = false)
- {
- global $smcFunc, $db_prefix;
- static $start = 0, $num_rows, $fields, $limit;
- if ($new_table)
- {
- $limit = strstr($tableName, 'log_') !== false ? 500 : 250;
- $start = 0;
- }
- $data = '';
- $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
-
- $crlf = "\r\n";
- $result = $smcFunc['db_query']('', '
- SELECT /*!40001 SQL_NO_CACHE */ *
- FROM `' . $tableName . '`
- LIMIT ' . $start . ', ' . $limit,
- array(
- 'security_override' => true,
- )
- );
-
- $num_rows = $smcFunc['db_num_rows']($result);
- if ($num_rows == 0)
- return '';
- if ($new_table)
- {
- $fields = array_keys($smcFunc['db_fetch_assoc']($result));
- $smcFunc['db_data_seek']($result, 0);
- }
-
- $data = 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
-
- while ($row = $smcFunc['db_fetch_assoc']($result))
- {
-
- $field_list = array();
- foreach ($row as $key => $item)
- {
-
- if (!isset($item))
- $field_list[] = 'NULL';
- elseif (is_numeric($item) && (int) $item == $item)
- $field_list[] = $item;
- else
- $field_list[] = '\'' . $smcFunc['db_escape_string']($item) . '\'';
- }
- $data .= '(' . implode(', ', $field_list) . ')' . ',' . $crlf . "\t";
- }
- $smcFunc['db_free_result']($result);
- $data = substr(trim($data), 0, -1) . ';' . $crlf . $crlf;
- $start += $limit;
- return $data;
- }
- function smf_db_table_sql($tableName)
- {
- global $smcFunc, $db_prefix;
- $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
-
- $crlf = "\r\n";
-
- $schema_create = 'DROP TABLE IF EXISTS `' . $tableName . '`;' . $crlf . $crlf;
-
- $schema_create .= 'CREATE TABLE `' . $tableName . '` (' . $crlf;
-
- $result = $smcFunc['db_query']('', '
- SHOW FIELDS
- FROM `{raw:table}`',
- array(
- 'table' => $tableName,
- )
- );
- while ($row = $smcFunc['db_fetch_assoc']($result))
- {
-
- $schema_create .= ' `' . $row['Field'] . '` ' . $row['Type'] . ($row['Null'] != 'YES' ? ' NOT NULL' : '');
-
- if (!empty($row['Default']) || $row['Null'] !== 'YES')
- {
-
- if ($row['Default'] == 'CURRENT_TIMESTAMP')
- $schema_create .= ' /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */';
-
- elseif ($row['Default'] !== null)
- {
-
- $type = strtolower($row['Type']);
- $isNumericColumn = strpos($type, 'int') !== false || strpos($type, 'bool') !== false || strpos($type, 'bit') !== false || strpos($type, 'float') !== false || strpos($type, 'double') !== false || strpos($type, 'decimal') !== false;
- $schema_create .= ' default ' . ($isNumericColumn ? $row['Default'] : '\'' . $smcFunc['db_escape_string']($row['Default']) . '\'');
- }
- }
-
- $schema_create .= ($row['Extra'] != '' ? ' ' . $row['Extra'] : '') . ',' . $crlf;
- }
- $smcFunc['db_free_result']($result);
-
- $schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
-
- $result = $smcFunc['db_query']('', '
- SHOW KEYS
- FROM `{raw:table}`',
- array(
- 'table' => $tableName,
- )
- );
- $indexes = array();
- while ($row = $smcFunc['db_fetch_assoc']($result))
- {
-
- $row['Key_name'] = $row['Key_name'] == 'PRIMARY' ? 'PRIMARY KEY' : (empty($row['Non_unique']) ? 'UNIQUE ' : ($row['Comment'] == 'FULLTEXT' || (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT') ? 'FULLTEXT ' : 'KEY ')) . '`' . $row['Key_name'] . '`';
-
- if (empty($indexes[$row['Key_name']]))
- $indexes[$row['Key_name']] = array();
-
- if (!empty($row['Sub_part']))
- $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`(' . $row['Sub_part'] . ')';
- else
- $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`';
- }
- $smcFunc['db_free_result']($result);
-
- foreach ($indexes as $keyname => $columns)
- {
-
- ksort($columns);
- $schema_create .= ',' . $crlf . ' ' . $keyname . ' (' . implode($columns, ', ') . ')';
- }
-
- $result = $smcFunc['db_query']('', '
- SHOW TABLE STATUS
- LIKE {string:table}',
- array(
- 'table' => strtr($tableName, array('_' => '\\_', '%' => '\\%')),
- )
- );
- $row = $smcFunc['db_fetch_assoc']($result);
- $smcFunc['db_free_result']($result);
-
- $schema_create .= $crlf . ') ENGINE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) . ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
- return $schema_create;
- }
- function smf_db_get_version()
- {
- global $smcFunc;
- $request = $smcFunc['db_query']('', '
- SELECT VERSION()',
- array(
- )
- );
- list ($ver) = $smcFunc['db_fetch_row']($request);
- $smcFunc['db_free_result']($request);
- return $ver;
- }
- ?>
|