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

PostgresDriver::addForeignKey() A
last analyzed 2022-04-08 21:26 UTC

↳ Parent: PostgresDriver

Complexity

Conditions	1
Paths	1

Size

Total Lines	10
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	7
c	1
b	0
f	0
nc	1
nop	5
dl	0
loc	10
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\Postgres;

use Kristuff\Patabase\Driver\ServerDriver;

/**
 * Class Driver
 *
 * Postgres Sql Driver
 * 
 * Data types (main):
 *  [Numeric Types]
 *      smallint, integer, bigint, (serial/bigserial)
 *      decimal, numeric 	
 *      real, double
 *  [Character Types] 
 *      character varying(n), varchar(n) 
 *      character(n), char(n) 
 *      text
 *  [Binary Data Types]
 *      bytea
 *  [Date/Time Types]
 *      timestamp, time, date, interval
 *  [Boolean Type]
 *      boolean     (literal: TRUE 't' 'true' 'y' 'yes' 'on' '1' | FALSE 'f' 'false' 'n' 'no' 'off' '0')
 *  [Geometric Types] 
 *  [Network Address Types]
 *  [Bit String Types]
 *      bit(n), bit varying(n)  cast...
 *  [UUID Type]
 *      uuid        
 */
class PostgresDriver extends ServerDriver
{
    /**
     * List of DSN attributes
     *
     * @access protected
     * @var array
     */
    protected $dsnAttributes = array(
        'hostname',
        'username',
        'password',
        'database'
    );

    /**
     * Escape an identifier
     *
     * @access public
     * @param string  $identifier
     *
     * @return string
     */
    public function escapeIdentifier(string $identifier) : string
    {
        return '"'.$identifier.'"';
    }
     
    /**
     * Escape a 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
    {
        $port    = !empty($settings['port'])     ?  ';port='.$settings['port']        : '';
        $dbname  = !empty($settings['database']) ?  ';dbname='.$settings['database']  : '';
        $dsn     = 'pgsql:host='.$settings['hostname'] .$port .$dbname ;

        $this->pdo = new \PDO(
            $dsn,
            $settings['username'],
            $settings['password'],
            array()
        );

        // make sure emulate prepare is false 
        //$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
    {
       // Postgres does not set pdo->lastInsertedId
       // use sequence
       try {
            $rq = $this->pdo->prepare('SELECT LASTVAL()');
            $rq->execute();
            // return string 
            return strval($rq->fetchColumn());
        }
        catch (\PDOException $e) {
            return 0;
        }
    }

    /**
     * Enable foreign keys
     *
     * @access public
     * @return void
     */
    public function enableForeignKeys(): void
    {
    }

    /**
     * Disable foreign keys
     *
     * @access public
     * @return void
     */
    public function disableForeignKeys(): void
    {
    }
    
