Database & Query Building

The database files are located in /GardenRoot/library/database, and are as follows:

class.database.php: The main database file that is included by the Garden bootstrapper.

class.databasestructure.php: A supplemental implementation of the database class specific to altering table (and view) structure in the database. This file is included on-demand.

class.dataset.php: The dataset class allows you to traverse, manipulate, and return data objects or arrays.

class.generic.driver.php: An abstract implementation of all properties and methods used to build and execute database queries.

class.generic.structure.php: An abstract implementation of all properties and methods used to build and execute database queries specific to altering database (and view) structure.

class.mysql.driver.php: A MySQL-specific implementation of the class.generic.driver.php abstract class. This class overrides the generic class where necessary in order to perform functions specific to MySQL.

class.mysql.structure.php: A MySQL-specific implementation of the class.generic.structure.php abstract class. This class overrides the generic class where necessary in order to perform functions related to altering database (and view) structure specific to MySQL.

Putting it all Together

As you can likely imagine, the MySQL-specific files are just one implementation of the generic driver and structure. Theoretically I could create other database-specific files and use those instead. When I first started working on the classes, for example, I created two other implementations: SQLite and Microsoft SQL Server. MySQL is the only implementation planned for launch, but others may come afterwards. With this in mind, the class.database.php file contains the following:

// Include the base/generic driver and dataset classes
include(PATH_LIBRARY . DS . 'database' . DS . 'class.generic.driver.php');
include(PATH_LIBRARY . DS . 'database' . DS . 'class.dataset.php');

// Include the selected driver class
$DatabaseEngine = $Config->Get('Database.Engine');
include(PATH_LIBRARY . DS . 'database' . DS . 'class.'.strtolower($DatabaseEngine).'.driver.php');

// Create a standard database class extended from the selected driver.
eval('class Database extends '.$DatabaseEngine.'Driver {}');

