1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
<?php
namespace Codeception\Lib\Driver;
use Codeception\Exception\ModuleException;
class PostgreSql extends Db
{
protected $putline = false;
protected $connection = null;
protected $searchPath = null;
/**
* Loads a SQL file.
*
* @param string $sql sql file
*/
public function load($sql)
{
$query = '';
$delimiter = ';';
$delimiterLength = 1;
$dollarsOpen = false;
foreach ($sql as $sqlLine) {
if (preg_match('/DELIMITER ([\;\$\|\\\\]+)/i', $sqlLine, $match)) {
$delimiter = $match[1];
$delimiterLength = strlen($delimiter);
continue;
}
$parsed = trim($query) == '' && $this->sqlLine($sqlLine);
if ($parsed) {
continue;
}
// Ignore $$ inside SQL standard string syntax such as in INSERT statements.
if (!preg_match('/\'.*\$\$.*\'/', $sqlLine)) {
$pos = strpos($sqlLine, '$$');
if (($pos !== false) && ($pos >= 0)) {
$dollarsOpen = !$dollarsOpen;
}
}
if (preg_match('/SET search_path = .*/i', $sqlLine, $match)) {
$this->searchPath = $match[0];
}
$query .= "\n" . rtrim($sqlLine);
if (!$dollarsOpen && substr($query, -1 * $delimiterLength, $delimiterLength) == $delimiter) {
$this->sqlQuery(substr($query, 0, -1 * $delimiterLength));
$query = '';
}
}
if ($query !== '') {
$this->sqlQuery($query);
}
}
public function cleanup()
{
$this->dbh->exec('DROP SCHEMA IF EXISTS public CASCADE;');
$this->dbh->exec('CREATE SCHEMA public;');
}
public function sqlLine($sql)
{
if (!$this->putline) {
return parent::sqlLine($sql);
}
if ($sql == '\.') {
$this->putline = false;
pg_put_line($this->connection, $sql . "\n");
pg_end_copy($this->connection);
pg_close($this->connection);
} else {
pg_put_line($this->connection, $sql . "\n");
}
return true;
}
public function sqlQuery($query)
{
if (strpos(trim($query), 'COPY ') === 0) {
if (!extension_loaded('pgsql')) {
throw new ModuleException(
'\Codeception\Module\Db',
"To run 'COPY' commands 'pgsql' extension should be installed"
);
}
if (defined('HHVM_VERSION')) {
throw new ModuleException(
'\Codeception\Module\Db',
"'COPY' command is not supported on HHVM, please use INSERT instead"
);
}
$constring = str_replace(';', ' ', substr($this->dsn, 6));
$constring .= ' user=' . $this->user;
$constring .= ' password=' . $this->password;
$this->connection = pg_connect($constring);
if ($this->searchPath !== null) {
pg_query($this->connection, $this->searchPath);
}
pg_query($this->connection, $query);
$this->putline = true;
} else {
$this->dbh->exec($query);
}
}
/**
* Get the last inserted ID of table.
*/
public function lastInsertId($table)
{
/*
* We make an assumption that the sequence name for this table
* is based on how postgres names sequences for SERIAL columns
*/
$sequenceName = $this->getQuotedName($table . '_id_seq');
$lastSequence = null;
try {
$lastSequence = $this->getDbh()->lastInsertId($sequenceName);
} catch (\PDOException $e) {
// in this case, the sequence name might be combined with the primary key name
}
// here we check if for instance, it's something like table_primary_key_seq instead of table_id_seq
// this could occur when you use some kind of import tool like pgloader
if (!$lastSequence) {
$primaryKeys = $this->getPrimaryKey($table);
$pkName = array_shift($primaryKeys);
$lastSequence = $this->getDbh()->lastInsertId($this->getQuotedName($table . '_' . $pkName . '_seq'));
}
return $lastSequence;
}
/**
* Returns the primary key(s) of the table, based on:
* https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns.
*
* @param string $tableName
*
* @return array[string]
*/
public function getPrimaryKey($tableName)
{
if (!isset($this->primaryKeys[$tableName])) {
$primaryKey = [];
$query = "SELECT a.attname
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = '$tableName'::regclass
AND i.indisprimary";
$stmt = $this->executeQuery($query, []);
$columns = $stmt->fetchAll(\PDO::FETCH_ASSOC);
foreach ($columns as $column) {
$primaryKey []= $column['attname'];
}
$this->primaryKeys[$tableName] = $primaryKey;
}
return $this->primaryKeys[$tableName];
}
}