0 follower

CSqliteSchema

Package system.db.schema.sqlite
Inheritance class CSqliteSchema » CDbSchema » CComponent
Since 1.0
Source Code framework/db/schema/sqlite/CSqliteSchema.php
CSqliteSchema is the class for retrieving metadata information from a SQLite (2/3) database.

Public Properties

Hide inherited properties

PropertyTypeDescriptionDefined By
columnTypes array the abstract column types mapped to physical column types. CSqliteSchema
commandBuilder CDbCommandBuilder the SQL command builder for this connection. CDbSchema
dbConnection CDbConnection database connection. CDbSchema
tableNames array Returns all table names in the database. CDbSchema
tables array Returns the metadata for all tables in the database. CDbSchema

Public Methods

Hide inherited methods

MethodDescriptionDefined By
__call() Calls the named method which is not a class method. CComponent
__construct() Constructor. CDbSchema
__get() Returns a property value, an event handler list or a behavior based on its name. CComponent
__isset() Checks if a property value is null. CComponent
__set() Sets value of a component property. CComponent
__unset() Sets a component property to be null. CComponent
addColumn() Builds a SQL statement for adding a new DB column. CDbSchema
addForeignKey() Builds a SQL statement for adding a foreign key constraint to an existing table. CSqliteSchema
addPrimaryKey() Builds a SQL statement for adding a primary key constraint to an existing table. CSqliteSchema
alterColumn() Builds a SQL statement for changing the definition of a column. CSqliteSchema
asa() Returns the named behavior object. CComponent
attachBehavior() Attaches a behavior to this component. CComponent
attachBehaviors() Attaches a list of behaviors to the component. CComponent
attachEventHandler() Attaches an event handler to an event. CComponent
canGetProperty() Determines whether a property can be read. CComponent
canSetProperty() Determines whether a property can be set. CComponent
checkIntegrity() Enables or disables integrity check. Note that this method used to do nothing before 1.1.14. Since 1.1.14 CSqliteSchema
compareTableNames() Compares two table names. CDbSchema
createIndex() Builds a SQL statement for creating a new index. CDbSchema
createTable() Builds a SQL statement for creating a new DB table. CDbSchema
detachBehavior() Detaches a behavior from the component. CComponent
detachBehaviors() Detaches all behaviors from the component. CComponent
detachEventHandler() Detaches an existing event handler. CComponent
disableBehavior() Disables an attached behavior. CComponent
disableBehaviors() Disables all behaviors attached to this component. CComponent
dropColumn() Builds a SQL statement for dropping a DB column. CSqliteSchema
dropForeignKey() Builds a SQL statement for dropping a foreign key constraint. CSqliteSchema
dropIndex() Builds a SQL statement for dropping an index. CSqliteSchema
dropPrimaryKey() Builds a SQL statement for removing a primary key constraint to an existing table. CSqliteSchema
dropTable() Builds a SQL statement for dropping a DB table. CDbSchema
enableBehavior() Enables an attached behavior. CComponent
enableBehaviors() Enables all behaviors attached to this component. CComponent
evaluateExpression() Evaluates a PHP expression or callback under the context of this component. CComponent
getColumnType() Converts an abstract column type into a physical column type. CDbSchema
getCommandBuilder() Returns the SQL command builder for this connection. CDbSchema
getDbConnection() Returns database connection. The connection is active. CDbSchema
getEventHandlers() Returns the list of attached event handlers for an event. CComponent
getTable() Obtains the metadata for the named table. CDbSchema
getTableNames() Returns all table names in the database. CDbSchema
getTables() Returns the metadata for all tables in the database. CDbSchema
hasEvent() Determines whether an event is defined. CComponent
hasEventHandler() Checks whether the named event has attached handlers. CComponent
hasProperty() Determines whether a property is defined. CComponent
quoteColumnName() Quotes a column name for use in a query. CDbSchema
quoteSimpleColumnName() Quotes a simple column name for use in a query. CDbSchema
quoteSimpleTableName() Quotes a simple table name for use in a query. CDbSchema
quoteTableName() Quotes a table name for use in a query. CDbSchema
raiseEvent() Raises an event. CComponent
refresh() Refreshes the schema. CDbSchema
renameColumn() Builds a SQL statement for renaming a column. CSqliteSchema
renameTable() Builds a SQL statement for renaming a DB table. CSqliteSchema
resetSequence() Resets the sequence value of a table's primary key. CSqliteSchema
truncateTable() Builds a SQL statement for truncating a DB table. CSqliteSchema

