---
title: "Phalcon Db"
version: "5.20"
---
> Documentation Index
> Fetch the complete documentation index at: https://docs.phalcon.io/llms.txt
> Use this file to discover all available pages before exploring further.
# Phalcon Db
:::info[NOTE]
All classes are prefixed with `Phalcon`
:::
## Db\Adapter\AbstractAdapter
Abstract
Base class for Phalcon\Db\Adapter adapters.
This class 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
```php
use 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(
"SELECT * FROM co_invoices LIMIT 5"
);
$result->setFetchMode(Enum::FETCH_NUM);
while ($invoice = $result->fetch()) {
print_r($invoice);
}
} catch (Exception $e) {
echo $e->getMessage(), PHP_EOL;
}
```
- **`Phalcon\Db\Adapter\AbstractAdapter`** - implements [`Phalcon\Db\Adapter\AdapterInterface`](#dbadapteradapterinterface), [`Phalcon\Events\EventsAwareInterface`](/5.20/api/phalcon_events/#eventseventsawareinterface)
- [`Phalcon\Db\Adapter\Pdo\AbstractPdo`](#dbadapterpdoabstractpdo)
`Phalcon\Db\CheckInterface` · `Phalcon\Db\ColumnInterface` · `Phalcon\Db\DialectInterface` · `Phalcon\Db\Enum` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\CannotInsertWithoutData` · `Phalcon\Db\Exceptions\IncompleteBindTypes` · `Phalcon\Db\Exceptions\InvalidDialectClass` · `Phalcon\Db\Exceptions\InvalidWhereConditions` · `Phalcon\Db\Exceptions\NestedTransactionChangeBlocked` · `Phalcon\Db\Exceptions\SavepointsNotSupported` · `Phalcon\Db\Exceptions\TableMustHaveColumn` · `Phalcon\Db\Exceptions\UpdateFieldCountMismatch` · `Phalcon\Db\Index` · `Phalcon\Db\IndexInterface` · `Phalcon\Db\RawValue` · `Phalcon\Db\Reference` · `Phalcon\Db\ReferenceInterface` · `Phalcon\Events\EventsAwareInterface` · `Phalcon\Events\ManagerInterface` · `Phalcon\Support\Settings`
### Method Summary
Phalcon\Db\Adapter constructor
Adds a CHECK constraint to a table. MySQL 8.0.16+ and PostgreSQL
Adds a column to a table
Adds a foreign key to a table
Adds an index to a table
Adds a primary key to a table
Creates a materialized view (PostgreSQL only - MySQL and SQLite
Creates a new savepoint
Creates a table
Creates a view
Deletes data from a table using custom RBDM SQL syntax
Lists table indexes
Lists table references
Drops a CHECK constraint from a table. SQLite throws.
Drops a column from a table
Drops a foreign key from a table
Drop an index from a table
Drops a materialized view (PostgreSQL only).
Drops a table's primary key
Drops a table from a schema/database
Drops a view
Escapes a column/table/schema name
Dumps the complete result of a query into an array
Returns the n'th field of first row in a SQL query result
Returns the first row in a SQL query result
Returns a SQL modified with a FOR UPDATE clause. The optional
Returns the SQL column definition from a column
Gets a list of columns
Gets the active connection unique identifier
Returns the default identity value to be inserted in an identity column
Returns the default value to make the RBDM use the default value declared
Return descriptor used to connect to the active database
Returns internal dialect instance
Name of the dialect used
Returns the internal event manager
Returns the savepoint name to use for nested transactions
Active SQL statement in the object without replace bound parameters
Active SQL statement in the object
Active SQL statement in the object
Active SQL variables in the object
Type of database system the adapter is used for
Inserts data into a table using custom RDBMS SQL syntax
Inserts data into a table using custom RBDM SQL syntax
Returns if nested transactions should use savepoints
Appends a LIMIT clause to $sqlQuery argument
List all tables on a database
List all views on a database
Modifies a table column based on a definition
Appends an `ON CONFLICT (...) DO UPDATE SET col = excluded.col`
Refreshes a materialized view (PostgreSQL only). Pass
Releases given savepoint
Appends a RETURNING clause to an INSERT/UPDATE/DELETE SQL statement
Rollbacks given savepoint
Sets the dialect used to produce the SQL
Sets the event manager
Set if nested transactions should use savepoints
Enables/disables options in the Database component.
Returns a SQL modified with a shared-lock clause. The optional
Check whether the database system requires a sequence to produce
Check whether the database system support the DEFAULT
Generates SQL checking for the existence of a schema.table
Gets creation options from a table
Updates data on a table using custom RBDM SQL syntax
Updates data on a table using custom RBDM SQL syntax
Check whether the database system requires an explicit value for identity
Generates SQL checking for the existence of a schema.view
### Properties
Connection ID
Active connection ID
Descriptor used to connect to a database
Dialect instance
Name of the dialect used
Event Manager
The real SQL statement - what was executed
Active SQL Bind Types
Active SQL Statement
Active SQL bound parameter variables
Current transaction level
Whether the database supports transactions with save points
Type of database system the adapter is used for
### Methods
__construct()
```php
public function __construct( array $descriptor );
```
Phalcon\Db\Adapter constructor
Note: the `options` key is forwarded to the static `setup()` method,
which writes process-global settings affecting every connection in the
process. See `setup()`.
addCheck()
```php
public function addCheck(
string $tableName,
string $schemaName,
CheckInterface $check
): bool;
```
Adds a CHECK constraint to a table. MySQL 8.0.16+ and PostgreSQL
issue `ALTER TABLE ... ADD CONSTRAINT ... CHECK (...)`; SQLite throws.
addColumn()
```php
public function addColumn(
string $tableName,
string $schemaName,
ColumnInterface $column
): bool;
```
Adds a column to a table
addForeignKey()
```php
public function addForeignKey(
string $tableName,
string $schemaName,
ReferenceInterface $reference
): bool;
```
Adds a foreign key to a table
addIndex()
```php
public function addIndex(
string $tableName,
string $schemaName,
IndexInterface $index
): bool;
```
Adds an index to a table
addPrimaryKey()
```php
public function addPrimaryKey(
string $tableName,
string $schemaName,
IndexInterface $index
): bool;
```
Adds a primary key to a table
createMaterializedView()
```php
public function createMaterializedView(
string $viewName,
array $definition,
string|null $schemaName = null
): bool;
```
Creates a materialized view (PostgreSQL only - MySQL and SQLite
throw via the dialect).
createSavepoint()
```php
public function createSavepoint( string $name ): bool;
```
Creates a new savepoint
createTable()
```php
public function createTable(
string $tableName,
string $schemaName,
array $definition
): bool;
```
Creates a table
createView()
```php
public function createView(
string $viewName,
array $definition,
string|null $schemaName = null
): bool;
```
Creates a view
delete()
```php
public function delete(
mixed $table,
string|null $whereCondition = null,
array $placeholders = [],
array $dataTypes = []
): bool;
```
Deletes data from a table using custom RBDM SQL syntax
```php
// Deleting existing invoice
$success = $connection->delete(
"co_invoices",
"inv_id = 101"
);
// Next SQL sentence is generated
DELETE FROM `co_invoices` WHERE `inv_id` = 101
```
Warning! If $whereCondition is string it not escaped.
describeIndexes()
```php
public function describeIndexes(
string $table,
string|null $schema = null
): IndexInterface[];
```
Lists table indexes
```php
print_r(
$connection->describeIndexes("co_orders_x_products")
);
```
This base implementation consumes the dialect's `describeIndexes()` SQL
as `FETCH_NUM` rows by position: column index 2 is the index key name and
column index 4 is the indexed column name. A custom dialect's
`describeIndexes()` SQL must emit columns in that order, or a custom
adapter must override this method. All bundled adapters except PostgreSQL
override it.
describeReferences()
```php
public function describeReferences(
string $table,
string|null $schema = null
): ReferenceInterface[];
```
Lists table references
```php
print_r(
$connection->describeReferences("co_orders_x_products")
);
```
This base implementation consumes the dialect's `describeReferences()`
SQL as `FETCH_NUM` rows by position: index 1 is the local column, index 2
the constraint name, index 3 the referenced schema, index 4 the
referenced table, and index 5 the referenced column. A custom dialect's
`describeReferences()` SQL must emit columns in that order, or a custom
adapter must override this method. Every bundled adapter (MySQL,
PostgreSQL, SQLite) overrides it, so this base implementation has no
in-tree caller and effectively assumes the PostgreSQL row shape.
dropCheck()
```php
public function dropCheck(
string $tableName,
string $schemaName,
string $checkName
): bool;
```
Drops a CHECK constraint from a table. SQLite throws.
dropColumn()
```php
public function dropColumn(
string $tableName,
string $schemaName,
string $columnName
): bool;
```
Drops a column from a table
dropForeignKey()
```php
public function dropForeignKey(
string $tableName,
string $schemaName,
string $referenceName
): bool;
```
Drops a foreign key from a table
dropIndex()
```php
public function dropIndex(
string $tableName,
string $schemaName,
mixed $indexName
): bool;
```
Drop an index from a table
dropMaterializedView()
```php
public function dropMaterializedView(
string $viewName,
string|null $schemaName = null,
bool $ifExists = true
): bool;
```
Drops a materialized view (PostgreSQL only).
dropPrimaryKey()
```php
public function dropPrimaryKey(
string $tableName,
string $schemaName
): bool;
```
Drops a table's primary key
dropTable()
```php
public function dropTable(
string $tableName,
string|null $schemaName = null,
bool $ifExists = true
): bool;
```
Drops a table from a schema/database
dropView()
```php
public function dropView(
string $viewName,
string|null $schemaName = null,
bool $ifExists = true
): bool;
```
Drops a view
escapeIdentifier()
```php
public function escapeIdentifier( mixed $identifier ): string;
```
Escapes a column/table/schema name
```php
$escapedTable = $connection->escapeIdentifier(
"co_invoices"
);
$escapedTable = $connection->escapeIdentifier(
[
"store",
"co_invoices",
]
);
```
fetchAll()
```php
public function fetchAll(
string $sqlQuery,
int $fetchMode = Enum::FETCH_ASSOC,
array $bindParams = [],
array $bindTypes = []
): array;
```
Dumps the complete result of a query into an array
```php
// Getting all invoices with associative indexes only
$invoices = $connection->fetchAll(
"SELECT * FROM co_invoices",
\Phalcon\Db\Enum::FETCH_ASSOC
);
foreach ($invoices as $invoice) {
print_r($invoice);
}
// Getting all invoices whose title contains the word "Test"
$invoices = $connection->fetchAll(
"SELECT * FROM co_invoices WHERE inv_title LIKE :inv_title",
\Phalcon\Db\Enum::FETCH_ASSOC,
[
"inv_title" => "%Test%",
]
);
foreach($invoices as $invoice) {
print_r($invoice);
}
```
fetchColumn()
```php
public function fetchColumn(
string $sqlQuery,
array $placeholders = [],
mixed $column = 0
): string|bool;
```
Returns the n'th field of first row in a SQL query result
```php
// Getting count of invoices
$invoicesCount = $connection->fetchColumn("SELECT count(*) FROM co_invoices");
print_r($invoicesCount);
// Getting the title of the last created invoice
$invoice = $connection->fetchColumn(
"SELECT inv_id, inv_title FROM co_invoices ORDER BY inv_created_at DESC",
1
);
print_r($invoice);
```
fetchOne()
```php
public function fetchOne(
string $sqlQuery,
mixed $fetchMode = Enum::FETCH_ASSOC,
array $bindParams = [],
array $bindTypes = []
): array;
```
Returns the first row in a SQL query result
```php
// Getting first invoice
$invoice = $connection->fetchOne("SELECT * FROM co_invoices");
print_r($invoice);
// Getting first invoice with associative indexes only
$invoice = $connection->fetchOne(
"SELECT * FROM co_invoices",
\Phalcon\Db\Enum::FETCH_ASSOC
);
print_r($invoice);
```
forUpdate()
```php
public function forUpdate(
string $sqlQuery,
string $modifier = ""
): string;
```
Returns a SQL modified with a FOR UPDATE clause. The optional
`modifier` is passed straight to the dialect (use `Dialect::LOCK_NOWAIT`
/ `Dialect::LOCK_SKIP_LOCKED` / `Dialect::LOCK_NONE`).
getColumnDefinition()
```php
public function getColumnDefinition( ColumnInterface $column ): string;
```
Returns the SQL column definition from a column
getColumnList()
```php
public function getColumnList( mixed $columnList ): string;
```
Gets a list of columns
getConnectionId()
```php
public function getConnectionId(): int;
```
Gets the active connection unique identifier
getDefaultIdValue()
```php
public function getDefaultIdValue(): RawValue;
```
Returns the default identity value to be inserted in an identity column
```php
// Inserting a new invoice with a valid default value for the column 'inv_id'
$success = $connection->insert(
"co_invoices",
[
$connection->getDefaultIdValue(),
"Test Invoice",
100,
],
[
"inv_id",
"inv_title",
"inv_total",
]
);
```
getDefaultValue()
```php
public function getDefaultValue(): RawValue;
```
Returns the default value to make the RBDM use the default value declared
in the table definition
```php
// Inserting a new invoice with a valid default value for the column 'inv_total'
$success = $connection->insert(
"co_invoices",
[
"Test Invoice",
$connection->getDefaultValue()
],
[
"inv_title",
"inv_total",
]
);
```
@todo Return NULL if this is not supported by the adapter
getDescriptor()
```php
public function getDescriptor(): array;
```
Return descriptor used to connect to the active database
getDialect()
```php
public function getDialect(): DialectInterface;
```
Returns internal dialect instance
getDialectType()
```php
public function getDialectType(): string;
```
Name of the dialect used
getEventsManager()
```php
public function getEventsManager(): ManagerInterface|null;
```
Returns the internal event manager
getNestedTransactionSavepointName()
```php
public function getNestedTransactionSavepointName(): string;
```
Returns the savepoint name to use for nested transactions
getRealSQLStatement()
```php
public function getRealSQLStatement(): string;
```
Active SQL statement in the object without replace bound parameters
getSQLBindTypes()
```php
public function getSQLBindTypes(): array;
```
Active SQL statement in the object
getSQLStatement()
```php
public function getSQLStatement(): string;
```
Active SQL statement in the object
getSQLVariables()
```php
public function getSQLVariables(): array;
```
Active SQL variables in the object
getType()
```php
public function getType(): string;
```
Type of database system the adapter is used for
insert()
```php
public function insert(
string $table,
array $values,
mixed $fields = null,
mixed $dataTypes = null
): bool;
```
Inserts data into a table using custom RDBMS SQL syntax
```php
// Inserting a new invoice
$success = $connection->insert(
"co_invoices",
["Test Invoice", 100],
["inv_title", "inv_total"]
);
// Next SQL sentence is sent to the database system
INSERT INTO `co_invoices` (`inv_title`, `inv_total`) VALUES ("Test Invoice", 100);
```
insertAsDict()
```php
public function insertAsDict(
string $table,
mixed $data,
mixed $dataTypes = null
): bool;
```
Inserts data into a table using custom RBDM SQL syntax
```php
// Inserting a new invoice
$success = $connection->insertAsDict(
"co_invoices",
[
"inv_title" => "Test Invoice",
"inv_total" => 100,
]
);
// Next SQL sentence is sent to the database system
INSERT INTO `co_invoices` (`inv_title`, `inv_total`) VALUES ("Test Invoice", 100);
```
isNestedTransactionsWithSavepoints()
```php
public function isNestedTransactionsWithSavepoints(): bool;
```
Returns if nested transactions should use savepoints
limit()
```php
public function limit(
string $sqlQuery,
mixed $number
): string;
```
Appends a LIMIT clause to $sqlQuery argument
```php
echo $connection->limit("SELECT * FROM co_invoices", 5);
```
listTables()
```php
public function listTables( string|null $schemaName = null ): array;
```
List all tables on a database
```php
print_r(
$connection->listTables("blog")
);
```
listViews()
```php
public function listViews( string|null $schemaName = null ): array;
```
List all views on a database
```php
print_r(
$connection->listViews("blog")
);
```
modifyColumn()
```php
public function modifyColumn(
string $tableName,
string $schemaName,
ColumnInterface $column,
ColumnInterface|null $currentColumn = null
): bool;
```
Modifies a table column based on a definition
onConflictUpdate()
```php
public function onConflictUpdate(
string $sqlQuery,
array $conflictColumns,
array $updateColumns
): string;
```
Appends an `ON CONFLICT (...) DO UPDATE SET col = excluded.col`
upsert clause to the supplied INSERT statement. Supported by
PostgreSQL and SQLite 3.24+; MySQL throws.
refreshMaterializedView()
```php
public function refreshMaterializedView(
string $viewName,
string|null $schemaName = null,
bool $concurrent = false
): bool;
```
Refreshes a materialized view (PostgreSQL only). Pass
`concurrent = true` for non-blocking refresh.
releaseSavepoint()
```php
public function releaseSavepoint( string $name ): bool;
```
Releases given savepoint
returning()
```php
public function returning(
string $sqlQuery,
array $columns
): string;
```
Appends a RETURNING clause to an INSERT/UPDATE/DELETE SQL statement
and returns the modified SQL. Supported by PostgreSQL and SQLite 3.35+;
MySQL throws (no RETURNING construct). Pass `["*"]` for `RETURNING *`.
rollbackSavepoint()
```php
public function rollbackSavepoint( string $name ): bool;
```
Rollbacks given savepoint
setDialect()
```php
public function setDialect( DialectInterface $dialect );
```
Sets the dialect used to produce the SQL
setEventsManager()
```php
public function setEventsManager( ManagerInterface $eventsManager ): void;
```
Sets the event manager
setNestedTransactionsWithSavepoints()
```php
public function setNestedTransactionsWithSavepoints( bool $nestedTransactionsWithSavepoints ): AdapterInterface;
```
Set if nested transactions should use savepoints
setup()
```php
public static function setup( array $options ): void;
```
Enables/disables options in the Database component.
The flags are stored as process-global `Phalcon\Support\Settings`
(`db.escape_identifiers`, `db.force_casting`) and therefore affect every
connection in the process at once, last-writer-wins. Call this once at
bootstrap; it is not per-connection configuration. Because the
constructor calls `setup()` whenever a descriptor carries an `options`
key, constructing one adapter with `options` can change the SQL another,
already-configured connection generates.
sharedLock()
```php
public function sharedLock(
string $sqlQuery,
string $modifier = ""
): string;
```
Returns a SQL modified with a shared-lock clause. The optional
`modifier` is passed straight to the dialect (use
`Dialect::LOCK_NOWAIT` / `Dialect::LOCK_SKIP_LOCKED` for PostgreSQL).
supportSequences()
```php
public function supportSequences(): bool;
```
Check whether the database system requires a sequence to produce
auto-numeric values
supportsDefaultValue()
```php
public function supportsDefaultValue(): bool;
```
Check whether the database system support the DEFAULT
keyword (SQLite does not support it)
tableExists()
```php
public function tableExists(
string $tableName,
string|null $schemaName = null
): bool;
```
Generates SQL checking for the existence of a schema.table
```php
var_dump(
$connection->tableExists("blog", "posts")
);
```
tableOptions()
```php
public function tableOptions(
string $tableName,
string|null $schemaName = null
): array;
```
Gets creation options from a table
```php
print_r(
$connection->tableOptions("co_invoices")
);
```
update()
```php
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
```php
// Updating existing invoice
$success = $connection->update(
"co_invoices",
["inv_title"],
["New Test Invoice"],
"inv_id = 101"
);
// Next SQL sentence is sent to the database system
UPDATE `co_invoices` SET `inv_title` = "New Test Invoice" WHERE inv_id = 101
// Updating existing invoice with array condition and $dataTypes
$success = $connection->update(
"co_invoices",
["inv_title"],
["New Test Invoice"],
[
"conditions" => "inv_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.
updateAsDict()
```php
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
```php
// Updating existing invoice
$success = $connection->updateAsDict(
"co_invoices",
[
"inv_title" => "New Test Invoice",
],
"inv_id = 101"
);
// Next SQL sentence is sent to the database system
UPDATE `co_invoices` SET `inv_title` = "New Test Invoice" WHERE inv_id = 101
```
useExplicitIdValue()
```php
public function useExplicitIdValue(): bool;
```
Check whether the database system requires an explicit value for identity
columns
viewExists()
```php
public function viewExists(
string $viewName,
string|null $schemaName = null
): bool;
```
Generates SQL checking for the existence of a schema.view
```php
var_dump(
$connection->viewExists("active_users", "posts")
);
```
## Db\Adapter\AdapterInterface
Interface
Phalcon\Db\Adapter\AdapterInterface
- [`Phalcon\Contracts\Db\Adapter\Adapter`](/5.20/api/phalcon_contracts/#contractsdbadapteradapter)
- **`Phalcon\Db\Adapter\AdapterInterface`**
`Phalcon\Contracts\Db\Adapter\Adapter`
## Db\Adapter\PdoFactory
Class
- [`Phalcon\Factory\AbstractConfigFactory`](/5.20/api/phalcon_factory/#factoryabstractconfigfactory)
- [`Phalcon\Factory\AbstractFactory`](/5.20/api/phalcon_factory/#factoryabstractfactory)
- **`Phalcon\Db\Adapter\PdoFactory`**
`Phalcon\Db\Adapter\Pdo\Mysql` · `Phalcon\Db\Adapter\Pdo\Postgresql` · `Phalcon\Db\Adapter\Pdo\Sqlite` · `Phalcon\Db\Exception` · `Phalcon\Factory\AbstractFactory` · `Phalcon\Traits\Support\Helper\Arr\GetTrait`
### Method Summary
Constructor
Factory to create an instance from a Config object
Create a new instance of the adapter
Returns the available adapters
### Methods
__construct()
```php
public function __construct( array $services = [] );
```
Constructor
load()
```php
public function load( mixed $config ): AdapterInterface;
```
Factory to create an instance from a Config object
newInstance()
```php
public function newInstance(
string $name,
array $options = []
): AdapterInterface;
```
Create a new instance of the adapter
getExceptionClass()
```php
protected function getExceptionClass(): string;
```
getServices()
```php
protected function getServices(): array;
```
Returns the available adapters
## Db\Adapter\Pdo\AbstractPdo
Abstract
Phalcon\Db\Adapter\Pdo is the Phalcon\Db that internally uses PDO to connect
to a database
```php
use Phalcon\Db\Adapter\Pdo\Mysql;
$config = [
"host" => "localhost",
"dbname" => "blog",
"port" => 3306,
"username" => "sigma",
"password" => "secret",
];
$connection = new Mysql($config);
```
- [`Phalcon\Db\Adapter\AbstractAdapter`](#dbadapterabstractadapter)
- **`Phalcon\Db\Adapter\Pdo\AbstractPdo`**
- [`Phalcon\Db\Adapter\Pdo\Mysql`](#dbadapterpdomysql)
- [`Phalcon\Db\Adapter\Pdo\Postgresql`](#dbadapterpdopostgresql)
- [`Phalcon\Db\Adapter\Pdo\Sqlite`](#dbadapterpdosqlite)
`Phalcon\Db\Adapter\AbstractAdapter` · `Phalcon\Db\Column` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\CannotPrepareStatement` · `Phalcon\Db\Exceptions\InvalidBindParameter` · `Phalcon\Db\Exceptions\MatchedParameterNotFound` · `Phalcon\Db\Exceptions\NoActiveTransaction` · `Phalcon\Db\ResultInterface` · `Phalcon\Db\Result\PdoResult` · `Phalcon\Events\ManagerInterface` · `Phalcon\Support\Settings`
### Method Summary
Constructor for Phalcon\Db\Adapter\Pdo
Returns the number of affected rows by the latest INSERT/UPDATE/DELETE
Starts a transaction in the connection
Closes the active connection returning success. Phalcon automatically
Commits the active transaction in the connection
This method is automatically called in \Phalcon\Db\Adapter\Pdo
Converts bound parameters such as :name: or ?1 into PDO bind params ?
Ensures the connection is alive, reconnecting in place if it is not.
Escapes a value to avoid SQL injections according to the active charset
Sends SQL statements to the database server returning the success state.
Executes a prepared statement binding. This function uses integer indexes
Returns whether transparent auto-reconnect is enabled.
Return the error info, if any
Return internal PDO handler
Returns the current transaction nesting level
Checks whether the connection is under a transaction
Returns the insert id for the auto_increment/serial column inserted in
Checks whether the underlying connection is still alive by issuing a
Returns a PDO prepared statement to be executed with 'executePrepared'
Sends SQL statements to the database server returning the success state.
Rollbacks the active transaction in the connection
Enables or disables transparent auto-reconnect on a lost connection.
Returns PDO adapter DSN defaults as a key-value map.
Recognizes whether an exception represents a lost ("gone away")
Constructs the SQL statement (with parameters)
### Constants
### Properties
Last affected rows
Whether to transparently reconnect and retry once when a query fails
because the connection was lost. Opt-in; off by default.
PDO Handler
### Methods
__construct()
```php
public function __construct( array $descriptor );
```
Constructor for Phalcon\Db\Adapter\Pdo
affectedRows()
```php
public function affectedRows(): int;
```
Returns the number of affected rows by the latest INSERT/UPDATE/DELETE
executed in the database system
```php
$connection->execute(
"DELETE FROM co_invoices"
);
echo $connection->affectedRows(), " were deleted";
```
begin()
```php
public function begin( bool $nesting = true ): bool;
```
Starts a transaction in the connection
close()
```php
public function close(): void;
```
Closes the active connection returning success. Phalcon automatically
closes and destroys active connections when the request ends
commit()
```php
public function commit( bool $nesting = true ): bool;
```
Commits the active transaction in the connection
connect()
```php
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.
```php
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();
```
convertBoundParams()
```php
public function convertBoundParams(
string $sql,
array $params = []
): array;
```
Converts bound parameters such as :name: or ?1 into PDO bind params ?
```php
print_r(
$connection->convertBoundParams(
"SELECT * FROM co_invoices WHERE inv_title = :inv_title:",
[
"Test Invoice",
]
)
);
```
ensureConnection()
```php
public function ensureConnection(): void;
```
Ensures the connection is alive, reconnecting in place if it is not.
escapeString()
```php
public function escapeString( string $str ): string;
```
Escapes a value to avoid SQL injections according to the active charset
in the connection
```php
$escapedStr = $connection->escapeString("some dangerous value");
```
execute()
```php
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 does not
return any rows
```php
// Inserting data
$success = $connection->execute(
"INSERT INTO co_invoices VALUES (1, 'Test Invoice')"
);
$success = $connection->execute(
"INSERT INTO co_invoices VALUES (?, ?)",
[
1,
"Test Invoice",
]
);
```
executePrepared()
```php
public function executePrepared(
\PDOStatement $statement,
array $placeholders,
array $dataTypes = []
): \PDOStatement;
```
Executes a prepared statement binding. This function uses integer indexes
starting from zero
```php
use Phalcon\Db\Column;
$statement = $db->prepare(
"SELECT * FROM co_invoices WHERE inv_title = :inv_title"
);
$result = $connection->executePrepared(
$statement,
[
"inv_title" => "Test Invoice",
],
[
"inv_title" => Column::BIND_PARAM_STR,
]
);
```
getAutoReconnect()
```php
public function getAutoReconnect(): bool;
```
Returns whether transparent auto-reconnect is enabled.
getErrorInfo()
```php
public function getErrorInfo(): array;
```
Return the error info, if any
getInternalHandler()
```php
public function getInternalHandler(): mixed;
```
Return internal PDO handler
getTransactionLevel()
```php
public function getTransactionLevel(): int;
```
Returns the current transaction nesting level
isUnderTransaction()
```php
public function isUnderTransaction(): bool;
```
Checks whether the connection is under a transaction
```php
$connection->begin();
// true
var_dump(
$connection->isUnderTransaction()
);
```
lastInsertId()
```php
public function lastInsertId( string|null $name = null ): string|bool;
```
Returns the insert id for the auto_increment/serial column inserted in
the latest executed SQL statement
```php
// Inserting a new invoice
$success = $connection->insert(
"co_invoices",
[
"Test Invoice",
100,
],
[
"inv_title",
"inv_total",
]
);
// Getting the generated id
$id = $connection->lastInsertId();
```
ping()
```php
public function ping(): bool;
```
Checks whether the underlying connection is still alive by issuing a
trivial query. Returns false if there is no handle or the probe fails.
prepare()
```php
public function prepare( string $sqlStatement ): \PDOStatement;
```
Returns a PDO prepared statement to be executed with 'executePrepared'
```php
use Phalcon\Db\Column;
$statement = $db->prepare(
"SELECT * FROM co_invoices WHERE inv_title = :inv_title"
);
$result = $connection->executePrepared(
$statement,
[
"inv_title" => "Test Invoice",
],
[
"inv_title" => Column::BIND_PARAM_INT,
]
);
```
query()
```php
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
```php
// Querying data
$resultset = $connection->query(
"SELECT * FROM co_invoices WHERE inv_status_flag = 1"
);
$resultset = $connection->query(
"SELECT * FROM co_invoices WHERE inv_status_flag = ?",
[
1,
]
);
```
rollback()
```php
public function rollback( bool $nesting = true ): bool;
```
Rollbacks the active transaction in the connection
setAutoReconnect()
```php
public function setAutoReconnect( bool $autoReconnect ): static;
```
Enables or disables transparent auto-reconnect on a lost connection.
getDsnDefaults()
```php
abstract protected function getDsnDefaults(): array;
```
Returns PDO adapter DSN defaults as a key-value map.
isConnectionError()
```php
protected function isConnectionError( \Throwable $exception ): bool;
```
Recognizes whether an exception represents a lost ("gone away")
connection. The base adapter cannot know driver specifics, so it
returns false; concrete adapters override this.
prepareRealSql()
```php
protected function prepareRealSql(
string $statement,
array $parameters
): void;
```
Constructs the SQL statement (with parameters)
@see https://stackoverflow.com/a/8403150
## Db\Adapter\Pdo\Mysql
Class
Specific functions for the MySQL database system
```php
use Phalcon\Db\Adapter\Pdo\Mysql;
$config = [
"host" => "localhost",
"dbname" => "blog",
"port" => 3306,
"username" => "sigma",
"password" => "secret",
];
$connection = new Mysql($config);
```
- [`Phalcon\Db\Adapter\AbstractAdapter`](#dbadapterabstractadapter)
- [`Phalcon\Db\Adapter\Pdo\AbstractPdo`](#dbadapterpdoabstractpdo)
- **`Phalcon\Db\Adapter\Pdo\Mysql`**
`Phalcon\Db\Adapter\Pdo\AbstractPdo` · `Phalcon\Db\Column` · `Phalcon\Db\ColumnInterface` · `Phalcon\Db\Enum` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\MissingForeignKeyChecks` · `Phalcon\Db\Index` · `Phalcon\Db\IndexInterface` · `Phalcon\Db\Reference` · `Phalcon\Db\ReferenceInterface`
### Method Summary
Adds a foreign key to a table
Returns an array of Phalcon\Db\Column objects describing a table
Lists table indexes
Lists table references
Returns PDO adapter DSN defaults as a key-value map.
Recognizes a MySQL "server has gone away" / "Lost connection" failure
### Properties
### Methods
addForeignKey()
```php
public function addForeignKey(
string $tableName,
string $schemaName,
ReferenceInterface $reference
): bool;
```
Adds a foreign key to a table
describeColumns()
```php
public function describeColumns(
string $table,
string|null $schema = null
): ColumnInterface[];
```
Returns an array of Phalcon\Db\Column objects describing a table
```php
print_r(
$connection->describeColumns("posts")
);
```
describeIndexes()
```php
public function describeIndexes(
string $table,
string|null $schema = null
): IndexInterface[];
```
Lists table indexes
```php
print_r(
$connection->describeIndexes("co_orders_x_products")
);
```
describeReferences()
```php
public function describeReferences(
string $table,
string|null $schema = null
): ReferenceInterface[];
```
Lists table references
```php
print_r(
$connection->describeReferences("co_orders_x_products")
);
```
getDsnDefaults()
```php
protected function getDsnDefaults(): array;
```
Returns PDO adapter DSN defaults as a key-value map.
isConnectionError()
```php
protected function isConnectionError( \Throwable $exception ): bool;
```
Recognizes a MySQL "server has gone away" / "Lost connection" failure
by the driver error code (2006 / 2013) with a message fallback.
## Db\Adapter\Pdo\Postgresql
Class
Specific functions for the PostgreSQL database system
```php
use Phalcon\Db\Adapter\Pdo\Postgresql;
$config = [
"host" => "localhost",
"dbname" => "blog",
"port" => 5432,
"username" => "postgres",
"password" => "secret",
];
$connection = new Postgresql($config);
```
- [`Phalcon\Db\Adapter\AbstractAdapter`](#dbadapterabstractadapter)
- [`Phalcon\Db\Adapter\Pdo\AbstractPdo`](#dbadapterpdoabstractpdo)
- **`Phalcon\Db\Adapter\Pdo\Postgresql`**
`Phalcon\Db\Adapter\Pdo\AbstractPdo` · `Phalcon\Db\Column` · `Phalcon\Db\ColumnInterface` · `Phalcon\Db\Enum` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\TableMustHaveColumn` · `Phalcon\Db\RawValue` · `Phalcon\Db\Reference` · `Phalcon\Db\ReferenceInterface` · `Throwable`
### Method Summary
Constructor for Phalcon\Db\Adapter\Pdo\Postgresql
This method is automatically called in Phalcon\Db\Adapter\Pdo
Creates a table
Returns an array of Phalcon\Db\Column objects describing a table
Lists table references
Returns the default identity value to be inserted in an identity column
Modifies a table column based on a definition
Check whether the database system requires a sequence to produce
Check whether the database system requires an explicit value for identity
Returns PDO adapter DSN defaults as a key-value map.
Recognizes a PostgreSQL connection-loss failure by SQLSTATE
### Properties
### Methods
__construct()
```php
public function __construct( array $descriptor );
```
Constructor for Phalcon\Db\Adapter\Pdo\Postgresql
connect()
```php
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.
createTable()
```php
public function createTable(
string $tableName,
string $schemaName,
array $definition
): bool;
```
Creates a table
describeColumns()
```php
public function describeColumns(
string $table,
string|null $schema = null
): ColumnInterface[];
```
Returns an array of Phalcon\Db\Column objects describing a table
```php
print_r(
$connection->describeColumns("posts")
);
```
describeReferences()
```php
public function describeReferences(
string $table,
string|null $schema = null
): ReferenceInterface[];
```
Lists table references
```php
print_r(
$connection->describeReferences("co_orders_x_products")
);
```
getDefaultIdValue()
```php
public function getDefaultIdValue(): RawValue;
```
Returns the default identity value to be inserted in an identity column
```php
// Inserting a new invoice with a valid default value for the column 'inv_id'
$success = $connection->insert(
"co_invoices",
[
$connection->getDefaultIdValue(),
"Test Invoice",
100,
],
[
"inv_id",
"inv_title",
"inv_total",
]
);
```
modifyColumn()
```php
public function modifyColumn(
string $tableName,
string $schemaName,
ColumnInterface $column,
ColumnInterface|null $currentColumn = null
): bool;
```
Modifies a table column based on a definition
supportSequences()
```php
public function supportSequences(): bool;
```
Check whether the database system requires a sequence to produce
auto-numeric values
useExplicitIdValue()
```php
public function useExplicitIdValue(): bool;
```
Check whether the database system requires an explicit value for identity
columns
getDsnDefaults()
```php
protected function getDsnDefaults(): array;
```
Returns PDO adapter DSN defaults as a key-value map.
isConnectionError()
```php
protected function isConnectionError( \Throwable $exception ): bool;
```
Recognizes a PostgreSQL connection-loss failure by SQLSTATE
(connection exception class 08, or admin/crash shutdown 57P0x) with a
message fallback.
## Db\Adapter\Pdo\Sqlite
Class
Specific functions for the SQLite database system
```php
use Phalcon\Db\Adapter\Pdo\Sqlite;
$connection = new Sqlite(
[
"dbname" => "/tmp/test.sqlite",
]
);
```
- [`Phalcon\Db\Adapter\AbstractAdapter`](#dbadapterabstractadapter)
- [`Phalcon\Db\Adapter\Pdo\AbstractPdo`](#dbadapterpdoabstractpdo)
- **`Phalcon\Db\Adapter\Pdo\Sqlite`**
`Phalcon\Db\Adapter\Pdo\AbstractPdo` · `Phalcon\Db\Column` · `Phalcon\Db\ColumnInterface` · `Phalcon\Db\Enum` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\MissingSqliteDatabase` · `Phalcon\Db\Index` · `Phalcon\Db\IndexInterface` · `Phalcon\Db\RawValue` · `Phalcon\Db\Reference` · `Phalcon\Db\ReferenceInterface`
### Method Summary
Constructor for Phalcon\Db\Adapter\Pdo\Sqlite
This method is automatically called in Phalcon\Db\Adapter\Pdo
Returns an array of Phalcon\Db\Column objects describing a table
Lists table indexes
Lists table references
Returns the default value to make the RBDM use the default value declared
SQLite does not support the DEFAULT keyword
Check whether the database system requires an explicit value for identity
Returns PDO adapter DSN defaults as a key-value map.
### Properties
### Methods
__construct()
```php
public function __construct( array $descriptor );
```
Constructor for Phalcon\Db\Adapter\Pdo\Sqlite
connect()
```php
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.
describeColumns()
```php
public function describeColumns(
string $table,
string|null $schema = null
): ColumnInterface[];
```
Returns an array of Phalcon\Db\Column objects describing a table
```php
print_r(
$connection->describeColumns("posts")
);
```
describeIndexes()
```php
public function describeIndexes(
string $table,
string|null $schema = null
): IndexInterface[];
```
Lists table indexes
```php
print_r(
$connection->describeIndexes("co_orders_x_products")
);
```
describeReferences()
```php
public function describeReferences(
string $table,
string|null $schema = null
): ReferenceInterface[];
```
Lists table references
getDefaultValue()
```php
public function getDefaultValue(): RawValue;
```
Returns the default value to make the RBDM use the default value declared
in the table definition
```php
// Inserting a new invoice with a valid default value for the column 'inv_total'
$success = $connection->insert(
"co_invoices",
[
"Test Invoice",
$connection->getDefaultValue(),
],
[
"inv_title",
"inv_total",
]
);
```
supportsDefaultValue()
```php
public function supportsDefaultValue(): bool;
```
SQLite does not support the DEFAULT keyword
useExplicitIdValue()
```php
public function useExplicitIdValue(): bool;
```
Check whether the database system requires an explicit value for identity
columns
getDsnDefaults()
```php
protected function getDsnDefaults(): array;
```
Returns PDO adapter DSN defaults as a key-value map.
## Db\Check
Class
Allows to define `CHECK` constraints on tables. CHECK constraints enforce
a boolean SQL predicate on each row of the table; rows that fail the
predicate are rejected at INSERT/UPDATE time.
```php
use Phalcon\Db\Check;
$positivePrice = new Check(
"chk_price_positive",
[
"expression" => "price > 0",
]
);
// Used inside a createTable() definition
$connection->createTable(
"products",
null,
[
"columns" => [ ... ],
"checks" => [$positivePrice],
]
);
// Or added to an existing table (MySQL 8.0.16+ and PostgreSQL).
// SQLite cannot add CHECK constraints to existing tables.
$connection->addCheck("products", null, $positivePrice);
```
- **`Phalcon\Db\Check`** - implements [`Phalcon\Db\CheckInterface`](#dbcheckinterface)
`Phalcon\Db\Exceptions\CheckExpressionRequired` · `Phalcon\Db\Exceptions\InvalidCheckExpression`
### Method Summary
Phalcon\Db\Check constructor
Returns the CHECK expression
Returns the constraint name (may be an empty string for unnamed)
### Properties
The boolean SQL predicate this constraint enforces.
The CHECK constraint name. An empty string indicates an unnamed
constraint - the dialect will emit the clause without a `CONSTRAINT`
prefix in that case.
### Methods
__construct()
```php
public function __construct(
string $name,
array $definition
);
```
Phalcon\Db\Check constructor
getExpression()
```php
public function getExpression(): string;
```
Returns the CHECK expression
getName()
```php
public function getName(): string;
```
Returns the constraint name (may be an empty string for unnamed)
## Db\CheckInterface
Interface
Phalcon\Db\CheckInterface
- [`Phalcon\Contracts\Db\Check`](/5.20/api/phalcon_contracts/#contractsdbcheck)
- **`Phalcon\Db\CheckInterface`**
`Phalcon\Contracts\Db\Check`
## Db\Column
Class
Allows to define columns to be used on create or alter table operations
```php
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("co_invoices", null, $column);
```
- **`Phalcon\Db\Column`** - implements [`Phalcon\Db\ColumnInterface`](#dbcolumninterface)
`Phalcon\Db\Exceptions\ColumnTypeRejectsAutoIncrement` · `Phalcon\Db\Exceptions\ColumnTypeRejectsScale` · `Phalcon\Db\Exceptions\ColumnTypeRequired` · `Phalcon\Db\Exceptions\GeneratedAutoIncrementConflict` · `Phalcon\Db\Exceptions\GeneratedDefaultConflict` · `Phalcon\Db\Exceptions\InvalidGenerationExpression`
### Method Summary
Phalcon\Db\Column constructor
Check whether field absolute to position in table
Returns the type of bind handling
Column's comment
Default column value
Returns the generation expression for a generated/computed column.
Column's name
Integer column number scale
Integer column size
Column data type
Column data type reference
Column data type values
Check whether column has default value
Whether the column is an array of its base type. Recognized by the
Auto-Increment
Check whether column have first position in table
Whether the column is a generated/computed column.
Whether a generated column is `STORED`. `false` means `VIRTUAL`.
Whether the column is declared `INVISIBLE` (MySQL 8.0.23+). Invisible
Not null
Check whether column have an numeric type
Column is part of the primary key?
Returns true if number column is unsigned
### Constants
Bind Type Blob
Bind Type Bool
Bind Type Decimal
Bind Type Integer
Bind Type Null
Bind Type String
Skip binding by type
Big integer abstract data type
Binary abstract data type
Bit abstract data type
Blob abstract data type
Bool abstract data type
PostgreSQL `BYTEA` binary type
Char abstract data type
PostgreSQL `CIDR` network-address type
Date abstract data type
PostgreSQL `DATERANGE` range-of-date type
Datetime abstract data type
Decimal abstract data type
Double abstract data type
Enum abstract data type
Float abstract data type
Spatial `GEOMETRY` base type (MySQL 5.7+; PostgreSQL + PostGIS)
Spatial `GEOMETRYCOLLECTION` type (MySQL; PostgreSQL + PostGIS)
PostgreSQL `INET` IPv4/IPv6 address type
PostgreSQL `INT4RANGE` range-of-integer type
PostgreSQL `INT8RANGE` range-of-bigint type
Int abstract data type
Json abstract data type
Jsonb abstract data type
Spatial `LINESTRING` type (MySQL; PostgreSQL + PostGIS)
Longblob abstract data type
Longtext abstract data type
PostgreSQL `MACADDR` MAC-address type
Mediumblob abstract data type
Mediumintegerr abstract data type
Mediumtext abstract data type
Spatial `MULTILINESTRING` type (MySQL; PostgreSQL + PostGIS)
Spatial `MULTIPOINT` type (MySQL; PostgreSQL + PostGIS)
Spatial `MULTIPOLYGON` type (MySQL; PostgreSQL + PostGIS)
PostgreSQL `NUMRANGE` range-of-numeric type
Spatial `POINT` type (MySQL; PostgreSQL + PostGIS)
Spatial `POLYGON` type (MySQL; PostgreSQL + PostGIS)
Smallint abstract data type
Text abstract data type
Time abstract data type
Timestamp abstract data type
Tinyblob abstract data type
Tinyint abstract data type
Tinytext abstract data type
PostgreSQL `TSRANGE` range-of-timestamp (without time zone) type
PostgreSQL `TSTZRANGE` range-of-timestamp (with time zone) type
UUID abstract data type
Varbinary abstract data type
Varchar abstract data type
### Properties
Column Position
Column is autoIncrement?
Bind Type
Column's comment
Default column value
Position is first
Generation expression for `GENERATED ALWAYS AS (...)`. Null when the
column is not a generated/computed column.
Whether a generated column is `STORED` (true) or `VIRTUAL` (false).
Ignored when the column is not generated. PostgreSQL only supports
`STORED` and emits it regardless of this flag.
Whether the column is `INVISIBLE` (MySQL 8.0.23+). Invisible columns
are excluded from `SELECT *` expansion but can still be referenced
explicitly.
Whether the column is an array of its base type. Recognized by the
PostgreSQL dialect (e.g. `INTEGER[]`, `TEXT[]`). MySQL and SQLite
ignore the flag.
The column have some numeric type?
Column's name
Column not nullable?
Default SQL definition is NOT NULL.
Column is part of the primary key?
Integer column number scale
Integer column size
Column data type
Column data type reference
Column data type values
Integer column unsigned?
### Methods
__construct()
```php
public function __construct(
string $name,
array $definition
);
```
Phalcon\Db\Column constructor
getAfterPosition()
```php
public function getAfterPosition(): string|null;
```
Check whether field absolute to position in table
getBindType()
```php
public function getBindType(): int;
```
Returns the type of bind handling
```php
public function getComment(): string|null;
```
Column's comment
getDefault()
```php
public function getDefault(): mixed;
```
Default column value
getGenerationExpression()
```php
public function getGenerationExpression(): string|null;
```
Returns the generation expression for a generated/computed column.
Returns `null` when the column is not generated.
getName()
```php
public function getName(): string;
```
Column's name
getScale()
```php
public function getScale(): int;
```
Integer column number scale
getSize()
```php
public function getSize(): int|string;
```
Integer column size
getType()
```php
public function getType(): int|string;
```
Column data type
getTypeReference()
```php
public function getTypeReference(): int;
```
Column data type reference
getTypeValues()
```php
public function getTypeValues(): array|string;
```
Column data type values
hasDefault()
```php
public function hasDefault(): bool;
```
Check whether column has default value
isArray()
```php
public function isArray(): bool;
```
Whether the column is an array of its base type. Recognized by the
PostgreSQL dialect (e.g. `INTEGER[]`, `TEXT[]`); MySQL and SQLite
ignore the flag.
isAutoIncrement()
```php
public function isAutoIncrement(): bool;
```
Auto-Increment
isFirst()
```php
public function isFirst(): bool;
```
Check whether column have first position in table
isGenerated()
```php
public function isGenerated(): bool;
```
Whether the column is a generated/computed column.
isGenerationStored()
```php
public function isGenerationStored(): bool;
```
Whether a generated column is `STORED`. `false` means `VIRTUAL`.
Always meaningful only when `isGenerated()` is `true`.
isInvisible()
```php
public function isInvisible(): bool;
```
Whether the column is declared `INVISIBLE` (MySQL 8.0.23+). Invisible
columns are excluded from `SELECT *` expansion but can still be
referenced explicitly. PostgreSQL and SQLite have no equivalent and
dialects targeting them ignore the flag.
isNotNull()
```php
public function isNotNull(): bool;
```
Not null
isNumeric()
```php
public function isNumeric(): bool;
```
Check whether column have an numeric type
isPrimary()
```php
public function isPrimary(): bool;
```
Column is part of the primary key?
isUnsigned()
```php
public function isUnsigned(): bool;
```
Returns true if number column is unsigned
## Db\ColumnInterface
Interface
Phalcon\Db\ColumnInterface
- [`Phalcon\Contracts\Db\Column`](/5.20/api/phalcon_contracts/#contractsdbcolumn)
- **`Phalcon\Db\ColumnInterface`**
`Phalcon\Contracts\Db\Column`
## Db\Dialect
Abstract
This is the base class to each database dialect. This implements
common methods to transform intermediate code into its RDBMS related syntax
- **`Phalcon\Db\Dialect`** - implements [`Phalcon\Db\DialectInterface`](#dbdialectinterface)
- [`Phalcon\Db\Dialect\Mysql`](#dbdialectmysql)
- [`Phalcon\Db\Dialect\Postgresql`](#dbdialectpostgresql)
- [`Phalcon\Db\Dialect\Sqlite`](#dbdialectsqlite)
`Phalcon\Db\Exceptions\ConflictTargetColumnRequired` · `Phalcon\Db\Exceptions\ConflictUpdateColumnRequired` · `Phalcon\Db\Exceptions\InvalidGroupByExpression` · `Phalcon\Db\Exceptions\InvalidListExpression` · `Phalcon\Db\Exceptions\InvalidOrderByExpression` · `Phalcon\Db\Exceptions\InvalidSqlExpression` · `Phalcon\Db\Exceptions\InvalidSqlExpressionType` · `Phalcon\Db\Exceptions\InvalidUnaryExpression` · `Phalcon\Db\Exceptions\MaterializedViewsNotSupported` · `Phalcon\Db\Exceptions\MissingDefinitionKey` · `Phalcon\Db\Exceptions\ReturningNotSupported` · `Phalcon\Db\Exceptions\UnsupportedOperator` · `Phalcon\Support\Settings`
### Method Summary
Generates SQL to create a materialized view. Supported by PostgreSQL
Generate SQL to create a new savepoint
Generates SQL to drop a materialized view. Supported by PostgreSQL.
Escape identifiers
Escape Schema
Returns a SQL modified with a FOR UPDATE clause. The optional `modifier`
Gets a list of columns with escaped identifiers
Returns registered functions
Resolve Column expressions
Transforms an intermediate representation for an expression into a database system valid expression
Transform an intermediate representation of a schema/table into a
Generates the SQL for LIMIT clause
Appends an `ON CONFLICT (col, ...) DO UPDATE SET col = excluded.col`
Generates SQL to refresh a materialized view. Supported by
Registers custom SQL functions
Generate SQL to release a savepoint
Returns a SQL statement extended with a `RETURNING` clause so the
Generate SQL to rollback a savepoint
Builds a SELECT statement
Checks whether the platform supports the full `ALTER TABLE` matrix:
Checks whether the platform supports materialized views. Only PostgreSQL
Checks whether the platform supports the `ON CONFLICT (...) DO UPDATE`
Checks whether the platform supports releasing savepoints.
Checks whether the platform supports the `RETURNING` clause. MySQL
Checks whether the platform supports savepoints
Checks the column type and if not string it returns the type reference
Checks the column type and returns the updated SQL statement
Escape a string literal for a single quoted SQL string. The standard
Builds a CHECK constraint clause from a `CheckInterface`, using the
Returns the size of the column enclosed in parentheses
Returns the column size and scale enclosed in parentheses
Builds the `GENERATED ALWAYS AS () VIRTUAL|STORED` clause for a
Builds the per-index parenthesized column list, honoring per-column
Renders a LIMIT/OFFSET value: a bound placeholder passes through, any
Resolve *
Resolve binary operations expressions
Resolve CASE expressions
Resolve CAST of values
Resolve CONVERT of values encodings
Resolve a FROM clause
Resolve function calls
Resolve a GROUP BY clause
Resolve a HAVING clause
Resolve a JOINs clause
Resolve a LIMIT clause
Resolve Lists
Resolve object expressions
Resolve an ORDER BY clause
Resolve qualified expressions
Resolve Column expressions
Resolve unary operations expressions
Resolve a WHERE clause
Prepares column for this RDBMS
Prepares qualified for this RDBMS
Prepares table for this RDBMS
### Properties
Dialect-specific operators that a concrete dialect must opt into
via supportedOperators; using one elsewhere throws.
Subset of guardedOperators that this dialect emits. Overridden per
dialect.
### Methods
createMaterializedView()
```php
public function createMaterializedView(
string $viewName,
array $definition,
string|null $schemaName = null
): string;
```
Generates SQL to create a materialized view. Supported by PostgreSQL
(`CREATE MATERIALIZED VIEW name AS `). Other dialects inherit
this throw - MySQL and SQLite have no materialized-view concept.
createSavepoint()
```php
public function createSavepoint( string $name ): string;
```
Generate SQL to create a new savepoint
dropMaterializedView()
```php
public function dropMaterializedView(
string $viewName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a materialized view. Supported by PostgreSQL.
escape()
```php
final public function escape(
string $str,
string|null $escapeChar = null
): string;
```
Escape identifiers
escapeSchema()
```php
final public function escapeSchema(
string $str,
string|null $escapeChar = null
): string;
```
Escape Schema
forUpdate()
```php
public function forUpdate(
string $sqlQuery,
string $modifier = ""
): string;
```
Returns a SQL modified with a FOR UPDATE clause. The optional `modifier`
appends a row-lock disposition keyword.
```php
$sql = $dialect->forUpdate("SELECT * FROM co_invoices");
echo $sql; // SELECT * FROM co_invoices FOR UPDATE
$sql = $dialect->forUpdate(
"SELECT * FROM co_invoices",
Dialect::LOCK_NOWAIT
);
echo $sql; // SELECT * FROM co_invoices FOR UPDATE NOWAIT
$sql = $dialect->forUpdate(
"SELECT * FROM co_invoices",
Dialect::LOCK_SKIP_LOCKED
);
echo $sql; // SELECT * FROM co_invoices FOR UPDATE SKIP LOCKED
```
getColumnList()
```php
final public function getColumnList(
array $columnList,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Gets a list of columns with escaped identifiers
```php
echo $dialect->getColumnList(
[
"column1",
"column",
]
);
```
getCustomFunctions()
```php
public function getCustomFunctions(): array;
```
Returns registered functions
getSqlColumn()
```php
final public function getSqlColumn(
mixed $column,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve Column expressions
getSqlExpression()
```php
public function getSqlExpression(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Transforms an intermediate representation for an expression into a database system valid expression
getSqlTable()
```php
final public function getSqlTable(
mixed $table,
string|null $escapeChar = null
): string;
```
Transform an intermediate representation of a schema/table into a
database system valid expression
limit()
```php
public function limit(
string $sqlQuery,
mixed $number
): string;
```
Generates the SQL for LIMIT clause
```php
// SELECT * FROM co_invoices LIMIT 10
echo $dialect->limit(
"SELECT * FROM co_invoices",
10
);
// SELECT * FROM co_invoices LIMIT 10 OFFSET 50
echo $dialect->limit(
"SELECT * FROM co_invoices",
[10, 50]
);
```
onConflictUpdate()
```php
public function onConflictUpdate(
string $sqlQuery,
array $conflictColumns,
array $updateColumns
): string;
```
Appends an `ON CONFLICT (col, ...) DO UPDATE SET col = excluded.col`
upsert clause to the supplied INSERT statement. The syntax is the
SQL standard form recognized by PostgreSQL (9.5+) and SQLite (3.24+).
MySQL overrides this method to throw because its `ON DUPLICATE KEY
UPDATE` has a different shape (deferred to parser item #23).
refreshMaterializedView()
```php
public function refreshMaterializedView(
string $viewName,
string|null $schemaName = null,
bool $concurrent = false
): string;
```
Generates SQL to refresh a materialized view. Supported by
PostgreSQL. Pass `concurrent = true` for `REFRESH MATERIALIZED VIEW
CONCURRENTLY ...`, which avoids blocking concurrent SELECTs (requires
the view to have a unique index).
registerCustomFunction()
```php
public function registerCustomFunction(
string $name,
callable $customFunction
): static;
```
Registers custom SQL functions
releaseSavepoint()
```php
public function releaseSavepoint( string $name ): string;
```
Generate SQL to release a savepoint
returning()
```php
public function returning(
string $sqlQuery,
array $columns
): string;
```
Returns a SQL statement extended with a `RETURNING` clause so the
INSERT/UPDATE/DELETE returns rows. Supported by PostgreSQL and
SQLite 3.35+. Pass `["*"]` for `RETURNING *`, or a list of column
names. The base implementation throws - MySQL inherits it because
MySQL has no RETURNING construct.
rollbackSavepoint()
```php
public function rollbackSavepoint( string $name ): string;
```
Generate SQL to rollback a savepoint
select()
```php
public function select( array $definition ): string;
```
Builds a SELECT statement
supportsAlterTable()
```php
public function supportsAlterTable(): bool;
```
Checks whether the platform supports the full `ALTER TABLE` matrix:
modifying existing columns and adding or dropping foreign keys, primary
keys, and check constraints. SQLite returns false - those operations
throw a dedicated `Sqlite*NotSupported` exception there (basic
`ADD COLUMN` remains available).
supportsMaterializedViews()
```php
public function supportsMaterializedViews(): bool;
```
Checks whether the platform supports materialized views. Only PostgreSQL
returns true; `createMaterializedView()` throws on the other dialects.
supportsOnConflictUpdate()
```php
public function supportsOnConflictUpdate(): bool;
```
Checks whether the platform supports the `ON CONFLICT (...) DO UPDATE`
upsert clause. MySQL returns false; `onConflictUpdate()` throws there.
supportsReleaseSavepoints()
```php
public function supportsReleaseSavepoints(): bool;
```
Checks whether the platform supports releasing savepoints.
supportsReturning()
```php
public function supportsReturning(): bool;
```
Checks whether the platform supports the `RETURNING` clause. MySQL
returns false; `returning()` throws there.
supportsSavepoints()
```php
public function supportsSavepoints(): bool;
```
Checks whether the platform supports savepoints
checkColumnType()
```php
protected function checkColumnType( ColumnInterface $column ): string;
```
Checks the column type and if not string it returns the type reference
checkColumnTypeSql()
```php
protected function checkColumnTypeSql( ColumnInterface $column ): string;
```
Checks the column type and returns the updated SQL statement
escapeStringLiteral()
```php
protected function escapeStringLiteral( string $value ): string;
```
Escape a string literal for a single quoted SQL string. The standard
way doubles the single quotes. A dialect where the backslash is an
escape character must override this method.
getCheckClause()
```php
protected function getCheckClause(
CheckInterface $check,
string $escapeChar = "`"
): string;
```
Builds a CHECK constraint clause from a `CheckInterface`, using the
provided escape character for the constraint name (so each dialect
gets its native quoting). Returns the clause body - the dialect's
`createTable()` / `addCheck()` is expected to prefix `ADD` or place
the result on its own line as appropriate.
getColumnSize()
```php
protected function getColumnSize( ColumnInterface $column ): string;
```
Returns the size of the column enclosed in parentheses
getColumnSizeAndScale()
```php
protected function getColumnSizeAndScale( ColumnInterface $column ): string;
```
Returns the column size and scale enclosed in parentheses
getGeneratedClause()
```php
protected function getGeneratedClause(
ColumnInterface $column,
bool $forceStored = false
): string;
```
Builds the `GENERATED ALWAYS AS () VIRTUAL|STORED` clause for a
generated/computed column. Returns an empty string when the column is
not generated. When `forceStored` is `true` the clause is always emitted
as `STORED` regardless of the column's `isGenerationStored()` flag -
PostgreSQL uses this since it only supports stored generated columns.
getIndexColumnList()
```php
protected function getIndexColumnList(
IndexInterface $index,
bool $wrapExpressions = true
): string;
```
Builds the per-index parenthesized column list, honoring per-column
sort directions when the index declares any. Returns the bare
comma-separated `getColumnList()` output when no directions are set,
preserving the legacy rendering exactly. When directions are set,
each column is followed by ` ASC` or ` DESC`; trailing positions
absent from the directions array default to `ASC`.
getLimitValue()
```php
protected function getLimitValue( mixed $value ): string;
```
Renders a LIMIT/OFFSET value: a bound placeholder passes through, any
other value is coerced to an integer to prevent SQL injection.
getSqlExpressionAll()
```php
final protected function getSqlExpressionAll(
array $expression,
string|null $escapeChar = null
): string;
```
Resolve *
getSqlExpressionBinaryOperations()
```php
final protected function getSqlExpressionBinaryOperations(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve binary operations expressions
getSqlExpressionCase()
```php
final protected function getSqlExpressionCase(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve CASE expressions
getSqlExpressionCastValue()
```php
final protected function getSqlExpressionCastValue(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve CAST of values
getSqlExpressionConvertValue()
```php
final protected function getSqlExpressionConvertValue(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve CONVERT of values encodings
getSqlExpressionFrom()
```php
final protected function getSqlExpressionFrom(
mixed $expression,
string|null $escapeChar = null
): string;
```
Resolve a FROM clause
getSqlExpressionFunctionCall()
```php
final protected function getSqlExpressionFunctionCall(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve function calls
getSqlExpressionGroupBy()
```php
final protected function getSqlExpressionGroupBy(
mixed $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve a GROUP BY clause
getSqlExpressionHaving()
```php
final protected function getSqlExpressionHaving(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve a HAVING clause
getSqlExpressionJoins()
```php
final protected function getSqlExpressionJoins(
mixed $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve a JOINs clause
getSqlExpressionLimit()
```php
final protected function getSqlExpressionLimit(
mixed $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve a LIMIT clause
getSqlExpressionList()
```php
final protected function getSqlExpressionList(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve Lists
getSqlExpressionObject()
```php
final protected function getSqlExpressionObject(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve object expressions
getSqlExpressionOrderBy()
```php
final protected function getSqlExpressionOrderBy(
mixed $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve an ORDER BY clause
getSqlExpressionQualified()
```php
final protected function getSqlExpressionQualified(
array $expression,
string|null $escapeChar = null
): string;
```
Resolve qualified expressions
getSqlExpressionScalar()
```php
final protected function getSqlExpressionScalar(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve Column expressions
getSqlExpressionUnaryOperations()
```php
final protected function getSqlExpressionUnaryOperations(
array $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve unary operations expressions
getSqlExpressionWhere()
```php
final protected function getSqlExpressionWhere(
mixed $expression,
string|null $escapeChar = null,
array $bindCounts = []
): string;
```
Resolve a WHERE clause
prepareColumnAlias()
```php
protected function prepareColumnAlias(
string $qualified,
string|null $alias = null,
string|null $escapeChar = null
): string;
```
Prepares column for this RDBMS
prepareQualified()
```php
protected function prepareQualified(
string $column,
string|null $domain = null,
string|null $escapeChar = null
): string;
```
Prepares qualified for this RDBMS
prepareTable()
```php
protected function prepareTable(
string $table,
string|null $schema = null,
string|null $alias = null,
string|null $escapeChar = null
): string;
```
Prepares table for this RDBMS
## Db\DialectInterface
Interface
Phalcon\Db\DialectInterface
- [`Phalcon\Contracts\Db\Dialect`](/5.20/api/phalcon_contracts/#contractsdbdialect)
- **`Phalcon\Db\DialectInterface`**
`Phalcon\Contracts\Db\Dialect`
## Db\Dialect\Mysql
Class
Generates database specific SQL for the MySQL RDBMS
- [`Phalcon\Db\Dialect`](#dbdialect)
- **`Phalcon\Db\Dialect\Mysql`**
`Phalcon\Db\CheckInterface` · `Phalcon\Db\Column` · `Phalcon\Db\ColumnInterface` · `Phalcon\Db\Dialect` · `Phalcon\Db\DialectInterface` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\MissingDefinitionKey` · `Phalcon\Db\Exceptions\MysqlOnConflictNotSupported` · `Phalcon\Db\Exceptions\UnrecognizedDataType` · `Phalcon\Db\IndexInterface` · `Phalcon\Db\RawValue` · `Phalcon\Db\ReferenceInterface`
### Method Summary
Generates SQL to add a CHECK constraint to an existing table.
Generates SQL to add a column to a table
Generates SQL to add an index to a table
Generates SQL to add an index to a table
Generates SQL to add the primary key to a table
Generates SQL to create a table
Generates SQL to create a view
Generates SQL describing a table
Generates SQL to query indexes on a table
Generates SQL to query foreign keys on a table
Generates SQL to delete a CHECK constraint from a table
Generates SQL to delete a column from a table
Generates SQL to delete a foreign key from a table
Generates SQL to delete an index from a table
Generates SQL to delete primary key from a table
Generates SQL to drop a table
Generates SQL to drop a view
Gets the column name in MySQL
Generates SQL to check DB parameter FOREIGN_KEY_CHECKS.
List all tables in database
Generates the SQL to list all views of a schema or user
Generates SQL to modify a column in a table
MySQL does not support the SQL-standard `ON CONFLICT DO UPDATE`
Returns a SQL modified with a LOCK IN SHARE MODE clause. The `modifier`
MySQL does not support the SQL-standard `ON CONFLICT (...) DO UPDATE`
Generates SQL checking for the existence of a schema.table
Generates the SQL to describe the table creation options
Generates SQL to truncate a table
Generates SQL checking for the existence of a schema.view
Escape a string literal for a single quoted SQL string. MySQL treats the
Generates SQL to add the table creation options
### Properties
### Methods
addCheck()
```php
public function addCheck(
string $tableName,
string $schemaName,
CheckInterface $check
): string;
```
Generates SQL to add a CHECK constraint to an existing table.
Enforced by MySQL 8.0.16+.
addColumn()
```php
public function addColumn(
string $tableName,
string $schemaName,
ColumnInterface $column
): string;
```
Generates SQL to add a column to a table
addForeignKey()
```php
public function addForeignKey(
string $tableName,
string $schemaName,
ReferenceInterface $reference
): string;
```
Generates SQL to add an index to a table
addIndex()
```php
public function addIndex(
string $tableName,
string $schemaName,
IndexInterface $index
): string;
```
Generates SQL to add an index to a table
addPrimaryKey()
```php
public function addPrimaryKey(
string $tableName,
string $schemaName,
IndexInterface $index
): string;
```
Generates SQL to add the primary key to a table
createTable()
```php
public function createTable(
string $tableName,
string $schemaName,
array $definition
): string;
```
Generates SQL to create a table
createView()
```php
public function createView(
string $viewName,
array $definition,
string|null $schemaName = null
): string;
```
Generates SQL to create a view
describeColumns()
```php
public function describeColumns(
string $table,
string|null $schema = null
): string;
```
Generates SQL describing a table
```php
print_r(
$dialect->describeColumns("posts")
);
```
describeIndexes()
```php
public function describeIndexes(
string $table,
string|null $schema = null
): string;
```
Generates SQL to query indexes on a table
describeReferences()
```php
public function describeReferences(
string $table,
string|null $schema = null
): string;
```
Generates SQL to query foreign keys on a table
dropCheck()
```php
public function dropCheck(
string $tableName,
string $schemaName,
string $checkName
): string;
```
Generates SQL to delete a CHECK constraint from a table
dropColumn()
```php
public function dropColumn(
string $tableName,
string $schemaName,
string $columnName
): string;
```
Generates SQL to delete a column from a table
dropForeignKey()
```php
public function dropForeignKey(
string $tableName,
string $schemaName,
string $referenceName
): string;
```
Generates SQL to delete a foreign key from a table
dropIndex()
```php
public function dropIndex(
string $tableName,
string $schemaName,
string $indexName
): string;
```
Generates SQL to delete an index from a table
dropPrimaryKey()
```php
public function dropPrimaryKey(
string $tableName,
string $schemaName
): string;
```
Generates SQL to delete primary key from a table
dropTable()
```php
public function dropTable(
string $tableName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a table
dropView()
```php
public function dropView(
string $viewName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a view
getColumnDefinition()
```php
public function getColumnDefinition( ColumnInterface $column ): string;
```
Gets the column name in MySQL
getForeignKeyChecks()
```php
public function getForeignKeyChecks(): string;
```
Generates SQL to check DB parameter FOREIGN_KEY_CHECKS.
listTables()
```php
public function listTables( string|null $schemaName = null ): string;
```
List all tables in database
```php
print_r(
$dialect->listTables("blog")
);
```
listViews()
```php
public function listViews( string|null $schemaName = null ): string;
```
Generates the SQL to list all views of a schema or user
modifyColumn()
```php
public function modifyColumn(
string $tableName,
string $schemaName,
ColumnInterface $column,
ColumnInterface|null $currentColumn = null
): string;
```
Generates SQL to modify a column in a table
onConflictUpdate()
```php
public function onConflictUpdate(
string $sqlQuery,
array $conflictColumns,
array $updateColumns
): string;
```
MySQL does not support the SQL-standard `ON CONFLICT DO UPDATE`
upsert syntax - it has its own `INSERT ... ON DUPLICATE KEY UPDATE`
which requires PHQL grammar work (deferred). The base helper is
overridden here to throw, preventing accidental emission of invalid
SQL on MySQL connections.
sharedLock()
```php
public function sharedLock(
string $sqlQuery,
string $modifier = ""
): string;
```
Returns a SQL modified with a LOCK IN SHARE MODE clause. The `modifier`
argument is accepted for signature parity with the contract but is
silently ignored on MySQL - its legacy `LOCK IN SHARE MODE` syntax has
no `NOWAIT` / `SKIP LOCKED` variant. Callers needing those modifiers
should target PostgreSQL or stay on `forUpdate()`.
```php
$sql = $dialect->sharedLock("SELECT * FROM co_invoices");
echo $sql; // SELECT * FROM co_invoices LOCK IN SHARE MODE
```
supportsOnConflictUpdate()
```php
public function supportsOnConflictUpdate(): bool;
```
MySQL does not support the SQL-standard `ON CONFLICT (...) DO UPDATE`
upsert clause; `onConflictUpdate()` throws.
tableExists()
```php
public function tableExists(
string $tableName,
string|null $schemaName = null
): string;
```
Generates SQL checking for the existence of a schema.table
```php
echo $dialect->tableExists("posts", "blog");
echo $dialect->tableExists("posts");
```
tableOptions()
```php
public function tableOptions(
string $table,
string|null $schema = null
): string;
```
Generates the SQL to describe the table creation options
truncateTable()
```php
public function truncateTable(
string $tableName,
string $schemaName
): string;
```
Generates SQL to truncate a table
viewExists()
```php
public function viewExists(
string $viewName,
string|null $schemaName = null
): string;
```
Generates SQL checking for the existence of a schema.view
escapeStringLiteral()
```php
protected function escapeStringLiteral( string $value ): string;
```
Escape a string literal for a single quoted SQL string. MySQL treats the
backslash as an escape character, so it must be doubled together with the
single quote.
getTableOptions()
```php
protected function getTableOptions( array $definition ): string;
```
Generates SQL to add the table creation options
## Db\Dialect\Postgresql
Class
Generates database specific SQL for the PostgreSQL RDBMS
- [`Phalcon\Db\Dialect`](#dbdialect)
- **`Phalcon\Db\Dialect\Postgresql`**
`Phalcon\Db\CheckInterface` · `Phalcon\Db\Column` · `Phalcon\Db\ColumnInterface` · `Phalcon\Db\Dialect` · `Phalcon\Db\DialectInterface` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\MissingDefinitionKey` · `Phalcon\Db\Exceptions\ReturningRequiresColumn` · `Phalcon\Db\Exceptions\UnrecognizedDataType` · `Phalcon\Db\IndexInterface` · `Phalcon\Db\RawValue` · `Phalcon\Db\ReferenceInterface`
### Method Summary
Generates SQL to add a CHECK constraint to an existing table.
Generates SQL to add a column to a table
Generates SQL to add an index to a table
Generates SQL to add an index to a table
Generates SQL to add the primary key to a table
Generates SQL to create a materialized view.
Generates SQL to create a table
Generates SQL to create a view
Generates SQL describing a table
Generates SQL to query indexes on a table
Generates SQL to query foreign keys on a table
Generates SQL to delete a CHECK constraint from a table
Generates SQL to delete a column from a table
Generates SQL to delete a foreign key from a table
Generates SQL to delete an index from a table
Generates SQL to drop a materialized view.
Generates SQL to delete primary key from a table
Generates SQL to drop a table
Generates SQL to drop a view
Gets the column name in PostgreSQL
List all tables in database
Generates the SQL to list all views of a schema or user
Generates SQL to modify a column in a table
Generates SQL to refresh a materialized view. When `concurrent` is
Appends a `RETURNING` clause to the supplied INSERT/UPDATE/DELETE
Returns a SQL modified with a `FOR SHARE` clause - PostgreSQL's
PostgreSQL supports materialized views (`CREATE MATERIALIZED VIEW`).
PostgreSQL supports the `RETURNING` clause.
Generates SQL checking for the existence of a schema.table
Generates the SQL to describe the table creation options
Generates SQL to truncate a table
Generates SQL checking for the existence of a schema.view
### Properties
### Methods
addCheck()
```php
public function addCheck(
string $tableName,
string $schemaName,
CheckInterface $check
): string;
```
Generates SQL to add a CHECK constraint to an existing table.
addColumn()
```php
public function addColumn(
string $tableName,
string $schemaName,
ColumnInterface $column
): string;
```
Generates SQL to add a column to a table
addForeignKey()
```php
public function addForeignKey(
string $tableName,
string $schemaName,
ReferenceInterface $reference
): string;
```
Generates SQL to add an index to a table
addIndex()
```php
public function addIndex(
string $tableName,
string $schemaName,
IndexInterface $index
): string;
```
Generates SQL to add an index to a table
addPrimaryKey()
```php
public function addPrimaryKey(
string $tableName,
string $schemaName,
IndexInterface $index
): string;
```
Generates SQL to add the primary key to a table
createMaterializedView()
```php
public function createMaterializedView(
string $viewName,
array $definition,
string|null $schemaName = null
): string;
```
Generates SQL to create a materialized view.
createTable()
```php
public function createTable(
string $tableName,
string $schemaName,
array $definition
): string;
```
Generates SQL to create a table
createView()
```php
public function createView(
string $viewName,
array $definition,
string|null $schemaName = null
): string;
```
Generates SQL to create a view
describeColumns()
```php
public function describeColumns(
string $table,
string|null $schema = null
): string;
```
Generates SQL describing a table
```php
print_r(
$dialect->describeColumns("posts")
);
```
describeIndexes()
```php
public function describeIndexes(
string $table,
string|null $schema = null
): string;
```
Generates SQL to query indexes on a table
describeReferences()
```php
public function describeReferences(
string $table,
string|null $schema = null
): string;
```
Generates SQL to query foreign keys on a table
dropCheck()
```php
public function dropCheck(
string $tableName,
string $schemaName,
string $checkName
): string;
```
Generates SQL to delete a CHECK constraint from a table
dropColumn()
```php
public function dropColumn(
string $tableName,
string $schemaName,
string $columnName
): string;
```
Generates SQL to delete a column from a table
dropForeignKey()
```php
public function dropForeignKey(
string $tableName,
string $schemaName,
string $referenceName
): string;
```
Generates SQL to delete a foreign key from a table
dropIndex()
```php
public function dropIndex(
string $tableName,
string $schemaName,
string $indexName
): string;
```
Generates SQL to delete an index from a table
dropMaterializedView()
```php
public function dropMaterializedView(
string $viewName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a materialized view.
dropPrimaryKey()
```php
public function dropPrimaryKey(
string $tableName,
string $schemaName
): string;
```
Generates SQL to delete primary key from a table
dropTable()
```php
public function dropTable(
string $tableName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a table
dropView()
```php
public function dropView(
string $viewName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a view
getColumnDefinition()
```php
public function getColumnDefinition( ColumnInterface $column ): string;
```
Gets the column name in PostgreSQL
listTables()
```php
public function listTables( string|null $schemaName = null ): string;
```
List all tables in database
```php
print_r(
$dialect->listTables("blog")
);
```
listViews()
```php
public function listViews( string|null $schemaName = null ): string;
```
Generates the SQL to list all views of a schema or user
modifyColumn()
```php
public function modifyColumn(
string $tableName,
string $schemaName,
ColumnInterface $column,
ColumnInterface|null $currentColumn = null
): string;
```
Generates SQL to modify a column in a table
refreshMaterializedView()
```php
public function refreshMaterializedView(
string $viewName,
string|null $schemaName = null,
bool $concurrent = false
): string;
```
Generates SQL to refresh a materialized view. When `concurrent` is
true, emits `REFRESH MATERIALIZED VIEW CONCURRENTLY ...` (avoids
blocking concurrent SELECTs; requires a unique index on the view).
returning()
```php
public function returning(
string $sqlQuery,
array $columns
): string;
```
Appends a `RETURNING` clause to the supplied INSERT/UPDATE/DELETE
statement. Pass `["*"]` for `RETURNING *`, or a list of column names.
sharedLock()
```php
public function sharedLock(
string $sqlQuery,
string $modifier = ""
): string;
```
Returns a SQL modified with a `FOR SHARE` clause - PostgreSQL's
equivalent of MySQL's `LOCK IN SHARE MODE`. The optional `modifier`
appends a row-lock disposition keyword (pass `Dialect::LOCK_NOWAIT`
or `Dialect::LOCK_SKIP_LOCKED`).
```php
echo $dialect->sharedLock("SELECT * FROM co_invoices");
// SELECT * FROM co_invoices FOR SHARE
echo $dialect->sharedLock(
"SELECT * FROM co_invoices",
Dialect::LOCK_NOWAIT
);
// SELECT * FROM co_invoices FOR SHARE NOWAIT
```
supportsMaterializedViews()
```php
public function supportsMaterializedViews(): bool;
```
PostgreSQL supports materialized views (`CREATE MATERIALIZED VIEW`).
supportsReturning()
```php
public function supportsReturning(): bool;
```
PostgreSQL supports the `RETURNING` clause.
tableExists()
```php
public function tableExists(
string $tableName,
string|null $schemaName = null
): string;
```
Generates SQL checking for the existence of a schema.table
```php
echo $dialect->tableExists("posts", "blog");
echo $dialect->tableExists("posts");
```
tableOptions()
```php
public function tableOptions(
string $table,
string|null $schema = null
): string;
```
Generates the SQL to describe the table creation options
truncateTable()
```php
public function truncateTable(
string $tableName,
string $schemaName
): string;
```
Generates SQL to truncate a table
viewExists()
```php
public function viewExists(
string $viewName,
string|null $schemaName = null
): string;
```
Generates SQL checking for the existence of a schema.view
castDefault()
```php
protected function castDefault( ColumnInterface $column ): string;
```
getTableOptions()
```php
protected function getTableOptions( array $definition ): string;
```
## Db\Dialect\Sqlite
Class
Generates database specific SQL for the SQLite RDBMS
- [`Phalcon\Db\Dialect`](#dbdialect)
- **`Phalcon\Db\Dialect\Sqlite`**
`Phalcon\Db\CheckInterface` · `Phalcon\Db\Column` · `Phalcon\Db\ColumnInterface` · `Phalcon\Db\Dialect` · `Phalcon\Db\DialectInterface` · `Phalcon\Db\Exception` · `Phalcon\Db\Exceptions\MissingDefinitionKey` · `Phalcon\Db\Exceptions\ReturningRequiresColumn` · `Phalcon\Db\Exceptions\SqliteAlterCheckNotSupported` · `Phalcon\Db\Exceptions\SqliteAlterColumnNotSupported` · `Phalcon\Db\Exceptions\SqliteAlterForeignKeyNotSupported` · `Phalcon\Db\Exceptions\SqliteAlterPrimaryKeyNotSupported` · `Phalcon\Db\Exceptions\SqliteDropCheckNotSupported` · `Phalcon\Db\Exceptions\SqliteDropForeignKeyNotSupported` · `Phalcon\Db\Exceptions\SqliteDropPrimaryKeyNotSupported` · `Phalcon\Db\Exceptions\UnrecognizedDataType` · `Phalcon\Db\IndexInterface` · `Phalcon\Db\RawValue` · `Phalcon\Db\ReferenceInterface`
### Method Summary
SQLite cannot ALTER an existing table to add a CHECK constraint;
Generates SQL to add a column to a table
Generates SQL to add an index to a table
Generates SQL to add an index to a table
Generates SQL to add the primary key to a table
Generates SQL to create a table
Generates SQL to create a view
Generates SQL describing a table
Generates SQL to query indexes detail on a table
Generates SQL to query indexes on a table
Generates SQL to query foreign keys on a table
SQLite cannot DROP a CHECK constraint from an existing table.
Generates SQL to delete a column from a table.
Generates SQL to delete a foreign key from a table
Generates SQL to delete an index from a table
Generates SQL to delete primary key from a table
Generates SQL to drop a table
Generates SQL to drop a view
Returns a SQL modified with a FOR UPDATE clause. SQLite has no
Gets the column name in SQLite
Generates the SQL to get query list of indexes
List all tables in database
Generates the SQL to list all views of a schema or user
Generates SQL to modify a column in a table
Appends a `RETURNING` clause to the supplied INSERT/UPDATE/DELETE
SQLite has no row-level shared-lock construct, so the original query
SQLite cannot modify existing columns or add/drop foreign keys, primary
SQLite (3.35+) supports the `RETURNING` clause.
Generates SQL checking for the existence of a schema.table
Generates the SQL to describe the table creation options
Generates SQL to truncate a table
Generates SQL checking for the existence of a schema.view
### Properties
### Methods
addCheck()
```php
public function addCheck(
string $tableName,
string $schemaName,
CheckInterface $check
): string;
```
SQLite cannot ALTER an existing table to add a CHECK constraint;
the constraint must be declared at CREATE TABLE time.
addColumn()
```php
public function addColumn(
string $tableName,
string $schemaName,
ColumnInterface $column
): string;
```
Generates SQL to add a column to a table
addForeignKey()
```php
public function addForeignKey(
string $tableName,
string $schemaName,
ReferenceInterface $reference
): string;
```
Generates SQL to add an index to a table
addIndex()
```php
public function addIndex(
string $tableName,
string $schemaName,
IndexInterface $index
): string;
```
Generates SQL to add an index to a table
addPrimaryKey()
```php
public function addPrimaryKey(
string $tableName,
string $schemaName,
IndexInterface $index
): string;
```
Generates SQL to add the primary key to a table
createTable()
```php
public function createTable(
string $tableName,
string $schemaName,
array $definition
): string;
```
Generates SQL to create a table
createView()
```php
public function createView(
string $viewName,
array $definition,
string|null $schemaName = null
): string;
```
Generates SQL to create a view
describeColumns()
```php
public function describeColumns(
string $table,
string|null $schema = null
): string;
```
Generates SQL describing a table
```php
print_r(
$dialect->describeColumns("posts")
);
```
describeIndex()
```php
public function describeIndex( string $index ): string;
```
Generates SQL to query indexes detail on a table
describeIndexes()
```php
public function describeIndexes(
string $table,
string|null $schema = null
): string;
```
Generates SQL to query indexes on a table
describeReferences()
```php
public function describeReferences(
string $table,
string|null $schema = null
): string;
```
Generates SQL to query foreign keys on a table
dropCheck()
```php
public function dropCheck(
string $tableName,
string $schemaName,
string $checkName
): string;
```
SQLite cannot DROP a CHECK constraint from an existing table.
dropColumn()
```php
public function dropColumn(
string $tableName,
string $schemaName,
string $columnName
): string;
```
Generates SQL to delete a column from a table.
SQLite 3.35+ supports `ALTER TABLE ... DROP COLUMN ...` directly. On
older versions the server rejects the statement at execution time;
cphalcon no longer pre-empts that rejection at the dialect level so
callers on 3.35+ can use the feature.
dropForeignKey()
```php
public function dropForeignKey(
string $tableName,
string $schemaName,
string $referenceName
): string;
```
Generates SQL to delete a foreign key from a table
dropIndex()
```php
public function dropIndex(
string $tableName,
string $schemaName,
string $indexName
): string;
```
Generates SQL to delete an index from a table
dropPrimaryKey()
```php
public function dropPrimaryKey(
string $tableName,
string $schemaName
): string;
```
Generates SQL to delete primary key from a table
dropTable()
```php
public function dropTable(
string $tableName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a table
dropView()
```php
public function dropView(
string $viewName,
string|null $schemaName = null,
bool $ifExists = true
): string;
```
Generates SQL to drop a view
forUpdate()
```php
public function forUpdate(
string $sqlQuery,
string $modifier = ""
): string;
```
Returns a SQL modified with a FOR UPDATE clause. SQLite has no
row-level locking, so the original query is returned unchanged
regardless of the `modifier` argument (`NOWAIT` / `SKIP LOCKED` are
silently ignored).
getColumnDefinition()
```php
public function getColumnDefinition( ColumnInterface $column ): string;
```
Gets the column name in SQLite
listIndexesSql()
```php
public function listIndexesSql(
string $table,
string|null $schema = null,
string|null $keyName = null
): string;
```
Generates the SQL to get query list of indexes
```php
print_r(
$dialect->listIndexesSql("blog")
);
```
listTables()
```php
public function listTables( string|null $schemaName = null ): string;
```
List all tables in database
```php
print_r(
$dialect->listTables("blog")
);
```
listViews()
```php
public function listViews( string|null $schemaName = null ): string;
```
Generates the SQL to list all views of a schema or user
modifyColumn()
```php
public function modifyColumn(
string $tableName,
string $schemaName,
ColumnInterface $column,
ColumnInterface|null $currentColumn = null
): string;
```
Generates SQL to modify a column in a table
returning()
```php
public function returning(
string $sqlQuery,
array $columns
): string;
```
Appends a `RETURNING` clause to the supplied INSERT/UPDATE/DELETE
statement. Supported by SQLite 3.35+. Pass `["*"]` for `RETURNING *`,
or a list of column names.
sharedLock()
```php
public function sharedLock(
string $sqlQuery,
string $modifier = ""
): string;
```
SQLite has no row-level shared-lock construct, so the original query
is returned unchanged regardless of the `modifier` argument.
supportsAlterTable()
```php
public function supportsAlterTable(): bool;
```
SQLite cannot modify existing columns or add/drop foreign keys, primary
keys, or check constraints through `ALTER TABLE`; those operations throw
a dedicated `Sqlite*NotSupported` exception.
supportsReturning()
```php
public function supportsReturning(): bool;
```
SQLite (3.35+) supports the `RETURNING` clause.
tableExists()
```php
public function tableExists(
string $tableName,
string|null $schemaName = null
): string;
```
Generates SQL checking for the existence of a schema.table
```php
echo $dialect->tableExists("posts", "blog");
echo $dialect->tableExists("posts");
```
tableOptions()
```php
public function tableOptions(
string $table,
string|null $schema = null
): string;
```
Generates the SQL to describe the table creation options
truncateTable()
```php
public function truncateTable(
string $tableName,
string $schemaName
): string;
```
Generates SQL to truncate a table
viewExists()
```php
public function viewExists(
string $viewName,
string|null $schemaName = null
): string;
```
Generates SQL checking for the existence of a schema.view
## Db\Enum
Class
Constants for Phalcon\Db
- **`Phalcon\Db\Enum`**
### Constants
## Db\Exception
Class
Exceptions thrown in Phalcon\Db will use this class
- `\Exception`
- **`Phalcon\Db\Exception`**
- [`Phalcon\Db\Exceptions\CannotInsertWithoutData`](#dbexceptionscannotinsertwithoutdata)
- [`Phalcon\Db\Exceptions\CannotPrepareStatement`](#dbexceptionscannotpreparestatement)
- [`Phalcon\Db\Exceptions\CheckExpressionRequired`](#dbexceptionscheckexpressionrequired)
- [`Phalcon\Db\Exceptions\ColumnTypeRejectsAutoIncrement`](#dbexceptionscolumntyperejectsautoincrement)
- [`Phalcon\Db\Exceptions\ColumnTypeRejectsScale`](#dbexceptionscolumntyperejectsscale)
- [`Phalcon\Db\Exceptions\ColumnTypeRequired`](#dbexceptionscolumntyperequired)
- [`Phalcon\Db\Exceptions\ConflictTargetColumnRequired`](#dbexceptionsconflicttargetcolumnrequired)
- [`Phalcon\Db\Exceptions\ConflictUpdateColumnRequired`](#dbexceptionsconflictupdatecolumnrequired)
- [`Phalcon\Db\Exceptions\ForeignKeyColumnsRequired`](#dbexceptionsforeignkeycolumnsrequired)
- [`Phalcon\Db\Exceptions\GeneratedAutoIncrementConflict`](#dbexceptionsgeneratedautoincrementconflict)
- [`Phalcon\Db\Exceptions\GeneratedDefaultConflict`](#dbexceptionsgenerateddefaultconflict)
- [`Phalcon\Db\Exceptions\IncompleteBindTypes`](#dbexceptionsincompletebindtypes)
- [`Phalcon\Db\Exceptions\InvalidBindParameter`](#dbexceptionsinvalidbindparameter)
- [`Phalcon\Db\Exceptions\InvalidCheckExpression`](#dbexceptionsinvalidcheckexpression)
- [`Phalcon\Db\Exceptions\InvalidDialectClass`](#dbexceptionsinvaliddialectclass)
- [`Phalcon\Db\Exceptions\InvalidGenerationExpression`](#dbexceptionsinvalidgenerationexpression)
- [`Phalcon\Db\Exceptions\InvalidGroupByExpression`](#dbexceptionsinvalidgroupbyexpression)
- [`Phalcon\Db\Exceptions\InvalidIndexColumns`](#dbexceptionsinvalidindexcolumns)
- [`Phalcon\Db\Exceptions\InvalidIndexDirections`](#dbexceptionsinvalidindexdirections)
- [`Phalcon\Db\Exceptions\InvalidIndexWhere`](#dbexceptionsinvalidindexwhere)
- [`Phalcon\Db\Exceptions\InvalidListExpression`](#dbexceptionsinvalidlistexpression)
- [`Phalcon\Db\Exceptions\InvalidOrderByExpression`](#dbexceptionsinvalidorderbyexpression)
- [`Phalcon\Db\Exceptions\InvalidSqlExpression`](#dbexceptionsinvalidsqlexpression)
- [`Phalcon\Db\Exceptions\InvalidSqlExpressionType`](#dbexceptionsinvalidsqlexpressiontype)
- [`Phalcon\Db\Exceptions\InvalidUnaryExpression`](#dbexceptionsinvalidunaryexpression)
- [`Phalcon\Db\Exceptions\InvalidWhereConditions`](#dbexceptionsinvalidwhereconditions)
- [`Phalcon\Db\Exceptions\InvalidWkb`](#dbexceptionsinvalidwkb)
- [`Phalcon\Db\Exceptions\MatchedParameterNotFound`](#dbexceptionsmatchedparameternotfound)
- [`Phalcon\Db\Exceptions\MaterializedViewsNotSupported`](#dbexceptionsmaterializedviewsnotsupported)
- [`Phalcon\Db\Exceptions\MissingDefinitionKey`](#dbexceptionsmissingdefinitionkey)
- [`Phalcon\Db\Exceptions\MissingForeignKeyChecks`](#dbexceptionsmissingforeignkeychecks)
- [`Phalcon\Db\Exceptions\MissingSqliteDatabase`](#dbexceptionsmissingsqlitedatabase)
- [`Phalcon\Db\Exceptions\MysqlOnConflictNotSupported`](#dbexceptionsmysqlonconflictnotsupported)
- [`Phalcon\Db\Exceptions\NestedTransactionChangeBlocked`](#dbexceptionsnestedtransactionchangeblocked)
- [`Phalcon\Db\Exceptions\NoActiveTransaction`](#dbexceptionsnoactivetransaction)
- [`Phalcon\Db\Exceptions\ReferencedColumnCountMismatch`](#dbexceptionsreferencedcolumncountmismatch)
- [`Phalcon\Db\Exceptions\ReferencedColumnsRequired`](#dbexceptionsreferencedcolumnsrequired)
- [`Phalcon\Db\Exceptions\ReferencedTableRequired`](#dbexceptionsreferencedtablerequired)
- [`Phalcon\Db\Exceptions\ReturningNotSupported`](#dbexceptionsreturningnotsupported)
- [`Phalcon\Db\Exceptions\ReturningRequiresColumn`](#dbexceptionsreturningrequirescolumn)
- [`Phalcon\Db\Exceptions\SavepointsNotSupported`](#dbexceptionssavepointsnotsupported)
- [`Phalcon\Db\Exceptions\SqliteAlterCheckNotSupported`](#dbexceptionssqlitealterchecknotsupported)
- [`Phalcon\Db\Exceptions\SqliteAlterColumnNotSupported`](#dbexceptionssqlitealtercolumnnotsupported)
- [`Phalcon\Db\Exceptions\SqliteAlterForeignKeyNotSupported`](#dbexceptionssqlitealterforeignkeynotsupported)
- [`Phalcon\Db\Exceptions\SqliteAlterPrimaryKeyNotSupported`](#dbexceptionssqlitealterprimarykeynotsupported)
- [`Phalcon\Db\Exceptions\SqliteDropCheckNotSupported`](#dbexceptionssqlitedropchecknotsupported)
- [`Phalcon\Db\Exceptions\SqliteDropForeignKeyNotSupported`](#dbexceptionssqlitedropforeignkeynotsupported)
- [`Phalcon\Db\Exceptions\SqliteDropPrimaryKeyNotSupported`](#dbexceptionssqlitedropprimarykeynotsupported)
- [`Phalcon\Db\Exceptions\TableMustHaveColumn`](#dbexceptionstablemusthavecolumn)
- [`Phalcon\Db\Exceptions\UnrecognizedDataType`](#dbexceptionsunrecognizeddatatype)
- [`Phalcon\Db\Exceptions\UnsupportedOperator`](#dbexceptionsunsupportedoperator)
- [`Phalcon\Db\Exceptions\UpdateFieldCountMismatch`](#dbexceptionsupdatefieldcountmismatch)
## Db\Exceptions\CannotInsertWithoutData
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\CannotInsertWithoutData`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct( string $table );
```
## Db\Exceptions\CannotPrepareStatement
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\CannotPrepareStatement`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\CheckExpressionRequired
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\CheckExpressionRequired`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ColumnTypeRejectsAutoIncrement
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ColumnTypeRejectsAutoIncrement`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ColumnTypeRejectsScale
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ColumnTypeRejectsScale`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ColumnTypeRequired
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ColumnTypeRequired`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ConflictTargetColumnRequired
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ConflictTargetColumnRequired`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ConflictUpdateColumnRequired
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ConflictUpdateColumnRequired`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ForeignKeyColumnsRequired
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ForeignKeyColumnsRequired`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\GeneratedAutoIncrementConflict
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\GeneratedAutoIncrementConflict`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\GeneratedDefaultConflict
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\GeneratedDefaultConflict`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\IncompleteBindTypes
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\IncompleteBindTypes`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidBindParameter
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidBindParameter`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidCheckExpression
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidCheckExpression`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidDialectClass
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidDialectClass`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct( string $className );
```
## Db\Exceptions\InvalidGenerationExpression
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidGenerationExpression`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidGroupByExpression
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidGroupByExpression`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidIndexColumns
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidIndexColumns`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidIndexDirections
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidIndexDirections`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidIndexWhere
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidIndexWhere`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidListExpression
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidListExpression`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidOrderByExpression
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidOrderByExpression`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidSqlExpression
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidSqlExpression`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidSqlExpressionType
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidSqlExpressionType`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct( string $type );
```
## Db\Exceptions\InvalidUnaryExpression
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidUnaryExpression`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidWhereConditions
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidWhereConditions`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\InvalidWkb
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\InvalidWkb`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct( string $reason );
```
## Db\Exceptions\MatchedParameterNotFound
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\MatchedParameterNotFound`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\MaterializedViewsNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\MaterializedViewsNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\MissingDefinitionKey
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\MissingDefinitionKey`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct( string $key );
```
## Db\Exceptions\MissingForeignKeyChecks
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\MissingForeignKeyChecks`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\MissingSqliteDatabase
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\MissingSqliteDatabase`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\MysqlOnConflictNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\MysqlOnConflictNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\NestedTransactionChangeBlocked
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\NestedTransactionChangeBlocked`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\NoActiveTransaction
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\NoActiveTransaction`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ReferencedColumnCountMismatch
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ReferencedColumnCountMismatch`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ReferencedColumnsRequired
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ReferencedColumnsRequired`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ReferencedTableRequired
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ReferencedTableRequired`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ReturningNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ReturningNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\ReturningRequiresColumn
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\ReturningRequiresColumn`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SavepointsNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SavepointsNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SqliteAlterCheckNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SqliteAlterCheckNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SqliteAlterColumnNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SqliteAlterColumnNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SqliteAlterForeignKeyNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SqliteAlterForeignKeyNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SqliteAlterPrimaryKeyNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SqliteAlterPrimaryKeyNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SqliteDropCheckNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SqliteDropCheckNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SqliteDropForeignKeyNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SqliteDropForeignKeyNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\SqliteDropPrimaryKeyNotSupported
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\SqliteDropPrimaryKeyNotSupported`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\TableMustHaveColumn
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\TableMustHaveColumn`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Exceptions\UnrecognizedDataType
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\UnrecognizedDataType`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct(
string $dialect,
string $column
);
```
## Db\Exceptions\UnsupportedOperator
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\UnsupportedOperator`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct( string $operator );
```
## Db\Exceptions\UpdateFieldCountMismatch
Class
- `\Exception`
- [`Phalcon\Db\Exception`](#dbexception)
- **`Phalcon\Db\Exceptions\UpdateFieldCountMismatch`**
`Phalcon\Db\Exception`
### Method Summary
### Methods
__construct()
```php
public function __construct();
```
## Db\Geometry\AbstractGeometry
Abstract
- **`Phalcon\Db\Geometry\AbstractGeometry`** - implements [`Phalcon\Db\Geometry\GeometryInterface`](#dbgeometrygeometryinterface)
- [`Phalcon\Db\Geometry\GeometryCollection`](#dbgeometrygeometrycollection)
- [`Phalcon\Db\Geometry\LineString`](#dbgeometrylinestring)
- [`Phalcon\Db\Geometry\MultiLineString`](#dbgeometrymultilinestring)
- [`Phalcon\Db\Geometry\MultiPoint`](#dbgeometrymultipoint)
- [`Phalcon\Db\Geometry\MultiPolygon`](#dbgeometrymultipolygon)
- [`Phalcon\Db\Geometry\Point`](#dbgeometrypoint)
- [`Phalcon\Db\Geometry\Polygon`](#dbgeometrypolygon)
### Method Summary
### Properties
### Methods
__toString()
```php
public function __toString(): string;
```
getSrid()
```php
public function getSrid(): int;
```
getType()
```php
abstract public function getType(): int;
```
toWkt()
```php
abstract public function toWkt(): string;
```
## Db\Geometry\GeometryCollection
Class
- [`Phalcon\Db\Geometry\AbstractGeometry`](#dbgeometryabstractgeometry)
- **`Phalcon\Db\Geometry\GeometryCollection`**
`Phalcon\Db\Column`
### Method Summary
### Properties
### Methods
__construct()
```php
public function __construct(
array $geometries,
int $srid = 0
);
```
getGeometries()
```php
public function getGeometries(): array;
```
getType()
```php
public function getType(): int;
```
toWkt()
```php
public function toWkt(): string;
```
## Db\Geometry\GeometryInterface
Interface
Phalcon\Db\Geometry\GeometryInterface
- [`Phalcon\Contracts\Db\Geometry\Geometry`](/5.20/api/phalcon_contracts/#contractsdbgeometrygeometry)
- **`Phalcon\Db\Geometry\GeometryInterface`**
`Phalcon\Contracts\Db\Geometry\Geometry`
## Db\Geometry\LineString
Class
- [`Phalcon\Db\Geometry\AbstractGeometry`](#dbgeometryabstractgeometry)
- **`Phalcon\Db\Geometry\LineString`**
`Phalcon\Db\Column`
### Method Summary
### Properties
### Methods
__construct()
```php
public function __construct(
array $points,
int $srid = 0
);
```
getPoints()
```php
public function getPoints(): array;
```
getType()
```php
public function getType(): int;
```
pointsWkt()
```php
public function pointsWkt(): string;
```
toWkt()
```php
public function toWkt(): string;
```
## Db\Geometry\MultiLineString
Class
- [`Phalcon\Db\Geometry\AbstractGeometry`](#dbgeometryabstractgeometry)
- **`Phalcon\Db\Geometry\MultiLineString`**
`Phalcon\Db\Column`
### Method Summary
### Properties
### Methods
__construct()
```php
public function __construct(
array $lineStrings,
int $srid = 0
);
```
getLineStrings()
```php
public function getLineStrings(): array;
```
getType()
```php
public function getType(): int;
```
toWkt()
```php
public function toWkt(): string;
```
## Db\Geometry\MultiPoint
Class
- [`Phalcon\Db\Geometry\AbstractGeometry`](#dbgeometryabstractgeometry)
- **`Phalcon\Db\Geometry\MultiPoint`**
`Phalcon\Db\Column`
### Method Summary
### Properties
### Methods
__construct()
```php
public function __construct(
array $points,
int $srid = 0
);
```
getPoints()
```php
public function getPoints(): array;
```
getType()
```php
public function getType(): int;
```
toWkt()
```php
public function toWkt(): string;
```
## Db\Geometry\MultiPolygon
Class
- [`Phalcon\Db\Geometry\AbstractGeometry`](#dbgeometryabstractgeometry)
- **`Phalcon\Db\Geometry\MultiPolygon`**
`Phalcon\Db\Column`
### Method Summary
### Properties
### Methods
__construct()
```php
public function __construct(
array $polygons,
int $srid = 0
);
```
getPolygons()
```php
public function getPolygons(): array;
```
getType()
```php
public function getType(): int;
```
toWkt()
```php
public function toWkt(): string;
```
## Db\Geometry\Point
Class
- [`Phalcon\Db\Geometry\AbstractGeometry`](#dbgeometryabstractgeometry)
- **`Phalcon\Db\Geometry\Point`**
`Phalcon\Db\Column`
### Method Summary
### Properties
### Methods
__construct()
```php
public function __construct(
float $x,
float $y,
int $srid = 0
);
```
coordsWkt()
```php
public function coordsWkt(): string;
```
getType()
```php
public function getType(): int;
```
getX()
```php
public function getX(): float;
```
getY()
```php
public function getY(): float;
```
toWkt()
```php
public function toWkt(): string;
```
## Db\Geometry\Polygon
Class
- [`Phalcon\Db\Geometry\AbstractGeometry`](#dbgeometryabstractgeometry)
- **`Phalcon\Db\Geometry\Polygon`**
`Phalcon\Db\Column`
### Method Summary
### Properties
### Methods
__construct()
```php
public function __construct(
array $rings,
int $srid = 0
);
```
getRings()
```php
public function getRings(): array;
```
getType()
```php
public function getType(): int;
```
ringsWkt()
```php
public function ringsWkt(): string;
```
toWkt()
```php
public function toWkt(): string;
```
## Db\Geometry\WkbParser
Class
Decodes a spatial column value into a geometry value object.
Handles MySQL's internal format (4-byte little-endian SRID prefix followed
by standard OGC WKB) and PostGIS EWKB returned as a hex string. 2D only:
any Z/M ordinates are read past and discarded.
- **`Phalcon\Db\Geometry\WkbParser`**
`Phalcon\Db\Exceptions\InvalidWkb`
### Method Summary
### Properties
### Methods
parse()
```php
public function parse( string $raw ): GeometryInterface;
```
readByte()
```php
protected function readByte(): int;
```
readDouble()
```php
protected function readDouble( bool $little ): float;
```
readGeometry()
```php
protected function readGeometry(
int $outerSrid,
int $depth = 0
): GeometryInterface;
```
readPoint()
```php
protected function readPoint(
bool $little,
bool $hasZ,
bool $hasM,
int $srid
): Point;
```
readPointList()
```php
protected function readPointList(
bool $little,
bool $hasZ,
bool $hasM
): array;
```
readRingList()
```php
protected function readRingList(
bool $little,
bool $hasZ,
bool $hasM
): array;
```
readUint32()
```php
protected function readUint32( bool $little ): int;
```
```php
protected function skipExtraOrdinates(
bool $little,
bool $hasZ,
bool $hasM
): void;
```
## Db\Index
Class
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.
The constructor accepts either the legacy positional form (a plain array
of column names) or a definition-array form (an associative array with a
`columns` key); the latter is the path used by features such as
`invisible` (MySQL 8.0+) and is the form that future per-index modifiers
will extend.
```php
// Legacy positional form
$unique = new \Phalcon\Db\Index(
'column_UNIQUE',
[
'column',
],
'UNIQUE'
);
$primary = new \Phalcon\Db\Index(
'PRIMARY',
[
'column',
]
);
// Definition-array form (MySQL 8.0+ invisible index)
$hidden = new \Phalcon\Db\Index(
'idx_hidden',
[
'columns' => ['col1'],
'type' => '',
'invisible' => true,
]
);
$connection->addIndex("co_invoices", null, $unique);
$connection->addIndex("co_invoices", null, $primary);
$connection->addIndex("co_invoices", null, $hidden);
```
- **`Phalcon\Db\Index`** - implements [`Phalcon\Db\IndexInterface`](#dbindexinterface)
`Phalcon\Db\Exceptions\InvalidIndexColumns` · `Phalcon\Db\Exceptions\InvalidIndexDirections` · `Phalcon\Db\Exceptions\InvalidIndexWhere`
### Method Summary
Phalcon\Db\Index constructor.
Index columns
Returns the per-column sort directions array (`ASC` / `DESC`).
Index name
Index type
Returns the partial-index `WHERE` predicate, or an empty string when
Whether the index is built `CONCURRENTLY` (PostgreSQL only). MySQL
Whether the index is declared `INVISIBLE` (MySQL 8.0+). Invisible
### Properties
Index columns
Whether to build the index without taking a strong lock that blocks
writes - emits `CONCURRENTLY` between `INDEX` and the index name on
PostgreSQL (`CREATE INDEX CONCURRENTLY name ON ...`). MySQL and
SQLite have no equivalent and ignore the flag.
Per-column sort directions (`ASC` / `DESC`). Empty array means
"emit no per-column direction" - preserves the legacy plain
`(col1, col2)` rendering. When populated, entries shorter than
the columns list default to `ASC` for the missing positions.
Whether the index is declared `INVISIBLE` (MySQL 8.0+). Invisible
indexes are ignored by the optimizer - useful for testing what
happens when an index is removed before actually dropping it.
PostgreSQL and SQLite have no equivalent and ignore the flag.
Index name
Index type
Optional partial-index `WHERE` predicate. Supported by PostgreSQL and
SQLite (`CREATE INDEX ... WHERE `); MySQL has no partial-index
concept and its dialect ignores this value. Empty string means no
predicate.
### Methods
__construct()
```php
public function __construct(
string $name,
array $columnsOrDefinition,
string $type = ""
);
```
Phalcon\Db\Index constructor.
Accepts either the legacy positional form `(name, columns, type)` or a
definition-array form `(name, ["columns" => [...], "type" => "...",
"invisible" => true, ...])`. Detection is based on the presence of a
`columns` key in the second argument; when present, the third
positional `type` argument is ignored in favor of the definition.
getColumns()
```php
public function getColumns(): array;
```
Index columns
getDirections()
```php
public function getDirections(): array;
```
Returns the per-column sort directions array (`ASC` / `DESC`).
Empty array means the index was declared without explicit per-column
directions and dialects emit the columns plainly. When populated,
entries are aligned with `getColumns()`; missing trailing positions
default to `ASC` at emission time.
getName()
```php
public function getName(): string;
```
Index name
getType()
```php
public function getType(): string;
```
Index type
getWhere()
```php
public function getWhere(): string;
```
Returns the partial-index `WHERE` predicate, or an empty string when
the index has none. Supported by PostgreSQL and SQLite; ignored by
the MySQL dialect (MySQL has no partial-index feature).
isConcurrent()
```php
public function isConcurrent(): bool;
```
Whether the index is built `CONCURRENTLY` (PostgreSQL only). MySQL
and SQLite have no equivalent and ignore the flag.
isInvisible()
```php
public function isInvisible(): bool;
```
Whether the index is declared `INVISIBLE` (MySQL 8.0+). Invisible
indexes are ignored by the optimizer but still maintained, so they
can be flipped back to visible without a rebuild.
## Db\IndexInterface
Interface
Phalcon\Db\IndexInterface
- [`Phalcon\Contracts\Db\Index`](/5.20/api/phalcon_contracts/#contractsdbindex)
- **`Phalcon\Db\IndexInterface`**
`Phalcon\Contracts\Db\Index`
## Db\Profiler
Class
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.
```php
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";
```
- **`Phalcon\Db\Profiler`**
`Phalcon\Db\Profiler\Item` · `Phalcon\Db\Traits\ElapsedTimeTrait`
### Method Summary
Returns the last profile executed in the profiler
Returns the configured maximum number of retained profiles
Returns the total number of SQL statements processed
Returns all the processed profiles
Returns the total time in nanoseconds spent by the profiles
Resets the profiler, cleaning up all the profiles
Sets the maximum number of retained profiles. 0 disables the cap
Starts the profile of a SQL sentence
Stops the active profile
### Properties
Active Item
All the Items in the active profile
Maximum number of profiles to retain. 0 (default) keeps the
original unbounded behavior; a positive value drops the oldest
profile FIFO before a new one is appended.
Total time spent by all profiles to complete in nanoseconds
### Methods
getLastProfile()
```php
public function getLastProfile(): Item;
```
Returns the last profile executed in the profiler
getMaxProfiles()
```php
public function getMaxProfiles(): int;
```
Returns the configured maximum number of retained profiles
(0 = unlimited)
getNumberTotalStatements()
```php
public function getNumberTotalStatements(): int;
```
Returns the total number of SQL statements processed
getProfiles()
```php
public function getProfiles(): Item[];
```
Returns all the processed profiles
getTotalElapsedNanoseconds()
```php
public function getTotalElapsedNanoseconds(): float;
```
Returns the total time in nanoseconds spent by the profiles
reset()
```php
public function reset(): static;
```
Resets the profiler, cleaning up all the profiles
setMaxProfiles()
```php
public function setMaxProfiles( int $maxProfiles ): static;
```
Sets the maximum number of retained profiles. 0 disables the cap
(the default; preserves the original unbounded behavior).
startProfile()
```php
public function startProfile(
string $sqlStatement,
array $sqlVariables = [],
array $sqlBindTypes = []
): static;
```
Starts the profile of a SQL sentence
stopProfile()
```php
public function stopProfile(): static;
```
Stops the active profile
## Db\Profiler\Item
Class
This class identifies each profile in a Phalcon\Db\Profiler
- **`Phalcon\Db\Profiler\Item`**
`Phalcon\Db\Traits\ElapsedTimeTrait`
### Method Summary
Return the timestamp when the profile ended
Return the timestamp when the profile started
Return the SQL bind types related to the profile
Return the SQL statement related to the profile
Return the SQL variables related to the profile
Returns the total time in nanoseconds spent by the profile
Return the timestamp when the profile ended
Return the timestamp when the profile started
Return the SQL bind types related to the profile
Return the SQL statement related to the profile
Return the SQL variables related to the profile
### Properties
Timestamp when the profile ended
Timestamp when the profile started
SQL bind types related to the profile
SQL statement related to the profile
SQL variables related to the profile
### Methods
getFinalTime()
```php
public function getFinalTime(): float;
```
Return the timestamp when the profile ended
getInitialTime()
```php
public function getInitialTime(): float;
```
Return the timestamp when the profile started
getSqlBindTypes()
```php
public function getSqlBindTypes(): array;
```
Return the SQL bind types related to the profile
getSqlStatement()
```php
public function getSqlStatement(): string;
```
Return the SQL statement related to the profile
getSqlVariables()
```php
public function getSqlVariables(): array;
```
Return the SQL variables related to the profile
getTotalElapsedNanoseconds()
```php
public function getTotalElapsedNanoseconds(): float;
```
Returns the total time in nanoseconds spent by the profile
setFinalTime()
```php
public function setFinalTime( float $finalTime ): static;
```
Return the timestamp when the profile ended
setInitialTime()
```php
public function setInitialTime( float $initialTime ): static;
```
Return the timestamp when the profile started
setSqlBindTypes()
```php
public function setSqlBindTypes( array $sqlBindTypes ): static;
```
Return the SQL bind types related to the profile
setSqlStatement()
```php
public function setSqlStatement( string $sqlStatement ): static;
```
Return the SQL statement related to the profile
setSqlVariables()
```php
public function setSqlVariables( array $sqlVariables ): static;
```
Return the SQL variables related to the profile
## Db\RawValue
Class
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.
```php
$subscriber = new Subscribers();
$subscriber->email = "andres@phalcon.io";
$subscriber->createdAt = new \Phalcon\Db\RawValue("now()");
$subscriber->save();
```
WARNING: a RawValue is emitted into the SQL verbatim, with no quoting or
escaping - including a RawValue passed as a query bind-parameter value, which
is spliced into the compiled SQL string rather than bound. Never wrap
request-derived or otherwise untrusted data in a RawValue; use ordinary bind
parameters for those. RawValue is only for developer-authored SQL fragments
(for example database functions such as now()).
- **`Phalcon\Db\RawValue`**
### Method Summary
Phalcon\Db\RawValue constructor
### Properties
Raw value without quoting or formatting
### Methods
__construct()
```php
public function __construct( mixed $value );
```
Phalcon\Db\RawValue constructor
__toString()
```php
public function __toString(): string;
```
getValue()
```php
public function getValue(): string;
```
## Db\Reference
Class
Allows to define reference constraints on tables
```php
$reference = new \Phalcon\Db\Reference(
"field_fk",
[
"referencedSchema" => "invoicing",
"referencedTable" => "products",
"columns" => [
"producttype",
"product_code",
],
"referencedColumns" => [
"type",
"code",
],
]
);
```
- **`Phalcon\Db\Reference`** - implements [`Phalcon\Db\ReferenceInterface`](#dbreferenceinterface)
`Phalcon\Db\Exceptions\ForeignKeyColumnsRequired` · `Phalcon\Db\Exceptions\ReferencedColumnCountMismatch` · `Phalcon\Db\Exceptions\ReferencedColumnsRequired` · `Phalcon\Db\Exceptions\ReferencedTableRequired`
### Method Summary
Phalcon\Db\Reference constructor
Local reference columns
Constraint name
ON DELETE
ON UPDATE
Referenced Columns
Referenced Schema
Referenced Table
Schema name
### Properties
Local reference columns
Constraint name
ON DELETE
ON UPDATE
Referenced Columns
Referenced Schema
Referenced Table
Schema name
### Methods
__construct()
```php
public function __construct(
string $name,
array $definition
);
```
Phalcon\Db\Reference constructor
getColumns()
```php
public function getColumns(): array;
```
Local reference columns
getName()
```php
public function getName(): string;
```
Constraint name
getOnDelete()
```php
public function getOnDelete(): string|null;
```
ON DELETE
getOnUpdate()
```php
public function getOnUpdate(): string|null;
```
ON UPDATE
getReferencedColumns()
```php
public function getReferencedColumns(): array;
```
Referenced Columns
getReferencedSchema()
```php
public function getReferencedSchema(): string|null;
```
Referenced Schema
getReferencedTable()
```php
public function getReferencedTable(): string;
```
Referenced Table
getSchemaName()
```php
public function getSchemaName(): string|null;
```
Schema name
## Db\ReferenceInterface
Interface
Phalcon\Db\ReferenceInterface
- [`Phalcon\Contracts\Db\Reference`](/5.20/api/phalcon_contracts/#contractsdbreference)
- **`Phalcon\Db\ReferenceInterface`**
`Phalcon\Contracts\Db\Reference`
## Db\ResultInterface
Interface
Phalcon\Db\ResultInterface
- [`Phalcon\Contracts\Db\Result`](/5.20/api/phalcon_contracts/#contractsdbresult)
- **`Phalcon\Db\ResultInterface`**
`Phalcon\Contracts\Db\Result`
## Db\Result\PdoResult
Class
Encapsulates the resultset internals
```php
$result = $connection->query("SELECT * FROM co_invoices ORDER BY inv_title");
$result->setFetchMode(
\Phalcon\Db\Enum::FETCH_NUM
);
while ($invoice = $result->fetchArray()) {
print_r($invoice);
}
```
- **`Phalcon\Db\Result\PdoResult`** - implements [`Phalcon\Db\ResultInterface`](#dbresultinterface)
`Phalcon\Db\Adapter\AdapterInterface` · `Phalcon\Db\Enum` · `Phalcon\Db\ResultInterface`
### Method Summary
Phalcon\Db\Result\Pdo constructor
Moves internal resultset cursor to another position letting us to fetch a
Allows to execute the statement again. Some database systems don't
Fetches an array/object of strings that corresponds to the fetched row,
Returns an array of arrays containing all the records in the result
Returns an array of strings that corresponds to the fetched row, or FALSE
Gets the internal PDO result object
Gets number of rows returned by a resultset
Changes the fetching mode affecting Phalcon\Db\Result\Pdo::fetch()
### Properties
Active fetch mode
Internal resultset
### Methods
__construct()
```php
public function __construct(
AdapterInterface $connection,
\PDOStatement $result,
mixed $sqlStatement = null,
mixed $bindParams = null,
mixed $bindTypes = null
);
```
Phalcon\Db\Result\Pdo constructor
dataSeek()
```php
public function dataSeek( int $number ): void;
```
Moves internal resultset cursor to another position letting us to fetch a
certain row
```php
$result = $connection->query(
"SELECT * FROM co_invoices ORDER BY inv_title"
);
// Move to third row on result
$result->dataSeek(2);
// Fetch third row
$row = $result->fetch();
```
execute()
```php
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
fetch()
```php
public function fetch(
int|null $fetchStyle = null,
int $cursorOrientation = Enum::FETCH_ORI_NEXT,
int $cursorOffset = 0
);
```
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()`
```php
$result = $connection->query("SELECT * FROM co_invoices ORDER BY inv_title");
$result->setFetchMode(
\Phalcon\Enum::FETCH_OBJ
);
while ($invoice = $result->fetch()) {
echo $invoice->inv_title;
}
```
fetchAll()
```php
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()`
```php
$result = $connection->query(
"SELECT * FROM co_invoices ORDER BY inv_title"
);
$invoices = $result->fetchAll();
```
fetchArray()
```php
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()`
```php
$result = $connection->query("SELECT * FROM co_invoices ORDER BY inv_title");
$result->setFetchMode(
\Phalcon\Enum::FETCH_NUM
);
while ($invoice = result->fetchArray()) {
print_r($invoice);
}
```
getInternalResult()
```php
public function getInternalResult(): \PDOStatement;
```
Gets the internal PDO result object
numRows()
```php
public function numRows(): int;
```
Gets number of rows returned by a resultset
```php
$result = $connection->query(
"SELECT * FROM co_invoices ORDER BY inv_title"
);
echo "There are ", $result->numRows(), " rows in the resultset";
```
setFetchMode()
```php
public function setFetchMode(
int $fetchMode,
mixed $colNoOrClassNameOrObject = null,
mixed $ctorargs = null
): bool;
```
Changes the fetching mode affecting Phalcon\Db\Result\Pdo::fetch()
```php
// 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
);
```
## Db\Traits\ElapsedTimeTrait
Trait
Derives elapsed milliseconds and seconds from the nanosecond total that the
using class exposes through getTotalElapsedNanoseconds().
- **`Phalcon\Db\Traits\ElapsedTimeTrait`**
[`Phalcon\Db\Profiler`](#dbprofiler) · [`Phalcon\Db\Profiler\Item`](#dbprofileritem)
### Method Summary
Returns the total time in milliseconds spent by the profiles
Returns the total time in nanoseconds spent by the profiles. Implemented
Returns the total time in seconds spent by the profiles
### Methods
getTotalElapsedMilliseconds()
```php
public function getTotalElapsedMilliseconds(): float;
```
Returns the total time in milliseconds spent by the profiles
getTotalElapsedNanoseconds()
```php
abstract public function getTotalElapsedNanoseconds(): float;
```
Returns the total time in nanoseconds spent by the profiles. Implemented
by the using class.
getTotalElapsedSeconds()
```php
public function getTotalElapsedSeconds(): float;
```
Returns the total time in seconds spent by the profiles
Source: https://docs.phalcon.io/5.20/api/phalcon_db/index.mdx