| 1 |
efrain |
1 |
<?php
|
|
|
2 |
/**
|
|
|
3 |
* ADOdb Data Dictionary base class.
|
|
|
4 |
*
|
|
|
5 |
* This file is part of ADOdb, a Database Abstraction Layer library for PHP.
|
|
|
6 |
*
|
|
|
7 |
* @package ADOdb
|
|
|
8 |
* @link https://adodb.org Project's web site and documentation
|
|
|
9 |
* @link https://github.com/ADOdb/ADOdb Source code and issue tracker
|
|
|
10 |
*
|
|
|
11 |
* The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
|
|
|
12 |
* and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
|
|
|
13 |
* any later version. This means you can use it in proprietary products.
|
|
|
14 |
* See the LICENSE.md file distributed with this source code for details.
|
|
|
15 |
* @license BSD-3-Clause
|
|
|
16 |
* @license LGPL-2.1-or-later
|
|
|
17 |
*
|
|
|
18 |
* @copyright 2000-2013 John Lim
|
|
|
19 |
* @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
|
|
|
20 |
*/
|
|
|
21 |
|
|
|
22 |
// security - hide paths
|
|
|
23 |
if (!defined('ADODB_DIR')) die();
|
|
|
24 |
|
|
|
25 |
/**
|
|
|
26 |
* Test script for parser
|
|
|
27 |
*/
|
|
|
28 |
function lens_ParseTest()
|
|
|
29 |
{
|
|
|
30 |
$str = "`zcol ACOL` NUMBER(32,2) DEFAULT 'The \"cow\" (and Jim''s dog) jumps over the moon' PRIMARY, INTI INT AUTO DEFAULT 0, zcol2\"afs ds";
|
|
|
31 |
print "<p>$str</p>";
|
|
|
32 |
$a= lens_ParseArgs($str);
|
|
|
33 |
print "<pre>";
|
|
|
34 |
print_r($a);
|
|
|
35 |
print "</pre>";
|
|
|
36 |
}
|
|
|
37 |
|
|
|
38 |
|
|
|
39 |
if (!function_exists('ctype_alnum')) {
|
|
|
40 |
function ctype_alnum($text) {
|
|
|
41 |
return preg_match('/^[a-z0-9]*$/i', $text);
|
|
|
42 |
}
|
|
|
43 |
}
|
|
|
44 |
|
|
|
45 |
//Lens_ParseTest();
|
|
|
46 |
|
|
|
47 |
/**
|
|
|
48 |
Parse arguments, treat "text" (text) and 'text' as quotation marks.
|
|
|
49 |
To escape, use "" or '' or ))
|
|
|
50 |
|
|
|
51 |
Will read in "abc def" sans quotes, as: abc def
|
|
|
52 |
Same with 'abc def'.
|
|
|
53 |
However if `abc def`, then will read in as `abc def`
|
|
|
54 |
|
|
|
55 |
@param endstmtchar Character that indicates end of statement
|
|
|
56 |
@param tokenchars Include the following characters in tokens apart from A-Z and 0-9
|
|
|
57 |
@returns 2 dimensional array containing parsed tokens.
|
|
|
58 |
*/
|
|
|
59 |
function lens_ParseArgs($args,$endstmtchar=',',$tokenchars='_.-')
|
|
|
60 |
{
|
|
|
61 |
$pos = 0;
|
|
|
62 |
$intoken = false;
|
|
|
63 |
$stmtno = 0;
|
|
|
64 |
$endquote = false;
|
|
|
65 |
$tokens = array();
|
|
|
66 |
$tokens[$stmtno] = array();
|
|
|
67 |
$max = strlen($args);
|
|
|
68 |
$quoted = false;
|
|
|
69 |
$tokarr = array();
|
|
|
70 |
|
|
|
71 |
while ($pos < $max) {
|
|
|
72 |
$ch = substr($args,$pos,1);
|
|
|
73 |
switch($ch) {
|
|
|
74 |
case ' ':
|
|
|
75 |
case "\t":
|
|
|
76 |
case "\n":
|
|
|
77 |
case "\r":
|
|
|
78 |
if (!$quoted) {
|
|
|
79 |
if ($intoken) {
|
|
|
80 |
$intoken = false;
|
|
|
81 |
$tokens[$stmtno][] = implode('',$tokarr);
|
|
|
82 |
}
|
|
|
83 |
break;
|
|
|
84 |
}
|
|
|
85 |
|
|
|
86 |
$tokarr[] = $ch;
|
|
|
87 |
break;
|
|
|
88 |
|
|
|
89 |
case '`':
|
|
|
90 |
if ($intoken) $tokarr[] = $ch;
|
|
|
91 |
case '(':
|
|
|
92 |
case ')':
|
|
|
93 |
case '"':
|
|
|
94 |
case "'":
|
|
|
95 |
|
|
|
96 |
if ($intoken) {
|
|
|
97 |
if (empty($endquote)) {
|
|
|
98 |
$tokens[$stmtno][] = implode('',$tokarr);
|
|
|
99 |
if ($ch == '(') $endquote = ')';
|
|
|
100 |
else $endquote = $ch;
|
|
|
101 |
$quoted = true;
|
|
|
102 |
$intoken = true;
|
|
|
103 |
$tokarr = array();
|
|
|
104 |
} else if ($endquote == $ch) {
|
|
|
105 |
$ch2 = substr($args,$pos+1,1);
|
|
|
106 |
if ($ch2 == $endquote) {
|
|
|
107 |
$pos += 1;
|
|
|
108 |
$tokarr[] = $ch2;
|
|
|
109 |
} else {
|
|
|
110 |
$quoted = false;
|
|
|
111 |
$intoken = false;
|
|
|
112 |
$tokens[$stmtno][] = implode('',$tokarr);
|
|
|
113 |
$endquote = '';
|
|
|
114 |
}
|
|
|
115 |
} else
|
|
|
116 |
$tokarr[] = $ch;
|
|
|
117 |
|
|
|
118 |
}else {
|
|
|
119 |
|
|
|
120 |
if ($ch == '(') $endquote = ')';
|
|
|
121 |
else $endquote = $ch;
|
|
|
122 |
$quoted = true;
|
|
|
123 |
$intoken = true;
|
|
|
124 |
$tokarr = array();
|
|
|
125 |
if ($ch == '`') $tokarr[] = '`';
|
|
|
126 |
}
|
|
|
127 |
break;
|
|
|
128 |
|
|
|
129 |
default:
|
|
|
130 |
|
|
|
131 |
if (!$intoken) {
|
|
|
132 |
if ($ch == $endstmtchar) {
|
|
|
133 |
$stmtno += 1;
|
|
|
134 |
$tokens[$stmtno] = array();
|
|
|
135 |
break;
|
|
|
136 |
}
|
|
|
137 |
|
|
|
138 |
$intoken = true;
|
|
|
139 |
$quoted = false;
|
|
|
140 |
$endquote = false;
|
|
|
141 |
$tokarr = array();
|
|
|
142 |
|
|
|
143 |
}
|
|
|
144 |
|
|
|
145 |
if ($quoted) $tokarr[] = $ch;
|
|
|
146 |
else if (ctype_alnum($ch) || strpos($tokenchars,$ch) !== false) $tokarr[] = $ch;
|
|
|
147 |
else {
|
|
|
148 |
if ($ch == $endstmtchar) {
|
|
|
149 |
$tokens[$stmtno][] = implode('',$tokarr);
|
|
|
150 |
$stmtno += 1;
|
|
|
151 |
$tokens[$stmtno] = array();
|
|
|
152 |
$intoken = false;
|
|
|
153 |
$tokarr = array();
|
|
|
154 |
break;
|
|
|
155 |
}
|
|
|
156 |
$tokens[$stmtno][] = implode('',$tokarr);
|
|
|
157 |
$tokens[$stmtno][] = $ch;
|
|
|
158 |
$intoken = false;
|
|
|
159 |
}
|
|
|
160 |
}
|
|
|
161 |
$pos += 1;
|
|
|
162 |
}
|
|
|
163 |
if ($intoken) $tokens[$stmtno][] = implode('',$tokarr);
|
|
|
164 |
|
|
|
165 |
return $tokens;
|
|
|
166 |
}
|
|
|
167 |
|
|
|
168 |
|
|
|
169 |
class ADODB_DataDict {
|
|
|
170 |
/** @var ADOConnection */
|
|
|
171 |
var $connection;
|
|
|
172 |
var $debug = false;
|
|
|
173 |
var $dropTable = 'DROP TABLE %s';
|
|
|
174 |
var $renameTable = 'RENAME TABLE %s TO %s';
|
|
|
175 |
var $dropIndex = 'DROP INDEX %s';
|
|
|
176 |
var $addCol = ' ADD';
|
|
|
177 |
var $alterCol = ' ALTER COLUMN';
|
|
|
178 |
var $dropCol = ' DROP COLUMN';
|
|
|
179 |
var $renameColumn = 'ALTER TABLE %s RENAME COLUMN %s TO %s'; // table, old-column, new-column, column-definitions (not used by default)
|
|
|
180 |
var $nameRegex = '\w';
|
|
|
181 |
var $nameRegexBrackets = 'a-zA-Z0-9_\(\)';
|
|
|
182 |
var $schema = false;
|
|
|
183 |
var $serverInfo = array();
|
|
|
184 |
var $autoIncrement = false;
|
|
|
185 |
var $dataProvider;
|
|
|
186 |
var $invalidResizeTypes4 = array('CLOB','BLOB','TEXT','DATE','TIME'); // for changeTableSQL
|
|
|
187 |
var $blobSize = 100; /// any varchar/char field this size or greater is treated as a blob
|
|
|
188 |
/// in other words, we use a text area for editing.
|
|
|
189 |
/** @var string Uppercase driver name */
|
|
|
190 |
var $upperName;
|
|
|
191 |
|
|
|
192 |
/*
|
|
|
193 |
* Indicates whether a BLOB/CLOB field will allow a NOT NULL setting
|
|
|
194 |
* The type is whatever is matched to an X or X2 or B type. We must
|
|
|
195 |
* explicitly set the value in the driver to switch the behaviour on
|
|
|
196 |
*/
|
|
|
197 |
public $blobAllowsNotNull;
|
|
|
198 |
/*
|
|
|
199 |
* Indicates whether a BLOB/CLOB field will allow a DEFAULT set
|
|
|
200 |
* The type is whatever is matched to an X or X2 or B type. We must
|
|
|
201 |
* explicitly set the value in the driver to switch the behaviour on
|
|
|
202 |
*/
|
|
|
203 |
public $blobAllowsDefaultValue;
|
|
|
204 |
|
|
|
205 |
|
|
|
206 |
/**
|
|
|
207 |
* @var string String to use to quote identifiers and names
|
|
|
208 |
*/
|
|
|
209 |
public $quote;
|
|
|
210 |
|
|
|
211 |
function getCommentSQL($table,$col)
|
|
|
212 |
{
|
|
|
213 |
return false;
|
|
|
214 |
}
|
|
|
215 |
|
|
|
216 |
function setCommentSQL($table,$col,$cmt)
|
|
|
217 |
{
|
|
|
218 |
return false;
|
|
|
219 |
}
|
|
|
220 |
|
|
|
221 |
function metaTables()
|
|
|
222 |
{
|
|
|
223 |
if (!$this->connection->isConnected()) return array();
|
|
|
224 |
return $this->connection->metaTables();
|
|
|
225 |
}
|
|
|
226 |
|
|
|
227 |
function metaColumns($tab, $upper=true, $schema=false)
|
|
|
228 |
{
|
|
|
229 |
if (!$this->connection->isConnected()) return array();
|
|
|
230 |
return $this->connection->metaColumns($this->tableName($tab), $upper, $schema);
|
|
|
231 |
}
|
|
|
232 |
|
|
|
233 |
function metaPrimaryKeys($tab,$owner=false,$intkey=false)
|
|
|
234 |
{
|
|
|
235 |
if (!$this->connection->isConnected()) return array();
|
|
|
236 |
return $this->connection->metaPrimaryKeys($this->tableName($tab), $owner, $intkey);
|
|
|
237 |
}
|
|
|
238 |
|
|
|
239 |
function metaIndexes($table, $primary = false, $owner = false)
|
|
|
240 |
{
|
|
|
241 |
if (!$this->connection->isConnected()) return array();
|
|
|
242 |
return $this->connection->metaIndexes($this->tableName($table), $primary, $owner);
|
|
|
243 |
}
|
|
|
244 |
|
|
|
245 |
function metaType($t,$len=-1,$fieldobj=false)
|
|
|
246 |
{
|
|
|
247 |
static $typeMap = array(
|
|
|
248 |
'VARCHAR' => 'C',
|
|
|
249 |
'VARCHAR2' => 'C',
|
|
|
250 |
'CHAR' => 'C',
|
|
|
251 |
'C' => 'C',
|
|
|
252 |
'STRING' => 'C',
|
|
|
253 |
'NCHAR' => 'C',
|
|
|
254 |
'NVARCHAR' => 'C',
|
|
|
255 |
'VARYING' => 'C',
|
|
|
256 |
'BPCHAR' => 'C',
|
|
|
257 |
'CHARACTER' => 'C',
|
|
|
258 |
'INTERVAL' => 'C', # Postgres
|
|
|
259 |
'MACADDR' => 'C', # postgres
|
|
|
260 |
'VAR_STRING' => 'C', # mysql
|
|
|
261 |
##
|
|
|
262 |
'LONGCHAR' => 'X',
|
|
|
263 |
'TEXT' => 'X',
|
|
|
264 |
'NTEXT' => 'X',
|
|
|
265 |
'M' => 'X',
|
|
|
266 |
'X' => 'X',
|
|
|
267 |
'CLOB' => 'X',
|
|
|
268 |
'NCLOB' => 'X',
|
|
|
269 |
'LVARCHAR' => 'X',
|
|
|
270 |
##
|
|
|
271 |
'BLOB' => 'B',
|
|
|
272 |
'IMAGE' => 'B',
|
|
|
273 |
'BINARY' => 'B',
|
|
|
274 |
'VARBINARY' => 'B',
|
|
|
275 |
'LONGBINARY' => 'B',
|
|
|
276 |
'B' => 'B',
|
|
|
277 |
##
|
|
|
278 |
'YEAR' => 'D', // mysql
|
|
|
279 |
'DATE' => 'D',
|
|
|
280 |
'D' => 'D',
|
|
|
281 |
##
|
|
|
282 |
'UNIQUEIDENTIFIER' => 'C', # MS SQL Server
|
|
|
283 |
##
|
|
|
284 |
'TIME' => 'T',
|
|
|
285 |
'TIMESTAMP' => 'T',
|
|
|
286 |
'DATETIME' => 'T',
|
|
|
287 |
'TIMESTAMPTZ' => 'T',
|
|
|
288 |
'SMALLDATETIME' => 'T',
|
|
|
289 |
'T' => 'T',
|
|
|
290 |
'TIMESTAMP WITHOUT TIME ZONE' => 'T', // postgresql
|
|
|
291 |
##
|
|
|
292 |
'BOOL' => 'L',
|
|
|
293 |
'BOOLEAN' => 'L',
|
|
|
294 |
'BIT' => 'L',
|
|
|
295 |
'L' => 'L',
|
|
|
296 |
##
|
|
|
297 |
'COUNTER' => 'R',
|
|
|
298 |
'R' => 'R',
|
|
|
299 |
'SERIAL' => 'R', // ifx
|
|
|
300 |
'INT IDENTITY' => 'R',
|
|
|
301 |
##
|
|
|
302 |
'INT' => 'I',
|
|
|
303 |
'INT2' => 'I',
|
|
|
304 |
'INT4' => 'I',
|
|
|
305 |
'INT8' => 'I',
|
|
|
306 |
'INTEGER' => 'I',
|
|
|
307 |
'INTEGER UNSIGNED' => 'I',
|
|
|
308 |
'SHORT' => 'I',
|
|
|
309 |
'TINYINT' => 'I',
|
|
|
310 |
'SMALLINT' => 'I',
|
|
|
311 |
'I' => 'I',
|
|
|
312 |
##
|
|
|
313 |
'LONG' => 'N', // interbase is numeric, oci8 is blob
|
|
|
314 |
'BIGINT' => 'N', // this is bigger than PHP 32-bit integers
|
|
|
315 |
'DECIMAL' => 'N',
|
|
|
316 |
'DEC' => 'N',
|
|
|
317 |
'REAL' => 'N',
|
|
|
318 |
'DOUBLE' => 'N',
|
|
|
319 |
'DOUBLE PRECISION' => 'N',
|
|
|
320 |
'SMALLFLOAT' => 'N',
|
|
|
321 |
'FLOAT' => 'N',
|
|
|
322 |
'NUMBER' => 'N',
|
|
|
323 |
'NUM' => 'N',
|
|
|
324 |
'NUMERIC' => 'N',
|
|
|
325 |
'MONEY' => 'N',
|
|
|
326 |
|
|
|
327 |
## informix 9.2
|
|
|
328 |
'SQLINT' => 'I',
|
|
|
329 |
'SQLSERIAL' => 'I',
|
|
|
330 |
'SQLSMINT' => 'I',
|
|
|
331 |
'SQLSMFLOAT' => 'N',
|
|
|
332 |
'SQLFLOAT' => 'N',
|
|
|
333 |
'SQLMONEY' => 'N',
|
|
|
334 |
'SQLDECIMAL' => 'N',
|
|
|
335 |
'SQLDATE' => 'D',
|
|
|
336 |
'SQLVCHAR' => 'C',
|
|
|
337 |
'SQLCHAR' => 'C',
|
|
|
338 |
'SQLDTIME' => 'T',
|
|
|
339 |
'SQLINTERVAL' => 'N',
|
|
|
340 |
'SQLBYTES' => 'B',
|
|
|
341 |
'SQLTEXT' => 'X',
|
|
|
342 |
## informix 10
|
|
|
343 |
"SQLINT8" => 'I8',
|
|
|
344 |
"SQLSERIAL8" => 'I8',
|
|
|
345 |
"SQLNCHAR" => 'C',
|
|
|
346 |
"SQLNVCHAR" => 'C',
|
|
|
347 |
"SQLLVARCHAR" => 'X',
|
|
|
348 |
"SQLBOOL" => 'L'
|
|
|
349 |
);
|
|
|
350 |
|
|
|
351 |
if (!$this->connection->isConnected()) {
|
|
|
352 |
$t = strtoupper($t);
|
|
|
353 |
if (isset($typeMap[$t])) return $typeMap[$t];
|
|
|
354 |
return ADODB_DEFAULT_METATYPE;
|
|
|
355 |
}
|
|
|
356 |
return $this->connection->metaType($t,$len,$fieldobj);
|
|
|
357 |
}
|
|
|
358 |
|
|
|
359 |
function nameQuote($name = NULL,$allowBrackets=false)
|
|
|
360 |
{
|
|
|
361 |
if (!is_string($name)) {
|
|
|
362 |
return false;
|
|
|
363 |
}
|
|
|
364 |
|
|
|
365 |
$name = trim($name);
|
|
|
366 |
|
|
|
367 |
if ( !is_object($this->connection) ) {
|
|
|
368 |
return $name;
|
|
|
369 |
}
|
|
|
370 |
|
|
|
371 |
$quote = $this->connection->nameQuote;
|
|
|
372 |
|
|
|
373 |
// if name is of the form `name`, quote it
|
|
|
374 |
if ( preg_match('/^`(.+)`$/', $name, $matches) ) {
|
|
|
375 |
return $quote . $matches[1] . $quote;
|
|
|
376 |
}
|
|
|
377 |
|
|
|
378 |
// if name contains special characters, quote it
|
|
|
379 |
$regex = ($allowBrackets) ? $this->nameRegexBrackets : $this->nameRegex;
|
|
|
380 |
|
|
|
381 |
if ( !preg_match('/^[' . $regex . ']+$/', $name) ) {
|
|
|
382 |
return $quote . $name . $quote;
|
|
|
383 |
}
|
|
|
384 |
|
|
|
385 |
return $name;
|
|
|
386 |
}
|
|
|
387 |
|
|
|
388 |
function tableName($name)
|
|
|
389 |
{
|
|
|
390 |
if ( $this->schema ) {
|
|
|
391 |
return $this->nameQuote($this->schema) .'.'. $this->nameQuote($name);
|
|
|
392 |
}
|
|
|
393 |
return $this->nameQuote($name);
|
|
|
394 |
}
|
|
|
395 |
|
|
|
396 |
// Executes the sql array returned by getTableSQL and getIndexSQL
|
|
|
397 |
function executeSQLArray($sql, $continueOnError = true)
|
|
|
398 |
{
|
|
|
399 |
$rez = 2;
|
|
|
400 |
$conn = $this->connection;
|
|
|
401 |
$saved = $conn->debug;
|
|
|
402 |
foreach($sql as $line) {
|
|
|
403 |
|
|
|
404 |
if ($this->debug) $conn->debug = true;
|
|
|
405 |
$ok = $conn->execute($line);
|
|
|
406 |
$conn->debug = $saved;
|
|
|
407 |
if (!$ok) {
|
|
|
408 |
if ($this->debug) ADOConnection::outp($conn->errorMsg());
|
|
|
409 |
if (!$continueOnError) return 0;
|
|
|
410 |
$rez = 1;
|
|
|
411 |
}
|
|
|
412 |
}
|
|
|
413 |
return $rez;
|
|
|
414 |
}
|
|
|
415 |
|
|
|
416 |
/**
|
|
|
417 |
Returns the actual type given a character code.
|
|
|
418 |
|
|
|
419 |
C: varchar
|
|
|
420 |
X: CLOB (character large object) or largest varchar size if CLOB is not supported
|
|
|
421 |
C2: Multibyte varchar
|
|
|
422 |
X2: Multibyte CLOB
|
|
|
423 |
|
|
|
424 |
B: BLOB (binary large object)
|
|
|
425 |
|
|
|
426 |
D: Date
|
|
|
427 |
T: Date-time
|
|
|
428 |
L: Integer field suitable for storing booleans (0 or 1)
|
|
|
429 |
I: Integer
|
|
|
430 |
F: Floating point number
|
|
|
431 |
N: Numeric or decimal number
|
|
|
432 |
*/
|
|
|
433 |
|
|
|
434 |
function actualType($meta)
|
|
|
435 |
{
|
|
|
436 |
$meta = strtoupper($meta);
|
|
|
437 |
|
|
|
438 |
/*
|
|
|
439 |
* Add support for custom meta types. We do this
|
|
|
440 |
* first, that allows us to override existing types
|
|
|
441 |
*/
|
|
|
442 |
if (isset($this->connection->customMetaTypes[$meta]))
|
|
|
443 |
return $this->connection->customMetaTypes[$meta]['actual'];
|
|
|
444 |
|
|
|
445 |
return $meta;
|
|
|
446 |
}
|
|
|
447 |
|
|
|
448 |
function createDatabase($dbname,$options=false)
|
|
|
449 |
{
|
|
|
450 |
$options = $this->_options($options);
|
|
|
451 |
$sql = array();
|
|
|
452 |
|
|
|
453 |
$s = 'CREATE DATABASE ' . $this->nameQuote($dbname);
|
|
|
454 |
if (isset($options[$this->upperName]))
|
|
|
455 |
$s .= ' '.$options[$this->upperName];
|
|
|
456 |
|
|
|
457 |
$sql[] = $s;
|
|
|
458 |
return $sql;
|
|
|
459 |
}
|
|
|
460 |
|
|
|
461 |
/*
|
|
|
462 |
Generates the SQL to create index. Returns an array of sql strings.
|
|
|
463 |
*/
|
|
|
464 |
function createIndexSQL($idxname, $tabname, $flds, $idxoptions = false)
|
|
|
465 |
{
|
|
|
466 |
if (!is_array($flds)) {
|
|
|
467 |
$flds = explode(',',$flds);
|
|
|
468 |
}
|
|
|
469 |
|
|
|
470 |
foreach($flds as $key => $fld) {
|
|
|
471 |
# some indexes can use partial fields, eg. index first 32 chars of "name" with NAME(32)
|
|
|
472 |
$flds[$key] = $this->nameQuote($fld,$allowBrackets=true);
|
|
|
473 |
}
|
|
|
474 |
|
|
|
475 |
return $this->_indexSQL($this->nameQuote($idxname), $this->tableName($tabname), $flds, $this->_options($idxoptions));
|
|
|
476 |
}
|
|
|
477 |
|
|
|
478 |
function dropIndexSQL ($idxname, $tabname = NULL)
|
|
|
479 |
{
|
|
|
480 |
return array(sprintf($this->dropIndex, $this->nameQuote($idxname), $this->tableName($tabname)));
|
|
|
481 |
}
|
|
|
482 |
|
|
|
483 |
function setSchema($schema)
|
|
|
484 |
{
|
|
|
485 |
$this->schema = $schema;
|
|
|
486 |
}
|
|
|
487 |
|
|
|
488 |
function addColumnSQL($tabname, $flds)
|
|
|
489 |
{
|
|
|
490 |
$tabname = $this->tableName($tabname);
|
|
|
491 |
$sql = array();
|
|
|
492 |
list($lines,$pkey,$idxs) = $this->_genFields($flds);
|
|
|
493 |
// genfields can return FALSE at times
|
|
|
494 |
if ($lines == null) $lines = array();
|
|
|
495 |
$alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
|
|
|
496 |
foreach($lines as $v) {
|
|
|
497 |
$sql[] = $alter . $v;
|
|
|
498 |
}
|
|
|
499 |
if (is_array($idxs)) {
|
|
|
500 |
foreach($idxs as $idx => $idxdef) {
|
|
|
501 |
$sql_idxs = $this->createIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
|
|
|
502 |
$sql = array_merge($sql, $sql_idxs);
|
|
|
503 |
}
|
|
|
504 |
}
|
|
|
505 |
return $sql;
|
|
|
506 |
}
|
|
|
507 |
|
|
|
508 |
/**
|
|
|
509 |
* Change the definition of one column
|
|
|
510 |
*
|
|
|
511 |
* As some DBMs can't do that on their own, you need to supply the complete definition of the new table,
|
|
|
512 |
* to allow recreating the table and copying the content over to the new table
|
| 1441 |
ariadna |
513 |
*
|
|
|
514 |
* @param string $tabname table-name
|
|
|
515 |
* @param array|string $flds column-name and type for the changed column
|
|
|
516 |
* @param string $tableflds='' complete definition of the new table, eg. for postgres, default ''
|
| 1 |
efrain |
517 |
* @param array|string $tableoptions='' options for the new table see createTableSQL, default ''
|
| 1441 |
ariadna |
518 |
*
|
| 1 |
efrain |
519 |
* @return array with SQL strings
|
|
|
520 |
*/
|
|
|
521 |
function alterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
|
|
|
522 |
{
|
|
|
523 |
$tabname = $this->tableName($tabname);
|
|
|
524 |
$sql = array();
|
|
|
525 |
list($lines,$pkey,$idxs) = $this->_genFields($flds);
|
|
|
526 |
// genfields can return FALSE at times
|
|
|
527 |
if ($lines == null) $lines = array();
|
|
|
528 |
$alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
|
|
|
529 |
foreach($lines as $v) {
|
|
|
530 |
$sql[] = $alter . $v;
|
|
|
531 |
}
|
|
|
532 |
if (is_array($idxs)) {
|
|
|
533 |
foreach($idxs as $idx => $idxdef) {
|
|
|
534 |
$sql_idxs = $this->createIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
|
|
|
535 |
$sql = array_merge($sql, $sql_idxs);
|
|
|
536 |
}
|
|
|
537 |
|
|
|
538 |
}
|
|
|
539 |
return $sql;
|
|
|
540 |
}
|
|
|
541 |
|
|
|
542 |
/**
|
|
|
543 |
* Rename one column
|
|
|
544 |
*
|
|
|
545 |
* Some DBMs can only do this together with changeing the type of the column (even if that stays the same, eg. mysql)
|
|
|
546 |
* @param string $tabname table-name
|
|
|
547 |
* @param string $oldcolumn column-name to be renamed
|
|
|
548 |
* @param string $newcolumn new column-name
|
|
|
549 |
* @param string $flds='' complete column-definition-string like for addColumnSQL, only used by mysql atm., default=''
|
|
|
550 |
* @return array with SQL strings
|
|
|
551 |
*/
|
|
|
552 |
function renameColumnSQL($tabname,$oldcolumn,$newcolumn,$flds='')
|
|
|
553 |
{
|
|
|
554 |
$tabname = $this->tableName($tabname);
|
|
|
555 |
if ($flds) {
|
|
|
556 |
list($lines,$pkey,$idxs) = $this->_genFields($flds);
|
|
|
557 |
// genfields can return FALSE at times
|
|
|
558 |
if ($lines == null) $lines = array();
|
|
|
559 |
$first = current($lines);
|
|
|
560 |
list(,$column_def) = preg_split("/[\t ]+/",$first,2);
|
|
|
561 |
}
|
|
|
562 |
return array(sprintf($this->renameColumn,$tabname,$this->nameQuote($oldcolumn),$this->nameQuote($newcolumn),$column_def));
|
|
|
563 |
}
|
|
|
564 |
|
|
|
565 |
/**
|
|
|
566 |
* Drop one column
|
|
|
567 |
*
|
|
|
568 |
* Some DBM's can't do that on their own, you need to supply the complete definition of the new table,
|
|
|
569 |
* to allow, recreating the table and copying the content over to the new table
|
|
|
570 |
* @param string $tabname table-name
|
|
|
571 |
* @param string $flds column-name and type for the changed column
|
|
|
572 |
* @param string $tableflds='' complete definition of the new table, eg. for postgres, default ''
|
|
|
573 |
* @param array|string $tableoptions='' options for the new table see createTableSQL, default ''
|
|
|
574 |
* @return array with SQL strings
|
|
|
575 |
*/
|
|
|
576 |
function dropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
|
|
|
577 |
{
|
|
|
578 |
$tabname = $this->tableName($tabname);
|
|
|
579 |
if (!is_array($flds)) $flds = explode(',',$flds);
|
|
|
580 |
$sql = array();
|
|
|
581 |
$alter = 'ALTER TABLE ' . $tabname . $this->dropCol . ' ';
|
|
|
582 |
foreach($flds as $v) {
|
|
|
583 |
$sql[] = $alter . $this->nameQuote($v);
|
|
|
584 |
}
|
|
|
585 |
return $sql;
|
|
|
586 |
}
|
|
|
587 |
|
|
|
588 |
function dropTableSQL($tabname)
|
|
|
589 |
{
|
|
|
590 |
return array (sprintf($this->dropTable, $this->tableName($tabname)));
|
|
|
591 |
}
|
|
|
592 |
|
|
|
593 |
function renameTableSQL($tabname,$newname)
|
|
|
594 |
{
|
|
|
595 |
return array (sprintf($this->renameTable, $this->tableName($tabname),$this->tableName($newname)));
|
|
|
596 |
}
|
|
|
597 |
|
|
|
598 |
/**
|
|
|
599 |
Generate the SQL to create table. Returns an array of sql strings.
|
|
|
600 |
*/
|
|
|
601 |
function createTableSQL($tabname, $flds, $tableoptions=array())
|
|
|
602 |
{
|
|
|
603 |
list($lines,$pkey,$idxs) = $this->_genFields($flds, true);
|
|
|
604 |
// genfields can return FALSE at times
|
|
|
605 |
if ($lines == null) $lines = array();
|
|
|
606 |
|
|
|
607 |
$taboptions = $this->_options($tableoptions);
|
|
|
608 |
$tabname = $this->tableName($tabname);
|
|
|
609 |
$sql = $this->_tableSQL($tabname,$lines,$pkey,$taboptions);
|
|
|
610 |
|
|
|
611 |
// ggiunta - 2006/10/12 - KLUDGE:
|
|
|
612 |
// if we are on autoincrement, and table options includes REPLACE, the
|
|
|
613 |
// autoincrement sequence has already been dropped on table creation sql, so
|
|
|
614 |
// we avoid passing REPLACE to trigger creation code. This prevents
|
|
|
615 |
// creating sql that double-drops the sequence
|
|
|
616 |
if ($this->autoIncrement && isset($taboptions['REPLACE']))
|
|
|
617 |
unset($taboptions['REPLACE']);
|
|
|
618 |
$tsql = $this->_triggers($tabname,$taboptions);
|
|
|
619 |
foreach($tsql as $s) $sql[] = $s;
|
|
|
620 |
|
|
|
621 |
if (is_array($idxs)) {
|
|
|
622 |
foreach($idxs as $idx => $idxdef) {
|
|
|
623 |
$sql_idxs = $this->createIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
|
|
|
624 |
$sql = array_merge($sql, $sql_idxs);
|
|
|
625 |
}
|
|
|
626 |
}
|
|
|
627 |
|
|
|
628 |
return $sql;
|
|
|
629 |
}
|
|
|
630 |
|
|
|
631 |
|
|
|
632 |
|
|
|
633 |
function _genFields($flds,$widespacing=false)
|
|
|
634 |
{
|
|
|
635 |
if (is_string($flds)) {
|
|
|
636 |
$padding = ' ';
|
|
|
637 |
$txt = $flds.$padding;
|
|
|
638 |
$flds = array();
|
|
|
639 |
$flds0 = lens_ParseArgs($txt,',');
|
|
|
640 |
$hasparam = false;
|
|
|
641 |
foreach($flds0 as $f0) {
|
|
|
642 |
$f1 = array();
|
|
|
643 |
foreach($f0 as $token) {
|
|
|
644 |
switch (strtoupper($token)) {
|
|
|
645 |
case 'INDEX':
|
|
|
646 |
$f1['INDEX'] = '';
|
|
|
647 |
// fall through intentionally
|
|
|
648 |
case 'CONSTRAINT':
|
|
|
649 |
case 'DEFAULT':
|
|
|
650 |
$hasparam = $token;
|
|
|
651 |
break;
|
|
|
652 |
default:
|
|
|
653 |
if ($hasparam) $f1[$hasparam] = $token;
|
|
|
654 |
else $f1[] = $token;
|
|
|
655 |
$hasparam = false;
|
|
|
656 |
break;
|
|
|
657 |
}
|
|
|
658 |
}
|
|
|
659 |
// 'index' token without a name means single column index: name it after column
|
|
|
660 |
if (array_key_exists('INDEX', $f1) && $f1['INDEX'] == '') {
|
|
|
661 |
$f1['INDEX'] = isset($f0['NAME']) ? $f0['NAME'] : $f0[0];
|
|
|
662 |
// check if column name used to create an index name was quoted
|
|
|
663 |
if (($f1['INDEX'][0] == '"' || $f1['INDEX'][0] == "'" || $f1['INDEX'][0] == "`") &&
|
|
|
664 |
($f1['INDEX'][0] == substr($f1['INDEX'], -1))) {
|
|
|
665 |
$f1['INDEX'] = $f1['INDEX'][0].'idx_'.substr($f1['INDEX'], 1, -1).$f1['INDEX'][0];
|
|
|
666 |
}
|
|
|
667 |
else
|
|
|
668 |
$f1['INDEX'] = 'idx_'.$f1['INDEX'];
|
|
|
669 |
}
|
|
|
670 |
// reset it, so we don't get next field 1st token as INDEX...
|
|
|
671 |
$hasparam = false;
|
|
|
672 |
|
|
|
673 |
$flds[] = $f1;
|
|
|
674 |
|
|
|
675 |
}
|
|
|
676 |
}
|
|
|
677 |
$this->autoIncrement = false;
|
|
|
678 |
$lines = array();
|
|
|
679 |
$pkey = array();
|
|
|
680 |
$idxs = array();
|
|
|
681 |
foreach($flds as $fld) {
|
|
|
682 |
if (is_array($fld))
|
|
|
683 |
$fld = array_change_key_case($fld,CASE_UPPER);
|
|
|
684 |
$fname = false;
|
|
|
685 |
$fdefault = false;
|
|
|
686 |
$fautoinc = false;
|
|
|
687 |
$ftype = false;
|
|
|
688 |
$fsize = false;
|
|
|
689 |
$fprec = false;
|
|
|
690 |
$fprimary = false;
|
|
|
691 |
$fnoquote = false;
|
|
|
692 |
$fdefts = false;
|
|
|
693 |
$fdefdate = false;
|
|
|
694 |
$fconstraint = false;
|
|
|
695 |
$fnotnull = false;
|
|
|
696 |
$funsigned = false;
|
|
|
697 |
$findex = '';
|
|
|
698 |
$funiqueindex = false;
|
|
|
699 |
$fOptions = array();
|
|
|
700 |
|
|
|
701 |
//-----------------
|
|
|
702 |
// Parse attributes
|
|
|
703 |
foreach($fld as $attr => $v) {
|
|
|
704 |
if ($attr == 2 && is_numeric($v))
|
|
|
705 |
$attr = 'SIZE';
|
|
|
706 |
elseif ($attr == 2 && strtoupper($ftype) == 'ENUM')
|
|
|
707 |
$attr = 'ENUM';
|
|
|
708 |
else if (is_numeric($attr) && $attr > 1 && !is_numeric($v))
|
|
|
709 |
$attr = strtoupper($v);
|
|
|
710 |
|
|
|
711 |
switch($attr) {
|
|
|
712 |
case '0':
|
|
|
713 |
case 'NAME': $fname = $v; break;
|
|
|
714 |
case '1':
|
|
|
715 |
case 'TYPE':
|
|
|
716 |
|
|
|
717 |
$ty = $v;
|
|
|
718 |
$ftype = $this->actualType(strtoupper($v));
|
|
|
719 |
break;
|
|
|
720 |
|
|
|
721 |
case 'SIZE':
|
|
|
722 |
$dotat = strpos($v,'.');
|
|
|
723 |
if ($dotat === false)
|
|
|
724 |
$dotat = strpos($v,',');
|
|
|
725 |
if ($dotat === false)
|
|
|
726 |
$fsize = $v;
|
|
|
727 |
else {
|
|
|
728 |
|
|
|
729 |
$fsize = substr($v,0,$dotat);
|
|
|
730 |
$fprec = substr($v,$dotat+1);
|
|
|
731 |
|
|
|
732 |
}
|
|
|
733 |
break;
|
|
|
734 |
case 'UNSIGNED': $funsigned = true; break;
|
|
|
735 |
case 'AUTOINCREMENT':
|
|
|
736 |
case 'AUTO': $fautoinc = true; $fnotnull = true; break;
|
|
|
737 |
case 'KEY':
|
|
|
738 |
// a primary key col can be non unique in itself (if key spans many cols...)
|
|
|
739 |
case 'PRIMARY': $fprimary = $v; $fnotnull = true; /*$funiqueindex = true;*/ break;
|
|
|
740 |
case 'DEF':
|
|
|
741 |
case 'DEFAULT': $fdefault = $v; break;
|
|
|
742 |
case 'NOTNULL': $fnotnull = $v; break;
|
|
|
743 |
case 'NOQUOTE': $fnoquote = $v; break;
|
|
|
744 |
case 'DEFDATE': $fdefdate = $v; break;
|
|
|
745 |
case 'DEFTIMESTAMP': $fdefts = $v; break;
|
|
|
746 |
case 'CONSTRAINT': $fconstraint = $v; break;
|
|
|
747 |
// let INDEX keyword create a 'very standard' index on column
|
|
|
748 |
case 'INDEX': $findex = $v; break;
|
|
|
749 |
case 'UNIQUE': $funiqueindex = true; break;
|
|
|
750 |
case 'ENUM':
|
|
|
751 |
$fOptions['ENUM'] = $v; break;
|
|
|
752 |
} //switch
|
|
|
753 |
} // foreach $fld
|
|
|
754 |
|
|
|
755 |
//--------------------
|
|
|
756 |
// VALIDATE FIELD INFO
|
|
|
757 |
if (!strlen($fname)) {
|
|
|
758 |
if ($this->debug) ADOConnection::outp("Undefined NAME");
|
|
|
759 |
return false;
|
|
|
760 |
}
|
|
|
761 |
|
|
|
762 |
$fid = strtoupper(preg_replace('/^`(.+)`$/', '$1', $fname));
|
|
|
763 |
$fname = $this->nameQuote($fname);
|
|
|
764 |
|
|
|
765 |
if (!strlen($ftype)) {
|
|
|
766 |
if ($this->debug) ADOConnection::outp("Undefined TYPE for field '$fname'");
|
|
|
767 |
return false;
|
|
|
768 |
} else {
|
|
|
769 |
$ftype = strtoupper($ftype);
|
|
|
770 |
}
|
|
|
771 |
|
|
|
772 |
$ftype = $this->_getSize($ftype, $ty, $fsize, $fprec, $fOptions);
|
|
|
773 |
|
|
|
774 |
if (($ty == 'X' || $ty == 'X2' || $ty == 'XL' || $ty == 'B') && !$this->blobAllowsNotNull)
|
|
|
775 |
/*
|
|
|
776 |
* some blob types do not accept nulls, so we override the
|
|
|
777 |
* previously defined value
|
|
|
778 |
*/
|
|
|
779 |
$fnotnull = false;
|
|
|
780 |
|
|
|
781 |
if ($fprimary)
|
|
|
782 |
$pkey[] = $fname;
|
|
|
783 |
|
|
|
784 |
if (($ty == 'X' || $ty == 'X2' || $ty == 'XL' || $ty == 'B') && !$this->blobAllowsDefaultValue)
|
|
|
785 |
/*
|
|
|
786 |
* some databases do not allow blobs to have defaults, so we
|
|
|
787 |
* override the previously defined value
|
|
|
788 |
*/
|
|
|
789 |
$fdefault = false;
|
|
|
790 |
|
|
|
791 |
// build list of indexes
|
|
|
792 |
if ($findex != '') {
|
|
|
793 |
if (array_key_exists($findex, $idxs)) {
|
|
|
794 |
$idxs[$findex]['cols'][] = ($fname);
|
|
|
795 |
if (in_array('UNIQUE', $idxs[$findex]['opts']) != $funiqueindex) {
|
|
|
796 |
if ($this->debug) ADOConnection::outp("Index $findex defined once UNIQUE and once not");
|
|
|
797 |
}
|
|
|
798 |
if ($funiqueindex && !in_array('UNIQUE', $idxs[$findex]['opts']))
|
|
|
799 |
$idxs[$findex]['opts'][] = 'UNIQUE';
|
|
|
800 |
}
|
|
|
801 |
else
|
|
|
802 |
{
|
|
|
803 |
$idxs[$findex] = array();
|
|
|
804 |
$idxs[$findex]['cols'] = array($fname);
|
|
|
805 |
if ($funiqueindex)
|
|
|
806 |
$idxs[$findex]['opts'] = array('UNIQUE');
|
|
|
807 |
else
|
|
|
808 |
$idxs[$findex]['opts'] = array();
|
|
|
809 |
}
|
|
|
810 |
}
|
|
|
811 |
|
|
|
812 |
//--------------------
|
|
|
813 |
// CONSTRUCT FIELD SQL
|
|
|
814 |
if ($fdefts) {
|
|
|
815 |
if (substr($this->connection->databaseType,0,5) == 'mysql') {
|
|
|
816 |
$ftype = 'TIMESTAMP';
|
|
|
817 |
} else {
|
|
|
818 |
$fdefault = $this->connection->sysTimeStamp;
|
|
|
819 |
}
|
|
|
820 |
} else if ($fdefdate) {
|
|
|
821 |
if (substr($this->connection->databaseType,0,5) == 'mysql') {
|
|
|
822 |
$ftype = 'TIMESTAMP';
|
|
|
823 |
} else {
|
|
|
824 |
$fdefault = $this->connection->sysDate;
|
|
|
825 |
}
|
|
|
826 |
} else if ($fdefault !== false && !$fnoquote) {
|
|
|
827 |
if ($ty == 'C' or $ty == 'X' or
|
|
|
828 |
( substr($fdefault,0,1) != "'" && !is_numeric($fdefault))) {
|
|
|
829 |
|
|
|
830 |
if (($ty == 'D' || $ty == 'T') && strtolower($fdefault) != 'null') {
|
|
|
831 |
// convert default date into database-aware code
|
|
|
832 |
if ($ty == 'T')
|
|
|
833 |
{
|
|
|
834 |
$fdefault = $this->connection->dbTimeStamp($fdefault);
|
|
|
835 |
}
|
|
|
836 |
else
|
|
|
837 |
{
|
|
|
838 |
$fdefault = $this->connection->dbDate($fdefault);
|
|
|
839 |
}
|
|
|
840 |
}
|
|
|
841 |
else
|
|
|
842 |
if (strlen($fdefault) != 1 && substr($fdefault,0,1) == ' ' && substr($fdefault,strlen($fdefault)-1) == ' ')
|
|
|
843 |
$fdefault = trim($fdefault);
|
|
|
844 |
else if (strtolower($fdefault) != 'null')
|
|
|
845 |
$fdefault = $this->connection->qstr($fdefault);
|
|
|
846 |
}
|
|
|
847 |
}
|
|
|
848 |
$suffix = $this->_createSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned);
|
|
|
849 |
|
|
|
850 |
// add index creation
|
|
|
851 |
if ($widespacing) $fname = str_pad($fname,24);
|
|
|
852 |
|
|
|
853 |
// check for field names appearing twice
|
|
|
854 |
if (array_key_exists($fid, $lines)) {
|
|
|
855 |
ADOConnection::outp("Field '$fname' defined twice");
|
|
|
856 |
}
|
|
|
857 |
|
|
|
858 |
$lines[$fid] = $fname.' '.$ftype.$suffix;
|
|
|
859 |
|
|
|
860 |
if ($fautoinc) $this->autoIncrement = true;
|
|
|
861 |
} // foreach $flds
|
|
|
862 |
|
|
|
863 |
return array($lines,$pkey,$idxs);
|
|
|
864 |
}
|
|
|
865 |
|
|
|
866 |
/**
|
|
|
867 |
GENERATE THE SIZE PART OF THE DATATYPE
|
|
|
868 |
$ftype is the actual type
|
|
|
869 |
$ty is the type defined originally in the DDL
|
|
|
870 |
*/
|
|
|
871 |
function _getSize($ftype, $ty, $fsize, $fprec, $options=false)
|
|
|
872 |
{
|
|
|
873 |
if (strlen($fsize) && $ty != 'X' && $ty != 'B' && strpos($ftype,'(') === false) {
|
|
|
874 |
$ftype .= "(".$fsize;
|
|
|
875 |
if (strlen($fprec)) $ftype .= ",".$fprec;
|
|
|
876 |
$ftype .= ')';
|
|
|
877 |
}
|
|
|
878 |
|
|
|
879 |
/*
|
|
|
880 |
* Handle additional options
|
|
|
881 |
*/
|
|
|
882 |
if (is_array($options))
|
|
|
883 |
{
|
|
|
884 |
foreach($options as $type=>$value)
|
|
|
885 |
{
|
|
|
886 |
switch ($type)
|
|
|
887 |
{
|
|
|
888 |
case 'ENUM':
|
|
|
889 |
$ftype .= '(' . $value . ')';
|
|
|
890 |
break;
|
|
|
891 |
|
|
|
892 |
default:
|
|
|
893 |
}
|
|
|
894 |
}
|
|
|
895 |
}
|
|
|
896 |
|
|
|
897 |
return $ftype;
|
|
|
898 |
}
|
|
|
899 |
|
|
|
900 |
|
|
|
901 |
// return string must begin with space
|
|
|
902 |
function _createSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
|
|
|
903 |
{
|
|
|
904 |
$suffix = '';
|
|
|
905 |
if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
|
|
|
906 |
if ($fnotnull) $suffix .= ' NOT NULL';
|
|
|
907 |
if ($fconstraint) $suffix .= ' '.$fconstraint;
|
|
|
908 |
return $suffix;
|
|
|
909 |
}
|
|
|
910 |
|
|
|
911 |
function _indexSQL($idxname, $tabname, $flds, $idxoptions)
|
|
|
912 |
{
|
|
|
913 |
$sql = array();
|
|
|
914 |
|
|
|
915 |
if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
|
|
|
916 |
$sql[] = sprintf ($this->dropIndex, $idxname);
|
|
|
917 |
if ( isset($idxoptions['DROP']) )
|
|
|
918 |
return $sql;
|
|
|
919 |
}
|
|
|
920 |
|
|
|
921 |
if ( empty ($flds) ) {
|
|
|
922 |
return $sql;
|
|
|
923 |
}
|
|
|
924 |
|
|
|
925 |
$unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
|
|
|
926 |
|
|
|
927 |
$s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
|
|
|
928 |
|
|
|
929 |
if ( isset($idxoptions[$this->upperName]) )
|
|
|
930 |
$s .= $idxoptions[$this->upperName];
|
|
|
931 |
|
|
|
932 |
if ( is_array($flds) )
|
|
|
933 |
$flds = implode(', ',$flds);
|
|
|
934 |
$s .= '(' . $flds . ')';
|
|
|
935 |
$sql[] = $s;
|
|
|
936 |
|
|
|
937 |
return $sql;
|
|
|
938 |
}
|
|
|
939 |
|
|
|
940 |
function _dropAutoIncrement($tabname)
|
|
|
941 |
{
|
|
|
942 |
return false;
|
|
|
943 |
}
|
|
|
944 |
|
|
|
945 |
function _tableSQL($tabname,$lines,$pkey,$tableoptions)
|
|
|
946 |
{
|
|
|
947 |
$sql = array();
|
|
|
948 |
|
|
|
949 |
if (isset($tableoptions['REPLACE']) || isset ($tableoptions['DROP'])) {
|
|
|
950 |
$sql[] = sprintf($this->dropTable,$tabname);
|
|
|
951 |
if ($this->autoIncrement) {
|
|
|
952 |
$sInc = $this->_dropAutoIncrement($tabname);
|
|
|
953 |
if ($sInc) $sql[] = $sInc;
|
|
|
954 |
}
|
|
|
955 |
if ( isset ($tableoptions['DROP']) ) {
|
|
|
956 |
return $sql;
|
|
|
957 |
}
|
|
|
958 |
}
|
|
|
959 |
|
|
|
960 |
$s = "CREATE TABLE $tabname (\n";
|
|
|
961 |
$s .= implode(",\n", $lines);
|
|
|
962 |
if (sizeof($pkey)>0) {
|
|
|
963 |
$s .= ",\n PRIMARY KEY (";
|
|
|
964 |
$s .= implode(", ",$pkey).")";
|
|
|
965 |
}
|
|
|
966 |
if (isset($tableoptions['CONSTRAINTS']))
|
|
|
967 |
$s .= "\n".$tableoptions['CONSTRAINTS'];
|
|
|
968 |
|
|
|
969 |
if (isset($tableoptions[$this->upperName.'_CONSTRAINTS']))
|
|
|
970 |
$s .= "\n".$tableoptions[$this->upperName.'_CONSTRAINTS'];
|
|
|
971 |
|
|
|
972 |
$s .= "\n)";
|
|
|
973 |
if (isset($tableoptions[$this->upperName])) $s .= $tableoptions[$this->upperName];
|
|
|
974 |
$sql[] = $s;
|
|
|
975 |
|
|
|
976 |
return $sql;
|
|
|
977 |
}
|
|
|
978 |
|
|
|
979 |
/**
|
|
|
980 |
GENERATE TRIGGERS IF NEEDED
|
|
|
981 |
used when table has auto-incrementing field that is emulated using triggers
|
|
|
982 |
*/
|
|
|
983 |
function _triggers($tabname,$taboptions)
|
|
|
984 |
{
|
|
|
985 |
return array();
|
|
|
986 |
}
|
|
|
987 |
|
|
|
988 |
/**
|
|
|
989 |
Sanitize options, so that array elements with no keys are promoted to keys
|
|
|
990 |
*/
|
|
|
991 |
function _options($opts)
|
|
|
992 |
{
|
|
|
993 |
if (!is_array($opts)) return array();
|
|
|
994 |
$newopts = array();
|
|
|
995 |
foreach($opts as $k => $v) {
|
|
|
996 |
if (is_numeric($k)) $newopts[strtoupper($v)] = $v;
|
|
|
997 |
else $newopts[strtoupper($k)] = $v;
|
|
|
998 |
}
|
|
|
999 |
return $newopts;
|
|
|
1000 |
}
|
|
|
1001 |
|
|
|
1002 |
|
|
|
1003 |
function _getSizePrec($size)
|
|
|
1004 |
{
|
|
|
1005 |
$fsize = false;
|
|
|
1006 |
$fprec = false;
|
|
|
1007 |
$dotat = strpos($size,'.');
|
|
|
1008 |
if ($dotat === false) $dotat = strpos($size,',');
|
|
|
1009 |
if ($dotat === false) $fsize = $size;
|
|
|
1010 |
else {
|
|
|
1011 |
$fsize = substr($size,0,$dotat);
|
|
|
1012 |
$fprec = substr($size,$dotat+1);
|
|
|
1013 |
}
|
|
|
1014 |
return array($fsize, $fprec);
|
|
|
1015 |
}
|
|
|
1016 |
|
|
|
1017 |
/**
|
|
|
1018 |
* This function changes/adds new fields to your table.
|
|
|
1019 |
*
|
|
|
1020 |
* You don't have to know if the col is new or not. It will check on its own.
|
|
|
1021 |
*
|
|
|
1022 |
* @param string $tablename
|
|
|
1023 |
* @param string $flds
|
|
|
1024 |
* @param string[] $tableoptions
|
|
|
1025 |
* @param bool $dropOldFlds
|
|
|
1026 |
*
|
|
|
1027 |
* @return string[] Array of SQL Commands
|
|
|
1028 |
*/
|
|
|
1029 |
function changeTableSQL($tablename, $flds, $tableoptions = false, $dropOldFlds=false)
|
|
|
1030 |
{
|
|
|
1031 |
global $ADODB_FETCH_MODE;
|
|
|
1032 |
$save = $ADODB_FETCH_MODE;
|
|
|
1033 |
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
|
|
|
1034 |
if ($this->connection->fetchMode !== false) $savem = $this->connection->setFetchMode(false);
|
|
|
1035 |
|
|
|
1036 |
// check table exists
|
|
|
1037 |
$save_handler = $this->connection->raiseErrorFn;
|
|
|
1038 |
$this->connection->raiseErrorFn = '';
|
|
|
1039 |
$cols = $this->metaColumns($tablename);
|
|
|
1040 |
$this->connection->raiseErrorFn = $save_handler;
|
|
|
1041 |
|
|
|
1042 |
if (isset($savem)) $this->connection->setFetchMode($savem);
|
|
|
1043 |
$ADODB_FETCH_MODE = $save;
|
|
|
1044 |
|
|
|
1045 |
if ( empty($cols)) {
|
|
|
1046 |
return $this->createTableSQL($tablename, $flds, $tableoptions);
|
|
|
1047 |
}
|
|
|
1048 |
|
| 1441 |
ariadna |
1049 |
$sql = [];
|
| 1 |
efrain |
1050 |
if (is_array($flds)) {
|
|
|
1051 |
// Cycle through the update fields, comparing
|
|
|
1052 |
// existing fields to fields to update.
|
|
|
1053 |
// if the Metatype and size is exactly the
|
|
|
1054 |
// same, ignore - by Mark Newham
|
|
|
1055 |
$holdflds = array();
|
| 1441 |
ariadna |
1056 |
$fields_to_add = [];
|
|
|
1057 |
$fields_to_alter = [];
|
| 1 |
efrain |
1058 |
foreach($flds as $k=>$v) {
|
|
|
1059 |
if ( isset($cols[$k]) && is_object($cols[$k]) ) {
|
|
|
1060 |
// If already not allowing nulls, then don't change
|
|
|
1061 |
$obj = $cols[$k];
|
|
|
1062 |
if (isset($obj->not_null) && $obj->not_null)
|
|
|
1063 |
$v = str_replace('NOT NULL','',$v);
|
|
|
1064 |
if (isset($obj->auto_increment) && $obj->auto_increment && empty($v['AUTOINCREMENT']))
|
|
|
1065 |
$v = str_replace('AUTOINCREMENT','',$v);
|
|
|
1066 |
|
|
|
1067 |
$c = $cols[$k];
|
|
|
1068 |
$ml = $c->max_length;
|
|
|
1069 |
$mt = $this->metaType($c->type,$ml);
|
|
|
1070 |
|
|
|
1071 |
if (isset($c->scale)) $sc = $c->scale;
|
|
|
1072 |
else $sc = 99; // always force change if scale not known.
|
|
|
1073 |
|
|
|
1074 |
if ($sc == -1) $sc = false;
|
|
|
1075 |
list($fsize, $fprec) = $this->_getSizePrec($v['SIZE']);
|
|
|
1076 |
|
|
|
1077 |
if ($ml == -1) $ml = '';
|
|
|
1078 |
if ($mt == 'X') $ml = $v['SIZE'];
|
|
|
1079 |
if (($mt != $v['TYPE']) || ($ml != $fsize || $sc != $fprec) || (isset($v['AUTOINCREMENT']) && $v['AUTOINCREMENT'] != $obj->auto_increment)) {
|
|
|
1080 |
$holdflds[$k] = $v;
|
| 1441 |
ariadna |
1081 |
$fields_to_alter[$k] = $v;
|
| 1 |
efrain |
1082 |
}
|
|
|
1083 |
} else {
|
| 1441 |
ariadna |
1084 |
$fields_to_add[$k] = $v;
|
| 1 |
efrain |
1085 |
$holdflds[$k] = $v;
|
|
|
1086 |
}
|
|
|
1087 |
}
|
|
|
1088 |
$flds = $holdflds;
|
| 1441 |
ariadna |
1089 |
|
|
|
1090 |
$sql = array_merge(
|
|
|
1091 |
$this->addColumnSQL($tablename, $fields_to_add),
|
|
|
1092 |
$this->alterColumnSql($tablename, $fields_to_alter)
|
|
|
1093 |
);
|
| 1 |
efrain |
1094 |
}
|
|
|
1095 |
|
|
|
1096 |
if ($dropOldFlds) {
|
|
|
1097 |
foreach ($cols as $id => $v) {
|
|
|
1098 |
if (!isset($lines[$id])) {
|
|
|
1099 |
$sql[] = $this->dropColumnSQL($tablename, $flds);
|
|
|
1100 |
}
|
|
|
1101 |
}
|
|
|
1102 |
}
|
|
|
1103 |
return $sql;
|
|
|
1104 |
}
|
|
|
1105 |
} // class
|