Database Access Layer

[Since 0.5.0]

Introduction

Pletfix's Database Access Layer takes care of the connection setup and abstracts access to the database engine. Furthermore, field types are abstracted over all supported database providers to translation to PHP data-types.

Pletfix has studied these libraries, picked up ideas from them, and partly adopted codes:

Currently, Pletfix provides a Database Access Layer for the the following database driver:

  • MSSQL
  • MySQL
  • PostgreSQL
  • SQLite

Configuration

The database configuration is located at config/database.php. As you can see, the most entries are environment variables.

Available Database Drivers

MSSQL MySQL PostgreSQL SQLite

MSSQL

Microsoft SQL Server also requires the typical connection parameters:

DB_STORE=sqlsrv
DB_MSSQL_HOST=127.0.0.1
DB_MSSQL_DATABASE=mydatabase
DB_MSSQL_USERNAME=sa
DB_MSSQL_PASSWORD=xxxx   

MySQL

The required connection parameters for a MySQL database are:

DB_STORE=mysql    
DB_MYSQL_HOST=localhost
DB_MYSQL_DATABASE=mydatabase
DB_MYSQL_USERNAME=myusername
DB_MYSQL_PASSWORD=mypassword

PostgreSQL

PostgreSQL requires the same information:

DB_STORE=pgsql
DB_PGSQL_HOST=localhost
DB_PGSQL_DATABASE=mydatabase
DB_PGSQL_USERNAME=myusername
DB_PGSQL_PASSWORD=mypassword

SQLite

Set the environment variables for SQLite like this:

DB_STORE=sqlite
DB_SQLITE_DATABASE=db/sqlite.db

The path for the database file is relative to the storage folder.

You may enter this command into a terminal to create a new SQLite database:

touch storage/db/sqlite.db

Connections

You may use the database() function to connect the default database store:

$db = database();

You can set the store name if you use an another store as the default. The store name should correspond to one of the stores listed in the stores configuration array in your database configuration file:

$db = database('my-database-store');

The database function returns a Database instance so you select could queries or manipulate the data.

The database function is just a shortcut to get a extended AbstractDatabase instance via the DatabaseFactory supported by Dependency Injector:

  $builder = DI::getInstance()->get('database-factory')->store($store);

Data Queries

Available Methods

Note! Because this methods expect pure SQL as the first argument, but the SQL syntax in detail varies depending on the database system, you should not call these methods directly and instead better use the QueryBuilder!

query()

You may run queries using the query method:

$users = database()->query('SELECT * FROM users WHERE role = ?', ['guest']);

The first argument passed to this method is raw SQL query. The second argument is any parameter bindings that need to be bound to the query.

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

$users = database()->query('SELECT * FROM users WHERE role = :role', ['role => 'guest]);

No matter how, it is highly recommended to use parameter binding because its provides protection against SQL injection.

The query method will always return an array of records. If you not set a third argument for the query function, each record within the array will be an associative array:

foreach ($users as $user) {
    echo $user['id'] . ':' . $user['role'] . '<br/>';    
}

If you set a class name as the third argument, each record will be an instance of this class:

$users = database()->query('SELECT * FROM users WHERE id = ?', [4711], User::class);
foreach ($users as $user) {
    echo $user->id . ':' . $user->role . '<br/>';    
}

If you use a class, the query will be assigned to the corresponding class properties according to the following rules:

  1. If there is a class property, which name is the same as a column name, the column value will be assigned to this property.
    Note, that the property could be private! Which is a bit unexpected but extremely useful.
  2. If there is no such property, then a magic __set() method will be called.
  3. If __set() method is not defined for the class, then a public property will be created and a column value assigned to it.
    You could create an empty __set() method if you like to avoid the automated property creation.

single()

Sometimes it's only possible to receive a single record. In this case it is more comfortable to use the single function unsteady the query function:

$user = database()->single('SELECT * FROM users WHERE id = ?', [4711], User::class);
if ($user !== null) {
    echo $user->id . ':' . $user->role . '<br/>';
}

scalar()

The scalar method fetches the first value (means the first column of the first record).

$count = database()->scalar('SELECT COUNT(*) FROM users WHERE role = :role', ['role => 'guest]);

cursor()

The cursor method runs a select statement against the database and returns a generator. With the cursor you could iterate the rows (via foreach) without fetch all the data at one time. This method is useful to handle big data.

foreach (database()->cursor('SELECT * FROM users WHERE role = ?', ['guest'], User::class) as $row) {
    echo $user->id . ':' . $user->role . '<br/>';
};

exec()

If you want to manipulate data, you could use the exec method, which is faster as query because no record have to be fetched to an array.

database()->exec('INSERT INTO users (firstname, lastname) VALUES (?, ?)', ['Stephen', 'Hawking']);

Transactions

The beginTransaction method starts a database transaction, the commit function finished this one and the rollback function will roll back the transaction.