Protected Methods

Hide inherited methods

MethodDescriptionDefined By
createColumn() Creates a table column. CSqliteSchema
createCommandBuilder() Creates a command builder for the database. CSqliteSchema
findColumns() Collects the table column metadata. CSqliteSchema
findConstraints() Collects the foreign key column details for the given table. CSqliteSchema
findTableNames() Returns all table names in the database. CSqliteSchema
loadTable() Loads the metadata for the specified table. CSqliteSchema

Property Details

columnTypes property (available since v1.1.6)
public array $columnTypes;

the abstract column types mapped to physical column types.

Method Details

addForeignKey() method (available since v1.1.6)
public string addForeignKey(string $name, string $table, string $columns, string $refTable, string $refColumns, string $delete=NULL, string $update=NULL)
$name string the name of the foreign key constraint.
$table string the table that the foreign key constraint will be added to.
$columns string the name of the column to that the constraint will be added on. If there are multiple columns, separate them with commas.
$refTable string the table that the foreign key references to.
$refColumns string the name of the column that the foreign key references to. If there are multiple columns, separate them with commas.
$delete string the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
$update string the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
{return} string the SQL statement for adding a foreign key constraint to an existing table.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#264 (show)
public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete=null, $update=null)
{
    throw new CDbException(Yii::t('yii', 'Adding a foreign key constraint to an existing table is not supported by SQLite.'));
}

Builds a SQL statement for adding a foreign key constraint to an existing table. Because SQLite does not support adding foreign key to an existing table, calling this method will throw an exception.

addPrimaryKey() method (available since v1.1.13)
public string addPrimaryKey(string $name, string $table, string|array $columns)
$name string the name of the primary key constraint.
$table string the table that the primary key constraint will be added to.
$columns string|array comma separated string or array of columns that the primary key will consist of.
{return} string the SQL statement for adding a primary key constraint to an existing table.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#322 (show)
public function addPrimaryKey($name,$table,$columns)
{
    throw new CDbException(Yii::t('yii', 'Adding a primary key after table has been created is not supported by SQLite.'));
}

Builds a SQL statement for adding a primary key constraint to an existing table. Because SQLite does not support adding a primary key on an existing table this method will throw an exception.

alterColumn() method (available since v1.1.6)
public string alterColumn(string $table, string $column, string $type)
$table string the table whose column is to be changed. The table name will be properly quoted by the method.
$column string the name of the column to be changed. The name will be properly quoted by the method.
$type string the new column type. The getColumnType method will be invoked to convert abstract column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
{return} string the SQL statement for changing the definition of a column.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#295 (show)
public function alterColumn($table, $column, $type)
{
    throw new CDbException(Yii::t('yii', 'Altering a DB column is not supported by SQLite.'));
}

Builds a SQL statement for changing the definition of a column. Because SQLite does not support altering a DB column, calling this method will throw an exception.

checkIntegrity() method (available since v1.1)
public void checkIntegrity(boolean $check=true, string $schema='')
$check boolean whether to turn on or off the integrity check.
$schema string the schema of the tables. Defaults to empty string, meaning the current or default schema.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#81 (show)
public function checkIntegrity($check=true,$schema='')
{
    $this->getDbConnection()->createCommand('PRAGMA foreign_keys='.(int)$check)->execute();
}

Enables or disables integrity check. Note that this method used to do nothing before 1.1.14. Since 1.1.14 it changes integrity check state as expected.

createColumn() method
protected CDbColumnSchema createColumn(array $column)
$column array column metadata
{return} CDbColumnSchema normalized column metadata
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#184 (show)
protected function createColumn($column)
{
    $c=new CSqliteColumnSchema;
    $c->name=$column['name'];
    $c->rawName=$this->quoteColumnName($c->name);
    $c->allowNull=!$column['notnull'];
    $c->isPrimaryKey=$column['pk']!=0;
    $c->isForeignKey=false;
    $c->comment=null; // SQLite does not support column comments at all

    $c->init(strtolower($column['type']),$column['dflt_value']);
    return $c;
}

Creates a table column.

createCommandBuilder() method
protected CSqliteCommandBuilder createCommandBuilder()
{return} CSqliteCommandBuilder command builder instance
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#101 (show)
protected function createCommandBuilder()
{
    return new CSqliteCommandBuilder($this);
}

Creates a command builder for the database.

dropColumn() method (available since v1.1.6)
public string dropColumn(string $table, string $column)
$table string the table whose column is to be dropped. The name will be properly quoted by the method.
$column string the name of the column to be dropped. The name will be properly quoted by the method.
{return} string the SQL statement for dropping a DB column.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#230 (show)
public function dropColumn($table, $column)
{
    throw new CDbException(Yii::t('yii', 'Dropping DB column is not supported by SQLite.'));
}

Builds a SQL statement for dropping a DB column. Because SQLite does not support dropping a DB column, calling this method will throw an exception.

dropForeignKey() method (available since v1.1.6)
public string dropForeignKey(string $name, string $table)
$name string the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
$table string the table whose foreign is to be dropped. The name will be properly quoted by the method.
{return} string the SQL statement for dropping a foreign key constraint.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#278 (show)
public function dropForeignKey($name, $table)
{
    throw new CDbException(Yii::t('yii', 'Dropping a foreign key constraint is not supported by SQLite.'));
}

Builds a SQL statement for dropping a foreign key constraint. Because SQLite does not support dropping a foreign key constraint, calling this method will throw an exception.

dropIndex() method (available since v1.1.6)
public string dropIndex(string $name, string $table)
$name string the name of the index to be dropped. The name will be properly quoted by the method.
$table string the table whose index is to be dropped. The name will be properly quoted by the method.
{return} string the SQL statement for dropping an index.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#307 (show)
public function dropIndex($name, $table)
{
    return 'DROP INDEX '.$this->quoteTableName($name);
}

Builds a SQL statement for dropping an index.

dropPrimaryKey() method (available since v1.1.13)
public string dropPrimaryKey(string $name, string $table)
$name string the name of the primary key constraint to be removed.
$table string the table that the primary key constraint will be removed from.
{return} string the SQL statement for removing a primary key constraint from an existing table.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#337 (show)
public function dropPrimaryKey($name,$table)
{
    throw new CDbException(Yii::t('yii', 'Removing a primary key after table has been created is not supported by SQLite.'));

}

Builds a SQL statement for removing a primary key constraint to an existing table. Because SQLite does not support dropping a primary key from an existing table this method will throw an exception

findColumns() method
protected boolean findColumns(CDbTableSchema $table)
$table CDbTableSchema the table metadata
{return} boolean whether the table exists in the database
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#131 (show)
protected function findColumns($table)
{
    $sql="PRAGMA table_info({$table->rawName})";
    $columns=$this->getDbConnection()->createCommand($sql)->queryAll();
    if(empty($columns))
        return false;

    foreach($columns as $column)
    {
        $c=$this->createColumn($column);
        $table->columns[$c->name]=$c;
        if($c->isPrimaryKey)
        {
            if($table->primaryKey===null)
                $table->primaryKey=$c->name;
            elseif(is_string($table->primaryKey))
                $table->primaryKey=array($table->primaryKey,$c->name);
            else
                $table->primaryKey[]=$c->name;
        }
    }
    if(is_string($table->primaryKey) && !strncasecmp($table->columns[$table->primaryKey]->dbType,'int',3))
    {
        $table->sequenceName='';
        $table->columns[$table->primaryKey]->autoIncrement=true;
    }

    return true;
}

Collects the table column metadata.

