Database Handler

:: Background ::

The Harmoni DB Handler offers a way to interface with databases of different kinds using identical PHP code. This is something that is not found any any other database wrapper out there. Many other wrappers claim to be database-independent, but the reality is that every database has its little quirks. While most claim to conform to ANSI SQL, this is only the truth on a very basic level. Any database will support the statement:

INSERT INTO table (id,name,email) VALUES (3,"John","john@company.com")

While this is all fine and good for the simplest of applications, our work demands much more complex queries involving multiple JOIN clauses of various types, etcetera, etcetera. Unfortunately there is great variation among different database back-ends when it comes to complex queries.

The Harmoni DB Handler solves this problem with but one minor setback: You can't use data-base specific functions (ie, a function only offered by one DB type). But, you can promise your clients a high level of interoperability and ease of integration. Why limit your target audience to only those that use MySQL when you could target MySQL, Oracle, Microsoft SQL Server and others?

:: How we do it ::

The DB Handler employs the use of "query objects" instead of having the programmer actually write SQL statements like every other wrapper we've come accross.

For any query to the database, you instantiate an object and set up your query through class functions. All the query information is stored in a universal format in the query object until told to send to a specific database, at which point database-specific SQL is generated. This allows you to write your program even before you have decided what back-end to use. Cool.

4.1.2 Setting up a Connection

In order to be able to operate on a database first you need to have a "Database Handler". What is a DataBase Handler?. It's the service provided by Harmoni that first translates your commands into the language of the database you use and then it actually executes the command. So as its name suggests, a "DataBase Handler" is the tool you use to "handle" your database. And this is the crucial layer of abstraction that enables the user to use the same PHP code for any of the supported databases ( MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and OKI's SQL OSID)

So, a DataBase Handler is created using the following code:


$dbHandler =& Services::getService("DBHandler");

The "Services" class is static, which means that no object of the class needs to be instantiated to access the methods declared in the class. Therefore, using the :: token we can call the "getService("DBHandler") method from the Services class without any instantiated object, which returns by reference the DataBase Handler. The reference is then stored in the variable "$dbHandler".

Ok, so now that you have the precious DataBase Handler in your possession, what are you going to do with it? Well, what a DataBase Handler needs is a DataBase to handle, so we need to specify what database he should operate on. Asssuming that we have a MySQL database, this is done through the following code:


$dbIndex = $dbHandler->addDatabase(
        new MySQLDatabase(
            "my.host.com",
            "myDatabaseName",
            "myUser",
            "myPass")
        );

Now that our DataBase Handler knows what our DataBase is and where it is, we need to tell it to open a connection to it. No commands can be passed to the DataBase unless our DataBase Handler has opened a connection to it. So, the connection is opened through:


$dbHandler->connect($dbIndex);

And equivalently, if you want to disconnect from the database you simply do:


$dbHandler->disconnect($dbIndex);

Great! Now we have added a database to our DataBase Handler. But what is the "$dbIndex" variable that we have been using? The answer has to do with how a DataBase Handler is created. The DBHandler class stores the Database that the user adds in an array. The purpose of this is that your DataBase Handler can "handle" more than one database. So the "$dbIndex" is the index of the array slot that your DataBase is stored. And if the database we just added is the first one, the variable $dbIndex has the value "0".

