wpide / App / Services / Database / Database.php

Database.php in WPIDE – File Manager & Code Editor 3.2, at App/Services/Database/Database.php

860 lines 23.3 KB

Raw Download Zip

1	<?php
2	namespace WPIDE\App\Services\Database;
3
4	use DateTime;
5	use WPIDE\App\App;
6	use WPIDE\App\Services\Service;
7	use WPIDE\App\Services\Storage\wpdb;
8	use WPIDE\App\Services\Database\Traits\DatabaseSchema;
9	use Ifsnop\Mysqldump as IMysqldump;
10	use const WPIDE\Constants\TMP_DIR;
11
12	class Database implements Service
13	{
14	use DatabaseSchema;
15
16	/* @var $db \wpdb */
17	protected $db;
18
19	public function init(array $config = [])
20	{
21	global $wpdb;
22
23	$this->db = $wpdb;
24	$this->db->hide_errors();
25
26	}
27
28	public function db(): \wpdb
29	{
30	return $this->db;
31	}
32
33	public function getReadonlyColumns(): array
34	{
35
36	$readonly = [];
37
38	$readonly[$this->db->users] = [
39	'user_login' => [
40	[
41	'key' => 'ID',
42	'value' => get_current_user_id()
43	]
44	]
45	];
46
47	$readonly[$this->db->options] = [
48	'option_name' => true,
49	'option_value' => [
50	[
51	'key' => 'option_name',
52	'value' => 'siteurl'
53	],
54	[
55	'key' => 'option_name',
56	'value' => 'home'
57	]
58	]
59	];
60
61	return $readonly;
62	}
63
64	public function getRestrictedRows(): array
65	{
66
67	$restricted = [];
68
69	$restricted[$this->db->users] = [
70	'ID' => get_current_user_id()
71	];
72
73	$restricted[$this->db->options] = [
74	'option_name' => ['siteurl', 'home']
75	];
76
77	return $restricted;
78	}
79
80	public function getTables(): array
81	{
82
83	$tables = [];
84	$results = $this->db->get_results('SHOW TABLES');
85
86	foreach($results as $result) {
87	$tables[] = current($result);
88	}
89
90	return [
91	'db' => DB_NAME,
92	'tables' => $tables,
93	'readonlyColumns' => $this->getReadonlyColumns(),
94	'restrictedRows' => $this->getRestrictedRows()
95	];
96	}
97
98	/**
99	* @throws \DI\DependencyException
100	* @throws \DI\NotFoundException
101	*/
102	public function getTableRows(string $table, $args = []): array
103	{
104
105	$defaults = [
106	'page' => 1,
107	'per_page' => 20,
108	'search_value' => null,
109	'search_field' => null,
110	];
111
112	$args = array_merge($defaults, $args);
113
114	$offset = ($args['page'] - 1) * $args['per_page'];
115	$total_rows = intval($this->db->get_var('select count(*) as count from ' . sanitize_text_field($table)));
116	$pages = ceil($total_rows / $args['per_page']);
117
118	$where = '';
119	if(!empty($args['search_value']) && !empty($args['search_field'])) {
120
121	$where = $this->db->prepare('WHERE '.sanitize_text_field($args['search_field']).' LIKE %s', '%'.$this->db->esc_like($args['search_value']).'%');
122
123	}else if(!empty($args['search_value'])) {
124
125	$this->db->get_results('SELECT * FROM '.sanitize_text_field($table).' LIMIT 0, 1', 'ARRAY_A');
126	$fields = $this->db->get_col_info();
127	$where = 'WHERE ';
128	$total = count($fields);
129	$i = 0;
130	foreach($fields as $field) {
131	$where .= $this->db->prepare($field.' LIKE %s', '%' . $this->db->esc_like($args['search_value']) . '%');
132	if($i < ($total - 1)) {
133	$where .= ' OR ';
134	}
135	$i++;
136	}
137	}
138
139	$query = $this->db->prepare('SELECT * FROM '.sanitize_text_field($table).' '.$where.' LIMIT %d, %d', $offset, $args['per_page']);
140
141	$rows = $this->db->get_results($query, 'ARRAY_A');
142	$fields = $this->db->get_col_info();
143
144	$structure = $this->getTableStructure($table);
145	$primaryKey = $this->getTablePrimaryKey($table);
146
147	return [
148	'rows' => $rows,
149	'offset' => $offset,
150	'page' => $args['page'],
151	'pages' => $pages,
152	'total' => $total_rows,
153	'fields' => $fields,
154	'structure' => $structure,
155	'primaryKey' => $primaryKey
156	];
157	}
158
159	/**
160	* @throws \DI\DependencyException
161	* @throws \DI\NotFoundException
162	*/
163	public function getTableRow(string $table, $id)
164	{
165
166	$primaryKey = $this->getTablePrimaryKey($table);
167
168	$content = $this->getTableRows($table, [
169	'search_value' => $id,
170	'search_field' => $primaryKey,
171	]);
172
173	foreach($content['rows'] as $row) {
174	if($row[$primaryKey] === $id) {
175	return $row;
176	}
177	}
178
179	return null;
180	}
181
182	/**
183	* @throws \DI\DependencyException
184	* @throws \DI\NotFoundException
185	*/
186	public function getTableStructure(string $table): array
187	{
188
189	return App::instance()->cache()->result($table.'_structure', function() use($table) {
190
191	$query = $this->db->prepare('
192	SELECT
193	column_name as Name,
194	UPPER(data_type) as Type,
195	column_key as Index_Type,
196	character_maximum_length as Length,
197	is_nullable as Is_Nullable,
198	extra as Auto_Increment
199	FROM information_schema.columns
200	WHERE
201	table_schema=%s
202	AND table_name=%s
203	', DB_NAME, $table);
204
205	$columns = $this->db->get_results($query);
206
207	$structure = [];
208	foreach($columns as $column) {
209	$column->Input_Type = $this->columnInputType($column);
210	$column->Is_Numeric = $this->isNumericColumn($column);
211	$column->Is_Int = $this->isIntColumn($column);
212	$column->Index_Type = $this->getColumnIndexKeyName($table, $column);
213	$column->Is_Nullable = $this->isNullableColumn($column);
214	$column->Auto_Increment = $this->isAutoIncrementColumn($column);
215	$column->Length = $this->getColumnDefaultLength($column);
216	$structure[$column->Name] = $column;
217	}
218
219	return $structure;
220	});
221	}
222
223	/**
224	* @throws \DI\DependencyException
225	* @throws \DI\NotFoundException
226	*/
227	public function getTableStructureRows(string $table): array
228	{
229
230	$structure = $this->getTableStructure($table);
231	$rows = array_values($structure);
232	$fields = !empty($rows) ? array_keys((array)$rows[0]) : [];
233
234	$dbSchema = $this->getDatabaseSchema();
235
236	return [
237	'rows' => $rows,
238	'offset' => 0,
239	'page' => 1,
240	'pages' => 1,
241	'total' => count($rows),
242	'fields' => $fields,
243	'structure' => $dbSchema,
244	'primaryKey' => "Name"
245	];
246	}
247
248	/**
249	* @throws \DI\DependencyException
250	* @throws \DI\NotFoundException
251	*/
252	public function getTableStructureRow(string $table, string $name)
253	{
254	$content = $this->getTableStructureRows($table);
255
256	foreach($content['rows'] as $row) {
257	if($row->Name === $name) {
258	return $row;
259	}
260	}
261
262	return null;
263	}
264
265	/**
266	* @throws \DI\DependencyException
267	* @throws \DI\NotFoundException
268	*/
269	public function getTablePrimaryKey($table) {
270
271	return App::instance()->cache()->result($table.'_pkey', function() use($table) {
272
273	$row = $this->db->get_row('SHOW INDEX FROM '.$table.' where key_name = "PRIMARY"');
274	return !empty($row) ? $row->Column_name : '';
275	});
276	}
277
278	/**
279	* @throws \DI\DependencyException
280	* @throws \DI\NotFoundException
281	*/
282	public function getTableAutoIncrementKey($table) {
283
284	return App::instance()->cache()->result($table.'_increment_key', function() use($table) {
285
286	$rows = $this->db->get_results("DESCRIBE $table");
287	$rows = array_filter($rows, function($row) {
288	return $row->Extra === 'auto_increment';
289	});
290
291	$row = !empty($rows) ? array_shift($rows) : null;
292	return !empty($row) ? $row->Field : null;
293	});
294	}
295
296	/**
297	* @throws \DI\DependencyException
298	* @throws \DI\NotFoundException
299	*/
300	public function getColumnInfo(string $table, string $columnName) {
301
302	return App::instance()->cache()->result($table.'_column_info', function() use($table, $columnName) {
303
304	$structure = $this->getTableStructure($table);
305
306	foreach ($structure as $info) {
307	if ($info->Name === $columnName) {
308	return $info;
309	}
310	}
311
312	return null;
313
314	});
315	}
316
317	public function getColumnIndexKeyName($table, $column): string
318	{
319
320	$row = $this->db->get_row($this->db->prepare("SHOW INDEX FROM $table where column_name = %s", $column->Name));
321
322	if(empty($row)) {
323	return '';
324	}
325
326	if($row->Key_name === 'PRIMARY') {
327	return 'PRIMARY';
328	}else if($row->Non_unique === '0') {
329	return 'UNIQUE';
330	}else{
331	return 'INDEX';
332	}
333
334	return '';
335	}
336
337	/**
338	* @throws \DI\DependencyException
339	* @throws \DI\NotFoundException
340	*/
341	public function getColumnDefaultLength($column):? string
342	{
343
344	if(!empty($column->Length)) {
345	return $column->Length;
346	}
347
348	$length = is_null($column->Length) ? '' : trim($column->Length);
349
350	$is_int = $this->isIntColumn($column);
351
352	if ($is_int) {
353	$length = empty($length) ? '11' : $length;
354	} else {
355	if($column->Type === 'VARCHAR') {
356	$length = '255';
357	}else if($column->Type === 'CHAR') {
358	$length = '32';
359	}
360	}
361
362	return $length;
363	}
364
365	public function isNullableColumn($column): bool
366	{
367
368	return $column->Is_Nullable === 'YES' \|\| $column->Is_Nullable === true;
369	}
370
371	public function isAutoIncrementColumn($column): bool
372	{
373
374	return $column->Auto_Increment === 'auto_increment' \|\| $column->Auto_Increment === true;
375	}
376
377	/**
378	* @throws \DI\DependencyException
379	* @throws \DI\NotFoundException
380	*/
381	public function isNumericColumn(Object $column): bool
382	{
383
384	return
385	$this->isIntColumn($column) \|\|
386	str_contains($column->Type, 'DECIMAL') \|\|
387	str_contains($column->Type, 'FLOAT') \|\|
388	str_contains($column->Type, 'DOUBLE') \|\|
389	str_contains($column->Type, 'REAL') \|\|
390	str_contains($column->Type, 'BIT') \|\|
391	str_contains($column->Type, 'BOOLEAN') \|\|
392	str_contains($column->Type, 'SERIAL');
393
394	}
395
396	/**
397	* @throws \DI\DependencyException
398	* @throws \DI\NotFoundException
399	*/
400	public function isIntColumn(Object $column): bool
401	{
402
403	return str_contains($column->Type, 'INT');
404	}
405
406	/**
407	* @throws \DI\DependencyException
408	* @throws \DI\NotFoundException
409	*/
410	public function isDateColumn(Object $column): bool
411	{
412
413	return $column->Type === 'DATE';
414	}
415
416	/**
417	* @throws \DI\DependencyException
418	* @throws \DI\NotFoundException
419	*/
420	public function isDateTimeColumn(Object $column): bool
421	{
422
423	return $column->Type === 'DATETIME';
424	}
425
426	/**
427	* @throws \DI\DependencyException
428	* @throws \DI\NotFoundException
429	*/
430	public function isBooleanColumn(Object $column): bool
431	{
432
433	return $column->Type === 'BOOLEAN';
434	}
435
436	/**
437	* @throws \DI\DependencyException
438	* @throws \DI\NotFoundException
439	*/
440	public function isTextColumn(Object $column): bool
441	{
442
443	return
444	str_contains($column->Type, 'TEXT') \|\|
445	str_contains($column->Type, 'JSON');
446
447	}
448
449	/**
450	* @throws \DI\DependencyException
451	* @throws \DI\NotFoundException
452	*/
453	public function columnInputType(Object $column): string
454	{
455	$inputType = 'text';
456
457	if($this->isBooleanColumn($column)) {
458	$inputType = 'boolean';
459	}else if($this->isNumericColumn($column)) {
460	$inputType = 'number';
461	}else if($this->isDateColumn($column)) {
462	$inputType = 'date';
463	}else if($this->isDateTimeColumn($column)) {
464	$inputType = 'datetime';
465	}else if($this->isTextColumn($column)) {
466	$inputType = 'textarea';
467	}
468
469	return $inputType;
470	}
471
472	/**
473	* @throws \DI\DependencyException
474	* @throws \DI\NotFoundException
475	* @throws \Exception
476	*/
477	public function saveTableRow(string $table, $id = null, array $row = [], $is_new = false, bool $is_structure = false)
478	{
479
480	if($is_structure) {
481	return $this->saveTableStructureRow($table, $id, $row, $is_new);
482	}
483
484	$structure = $this->getTableStructure($table);
485	$primaryKey = $this->getTablePrimaryKey($table);
486	$autoIncrementKey = $this->getTableAutoIncrementKey($table);
487
488	if(empty($primaryKey)) {
489	throw new \Exception(__('Table PRIMARY KEY must be defined! You can do so within the structure view.', 'wpide'));
490	}
491
492	$primaryKeyValue = $row[$primaryKey];
493
494	if(empty($autoIncrementKey) && is_null($primaryKeyValue)) {
495	throw new \Exception(__('Primary key value cannot be empty!', 'wpide'));
496	}
497
498	$format = [];
499	foreach($row as $key => $value) {
500
501	$column = $structure[$key];
502
503	if($column->Auto_Increment) {
504	continue;
505	}
506
507	if(is_null($value) && $column->Is_Nullable) {
508	continue;
509	}
510
511	if($this->isNumericColumn($column)) {
512	$value = !is_null($value) ? $value : 0;
513	$row[$key] = $value;
514	$format[] = '%d';
515	if(!is_numeric($value)) {
516	throw new \Exception(sprintf(__('Column "%s" value should be numeric!', 'wpide'), $key));
517	}
518	}else{
519	$value = !is_null($value) ? $value : '';
520	$row[$key] = $value;
521	$format[] = '%s';
522	if(!is_string($value)) {
523	throw new \Exception(sprintf(__('Column "%s" value should be of type string!', 'wpide'), $key));
524	}
525	}
526	}
527
528	if($is_new) {
529	if($this->db->insert($table, $row, $format) !== false) {
530	return !empty($autoIncrementKey) ? $this->db->insert_id : $primaryKeyValue;
531	}
532	if($this->db->last_error) {
533	throw new \Exception($this->db->last_error);
534	}
535	return false;
536	}
537
538	$where = [$primaryKey => $id];
539	$column = $structure[$primaryKey];
540
541	$whereFormat = [];
542	if($this->isNumericColumn($column)) {
543	$whereFormat[] = '%d';
544	}else{
545	$whereFormat[] = '%s';
546	}
547
548	if($this->db->update($table, $row, $where, $format, $whereFormat) !== false) {
549	return $primaryKeyValue;
550	}
551	if($this->db->last_error) {
552	throw new \Exception($this->db->last_error);
553	}
554	return false;
555	}
556
557	/**
558	* @throws \DI\DependencyException
559	* @throws \DI\NotFoundException
560	* @throws \Exception
561	*/
562	public function deleteTableRows(string $table, array $ids = [], bool $is_structure = false): bool
563	{
564
565	if($is_structure) {
566	return $this->deleteTableStructureRows($table, $ids);
567	}
568
569	$primaryKey = $this->getTablePrimaryKey($table);
570
571	if(empty($primaryKey)) {
572	throw new \Exception(__('Table PRIMARY KEY must be defined! You can do so within the structure view.', 'wpide'));
573	}
574
575	$column = $this->getColumnInfo($table, $primaryKey);
576
577	if($this->isNumericColumn($column)) {
578	$ids = array_map( 'absint', $ids ) ;
579	}else{
580	$ids = array_map( function($id) {
581	return "'$id'";
582	}, $ids ) ;
583	}
584
585	$ids = implode( ',', $ids);
586
587	return $this->query("DELETE FROM $table WHERE $primaryKey IN($ids)");
588	}
589
590	/**
591	* @throws \Exception
592	*/
593	public function saveTableStructureRow(string $table, $name = null, array $row = [], $is_new = false)
594	{
595
596	$primaryKeyValue = $row["Name"];
597
598	if(empty($primaryKeyValue)) {
599	throw new \Exception(__('Field name cannot be empty!', 'wpide'));
600	}
601
602	$action = $is_new ? "ADD" : "CHANGE";
603	$columns = $name. ' '.$row['Name'];
604
605	$sql = "ALTER TABLE $table $action ";
606
607	$length = $this->getColumnDefaultLength((object) $row);
608
609	$nullable = 'NULL';
610	if ($row['Is_Nullable'] === false) {
611	$nullable = 'NOT NULL';
612	}
613	if($row['Type'] == "TEXT" \|\| $row['Type'] == "LONGTEXT" \|\| $row['Type'] == "MEDIUMTEXT" \|\| $row['Type'] == "TINYTEXT" \|\| $row['Type'] == "TINYBLOB" \|\| $row['Type'] == "MEDIUMBLOB" \|\| $row['Type'] == "BLOB" \|\| $row['Type'] == "LONGBLOB" \|\| $row['Type'] == "DATE" \|\| $row['Type'] == "DATETIME" \|\| $row['Type'] == "DATETIME" \|\| $row['Type'] == "TIMESTAMP" \|\| $row['Type'] == "TIME"){
614	$sql .= $columns.' '.$row['Type'].' '.$nullable;
615	} else {
616	$sql .= $columns.' '.$row['Type'].'('.$length.') '.$nullable;
617	if ($row['Auto_Increment'] === true) {
618	$sql .= ' AUTO_INCREMENT';
619	}
620	}
621
622	if ($this->query($sql)) {
623
624	$this->updateColumnIndexes($table, $row, $is_new);
625
626	return $primaryKeyValue;
627	}
628	if($this->db->last_error) {
629	throw new \Exception($this->db->last_error);
630	}
631	return false;
632	}
633
634	/**
635	* @throws \Exception
636	*/
637	public function updateColumnIndexes(string $table, array $row = [], $is_new = false)
638	{
639
640	if(!isset($row['Index_Type'])) {
641	return;
642	}
643
644	$existingPrimaryKey = $this->getTablePrimaryKey($table);
645
646	$name = $row['Name'];
647	$indexType = !empty($row['Index_Type']) ? $row['Index_Type'] : '';
648	$isPrimary = $indexType === 'PRIMARY';
649
650	if(!empty($existingPrimaryKey) && $existingPrimaryKey !== $name && $isPrimary) {
651	$this->dropTablePrimaryKey($table);
652	}
653
654	// If existing row, drop previous indexes before adding new ones
655	if(!$is_new) {
656
657	$existing_indexes = $this->db->get_results($this->db->prepare("SHOW INDEX FROM $table where column_name = %s", $name));
658
659	if (!empty($existing_indexes)) {
660	foreach ($existing_indexes as $index) {
661
662	$previousIsPrimary = $index->Key_name === 'PRIMARY';
663
664	if ($previousIsPrimary) {
665	if(!$isPrimary) {
666	throw new \Exception(__('A PRIMARY KEY is required! Before changing the INDEX type, set another field as PRIMARY.', 'wpide'));
667	}
668	} else {
669	$this->dropTableIndex($table, $index->Key_name);
670	}
671	}
672	}
673	}
674
675	if(!empty($indexType)) {
676
677	if($isPrimary) {
678	$this->addTablePrimaryKey($table, $name);
679	}else{
680	$this->addTableIndex($table, $indexType, $name);
681	}
682	}
683
684	}
685
686	/**
687	* @throws \DI\NotFoundException
688	* @throws \DI\DependencyException
689	* @throws \Exception
690	*/
691	public function deleteTableStructureRows(string $table, array $names = []): bool
692	{
693
694	$primaryKey = $this->getTablePrimaryKey($table);
695
696	$names = array_map( 'sanitize_text_field', $names ) ;
697
698	$sql = "ALTER TABLE $table ";
699	$total = count($names);
700	foreach($names as $i => $name) {
701	$sql .= "DROP COLUMN $name";
702	if($i < ($total - 1)) {
703	$sql .= ', ';
704	}
705
706	if ($primaryKey === $name) {
707	throw new \Exception(__('A PRIMARY KEY is required! Before deleting a PRIMARY field, set another field as PRIMARY.', 'wpide'));
708	}
709	}
710
711	return $this->query($sql);
712	}
713
714	/**
715	* @throws \Exception
716	*/
717	public function createTable(string $table): bool
718	{
719
720	$charset_collate = $this->db->get_charset_collate();
721
722	$sql = "
723	CREATE TABLE `$table` (
724	`id` INT NOT NULL AUTO_INCREMENT,
725	PRIMARY KEY (`id`)
726	) $charset_collate;";
727
728	return $this->query($sql);
729	}
730
731	/**
732	* @throws \Exception
733	*/
734	public function dropTablePrimaryKey($table): bool
735	{
736
737	return $this->query("ALTER TABLE $table DROP PRIMARY KEY");
738	}
739
740	/**
741	* @throws \Exception
742	*/
743	public function dropTableIndex($table, $key): bool
744	{
745
746	return $this->query("ALTER TABLE $table DROP INDEX $key");
747	}
748
749	/**
750	* @throws \Exception
751	*/
752	public function addTablePrimaryKey($table, $key): bool
753	{
754
755	$previous = $this->getTablePrimaryKey($table);
756
757	if($previous !== $key) {
758	return $this->query("ALTER TABLE $table ADD PRIMARY KEY ($key)");
759	}
760
761	return false;
762	}
763
764	/**
765	* @throws \Exception
766	*/
767	public function addTableIndex($table, $type, $key): bool
768	{
769
770	$type === 'UNIQUE' ? 'UNIQUE' : 'INDEX';
771	return $this->query("ALTER TABLE $table ADD $type ($key)");
772	}
773
774	/**
775	* @throws \Exception
776	*/
777	public function dropTable(string $table): bool
778	{
779	return $this->query("DROP table `$table`");
780	}
781
782	/**
783	* @throws \Exception
784	*/
785	public function emptyTable(string $table): bool
786	{
787	$this->query("SET FOREIGN_KEY_CHECKS = 0");
788	$success = $this->query("TRUNCATE TABLE `".DB_NAME."`.`".$table."`");
789	$this->query("SET FOREIGN_KEY_CHECKS = 1");
790
791	return $success;
792	}
793
794	/**
795	* @throws \Exception
796	*/
797	public function exportTable(string $table): string
798	{
799
800	$dump = new IMysqldump\Mysqldump('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, [
801	'add-drop-database' => true,
802	'add-drop-table' => true,
803	'include-tables' => [
804	$table
805	]
806	]);
807
808	$datetime = (new DateTime)->format('Y-m-d H\hi');
809	$dumpfile = $table.'-'.$datetime.'.sql';
810	$dumpfilePath = TMP_DIR.'/'.$dumpfile;
811
812	$dump->start($dumpfilePath);
813
814	return $dumpfile;
815	}
816
817	/**
818	* @throws \Exception
819	*/
820	public function exportDatabase(): string
821	{
822
823	$dump = new IMysqldump\Mysqldump('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, [
824	'add-drop-database' => true,
825	'add-drop-table' => true,
826	]);
827
828	$datetime = (new DateTime)->format('Y-m-d H\hi');
829	$dumpfile = DB_NAME.'-'.$datetime.'.sql';
830	$dumpfilePath = TMP_DIR.'/'.$dumpfile;
831
832	$dump->start($dumpfilePath);
833
834	return $dumpfile;
835	}
836
837	/**
838	* @throws \Exception
839	*/
840	public function exportTableStructure(string $table): bool
841	{
842	return $this->query("SHOW CREATE TABLE `".DB_NAME."`.`".$table."`");
843	}
844
845	/**
846	* @throws \Exception
847	*/
848	protected function query($sql): bool
849	{
850
851	if ($this->db->query($sql) !== false) {
852	return true;
853	}else{
854	if($this->db->last_error) {
855	throw new \Exception($this->db->last_error, 500);
856	}
857	return false;
858	}
859	}
860	}