Thursday May 16, 2013

Offline Processing in PHP with Advanced Queuing

Offloading slow batch tasks to an external process is a common method of improving website responsiveness. One great way to initiate such background tasks in PHP is to use Oracle Streams Advanced Queuing in a producer-consumer message passing fashion. Oracle AQ is highly configurable. Messages can queued by multiple producers. Different consumers can filter messages. From PHP, the PL/SQL interface to AQ is used. There are also Java, C and HTTPS interfaces, allowing wide architectural freedom. Oracle Advanced Queuing is included in all editions of the database.

The following example simulates an application user registration system where the PHP application queues each new user's street address. An external system monitoring the queue can then fetch and process that address. In real life the external system might initiate a snail-mail welcome letter, or do further, slower automated validation on the address.

The following SQL*Plus script qcreate.sql creates a new Oracle user demoqueue with permission to create and use queues. A payload type for the address is created and a queue is set up for this payload.

-- qcreate.sql

connect / as sysdba
drop user demoqueue cascade;

create user demoqueue identified by welcome;
grant connect, resource to demoqueue;
grant aq_administrator_role, aq_user_role to demoqueue;
grant execute on dbms_aq to demoqueue;
grant create type to demoqueue;

connect demoqueue/welcome@localhost/orcl

-- The data we want to queue
create or replace type user_address_type as object (
  name        varchar2(10),
  address     varchar2(50)
);
/

-- Create and start the queue
begin
 dbms_aqadm.create_queue_table(
   queue_table        =>  'demoqueue.addr_queue_tab',
   queue_payload_type =>  'demoqueue.user_address_type');
end;
/

begin
 dbms_aqadm.create_queue(
   queue_name         =>  'demoqueue.addr_queue',
   queue_table        =>  'demoqueue.addr_queue_tab');
end;
/

begin
 dbms_aqadm.start_queue(
   queue_name         => 'demoqueue.addr_queue',
   enqueue            => true);
end;
/

The script qhelper.sql creates two useful helper functions to enqueue and dequeue messages:

-- qhelper.sql
-- Helpful address enqueue/dequeue procedures

connect demoqueue/welcome@localhost/orcl

-- Put an address in the queue
create or replace procedure my_enq(name_p in varchar2, address_p in varchar2) as
  user_address       user_address_type;
  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  enq_id             raw(16);
begin
  user_address := user_address_type(name_p, address_p);
  dbms_aq.enqueue(queue_name         => 'demoqueue.addr_queue',
                  enqueue_options    => enqueue_options,
                  message_properties => message_properties,
                  payload            => user_address,
                  msgid              => enq_id);
  commit;
end;
/
show errors

-- Get an address from the queue
create or replace procedure my_deq(name_p out varchar2, address_p out varchar2) as
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  user_address       user_address_type;
  enq_id             raw(16);
begin
  dbms_aq.dequeue(queue_name         => 'demoqueue.addr_queue',
                  dequeue_options    => dequeue_options,
                  message_properties => message_properties,
                  payload            => user_address,
                  msgid              => enq_id);
  name_p    := user_address.name;
  address_p := user_address.address;
  commit;
end;
/
show errors

The script newuser.php is the part of the PHP application that handles site registration for a new user. It queues a message containing their address and continues executing:

<?php
// newuser.php

$c = oci_connect("demoqueue", "welcome", "localhost/orcl");

// The new user details
$username = 'Fred';
$address  = '500 Oracle Parkway';

// Enqueue the address for later offline handling
$s = oci_parse($c, "begin my_enq(:username, :address); end;");
oci_bind_by_name($s, ":username", $username, 10);
oci_bind_by_name($s, ":address",  $address,  50);
$r = oci_execute($s);

// Continue executing
echo "Welcome $username\n";

?>

It executes an anonymous PL/SQL block to create and enqueue the address message. The immediate script output is simply the echoed welcome message:

Welcome Fred

Once this PHP script is executed, any application can dequeue the new message at its leisure. For example, the following SQL*Plus commands call the helper my_deq() dequeue function and displays the user details:

-- getuser.sql

connect demoqueue/welcome@localhost/orcl

