PX:DBObject

From Peroxide

Contents 1 Introduction 2 A simple example 2.1 Deriving from DBObject and providing members 2.2 Adding setter and getter methods 2.3 Defining column data types 3 Mapping to tables 3.1 Table naming conventions 3.2 Using an existing table 3.3 Creating a table from scratch 4 Object Identity 4.1 Changing ID columns when using existing tables 5 Querying the database 5.1 Getting an object by it's ID 5.2 Querying objects by their properties 5.3 Sorting and Paging

Introduction

perOXide features a quite powerful, yet extremely simple to use object-relational mapping support. Object-relational mapping allows you to access the database in an uniform and abstract manner, therefore aiding you in supporting multiple target databases. You write all the same code, no matter if you work with a MySQL or a SQL Server database. You can either create your own tables or access existing ones (with some limitations).

A simple example

Lets assume we want to create a database for electronic parts. First off, we would need to create a table that holds the parts information. To keep it simple for starters, we assume that a part has a name, a description and a price.

Deriving from DBObject and providing members

To make this work, we will create a new class, which we derive from DBObject which is the generic base class of all database objects. Then we add three members to that class, which will make up the "columns" of our table.

Adding setter and getter methods

It is a good practice to make members private and declare accessor methods for setting and retrieving the member's values. DBObject expects any member that should be persisted to the database to have a getter and setter accessor method, so we will add these right now:

Defining column data types

We are nearly done. All that's left to make the example functional is, that we need to tell perOXide about the types that should be used to reflect the members inside the database. This is done, by providing a function called tableDefinition which returns a string providing information about the columns and their types.

Since every database offers slightly different types, perOXide offers a database agnostic set of data types that will be mapped to the closest matching type available on the used database. The following table shows the provided types:

Type	Description
C	Varchar, capped to 255 characters
X	Larger varchar, capped to 4000 characters (to be compatible with Oracle).
XL	For Oracle, returns CLOB, otherwise the largest varchar size.
C2	Multibyte varchar
X2	Multibyte varchar (largest size)
B	BLOB (binary large object)
D	Date (some databases do not support this, and we return a datetime type)
T	Datetime or Timestamp
L	Integer field suitable for storing booleans (0 or 1)
I	Integer (mapped to I4)
I1	1-byte integer
I2	2-byte integer
I4	4-byte integer
I8	8-byte integer
F	Floating point number
N	Numeric or decimal number

Mapping to tables

Table naming conventions

perOXide heavily relies on naming conventions for the database abstraction. We try to follow the DRY principle as much as possible to keep the implementation simple and readable. By default, the table is named like the class in lowercase prefixed with a string that can be configured in SpawnConfig.php. So the table name for ElectronicPart would be myprefix_electronicpart. The prefix is useful if your application has to share the database with other applications and you want to avoid table naming conflicts. The column names are named like the members of your object, e.g. if you have a member named description with a getter and setter method, then column will be named description as well. The following table shows the used naming conventions. Print it and stick it somewhere near your monitor.

Element	Naming convention
Table name	derived from class name plus a configurable prefix, can be overridden using the tableName function
Column name	derived from the member name
ID field	named id by default, can be overridden by using the idFieldName function

Using an existing table

There might be cases when you already have an existing table which you want to reuse. Let's assume there already is a table named parts in your database which has id, name, description and price columns. You can tell perOXide to use a different table name by overriding the tableName function in ElectronicPart. This function returns the name of the table to which the object is mapped.

This will make perOXide use the table parts instead of myprefix_electronicpart. To map to the columns of the existing table you have to name the members and their getters and setters accordingly, so they will match the names of the columns in the existing table.

Creating a table from scratch

Most of the time when you don't work with predefined tables, you will want to create a table that matches your defined object. In our case, we want to create a table for our ElectronicPart class. The easiest way to do this, is to create an instance of the object and then call the createTable function.

This will create the table and all its columns in the database. If you specified a different table name using the tableName function, this will be honored.

Object Identity

All objects have to have a single field which can be used to uniquely identify the object. perOXide does not support composite keys, however this isn't a problem in the vast majority of the applications we have written so far. All objects that derive from DBObject inherit the methods getID and setID which can be used to get and set the unique identity of the object. By default this maps to a 32-bit integer field named id which is part of the object's mapped table.

When saving new objects, perOXide will automatically create a locally unique ID for each object, so you don't have to come up with one on your own. We will cover this later when discussing about storing objects.

Changing ID columns when using existing tables

When using existing tables, you might want to change the name of the id column to something else. Lets assume the ID field of the parts table mentioned above is partId instead of id. In this case you can override the function idFieldName to let perOXide know about this:

Querying the database

All queries on perOXide controlled objects is done using an object oriented interface. perOXide will create the necessary SQL for you and make sure it fits the database that is working under the hood. Also perOXide takes care about SQL injection so you don't have to manually check or escape all the parameters that you get from the HTTP request - perOXide will make sure that all query parameters are correctly escaped before wiring them into the SQL.

Getting an object by it's ID

In many cases you will have the object's ID and want to load the object's contents from the database (e.g. you want to display a single electronic part). This can be easily achieved by creating an object instance, setting the ID field and then calling the load method which will load the object's properties from the database and put them right into the object instance that load was called upon. The load method will create some SQL like:

Here is some example code, showing the usage of the load method.

This is a very easy way of retrieving data from the database. Note, that even if you use a different column name for the object's ID column, you still have to call the setID method. perOXide will make sure the value is mapped to the correct column.

Querying objects by their properties

Getting objects by their ID is easy, however in many cases you will want some more elaborate possiblities to query objects. Assume you have the name of a part and want to find all parts with that name. To achieve this, you can use perOXide's query by example facility, which is suitable for the majority of all queries you will possibly encounter. The idea behind it is to set an object up so it just looks like the object you are looking for. In our case, we look for an electronic part with a certain name.

Now that we have set the object up, all that's left is calling the qbe (Query By Example) function, which is inherited from DBObject. This function will look at the object that it is called upon, will create appropriate SQL, retrieve the results from the database and transform it back into objects which will be returned as an array of objects:

Of course you can set more than one property. Assume the ElectronicPart also has a category in which the part is listed.

The more properties of the object you set, the more constraints you add to the query. The above example would yield the following SQL query:

Sorting and Paging

For displaying results on a web site it is impractical for a number of reasons to fetch the whole database contents and simply print them on a single page. The usual approach is to distribute the results of a query over multiple pages. perOXide offers a function to do just that - qbeRanged. This function does exactly the same as qbe but it adds the possibility to limit result sets.

This is, where database independent code becomes king, as there are different ways of achieving the paging depending on your database. For MySQL this would create SQL something like:

For other databases, which do not support the LIMIT clause, perOXide will emulate the desired behaviour. This way, you don't have to worry about your code working on any supported database.

Another important capability is the sorting of the results. You can use qbeRanged as well for sorting. Due to historic reasons there are some idiosyncracies regarding the parameter order which will be addressed in a future version of perOXide. The following code would select the first 20 parts in a certain category and order it by price ascending then name, descending: