3
namespace YoastSEO_Vendor;
9
* @package Ruckusing_Adapter
11
* @author Cody Caughlan <codycaughlan % gmail . com>
12
* @link https://github.com/ruckus/ruckusing-migrations
14
// max length of an identifier like a column or index name
15
\define('YoastSEO_Vendor\\PG_MAX_IDENTIFIER_LENGTH', 64);
17
* Implementation of Ruckusing_Adapter_PgSQL_Base
20
* @package Ruckusing_Adapter
22
* @author Cody Caughlan <codycaughlan % gmail . com>
23
* @link https://github.com/ruckus/ruckusing-migrations
25
class Ruckusing_Adapter_PgSQL_Base extends \YoastSEO_Vendor\Ruckusing_Adapter_Base implements \YoastSEO_Vendor\Ruckusing_Adapter_Interface
32
private $_name = "Postgres";
38
private $_tables = array();
44
private $_tables_loaded = \false;
50
private $_version = '1.0';
52
* Indicate if is in transaction
56
private $_in_trx = \false;
58
* Creates an instance of Ruckusing_Adapter_PgSQL_Base
60
* @param array $dsn The current dsn being used
61
* @param Ruckusing_Util_Logger $logger the current logger
63
* @return Ruckusing_Adapter_PgSQL_Base
65
public function __construct($dsn, $logger)
67
parent::__construct($dsn);
69
$this->set_logger($logger);
72
* Get the current db name
76
public function get_database_name()
78
return $this->db_info['database'];
81
* Check support for migrations
85
public function supports_migrations()
90
* Get the column native types
94
public function native_database_types()
96
$types = array('primary_key' => array('name' => 'serial'), 'string' => array('name' => 'varchar', 'limit' => 255), 'text' => array('name' => 'text'), 'tinytext' => array('name' => 'text'), 'mediumtext' => array('name' => 'text'), 'integer' => array('name' => 'integer'), 'tinyinteger' => array('name' => 'smallint'), 'smallinteger' => array('name' => 'smallint'), 'mediuminteger' => array('name' => 'integer'), 'biginteger' => array('name' => 'bigint'), 'float' => array('name' => 'float'), 'decimal' => array('name' => 'decimal', 'scale' => 0, 'precision' => 10), 'datetime' => array('name' => 'timestamp'), 'timestamp' => array('name' => 'timestamp'), 'time' => array('name' => 'time'), 'date' => array('name' => 'date'), 'binary' => array('name' => 'bytea'), 'tinybinary' => array('name' => "bytea"), 'mediumbinary' => array('name' => "bytea"), 'longbinary' => array('name' => "bytea"), 'boolean' => array('name' => 'boolean'), 'tsvector' => array('name' => 'tsvector'), 'uuid' => array('name' => 'uuid'));
99
//-----------------------------------
101
//-----------------------------------
103
* Create the schema table, if necessary
105
public function create_schema_version_table()
107
if (!$this->has_table($this->get_schema_version_table_name())) {
108
$t = $this->create_table($this->get_schema_version_table_name(), array('id' => \false));
109
$t->column('version', 'string');
111
$this->add_index($this->get_schema_version_table_name(), 'version', array('unique' => \true));
117
public function start_transaction()
119
if ($this->inTransaction() === \false) {
120
$this->beginTransaction();
126
public function commit_transaction()
128
if ($this->inTransaction()) {
133
* Rollback Transaction
135
public function rollback_transaction()
137
if ($this->inTransaction()) {
144
* @param string $column_name the column name
145
* @param string $type the type of the column
146
* @param array $options column options
150
public function column_definition($column_name, $type, $options = null)
152
$col = new \YoastSEO_Vendor\Ruckusing_Adapter_ColumnDefinition($this, $column_name, $type, $options);
153
return $col->__toString();
156
* Returns a table's primary key and belonging sequence.
158
* @param string $table the table name
162
public function pk_and_sequence_for($table)
165
SELECT attr.attname, seq.relname
171
WHERE seq.oid = dep.objid
172
AND seq.relkind = 'S'
173
AND attr.attrelid = dep.refobjid
174
AND attr.attnum = dep.refobjsubid
175
AND attr.attrelid = cons.conrelid
176
AND attr.attnum = cons.conkey[1]
177
AND cons.contype = 'p'
178
AND dep.refobjid = '%s'::regclass
180
$sql = \sprintf($sql, $table);
181
$result = $this->select_one($sql);
183
return array($result['attname'], $result['relname']);
188
//-------- DATABASE LEVEL OPERATIONS
190
* Create database cannot run in a transaction block so if we're in a transaction
191
* than commit it, do our thing and then re-invoke the transaction
193
* @param string $db the db name
195
* @param array $options
198
public function create_database($db, $options = array())
200
$was_in_transaction = \false;
201
if ($this->inTransaction()) {
202
$this->commit_transaction();
203
$was_in_transaction = \true;
205
if (!\array_key_exists('encoding', $options)) {
206
$options['encoding'] = 'utf8';
208
$ddl = \sprintf("CREATE DATABASE %s", $this->identifier($db));
209
if (\array_key_exists('owner', $options)) {
210
$ddl .= " OWNER = \"{$options['owner']}\"";
212
if (\array_key_exists('template', $options)) {
213
$ddl .= " TEMPLATE = \"{$options['template']}\"";
215
if (\array_key_exists('encoding', $options)) {
216
$ddl .= " ENCODING = '{$options['encoding']}'";
218
if (\array_key_exists('tablespace', $options)) {
219
$ddl .= " TABLESPACE = \"{$options['tablespace']}\"";
221
if (\array_key_exists('connection_limit', $options)) {
222
$connlimit = \intval($options['connection_limit']);
223
$ddl .= " CONNECTION LIMIT = {$connlimit}";
225
$result = $this->query($ddl);
226
if ($was_in_transaction) {
227
$this->start_transaction();
228
$was_in_transaction = \false;
230
return $result === \true;
233
* Check if a db exists
235
* @param string $db the db name
239
public function database_exists($db)
241
$sql = \sprintf("SELECT datname FROM pg_database WHERE datname = '%s'", $db);
242
$result = $this->select_one($sql);
243
return \count($result) == 1 && $result['datname'] == $db;
248
* @param string $db the db name
252
public function drop_database($db)
254
if (!$this->database_exists($db)) {
257
$ddl = \sprintf("DROP DATABASE IF EXISTS %s", $this->quote_table_name($db));
258
$result = $this->query($ddl);
259
return $result === \true;
262
* Dump the complete schema of the DB. This is really just all of the
263
* CREATE TABLE statements for all of the tables in the DB.
264
* NOTE: this does NOT include any INSERT statements or the actual data
266
* @param string $output_file the filepath to output to
270
public function schema($output_file)
272
$command = \sprintf("pg_dump -U %s -Fp -s -f '%s' %s --host %s", $this->db_info['user'], $output_file, $this->db_info['database'], $this->db_info['host']);
273
return \system($command);
276
* Check if a table exists
278
* @param string $tbl the table name
279
* @param boolean $reload_tables reload table or not
283
public function table_exists($tbl, $reload_tables = \false)
285
$this->load_tables($reload_tables);
286
return \array_key_exists($tbl, $this->_tables);
288
public function execute($query)
290
return $this->query($query);
293
* Wrapper to execute a query
295
* @param string $query query to run
297
* @throws Ruckusing_Exception
300
public function query($query)
302
$this->logger->log($query);
303
$query_type = $this->determine_query_type($query);
305
if ($query_type == \YoastSEO_Vendor\SQL_SELECT || $query_type == \YoastSEO_Vendor\SQL_SHOW) {
306
$res = \pg_query($this->conn, $query);
307
if ($this->isError($res)) {
308
throw new \YoastSEO_Vendor\Ruckusing_Exception(\sprintf("Error executing 'query' with:\n%s\n\nReason: %s\n\n", $query, \pg_last_error($this->conn)), \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
310
while ($row = \pg_fetch_assoc($res)) {
315
// INSERT, DELETE, etc...
316
$res = \pg_query($this->conn, $query);
317
if ($this->isError($res)) {
318
throw new \YoastSEO_Vendor\Ruckusing_Exception(\sprintf("Error executing 'query' with:\n%s\n\nReason: %s\n\n", $query, \pg_last_error($this->conn)), \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
320
// if the query contained a 'RETURNING' class then grab its value
321
$returning_regex = '/ RETURNING \\"(.+)\\"$/';
323
if (\preg_match($returning_regex, $query, $matches)) {
324
if (\count($matches) == 2) {
325
$returning_column_value = \pg_fetch_result($res, 0, $matches[1]);
326
return $returning_column_value;
333
* Execute several queries
335
* @param string $queries queries to run
337
* @throws Ruckusing_Exception
340
public function multi_query($queries)
342
$res = \pg_query($this->conn, $queries);
343
if ($this->isError($res)) {
344
throw new \YoastSEO_Vendor\Ruckusing_Exception(\sprintf("Error executing 'query' with:\n%s\n\nReason: %s\n\n", $queries, \pg_last_error($this->conn)), \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
351
* @param string $query query to run
353
* @throws Ruckusing_Exception
356
public function select_one($query)
358
$this->logger->log($query);
359
$query_type = $this->determine_query_type($query);
360
if ($query_type == \YoastSEO_Vendor\SQL_SELECT || $query_type == \YoastSEO_Vendor\SQL_SHOW) {
361
$res = \pg_query($this->conn, $query);
362
if ($this->isError($res)) {
363
throw new \YoastSEO_Vendor\Ruckusing_Exception(\sprintf("Error executing 'query' with:\n%s\n\nReason: %s\n\n", $query, \pg_last_error($this->conn)), \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
365
return \pg_fetch_assoc($res);
367
throw new \YoastSEO_Vendor\Ruckusing_Exception("Query for select_one() is not one of SELECT or SHOW: {$query}", \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
373
* @param string $query query to run
377
public function select_all($query)
379
return $this->query($query);
382
* Use this method for non-SELECT queries
383
* Or anything where you dont necessarily expect a result string, e.g. DROPs, CREATEs, etc.
385
* @param string $ddl query to run
389
public function execute_ddl($ddl)
391
$result = $this->query($ddl);
397
* @param string $tbl the table name
401
public function drop_table($tbl)
403
$ddl = \sprintf("DROP TABLE IF EXISTS %s", $this->quote_table_name($tbl));
404
$result = $this->query($ddl);
409
* @param string $table_name the table name
410
* @param array $options the options
411
* @return bool|Ruckusing_Adapter_PgSQL_TableDefinition
413
public function create_table($table_name, $options = array())
415
return new \YoastSEO_Vendor\Ruckusing_Adapter_PgSQL_TableDefinition($this, $table_name, $options);
418
* Escape a string for mysql
420
* @param string $string the string
424
public function quote_string($string)
426
return \pg_escape_string($string);
431
* @param string $string the string
435
public function identifier($string)
437
return '"' . $string . '"';
442
* @param string $string the string
446
public function quote_table_name($string)
448
return '"' . $string . '"';
453
* @param string $string the string
457
public function quote_column_name($string)
459
return '"' . $string . '"';
464
* @param string $value the string
465
* @param string $column the column
469
public function quote($value, $column = null)
471
$type = \gettype($value);
472
if ($type == "double") {
474
} elseif ($type == "integer") {
477
// TODO: this global else is probably going to be problematic.
478
// I think eventually we'll need to do more introspection and handle all possible types
484
"double" (for historical reasons "double" is returned in case of a float, and not simply "float")
497
* Also renames a table's primary key sequence if the sequence name matches the Ruckusing Migrations default.
499
* @param string $name the current table name
500
* @param string $new_name the new table name
502
* @throws Ruckusing_Exception
505
public function rename_table($name, $new_name)
508
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing original column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
510
if (empty($new_name)) {
511
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing new column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
513
$sql = \sprintf("ALTER TABLE %s RENAME TO %s", $this->identifier($name), $this->identifier($new_name));
514
$this->execute_ddl($sql);
515
$pk_and_sequence_for = $this->pk_and_sequence_for($new_name);
516
if (!empty($pk_and_sequence_for)) {
517
list($pk, $seq) = $pk_and_sequence_for;
518
if ($seq == "{$name}_{$pk}_seq") {
519
$new_seq = "{$new_name}_{$pk}_seq";
520
$this->execute_ddl("ALTER TABLE {$seq} RENAME TO {$new_seq}");
527
* @param string $table_name the table name
528
* @param string $column_name the column name
529
* @param string $type the column type
530
* @param array $options column options
532
* @throws Ruckusing_Exception
535
public function add_column($table_name, $column_name, $type, $options = array())
537
if (empty($table_name)) {
538
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
540
if (empty($column_name)) {
541
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
544
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing type parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
547
if (!\array_key_exists('limit', $options)) {
548
$options['limit'] = null;
550
if (!\array_key_exists('precision', $options)) {
551
$options['precision'] = null;
553
if (!\array_key_exists('scale', $options)) {
554
$options['scale'] = null;
556
$sql = \sprintf("ALTER TABLE %s ADD COLUMN %s %s", $this->quote_table_name($table_name), $this->quote_column_name($column_name), $this->type_to_sql($type, $options));
557
$sql .= $this->add_column_options($type, $options);
558
return $this->execute_ddl($sql);
563
* @param string $table_name the table name
564
* @param string $column_name the column name
568
public function remove_column($table_name, $column_name)
570
$sql = \sprintf("ALTER TABLE %s DROP COLUMN %s", $this->quote_table_name($table_name), $this->quote_column_name($column_name));
571
return $this->execute_ddl($sql);
576
* @param string $table_name the table name
577
* @param string $column_name the column name
578
* @param string $new_column_name the new column name
580
* @throws Ruckusing_Exception
583
public function rename_column($table_name, $column_name, $new_column_name)
585
if (empty($table_name)) {
586
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
588
if (empty($column_name)) {
589
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing original column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
591
if (empty($new_column_name)) {
592
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing new column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
594
$column_info = $this->column_info($table_name, $column_name);
595
$current_type = $column_info['type'];
596
$sql = \sprintf("ALTER TABLE %s RENAME COLUMN %s TO %s", $this->quote_table_name($table_name), $this->quote_column_name($column_name), $this->quote_column_name($new_column_name));
597
return $this->execute_ddl($sql);
602
* @param string $table_name the table name
603
* @param string $column_name the column name
604
* @param string $type the column type
605
* @param array $options column options
607
* @throws Ruckusing_Exception
610
public function change_column($table_name, $column_name, $type, $options = array())
612
if (empty($table_name)) {
613
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
615
if (empty($column_name)) {
616
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing original column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
619
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing type parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
621
$column_info = $this->column_info($table_name, $column_name);
623
if (!\array_key_exists('limit', $options)) {
624
$options['limit'] = null;
626
if (!\array_key_exists('precision', $options)) {
627
$options['precision'] = null;
629
if (!\array_key_exists('scale', $options)) {
630
$options['scale'] = null;
632
$sql = \sprintf("ALTER TABLE %s ALTER COLUMN %s TYPE %s", $this->quote_table_name($table_name), $this->quote_column_name($column_name), $this->type_to_sql($type, $options));
633
$sql .= $this->add_column_options($type, $options, \true);
634
if (\array_key_exists('default', $options)) {
635
$this->change_column_default($table_name, $column_name, $options['default']);
637
if (\array_key_exists('null', $options)) {
638
$default = \array_key_exists('default', $options) ? $options['default'] : null;
639
$this->change_column_null($table_name, $column_name, $options['null'], $default);
641
return $this->execute_ddl($sql);
644
* Change column default
646
* @param string $table_name the table name
647
* @param string $column_name the column name
648
* @param string $default
652
private function change_column_default($table_name, $column_name, $default)
654
$sql = \sprintf("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s", $this->quote_table_name($table_name), $this->quote_column_name($column_name), $this->quote($default));
655
$this->execute_ddl($sql);
660
* @param string $table_name the table name
661
* @param string $column_name the column name
662
* @param string $null
663
* @param string $default
667
private function change_column_null($table_name, $column_name, $null, $default = null)
669
if ($null === \false || $default !== null) {
670
$sql = \sprintf("UPDATE %s SET %s=%s WHERE %s IS NULL", $this->quote_table_name($table_name), $this->quote_column_name($column_name), $this->quote($default), $this->quote_column_name($column_name));
673
$sql = \sprintf("ALTER TABLE %s ALTER %s %s NOT NULL", $this->quote_table_name($table_name), $this->quote_column_name($column_name), $null ? 'DROP' : 'SET');
679
* @param string $table the table name
680
* @param string $column the column name
682
* @throws Ruckusing_Exception
685
public function column_info($table, $column)
688
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
690
if (empty($column)) {
691
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing original column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
695
SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
696
FROM pg_attribute a LEFT JOIN pg_attrdef d
697
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
698
WHERE a.attrelid = '%s'::regclass
700
AND a.attnum > 0 AND NOT a.attisdropped
703
$sql = \sprintf($sql, $this->quote_table_name($table), $column);
704
$result = $this->select_one($sql);
706
if (\is_array($result)) {
707
$data['type'] = $result['format_type'];
708
$data['name'] = $column;
709
$data['field'] = $column;
710
$data['null'] = $result['attnotnull'] == 'f';
711
$data['default'] = $result['adsrc'];
716
} catch (\Exception $e) {
723
* @param string $table_name the table name
724
* @param string $column_name the column name
725
* @param array $options index options
727
* @throws Ruckusing_Exception
730
public function add_index($table_name, $column_name, $options = array())
732
if (empty($table_name)) {
733
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
735
if (empty($column_name)) {
736
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
739
if (\is_array($options) && \array_key_exists('unique', $options) && $options['unique'] === \true) {
744
//did the user specify an index name?
745
if (\is_array($options) && \array_key_exists('name', $options)) {
746
$index_name = $options['name'];
748
$index_name = \YoastSEO_Vendor\Ruckusing_Util_Naming::index_name($table_name, $column_name);
750
if (\strlen($index_name) > \YoastSEO_Vendor\PG_MAX_IDENTIFIER_LENGTH) {
751
$msg = "The auto-generated index name is too long for Postgres (max is 64 chars). ";
752
$msg .= "Considering using 'name' option parameter to specify a custom name for this index.";
753
$msg .= " Note: you will also need to specify";
754
$msg .= " this custom name in a drop_index() - if you have one.";
755
throw new \YoastSEO_Vendor\Ruckusing_Exception($msg, \YoastSEO_Vendor\Ruckusing_Exception::INVALID_INDEX_NAME);
757
if (!\is_array($column_name)) {
758
$column_names = array($column_name);
760
$column_names = $column_name;
763
foreach ($column_names as $name) {
764
$cols[] = $this->quote_column_name($name);
766
$sql = \sprintf("CREATE %sINDEX %s ON %s(%s)", $unique ? "UNIQUE " : "", $this->quote_column_name($index_name), $this->quote_column_name($table_name), \join(", ", $cols));
767
return $this->execute_ddl($sql);
772
* @param string $table_name the table name
773
* @param string $column_name the column name
774
* @param array $options index options
776
* @throws Ruckusing_Exception
779
public function remove_index($table_name, $column_name, $options = array())
781
if (empty($table_name)) {
782
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
784
if (empty($column_name)) {
785
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
787
//did the user specify an index name?
788
if (\is_array($options) && \array_key_exists('name', $options)) {
789
$index_name = $options['name'];
791
$index_name = \YoastSEO_Vendor\Ruckusing_Util_Naming::index_name($table_name, $column_name);
793
$sql = \sprintf("DROP INDEX %s", $this->quote_column_name($index_name));
794
return $this->execute_ddl($sql);
799
* @param string $table_name The table name
800
* @param string $created_column_name Created at column name
801
* @param string $updated_column_name Updated at column name
805
public function add_timestamps($table_name, $created_column_name, $updated_column_name)
807
if (empty($table_name)) {
808
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
810
if (empty($created_column_name)) {
811
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing created at column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
813
if (empty($updated_column_name)) {
814
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing updated at column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
816
$created_at = $this->add_column($table_name, $created_column_name, "datetime", array("null" => \false));
817
$updated_at = $this->add_column($table_name, $updated_column_name, "datetime", array("null" => \false));
818
return $created_at && $updated_at;
823
* @param string $table_name The table name
824
* @param string $created_column_name Created at column name
825
* @param string $updated_column_name Updated at column name
829
public function remove_timestamps($table_name, $created_column_name, $updated_column_name)
831
if (empty($table_name)) {
832
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
834
if (empty($created_column_name)) {
835
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing created at column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
837
if (empty($updated_column_name)) {
838
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing updated at column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
840
$created_at = $this->remove_column($table_name, $created_column_name);
841
$updated_at = $this->remove_column($table_name, $updated_column_name);
842
return $created_at && $updated_at;
847
* @param string $table_name the table name
848
* @param string $column_name the column name
849
* @param array $options index options
851
* @throws Ruckusing_Exception
854
public function has_index($table_name, $column_name, $options = array())
856
if (empty($table_name)) {
857
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing table name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
859
if (empty($column_name)) {
860
throw new \YoastSEO_Vendor\Ruckusing_Exception("Missing column name parameter", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
862
//did the user specify an index name?
863
if (\is_array($options) && \array_key_exists('name', $options)) {
864
$index_name = $options['name'];
866
$index_name = \YoastSEO_Vendor\Ruckusing_Util_Naming::index_name($table_name, $column_name);
868
$indexes = $this->indexes($table_name);
869
foreach ($indexes as $idx) {
870
if ($idx['name'] == $index_name) {
877
* Return all indexes of a table
879
* @param string $table_name the table name
883
public function indexes($table_name)
886
SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
888
INNER JOIN pg_index d ON t.oid = d.indrelid
889
INNER JOIN pg_class i ON d.indexrelid = i.oid
890
WHERE i.relkind = 'i'
891
AND d.indisprimary = 'f'
893
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
896
$sql = \sprintf($sql, $table_name);
897
$result = $this->select_all($sql);
899
foreach ($result as $row) {
900
$indexes[] = array('name' => $row['relname'], 'unique' => $row['indisunique'] == 't' ? \true : \false);
907
* @param string $table_name the table name
911
public function primary_keys($table_name)
915
pg_attribute.attname,
916
format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
917
FROM pg_index, pg_class, pg_attribute
919
pg_class.oid = '%s'::regclass AND
920
indrelid = pg_class.oid AND
921
pg_attribute.attrelid = pg_class.oid AND
922
pg_attribute.attnum = any(pg_index.indkey)
925
$sql = \sprintf($sql, $table_name);
926
$result = $this->select_all($sql);
927
$primary_keys = array();
928
foreach ($result as $row) {
929
$primary_keys[] = array('name' => $row['attname'], 'type' => $row['format_type']);
931
return $primary_keys;
934
* Convert type to sql
936
* @param string $type the native type
937
* @param array $options
939
* @throws Ruckusing_Exception
942
public function type_to_sql($type, $options = array())
944
$natives = $this->native_database_types();
945
if (!\array_key_exists($type, $natives)) {
946
$error = \sprintf("Error: I dont know what column type of '%s' maps to for Postgres.", $type);
947
$error .= "\nYou provided: {$type}\n";
948
$error .= "Valid types are: \n";
949
$types = \array_keys($natives);
950
foreach ($types as $t) {
951
if ($t == 'primary_key') {
954
$error .= "\t{$t}\n";
956
throw new \YoastSEO_Vendor\Ruckusing_Exception($error, \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
961
if (isset($options['precision'])) {
962
$precision = $options['precision'];
964
if (isset($options['scale'])) {
965
$scale = $options['scale'];
967
if (isset($options['limit'])) {
968
$limit = $options['limit'];
970
$native_type = $natives[$type];
971
if (\is_array($native_type) && \array_key_exists('name', $native_type)) {
972
$column_type_sql = $native_type['name'];
976
if ($type == "decimal") {
977
//ignore limit, use precison and scale
978
if ($precision == null && \array_key_exists('precision', $native_type)) {
979
$precision = $native_type['precision'];
981
if ($scale == null && \array_key_exists('scale', $native_type)) {
982
$scale = $native_type['scale'];
984
if ($precision != null) {
985
if (\is_int($scale)) {
986
$column_type_sql .= \sprintf("(%d, %d)", $precision, $scale);
988
$column_type_sql .= \sprintf("(%d)", $precision);
993
throw new \YoastSEO_Vendor\Ruckusing_Exception("Error adding decimal column: precision cannot be empty if scale is specified", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_ARGUMENT);
998
// integer columns dont support limit (sizing)
999
if ($native_type['name'] != "integer") {
1000
if ($limit == null && \array_key_exists('limit', $native_type)) {
1001
$limit = $native_type['limit'];
1004
$column_type_sql .= \sprintf("(%d)", $limit);
1007
return $column_type_sql;
1011
* Add column options
1013
* @param string $type the native type
1014
* @param array $options
1015
* @param boolean $performing_change
1019
public function add_column_options($type, $options, $performing_change = \false)
1022
if (!\is_array($options)) {
1025
if (!$performing_change) {
1026
if (\array_key_exists('default', $options) && $options['default'] !== null) {
1027
if (\is_int($options['default'])) {
1028
$default_format = '%d';
1029
} elseif (\is_bool($options['default'])) {
1030
$default_format = "'%d'";
1032
$default_format = "'%s'";
1034
$default_value = \sprintf($default_format, $options['default']);
1035
$sql .= \sprintf(" DEFAULT %s", $default_value);
1037
if (\array_key_exists('null', $options) && $options['null'] === \false) {
1038
$sql .= " NOT NULL";
1043
//add_column_options
1045
* Set current version
1047
* @param string $version the version
1051
public function set_current_version($version)
1053
$sql = \sprintf("INSERT INTO %s (version) VALUES ('%s')", $this->get_schema_version_table_name(), $version);
1054
return $this->execute_ddl($sql);
1059
* @param string $version the version
1063
public function remove_version($version)
1065
$sql = \sprintf("DELETE FROM %s WHERE version = '%s'", $this->get_schema_version_table_name(), $version);
1066
return $this->execute_ddl($sql);
1069
* Return a message displaying the current version
1073
public function __toString()
1075
return "Ruckusing_Adapter_PgSQL_Base, version " . $this->_version;
1077
//-----------------------------------
1079
//-----------------------------------
1083
* @param string $dsn the current dsn
1085
private function connect($dsn)
1087
$this->db_connect($dsn);
1092
* @param string $dsn the current dsn
1094
* @throws Ruckusing_Exception
1097
private function db_connect($dsn)
1099
if (!\function_exists('pg_connect')) {
1100
throw new \YoastSEO_Vendor\Ruckusing_Exception("\nIt appears you have not compiled PHP with Postgres support: missing function pg_connect()", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_CONFIG);
1102
$db_info = $this->get_dsn();
1104
$this->db_info = $db_info;
1105
$conninfo = \sprintf('host=%s port=%s dbname=%s user=%s password=%s', $db_info['host'], !empty($db_info['port']) ? $db_info['port'] : '5432', $db_info['database'], $db_info['user'], $db_info['password']);
1106
$this->conn = \pg_connect($conninfo);
1107
if ($this->conn === \FALSE) {
1108
throw new \YoastSEO_Vendor\Ruckusing_Exception("\n\nCould not connect to the DB, check host / user / password\n\n", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_CONFIG);
1112
throw new \YoastSEO_Vendor\Ruckusing_Exception("\n\nCould not extract DB connection information from: {$dsn}\n\n", \YoastSEO_Vendor\Ruckusing_Exception::INVALID_CONFIG);
1122
private function isError($o)
1124
return $o === \FALSE;
1127
* Initialize an array of table names
1129
* @param boolean $reload
1131
private function load_tables($reload = \true)
1133
if ($this->_tables_loaded == \false || $reload) {
1134
$this->_tables = array();
1135
//clear existing structure
1136
$sql = "SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false))";
1137
$res = \pg_query($this->conn, $sql);
1138
while ($row = \pg_fetch_row($res)) {
1140
$this->_tables[$table] = \true;
1147
* @param string $query query to run
1151
private function determine_query_type($query)
1153
$query = \strtolower(\trim($query));
1155
\preg_match('/^(\\w)*/i', $query, $match);
1159
return \YoastSEO_Vendor\SQL_SELECT;
1161
return \YoastSEO_Vendor\SQL_UPDATE;
1163
return \YoastSEO_Vendor\SQL_DELETE;
1165
return \YoastSEO_Vendor\SQL_INSERT;
1167
return \YoastSEO_Vendor\SQL_ALTER;
1169
return \YoastSEO_Vendor\SQL_DROP;
1171
return \YoastSEO_Vendor\SQL_CREATE;
1173
return \YoastSEO_Vendor\SQL_SHOW;
1175
return \YoastSEO_Vendor\SQL_RENAME;
1177
return \YoastSEO_Vendor\SQL_SET;
1179
return \YoastSEO_Vendor\SQL_UNKNOWN_QUERY_TYPE;
1182
private function is_select($query_type)
1184
return $query_type == \YoastSEO_Vendor\SQL_SELECT;
1187
* Detect whether or not the string represents a function call and if so
1188
* do not wrap it in single-quotes, otherwise do wrap in single quotes.
1190
* @param string $str
1194
private function is_sql_method_call($str)
1197
return \substr($str, -2, 2) == "()";
1200
* Check if in transaction
1204
private function inTransaction()
1206
return $this->_in_trx;
1211
private function beginTransaction()
1213
if ($this->_in_trx === \true) {
1214
throw new \YoastSEO_Vendor\Ruckusing_Exception('Transaction already started', \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
1216
\pg_query($this->conn, "BEGIN");
1217
$this->_in_trx = \true;
1220
* Commit a transaction
1222
private function commit()
1224
if ($this->_in_trx === \false) {
1225
throw new \YoastSEO_Vendor\Ruckusing_Exception('Transaction not started', \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
1227
\pg_query($this->conn, "COMMIT");
1228
$this->_in_trx = \false;
1231
* Rollback a transaction
1233
private function rollback()
1235
if ($this->_in_trx === \false) {
1236
throw new \YoastSEO_Vendor\Ruckusing_Exception('Transaction not started', \YoastSEO_Vendor\Ruckusing_Exception::QUERY_ERROR);
1238
\pg_query($this->conn, "ROLLBACK");
1239
$this->_in_trx = \false;