====== 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. ===== Making a Connection ===== 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 [[docs:appendix:glossary#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(); } ===== Executing Queries ===== 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. ==== Chaining Query Methods ==== 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 [[docs:appendix:glossary#syntactic_sugar|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()**''. [[#query_method_reference|Listed below]] are all the possible query methods and what they do. ==== Using SQL ==== 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(); // ... ==== Custom Query Objects ==== 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. === Example === 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. ==== Query Method Reference ==== ^ ''Query::from($table)'' ^^ | Specifies the primary table to be queried (excluding joins) || | ''$table'' | The name of the table to query from. | | || ^ ''Query::where($field, $operator, $value)'' ^^ | The equivalent to a SQL ''WHERE'' clause. || | ''$field'' | The field on which to apply the operator. | | ''$operator'' | The operator used to compare the field and value. | | ''$value'' | The value to compare to the field. | | || ^ ''Query::match($record)'' ^^ | Sets the query to retrieve all records that match the specified ''Record''. || | ''$record'' | The ''Bedrock::Model::Record'' object to match against. | | || ^ ''Query::limit($start, $count)'' ^^ | Applies a limit to the number of results returned, and where to start. || | ''$start'' | The nth record from which to start. | | ''$count'' | The total number of records to return. | | || ^ ''Query::sort($sortParams)'' ^^ | Specifies sort parameters to use. || | ''$sortParams'' | A string containing fields to sort by and sort direction.|| ===== Working With Result Sets & Records ===== 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 '