1: <?php
  2: /**
  3:  * CDbCommandBuilder class file.
  4:  *
  5:  * @author Qiang Xue <qiang.xue@gmail.com>
  6:  * @link http://www.yiiframework.com/
  7:  * @copyright 2008-2013 Yii Software LLC
  8:  * @license http://www.yiiframework.com/license/
  9:  */
 10: 
 11: /**
 12:  * CDbCommandBuilder provides basic methods to create query commands for tables.
 13:  *
 14:  * @property CDbConnection $dbConnection Database connection.
 15:  * @property CDbSchema $schema The schema for this command builder.
 16:  *
 17:  * @author Qiang Xue <qiang.xue@gmail.com>
 18:  * @package system.db.schema
 19:  * @since 1.0
 20:  */
 21: class CDbCommandBuilder extends CComponent
 22: {
 23:     const PARAM_PREFIX=':yp';
 24: 
 25:     private $_schema;
 26:     private $_connection;
 27: 
 28:     /**
 29:      * @param CDbSchema $schema the schema for this command builder
 30:      */
 31:     public function __construct($schema)
 32:     {
 33:         $this->_schema=$schema;
 34:         $this->_connection=$schema->getDbConnection();
 35:     }
 36: 
 37:     /**
 38:      * @return CDbConnection database connection.
 39:      */
 40:     public function getDbConnection()
 41:     {
 42:         return $this->_connection;
 43:     }
 44: 
 45:     /**
 46:      * @return CDbSchema the schema for this command builder.
 47:      */
 48:     public function getSchema()
 49:     {
 50:         return $this->_schema;
 51:     }
 52: 
 53:     /**
 54:      * Returns the last insertion ID for the specified table.
 55:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
 56:      * @return mixed last insertion id. Null is returned if no sequence name.
 57:      */
 58:     public function getLastInsertID($table)
 59:     {
 60:         $this->ensureTable($table);
 61:         if($table->sequenceName!==null)
 62:             return $this->_connection->getLastInsertID($table->sequenceName);
 63:         else
 64:             return null;
 65:     }
 66: 
 67:     /**
 68:      * Creates a SELECT command for a single table.
 69:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
 70:      * @param CDbCriteria $criteria the query criteria
 71:      * @param string $alias the alias name of the primary table. Defaults to 't'.
 72:      * @return CDbCommand query command.
 73:      */
 74:     public function createFindCommand($table,$criteria,$alias='t')
 75:     {
 76:         $this->ensureTable($table);
 77:         $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;
 78:         if($criteria->alias!='')
 79:             $alias=$criteria->alias;
 80:         $alias=$this->_schema->quoteTableName($alias);
 81: 
 82:         // issue 1432: need to expand * when SQL has JOIN
 83:         if($select==='*' && !empty($criteria->join))
 84:         {
 85:             $prefix=$alias.'.';
 86:             $select=array();
 87:             foreach($table->getColumnNames() as $name)
 88:                 $select[]=$prefix.$this->_schema->quoteColumnName($name);
 89:             $select=implode(', ',$select);
 90:         }
 91: 
 92:         $sql=($criteria->distinct ? 'SELECT DISTINCT':'SELECT')." {$select} FROM {$table->rawName} $alias";
 93:         $sql=$this->applyJoin($sql,$criteria->join);
 94:         $sql=$this->applyCondition($sql,$criteria->condition);
 95:         $sql=$this->applyGroup($sql,$criteria->group);
 96:         $sql=$this->applyHaving($sql,$criteria->having);
 97:         $sql=$this->applyOrder($sql,$criteria->order);
 98:         $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
 99:         $command=$this->_connection->createCommand($sql);
100:         $this->bindValues($command,$criteria->params);
101:         return $command;
102:     }
103: 
104:     /**
105:      * Creates a COUNT(*) command for a single table.
106:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
107:      * @param CDbCriteria $criteria the query criteria
108:      * @param string $alias the alias name of the primary table. Defaults to 't'.
109:      * @return CDbCommand query command.
110:      */
111:     public function createCountCommand($table,$criteria,$alias='t')
112:     {
113:         $this->ensureTable($table);
114:         if($criteria->alias!='')
115:             $alias=$criteria->alias;
116:         $alias=$this->_schema->quoteTableName($alias);
117: 
118:         if(!empty($criteria->group) || !empty($criteria->having))
119:         {
120:             $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;
121:             if($criteria->alias!='')
122:                 $alias=$criteria->alias;
123:             $sql=($criteria->distinct ? 'SELECT DISTINCT':'SELECT')." {$select} FROM {$table->rawName} $alias";
124:             $sql=$this->applyJoin($sql,$criteria->join);
125:             $sql=$this->applyCondition($sql,$criteria->condition);
126:             $sql=$this->applyGroup($sql,$criteria->group);
127:             $sql=$this->applyHaving($sql,$criteria->having);
128:             $sql="SELECT COUNT(*) FROM ($sql) sq";
129:         }
130:         else
131:         {
132:             if(is_string($criteria->select) && stripos($criteria->select,'count')===0)
133:                 $sql="SELECT ".$criteria->select;
134:             elseif($criteria->distinct)
135:             {
136:                 if(is_array($table->primaryKey))
137:                 {
138:                     $pk=array();
139:                     foreach($table->primaryKey as $key)
140:                         $pk[]=$alias.'.'.$key;
141:                     $pk=implode(', ',$pk);
142:                 }
143:                 else
144:                     $pk=$alias.'.'.$table->primaryKey;
145:                 $sql="SELECT COUNT(DISTINCT $pk)";
146:             }
147:             else
148:                 $sql="SELECT COUNT(*)";
149:             $sql.=" FROM {$table->rawName} $alias";
150:             $sql=$this->applyJoin($sql,$criteria->join);
151:             $sql=$this->applyCondition($sql,$criteria->condition);
152:         }
153: 
154:         // Suppress binding of parameters belonging to the ORDER clause. Issue #1407.
155:         if($criteria->order && $criteria->params)
156:         {
157:             $params1=array();
158:             preg_match_all('/(:\w+)/',$sql,$params1);
159:             $params2=array();
160:             preg_match_all('/(:\w+)/',$this->applyOrder($sql,$criteria->order),$params2);
161:             foreach(array_diff($params2[0],$params1[0]) as $param)
162:                 unset($criteria->params[$param]);
163:         }
164: 
165:         // Do the same for SELECT part.
166:         if($criteria->select && $criteria->params)
167:         {
168:             $params1=array();
169:             preg_match_all('/(:\w+)/',$sql,$params1);
170:             $params2=array();
171:             preg_match_all('/(:\w+)/',$sql.' '.(is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select),$params2);
172:             foreach(array_diff($params2[0],$params1[0]) as $param)
173:                 unset($criteria->params[$param]);
174:         }
175: 
176:         $command=$this->_connection->createCommand($sql);
177:         $this->bindValues($command,$criteria->params);
178:         return $command;
179:     }
180: 
181:     /**
182:      * Creates a DELETE command.
183:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
184:      * @param CDbCriteria $criteria the query criteria
185:      * @return CDbCommand delete command.
186:      */
187:     public function createDeleteCommand($table,$criteria)
188:     {
189:         $this->ensureTable($table);
190:         $sql="DELETE FROM {$table->rawName}";
191:         $sql=$this->applyJoin($sql,$criteria->join);
192:         $sql=$this->applyCondition($sql,$criteria->condition);
193:         $sql=$this->applyGroup($sql,$criteria->group);
194:         $sql=$this->applyHaving($sql,$criteria->having);
195:         $sql=$this->applyOrder($sql,$criteria->order);
196:         $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
197:         $command=$this->_connection->createCommand($sql);
198:         $this->bindValues($command,$criteria->params);
199:         return $command;
200:     }
201: 
202:     /**
203:      * Creates an INSERT command.
204:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
205:      * @param array $data data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored.
206:      * @return CDbCommand insert command
207:      */
208:     public function createInsertCommand($table,$data)
209:     {
210:         $this->ensureTable($table);
211:         $fields=array();
212:         $values=array();
213:         $placeholders=array();
214:         $i=0;
215:         foreach($data as $name=>$value)
216:         {
217:             if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull))
218:             {
219:                 $fields[]=$column->rawName;
220:                 if($value instanceof CDbExpression)
221:                 {
222:                     $placeholders[]=$value->expression;
223:                     foreach($value->params as $n=>$v)
224:                         $values[$n]=$v;
225:                 }
226:                 else
227:                 {
228:                     $placeholders[]=self::PARAM_PREFIX.$i;
229:                     $values[self::PARAM_PREFIX.$i]=$column->typecast($value);
230:                     $i++;
231:                 }
232:             }
233:         }
234:         if($fields===array())
235:         {
236:             $pks=is_array($table->primaryKey) ? $table->primaryKey : array($table->primaryKey);
237:             foreach($pks as $pk)
238:             {
239:                 $fields[]=$table->getColumn($pk)->rawName;
240:                 $placeholders[]=$this->getIntegerPrimaryKeyDefaultValue();
241:             }
242:         }
243:         $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')';
244:         $command=$this->_connection->createCommand($sql);
245: 
246:         foreach($values as $name=>$value)
247:             $command->bindValue($name,$value);
248: 
249:         return $command;
250:     }
251: 
252:     /**
253:      * Creates a multiple INSERT command.
254:      * This method could be used to achieve better performance during insertion of the large
255:      * amount of data into the database tables.
256:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
257:      * @param array[] $data list data to be inserted, each value should be an array in format (column name=>column value).
258:      * If a key is not a valid column name, the corresponding value will be ignored.
259:      * @return CDbCommand multiple insert command
260:      * @since 1.1.14
261:      */
262:     public function createMultipleInsertCommand($table,array $data)
263:     {
264:         return $this->composeMultipleInsertCommand($table,$data);
265:     }
266: 
267:     /**
268:      * Creates a multiple INSERT command.
269:      * This method compose the SQL expression via given part templates, providing ability to adjust
270:      * command for different SQL syntax.
271:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
272:      * @param array[] $data list data to be inserted, each value should be an array in format (column name=>column value).
273:      * If a key is not a valid column name, the corresponding value will be ignored.
274:      * @param array $templates templates for the SQL parts.
275:      * @return CDbCommand multiple insert command
276:      * @throws CDbException if $data is empty.
277:      */
278:     protected function composeMultipleInsertCommand($table,array $data,array $templates=array())
279:     {
280:         if (empty($data))
281:             throw new CDbException(Yii::t('yii','Can not generate multiple insert command with empty data set.'));
282:         $templates=array_merge(
283:             array(
284:                 'main'=>'INSERT INTO {{tableName}} ({{columnInsertNames}}) VALUES {{rowInsertValues}}',
285:                 'columnInsertValue'=>'{{value}}',
286:                 'columnInsertValueGlue'=>', ',
287:                 'rowInsertValue'=>'({{columnInsertValues}})',
288:                 'rowInsertValueGlue'=>', ',
289:                 'columnInsertNameGlue'=>', ',
290:             ),
291:             $templates
292:         );
293:         $this->ensureTable($table);
294:         $tableName=$table->rawName;
295:         $params=array();
296:         $columnInsertNames=array();
297:         $rowInsertValues=array();
298: 
299:         $columns=array();
300:         foreach($data as $rowData)
301:         {
302:             foreach($rowData as $columnName=>$columnValue)
303:             {
304:                 if(!in_array($columnName,$columns,true))
305:                     if($table->getColumn($columnName)!==null)
306:                         $columns[]=$columnName;
307:             }
308:         }
309:         foreach($columns as $name)
310:             $columnInsertNames[$name]=$this->getDbConnection()->quoteColumnName($name);
311:         $columnInsertNamesSqlPart=implode($templates['columnInsertNameGlue'],$columnInsertNames);
312: 
313:         foreach($data as $rowKey=>$rowData)
314:         {
315:             $columnInsertValues=array();
316:             foreach($columns as $columnName)
317:             {
318:                 $column=$table->getColumn($columnName);
319:                 $columnValue=array_key_exists($columnName,$rowData) ? $rowData[$columnName] : new CDbExpression('NULL');
320:                 if($columnValue instanceof CDbExpression)
321:                 {
322:                     $columnInsertValue=$columnValue->expression;
323:                     foreach($columnValue->params as $columnValueParamName=>$columnValueParam)
324:                         $params[$columnValueParamName]=$columnValueParam;
325:                 }
326:                 else
327:                 {
328:                     $columnInsertValue=':'.$columnName.'_'.$rowKey;
329:                     $params[':'.$columnName.'_'.$rowKey]=$column->typecast($columnValue);
330:                 }
331:                 $columnInsertValues[]=strtr($templates['columnInsertValue'],array(
332:                     '{{column}}'=>$columnInsertNames[$columnName],
333:                     '{{value}}'=>$columnInsertValue,
334:                 ));
335:             }
336:             $rowInsertValues[]=strtr($templates['rowInsertValue'],array(
337:                 '{{tableName}}'=>$tableName,
338:                 '{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
339:                 '{{columnInsertValues}}'=>implode($templates['columnInsertValueGlue'],$columnInsertValues)
340:             ));
341:         }
342: 
343:         $sql=strtr($templates['main'],array(
344:             '{{tableName}}'=>$tableName,
345:             '{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
346:             '{{rowInsertValues}}'=>implode($templates['rowInsertValueGlue'], $rowInsertValues),
347:         ));
348:         $command=$this->getDbConnection()->createCommand($sql);
349: 
350:         foreach($params as $name=>$value)
351:             $command->bindValue($name,$value);
352: 
353:         return $command;
354:     }
355: 
356:     /**
357:      * Creates an UPDATE command.
358:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
359:      * @param array $data list of columns to be updated (name=>value)
360:      * @param CDbCriteria $criteria the query criteria
361:      * @throws CDbException if no columns are being updated for the given table
362:      * @return CDbCommand update command.
363:      */
364:     public function createUpdateCommand($table,$data,$criteria)
365:     {
366:         $this->ensureTable($table);
367:         $fields=array();
368:         $values=array();
369:         $bindByPosition=isset($criteria->params[0]);
370:         $i=0;
371:         foreach($data as $name=>$value)
372:         {
373:             if(($column=$table->getColumn($name))!==null)
374:             {
375:                 if($value instanceof CDbExpression)
376:                 {
377:                     $fields[]=$column->rawName.'='.$value->expression;
378:                     foreach($value->params as $n=>$v)
379:                         $values[$n]=$v;
380:                 }
381:                 elseif($bindByPosition)
382:                 {
383:                     $fields[]=$column->rawName.'=?';
384:                     $values[]=$column->typecast($value);
385:                 }
386:                 else
387:                 {
388:                     $fields[]=$column->rawName.'='.self::PARAM_PREFIX.$i;
389:                     $values[self::PARAM_PREFIX.$i]=$column->typecast($value);
390:                     $i++;
391:                 }
392:             }
393:         }
394:         if($fields===array())
395:             throw new CDbException(Yii::t('yii','No columns are being updated for table "{table}".',
396:                 array('{table}'=>$table->name)));
397:         $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields);
398:         $sql=$this->applyJoin($sql,$criteria->join);
399:         $sql=$this->applyCondition($sql,$criteria->condition);
400:         $sql=$this->applyOrder($sql,$criteria->order);
401:         $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
402: 
403:         $command=$this->_connection->createCommand($sql);
404:         $this->bindValues($command,array_merge($values,$criteria->params));
405: 
406:         return $command;
407:     }
408: 
409:     /**
410:      * Creates an UPDATE command that increments/decrements certain columns.
411:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
412:      * @param array $counters counters to be updated (counter increments/decrements indexed by column names.)
413:      * @param CDbCriteria $criteria the query criteria
414:      * @throws CDbException if no columns are being updated for the given table
415:      * @return CDbCommand the created command
416:      */
417:     public function createUpdateCounterCommand($table,$counters,$criteria)
418:     {
419:         $this->ensureTable($table);
420:         $fields=array();
421:         foreach($counters as $name=>$value)
422:         {
423:             if(($column=$table->getColumn($name))!==null)
424:             {
425:                 $value=(float)$value;
426:                 if($value<0)
427:                     $fields[]="{$column->rawName}={$column->rawName}-".(-$value);
428:                 else
429:                     $fields[]="{$column->rawName}={$column->rawName}+".$value;
430:             }
431:         }
432:         if($fields!==array())
433:         {
434:             $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields);
435:             $sql=$this->applyJoin($sql,$criteria->join);
436:             $sql=$this->applyCondition($sql,$criteria->condition);
437:             $sql=$this->applyOrder($sql,$criteria->order);
438:             $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
439:             $command=$this->_connection->createCommand($sql);
440:             $this->bindValues($command,$criteria->params);
441:             return $command;
442:         }
443:         else
444:             throw new CDbException(Yii::t('yii','No counter columns are being updated for table "{table}".',
445:                 array('{table}'=>$table->name)));
446:     }
447: 
448:     /**
449:      * Creates a command based on a given SQL statement.
450:      * @param string $sql the explicitly specified SQL statement
451:      * @param array $params parameters that will be bound to the SQL statement
452:      * @return CDbCommand the created command
453:      */
454:     public function createSqlCommand($sql,$params=array())
455:     {
456:         $command=$this->_connection->createCommand($sql);
457:         $this->bindValues($command,$params);
458:         return $command;
459:     }
460: 
461:     /**
462:      * Alters the SQL to apply JOIN clause.
463:      * @param string $sql the SQL statement to be altered
464:      * @param string $join the JOIN clause (starting with join type, such as INNER JOIN)
465:      * @return string the altered SQL statement
466:      */
467:     public function applyJoin($sql,$join)
468:     {
469:         if($join!='')
470:             return $sql.' '.$join;
471:         else
472:             return $sql;
473:     }
474: 
475:     /**
476:      * Alters the SQL to apply WHERE clause.
477:      * @param string $sql the SQL statement without WHERE clause
478:      * @param string $condition the WHERE clause (without WHERE keyword)
479:      * @return string the altered SQL statement
480:      */
481:     public function applyCondition($sql,$condition)
482:     {
483:         if($condition!='')
484:             return $sql.' WHERE '.$condition;
485:         else
486:             return $sql;
487:     }
488: 
489:     /**
490:      * Alters the SQL to apply ORDER BY.
491:      * @param string $sql SQL statement without ORDER BY.
492:      * @param string $orderBy column ordering
493:      * @return string modified SQL applied with ORDER BY.
494:      */
495:     public function applyOrder($sql,$orderBy)
496:     {
497:         if($orderBy!='')
498:             return $sql.' ORDER BY '.$orderBy;
499:         else
500:             return $sql;
501:     }
502: 
503:     /**
504:      * Alters the SQL to apply LIMIT and OFFSET.
505:      * Default implementation is applicable for PostgreSQL, MySQL, MariaDB and SQLite.
506:      * @param string $sql SQL query string without LIMIT and OFFSET.
507:      * @param integer $limit maximum number of rows, -1 to ignore limit.
508:      * @param integer $offset row offset, -1 to ignore offset.
509:      * @return string SQL with LIMIT and OFFSET
510:      */
511:     public function applyLimit($sql,$limit,$offset)
512:     {
513:         if($limit>=0)
514:             $sql.=' LIMIT '.(int)$limit;
515:         if($offset>0)
516:             $sql.=' OFFSET '.(int)$offset;
517:         return $sql;
518:     }
519: 
520:     /**
521:      * Alters the SQL to apply GROUP BY.
522:      * @param string $sql SQL query string without GROUP BY.
523:      * @param string $group GROUP BY
524:      * @return string SQL with GROUP BY.
525:      */
526:     public function applyGroup($sql,$group)
527:     {
528:         if($group!='')
529:             return $sql.' GROUP BY '.$group;
530:         else
531:             return $sql;
532:     }
533: 
534:     /**
535:      * Alters the SQL to apply HAVING.
536:      * @param string $sql SQL query string without HAVING
537:      * @param string $having HAVING
538:      * @return string SQL with HAVING
539:      */
540:     public function applyHaving($sql,$having)
541:     {
542:         if($having!='')
543:             return $sql.' HAVING '.$having;
544:         else
545:             return $sql;
546:     }
547: 
548:     /**
549:      * Binds parameter values for an SQL command.
550:      * @param CDbCommand $command database command
551:      * @param array $values values for binding (integer-indexed array for question mark placeholders, string-indexed array for named placeholders)
552:      */
553:     public function bindValues($command, $values)
554:     {
555:         if(($n=count($values))===0)
556:             return;
557:         if(isset($values[0])) // question mark placeholders
558:         {
559:             for($i=0;$i<$n;++$i)
560:                 $command->bindValue($i+1,$values[$i]);
561:         }
562:         else // named placeholders
563:         {
564:             foreach($values as $name=>$value)
565:             {
566:                 if($name[0]!==':')
567:                     $name=':'.$name;
568:                 $command->bindValue($name,$value);
569:             }
570:         }
571:     }
572: 
573:     /**
574:      * Creates a query criteria.
575:      * @param mixed $condition query condition or criteria.
576:      * If a string, it is treated as query condition (the WHERE clause);
577:      * If an array, it is treated as the initial values for constructing a {@link CDbCriteria} object;
578:      * Otherwise, it should be an instance of {@link CDbCriteria}.
579:      * @param array $params parameters to be bound to an SQL statement.
580:      * This is only used when the first parameter is a string (query condition).
581:      * In other cases, please use {@link CDbCriteria::params} to set parameters.
582:      * @return CDbCriteria the created query criteria
583:      * @throws CException if the condition is not string, array and CDbCriteria
584:      */
585:     public function createCriteria($condition='',$params=array())
586:     {
587:         if(is_array($condition))
588:             $criteria=new CDbCriteria($condition);
589:         elseif($condition instanceof CDbCriteria)
590:             $criteria=clone $condition;
591:         else
592:         {
593:             $criteria=new CDbCriteria;
594:             $criteria->condition=$condition;
595:             $criteria->params=$params;
596:         }
597:         return $criteria;
598:     }
599: 
600:     /**
601:      * Creates a query criteria with the specified primary key.
602:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
603:      * @param mixed $pk primary key value(s). Use array for multiple primary keys. For composite key, each key value must be an array (column name=>column value).
604:      * @param mixed $condition query condition or criteria.
605:      * If a string, it is treated as query condition;
606:      * If an array, it is treated as the initial values for constructing a {@link CDbCriteria};
607:      * Otherwise, it should be an instance of {@link CDbCriteria}.
608:      * @param array $params parameters to be bound to an SQL statement.
609:      * This is only used when the second parameter is a string (query condition).
610:      * In other cases, please use {@link CDbCriteria::params} to set parameters.
611:      * @param string $prefix column prefix (ended with dot). If null, it will be the table name
612:      * @return CDbCriteria the created query criteria
613:      */
614:     public function createPkCriteria($table,$pk,$condition='',$params=array(),$prefix=null)
615:     {
616:         $this->ensureTable($table);
617:         $criteria=$this->createCriteria($condition,$params);
618:         if($criteria->alias!='')
619:             $prefix=$this->_schema->quoteTableName($criteria->alias).'.';
620:         if(!is_array($pk)) // single key
621:             $pk=array($pk);
622:         if(is_array($table->primaryKey) && !isset($pk[0]) && $pk!==array()) // single composite key
623:             $pk=array($pk);
624:         $condition=$this->createInCondition($table,$table->primaryKey,$pk,$prefix);
625:         if($criteria->condition!='')
626:             $criteria->condition=$condition.' AND ('.$criteria->condition.')';
627:         else
628:             $criteria->condition=$condition;
629: 
630:         return $criteria;
631:     }
632: 
633:     /**
634:      * Generates the expression for selecting rows of specified primary key values.
635:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
636:      * @param array $values list of primary key values to be selected within
637:      * @param string $prefix column prefix (ended with dot). If null, it will be the table name
638:      * @return string the expression for selection
639:      */
640:     public function createPkCondition($table,$values,$prefix=null)
641:     {
642:         $this->ensureTable($table);
643:         return $this->createInCondition($table,$table->primaryKey,$values,$prefix);
644:     }
645: 
646:     /**
647:      * Creates a query criteria with the specified column values.
648:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
649:      * @param array $columns column values that should be matched in the query (name=>value)
650:      * @param mixed $condition query condition or criteria.
651:      * If a string, it is treated as query condition;
652:      * If an array, it is treated as the initial values for constructing a {@link CDbCriteria};
653:      * Otherwise, it should be an instance of {@link CDbCriteria}.
654:      * @param array $params parameters to be bound to an SQL statement.
655:      * This is only used when the third parameter is a string (query condition).
656:      * In other cases, please use {@link CDbCriteria::params} to set parameters.
657:      * @param string $prefix column prefix (ended with dot). If null, it will be the table name
658:      * @throws CDbException if specified column is not found in given table
659:      * @return CDbCriteria the created query criteria
660:      */
661:     public function createColumnCriteria($table,$columns,$condition='',$params=array(),$prefix=null)
662:     {
663:         $this->ensureTable($table);
664:         $criteria=$this->createCriteria($condition,$params);
665:         if($criteria->alias!='')
666:             $prefix=$this->_schema->quoteTableName($criteria->alias).'.';
667:         $bindByPosition=isset($criteria->params[0]);
668:         $conditions=array();
669:         $values=array();
670:         $i=0;
671:         if($prefix===null)
672:             $prefix=$table->rawName.'.';
673:         foreach($columns as $name=>$value)
674:         {
675:             if(($column=$table->getColumn($name))!==null)
676:             {
677:                 if(is_array($value))
678:                     $conditions[]=$this->createInCondition($table,$name,$value,$prefix);
679:                 elseif($value!==null)
680:                 {
681:                     if($bindByPosition)
682:                     {
683:                         $conditions[]=$prefix.$column->rawName.'=?';
684:                         $values[]=$value;
685:                     }
686:                     else
687:                     {
688:                         $conditions[]=$prefix.$column->rawName.'='.self::PARAM_PREFIX.$i;
689:                         $values[self::PARAM_PREFIX.$i]=$value;
690:                         $i++;
691:                     }
692:                 }
693:                 else
694:                     $conditions[]=$prefix.$column->rawName.' IS NULL';
695:             }
696:             else
697:                 throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".',
698:                     array('{table}'=>$table->name,'{column}'=>$name)));
699:         }
700:         $criteria->params=array_merge($values,$criteria->params);
701:         if(isset($conditions[0]))
702:         {
703:             if($criteria->condition!='')
704:                 $criteria->condition=implode(' AND ',$conditions).' AND ('.$criteria->condition.')';
705:             else
706:                 $criteria->condition=implode(' AND ',$conditions);
707:         }
708:         return $criteria;
709:     }
710: 
711:     /**
712:      * Generates the expression for searching the specified keywords within a list of columns.
713:      * The search expression is generated using the 'LIKE' SQL syntax.
714:      * Every word in the keywords must be present and appear in at least one of the columns.
715:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
716:      * @param array $columns list of column names for potential search condition.
717:      * @param mixed $keywords search keywords. This can be either a string with space-separated keywords or an array of keywords.
718:      * @param string $prefix optional column prefix (with dot at the end). If null, the table name will be used as the prefix.
719:      * @param boolean $caseSensitive whether the search is case-sensitive. Defaults to true.
720:      * @throws CDbException if specified column is not found in given table
721:      * @return string SQL search condition matching on a set of columns. An empty string is returned
722:      * if either the column array or the keywords are empty.
723:      */
724:     public function createSearchCondition($table,$columns,$keywords,$prefix=null,$caseSensitive=true)
725:     {
726:         $this->ensureTable($table);
727:         if(!is_array($keywords))
728:             $keywords=preg_split('/\s+/u',$keywords,-1,PREG_SPLIT_NO_EMPTY);
729:         if(empty($keywords))
730:             return '';
731:         if($prefix===null)
732:             $prefix=$table->rawName.'.';
733:         $conditions=array();
734:         foreach($columns as $name)
735:         {
736:             if(($column=$table->getColumn($name))===null)
737:                 throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".',
738:                     array('{table}'=>$table->name,'{column}'=>$name)));
739:             $condition=array();
740:             foreach($keywords as $keyword)
741:             {
742:                 $keyword='%'.strtr($keyword,array('%'=>'\%', '_'=>'\_', '\\'=>'\\\\')).'%';
743:                 if($caseSensitive)
744:                     $condition[]=$prefix.$column->rawName.' LIKE '.$this->_connection->quoteValue($keyword);
745:                 else
746:                     $condition[]='LOWER('.$prefix.$column->rawName.') LIKE LOWER('.$this->_connection->quoteValue($keyword).')';
747:             }
748:             $conditions[]=implode(' AND ',$condition);
749:         }
750:         return '('.implode(' OR ',$conditions).')';
751:     }
752: 
753:     /**
754:      * Generates the expression for selecting rows of specified primary key values.
755:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
756:      * @param mixed $columnName the column name(s). It can be either a string indicating a single column
757:      * or an array of column names. If the latter, it stands for a composite key.
758:      * @param array $values list of key values to be selected within
759:      * @param string $prefix column prefix (ended with dot). If null, it will be the table name
760:      * @throws CDbException if specified column is not found in given table
761:      * @return string the expression for selection
762:      */
763:     public function createInCondition($table,$columnName,$values,$prefix=null)
764:     {
765:         if(($n=count($values))<1)
766:             return '0=1';
767: 
768:         $this->ensureTable($table);
769: 
770:         if($prefix===null)
771:             $prefix=$table->rawName.'.';
772: 
773:         $db=$this->_connection;
774: 
775:         if(is_array($columnName) && count($columnName)===1)
776:             $columnName=reset($columnName);
777: 
778:         if(is_string($columnName)) // simple key
779:         {
780:             if(!isset($table->columns[$columnName]))
781:                 throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".',
782:                 array('{table}'=>$table->name, '{column}'=>$columnName)));
783:             $column=$table->columns[$columnName];
784: 
785:             $values=array_values($values);
786:             foreach($values as &$value)
787:             {
788:                 $value=$column->typecast($value);
789:                 if(is_string($value))
790:                     $value=$db->quoteValue($value);
791:             }
792:             if($n===1)
793:                 return $prefix.$column->rawName.($values[0]===null?' IS NULL':'='.$values[0]);
794:             else
795:                 return $prefix.$column->rawName.' IN ('.implode(', ',$values).')';
796:         }
797:         elseif(is_array($columnName)) // composite key: $values=array(array('pk1'=>'v1','pk2'=>'v2'),array(...))
798:         {
799:             foreach($columnName as $name)
800:             {
801:                 if(!isset($table->columns[$name]))
802:                     throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".',
803:                     array('{table}'=>$table->name, '{column}'=>$name)));
804: 
805:                 for($i=0;$i<$n;++$i)
806:                 {
807:                     if(isset($values[$i][$name]))
808:                     {
809:                         $value=$table->columns[$name]->typecast($values[$i][$name]);
810:                         if(is_string($value))
811:                             $values[$i][$name]=$db->quoteValue($value);
812:                         else
813:                             $values[$i][$name]=$value;
814:                     }
815:                     else
816:                         throw new CDbException(Yii::t('yii','The value for the column "{column}" is not supplied when querying the table "{table}".',
817:                             array('{table}'=>$table->name,'{column}'=>$name)));
818:                 }
819:             }
820:             if(count($values)===1)
821:             {
822:                 $entries=array();
823:                 foreach($values[0] as $name=>$value)
824:                     $entries[]=$prefix.$table->columns[$name]->rawName.($value===null?' IS NULL':'='.$value);
825:                 return implode(' AND ',$entries);
826:             }
827: 
828:             return $this->createCompositeInCondition($table,$values,$prefix);
829:         }
830:         else
831:             throw new CDbException(Yii::t('yii','Column name must be either a string or an array.'));
832:     }
833: 
834:     /**
835:      * Generates the expression for selecting rows with specified composite key values.
836:      * @param CDbTableSchema $table the table schema
837:      * @param array $values list of primary key values to be selected within
838:      * @param string $prefix column prefix (ended with dot)
839:      * @return string the expression for selection
840:      */
841:     protected function createCompositeInCondition($table,$values,$prefix)
842:     {
843:         $keyNames=array();
844:         foreach(array_keys($values[0]) as $name)
845:             $keyNames[]=$prefix.$table->columns[$name]->rawName;
846:         $vs=array();
847:         foreach($values as $value)
848:             $vs[]='('.implode(', ',$value).')';
849:         return '('.implode(', ',$keyNames).') IN ('.implode(', ',$vs).')';
850:     }
851: 
852:     /**
853:      * Checks if the parameter is a valid table schema.
854:      * If it is a string, the corresponding table schema will be retrieved.
855:      * @param mixed $table table schema ({@link CDbTableSchema}) or table name (string).
856:      * If this refers to a valid table name, this parameter will be returned with the corresponding table schema.
857:      * @throws CDbException if the table name is not valid
858:      */
859:     protected function ensureTable(&$table)
860:     {
861:         if(is_string($table) && ($table=$this->_schema->getTable($tableName=$table))===null)
862:             throw new CDbException(Yii::t('yii','Table "{table}" does not exist.',
863:                 array('{table}'=>$tableName)));
864:     }
865: 
866:     /**
867:      * Returns default value of the integer/serial primary key. Default value means that the next
868:      * autoincrement/sequence value would be used.
869:      * @return string default value of the integer/serial primary key.
870:      * @since 1.1.14
871:      */
872:     protected function getIntegerPrimaryKeyDefaultValue()
873:     {
874:         return 'NULL';
875:     }
876: }
877: