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

Schema.php in FluentBoards – Project Management, Task Management, Goal Tracking, Kanban Board, and, Team Collaboration 1.32, at vendor/wpfluent/framework/src/WPFluent/Database/Schema.php

400 lines 9.8 KB

Raw Download Zip

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