1: <?php
   2: 
   3: namespace RedBeanPHP\QueryWriter;
   4: 
   5: use RedBeanPHP\Adapter\DBAdapter as DBAdapter;
   6: use RedBeanPHP\RedException as RedException;
   7: use RedBeanPHP\QueryWriter as QueryWriter;
   8: use RedBeanPHP\OODBBean as OODBBean;
   9: use RedBeanPHP\RedException\SQL as SQLException;
  10: 
  11: /**
  12:  * RedBeanPHP Abstract Query Writer.
  13:  * Represents an abstract Database to RedBean
  14:  * To write a driver for a different database for RedBean
  15:  * Contains a number of functions all implementors can
  16:  * inherit or override.
  17:  *
  18:  * @file    RedBeanPHP/QueryWriter/AQueryWriter.php
  19:  * @author  Gabor de Mooij and the RedBeanPHP Community
  20:  * @license BSD/GPLv2
  21:  *
  22:  * @copyright
  23:  * (c) copyright G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
  24:  * This source file is subject to the BSD/GPLv2 License that is bundled
  25:  * with this source code in the file license.txt.
  26:  */
  27: abstract class AQueryWriter
  28: {
  29:     /**
  30:      * @var array
  31:      */
  32:     private static $sqlFilters = array();
  33: 
  34:     /**
  35:      * @var boolean
  36:      */
  37:     private static $flagSQLFilterSafeMode = false;
  38: 
  39:     /**
  40:      * @var boolean
  41:      */
  42:     private static $flagNarrowFieldMode = true;
  43: 
  44:     /**
  45:      * @var array
  46:      */
  47:     public static $renames = array();
  48: 
  49:     /**
  50:      * @var DBAdapter
  51:      */
  52:     protected $adapter;
  53: 
  54:     /**
  55:      * @var string
  56:      */
  57:     protected $defaultValue = 'NULL';
  58: 
  59:     /**
  60:      * @var string
  61:      */
  62:     protected $quoteCharacter = '';
  63: 
  64:     /**
  65:      * @var boolean
  66:      */
  67:     protected $flagUseCache = TRUE;
  68: 
  69:     /**
  70:      * @var array
  71:      */
  72:     protected $cache = array();
  73: 
  74:     /**
  75:      * @var integer
  76:      */
  77:     protected $maxCacheSizePerType = 20;
  78: 
  79:     /**
  80:      * @var array
  81:      */
  82:     public $typeno_sqltype = array();
  83: 
  84:     /**
  85:      * Checks whether a number can be treated like an int.
  86:      *
  87:      * @param  string $value string representation of a certain value
  88:      *
  89:      * @return boolean
  90:      */
  91:     public static function canBeTreatedAsInt( $value )
  92:     {
  93:         return (bool) ( strval( $value ) === strval( intval( $value ) ) );
  94:     }
  95: 
  96:     /**
  97:      * @see QueryWriter::getAssocTableFormat
  98:      */
  99:     public static function getAssocTableFormat( $types )
 100:     {
 101:         sort( $types );
 102: 
 103:         $assoc = implode( '_', $types );
 104: 
 105:         return ( isset( self::$renames[$assoc] ) ) ? self::$renames[$assoc] : $assoc;
 106:     }
 107: 
 108:     /**
 109:      * @see QueryWriter::renameAssociation
 110:      */
 111:     public static function renameAssociation( $from, $to = NULL )
 112:     {
 113:         if ( is_array( $from ) ) {
 114:             foreach ( $from as $key => $value ) self::$renames[$key] = $value;
 115: 
 116:             return;
 117:         }
 118: 
 119:         self::$renames[$from] = $to;
 120:     }
 121: 
 122:     /**
 123:      * Globally available service method for RedBeanPHP.
 124:      * Converts a camel cased string to a snake cased string.
 125:      *
 126:      * @param string $camel camelCased string to converty to snake case
 127:      *
 128:      * @return string
 129:      */
 130:     public static function camelsSnake( $camel )
 131:     {
 132:         return strtolower( preg_replace( '/(?<=[a-z])([A-Z])|([A-Z])(?=[a-z])/', '_$1$2', $camel ) );
 133:     }
 134: 
 135:     /**
 136:      * Clears renames.
 137:      *
 138:      * @return void
 139:      */
 140:     public static function clearRenames()
 141:     {
 142:         self::$renames = array();
 143:     }
 144: 
 145:     /**
 146:      * Toggles 'Narrow Field Mode'.
 147:      * In Narrow Field mode the queryRecord method will
 148:      * narrow its selection field to
 149:      *
 150:      * SELECT {table}.*
 151:      *
 152:      * instead of
 153:      *
 154:      * SELECT *
 155:      *
 156:      * This is a better way of querying because it allows
 157:      * more flexibility (for instance joins). However if you need
 158:      * the wide selector for backward compatibility; use this method
 159:      * to turn OFF Narrow Field Mode by passing FALSE.
 160:      *
 161:      * @param boolean $narrowField TRUE = Narrow Field FALSE = Wide Field
 162:      *
 163:      * @return void
 164:      */
 165:     public static function setNarrowFieldMode( $narrowField )
 166:     {
 167:         self::$flagNarrowFieldMode = (boolean) $narrowField;
 168:     }
 169: 
 170:     /**
 171:      * Sets SQL filters.
 172:      * This is a lowlevel method to set the SQL filter array.
 173:      * The format of this array is:
 174:      *
 175:      * <code>
 176:      * array(
 177:      *      '<MODE, i.e. 'r' for read, 'w' for write>' => array(
 178:      *          '<TABLE NAME>' => array(
 179:      *              '<COLUMN NAME>' => '<SQL>'
 180:      *          )
 181:      *      )
 182:      * )
 183:      * </code>
 184:      *
 185:      * Example:
 186:      *
 187:      * <code>
 188:      * array(
 189:      *   QueryWriter::C_SQLFILTER_READ => array(
 190:      *  'book' => array(
 191:      *      'title' => ' LOWER(book.title) '
 192:      *  )
 193:      * )
 194:      * </code>
 195:      *
 196:      * Note that you can use constants instead of magical chars
 197:      * as keys for the uppermost array.
 198:      * This is a lowlevel method. For a more friendly method
 199:      * please take a look at the facade: R::bindFunc().
 200:      *
 201:      * @param array list of filters to set
 202:      *
 203:      * @return void
 204:      */
 205:     public static function setSQLFilters( $sqlFilters, $safeMode = false )
 206:     {
 207:         self::$flagSQLFilterSafeMode = (boolean) $safeMode;
 208:         self::$sqlFilters = $sqlFilters;
 209:     }
 210: 
 211:     /**
 212:      * Returns current SQL Filters.
 213:      * This method returns the raw SQL filter array.
 214:      * This is a lowlevel method. For a more friendly method
 215:      * please take a look at the facade: R::bindFunc().
 216:      *
 217:      * @return array
 218:      */
 219:     public static function getSQLFilters()
 220:     {
 221:         return self::$sqlFilters;
 222:     }
 223: 
 224:     /**
 225:      * Returns a cache key for the cache values passed.
 226:      * This method returns a fingerprint string to be used as a key to store
 227:      * data in the writer cache.
 228:      *
 229:      * @param array $keyValues key-value to generate key for
 230:      *
 231:      * @return string
 232:      */
 233:     private function getCacheKey( $keyValues )
 234:     {
 235:         return json_encode( $keyValues );
 236:     }
 237: 
 238:     /**
 239:      * Returns the values associated with the provided cache tag and key.
 240:      *
 241:      * @param string $cacheTag cache tag to use for lookup
 242:      * @param string $key      key to use for lookup
 243:      *
 244:      * @return mixed
 245:      */
 246:     private function getCached( $cacheTag, $key )
 247:     {
 248:         $sql = $this->adapter->getSQL();
 249: 
 250:         if ($this->updateCache()) {
 251:             if ( isset( $this->cache[$cacheTag][$key] ) ) {
 252:                 return $this->cache[$cacheTag][$key];
 253:             }
 254:         }
 255: 
 256:         return NULL;
 257:     }
 258: 
 259:     /**
 260:      * Checks if the previous query had a keep-cache tag.
 261:      * If so, the cache will persist, otherwise the cache will be flushed.
 262:      *
 263:      * Returns TRUE if the cache will remain and FALSE if a flush has
 264:      * been performed.
 265:      *
 266:      * @return boolean
 267:      */
 268:     private function updateCache()
 269:     {
 270:         $sql = $this->adapter->getSQL();
 271:         if ( strpos( $sql, '-- keep-cache' ) !== strlen( $sql ) - 13 ) {
 272:             // If SQL has been taken place outside of this method then something else then
 273:             // a select query might have happened! (or instruct to keep cache)
 274:             $this->cache = array();
 275:             return FALSE;
 276:         }
 277:         return TRUE;
 278:     }
 279: 
 280:     /**
 281:      * Stores data from the writer in the cache under a specific key and cache tag.
 282:      * A cache tag is used to make sure the cache remains consistent. In most cases the cache tag
 283:      * will be the bean type, this makes sure queries associated with a certain reference type will
 284:      * never contain conflicting data.
 285:      * Why not use the cache tag as a key? Well
 286:      * we need to make sure the cache contents fits the key (and key is based on the cache values).
 287:      * Otherwise it would be possible to store two different result sets under the same key (the cache tag).
 288:      *
 289:      * In previous versions you could only store one key-entry, I have changed this to
 290:      * improve caching efficiency (issue #400).
 291:      *
 292:      * @param string $cacheTag cache tag (secondary key)
 293:      * @param string $key      key to store values under
 294:      * @param array  $values   content to be stored
 295:      *
 296:      * @return void
 297:      */
 298:     private function putResultInCache( $cacheTag, $key, $values )
 299:     {
 300:         if ( isset( $this->cache[$cacheTag] ) ) {
 301:             if ( count( $this->cache[$cacheTag] ) > $this->maxCacheSizePerType ) array_shift( $this->cache[$cacheTag] );
 302:         } else {
 303:             $this->cache[$cacheTag] = array();
 304:         }
 305: 
 306:         $this->cache[$cacheTag][$key] = $values;
 307:     }
 308: 
 309:     /**
 310:      * Creates an SQL snippet from a list of conditions of format:
 311:      *
 312:      * <code>
 313:      * array(
 314:      *    key => array(
 315:      *           value1, value2, value3 ....
 316:      *        )
 317:      * )
 318:      * </code>
 319:      *
 320:      * @param array  $conditions list of conditions
 321:      * @param array  $bindings   parameter bindings for SQL snippet
 322:      * @param string $addSql     additional SQL snippet to append to result
 323:      *
 324:      * @return string
 325:      */
 326:     private function makeSQLFromConditions( $conditions, &$bindings, $addSql = '' )
 327:     {
 328:         reset( $bindings );
 329:         $firstKey       = key( $bindings );
 330:         $paramTypeIsNum = ( is_numeric( $firstKey ) );
 331:         $counter        = 0;
 332: 
 333:         $sqlConditions = array();
 334:         foreach ( $conditions as $column => $values ) {
 335:             if ( !count( $values ) ) continue;
 336: 
 337:             $sql = $this->esc( $column );
 338:             $sql .= ' IN ( ';
 339: 
 340:             if ( !is_array( $values ) ) $values = array( $values );
 341: 
 342:             // If it's safe to skip bindings, do so...
 343:             if ( ctype_digit( implode( '', $values ) ) ) {
 344:                 $sql .= implode( ',', $values ) . ' ) ';
 345: 
 346:                 // only numeric, cant do much harm
 347:                 $sqlConditions[] = $sql;
 348:             } else {
 349: 
 350:                 if ( $paramTypeIsNum ) {
 351:                     $sql .= implode( ',', array_fill( 0, count( $values ), '?' ) ) . ' ) ';
 352: 
 353:                     array_unshift($sqlConditions, $sql);
 354: 
 355:                     foreach ( $values as $k => $v ) {
 356:                         $values[$k] = strval( $v );
 357: 
 358:                         array_unshift( $bindings, $v );
 359:                     }
 360:                 } else {
 361: 
 362:                     $slots = array();
 363: 
 364:                     foreach( $values as $k => $v ) {
 365:                         $slot            = ':slot'.$counter++;
 366:                         $slots[]         = $slot;
 367:                         $bindings[$slot] = strval( $v );
 368:                     }
 369: 
 370:                     $sql .= implode( ',', $slots ).' ) ';
 371:                     $sqlConditions[] = $sql;
 372:                 }
 373:             }
 374:         }
 375: 
 376:         $sql = '';
 377:         if ( is_array( $sqlConditions ) && count( $sqlConditions ) > 0 ) {
 378:             $sql = implode( ' AND ', $sqlConditions );
 379:             $sql = " WHERE ( $sql ) ";
 380: 
 381:             if ( $addSql ) $sql .= $addSql;
 382:         } elseif ( $addSql ) {
 383:             $sql = $addSql;
 384:         }
 385: 
 386:         return $sql;
 387:     }
 388: 
 389:     /**
 390:      * Returns the table names and column names for a relational query.
 391:      *
 392:      * @param string  $sourceType type of the source bean
 393:      * @param string  $destType   type of the bean you want to obtain using the relation
 394:      * @param boolean $noQuote    TRUE if you want to omit quotes
 395:      *
 396:      * @return array
 397:      */
 398:     private function getRelationalTablesAndColumns( $sourceType, $destType, $noQuote = FALSE )
 399:     {
 400:         $linkTable   = $this->esc( $this->getAssocTable( array( $sourceType, $destType ) ), $noQuote );
 401:         $sourceCol   = $this->esc( $sourceType . '_id', $noQuote );
 402: 
 403:         if ( $sourceType === $destType ) {
 404:             $destCol = $this->esc( $destType . '2_id', $noQuote );
 405:         } else {
 406:             $destCol = $this->esc( $destType . '_id', $noQuote );
 407:         }
 408: 
 409:         $sourceTable = $this->esc( $sourceType, $noQuote );
 410:         $destTable   = $this->esc( $destType, $noQuote );
 411: 
 412:         return array( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol );
 413:     }
 414: 
 415:     /**
 416:      * Given a type and a property name this method
 417:      * returns the foreign key map section associated with this pair.
 418:      *
 419:      * @param string $type     name of the type
 420:      * @param string $property name of the property
 421:      *
 422:      * @return array|NULL
 423:      */
 424:     protected function getForeignKeyForTypeProperty( $type, $property )
 425:     {
 426:         $property = $this->esc( $property, TRUE );
 427: 
 428:         try {
 429:             $map = $this->getKeyMapForType( $type );
 430:         } catch ( SQLException $e ) {
 431:             return NULL;
 432:         }
 433: 
 434:         foreach( $map as $key ) {
 435:             if ( $key['from'] === $property ) return $key;
 436:         }
 437:         return NULL;
 438:     }
 439: 
 440:     /**
 441:      * Returns the foreign key map (FKM) for a type.
 442:      * A foreign key map describes the foreign keys in a table.
 443:      * A FKM always has the same structure:
 444:      *
 445:      * <code>
 446:      * array(
 447:      *  'name'      => <name of the foreign key>
 448:      *    'from'      => <name of the column on the source table>
 449:      *    'table'     => <name of the target table>
 450:      *    'to'        => <name of the target column> (most of the time 'id')
 451:      *    'on_update' => <update rule: 'SET NULL','CASCADE' or 'RESTRICT'>
 452:      *    'on_delete' => <delete rule: 'SET NULL','CASCADE' or 'RESTRICT'>
 453:      * )
 454:      * </code>
 455:      *
 456:      * @note the keys in the result array are FKDLs, i.e. descriptive unique
 457:      * keys per source table. Also see: AQueryWriter::makeFKLabel for details.
 458:      *
 459:      * @param string $type the bean type you wish to obtain a key map of
 460:      *
 461:      * @return array
 462:      */
 463:     protected function getKeyMapForType( $type )
 464:     {
 465:         return array();
 466:     }
 467: 
 468:     /**
 469:      * This method makes a key for a foreign key description array.
 470:      * This key is a readable string unique for every source table.
 471:      * This uniform key is called the FKDL Foreign Key Description Label.
 472:      * Note that the source table is not part of the FKDL because
 473:      * this key is supposed to be 'per source table'. If you wish to
 474:      * include a source table, prefix the key with 'on_table_<SOURCE>_'.
 475:      *
 476:      * @param string $from  the column of the key in the source table
 477:      * @param string $type  the type (table) where the key points to
 478:      * @param string $to    the target column of the foreign key (mostly just 'id')
 479:      *
 480:      * @return string
 481:      */
 482:     protected function makeFKLabel($from, $type, $to)
 483:     {
 484:         return "from_{$from}_to_table_{$type}_col_{$to}";
 485:     }
 486: 
 487:     /**
 488:      * Returns an SQL Filter snippet for reading.
 489:      *
 490:      * @param string $type type of bean
 491:      *
 492:      * @return string
 493:      */
 494:     protected function getSQLFilterSnippet( $type )
 495:     {
 496:         $existingCols = array();
 497:         if (self::$flagSQLFilterSafeMode) {
 498:             $existingCols = $this->getColumns( $type );
 499:         }
 500: 
 501:         $sqlFilters = array();
 502:         if ( isset( self::$sqlFilters[QueryWriter::C_SQLFILTER_READ][$type] ) ) {
 503:             foreach( self::$sqlFilters[QueryWriter::C_SQLFILTER_READ][$type] as $property => $sqlFilter ) {
 504:                 if ( !self::$flagSQLFilterSafeMode || isset( $existingCols[$property] ) ) {
 505:                     $sqlFilters[] = $sqlFilter.' AS '.$property.' ';
 506:                 }
 507:             }
 508:         }
 509:         $sqlFilterStr = ( count($sqlFilters) ) ? ( ','.implode( ',', $sqlFilters ) ) : '';
 510:         return $sqlFilterStr;
 511:     }
 512: 
 513:     /**
 514:      * Generates a list of parameters (slots) for an SQL snippet.
 515:      * This method calculates the correct number of slots to insert in the
 516:      * SQL snippet and determines the correct type of slot. If the bindings
 517:      * array contains named parameters this method will return named ones and
 518:      * update the keys in the value list accordingly (that's why we use the &).
 519:      *
 520:      * If you pass an offset the bindings will be re-added to the value list.
 521:      * Some databases cant handle duplicate parameter names in queries.
 522:      *
 523:      * @param array   &$valueList    list of values to generate slots for (gets modified if needed)
 524:      * @param array   $otherBindings list of additional bindings
 525:      * @param integer $offset        start counter at...
 526:      *
 527:      * @return string
 528:      */
 529:     protected function getParametersForInClause( &$valueList, $otherBindings, $offset = 0 )
 530:     {
 531:         if ( is_array( $otherBindings ) && count( $otherBindings ) > 0 ) {
 532:             reset( $otherBindings );
 533: 
 534:             $key = key( $otherBindings );
 535: 
 536:             if ( !is_numeric($key) ) {
 537:                 $filler  = array();
 538:                 $newList = (!$offset) ? array() : $valueList;
 539:                 $counter = $offset;
 540: 
 541:                 foreach( $valueList as $value ) {
 542:                     $slot           = ':slot' . ( $counter++ );
 543:                     $filler[]       = $slot;
 544:                     $newList[$slot] = $value;
 545:                 }
 546: 
 547:                 // Change the keys!
 548:                 $valueList = $newList;
 549: 
 550:                 return implode( ',', $filler );
 551:             }
 552:         }
 553: 
 554:         return implode( ',', array_fill( 0, count( $valueList ), '?' ) );
 555:     }
 556: 
 557:     /**
 558:      * Adds a data type to the list of data types.
 559:      * Use this method to add a new column type definition to the writer.
 560:      * Used for UUID support.
 561:      *
 562:      * @param integer $dataTypeID    magic number constant assigned to this data type
 563:      * @param string  $SQLDefinition SQL column definition (i.e. INT(11))
 564:      *
 565:      * @return self
 566:      */
 567:     protected function addDataType( $dataTypeID, $SQLDefinition )
 568:     {
 569:         $this->typeno_sqltype[ $dataTypeID ] = $SQLDefinition;
 570:         $this->sqltype_typeno[ $SQLDefinition ] = $dataTypeID;
 571:         return $this;
 572:     }
 573: 
 574:     /**
 575:      * Returns the sql that should follow an insert statement.
 576:      *
 577:      * @param string $table name
 578:      *
 579:      * @return string
 580:      */
 581:     protected function getInsertSuffix( $table )
 582:     {
 583:         return '';
 584:     }
 585: 
 586:     /**
 587:      * Checks whether a value starts with zeros. In this case
 588:      * the value should probably be stored using a text datatype instead of a
 589:      * numerical type in order to preserve the zeros.
 590:      *
 591:      * @param string $value value to be checked.
 592:      *
 593:      * @return boolean
 594:      */
 595:     protected function startsWithZeros( $value )
 596:     {
 597:         $value = strval( $value );
 598: 
 599:         if ( strlen( $value ) > 1 && strpos( $value, '0' ) === 0 && strpos( $value, '0.' ) !== 0 ) {
 600:             return TRUE;
 601:         } else {
 602:             return FALSE;
 603:         }
 604:     }
 605: 
 606:     /**
 607:      * Inserts a record into the database using a series of insert columns
 608:      * and corresponding insertvalues. Returns the insert id.
 609:      *
 610:      * @param string $table         table to perform query on
 611:      * @param array  $insertcolumns columns to be inserted
 612:      * @param array  $insertvalues  values to be inserted
 613:      *
 614:      * @return integer
 615:      */
 616:     protected function insertRecord( $type, $insertcolumns, $insertvalues )
 617:     {
 618:         $default = $this->defaultValue;
 619:         $suffix  = $this->getInsertSuffix( $type );
 620:         $table   = $this->esc( $type );
 621: 
 622:         if ( count( $insertvalues ) > 0 && is_array( $insertvalues[0] ) && count( $insertvalues[0] ) > 0 ) {
 623: 
 624:             $insertSlots = array();
 625:             foreach ( $insertcolumns as $k => $v ) {
 626:                 $insertcolumns[$k] = $this->esc( $v );
 627: 
 628:                 if (isset(self::$sqlFilters['w'][$type][$v])) {
 629:                     $insertSlots[] = self::$sqlFilters['w'][$type][$v];
 630:                 } else {
 631:                     $insertSlots[] = '?';
 632:                 }
 633:             }
 634: 
 635:             $insertSQL = "INSERT INTO $table ( id, " . implode( ',', $insertcolumns ) . " ) VALUES
 636:             ( $default, " . implode( ',', $insertSlots ) . " ) $suffix";
 637: 
 638:             $ids = array();
 639:             foreach ( $insertvalues as $i => $insertvalue ) {
 640:                 $ids[] = $this->adapter->getCell( $insertSQL, $insertvalue, $i );
 641:             }
 642: 
 643:             $result = count( $ids ) === 1 ? array_pop( $ids ) : $ids;
 644:         } else {
 645:             $result = $this->adapter->getCell( "INSERT INTO $table (id) VALUES($default) $suffix" );
 646:         }
 647: 
 648:         if ( $suffix ) return $result;
 649: 
 650:         $last_id = $this->adapter->getInsertID();
 651: 
 652:         return $last_id;
 653:     }
 654: 
 655:     /**
 656:      * Checks table name or column name.
 657:      *
 658:      * @param string $table table string
 659:      *
 660:      * @return string
 661:      */
 662:     protected function check( $struct )
 663:     {
 664:         if ( !is_string( $struct ) || !preg_match( '/^[a-zA-Z0-9_]+$/', $struct ) ) {
 665:             throw new RedException( 'Identifier does not conform to RedBeanPHP security policies.' );
 666:         }
 667: 
 668:         return $struct;
 669:     }
 670: 
 671:     /**
 672:      * Checks whether the specified type (i.e. table) already exists in the database.
 673:      * Not part of the Object Database interface!
 674:      *
 675:      * @param string $table table name
 676:      *
 677:      * @return boolean
 678:      */
 679:     public function tableExists( $table )
 680:     {
 681:         $tables = $this->getTables();
 682: 
 683:         return in_array( $table, $tables );
 684:     }
 685: 
 686:     /**
 687:      * @see QueryWriter::glueSQLCondition
 688:      */
 689:     public function glueSQLCondition( $sql, $glue = NULL )
 690:     {
 691:         static $snippetCache = array();
 692: 
 693:         if ( trim( $sql ) === '' ) {
 694:             return $sql;
 695:         }
 696: 
 697:         $key = $glue . '|' . $sql;
 698: 
 699:         if ( isset( $snippetCache[$key] ) ) {
 700:             return $snippetCache[$key];
 701:         }
 702: 
 703:         $lsql = ltrim( $sql );
 704: 
 705:         if ( preg_match( '/^(INNER|LEFT|RIGHT|JOIN|AND|OR|WHERE|ORDER|GROUP|HAVING|LIMIT|OFFSET)\s+/i', $lsql ) ) {
 706:             if ( $glue === QueryWriter::C_GLUE_WHERE && stripos( $lsql, 'AND' ) === 0 ) {
 707:                 $snippetCache[$key] = ' WHERE ' . substr( $lsql, 3 );
 708:             } else {
 709:                 $snippetCache[$key] = $sql;
 710:             }
 711:         } else {
 712:             $snippetCache[$key] = ( ( $glue === QueryWriter::C_GLUE_AND ) ? ' AND ' : ' WHERE ') . $sql;
 713:         }
 714: 
 715:         return $snippetCache[$key];
 716:     }
 717: 
 718:     /**
 719:      * @see QueryWriter::glueLimitOne
 720:      */
 721:     public function glueLimitOne( $sql = '')
 722:     {
 723:         return ( strpos( strtoupper( $sql ), 'LIMIT' ) === FALSE ) ? ( $sql . ' LIMIT 1 ' ) : $sql;
 724:     }
 725: 
 726:     /**
 727:      * @see QueryWriter::esc
 728:      */
 729:     public function esc( $dbStructure, $dontQuote = FALSE )
 730:     {
 731:         $this->check( $dbStructure );
 732: 
 733:         return ( $dontQuote ) ? $dbStructure : $this->quoteCharacter . $dbStructure . $this->quoteCharacter;
 734:     }
 735: 
 736:     /**
 737:      * @see QueryWriter::addColumn
 738:      */
 739:     public function addColumn( $type, $column, $field )
 740:     {
 741:         $table  = $type;
 742:         $type   = $field;
 743:         $table  = $this->esc( $table );
 744:         $column = $this->esc( $column );
 745: 
 746:         $type = ( isset( $this->typeno_sqltype[$type] ) ) ? $this->typeno_sqltype[$type] : '';
 747: 
 748:         $this->adapter->exec( "ALTER TABLE $table ADD $column $type " );
 749:     }
 750: 
 751:     /**
 752:      * @see QueryWriter::updateRecord
 753:      */
 754:     public function updateRecord( $type, $updatevalues, $id = NULL )
 755:     {
 756:         $table = $type;
 757: 
 758:         if ( !$id ) {
 759:             $insertcolumns = $insertvalues = array();
 760: 
 761:             foreach ( $updatevalues as $pair ) {
 762:                 $insertcolumns[] = $pair['property'];
 763:                 $insertvalues[]  = $pair['value'];
 764:             }
 765: 
 766:             //Otherwise psql returns string while MySQL/SQLite return numeric causing problems with additions (array_diff)
 767:             return (string) $this->insertRecord( $table, $insertcolumns, array( $insertvalues ) );
 768:         }
 769: 
 770:         if ( $id && !count( $updatevalues ) ) {
 771:             return $id;
 772:         }
 773: 
 774:         $table = $this->esc( $table );
 775:         $sql   = "UPDATE $table SET ";
 776: 
 777:         $p = $v = array();
 778: 
 779:         foreach ( $updatevalues as $uv ) {
 780: 
 781:             if ( isset( self::$sqlFilters['w'][$type][$uv['property']] ) ) {
 782:                 $p[] = " {$this->esc( $uv["property"] )} = ". self::$sqlFilters['w'][$type][$uv['property']];
 783:             } else {
 784:                 $p[] = " {$this->esc( $uv["property"] )} = ? ";
 785:             }
 786: 
 787:             $v[] = $uv['value'];
 788:         }
 789: 
 790:         $sql .= implode( ',', $p ) . ' WHERE id = ? ';
 791: 
 792:         $v[] = $id;
 793: 
 794:         $this->adapter->exec( $sql, $v );
 795: 
 796:         return $id;
 797:     }
 798: 
 799:     /**
 800:      * @see QueryWriter::writeJoin
 801:      */
 802:     public function writeJoin( $type, $targetType, $leftRight = 'LEFT' )
 803:     {
 804:         if ( $leftRight !== 'LEFT' && $leftRight !== 'RIGHT' && $leftRight !== 'INNER' )
 805:             throw new RedException( 'Invalid JOIN.' );
 806: 
 807:         $table = $this->esc( $type );
 808:         $targetTable = $this->esc( $targetType );
 809:         $field = $this->esc( $targetType, TRUE );
 810:         return " {$leftRight} JOIN {$targetTable} ON {$targetTable}.id = {$table}.{$field}_id ";
 811:     }
 812: 
 813:     /**
 814:      * @see QueryWriter::queryRecord
 815:      */
 816:     public function queryRecord( $type, $conditions = array(), $addSql = NULL, $bindings = array() )
 817:     {
 818:         $addSql = $this->glueSQLCondition( $addSql, ( count($conditions) > 0) ? QueryWriter::C_GLUE_AND : NULL );
 819: 
 820:         $key = NULL;
 821:         if ( $this->flagUseCache ) {
 822:             $key = $this->getCacheKey( array( $conditions, $addSql, $bindings, 'select' ) );
 823: 
 824:             if ( $cached = $this->getCached( $type, $key ) ) {
 825:                 return $cached;
 826:             }
 827:         }
 828: 
 829:         $table = $this->esc( $type );
 830: 
 831:         $sqlFilterStr = '';
 832:         if ( count( self::$sqlFilters ) ) {
 833:             $sqlFilterStr = $this->getSQLFilterSnippet( $type );
 834:         }
 835: 
 836:         $sql   = $this->makeSQLFromConditions( $conditions, $bindings, $addSql );
 837: 
 838:         $fieldSelection = ( self::$flagNarrowFieldMode ) ? "{$table}.*" : '*';
 839:         $sql   = "SELECT {$fieldSelection} {$sqlFilterStr} FROM {$table} {$sql} -- keep-cache";
 840: 
 841:         $rows  = $this->adapter->get( $sql, $bindings );
 842: 
 843:         if ( $this->flagUseCache && $key ) {
 844:             $this->putResultInCache( $type, $key, $rows );
 845:         }
 846: 
 847:         return $rows;
 848:     }
 849: 
 850:     /**
 851:      * @see QueryWriter::queryRecordWithCursor
 852:      */
 853:     public function queryRecordWithCursor( $type, $addSql = NULL, $bindings = array() )
 854:     {
 855:         $sql = $this->glueSQLCondition( $addSql, NULL );
 856:         $table = $this->esc( $type );
 857:         $sql   = "SELECT {$table}.* FROM {$table} {$sql}";
 858:         return $this->adapter->getCursor( $sql, $bindings );
 859:     }
 860: 
 861:     /**
 862:      * @see QueryWriter::queryRecordRelated
 863:      */
 864:     public function queryRecordRelated( $sourceType, $destType, $linkIDs, $addSql = '', $bindings = array() )
 865:     {
 866:         $addSql = $this->glueSQLCondition( $addSql, QueryWriter::C_GLUE_WHERE );
 867: 
 868:         list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
 869: 
 870:         $key = $this->getCacheKey( array( $sourceType, $destType, implode( ',', $linkIDs ), $addSql, $bindings ) );
 871: 
 872:         if ( $this->flagUseCache && $cached = $this->getCached( $destType, $key ) ) {
 873:             return $cached;
 874:         }
 875: 
 876:         $inClause = $this->getParametersForInClause( $linkIDs, $bindings );
 877: 
 878:         $sqlFilterStr = '';
 879:         if ( count( self::$sqlFilters ) ) {
 880:             $sqlFilterStr = $this->getSQLFilterSnippet( $destType );
 881:         }
 882: 
 883:         if ( $sourceType === $destType ) {
 884:             $inClause2 = $this->getParametersForInClause( $linkIDs, $bindings, count( $bindings ) ); //for some databases
 885:             $sql = "
 886:             SELECT
 887:                 {$destTable}.* {$sqlFilterStr} ,
 888:                 COALESCE(
 889:                 NULLIF({$linkTable}.{$sourceCol}, {$destTable}.id),
 890:                 NULLIF({$linkTable}.{$destCol}, {$destTable}.id)) AS linked_by
 891:             FROM {$linkTable}
 892:             INNER JOIN {$destTable} ON
 893:             ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ($inClause) ) OR
 894:             ( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} IN ($inClause2) )
 895:             {$addSql}
 896:             -- keep-cache";
 897: 
 898:             $linkIDs = array_merge( $linkIDs, $linkIDs );
 899:         } else {
 900:             $sql = "
 901:             SELECT
 902:                 {$destTable}.* {$sqlFilterStr},
 903:                 {$linkTable}.{$sourceCol} AS linked_by
 904:             FROM {$linkTable}
 905:             INNER JOIN {$destTable} ON
 906:             ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ($inClause) )
 907:             {$addSql}
 908:             -- keep-cache";
 909:         }
 910: 
 911:         $bindings = array_merge( $linkIDs, $bindings );
 912: 
 913:         $rows = $this->adapter->get( $sql, $bindings );
 914: 
 915:         $this->putResultInCache( $destType, $key, $rows );
 916: 
 917:         return $rows;
 918:     }
 919: 
 920:     /**
 921:      * @see QueryWriter::queryRecordLink
 922:      */
 923:     public function queryRecordLink( $sourceType, $destType, $sourceID, $destID )
 924:     {
 925:         list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
 926: 
 927:         $key = $this->getCacheKey( array( $sourceType, $destType, $sourceID, $destID ) );
 928: 
 929:         if ( $this->flagUseCache && $cached = $this->getCached( $linkTable, $key ) ) {
 930:             return $cached;
 931:         }
 932: 
 933:         $sqlFilterStr = '';
 934:         if ( count( self::$sqlFilters ) ) {
 935:             $sqlFilterStr = $this->getSQLFilterSnippet( $destType );
 936:         }
 937: 
 938:         if ( $sourceTable === $destTable ) {
 939:             $sql = "SELECT {$linkTable}.* {$sqlFilterStr} FROM {$linkTable}
 940:                 WHERE ( {$sourceCol} = ? AND {$destCol} = ? ) OR
 941:                  ( {$destCol} = ? AND {$sourceCol} = ? ) -- keep-cache";
 942:             $row = $this->adapter->getRow( $sql, array( $sourceID, $destID, $sourceID, $destID ) );
 943:         } else {
 944:             $sql = "SELECT {$linkTable}.* {$sqlFilterStr} FROM {$linkTable}
 945:                 WHERE {$sourceCol} = ? AND {$destCol} = ? -- keep-cache";
 946:             $row = $this->adapter->getRow( $sql, array( $sourceID, $destID ) );
 947:         }
 948: 
 949:         $this->putResultInCache( $linkTable, $key, $row );
 950: 
 951:         return $row;
 952:     }
 953: 
 954:     /**
 955:      * @see QueryWriter::queryTagged
 956:      */
 957:     public function queryTagged( $type, $tagList, $all = FALSE, $addSql = '', $bindings = array() )
 958:     {
 959:         $assocType = $this->getAssocTable( array( $type, 'tag' ) );
 960:         $assocTable = $this->esc( $assocType );
 961:         $assocField = $type . '_id';
 962:         $table = $this->esc( $type );
 963:         $slots = implode( ',', array_fill( 0, count( $tagList ), '?' ) );
 964:         $score = ( $all ) ? count( $tagList ) : 1;
 965: 
 966:         $sql = "
 967:             SELECT {$table}.*, count({$table}.id) FROM {$table}
 968:             INNER JOIN {$assocTable} ON {$assocField} = {$table}.id
 969:             INNER JOIN tag ON {$assocTable}.tag_id = tag.id
 970:             WHERE tag.title IN ({$slots})
 971:             GROUP BY {$table}.id
 972:             HAVING count({$table}.id) >= ?
 973:             {$addSql}
 974:         ";
 975: 
 976:         $bindings = array_merge( $tagList, array( $score ), $bindings );
 977:         $rows = $this->adapter->get( $sql, $bindings );
 978:         return $rows;
 979:     }
 980: 
 981:     /**
 982:      * @see QueryWriter::queryRecordCount
 983:      */
 984:     public function queryRecordCount( $type, $conditions = array(), $addSql = NULL, $bindings = array() )
 985:     {
 986:         $addSql = $this->glueSQLCondition( $addSql );
 987: 
 988:         $table  = $this->esc( $type );
 989: 
 990:         $this->updateCache(); //check if cache chain has been broken
 991: 
 992:         $sql    = $this->makeSQLFromConditions( $conditions, $bindings, $addSql );
 993:         $sql    = "SELECT COUNT(*) FROM {$table} {$sql} -- keep-cache";
 994: 
 995:         return (int) $this->adapter->getCell( $sql, $bindings );
 996:     }
 997: 
 998:     /**
 999:      * @see QueryWriter::queryRecordCountRelated
1000:      */
1001:     public function queryRecordCountRelated( $sourceType, $destType, $linkID, $addSql = '', $bindings = array() )
1002:     {
1003:         list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
1004: 
1005:         $this->updateCache(); //check if cache chain has been broken
1006: 
1007:         if ( $sourceType === $destType ) {
1008:             $sql = "
1009:             SELECT COUNT(*) FROM {$linkTable}
1010:             INNER JOIN {$destTable} ON
1011:             ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} = ? ) OR
1012:             ( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} = ? )
1013:             {$addSql}
1014:             -- keep-cache";
1015: 
1016:             $bindings = array_merge( array( $linkID, $linkID ), $bindings );
1017:         } else {
1018:             $sql = "
1019:             SELECT COUNT(*) FROM {$linkTable}
1020:             INNER JOIN {$destTable} ON
1021:             ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} = ? )
1022:             {$addSql}
1023:             -- keep-cache";
1024: 
1025:             $bindings = array_merge( array( $linkID ), $bindings );
1026:         }
1027: 
1028:         return (int) $this->adapter->getCell( $sql, $bindings );
1029:     }
1030: 
1031:     /**
1032:      * @see QueryWriter::deleteRecord
1033:      */
1034:     public function deleteRecord( $type, $conditions = array(), $addSql = NULL, $bindings = array() )
1035:     {
1036:         $addSql = $this->glueSQLCondition( $addSql );
1037: 
1038:         $table  = $this->esc( $type );
1039: 
1040:         $sql    = $this->makeSQLFromConditions( $conditions, $bindings, $addSql );
1041:         $sql    = "DELETE FROM {$table} {$sql}";
1042: 
1043:         $this->adapter->exec( $sql, $bindings );
1044:     }
1045: 
1046:     /**
1047:      * @see QueryWriter::deleteRelations
1048:      */
1049:     public function deleteRelations( $sourceType, $destType, $sourceID )
1050:     {
1051:         list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
1052: 
1053:         if ( $sourceTable === $destTable ) {
1054:             $sql = "DELETE FROM {$linkTable}
1055:                 WHERE ( {$sourceCol} = ? ) OR
1056:                 ( {$destCol} = ?  )
1057:             ";
1058: 
1059:             $this->adapter->exec( $sql, array( $sourceID, $sourceID ) );
1060:         } else {
1061:             $sql = "DELETE FROM {$linkTable}
1062:                 WHERE {$sourceCol} = ? ";
1063: 
1064:             $this->adapter->exec( $sql, array( $sourceID ) );
1065:         }
1066:     }
1067: 
1068:     /**
1069:      * @see QueryWriter::widenColumn
1070:      */
1071:     public function widenColumn( $type, $property, $dataType )
1072:     {
1073:         if ( !isset($this->typeno_sqltype[$dataType]) ) return FALSE;
1074: 
1075:         $table   = $this->esc( $type );
1076:         $column  = $this->esc( $property );
1077: 
1078:         $newType = $this->typeno_sqltype[$dataType];
1079: 
1080:         $this->adapter->exec( "ALTER TABLE $table CHANGE $column $column $newType " );
1081: 
1082:         return TRUE;
1083:     }
1084: 
1085:     /**
1086:      * @see QueryWriter::wipe
1087:      */
1088:     public function wipe( $type )
1089:     {
1090:         $table = $this->esc( $type );
1091: 
1092:         $this->adapter->exec( "TRUNCATE $table " );
1093:     }
1094: 
1095:     /**
1096:      * @see QueryWriter::renameAssocTable
1097:      */
1098:     public function renameAssocTable( $from, $to = NULL )
1099:     {
1100:         self::renameAssociation( $from, $to );
1101:     }
1102: 
1103:     /**
1104:      * @see QueryWriter::getAssocTable
1105:      */
1106:     public function getAssocTable( $types )
1107:     {
1108:         return self::getAssocTableFormat( $types );
1109:     }
1110: 
1111:     /**
1112:      * Turns caching on or off. Default: off.
1113:      * If caching is turned on retrieval queries fired after eachother will
1114:      * use a result row cache.
1115:      *
1116:      * @param boolean
1117:      *
1118:      * @return void
1119:      */
1120:     public function setUseCache( $yesNo )
1121:     {
1122:         $this->flushCache();
1123: 
1124:         $this->flagUseCache = (bool) $yesNo;
1125:     }
1126: 
1127:     /**
1128:      * Flushes the Query Writer Cache.
1129:      * Clears the internal query cache array and returns its overall
1130:      * size.
1131:      *
1132:      * @return integer
1133:      */
1134:     public function flushCache( $newMaxCacheSizePerType = NULL )
1135:     {
1136:         if ( !is_null( $newMaxCacheSizePerType ) && $newMaxCacheSizePerType > 0 ) {
1137:             $this->maxCacheSizePerType = $newMaxCacheSizePerType;
1138:         }
1139:         $count = count( $this->cache, COUNT_RECURSIVE );
1140:         $this->cache = array();
1141:         return $count;
1142:     }
1143: 
1144:     /**
1145:      * @deprecated Use esc() instead.
1146:      *
1147:      * @param string  $column   column to be escaped
1148:      * @param boolean $noQuotes omit quotes
1149:      *
1150:      * @return string
1151:      */
1152:     public function safeColumn( $column, $noQuotes = FALSE )
1153:     {
1154:         return $this->esc( $column, $noQuotes );
1155:     }
1156: 
1157:     /**
1158:      * @deprecated Use esc() instead.
1159:      *
1160:      * @param string  $table    table to be escaped
1161:      * @param boolean $noQuotes omit quotes
1162:      *
1163:      * @return string
1164:      */
1165:     public function safeTable( $table, $noQuotes = FALSE )
1166:     {
1167:         return $this->esc( $table, $noQuotes );
1168:     }
1169: 
1170:     /**
1171:      * @see QueryWriter::inferFetchType
1172:      */
1173:     public function inferFetchType( $type, $property )
1174:     {
1175:         $type = $this->esc( $type, TRUE );
1176:         $field = $this->esc( $property, TRUE ) . '_id';
1177:         $keys = $this->getKeyMapForType( $type );
1178: 
1179:         foreach( $keys as $key ) {
1180:             if (
1181:                 $key['from'] === $field
1182:             ) return $key['table'];
1183:         }
1184:         return NULL;
1185:     }
1186: 
1187:     /**
1188:      * @see QueryWriter::addUniqueConstraint
1189:      */
1190:     public function addUniqueIndex( $type, $properties )
1191:     {
1192:         return $this->addUniqueConstraint( $type, $properties );
1193:     }
1194: }
1195: