1: <?php
  2: /*
  3:  You may not change or alter any portion of this comment or credits
  4:  of supporting developers from this source code or any supporting source code
  5:  which is considered copyrighted (c) material of the original comment or credit authors.
  6: 
  7:  This program is distributed in the hope that it will be useful,
  8:  but WITHOUT ANY WARRANTY; without even the implied warranty of
  9:  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 10:  */
 11: 
 12: namespace Xmf\Database;
 13: 
 14: use Xmf\Language;
 15: 
 16: /**
 17:  * Xmf\Database\Tables
 18:  *
 19:  * inspired by Yii CDbMigration
 20:  *
 21:  * Build a work queue of database changes needed to implement new and
 22:  * changed tables. Define table(s) you are dealing with and any desired
 23:  * change(s). If the changes are already in place (i.e. the new column
 24:  * already exists) no work is added. Then executeQueue() to process the
 25:  * whole set.
 26:  *
 27:  * @category  Xmf\Database\Tables
 28:  * @package   Xmf
 29:  * @author    Richard Griffith <richard@geekwright.com>
 30:  * @copyright 2011-2016 XOOPS Project (http://xoops.org)
 31:  * @license   GNU GPL 2 or later (http://www.gnu.org/licenses/gpl-2.0.html)
 32:  * @link      http://xoops.org
 33:  */
 34: class Tables
 35: {
 36:     /**
 37:      * @var \XoopsDatabase
 38:      */
 39:     protected $db;
 40: 
 41:     /**
 42:      * @var string
 43:      */
 44:     protected $databaseName;
 45: 
 46:     /**
 47:      * @var array Tables
 48:      */
 49:     protected $tables;
 50: 
 51:     /**
 52:      * @var array Work queue
 53:      */
 54:     protected $queue;
 55: 
 56:     /**
 57:      * @var string last error message
 58:      */
 59:     protected $lastError;
 60: 
 61:     /**
 62:      * @var int last error number
 63:      */
 64:     protected $lastErrNo;
 65: 
 66:     /**
 67:      * Constructor
 68:      *
 69:      */
 70:     public function __construct()
 71:     {
 72:         Language::load('xmf');
 73: 
 74:         $this->db = \XoopsDatabaseFactory::getDatabaseConnection();
 75:         $this->databaseName = XOOPS_DB_NAME;
 76:         $this->resetQueue();
 77:     }
 78: 
 79:     /**
 80:      * Return a table name, prefixed with site table prefix
 81:      *
 82:      * @param string $table table name to contain prefix
 83:      *
 84:      * @return string table name with prefix
 85:      */
 86:     protected function name($table)
 87:     {
 88:         return $this->db->prefix($table);
 89:     }
 90: 
 91:     /**
 92:      * Add new column for table to the work queue
 93:      *
 94:      * @param string $table      table to contain the column
 95:      * @param string $column     name of column to add
 96:      * @param string $attributes column_definition
 97:      *
 98:      * @return bool true if no errors, false if errors encountered
 99:      */
100:     public function addColumn($table, $column, $attributes)
101:     {
102:         $columnDef = array(
103:             'name' => $column,
104:             'attributes' => $attributes
105:         );
106: 
107:         // Find table def.
108:         if (isset($this->tables[$table])) {
109:             $tableDef = &$this->tables[$table];
110:             // Is this on a table we are adding?
111:             if (isset($tableDef['create']) && $tableDef['create']) {
112:                 array_push($tableDef['columns'], $columnDef);
113:             } else {
114:                 foreach ($tableDef['columns'] as $col) {
115:                     if (strcasecmp($col['name'], $column) == 0) {
116:                         return true;
117:                     }
118:                 }
119:                 $this->queue[] = "ALTER TABLE `{$tableDef['name']}`"
120:                     . " ADD COLUMN `{$column}` {$columnDef['attributes']}";
121:                 array_push($tableDef['columns'], $columnDef);
122:             }
123:         } else {
124:             return $this->tableNotEstablished();
125:         }
126: 
127:         return true; // exists or is added to queue
128:     }
129: 
130:     /**
131:      * Add new primary key definition for table to work queue
132:      *
133:      * @param string $table  table
134:      * @param string $column column or comma separated list of columns
135:      *                       to use as primary key
136:      *
137:      * @return bool true if no errors, false if errors encountered
138:      */
139:     public function addPrimaryKey($table, $column)
140:     {
141:         $columns = str_getcsv(str_replace(' ', '', $column));
142:         $columnList = '';
143:         $firstComma = '';
144:         foreach ($columns as $col) {
145:             $columnList .= "{$firstComma}`{$col}`";
146:             $firstComma = ', ';
147:         }
148:         if (isset($this->tables[$table])) {
149:             if (isset($this->tables[$table]['create']) && $this->tables[$table]['create']) {
150:                 $this->tables[$table]['keys']['PRIMARY']['columns'] = $columnList;
151:             } else {
152:                 $this->queue[] = "ALTER TABLE `{$this->tables[$table]['name']}` ADD PRIMARY KEY({$columnList})";
153:             }
154:         } else {
155:             return $this->tableNotEstablished();
156:         }
157: 
158:         return true;
159:     }
160: 
161:     /**
162:      * Add new index definition for index to work queue
163:      *
164:      * @param string $name   name of index to add
165:      * @param string $table  table indexed
166:      * @param string $column column or a comma separated list of columns
167:      *                        to use as the key
168:      * @param bool   $unique true if index is to be unique
169:      *
170:      * @return bool true if no errors, false if errors encountered
171:      */
172:     public function addIndex($name, $table, $column, $unique = false)
173:     {
174:         $columns = str_getcsv(str_replace(' ', '', $column));
175:         $columnList = '';
176:         $firstComma = '';
177:         foreach ($columns as $col) {
178:             $columnList .= "{$firstComma}`{$col}`";
179:             $firstComma = ', ';
180:         }
181:         if (isset($this->tables[$table])) {
182:             if (isset($this->tables[$table]['create']) && $this->tables[$table]['create']) {
183:                 $this->tables[$table]['keys'][$name]['columns'] = $columnList;
184:                 $this->tables[$table]['keys'][$name]['unique'] = (bool) $unique;
185:             } else {
186:                 $add = ($unique ? 'ADD UNIQUE INDEX' : 'ADD INDEX');
187:                 $this->queue[] = "ALTER TABLE `{$this->tables[$table]['name']}` {$add} `{$name}` ({$columnList})";
188:             }
189:         } else {
190:             return $this->tableNotEstablished();
191:         }
192: 
193:         return true;
194:     }
195: 
196:     /**
197:      * Load table schema from database, or starts new empty schema if
198:      * table does not exist
199:      *
200:      * @param string $table table
201:      *
202:      * @return bool true if no errors, false if errors encountered
203:      */
204:     public function addTable($table)
205:     {
206:         if (isset($this->tables[$table])) {
207:             return true;
208:         }
209:         $tableDef = $this->getTable($table);
210:         if (is_array($tableDef)) {
211:             $this->tables[$table] = $tableDef;
212: 
213:             return true;
214:         } else {
215:             if ($tableDef === true) {
216:                 $tableDef = array(
217:                     'name' => $this->name($table),
218:                     'options' => 'ENGINE=InnoDB',
219:                     'columns' => array(),
220:                     'keys' => array(),
221:                     'create' => true,
222:                 );
223:                 $this->tables[$table] = $tableDef;
224: 
225:                 $this->queue[] = array('createtable' => $table);
226: 
227:                 return true;
228:             } else {
229:                 return false;
230:             }
231:         }
232:     }
233: 
234:     /**
235:      * AddTable only if it exists
236:      *
237:      * @param string $table table
238:      *
239:      * @return bool true if table exists, false otherwise
240:      */
241:     public function useTable($table)
242:     {
243:         if (isset($this->tables[$table])) {
244:             return true;
245:         }
246:         $tableDef = $this->getTable($table);
247:         if (is_array($tableDef)) {
248:             $this->tables[$table] = $tableDef;
249:             return true;
250:         }
251:         return false;
252:     }
253: 
254:     /**
255:      * Get column attributes
256:      *
257:      * @param string $table  table containing the column
258:      * @param string $column column to alter
259:      *
260:      * @return string|bool attribute string, or false if error encountered
261:      */
262:     public function getColumnAttributes($table, $column)
263:     {
264:         // Find table def.
265:         if (isset($this->tables[$table])) {
266:             $tableDef = $this->tables[$table];
267:             // loop thru and find the column
268:             foreach ($tableDef['columns'] as $col) {
269:                 if (strcasecmp($col['name'], $column) === 0) {
270:                     return $col['attributes'];
271:                 }
272:             }
273:         }
274: 
275:         return false;
276:     }
277: 
278:     /**
279:      * Get indexes for a table
280:      *
281:      * @param string $table get indexes for this named table
282:      *
283:      * @return array|bool array of indexes, or false if error encountered
284:      */
285:     public function getTableIndexes($table)
286:     {
287:         // Find table def.
288:         if (isset($this->tables[$table]) && isset($this->tables[$table]['keys'])) {
289:             return $this->tables[$table]['keys'];
290:         }
291: 
292:         return false;
293:     }
294: 
295:     /**
296:      * Add alter column operation to the work queue
297:      *
298:      * @param string $table      table containing the column
299:      * @param string $column     column to alter
300:      * @param string $attributes new column_definition
301:      * @param string $newName    new name for column, blank to keep same
302:      *
303:      * @return bool true if no errors, false if errors encountered
304:      */
305:     public function alterColumn($table, $column, $attributes, $newName = '')
306:     {
307:         if (empty($newName)) {
308:             $newName = $column;
309:         }
310:         // Find table def.
311:         if (isset($this->tables[$table])) {
312:             $tableDef = &$this->tables[$table];
313:             // Is this on a table we are adding?
314:             if (isset($tableDef['create']) && $tableDef['create']) {
315:                 // loop thru and find the column
316:                 foreach ($tableDef['columns'] as &$col) {
317:                     if (strcasecmp($col['name'], $column) == 0) {
318:                         $col['name'] = $newName;
319:                         $col['attributes'] = $attributes;
320:                         break;
321:                     }
322:                 }
323: 
324:                 return true;
325:             } else {
326:                 $this->queue[] = "ALTER TABLE `{$tableDef['name']}` " .
327:                     "CHANGE COLUMN `{$column}` `{$newName}` {$attributes} ";
328:                 // loop thru and find the column
329:                 foreach ($tableDef['columns'] as &$col) {
330:                     if (strcasecmp($col['name'], $column) == 0) {
331:                         $col['name'] = $newName;
332:                         $col['attributes'] = $attributes;
333:                         break;
334:                     }
335:                 }
336:             }
337:         } else {
338:             return $this->tableNotEstablished();
339:         }
340: 
341:         return true;
342:     }
343: 
344:     /**
345:      * Loads table schema from database, and adds newTable with that
346:      * schema to the queue
347:      *
348:      * @param string $table    existing table
349:      * @param string $newTable new table
350:      * @param bool   $withData true to copy data, false for schema only
351:      *
352:      * @return bool true if no errors, false if errors encountered
353:      */
354:     public function copyTable($table, $newTable, $withData = false)
355:     {
356:         if (isset($this->tables[$newTable])) {
357:             return true;
358:         }
359:         $tableDef = $this->getTable($table);
360:         $copy = $this->name($newTable);
361:         $original = $this->name($table);
362: 
363:         if (is_array($tableDef)) {
364:             $tableDef['name'] = $copy;
365:             if ($withData) {
366:                 $this->queue[] = "CREATE TABLE `{$copy}` LIKE `{$original}` ;";
367:                 $this->queue[] = "INSERT INTO `{$copy}` SELECT * FROM `{$original}` ;";
368:             } else {
369:                 $tableDef['create'] = true;
370:                 $this->queue[] = array('createtable' => $newTable);
371:             }
372:             $this->tables[$newTable] = $tableDef;
373: 
374:             return true;
375:         } else {
376:             return false;
377:         }
378:     }
379: 
380:     /**
381:      * Add drop column operation to the work queue
382:      *
383:      * @param string $table  table containing the column
384:      * @param string $column column to drop
385:      *
386:      * @return bool true if no errors, false if errors encountered
387:      */
388:     public function dropColumn($table, $column)
389:     {
390:         // Find table def.
391:         if (isset($this->tables[$table])) {
392:             $tableDef = $this->tables[$table];
393:             $this->queue[] = "ALTER TABLE `{$tableDef['name']}` DROP COLUMN `{$column}`";
394:         } else {
395:             return $this->tableNotEstablished();
396:         }
397: 
398:         return true;
399:     }
400: 
401:     /**
402:      * Add drop index operation to the work queue
403:      *
404:      * @param string $name  name of index to drop
405:      * @param string $table table indexed
406:      *
407:      * @return bool true if no errors, false if errors encountered
408:      */
409:     public function dropIndex($name, $table)
410:     {
411:         if (isset($this->tables[$table])) {
412:             $tableDef = $this->tables[$table];
413:             $this->queue[] = "ALTER TABLE `{$tableDef['name']}` DROP INDEX `{$name}`";
414:         } else {
415:             return $this->tableNotEstablished();
416:         }
417: 
418:         return true;
419:     }
420: 
421:     /**
422:      * Add drop for all (non-PRIMARY) keys for a table to the work
423:      * queue. This can be used to clean up indexes with automatic names.
424:      *
425:      * @param string $table table indexed
426:      *
427:      * @return bool true if no errors, false if errors encountered
428:      */
429:     public function dropIndexes($table)
430:     {
431:         // Find table def.
432:         if (isset($this->tables[$table])) {
433:             $tableDef = &$this->tables[$table];
434:             // Is this on a table we are adding?
435:             if (isset($tableDef['create']) && $tableDef['create']) {
436:                 // strip everything but the PRIMARY from definition
437:                 foreach ($tableDef['keys'] as $keyName => $key) {
438:                     if ($keyName !== 'PRIMARY') {
439:                         unset($tableDef['keys'][$keyName]);
440:                     }
441:                 }
442:             } else {
443:                 // build drops to strip everything but the PRIMARY
444:                 foreach ($tableDef['keys'] as $keyName => $key) {
445:                     if ($keyName !== 'PRIMARY') {
446:                         $this->queue[] = "ALTER TABLE `{$tableDef['name']}` DROP INDEX {$keyName}";
447:                     }
448:                 }
449:             }
450:         } else {
451:             return $this->tableNotEstablished();
452:         }
453: 
454:         return true;
455:     }
456: 
457:     /**
458:      * Add drop of PRIMARY key for a table to the work queue
459:      *
460:      * @param string $table table
461:      *
462:      * @return bool true if no errors, false if errors encountered
463:      */
464:     public function dropPrimaryKey($table)
465:     {
466:         if (isset($this->tables[$table])) {
467:             $tableDef = $this->tables[$table];
468:             $this->queue[] = "ALTER TABLE `{$tableDef['name']}` DROP PRIMARY KEY ";
469:         } else {
470:             return $this->tableNotEstablished();
471:         }
472: 
473:         return true;
474:     }
475: 
476:     /**
477:      * Add drop of table to the work queue
478:      *
479:      * @param string $table table
480:      *
481:      * @return bool true if no errors, false if errors encountered
482:      */
483:     public function dropTable($table)
484:     {
485:         if (isset($this->tables[$table])) {
486:             $tableDef = $this->tables[$table];
487:             $this->queue[] = "DROP TABLE `{$tableDef['name']}` ";
488:             unset($this->tables[$table]);
489:         }
490:         // no table is not an error since we are dropping it anyway
491:         return true;
492:     }
493: 
494: 
495:     /**
496:      * Add rename table operation to the work queue
497:      *
498:      * @param string $table   table
499:      * @param string $newName new table name
500:      *
501:      * @return bool true if no errors, false if errors encountered
502:      */
503:     public function renameTable($table, $newName)
504:     {
505:         if (isset($this->tables[$table])) {
506:             $tableDef = $this->tables[$table];
507:             $newTable = $this->name($newName);
508:             $this->queue[] = "ALTER TABLE `{$tableDef['name']}` RENAME TO `{$newTable}`";
509:             $tableDef['name'] = $newTable;
510:             $this->tables[$newName] = $tableDef;
511:             unset($this->tables[$table]);
512:         } else {
513:             return $this->tableNotEstablished();
514:         }
515: 
516:         return true;
517:     }
518: 
519:     /**
520:      * Add alter table table_options (ENGINE, DEFAULT CHARSET, etc.)
521:      * to work queue
522:      *
523:      * @param string $table   table
524:      * @param string $options table_options
525:      *
526:      * @return bool true if no errors, false if errors encountered
527:      */
528:     public function setTableOptions($table, $options)
529:     {
530:         if (isset($this->tables[$table])) {
531:             $tableDef = &$this->tables[$table];
532:             // Is this on a table we are adding?
533:             if (isset($tableDef['create']) && $tableDef['create']) {
534:                 $tableDef['options'] = $options;
535:                 return true;
536:             } else {
537:                 $this->queue[] = "ALTER TABLE `{$tableDef['name']}` {$options} ";
538:                 $tableDef['options'] = $options;
539:                 return true;
540:             }
541:         } else {
542:             return $this->tableNotEstablished();
543:         }
544:     }
545: 
546: 
547:     /**
548:      * Clear the work queue
549:      *
550:      * @return void
551:      */
552:     public function resetQueue()
553:     {
554:         $this->tables = array();
555:         $this->queue  = array();
556:     }
557: 
558:     /**
559:      * Executes the work queue
560:      *
561:      * @param bool $force true to force updates even if this is a 'GET' request
562:      *
563:      * @return bool true if no errors, false if errors encountered
564:      */
565:     public function executeQueue($force = false)
566:     {
567:         $this->expandQueue();
568:         foreach ($this->queue as &$ddl) {
569:             if (is_array($ddl)) {
570:                 if (isset($ddl['createtable'])) {
571:                     $ddl = $this->renderTableCreate($ddl['createtable']);
572:                 }
573:             }
574:             $result = $this->execSql($ddl, $force);
575:             if (!$result) {
576:                 $this->lastError = $this->db->error();
577:                 $this->lastErrNo = $this->db->errno();
578: 
579:                 return false;
580:             }
581:         }
582: 
583:         return true;
584:     }
585: 
586: 
587:     /**
588:      * Create a DELETE statement and add it to the work queue
589:      *
590:      * @param string                 $table    table
591:      * @param string|CriteriaElement $criteria string where clause or object criteria
592:      *
593:      * @return bool true if no errors, false if errors encountered
594:      */
595:     public function delete($table, $criteria)
596:     {
597:         if (isset($this->tables[$table])) {
598:             $tableDef = $this->tables[$table];
599:             $where = '';
600:             if (is_scalar($criteria)) {
601:                 $where = $criteria;
602:             } elseif (is_object($criteria)) {
603:                 /* @var  $criteria \CriteriaCompo */
604:                 $where = $criteria->renderWhere();
605:             }
606:             $this->queue[] = "DELETE FROM `{$tableDef['name']}` {$where}";
607:         } else {
608:             return $this->tableNotEstablished();
609:         }
610: 
611:         return true;
612:     }
613: 
614:     /**
615:      * Create an INSERT SQL statement and add it to the work queue.
616:      *
617:      * @param string  $table      table
618:      * @param array   $columns    array of 'column'=>'value' entries
619:      * @param boolean $quoteValue true to quote values, false if caller handles quoting
620:      *
621:      * @return boolean true if no errors, false if errors encountered
622:      */
623:     public function insert($table, $columns, $quoteValue = true)
624:     {
625:         if (isset($this->tables[$table])) {
626:             $tableDef = $this->tables[$table];
627:             $colSql = '';
628:             $valSql = '';
629:             foreach ($tableDef['columns'] as $col) {
630:                 $comma = empty($colSql) ? '' : ', ';
631:                 if (isset($columns[$col['name']])) {
632:                     $colSql .= "{$comma}`{$col['name']}`";
633:                     $valSql .= $comma
634:                         . ($quoteValue ? $this->db->quote($columns[$col['name']]) : $columns[$col['name']]);
635:                 }
636:             }
637:             $sql = "INSERT INTO `{$tableDef['name']}` ({$colSql}) VALUES({$valSql})";
638:             $this->queue[] = $sql;
639: 
640:             return true;
641:         } else {
642:             return $this->tableNotEstablished();
643:         }
644:     }
645: 
646:     /**
647:      * Create an UPDATE SQL statement and add it to the work queue
648:      *
649:      * @param string                 $table      table
650:      * @param array                  $columns    array of 'column'=>'value' entries
651:      * @param string|CriteriaElement $criteria   string where clause or object criteria
652:      * @param boolean                $quoteValue true to quote values, false if caller handles quoting
653:      *
654:      * @return boolean true if no errors, false if errors encountered
655:      */
656:     public function update($table, $columns, $criteria, $quoteValue = true)
657:     {
658:         if (isset($this->tables[$table])) {
659:             $tableDef = $this->tables[$table];
660:             $where = '';
661:             if (is_scalar($criteria)) {
662:                 $where = $criteria;
663:             } elseif (is_object($criteria)) {
664:                 /* @var  $criteria \CriteriaCompo */
665:                 $where = $criteria->renderWhere();
666:             }
667:             $colSql = '';
668:             foreach ($tableDef['columns'] as $col) {
669:                 $comma = empty($colSql) ? '' : ', ';
670:                 if (isset($columns[$col['name']])) {
671:                     $colSql .= "{$comma}`{$col['name']}` = "
672:                         . ($quoteValue ? $this->db->quote($columns[$col['name']]) : $columns[$col['name']]);
673:                 }
674:             }
675:             $sql = "UPDATE `{$tableDef['name']}` SET {$colSql} {$where}";
676:             $this->queue[] = $sql;
677: 
678:             return true;
679:         } else {
680:             return $this->tableNotEstablished();
681:         }
682:     }
683: 
684:     /**
685:      * Add statement to remove all rows from a table to the work queue
686:      *
687:      * @param string $table table
688:      *
689:      * @return bool true if no errors, false if errors encountered
690:      */
691:     public function truncate($table)
692:     {
693:         if (isset($this->tables[$table])) {
694:             $tableDef = $this->tables[$table];
695:             $this->queue[] = "TRUNCATE TABLE `{$tableDef['name']}`";
696:         } else {
697:             return $this->tableNotEstablished();
698:         }
699: 
700:         return true;
701:     }
702: 
703: 
704: 
705:     /**
706:      * return SQL to create the table
707:      *
708:      * This method does NOT modify the work queue
709:      *
710:      * @param string $table    table
711:      * @param bool   $prefixed true to return with table name prefixed
712:      *
713:      * @return string|false string SQL to create table, or false if errors encountered
714:      */
715:     protected function renderTableCreate($table, $prefixed = false)
716:     {
717:         if (isset($this->tables[$table])) {
718:             $tableDef = $this->tables[$table];
719:             $tableName = ($prefixed ? $tableDef['name'] : $table);
720:             $sql = "CREATE TABLE `{$tableName}` (";
721:             $firstComma = '';
722:             foreach ($tableDef['columns'] as $col) {
723:                 $sql .= "{$firstComma}\n    `{$col['name']}`  {$col['attributes']}";
724:                 $firstComma = ',';
725:             }
726:             $keySql = '';
727:             foreach ($tableDef['keys'] as $keyName => $key) {
728:                 if ($keyName === 'PRIMARY') {
729:                     $keySql .= ",\n  PRIMARY KEY ({$key['columns']})";
730:                 } else {
731:                     $unique = $key['unique'] ? 'UNIQUE ' : '';
732:                     $keySql .= ",\n  {$unique}KEY {$keyName} ({$key['columns']})";
733:                 }
734:             }
735:             $sql .= $keySql;
736:             $sql .= "\n) {$tableDef['options']}";
737: 
738:             return $sql;
739:         } else {
740:             return $this->tableNotEstablished();
741:         }
742:     }
743: 
744:     /**
745:      * execute an SQL statement
746:      *
747:      * @param string $sql   SQL statement to execute
748:      * @param bool   $force true to use force updates even in safe requests
749:      *
750:      * @return mixed result resource if no error,
751:      *               true if no error but no result
752:      *               false if error encountered.
753:      *               Any error message is in $this->lastError;
754:      */
755:     protected function execSql($sql, $force = false)
756:     {
757:         if ($force) {
758:             $result = $this->db->queryF($sql);
759:         } else {
760:             $result = $this->db->query($sql);
761:         }
762: 
763:         if (!$result) {
764:             $this->lastError = $this->db->error();
765:             $this->lastErrNo = $this->db->errno();
766:         }
767: 
768:         return $result;
769:     }
770: 
771:     /**
772:      * fetch the next row of a result set
773:      *
774:      * @param resource $result as returned by query
775:      *
776:      * @return mixed false on error
777:      */
778:     protected function fetch($result)
779:     {
780:         return $this->db->fetchArray($result);
781:     }
782: 
783:     /**
784:      * get table definition from INFORMATION_SCHEMA
785:      *
786:      * @param string $table table
787:      *
788:      * @return array|bool table definition array if table exists, true if table not defined, or
789:      *                    false on error. Error message in $this->lastError;
790:      */
791:     protected function getTable($table)
792:     {
793:         $tableDef = array();
794: 
795:         $sql  = 'SELECT TABLE_NAME, ENGINE, CHARACTER_SET_NAME ';
796:         $sql .= ' FROM `INFORMATION_SCHEMA`.`TABLES` t, ';
797:         $sql .= ' `INFORMATION_SCHEMA`.`COLLATIONS` c ';
798:         $sql .= ' WHERE t.TABLE_SCHEMA = \'' . $this->databaseName . '\' ';
799:         $sql .= ' AND t.TABLE_NAME = \'' . $this->name($table) . '\' ';
800:         $sql .= ' AND t.TABLE_COLLATION  = c.COLLATION_NAME ';
801: 
802:         $result = $this->execSql($sql);
803:         if (!$result) {
804:             return false;
805:         }
806:         $tableSchema = $this->fetch($result);
807:         if (empty($tableSchema)) {
808:             return true;
809:         }
810:         $tableDef['name'] = $tableSchema['TABLE_NAME'];
811:         $tableDef['options'] = 'ENGINE=' . $tableSchema['ENGINE'] . ' '
812:             . 'DEFAULT CHARSET=' . $tableSchema['CHARACTER_SET_NAME'];
813: 
814:         $sql  = 'SELECT * ';
815:         $sql .= ' FROM `INFORMATION_SCHEMA`.`COLUMNS` ';
816:         $sql .= ' WHERE TABLE_SCHEMA = \'' . $this->databaseName . '\' ';
817:         $sql .= ' AND TABLE_NAME = \'' . $this->name($table) . '\' ';
818:         $sql .= ' ORDER BY `ORDINAL_POSITION` ';
819: 
820:         $result = $this->execSql($sql);
821: 
822:         while ($column = $this->fetch($result)) {
823:             $attributes = ' ' . $column['COLUMN_TYPE'] . ' '
824:                 . (($column['IS_NULLABLE'] === 'NO') ? ' NOT NULL ' : '')
825:                 . (($column['COLUMN_DEFAULT'] === null) ? '' : " DEFAULT '" . $column['COLUMN_DEFAULT'] . "' ")
826:                 . $column['EXTRA'];
827: 
828:             $columnDef = array(
829:                 'name' => $column['COLUMN_NAME'],
830:                 'attributes' => $attributes
831:             );
832: 
833:             $tableDef['columns'][] = $columnDef;
834:         };
835: 
836:         $sql  = 'SELECT `INDEX_NAME`, `SEQ_IN_INDEX`, `NON_UNIQUE`, ';
837:         $sql .= ' `COLUMN_NAME`, `SUB_PART` ';
838:         $sql .= ' FROM `INFORMATION_SCHEMA`.`STATISTICS` ';
839:         $sql .= ' WHERE TABLE_SCHEMA = \'' . $this->databaseName . '\' ';
840:         $sql .= ' AND TABLE_NAME = \'' . $this->name($table) . '\' ';
841:         $sql .= ' ORDER BY `INDEX_NAME`, `SEQ_IN_INDEX` ';
842: 
843:         $result = $this->execSql($sql);
844: 
845:         $lastKey = '';
846:         $keyCols = '';
847:         $keyUnique = false;
848:         while ($key = $this->fetch($result)) {
849:             if ($lastKey != $key['INDEX_NAME']) {
850:                 if (!empty($lastKey)) {
851:                     $tableDef['keys'][$lastKey]['columns'] = $keyCols;
852:                     $tableDef['keys'][$lastKey]['unique'] = $keyUnique;
853:                 }
854:                 $lastKey = $key['INDEX_NAME'];
855:                 $keyCols = $key['COLUMN_NAME'];
856:                 if (!empty($key['SUB_PART'])) {
857:                     $keyCols .= ' (' . $key['SUB_PART'] . ')';
858:                 }
859:                 $keyUnique = !$key['NON_UNIQUE'];
860:             } else {
861:                 $keyCols .= ', ' . $key['COLUMN_NAME'];
862:                 if (!empty($key['SUB_PART'])) {
863:                     $keyCols .= ' (' . $key['SUB_PART'] . ')';
864:                 }
865:             }
866:         };
867:         if (!empty($lastKey)) {
868:             $tableDef['keys'][$lastKey]['columns'] = $keyCols;
869:             $tableDef['keys'][$lastKey]['unique'] = $keyUnique;
870:         }
871: 
872:         return $tableDef;
873:     }
874: 
875:     /**
876:      * During processing, tables to be created are put in the queue as
877:      * an array('createtable' => tablename) since the definition is not
878:      * complete. This method will expand those references to the full
879:      * ddl to create the table.
880:      *
881:      * @return void
882:      */
883:     protected function expandQueue()
884:     {
885:         foreach ($this->queue as &$ddl) {
886:             if (is_array($ddl)) {
887:                 if (isset($ddl['createtable'])) {
888:                     $ddl = $this->renderTableCreate($ddl['createtable'], true);
889:                 }
890:             }
891:         }
892:     }
893: 
894:     /**
895:      * Return message from last error encountered
896:      *
897:      * @return string last error message
898:      */
899:     public function getLastError()
900:     {
901:         return $this->lastError;
902:     }
903: 
904:     /**
905:      * Return code from last error encountered
906:      *
907:      * @return int last error number
908:      */
909:     public function getLastErrNo()
910:     {
911:         return $this->lastErrNo;
912:     }
913: 
914:     /**
915:      * dumpTables - utility function to dump raw tables array
916:      *
917:      * @return array tables
918:      */
919:     public function dumpTables()
920:     {
921:         return $this->tables;
922:     }
923: 
924:     /**
925:      * dumpQueue - utility function to dump the work queue
926:      *
927:      * @return array work queue
928:      */
929:     public function dumpQueue()
930:     {
931:         $this->expandQueue();
932: 
933:         return $this->queue;
934:     }
935: 
936:     /**
937:      * addToQueue - utility function to add a statement to the work queue
938:      *
939:      * @param string $sql an SQL/DDL statement to add
940:      *
941:      * @return void
942:      */
943:     public function addToQueue($sql)
944:     {
945:         $this->queue[] = $sql;
946:     }
947: 
948:     /**
949:      * Set lastError as table not established
950:      *
951:      * @return false
952:      */
953:     protected function tableNotEstablished()
954:     {
955:         $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
956:         $this->lastErrNo = -1;
957:         return false;
958:     }
959: }
960: