From Harmoni

Note: We are considering a new the Database abstraction system based on the Zend_Db and PHP 5's new PDO extension. See Harmoni_Db for details. If we decide to move to PDO, the existing Database Manager service will be retained and have its implementation updated to make use of the new system.

Please post any comments or suggestions on this topic to the Harmoni Development Forum.

---

The Harmoni database wrapper, handled by the DBHandler class, provides access to SQL databases. Supported databases include: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

The purpose of the Database Manager is two-fold. First, it allows you to make PHP statements that will work with any SQL database. Secondly, the Database Manager query objects can take care of quoting and escaping of SQL parameters to help prevent SQL injection vulnerabilities. The Security Best Practices page details this issue.

Contents 1 Basic Database Manager Usage 1.1 Connecting/Disconnecting 1.2 Select Query 1.3 Insert Query 1.4 Update Query 1.5 Delete Query 2 In-Depth Usage 2.1 Background 2.2 How we do it 2.3 Setting up a Connection 2.4 Select Queries 2.5 Insert Queries 2.6 Update Queries 2.7 Delete Queries 2.8 Generic Queries 2.8.1 Running Generic Queries 2.8.2 Using Results From Generic Queries 3 Notes and Limitations 4 Database Server Specifics 4.1 MySQL 4.1.1 Notes 4.1.2 Issues and Bugs 4.2 PostgreSQL 4.2.1 Notes 4.2.2 Issues and Bugs

Basic Database Manager Usage

Connecting/Disconnecting

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

$dbIndex = $dbHandler->addDatabase(
        new PostgreSQLDatabase(
            "my.host.com",
            "myDatabaseName",
            "myUser",
            "secret")
        );
        
$dbHandler->connect($dbIndex);
// you could also use $dbHandler->pConnect($dbIndex);
// for a persistent connection.

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

$dbHandler->disconnect($dbIndex);

The above code does nothing more than connect to a Postgres database and then disconnect. Amazing.

Select Query

Now, let's fetch some data from a table named "table1".

$query = new SelectQuery;

$query->addTable("table1");
$query->addColumn("column1");
$query->addColumn("column2", "anAlias"); // fetch column2 AS anAlias
$query->addWhereEqual("column1", "Jones");

// execute the query!
$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();
}

The above might output something like:

Our query has 3 rows!
Result row: column1 = Jones, anAlias = Fred
Result row: column1 = Jones, anAlias = George
Result row: column1 = Jones, anAlias = Franky

The SQL query could look something like:

SELECT column1, column2 AS anAlias
        FROM table1
        WHERE column1 = 'jones'

This is a very simple example. The SelectQuery can do a whole lot more. Take a look at the PHPDoc or later chapters for more information.

Insert Query

$query = new InsertQuery;
$query->setTable("table1");

$query->addValue("column1", "Jones");
$query->addValue("column2", "Margy");

// Insert Queries will always have at least one row of values, use createRow() to add values to a new row.
$query->createRow();
$query->addValue("column1", "O'Reilly");
$query->addValue("column2", "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";
}

Under MySQL, the query would look like:

INSERT
    INTO table1
    (column1, column2)
    VALUES ('Jones', 'Margy')
    VALUES ('O\'Reilly', 'Bobby')

Update Query

$query = new UpdateQuery;
$query->setTable("table1");
$query->addValue("column1", "Yodle");
$query->addWhereEqual("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"

Delete Query

$query = new DeleteQuery;
$query->setTable("table1");
$query->addWhereEqual("column1", "Yodle");

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

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

Bad new... the "Yodle" family just choked. Away they go. Here's the SQL:

DELETE
    FROM table1
    WHERE column1 = "Yodle"

So, now you know how to do the basics and essentials to use databases. And no more writing SQL for you SQL-phobic folks out there!

In-Depth Usage

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.

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

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:

$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);

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:

$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:

