Cora's Database class

NOTE: IF YOU ARE GOING TO BE GRABBING CLASS RECORDS FROM A DATABASE, YOU SHOULD CONSIDER USING THE ADM ORM, NOT THIS LOWER LEVEL DATABASE CLASS.

In a nutshell, Cora's Database class acts as a Data Access Object. It offers you a simple way to interact with a database, without having to worry about implementation specific details for different database types. The ELI5 explanation goes like this:
Say you build an application that uses Database-A, and Database-A uses commands that look like the following:

SELECT first_name FROM users WHERE id=12;

However, one day your boss says to you that he wants you to switch over to using Database-B. Database-B however uses a different command syntax though that looks something like this:

db.users.find( { id: 12 }, { first_name: 1 } );

WHOA NELLY! If you haven't been using a Data Access Object, then the fact that this new database uses a different syntax could be devastating. You might literally have to rewrite HUGE portions of your application to make everything work with this new format. NOT GOOD! This is where a DAO comes in handy. Let's say you've written all your database queries in your application using a DAO and it has its own format that looks something like so:

$db ->select('first_name')
    ->from('users')
    ->where('id', 12)
    ->exec();

If your app is using this DAO, now when your boss tells you to start using database-B, all that you need to do is write translation instructions (aka an 'adaptor') for your DAO so it knows how to turn the above into the instuction format database-B uses (or more likely, someone else has already written an adaptor, can you just have to download it). In this way, Cora's Database class acts as an abstraction layer that helps separate your application from any one specific database implementation.

How it's Implemented

When you construct an SQL statement using the Database class' query building methods, the resulting query parameters are stored as raw data within the Database class (mostly stored in arrays). In order to take that raw data and then execute a query, you have to utilize an adaptor that extends the Database class and implements the exec() method. The adaptor is responsible for taking that raw data and translating it into the format required by its associated Database in order to execute the query.

Currently Supported Databases

Cora currently has adaptor classes for:

MySQL (Db_MySQL.php)

Introduction

Below is a basic example that grabs and echoes the name of all the users in a users table who's type is 'admin'.

// Create Database object with adaptor
$db = new \Cora\Db_MySQL();

// Create query
$db ->select('name')
    ->from('users')
    ->where('type', 'admin');

// Execute query and get returned result.
$query = $db->exec();

// Print all the names
foreach($query->fetchAll() as $user) {
    echo $user['name'];
}

As you probably see, the basic formula is you create an instance of a database adaptor (Db_MySQL extends Database), construct some query using the Database class' methods, then call the exec() function to execute the query and return any expected result. Query methods can be chained together as you see above too.

Security

The Database class and its query building methods are extremely powerful, and as such, there are dangerous scenarios of which you need to be aware! Column VALUES are sanitized to protect your app from SQL Injection attacks. However, COLUMN name parameters and OPERATOR parameters (=, >, <, IN, etc) are NOT sanitized!!! For this reason you should never under any circumstance pass in a value you receive from a user into one of those fields! Doing so will leave a huge security hole in your app that an attacker could take advantage of to destroy or compromise everything.

Below is a SAFE query:

// Create and execute query
$db ->select('name')
    ->from('users')
    ->where('name', $_POST['userName']);
$query = $db->exec();

Below is an EXTREMELY UNSAFE query:

// Create and execute query
$db ->select('name')
    ->from('users')
    ->where($_POST['column'], $_POST['value']);
$query = $db->exec();

Methods

Utility Methods

The following methods are available when creating any type of query:

getQuery() (returns the compiled query)
exec() (executes the query that's been constructed and calls Reset)
reset() (resets the Database class so it's ready for a new query)

SELECT Statements

The methods available when creating a select statement are:

UPDATE Statements

The methods available when creating an update statement are:

INSERT Statements

The methods available when creating an insert statement are:

DELETE Statements

The methods available when creating an delete statement are:

CREATE Statements

The methods available when creating an create statement are:

Advanced Examples

Creating a table:

$db ->create('locations')
            ->field('id', 'int', 'NOT NULL AUTO_INCREMENT')
            ->field('name', 'varchar(255)')
            ->field('address', 'varchar(255)')
            ->field('user_id', 'int')
            ->primaryKey('id')
            ->foreignKey('user_id', 'users', 'id');
$db->exec();

Inserting data:

$db ->insert('name, email, type')
    ->into('users')
    ->values([
        ['bob', "bob's@gmail.com", 'admin'],
        ['john', 'john@gmail.com', 'admin'],
        ['john', 'john@gmail.com', 'scrub'],
        ['sally', 'sally@yahoo.com', 'user'],
        ['susan', 'susan@aol.com', 'user']
    ])
    ->exec();

More advanced Where statement:

$db ->select('id')
    ->distinct()
    ->from('users')
    ->from('profile')
    ->where([
        ['created_time', '>=', '2016-01-01', 'OR'],
        ['name', 'LIKE', 's%']
    ])
    ->where([
        ['type', '=', 'admin', 'OR'],
        ['type', '=', 'moderator']
    ])
    ->exec();

Mega Query:

$tables = array('table1', 'table2', 'table3');
$fields = array('id', 'name', 'email');
$conditions = array(
    ['id', '>', '100', 'OR'],
    ['name', 'LIKE', '%s']
);

$groupBys = ['field1', 'field2', 'field3'];

$havings = array(
    ['amount', '>', '1000'],
    ['savings', '>', '100']
);

$orHavings = array(
    ['amount2', '>', '1000'],
    ['savings2', '>', '100']
);

$joinConditions = array(
    ['Orders.customerID', '=', 'Customers.customerID'],
    ['User.type', '=', 'Customer.type']
);


$db = new Cora\Db_MySQL();

echo $db->select($fields)
        ->from($tables)
        ->join('customers', $joinConditions, 'OUTER')
        ->where($conditions)
        ->orWhere($conditions)
        ->in('name', 'value1, value2, value3')
        ->in('name', 'SELECT * FROM users')
        ->in('type', $groupBys)
        ->groupBy($groupBys)
        ->having($havings)
        ->orHaving($orHavings)
        ->having($orHavings)
        ->orderBy('name', 'DESC')
        ->orderBy('type', 'ASC')
        ->limit(10)
        ->offset(20)
        ->getQuery();