| Namespace | Phalcon\Db | Uses | \PDO |
Phalcon\Db and its related classes provide a simple SQL database interface for Phalcon Framework. The Phalcon\Db is the basic class you use to connect your PHP application to an RDBMS. There is a different adapter class for each brand of RDBMS.
This component is intended to lower level database operations. If you want to interact with databases using higher level of abstraction use Phalcon\Mvc\Model.
Phalcon\Db\AbstractDb is an abstract class. You only can use it with a database adapter like Phalcon\Db\Adapter\Pdo
Czech
German
Greek
English (US)
Spanish
Persian
French
Indonesian
Italian
Korean
Polish
Portuguese, Brazilian
Russian
Turkish
Ukrainian
Chinese Simplifieduse Phalcon\Db;
use Phalcon\Db\Exception;
use Phalcon\Db\Adapter\Pdo\Mysql as MysqlConnection;
try {
$connection = new MysqlConnection(
[
"host" => "192.168.0.11",
"username" => "sigma",
"password" => "secret",
"dbname" => "blog",
"port" => "3306",
]
);
$result = $connection->query(
"SELECTFROM robots LIMIT 5"
);
$result->setFetchMode(Enum::FETCH_NUM);
while ($robot = $result->fetch()) {
print_r($robot);
}
} catch (Exception $e) {
echo $e->getMessage(), PHP_EOL;
}
public static function setup( array $options ): void;
Enables/disables options in the Database component
| Namespace | Phalcon\Db\Adapter | Uses | Phalcon\Db\DialectInterface, Phalcon\Db\ColumnInterface, Phalcon\Db\Enum, Phalcon\Db\Exception, Phalcon\Db\Index, Phalcon\Db\IndexInterface, Phalcon\Db\Reference, Phalcon\Db\ReferenceInterface, Phalcon\Db\RawValue, Phalcon\Events\EventsAwareInterface, Phalcon\Events\ManagerInterface | Implements | AdapterInterface, EventsAwareInterface |
Base class for Phalcon\Db\Adapter adapters
/**
* Connection ID
*
* @var int
*/
protected static connectionConsecutive = 0;
/**
* Active connection ID
*
* @var int
*/
protected connectionId;
/**
* Descriptor used to connect to a database
*
* @var array
*/
protected descriptor;
/**
* Dialect instance
*
* @var object
*/
protected dialect;
/**
* Name of the dialect used
*
* @var string
*/
protected dialectType;
/**
* Event Manager
*
* @var ManagerInterface|null
*/
protected eventsManager;
/**
* The real SQL statement - what was executed
*
* @var string
*/
protected realSqlStatement;
/**
* Active SQL Bind Types
*
* @var array
*/
protected sqlBindTypes;
/**
* Active SQL Statement
*
* @var string
*/
protected sqlStatement;
/**
* Active SQL bound parameter variables
*
* @var array
*/
protected sqlVariables;
/**
* Current transaction level
*
* @var int
*/
protected transactionLevel = 0;
/**
* Whether the database supports transactions with save points
*
* @var bool
*/
protected transactionsWithSavepoints = false;
/**
* Type of database system the adapter is used for
*
* @var string
*/
protected type;
public function __construct( array $descriptor );
Phalcon\Db\Adapter constructor
public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): bool;
Adds a column to a table
public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): bool;
Adds a foreign key to a table
public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): bool;
Adds an index to a table
public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): bool;
Adds a primary key to a table
public function createSavepoint( string $name ): bool;
Creates a new savepoint
public function createTable( string $tableName, string $schemaName, array $definition ): bool;
Creates a table
public function createView( string $viewName, array $definition, string $schemaName = null ): bool;
Creates a view
public function delete( mixed $table, string $whereCondition = null, array $placeholders = [], array $dataTypes = [] ): bool;
Deletes data from a table using custom RBDM SQL syntax
// Deleting existing robot
$success = $connection->delete(
"robots",
"id = 101"
);
// Next SQL sentence is generated
DELETE FROM `robots` WHERE `id` = 101
public function describeIndexes( string $table, string $schema = null ): IndexInterface[];
Lists table indexes
print_r(
$connection->describeIndexes("robots_parts")
);
public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];
Lists table references
print_r(
$connection->describeReferences("robots_parts")
);
public function dropColumn( string $tableName, string $schemaName, string $columnName ): bool;
Drops a column from a table
public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): bool;
Drops a foreign key from a table
public function dropIndex( string $tableName, string $schemaName, mixed $indexName ): bool;
Drop an index from a table
public function dropPrimaryKey( string $tableName, string $schemaName ): bool;
Drops a table’s primary key
public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): bool;
Drops a table from a schema/database
public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): bool;
Drops a view
public function escapeIdentifier( mixed $identifier ): string;
Escapes a column/table/schema name
$escapedTable = $connection->escapeIdentifier(
"robots"
);
$escapedTable = $connection->escapeIdentifier(
[
"store",
"robots",
]
);
public function fetchAll( string $sqlQuery, int $fetchMode = static-constant-access, array $bindParams = [], array $bindTypes = [] ): array;
Dumps the complete result of a query into an array
// Getting all robots with associative indexes only
$robots = $connection->fetchAll(
"SELECTFROM robots",
\Phalcon\Db\Enum::FETCH_ASSOC
);
foreach ($robots as $robot) {
print_r($robot);
}
// Getting all robots that contains word "robot" withing the name
$robots = $connection->fetchAll(
"SELECTFROM robots WHERE name LIKE :name",
\Phalcon\Db\Enum::FETCH_ASSOC,
[
"name" => "%robot%",
]
);
foreach($robots as $robot) {
print_r($robot);
}
public function fetchColumn( string $sqlQuery, array $placeholders = [], mixed $column = int ): string | bool;
Returns the n’th field of first row in a SQL query result
// Getting count of robots
$robotsCount = $connection->fetchColumn("SELECT count(*) FROM robots");
print_r($robotsCount);
// Getting name of last edited robot
$robot = $connection->fetchColumn(
"SELECT id, name FROM robots ORDER BY modified DESC",
1
);
print_r($robot);
public function fetchOne( string $sqlQuery, mixed $fetchMode = static-constant-access, array $bindParams = [], array $bindTypes = [] ): array;
Returns the first row in a SQL query result
// Getting first robot
$robot = $connection->fetchOne("SELECTFROM robots");
print_r($robot);
// Getting first robot with associative indexes only
$robot = $connection->fetchOne(
"SELECTFROM robots",
\Phalcon\Db\Enum::FETCH_ASSOC
);
print_r($robot);
public function forUpdate( string $sqlQuery ): string;
Returns a SQL modified with a FOR UPDATE clause
public function getColumnDefinition( ColumnInterface $column ): string;
Returns the SQL column definition from a column
public function getColumnList( mixed $columnList ): string;
Gets a list of columns
public function getConnectionId(): string;
Gets the active connection unique identifier
public function getDefaultIdValue(): RawValue;
Returns the default identity value to be inserted in an identity column
// Inserting a new robot with a valid default value for the column 'id'
$success = $connection->insert(
"robots",
[
$connection->getDefaultIdValue(),
"Astro Boy",
1952,
],
[
"id",
"name",
"year",
]
);
public function getDefaultValue(): RawValue;
Returns the default value to make the RBDM use the default value declared in the table definition
// Inserting a new robot with a valid default value for the column 'year'
$success = $connection->insert(
"robots",
[
"Astro Boy",
$connection->getDefaultValue()
],
[
"name",
"year",
]
);
@todo Return NULL if this is not supported by the adapter
public function getDescriptor(): array;
Return descriptor used to connect to the active database
public function getDialect(): DialectInterface;
Returns internal dialect instance
public function getDialectType(): string;
Name of the dialect used
public function getEventsManager(): ManagerInterface | null;
内部イベントマネージャーを返します
public function getNestedTransactionSavepointName(): string;
Returns the savepoint name to use for nested transactions
public function getRealSQLStatement(): string;
Active SQL statement in the object without replace bound parameters
public function getSQLBindTypes(): array;
Active SQL statement in the object
public function getSQLStatement(): string;
Active SQL statement in the object
public function getSQLVariables(): array;
Active SQL variables in the object
public function getType(): string;
Type of database system the adapter is used for
public function insert( string $table, array $values, mixed $fields = null, mixed $dataTypes = null ): bool;
Inserts data into a table using custom RDBMS SQL syntax
// Inserting a new robot
$success = $connection->insert(
"robots",
["Astro Boy", 1952],
["name", "year"]
);
// Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);
public function insertAsDict( string $table, mixed $data, mixed $dataTypes = null ): bool;
Inserts data into a table using custom RBDM SQL syntax
// Inserting a new robot
$success = $connection->insertAsDict(
"robots",
[
"name" => "Astro Boy",
"year" => 1952,
]
);
// Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);
public function isNestedTransactionsWithSavepoints(): bool;
Returns if nested transactions should use savepoints
public function limit( string $sqlQuery, int $number ): string;
Appends a LIMIT clause to $sqlQuery argument
echo $connection->limit("SELECTFROM robots", 5);
public function listTables( string $schemaName = null ): array;
List all tables on a database
print_r(
$connection->listTables("blog")
);
public function listViews( string $schemaName = null ): array;
List all views on a database
print_r(
$connection->listViews("blog")
);
public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): bool;
Modifies a table column based on a definition
public function releaseSavepoint( string $name ): bool;
Releases given savepoint
public function rollbackSavepoint( string $name ): bool;
Rollbacks given savepoint
public function setDialect( DialectInterface $dialect );
Sets the dialect used to produce the SQL
public function setEventsManager( ManagerInterface $eventsManager ): void;
Sets the event manager
public function setNestedTransactionsWithSavepoints( bool $nestedTransactionsWithSavepoints ): AdapterInterface;
Set if nested transactions should use savepoints
public function sharedLock( string $sqlQuery ): string;
Returns a SQL modified with a LOCK IN SHARE MODE clause
public function supportSequences(): bool;
Check whether the database system requires a sequence to produce auto-numeric values
public function supportsDefaultValue(): bool;
Check whether the database system support the DEFAULT keyword (SQLite does not support it)
@deprecated Will re removed in the next version
public function tableExists( string $tableName, string $schemaName = null ): bool;
Generates SQL checking for the existence of a schema.table
var_dump(
$connection->tableExists("blog", "posts")
);
public function tableOptions( string $tableName, string $schemaName = null ): array;
Gets creation options from a table
print_r(
$connection->tableOptions("robots")
);
public function update( string $table, mixed $fields, mixed $values, mixed $whereCondition = null, mixed $dataTypes = null ): bool;
Updates data on a table using custom RBDM SQL syntax
// Updating existing robot
$success = $connection->update(
"robots",
["name"],
["New Astro Boy"],
"id = 101"
);
// Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101
// Updating existing robot with array condition and $dataTypes
$success = $connection->update(
"robots",
["name"],
["New Astro Boy"],
[
"conditions" => "id = ?",
"bind" => [$some_unsafe_id],
"bindTypes" => [PDO::PARAM_INT], // use only if you use $dataTypes param
],
[
PDO::PARAM_STR
]
);
Warning! If $whereCondition is string it not escaped.
public function updateAsDict( string $table, mixed $data, mixed $whereCondition = null, mixed $dataTypes = null ): bool;
Updates data on a table using custom RBDM SQL syntax Another, more convenient syntax
// Updating existing robot
$success = $connection->updateAsDict(
"robots",
[
"name" => "New Astro Boy",
],
"id = 101"
);
// Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101
public function useExplicitIdValue(): bool;
Check whether the database system requires an explicit value for identity columns
public function viewExists( string $viewName, string $schemaName = null ): bool;
Generates SQL checking for the existence of a schema.view
var_dump(
$connection->viewExists("active_users", "posts")
);
| Namespace | Phalcon\Db\Adapter | Uses | Phalcon\Db\DialectInterface, Phalcon\Db\ResultInterface, Phalcon\Db\ColumnInterface, Phalcon\Db\IndexInterface, Phalcon\Db\RawValue, Phalcon\Db\ReferenceInterface |
Interface for Phalcon\Db adapters
public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): bool;
Adds a column to a table
public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): bool;
Adds a foreign key to a table
public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): bool;
Adds an index to a table
public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): bool;
Adds a primary key to a table
public function affectedRows(): int;
Returns the number of affected rows by the last INSERT/UPDATE/DELETE reported by the database system
public function begin( bool $nesting = bool ): bool;
Starts a transaction in the connection
public function close(): void;
Closes active connection returning success. Phalcon automatically closes and destroys active connections within Phalcon\Db\Pool
public function commit( bool $nesting = bool ): bool;
Commits the active transaction in the connection
public function connect( array $descriptor = [] ): void;
This method is automatically called in \Phalcon\Db\Adapter\Pdo constructor. Call it when you need to restore a database connection
public function createSavepoint( string $name ): bool;
Creates a new savepoint
public function createTable( string $tableName, string $schemaName, array $definition ): bool;
Creates a table
public function createView( string $viewName, array $definition, string $schemaName = null ): bool;
Creates a view
public function delete( mixed $table, string $whereCondition = null, array $placeholders = [], array $dataTypes = [] ): bool;
Deletes data from a table using custom RDBMS SQL syntax
public function describeColumns( string $table, string $schema = null ): ColumnInterface[];
Returns an array of Phalcon\Db\Column objects describing a table
public function describeIndexes( string $table, string $schema = null ): IndexInterface[];
Lists table indexes
public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];
Lists table references
public function dropColumn( string $tableName, string $schemaName, string $columnName ): bool;
Drops a column from a table
public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): bool;
Drops a foreign key from a table
public function dropIndex( string $tableName, string $schemaName, string $indexName ): bool;
Drop an index from a table
public function dropPrimaryKey( string $tableName, string $schemaName ): bool;
Drops primary key from a table
public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): bool;
Drops a table from a schema/database
public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): bool;
Drops a view
public function escapeIdentifier( mixed $identifier ): string;
Escapes a column/table/schema name
public function escapeString( string $str ): string;
Escapes a value to avoid SQL injections
public function execute( string $sqlStatement, array $bindParams = [], array $bindTypes = [] ): bool;
Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server doesn’t return any rows
public function fetchAll( string $sqlQuery, int $fetchMode = int, array $bindParams = [], array $bindTypes = [] ): array;
Dumps the complete result of a query into an array
public function fetchColumn( string $sqlQuery, array $placeholders = [], mixed $column = int ): string | bool;
Returns the n’th field of first row in a SQL query result
// Getting count of robots
$robotsCount = $connection->fetchColumn("SELECT COUNT(*) FROM robots");
print_r($robotsCount);
// Getting name of last edited robot
$robot = $connection->fetchColumn(
"SELECT id, name FROM robots ORDER BY modified DESC",
1
);
print_r($robot);
public function fetchOne( string $sqlQuery, int $fetchMode = int, array $bindParams = [], array $bindTypes = [] ): array;
Returns the first row in a SQL query result
public function forUpdate( string $sqlQuery ): string;
Returns a SQL modified with a FOR UPDATE clause
public function getColumnDefinition( ColumnInterface $column ): string;
Returns the SQL column definition from a column
public function getColumnList( mixed $columnList ): string;
Gets a list of columns
public function getConnectionId(): string;
Gets the active connection unique identifier
public function getDefaultIdValue(): RawValue;
Return the default identity value to insert in an identity column
public function getDefaultValue(): RawValue;
Returns the default value to make the RBDM use the default value declared in the table definition
// Inserting a new robot with a valid default value for the column 'year'
$success = $connection->insert(
"robots",
[
"Astro Boy",
$connection->getDefaultValue()
],
[
"name",
"year",
]
);
@todo Return NULL if this is not supported by the adapter
public function getDescriptor(): array;
Return descriptor used to connect to the active database
public function getDialect(): DialectInterface;
Returns internal dialect instance
public function getDialectType(): string;
Returns the name of the dialect used
public function getInternalHandler(): mixed;
Return internal PDO handler
public function getNestedTransactionSavepointName(): string;
Returns the savepoint name to use for nested transactions
public function getRealSQLStatement(): string;
Active SQL statement in the object without replace bound parameters
public function getSQLBindTypes(): array;
Active SQL statement in the object
public function getSQLStatement(): string;
Active SQL statement in the object
public function getSQLVariables(): array;
Active SQL statement in the object
public function getType(): string;
Returns type of database system the adapter is used for
public function insert( string $table, array $values, mixed $fields = null, mixed $dataTypes = null ): bool;
Inserts data into a table using custom RDBMS SQL syntax
public function insertAsDict( string $table, mixed $data, mixed $dataTypes = null ): bool;
Inserts data into a table using custom RBDM SQL syntax
// Inserting a new robot
$success = $connection->insertAsDict(
"robots",
[
"name" => "Astro Boy",
"year" => 1952,
]
);
// Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);
public function isNestedTransactionsWithSavepoints(): bool;
Returns if nested transactions should use savepoints
public function isUnderTransaction(): bool;
Checks whether connection is under database transaction
public function lastInsertId( string $name = null ): string | bool;
Returns insert id for the auto_increment column inserted in the last SQL statement
public function limit( string $sqlQuery, int $number ): string;
Appends a LIMIT clause to sqlQuery argument
public function listTables( string $schemaName = null ): array;
List all tables on a database
public function listViews( string $schemaName = null ): array;
List all views on a database
public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): bool;
Modifies a table column based on a definition
public function query( string $sqlStatement, array $bindParams = [], array $bindTypes = [] ): ResultInterface | bool;
Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server returns rows
public function releaseSavepoint( string $name ): bool;
Releases given savepoint
public function rollback( bool $nesting = bool ): bool;
Rollbacks the active transaction in the connection
public function rollbackSavepoint( string $name ): bool;
Rollbacks given savepoint
public function setNestedTransactionsWithSavepoints( bool $nestedTransactionsWithSavepoints ): AdapterInterface;
Set if nested transactions should use savepoints
public function sharedLock( string $sqlQuery ): string;
Returns a SQL modified with a LOCK IN SHARE MODE clause
public function supportSequences(): bool;
Check whether the database system requires a sequence to produce auto-numeric values
public function supportsDefaultValue(): bool;
SQLite does not support the DEFAULT keyword
@deprecated Will re removed in the next version
public function tableExists( string $tableName, string $schemaName = null ): bool;
Generates SQL checking for the existence of a schema.table
public function tableOptions( string $tableName, string $schemaName = null ): array;
Gets creation options from a table
public function update( string $table, mixed $fields, mixed $values, mixed $whereCondition = null, mixed $dataTypes = null ): bool;
Updates data on a table using custom RDBMS SQL syntax
public function updateAsDict( string $table, mixed $data, mixed $whereCondition = null, mixed $dataTypes = null ): bool;
Updates data on a table using custom RBDM SQL syntax Another, more convenient syntax
// Updating existing robot
$success = $connection->updateAsDict(
"robots",
[
"name" => "New Astro Boy",
],
"id = 101"
);
// Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101
public function useExplicitIdValue(): bool;
Check whether the database system requires an explicit value for identity columns
public function viewExists( string $viewName, string $schemaName = null ): bool;
Generates SQL checking for the existence of a schema.view
| Namespace | Phalcon\Db\Adapter\Pdo | Uses | Phalcon\Db\Adapter\AbstractAdapter, Phalcon\Db\Column, Phalcon\Db\Exception, Phalcon\Db\Result\PdoResult, Phalcon\Db\ResultInterface, Phalcon\Events\ManagerInterface | Extends | AbstractAdapter |
Phalcon\Db\Adapter\Pdo is the Phalcon\Db that internally uses PDO to connect to a database
use Phalcon\Db\Adapter\Pdo\Mysql;
$config = [
"host" => "localhost",
"dbname" => "blog",
"port" => 3306,
"username" => "sigma",
"password" => "secret",
];
$connection = new Mysql($config);
/**
* Last affected rows
*
* @var int
*/
protected affectedRows = 0;
/**
* PDO Handler
*
* @var \PDO
*/
protected pdo;
public function __construct( array $descriptor );
Constructor for Phalcon\Db\Adapter\Pdo
public function affectedRows(): int;
Returns the number of affected rows by the latest INSERT/UPDATE/DELETE executed in the database system
$connection->execute(
"DELETE FROM robots"
);
echo $connection->affectedRows(), " were deleted";
public function begin( bool $nesting = bool ): bool;
Starts a transaction in the connection
public function close(): void;
Closes the active connection returning success. Phalcon automatically closes and destroys active connections when the request ends
public function commit( bool $nesting = bool ): bool;
Commits the active transaction in the connection
public function connect( array $descriptor = [] ): void;
This method is automatically called in \Phalcon\Db\Adapter\Pdo constructor.
Call it when you need to restore a database connection.
use Phalcon\Db\Adapter\Pdo\Mysql;
// Make a connection
$connection = new Mysql(
[
"host" => "localhost",
"username" => "sigma",
"password" => "secret",
"dbname" => "blog",
"port" => 3306,
]
);
// Reconnect
$connection->connect();
public function convertBoundParams( string $sql, array $params = [] ): array;
Converts bound parameters such as :name: or ?1 into PDO bind params ?
print_r(
$connection->convertBoundParams(
"SELECTFROM robots WHERE name = :name:",
[
"Bender",
]
)
);
public function escapeString( string $str ): string;
Escapes a value to avoid SQL injections according to the active charset in the connection
$escapedStr = $connection->escapeString("some dangerous value");
public function execute( string $sqlStatement, array $bindParams = [], array $bindTypes = [] ): bool;
Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server doesn’t return any rows
// Inserting data
$success = $connection->execute(
"INSERT INTO robots VALUES (1, 'Astro Boy')"
);
$success = $connection->execute(
"INSERT INTO robots VALUES (?, ?)",
[
1,
"Astro Boy",
]
);
public function executePrepared( \PDOStatement $statement, array $placeholders, mixed $dataTypes ): \PDOStatement;
Executes a prepared statement binding. This function uses integer indexes starting from zero
use Phalcon\Db\Column;
$statement = $db->prepare(
"SELECTFROM robots WHERE name = :name"
);
$result = $connection->executePrepared(
$statement,
[
"name" => "Voltron",
],
[
"name" => Column::BIND_PARAM_STR,
]
);
public function getErrorInfo(): array;
Return the error info, if any
public function getInternalHandler(): mixed;
Return internal PDO handler
public function getTransactionLevel(): int;
Returns the current transaction nesting level
public function isUnderTransaction(): bool;
Checks whether the connection is under a transaction
$connection->begin();
// true
var_dump(
$connection->isUnderTransaction()
);
public function lastInsertId( string $name = null ): string | bool;
Returns the insert id for the auto_increment/serial column inserted in the latest executed SQL statement
// Inserting a new robot
$success = $connection->insert(
"robots",
[
"Astro Boy",
1952,
],
[
"name",
"year",
]
);
// Getting the generated id
$id = $connection->lastInsertId();
public function prepare( string $sqlStatement ): \PDOStatement;
Returns a PDO prepared statement to be executed with ‘executePrepared’
use Phalcon\Db\Column;
$statement = $db->prepare(
"SELECTFROM robots WHERE name = :name"
);
$result = $connection->executePrepared(
$statement,
[
"name" => "Voltron",
],
[
"name" => Column::BIND_PARAM_INT,
]
);
public function query( string $sqlStatement, array $bindParams = [], array $bindTypes = [] ): ResultInterface | bool;
Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server is returning rows
// Querying data
$resultset = $connection->query(
"SELECTFROM robots WHERE type = 'mechanical'"
);
$resultset = $connection->query(
"SELECTFROM robots WHERE type = ?",
[
"mechanical",
]
);
public function rollback( bool $nesting = bool ): bool;
Rollbacks the active transaction in the connection
abstract protected function getDsnDefaults(): array;
Returns PDO adapter DSN defaults as a key-value map.
protected function prepareRealSql( string $statement, array $parameters ): void;
Constructs the SQL statement (with parameters)
@see https://stackoverflow.com/a/8403150
| Namespace | Phalcon\Db\Adapter\Pdo | Uses | Phalcon\Db\Adapter\Pdo\AbstractPdo, Phalcon\Db\Column, Phalcon\Db\ColumnInterface, Phalcon\Db\Enum, Phalcon\Db\Exception, Phalcon\Db\Index, Phalcon\Db\IndexInterface, Phalcon\Db\Reference, Phalcon\Db\ReferenceInterface | Extends | PdoAdapter |
Specific functions for the MySQL database system
use Phalcon\Db\Adapter\Pdo\Mysql;
$config = [
"host" => "localhost",
"dbname" => "blog",
"port" => 3306,
"username" => "sigma",
"password" => "secret",
];
$connection = new Mysql($config);
/**
* @var string
*/
protected dialectType = mysql;
/**
* @var string
*/
protected type = mysql;
public function __construct( array $descriptor );
Constructor for Phalcon\Db\Adapter\Pdo
public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): bool;
Adds a foreign key to a table
public function describeColumns( string $table, string $schema = null ): ColumnInterface[];
Returns an array of Phalcon\Db\Column objects describing a table
print_r(
$connection->describeColumns("posts")
);
public function describeIndexes( string $table, string $schema = null ): IndexInterface[];
Lists table indexes
print_r(
$connection->describeIndexes("robots_parts")
);
public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];
Lists table references
print_r(
$connection->describeReferences("robots_parts")
);
protected function getDsnDefaults(): array;
Returns PDO adapter DSN defaults as a key-value map.
| Namespace | Phalcon\Db\Adapter\Pdo | Uses | Phalcon\Db\Adapter\Pdo\AbstractPdo, Phalcon\Db\Column, Phalcon\Db\ColumnInterface, Phalcon\Db\Enum, Phalcon\Db\Exception, Phalcon\Db\RawValue, Phalcon\Db\Reference, Phalcon\Db\ReferenceInterface, Throwable | Extends | PdoAdapter |
Specific functions for the PostgreSQL database system
use Phalcon\Db\Adapter\Pdo\Postgresql;
$config = [
"host" => "localhost",
"dbname" => "blog",
"port" => 5432,
"username" => "postgres",
"password" => "secret",
];
$connection = new Postgresql($config);
/**
* @var string
*/
protected dialectType = postgresql;
/**
* @var string
*/
protected type = pgsql;
public function __construct( array $descriptor );
Constructor for Phalcon\Db\Adapter\Pdo\Postgresql
public function connect( array $descriptor = [] ): void;
This method is automatically called in Phalcon\Db\Adapter\Pdo constructor. Call it when you need to restore a database connection.
public function createTable( string $tableName, string $schemaName, array $definition ): bool;
Creates a table
public function describeColumns( string $table, string $schema = null ): ColumnInterface[];
Returns an array of Phalcon\Db\Column objects describing a table
print_r(
$connection->describeColumns("posts")
);
public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];
Lists table references
print_r(
$connection->describeReferences("robots_parts")
);
public function getDefaultIdValue(): RawValue;
Returns the default identity value to be inserted in an identity column
// Inserting a new robot with a valid default value for the column 'id'
$success = $connection->insert(
"robots",
[
$connection->getDefaultIdValue(),
"Astro Boy",
1952,
],
[
"id",
"name",
"year",
]
);
public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): bool;
Modifies a table column based on a definition
public function supportSequences(): bool;
Check whether the database system requires a sequence to produce auto-numeric values
public function useExplicitIdValue(): bool;
Check whether the database system requires an explicit value for identity columns
protected function getDsnDefaults(): array;
Returns PDO adapter DSN defaults as a key-value map.
| Namespace | Phalcon\Db\Adapter\Pdo | Uses | Phalcon\Db\Adapter\Pdo\AbstractPdo, Phalcon\Db\Column, Phalcon\Db\ColumnInterface, Phalcon\Db\Enum, Phalcon\Db\Exception, Phalcon\Db\Index, Phalcon\Db\IndexInterface, Phalcon\Db\RawValue, Phalcon\Db\Reference, Phalcon\Db\ReferenceInterface | Extends | PdoAdapter |
Specific functions for the SQLite database system
use Phalcon\Db\Adapter\Pdo\Sqlite;
$connection = new Sqlite(
[
"dbname" => "/tmp/test.sqlite",
]
);
/**
* @var string
*/
protected dialectType = sqlite;
/**
* @var string
*/
protected type = sqlite;
public function __construct( array $descriptor );
Constructor for Phalcon\Db\Adapter\Pdo\Sqlite
public function connect( array $descriptor = [] ): void;
This method is automatically called in Phalcon\Db\Adapter\Pdo constructor. Call it when you need to restore a database connection.
public function describeColumns( string $table, string $schema = null ): ColumnInterface[];
Returns an array of Phalcon\Db\Column objects describing a table
print_r(
$connection->describeColumns("posts")
);
public function describeIndexes( string $table, string $schema = null ): IndexInterface[];
Lists table indexes
print_r(
$connection->describeIndexes("robots_parts")
);
public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];
Lists table references
public function getDefaultValue(): RawValue;
Returns the default value to make the RBDM use the default value declared in the table definition
// Inserting a new robot with a valid default value for the column 'year'
$success = $connection->insert(
"robots",
[
"Astro Boy",
$connection->getDefaultValue(),
],
[
"name",
"year",
]
);
public function supportsDefaultValue(): bool;
SQLite does not support the DEFAULT keyword
@deprecated Will re removed in the next version
public function useExplicitIdValue(): bool;
Check whether the database system requires an explicit value for identity columns
protected function getDsnDefaults(): array;
Returns PDO adapter DSN defaults as a key-value map.
| Namespace | Phalcon\Db\Adapter | Uses | Phalcon\Factory\AbstractFactory, Phalcon\Support\Helper\Arr\Get | Extends | AbstractFactory |
This file is part of the Phalcon Framework.
(c) Phalcon Team [email protected]
For the full copyright and license information, please view the LICENSE.txt file that was distributed with this source code.
public function __construct( array $services = [] );
Constructor
public function load( mixed $config ): AdapterInterface;
Factory to create an instance from a Config object
public function newInstance( string $name, array $options = [] ): AdapterInterface;
このアダプターの新しいインスタンスを作成
protected function getExceptionClass(): string;
protected function getServices(): array;
Returns the available adapters
| Namespace | Phalcon\Db | Implements | ColumnInterface |
Allows to define columns to be used on create or alter table operations
use Phalcon\Db\Column as Column;
// Column definition
$column = new Column(
"id",
[
"type" => Column::TYPE_INTEGER,
"size" => 10,
"unsigned" => true,
"notNull" => true,
"autoIncrement" => true,
"first" => true,
"comment" => "",
]
);
// Add column to existing table
$connection->addColumn("robots", null, $column);
const BIND_PARAM_BLOB = 3;
const BIND_PARAM_BOOL = 5;
const BIND_PARAM_DECIMAL = 32;
const BIND_PARAM_INT = 1;
const BIND_PARAM_NULL = 0;
const BIND_PARAM_STR = 2;
const BIND_SKIP = 1024;
const TYPE_BIGINTEGER = 14;
const TYPE_BINARY = 26;
const TYPE_BIT = 19;
const TYPE_BLOB = 11;
const TYPE_BOOLEAN = 8;
const TYPE_CHAR = 5;
const TYPE_DATE = 1;
const TYPE_DATETIME = 4;
const TYPE_DECIMAL = 3;
const TYPE_DOUBLE = 9;
const TYPE_ENUM = 18;
const TYPE_FLOAT = 7;
const TYPE_INTEGER = 0;
const TYPE_JSON = 15;
const TYPE_JSONB = 16;
const TYPE_LONGBLOB = 13;
const TYPE_LONGTEXT = 24;
const TYPE_MEDIUMBLOB = 12;
const TYPE_MEDIUMINTEGER = 21;
const TYPE_MEDIUMTEXT = 23;
const TYPE_SMALLINTEGER = 22;
const TYPE_TEXT = 6;
const TYPE_TIME = 20;
const TYPE_TIMESTAMP = 17;
const TYPE_TINYBLOB = 10;
const TYPE_TINYINTEGER = 26;
const TYPE_TINYTEXT = 25;
const TYPE_VARBINARY = 27;
const TYPE_VARCHAR = 2;
/**
* Column Position
*
* @var string|null
*/
protected after;
/**
* Column is autoIncrement?
*
* @var bool
*/
protected autoIncrement = false;
/**
* Bind Type
*
* @var int
*/
protected bindType = 2;
/**
* Column's comment
*
* @var string|null
*/
protected comment;
/**
* Default column value
*
* @var mixed|null
*/
protected defaultValue;
/**
* Position is first
*
* @var bool
*/
protected first = false;
/**
* The column have some numeric type?
*
* @var bool
*/
protected isNumeric = false;
/**
* Column's name
*
* @var string
*/
protected name;
/**
* Column not nullable?
*
* Default SQL definition is NOT NULL.
*
* @var bool
*/
protected notNull = true;
/**
* Column is part of the primary key?
*
* @var bool
*/
protected primary = false;
/**
* Integer column number scale
*
* @var int
*/
protected scale = 0;
/**
* Integer column size
*
* @var int|string
*/
protected size = 0;
/**
* Column data type
*
* @var int
*/
protected type;
/**
* Column data type reference
*
* @var int
*/
protected typeReference = -1;
/**
* Column data type values
*
* @var array|string
*/
protected typeValues;
/**
* Integer column unsigned?
*
* @var bool
*/
protected unsigned = false;
public function __construct( string $name, array $definition );
Phalcon\Db\Column constructor
public function getAfterPosition(): string | null;
Check whether field absolute to position in table
public function getBindType(): int;
Returns the type of bind handling
public function getComment(): string | null;
Column’s comment
public function getDefault(): mixed;
Default column value
public function getName(): string;
Column’s name
public function getScale(): int;
Integer column number scale
public function getSize(): int | string;
Integer column size
public function getType(): int;
Column data type
public function getTypeReference(): int;
Column data type reference
public function getTypeValues(): array | string;
Column data type values
public function hasDefault(): bool;
Check whether column has default value
public function isAutoIncrement(): bool;
Auto-Increment
public function isFirst(): bool;
Check whether column have first position in table
public function isNotNull(): bool;
Not null
public function isNumeric(): bool;
Check whether column have an numeric type
public function isPrimary(): bool;
Column is part of the primary key?
public function isUnsigned(): bool;
Returns true if number column is unsigned
| Namespace | Phalcon\Db |
Interface for Phalcon\Db\Column
public function getAfterPosition(): string | null;
Check whether field absolute to position in table
public function getBindType(): int;
Returns the type of bind handling
public function getDefault(): mixed;
Returns default value of column
public function getName(): string;
Returns column name
public function getScale(): int;
Returns column scale
public function getSize(): int | string;
Returns column size
public function getType(): int;
Returns column type
public function getTypeReference(): int;
Returns column type reference
public function getTypeValues(): array | string;
Returns column type values
public function hasDefault(): bool;
Check whether column has default value
public function isAutoIncrement(): bool;
Auto-Increment
public function isFirst(): bool;
Check whether column have first position in table
public function isNotNull(): bool;
Not null
public function isNumeric(): bool;
Check whether column have an numeric type
public function isPrimary(): bool;
Column is part of the primary key?
public function isUnsigned(): bool;
Returns true if number column is unsigned
| Namespace | Phalcon\Db | Implements | DialectInterface |
This is the base class to each database dialect. This implements common methods to transform intermediate code into its RDBMS related syntax
/**
* @var string
*/
protected escapeChar;
/**
* @var array
*/
protected customFunctions;
public function createSavepoint( string $name ): string;
Generate SQL to create a new savepoint
final public function escape( string $str, string $escapeChar = null ): string;
Escape identifiers
final public function escapeSchema( string $str, string $escapeChar = null ): string;
Escape Schema
public function forUpdate( string $sqlQuery ): string;
Returns a SQL modified with a FOR UPDATE clause
$sql = $dialect->forUpdate("SELECTFROM robots");
echo $sql; // SELECTFROM robots FOR UPDATE
final public function getColumnList( array $columnList, string $escapeChar = null, array $bindCounts = [] ): string;
Gets a list of columns with escaped identifiers
echo $dialect->getColumnList(
[
"column1",
"column",
]
);
public function getCustomFunctions(): array;
Returns registered functions
final public function getSqlColumn( mixed $column, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve Column expressions
public function getSqlExpression( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Transforms an intermediate representation for an expression into a database system valid expression
final public function getSqlTable( mixed $table, string $escapeChar = null ): string;
Transform an intermediate representation of a schema/table into a database system valid expression
public function limit( string $sqlQuery, mixed $number ): string;
Generates the SQL for LIMIT clause
// SELECTFROM robots LIMIT 10
echo $dialect->limit(
"SELECTFROM robots",
10
);
// SELECTFROM robots LIMIT 10 OFFSET 50
echo $dialect->limit(
"SELECTFROM robots",
[10, 50]
);
public function registerCustomFunction( string $name, callable $customFunction ): Dialect;
Registers custom SQL functions
public function releaseSavepoint( string $name ): string;
Generate SQL to release a savepoint
public function rollbackSavepoint( string $name ): string;
Generate SQL to rollback a savepoint
public function select( array $definition ): string;
Builds a SELECT statement
public function supportsReleaseSavepoints(): bool;
Checks whether the platform supports releasing savepoints.
public function supportsSavepoints(): bool;
Checks whether the platform supports savepoints
protected function checkColumnType( ColumnInterface $column ): string;
Checks the column type and if not string it returns the type reference
protected function checkColumnTypeSql( ColumnInterface $column ): string;
Checks the column type and returns the updated SQL statement
protected function getColumnSize( ColumnInterface $column ): string;
Returns the size of the column enclosed in parentheses
protected function getColumnSizeAndScale( ColumnInterface $column ): string;
Returns the column size and scale enclosed in parentheses
final protected function getSqlExpressionAll( array $expression, string $escapeChar = null ): string;
Resolve
final protected function getSqlExpressionBinaryOperations( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve binary operations expressions
final protected function getSqlExpressionCase( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve CASE expressions
final protected function getSqlExpressionCastValue( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve CAST of values
final protected function getSqlExpressionConvertValue( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve CONVERT of values encodings
final protected function getSqlExpressionFrom( mixed $expression, string $escapeChar = null ): string;
Resolve a FROM clause
final protected function getSqlExpressionFunctionCall( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve function calls
final protected function getSqlExpressionGroupBy( mixed $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve a GROUP BY clause
final protected function getSqlExpressionHaving( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve a HAVING clause
final protected function getSqlExpressionJoins( mixed $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve a JOINs clause
final protected function getSqlExpressionLimit( mixed $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve a LIMIT clause
final protected function getSqlExpressionList( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve Lists
final protected function getSqlExpressionObject( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve object expressions
final protected function getSqlExpressionOrderBy( mixed $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve an ORDER BY clause
final protected function getSqlExpressionQualified( array $expression, string $escapeChar = null ): string;
Resolve qualified expressions
final protected function getSqlExpressionScalar( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve Column expressions
final protected function getSqlExpressionUnaryOperations( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve unary operations expressions
final protected function getSqlExpressionWhere( mixed $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Resolve a WHERE clause
protected function prepareColumnAlias( string $qualified, string $alias = null, string $escapeChar = null ): string;
Prepares column for this RDBMS
protected function prepareQualified( string $column, string $domain = null, string $escapeChar = null ): string;
Prepares qualified for this RDBMS
protected function prepareTable( string $table, string $schema = null, string $alias = null, string $escapeChar = null ): string;
Prepares table for this RDBMS
| Namespace | Phalcon\Db\Dialect | Uses | Phalcon\Db\Dialect, Phalcon\Db\Column, Phalcon\Db\Exception, Phalcon\Db\IndexInterface, Phalcon\Db\ColumnInterface, Phalcon\Db\ReferenceInterface, Phalcon\Db\DialectInterface | Extends | Dialect |
Generates database specific SQL for the MySQL RDBMS
/**
* @var string
*/
protected escapeChar = `;
public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;
Generates SQL to add a column to a table
public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;
Generates SQL to add an index to a table
public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add an index to a table
public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add the primary key to a table
public function createTable( string $tableName, string $schemaName, array $definition ): string;
Generates SQL to create a table
public function createView( string $viewName, array $definition, string $schemaName = null ): string;
Generates SQL to create a view
public function describeColumns( string $table, string $schema = null ): string;
Generates SQL describing a table
print_r(
$dialect->describeColumns("posts")
);
public function describeIndexes( string $table, string $schema = null ): string;
Generates SQL to query indexes on a table
public function describeReferences( string $table, string $schema = null ): string;
Generates SQL to query foreign keys on a table
public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;
Generates SQL to delete a column from a table
public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;
Generates SQL to delete a foreign key from a table
public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;
Generates SQL to delete an index from a table
public function dropPrimaryKey( string $tableName, string $schemaName ): string;
Generates SQL to delete primary key from a table
public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): string;
Generates SQL to drop a table
public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;
Generates SQL to drop a view
public function getColumnDefinition( ColumnInterface $column ): string;
Gets the column name in MySQL
public function getForeignKeyChecks(): string;
Generates SQL to check DB parameter FOREIGN_KEY_CHECKS.
public function listTables( string $schemaName = null ): string;
List all tables in database
print_r(
$dialect->listTables("blog")
);
public function listViews( string $schemaName = null ): string;
Generates the SQL to list all views of a schema or user
public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;
Generates SQL to modify a column in a table
public function sharedLock( string $sqlQuery ): string;
Returns a SQL modified with a LOCK IN SHARE MODE clause
$sql = $dialect->sharedLock("SELECTFROM robots");
echo $sql; // SELECTFROM robots LOCK IN SHARE MODE
public function tableExists( string $tableName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.table
echo $dialect->tableExists("posts", "blog");
echo $dialect->tableExists("posts");
public function tableOptions( string $table, string $schema = null ): string;
Generates the SQL to describe the table creation options
public function truncateTable( string $tableName, string $schemaName ): string;
Generates SQL to truncate a table
public function viewExists( string $viewName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.view
protected function getTableOptions( array $definition ): string;
Generates SQL to add the table creation options
| Namespace | Phalcon\Db\Dialect | Uses | Phalcon\Db\Dialect, Phalcon\Db\Column, Phalcon\Db\Exception, Phalcon\Db\IndexInterface, Phalcon\Db\ColumnInterface, Phalcon\Db\ReferenceInterface, Phalcon\Db\DialectInterface | Extends | Dialect |
Generates database specific SQL for the PostgreSQL RDBMS
/**
* @var string
*/
protected escapeChar = \";
public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;
Generates SQL to add a column to a table
public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;
Generates SQL to add an index to a table
public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add an index to a table
public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add the primary key to a table
public function createTable( string $tableName, string $schemaName, array $definition ): string;
Generates SQL to create a table
public function createView( string $viewName, array $definition, string $schemaName = null ): string;
Generates SQL to create a view
public function describeColumns( string $table, string $schema = null ): string;
Generates SQL describing a table
print_r(
$dialect->describeColumns("posts")
);
public function describeIndexes( string $table, string $schema = null ): string;
Generates SQL to query indexes on a table
public function describeReferences( string $table, string $schema = null ): string;
Generates SQL to query foreign keys on a table
public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;
Generates SQL to delete a column from a table
public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;
Generates SQL to delete a foreign key from a table
public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;
Generates SQL to delete an index from a table
public function dropPrimaryKey( string $tableName, string $schemaName ): string;
Generates SQL to delete primary key from a table
public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): string;
Generates SQL to drop a table
public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;
Generates SQL to drop a view
public function getColumnDefinition( ColumnInterface $column ): string;
Gets the column name in PostgreSQL
public function listTables( string $schemaName = null ): string;
List all tables in database
print_r(
$dialect->listTables("blog")
);
public function listViews( string $schemaName = null ): string;
Generates the SQL to list all views of a schema or user
public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;
Generates SQL to modify a column in a table
public function sharedLock( string $sqlQuery ): string;
Returns a SQL modified a shared lock statement. For now this method returns the original query
public function tableExists( string $tableName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.table
echo $dialect->tableExists("posts", "blog");
echo $dialect->tableExists("posts");
public function tableOptions( string $table, string $schema = null ): string;
Generates the SQL to describe the table creation options
public function truncateTable( string $tableName, string $schemaName ): string;
Generates SQL to truncate a table
public function viewExists( string $viewName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.view
protected function castDefault( ColumnInterface $column ): string;
protected function getTableOptions( array $definition ): string;
| Namespace | Phalcon\Db\Dialect | Uses | Phalcon\Db\Column, Phalcon\Db\Exception, Phalcon\Db\IndexInterface, Phalcon\Db\Dialect, Phalcon\Db\DialectInterface, Phalcon\Db\ColumnInterface, Phalcon\Db\ReferenceInterface | Extends | Dialect |
Generates database specific SQL for the SQLite RDBMS
/**
* @var string
*/
protected escapeChar = \";
public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;
Generates SQL to add a column to a table
public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;
Generates SQL to add an index to a table
public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add an index to a table
public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add the primary key to a table
public function createTable( string $tableName, string $schemaName, array $definition ): string;
Generates SQL to create a table
public function createView( string $viewName, array $definition, string $schemaName = null ): string;
Generates SQL to create a view
public function describeColumns( string $table, string $schema = null ): string;
Generates SQL describing a table
print_r(
$dialect->describeColumns("posts")
);
public function describeIndex( string $index ): string;
Generates SQL to query indexes detail on a table
public function describeIndexes( string $table, string $schema = null ): string;
Generates SQL to query indexes on a table
public function describeReferences( string $table, string $schema = null ): string;
Generates SQL to query foreign keys on a table
public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;
Generates SQL to delete a column from a table
public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;
Generates SQL to delete a foreign key from a table
public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;
Generates SQL to delete an index from a table
public function dropPrimaryKey( string $tableName, string $schemaName ): string;
Generates SQL to delete primary key from a table
public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): string;
Generates SQL to drop a table
public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;
Generates SQL to drop a view
public function forUpdate( string $sqlQuery ): string;
Returns a SQL modified with a FOR UPDATE clause. For SQLite it returns the original query
public function getColumnDefinition( ColumnInterface $column ): string;
Gets the column name in SQLite
public function listIndexesSql( string $table, string $schema = null, string $keyName = null ): string;
Generates the SQL to get query list of indexes
print_r(
$dialect->listIndexesSql("blog")
);
public function listTables( string $schemaName = null ): string;
List all tables in database
print_r(
$dialect->listTables("blog")
);
public function listViews( string $schemaName = null ): string;
Generates the SQL to list all views of a schema or user
public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;
Generates SQL to modify a column in a table
public function sharedLock( string $sqlQuery ): string;
Returns a SQL modified a shared lock statement. For now this method returns the original query
public function tableExists( string $tableName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.table
echo $dialect->tableExists("posts", "blog");
echo $dialect->tableExists("posts");
public function tableOptions( string $table, string $schema = null ): string;
Generates the SQL to describe the table creation options
public function truncateTable( string $tableName, string $schemaName ): string;
Generates SQL to truncate a table
public function viewExists( string $viewName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.view
| Namespace | Phalcon\Db |
Interface for Phalcon\Db dialects
public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;
Generates SQL to add a column to a table
public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;
Generates SQL to add an index to a table
public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add an index to a table
public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;
Generates SQL to add the primary key to a table
public function createSavepoint( string $name ): string;
Generate SQL to create a new savepoint
public function createTable( string $tableName, string $schemaName, array $definition ): string;
Generates SQL to create a table
public function createView( string $viewName, array $definition, string $schemaName = null ): string;
Generates SQL to create a view
public function describeColumns( string $table, string $schema = null ): string;
Generates SQL to describe a table
public function describeIndexes( string $table, string $schema = null ): string;
Generates SQL to query indexes on a table
public function describeReferences( string $table, string $schema = null ): string;
Generates SQL to query foreign keys on a table
public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;
Generates SQL to delete a column from a table
public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;
Generates SQL to delete a foreign key from a table
public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;
Generates SQL to delete an index from a table
public function dropPrimaryKey( string $tableName, string $schemaName ): string;
Generates SQL to delete primary key from a table
public function dropTable( string $tableName, string $schemaName, bool $ifExists = bool ): string;
Generates SQL to drop a table
public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;
Generates SQL to drop a view
public function forUpdate( string $sqlQuery ): string;
Returns a SQL modified with a FOR UPDATE clause
public function getColumnDefinition( ColumnInterface $column ): string;
Gets the column name in RDBMS
public function getColumnList( array $columnList ): string;
Gets a list of columns
public function getCustomFunctions(): array;
Returns registered functions
public function getSqlExpression( array $expression, string $escapeChar = null, array $bindCounts = [] ): string;
Transforms an intermediate representation for an expression into a database system valid expression
public function limit( string $sqlQuery, mixed $number ): string;
Generates the SQL for LIMIT clause
public function listTables( string $schemaName = null ): string;
List all tables in database
public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;
Generates SQL to modify a column in a table
public function registerCustomFunction( string $name, callable $customFunction ): Dialect;
Registers custom SQL functions
public function releaseSavepoint( string $name ): string;
Generate SQL to release a savepoint
public function rollbackSavepoint( string $name ): string;
Generate SQL to rollback a savepoint
public function select( array $definition ): string;
Builds a SELECT statement
public function sharedLock( string $sqlQuery ): string;
Returns a SQL modified with a LOCK IN SHARE MODE clause
public function supportsReleaseSavepoints(): bool;
Checks whether the platform supports releasing savepoints.
public function supportsSavepoints(): bool;
Checks whether the platform supports savepoints
public function tableExists( string $tableName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.table
public function tableOptions( string $table, string $schema = null ): string;
Generates the SQL to describe the table creation options
public function viewExists( string $viewName, string $schemaName = null ): string;
Generates SQL checking for the existence of a schema.view
| Namespace | Phalcon\Db |
Constants for Phalcon\Db
const FETCH_ASSOC;
const FETCH_BOTH;
const FETCH_BOUND;
const FETCH_CLASS;
const FETCH_CLASSTYPE;
const FETCH_COLUMN;
const FETCH_DEFAULT = 0;
const FETCH_FUNC;
const FETCH_GROUP;
const FETCH_INTO;
const FETCH_KEY_PAIR;
const FETCH_LAZY;
const FETCH_NAMED;
const FETCH_NUM;
const FETCH_OBJ;
const FETCH_ORI_NEXT;
const FETCH_PROPS_LATE;
const FETCH_SERIALIZE;
const FETCH_UNIQUE;
| Namespace | Phalcon\Db | Extends | \Exception |
Exceptions thrown in Phalcon\Db will use this class
| Namespace | Phalcon\Db | Implements | IndexInterface |
Allows to define indexes to be used on tables. Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index
// Define new unique index
$index_unique = new \Phalcon\Db\Index(
'column_UNIQUE',
[
'column',
'column',
],
'UNIQUE'
);
// Define new primary index
$index_primary = new \Phalcon\Db\Index(
'PRIMARY',
[
'column',
]
);
// Add index to existing table
$connection->addIndex("robots", null, $index_unique);
$connection->addIndex("robots", null, $index_primary);
/**
* Index columns
*
* @var array
*/
protected columns;
/**
* Index name
*
* @var string
*/
protected name;
/**
* Index type
*
* @var string
*/
protected type;
public function __construct( string $name, array $columns, string $type = string );
Phalcon\Db\Index constructor
public function getColumns(): array;
Index columns
public function getName(): string;
Index name
public function getType(): string;
Index type
| Namespace | Phalcon\Db |
Interface for Phalcon\Db\Index
public function getColumns(): array;
Gets the columns that corresponds the index
public function getName(): string;
Gets the index name
public function getType(): string;
Gets the index type
| Namespace | Phalcon\Db | Uses | Phalcon\Db\Profiler\Item |
Instances of Phalcon\Db can generate execution profiles on SQL statements sent to the relational database. Profiled information includes execution time in milliseconds. This helps you to identify bottlenecks in your applications.
use Phalcon\Db\Profiler;
use Phalcon\Events\Event;
use Phalcon\Events\Manager;
$profiler = new Profiler();
$eventsManager = new Manager();
$eventsManager->attach(
"db",
function (Event $event, $connection) use ($profiler) {
if ($event->getType() === "beforeQuery") {
$sql = $connection->getSQLStatement();
// Start a profile with the active connection
$profiler->startProfile($sql);
}
if ($event->getType() === "afterQuery") {
// Stop the active profile
$profiler->stopProfile();
}
}
);
// Set the event manager on the connection
$connection->setEventsManager($eventsManager);
$sql = "SELECT buyer_name, quantity, product_name
FROM buyers LEFT JOIN products ON
buyers.pid=products.id";
// Execute a SQL statement
$connection->query($sql);
// Get the last profile in the profiler
$profile = $profiler->getLastProfile();
echo "SQL Statement: ", $profile->getSQLStatement(), "\n";
echo "Start Time: ", $profile->getInitialTime(), "\n";
echo "Final Time: ", $profile->getFinalTime(), "\n";
echo "Total Elapsed Time: ", $profile->getTotalElapsedSeconds(), "\n";
/**
* Active Item
*
* @var Item
*/
protected activeProfile;
/**
* All the Items in the active profile
*
* @var Item[]
*/
protected allProfiles;
/**
* Total time spent by all profiles to complete in nanoseconds
*
* @var float
*/
protected totalNanoseconds = 0;
public function getLastProfile(): Item;
Returns the last profile executed in the profiler
public function getNumberTotalStatements(): int;
Returns the total number of SQL statements processed
public function getProfiles(): Item[];
Returns all the processed profiles
public function getTotalElapsedMilliseconds(): double;
Returns the total time in milliseconds spent by the profiles
public function getTotalElapsedNanoseconds(): double;
Returns the total time in nanoseconds spent by the profiles
public function getTotalElapsedSeconds(): double;
Returns the total time in seconds spent by the profiles
public function reset(): Profiler;
Resets the profiler, cleaning up all the profiles
public function startProfile( string $sqlStatement, array $sqlVariables = [], array $sqlBindTypes = [] ): Profiler;
Starts the profile of a SQL sentence
public function stopProfile(): Profiler;
Stops the active profile
| Namespace | Phalcon\Db\Profiler |
This class identifies each profile in a Phalcon\Db\Profiler
/**
* Timestamp when the profile ended
*
* @var double
*/
protected finalTime;
/**
* Timestamp when the profile started
*
* @var double
*/
protected initialTime;
/**
* SQL bind types related to the profile
*
* @var array
*/
protected sqlBindTypes;
/**
* SQL statement related to the profile
*
* @var string
*/
protected sqlStatement;
/**
* SQL variables related to the profile
*
* @var array
*/
protected sqlVariables;
public function getFinalTime(): double;
Return the timestamp when the profile ended
public function getInitialTime(): double;
Return the timestamp when the profile started
public function getSqlBindTypes(): array;
Return the SQL bind types related to the profile
public function getSqlStatement(): string;
Return the SQL statement related to the profile
public function getSqlVariables(): array;
Return the SQL variables related to the profile
public function getTotalElapsedMilliseconds(): double;
Returns the total time in milliseconds spent by the profile
public function getTotalElapsedNanoseconds(): double;
Returns the total time in nanoseconds spent by the profile
public function getTotalElapsedSeconds(): double;
Returns the total time in seconds spent by the profile
public function setFinalTime( double $finalTime ): Item;
Return the timestamp when the profile ended
public function setInitialTime( double $initialTime ): Item;
Return the timestamp when the profile started
public function setSqlBindTypes( array $sqlBindTypes ): Item;
Return the SQL bind types related to the profile
public function setSqlStatement( string $sqlStatement ): Item;
Return the SQL statement related to the profile
public function setSqlVariables( array $sqlVariables ): Item;
Return the SQL variables related to the profile
| Namespace | Phalcon\Db |
This class allows to insert/update raw data without quoting or formatting.
The next example shows how to use the MySQL now() function as a field value.
$subscriber = new Subscribers();
$subscriber->email = "[email protected]";
$subscriber->createdAt = new \Phalcon\Db\RawValue("now()");
$subscriber->save();
/**
* Raw value without quoting or formatting
*
* @var string
*/
protected value;
public function __construct( mixed $value );
Phalcon\Db\RawValue constructor
public function __toString(): string;
public function getValue(): string;
| Namespace | Phalcon\Db | Implements | ReferenceInterface |
Allows to define reference constraints on tables
$reference = new \Phalcon\Db\Reference(
"field_fk",
[
"referencedSchema" => "invoicing",
"referencedTable" => "products",
"columns" => [
"producttype",
"product_code",
],
"referencedColumns" => [
"type",
"code",
],
]
);
/**
* Local reference columns
*
* @var array
*/
protected columns;
/**
* Constraint name
*
* @var string
*/
protected name;
/**
* Referenced Columns
*
* @var array
*/
protected referencedColumns;
/**
* Referenced Schema
*
* @var string
*/
protected referencedSchema;
/**
* Referenced Table
*
* @var string
*/
protected referencedTable;
/**
* Schema name
*
* @var string
*/
protected schemaName;
/**
* ON DELETE
*
* @var string
*/
protected onDelete;
/**
* ON UPDATE
*
* @var string
*/
protected onUpdate;
public function __construct( string $name, array $definition );
Phalcon\Db\Reference constructor
public function getColumns(): array;
Local reference columns
public function getName(): string;
Constraint name
public function getOnDelete(): string;
ON DELETE
public function getOnUpdate(): string;
ON UPDATE
public function getReferencedColumns(): array;
Referenced Columns
public function getReferencedSchema(): string;
Referenced Schema
public function getReferencedTable(): string;
Referenced Table
public function getSchemaName(): string;
Schema name
| Namespace | Phalcon\Db |
Interface for Phalcon\Db\Reference
public function getColumns(): array;
Gets local columns which reference is based
public function getName(): string;
Gets the index name
public function getOnDelete(): string;
Gets the referenced on delete
public function getOnUpdate(): string;
Gets the referenced on update
public function getReferencedColumns(): array;
Gets referenced columns
public function getReferencedSchema(): string;
Gets the schema where referenced table is
public function getReferencedTable(): string;
Gets the referenced table
public function getSchemaName(): string;
Gets the schema where referenced table is
| Namespace | Phalcon\Db\Result | Uses | Phalcon\Db\Enum, Phalcon\Db\ResultInterface, Phalcon\Db\Adapter\AdapterInterface | Implements | ResultInterface |
Encapsulates the resultset internals
$result = $connection->query("SELECTFROM robots ORDER BY name");
$result->setFetchMode(
\Phalcon\Db\Enum::FETCH_NUM
);
while ($robot = $result->fetchArray()) {
print_r($robot);
}
/**
* @var array
*/
protected bindParams;
/**
* @var array
*/
protected bindTypes;
/**
* @var AdapterInterface
*/
protected connection;
/**
* Active fetch mode
*
* @var int
*/
protected fetchMode;
/**
* Internal resultset
*
* @var \PDOStatement
*/
protected pdoStatement;
/**
* @var mixed
* TODO: Check if this property is used
*/
protected result;
/**
* @var bool
*/
protected rowCount = false;
/**
* @var string|null
*/
protected sqlStatement;
public function __construct( AdapterInterface $connection, \PDOStatement $result, mixed $sqlStatement = null, mixed $bindParams = null, mixed $bindTypes = null );
Phalcon\Db\Result\Pdo constructor
public function dataSeek( int $number ): void;
Moves internal resultset cursor to another position letting us to fetch a certain row
$result = $connection->query(
"SELECTFROM robots ORDER BY name"
);
// Move to third row on result
$result->dataSeek(2);
// Fetch third row
$row = $result->fetch();
public function execute(): bool;
Allows to execute the statement again. Some database systems don’t support scrollable cursors. So, as cursors are forward only, we need to execute the cursor again to fetch rows from the beginning
public function fetch( int $fetchStyle = null, int $cursorOrientation = static-constant-access, int $cursorOffset = int );
Fetches an array/object of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()
$result = $connection->query("SELECT * FROM robots ORDER BY name");
$result->setFetchMode(
\Phalcon\Enum::FETCH_OBJ
);
while ($robot = $result->fetch()) {
echo $robot->name;
}
public function fetchAll( int $mode = Enum::FETCH_DEFAULT, mixed $fetchArgument = Enum::FETCH_ORI_NEXT, mixed $constructorArgs = null ): array;
Returns an array of arrays containing all the records in the result This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()
$result = $connection->query(
"SELECT * FROM robots ORDER BY name"
);
$robots = $result->fetchAll();
public function fetchArray();
Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()
$result = $connection->query("SELECT * FROM robots robots ORDER BY name");
$result->setFetchMode(
\Phalcon\Enum::FETCH_NUM
);
while ($robot = result->fetchArray()) {
print_r($robot);
}
public function getInternalResult(): \PDOStatement;
Gets the internal PDO result object
public function numRows(): int;
Gets number of rows returned by a resultset
$result = $connection->query(
"SELECT * FROM robots robots ORDER BY name"
);
echo "There are ", $result->numRows(), " rows in the resultset";
public function setFetchMode( int $fetchMode, mixed $colNoOrClassNameOrObject = null, mixed $ctorargs = null ): bool;
Changes the fetching mode affecting Phalcon\Db\Result\Pdo::fetch()
// Return array with integer indexes
$result->setFetchMode(
\Phalcon\Enum::FETCH_NUM
);
// Return associative array without integer indexes
$result->setFetchMode(
\Phalcon\Enum::FETCH_ASSOC
);
// Return associative array together with integer indexes
$result->setFetchMode(
\Phalcon\Enum::FETCH_BOTH
);
// Return an object
$result->setFetchMode(
\Phalcon\Enum::FETCH_OBJ
);
| Namespace | Phalcon\Db |
Interface for Phalcon\Db\Result objects
public function dataSeek( int $number );
Moves internal resultset cursor to another position letting us to fetch a certain row
public function execute(): bool;
Allows to execute the statement again. Some database systems don’t support scrollable cursors. So, as cursors are forward only, we need to execute the cursor again to fetch rows from the beginning
public function fetch(): mixed;
Fetches an array/object of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()
public function fetchAll(): array;
Returns an array of arrays containing all the records in the result. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()
public function fetchArray(): mixed;
Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()
public function getInternalResult(): \PDOStatement;
Gets the internal PDO result object
public function numRows(): int;
Gets number of rows returned by a resultset
public function setFetchMode( int $fetchMode ): bool;
Changes the fetching mode affecting Phalcon\Db\Result\Pdo::fetch()