Database Access

ATK Howto: Database Access Complexity: Easy Author: Ivo Jansch <ivo@achievo.org>

List of other Howto's

Intro

While it's possible to write a simple data management application in ATK without having to access the database yourself (ATK does most of the access for you), in larger applications you're inevitably going to need to access the database from the code.

There are three ways to accomplish this, each of which will be explained below.

Direct SQL access

The quickest way to access the database, is to use the database connection directly. The advantage is that you can very quickly run any SQL statement you want.

The disadvantage is that you have to take notice that the SQL query you write may not be compatible with other database servers. If you write a complex Oracle statement, your app may later on not work with PostgreSQL or MySQL.

Another drawback is that you, or any programmer working on your code, have to have knowledge about the database schema.

If you're fine with these disadvantages, here's the code to access the database directly.

  // Default database connection:
  $db = &atkGetDb();
  $db->query("UPDATE employees SET name='ivo' WHERE login='ivo'");

  // Node's database connection:
  $db = $node->getDb();
  $db->query("UPDATE employees SET name='ivo' WHERE login='ivo'");

For select queries, instead of the query() method, you can use the getrows() method, like this:

  $rows = $db->getrows("SELECT * FROM employee");

Note that this statement loads all records into memory at once. If you need to retrieve large amounts of data, it's better to grab them from the db as you need them. In that case, you can use the query() method in combination with the next_record() method:

  $db = &atkGetDb();
  
  $db->query("SELECT * FROM employee");
  
  while ($db->next_record())
  {
    $record = $db->m_record;
    // Go on to process record...
  }

Using the query abstraction layer

ATK has an abstraction layer to build queries that will run on all supported databases. Its features are a common denominator of the features of the supported databases.

The main advantage of using this layer is that your application can be moved to another type of database server without any changes. Another advantage is that in situations where queries are build dynamically, based on conditions, defined by metadata etc., this api makes it easier to build the query.

Let's first look at an example. Suppose we want to retrieve the names of the employees, ordered alphabetically, and we only need the first 10 records. Here's the code to accomplish that:

  $query = &atkQuery::create()
  
  $query->addTable("employee");
  $query->addField("name");
  $query->addOrderBy("name");
  $query->setLimit(0, 10);
  
  $result = $query->executeSelect();

At first, a query instance is created. The instance is created based on the configuration settings, so it instantiates the appropriate object for the database server you are using.

Next, the query is build using the methods that atkQuery provides.

Finally, the query is executed. In our example this will generate the query "SELECT name FROM employee ORDER BY name LIMIT 0,10" if we are using mysql. In Oracle however, the 'LIMIT' statement does not exist. But the query object handles this nicely, by generating a different query that will work in Oracle.

See the API documentation for atkQuery to have a look at the possibilities of the query abstraction layer. There are methods to create joins, use group by statements, so in principle any query can be created like this.

Using the nodes

The highest level of abstraction can be achieved by not using the database layer at all. You probably already programmed nodes that represent your database tables. You can use this to retrieve data.

Here is an example:

  $emp = &getNode("company.employee");
  $result = $emp->selectDb();

The first line retrieves the 'employee' node from the 'company' module. The next line performs a select query on the node. It respects filters, so any filter already implemented in the node is reflected in the data that is retrieved. You can ofcourse add additional filters using the addFilter() method.

Furthermore, selectDb has a set of paramaters which you can use to influence the result. You can add a where-clause, specify which attributes you want to load, or the ones you want to exclude. The $mode parameter is interesting because you can set it to "edit" for example, at which point selectDb will retrieve all attributes that do not have an AF_HIDE_EDIT flag.

There are more advantages to this approach:

• No knowledge of the underlying database is required, just knowing the nodes is enough
• This also loads relations! The node takes care of the joins, subqueries for one-to-many relations etc.
• This also loads attributes that have custom loading mechanisms (file readers, calculated values etc.)
• This also loads nodes which have custom loading logic (where selectDb is overridden for example)

See the API docs for the selectDb for more parameters.