$db = database();
$db->beginTransaction();
try {
    $db->insert('users', ['firstname' => 'Stephen', 'lastname' => 'Hawking']);
    $db->insert('users', ['firstname' => 'Albert',  'lastname' => 'Einstein']);
    $db->commit();

} catch (\Exception $e) {
    $db->rollback();
}

The supportsSavepoints method determines if the database driver can marks the current point within a transaction. If it's like that, transactions can also be nested. The transactionLevel method is then helpful to determine the number of active transactions:

$db->beginTransaction();
echo $db->transactionLevel(); // 1
...    
$db->beginTransaction();
echo $db->transactionLevel(); // 2
...
$db->rollback();
echo $db->transactionLevel(); // 1
...
$db->commit();
echo $db->transactionLevel(); // 0

Closures

Pletfix also offers the transaction method which executes a closure function within a transaction:

database()->transaction(function(Database $db) {
    $db->insert('users', ['firstname' => 'Stephen', 'lastname' => 'Hawking']);
    $db->insert('users', ['firstname' => 'Albert',  'lastname' => 'Einstein']);
});

If an exception is thrown within the transaction closure, the transaction will automatically be rolled back. And of course, if the closure executes successfully, the transaction will automatically be committed.

Schema

The schema function returns an instance of Schema that allows you to change the structure of the database.

$schema = database()->schema();

The schema functions are mainly used in migration files.

Field Type Mapping

The following table shows an overview of the Pletfix's type abstraction for the database schema and migrations files. The matrix contains the mapping information for how a specific abstract type is mapped to the database and back to PHP. The Table based on Doctrine's Mapping Matrix.

