Skip to content

DATABASE.md

Latest commit

 

History

History
440 lines (359 loc) · 13.3 KB

DATABASE.md

File metadata and controls

440 lines (359 loc) · 13.3 KB
Raw

Database API

The database API shipped with Symphony leverages the capabilities given by php's PDO database abstraction extension. But, since it is not possible to get truly safe dynamic SQL statement, even with PDO, it was important to build a new API with Symphony's needs in mind, which requires dynamic table and column names.

Hence, the API was designed with the following goals, in that order:

  1. Safety/security
  2. Ease of use
  3. Performance
  4. Almost 100% retro-compatible with LTS APIs.

Breaking changes in 3.0.0

Before 3.0.0, all database operations resided in the MySQL class. This class as been renamed to Database in 3.0.0. In order to make it easier to migrate to 3.0.0, it still ships a MySQL class, but does not use it. In future versions, it will be removed, so make sure that you access everything via Symphony::Database() instance.

There used to be two ways to execute SQL queries: query() and fetch(). fetch() was used when we wanted to get tabular results. In all other cases, query() was used.

Light wrappers around those two methods did exist. Developers could either call fetchCol(), fetchRow() or fetchVar() to reduce the tabular data in a single call. This was less than optimal, because the signatures of those functions where hard to remember and error prone. Developers could also call update(), insert() and delete() to issue quick write statements. But again, this was not flexible. Those six functions still exist and work as they used to, but they got refactored. This compatibility layer will be removed in future versions, so make sure to update your code with the new API.

API

In order to execute SQL statements, the first thing to do is to create a DatabaseStatement object. The Database class provides factory methods that allow the creation of all derived DatabaseStatement classes. Once the statement object is created, configuration and execution of the statement are left to the caller. This makes it possible to pass statements around before the statement gets executed. It also removes the need to concat and parse strings, since all this heavy lifting is done under the hood.

The API uses chains of command that return the current instance. This often eliminates the need to create statement variables and go straight to the result.

In order to execute the statement, developers needs to call the execute() method, which returns a DatabaseStatementResult object. The result exposes a success() method and a direct access to the underlying PDO result.

$stm = Symphony::Database()->statement('This is a raw SQL statement');
$result = $stm->execute();
if (!$result->success()) {
    die('It did not worked');
}

When the query returns tabular data, execute() will return a DatabaseTabularResult instance. This class inherits from DatabaseStatementResult and add methods to be able to access the data with ease. When possible, the old vocabulary is used. This makes DatabaseTabularResult expose methods like rows(), next(), column(), rowsIndexedByColumn(), variable(), integer(), float(), boolean() and string().

Note that if there was a SQL error, PDO is configured to throw an exception. We wrap those errors in a DatabaseStatementException in order to provide more context.

Each specialized DatabaseStatement exposes methods that are specific to its usage. For example, DatabaseQuery, DatabaseUpdate and DatabaseDelete all share a where() method. Most methods are named by the associated SQL keywords they create.

Specialized DatabaseStatement are created via factory methods on the Database object. Here is the list of all factory methods, the type of object they create and their basic usage:

select() -> DatabaseQuery

Symphony::Database()
    ->select(['t.*'])
    ->from('table')
    ->as('t')
    ->leftJoin('join', 'j')
    ->on(['t.id' => '$j.other_id'])
    ->where(['j.x' => 2])
    ->limit(2)
    ->execute()
    ->rows();

show() -> DatabaseShow

Symphony::Database()->show()->like('tbl_%')->execute()->rows();

showColumns() -> DatabaseShow

Symphony::Database()->showColumns()->where(['x' => 'name'])->execute()->rows();

showIndex() -> DatabaseShow

Symphony::Database()->showIndex()->where(['x' => 'name'])->execute()->rows();

insert() -> DatabaseInsert

Symphony::Database()
    ->insert('table')
    ->values([
        'x' => 1,
        'y' => 'TEST',
        'z' => true
    ])
    ->execute()
    ->success();

update() -> DatabaseUpdate

Symphony::Database()
    ->update('table')
    ->set([
        'x' => 1,
        'y' => 'TEST',
    ])
    ->execute()
    ->success();

