Phalcon Db

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;
}

public function __construct( array $descriptor );

public function addCheck(
    string $tableName,
    string $schemaName,
    CheckInterface $check
): bool;

public function addColumn(
    string $tableName,
    string $schemaName,
    ColumnInterface $column
): bool;

public function addForeignKey(
    string $tableName,
    string $schemaName,
    ReferenceInterface $reference
): bool;

public function addIndex(
    string $tableName,
    string $schemaName,
    IndexInterface $index
): bool;

public function addPrimaryKey(
    string $tableName,
    string $schemaName,
    IndexInterface $index
): bool;

public function createMaterializedView(
    string $viewName,
    array $definition,
    string $schemaName = null
): bool;

public function createSavepoint( string $name ): bool;

public function createTable(
    string $tableName,
    string $schemaName,
    array $definition
): bool;

public function createView(
    string $viewName,
    array $definition,
    string $schemaName = null
): bool;

public function delete(
    mixed $table,
    string $whereCondition = null,
    array $placeholders = [],
    array $dataTypes = []
): bool;

// Deleting existing invoice
$success = $connection->delete(
    "co_invoices",
    "inv_id = 101"
);

// Next SQL sentence is generated
DELETE FROM `co_invoices` WHERE `inv_id` = 101

public function describeIndexes(
    string $table,
    string $schema = null
): IndexInterface[];

print_r(
    $connection->describeIndexes("co_orders_x_products")
);

public function describeReferences(
    string $table,
    string $schema = null
): ReferenceInterface[];

print_r(
    $connection->describeReferences("co_orders_x_products")
);

public function dropCheck(
    string $tableName,
    string $schemaName,
    string $checkName
): bool;

public function dropColumn(
    string $tableName,
    string $schemaName,
    string $columnName
): bool;

public function dropForeignKey(
    string $tableName,
    string $schemaName,
    string $referenceName
): bool;

public function dropIndex(
    string $tableName,
    string $schemaName,
    mixed $indexName
): bool;

public function dropMaterializedView(
    string $viewName,
    string $schemaName = null,
    bool $ifExists = true
): bool;

public function dropPrimaryKey(
    string $tableName,
    string $schemaName
): bool;

public function dropTable(
    string $tableName,
    string $schemaName = null,
    bool $ifExists = true
): bool;

public function dropView(
    string $viewName,
    string $schemaName = null,
    bool $ifExists = true
): bool;

public function escapeIdentifier( mixed $identifier ): string;

$escapedTable = $connection->escapeIdentifier(
    "co_invoices"
);

$escapedTable = $connection->escapeIdentifier(
    [
        "store",
        "co_invoices",
    ]
);

public function fetchAll(
    string $sqlQuery,
    int $fetchMode = Enum::FETCH_ASSOC,
    array $bindParams = [],
    array $bindTypes = []
): array;

// 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);
}

public function fetchColumn(
    string $sqlQuery,
    array $placeholders = [],
    mixed $column = 0
): string|bool;

// 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);

public function fetchOne(
    string $sqlQuery,
    mixed $fetchMode = Enum::FETCH_ASSOC,
    array $bindParams = [],
    array $bindTypes = []
): array;

// 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);

public function forUpdate(
    string $sqlQuery,
    string $modifier = ""
): string;

public function getColumnDefinition( ColumnInterface $column ): string;

public function getColumnList( mixed $columnList ): string;

public function getConnectionId(): int;

public function getDefaultIdValue(): RawValue;

// 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",
    ]
);

public function getDefaultValue(): RawValue;

// 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",
    ]
);

public function getDescriptor(): array;

public function getDialect(): DialectInterface;

public function getDialectType(): string;

public function getEventsManager(): ManagerInterface|null;

public function getNestedTransactionSavepointName(): string;

public function getRealSQLStatement(): string;

public function getSQLBindTypes(): array;

public function getSQLStatement(): string;

public function getSQLVariables(): array;

public function getType(): string;

public function insert(
    string $table,
    array $values,
    mixed $fields = null,
    mixed $dataTypes = null
): bool;

// 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);

public function insertAsDict(
    string $table,
    mixed $data,
    mixed $dataTypes = null
): bool;

// 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);

public function isNestedTransactionsWithSavepoints(): bool;

public function limit(
    string $sqlQuery,
    mixed $number
): string;

echo $connection->limit("SELECT * FROM co_invoices", 5);

public function listTables( string $schemaName = null ): array;

print_r(
    $connection->listTables("blog")
);

public function listViews( string $schemaName = null ): array;

print_r(
    $connection->listViews("blog")
);