findConstraints() method
protected void findConstraints(CDbTableSchema $table)
$table CDbTableSchema the table metadata
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#165 (show)
protected function findConstraints($table)
{
    $foreignKeys=array();
    $sql="PRAGMA foreign_key_list({$table->rawName})";
    $keys=$this->getDbConnection()->createCommand($sql)->queryAll();
    foreach($keys as $key)
    {
        $column=$table->columns[$key['from']];
        $column->isForeignKey=true;
        $foreignKeys[$key['from']]=array($key['table'],$key['to']);
    }
    $table->foreignKeys=$foreignKeys;
}

Collects the foreign key column details for the given table.

findTableNames() method
protected array findTableNames(string $schema='')
$schema string the schema of the tables. This is not used for sqlite database.
{return} array all table names in the database.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#91 (show)
protected function findTableNames($schema='')
{
    $sql="SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'";
    return $this->getDbConnection()->createCommand($sql)->queryColumn();
}

Returns all table names in the database.

loadTable() method
protected CDbTableSchema loadTable(string $name)
$name string table name
{return} CDbTableSchema driver dependent table metadata. Null if the table does not exist.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#111 (show)
protected function loadTable($name)
{
    $table=new CDbTableSchema;
    $table->name=$name;
    $table->rawName=$this->quoteTableName($name);

    if($this->findColumns($table))
    {
        $this->findConstraints($table);
        return $table;
    }
    else
        return null;
}

Loads the metadata for the specified table.

renameColumn() method (available since v1.1.6)
public string renameColumn(string $table, string $name, string $newName)
$table string the table whose column is to be renamed. The name will be properly quoted by the method.
$name string the old name of the column. The name will be properly quoted by the method.
$newName string the new name of the column. The name will be properly quoted by the method.
{return} string the SQL statement for renaming a DB column.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#245 (show)
public function renameColumn($table, $name, $newName)
{
    throw new CDbException(Yii::t('yii', 'Renaming a DB column is not supported by SQLite.'));
}

Builds a SQL statement for renaming a column. Because SQLite does not support renaming a DB column, calling this method will throw an exception.

renameTable() method (available since v1.1.13)
public string renameTable(string $table, string $newName)
$table string the table to be renamed. The name will be properly quoted by the method.
$newName string the new table name. The name will be properly quoted by the method.
{return} string the SQL statement for renaming a DB table.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#205 (show)
public function renameTable($table, $newName)
{
    return 'ALTER TABLE ' . $this->quoteTableName($table) . ' RENAME TO ' . $this->quoteTableName($newName);
}

Builds a SQL statement for renaming a DB table.

resetSequence() method (available since v1.1)
public void resetSequence(CDbTableSchema $table, integer|null $value=NULL)
$table CDbTableSchema the table schema whose primary key sequence will be reset
$value integer|null the value for the primary key of the next new row inserted. If this is not set, the next new row's primary key will have the max value of a primary key plus one (i.e. sequence trimming).
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#52 (show)
public function resetSequence($table,$value=null)
{
    if($table->sequenceName===null)
        return;
    if($value!==null)
        $value=(int)($value)-1;
    else
        $value=(int)$this->getDbConnection()
            ->createCommand("SELECT MAX(`{$table->primaryKey}`) FROM {$table->rawName}")
            ->queryScalar();
    try
    {
        // it's possible that 'sqlite_sequence' does not exist
        $this->getDbConnection()
            ->createCommand("UPDATE sqlite_sequence SET seq='$value' WHERE name='{$table->name}'")
            ->execute();
    }
    catch(Exception $e)
    {
    }
}

Resets the sequence value of a table's primary key. The sequence will be reset such that the primary key of the next new row inserted will have the specified value or max value of a primary key plus one (i.e. sequence trimming).

truncateTable() method (available since v1.1.6)
public string truncateTable(string $table)
$table string the table to be truncated. The name will be properly quoted by the method.
{return} string the SQL statement for truncating a DB table.
Source Code: framework/db/schema/sqlite/CSqliteSchema.php#216 (show)
public function truncateTable($table)
{
    return "DELETE FROM ".$this->quoteTableName($table);
}

Builds a SQL statement for truncating a DB table.