# wpide/3.1/App/Services/Database/Database.php

WPIDE – File Manager & Code Editor, version 3.1. 860 lines.

- Page: https://pluginprobe.com/plugins/wpide/3.1/code/App/Services/Database/Database.php
- Raw: https://pluginprobe.com/plugins/wpide/3.1/raw/App/Services/Database/Database.php
- Modified: 2022-07-30T21:44:40+00:00

Line numbers below start at 1. Link to a line or a range by appending a fragment to the
page URL, for example `https://pluginprobe.com/plugins/wpide/3.1/code/App/Services/Database/Database.php#L10-L20`.

```php
<?php
namespace WPIDE\App\Services\Database;

use DateTime;
use WPIDE\App\App;
use WPIDE\App\Services\Service;
use WPIDE\App\Services\Storage\wpdb;
use WPIDE\App\Services\Database\Traits\DatabaseSchema;
use Ifsnop\Mysqldump as IMysqldump;
use const WPIDE\Constants\TMP_DIR;

class Database implements Service
{
    use DatabaseSchema;

    /* @var $db \wpdb */
    protected $db;

    public function init(array $config = [])
    {
        global $wpdb;

        $this->db = $wpdb;
        $this->db->hide_errors();

    }

    public function db(): \wpdb
    {
        return $this->db;
    }

    public function getReadonlyColumns(): array
    {

        $readonly = [];

        $readonly[$this->db->users] = [
            'user_login' => [
                [
                    'key' => 'ID',
                    'value' => get_current_user_id()
                ]
            ]
        ];

        $readonly[$this->db->options] = [
            'option_name' => true,
            'option_value' => [
                [
                    'key' => 'option_name',
                    'value' => 'siteurl'
                ],
                [
                    'key' => 'option_name',
                    'value' => 'home'
                ]
            ]
        ];

        return $readonly;
    }

    public function getRestrictedRows(): array
    {

        $restricted = [];

        $restricted[$this->db->users] = [
            'ID' => get_current_user_id()
        ];

        $restricted[$this->db->options] = [
            'option_name' => ['siteurl', 'home']
        ];

        return $restricted;
    }

    public function getTables(): array
    {

        $tables = [];
        $results = $this->db->get_results('SHOW TABLES');

        foreach($results as $result) {
            $tables[] = current($result);
        }

        return [
            'db' => DB_NAME,
            'tables' => $tables,
            'readonlyColumns' => $this->getReadonlyColumns(),
            'restrictedRows' => $this->getRestrictedRows()
        ];
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getTableRows(string $table, $args = []): array
    {

        $defaults = [
            'page' => 1,
            'per_page' => 20,
            'search_value' => null,
            'search_field' => null,
        ];

        $args = array_merge($defaults, $args);

        $offset = ($args['page'] - 1) * $args['per_page'];
        $total_rows = intval($this->db->get_var('select count(*) as count from ' . sanitize_text_field($table)));
        $pages = ceil($total_rows / $args['per_page']);

        $where = '';
        if(!empty($args['search_value']) && !empty($args['search_field'])) {

            $where = $this->db->prepare('WHERE '.sanitize_text_field($args['search_field']).' LIKE %s', '%'.$this->db->esc_like($args['search_value']).'%');

        }else if(!empty($args['search_value'])) {

            $this->db->get_results('SELECT * FROM '.sanitize_text_field($table).' LIMIT 0, 1', 'ARRAY_A');
            $fields = $this->db->get_col_info();
            $where = 'WHERE ';
            $total = count($fields);
            $i = 0;
            foreach($fields as $field) {
                $where .= $this->db->prepare($field.' LIKE %s', '%' . $this->db->esc_like($args['search_value']) . '%');
                if($i < ($total - 1)) {
                    $where .= ' OR ';
                }
                $i++;
            }
        }

        $query = $this->db->prepare('SELECT * FROM '.sanitize_text_field($table).' '.$where.' LIMIT %d, %d', $offset, $args['per_page']);

        $rows = $this->db->get_results($query, 'ARRAY_A');
        $fields = $this->db->get_col_info();

        $structure = $this->getTableStructure($table);
        $primaryKey = $this->getTablePrimaryKey($table);

        return [
            'rows' => $rows,
            'offset' => $offset,
            'page' => $args['page'],
            'pages' => $pages,
            'total' => $total_rows,
            'fields' => $fields,
            'structure' => $structure,
            'primaryKey' => $primaryKey
        ];
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getTableRow(string $table, $id)
    {

        $primaryKey = $this->getTablePrimaryKey($table);

        $content = $this->getTableRows($table, [
            'search_value' => $id,
            'search_field' => $primaryKey,
        ]);

        foreach($content['rows'] as $row) {
            if($row[$primaryKey] === $id) {
                return $row;
            }
        }

        return null;
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getTableStructure(string $table): array
    {

        return App::instance()->cache()->result($table.'_structure', function() use($table) {

            $query = $this->db->prepare('
                SELECT 
                    column_name as Name,
                    UPPER(data_type) as Type,
                    column_key as Index_Type,
                    character_maximum_length as Length,
                    is_nullable as Is_Nullable,
                    extra as Auto_Increment
                FROM information_schema.columns 
                WHERE 
                    table_schema=%s 
                    AND table_name=%s
            ', DB_NAME, $table);

            $columns = $this->db->get_results($query);

            $structure = [];
            foreach($columns as $column) {
                $column->Input_Type = $this->columnInputType($column);
                $column->Is_Numeric = $this->isNumericColumn($column);
                $column->Is_Int = $this->isIntColumn($column);
                $column->Index_Type = $this->getColumnIndexKeyName($table, $column);
                $column->Is_Nullable = $this->isNullableColumn($column);
                $column->Auto_Increment = $this->isAutoIncrementColumn($column);
                $column->Length = $this->getColumnDefaultLength($column);
                $structure[$column->Name] = $column;
            }

            return $structure;
        });
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getTableStructureRows(string $table): array
    {

        $structure = $this->getTableStructure($table);
        $rows = array_values($structure);
        $fields = !empty($rows) ? array_keys((array)$rows[0]) : [];

        $dbSchema = $this->getDatabaseSchema();

        return [
            'rows' => $rows,
            'offset' => 0,
            'page' => 1,
            'pages' => 1,
            'total' => count($rows),
            'fields' => $fields,
            'structure' => $dbSchema,
            'primaryKey' => "Name"
        ];
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getTableStructureRow(string $table, string $name)
    {
        $content = $this->getTableStructureRows($table);

        foreach($content['rows'] as $row) {
            if($row->Name === $name) {
                return $row;
            }
        }

        return null;
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getTablePrimaryKey($table) {

        return App::instance()->cache()->result($table.'_pkey', function() use($table) {

            $row = $this->db->get_row('SHOW INDEX FROM '.$table.' where key_name = "PRIMARY"');
            return !empty($row) ? $row->Column_name : '';
        });
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getTableAutoIncrementKey($table) {

        return App::instance()->cache()->result($table.'_increment_key', function() use($table) {

            $rows = $this->db->get_results("DESCRIBE $table");
            $rows = array_filter($rows, function($row) {
                return $row->Extra === 'auto_increment';
            });

            $row = !empty($rows) ? array_shift($rows) : null;
            return !empty($row) ? $row->Field : null;
        });
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getColumnInfo(string $table, string $columnName) {

        return App::instance()->cache()->result($table.'_column_info', function() use($table, $columnName) {

            $structure = $this->getTableStructure($table);

            foreach ($structure as $info) {
                if ($info->Name === $columnName) {
                    return $info;
                }
            }

            return null;

        });
    }

    public function getColumnIndexKeyName($table, $column): string
    {

        $row = $this->db->get_row($this->db->prepare("SHOW INDEX FROM $table where column_name = %s", $column->Name));

        if(empty($row)) {
            return '';
        }

        if($row->Key_name === 'PRIMARY') {
            return 'PRIMARY';
        }else if($row->Non_unique === '0') {
            return 'UNIQUE';
        }else{
            return 'INDEX';
        }

        return '';
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function getColumnDefaultLength($column):? string
    {

        if(!empty($column->Length)) {
            return $column->Length;
        }

        $length = is_null($column->Length) ? '' : trim($column->Length);

        $is_int = $this->isIntColumn($column);

        if ($is_int) {
            $length = empty($length) ? '11' : $length;
        } else {
            if($column->Type ===  'VARCHAR') {
                $length = '255';
            }else if($column->Type ===  'CHAR') {
                $length = '32';
            }
        }

        return $length;
    }

    public function isNullableColumn($column): bool
    {

        return $column->Is_Nullable === 'YES' || $column->Is_Nullable === true;
    }

    public function isAutoIncrementColumn($column): bool
    {

        return $column->Auto_Increment === 'auto_increment' || $column->Auto_Increment === true;
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function isNumericColumn(Object $column): bool
    {

        return
            $this->isIntColumn($column) ||
            str_contains($column->Type, 'DECIMAL') ||
            str_contains($column->Type, 'FLOAT') ||
            str_contains($column->Type, 'DOUBLE') ||
            str_contains($column->Type, 'REAL') ||
            str_contains($column->Type, 'BIT') ||
            str_contains($column->Type, 'BOOLEAN') ||
            str_contains($column->Type, 'SERIAL');

    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function isIntColumn(Object $column): bool
    {

        return str_contains($column->Type, 'INT');
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function isDateColumn(Object $column): bool
    {

        return $column->Type === 'DATE';
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function isDateTimeColumn(Object $column): bool
    {

        return $column->Type === 'DATETIME';
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function isBooleanColumn(Object $column): bool
    {

        return $column->Type === 'BOOLEAN';
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function isTextColumn(Object $column): bool
    {

        return
            str_contains($column->Type, 'TEXT') ||
            str_contains($column->Type, 'JSON');

    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     */
    public function columnInputType(Object $column): string
    {
        $inputType = 'text';

        if($this->isBooleanColumn($column)) {
            $inputType = 'boolean';
        }else if($this->isNumericColumn($column)) {
            $inputType = 'number';
        }else if($this->isDateColumn($column)) {
            $inputType = 'date';
        }else if($this->isDateTimeColumn($column)) {
            $inputType = 'datetime';
        }else if($this->isTextColumn($column)) {
            $inputType = 'textarea';
        }

        return $inputType;
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     * @throws \Exception
     */
    public function saveTableRow(string $table, $id = null, array $row = [], $is_new = false, bool $is_structure = false)
    {

        if($is_structure) {
            return $this->saveTableStructureRow($table, $id, $row, $is_new);
        }

        $structure = $this->getTableStructure($table);
        $primaryKey = $this->getTablePrimaryKey($table);
        $autoIncrementKey = $this->getTableAutoIncrementKey($table);

        if(empty($primaryKey)) {
            throw new \Exception(__('Table PRIMARY KEY must be defined! You can do so within the structure view.', 'wpide'));
        }

        $primaryKeyValue = $row[$primaryKey];

        if(empty($autoIncrementKey) && is_null($primaryKeyValue)) {
            throw new \Exception(__('Primary key value cannot be empty!', 'wpide'));
        }

        $format = [];
        foreach($row as $key => $value) {

            $column = $structure[$key];

            if($column->Auto_Increment) {
                continue;
            }

            if(is_null($value) && $column->Is_Nullable) {
                continue;
            }

            if($this->isNumericColumn($column)) {
                $value = !is_null($value) ? $value : 0;
                $row[$key] = $value;
                $format[] = '%d';
                if(!is_numeric($value)) {
                    throw new \Exception(sprintf(__('Column "%s" value should be numeric!', 'wpide'), $key));
                }
            }else{
                $value = !is_null($value) ? $value : '';
                $row[$key] = $value;
                $format[] = '%s';
                if(!is_string($value)) {
                    throw new \Exception(sprintf(__('Column "%s" value should be of type string!', 'wpide'), $key));
                }
            }
        }

        if($is_new) {
            if($this->db->insert($table, $row, $format) !== false) {
                return !empty($autoIncrementKey) ? $this->db->insert_id : $primaryKeyValue;
            }
            if($this->db->last_error) {
                throw new \Exception($this->db->last_error);
            }
            return false;
        }

        $where = [$primaryKey => $id];
        $column = $structure[$primaryKey];

        $whereFormat = [];
        if($this->isNumericColumn($column)) {
            $whereFormat[] = '%d';
        }else{
            $whereFormat[] = '%s';
        }

        if($this->db->update($table, $row, $where, $format, $whereFormat) !== false) {
            return $primaryKeyValue;
        }
        if($this->db->last_error) {
            throw new \Exception($this->db->last_error);
        }
        return false;
    }

    /**
     * @throws \DI\DependencyException
     * @throws \DI\NotFoundException
     * @throws \Exception
     */
    public function deleteTableRows(string $table, array $ids = [], bool $is_structure = false): bool
    {

        if($is_structure) {
            return $this->deleteTableStructureRows($table, $ids);
        }

        $primaryKey = $this->getTablePrimaryKey($table);

        if(empty($primaryKey)) {
            throw new \Exception(__('Table PRIMARY KEY must be defined! You can do so within the structure view.', 'wpide'));
        }

        $column = $this->getColumnInfo($table, $primaryKey);

        if($this->isNumericColumn($column)) {
            $ids = array_map( 'absint', $ids ) ;
        }else{
            $ids = array_map( function($id) {
                return "'$id'";
            }, $ids ) ;
        }

        $ids = implode( ',', $ids);

        return $this->query("DELETE FROM $table WHERE $primaryKey IN($ids)");
    }

    /**
     * @throws \Exception
     */
    public function saveTableStructureRow(string $table, $name = null, array $row = [], $is_new = false)
    {

        $primaryKeyValue = $row["Name"];

        if(empty($primaryKeyValue)) {
            throw new \Exception(__('Field name cannot be empty!', 'wpide'));
        }

        $action = $is_new ? "ADD" : "CHANGE";
        $columns = $name. ' '.$row['Name'];

        $sql = "ALTER TABLE $table $action ";

        $length = $this->getColumnDefaultLength((object) $row);

        $nullable = 'NULL';
        if ($row['Is_Nullable'] === false) {
            $nullable = 'NOT NULL';
        }
        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"){
            $sql .= $columns.' '.$row['Type'].' '.$nullable;
        } else {
            $sql .= $columns.' '.$row['Type'].'('.$length.') '.$nullable;
            if ($row['Auto_Increment'] === true) {
                $sql .= ' AUTO_INCREMENT';
            }
        }

        if ($this->query($sql)) {

            $this->updateColumnIndexes($table, $row, $is_new);

            return $primaryKeyValue;
        }
        if($this->db->last_error) {
            throw new \Exception($this->db->last_error);
        }
        return false;
    }

    /**
     * @throws \Exception
     */
    public function updateColumnIndexes(string $table, array $row = [], $is_new = false)
    {

        if(!isset($row['Index_Type'])) {
            return;
        }
        
        $existingPrimaryKey = $this->getTablePrimaryKey($table);

        $name = $row['Name'];
        $indexType = !empty($row['Index_Type']) ? $row['Index_Type'] : '';
        $isPrimary = $indexType === 'PRIMARY';

        if(!empty($existingPrimaryKey) && $existingPrimaryKey !== $name && $isPrimary) {
            $this->dropTablePrimaryKey($table);
        }

        // If existing row, drop previous indexes before adding new ones
        if(!$is_new) {

            $existing_indexes = $this->db->get_results($this->db->prepare("SHOW INDEX FROM $table where column_name = %s", $name));

            if (!empty($existing_indexes)) {
                foreach ($existing_indexes as $index) {

                    $previousIsPrimary = $index->Key_name === 'PRIMARY';

                    if ($previousIsPrimary) {
                        if(!$isPrimary) {
                            throw new \Exception(__('A PRIMARY KEY is required! Before changing the INDEX type, set another field as PRIMARY.', 'wpide'));
                        }
                    } else {
                        $this->dropTableIndex($table, $index->Key_name);
                    }
                }
            }
        }

        if(!empty($indexType)) {

            if($isPrimary) {
                $this->addTablePrimaryKey($table, $name);
            }else{
                $this->addTableIndex($table, $indexType, $name);
            }
        }

    }

    /**
     * @throws \DI\NotFoundException
     * @throws \DI\DependencyException
     * @throws \Exception
     */
    public function deleteTableStructureRows(string $table, array $names = []): bool
    {

        $primaryKey = $this->getTablePrimaryKey($table);

        $names = array_map( 'sanitize_text_field', $names ) ;

        $sql = "ALTER TABLE $table ";
        $total = count($names);
        foreach($names as $i => $name) {
            $sql .= "DROP COLUMN $name";
            if($i < ($total - 1)) {
                $sql .= ', ';
            }

            if ($primaryKey === $name) {
                throw new \Exception(__('A PRIMARY KEY is required! Before deleting a PRIMARY field, set another field as PRIMARY.', 'wpide'));
            }
        }

        return $this->query($sql);
    }

    /**
     * @throws \Exception
     */
    public function createTable(string $table): bool
    {

        $charset_collate = $this->db->get_charset_collate();

        $sql = "
        CREATE TABLE `$table` (
          `id` INT NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (`id`)
        ) $charset_collate;";

        return $this->query($sql);
    }

    /**
     * @throws \Exception
     */
    public function dropTablePrimaryKey($table): bool
    {

        return $this->query("ALTER TABLE $table DROP PRIMARY KEY");
    }

    /**
     * @throws \Exception
     */
    public function dropTableIndex($table, $key): bool
    {

        return $this->query("ALTER TABLE $table DROP INDEX $key");
    }

    /**
     * @throws \Exception
     */
    public function addTablePrimaryKey($table, $key): bool
    {

        $previous = $this->getTablePrimaryKey($table);

        if($previous !== $key) {
            return $this->query("ALTER TABLE $table ADD PRIMARY KEY ($key)");
        }

        return false;
    }

    /**
     * @throws \Exception
     */
    public function addTableIndex($table, $type, $key): bool
    {

        $type === 'UNIQUE' ? 'UNIQUE' : 'INDEX';
        return $this->query("ALTER TABLE $table ADD $type ($key)");
    }

    /**
     * @throws \Exception
     */
    public function dropTable(string $table): bool
    {
        return $this->query("DROP table `$table`");
    }

    /**
     * @throws \Exception
     */
    public function emptyTable(string $table): bool
    {
        $this->query("SET FOREIGN_KEY_CHECKS = 0");
        $success = $this->query("TRUNCATE TABLE `".DB_NAME."`.`".$table."`");
        $this->query("SET FOREIGN_KEY_CHECKS = 1");

        return $success;
    }

    /**
     * @throws \Exception
     */
    public function exportTable(string $table): string
    {

        $dump = new IMysqldump\Mysqldump('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, [
            'add-drop-database' => true,
            'add-drop-table' => true,
            'include-tables' => [
                $table
            ]
        ]);

        $datetime = (new DateTime)->format('Y-m-d H\hi');
        $dumpfile = $table.'-'.$datetime.'.sql';
        $dumpfilePath = TMP_DIR.'/'.$dumpfile;

        $dump->start($dumpfilePath);

        return $dumpfile;
    }

    /**
     * @throws \Exception
     */
    public function exportDatabase(): string
    {

        $dump = new IMysqldump\Mysqldump('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, [
            'add-drop-database' => true,
            'add-drop-table' => true,
        ]);

        $datetime = (new DateTime)->format('Y-m-d H\hi');
        $dumpfile = DB_NAME.'-'.$datetime.'.sql';
        $dumpfilePath = TMP_DIR.'/'.$dumpfile;

        $dump->start($dumpfilePath);

        return $dumpfile;
    }

    /**
     * @throws \Exception
     */
    public function exportTableStructure(string $table): bool
    {
        return $this->query("SHOW CREATE TABLE `".DB_NAME."`.`".$table."`");
    }

    /**
     * @throws \Exception
     */
    protected function query($sql): bool
    {

        if ($this->db->query($sql) !== false) {
            return true;
        }else{
            if($this->db->last_error) {
                throw new \Exception($this->db->last_error, 500);
            }
            return false;
        }
    }
}
```