3 namespace RedBeanPHP\QueryWriter;
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;
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.
18 * @file RedBeanPHP/QueryWriter/AQueryWriter.php
19 * @author Gabor de Mooij and the RedBeanPHP Community
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.
27 abstract class AQueryWriter
32 private static $sqlFilters = array();
37 private static $flagSQLFilterSafeMode = false;
42 private static $flagNarrowFieldMode = true;
47 public static $renames = array();
57 protected $defaultValue = 'NULL';
62 protected $quoteCharacter = '';
67 protected $flagUseCache = TRUE;
72 protected $cache = array();
77 protected $maxCacheSizePerType = 20;
82 public $typeno_sqltype = array();
85 * Checks whether a number can be treated like an int.
87 * @param string $value string representation of a certain value
91 public static function canBeTreatedAsInt( $value )
93 return (bool) ( strval( $value ) === strval( intval( $value ) ) );
97 * @see QueryWriter::getAssocTableFormat
99 public static function getAssocTableFormat( $types )
103 $assoc = implode( '_', $types );
105 return ( isset( self::$renames[$assoc] ) ) ? self::$renames[$assoc] : $assoc;
109 * @see QueryWriter::renameAssociation
111 public static function renameAssociation( $from, $to = NULL )
113 if ( is_array( $from ) ) {
114 foreach ( $from as $key => $value ) self::$renames[$key] = $value;
119 self::$renames[$from] = $to;
123 * Globally available service method for RedBeanPHP.
124 * Converts a camel cased string to a snake cased string.
126 * @param string $camel camelCased string to converty to snake case
130 public static function camelsSnake( $camel )
132 return strtolower( preg_replace( '/(?<=[a-z])([A-Z])|([A-Z])(?=[a-z])/', '_$1$2', $camel ) );
140 public static function clearRenames()
142 self::$renames = array();
146 * Toggles 'Narrow Field Mode'.
147 * In Narrow Field mode the queryRecord method will
148 * narrow its selection field to
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.
161 * @param boolean $narrowField TRUE = Narrow Field FALSE = Wide Field
165 public static function setNarrowFieldMode( $narrowField )
167 self::$flagNarrowFieldMode = (boolean) $narrowField;
172 * This is a lowlevel method to set the SQL filter array.
173 * The format of this array is:
177 * '<MODE, i.e. 'r' for read, 'w' for write>' => array(
178 * '<TABLE NAME>' => array(
179 * '<COLUMN NAME>' => '<SQL>'
189 * QueryWriter::C_SQLFILTER_READ => array(
191 * 'title' => ' LOWER(book.title) '
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().
201 * @param array list of filters to set
205 public static function setSQLFilters( $sqlFilters, $safeMode = false )
207 self::$flagSQLFilterSafeMode = (boolean) $safeMode;
208 self::$sqlFilters = $sqlFilters;
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().
219 public static function getSQLFilters()
221 return self::$sqlFilters;
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.
229 * @param array $keyValues key-value to generate key for
233 private function getCacheKey( $keyValues )
235 return json_encode( $keyValues );
239 * Returns the values associated with the provided cache tag and key.
241 * @param string $cacheTag cache tag to use for lookup
242 * @param string $key key to use for lookup
246 private function getCached( $cacheTag, $key )
248 $sql = $this->adapter->getSQL();
250 if ($this->updateCache()) {
251 if ( isset( $this->cache[$cacheTag][$key] ) ) {
252 return $this->cache[$cacheTag][$key];
260 * Checks if the previous query had a keep-cache tag.
261 * If so, the cache will persist, otherwise the cache will be flushed.
263 * Returns TRUE if the cache will remain and FALSE if a flush has
268 private function updateCache()
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();
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).
289 * In previous versions you could only store one key-entry, I have changed this to
290 * improve caching efficiency (issue #400).
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
298 private function putResultInCache( $cacheTag, $key, $values )
300 if ( isset( $this->cache[$cacheTag] ) ) {
301 if ( count( $this->cache[$cacheTag] ) > $this->maxCacheSizePerType ) array_shift( $this->cache[$cacheTag] );
303 $this->cache[$cacheTag] = array();
306 $this->cache[$cacheTag][$key] = $values;
310 * Creates an SQL snippet from a list of conditions of format:
315 * value1, value2, value3 ....
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
326 private function makeSQLFromConditions( $conditions, &$bindings, $addSql = '' )
329 $firstKey = key( $bindings );
330 $paramTypeIsNum = ( is_numeric( $firstKey ) );
333 $sqlConditions = array();
334 foreach ( $conditions as $column => $values ) {
335 if ( !count( $values ) ) continue;
337 $sql = $this->esc( $column );
340 if ( !is_array( $values ) ) $values = array( $values );
342 if ( $paramTypeIsNum ) {
343 $sql .= implode( ',', array_fill( 0, count( $values ), '?' ) ) . ' ) ';
345 array_unshift($sqlConditions, $sql);
347 foreach ( $values as $k => $v ) {
348 $values[$k] = strval( $v );
350 array_unshift( $bindings, $v );
356 foreach( $values as $k => $v ) {
357 $slot = ':slot'.$counter++;
359 $bindings[$slot] = strval( $v );
362 $sql .= implode( ',', $slots ).' ) ';
363 $sqlConditions[] = $sql;
368 if ( is_array( $sqlConditions ) && count( $sqlConditions ) > 0 ) {
369 $sql = implode( ' AND ', $sqlConditions );
370 $sql = " WHERE ( $sql ) ";
372 if ( $addSql ) $sql .= $addSql;
373 } elseif ( $addSql ) {
381 * Returns the table names and column names for a relational query.
383 * @param string $sourceType type of the source bean
384 * @param string $destType type of the bean you want to obtain using the relation
385 * @param boolean $noQuote TRUE if you want to omit quotes
389 private function getRelationalTablesAndColumns( $sourceType, $destType, $noQuote = FALSE )
391 $linkTable = $this->esc( $this->getAssocTable( array( $sourceType, $destType ) ), $noQuote );
392 $sourceCol = $this->esc( $sourceType . '_id', $noQuote );
394 if ( $sourceType === $destType ) {
395 $destCol = $this->esc( $destType . '2_id', $noQuote );
397 $destCol = $this->esc( $destType . '_id', $noQuote );
400 $sourceTable = $this->esc( $sourceType, $noQuote );
401 $destTable = $this->esc( $destType, $noQuote );
403 return array( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol );
407 * Given a type and a property name this method
408 * returns the foreign key map section associated with this pair.
410 * @param string $type name of the type
411 * @param string $property name of the property
415 protected function getForeignKeyForTypeProperty( $type, $property )
417 $property = $this->esc( $property, TRUE );
420 $map = $this->getKeyMapForType( $type );
421 } catch ( SQLException $e ) {
425 foreach( $map as $key ) {
426 if ( $key['from'] === $property ) return $key;
432 * Returns the foreign key map (FKM) for a type.
433 * A foreign key map describes the foreign keys in a table.
434 * A FKM always has the same structure:
438 * 'name' => <name of the foreign key>
439 * 'from' => <name of the column on the source table>
440 * 'table' => <name of the target table>
441 * 'to' => <name of the target column> (most of the time 'id')
442 * 'on_update' => <update rule: 'SET NULL','CASCADE' or 'RESTRICT'>
443 * 'on_delete' => <delete rule: 'SET NULL','CASCADE' or 'RESTRICT'>
447 * @note the keys in the result array are FKDLs, i.e. descriptive unique
448 * keys per source table. Also see: AQueryWriter::makeFKLabel for details.
450 * @param string $type the bean type you wish to obtain a key map of
454 protected function getKeyMapForType( $type )
460 * This method makes a key for a foreign key description array.
461 * This key is a readable string unique for every source table.
462 * This uniform key is called the FKDL Foreign Key Description Label.
463 * Note that the source table is not part of the FKDL because
464 * this key is supposed to be 'per source table'. If you wish to
465 * include a source table, prefix the key with 'on_table_<SOURCE>_'.
467 * @param string $from the column of the key in the source table
468 * @param string $type the type (table) where the key points to
469 * @param string $to the target column of the foreign key (mostly just 'id')
473 protected function makeFKLabel($from, $type, $to)
475 return "from_{$from}_to_table_{$type}_col_{$to}";
479 * Returns an SQL Filter snippet for reading.
481 * @param string $type type of bean
485 protected function getSQLFilterSnippet( $type )
487 $existingCols = array();
488 if (self::$flagSQLFilterSafeMode) {
489 $existingCols = $this->getColumns( $type );
492 $sqlFilters = array();
493 if ( isset( self::$sqlFilters[QueryWriter::C_SQLFILTER_READ][$type] ) ) {
494 foreach( self::$sqlFilters[QueryWriter::C_SQLFILTER_READ][$type] as $property => $sqlFilter ) {
495 if ( !self::$flagSQLFilterSafeMode || isset( $existingCols[$property] ) ) {
496 $sqlFilters[] = $sqlFilter.' AS '.$property.' ';
500 $sqlFilterStr = ( count($sqlFilters) ) ? ( ','.implode( ',', $sqlFilters ) ) : '';
501 return $sqlFilterStr;
505 * Generates a list of parameters (slots) for an SQL snippet.
506 * This method calculates the correct number of slots to insert in the
507 * SQL snippet and determines the correct type of slot. If the bindings
508 * array contains named parameters this method will return named ones and
509 * update the keys in the value list accordingly (that's why we use the &).
511 * If you pass an offset the bindings will be re-added to the value list.
512 * Some databases cant handle duplicate parameter names in queries.
514 * @param array &$valueList list of values to generate slots for (gets modified if needed)
515 * @param array $otherBindings list of additional bindings
516 * @param integer $offset start counter at...
520 protected function getParametersForInClause( &$valueList, $otherBindings, $offset = 0 )
522 if ( is_array( $otherBindings ) && count( $otherBindings ) > 0 ) {
523 reset( $otherBindings );
525 $key = key( $otherBindings );
527 if ( !is_numeric($key) ) {
529 $newList = (!$offset) ? array() : $valueList;
532 foreach( $valueList as $value ) {
533 $slot = ':slot' . ( $counter++ );
535 $newList[$slot] = $value;
539 $valueList = $newList;
541 return implode( ',', $filler );
545 return implode( ',', array_fill( 0, count( $valueList ), '?' ) );
549 * Adds a data type to the list of data types.
550 * Use this method to add a new column type definition to the writer.
551 * Used for UUID support.
553 * @param integer $dataTypeID magic number constant assigned to this data type
554 * @param string $SQLDefinition SQL column definition (i.e. INT(11))
558 protected function addDataType( $dataTypeID, $SQLDefinition )
560 $this->typeno_sqltype[ $dataTypeID ] = $SQLDefinition;
561 $this->sqltype_typeno[ $SQLDefinition ] = $dataTypeID;
566 * Returns the sql that should follow an insert statement.
568 * @param string $table name
572 protected function getInsertSuffix( $table )
578 * Checks whether a value starts with zeros. In this case
579 * the value should probably be stored using a text datatype instead of a
580 * numerical type in order to preserve the zeros.
582 * @param string $value value to be checked.
586 protected function startsWithZeros( $value )
588 $value = strval( $value );
590 if ( strlen( $value ) > 1 && strpos( $value, '0' ) === 0 && strpos( $value, '0.' ) !== 0 ) {
598 * Inserts a record into the database using a series of insert columns
599 * and corresponding insertvalues. Returns the insert id.
601 * @param string $table table to perform query on
602 * @param array $insertcolumns columns to be inserted
603 * @param array $insertvalues values to be inserted
607 protected function insertRecord( $type, $insertcolumns, $insertvalues )
609 $default = $this->defaultValue;
610 $suffix = $this->getInsertSuffix( $type );
611 $table = $this->esc( $type );
613 if ( count( $insertvalues ) > 0 && is_array( $insertvalues[0] ) && count( $insertvalues[0] ) > 0 ) {
615 $insertSlots = array();
616 foreach ( $insertcolumns as $k => $v ) {
617 $insertcolumns[$k] = $this->esc( $v );
619 if (isset(self::$sqlFilters['w'][$type][$v])) {
620 $insertSlots[] = self::$sqlFilters['w'][$type][$v];
622 $insertSlots[] = '?';
626 $insertSQL = "INSERT INTO $table ( id, " . implode( ',', $insertcolumns ) . " ) VALUES
627 ( $default, " . implode( ',', $insertSlots ) . " ) $suffix";
630 foreach ( $insertvalues as $i => $insertvalue ) {
631 $ids[] = $this->adapter->getCell( $insertSQL, $insertvalue, $i );
634 $result = count( $ids ) === 1 ? array_pop( $ids ) : $ids;
636 $result = $this->adapter->getCell( "INSERT INTO $table (id) VALUES($default) $suffix" );
639 if ( $suffix ) return $result;
641 $last_id = $this->adapter->getInsertID();
647 * Checks table name or column name.
649 * @param string $table table string
653 protected function check( $struct )
655 if ( !is_string( $struct ) || !preg_match( '/^[a-zA-Z0-9_]+$/', $struct ) ) {
656 throw new RedException( 'Identifier does not conform to RedBeanPHP security policies.' );
663 * Checks whether the specified type (i.e. table) already exists in the database.
664 * Not part of the Object Database interface!
666 * @param string $table table name
670 public function tableExists( $table )
672 $tables = $this->getTables();
674 return in_array( $table, $tables );
678 * @see QueryWriter::glueSQLCondition
680 public function glueSQLCondition( $sql, $glue = NULL )
682 static $snippetCache = array();
684 if ( trim( $sql ) === '' ) {
688 $key = $glue . '|' . $sql;
690 if ( isset( $snippetCache[$key] ) ) {
691 return $snippetCache[$key];
694 $lsql = ltrim( $sql );
696 if ( preg_match( '/^(INNER|LEFT|RIGHT|JOIN|AND|OR|WHERE|ORDER|GROUP|HAVING|LIMIT|OFFSET)\s+/i', $lsql ) ) {
697 if ( $glue === QueryWriter::C_GLUE_WHERE && stripos( $lsql, 'AND' ) === 0 ) {
698 $snippetCache[$key] = ' WHERE ' . substr( $lsql, 3 );
700 $snippetCache[$key] = $sql;
703 $snippetCache[$key] = ( ( $glue === QueryWriter::C_GLUE_AND ) ? ' AND ' : ' WHERE ') . $sql;
706 return $snippetCache[$key];
710 * @see QueryWriter::glueLimitOne
712 public function glueLimitOne( $sql = '')
714 return ( strpos( strtoupper( $sql ), 'LIMIT' ) === FALSE ) ? ( $sql . ' LIMIT 1 ' ) : $sql;
718 * @see QueryWriter::esc
720 public function esc( $dbStructure, $dontQuote = FALSE )
722 $this->check( $dbStructure );
724 return ( $dontQuote ) ? $dbStructure : $this->quoteCharacter . $dbStructure . $this->quoteCharacter;
728 * @see QueryWriter::addColumn
730 public function addColumn( $type, $column, $field )
734 $table = $this->esc( $table );
735 $column = $this->esc( $column );
737 $type = ( isset( $this->typeno_sqltype[$type] ) ) ? $this->typeno_sqltype[$type] : '';
739 $this->adapter->exec( "ALTER TABLE $table ADD $column $type " );
743 * @see QueryWriter::updateRecord
745 public function updateRecord( $type, $updatevalues, $id = NULL )
750 $insertcolumns = $insertvalues = array();
752 foreach ( $updatevalues as $pair ) {
753 $insertcolumns[] = $pair['property'];
754 $insertvalues[] = $pair['value'];
757 //Otherwise psql returns string while MySQL/SQLite return numeric causing problems with additions (array_diff)
758 return (string) $this->insertRecord( $table, $insertcolumns, array( $insertvalues ) );
761 if ( $id && !count( $updatevalues ) ) {
765 $table = $this->esc( $table );
766 $sql = "UPDATE $table SET ";
770 foreach ( $updatevalues as $uv ) {
772 if ( isset( self::$sqlFilters['w'][$type][$uv['property']] ) ) {
773 $p[] = " {$this->esc( $uv["property"] )} = ". self::$sqlFilters['w'][$type][$uv['property']];
775 $p[] = " {$this->esc( $uv["property"] )} = ? ";
781 $sql .= implode( ',', $p ) . ' WHERE id = ? ';
785 $this->adapter->exec( $sql, $v );
791 * @see QueryWriter::writeJoin
793 public function writeJoin( $type, $targetType, $leftRight = 'LEFT' )
795 if ( $leftRight !== 'LEFT' && $leftRight !== 'RIGHT' && $leftRight !== 'INNER' )
796 throw new RedException( 'Invalid JOIN.' );
798 $table = $this->esc( $type );
799 $targetTable = $this->esc( $targetType );
800 $field = $this->esc( $targetType, TRUE );
801 return " {$leftRight} JOIN {$targetTable} ON {$targetTable}.id = {$table}.{$field}_id ";
805 * @see QueryWriter::queryRecord
807 public function queryRecord( $type, $conditions = array(), $addSql = NULL, $bindings = array() )
809 $addSql = $this->glueSQLCondition( $addSql, ( count($conditions) > 0) ? QueryWriter::C_GLUE_AND : NULL );
812 if ( $this->flagUseCache ) {
813 $key = $this->getCacheKey( array( $conditions, $addSql, $bindings, 'select' ) );
815 if ( $cached = $this->getCached( $type, $key ) ) {
820 $table = $this->esc( $type );
823 if ( count( self::$sqlFilters ) ) {
824 $sqlFilterStr = $this->getSQLFilterSnippet( $type );
827 $sql = $this->makeSQLFromConditions( $conditions, $bindings, $addSql );
829 $fieldSelection = ( self::$flagNarrowFieldMode ) ? "{$table}.*" : '*';
830 $sql = "SELECT {$fieldSelection} {$sqlFilterStr} FROM {$table} {$sql} -- keep-cache";
832 $rows = $this->adapter->get( $sql, $bindings );
834 if ( $this->flagUseCache && $key ) {
835 $this->putResultInCache( $type, $key, $rows );
842 * @see QueryWriter::queryRecordWithCursor
844 public function queryRecordWithCursor( $type, $addSql = NULL, $bindings = array() )
846 $sql = $this->glueSQLCondition( $addSql, NULL );
847 $table = $this->esc( $type );
848 $sql = "SELECT {$table}.* FROM {$table} {$sql}";
849 return $this->adapter->getCursor( $sql, $bindings );
853 * @see QueryWriter::queryRecordRelated
855 public function queryRecordRelated( $sourceType, $destType, $linkIDs, $addSql = '', $bindings = array() )
857 $addSql = $this->glueSQLCondition( $addSql, QueryWriter::C_GLUE_WHERE );
859 list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
861 $key = $this->getCacheKey( array( $sourceType, $destType, implode( ',', $linkIDs ), $addSql, $bindings ) );
863 if ( $this->flagUseCache && $cached = $this->getCached( $destType, $key ) ) {
867 $inClause = $this->getParametersForInClause( $linkIDs, $bindings );
870 if ( count( self::$sqlFilters ) ) {
871 $sqlFilterStr = $this->getSQLFilterSnippet( $destType );
874 if ( $sourceType === $destType ) {
875 $inClause2 = $this->getParametersForInClause( $linkIDs, $bindings, count( $bindings ) ); //for some databases
878 {$destTable}.* {$sqlFilterStr} ,
880 NULLIF({$linkTable}.{$sourceCol}, {$destTable}.id),
881 NULLIF({$linkTable}.{$destCol}, {$destTable}.id)) AS linked_by
883 INNER JOIN {$destTable} ON
884 ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ($inClause) ) OR
885 ( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} IN ($inClause2) )
889 $linkIDs = array_merge( $linkIDs, $linkIDs );
893 {$destTable}.* {$sqlFilterStr},
894 {$linkTable}.{$sourceCol} AS linked_by
896 INNER JOIN {$destTable} ON
897 ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ($inClause) )
902 $bindings = array_merge( $linkIDs, $bindings );
904 $rows = $this->adapter->get( $sql, $bindings );
906 $this->putResultInCache( $destType, $key, $rows );
912 * @see QueryWriter::queryRecordLink
914 public function queryRecordLink( $sourceType, $destType, $sourceID, $destID )
916 list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
918 $key = $this->getCacheKey( array( $sourceType, $destType, $sourceID, $destID ) );
920 if ( $this->flagUseCache && $cached = $this->getCached( $linkTable, $key ) ) {
925 if ( count( self::$sqlFilters ) ) {
926 $sqlFilterStr = $this->getSQLFilterSnippet( $destType );
929 if ( $sourceTable === $destTable ) {
930 $sql = "SELECT {$linkTable}.* {$sqlFilterStr} FROM {$linkTable}
931 WHERE ( {$sourceCol} = ? AND {$destCol} = ? ) OR
932 ( {$destCol} = ? AND {$sourceCol} = ? ) -- keep-cache";
933 $row = $this->adapter->getRow( $sql, array( $sourceID, $destID, $sourceID, $destID ) );
935 $sql = "SELECT {$linkTable}.* {$sqlFilterStr} FROM {$linkTable}
936 WHERE {$sourceCol} = ? AND {$destCol} = ? -- keep-cache";
937 $row = $this->adapter->getRow( $sql, array( $sourceID, $destID ) );
940 $this->putResultInCache( $linkTable, $key, $row );
946 * @see QueryWriter::queryTagged
948 public function queryTagged( $type, $tagList, $all = FALSE, $addSql = '', $bindings = array() )
950 $assocType = $this->getAssocTable( array( $type, 'tag' ) );
951 $assocTable = $this->esc( $assocType );
952 $assocField = $type . '_id';
953 $table = $this->esc( $type );
954 $slots = implode( ',', array_fill( 0, count( $tagList ), '?' ) );
955 $score = ( $all ) ? count( $tagList ) : 1;
958 SELECT {$table}.*, count({$table}.id) FROM {$table}
959 INNER JOIN {$assocTable} ON {$assocField} = {$table}.id
960 INNER JOIN tag ON {$assocTable}.tag_id = tag.id
961 WHERE tag.title IN ({$slots})
963 HAVING count({$table}.id) >= ?
967 $bindings = array_merge( $tagList, array( $score ), $bindings );
968 $rows = $this->adapter->get( $sql, $bindings );
973 * @see QueryWriter::queryRecordCount
975 public function queryRecordCount( $type, $conditions = array(), $addSql = NULL, $bindings = array() )
977 $addSql = $this->glueSQLCondition( $addSql );
979 $table = $this->esc( $type );
981 $this->updateCache(); //check if cache chain has been broken
983 $sql = $this->makeSQLFromConditions( $conditions, $bindings, $addSql );
984 $sql = "SELECT COUNT(*) FROM {$table} {$sql} -- keep-cache";
986 return (int) $this->adapter->getCell( $sql, $bindings );
990 * @see QueryWriter::queryRecordCountRelated
992 public function queryRecordCountRelated( $sourceType, $destType, $linkID, $addSql = '', $bindings = array() )
994 list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
996 $this->updateCache(); //check if cache chain has been broken
998 if ( $sourceType === $destType ) {
1000 SELECT COUNT(*) FROM {$linkTable}
1001 INNER JOIN {$destTable} ON
1002 ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} = ? ) OR
1003 ( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} = ? )
1007 $bindings = array_merge( array( $linkID, $linkID ), $bindings );
1010 SELECT COUNT(*) FROM {$linkTable}
1011 INNER JOIN {$destTable} ON
1012 ( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} = ? )
1016 $bindings = array_merge( array( $linkID ), $bindings );
1019 return (int) $this->adapter->getCell( $sql, $bindings );
1023 * @see QueryWriter::deleteRecord
1025 public function deleteRecord( $type, $conditions = array(), $addSql = NULL, $bindings = array() )
1027 $addSql = $this->glueSQLCondition( $addSql );
1029 $table = $this->esc( $type );
1031 $sql = $this->makeSQLFromConditions( $conditions, $bindings, $addSql );
1032 $sql = "DELETE FROM {$table} {$sql}";
1034 $this->adapter->exec( $sql, $bindings );
1038 * @see QueryWriter::deleteRelations
1040 public function deleteRelations( $sourceType, $destType, $sourceID )
1042 list( $sourceTable, $destTable, $linkTable, $sourceCol, $destCol ) = $this->getRelationalTablesAndColumns( $sourceType, $destType );
1044 if ( $sourceTable === $destTable ) {
1045 $sql = "DELETE FROM {$linkTable}
1046 WHERE ( {$sourceCol} = ? ) OR
1050 $this->adapter->exec( $sql, array( $sourceID, $sourceID ) );
1052 $sql = "DELETE FROM {$linkTable}
1053 WHERE {$sourceCol} = ? ";
1055 $this->adapter->exec( $sql, array( $sourceID ) );
1060 * @see QueryWriter::widenColumn
1062 public function widenColumn( $type, $property, $dataType )
1064 if ( !isset($this->typeno_sqltype[$dataType]) ) return FALSE;
1066 $table = $this->esc( $type );
1067 $column = $this->esc( $property );
1069 $newType = $this->typeno_sqltype[$dataType];
1071 $this->adapter->exec( "ALTER TABLE $table CHANGE $column $column $newType " );
1077 * @see QueryWriter::wipe
1079 public function wipe( $type )
1081 $table = $this->esc( $type );
1083 $this->adapter->exec( "TRUNCATE $table " );
1087 * @see QueryWriter::renameAssocTable
1089 public function renameAssocTable( $from, $to = NULL )
1091 self::renameAssociation( $from, $to );
1095 * @see QueryWriter::getAssocTable
1097 public function getAssocTable( $types )
1099 return self::getAssocTableFormat( $types );
1103 * Turns caching on or off. Default: off.
1104 * If caching is turned on retrieval queries fired after eachother will
1105 * use a result row cache.
1111 public function setUseCache( $yesNo )
1113 $this->flushCache();
1115 $this->flagUseCache = (bool) $yesNo;
1119 * Flushes the Query Writer Cache.
1120 * Clears the internal query cache array and returns its overall
1125 public function flushCache( $newMaxCacheSizePerType = NULL )
1127 if ( !is_null( $newMaxCacheSizePerType ) && $newMaxCacheSizePerType > 0 ) {
1128 $this->maxCacheSizePerType = $newMaxCacheSizePerType;
1130 $count = count( $this->cache, COUNT_RECURSIVE );
1131 $this->cache = array();
1136 * @deprecated Use esc() instead.
1138 * @param string $column column to be escaped
1139 * @param boolean $noQuotes omit quotes
1143 public function safeColumn( $column, $noQuotes = FALSE )
1145 return $this->esc( $column, $noQuotes );
1149 * @deprecated Use esc() instead.
1151 * @param string $table table to be escaped
1152 * @param boolean $noQuotes omit quotes
1156 public function safeTable( $table, $noQuotes = FALSE )
1158 return $this->esc( $table, $noQuotes );
1162 * @see QueryWriter::inferFetchType
1164 public function inferFetchType( $type, $property )
1166 $type = $this->esc( $type, TRUE );
1167 $field = $this->esc( $property, TRUE ) . '_id';
1168 $keys = $this->getKeyMapForType( $type );
1170 foreach( $keys as $key ) {
1172 $key['from'] === $field
1173 ) return $key['table'];
1179 * @see QueryWriter::addUniqueConstraint
1181 public function addUniqueIndex( $type, $properties )
1183 return $this->addUniqueConstraint( $type, $properties );