Models & Database Access

To store and retrieve data for your application you can use part, or all, of the database utilities offered in Bedrock's model layer. Currently the model layer only supports MySQL databases, though more are planned. Custom data sources can be built as well.

Before we can do anything with a database, we must connect to it. The method used to connect to a database is the same regardless of the database platform you are using. In fact, you can even build in support for a database that isn't yet officially supported.

Database connections are represented with a Bedrock::Model::Database object. To initialize a new connection, simply create a new Database object instance passing the DSN or a Config object containing the needed properties.

Consider the following code, which initializes a connection to a MySQL database:

// Create a new database connection. 
$connection = new Bedrock_Model_Database('mysql://localhost:3600'); 
 
// Optional: Load all current tables and settings. 
$connection->load();

You may have noticed that on line 5 we have also made a call to Database::load() which will retrieve all details about the database. This includes any existing tables as well as any available details about the database (such as the database name, character set, description info, etc.). This step is not essential, but can be useful in applications that make use of this information.

At this point you should be connected to your database and are ready to start querying it for all sorts of exciting data. In the event that a connection to your database cannot be made, a Bedrock::Model::Exception will be thrown with a message describing the problem. To properly handle such a problem, consider creating your connection within a try-catch block that will catch the thrown exception:

try { 
    // Create a new database connection. 
    $connection = new Bedrock_Model_Database('mysql://localhost:3600'); 
 
    // Optional: Load all current tables and settings. 
    $connection->load(); 
} 
catch(Bedrock_Model_Exception $ex) { 
    echo 'A database connection could not be made: ' . $ex->getMessage(); 
}

Querying your database can be done by either executing SQL queries directly, or by using Bedrock's chainable query methods. Both approaches can be used separately or together, and will depend on personal preference as well as the specific needs of an application.

Bedrock offers the ability to query a database using a chain of method calls rather than constructing a complex SQL string. This provides the developer with additional syntactic sugar that is often easier to read and is particularly useful in cases where queries are constructed on-the-fly. It also allows the model layer to utilize built-in protections against SQL injection automatically. Additionally, it allows the model layer to handle any subtle differences with different supported databases.

Below is an example of a simple query for all users having the specified email address “info@bedrockframework.com”:

try { 
    // Connect to your database. 
    // ... 
 
    // Execute Query 
    $resultSet = Bedrock_Model_Query::from('users')->where('email', '=', 'info@bedrockframework.com')->execute(); 
 
    // Do something with the results. 
    // ... 
} 
catch(Bedrock_Model_Exception $ex) { 
    echo 'The query was not executed successfully: ' . $ex->getMessage(); 
}

As you may have guessed, each clause in a normal SQL statement is represented by its own method. In the example above, the WHERE clause is represented by Query::where() and the FROM clause is represented by Query::from(). Listed below are all the possible query methods and what they do.

As with chained-method queries, to query a database using plain old vanilla SQL you must first create a new Bedrock::Model::Query object. This object is used assemble and execute queries and contains a number of query methods for doing so. In this case, we are only concerned with Query::sql() which is the query method used to execute SQL directly:

try { 
    // Connect to your database. 
    // ... 
 
    // Create Query 
    $sql = 'SELECT * FROM users WHERE email = 'info@bedrockframework.com''; 
 
    // Execute Query 
    $resultSet = Bedrock_Model_Query::sql($sql)->execute(); 
 
    // Do something with the results. 
    // ... 
} 
catch(Bedrock_Model_Exception $ex) { 
    echo 'The query was not executed successfully: ' . $ex->getMessage(); 
}

As you can see in the code above, even when executing SQL directly, we can take advantage of the Query object's chainable methods. Almost every method in the Query class returns a reference to itself, meaning you do not need to wait until the next line to make another call to the same Query object. Note that the above code could have also been written like this:

// ... 
 
// Execute Query 
$query = Bedrock_Model_Query::sql($sql); 
$query->execute(); 
 
// ...

Sometimes it is more practical to create a container for related queries that are used often and in more than one place. This can be accomplished through the creation of custom Query objects that extend the Query class. This approach is recommended only for larger, more complex applications. Use in smaller applications typically creates more bloat reduced readability with little to no gain for the effort.