public function modifyColumn(
    string $tableName,
    string $schemaName,
    ColumnInterface $column,
    ColumnInterface $currentColumn = null
): bool;

public function onConflictUpdate(
    string $sqlQuery,
    array $conflictColumns,
    array $updateColumns
): string;

public function refreshMaterializedView(
    string $viewName,
    string $schemaName = null,
    bool $concurrent = false
): bool;

public function releaseSavepoint( string $name ): bool;

public function returning(
    string $sqlQuery,
    array $columns
): string;

public function rollbackSavepoint( string $name ): bool;

public function setDialect( DialectInterface $dialect );

public function setEventsManager( ManagerInterface $eventsManager ): void;

public function setNestedTransactionsWithSavepoints( bool $nestedTransactionsWithSavepoints ): AdapterInterface;

public static function setup( array $options ): void;

public function sharedLock(
    string $sqlQuery,
    string $modifier = ""
): string;

public function supportSequences(): bool;

public function supportsDefaultValue(): bool;

public function tableExists(
    string $tableName,
    string $schemaName = null
): bool;

var_dump(
    $connection->tableExists("blog", "posts")
);

public function tableOptions(
    string $tableName,
    string $schemaName = null
): array;

print_r(
    $connection->tableOptions("co_invoices")
);

public function update(
    string $table,
    mixed $fields,
    mixed $values,
    mixed $whereCondition = null,
    mixed $dataTypes = null
): bool;

// 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
    ]
);

public function updateAsDict(
    string $table,
    mixed $data,
    mixed $whereCondition = null,
    mixed $dataTypes = null
): bool;

// 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

public function useExplicitIdValue(): bool;

public function viewExists(
    string $viewName,
    string $schemaName = null
): bool;

var_dump(
    $connection->viewExists("active_users", "posts")
);

public function __construct( array $services = [] );

public function load( mixed $config ): AdapterInterface;

public function newInstance(
    string $name,
    array $options = []
): AdapterInterface;

protected function getExceptionClass(): string;

protected function getServices(): array;

use Phalcon\Db\Adapter\Pdo\Mysql;

$config = [
    "host"     => "localhost",
    "dbname"   => "blog",
    "port"     => 3306,
    "username" => "sigma",
    "password" => "secret",
];

$connection = new Mysql($config);

public function __construct( array $descriptor );

public function affectedRows(): int;

$connection->execute(
    "DELETE FROM co_invoices"
);

echo $connection->affectedRows(), " were deleted";

public function begin( bool $nesting = true ): bool;

public function close(): void;

public function commit( bool $nesting = true ): bool;

public function connect( array $descriptor = [] ): void;

use Phalcon\Db\Adapter\Pdo\Mysql;

// Make a connection
$connection = new Mysql(
    [
        "host"     => "localhost",
        "username" => "sigma",
        "password" => "secret",
        "dbname"   => "blog",
        "port"     => 3306,
    ]
);

// Reconnect
$connection->connect();

public function convertBoundParams(
    string $sql,
    array $params = []
): array;

print_r(
    $connection->convertBoundParams(
        "SELECT * FROM co_invoices WHERE inv_title = :inv_title:",
        [
            "Test Invoice",
        ]
    )
);

public function ensureConnection(): void;

public function escapeString( string $str ): string;

$escapedStr = $connection->escapeString("some dangerous value");

public function execute(
    string $sqlStatement,
    array $bindParams = [],
    array $bindTypes = []
): bool;

// Inserting data
$success = $connection->execute(
    "INSERT INTO co_invoices VALUES (1, 'Test Invoice')"
);

$success = $connection->execute(
    "INSERT INTO co_invoices VALUES (?, ?)",
    [
        1,
        "Test Invoice",
    ]
);

public function executePrepared(
    \PDOStatement $statement,
    array $placeholders,
    array $dataTypes = []
): \PDOStatement;

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,
    ]
);

public function getAutoReconnect(): bool;

public function getErrorInfo(): array;

public function getInternalHandler(): mixed;

public function getTransactionLevel(): int;

public function isUnderTransaction(): bool;

$connection->begin();

// true
var_dump(
    $connection->isUnderTransaction()
);

public function lastInsertId( string $name = null ): string|bool;

// Inserting a new invoice
$success = $connection->insert(
    "co_invoices",
    [
        "Test Invoice",
        100,
    ],
    [
        "inv_title",
        "inv_total",
    ]
);

// Getting the generated id
$id = $connection->lastInsertId();

public function ping(): bool;

public function prepare( string $sqlStatement ): \PDOStatement;

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,
    ]
);

public function query(
    string $sqlStatement,
    array $bindParams = [],
    array $bindTypes = []
): ResultInterface|bool;

