fluent-community / vendor / wpfluent / framework / src / WPFluent / Database / Schema.php

Schema.php in FluentCommunity – Ultra-Fast High-Performance Social Network, Community, LMS & Online Courses 1.0.94, at vendor/wpfluent/framework/src/WPFluent/Database/Schema.php

500 lines 12.4 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCommunity\Framework\Database;
4
5	use FluentCommunity\Framework\Database\Concerns\MaintainsDatabase;
6
7	class Schema
8	{
9	use MaintainsDatabase;
10
11	/**
12	* Get the global $wpdb instance
13	*
14	* @return global $wpdb instance
15	*/
16	public static function db()
17	{
18	return $GLOBALS['wpdb'];
19	}
20
21	/**
22	* Get schema/db information
23	*
24	* @return string\|array
25	*/
26	public static function getInfo($key = null)
27	{
28	$db = static::db();
29
30	$info = [
31	'dbname' => $db->dbname,
32	'prefix' => $db->prefix,
33	'dbhost' => $db->dbhost,
34	'username' => $db->dbuser,
35	'password' => $db->dbpassword,
36	'charset' => $db->charset,
37	'collation' => $db->collate,
38	'tables' => static::getTableList()
39	];
40
41	return $key ? $info[$key] : $info;
42	}
43
44	/**
45	* Migrates database table(s)
46	*
47	* @param string\|array $table The table name without prefix
48	* or an array where each key is table name and value is sql.
49	*
50	* @param string $sql Optional
51	* @return mixed
52	*/
53	public static function migrate($table, $sql = null)
54	{
55	if (!$sql && is_array($table)) {
56	$result = [];
57	foreach ($table as $t => $sql) {
58	$result = array_merge(
59	$result, (array) static::createTable($t, $sql)
60	);
61	}
62	return $result;
63	} else {
64	return static::createTable($table, $sql);
65	}
66	}
67
68	/**
69	* Creates a new table if doesn't exist using dbDelta function
70	*
71	* @param string $table The table name without the prefix
72	* @param string $sql The sql to create table or an absolute path of a
73	* .sql file containing the column definations for creating the new table.
74	*
75	* @return string Message
76	*/
77	public static function createTableIfNotExist($table, $sql)
78	{
79	if (!static::hasTable($table)) {
80	return static::createTable($table, $sql);
81	}
82	}
83
84	/**
85	* Checks if a column exists in a table
86	*
87	* @param string $column The column name of the table
88	* @param string $table The table name without prefix
89	* @return boolean
90	*/
91
92	public static function hasColumn($column, $table)
93	{
94	$wpdb = static::db();
95
96	$table = static::table($table);
97
98	$columns = $wpdb->get_col("DESCRIBE $table");
99
100	return in_array($column, $columns);
101	}
102
103	/**
104	* Checks if a table exists
105	*
106	* @param string $table The table name without prefix
107	* @return boolean
108	*/
109	public static function hasTable($table)
110	{
111	$wpdb = static::db();
112
113	$table = static::table($table);
114
115	$result = $wpdb->get_var("SHOW TABLES LIKE '" . $table . "'") == $table;
116
117	if ($result) {
118	return $result;
119	}
120
121	// Check if any temporary table exists by this table name.
122
123	// At first, store the original state of the error suppress
124	// error and then turn off the error from being shown, so
125	// error will be not shown if there is no temporary
126	// table, then restore the error state.
127	$isErrorSuppressed = $wpdb->suppress_errors;
128
129	$wpdb->suppress_errors = true;
130
131	$result = static::query("SELECT 1 FROM %{$table}% WHERE 0");
132
133	$wpdb->suppress_errors = $isErrorSuppressed;
134
135	return $result === 0;
136	}
137
138	/**
139	* Resolves the table prefix and makes the table name with prefix
140	*
141	* @param string $table The table name without the prefix
142	* @return string The resolved table name with the prefix
143	*/
144	public static function table($table)
145	{
146	$wpdb = static::db();
147
148	$prefix = $wpdb->prefix;
149
150	if (strpos($table, $prefix) === 0) {
151	return $table;
152	}
153
154	return isset($wpdb->{$table}) ? $wpdb->{$table} : ($wpdb->prefix.$table);
155	}
156
157
158	/**
159	* Resolves the sql prefix
160	*
161	* @param string $sql The file name or the raw sql
162	* @return string The resolved sql
163	*/
164	public static function sql($sql = '')
165	{
166	$allowedSqlFileFormats = [
167	'sql'
168	];
169
170	foreach ($allowedSqlFileFormats as $format) {
171	if (str_ends_with($sql, '.' . $format)) {
172	$sql = @file_exists($sql) ? file_get_contents($sql) : $sql;
173	break;
174	}
175	}
176
177	return $sql;
178	}
179
180	/**
181	* Creates a new table using dbDelta function or alters the table if exists.
182	*
183	* @param string $table The table name without the prefix
184	* @param string $sql The sql to create table or an absolute path of a
185	* .sql file containing the column definations for creating the new table.
186	*
187	* @return string message
188	*/
189	public static function createTable($table, $sql)
190	{
191	$table = static::table($table);
192
193	$sql = static::sql($sql);
194
195	$collate = static::db()->get_charset_collate();
196
197	return static::callDBDelta(
198	"CREATE TABLE $table (
199	".PHP_EOL.trim(trim($sql), ',').PHP_EOL."
200	) $collate;"
201	);
202	}
203
204	/**
205	* Alters an existing table if exists
206	*
207	* @param string $table The table name without the prefix
208	* @param string $sql The sql to create table or an absolute path of a
209	* .sql file containing the column definations for creating the new table.
210	*
211	* @return string message
212	*/
213	public static function alterTableIfExists($table, $sql)
214	{
215	if (static::hasTable($table)) {
216	return static::alterTable($table, $sql);
217	}
218	}
219
220	/**
221	* Alters an existing table
222	*
223	* @param string $table The table name without the prefix
224	* @param string $sql The sql to create table or an absolute path of a
225	* .sql file containing the column definations for creating the new table.
226	*
227	* @return string message
228	*/
229	public static function alterTable($table, $sql)
230	{
231	$table = static::table($table);
232
233	$sql = static::sql($sql);
234
235	$sql = array_map(function($i) { return trim($i);}, explode(',', $sql));
236
237	$sql = "ALTER TABLE $table ".PHP_EOL.rtrim(
238	trim(implode(','.PHP_EOL, $sql)), ';'
239	).";";
240
241	return static::query($sql);
242	}
243
244	/**
245	* Alters an existing table using dbDelta function if exists, otherwise creates
246	* it. Alters an existing table but takes the table creation column defination.
247	* This is because, the dbDelta functioin can create or update a table using
248	* the table creation defination. In this, case, if a table exists and the
249	* columns are matched then nothing happens but if there's any difference
250	* in the new sql then the dbDelta alters the table using the new sql
251	* defination but doesn't delete any columns. So, after the dbDelta
252	* finishes it's job, any non-existing columns in the new sql
253	* defination will be deleted from the existing table. if
254	* table is not there then the table gets created.
255	*
256	* @param string $table The table name without the prefix
257	* @param string $sql The sql to create table or an absolute path of a
258	* .sql file containing the column definations for creating the new table.
259	*
260	* @return string message
261	*/
262	public static function updateTable($table, $sql)
263	{
264	$result = static::createTable(
265	$table, implode(",\n", array_map('trim', explode(',', $sql)))
266	);
267
268	if ($existingColumns = static::getColumns($table)) {
269
270	$columns = array_map(function($l) {
271	if (preg_match('/(\S+)/', $l, $matches)) return $matches[1];
272	}, array_map('trim', explode(',', $sql)));
273
274	foreach ($existingColumns as $column) {
275	if (!in_array($column, $columns)) {
276	$tbl = static::table($table);
277	static::query("alter table $tbl drop column $column");
278	$tblColumn = $tbl.'.'.$column;
279	$result[$tblColumn] = "Dropped column {$tblColumn}";
280	}
281	}
282	}
283
284	return $result;
285	}
286
287	/**
288	* Drops/deletes an existing table if exists
289	*
290	* @param string $table The table name without the prefix
291	* @param bool $disableForeignKeyCheck Optional. Whether to disable foreign key checks before dropping the table. Default is true. *
292	* @return bool
293	*/
294
295	public static function dropTableIfExists($table, $disableForeignKeyCheck = true)
296	{
297	if (static::hasTable($table)) {
298	if ($disableForeignKeyCheck) {
299	static::db()->query("ALTER TABLE " . static::table($table) . " DISABLE KEYS;");
300	}
301	return static::db()->query('DROP TABLE ' . static::table($table));
302	}
303	}
304
305	/**
306	* Truncate a table.
307	*
308	* @param string $table
309	* @return bool
310	*/
311	public static function truncate($table)
312	{
313	$table = static::table($table);
314
315	return static::db()->query("TRUNCATE TABLE $table");
316	}
317
318	/**
319	* Truncate a table if exists.
320	*
321	* @param string $table
322	* @return bool
323	*/
324	public static function truncateTableIfExists($table)
325	{
326	if (static::hasTable($table)) {
327	return static::truncate($table);
328	}
329	}
330
331	/**
332	* Adds a new index to a column of given table.
333	*
334	* @param string $table Table name
335	* @param string $index Columns name
336	* @return bool
337	* @see https://developer.wordpress.org/reference/functions/add_clean_index
338	*/
339	public static function addIndex($table, $index)
340	{
341	return add_clean_index(static::table($table), $index);
342	}
343
344	/**
345	* Drops an index from a column of given table.
346	*
347	* @param string $table Table name
348	* @param string $index Columns name
349	* @return bool
350	* @see https://developer.wordpress.org/reference/functions/drop_index
351	*/
352	public static function dropIndex($table, $index)
353	{
354	return drop_index(static::table($table), $index);
355	}
356
357	/**
358	* Makes raw query and can resolve the table name from the query
359	* and can form a full table name including the table prefix if
360	* the table name is wrapped like: %table_name% in the query.
361	*
362	* @param straing $query
363	* @return mixed
364	*/
365	public static function query($query)
366	{
367	if (preg_match('/%.*%/', $query, $m)) {
368	$query = str_replace(
369	$m[0], static::table(trim($m[0], '%')), $query
370	);
371	}
372
373	return static::db()->query($query);
374	}
375
376	/**
377	* Get a list of all columns from the given table name.
378	*
379	* @param string $table The table name without the prefix
380	* @return array
381	*/
382	public static function getColumns($table)
383	{
384	if (static::hasTable($table)) {
385	return static::db()->get_col(
386	'DESC ' . static::table($table), 0
387	);
388	}
389	}
390
391	/**
392	* Gets a list of all columns including column information
393	*
394	* @param string $table The table name without the prefix
395	* @return array
396	*/
397	public static function getColumnsWithTypes($table)
398	{
399	if (!static::hasTable($table)) return;
400
401	$db = static::db()->dbname;
402	$table = static::table($table);
403	$fields = [
404	'COLUMN_NAME',
405	'ORDINAL_POSITION',
406	'COLUMN_DEFAULT',
407	'IS_NULLABLE',
408	'DATA_TYPE',
409	'CHARACTER_MAXIMUM_LENGTH',
410	'NUMERIC_PRECISION',
411	'NUMERIC_SCALE',
412	'COLUMN_KEY',
413	'EXTRA',
414	];
415
416	$sql = "SELECT " . implode(',', $fields) . " FROM INFORMATION_SCHEMA.COLUMNS";
417	$sql .= " WHERE TABLE_NAME = '".$table."' AND TABLE_SCHEMA = '".$db."'";
418
419	return array_map(function($i) {
420	$item = [];
421	foreach ((array) $i as $key => $value) {
422	$item[strtolower($key)] = $value;
423	}
424	return $item;
425	}, static::db()->get_results($sql));
426	}
427
428	/**
429	* Retrieves the list of all available built-in tables from
430	* the database using WordPress' $wpdb->tables native method.
431	*
432	* @param string $scope
433	* @param boolean $prefix
434	* @param integer $blogId
435	* @return string[] WP Table names. When a prefix is requested,
436	* the key is the unprefixed table name.
437	* @see https://developer.wordpress.org/reference/classes/wpdb/tables/
438	*/
439	public static function tables($scope = 'all', $prefix = true, $blogId = 0)
440	{
441	return static::db()->tables($scope, $prefix, $blogId);
442	}
443
444	/**
445	* Retrieves the list of all available tables from the database.
446	*
447	* @param string $dbname optional
448	* @return array
449	*/
450	public static function getTables($dbname = null)
451	{
452	return static::getTableList($dbname);
453	}
454
455	/**
456	* Retrieves the list of all available tables in the database.
457	*
458	* @param string $dbname optional
459	* @return array
460	*/
461	public static function getTableList($dbname = null)
462	{
463	$dbname = $dbname ?: static::db()->dbname;
464	$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
465	$sql .= " WHERE TABLE_SCHEMA = '".$dbname."'";
466
467	return array_map(function($i) {
468	return $i->TABLE_NAME;
469	}, static::db()->get_results($sql));
470	}
471
472	/**
473	* Retrieve the current database engine name.
474	*
475	* @param string $table
476	* @return string
477	*/
478	public static function getEngine($table)
479	{
480	return static::db()->get_var(
481	'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = "' . static::table($table) . '"'
482	);
483	}
484
485	/**
486	* The wrapper for calling dbDelta function
487	*
488	* @param string $sql
489	* @return mixed
490	*/
491	public static function callDBDelta($sql)
492	{
493	if (!function_exists('dbDelta')) {
494	require (ABSPATH . 'wp-admin/includes/upgrade.php');
495	}
496
497	return dbDelta($sql);
498	}
499	}
500