delete() -> DatabaseDelete

Symphony::Database()
    ->delete('table')
    ->where(['id' => 2])
    ->limit(1)
    ->execute()
    ->success();

drop() -> DatabaseDrop

Symphony::Database()->drop('table')->table('table2')->execute()->success();

dropIfExists() -> DatabaseDrop

Symphony::Database()->dropIfExists('table')->execute()->success();

describe() -> DatabaseDescribe

Symphony::Database()->describe('table')->field('field')->execute()->rows();

create() -> DatabaseCreate

Symphony::Database()
    ->create('table')
    ->fields([
        'x' => [
            'type' => 'varchar(100)',
            'default' => 'TATA',
        ],
        'y' => [
            'type' => 'datetime',
            'default' => '2012-01-01 12:12:12'
        ],
        'z' => [
            'type' => 'enum',
            'values' => ['yes', 'no'],
            'default' => 'yes',
        ],
        'id' => [
            'type' => 'int(11)',
            'auto' => true
        ]
    ])
    ->keys([
        'id' => 'primary',
        'x1' => [
            'type' => 'unique',
            'cols' => ['x', 'y']
        ],
    ])
    ->execute()
    ->success();

alter() -> DatabaseAlter

Symphony::Database()
    ->alter('table')
    ->add([
        'x' => 'varchar(100)'
    ])
    ->first()
    ->change('z', [
        'y' => 'varchar(200)'
    ])
    ->dropKey('x')
    ->execute()
    ->success();

rename() -> DatabaseRename

Symphony::Database()
    ->rename('table')
    ->to('new_table_name')
    ->execute()
    ->success();

optimize() -> DatabaseOptimize

Symphony::Database()->optimize('table')->execute()->success();

truncate() -> DatabaseTruncate

Symphony::Database()->truncate('table')->execute()->success();

set() -> DatabaseSet

Symphony::Database()->set('variable')->value('value')->execute()->success();

transaction() -> DatabaseTransaction

Symphony::Database()
    ->transaction(function (\Database $db){
        $db->insert(...);
        $db->update(...);
    })
    ->execute()
    ->success();

statement() -> DatabaseStatement

// THIS IS UNSAFE!!! Make sure that user submitted data is properly escaped!
// Theoretically, you should not need it.
Symphony::Database()->statement('Raw SQL')->execute()->success();

Statements safety

All DatabaseStatement make sure that they generate SAFE SQL, by escaping all values and checking the resulting SQL string to invalid characters, such as ', \ and --. In order to make the compatibility layer work, SQL strings can be added using the unsafeAppendSQLPart() method and then flagged as unsafe by calling unsafe(). This turns off most of the validity checks. Developers should always try to avoid using statement() and unsafeAppendSQLPart().

Migration

Replace all MySQL references. Instead use Symphony::Database(). We now try to avoid static members, so it is always safer to use the instance instead.

Replace fetch(), fetchCol(), fetchRow() and fetchVar() with select(). Then use the DatabaseQueryResult returned by execute() to manipulate the actual result by either calling rows(), next(), column(), variable().

Update calls to update(), insert() and delete() and pass a single argument to them: the table name. Keeping the multiple parameters will make the compatibility layer delegate the calls to the old implementation that has been moved in _update(), _insert() and _delete().

Please note that delete() used to work with a single argument, deleting ALL records in the table. This is not supported by the compatibility layer.

Update calls to query() must be replaced with their safe equivalent, using the provided factory methods.

With mysqli all values needed to be properly escaped manually by the developers. Calls to quote(), quoteFields(), cleanValue(), cleanFields() can be removed since escaping is made automatically when using the safe methods. You can now pass user submitted input directly to the database API (except when using raw SQL statement).

Calls to determineQueryType() must be removed, since it shouldn't be needed anymore. Finally, debug() has been renamed getLogs().

API Stability

Even if the API is new, the public PHP API should be pretty stable. Developers should not depend on public methods marked as @internal. Developers should also not depend on the string of generated SQL statement, as this can change overtime.

Array syntax

