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.13, at vendor/wpfluent/framework/src/WPFluent/Database/Schema.php

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