Now if we want to add another database(let's say this time is a PostgreSQL) to our DataBase Handler we just say:


$dbIndex2 = $dbHandler->addDatabase(
        new PostgreSQLDatabase(
            "my.host.com",
            "myDatabaseName",
            "myUser",
            "myPass")
        );

where "$dbIndex2" should have the value 1 if this is the second database we add to our DataBase Handler

NOTE

Don't forget that the counting of the array slots begins from 0. So the first slot of the array has index 0, the second slot has index 1, and so on...

Now in order to connect to the PostgreSQLDatabase we use:


$dbHandler->connect($dbIndex2);

That's it! Your database (which is stored in the variable $dbHandler) can operate on a MySQLDatabase and on a PostgreSQLDatabase which have index $dbIndex and $dbIndex2 respectively.

Now let's look at a thorough example:

Example


$dbHandler =& Services::getService("DBHandler");  //store the DataBase Handler



//Add a database to the DBHandler (in this case MySQL)

$dbIndex = $dbHandler->addDatabase(

new MySQLDatabase(

"my.host.com",

"myDatabaseName",

"myUser",

"myPass")

);



//Connect to it

$dbHandler->connect($dbIndex);

// you could also use $dbHandler->pConnect($dbIndex);

// for a persistent connection.



if ($dbHandler->isConnected($dbIndex)) print "We're connected!";

else print "Connection failed";



//Add a second DataBase

$dbIndex2 = $dbHandler->addDatabase(

new PostgreSQLDatabase(

"my.host.com",

"myDatabaseName",

"myUser",

"myPass")

);

//Connect to it

$dbHandler->connect($dbIndex2);



//Disconnect from both of them

$dbHandler->disconnect($dbIndex); 

$dbHandler->disconnect($dbIndex2);

4.1.3 Select Queries

Ok, so now we know how to make a DataBase Handler and how to connect to one (or more) databases. What then? How do we pass the appropriate queries to the database? How can we retrieve information from the database? Well, first we have to instantiate the appropriate query, in this case a SELECT query.

So, we create a Query through:


$query =& new SelectQuery;

The variable "$query" can now call any of the SelectQuery methods to formulate the appropriate query.

For example:

Example


$query =& new SelectQuery; //Create the SelectQuery



$query->addTable("table1"); //Set the table from which the SelectQuery will select info

$query->addColumn("column1");//Add a first column to the SelectQuery 

$query->addColumn("column2");//Add another column to the SelectQuery 



//...and execute the query through our dbHandler on the MySQL Database

$result =& $dbHandler->query($query, $dbIndex);

The SQL query would look like:

SELECT column1, column2 FROM table1

But Queries can be much more complicated than this. Does Harmoni provide support for more restrictive Select Queries? Of course it does! Currently, with a Harmoni SelectQuery you can have WHERE clauses, GROUP BY and HAVING clauses, ORDER BY clauses and any type of JOIN between tables. So let's look at a slightly more complicated version of the previous example:

Example


$query =& new SelectQuery; //Create the SelectQuery



$query->addTable("table1"); //Set the table from which the SelectQuery will select info

$query->addTable("table2", INNER_JOIN);//Inner join "table2" with the previous

$query->addColumn("table1.column1");//Add a first column to the SelectQuery 

$query->addColumn("table1.column2", "anAlias");//Add another column to the SelectQuery with the alias "anAlias"

$query->addWhere("table1.column1 = 'jones'"); //A WHERE clause with "column1 = 'jones'

$query->addWhere("table1.column1 = 'brown'", _OR); //Another WHERE clause connected with the OR operator

$query->addOrderBy("table1.column2");   //A GROUP BY clause



//...and execute the query through our dbHandler on the MySQL Database

$result =& $dbHandler->query($query, $dbIndex);

And the SQL code of this example would look like:

SELECT
    table1.column1,
    table1.column2 AS anAlias
FROM
    table1
        INNER JOIN
    table2
WHERE
    table1.column1 = 'jones'
        OR
    table1.column1 = 'brown'
ORDER BY
    table1.column2 ASC

Great! Now we can make some serious use of our Database. How can we see what our SELECT query produced though? Don't forget that when we pass the query to the database through the code:


$result =& $dbHandler->query($query, $dbIndex);

the result of the query is saved in the variable $result. How can we use the result? Take a look at the following example:

Example




..<previous code goes here>..



//...and execute the query through our dbHandler on the MySQL Database

$result =& $dbHandler->query($query, $dbIndex);





print "Our query has ". $result->getNumberOfRows() . "rows!\n";



while ($result->hasMoreRows()) {

    $array = $result->getCurrentRow();

    



    print "Result row: column1 = " . $array['column1'] . ", column2 AS anAlias: " .

                $array['anAlias'] . "\n";



    $result->advanceRow();



}

And the output of this might look like:

Our query has 3 rows!
Result row: column1 = jones, anAlias = franky
Result row: column1 = brown, anAlias = fred
Result row: column1 = jones, anAlias = george

And in order to give you an idea of how complicated a SELECT Query can be take a look at the following example:

Example


$dbHandler =& Services::requireService("DBHandler");

$query =& new SelectQuery();



//this is just a string

$db = $this->_sharedDB.".";



// set the columns to select

// these are columns related to the main group

$query->addColumn("groups_id", "subgroup0_id", $db."subgroup0");

$query->addColumn("groups_display_name", "display_name", $db."subgroup0");

$query->addColumn("groups_description", "group_description", $db."subgroup0");

$query->addColumn("type_domain", "domain", $db."type");

$query->addColumn("type_authority", "authority", $db."type");

$query->addColumn("type_keyword", "keyword", $db."type");

$query->addColumn("type_description", "type_description", $db."type");



// set the tables

$query->addTable($db."groups", NO_JOIN, "", "subgroup0");

$joinc = $db."subgroup0.fk_type = ".$db."type.type_id";

$query->addTable($db."type", INNER_JOIN, $joinc);

$joinc = $db."subgroup0.groups_id = ".$db."subgroup1.fk_parent";

$query->addTable($db."j_groups_groups", LEFT_JOIN, $joinc, "subgroup1");

$query->addColumn("fk_child", "subgroup".($level+1)."_id", "subgroup1");



// now left join with itself.

// maximum number of joins is 31, we've used 3 already, so there are 28 left

// bottom line: a maximum group hierarchy of 29 levels

for ($level = 1; $level <= 28; $level++) {

    $joinc = "subgroup".($level).".fk_child = subgroup".($level+1).".fk_parent";

    $query->addTable($db."j_groups_groups", LEFT_JOIN, $joinc, "subgroup".($level+1));

    $query->addColumn("fk_child", "subgroup".($level+1)."_id", "subgroup".($level+1));

}

And the SQL Query would look like:

SELECT
    doboHarmoniTest.subgroup0.groups_id AS subgroup0_id,
    doboHarmoniTest.subgroup0.groups_display_name AS display_name,
    doboHarmoniTest.subgroup0.groups_description AS group_description,
    doboHarmoniTest.type.type_domain AS domain,
    doboHarmoniTest.type.type_authority AS authority,
    doboHarmoniTest.type.type_keyword AS keyword,
    doboHarmoniTest.type.type_description AS type_description,
    subgroup1.fk_child AS subgroup1_id,
    subgroup2.fk_child AS subgroup2_id,
    subgroup3.fk_child AS subgroup3_id,
    subgroup4.fk_child AS subgroup4_id,
    subgroup5.fk_child AS subgroup5_id,
    subgroup6.fk_child AS subgroup6_id,
    subgroup7.fk_child AS subgroup7_id,
    subgroup8.fk_child AS subgroup8_id,
    subgroup9.fk_child AS subgroup9_id,
    subgroup10.fk_child AS subgroup10_id,
    subgroup11.fk_child AS subgroup11_id,
    subgroup12.fk_child AS subgroup12_id,
    subgroup13.fk_child AS subgroup13_id,
    subgroup14.fk_child AS subgroup14_id,
    subgroup15.fk_child AS subgroup15_id,
    subgroup16.fk_child AS subgroup16_id,
    subgroup17.fk_child AS subgroup17_id,
    subgroup18.fk_child AS subgroup18_id,
    subgroup19.fk_child AS subgroup19_id,
    subgroup20.fk_child AS subgroup20_id,
    subgroup21.fk_child AS subgroup21_id,
    subgroup22.fk_child AS subgroup22_id,
    subgroup23.fk_child AS subgroup23_id,
    subgroup24.fk_child AS subgroup24_id,
    subgroup25.fk_child AS subgroup25_id,
    subgroup26.fk_child AS subgroup26_id,
    subgroup27.fk_child AS subgroup27_id,
    subgroup28.fk_child AS subgroup28_id,
    subgroup29.fk_child AS subgroup29_id
FROM
    doboHarmoniTest.groups AS subgroup0
        INNER JOIN
    doboHarmoniTest.type
        ON doboHarmoniTest.subgroup0.fk_type = doboHarmoniTest.type.type_id
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup1
        ON doboHarmoniTest.subgroup0.groups_id = doboHarmoniTest.subgroup1.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup2
        ON subgroup1.fk_child = subgroup2.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup3
        ON subgroup2.fk_child = subgroup3.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup4
        ON subgroup3.fk_child = subgroup4.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup5
        ON subgroup4.fk_child = subgroup5.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup6
        ON subgroup5.fk_child = subgroup6.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup7
        ON subgroup6.fk_child = subgroup7.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup8
        ON subgroup7.fk_child = subgroup8.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup9
        ON subgroup8.fk_child = subgroup9.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup10
        ON subgroup9.fk_child = subgroup10.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup11
        ON subgroup10.fk_child = subgroup11.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup12
        ON subgroup11.fk_child = subgroup12.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup13
        ON subgroup12.fk_child = subgroup13.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup14
        ON subgroup13.fk_child = subgroup14.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup15
        ON subgroup14.fk_child = subgroup15.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup16
        ON subgroup15.fk_child = subgroup16.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup17
        ON subgroup16.fk_child = subgroup17.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup18
        ON subgroup17.fk_child = subgroup18.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup19
        ON subgroup18.fk_child = subgroup19.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup20
        ON subgroup19.fk_child = subgroup20.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup21
        ON subgroup20.fk_child = subgroup21.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup22
        ON subgroup21.fk_child = subgroup22.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup23
        ON subgroup22.fk_child = subgroup23.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup24
        ON subgroup23.fk_child = subgroup24.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup25
        ON subgroup24.fk_child = subgroup25.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup26
        ON subgroup25.fk_child = subgroup26.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup27
        ON subgroup26.fk_child = subgroup27.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup28
        ON subgroup27.fk_child = subgroup28.fk_parent
        LEFT JOIN
    doboHarmoniTest.j_groups_groups AS subgroup29
        ON subgroup28.fk_child = subgroup29.fk_parent

For more information on the SELECT queries and what you can do with them see the PHPDoc of Harmoni

4.1.4 Insert Queries

The Insert Query is the wrapper function for the insert queries. The Insert queries are not as vast as the Select Queries and the PHPDoc is the best way to see what you can do with the Insert Queries.

A basic example of what you can do follows:

Example




..<previous code goes here>..



$query =& new InsertQuery; //Create an InsertQuery

$query->setTable("table1");  //The table in which you are going to insert



$query->setColumns{

    array(

        "column1", "column2")

    };





$query->addRowOfValues{

    array(    

        "'jones'","'margy'")

    };



$query->addRowOfValues{

    array(

        "'jameson'", "'bobby'")

    };



$result =& $dbHandler->query($query, $dbIndex);



if ($result) {

print "Success! We inserted " . $query->getNumberOfRows() . " rows into the database!\n";

} else {

print "Ergh. Something happened.\n";

}

And the SQL query that is generated looks like:

INSERT INTO table1
    (column1, column2)
    VALUES('jones', 'margy'), ('jameson', 'bobby')

The InsertQuery class has a method called "setAutoIncrementColumn($column, $sequence)" which sets the auto increment column and which was written to be used with an Oracle database. This method is accessed like every other method of the InsertQuery class.

4.1.5 Update Queries

The UpdateQuery class defines its own wrapper methods for query passing to the database. An example of an Update Query follows:

Example




..<previous code goes here>..



$query =& new UpdateQuery;



$query->setTable("table1");

$query->setColumns(array("column1"));

$query->setValues(array("'yodle'"));

$query->addWhere("column1='jones'");



$result =& $dbHandler->query($query,$dbIndex);



print "There were " . $result->getNumberOfRows() . " members of the jones family.";

The "jones" family just changed their last name to "yodle". Don't ask me. Here's the query under MySQL:

UPDATE
    table1
    SET column1 = 'yodle'
    WHERE column1 = 'jones'

Of course the Update query can be more specific using more WHERE clauses connected with the usual logical operators and by updating more columns at a time. Again, the PHPDoc can be really helpful in formatting your Update queries.

4.1.6 Delete Queries

The Delete Queries delete entries from the database you use. For a general idea, consider the following example:

Example




..<previous code goes here>..



$query =& new DeleteQuery;



$query->setTable("table1");

$query->addWhere("column1='yodle'");



$result =& $dbHandler->query($query,$dbIndex);



print "The yodle family just lost " . $result->getNumberOfRows() . " members.";

And the SQL query looks like:

DELETE
    FROM table1
    WHERE column1 = 'yodle'

And don't forget to check out the PHPDoc again...

4.1.7 Generic Queries

We are almost there! Now you know how to use the Harmoni wrapper functions to pass your queries to your database. But Harmoni does not restrict you to the queries defined above but gives you more flexibility throught the class "GenericSQLQuery". This class allows you to create a query that is not restricted to the queries discussed above but gives you the flexibility to pass the SQL query itself as a string.

The first step is to create the Query itself and declare it a GenericSQLQuery. So:


$query =& new GenericSQLQuery;

And the steps that we take to pass that Query to the database through our DataBase Handler are equivalent to the other queries. An example follows:

Example




..<previous code goes here>..



$query =& new GenericSQLQuery;



$query->addSQLQuery("

CREATE TABLE 

Users( 

ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME TEXT,

DATEOFBIRTH DATE NOT NULL

) 

");



$result =& $dbHandler->query($query,$dbIndex);

So, the GenericSQLQuery "gets" the SQL query as a string and gives it the form of a query.