Mercurial > hg > vamp-website

annotate forum/Sources/DbExtra-postgresql.php @ 87:df86d318892b website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Link to SampleType doc
author Chris Cannam
date Mon, 10 Feb 2014 18:11:48 +0000
parents e3e11437ecea
children
rev   line source
Chris@76 1 <?php
Chris@76 2
Chris@76 3 /**
Chris@76 4 * Simple Machines Forum (SMF)
Chris@76 5 *
Chris@76 6 * @package SMF
Chris@76 7 * @author Simple Machines http://www.simplemachines.org
Chris@76 8 * @copyright 2011 Simple Machines
Chris@76 9 * @license http://www.simplemachines.org/about/smf/license.php BSD
Chris@76 10 *
Chris@76 11 * @version 2.0
Chris@76 12 */
Chris@76 13
Chris@76 14 if (!defined('SMF'))
Chris@76 15 die('Hacking attempt...');
Chris@76 16
Chris@76 17 /* This file contains rarely used extended database functionality.
Chris@76 18
Chris@76 19 void db_extra_init()
Chris@76 20 - add this file's functions to the $smcFunc array.
Chris@76 21
Chris@76 22 resource smf_db_backup_table($table, $backup_table)
Chris@76 23 - backup $table to $backup_table.
Chris@76 24 - returns the request handle to the table creation query
Chris@76 25
Chris@76 26 string function smf_db_get_version()
Chris@76 27 - get the version number.
Chris@76 28
Chris@76 29 string db_insert_sql(string table_name)
Chris@76 30 - gets all the necessary INSERTs for the table named table_name.
Chris@76 31 - goes in 250 row segments.
Chris@76 32 - returns the query to insert the data back in.
Chris@76 33 - returns an empty string if the table was empty.
Chris@76 34
Chris@76 35 array smf_db_list_tables($db = false, $filter = false)
Chris@76 36 - lists all tables in the database
Chris@76 37 - could be filtered according to $filter
Chris@76 38 - returns an array of table names. (strings)
Chris@76 39
Chris@76 40 float smf_db_optimize_table($table)
Chris@76 41 - optimize a table
Chris@76 42 - $table - the table to be optimized
Chris@76 43 - returns how much it was gained
Chris@76 44
Chris@76 45 string db_table_sql(string table_name)
Chris@76 46 - dumps the CREATE for the specified table. (by table_name.)
Chris@76 47 - returns the CREATE statement.
Chris@76 48
Chris@76 49 */
Chris@76 50
Chris@76 51 // Add the file functions to the $smcFunc array.
Chris@76 52 function db_extra_init()
Chris@76 53 {
Chris@76 54 global $smcFunc;
Chris@76 55
Chris@76 56 if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
Chris@76 57 $smcFunc += array(
Chris@76 58 'db_backup_table' => 'smf_db_backup_table',
Chris@76 59 'db_optimize_table' => 'smf_db_optimize_table',
Chris@76 60 'db_insert_sql' => 'smf_db_insert_sql',
Chris@76 61 'db_table_sql' => 'smf_db_table_sql',
Chris@76 62 'db_list_tables' => 'smf_db_list_tables',
Chris@76 63 'db_get_version' => 'smf_db_get_version',
Chris@76 64 );
Chris@76 65 }
Chris@76 66
Chris@76 67 // Backup $table to $backup_table.
Chris@76 68 function smf_db_backup_table($table, $backup_table)
Chris@76 69 {
Chris@76 70 global $smcFunc, $db_prefix;
Chris@76 71
Chris@76 72 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 73
Chris@76 74 // Do we need to drop it first?
Chris@76 75 $tables = smf_db_list_tables(false, $backup_table);
Chris@76 76 if (!empty($tables))
Chris@76 77 $smcFunc['db_query']('', '
Chris@76 78 DROP TABLE {raw:backup_table}',
Chris@76 79 array(
Chris@76 80 'backup_table' => $backup_table,
Chris@76 81 )
Chris@76 82 );
Chris@76 83
Chris@76 84 //!!! Should we create backups of sequences as well?
Chris@76 85 $smcFunc['db_query']('', '
Chris@76 86 CREATE TABLE {raw:backup_table}
Chris@76 87 (
Chris@76 88 LIKE {raw:table}
Chris@76 89 INCLUDING DEFAULTS
Chris@76 90 )',
Chris@76 91 array(
Chris@76 92 'backup_table' => $backup_table,
Chris@76 93 'table' => $table,
Chris@76 94 )
Chris@76 95 );
Chris@76 96 $smcFunc['db_query']('', '
Chris@76 97 INSERT INTO {raw:backup_table}
Chris@76 98 SELECT * FROM {raw:table}',
Chris@76 99 array(
Chris@76 100 'backup_table' => $backup_table,
Chris@76 101 'table' => $table,
Chris@76 102 )
Chris@76 103 );
Chris@76 104 }
Chris@76 105
Chris@76 106 // Optimize a table - return data freed!
Chris@76 107 function smf_db_optimize_table($table)
Chris@76 108 {
Chris@76 109 global $smcFunc, $db_prefix;
Chris@76 110
Chris@76 111 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 112
Chris@76 113 $request = $smcFunc['db_query']('', '
Chris@76 114 VACUUM ANALYZE {raw:table}',
Chris@76 115 array(
Chris@76 116 'table' => $table,
Chris@76 117 )
Chris@76 118 );
Chris@76 119 if (!$request)
Chris@76 120 return -1;
Chris@76 121
Chris@76 122 $row = $smcFunc['db_fetch_assoc']($request);
Chris@76 123 $smcFunc['db_free_result']($request);
Chris@76 124
Chris@76 125 if (isset($row['Data_free']))
Chris@76 126 return $row['Data_free'] / 1024;
Chris@76 127 else
Chris@76 128 return 0;
Chris@76 129 }
Chris@76 130
Chris@76 131 // List all the tables in the database.
Chris@76 132 function smf_db_list_tables($db = false, $filter = false)
Chris@76 133 {
Chris@76 134 global $smcFunc;
Chris@76 135
Chris@76 136 $request = $smcFunc['db_query']('', '
Chris@76 137 SELECT tablename
Chris@76 138 FROM pg_tables
Chris@76 139 WHERE schemaname = {string:schema_public}' . ($filter == false ? '' : '
Chris@76 140 AND tablename LIKE {string:filter}') . '
Chris@76 141 ORDER BY tablename',
Chris@76 142 array(
Chris@76 143 'schema_public' => 'public',
Chris@76 144 'filter' => $filter,
Chris@76 145 )
Chris@76 146 );
Chris@76 147
Chris@76 148 $tables = array();
Chris@76 149 while ($row = $smcFunc['db_fetch_row']($request))
Chris@76 150 $tables[] = $row[0];
Chris@76 151 $smcFunc['db_free_result']($request);
Chris@76 152
Chris@76 153 return $tables;
Chris@76 154 }
Chris@76 155
Chris@76 156 // Get the content (INSERTs) for a table.
Chris@76 157 function smf_db_insert_sql($tableName)
Chris@76 158 {
Chris@76 159 global $smcFunc, $db_prefix;
Chris@76 160
Chris@76 161 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76 162
Chris@76 163 // This will be handy...
Chris@76 164 $crlf = "\r\n";
Chris@76 165
Chris@76 166 // Get everything from the table.
Chris@76 167 $result = $smcFunc['db_query']('', '
Chris@76 168 SELECT *
Chris@76 169 FROM {raw:table}',
Chris@76 170 array(
Chris@76 171 'table' => $tableName,
Chris@76 172 )
Chris@76 173 );
Chris@76 174
Chris@76 175 // The number of rows, just for record keeping and breaking INSERTs up.
Chris@76 176 $num_rows = $smcFunc['db_num_rows']($result);
Chris@76 177
Chris@76 178 if ($num_rows == 0)
Chris@76 179 return '';
Chris@76 180
Chris@76 181 $fields = array_keys($smcFunc['db_fetch_assoc']($result));
Chris@76 182 $smcFunc['db_data_seek']($result, 0);
Chris@76 183
Chris@76 184 // Start it off with the basic INSERT INTO.
Chris@76 185 $data = '';
Chris@76 186 $insert_msg = $crlf . 'INSERT INTO ' . $tableName . $crlf . "\t" . '(' . implode(', ', $fields) . ')' . $crlf . 'VALUES ' . $crlf . "\t";
Chris@76 187
Chris@76 188 // Loop through each row.
Chris@76 189 while ($row = $smcFunc['db_fetch_row']($result))
Chris@76 190 {
Chris@76 191 // Get the fields in this row...
Chris@76 192 $field_list = array();
Chris@76 193 for ($j = 0; $j < $smcFunc['db_num_fields']($result); $j++)
Chris@76 194 {
Chris@76 195 // Try to figure out the type of each field. (NULL, number, or 'string'.)
Chris@76 196 if (!isset($row[$j]))
Chris@76 197 $field_list[] = 'NULL';
Chris@76 198 elseif (is_numeric($row[$j]) && (int) $row[$j] == $row[$j])
Chris@76 199 $field_list[] = $row[$j];
Chris@76 200 else
Chris@76 201 $field_list[] = '\'' . $smcFunc['db_escape_string']($row[$j]) . '\'';
Chris@76 202 }
Chris@76 203
Chris@76 204 // 'Insert' the data.
Chris@76 205 $data .= $insert_msg . '(' . implode(', ', $field_list) . ');';
Chris@76 206 }
Chris@76 207 $smcFunc['db_free_result']($result);
Chris@76 208
Chris@76 209 // Return an empty string if there were no rows.
Chris@76 210 return $num_rows == 0 ? '' : $data;
Chris@76 211 }
Chris@76 212
Chris@76 213 // Get the schema (CREATE) for a table.
Chris@76 214 function smf_db_table_sql($tableName)
Chris@76 215 {
Chris@76 216 global $smcFunc, $db_prefix;
Chris@76 217
Chris@76 218 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76 219
Chris@76 220 // This will be needed...
Chris@76 221 $crlf = "\r\n";
Chris@76 222
Chris@76 223 // Start the create table...
Chris@76 224 $schema_create = 'CREATE TABLE ' . $tableName . ' (' . $crlf;
Chris@76 225 $index_create = '';
Chris@76 226 $seq_create = '';
Chris@76 227
Chris@76 228 // Find all the fields.
Chris@76 229 $result = $smcFunc['db_query']('', '
Chris@76 230 SELECT column_name, column_default, is_nullable, data_type, character_maximum_length
Chris@76 231 FROM information_schema.columns
Chris@76 232 WHERE table_name = {string:table}
Chris@76 233 ORDER BY ordinal_position',
Chris@76 234 array(
Chris@76 235 'table' => $tableName,
Chris@76 236 )
Chris@76 237 );
Chris@76 238 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 239 {
Chris@76 240 if ($row['data_type'] == 'character varying')
Chris@76 241 $row['data_type'] = 'varchar';
Chris@76 242 elseif ($row['data_type'] == 'character')
Chris@76 243 $row['data_type'] = 'char';
Chris@76 244 if ($row['character_maximum_length'])
Chris@76 245 $row['data_type'] .= '(' . $row['character_maximum_length'] . ')';
Chris@76 246
Chris@76 247 // Make the CREATE for this column.
Chris@76 248 $schema_create .= ' "' . $row['column_name'] . '" ' . $row['data_type'] . ($row['is_nullable'] != 'YES' ? ' NOT NULL' : '');
Chris@76 249
Chris@76 250 // Add a default...?
Chris@76 251 if (trim($row['column_default']) != '')
Chris@76 252 {
Chris@76 253 $schema_create .= ' default ' . $row['column_default'] . '';
Chris@76 254
Chris@76 255 // Auto increment?
Chris@76 256 if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
Chris@76 257 {
Chris@76 258 // Get to find the next variable first!
Chris@76 259 $count_req = $smcFunc['db_query']('', '
Chris@76 260 SELECT MAX("{raw:column}")
Chris@76 261 FROM {raw:table}',
Chris@76 262 array(
Chris@76 263 'column' => $row['column_name'],
Chris@76 264 'table' => $tableName,
Chris@76 265 )
Chris@76 266 );
Chris@76 267 list ($max_ind) = $smcFunc['db_fetch_row']($count_req);
Chris@76 268 $smcFunc['db_free_result']($count_req);
Chris@76 269 // Get the right bloody start!
Chris@76 270 $seq_create .= 'CREATE SEQUENCE ' . $matches[1] . ' START WITH ' . ($max_ind + 1) . ';' . $crlf . $crlf;
Chris@76 271 }
Chris@76 272 }
Chris@76 273
Chris@76 274 $schema_create .= ',' . $crlf;
Chris@76 275 }
Chris@76 276 $smcFunc['db_free_result']($result);
Chris@76 277
Chris@76 278 // Take off the last comma.
Chris@76 279 $schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
Chris@76 280
Chris@76 281 $result = $smcFunc['db_query']('', '
Chris@76 282 SELECT CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary, pg_get_indexdef(i.indexrelid) AS inddef
Chris@76 283 FROM pg_class AS c
Chris@76 284 INNER JOIN pg_index AS i ON (i.indrelid = c.oid)
Chris@76 285 INNER JOIN pg_class AS c2 ON (c2.oid = i.indexrelid)
Chris@76 286 WHERE c.relname = {string:table}',
Chris@76 287 array(
Chris@76 288 'table' => $tableName,
Chris@76 289 )
Chris@76 290 );
Chris@76 291 $indexes = array();
Chris@76 292 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 293 {
Chris@76 294 if ($row['is_primary'])
Chris@76 295 {
Chris@76 296 if (preg_match('~\(([^\)]+?)\)~i', $row['inddef'], $matches) == 0)
Chris@76 297 continue;
Chris@76 298
Chris@76 299 $index_create .= $crlf . 'ALTER TABLE ' . $tableName . ' ADD PRIMARY KEY ("' . $matches[1] . '");';
Chris@76 300 }
Chris@76 301 else
Chris@76 302 $index_create .= $crlf . $row['inddef'] . ';';
Chris@76 303 }
Chris@76 304 $smcFunc['db_free_result']($result);
Chris@76 305
Chris@76 306 // Finish it off!
Chris@76 307 $schema_create .= $crlf . ');';
Chris@76 308
Chris@76 309 return $seq_create . $schema_create . $index_create;
Chris@76 310 }
Chris@76 311
Chris@76 312 // Get the version number.
Chris@76 313 function smf_db_get_version()
Chris@76 314 {
Chris@76 315 global $smcFunc;
Chris@76 316
Chris@76 317 $request = $smcFunc['db_query']('', '
Chris@76 318 SHOW server_version',
Chris@76 319 array(
Chris@76 320 )
Chris@76 321 );
Chris@76 322 list ($ver) = $smcFunc['db_fetch_row']($request);
Chris@76 323 $smcFunc['db_free_result']($request);
Chris@76 324
Chris@76 325 return $ver;
Chris@76 326 }
Chris@76 327
Chris@76 328 ?>