// 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,
    ]
);

public function rollback( bool $nesting = true ): bool;

public function setAutoReconnect( bool $autoReconnect ): static;

abstract protected function getDsnDefaults(): array;

protected function isConnectionError( \Throwable $exception ): bool;

protected function prepareRealSql(
    string $statement,
    array $parameters
): void;

use Phalcon\Db\Adapter\Pdo\Mysql;

$config = [
    "host"     => "localhost",
    "dbname"   => "blog",
    "port"     => 3306,
    "username" => "sigma",
    "password" => "secret",
];

$connection = new Mysql($config);

public function addForeignKey(
    string $tableName,
    string $schemaName,
    ReferenceInterface $reference
): bool;

public function describeColumns(
    string $table,
    string $schema = null
): ColumnInterface[];

print_r(
    $connection->describeColumns("posts")
);

public function describeIndexes(
    string $table,
    string $schema = null
): IndexInterface[];

print_r(
    $connection->describeIndexes("co_orders_x_products")
);

public function describeReferences(
    string $table,
    string $schema = null
): ReferenceInterface[];

print_r(
    $connection->describeReferences("co_orders_x_products")
);

protected function getDsnDefaults(): array;

protected function isConnectionError( \Throwable $exception ): bool;

use Phalcon\Db\Adapter\Pdo\Postgresql;

$config = [
    "host"     => "localhost",
    "dbname"   => "blog",
    "port"     => 5432,
    "username" => "postgres",
    "password" => "secret",
];

$connection = new Postgresql($config);

public function __construct( array $descriptor );

public function connect( array $descriptor = [] ): void;

public function createTable(
    string $tableName,
    string $schemaName,
    array $definition
): bool;

public function describeColumns(
    string $table,
    string $schema = null
): ColumnInterface[];

print_r(
    $connection->describeColumns("posts")
);

public function describeReferences(
    string $table,
    string $schema = null
): ReferenceInterface[];

print_r(
    $connection->describeReferences("co_orders_x_products")
);

public function getDefaultIdValue(): RawValue;

// 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",
    ]
);

public function modifyColumn(
    string $tableName,
    string $schemaName,
    ColumnInterface $column,
    ColumnInterface $currentColumn = null
): bool;

public function supportSequences(): bool;

public function useExplicitIdValue(): bool;

protected function getDsnDefaults(): array;

protected function isConnectionError( \Throwable $exception ): bool;

use Phalcon\Db\Adapter\Pdo\Sqlite;

$connection = new Sqlite(
    [
        "dbname" => "/tmp/test.sqlite",
    ]
);

public function __construct( array $descriptor );

public function connect( array $descriptor = [] ): void;

public function describeColumns(
    string $table,
    string $schema = null
): ColumnInterface[];

print_r(
    $connection->describeColumns("posts")
);

public function describeIndexes(
    string $table,
    string $schema = null
): IndexInterface[];

print_r(
    $connection->describeIndexes("co_orders_x_products")
);

public function describeReferences(
    string $table,
    string $schema = null
): ReferenceInterface[];

public function getDefaultValue(): RawValue;

// 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",
    ]
);

public function supportsDefaultValue(): bool;

public function useExplicitIdValue(): bool;

protected function getDsnDefaults(): array;

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);

public function __construct(
    string $name,
    array $definition
);

public function getExpression(): string;

public function getName(): string;

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);

public function __construct(
    string $name,
    array $definition
);

public function getAfterPosition(): string|null;

public function getBindType(): int;

public function getComment(): string|null;

public function getDefault(): mixed;

public function getGenerationExpression(): string|null;

public function getName(): string;

public function getScale(): int;

public function getSize(): int|string;

public function getType(): int|string;

public function getTypeReference(): int;

public function getTypeValues(): array|string;

public function hasDefault(): bool;

public function isArray(): bool;

public function isAutoIncrement(): bool;

public function isFirst(): bool;

public function isGenerated(): bool;

public function isGenerationStored(): bool;

public function isInvisible(): bool;

public function isNotNull(): bool;

public function isNumeric(): bool;

public function isPrimary(): bool;

public function isUnsigned(): bool;

public function createMaterializedView(
    string $viewName,
    array $definition,
    string $schemaName = null
): string;

public function createSavepoint( string $name ): string;

public function dropMaterializedView(
    string $viewName,
    string $schemaName = null,
    bool $ifExists = true
): string;

final public function escape(
    string $str,
    string $escapeChar = null
): string;

final public function escapeSchema(
    string $str,
    string $escapeChar = null
): string;

public function forUpdate(
    string $sqlQuery,
    string $modifier = ""
): string;

$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