Irfan's Database
Create models and/or access your databases with ease and least overhead.
A bare-minimum and simple database access., (*1)
Installation
$ composer require irfantoor/database
Creating a Database object
method: new Database(?array $connection = null), (*2)
parameters:
- array $connection - Connection array containing the parameters required
by the Database Engines like MySQL, SQLite ..., (*3)
returns:
Database object, (*4)
example:, (*5)
<?php
$db = new Database(
[
'type' => 'sqlite',
'file' => 'posts.sqlite'
]
);
Connect to a Database Engine
method: connect(array $connection), (*6)
parameteres:
- array $connection, (*7)
returns:
- true - If the the database engine was successfully connected
- false - If could not connect to the engine, (*8)
example:, (*9)
<?php
# a database object is created, but it needs to be connected to a database
# before querring
$db = new Database();
# for sql
$connection = [
'type' => 'sqlite',
'file' => 'storage_path/users.sqlite',
];
# for mysql
$connection = [
'type' => 'mysql',
'host' => 'localhost',
'user' => 'root',
'password' => 'toor',
'db_name' => 'test',
];
$db->connect($connection);
# Note: the definition of 'type' in connection is obligatory.
Actions passed to database engine
Executes a raw SQL
method: query(string $sql, array $data = []), (*10)
parameteres:
- string $sql,
- array $bind associative array to bind data in sql while preparing, (*11)
returns:
- true or false - if the query is of the type UPDATE, INSERT and DELETE
- array - returns the result of the SELECT query, (*12)
example:, (*13)
<?php
$result = $db->query('SECLECT count(*) from users where valid=true');
Inserts a record into a connected database
method: insert(string $table, array $record, array $bind = []), (*14)
parameteres:
- string $table - The table to be queried
- array $record - associative array of record, (*15)
Values might contain variables of the form :id etc, which are filled using the prepare
mechanism, taking data from bind array e.g. ['id' => :id, 'name' => :name ]
Note: record must contain all of the required fields
- array $bind - associative array e.g. ['id' => $_GET['id'] ?? 1], (*16)
returns:
- true - if the record was inserted
- false - record was not inserted, (*17)
example:, (*18)
<?php
$db->insert('users', ['name' => 'Fabien Potencier', 'email' => 'fabien@symfony.com']);
# OR
$user = [
'name' => 'Irfan TOOR',
'email' => 'email@irfantoor.com',
'password' => 'its-a-test',
];
$db->insert('users', $user);
# NOTE: the query will be prepared and values will be bound automatically
Updates an existing record
method: update(string $table, array $record, array $options = []), (*19)
parameteres:
- string $table
- array $record associated array only includes data to be updated, (*20)
e.g $record = [
'id' => 1,
'user' => 'root',
'password' => 'toor',
'groups' => 'admin,user,backup',
'remote' => false,
];
- array $options contains where, limit or bind etc.
e.g $options = [
'where' => 'id = :id', <------------+
'limit' => 1, |
'bind' => [ |
'id' => $_GET['root_id'] ?? 1, -+
]
];
If options are not provided following are the assumed defaults:
- 'where' => '1 = 1',
- 'limit' => 1, // see DatabaseEngineInterface::get
- 'bind' => [],, (*21)
returns:
- true - if successful
- false - otherwise, (*22)
example:, (*23)
<?php
$db->update('users',
[
'password' => $new_password,
],
[
'where' => 'email = :email',
'bind' => [
'email' => $email
]
]
);
Removes a record from database
method: remove(string $table, array $options), (*24)
parameteres:
- string $table
- array $options contains where, limit or bind options
If options are not provided following are the assumed defaults:, (*25)
[
'where' => '1 = 0', # forces that a where be provided
'limit' => 1, # see DatabaseEngineInterface::get
'bind' => [], # see DatabaseEngineInterface::update
]
returns:
- true - if removed successfully
- false - otherwise, (*26)
example:, (*27)
<?php
$db->remove(
'users',
[
'where' => 'email = :email',
'bind' => [
'email' => $email
]
]
);
Retreives list of records
method: get(string $table, array $options = []), (*28)
parameteres:
- string $table
- array $options - Associative array containing where, order_by, limit and bind, (*29)
If limit is an int, the records are retrived from start, if its an array it is
interpretted like [int $from, int $count], $from indicates number of records to
skip and $count indicates number of records to retrieve., (*30)
e.g. $options = [
'limit' => 1 or 'limit' => [0, 10]
'order_by' => 'ASC id, DESC date',
'where' => 'date < :date', <---------------------------+
'bind' => ['date' => $_POST['date'] ?? date('d-m-Y')], +
# bind: see DatabaseEngineInterface::update
];
returns:, (*31)
array [row ...] containing the array of rows or null if not found, (*32)
example:, (*33)
<?php
$list = $db->get('posts', [
'where' => 'created_at like :date',
'order_by' => 'created_at DESC, id DESC',
'limit' => [0, 10],
'bind' => ['date' => '%' . $_GET['date'] . '%']
]);
Retreives only the first record
method: getFirst(string $table, array $options = []);, (*34)
parameteres:
- string $table name of the table e.g. $table = 'useres';
- array $options as explained in DatabaseEngineInterface::get, (*35)
returns:, (*36)
array containing the associative key=>value pairs of the row or null otherwise, (*37)
example:, (*38)
<?php
$last_post = $db->getFirst('posts', ['orderby' => 'date DESC']);
Database Models
NOTE: Currently Models only supports SQLite db, (*39)
Models use the database and calls as explained above. Since a model is tied to a
table, therefore the same calls (of database) apply to a model except that the first prameter of
table_name is not present in the methods., (*40)
Creating a model
example: Models\Users.php, (*41)
<?php
namespace Models\Users;
use IrfanTOOR\Database\Model;
class Users extends Model
{
function __construct($connection)
{
# schema needs to be defined
$this->schema = [
'id' => 'INTEGER PRIMARY KEY',
'name' => 'NOT NULL',
'email' => 'COLLATE NOCASE',
'password' => 'NOT NULL',
'token',
'validated' => 'BOOL DEFAULT false',
'created_on' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
'updated_on' => 'INTEGER'
];
# indices need to be defined
$this->indices = [
['index' => 'name'],
['unique' => 'email'],
];
# call the constructor with the $connection
parent::__construct($connection);
}
}
Model constructor
method: $users = new Users(array $connection), (*42)
parameteres:
- array $connection - ['file' => $db_path . 'users.sqlite', 'table' => 'users'], (*43)
returns:, (*44)
Users model object, (*45)
example:, (*46)
<?php
use Model\Users;
$connection = [
'file' => $db_path . 'users.sqlite',
'table' => 'users'
];
# NOTE: If table name is not provided Model name e.g. 'Users' will be converted
# to lowercase i.e. 'users' and will be used as table name.
$users = new Users($connection);
Retrieves the name of the database file
method: getDatabaseFile(), (*47)
parameteres: none, (*48)
returns:, (*49)
string - pathname of the sqlite file the model is connected to, (*50)
example:, (*51)
<?php
$file = $users->getDatabaseFile();
Prepares a schema of the datbase from model definition and returns it
method: prepareSchema(), (*52)
parameteres: none, (*53)
returns:, (*54)
string - Raw SQL schema, prepared from the definition of schema and indices,
which were provided while wrinting the model (ref: Creating a Model), is returned.
This schema can be used to create the sqlite file manually., (*55)
example:, (*56)
<?php
$schema = $users->prepareSchema();
echo $schema;
Deploy the schema
method: deploySchema(string $schema), (*57)
parameteres:
- string $schema - The schema to be deployed to the connected file, (*58)
throws: Exception - in case of error, (*59)
returns: nothing, (*60)
example:, (*61)
<?php
$file = $sb_path . 'users.sqlite';
# create a file and deploy the schema if it does not exist
if (!file_exists($file)) {
file_put_contents($file, '');
$users = new Users(['file' => $file]);
$schema = $users->prepareSchema();
$users->deploySchema($schema);
}
Insert a record
method: insert(array $record, array $bind = []), (*62)
parameteres:
- array $record Asociative array of record,, (*63)
values might contain variables of the form :id etc, which are filled using the
prepare mechanism, taking data from bind array e.g. ['id' => :id, 'name' => :name ]
Note: record must contain all of the required fields, (*64)
- array $bind - The data we need to bind to the :placeholders in $record
returns:
- true - if inserted the record successfully
- false - otherwise, (*65)
example:, (*66)
<?php
$user = [
'name' => 'Irfan TOOR',
'email' => 'email@irfantoor.com',
'password' => 'some-password',
];
$users->insert($user);
Insert or update a record
This method inserts the record if the record deoes not exist, or updates the
existing one., (*67)
method: insertOrUpdate(array $record, array $bind = []), (*68)
parameteres:
- array $record - Associative array represnting one record
- array $bind - The data we need to bind to the :placeholders in $record, (*69)
returns:
- true - if inserted or updated the record successfully
- false - otherwise, (*70)
example:, (*71)
<?php
$user['password'] = 'password-to-be-updated';
$users->insertOrUpdate($user); # updates the record of previous example
$user = [
'name' => 'Some User',
'email' => 'email@example.com',
'password' => 'some-password',
];
$users->insertOrUpdate($user); # inserts the record now
Update an existing record
method: update(array $record, array $options = []), (*72)
parameteres:
- array $record - Associative array represnting one record
- array $options - The where clause or the binding data etc., (*73)
returns:
- true - if updated the record successfully
- false - otherwise, (*74)
example:, (*75)
<?php
$email = 'email@example.com';
$users->update(
# only the record data which we need to modify
[
'password' => 'password',
],
# options
[
'where' => 'email = :email',
'bind' => [
'email' => $email
]
]
);
Remove an existing record
method: remove(array $options), (*76)
parameteres:
- array $options - The where clause or the binding data etc., (*77)
returns:
- true - if removed the record successfully
- false - otherwise, (*78)
example:, (*79)
<?php
$users->remove([
'where' => 'email = :email',
'bind' => [
'email' => $email
]
]);
Retrieve a list of records
method: get(array $options = []), (*80)
parameteres:
- array $options - The where clause or the binding data etc., (*81)
returns:
array or records or null, (*82)
example:, (*83)
<?php
$list = $users->get();
$list = $users->get(['where' => 'validated = true']);
$list = $posts->get(
[
'where' => 'created_at like :date',
'order_by' => 'created_at DESC, id DESC',
'limit' => [0, 10],
'bind' => ['date' => '%' . $_GET['date'] . '%']
]
);
Retrieve the first record
method: getFirst(array $options = []), (*84)
parameteres:
- array $options - The where clause or the binding data etc. this might include
the order_by and limit parameters, (*85)
returns:
- array - an associative array containing the record
- null - if could not find one, (*86)
example:, (*87)
<?php
$user = $users->getFirst();
$last_post = $posts->getFirst(['orderby' => 'date DESC']);
Verify if a record exists
method: has($options = []), (*88)
parameteres:
- array $options - The where clause or the binding data etc., (*89)
returns:
- true - if record exists
- false - otherwise, (*90)
example:, (*91)
<?php
$users->has(
[
'where' => 'email = :email',
'bind' => [
'email' =>$email,
]
]
);
Wallogit.com
Irfan TOOR