1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Class Firebird |
4
|
|
|
* |
5
|
|
|
* @filesource Firebird.php |
6
|
|
|
* @created 11.01.2018 |
7
|
|
|
* @package chillerlan\Database\Dialects |
8
|
|
|
* @author Smiley <[email protected]> |
9
|
|
|
* @copyright 2018 Smiley |
10
|
|
|
* @license MIT |
11
|
|
|
*/ |
12
|
|
|
|
13
|
|
|
namespace chillerlan\Database\Dialects; |
14
|
|
|
|
15
|
|
|
class Firebird extends DialectAbstract{ |
16
|
|
|
|
17
|
|
|
/** @inheritdoc */ |
18
|
|
|
public function select(array $cols, array $from, string $where = null, $limit = null, $offset = null, bool $distinct = null, array $groupby = null, array $orderby = null):array{ |
19
|
|
|
$sql = ['SELECT']; |
20
|
|
|
|
21
|
|
|
if($limit !== null){ |
22
|
|
|
$sql[] = 'FIRST ? SKIP ?'; |
23
|
|
|
} |
24
|
|
|
|
25
|
|
|
if($distinct){ |
26
|
|
|
$sql[] = 'DISTINCT'; |
27
|
|
|
} |
28
|
|
|
|
29
|
|
|
!empty($cols) |
30
|
|
|
? $sql[] = implode(', ', $cols) |
31
|
|
|
: $sql[] = '*'; |
32
|
|
|
|
33
|
|
|
$sql[] = 'FROM'; |
34
|
|
|
$sql[] = implode(', ', $from); |
35
|
|
|
$sql[] = $where; |
36
|
|
|
|
37
|
|
|
if(!empty($groupby)){ |
38
|
|
|
$sql[] = 'GROUP BY'; |
39
|
|
|
$sql[] = implode(', ', $groupby); |
40
|
|
|
} |
41
|
|
|
|
42
|
|
|
if(!empty($orderby)){ |
43
|
|
|
$sql[] = 'ORDER BY'; |
44
|
|
|
$sql[] = implode(', ', $orderby); |
45
|
|
|
} |
46
|
|
|
|
47
|
|
|
return $sql; |
48
|
|
|
} |
49
|
|
|
|
50
|
|
|
/** @inheritdoc */ |
51
|
|
|
public function createTable(string $table, array $cols, string $primaryKey = null, bool $ifNotExists = null, bool $temp = null, string $dir = null):array{ |
52
|
|
|
$sql = [$ifNotExists ? 'RECREATE' : 'CREATE']; // nasty |
53
|
|
|
|
54
|
|
|
if($temp){ |
55
|
|
|
$sql[] = 'GLOBAL TEMPORARY'; |
56
|
|
|
} |
57
|
|
|
|
58
|
|
|
$sql[] = 'TABLE'; |
59
|
|
|
|
60
|
|
|
$n = explode('.', $table); |
61
|
|
|
|
62
|
|
|
$sql[] = $this->quote($n[count($n) - 1]); |
63
|
|
|
|
64
|
|
|
$_cols = []; |
65
|
|
|
|
66
|
|
|
if(!empty($cols)){ |
67
|
|
|
|
68
|
|
|
foreach($cols as $name => $col){ |
69
|
|
|
|
70
|
|
|
if(strtolower($name) === strtolower($primaryKey)){ |
|
|
|
|
71
|
|
|
$x = explode(' NOT NULL', $col, 2); |
72
|
|
|
|
73
|
|
|
if(count($x) > 0){ |
74
|
|
|
$col = $x[0].' NOT NULL PRIMARY KEY'; |
75
|
|
|
$col .= $x[1] ?? ''; |
76
|
|
|
} |
77
|
|
|
|
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
$_cols[] = $col; |
81
|
|
|
} |
82
|
|
|
|
83
|
|
|
$sql[] = '('.implode(', ', $_cols).')'; |
84
|
|
|
} |
85
|
|
|
|
86
|
|
|
return $sql; |
87
|
|
|
} |
88
|
|
|
|
89
|
|
|
/** @inheritdoc */ |
90
|
|
|
public function dropTable(string $table, bool $ifExists):array{ |
91
|
|
|
/* |
92
|
|
|
if($ifExists){ |
93
|
|
|
$sql[] = ' |
94
|
|
|
EXECUTE BLOCK AS BEGIN |
95
|
|
|
IF ( EXISTS ( SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = '.$this->quote($table).' )) |
96
|
|
|
THEN BEGIN |
97
|
|
|
EXECUTE STATEMENT \'DROP TABLE '.$this->quote($table).';\' ; |
98
|
|
|
END |
99
|
|
|
END ^ |
100
|
|
|
'; |
101
|
|
|
} |
102
|
|
|
else{ |
103
|
|
|
|
104
|
|
|
} |
105
|
|
|
*/ |
106
|
|
|
$sql = ['DROP TABLE']; |
107
|
|
|
$sql[] = $this->quote($table); |
108
|
|
|
|
109
|
|
|
|
110
|
|
|
return $sql; |
111
|
|
|
} |
112
|
|
|
|
113
|
|
|
/** @inheritdoc */ |
114
|
|
|
public function fieldspec(string $name, string $type, $length = null, string $attribute = null, string $collation = null, bool $isNull = null, string $defaultType = null, $defaultValue = null, string $extra = null):string{ |
115
|
|
|
$type = strtoupper(trim($type)); |
116
|
|
|
|
117
|
|
|
$field = [$this->quote(trim($name))]; |
118
|
|
|
|
119
|
|
|
$type_translation = [ |
120
|
|
|
'TINYINT' => 'SMALLINT', |
121
|
|
|
'MEDIUMINT' => 'INT', |
122
|
|
|
'BIGINT' => 'INT64', |
123
|
|
|
'REAL' => 'DOUBLE PRECISION', |
124
|
|
|
'DOUBLE' => 'DOUBLE PRECISION', |
125
|
|
|
'BOOLEAN' => 'CHAR(1)', |
126
|
|
|
'BINARY' => 'CHAR', |
127
|
|
|
'VARBINARY' => 'CHAR', |
128
|
|
|
'TINYTEXT' => 'VARCHAR(255)', |
129
|
|
|
'DATETIME' => 'TIMESTAMP', |
130
|
|
|
'IMAGE' => 'BLOB', |
131
|
|
|
'TEXT' => 'BLOB SUB_TYPE TEXT', |
132
|
|
|
'MEDIUMTEXT' => 'BLOB SUB_TYPE TEXT', |
133
|
|
|
'LONGTEXT' => 'BLOB SUB_TYPE TEXT', |
134
|
|
|
][$type] ?? false; |
135
|
|
|
|
136
|
|
|
if($type_translation){ |
137
|
|
|
$field[] = $type_translation; |
138
|
|
|
} |
139
|
|
|
elseif(in_array($type, ['CHAR', 'VARCHAR', 'DECIMAL', 'NUMERIC'], true)){ |
140
|
|
|
$field[] = $type.'('.$length.')'; |
141
|
|
|
} |
142
|
|
|
else{ |
143
|
|
|
$field[] = $type; |
144
|
|
|
} |
145
|
|
|
|
146
|
|
|
if($isNull === false && !in_array($type, ['DATE', 'TIME', 'TIMESTAMP'], true)){ |
147
|
|
|
$field[] = 'NOT NULL'; |
148
|
|
|
} |
149
|
|
|
|
150
|
|
|
$defaultType = strtoupper($defaultType); |
|
|
|
|
151
|
|
|
|
152
|
|
|
if($defaultType === 'USER_DEFINED'){ |
153
|
|
|
|
154
|
|
|
switch(true){ |
155
|
|
|
case $type === 'TIMESTAMP' && intval($defaultValue) === 0: |
156
|
|
|
$field[] = 'DEFAULT 0'; |
157
|
|
|
break; |
158
|
|
|
case strtoupper($defaultValue) === 'NULL' && $isNull === true: |
159
|
|
|
$field[] = 'DEFAULT NULL'; |
160
|
|
|
break; |
161
|
|
|
case $type === 'BOOLEAN': |
162
|
|
|
$field[] = 'DEFAULT '.(preg_match('/^1|T|TRUE|YES$/i', $defaultValue) ? '1' : '0'); |
163
|
|
|
break; |
164
|
|
|
default: |
165
|
|
|
$field[] = 'DEFAULT \''.$defaultValue.'\''; |
166
|
|
|
} |
167
|
|
|
|
168
|
|
|
} |
169
|
|
|
elseif($defaultType === 'CURRENT_TIMESTAMP'){ |
170
|
|
|
$field[] = 'DEFAULT CURRENT_TIMESTAMP'; |
171
|
|
|
} |
172
|
|
|
elseif($defaultType === 'NULL' && $isNull === true){ |
173
|
|
|
$field[] = 'DEFAULT NULL'; |
174
|
|
|
} |
175
|
|
|
|
176
|
|
|
if($attribute){ |
177
|
|
|
$field[] = $attribute; |
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
if($extra){ |
181
|
|
|
$field[] = $extra; |
182
|
|
|
} |
183
|
|
|
|
184
|
|
|
return implode(' ', $field); |
185
|
|
|
} |
186
|
|
|
|
187
|
|
|
/** @inheritdoc */ |
188
|
|
|
public function truncate(string $table):array{ |
189
|
|
|
$sql = ['DELETE FROM'];// RECREATE TABLE [table spec] ...stupid firebird 2.5 |
190
|
|
|
$sql[] = $this->quote($table); |
191
|
|
|
|
192
|
|
|
return $sql; |
193
|
|
|
} |
194
|
|
|
|
195
|
|
|
/** @inheritdoc */ |
196
|
|
|
public function showDatabases():array{ |
197
|
|
|
/** @noinspection SqlResolve */ |
198
|
|
|
return ['SELECT TRIM(LOWER(MON$DATABASE_NAME)) AS "Database" FROM MON$DATABASE']; |
199
|
|
|
} |
200
|
|
|
|
201
|
|
|
|
202
|
|
|
/** @inheritdoc */ |
203
|
|
|
public function showTables(string $database = null, string $pattern = null, string $where = null):array{ |
204
|
|
|
/** @noinspection SqlResolve */ |
205
|
|
|
return ['SELECT TRIM(RDB$RELATION_NAME) AS "tablename" FROM RDB$RELATIONS WHERE RDB$VIEW_BLR IS NULL AND (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)']; |
206
|
|
|
} |
207
|
|
|
|
208
|
|
|
/** |
209
|
|
|
* this is such a hack. i hate firebird so much. |
210
|
|
|
* |
211
|
|
|
* @link https://stackoverflow.com/a/12074601 |
212
|
|
|
* @param string $table |
213
|
|
|
* |
214
|
|
|
* @return array |
215
|
|
|
* @noinspection SqlResolve |
216
|
|
|
*/ |
217
|
|
|
/* public function showCreateTable(string $table):array{ |
218
|
|
|
|
219
|
|
|
$def = $this->db->prepared(' |
220
|
|
|
SELECT |
221
|
|
|
RF.RDB$FIELD_POSITION AS "id", |
222
|
|
|
TRIM(RF.RDB$FIELD_NAME) AS "name", |
223
|
|
|
(CASE F.RDB$FIELD_TYPE |
224
|
|
|
WHEN 7 THEN |
225
|
|
|
CASE F.RDB$FIELD_SUB_TYPE |
226
|
|
|
WHEN 0 THEN \'SMALLINT\' |
227
|
|
|
WHEN 1 THEN \'NUMERIC(\' || F.RDB$FIELD_PRECISION || \',\' || (-F.RDB$FIELD_SCALE) || \')\' |
228
|
|
|
WHEN 2 THEN \'DECIMAL(\' || F.RDB$FIELD_PRECISION || \',\' || (-F.RDB$FIELD_SCALE) || \')\' |
229
|
|
|
END |
230
|
|
|
WHEN 8 THEN |
231
|
|
|
CASE F.RDB$FIELD_SUB_TYPE |
232
|
|
|
WHEN 0 THEN \'INTEGER\' |
233
|
|
|
WHEN 1 THEN \'NUMERIC(\' || F.RDB$FIELD_PRECISION || \',\' || (-F.RDB$FIELD_SCALE) || \')\' |
234
|
|
|
WHEN 2 THEN \'DECIMAL(\' || F.RDB$FIELD_PRECISION || \',\' || (-F.RDB$FIELD_SCALE) || \')\' |
235
|
|
|
END |
236
|
|
|
WHEN 9 THEN \'QUAD\' |
237
|
|
|
WHEN 10 THEN \'FLOAT\' |
238
|
|
|
WHEN 12 THEN \'DATE\' |
239
|
|
|
WHEN 13 THEN \'TIME\' |
240
|
|
|
WHEN 14 THEN \'CHAR(\' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || \') \' |
241
|
|
|
WHEN 16 THEN |
242
|
|
|
CASE F.RDB$FIELD_SUB_TYPE |
243
|
|
|
WHEN 0 THEN \'BIGINT\' |
244
|
|
|
WHEN 1 THEN \'NUMERIC(\' || F.RDB$FIELD_PRECISION || \', \' || (-F.RDB$FIELD_SCALE) || \')\' |
245
|
|
|
WHEN 2 THEN \'DECIMAL(\' || F.RDB$FIELD_PRECISION || \', \' || (-F.RDB$FIELD_SCALE) || \')\' |
246
|
|
|
END |
247
|
|
|
WHEN 27 THEN \'DOUBLE\' |
248
|
|
|
WHEN 35 THEN \'TIMESTAMP\' |
249
|
|
|
WHEN 37 THEN \'VARCHAR(\' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || \')\' |
250
|
|
|
WHEN 40 THEN \'CSTRING\' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || \')\' |
251
|
|
|
WHEN 45 THEN \'BLOB_ID\' |
252
|
|
|
WHEN 261 THEN \'BLOB SUB_TYPE \' || F.RDB$FIELD_SUB_TYPE |
253
|
|
|
ELSE \'RDB$FIELD_TYPE: \' || F.RDB$FIELD_TYPE || \'?\' |
254
|
|
|
END) AS "type", |
255
|
|
|
IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, NULL, \'NOT NULL\') AS "isnull", |
256
|
|
|
COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) AS "default", |
257
|
|
|
TRIM(CH.RDB$CHARACTER_SET_NAME) AS "charset", |
258
|
|
|
TRIM(DCO.RDB$COLLATION_NAME) AS "collation", |
259
|
|
|
TRIM(F.RDB$VALIDATION_SOURCE) AS "check", |
260
|
|
|
TRIM(RF.RDB$DESCRIPTION) AS "desc" |
261
|
|
|
FROM RDB$RELATION_FIELDS RF |
262
|
|
|
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE) |
263
|
|
|
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID) |
264
|
|
|
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)) |
265
|
|
|
WHERE (RF.RDB$RELATION_NAME = ?) AND (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0) |
266
|
|
|
ORDER BY RF.RDB$FIELD_POSITION |
267
|
|
|
', [$table]); |
268
|
|
|
|
269
|
|
|
$idx = $this->db->prepared('SELECT S.RDB$FIELD_POSITION AS "pos", S.RDB$FIELD_NAME AS "field", I.RDB$INDEX_ID AS "id", I.RDB$UNIQUE_FLAG AS "unique" FROM RDB$INDEX_SEGMENTS AS S, RDB$INDICES AS I WHERE S.RDB$INDEX_NAME = I.RDB$INDEX_NAME AND I.RDB$RELATION_NAME = ?', [$table]); |
270
|
|
|
|
271
|
|
|
$fields = []; |
272
|
|
|
if($def instanceof ResultInterface && $def->length > 0){ |
273
|
|
|
|
274
|
|
|
foreach($def as $field){ |
275
|
|
|
$index = $idx[$field->id]['id'] ?? false; |
276
|
|
|
|
277
|
|
|
if($index){ |
278
|
|
|
$index = $index === 1 ? 'PRIMARY KEY' : 'UNIQUE'; // @todo |
279
|
|
|
} |
280
|
|
|
|
281
|
|
|
$fields[] = $this->fieldspec(trim($field->name), trim($field->type), null, null, null, $field->isnull !== 'NOT NULL', null, null, trim($field->default.$index)); |
282
|
|
|
} |
283
|
|
|
|
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
$this->db->prepared('RECREATE GLOBAL TEMPORARY TABLE TEMP$SQL_CREATE ("name" BLOB SUB_TYPE TEXT CHARACTER SET UTF8 NOT NULL, "create" BLOB SUB_TYPE TEXT CHARACTER SET UTF8 NOT NULL) ON COMMIT PRESERVE ROWS'); |
287
|
|
|
|
288
|
|
|
$create = sprintf('CREATE TABLE %1$s (%2$s)', $this->quote($table), PHP_EOL.implode(','.PHP_EOL, $fields).PHP_EOL); |
289
|
|
|
|
290
|
|
|
$this->db->prepared('INSERT INTO TEMP$SQL_CREATE ("name", "create") VALUES (?, ?)', [$table, $create]); |
291
|
|
|
|
292
|
|
|
return ['SELECT "name" AS "Table", "create" AS "Create Table" FROM TEMP$SQL_CREATE']; |
293
|
|
|
} |
294
|
|
|
*/ |
295
|
|
|
} |
296
|
|
|
|