Almost all methods expect a single argument, which is either a value or an array. The basic rule of thumb when dealing with arrays is that keys represent column names and values are literals.

There are 3 more complex syntaxes, that are defined in DatabaseWhereDefinition, DatabaseKeyDefinition and DatabaseColumnDefinition, which respectively describe WHERE, KEY and COLUMN definitions.

For a more complete overview of the possibilities, you should check the tests under the tests directory.

where()

where() uses an "array syntax" to express the filters. For more information, check the PHP doc.

// With a basic query
$stm = Symphony::Database()->select(['col'])->from('table');

// By default, the key is the column name and the value is the literal value to look for.
$stm->where(['x' => 'y']);

// The value can be also be a [operator => value] pair
$stm->where(['x' => ['<' => 1]]);

// It supports many many operators
$stm->where(['or' => [
    ['x' => ['<' => 1]],
    ['y' => 'x']
]]);
$stm->where(['or' => [
    'in' => ['x' => ['y', 'z']]
]]);

// Even dates!
$stm->where(['or' => [
    'date' => [
        'start' => 'today',
        'end' => 'tomorrow',
    ]
]]);

// To use a column name as the value, simple prefix it with `$`
$stm->where(['x' => '$id']);

// Functions are also supported!
$stm->where(['x' => ['<=' => 'SUM(total)']]);

// Sub queries are first class citizens
$sub = $stm->select(['col'])->from('table');
$stm->where(['x' => ['in' => $sub]]);

// Note that calling where() multiple times on the same statement will
// join all filters with an AND operation.

keys()

keys() uses an "array syntax" to express all the values needed to create table keys. For more information, check the PHP doc.

// With a statement (alter or create)
$stm = Symphony::Database()->create('table');

// You can create simple keys with a single column (the name of the column will be used as the key name)
$stm->keys(['col' => 'type']);

// There are 5 valid types: 'primary', 'key', 'index', 'unique', 'fulltext'
$stm->keys([
    'id' => 'primary',
    'text' => 'fulltext',
    'ssn' => 'unique'
]);

// Using a string as the array value is a shortcut to the complete form, which can specify options
$stm->keys(['col' => [
    'type' => 'type',
    'cols' => ['col']
]]);

// Keys can reference multiple columns
$stm->keys(['xid' => [
    'type' => 'index',
    'cols' => ['x', 'id']
]]);

// Which can also accept a size definition
$stm->keys(['xid' => [
    'type' => 'index',
    'cols' => [
        'x' => 333,
        'id' => 333
    ]
]]);

// When using alter
$stm = Symphony::Database()->alter('table');

// The syntax for the key definition is the same, but the right method must be used
$stm->addKey([...])
    ->dropKey('key')
    ->addIndex([...])
    ->dropIndex('index')
    ->addPrimaryKey('id')
    ->dropPrimaryKey();

fields()

fields() uses an "array syntax" to express all the values needed to create table fields. For more information, check the PHP doc.

// With a statement (alter or create)
$stm = Symphony::Database()->create('table');

// You can create simple columns
$stm->fields(['col' => 'type']);

// There are 5 valid types: 'varchar', 'text', 'enum()', 'int()', 'datetime'
$stm->fields([
    'id' => 'int(11)',
    'text' => 'text',
    'ssn' => 'varchar(16)'
]);

// Using a string as the array value is a shortcut to the complete form, which can specify options
$stm->fields(['id' => [
    'type' => 'int(11)',
    'signed' => false,
    'null' => false,
    'collate' => 'utf8',
    'default' => 'DEF',
]]);

// Integer be auto-increment (which disable null and default values)
$stm->fields(['id' => [
    'type' => 'int(11)',
    'auto' => true
]]);

// The default collate of the table will be used when not set otherwise
$stm->collate('utf8')
    ->fields([
        'text' => 'text', // Will use utf8 collate
    ]);

// When using alter
$stm = Symphony::Database()->alter('table');

// The syntax for the key definition is the same, but the right method must be used
$stm->add([...])->after('col')
    ->drop('col')
    ->change('col', [...])
    ->modify([...]);