set serveroutput on
declare
  name varchar2(10);
  address varchar2(50);
begin
  my_deq(name, address);
  dbms_output.put_line('Name     : ' || name);
  dbms_output.put_line('Address  : ' || address);
end;
/

The output is:

Name     : Fred
Address  : 500 Oracle Parkway

If you instead want to check the queue from PHP, use getuser.php:

<?php
// getuser.php

$c = oci_connect("demoqueue", "welcome", "localhost/orcl");

// dequeue the message
$sql = "begin my_deq(:username, :address); end;";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":username", $username, 10);
oci_bind_by_name($s, ":address", $address, 50);
$r = oci_execute($s);

echo "Name     : $username\n";
echo "Address  : $address\n";

?>

If the dequeue operation is called without anything in the queue, it will block waiting for a message until the queue wait time expires. This is configurable by setting a zero wait time dequeue_options.wait := 0; before calling dbms_aq.dequeue.

The PL/SQL API has much more functionality than shown in this overview. For example you can enqueue an array of messages, or listen to more than one queue. Queuing is highly configurable and scalable, providing a great way to distribute workload for web or mobile applications. More information about AQ is in the Oracle Streams Advanced Queuing User's Guide.

Bootnote: The basis for this blog post comes from the Underground PHP and Oracle Manual

This post was updated to show setting a zero wait time.

Tuesday May 14, 2013

Getting Started with PHP Zend Framework 2 for Oracle DB

This post shows the changes to the ZF2 tutorial application to allow it to run with Oracle Database 11gR2.

Oracle Database SQL identifiers are case insensitive by default so "select Abc from Xyz" is the same as "select abc from xyz". However the identifier metadata returned to programs like PHP is standardized to uppercase by default. After executing either query PHP knows that column "ABC" was selected from table "XYZ".

In PHP code, array indices and object attributes need to match the schema identifier case that is returned by the database. This is either done by using uppercase indices and attributes in the PHP code, or by forcing the SQL schema to case-sensitively use lower-case names.

The former approach is more common, and is shown here.

The instructions for creating the sample ZF2 application are here. Follow those steps as written, making the substitutions shown below.

Schema

In Oracle 11gR2, the schema can be created like:

DROP USER ZF2 CASCADE;

CREATE USER ZF2 IDENTIFIED BY WELCOME
    DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
    TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION
    , CREATE TABLE
    , CREATE PROCEDURE
    , CREATE SEQUENCE
    , CREATE TRIGGER
    , CREATE VIEW
    , CREATE SYNONYM
    , ALTER SESSION
TO ZF2;

CONNECT ZF2/WELCOME

CREATE TABLE ALBUM (
  ID NUMBER NOT NULL,
  ARTIST VARCHAR2(100) NOT NULL,
  TITLE VARCHAR2(100) NOT NULL,
  PRIMARY KEY (ID)
);

CREATE SEQUENCE ALBUMSEQ;

CREATE TRIGGER ALBUMTRIGGER BEFORE INSERT ON ALBUM FOR EACH ROW
BEGIN
  :NEW.ID := ALBUMSEQ.NEXTVAL;
END;
/

INSERT INTO ALBUM (ARTIST, TITLE)
    VALUES ('The  Military  Wives', 'In  My  Dreams');
INSERT INTO ALBUM (ARTIST, TITLE)
    VALUES ('Adele', '21');
INSERT INTO ALBUM (ARTIST, TITLE)
    VALUES ('Bruce  Springsteen', 'Wrecking Ball (Deluxe)');
INSERT INTO ALBUM (ARTIST, TITLE)
    VALUES ('Lana  Del  Rey', 'Born  To  Die');
INSERT INTO ALBUM (ARTIST, TITLE)
    VALUES ('Gotye', 'Making  Mirrors');

COMMIT;

Driver and Credentials

The driver and credentials are Oracle-specific. Always use the OCI8 adapter in ZF, since it is more stable and has better scalability. Specifying a character set will make connection faster.

