MysqlDriver::sqlCreateTableOptions() - Code Metrics - kristuff/patabase - Measure and Improve Code Quality continuously with Scrutinizer

MysqlDriver::sqlCreateTableOptions() A
last analyzed 2022-04-08 21:26 UTC

↳ Parent: MysqlDriver

Complexity

Conditions	4
Paths	8

Size

Total Lines	6
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	4
eloc	4
c	1
b	0
f	0
nc	8
nop	0
dl	0
loc	6
rs	10

<?php declare(strict_types=1);

/** 
 *  ___      _        _
 * | _ \__ _| |_ __ _| |__  __ _ ___ ___
 * |  _/ _` |  _/ _` | '_ \/ _` (_-</ -_)
 * |_| \__,_|\__\__,_|_.__/\__,_/__/\___|
 * 
 * This file is part of Kristuff\Patabase.
 * (c) Kristuff <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @version    1.0.1
 * @copyright  2017-2022 Christophe Buliard
 */

namespace Kristuff\Patabase\Driver\Mysql;

use Kristuff\Patabase\Driver\ServerDriver;

/**
 * Class MysqlDriver
 *
 * Data types:
 *  CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET
 *  INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
 *  DECIMAL, NUMERIC
 *  FLOAT, DOUBLE
 *  BIT(n)
 *   
 */
class MysqlDriver extends ServerDriver
{
    /**
     * List of DSN attributes
     *
     * @access protected
     * @var array
     */
    protected $dsnAttributes = array(
        'hostname',
        'username',
        'password',
        'database'
    );

    /**
     * Escape identifier
     *
     * @access public
     * @param string  $identifier
     *
     * @return string
     */
    public function escapeIdentifier(string $identifier): string
    {
        return '`' . $identifier .'`';
    }

    /**
     * Escape value
     *
     * @access public
     * @param string  $value
     *
     * @return string
     */
    public function escapeValue(string $value): string
    {
        return "'".$value."'";
    }

    /**
     * Create a new PDO connection
     *
     * @access public
     * @param array   $settings
     *
     * @return void
     */
    public function createConnection(array $settings): void
    {
        $charset = !empty($settings['charset'])  ?  ';charset='.$settings['charset']  : ';charset=utf8';
        $port    = !empty($settings['port'])     ?  ';port='.$settings['port']        : '';
        $dbname  = !empty($settings['database']) ?  ';dbname='.$settings['database']  : '';
        $options = [
          //  \PDO::ATTR_EMULATE_PREPARES => false,
        ];

        $this->pdo = new \PDO(
            'mysql:host='.$settings['hostname'] .$port .$dbname .$charset,
            $settings['username'],
            $settings['password'],
            $options
        );

        // emulate prepare is true by default in mysql
        // TODO 
        //  $this->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
        //  $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    }

    /**
     * Get last inserted id
     *
     * @access public
     * @return string
     */
    public function lastInsertedId(): string
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * Enable foreign keys
     *
     * @access public
     * @return void
     */
    public function enableForeignKeys(): void
    {
        $this->pdo->exec('SET FOREIGN_KEY_CHECKS=1');
    }

    /**
     * Disable foreign keys
     *
     * @access public
     * @return void
     */
    public function disableForeignKeys(): void
    {
        $this->pdo->exec('SET FOREIGN_KEY_CHECKS=0');
    }

    /**
     * Get whether foreign keys are enabled or not
     * For compatibility with Sqlite, not implemented in that driver (allways enabled), return true
     * 
     * @access public
     * @return bool     true if foreign keys are enabled, otherwise false
     */
    public function isForeignKeyEnabled(): bool
    {
        return true;
    }
    
    /**
     * Add a foreign key
     * 
     * @access public
     * @param string   $fkName         The constraint name
     * @param string   $srcTable       The source table
     * @param string   $srcColumn      The source column 
     * @param string   $refTable       The referenced table
     * @param string   $refColumn      The referenced column
     *
     * @return bool    True if the foreign key has been created, otherwise false
     */
    public function addForeignKey(string $fkName, string $srcTable, string $srcColumn, string $refTable, string $refColumn): bool
    {
        $sql = sprintf('ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s)',
                       $this->escape($srcTable),
                       $fkName,
                       $this->escape($srcColumn),
                       $this->escape($refTable),
                       $this->escape($refColumn)
        );
        return $this->prepareAndExecuteSql($sql);
    }

    /**
     * Drop a foreign key
     * 
     * @access public
     * @param string   $fkName         The constraint name
     * @param string   $tableName      The source table
     *
     * @return bool    True if the foreign key has been dropped, otherwise false
     */
    public function dropForeignKey(string $fkName, string $tableName): bool
    {
        $sql = sprintf('ALTER TABLE %s DROP FOREIGN KEY %s',
                       $this->escape($tableName),
                       $fkName
        );
        return $this->prepareAndExecuteSql($sql);
    }
    
    /**
     * Checks if a database exists
     *
     * @access public
     * @param string   $databaseName   The database name
     *
     * @return bool     True if the given database exists, otherwise false.
     */
    public function databaseExists(string $databaseName) : bool
    {
        $sql = 'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = :dbName'; 
        $query = $this->pdo->prepare($sql);
        $query->bindValue(':dbName',  $databaseName, \PDO::PARAM_STR);
        $query->execute();
        return (bool) $query->fetchColumn();
    }

    /**
     * Create a database
     *
     * @access public
     * @param string   $databaseName   The database name
     * @param string   $owner          The database owner. This parameter is not honored in Mysql. Default is null
     * @param string   $template       The template to use. This parameter is not honored in Mysql. Default is null
     *
     * @return bool     True if the database has been created, otherwise false.
     */
    public function createDatabase(string $databaseName, ?string $owner = null, ?string $template = null): bool
    {
        $sql = trim(sprintf('CREATE DATABASE %s',  $this->escape($databaseName)));
        return $this->prepareAndExecuteSql($sql);
    }
    
    /**
     * Create a user
     *
     * @access public
     * @param string   $userName         The user name
     * @param string   $userpassword     The user password
     *
     * @return bool     True if the user has been created, otherwise false. 
     */
    public function createUser(string $userName, string $userPassword) : bool
    {
        $sql = trim(sprintf('CREATE USER %s@%s IDENTIFIED BY %s', 
                    $this->escape($userName),
                    $this->escape($this->getHostName()),
                    "'" . $userPassword ."'"
        ));
        return $this->prepareAndExecuteSql($sql);
   }

    /**
     * Drop a user
     *
     * @access public
     * @param string   $userName         The user name
     * @param bool     $ifExists         (optional) True if the user must be deleted only when exists. Default is false.
     *
     * @return bool     True if the user has been dropped or does not exist when $ifExists is set to True, otherwise false. 
     */
    public function dropUser(string $userName, bool $ifExists = false): bool
    {
        $sql = trim(sprintf('DROP USER %s %s@%s', 
                    $ifExists === true ? 'IF EXISTS': '',
                    $this->escape($userName),
                    $this->escape($this->getHostName())
        ));
        return $this->prepareAndExecuteSql($sql);
    }

    /**
     * Grant user permissions on given database
     *
     * @access public
     * @param string   $databaseName     The database name
     * @param string   $userName         The user name
     *
     * @return bool     True if the user has been granted, otherwise false. 
     */
     public function grantUser(string $databaseName, string $userName): bool
    {
        $sql = trim(sprintf('GRANT ALL ON %s.* TO %s@%s; FLUSH PRIVILEGES;', 
            $this->escape($databaseName),
            $this->escape($userName),
            $this->escape($this->getHostName())
        ));
        return $this->prepareAndExecuteSql($sql);
    }

    /**
     * Get the SQL for show databases
     *
     * @access public
     * @return string
     */
    public function sqlShowDatabases(): string
    {
        return 'SHOW DATABASES';
    }

    /**
     * Get the SQL for show tables
     *
     * @access public
     * @return string
     */
    public function sqlShowTables(): string
    {
        return 'SHOW TABLES';
    }

    /**
     * Get the SQL for show users
     *
     * @access public
     * @return string
     */
    public function sqlShowUsers(): string
    {
        return 'SELECT DISTINCT user FROM mysql.user';
    }

    /**
     * Get the options for CREATE TABLE query
     *
     * @access protected
     * @return string
     */
    public function sqlCreateTableOptions(): string 
    {
        $engine =  !empty($settings['engine'])  ? $settings['engine']  : 'InnoDB';

        $charset = !empty($settings['charset']) ? $settings['charset'] : 'utf8';
        $collate = !empty($settings['collate']) ? $settings['collate'] : 'utf8_unicode_ci';
        return sprintf('ENGINE=%s DEFAULT CHARSET=%s COLLATE=%s;', $engine, $charset, $collate);
    } 
    
    /**
     * Get the SQL for random function 
     *
     * @access public
     * @param  int      $seed    The random seed. Default is null.
     *
     * @return string         
     */
    public function sqlRandom($seed = null): string
    {
        return sprintf('rand(%s)', !empty($seed) ? $seed : '');   
    }

    /**
     * Gets/returns the SQL for auto increment column.
     *
     * @access public
     * @param string   $type   The sql column type
     * 
     * @return string
     */
    public function sqlColumnAutoIncrement(string $type): string
    {
        return $type .' AUTO_INCREMENT';
    }
    
}


1			<?php declare(strict_types=1);
2
3			/**
4			* ___ _ _
5			* \| _ \__ _\| \|_ __ _\| \|__ __ _ ___ ___
6			* \| _/ _` \| _/ _` \| '_ \/ _` (_-</ -_)
7			* \|_\| \__,_\|\__\__,_\|_.__/\__,_/__/\___\|
8			*
9			* This file is part of Kristuff\Patabase.
10			* (c) Kristuff <[email protected]>
11			*
12			* For the full copyright and license information, please view the LICENSE
13			* file that was distributed with this source code.
14			*
15			* @version 1.0.1
16			* @copyright 2017-2022 Christophe Buliard
17			*/
18
19			namespace Kristuff\Patabase\Driver\Mysql;
20
21			use Kristuff\Patabase\Driver\ServerDriver;
22
23			/**
24			* Class MysqlDriver
25			*
26			* Data types:
27			* CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET
28			* INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
29			* DECIMAL, NUMERIC
30			* FLOAT, DOUBLE
31			* BIT(n)
32			*
33			*/
34			class MysqlDriver extends ServerDriver
35			{
36			/**
37			* List of DSN attributes
38			*
39			* @access protected
40			* @var array
41			*/
42			protected $dsnAttributes = array(
43			'hostname',
44			'username',
45			'password',
46			'database'
47			);
48
49			/**
50			* Escape identifier
51			*
52			* @access public
53			* @param string $identifier
54			*
55			* @return string
56			*/
57			public function escapeIdentifier(string $identifier): string
58			{
59			return '`' . $identifier .'`';
60			}
61
62			/**
63			* Escape value
64			*
65			* @access public
66			* @param string $value
67			*
68			* @return string
69			*/
70			public function escapeValue(string $value): string
71			{
72			return "'".$value."'";
73			}
74
75			/**
76			* Create a new PDO connection
77			*
78			* @access public
79			* @param array $settings
80			*
81			* @return void
82			*/
83			public function createConnection(array $settings): void
84			{
85			$charset = !empty($settings['charset']) ? ';charset='.$settings['charset'] : ';charset=utf8';
86			$port = !empty($settings['port']) ? ';port='.$settings['port'] : '';
87			$dbname = !empty($settings['database']) ? ';dbname='.$settings['database'] : '';
88			$options = [
89			// \PDO::ATTR_EMULATE_PREPARES => false,
90			];
91
92			$this->pdo = new \PDO(
93			'mysql:host='.$settings['hostname'] .$port .$dbname .$charset,
94			$settings['username'],
95			$settings['password'],
96			$options
97			);
98
99			// emulate prepare is true by default in mysql
100			// TODO
101			// $this->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
102			// $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
103			}
104
105			/**
106			* Get last inserted id
107			*
108			* @access public
109			* @return string
110			*/
111			public function lastInsertedId(): string
112			{
113			return $this->pdo->lastInsertId();
114			}
115
116			/**
117			* Enable foreign keys
118			*
119			* @access public
120			* @return void
121			*/
122			public function enableForeignKeys(): void
123			{
124			$this->pdo->exec('SET FOREIGN_KEY_CHECKS=1');
125			}
126
127			/**
128			* Disable foreign keys
129			*
130			* @access public
131			* @return void
132			*/
133			public function disableForeignKeys(): void
134			{
135			$this->pdo->exec('SET FOREIGN_KEY_CHECKS=0');
136			}
137
138			/**
139			* Get whether foreign keys are enabled or not
140			* For compatibility with Sqlite, not implemented in that driver (allways enabled), return true
141			*
142			* @access public
143			* @return bool true if foreign keys are enabled, otherwise false
144			*/
145			public function isForeignKeyEnabled(): bool
146			{
147			return true;
148			}
149
150			/**
151			* Add a foreign key
152			*
153			* @access public
154			* @param string $fkName The constraint name
155			* @param string $srcTable The source table
156			* @param string $srcColumn The source column
157			* @param string $refTable The referenced table
158			* @param string $refColumn The referenced column
159			*
160			* @return bool True if the foreign key has been created, otherwise false
161			*/
162			public function addForeignKey(string $fkName, string $srcTable, string $srcColumn, string $refTable, string $refColumn): bool
163			{
164			$sql = sprintf('ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s)',
165			$this->escape($srcTable),
166			$fkName,
167			$this->escape($srcColumn),
168			$this->escape($refTable),
169			$this->escape($refColumn)
170			);
171			return $this->prepareAndExecuteSql($sql);
172			}
173
174			/**
175			* Drop a foreign key
176			*
177			* @access public
178			* @param string $fkName The constraint name
179			* @param string $tableName The source table
180			*
181			* @return bool True if the foreign key has been dropped, otherwise false
182			*/
183			public function dropForeignKey(string $fkName, string $tableName): bool
184			{
185			$sql = sprintf('ALTER TABLE %s DROP FOREIGN KEY %s',
186			$this->escape($tableName),
187			$fkName
188			);
189			return $this->prepareAndExecuteSql($sql);
190			}
191
192			/**
193			* Checks if a database exists
194			*
195			* @access public
196			* @param string $databaseName The database name
197			*
198			* @return bool True if the given database exists, otherwise false.
199			*/
200			public function databaseExists(string $databaseName) : bool
201			{
202			$sql = 'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = :dbName';
203			$query = $this->pdo->prepare($sql);
204			$query->bindValue(':dbName', $databaseName, \PDO::PARAM_STR);
205			$query->execute();
206			return (bool) $query->fetchColumn();
207			}
208
209			/**
210			* Create a database
211			*
212			* @access public
213			* @param string $databaseName The database name
214			* @param string $owner The database owner. This parameter is not honored in Mysql. Default is null
215			* @param string $template The template to use. This parameter is not honored in Mysql. Default is null
216			*
217			* @return bool True if the database has been created, otherwise false.
218			*/
219			public function createDatabase(string $databaseName, ?string $owner = null, ?string $template = null): bool
220			{
221			$sql = trim(sprintf('CREATE DATABASE %s', $this->escape($databaseName)));
222			return $this->prepareAndExecuteSql($sql);
223			}
224
225			/**
226			* Create a user
227			*
228			* @access public
229			* @param string $userName The user name
230			* @param string $userpassword The user password
231			*
232			* @return bool True if the user has been created, otherwise false.
233			*/
234			public function createUser(string $userName, string $userPassword) : bool
235			{
236			$sql = trim(sprintf('CREATE USER %s@%s IDENTIFIED BY %s',
237			$this->escape($userName),
238			$this->escape($this->getHostName()),
239			"'" . $userPassword ."'"
240			));
241			return $this->prepareAndExecuteSql($sql);
242			}
243
244			/**
245			* Drop a user
246			*
247			* @access public
248			* @param string $userName The user name
249			* @param bool $ifExists (optional) True if the user must be deleted only when exists. Default is false.
250			*
251			* @return bool True if the user has been dropped or does not exist when $ifExists is set to True, otherwise false.
252			*/
253			public function dropUser(string $userName, bool $ifExists = false): bool
254			{
255			$sql = trim(sprintf('DROP USER %s %s@%s',
256			$ifExists === true ? 'IF EXISTS': '',
257			$this->escape($userName),
258			$this->escape($this->getHostName())
259			));
260			return $this->prepareAndExecuteSql($sql);
261			}
262
263			/**
264			* Grant user permissions on given database
265			*
266			* @access public
267			* @param string $databaseName The database name
268			* @param string $userName The user name
269			*
270			* @return bool True if the user has been granted, otherwise false.
271			*/
272			public function grantUser(string $databaseName, string $userName): bool
273			{
274			$sql = trim(sprintf('GRANT ALL ON %s.* TO %s@%s; FLUSH PRIVILEGES;',
275			$this->escape($databaseName),
276			$this->escape($userName),
277			$this->escape($this->getHostName())
278			));
279			return $this->prepareAndExecuteSql($sql);
280			}
281
282			/**
283			* Get the SQL for show databases
284			*
285			* @access public
286			* @return string
287			*/
288			public function sqlShowDatabases(): string
289			{
290			return 'SHOW DATABASES';
291			}
292
293			/**
294			* Get the SQL for show tables
295			*
296			* @access public
297			* @return string
298			*/
299			public function sqlShowTables(): string
300			{
301			return 'SHOW TABLES';
302			}
303
304			/**
305			* Get the SQL for show users
306			*
307			* @access public
308			* @return string
309			*/
310			public function sqlShowUsers(): string
311			{
312			return 'SELECT DISTINCT user FROM mysql.user';
313			}
314
315			/**
316			* Get the options for CREATE TABLE query
317			*
318			* @access protected
319			* @return string
320			*/
321			public function sqlCreateTableOptions(): string
322			{
323			$engine = !empty($settings['engine']) ? $settings['engine'] : 'InnoDB';
			0 ignored issues – show Comprehensibility Best Practice introduced 2020-04-20 13:52 UTC by Report Bug Copy Issue Report The variable `$settings` seems to never exist and therefore `empty` should always be `true`. Loading history...
324			$charset = !empty($settings['charset']) ? $settings['charset'] : 'utf8';
325			$collate = !empty($settings['collate']) ? $settings['collate'] : 'utf8_unicode_ci';
326			return sprintf('ENGINE=%s DEFAULT CHARSET=%s COLLATE=%s;', $engine, $charset, $collate);
327			}
328
329			/**
330			* Get the SQL for random function
331			*
332			* @access public
333			* @param int $seed The random seed. Default is null.
334			*
335			* @return string
336			*/
337			public function sqlRandom($seed = null): string
338			{
339			return sprintf('rand(%s)', !empty($seed) ? $seed : '');
340			}
341
342			/**
343			* Gets/returns the SQL for auto increment column.
344			*
345			* @access public
346			* @param string $type The sql column type
347			*
348			* @return string
349			*/
350			public function sqlColumnAutoIncrement(string $type): string
351			{
352			return $type .' AUTO_INCREMENT';
353			}
354
355			}
356

kristuff / patabase

MysqlDriver::sqlCreateTableOptions() A last analyzed 2022-04-08 21:26 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

MysqlDriver::sqlCreateTableOptions() A
last analyzed 2022-04-08 21:26 UTC