4 V4.92a 29 Aug 2006 (c) 2000-2006 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
20 // security - hide paths
21 if (!defined('ADODB_DIR')) die();
23 function Lens_ParseTest()
25 $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";
27 $a= Lens_ParseArgs($str);
34 if (!function_exists('ctype_alnum')) {
35 function ctype_alnum($text) {
36 return preg_match('/^[a-z0-9]*$/i', $text);
43 Parse arguments, treat "text" (text) and 'text' as quotation marks.
44 To escape, use "" or '' or ))
46 Will read in "abc def" sans quotes, as: abc def
48 However if `abc def`, then will read in as `abc def`
50 @param endstmtchar Character that indicates end of statement
51 @param tokenchars Include the following characters in tokens apart from A-Z and 0-9
52 @returns 2 dimensional array containing parsed tokens.
54 function Lens_ParseArgs($args,$endstmtchar=',',$tokenchars='_.-')
61 $tokens[$stmtno] = array();
67 $ch = substr($args,$pos,1);
76 $tokens[$stmtno][] = implode('',$tokarr);
85 if ($intoken) $tokarr[] = $ch;
92 if (empty($endquote)) {
93 $tokens[$stmtno][] = implode('',$tokarr);
94 if ($ch == '(') $endquote = ')';
99 } else if ($endquote == $ch) {
100 $ch2 = substr($args,$pos+
1,1);
101 if ($ch2 == $endquote) {
107 $tokens[$stmtno][] = implode('',$tokarr);
115 if ($ch == '(') $endquote = ')';
116 else $endquote = $ch;
120 if ($ch == '`') $tokarr[] = '`';
127 if ($ch == $endstmtchar) {
129 $tokens[$stmtno] = array();
140 if ($quoted) $tokarr[] = $ch;
141 else if (ctype_alnum($ch) ||
strpos($tokenchars,$ch) !== false) $tokarr[] = $ch;
143 if ($ch == $endstmtchar) {
144 $tokens[$stmtno][] = implode('',$tokarr);
146 $tokens[$stmtno] = array();
151 $tokens[$stmtno][] = implode('',$tokarr);
152 $tokens[$stmtno][] = $ch;
158 if ($intoken) $tokens[$stmtno][] = implode('',$tokarr);
164 class ADODB_DataDict
{
167 var $dropTable = 'DROP TABLE %s';
168 var $renameTable = 'RENAME TABLE %s TO %s';
169 var $dropIndex = 'DROP INDEX %s';
170 var $addCol = ' ADD';
171 var $alterCol = ' ALTER COLUMN';
172 var $dropCol = ' DROP COLUMN';
173 var $renameColumn = 'ALTER TABLE %s RENAME COLUMN %s TO %s'; // table, old-column, new-column, column-definitions (not used by default)
174 var $nameRegex = '\w';
175 var $nameRegexBrackets = 'a-zA-Z0-9_\(\)';
177 var $serverInfo = array();
178 var $autoIncrement = false;
180 var $invalidResizeTypes4 = array('CLOB','BLOB','TEXT','DATE','TIME'); // for changetablesql
181 var $blobSize = 100; /// any varchar/char field this size or greater is treated as a blob
182 /// in other words, we use a text area for editting.
184 function GetCommentSQL($table,$col)
189 function SetCommentSQL($table,$col,$cmt)
194 function MetaTables()
196 if (!$this->connection
->IsConnected()) return array();
197 return $this->connection
->MetaTables();
200 function MetaColumns($tab, $upper=true, $schema=false)
202 if (!$this->connection
->IsConnected()) return array();
203 return $this->connection
->MetaColumns($this->TableName($tab), $upper, $schema);
206 function MetaPrimaryKeys($tab,$owner=false,$intkey=false)
208 if (!$this->connection
->IsConnected()) return array();
209 return $this->connection
->MetaPrimaryKeys($this->TableName($tab), $owner, $intkey);
212 function MetaIndexes($table, $primary = false, $owner = false)
214 if (!$this->connection
->IsConnected()) return array();
215 return $this->connection
->MetaIndexes($this->TableName($table), $primary, $owner);
218 function MetaType($t,$len=-1,$fieldobj=false)
220 return ADORecordSet
::MetaType($t,$len,$fieldobj);
223 function NameQuote($name = NULL,$allowBrackets=false)
225 if (!is_string($name)) {
231 if ( !is_object($this->connection
) ) {
235 $quote = $this->connection
->nameQuote
;
237 // if name is of the form `name`, quote it
238 if ( preg_match('/^`(.+)`$/', $name, $matches) ) {
239 return $quote . $matches[1] . $quote;
242 // if name contains special characters, quote it
243 $regex = ($allowBrackets) ?
$this->nameRegexBrackets
: $this->nameRegex
;
245 if ( !preg_match('/^[' . $regex . ']+$/', $name) ) {
246 return $quote . $name . $quote;
252 function TableName($name)
254 if ( $this->schema
) {
255 return $this->NameQuote($this->schema
) .'.'. $this->NameQuote($name);
257 return $this->NameQuote($name);
260 // Executes the sql array returned by GetTableSQL and GetIndexSQL
261 function ExecuteSQLArray($sql, $continueOnError = true)
264 $conn = &$this->connection
;
265 $saved = $conn->debug
;
266 foreach($sql as $line) {
268 if ($this->debug
) $conn->debug
= true;
269 $ok = $conn->Execute($line);
270 $conn->debug
= $saved;
272 if ($this->debug
) ADOConnection
::outp($conn->ErrorMsg());
273 if (!$continueOnError) return 0;
281 Returns the actual type given a character code.
284 X: CLOB (character large object) or largest varchar size if CLOB is not supported
285 C2: Multibyte varchar
288 B: BLOB (binary large object)
292 L: Integer field suitable for storing booleans (0 or 1)
294 F: Floating point number
295 N: Numeric or decimal number
298 function ActualType($meta)
303 function CreateDatabase($dbname,$options=false)
305 $options = $this->_Options($options);
308 $s = 'CREATE DATABASE ' . $this->NameQuote($dbname);
309 if (isset($options[$this->upperName
]))
310 $s .= ' '.$options[$this->upperName
];
317 Generates the SQL to create index. Returns an array of sql strings.
319 function CreateIndexSQL($idxname, $tabname, $flds, $idxoptions = false)
321 if (!is_array($flds)) {
322 $flds = explode(',',$flds);
325 foreach($flds as $key => $fld) {
326 # some indexes can use partial fields, eg. index first 32 chars of "name" with NAME(32)
327 $flds[$key] = $this->NameQuote($fld,$allowBrackets=true);
330 return $this->_IndexSQL($this->NameQuote($idxname), $this->TableName($tabname), $flds, $this->_Options($idxoptions));
333 function DropIndexSQL ($idxname, $tabname = NULL)
335 return array(sprintf($this->dropIndex
, $this->NameQuote($idxname), $this->TableName($tabname)));
338 function SetSchema($schema)
340 $this->schema
= $schema;
343 function AddColumnSQL($tabname, $flds)
345 $tabname = $this->TableName ($tabname);
347 list($lines,$pkey) = $this->_GenFields($flds);
348 $alter = 'ALTER TABLE ' . $tabname . $this->addCol
. ' ';
349 foreach($lines as $v) {
350 $sql[] = $alter . $v;
356 * Change the definition of one column
358 * As some DBM's can't do that on there own, you need to supply the complete defintion of the new table,
359 * to allow, recreating the table and copying the content over to the new table
360 * @param string $tabname table-name
361 * @param string $flds column-name and type for the changed column
362 * @param string $tableflds='' complete defintion of the new table, eg. for postgres, default ''
363 * @param array/string $tableoptions='' options for the new table see CreateTableSQL, default ''
364 * @return array with SQL strings
366 function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
368 $tabname = $this->TableName ($tabname);
370 list($lines,$pkey) = $this->_GenFields($flds);
371 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol
. ' ';
372 foreach($lines as $v) {
373 $sql[] = $alter . $v;
381 * Some DBM's can only do this together with changeing the type of the column (even if that stays the same, eg. mysql)
382 * @param string $tabname table-name
383 * @param string $oldcolumn column-name to be renamed
384 * @param string $newcolumn new column-name
385 * @param string $flds='' complete column-defintion-string like for AddColumnSQL, only used by mysql atm., default=''
386 * @return array with SQL strings
388 function RenameColumnSQL($tabname,$oldcolumn,$newcolumn,$flds='')
390 $tabname = $this->TableName ($tabname);
392 list($lines,$pkey) = $this->_GenFields($flds);
393 list(,$first) = each($lines);
394 list(,$column_def) = split("[\t ]+",$first,2);
396 return array(sprintf($this->renameColumn
,$tabname,$this->NameQuote($oldcolumn),$this->NameQuote($newcolumn),$column_def));
402 * Some DBM's can't do that on there own, you need to supply the complete defintion of the new table,
403 * to allow, recreating the table and copying the content over to the new table
404 * @param string $tabname table-name
405 * @param string $flds column-name and type for the changed column
406 * @param string $tableflds='' complete defintion of the new table, eg. for postgres, default ''
407 * @param array/string $tableoptions='' options for the new table see CreateTableSQL, default ''
408 * @return array with SQL strings
410 function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
412 $tabname = $this->TableName ($tabname);
413 if (!is_array($flds)) $flds = explode(',',$flds);
415 $alter = 'ALTER TABLE ' . $tabname . $this->dropCol
. ' ';
416 foreach($flds as $v) {
417 $sql[] = $alter . $this->NameQuote($v);
422 function DropTableSQL($tabname)
424 return array (sprintf($this->dropTable
, $this->TableName($tabname)));
427 function RenameTableSQL($tabname,$newname)
429 return array (sprintf($this->renameTable
, $this->TableName($tabname),$this->TableName($newname)));
433 Generate the SQL to create table. Returns an array of sql strings.
435 function CreateTableSQL($tabname, $flds, $tableoptions=false)
437 if (!$tableoptions) $tableoptions = array();
439 list($lines,$pkey) = $this->_GenFields($flds, true);
441 $taboptions = $this->_Options($tableoptions);
442 $tabname = $this->TableName ($tabname);
443 $sql = $this->_TableSQL($tabname,$lines,$pkey,$taboptions);
445 $tsql = $this->_Triggers($tabname,$taboptions);
446 foreach($tsql as $s) $sql[] = $s;
451 function _GenFields($flds,$widespacing=false)
453 if (is_string($flds)) {
455 $txt = $flds.$padding;
457 $flds0 = Lens_ParseArgs($txt,',');
459 foreach($flds0 as $f0) {
461 foreach($f0 as $token) {
462 switch (strtoupper($token)) {
468 if ($hasparam) $f1[$hasparam] = $token;
478 $this->autoIncrement
= false;
481 foreach($flds as $fld) {
482 $fld = _array_change_key_case($fld);
494 $fconstraint = false;
500 foreach($fld as $attr => $v) {
501 if ($attr == 2 && is_numeric($v)) $attr = 'SIZE';
502 else if (is_numeric($attr) && $attr > 1 && !is_numeric($v)) $attr = strtoupper($v);
506 case 'NAME': $fname = $v; break;
508 case 'TYPE': $ty = $v; $ftype = $this->ActualType(strtoupper($v)); break;
511 $dotat = strpos($v,'.'); if ($dotat === false) $dotat = strpos($v,',');
512 if ($dotat === false) $fsize = $v;
514 $fsize = substr($v,0,$dotat);
515 $fprec = substr($v,$dotat+
1);
518 case 'UNSIGNED': $funsigned = true; break;
519 case 'AUTOINCREMENT':
520 case 'AUTO': $fautoinc = true; $fnotnull = true; break;
522 case 'PRIMARY': $fprimary = $v; $fnotnull = true; break;
524 case 'DEFAULT': $fdefault = $v; break;
525 case 'NOTNULL': $fnotnull = $v; break;
526 case 'NOQUOTE': $fnoquote = $v; break;
527 case 'DEFDATE': $fdefdate = $v; break;
528 case 'DEFTIMESTAMP': $fdefts = $v; break;
529 case 'CONSTRAINT': $fconstraint = $v; break;
533 //--------------------
534 // VALIDATE FIELD INFO
535 if (!strlen($fname)) {
536 if ($this->debug
) ADOConnection
::outp("Undefined NAME");
540 $fid = strtoupper(preg_replace('/^`(.+)`$/', '$1', $fname));
541 $fname = $this->NameQuote($fname);
543 if (!strlen($ftype)) {
544 if ($this->debug
) ADOConnection
::outp("Undefined TYPE for field '$fname'");
547 $ftype = strtoupper($ftype);
550 $ftype = $this->_GetSize($ftype, $ty, $fsize, $fprec);
552 if ($ty == 'X' ||
$ty == 'X2' ||
$ty == 'B') $fnotnull = false; // some blob types do not accept nulls
554 if ($fprimary) $pkey[] = $fname;
556 // some databases do not allow blobs to have defaults
557 if ($ty == 'X') $fdefault = false;
559 //--------------------
560 // CONSTRUCT FIELD SQL
562 if (substr($this->connection
->databaseType
,0,5) == 'mysql') {
563 $ftype = 'TIMESTAMP';
565 $fdefault = $this->connection
->sysTimeStamp
;
567 } else if ($fdefdate) {
568 if (substr($this->connection
->databaseType
,0,5) == 'mysql') {
569 $ftype = 'TIMESTAMP';
571 $fdefault = $this->connection
->sysDate
;
573 } else if ($fdefault !== false && !$fnoquote)
574 if ($ty == 'C' or $ty == 'X' or
575 ( substr($fdefault,0,1) != "'" && !is_numeric($fdefault)))
576 if (strlen($fdefault) != 1 && substr($fdefault,0,1) == ' ' && substr($fdefault,strlen($fdefault)-1) == ' ')
577 $fdefault = trim($fdefault);
578 else if (strtolower($fdefault) != 'null')
579 $fdefault = $this->connection
->qstr($fdefault);
580 $suffix = $this->_CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned);
582 if ($widespacing) $fname = str_pad($fname,24);
583 $lines[$fid] = $fname.' '.$ftype.$suffix;
585 if ($fautoinc) $this->autoIncrement
= true;
588 return array($lines,$pkey);
591 GENERATE THE SIZE PART OF THE DATATYPE
592 $ftype is the actual type
593 $ty is the type defined originally in the DDL
595 function _GetSize($ftype, $ty, $fsize, $fprec)
597 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && strpos($ftype,'(') === false) {
598 $ftype .= "(".$fsize;
599 if (strlen($fprec)) $ftype .= ",".$fprec;
606 // return string must begin with space
607 function _CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint)
610 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
611 if ($fnotnull) $suffix .= ' NOT NULL';
612 if ($fconstraint) $suffix .= ' '.$fconstraint;
616 function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
620 if ( isset($idxoptions['REPLACE']) ||
isset($idxoptions['DROP']) ) {
621 $sql[] = sprintf ($this->dropIndex
, $idxname);
622 if ( isset($idxoptions['DROP']) )
626 if ( empty ($flds) ) {
630 $unique = isset($idxoptions['UNIQUE']) ?
' UNIQUE' : '';
632 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
634 if ( isset($idxoptions[$this->upperName
]) )
635 $s .= $idxoptions[$this->upperName
];
637 if ( is_array($flds) )
638 $flds = implode(', ',$flds);
639 $s .= '(' . $flds . ')';
645 function _DropAutoIncrement($tabname)
650 function _TableSQL($tabname,$lines,$pkey,$tableoptions)
654 if (isset($tableoptions['REPLACE']) ||
isset ($tableoptions['DROP'])) {
655 $sql[] = sprintf($this->dropTable
,$tabname);
656 if ($this->autoIncrement
) {
657 $sInc = $this->_DropAutoIncrement($tabname);
658 if ($sInc) $sql[] = $sInc;
660 if ( isset ($tableoptions['DROP']) ) {
664 $s = "CREATE TABLE $tabname (\n";
665 $s .= implode(",\n", $lines);
666 if (sizeof($pkey)>0) {
667 $s .= ",\n PRIMARY KEY (";
668 $s .= implode(", ",$pkey).")";
670 if (isset($tableoptions['CONSTRAINTS']))
671 $s .= "\n".$tableoptions['CONSTRAINTS'];
673 if (isset($tableoptions[$this->upperName
.'_CONSTRAINTS']))
674 $s .= "\n".$tableoptions[$this->upperName
.'_CONSTRAINTS'];
677 if (isset($tableoptions[$this->upperName
])) $s .= $tableoptions[$this->upperName
];
684 GENERATE TRIGGERS IF NEEDED
685 used when table has auto-incrementing field that is emulated using triggers
687 function _Triggers($tabname,$taboptions)
693 Sanitize options, so that array elements with no keys are promoted to keys
695 function _Options($opts)
697 if (!is_array($opts)) return array();
699 foreach($opts as $k => $v) {
700 if (is_numeric($k)) $newopts[strtoupper($v)] = $v;
701 else $newopts[strtoupper($k)] = $v;
707 "Florian Buzin [ easywe ]" <florian.buzin#easywe.de>
709 This function changes/adds new fields to your table. You don't
710 have to know if the col is new or not. It will check on its own.
712 function ChangeTableSQL($tablename, $flds, $tableoptions = false)
714 global $ADODB_FETCH_MODE;
716 $save = $ADODB_FETCH_MODE;
717 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC
;
718 if ($this->connection
->fetchMode
!== false) $savem = $this->connection
->SetFetchMode(false);
720 // check table exists
721 $save_handler = $this->connection
->raiseErrorFn
;
722 $this->connection
->raiseErrorFn
= '';
723 $cols = $this->MetaColumns($tablename);
724 $this->connection
->raiseErrorFn
= $save_handler;
726 if (isset($savem)) $this->connection
->SetFetchMode($savem);
727 $ADODB_FETCH_MODE = $save;
730 return $this->CreateTableSQL($tablename, $flds, $tableoptions);
733 if (is_array($flds)) {
734 // Cycle through the update fields, comparing
735 // existing fields to fields to update.
736 // if the Metatype and size is exactly the
737 // same, ignore - by Mark Newham
739 foreach($flds as $k=>$v) {
740 if ( isset($cols[$k]) && is_object($cols[$k]) ) {
741 // If already not allowing nulls, then don't change
743 if (isset($obj->not_null
) && $obj->not_null
)
744 $v = str_replace('NOT NULL','',$v);
747 $ml = $c->max_length
;
748 $mt = $this->MetaType($c->type
,$ml);
749 if ($ml == -1) $ml = '';
750 if ($mt == 'X') $ml = $v['SIZE'];
751 if (($mt != $v['TYPE']) ||
$ml != $v['SIZE']) {
762 // already exists, alter table instead
763 list($lines,$pkey) = $this->_GenFields($flds);
764 $alter = 'ALTER TABLE ' . $this->TableName($tablename);
767 foreach ( $lines as $id => $v ) {
768 if ( isset($cols[$id]) && is_object($cols[$id]) ) {
770 $flds = Lens_ParseArgs($v,',');
772 // We are trying to change the size of the field, if not allowed, simply ignore the request.
773 if ($flds && in_array(strtoupper(substr($flds[0][1],0,4)),$this->invalidResizeTypes4
)) continue;
775 $sql[] = $alter . $this->alterCol
. ' ' . $v;
777 $sql[] = $alter . $this->addCol
. ' ' . $v;