zf2-tutorial/config/autoload/global.php:
 return array(
     'db' => array(
-        'driver'         => 'Pdo',
-        'dsn'            => 'mysql:dbname=zf2tutorial;host=localhost',
-        'driver_options' => array(
-            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
-        ),
+        'driver'         => 'OCI8',
+        'connection_string' => 'localhost/orcl',
+        'character_set' => 'AL32UTF8',
     ),
     'service_manager' => array(
         'factories' => array(
zf2-tutorial/config/autoload/local.php:
 return array(
     'db' => array(
-        'username' => 'YOUR USERNAME HERE',
-        'password' => 'YOUR USERNAME HERE',
+        'username' => 'ZF2',
+        'password' => 'WELCOME',
     ),
     // Whether or not to enable a configuration cache.
     // If enabled, the merged configuration will be cached and used in

Attribute & Index Changes

The rest of the application changes are just to handle the case of the Oracle identifiers correctly.

zf2-tutorial/module/Album/Module.php
                     $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                     $resultSetPrototype = new ResultSet();
                     $resultSetPrototype->setArrayObjectPrototype(new Album());
-                    return new TableGateway('album', $dbAdapter, null, $resultSetPrototype);
+                    return new TableGateway('ALBUM', $dbAdapter, null, $resultSetPrototype);
                 },
             ),
         );
zf2-tutorial/module/Album/view/album/album/add.phtml
 $form->prepare();
 
 echo $this->form()->openTag($form);
-echo $this->formHidden($form->get('id'));
-echo $this->formRow($form->get('title'));
-echo $this->formRow($form->get('artist'));
+echo $this->formHidden($form->get('ID'));
+echo $this->formRow($form->get('TITLE'));
+echo $this->formRow($form->get('ARTIST'));
 echo $this->formSubmit($form->get('submit'));
 echo $this->form()->closeTag();
zf2-tutorial/module/Album/view/album/album/delete.phtml
 <h1><?php echo $this->escapeHtml($title); ?></h1>
 
 <p>Are you sure that you want to delete
-'<?php echo $this->escapeHtml($album->title); ?>' by
-'<?php echo $this->escapeHtml($album->artist); ?>'?
+'<?php echo $this->escapeHtml($album->TITLE); ?>' by
+'<?php echo $this->escapeHtml($album->ARTIST); ?>'?
 </p>
 <?php
 $url = $this->url('album', array(
            'action' => 'delete',
-           'id'     => $this->id,
+           'id'     => $this->ID,
        ));
 ?>
 <form action="<?php echo $url; ?>" method="post">
     <div>
-    <input type="hidden" name="id" value="<?php echo (int) $album->id; ?>" />
+    <input type="hidden" name="id" value="<?php echo (int) $album->ID; ?>" />
     <input type="submit" name="del" value="Yes" />
     <input type="submit" name="del" value="No" />
     </div>
zf2-tutorial/module/Album/view/album/album/edit.phtml
         'album',
         array(
             'action' => 'edit',
-            'id'     => $this->id,
+            'id'     => $this->ID,
         )
     ));
 $form->prepare();
 
 echo $this->form()->openTag($form);
-echo $this->formHidden($form->get('id'));
-echo $this->formRow($form->get('title'));
-echo $this->formRow($form->get('artist'));
+echo $this->formHidden($form->get('ID'));
+echo $this->formRow($form->get('TITLE'));
+echo $this->formRow($form->get('ARTIST'));
 echo $this->formSubmit($form->get('submit'));
 echo $this->form()->closeTag();
zf2-tutorial/module/Album/view/album/album/index.phtml
</tr>
 <?php foreach ($albums as $album) : ?>
 <tr>
-<td><?php echo $this->escapeHtml($album->title);?></td>
-<td><?php echo $this->escapeHtml($album->artist);?></td>
+<td><?php echo $this->escapeHtml($album->TITLE);?></td>
+<td><?php echo $this->escapeHtml($album->ARTIST);?></td>
 <td>
 <a href="<?php echo $this->url('album',
-            array('action'=>'edit', 'id' => $album->id));?>">Edit</a>
+            array('action'=>'edit', 'id' => $album->ID));?>">Edit</a>
     <a href="<?php echo $this->url('album',
