README

Create models and/or access your databases with ease and least overhead. A bare-minimum and simple database access.

Installation

$ composer require irfantoor/database

Creating a Database object

method: new Database(?array $connection = null)

parameters:

• array $connection - Connection array containing the parameters required by the Database Engines like MySQL, SQLite ...

returns: Database object

example:

<?php
$db = new Database(
    [
        'type' => 'sqlite',
        'file' => 'posts.sqlite'
    ]
);

Connect to a Database Engine

method: connect(array $connection)

parameteres:

• array $connection

returns:

• true - If the the database engine was successfully connected
• false - If could not connect to the engine

example:

<?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 = [])

parameteres:

• string $sql,
• array $bind associative array to bind data in sql while preparing

returns:

• true or false - if the query is of the type UPDATE, INSERT and DELETE
• array - returns the result of the SELECT query

example:

<?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 = [])

parameteres:

• string $table - The table to be queried
• array $record - associative array of record

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]

returns:

• true - if the record was inserted
• false - record was not inserted

example:

<?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 = [])

parameteres:

• string $table
• array $record associated array only includes data to be updated

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' => [],

returns:

• true - if successful
• false - otherwise

example:

<?php
$db->update('users', 
    [
        'password' => $new_password,
    ],
    [
        'where' => 'email = :email',
        'bind'  => [
            'email' => $email
        ]
    ]
);

Removes a record from database

method: remove(string $table, array $options)

parameteres:

• string $table
• array $options contains where, limit or bind options If options are not provided following are the assumed defaults:

 [
     '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

example:

<?php
$db->remove(
    'users', 
    [
        'where' => 'email = :email', 
        'bind' => [
            'email' => $email
        ]
    ]
);

Retreives list of records

method: get(string $table, array $options = [])

parameteres:

• string $table
• array $options - Associative array containing where, order_by, limit and bind

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.

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:

array [row ...] containing the array of rows or null if not found

example:

<?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 = []);

parameteres:

• string $table name of the table e.g. $table = 'useres';
• array $options as explained in DatabaseEngineInterface::get

returns:

array containing the associative key=>value pairs of the row or null otherwise

example:

<?php
$last_post = $db->getFirst('posts', ['orderby' => 'date DESC']);

Database Models

NOTE: Currently Models only supports SQLite db

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.

Creating a model

example: Models\Users.php

<?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)

parameteres:

• array $connection - ['file' => $db_path . 'users.sqlite', 'table' => 'users']

returns:

Users model object

example:

<?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()

parameteres: none

returns:

string - pathname of the sqlite file the model is connected to

example:

<?php
$file =  $users->getDatabaseFile();

Prepares a schema of the datbase from model definition and returns it

method: prepareSchema()

parameteres: none

returns:

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.

example:

<?php
$schema = $users->prepareSchema();
echo $schema;

Deploy the schema

method: deploySchema(string $schema)

parameteres:

• string $schema - The schema to be deployed to the connected file

throws: Exception - in case of error

returns: nothing

example:

<?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 = [])

parameteres:

• array $record Asociative array of record,

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 - The data we need to bind to the :placeholders in $record

returns:

• true - if inserted the record successfully
• false - otherwise

example:

<?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.

method: insertOrUpdate(array $record, array $bind = [])

parameteres:

• array $record - Associative array represnting one record
• array $bind - The data we need to bind to the :placeholders in $record

returns:

• true - if inserted or updated the record successfully
• false - otherwise

example:

<?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 = [])

parameteres:

• array $record - Associative array represnting one record
• array $options - The where clause or the binding data etc.

returns:

• true - if updated the record successfully
• false - otherwise

example:

<?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)

parameteres:

• array $options - The where clause or the binding data etc.

returns:

• true - if removed the record successfully
• false - otherwise

example:

<?php
$users->remove([
    'where' => 'email = :email',
    'bind' => [
        'email' => $email
    ]
]);

Retrieve a list of records

method: get(array $options = [])

parameteres:

• array $options - The where clause or the binding data etc.

returns: array or records or null

example:

<?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 = [])

parameteres:

• array $options - The where clause or the binding data etc. this might include the order_by and limit parameters

returns:

• array - an associative array containing the record
• null - if could not find one

example:

<?php
$user = $users->getFirst();
$last_post = $posts->getFirst(['orderby' => 'date DESC']);

Verify if a record exists

method: has($options = [])

parameteres:

• array $options - The where clause or the binding data etc.

returns:

• true - if record exists
• false - otherwise

example:

<?php
$users->has(
    [
        'where' => 'email = :email',
        'bind' => [
            'email' =>$email,
        ]
    ]
);