$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->addWhereEqual("table1.column1", "O'Reilly"); //A WHERE clause with "column1 = 'O\'Reilly'
$query->addWhereEqual("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 = 'O\'Reilly'
        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:

..<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 = O'Reilly, anAlias = Franky
Result row: column1 = Brown, anAlias = Fred
Result row: column1 = O'Reilly, 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:

$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

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:

..<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.

Update Queries

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

..<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.

Delete Queries

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

..<previous code goes here>..

$query = new DeleteQuery;

$query->setTable("table1");
$query->addWhereEqual("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...

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. Be aware that usage of the GenericSQLQuery puts more responsibility on your part to maintain portability across database systems -- if that is a desired characteristic of your software.

Running Generic Queries

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:

..<previous code goes here>..

$query = new GenericSQLQuery;

$query->addSQLQuery("
CREATE TABLE
users(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name TEXT,
date_of_birth 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.

Note that the query string can also be passed to the query object in the constructor:

$query = new GenericSQLQuery("SELECT * FROM users WHERE id = '1'");

Using Results From Generic Queries

For generic queries that execute a SELECT, DELETE, INSERT, or UPDATE, you can use their results as you would a non-generic result. The various GenericQueryResult classes all implement the following methods rather than replicating the functionality themselves:

returnAsSelectQueryResult()
returnAsInsertQueryResult()
returnAsUpdateQueryResult()
returnAsDeleteQueryResult()

Usage Example:

$query = new GenericSQLQuery("SELECT * FROM users WHERE id = '1');

$dbc = Services::getService("DatabaseManager");
$result = $dbc->query($query);
$result = $result->returnAsSelectQueryResult();

print_r($result->getCurrentRow());

Notes and Limitations

• JOIN statments (and legacy WHERE statements still in some Harmoni services) use relation strings rather than column and relation parameters, preventing the database implementation from being able to change the SQL syntax for different identifier quoting styles of different database servers. A refactoring of the Database Manager should allow the relationships to be specified in a way that allows the implementation to modify the column identifiers.

• The implementations of SelectQuery, InsertQuery, UpdateQuery, and DeleteQuery objects do string concatenation to early. Concatenation should be held out until the database-specific implementation to more easily provide hooks for a greater scope of query changes.

Database Server Specifics

The Harmoni Database Manager is designed to enable queries written once to run transparently on multiple database server platforms without changes to the application code. In practice, differences in the features supported by various server platforms mean that applications can only rely on the features that are common to all database systems that they run on. No database wrapper is going to enable an application that relies on triggers and stored procedures in a database run on a database server that lacks those. What the Harmoni Database Manager can do, is abstract away much of the syntax for building SQL queries so that the particulars in the required formatting of the SQL query are handled automatically.

Below are some notes and details when developing on or for a particular database server. As the Harmoni Database Manager is improved and refactored, most of these limitations will cease to be a problem, but these are the things to be aware of now.

MySQL

As most of the initial work on Harmoni was done in a MySQL environment, there are very few caveats here aside from the reminder to note the limitations of other systems so that your queries might be more portable.

Notes

• In general, avoid upper case in table and column names when using the current version of Harmoni. A limitation in the current Database Manager implementation makes it hard to reliably quote column and table names in every query such that case sensitivity can be preserved across all systems.

• MySQL only supports a single level of transaction nesting. The Database Manager allow usage of nested transactions when using MySQL by keeping track of a stack of trasaction BEGINs and COMMITs, and only actually executes the outermost BEGIN and COMMIT statements.

• As of the time of this writing, MySQL does not support triggers or stored procedures. Usage of these database features by an application will preclude its operation on MySQL.

• the AUTO_INCREMENT column property is a MySQL-specific feature. For any InsertQuery objects that are built for tables using AUTO_INCREMENT columns, be sure to inform the query object of the auto-incrementing column with a

  $query->setAutoIncrementColumn('col_name', 'sequence_name');

  statement.

Issues and Bugs

None at this time.

PostgreSQL

Notes

• PostgreSQL folds all identifiers (column names, table names, functions names, etc) to lowercase by default, unless the identifiers are quoted. Because of this, avoid upper case in table and column names when using the current version of Harmoni. A limitation in the current Database Manager implementation makes it hard to reliably quote column and table names in every query such that case sensitivity can be preserved across all systems.

• The current PostgreSQL version of the Harmoni tables includes a script that must be run by a [database] superuser in order to install the plpgsql language. This script also creates several trigger functions that are used by the DigitalRepository tables.

• The 'public' schema is currently assumed. Future refactoring of the the Database Manager should add support for using other schemas.

Issues and Bugs

• The current PostgreSQL table definitions were ported from the MySQL table definitions after much PostgreSQL documentation reading and a little practice. The have been tested to install properly and Segue(v2) has been tested as running on them. That said, the table definitions should be looked over by someone with more PostgreSQL experience before production usage. Some particulars to look at follow:
  • The digital repository implementation base64_encodes its file data, so I made the data field in the dr_file_data type text, rather than messing with the LOB stores. This seems to work, but maybe there is a better way to do this.
  • The data type for dm_blob data should maybe be changed from text...