-            array('action'=>'delete', 'id' => $album->id));?>">Delete</a>
+            array('action'=>'delete', 'id' => $album->ID));?>">Delete</a>
     </td>
     </tr>
 <?php endforeach; ?>
zf2-tutorial/module/Album/src/Album/Model/Album.php
 class Album
 {
-    public $id;
-    public $artist;
-    public $title;
+    public $ID;
+    public $ARTIST;
+    public $TITLE;
     protected $inputFilter;
 
     public function exchangeArray($data)
     {
-        $this->id     = (!empty($data['id'])) ? $data['id'] : null;
-        $this->artist = (!empty($data['artist'])) ? $data['artist'] : null;
-        $this->title  = (!empty($data['title'])) ? $data['title'] : null;
+        $this->ID     = (!empty($data['ID'])) ? $data['ID'] : null;
+        $this->ARTIST = (!empty($data['ARTIST'])) ? $data['ARTIST'] : null;
+        $this->TITLE  = (!empty($data['TITLE'])) ? $data['TITLE'] : null;
     }
 
     public function getArrayCopy()

and

             $factory     = new InputFactory();
 
             $inputFilter->add($factory->createInput(array(
-                        'name'     => 'id',
+                        'name'     => 'ID',
                         'required' => true,
                         'filters'  => array(
                             array('name' => 'Int'),

and

                     )));
 
             $inputFilter->add($factory->createInput(array(
-                        'name'     => 'artist',
+                        'name'     => 'ARTIST',
                         'required' => true,
                         'filters'  => array(
                             array('name' => 'StripTags'),

and

                     )));
 
             $inputFilter->add($factory->createInput(array(
-                        'name'     => 'title',
+                        'name'     => 'TITLE',
                         'required' => true,
                         'filters'  => array(
                             array('name' => 'StripTags'),
zf2-tutorial/module/Album/src/Album/Model/AlbumTable.php
     public function getAlbum($id)
     {
         $id  = (int) $id;
-        $rowset = $this->tableGateway->select(array('id' => $id));
+        $rowset = $this->tableGateway->select(array('ID' => $id));
         $row = $rowset->current();
         if (!$row) {
             throw new \Exception("Could not find row $id");

and

     public function saveAlbum(Album $album)
     {
         $data = array(
-            'artist' => $album->artist,
-            'title'  => $album->title,
+            'ARTIST' => $album->ARTIST,
+            'TITLE'  => $album->TITLE,
         );
 
-        $id = (int)$album->id;
+        $id = (int)$album->ID;
         if ($id == 0) {
             $this->tableGateway->insert($data);
         } else {
             if ($this->getAlbum($id)) {
-                $this->tableGateway->update($data, array('id' => $id));
+                $this->tableGateway->update($data, array('ID' => $id));
             } else {
                 throw new \Exception('Form id does not exist');
             }

and

     public function deleteAlbum($id)
     {
-        $this->tableGateway->delete(array('id' => $id));
+        $this->tableGateway->delete(array('ID' => $id));
     }
 }
zf2-tutorial/module/Album/src/Album/Form/AlbumForm.php
         parent::__construct('album');
         $this->setAttribute('method', 'post');
         $this->add(array(
-                'name' => 'id',
+                'name' => 'ID',
                 'type' => 'Hidden',
             ));
         $this->add(array(
-                'name' => 'title',
+                'name' => 'TITLE',
                 'type' => 'Text',
                 'options' => array(
                     'label' => 'Title',
                 ),
             ));
         $this->add(array(
-                'name' => 'artist',
+                'name' => 'ARTIST',
                 'type' => 'Text',
                 'options' => array(
                     'label' => 'Artist',
zf2-tutorial/module/Album/src/Album/Controller/AlbumController.php
         }
 
         return array(
-            'id' => $id,
+            'ID' => $id,
             'form' => $form,
         );
     }

and

         }
 
         return array(
-            'id'    => $id,
+            'ID'    => $id,
             'album' => $this->getAlbumTable()->getAlbum($id)
         );
     }

When you create applications from scratch it will be straightforward to get it all working.

About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Categories
Archives
« May 2013 »
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
15
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today