4 V4.20 22 Feb 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.
5 Released under both BSD license and Lesser GPL library license.
6 Whenever there is any discrepancy between the two licenses,
7 the BSD license will take precedence.
9 Set tabs to 4 for best viewing.
13 See adodb/tests/test-datadict.php for docs and examples.
17 Test script for parser
19 function Lens_ParseTest()
21 $str = "`zcol ACOL` NUMBER(32,2) DEFAULT 'The \"cow\" (and Jim''s dog) jumps over the moon' PRIMARY, INTI INT AUTO DEFAULT 0";
23 $a= Lens_ParseArgs($str);
29 if (!function_exists('ctype_alnum')) {
30 function ctype_alnum($text) {
31 return preg_match('/^[a-z0-9]*$/i', $text);
38 Parse arguments, treat "text" (text) and 'text' as quotation marks.
39 To escape, use "" or '' or ))
41 Will read in "abc def" sans quotes, as: abc def
43 However if `abc def`, then will read in as `abc def`
45 @param endstmtchar Character that indicates end of statement
46 @param tokenchars Include the following characters in tokens apart from A-Z and 0-9
47 @returns 2 dimensional array containing parsed tokens.
49 function Lens_ParseArgs($args,$endstmtchar=',',$tokenchars='_.-')
56 $tokens[$stmtno] = array();
61 $ch = substr($args,$pos,1);
70 $tokens[$stmtno][] = implode('',$tokarr);
79 if ($intoken) $tokarr[] = $ch;
86 if (empty($endquote)) {
87 $tokens[$stmtno][] = implode('',$tokarr);
88 if ($ch == '(') $endquote = ')';
93 } else if ($endquote == $ch) {
94 $ch2 = substr($args,$pos+
1,1);
95 if ($ch2 == $endquote) {
101 $tokens[$stmtno][] = implode('',$tokarr);
109 if ($ch == '(') $endquote = ')';
110 else $endquote = $ch;
114 if ($ch == '`') $tokarr[] = '`';
121 if ($ch == $endstmtchar) {
123 $tokens[$stmtno] = array();
134 if ($quoted) $tokarr[] = $ch;
135 else if (ctype_alnum($ch) ||
strpos($tokenchars,$ch) !== false) $tokarr[] = $ch;
137 if ($ch == $endstmtchar) {
138 $tokens[$stmtno][] = implode('',$tokarr);
140 $tokens[$stmtno] = array();
145 $tokens[$stmtno][] = implode('',$tokarr);
146 $tokens[$stmtno][] = $ch;
157 class ADODB_DataDict
{
160 var $dropTable = 'DROP TABLE %s';
161 var $dropIndex = 'DROP INDEX %s';
162 var $addCol = ' ADD';
163 var $alterCol = ' ALTER COLUMN';
164 var $dropCol = ' DROP COLUMN';
165 var $nameRegex = '\w';
167 var $serverInfo = array();
168 var $autoIncrement = false;
170 var $blobSize = 100; /// any varchar/char field this size or greater is treated as a blob
171 /// in other words, we use a text area for editting.
173 function GetCommentSQL($table,$col)
178 function SetCommentSQL($table,$col,$cmt)
183 function &MetaTables()
185 return $this->connection
->MetaTables();
188 function &MetaColumns($tab, $upper=true, $schema=false)
190 return $this->connection
->MetaColumns($this->TableName($tab), $upper, $schema);
193 function &MetaPrimaryKeys($tab,$owner=false,$intkey=false)
195 return $this->connection
->MetaPrimaryKeys($this->TableName($tab), $owner, $intkey);
198 function &MetaIndexes($table, $primary = false, $owner = false)
200 return $this->connection
->MetaIndexes($this->TableName($table), $primary, $owner);
203 function MetaType($t,$len=-1,$fieldobj=false)
205 return ADORecordSet
::MetaType($t,$len,$fieldobj);
208 function NameQuote($name = NULL)
210 if (!is_string($name)) {
216 if ( !is_object($this->connection
) ) {
220 $quote = $this->connection
->nameQuote
;
222 // if name is of the form `name`, quote it
223 if ( preg_match('/^`(.+)`$/', $name, $matches) ) {
224 return $quote . $matches[1] . $quote;
227 // if name contains special characters, quote it
228 if ( !preg_match('/^[' . $this->nameRegex
. ']+$/', $name) ) {
229 return $quote . $name . $quote;
235 function TableName($name)
237 if ( $this->schema
) {
238 return $this->NameQuote($this->schema
) .'.'. $this->NameQuote($name);
240 return $this->NameQuote($name);
243 // Executes the sql array returned by GetTableSQL and GetIndexSQL
244 function ExecuteSQLArray($sql, $continueOnError = true)
247 $conn = &$this->connection
;
248 $saved = $conn->debug
;
249 foreach($sql as $line) {
251 if ($this->debug
) $conn->debug
= true;
252 $ok = $conn->Execute($line);
253 $conn->debug
= $saved;
255 if ($this->debug
) ADOConnection
::outp($conn->ErrorMsg());
256 if (!$continueOnError) return 0;
264 Returns the actual type given a character code.
267 X: CLOB (character large object) or largest varchar size if CLOB is not supported
268 C2: Multibyte varchar
271 B: BLOB (binary large object)
275 L: Integer field suitable for storing booleans (0 or 1)
277 F: Floating point number
278 N: Numeric or decimal number
281 function ActualType($meta)
286 function CreateDatabase($dbname,$options=false)
288 $options = $this->_Options($options);
291 $s = 'CREATE DATABASE ' . $this->NameQuote($dbname);
292 if (isset($options[$this->upperName
]))
293 $s .= ' '.$options[$this->upperName
];
300 Generates the SQL to create index. Returns an array of sql strings.
302 function CreateIndexSQL($idxname, $tabname, $flds, $idxoptions = false)
304 if (!is_array($flds)) {
305 $flds = explode(',',$flds);
308 foreach($flds as $key => $fld) {
309 $flds[$key] = $this->NameQuote($fld);
312 return $this->_IndexSQL($this->NameQuote($idxname), $this->TableName($tabname), $flds, $this->_Options($idxoptions));
315 function DropIndexSQL ($idxname, $tabname = NULL)
317 return array(sprintf($this->dropIndex
, $this->NameQuote($idxname), $this->TableName($tabname)));
320 function SetSchema($schema)
322 $this->schema
= $schema;
325 function AddColumnSQL($tabname, $flds)
327 $tabname = $this->TableName ($tabname);
329 list($lines,$pkey) = $this->_GenFields($flds);
330 $alter = 'ALTER TABLE ' . $tabname . $this->addCol
. ' ';
331 foreach($lines as $v) {
332 $sql[] = $alter . $v;
337 function AlterColumnSQL($tabname, $flds)
339 $tabname = $this->TableName ($tabname);
341 list($lines,$pkey) = $this->_GenFields($flds);
342 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol
. ' ';
343 foreach($lines as $v) {
344 $sql[] = $alter . $v;
349 function DropColumnSQL($tabname, $flds)
351 $tabname = $this->TableName ($tabname);
352 if (!is_array($flds)) $flds = explode(',',$flds);
354 $alter = 'ALTER TABLE ' . $tabname . $this->dropCol
. ' ';
355 foreach($flds as $v) {
356 $sql[] = $alter . $this->NameQuote($v);
361 function DropTableSQL($tabname)
363 return array (sprintf($this->dropTable
, $this->TableName($tabname)));
367 Generate the SQL to create table. Returns an array of sql strings.
369 function CreateTableSQL($tabname, $flds, $tableoptions=false)
371 if (!$tableoptions) $tableoptions = array();
373 list($lines,$pkey) = $this->_GenFields($flds);
375 $taboptions = $this->_Options($tableoptions);
376 $tabname = $this->TableName ($tabname);
377 $sql = $this->_TableSQL($tabname,$lines,$pkey,$taboptions);
379 $tsql = $this->_Triggers($tabname,$taboptions);
380 foreach($tsql as $s) $sql[] = $s;
385 function _GenFields($flds)
387 if (is_string($flds)) {
389 $txt = $flds.$padding;
391 $flds0 = Lens_ParseArgs($txt,',');
393 foreach($flds0 as $f0) {
395 foreach($f0 as $token) {
396 switch (strtoupper($token)) {
402 if ($hasparam) $f1[$hasparam] = $token;
412 $this->autoIncrement
= false;
415 foreach($flds as $fld) {
416 $fld = _array_change_key_case($fld);
428 $fconstraint = false;
434 foreach($fld as $attr => $v) {
435 if ($attr == 2 && is_numeric($v)) $attr = 'SIZE';
436 else if (is_numeric($attr) && $attr > 1 && !is_numeric($v)) $attr = strtoupper($v);
440 case 'NAME': $fname = $v; break;
442 case 'TYPE': $ty = $v; $ftype = $this->ActualType(strtoupper($v)); break;
445 $dotat = strpos($v,'.'); if ($dotat === false) $dotat = strpos($v,',');
446 if ($dotat === false) $fsize = $v;
448 $fsize = substr($v,0,$dotat);
449 $fprec = substr($v,$dotat+
1);
452 case 'UNSIGNED': $funsigned = true; break;
453 case 'AUTOINCREMENT':
454 case 'AUTO': $fautoinc = true; $fnotnull = true; break;
456 case 'PRIMARY': $fprimary = $v; $fnotnull = true; break;
458 case 'DEFAULT': $fdefault = $v; break;
459 case 'NOTNULL': $fnotnull = $v; break;
460 case 'NOQUOTE': $fnoquote = $v; break;
461 case 'DEFDATE': $fdefdate = $v; break;
462 case 'DEFTIMESTAMP': $fdefts = $v; break;
463 case 'CONSTRAINT': $fconstraint = $v; break;
467 //--------------------
468 // VALIDATE FIELD INFO
469 if (!strlen($fname)) {
470 if ($this->debug
) ADOConnection
::outp("Undefined NAME");
474 $fid = strtoupper(preg_replace('/^`(.+)`$/', '$1', $fname));
475 $fname = $this->NameQuote($fname);
477 if (!strlen($ftype)) {
478 if ($this->debug
) ADOConnection
::outp("Undefined TYPE for field '$fname'");
481 $ftype = strtoupper($ftype);
484 $ftype = $this->_GetSize($ftype, $ty, $fsize, $fprec);
486 if ($ty == 'X' ||
$ty == 'X2' ||
$ty == 'B') $fnotnull = false; // some blob types do not accept nulls
488 if ($fprimary) $pkey[] = $fname;
490 // some databases do not allow blobs to have defaults
491 if ($ty == 'X') $fdefault = false;
493 //--------------------
494 // CONSTRUCT FIELD SQL
496 if (substr($this->connection
->databaseType
,0,5) == 'mysql') {
497 $ftype = 'TIMESTAMP';
499 $fdefault = $this->connection
->sysTimeStamp
;
501 } else if ($fdefdate) {
502 if (substr($this->connection
->databaseType
,0,5) == 'mysql') {
503 $ftype = 'TIMESTAMP';
505 $fdefault = $this->connection
->sysDate
;
507 } else if (strlen($fdefault) && !$fnoquote)
508 if ($ty == 'C' or $ty == 'X' or
509 ( substr($fdefault,0,1) != "'" && !is_numeric($fdefault)))
510 if (strlen($fdefault) != 1 && substr($fdefault,0,1) == ' ' && substr($fdefault,strlen($fdefault)-1) == ' ')
511 $fdefault = trim($fdefault);
512 else if (strtolower($fdefault) != 'null')
513 $fdefault = $this->connection
->qstr($fdefault);
514 $suffix = $this->_CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned);
516 $fname = str_pad($fname,16);
517 $lines[$fid] = $fname.' '.$ftype.$suffix;
519 if ($fautoinc) $this->autoIncrement
= true;
522 return array($lines,$pkey);
525 GENERATE THE SIZE PART OF THE DATATYPE
526 $ftype is the actual type
527 $ty is the type defined originally in the DDL
529 function _GetSize($ftype, $ty, $fsize, $fprec)
531 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && strpos($ftype,'(') === false) {
532 $ftype .= "(".$fsize;
533 if (strlen($fprec)) $ftype .= ",".$fprec;
540 // return string must begin with space
541 function _CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint)
544 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
545 if ($fnotnull) $suffix .= ' NOT NULL';
546 if ($fconstraint) $suffix .= ' '.$fconstraint;
550 function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
554 if ( isset($idxoptions['REPLACE']) ||
isset($idxoptions['DROP']) ) {
555 $sql[] = sprintf ($this->dropIndex
, $idxname);
556 if ( isset($idxoptions['DROP']) )
560 if ( empty ($flds) ) {
564 $unique = isset($idxoptions['UNIQUE']) ?
' UNIQUE' : '';
566 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
568 if ( isset($idxoptions[$this->upperName
]) )
569 $s .= $idxoptions[$this->upperName
];
571 if ( is_array($flds) )
572 $flds = implode(', ',$flds);
573 $s .= '(' . $flds . ')';
579 function _DropAutoIncrement($tabname)
584 function _TableSQL($tabname,$lines,$pkey,$tableoptions)
588 if (isset($tableoptions['REPLACE']) ||
isset ($tableoptions['DROP'])) {
589 $sql[] = sprintf($this->dropTable
,$tabname);
590 if ($this->autoIncrement
) {
591 $sInc = $this->_DropAutoIncrement($tabname);
592 if ($sInc) $sql[] = $sInc;
594 if ( isset ($tableoptions['DROP']) ) {
598 $s = "CREATE TABLE $tabname (\n";
599 $s .= implode(",\n", $lines);
600 if (sizeof($pkey)>0) {
601 $s .= ",\n PRIMARY KEY (";
602 $s .= implode(", ",$pkey).")";
604 if (isset($tableoptions['CONSTRAINTS']))
605 $s .= "\n".$tableoptions['CONSTRAINTS'];
607 if (isset($tableoptions[$this->upperName
.'_CONSTRAINTS']))
608 $s .= "\n".$tableoptions[$this->upperName
.'_CONSTRAINTS'];
611 if (isset($tableoptions[$this->upperName
])) $s .= $tableoptions[$this->upperName
];
618 GENERATE TRIGGERS IF NEEDED
619 used when table has auto-incrementing field that is emulated using triggers
621 function _Triggers($tabname,$taboptions)
627 Sanitize options, so that array elements with no keys are promoted to keys
629 function _Options($opts)
631 if (!is_array($opts)) return array();
633 foreach($opts as $k => $v) {
634 if (is_numeric($k)) $newopts[strtoupper($v)] = $v;
635 else $newopts[strtoupper($k)] = $v;
641 "Florian Buzin [ easywe ]" <florian.buzin@easywe.de>
643 This function changes/adds new fields to your table. You don't
644 have to know if the col is new or not. It will check on its own.
646 function ChangeTableSQL($tablename, $flds, $tableoptions = false)
648 if ( !is_array($cols = &$this->MetaColumns($tablename)) ) {
649 return $this->CreateTableSQL($tablename, $flds, $tableoptions);
652 list($lines,$pkey) = $this->_GenFields($flds);
653 $alter = 'ALTER TABLE ' . $this->TableName($tablename);
656 foreach ( $lines as $id => $v ) {
657 if ( isset($cols[$id]) && is_object($cols[$id]) ) {
658 $sql[] = $alter . $this->alterCol
. ' ' . $v;
660 $sql[] = $alter . $this->addCol
. ' ' . $v;