Database Interface without SQL Object Interaction

DatabaseInterface2.php

<?php
/**
 * Database Interface
 *
 * @package   Molajo
 * @copyright 2013 Amy Stephen. All rights reserved.
 * @license   http://www.opensource.org/licenses/mit-license.html MIT License
 */
namespace Molajo\Database\Api;

defined('MOLAJO') or die;

use Molajo\Database\Exception\DatabaseException;

/**
 * Database Interface
 *
 * @package   Molajo
 * @license   http://www.opensource.org/licenses/mit-license.html MIT License
 * @copyright 2013 Amy Stephen. All rights reserved.
 * @since     1.0
 */
interface DatabaseInterface
{
    /**
     * Connect to the Database, passing through credentials and other data needed to secure a connection
     *
     *  $options                    = array();
     *  $options['db_type']         = $db_type;
     *  $options['db_host']         = $db_host;
     *  $options['db_port']         = $db_port;
     *  $options['db_socket']       = $db_socket;
     *  $options['db_user']         = $db_user;
     *  $options['db_password']     = $db_password;
     *  $options['db_name']         = $db;
     *  $options['db_prefix']       = $db_prefix;
     *  $options['process_plugins'] = $process_plugins;
     *  $options['select']          = true;
     *
     *  try {
     *      $adapter_handler = $this->getAdapterHandler('Adaptername');
     *      $this->adapter = $this->getAdapter($adapter_handler,  $options);
     *
     *  } catch (Exception $e) {
     *      throw new DatabaseException ('Database Adapter instantiation failed.' . $e->getMessage());
     *  }
     *
     * @param   array  $options
     *
     * @return  $this
     * @since   1.0
     * @throws  DatabaseException
     */
    public function connect($options = array());

    /**
     * Retrieves the PHP date format compliant with the database driver
     *
     *  $date_format = $this->adapter->getDateFormat();
     *
     * @return  string
     * @since   1.0
     * @throws  DatabaseException
     */
    public function getDateFormat();

    /**
     * Returns a value for null date compliant with the database driver
     *
     *  $null_date = $this->adapter->getNullDate();
     *
     * @return  string
     * @since   1.0
     * @throws  DatabaseException
     */
    public function getNullDate();

    /**
     * Returns the value sent in, quoted for use in a database query
     *
     *  $this->adapter->quote($string);
     *
     * @param   string $value
     *
     * @return  mixed
     * @since   1.0
     * @throws  DatabaseException
     */
    public function quote($value);

    /**
     * Returns the name sent in (ex. table name or column name), quoted for use in a database query
     *
     *  $this->adapter->quoteName($field_name);
     *
     * @param   string $name
     *
     * @return  string
     * @since   1.0
     * @throws  DatabaseException
     */
    public function quoteName($name);

    /**
     * Escapes value sent in for use in a database query
     *
     *  $this->adapter->escape($text);
     *
     * @param   string  $text
     *
     * @return  string
     * @since   1.0
     * @throws  DatabaseException
     */
    public function escape($text);

    /**
     * Sends SQL to the database, returning a single data value as the result
     *
     * $results = $this->adapter->loadResult($sql);
     * echo $results->title;
     *
     * @param   string   $sql
     *
     * @return  object
     * @since   1.0
     * @throws  DatabaseException
     */
    public function loadResult($sql);

    /**
     * Sends the SQL request to the database, returning an array of rows, each row of which is an object. 
     *  $Offset represents the row number in the result set from which to start. 
     *  $Limit specifies the maximum number of rows to be returned.
     *
     * $results = $this->adapter->loadObjectList($sql, $offset, $limit);
     *
     * if (count($results) > 0) {
     *      foreach ($results as $row) {
     *          $title = $results->title;
     *          $author = $results->author;
     *          // etc.
     *      }
     * }
     *
     * @param   string    $sql
     * @param   null|int  $offset
     * @param   null|int  $limit
     *
     * @return  object
     * @since   1.0
     * @throws  DatabaseException
     */
    public function loadObjectList($sql, $offset = null, $limit = null);

