Mercurial > hg > vamp-website

comparison forum/Sources/DbExtra-sqlite.php @ 76:e3e11437ecea website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add forum code
author Chris Cannam
date Sun, 07 Jul 2013 11:25:48 +0200
parents
children
comparison
equal deleted inserted replaced
75:72f59aa7e503 76:e3e11437ecea
1 <?php
2
3 /**
4 * Simple Machines Forum (SMF)
5 *
6 * @package SMF
7 * @author Simple Machines http://www.simplemachines.org
8 * @copyright 2011 Simple Machines
9 * @license http://www.simplemachines.org/about/smf/license.php BSD
10 *
11 * @version 2.0
12 */
13
14 if (!defined('SMF'))
15 die('Hacking attempt...');
16
17 /* This file contains rarely used extended database functionality.
18
19 void db_extra_init()
20 - add this file's functions to the $smcFunc array.
21
22 resource smf_db_backup_table($table, $backup_table)
23 - backup $table to $backup_table.
24 - returns the request handle to the table creation query
25
26 string function smf_db_get_version()
27 - get the version number.
28
29 string db_insert_sql(string table_name)
30 - gets all the necessary INSERTs for the table named table_name.
31 - goes in 250 row segments.
32 - returns the query to insert the data back in.
33 - returns an empty string if the table was empty.
34
35 array smf_db_list_tables($db = false, $filter = false)
36 - lists all tables in the database
37 - could be filtered according to $filter
38 - returns an array of table names. (strings)
39
40 float smf_db_optimize_table($table)
41 - optimize a table
42 - $table - the table to be optimized
43 - returns how much it was gained
44
45 string db_table_sql(string table_name)
46 - dumps the CREATE for the specified table. (by table_name.)
47 - returns the CREATE statement.
48
49 */
50
51 // Add the file functions to the $smcFunc array.
52 function db_extra_init()
53 {
54 global $smcFunc;
55
56 if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
57 $smcFunc += array(
58 'db_backup_table' => 'smf_db_backup_table',
59 'db_optimize_table' => 'smf_db_optimize_table',
60 'db_insert_sql' => 'smf_db_insert_sql',
61 'db_table_sql' => 'smf_db_table_sql',
62 'db_list_tables' => 'smf_db_list_tables',
63 'db_get_backup' => 'smf_db_get_backup',
64 'db_get_version' => 'smf_db_get_version',
65 );
66 }
67
68 // Backup $table to $backup_table.
69 function smf_db_backup_table($table, $backup_table)
70 {
71 global $smcFunc, $db_prefix;
72
73 $table = str_replace('{db_prefix}', $db_prefix, $table);
74
75 $result = $smcFunc['db_query']('', '
76 SELECT sql
77 FROM sqlite_master
78 WHERE type = {string:txttable}
79 AND name = {string:table}',
80 array(
81 'table' => $table,
82 'txttable' => 'table'
83 )
84 );
85 list ($create) = $smcFunc['db_fetch_row']($result);
86 $smcFunc['db_free_result']($result);
87
88 $create = preg_split('/[\n\r]/', $create);
89 $auto_inc = '';
90
91 // Remove the first line and check to see if the second one contain useless info.
92 unset($create[0]);
93 if (trim($create[1]) == '(')
94 unset($create[1]);
95 if (trim($create[count($create)]) == ')')
96 unset($create[count($create)]);
97
98 foreach ($create as $k => $l)
99 {
100 // Get the name of the auto_increment column.
101 if (strpos($l, 'primary') || strpos($l, 'PRIMARY'))
102 $auto_inc = trim($l);
103
104 // Skip everything but keys...
105 if ((strpos($l, 'KEY') !== false && strpos($l, 'PRIMARY KEY') === false) || strpos($l, $table) !== false || strpos(trim($l), 'PRIMARY KEY') === 0)
106 unset($create[$k]);
107 }
108
109 if (!empty($create))
110 $create = '(
111 ' . implode('
112 ', $create) . ')';
113 else
114 $create = '';
115
116 // Is there an extra junk at the end?
117 if (substr($create, -2, 1) == ',')
118 $create = substr($create, 0, -2) . ')';
119 if (substr($create, -2) == '))')
120 $create = substr($create, 0, -1);
121
122 $smcFunc['db_query']('', '
123 DROP TABLE {raw:backup_table}',
124 array(
125 'backup_table' => $backup_table,
126 'db_error_skip' => true,
127 )
128 );
129
130 $request = $smcFunc['db_quote']('
131 CREATE TABLE {raw:backup_table} {raw:create}',
132 array(
133 'backup_table' => $backup_table,
134 'create' => $create,
135 ));
136
137 $smcFunc['db_query']('', '
138 CREATE TABLE {raw:backup_table} {raw:create}',
139 array(
140 'backup_table' => $backup_table,
141 'create' => $create,
142 ));
143
144 $request = $smcFunc['db_query']('', '
145 INSERT INTO {raw:backup_table}
146 SELECT *
147 FROM {raw:table}',
148 array(
149 'backup_table' => $backup_table,
150 'table' => $table,
151 ));
152
153 return $request;
154 }
155
156 // Optimize a table - return data freed!
157 function smf_db_optimize_table($table)
158 {
159 global $smcFunc, $db_prefix;
160
161 $table = str_replace('{db_prefix}', $db_prefix, $table);
162
163 $request = $smcFunc['db_query']('', '
164 VACUUM {raw:table}',
165 array(
166 'table' => $table,
167 )
168 );
169 if (!$request)
170 return -1;
171
172 $row = $smcFunc['db_fetch_assoc']($request);
173 $smcFunc['db_free_result']($request);
174
175 // The function returns nothing.
176 return 0;
177 }
178
179 // List all the tables in the database.
180 function smf_db_list_tables($db = false, $filter = false)
181 {
182 global $smcFunc;
183
184 $filter = $filter == false ? '' : ' AND name LIKE \'' . str_replace("\_", "_", $filter) . '\'';
185
186 $request = $smcFunc['db_query']('', '
187 SELECT name
188 FROM sqlite_master
189 WHERE type = {string:type}
190 {raw:filter}
191 ORDER BY name',
192 array(
193 'type' => 'table',
194 'filter' => $filter,
195 )
196 );
197 $tables = array();
198 while ($row = $smcFunc['db_fetch_row']($request))
199 $tables[] = $row[0];
200 $smcFunc['db_free_result']($request);
201
202 return $tables;
203 }
204
205 // Get the content (INSERTs) for a table.
206 function smf_db_insert_sql($tableName)
207 {
208 global $smcFunc, $db_prefix;
209
210 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
211
212 // This will be handy...
213 $crlf = "\r\n";
214
215 // Get everything from the table.
216 $result = $smcFunc['db_query']('', '
217 SELECT *
218 FROM {raw:table}',
219 array(
220 'table' => $tableName,
221 )
222 );
223
224 // The number of rows, just for record keeping and breaking INSERTs up.
225 $num_rows = $smcFunc['db_num_rows']($result);
226
227 if ($num_rows == 0)
228 return '';
229
230 $fields = array_keys($smcFunc['db_fetch_assoc']($result));
231
232 // SQLite fetches an array so we need to filter out the numberic index for the columns.
233 foreach ($fields as $key => $name)
234 if (is_numeric($name))
235 unset($fields[$key]);
236
237 $smcFunc['db_data_seek']($result, 0);
238
239 // Start it off with the basic INSERT INTO.
240 $data = 'BEGIN TRANSACTION;' . $crlf;
241
242 // Loop through each row.
243 while ($row = $smcFunc['db_fetch_row']($result))
244 {
245 // Get the fields in this row...
246 $field_list = array();
247 for ($j = 0; $j < $smcFunc['db_num_fields']($result); $j++)
248 {
249 // Try to figure out the type of each field. (NULL, number, or 'string'.)
250 if (!isset($row[$j]))
251 $field_list[] = 'NULL';
252 elseif (is_numeric($row[$j]) && (int) $row[$j] == $row[$j])
253 $field_list[] = $row[$j];
254 else
255 $field_list[] = '\'' . $smcFunc['db_escape_string']($row[$j]) . '\'';
256 }
257 $data .= 'INSERT INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $field_list) . ');' . $crlf;
258 }
259 $smcFunc['db_free_result']($result);
260
261 // Return an empty string if there were no rows.
262 return $num_rows == 0 ? '' : $data . 'COMMIT;' . $crlf;
263 }
264
265 // Get the schema (CREATE) for a table.
266 function smf_db_table_sql($tableName)
267 {
268 global $smcFunc, $db_prefix;
269
270 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
271
272 // This will be needed...
273 $crlf = "\r\n";
274
275 // Start the create table...
276 $schema_create = '';
277 $index_create = '';
278
279 // Let's get the create statement directly from SQLite.
280 $result = $smcFunc['db_query']('', '
281 SELECT sql
282 FROM sqlite_master
283 WHERE type = {string:type}
284 AND name = {string:table_name}',
285 array(
286 'type' => 'table',
287 'table_name' => $tableName,
288 )
289 );
290 list ($schema_create) = $smcFunc['db_fetch_row']($result);
291 $smcFunc['db_free_result']($result);
292
293 // Now the indexes.
294 $result = $smcFunc['db_query']('', '
295 SELECT sql
296 FROM sqlite_master
297 WHERE type = {string:type}
298 AND tbl_name = {string:table_name}',
299 array(
300 'type' => 'index',
301 'table_name' => $tableName,
302 )
303 );
304 $indexes = array();
305 while ($row = $smcFunc['db_fetch_assoc']($result))
306 if (trim($row['sql']) != '')
307 $indexes[] = $row['sql'];
308 $smcFunc['db_free_result']($result);
309
310 $index_create .= implode(';' . $crlf, $indexes);
311 $schema_create = empty($indexes) ? rtrim($schema_create) : $schema_create . ';' . $crlf . $crlf;
312
313 return $schema_create . $index_create;
314 }
315
316 // Get the version number.
317 function smf_db_get_version()
318 {
319 return sqlite_libversion();
320 }
321
322 // Simple return the database - and die!
323 function smf_db_get_backup()
324 {
325 global $db_name;
326
327 $db_file = substr($db_name, -3) === '.db' ? $db_name : $db_name . '.db';
328
329 // Add more info if zipped...
330 $ext = '';
331 if (isset($_REQUEST['compress']) && function_exists('gzencode'))
332 $ext = '.gz';
333
334 // Do the remaining headers.
335 header('Content-Disposition: attachment; filename="' . $db_file . $ext . '"');
336 header('Cache-Control: private');
337 header('Connection: close');
338
339 // Literally dump the contents. Try reading the file first.
340 if (@readfile($db_file) == null)
341 echo file_get_contents($db_file);
342
343 obExit(false);
344 }
345
346 ?>