PHPackages                             zethika/virtual-sql - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. zethika/virtual-sql

ActiveLibrary[Database & ORM](/categories/database)

zethika/virtual-sql
===================

A class-based abstraction layer for MySQL, designed to help with programmatically writing MySQL queries.

1.8.2(6mo ago)31.5k↓85.7%MITPHPPHP >=8.0

Since Apr 30Pushed 6mo ago1 watchersCompare

[ Source](https://github.com/zethika/virtual-sql)[ Packagist](https://packagist.org/packages/zethika/virtual-sql)[ Docs](https://github.com/zethika/virtual-sql)[ RSS](/packages/zethika-virtual-sql/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (10)DependenciesVersions (42)Used By (0)

Virtual SQL
===========

[](#virtual-sql)

Virtual SQL is a class-based abstraction layer for MySQL, designed to help with programmatically writing MySQL queries.
It allows working with query structures via a singular VirtualSqlQuery class, abstracting the actual MySQL syntax away from consideration.

Installation
------------

[](#installation)

```
composer require zethika/virtual-sql

```

Table definitions
-----------------

[](#table-definitions)

Virtual SQL provides a class (VirtualSqlTable) for defining a database table and its columns, which needs to be used when working with queries towards those tables.
These table definitions can be generated at runtime, by providing a PDO instance to the VirtualSqlTableDefinitionGenerator Singleton.
It can then extract the CREATE TABLE definition directly from the database and provide an instance representing a given table.
The CREATE TABLE statements extracted from the database is kept in memory, to minimize excess SQL queries from repeated calls to the same table.

```
use VirtualSql\Generator\VirtualSqlTableDefinitionGenerator;

try {
    $pdo = new PDO('mysql:host='.$_ENV['DB_HOST'].';dbname='.$_ENV['DB_NAME'], $_ENV['DB_USER'], $_ENV['DB_PASS']);
    $pdo->exec("SET NAMES utf8");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    trigger_error($e->getMessage(),E_USER_ERROR);
}

$generator = VirtualSqlTableDefinitionGenerator::getInstance();
$generator->init($pdo);

// A VirtualSql\Definition\VirtualSqlTable instance
$tableDefinition = $generator->generateTableDefinition('table_name');

```

Queries
-------

[](#queries)

Queries are built using child classes of VirtualSqlQuery and is the programmatic interface, with which the runtime builds the query.
They provide a series of helper functions, depending on which type of query that's being built, for easier manipulation of the query parts.

Query instances can be generated by providing the VirtualSqlQuery::factory method a type constant, and a VirtualSqlTable instance for the base table.
Supported types are TYPE\_SELECT, TYPE\_INSERT, TYPE\_UPDATE and TYPE\_DELETE

```
use VirtualSql\Query\VirtualSqlQuery;
$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_SELECT,$table);

```

### Query config parameter

[](#query-config-parameter)

A third parameter can be provided to the factory method, an associative array $config.
This can be used to set most values in a query at instantiation.

The possible keys for it depends on the specific Query type. All values set via the $config parameter can also be manipulated via method calls after instantiation.

#### SELECT

[](#select)

```
$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_SELECT,$table, [
    // Array of VirtualSqlColumn instances, describing which columns to select
    'selects' => [
        $table->getColumn('some_column'),
        $table->getColumn('another_column')
    ],
    // Array of VirtualSqlJoin instances, describing which joins to perform
    // It's recommended using the query helper methods instead
    'joins' => [
        new VirtualSqlJoin($fromColumn, $toColumn)
    ],
    // VirtualSqlConditionSet instance representing the base condition set
    'where' => new VirtualSqlConditionSet(VirtualSqlConstant::OPERATOR_AND, $conditions),
    // int|null representing the LIMIT parameter
    'limit' => 10,
    // int|null representing the OFFSET parameter
    'offset' => 10
]);

```

#### INSERT

[](#insert)

```
$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_INSERT,$table, [
    // Array of VirtualSqlColumn instances, describing which columns to insert into
    'columns' => $table->getColumns(),
    // Array of associative arrays describing the value sets being inserted
    'valueSets' => [
        [
            'column_1' => 'some_value',
            'column_2' => 'some_value,
        ],
        [
            'column_1' => 'some_value',
            'column_2' => 'some_value,
        ]
    ],
    // Array of VirtualSqlColumn instances, describing which columns should be updated in the ON DUPLICATE KEY UPDATE part
    // If none are provided, this query part will not be generated
    'onDuplicateUpdateColumns' => [
        $uuidTable->getColumn('blog_id'),
        $uuidTable->getColumn('resource_id'),
    ]
]);

```

#### UPDATE

[](#update)

```
$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_UPDATE,$table, [
    // Array of VirtualSqlColumn instances, describing which columns to update
    'columns' => $table->getColumns(),
    // Associative array describing the values being updated
    'values' => [
        'column_1' => 'some_value',
        'column_2' => 'some_value,
    ],
    // Array of VirtualSqlJoin instances, describing which joins to perform
    // It's recommended using the query helper methods instead
    'joins' => [
        new VirtualSqlJoin($fromColumn, $toColumn)
    ],
    // VirtualSqlConditionSet instance representing the base condition set
    'where' => new VirtualSqlConditionSet(VirtualSqlConstant::OPERATOR_AND, $conditions),
]);

```

#### DELETE

[](#delete)

```
$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_DELETE,$table, [
    // Array of VirtualSqlJoin instances, describing which joins to perform
    // It's recommended using the query helper methods instead
    'joins' => [
        new VirtualSqlJoin($fromColumn, $toColumn)
    ],
    // VirtualSqlConditionSet instance representing the base condition set
    'where' => new VirtualSqlConditionSet(VirtualSqlConstant::OPERATOR_AND, $conditions),
    // int|null representing the LIMIT parameter
    'limit' => 10,
]);

```

Full example
------------

[](#full-example)

```