README

Anax Database Query Builder module as an extension to anax/database to enable querying the datase using methods instead of SQL.

This module is used to implement the module Database Active Record anax\database-active-record.

The module is tested using MySQL and SQLite.

Table of content

Install
Development
Class, interface, trait
Exceptions
DI service
Access as framework service
Basic usage
Dependency
License

Install

You can install the module from anax/database-query-builder on Packagist using composer.

composer require anax/database-query-builder

You can then copy the default configuration files as a start.

# In the root of your Anax installation
rsync -av vendor/anax/database-query-builder/config .

Development

To work as a developer you clone the repo and install the local environment through make. Then you can run the unit tests.

make install
make test

Class, interface, trait

The following classes, interfaces and traits exists.

The following parts are related to the feature of a SQL query builder.

Class, interface, trait	Description
`Anax\Database\QueryBuilderTrait`	A trait implementing SQL query builder, based upon `Anax\Database\Database`.
`Anax\Database\DatabaseQueryBuilder`	A database class using the SQL query builder trait (used by the Active Record module) and extending the database class.

Exceptions

All exceptions are in the namespace Anax\DatabaseQueryBuilder\Exception\. The following exceptions exists and may be thrown.

Exception	Description
`BuildException`	When failing to build a SQL query.

DI service

The database query builder is created as a framework service within $di. The following is a sample on how the database query builder service is created through config/di/dbqb.php.

/**
 * Configuration file for database query builder service.
 */
return [
    // Services to add to the container.
    "services" => [
        "dbqb" => [
            "shared" => true,
            "callback" => function () {
                $obj = new \Anax\DatabaseQueryBuilder\DatabaseQueryBuilder();

                // Load the configuration files
                $cfg = $this->get("configuration");
                $config = $cfg->load("database");

                // Set the database configuration
                $connection = $config["config"] ?? [];
                $db->setOptions($connection);
                $db->setDefaultsFromConfiguration();

                return $db;
            }
        ],
    ],
];

Access as framework service

You can access the module as a framework service and use it as an ordinary database service.

$sql = "SELECT * FROM movie;";

$db = $di->get("dbqb");
$db->connect();
$res = $db->executeFetchAll($sql);

This is since the class \Anax\DatabaseQueryBuilder\DatabaseQueryBuilder extends the database class \Anax\Database\Database.

Basic usage

This is the basic usage of the query builder.

You start by creating a database object from the query builder class and connect to the database.

$this->db = new DatabaseQueryBuilder([
    "dsn" => "sqlite::memory:",
]);
$this->db->setDefaultsFromConfiguration();
$this->db->connect();

This is more or less the same as retrieving the class from the $di container.

You can now create a table.

// Create a table
$this->db->createTable(
    'user',
    [
        'id'    => ['integer', 'primary key', 'not null'],
        'age'   => ['integer'],
        'name'  => ['varchar(10)']
    ]
)->execute();

The table is created.

You can now insert rows into the table.

$this->db->insert(
    "user",
    [
        "age" => 3,
        "name" => "three",
    ]
)->execute();

$last = $this->db->lastInsertId(); // 1
$rows = $this->db->rowCount();     // 1

You can now query the table.

$res = $this->db->select("*")
                ->from("user")
                ->where("id = 1")
                ->execute()
                ->fetch();

$res->id;   // 1
$res->age;  // 3
$res->name; // "three"

That is the basic usage and the idea is to create the SQL-queries using class methods and build tha actual SQL query behind the scene.

Dependency

This module depends upon, and extends, the database abstraction layer anax\database.

The module is usually used within an Anax installation but can also be used without Anax.

License

This software carries a MIT license. See LICENSE.txt for details.

 .  
..:  Copyright (c) 2013 - 2018 Mikael Roos, mos@dbwebb.se

anax / database-query-builder

Maintainers

Details