Issues (1014)

Sources/DbExtra-postgresql.php (1 issue)

Labels
Severity
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
It seems like you are loosely comparing $filter of type boolean|string against false; this is ambiguous if the string can be empty. Consider using a strict comparison === instead.
Loading history...
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
?>