Skip to content

topics.objectpersistence

Jump to bottom Edit New page
Alex Kiesel edited this page Jun 7, 2012 · 10 revisions

Databases: Object persistence

The XP framework also offers an API to object persistence via the rdbms.DataSet and rdbms.Peer classes. These classes implement a variation of the "Row Data Gateway" pattern (described by Martin Fowler):

  An object that acts as a Gateway to a single record in a data source.
  There is one instance per row.

and the "Table Data Gateway" (for details, see here):

  A Table Data Gateway holds all the SQL for accessing a single table or
  view: selects, inserts, updates, and deletes. Other code calls its
  methods for all interaction with the database.

In the XP framework, the Table Data Gateway is called Peer, the "Row Data Gateway" is implemented by subclassing DataSet.

API

Every DataSet class has a Peer class associated with it. This happens automatically as soon as the class is loaded. The Peer class takes care of the interaction with the RDBMS and using the rdbms.DBConnection classes via the rdbms.ConnectionManager. It does not usually need to be used directly, its methods are invoked by the DataSet classes.

Assume the following database structure:

+-------------------------+
| news                    |
+-------------------------+
| +news_id: numeric       |
| +author: varchar(50)    |
| +title: varchar(255)    |
| +body: text             |
| +lastchange: datetime   |
+-------------------------+

To be able to access this table using the object persistence api, we will first have to generate a DataSet subclass. This is done by using the generator utility - this creates a class called News which will be our means of accessing the news table.

  • The generated class extends the rdbms.DataSet class
  • For each field in the table, a member is created
  • For each member, a getter an a setter is created
  • For each index, a static getter is created
Note: The Peer class uses the connection manager to retrieve a connection to the RDBMS, so passing a connection to all the methods is not necessary. Instead, it is expected that you register an appropriate connection to the `rdbms.ConnectionManager`: 
<?php
  ConnectionManager::getInstance()->register(
    DriverManager::getConnection('pgsql://user:password@host/DB?autoconnect=1'),
    'news'
  );
?>

The generated class will contain the name of the connection it is using.

Retrieving an object

To retrieve an object the static accessors are used:

<?php
  // Retrieve an instance of the news class by its primary key news_id.
  // This basically equals to:
  //
  //   select * from news where news_id= 1
  //
  Console::writeLine('News #1: ', News::getByNews_id(1));
?>

In the above case, the result will be a single instance of the News class because the primary key is - of course - unique. Assuming we have a non-unique index on the field author, we'd get an array:

<?php
  // Retrieve instances of the news class where 'timm' is the author's
  // field value:
  //
  //   select * from news where author= 'timm'
  //
  foreach (News::getByAuthor('timm') as $n) {
    Console::writeLinef('- News #%d: %s', $n->getNews_id(), $n->getTitle());
  }
?>

Creating an object

To create a new entry in the news table, use the following:

<?php
  // Create a News object and insert it. This executes the following SQL:
  //
  //   insert into news (author, title, body, lastchange) values (...)
  //
  // The insert() method returns the identity value if applicable.
  with ($n= new News()); {
    $n->setAuthor('hubert');
    $n->setTitle('Good news, everyone!');
    $n->setBody('I have created ...');
    $n->setLastchange(Date::now());

    $id= $n->insert();
  }
  
  Console::writeLinef('Created news entry, id is %d', $id);
?>

Modifying an object

To modify an existing object, we retrieve one from the database, use the setters to modify the data and then write it back using the update() method.

<?php
  // 1) Retrieve an object
  $news= News::getByNews_id(1);
  
  // 2) Modify it
  $news->setTitle('Updated: '.$news->getTitle());
  
  // 3) Write it back to the database. Only the modified fields will get updated:
  //
  //  update news set title = "Updated: Good news, everyone!" where news_id = 1
  //
  // The update() method will use the primary key in the where clause
  $news->update();
?>

Using criteria

To compose more complex queries, one can use the rdbms.Criteria class and pass it to the rdbms.Peer object associated with the dataset class.

<?php
  // select * from news where author= 'timm' and title like 'Hello%'
  $entries= News::getPeer()->doSelect(create(new Criteria())
    ->add('author', 'timm', EQUAL)
    ->add('title', 'Hello%', LIKE)
  );
?>

The add() method is overloaded and has the following signatures:

<?php
  public self add(string field, string value, string op)
  public self add(rdbms.criterion.Criterion c)
?>

Restrictions

The default way is to narrow results by adding more criteria with and. The rdbms.criterion.Restrictions class offers extended possibilities - methods available are:

  • in() and notIn()
  • like(), ilike() and notLike()
  • equal and notEqual()
  • lessThan() and lessThanOrEqualTo()
  • greaterThan() and greaterThanOrEqualTo()
  • between()
  • anyOf() and allOf()
  • not()

The above can be rewritten using the second version as follows:

<?php
  // select * from news where author= 'timm' and title like 'Hello%'
  $entries= News::getPeer()->doSelect(create(new Criteria())
    ->add(Restrictions::equal('author', 'timm'))
    ->add(Restrictions::like('title', 'Hello%'))
  );
?>

Between

The between() constraint selects values with an upper and lower boundary

<?php
  // select * from news where news_id between 1 and 100
  $entries= News::getPeer()->doSelect(new Criteria(Restrictions::between(
    'news_id',
    1,
    100
  )));
?>

Conjunction / disjunction / negation

By using the anyOf, allOf and not factory methods, queries with or, and and not can be created:

<?php
  // Restrictions::anyOf()
  //
  //   select * from news where (author = 'timm' or title like '%timm%')
  //
  $entries= News::getPeer()->doSelect(new Criteria(Restrictions::anyOf(
    Restrictions::equal('author', 'timm'),
    Restrictions::like('title', '%timm%')
  )));
  
  // Restrictions::allOf()
  //
  //   select * from news where (author = 'timm' and title like '%timm%')
  //
  $entries= News::getPeer()->doSelect(new Criteria(Restrictions::allOf(
    Restrictions::equal('author', 'timm'),
    Restrictions::like('title', '%timm%')
  )));
  
  // Restrictions::not()
  //
  //   select * from news where not (title like '%timm%')
  //
  $entries= News::getPeer()->doSelect(new Criteria(Restrictions::not(
    Restrictions::like('title', '%timm%')
  )));
?>
Add a custom footer
Add a custom sidebar
Clone this wiki locally