Abstract PHP MySql PostgreSQL MSSQL SQLite
identity integer INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT SERIAL INT NOT NULL IDENTITY(1,1) PRIMARY KEY INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
bigidentity integer BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT BIGSERIAL BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
smallint integer SMALLINT SMALLINT SMALLINT SMALLINT 
integer integer INT INTEGER  INT INT
unsigned integer INT UNSIGNED  INTEGER  INT INT UNSIGNED 
bigint string BIGINT BIGINT  BIGINT BIGINT
numeric string DECIMAL(p, s) NUMERIC(p, s) NUMERIC(p, s) NUMERIC(p, s)
float float DOUBLE DOUBLE PRECISION FLOAT DOUBLE
string string VARCHAR(n)  VARCHAR(n) NVARCHAR(n), NCHAR(n) VARCHAR(n) 
text string TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT TEXT TEXT, VARCHAR(MAX) TEXT
guid string VARCHAR(36)  UUID UNIQUEIDENTIFIER UUID
binary resource VARBINARY(n)  BYTEA  VARBINARY(n), BINARY(n) VARBINARY(n) 
blob resource TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB  BYTEA IMAGEVARBINARY(MAX) BLOB
boolean boolean TINYINT(1) BOOLEAN BIT BOOLEAN
date DateTime DATE DATE DATE (#1) DATE
datetime DateTime DATETIME  TIMESTAMP(0) WITHOUT TIME ZONE DATETIME DATETIME
timestamp DateTime TIMESTAMP (#2) TIMESTAMP(0) WITH TIME ZONE DATETIMEOFFSET(6) (#1) TIMESTAMP 
time DateTime TIME TIME(0) WITHOUT TIME ZONE TIME(0) (#1) TIME
array array TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT  TEXT TEXT, VARCHAR(MAX) TEXT
json array JSON (#3) JSONB (#4) TEXT, VARCHAR(MAX) TEXT 
object object TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT TEXT TEXT, VARCHAR(MAX) TEXT
  • (#1) Before MS SQL Server 2008: DATETIME
  • (#2) Extra: ON UPDATE CURRENT_TIMESTAMP; Default: CURRENT_TIMESTAMP
  • (#3) Before MySql 5.7.8: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
  • (#4) Before PostgreSQL 9.2: TEXT; before PostgreSQL 9.4: JSON

Available Methods

Method Listing

tables()

The tables method returns an associative array of tables in the database.

$tables = database()->schema()->tables();

The key of the returned array is the table name and the value lists following table attributes:

Each return item is a array with the following values:

  • name: (string) The table name
  • collation: (string) The default collation of the table.
  • comment: (string) A hidden comment.

columns()

The columns method returns an associative array of information about the columns of the table.

$columns = database()->schema()->columns('books');

The key of the returned array is the column name and the value lists following column attributes:

  • name: (string) The column name.
  • type: (string) The column data type.
  • size: (int) The column size (the maximum number of digits).
  • scale: (int) The number of digits to the right of the numeric point. It must be no larger than size.
  • nullable: (bool) Is the column not marked as NOT NULL?
  • default: (mixed) The default value for the column.
  • collation: (string) The collation of the column.
  • comment: (string) A hidden comment.

The column type is mapped to an abstract type, see also Field Type Mapping.

indexes()

The indexes method returns an associative array of indexes in the table:

$indexes = database()->schema()->indexes('books');

The key of the returned array is the index name and the value lists following index attributes:

  • name: (string) The index name.
  • columns: (array) The list of column names.
  • unique: (bool) Is the index a unique index?
  • primary: (bool) Is the index the primary key?

create-table()

The createTable method creates a new table on the schema:

database()->schema()->createTable('books', [
    'id'        => ['type' => 'identity'],
    'title'     => ['type' => 'string'],
    'author_id' => ['type' => 'integer'],
], $options);

Argument $columns is an associative array where the key is the column name and the value is the column attributes. A column attribute is a array with the following values:

  • type: (string) The column data type. It should be given as an abstract type, see also Field Type Mapping.
  • size: (int) The column size (the maximum number of digits).
  • scale: (int) The number of digits to the right of the numeric point. It must be no larger than size.
  • nullable: (bool) Is the column is not marked as NOT NULL.
  • default: (mixed) The default value for the column.
  • collation: (string) The collation of the column.
  • comment: (string) A hidden comment.

The third argument $options is optional and could have following values:

  • temporary: (bool) The table is temporary. todo kann raus!
  • collation: (string) The default collation of the table.
  • comment: (string) A hidden comment.

dropTable()

The dropTable method drops a table from the schema:

database()->schema()->dropTable('books');

renameTable()

The renameTable method set a new name for a given table on the schema:

database()->schema()->renameTable($from, $to);

truncateTable()

The truncateTable method truncates the table.

database()->schema()->truncateTable('books');

addColumn()

The addColumn method adds a new column to the table. The example below adds a new column "genre_id" to the table "books":

database()->schema()->addColumn('books', 'genre_id', $options);

Argument $options is an array and have following values:

  • type: (string) The column data type. It should be given as an abstract type, see also Field Type Mapping.
  • size: (int) The column size (the maximum number of digits).
  • scale: (int) The number of digits to the right of the numeric point. It must be no larger than size.
  • nullable: (bool) Is the column is not marked as NOT NULL.
  • default: (mixed) The default value for the column.
  • collation: (string) The collation of the column.
  • comment: (string) A hidden comment.

dropColumn()

The dropColumn method deletes a column from the table:

database()->schema()->dropColumn('books', 'genre_id');

renameColumn()

The renameColumn method set a new name for a given column. The example below change in the table "books" the name of the column "caption" to "title":

database()->schema()->renameColumn('books', 'caption', 'title');

addIndex()

The addIndex method creates an index for a given table, e.g. for table "books":

database()->schema()->addIndex('books', ['title', 'author_id'], ['unique' => true]);

The second argument is the list of columns.

The third argument is an array to specify the columns and other options for the index:

  • name: (string) The name of the index. It will be generated automatically if not set. It will be ignored by a primary key.
  • unique: (bool) The index is a unique index.
  • primary: (bool) The index is the primary key.

dropIndex()

The dropIndex method deletes a index from the table by given index name or options:

database()->schema()->dropIndex('books', ['title', 'author_id'], ['unique' => true]);

The second argument is the list of columns. Could be null if the index name is given or it's the primary index.

The third argument is an array with index options and is only needed if the index name is not set:

  • name: (string) The name of the index. It will be generated automatically if not set.
  • unique: (bool) The index is a unique index. It's needed to generate the name.
  • primary: (bool) The index is the primary key.

zero()

The zero method gets a Zero-Value by given column type.

database()->schema()->zero('string');

The type should be given as an abstract type, see also Field Type Mapping.

Miscellanea Functions

Method Listing

builder()

The builder method creates a new QueryBuilder instance:

$builder = database()->builder();

config()

The config method gets the database configuration.

$driver = database()->config('driver');    

connect()

The connect method connects to the database.

database()->connect();

dump()

The method binds the given values to a SQL statement and print it out without executing:

database->dump('SELECT * FROM users WHERE username = ?', [$username]);

If you set the third argument to true, the method will return the information rather than print it:

$dump = database->dump('SELECT * FROM users WHERE username = ?', [$username], true);

disconnect()

The disconnect method disconnects from the database:

database()->disconnect();

lastInsertId()

The method returns the last inserted autoincrement sequence value.

If you don't insert any values before, the function returns 0.

If you insert multiple rows using a single insert() call, lastInsertId() returns dependency of the driver the first or last inserted row.

Set PHP manuals for more information.

quote()

The quote method quotes a value for use in an SQL statement.

$quotedValue = database()->quote('Hello world!'); // "'Hello world!'"

quoteName()

The quoteName method quotes a identifier name (e.g. table, column or index name):

$quotedTableName = database()->quoteName('books'); // "`books`"

reconnect()

The reconnect method reconnects to the database:

database()->reconnect();

table()

The table method returns an instance of QueryBuilder for the given table.

$builder = database()->table('books');

You could also define an alias for your table if you prefer it:

$builder = database()->table('books', 't1');

Note, this is equal with that:

$builder = database()->builder()->from('books', 't1');

version()

The version method returns the server version:

echo database()->version();

The method returns FALSE if the driver does not support getting attributes.


(edit on GitHub)