    /**
     * 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 $ifExists = false): bool
    {
        $sql = sprintf('ALTER TABLE %s DROP CONSTRAINT %s %s',
                       $this->escape($tableName),
                       $ifExists ? 'IF EXISTS' : '',
                       $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 COUNT(*) FROM pg_database WHERE datname = :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. 
     * @param string   $template       The template to use. Default is 'template0'
     *
     * @return bool     True if the database has been created, otherwise false.
     */
    public function createDatabase(string $databaseName, ?string $owner= null, ?string $template = 'template0'): bool
    {
        $sql = trim(sprintf('CREATE DATABASE %s %s TEMPLATE %s', 
            $this->escape($databaseName),
            isset($owner) ? 'OWNER '. $this->escape($owner) : '', 
            $template
        ));
        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 PASSWORD %s', 
                    $this->escape($userName), 
                    "'" . $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', 
                    $ifExists === true ? 'IF EXISTS': '',
                    $this->escape($userName)
        ));
        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
    {
        // ALL PRIVILEGES Grant all of the available privileges at once. The PRIVILEGES keyword 
        // is optional in PostgreSQL, though it is required by strict SQL.

        // GRANT CONNECT ON DATABASE database_name TO user_name;
        //$sql = trim(sprintf('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %s TO %s;', 
        $sql = trim(sprintf('GRANT CONNECT ON DATABASE %s TO %s;', 
            $this->escape($databaseName),
            $this->escape($userName)
        ));
        return $this->prepareAndExecuteSql($sql);
    }

    /**
     * Get the SQL for show databases
     *
     * @access public
     * @return string
     */
    public function sqlShowDatabases(): string
    {
        return 'SELECT datname FROM pg_database WHERE datistemplate = false;';
    }

    /**
     * Get the SQL for show tables
     *
     * @access public
     * @return string
     */
    public function sqlShowTables(): string
    {
        return "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type = 'BASE TABLE' ORDER BY table_name;";
    }

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

    /**
     * Get the SQL for random function 
     *
     * Parameter $seed is not honored in Postgres.
     *
     * @access public
     * @param  int   $seed    The random seed. Default is null. 
     *
     * @return string         
     */
    public function sqlRandom($seed = null): string
    {
        return 'random()';   
    }

    /**
     * Get the SQL for auto increment column
     *
     * @access public
     * @param string   $type   The sql column type
     * 
     * @return string
     */
    public function sqlColumnAutoIncrement(string $type): string
    {
        // SERIAL/BIGSERIAL is a type in postgres
        return strtolower($type) === 'bigint' ? 'bigserial' : 'serial';
    }



}

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\Postgres;
20
21			use Kristuff\Patabase\Driver\ServerDriver;
22
23			/**
24			* Class Driver
25			*
26			* Postgres Sql Driver
27			*
28			* Data types (main):
29			* [Numeric Types]
30			* smallint, integer, bigint, (serial/bigserial)
31			* decimal, numeric
32			* real, double
33			* [Character Types]
34			* character varying(n), varchar(n)
35			* character(n), char(n)
36			* text
37			* [Binary Data Types]
38			* bytea
39			* [Date/Time Types]
40			* timestamp, time, date, interval
41			* [Boolean Type]
42			* boolean (literal: TRUE 't' 'true' 'y' 'yes' 'on' '1' \| FALSE 'f' 'false' 'n' 'no' 'off' '0')
43			* [Geometric Types]
44			* [Network Address Types]
45			* [Bit String Types]
46			* bit(n), bit varying(n) cast...
47			* [UUID Type]
48			* uuid
49			*/
50			class PostgresDriver extends ServerDriver
51			{
52			/**
53			* List of DSN attributes
54			*
55			* @access protected
56			* @var array
57			*/
58			protected $dsnAttributes = array(
59			'hostname',
60			'username',
61			'password',
62			'database'
63			);
64
65			/**
66			* Escape an identifier
67			*
68			* @access public
69			* @param string $identifier
70			*
71			* @return string
72			*/
73			public function escapeIdentifier(string $identifier) : string
74			{
75			return '"'.$identifier.'"';
76			}
77
78			/**
79			* Escape a value
80			*
81			* @access public
82			* @param string $value
83			*
84			* @return string
85			*/
86			public function escapeValue(string $value): string
87			{
88			return "'".$value."'";
89			}
90
91			/**
92			* Create a new PDO connection
93			*
94			* @access public
95			* @param array $settings
96			*
97			* @return void
98			*/
99			public function createConnection(array $settings): void
100			{
101			$port = !empty($settings['port']) ? ';port='.$settings['port'] : '';
102			$dbname = !empty($settings['database']) ? ';dbname='.$settings['database'] : '';
103			$dsn = 'pgsql:host='.$settings['hostname'] .$port .$dbname ;
104
105			$this->pdo = new \PDO(
106			$dsn,
107			$settings['username'],
108			$settings['password'],
109			array()
110			);
111
112			// make sure emulate prepare is false
113			//$this->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
114			//$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
115			}
116
117			/**
118			* Get last inserted id
119			*
120			* @access public
121			* @return string
122			*/
123			public function lastInsertedId(): string
124			{
125			// Postgres does not set pdo->lastInsertedId
126			// use sequence
127			try {
128			$rq = $this->pdo->prepare('SELECT LASTVAL()');
129			$rq->execute();
130			// return string
131			return strval($rq->fetchColumn());
132			}
133			catch (\PDOException $e) {
134			return 0;
135			}
136			}
137
138			/**
139			* Enable foreign keys
140			*
141			* @access public
142			* @return void
143			*/
144			public function enableForeignKeys(): void
145			{
146			}
147
148			/**
149			* Disable foreign keys
150			*
151			* @access public
152			* @return void
153			*/
154			public function disableForeignKeys(): void
155			{
156			}
157
158			/**
159			* Get whether foreign keys are enabled or not
160			* For compatibility with Sqlite, not implemented in that driver (allways enabled), return true
161
162			* @access public
163			* @return bool true if foreign keys are enabled, otherwise false
164			*/
165			public function isForeignKeyEnabled(): bool
166			{
167			return true;
168			}
169
170			/**
171			* Add a foreign key
172			*
173			* @access public
174			* @param string $fkName The constraint name
175			* @param string $srcTable The source table
176			* @param string $srcColumn The source column
177			* @param string $refTable The referenced table
178			* @param string $refColumn The referenced column
179			*
180			* @return bool True if the foreign key has been created, otherwise false
181			*/
182			public function addForeignKey(string $fkName, string $srcTable, string $srcColumn, string $refTable, string $refColumn): bool
183			{
184			$sql = sprintf('ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s)',
185			$this->escape($srcTable),
186			$fkName,
187			$this->escape($srcColumn),
188			$this->escape($refTable),
189			$this->escape($refColumn)
190			);
191			return $this->prepareAndExecuteSql($sql);
192			}
193
194			/**
195			* Drop a foreign key
196			*
197			* @access public
198			* @param string $fkName The constraint name
199			* @param string $tableName The source table
200			*
201			* @return bool True if the foreign key has been dropped, otherwise false
202			*/
203			public function dropForeignKey(string $fkName, string $tableName, bool $ifExists = false): bool
204			{
205			$sql = sprintf('ALTER TABLE %s DROP CONSTRAINT %s %s',
206			$this->escape($tableName),
207			$ifExists ? 'IF EXISTS' : '',
208			$fkName
209			);
210			return $this->prepareAndExecuteSql($sql);
211			}
212
213			/**
214			* Checks if a database exists
215			*
216			* @access public
217			* @param string $databaseName The database name
218			*
219			* @return bool True if the given database exists, otherwise false.
220			*/
221			public function databaseExists(string $databaseName): bool
222			{
223			$sql = 'SELECT COUNT(*) FROM pg_database WHERE datname = :dbName';
224			$query = $this->pdo->prepare($sql);
225			$query->bindValue(':dbName', $databaseName, \PDO::PARAM_STR);
226			$query->execute();
227			return (bool) $query->fetchColumn();
228			}
229
230			/**
231			* Create a database
232			*
233			* @access public
234			* @param string $databaseName The database name
235			* @param string $owner The database owner.
236			* @param string $template The template to use. Default is 'template0'
237			*
238			* @return bool True if the database has been created, otherwise false.
239			*/
240			public function createDatabase(string $databaseName, ?string $owner= null, ?string $template = 'template0'): bool
241			{
242			$sql = trim(sprintf('CREATE DATABASE %s %s TEMPLATE %s',
243			$this->escape($databaseName),
244			isset($owner) ? 'OWNER '. $this->escape($owner) : '',
245			$template
246			));
247			return $this->prepareAndExecuteSql($sql);
248			}
249
250			/**
251			* Create a user
252			*
253			* @access public
254			* @param string $userName The user name
255			* @param string $userpassword The user password
256			*
257			* @return bool True if the user has been created, otherwise false.
258			*/
259			public function createUser(string $userName, string $userPassword): bool
260			{
261			$sql = trim(sprintf('CREATE USER %s PASSWORD %s',
262			$this->escape($userName),
263			"'" . $userPassword ."'"
264			));
265			return $this->prepareAndExecuteSql($sql);
266			}
267
268			/**
269			* Drop a user
270			*
271			* @access public
272			* @param string $userName The user name
273			* @param bool $ifExists (optional) True if the user must be deleted only when exists. Default is false.
274			*
275			* @return bool True if the user has been dropped or does not exist when $ifExists is set to True, otherwise false.
276			*/
277			public function dropUser(string $userName, bool $ifExists = false): bool
278			{
279			$sql = trim(sprintf('DROP USER %s %s',
280			$ifExists === true ? 'IF EXISTS': '',
281			$this->escape($userName)
282			));
283			return $this->prepareAndExecuteSql($sql);
284			}
285
286			/**
287			* Grant user permissions on given database
288			*
289			* @access public
290			* @param string $databaseName The database name
291			* @param string $userName The user name
292			*
293			* @return bool True if the user has been granted, otherwise false.
294			*/
295			public function grantUser(string $databaseName, string $userName): bool
296			{
297			// ALL PRIVILEGES Grant all of the available privileges at once. The PRIVILEGES keyword
298			// is optional in PostgreSQL, though it is required by strict SQL.
299
300			// GRANT CONNECT ON DATABASE database_name TO user_name;
301			//$sql = trim(sprintf('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %s TO %s;',
302			$sql = trim(sprintf('GRANT CONNECT ON DATABASE %s TO %s;',
303			$this->escape($databaseName),
304			$this->escape($userName)
305			));
306			return $this->prepareAndExecuteSql($sql);
307			}
308
309			/**
310			* Get the SQL for show databases
311			*
312			* @access public
313			* @return string
314			*/
315			public function sqlShowDatabases(): string
316			{
317			return 'SELECT datname FROM pg_database WHERE datistemplate = false;';
318			}
319
320			/**
321			* Get the SQL for show tables
322			*
323			* @access public
324			* @return string
325			*/
326			public function sqlShowTables(): string
327			{
328			return "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type = 'BASE TABLE' ORDER BY table_name;";
329			}
330
331			/**
332			* Get the SQL for show users
333			*
334			* @access public
335			* @return string
336			*/
337			public function sqlShowUsers(): string
338			{
339			return 'SELECT usename FROM pg_user';
340			}
341
342			/**
343			* Get the SQL for random function
344			*
345			* Parameter $seed is not honored in Postgres.
346			*
347			* @access public
348			* @param int $seed The random seed. Default is null.
349			*
350			* @return string
351			*/
352			public function sqlRandom($seed = null): string
353			{
354			return 'random()';
355			}
356
357			/**
358			* Get the SQL for auto increment column
359			*
360			* @access public
361			* @param string $type The sql column type
362			*
363			* @return string
364			*/
365			public function sqlColumnAutoIncrement(string $type): string
366			{
367			// SERIAL/BIGSERIAL is a type in postgres
368			return strtolower($type) === 'bigint' ? 'bigserial' : 'serial';
369			}
370
371
372
373			}

kristuff / patabase

PostgresDriver::addForeignKey() A last analyzed 2022-04-08 21:26 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

PostgresDriver::addForeignKey() A
last analyzed 2022-04-08 21:26 UTC