1 | <?php |
||
2 | |||
3 | /** |
||
4 | * This file contains rarely used extended database functionality. |
||
5 | * |
||
6 | * Simple Machines Forum (SMF) |
||
7 | * |
||
8 | * @package SMF |
||
9 | * @author Simple Machines https://www.simplemachines.org |
||
10 | * @copyright 2022 Simple Machines and individual contributors |
||
11 | * @license https://www.simplemachines.org/about/smf/license.php BSD |
||
12 | * |
||
13 | * @version 2.1.0 |
||
14 | */ |
||
15 | |||
16 | if (!defined('SMF')) |
||
17 | die('No direct access...'); |
||
18 | |||
19 | /** |
||
20 | * Add the functions implemented in this file to the $smcFunc array. |
||
21 | */ |
||
22 | function db_extra_init() |
||
23 | { |
||
24 | global $smcFunc; |
||
25 | |||
26 | if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table') |
||
27 | $smcFunc += array( |
||
28 | 'db_backup_table' => 'smf_db_backup_table', |
||
29 | 'db_optimize_table' => 'smf_db_optimize_table', |
||
30 | 'db_table_sql' => 'smf_db_table_sql', |
||
31 | 'db_list_tables' => 'smf_db_list_tables', |
||
32 | 'db_get_version' => 'smf_db_get_version', |
||
33 | 'db_get_vendor' => 'smf_db_get_vendor', |
||
34 | 'db_allow_persistent' => 'smf_db_allow_persistent', |
||
35 | ); |
||
36 | } |
||
37 | |||
38 | /** |
||
39 | * Backup $table to $backup_table. |
||
40 | * |
||
41 | * @param string $table The name of the table to backup |
||
42 | * @param string $backup_table The name of the backup table for this table |
||
43 | * @return resource -the request handle to the table creation query |
||
44 | */ |
||
45 | function smf_db_backup_table($table, $backup_table) |
||
46 | { |
||
47 | global $smcFunc, $db_prefix; |
||
48 | |||
49 | $table = str_replace('{db_prefix}', $db_prefix, $table); |
||
50 | |||
51 | // Do we need to drop it first? |
||
52 | $tables = smf_db_list_tables(false, $backup_table); |
||
53 | if (!empty($tables)) |
||
54 | $smcFunc['db_query']('', ' |
||
55 | DROP TABLE {raw:backup_table}', |
||
56 | array( |
||
57 | 'backup_table' => $backup_table, |
||
58 | ) |
||
59 | ); |
||
60 | |||
61 | /** |
||
62 | * @todo Should we create backups of sequences as well? |
||
63 | */ |
||
64 | $smcFunc['db_query']('', ' |
||
65 | CREATE TABLE {raw:backup_table} |
||
66 | ( |
||
67 | LIKE {raw:table} |
||
68 | INCLUDING DEFAULTS |
||
69 | )', |
||
70 | array( |
||
71 | 'backup_table' => $backup_table, |
||
72 | 'table' => $table, |
||
73 | ) |
||
74 | ); |
||
75 | $smcFunc['db_query']('', ' |
||
76 | INSERT INTO {raw:backup_table} |
||
77 | SELECT * FROM {raw:table}', |
||
78 | array( |
||
79 | 'backup_table' => $backup_table, |
||
80 | 'table' => $table, |
||
81 | ) |
||
82 | ); |
||
83 | } |
||
84 | |||
85 | /** |
||
86 | * This function optimizes a table. |
||
87 | * |
||
88 | * @param string $table The table to be optimized |
||
89 | * @return int How much space was gained |
||
90 | */ |
||
91 | function smf_db_optimize_table($table) |
||
92 | { |
||
93 | global $smcFunc, $db_prefix; |
||
94 | |||
95 | $table = str_replace('{db_prefix}', $db_prefix, $table); |
||
96 | |||
97 | $pg_tables = array('pg_catalog', 'information_schema'); |
||
98 | |||
99 | $request = $smcFunc['db_query']('', ' |
||
100 | SELECT pg_relation_size(C.oid) AS "size" |
||
101 | FROM pg_class C |
||
102 | LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) |
||
103 | WHERE nspname NOT IN ({array_string:pg_tables}) |
||
104 | AND relname = {string:table}', |
||
105 | array( |
||
106 | 'table' => $table, |
||
107 | 'pg_tables' => $pg_tables, |
||
108 | ) |
||
109 | ); |
||
110 | |||
111 | $row = $smcFunc['db_fetch_assoc']($request); |
||
112 | $smcFunc['db_free_result']($request); |
||
113 | |||
114 | $old_size = $row['size']; |
||
115 | |||
116 | $request = $smcFunc['db_query']('', ' |
||
117 | VACUUM FULL ANALYZE {raw:table}', |
||
118 | array( |
||
119 | 'table' => $table, |
||
120 | ) |
||
121 | ); |
||
122 | |||
123 | if (!$request) |
||
124 | return -1; |
||
125 | |||
126 | $request = $smcFunc['db_query']('', ' |
||
127 | SELECT pg_relation_size(C.oid) AS "size" |
||
128 | FROM pg_class C |
||
129 | LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) |
||
130 | WHERE nspname NOT IN ({array_string:pg_tables}) |
||
131 | AND relname = {string:table}', |
||
132 | array( |
||
133 | 'table' => $table, |
||
134 | 'pg_tables' => $pg_tables, |
||
135 | ) |
||
136 | ); |
||
137 | |||
138 | $row = $smcFunc['db_fetch_assoc']($request); |
||
139 | $smcFunc['db_free_result']($request); |
||
140 | |||
141 | if (isset($row['size'])) |
||
142 | return ($old_size - $row['size']) / 1024; |
||
143 | else |
||
144 | return 0; |
||
145 | } |
||
146 | |||
147 | /** |
||
148 | * This function lists all tables in the database. |
||
149 | * The listing could be filtered according to $filter. |
||
150 | * |
||
151 | * @param string|boolean $db string The database name or false to use the current DB |
||
152 | * @param string|boolean $filter String to filter by or false to list all tables |
||
153 | * @return array An array of table names |
||
154 | */ |
||
155 | function smf_db_list_tables($db = false, $filter = false) |
||
156 | { |
||
157 | global $smcFunc; |
||
158 | |||
159 | $request = $smcFunc['db_query']('', ' |
||
160 | SELECT tablename |
||
161 | FROM pg_tables |
||
162 | WHERE schemaname = {string:schema_public}' . ($filter == false ? '' : ' |
||
0 ignored issues
–
show
Bug
introduced
by
![]() |
|||
163 | AND tablename LIKE {string:filter}') . ' |
||
164 | ORDER BY tablename', |
||
165 | array( |
||
166 | 'schema_public' => 'public', |
||
167 | 'filter' => $filter, |
||
168 | ) |
||
169 | ); |
||
170 | |||
171 | $tables = array(); |
||
172 | while ($row = $smcFunc['db_fetch_row']($request)) |
||
173 | $tables[] = $row[0]; |
||
174 | $smcFunc['db_free_result']($request); |
||
175 | |||
176 | return $tables; |
||
177 | } |
||
178 | |||
179 | /** |
||
180 | * Dumps the schema (CREATE) for a table. |
||
181 | * |
||
182 | * @todo why is this needed for? |
||
183 | * @param string $tableName The name of the table |
||
184 | * @return string The "CREATE TABLE" SQL string for this table |
||
185 | */ |
||
186 | function smf_db_table_sql($tableName) |
||
187 | { |
||
188 | global $smcFunc, $db_prefix; |
||
189 | |||
190 | $tableName = str_replace('{db_prefix}', $db_prefix, $tableName); |
||
191 | |||
192 | // This will be needed... |
||
193 | $crlf = "\r\n"; |
||
194 | |||
195 | // Drop it if it exists. |
||
196 | $schema_create = 'DROP TABLE IF EXISTS ' . $tableName . ';' . $crlf . $crlf; |
||
197 | |||
198 | // Start the create table... |
||
199 | $schema_create .= 'CREATE TABLE ' . $tableName . ' (' . $crlf; |
||
200 | $index_create = ''; |
||
201 | $seq_create = ''; |
||
202 | |||
203 | // Find all the fields. |
||
204 | $result = $smcFunc['db_query']('', ' |
||
205 | SELECT column_name, column_default, is_nullable, data_type, character_maximum_length |
||
206 | FROM information_schema.columns |
||
207 | WHERE table_name = {string:table} |
||
208 | ORDER BY ordinal_position', |
||
209 | array( |
||
210 | 'table' => $tableName, |
||
211 | ) |
||
212 | ); |
||
213 | while ($row = $smcFunc['db_fetch_assoc']($result)) |
||
214 | { |
||
215 | if ($row['data_type'] == 'character varying') |
||
216 | $row['data_type'] = 'varchar'; |
||
217 | elseif ($row['data_type'] == 'character') |
||
218 | $row['data_type'] = 'char'; |
||
219 | if ($row['character_maximum_length']) |
||
220 | $row['data_type'] .= '(' . $row['character_maximum_length'] . ')'; |
||
221 | |||
222 | // Make the CREATE for this column. |
||
223 | $schema_create .= ' "' . $row['column_name'] . '" ' . $row['data_type'] . ($row['is_nullable'] != 'YES' ? ' NOT NULL' : ''); |
||
224 | |||
225 | // Add a default...? |
||
226 | if (trim($row['column_default']) != '') |
||
227 | { |
||
228 | $schema_create .= ' default ' . $row['column_default'] . ''; |
||
229 | |||
230 | // Auto increment? |
||
231 | if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0) |
||
232 | { |
||
233 | // Get to find the next variable first! |
||
234 | $count_req = $smcFunc['db_query']('', ' |
||
235 | SELECT MAX("{raw:column}") |
||
236 | FROM {raw:table}', |
||
237 | array( |
||
238 | 'column' => $row['column_name'], |
||
239 | 'table' => $tableName, |
||
240 | ) |
||
241 | ); |
||
242 | list ($max_ind) = $smcFunc['db_fetch_row']($count_req); |
||
243 | $smcFunc['db_free_result']($count_req); |
||
244 | // Get the right bloody start! |
||
245 | $seq_create .= 'CREATE SEQUENCE ' . $matches[1] . ' START WITH ' . ($max_ind + 1) . ';' . $crlf . $crlf; |
||
246 | } |
||
247 | } |
||
248 | |||
249 | $schema_create .= ',' . $crlf; |
||
250 | } |
||
251 | $smcFunc['db_free_result']($result); |
||
252 | |||
253 | // Take off the last comma. |
||
254 | $schema_create = substr($schema_create, 0, -strlen($crlf) - 1); |
||
255 | |||
256 | $result = $smcFunc['db_query']('', ' |
||
257 | SELECT pg_get_indexdef(i.indexrelid) AS inddef |
||
258 | FROM pg_class AS c |
||
259 | INNER JOIN pg_index AS i ON (i.indrelid = c.oid) |
||
260 | INNER JOIN pg_class AS c2 ON (c2.oid = i.indexrelid) |
||
261 | WHERE c.relname = {string:table} AND i.indisprimary is {raw:pk}', |
||
262 | array( |
||
263 | 'table' => $tableName, |
||
264 | 'pk' => 'false', |
||
265 | ) |
||
266 | ); |
||
267 | |||
268 | while ($row = $smcFunc['db_fetch_assoc']($result)) |
||
269 | { |
||
270 | $index_create .= $crlf . $row['inddef'] . ';'; |
||
271 | } |
||
272 | |||
273 | $smcFunc['db_free_result']($result); |
||
274 | |||
275 | $result = $smcFunc['db_query']('', ' |
||
276 | SELECT pg_get_constraintdef(c.oid) as pkdef |
||
277 | FROM pg_constraint as c |
||
278 | WHERE c.conrelid::regclass::text = {string:table} AND |
||
279 | c.contype = {string:constraintType}', |
||
280 | array( |
||
281 | 'table' => $tableName, |
||
282 | 'constraintType' => 'p', |
||
283 | ) |
||
284 | ); |
||
285 | |||
286 | while ($row = $smcFunc['db_fetch_assoc']($result)) |
||
287 | { |
||
288 | $index_create .= $crlf . 'ALTER TABLE ' . $tableName . ' ADD ' . $row['pkdef'] . ';'; |
||
289 | } |
||
290 | |||
291 | $smcFunc['db_free_result']($result); |
||
292 | |||
293 | // Finish it off! |
||
294 | $schema_create .= $crlf . ');'; |
||
295 | |||
296 | return $seq_create . $schema_create . $index_create; |
||
297 | } |
||
298 | |||
299 | /** |
||
300 | * Get the version number. |
||
301 | * |
||
302 | * @return string The version |
||
303 | */ |
||
304 | function smf_db_get_version() |
||
305 | { |
||
306 | global $db_connection; |
||
307 | static $ver; |
||
308 | |||
309 | if (!empty($ver)) |
||
310 | return $ver; |
||
311 | |||
312 | $ver = pg_version($db_connection)['server']; |
||
313 | |||
314 | return $ver; |
||
315 | } |
||
316 | |||
317 | /** |
||
318 | * Return PostgreSQL |
||
319 | * |
||
320 | * @return string The database engine we are using |
||
321 | */ |
||
322 | function smf_db_get_vendor() |
||
323 | { |
||
324 | return 'PostgreSQL'; |
||
325 | } |
||
326 | |||
327 | /** |
||
328 | * Figures out if persistent connection is allowed |
||
329 | * |
||
330 | * @return boolean |
||
331 | */ |
||
332 | function smf_db_allow_persistent() |
||
333 | { |
||
334 | $value = ini_get('pgsql.allow_persistent'); |
||
335 | if (strtolower($value) == 'on' || strtolower($value) == 'true' || $value == '1') |
||
336 | return true; |
||
337 | else |
||
338 | return false; |
||
339 | } |
||
340 | |||
341 | ?> |