So, as you can see, the first thing I do is include the generic driver and the dataset classes. These are required in order to perform any database requests. Next up I grab the defined database engine from the configuration (in it's current state: MySQL), and include the driver for that class.

Finally, I create a class simply called "Database" that extends the driver-specific class. So, no matter what driver is implemented, you can always get at your database class by instantiating a new Database();

Using the Database class

Note: The Database class uses PDO to query the database. This is never apparent outside of the class, but is a point of interest to some.

The database class allows you to do everything from querying with a straight SQL string, to building a sql string and executing it with various Database methods, to one-line convenience methods that return a dataset with a single line of code. My main goal was to add flexibility. Let's start with an example of building a simple select statement. Imagine you wanted to use the database class to query the following information:

select Name
   ,Color
   ,MaxSpeed
from Car
where MaxSpeed > 80;

In it's most simple form, the Database class could be used to retrieve this information by placing that SQL into a $Sql variable and calling the following:

$DataSet = $Database->Query($Sql);

However, this is not really the preferred method. After all, what happens down the road when we implement a new database class with a different driver that doesn't support the exact same syntax as MySQL? So, let's build the same query using the Database class' various query building methods:

$Database->Select('Name, Color, MaxSpeed');
$Database->From('Car');
$Database->Where('MaxSpeed >', '80');
$DataSet = $Database->Get();

Fairly simple, but the Database query building methods also implement chaining. So, this query could have just as easily been written in the following manner:

$DataSet = $Database->Select('Name, Color, MaxSpeed')->From('Car')->Where('MaxSpeed >', '80')->Get();

Neat! But the Database class also has a bunch of different convenience methods. So, let's try building the same query using Database::GetWhere():

$DataSet = $Database->GetWhere('Car', array('MaxSpeed >' => '80'));

OK, so we can retrieve data from the database in various different ways. Let's try a more complex example with some joins. How about this:

select Car.Name
   ,Car.Color
   ,Car.MaxSpeed
   ,Brand.Name as Brand
   ,Engine.Name as Engine
from Car
inner join Brand
   on Car.BrandID = Brand.BrandID
inner join Engine
   on Car.EngineID = Engine.EngineID
where Car.MaxSpeed > 80;

Piece of cake:

$DataSet = $Database->Select('Car.Name, Car.Color, Car.MaxSpeed')
    ->Select('Brand.Name', '', 'Brand')
    ->Select('Engine.Name', '', 'Engine')
    ->From('Car')
    ->Join('Brand', 'Car.BrandID = Brand.BrandID')
    ->Join('Engine', 'Car.EngineID = Engine.EngineID')
    ->Where('Car.MaxSpeed >', '80')
    ->Get();

But that's a bit verbose. How about if we used aliases instead, like this:

select c.Name
   ,c.Color
   ,c.MaxSpeed
   ,b.Name as Brand
   ,e.Name as Engine
from Car c
inner join Brand b
   on c.BrandID = b.BrandID
inner join Engine e
   on c.EngineID = e.EngineID
where c.MaxSpeed > 80;

Not a problem:

$DataSet = $Database->Select('c.Name, c.Color, c.MaxSpeed')
    ->Select('b.Name', '', 'Brand')
    ->Select('e.Name', '', 'Engine')
    ->From('Car c')
    ->Join('Brand b', 'c.BrandID = b.BrandID')
    ->Join('Engine e', 'c.EngineID = e.EngineID')
    ->Where('c.MaxSpeed >', '80')
    ->Get();

And how about if we added some more conditions to the where clause? You could continue to add lines to the query:

// ...
$Database->Where('c.MaxSpeed >', '80');
$Database->Where('c.Color', 'Blue');

Or you could simply manipulate your original where definition to use an associative array of conditions:

// ...
$Database->Where(array('c.MaxSpeed >' => '80', 'c.Color' => 'Blue'));

You might be wondering how the database class handles database table prefixes. For example, in Vanilla 1, all of the vanilla-related tables were prefixed with "LUM_". So, what if our example tables had the LUM_ prefix? How would that change our Database class' method calls?

select c.Name
   ,c.Color
   ,c.MaxSpeed
   ,b.Name as Brand
   ,e.Name as Engine
from LUM_Car c
inner join LUM_Brand b
   on c.BrandID = b.BrandID
inner join LUM_Engine e
   on c.EngineID = e.EngineID
where c.MaxSpeed > 80;

The simple answer is that it wouldn't. You'd still specify all of the queries in the ways I've defined above. The database class has a "DatabasePrefix" property that is defined by the Garden bootstrapper (and can be changed at any time on the fly). You never need to concern yourself with database table prefixes. The Database class will search and prepend them where necessary as queries are built.

Connection

One final thing to note is that the database class lazy-loads the database connection. This means that there are no connections to the database opened until you actually run a query. So, if a page doesn't require any database access, the database class will never open a connection unnecessarily.

DataSets

In all of the examples above, the database queries have resulted in returning a variable I've called $DataSet. This $DataSet variable is actually an object instantiation of the DataSet class in the class.dataset.php file discussed above. The DataSet class is used to traverse the dataset that was created as a result of the query, and return the rows of data in either associative array or object format. Once again, flexibility is the purpose. Let's see some examples of what we can do with a DataSet object:

// See how many rows were returned by the query:
$NumRows = $DataSet->NumRows();

// Get the first row in the dataset:
$FirstRow = $DataSet->FirstRow();
// And do something with it:
print_r($FirstRow);
// Would print:
stdClass Object
(
    [Name] => 'Civic'
    [Color] => 'White'
    [MaxSpeed] => '120'
    [Brand] => 'Honda'
    [Engine] => 'Sad'
)
// Or, get the first row as an associative array, instead:
$FirstRow = $DataSet->FirstRow('', DATASET_TYPE_ARRAY);
// And do something with it:
print_r($FirstRow);
// Would print:
Array
(
    [Name] => 'Civic'
    [Color] => 'White'
    [MaxSpeed] => '120'
    [Brand] => 'Honda'
    [Engine] => 'Sad'
)

And for one more example on using DataSets, here's how one might decide to traverse a set of data:

<ul class="Cars">
<?php foreach ($DataSet->Result() as $Car) { ?>
   <li>
      <h3><php echo $Car->Name; ?></h3>
      This car is <?php echo $Car->Color; ?>,
      it has a <?php echo $Car->Engine; ?> engine,
      and it goes <?php echo $Car->MaxSpeed; ?>KM/h
   </li>
<?php } ?>
</ul>

Powered by Vanilla