| @@ -1,860 +1,12 @@ | ||
| 1 | 1 | <?php |
| 2 | + | |
| 2 | 3 | namespace WPIDE\App\Services\Database; |
| 3 | 4 | |
| 4 | -use DateTime; | |
| 5 | +use WPIDE\App\Classes\Freemius; | |
| 5 | 6 | use WPIDE\App\App; |
| 6 | 7 | use WPIDE\App\Services\Service; |
| 7 | 8 | use WPIDE\App\Services\Storage\wpdb; |
| 8 | 9 | use WPIDE\App\Services\Database\Traits\DatabaseSchema; |
| 9 | 10 | 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 | -} | |
| 11 | +use DateTime; | |
| 12 | +use const WPIDE\Constants\TMP_DIR; | |
