public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'INSERT';
if (!empty($this->options)) {
$sql .= $this->buildOptionClause();
}
$sql .= ' INTO ' . $driver->quoteTable($this->intoTable);
// append partition clause if needed.
$sql .= $this->buildPartitionClause($driver, $args);
$valuesClauses = array();
$varCnt = 1;
// build columns
$columns = $this->getColumnNames($driver);
foreach ($this->values as $values) {
$deflatedValues = array();
foreach ($values as $key => $value) {
$deflatedValues[] = $driver->deflate($value, $args);
}
$valuesClauses[] = '(' . join(',', $deflatedValues) . ')';
}
$sql .= ' (' . join(',', $columns) . ')' . ' VALUES ' . join(', ', $valuesClauses);
// Check if RETURNING is supported
if ($this->returning && $driver instanceof PgSQLDriver) {
$sql .= ' RETURNING ' . join(',', $driver->quoteColumns($this->returning));
}
return $sql;
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = '';
if ($driver instanceof MySQLDriver) {
$sql = 'CHANGE COLUMN ';
if (is_string($this->fromColumn)) {
$sql .= $driver->quoteIdentifier($this->fromColumn);
} elseif ($this->fromColumn instanceof Column) {
$sql .= $driver->quoteIdentifier($this->fromColumn->getName());
}
// the 'toColumn' must be a type of Column, we need at least column type to rename.
// $sql .= ' ' . $driver->quoteIdentifier($this->toColumn->getName()) . ' ' . $this->toColumn->getType();
$sql .= ' ' . $this->toColumn->buildDefinitionSql($driver, $args);
} elseif ($driver instanceof PgSQLDriver) {
// ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
$sql = 'RENAME COLUMN ';
if (is_string($this->fromColumn)) {
$sql .= $driver->quoteIdentifier($this->fromColumn);
} elseif ($this->fromColumn instanceof Column) {
$sql .= $driver->quoteIdentifier($this->fromColumn->getName());
}
// the 'toColumn' must be a type of Column, we need at least column type to rename.
$sql .= ' TO ' . $driver->quoteIdentifier($this->toColumn->getName());
} else {
throw new UnsupportedDriverException($driver, $this);
}
return $sql;
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
// XXX: $pat can be a Bind object
$isBind = $this->pat instanceof Bind;
$pat = $isBind ? $this->pat->getValue() : $this->pat;
switch ($this->criteria) {
case Criteria::CONTAINS:
$pat = '%' . $pat . '%';
break;
case Criteria::STARTS_WITH:
$pat = $pat . '%';
break;
case Criteria::ENDS_WITH:
$pat = '%' . $pat;
break;
case Criteria::EXACT:
$pat = $pat;
break;
default:
$pat = '%' . $pat . '%';
break;
}
if ($isBind) {
$this->pat->setValue($pat);
} else {
$this->pat = $pat;
}
return $this->exprStr . ' LIKE ' . $driver->deflate($this->pat, $args);
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = '';
foreach ($this->columns as $col) {
$sql .= $driver->quoteIdentifier($col) . ',';
}
return rtrim($sql, ',');
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'DROP DATABASE';
if ($driver instanceof MySQLDriver) {
$sql .= $this->buildIfExistsClause();
}
$sql .= ' ' . $driver->quoteIdentifier($this->dbName);
return $sql;
}
public static function create(PDO $connection, BaseDriver $driver)
{
$class = 'LazyRecord\\TableParser\\' . ucfirst($driver->getDriverName()) . 'TableParser';
if (class_exists($class, true)) {
return new $class($connection, $driver);
} else {
throw new Exception("parser driver does not support {$driver->getDriverName()} currently.");
}
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = '';
// constrain symbol is optional but only supported by MySQL
if ($this->symbol) {
$sql .= 'CONSTRAINT ' . $driver->quoteIdentifier($this->symbol) . ' ';
}
$sql .= $this->buildKeyClause($driver, $args);
return $sql;
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
if ($this->clauseType == self::SET_DEFAULT) {
return 'ALTER COLUMN ' . $driver->quoteIdentifier($this->name) . ' SET DEFAULT ' . $driver->deflate($this->defaultValue);
} else {
if ($this->clauseType == self::DROP_DEFAULT) {
return 'ALTER COLUMN ' . $driver->quoteIdentifier($this->name) . ' DROP DEFAULT';
} else {
throw new InvalidArgumentException('You should call either setDefault nor dropDefault');
}
}
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'REFERENCES ' . $driver->quoteIdentifier($this->tableName);
$sql .= ' (' . $this->columns->toSql($driver, $args) . ')';
if ($this->onUpdateAction) {
$sql .= ' ON UPDATE ' . $this->onUpdateAction;
}
if ($this->onDeleteAction) {
$sql .= ' ON DELETE ' . $this->onDeleteAction;
}
return $sql;
}
public function deflate($value, ArgumentArray $args = NULL)
{
if ($value instanceof DateTime) {
// MySQL does not support date time string with timezone
return $this->quote($value->format('Y-m-d H:i:s'));
}
return parent::deflate($value, $args);
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = '';
if (is_array($this->expr)) {
foreach ($this->expr as $val) {
$sql .= ',' . $driver->deflate($val, $args);
}
$sql = ltrim($sql, ',');
} elseif ($this->expr instanceof ToSqlInterface) {
$sql = $driver->deflate($this->expr, $args);
} elseif ($this->expr instanceof Raw) {
$sql = $this->expr->__toString();
} elseif (is_string($this->expr)) {
$sql = $this->expr;
} else {
throw new InvalidArgumentException('Invalid expr type');
}
return '(' . $sql . ')';
}
public static function deflate($value, $isa, BaseDriver $driver = NULL)
{
switch ($isa) {
case 'int':
return (int) $value;
case 'str':
return (string) $value;
case 'double':
return (double) $value;
case 'float':
return floatval($value);
case "json":
return json_encode($value);
case "DateTime":
if (is_a($value, 'DateTime', true)) {
return $value->format(DateTime::ATOM);
}
if (!$value) {
return NULL;
}
return $value;
// might return ""
// might return ""
case 'boolean':
case 'bool':
// Convert string into bool
if (is_string($value)) {
if ($value === '' || $value === '0' || strncasecmp($value, 'false', 5) == 0) {
$value = false;
} elseif ($value === '1' || strncasecmp($value, 'true', 4) == 0) {
$value = true;
}
}
if ($driver) {
return $driver->deflate($value);
}
return (bool) $value ? 1 : 0;
}
/* respect the data type to inflate value */
return $value;
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'CHANGE COLUMN ';
if (is_string($this->fromColumn)) {
$sql .= $driver->quoteIdentifier($this->fromColumn);
} elseif ($this->fromColumn instanceof Column) {
$sql .= $driver->quoteIdentifier($this->fromColumn->getName());
}
// the 'toColumn' must be a type of Column, we need at least column type to rename.
$sql .= ' ' . $driver->quoteIdentifier($this->toColumn->getName()) . ' ' . $this->toColumn->buildDefinitionSqlForModify($driver, $args);
if ($driver instanceof MySQLDriver) {
if ($this->after) {
$sql .= ' AFTER ' . $driver->quoteIdentifier($this->after);
} else {
if ($this->first) {
$sql .= ' FIRST';
}
}
}
return $sql;
}
/**
* For variable placeholder like PDO, we need 1 or 0 for boolean type,
*
* For pgsql and mysql sql statement,
* we use TRUE or FALSE for boolean type.
*
* FOr sqlite sql statement:
* we use 1 or 0 for boolean type.
*/
public function deflate($value, ArgumentArray $args = NULL)
{
// Special cases for SQLite
if ($value === true) {
return 1;
} elseif ($value === false) {
return 0;
} else {
return parent::deflate($value, $args);
}
return $value;
}
/**
Build reference
track(
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
artist_id INTEGER REFERENCES artist
)
MySQL Syntax:
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
A reference example:
PRIMARY KEY (`idEmployee`) ,
CONSTRAINT `fkEmployee_Addresses`
FOREIGN KEY `fkEmployee_Addresses` (`idAddresses`)
REFERENCES `schema`.`Addresses` (`idAddresses`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
*/
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = "CREATE";
if ($this->temporary) {
$sql .= " TEMPORARY";
}
$sql .= " TABLE " . $driver->quoteIdentifier($this->tableName);
$sql .= "(";
$columnClauses = array();
foreach ($this->columns as $col) {
$sql .= "\n" . $col->toSql($driver, $args) . ",";
}
if ($constraints = $this->getConstraints()) {
foreach ($constraints as $constraint) {
$sql .= "\n" . $constraint->toSql($driver, $args) . ",";
}
}
$sql = rtrim($sql, ',') . "\n)";
if ($this->engine && $driver instanceof MySQLDriver) {
$sql .= ' ENGINE=' . $this->engine;
}
return $sql;
}
public static function deflate($value, $isa, BaseDriver $driver = null)
{
switch ($isa) {
case 'int':
return (int) $value;
case 'str':
return (string) $value;
case 'double':
return (double) $value;
case 'float':
return floatval($value);
case 'json':
return json_encode($value);
case 'bool':
// Convert string into bool
if (is_string($value)) {
if ($value === '' || $value === '0' || strncasecmp($value, 'false', 5) == 0) {
$value = false;
} elseif ($value === '1' || strncasecmp($value, 'true', 4) == 0) {
$value = true;
}
}
if ($driver) {
return $driver->deflate($value);
}
return (bool) $value ? 1 : 0;
}
if ($value instanceof DateTime) {
if ($driver instanceof PDOMySQLDriver) {
return $value->format('Y-m-d H:i:s');
}
return $value->format(DateTime::ATOM);
}
/* respect the data type to inflate value */
return $value;
}
public function buildKeyClause(BaseDriver $driver, ArgumentArray $args)
{
$sql = $this->keyType;
// MySQL supports custom index name and index type
if ($driver instanceof MySQLDriver) {
if ($this->indexName) {
$sql .= ' ' . $driver->quoteIdentifier($this->indexName);
}
if ($this->indexType) {
$sql .= ' USING ' . $this->indexType;
}
}
$sql .= ' (' . $this->keyColumns->toSql($driver, $args) . ')';
if ($this->references) {
$sql .= ' ' . $this->references->toSql($driver, $args);
}
return $sql;
}
public function buildFromClause(BaseDriver $driver, ArgumentArray $args)
{
if (empty($this->deleteTables)) {
throw new IncompleteSettingsException('DeleteQuery requires tables to delete.');
}
$tableRefs = array();
foreach ($this->deleteTables as $k => $v) {
/* "column AS alias" OR just "column" */
if (is_string($k)) {
$sql = $driver->quoteTable($k) . ' AS ' . $v;
$tableRefs[] = $sql;
} elseif (is_integer($k) || is_numeric($k)) {
$sql = $driver->quoteTable($v);
$tableRefs[] = $sql;
}
}
return ' FROM ' . join(', ', $tableRefs);
}
public function buildFromClause(BaseDriver $driver, ArgumentArray $args)
{
$tableRefs = array();
foreach ($this->from as $k => $v) {
/* "column AS alias" OR just "column" */
if (is_string($k)) {
$sql = $driver->quoteTable($k) . ' AS ' . $v;
if ($driver instanceof MySQLDriver) {
if ($this->definedIndexHint($v)) {
$sql .= $this->buildIndexHintClauseByTableRef($v, $driver, $args);
} elseif ($this->definedIndexHint($k)) {
$sql .= $this->buildIndexHintClauseByTableRef($k, $driver, $args);
}
}
$tableRefs[] = $sql;
} elseif (is_integer($k) || is_numeric($k)) {
$sql = $driver->quoteTable($v);
if ($driver instanceof MySQLDriver && $this->definedIndexHint($v)) {
$sql .= $this->buildIndexHintClauseByTableRef($v, $driver, $args);
}
$tableRefs[] = $sql;
}
}
if (!empty($tableRefs)) {
return ' FROM ' . join(', ', $tableRefs);
}
return '';
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
return $this->exprStr . ' REGEXP ' . $driver->deflate($this->pat, $args);
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'DROP COLUMN ';
$sql .= $driver->quoteIdentifier($this->column->name);
return $sql;
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
return 'DROP INDEX ' . $driver->quoteIdentifier($this->indexName);
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
return $this->op . ' ' . $driver->deflate($this->operand, $args);
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'ALTER TABLE ' . $driver->quoteIdentifier($this->table) . ' ';
$alterSpecSqls = array();
foreach ($this->specs as $spec) {
$alterSpecSqls[] = $spec->toSql($driver, $args);
}
$sql .= join(",\n ", $alterSpecSqls);
return $sql;
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
return $this->exprStr . ' BETWEEN ' . $driver->deflate($this->min, $args) . ' AND ' . $driver->deflate($this->max, $args);
}
public function buildFromClause(BaseDriver $driver, ArgumentArray $args)
{
if (empty($this->updateTables)) {
throw new IncompleteSettingsException('UpdateQuery requires at least one table to update.');
}
$tableRefs = array();
foreach ($this->updateTables as $k => $alias) {
/* "column AS alias" OR just "column" */
if (is_string($k)) {
$sql = $driver->quoteTable($k) . ' AS ' . $alias;
if ($driver instanceof MySQLDriver) {
if ($this->definedIndexHint($alias)) {
$sql .= $this->buildIndexHintClauseByTableRef($alias, $driver, $args);
} elseif ($this->definedIndexHint($k)) {
$sql .= $this->buildIndexHintClauseByTableRef($k, $driver, $args);
}
}
$tableRefs[] = $sql;
} elseif (is_integer($k) || is_numeric($k)) {
$sql = $driver->quoteTable($alias);
if ($driver instanceof MySQLDriver) {
if ($this->definedIndexHint($alias)) {
$sql .= $this->buildIndexHintClauseByTableRef($alias, $driver, $args);
}
}
$tableRefs[] = $sql;
}
}
return ' ' . join(', ', $tableRefs);
}
protected function buildMySQLQuery(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'CREATE';
if ($this->type) {
// validate index type
$sql .= ' ' . $this->type;
}
$sql .= ' INDEX';
$sql .= ' ' . $driver->quoteIdentifier($this->name) . ' ON ' . $driver->quoteIdentifier($this->tableName);
if (!empty($this->columns)) {
$sql .= ' (' . join(',', $this->columns) . ')';
}
if ($this->method) {
$sql .= ' USING ' . $this->method;
}
return $sql;
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
return 'AUTO_INCREMENT = ' . $driver->deflate($this->value);
}
public function toSql(BaseDriver $driver, ArgumentArray $args)
{
$sql = 'CREATE DATABASE';
if ($this->ifNotExists && $driver instanceof MySQLDriver) {
$sql .= ' IF NOT EXISTS';
}
$sql .= ' ' . $driver->quoteIdentifier($this->dbName);
if ($driver instanceof MySQLDriver) {
if ($this->characterSet) {
$sql .= ' CHARACTER SET ' . $driver->quote($this->characterSet);
}
if ($this->collate) {
$sql .= ' COLLATE ' . $driver->quote($this->collate);
}
} elseif ($driver instanceof PgSQLDriver) {
/**
* PostgreSQL properties
*/
if ($this->owner) {
$sql .= ' OWNER ' . $driver->quote($this->owner);
}
if ($this->template) {
$sql .= ' TEMPLATE ' . $driver->quote($this->template);
}
if ($this->encoding) {
$sql .= ' ENCODING ' . $driver->quote($this->encoding);
}
if ($this->collate) {
$sql .= ' LC_COLLATE ' . $driver->quote($this->collate);
}
if ($this->ctype) {
$sql .= ' LC_CTYPE ' . $driver->quote($this->ctype);
}
if ($this->tablespace) {
$sql .= ' TABLESPACE ' . $driver->quote($this->tablespace);
}
if ($this->connectionLimit) {
$sql .= ' CONNECTION LIMIT ' . $this->connectionLimit;
}
}
return $sql;
}
public function buildDefinitionSql(BaseDriver $driver, ArgumentArray $args)
{
$isa = $this->isa ?: 'str';
$sql = '';
$sql .= $driver->quoteIdentifier($this->name);
$sql .= $this->buildTypeClause($driver);
$sql .= $this->buildUnsignedClause($driver);
$sql .= $this->buildNullClause($driver);
$sql .= $this->buildDefaultClause($driver);
$sql .= $this->buildPrimaryKeyClause($driver);
$sql .= $this->buildAutoIncrementClause($driver);
$sql .= $this->buildUniqueClause($driver);
if ($this->comment) {
$sql .= ' COMMENT ' . $driver->deflate($this->comment);
}
return $sql;
}
