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