9. DB

There are times in the application when you will need to be able to access the database connection, and deal with SQL directly. The DB package provides the API to do this. It is important to note that the database connection should never be accessed from within Controller/View code.

A connection to oracle will be established during user authentication. No database connection can be accessed until valid authentication is performed using the Session package.

An initial session is established using the LoginController. This session is subsequently verified on each request when the ApplicationController::_authenticate method executes before any action.

For the most part, the model code assumes it has a valid connection established (or the user will be redirected to authenticate).

The syntax for accessing the connection from within a model:

// instance methods use the _db property to access the connection
public function getDocs()
{
    ...
    $this->_db->query("SELECT * FROM documents");
    ...
}

// static methods must first get the db object instance
static public function findDocs()
{
    ...
    $db = BaseDB::getInstance();
    $db->query("SELECT * FROM documents");
    ...
}

The syntax for accessing the connection from withing a unit test:

// a connection is established when the code accesses the $this->db property
static public function testFindDocs()
{
    ...
    $this->db->query("SELECT * FROM documents");
    ...
}

Querying involves preparing a statement, binding the variables, and then executing that statement to get a result handle. There are a few API shortcuts for performing all of this in one straight shot, however we will start with the core DB API.

9.3.1 Prepared Statements

All database queries use prepared statements. This is preferred to direct execution because of our reliance on bind variables. Calling $db->prepare($sql) returns an DBStatement object.

...
$sql = "SELECT * FROM documents";
$stmt = $this->_db->prepare($sql);
$result = $stmt->execute();
...

9.3.2 Bind Variables

Once we have prepared a statement, we can now use our DBStatement object to bind variables to the statement. Once we have set all of our bind variables, we execute the statement using $stmt->execute()

...
$sql = "SELECT * 
          FROM documents 
         WHERE documentid=:id 
           AND is_active=:active";
$stmt = $this->_db->prepare();
$stmt->bind(':id', 123);
$stmt->bind(':active', 1);
$result = $stmt->execute();
...

9.3.3 Fluent Interfaces

In an effort to make more readable code, the DB methods provide support for a fluent interface syntax. Each method (prepare/bind/execute) returns an object that can be chained together to avoid redundant typing. This is the preferred way of executing this type of SQL.

...
$sql = "SELECT * 
          FROM documents 
         WHERE documentid=:id 
           AND is_active=:active";
$result = $this->_db->prepare()
                    ->bind(':id', 123)
                    ->bind(':active', 1)
                    ->execute();
...

9.3.4 PL/SQL Return Variables

There are times when you will need to get a return value from a stored procedure. This can be done by binding a null value to the bind variable along with a specified max size of BaseDB::PLSQL_SZ. After the sql is executed, you can access the return variable using $stmt->getSprocVar(':varName').

...
$stmt = $this->_db->prepare("BEGIN
                             :sort := md_utl.md_value_sorting_field(:id);
                             END;");
$stmt->bind(':id', $metadataId);
$stmt->bind(':sort', null, BaseDB::PLSQL_SZ);
$stmt->execute();

$sortFields = $stmt->getSprocVar(':sort');
...

In the case of SELECT statements, you will be left with a QueryResult object as a result of your $stmt->execute() statement. This can be iterated over using the $result->fetchRow() method.

...
$result = $stmt->execute();
while ($row = $result->fetchRow()) {
    $docId = $row['DOCUMENTID'];
}
...

Now writing all those redundant prepare, bind, and executes could possibly drive you nuts. Luckily there is salvation in the form of some shortcut methods that perform all the above under the hood so that you do not have to.

9.6.1 query()

This method provides a quick way to prepare/bind/execute. The 2nd argument is an optional array of bind variables.

...
$sql = "SELECT * FROM documents WHERE id=:id";
$result = $this->_db->query($sql, array(':id' => 123));
...

9.6.2 getOne()

This method provides a quick way to retrieve a single column from a single row. The 2nd argument is an optional array of bind variables.

...
$sql = "SELECT COUNT(1) FROM documents WHERE name=:name";
$docCount = $this->_db->getOne($sql, array(':name' => 'Document Name'));
...

9.6.3 getRow()

This method provides a quick way to retrieve a single row. The 2nd argument is an optional array of bind variables.

...
$sql = "SELECT * FROM documents WHERE id=:id";
$doc = $this->_db->getRow($sql, array(':id' => 123));
...

9.6.4 getAll()

This method provides a quick way to retrieve all the rows. The 2nd argument is an optional array of bind variables.

...
$sql = "SELECT * FROM documents";
$doc = $this->_db->getAll($sql);
...

There are times when you'll want to group a series of database changes together in a way that either all changes are applied or none of the changes are applied. In these cases, you'll want to use a transaction to ensure the integrity of your operation.

Transactions should be performed in the model code. The two methods used are beginTransaction() and endTransaction(). Passing true as an argument to endTransaction will commit the data while padding false will rollback.

...
public function moveRecords()
{
    // use exception handling to check for errors
    try {
        $this->db->beginTransaction();
        $this->db->query("INSERT INTO table_2 SELECT * FROM table_1");
        $this->db->query("DELETE FROM table_1");
        $this->db->endTransaction(true);

    // if any errors happen, rollback the transaction 
    } catch (Exception $e) {
        $this->db->endTransaction(false);
    }
}
...