A common use for this approach is for handling user account related queries. In such a case, it would make sense to define some commonly used queries in a “users” Query object:

/** 
 * <... docblock ...> 
 */ 
class Application_Model_Query_Users extends Bedrock_Model_Query { 
    /** 
     * <... docblock ...> 
     */ 
    public static function withId($userId) { 
        Bedrock_Common_Logger::logEntry(); 
 
        try { 
            $result = self::from('users')->where('id', '=', $userId)->execute(); 
 
            Bedrock_Common_Logger::logExit(); 
            return $result; 
        } 
        catch(Exception $ex) { 
            Bedrock_Common_Logger::exception($ex); 
            Bedrock_Common_Logger::logExit(); 
        } 
    } 
}

This query could then be executed from anywhere in the application using this line:

$userData = Application_Model_Query_Users::withId(23);

For the sake of illustration, a simple single-line query has been used in this example. In practice, it would not be worth the overhead for such a simple query. However, when dealing with multi-line queries with one or more conditionals, the value of this approach becomes much more apparent.

When a query is executed, a Bedrock::Model::ResultSet is returned containing a collection of Bedrock::Model::Record objects representing each record that was returned. ResultSet objects can be traversed like arrays and can also be used in loops, making it easy to iterate over a collection of results.

In the example below, we query the database for all records in the users table and loop through the results to produce a list of usernames and email addresses.

// Query Database 
$results = Bedrock_Model_Query::from('users')->execute(); 
 
// Display List of Users 
echo '<ul>'; 
 
foreach($results as $record) { 
    echo '<li>' . $record->username . '(' . $record->email . ')</li>'; 
} 
 
echo '<ul>';

Record objects represent an actual row in the database and can be used to both read and write data. If associated records have been requested, a special column will be added for each corresponding table, and will point to a ResultSet containing the associated records.

// Query Database 
$results = Bedrock_Model_Query::from('users') 
    ->where('username', '=', 'bedrock')->execute(); 
 
$record = $results[0]; 
 
// Basic Record Contents (User Account) 
echo 'Username: ' . $record->username . "\n"; 
echo 'Email: ' . $record->email . "\n"; 
echo 'User ID: ' . $record->id . "\n"; 
 
// Associated Records (User's Posts) 
foreach($record->posts as $key => $post) { 
    echo 'Post #' . $key . ': ' . $post->title . "\n"; 
}

Bedrock's model layer supports the import and export of both table schemas and data. In both cases a variety of formats are supported including SQL, CSV, YAML, and XML. This makes all persistent data and its structure extremely portable, making migrations and backups a snap.

To import a single schema definition, make a call to Bedrock::Model::Table::importSchema(), passing the path to the source file and its format:

Bedrock_Model_Table::importSchema('/path/to/schema.sql', 
    Bedrock_Model_Table::FORMAT_TYPE_SQL);

If a database's entire collection of table schemas are defined in a single file, it can be imported passing the same parameters above to Bedrock::Model::Database::importTableSchemas().

To export a single table schema, make a call to Bedrock::Model::Table::exportSchema() on an existing Table instance, passing the path to the destination file and the format to use:

$usersTable->exportSchema('/path/to/schema.xml', 
    Bedrock_Model_Table::FORMAT_TYPE_XML);

To export all tables defined in a database, make a call to Bedrock::Model::Database::exportTableSchemas() on a valid Database instance, passing the same parameters as above.

To import table data, make a call to Bedrock::Model::Table::importData() on an existing Table instance, passing the path to the source file, its format, and whether or not the data should be appended to any existing data in the table (if false, existing data will be lost):

$usersTable->importData('/path/to/data.csv', 
    Bedrock_Model_Table::FORMAT_TYPE_CSV, false);

To import table data for all tables in a database using a single file, make a call to Bedrock::Model::Database::importTableData() on a valid Database instance, passing the same parameters as above.

To export table data, make a call to Bedrock::Model::Table::exportData() on an existing Table instance, passing the path to the destination file and the format to use:

$usersTable->exportData('/path/to/data.yaml', 
    Bedrock_Model_Table::FORMAT_TYPE_YAML);

To export all data for all tables in a database, call Bedrock::Model::Database::exportTableData() on a valid Database instance, passing the same parameters as above.