Database Access

From Achievo/ATK Wiki

Jump to: navigation, search

ATK Howto: Database Access

Complexity: Easy
Author: Ivo Jansch <ivo@achievo.org>

List of other Howto's

Contents

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

Prepared Statements

The prefered way to do the above direct SQL access is to use Prepared Statements, which add another layer of flexiblity on the DB api. There's a separate Prepared Statements howto explaining this in detail.

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.

Simple access

Here is an example: 

  $emp = atkGetNode("company.employee");
  $records = $emp->select()->getAllRows();

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, select() has a fluent interface 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($mode) method is interesting because you can set it to "edit" for example, at which point select() 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 select() is overridden for example)

See the API docs for more information.

Including relations

If you want nested (multi-dimensional) arrays with all the relations followed (i.e. if you want an array of "tracks" where the value of the "album_id" keys are themselves arrays representing an album, and so on), then--because of all the work ATK has to do behind the scenes--you have less flexibility, and are only able to control the WHERE clause of the generated SQL. This is done with the addFilter method.

For example: 

$node = newNode("track");
$node->addFilter("t.album_id = 79");
$res = $node->select()->getAllRows();

If the track node defines a hasOne() relation the album node, this will result in a query that joins the album table (ATK constructs this query for you), and will return a $res that looks something like: 

array(
  array(
    "name" => "King of the Mountain",
    // other keys
    "album_id" => array(
      "name" => "Aerial: A Sea of Honey",
      "year" => 2005
      // other key-value pairs
    )
  ),
  array(
    "name" => "Pi",
    // other keys
    "album_id" => array(
      "name" => "Aerial: A Sea of Honey",
      "year" => 2005
      // other key-value pairs
    )
  )
  // ...
);

The only way to influence the results returned by a node is via addFilter(). However, if it is difficult or inconvenient to restructure your query in terms of filters and your database supports subqueries, you may be able to use you original raw SQL query without only minor modifications via an id IN (...) clause.

For example, if you want "foo" records (with all relations), and have a working query that looks like this: 

SELECT foo
FROM foo, bar, baz
WHERE 
foo.bar_id = bar.id AND
bar.baz_id = baz.id AND
baz.id = 42;

then if you change the query slightly so that it returns ids only (SELECT foo.id FROM foo, bar, baz...) then it can be dropped in to your ATK code via: 

$node->addFilter("foo.id IN (SELECT foo.id FROM foo, bar, baz...)");

Using atkDDL

If you need some flexibility to change tables on the fly or during development, use the atkDDL classes. Db-neutral database migrations can be easily achieved.

  • First, in the Achievo package, check out module/setup. It has a setup node in there that uses the atkDDL module to actually read the node and generate the DDL to create the table from that. It is an amazing module. (more detail later...)
Retrieved from "http://www.atkframework.com/wiki/Database_Access"
Personal tools
Navigation