Developer Guide: Accessing the Database

From Mothership
Jump to: navigation, search

Mothership uses MySQL for its database language.

The Query Parser

To use the database safely, all queries are run through the QueryParser. This allows you to declare variables within the SQL query and set what type they should be attributed to. The syntax is as follows:

:variableName?type

The variables are then given as an array in a second parameter, with the variable name as the key:

['variableName' => $variableValue]

Variable types

Each type has a single character representation. The supported types are as followed:

s

Declares the variable as a string

i

Declares the variable as an integer

f

Declares the variable as a float

d

Declares the variable as a datetime (will be stored as a unix timestamp)

b

Declares the variable as a boolean (will be stored as a 1 or 0)

Usage

So an example of this in action:

$parser = new QueryParser($connection);

$parser->parse("
    INSERT INTO
        table
    VALUES
    (
        :string?s,
        :integer?i,
        :float?f,
        :datetime?d,
        :boolean?b
    )
", [
    'string' => 'This is a string',
    'integer' => 12,
    'float' => 1.1,
    'datetime' => new \DateTime,
    'boolean' => true
]);


Variable flags

In addition to the types, there are two special flags that can be added to the flag

j

Prepended to the type declaration, it determines that the variable is an array and should be imploded into a comma separated list:

$parser = new QueryParser($connection);

$parser->parse("
    SELECT
        *
    FROM
        table
    WHERE
        id
    IN
        (:ids?ji)
", [
    'ids' => [1, 2, 3]
]);

n

Added after the type declaration, allows the variable to be declared as NULL if it is falsey

$parser = new QueryParser($connection);

$parser->parse("
    INSERT INTO
        table
    VALUES
    (
        :stringOrNull?sn
    )
", [
    'stringOrNull' => null,
]);

Writing queries

There are three main ways to write database queries:

  • Using the Query object
  • Using the Transaction object
  • Using the QueryBuilder

The Query Object

The Query object represents a single database query of any type. It is the most basic way to access the database. To use it, you will need to call the db.query from the service container, and then call the run() method, and returns an instance of Message\Cog\DB\Result. The parameters match those of the QueryParser, with the first parameter being the SQL string, and the second parameter being an array of variables to insert into the query:

$this->get('db.query')->run("
    INSERT INTO
        table
    VALUES
    (
        :string?s,
        :integer?i,
        :float?f,
        :datetime?d,
        :boolean?b
    )
", [
    'string' => 'This is a string',
    'integer' => 12,
    'float' => 1.1,
    'datetime' => new \DateTime,
    'boolean' => true
]);

The Transaction Object

The Transaction object represents multiple non-SELECT queries, chained together in a single database transaction. To use it, you will need to call the db.transaction from the service container, and then call the add() method to add a new query to the transaction. It has the same parameters as the Query::run() method. Adding the queries will not execute them, however. To execute the queries, you will need to call the commit() method:

$transaction = $this->get('db.transaction');
$transaction->add("
    INSERT INTO
        table
    VALUES
    (
        :string?s,
        :integer?i,
        :float?f,
        :datetime?d,
        :boolean?b
    )
", [
    'string' => 'This is a string',
    'integer' => 12,
    'float' => 1.1,
    'datetime' => new \DateTime,
    'boolean' => true
]);

$transaction->add("
    INSERT INTO
        another_table
    VALUES
    (
        :stringOrNull?sn
    )
", [
    'stringOrNull' => null,
]);

$transaction->commit();

Note: The db.transaction service is not a singleton, and so will need to be set against a variable for it to work.

The QueryBuilder

The QueryBuilder object (implementing the QueryBuilderInterface)provides a fluid interface for creating SELECT statements. It is designed for allowing queries to be passed around between classes and methods. To use it, you need to call the getQueryBuilder() method on the db.query.builder.factory service. This will create a new instance of the QueryBuilder.

The query builder can return an instance of the Query object by calling getQuery(), and can return the complete SQL string by calling getQueryString()

To run a basic query:

$result = $this->get('db.query.builder.factory')->getQueryBuilder()
    ->select(['locale'])
    ->from('product_export')
    ->leftJoin('product', 'product.product_id = product_export.product_id')
    ->where('product.product_id = :id?i', ['id' => 1])
    ->getQuery()
    ->run()
;

The QueryBuilder covers most methods you would need in a SELECT query:

select($select, $distinct = false)

Determine which columns to select

  • $select - a string or an array of strings, to determine which columns to select.
  • $distinct - a boolean determining whether to select unique values only, defaults to false.

from($alias, $table = null)

Determine which table to select from

  • $alias - a string determining how the table will be referred to. If $table is set to null, it will assume that this is the table's actual name.
  • $table - if not set to null, will assume that $alias is an alias for the table declared here. This can be either a string representing a table name, or an instance of QueryBuilderInterface representing a subquery.

join($alias, $onStatement, $table = null)

Add a JOIN statement to the query

  • $alias - like from(), represents how a table will be referred to in the query. If $table is set to null, it will assume that this is the table's actual name.
  • $onStatement - the ON statement used for the join. Note: USING statements are not currently supported.
  • $table - if not set to null, will assume that $alias is an alias for the table declared here. This can be either a string representing a table name, or an instance of QueryBuilderInterface representing a subquery.

leftJoin($alias, $onStatement, $table = null)

Same as join(), except it adds a LEFT JOIN instead of a regular JOIN.

where($statement, array $variables = null, $and = true)

Adds a WHERE statement to the query.

  • statement - the WHERE statement, with variables aliased as outlined above.
  • variables - the variables to pass to the QueryParser.
  • $and - a boolean representing whether the statement's relationship to the previous where statement should be an AND or an OR. Set as true for AND, and false for OR.

groupBy($groupBy)

Adds a GROUP BY statement to the query

  • $groupBy - can be a string or an array of strings, representing columns to group by

having($statement, array $variables = [], $and = true)

Apply a HAVING statement after the GROUP BY. The variables match that of the where().

orderBy($orderBy)

Set which fields to order the result by

  • $orderBy - a string or array of strings, representing which columns to order by. Note: 'ASC' and 'DESC' should be included as part of this string.

limit($offset, $limit = null)

Set the limit on the results for the query

  • $offset - if $limit is null, this represents the upper limit where 0 is the lower limit, otherwise it represents the lower limit.
  • $limit - if not null, this represents the upper limit

union(QueryBuilderInterface $queryBuilder ...)

Union an unlimited number of queries represented by query builders to the first query

unionAll(QueryBuilderInterface $queryBuilder ...)

Union an unlimited number of queries represented by query builders to the first query using UNION ALL