    /**
     * Execute $sql as a database query. Can used to select data, create tables, insert, update, delete data, etc.
     *
     * $results = $this->adapter->execute($sql);
     *
     * @param   null|string $sql
     *
     * @return  object
     * @since   1.0
     * @throws  DatabaseException
     */
    public function execute($sql);

    /**
     * Returns the primary key following insert
     *
     * $sql = 'INSERT (x, y, z) INTO $__table VALUES (1, 2, 3)';
     * $results = $this->adapter->execute($sql);
     *
     * echo $this->adapter->getInsertId();
     *
     * @return  integer
     * @since   1.0
     * @throws  DatabaseException
     */
    public function getInsertId();

    /**
     * Disconnects from Database and removes the database connection 
     *
     * echo $this->adapter->disconnect();
     *
     * @return  $this
     * @since   1.0
     * @throws  DatabaseException
     */
    public function disconnect();
}

How about PDO?

http://www.php.net/manual/en/intro.pdo.php

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.

@kinncj Thanks, I have not looked at that, yet. After a brief look, I see it's database specific, I'll look more closely and see if there is an abstracted layer. It still says experimental, too, but I do agree with the notion that if PHP already has an abstracted interface, that's the way to go.

@AmyStephen by SRP, I mean Single responsibility principle - this interface handles more than a single thing:

• WHAT has to be sent to the DB (getDateFormat, getNullDate, quote, quoteName, escape)
• HOW it is sent to the db (execute)
• HOW it is retrieved from the DB (loadResult, loadObjectList, getInsertId)
• HOW to connect to the db (connect, disconnect)

All these are non-homogeneous tasks that should be moved to different classes in my opinion.

I generally tend to agree with @kinncg here - PDO does the job and does it well, especially the escaping/quoting stuff (we don't manually do that in 2013, it's dangerous!).

You may look into the DBAL if you want to look into something that tries to normalize the difference across DB vendors, but it's quite a monster for some good reasons. We have a platform subsystem that generates vendor-specific SQL depending on the current connection driver.

@Ocramius - thanks for the SRP explanation, makes good sense. (The execute could also retrieve data, depending on how it is used.) I think it's also important to keep the Interface simple. It is basically a way for projects to build in a way to bring in other package from other projects -- so, it's just a handler, not a "new" solution. So, have to think on that.

Backing up a second, though, the point of FIG, and, in this case, proposing interfaces to a database is to address interoperability between project packages.

Assuming each project has already has a database package, (and most have some type of SQL generation tool) then the question is how can an Interface approach help the projects take that first step towards interoperability.

In a sense, we are retracing the steps projects took. There are layers to these solutions and we want to rediscover the layers in the database solutions area -- so that we can find common ground between these projects -- and then take those steps forward together building interfaces for the more sophisticated, higher level elements (like the SQL generation).

In this sense, it is a different question than to ask "what is the best solution", we are recognizing the reality of where projects are -- trying to bridge between existing solutions using interfaces -- and it's likely then that the stronger solutions will become more prominent and "the standard."

It seems to me maybe PDO answers the question "what is best" (assuming new development today) -- not "where are we all collectively at" -- accepting what is already in place as "here's where we get started."

You might think of it as an "entry point" for something like Doctrine, too. What's the easiest way to define an Interface for Doctrine so that it can be plugged into existing applications (WITHOUT CHANGING the application). Then, a roadmap emerges for how to get more out of those higher end ORM solutions.

Relevant work from FIG membership:

Doctrine Connection has prepare, query, quote, exec, lastInsertId, beginTransaction, commit, rollBack, errorCode, ErrorInfo

zf2 Database Adapters. The ConnectionInterface has connect, isConnected, disconnect, beginTransaction, commit, rollback, execute, getLastGenerateValue

Laravel The ConnectionInterface has selectOne, select, insert, update, delete, statement, transaction

Lithium Abstract handler for databases encoding, error, execute, insertId, connect, disconnect, name (escaping), value (escaping), create, read, queryExport, update, delete,