PHP and Oracle: Christopher Jones

Introduction

The Edition-Based Redefinition (EBR) feature of Oracle Database 11gR2 is ideal for web sites that aim for no downtime when releasing application enhancements. Editioning allows certain database objects to have multiple versions all available at the same time. Applications can decide at runtime which version of the objects should be used.

In this example I show how Oracle editioning can be used to upgrade a "live" PHP application. The changes can be made and tested on the production database and then enabled for all users with a one keyword change to the application.

The objects that can be editioned by Oracle are:

• synonyms
• views
• PL/SQL object types:
  • function
  • library
  • package and package body
  • procedure
  • trigger
  • type and type body

Tables themselves can't be editioned but there is support for moving and viewing data across editions.

The Initial PHP Application

To start the example, run SQL*Plus as the user SYSTEM and grant edition access to the application user CJ:

  sqlplus system/systempassword
  grant create any edition to cj;
  alter user cj enable editions;

As the user CJ create a table of employees for the application:

  sqlplus cj/cj
  drop table myemp;
  create table myemp (name varchar2(10), hoursworked number);
  insert into myemp (name, hoursworked) values ('alison', 200);
  insert into myemp (name, hoursworked) values ('kris',   200);
  insert into myemp (name, hoursworked) values ('wenji',  200);
  commit;

Now create the stored function that calculates the number of days of vacation an employee is eligible for. This function is stored in the database so all Oracle applications can reuse the same logic:

  create or replace function 
            vacationdaysleft(p_name in varchar2) return number as
    vdl number;
  begin
   -- For every 40 hours worked, you get 1 day of vacation
   select floor(hoursworked / 40) into vdl 
     from myemp 
     where myemp.name = p_name;
   return vdl;
  end;
  /

Test the function in SQL*Plus by calling:

  select name, vacationdaysleft(name) from myemp;

This returns:

  NAME                           VACATIONDAYSLEFT(NAME)
  ------------------------------ ----------------------
  alison                                              5
  kris                                                5
  wenji                                               5

In PHP OCI8 1.3 the function could be used in an example file, vacation.php:

  <?php

  $c = oci_connect('cj', 'cj', 'localhost/orcl');

  $s = oci_parse($c, 'alter session set edition = ora$base');
  oci_execute($s);

  $s = oci_parse($c, "begin :vdl := vacationdaysleft(:name); end;");
  oci_bind_by_name($s, ":vdl", $vdl, 10);
  oci_bind_by_name($s, ":name", $name, 10);
  $name = 'alison';
  oci_execute($s);

  echo "$name has ".$vdl." days vacation left" . PHP_EOL;

  ?>

This makes a simple call to the stored function and returns in $vdl the number of days vacation for person $name.

Note the ALTER SESSION call. I've added it because I know I'll be using the editioning feature. The 'ora$base' token means to use the root or first edition of objects, i.e. the PL/SQL function vacationdaysleft() we just created. The ALTER statement should appear pretty much immediately after the connection is made - you don't want parts of the application to be using the wrong edition.

The PHP script produces output like:

  $ php vacation.php
  alison has 5 days vacation left

This is good. We put the application into production and employees start using it. All is well for a while.

Enhancing the Application

Now assume the rate used to calculate vacation hours needs to be changed so that now for every 30 hours worked, employees get one day of vacation. Also we want the calculation to include how many days vacation they have already taken.

First, we need a new column to store the vacation they have taken. Adding this column won't affect the running PHP application since it doesn't know about it (this is a good reminder never to do "select *" in an application). The new column is:

  alter table myemp add daysvacationtaken number;

We need to populate that column manually, here with values I've invented:

  update myemp set daysvacationtaken = 2 where name = 'alison';
  update myemp set daysvacationtaken = 3 where name = 'kris';
  update myemp set daysvacationtaken = 0 where name = 'wenji';
  commit;

For more complex scenarios the editioning feature has CROSSEDITION triggers and editioning views to help ensure the appropriate data is available and used correctly in the old and new editions of the application. Remember our current and future enhanced versions of the PL/SQL function can be accessing the same table concurrently - only the PL/SQL code has multiple versions.

Now we need to create the new version of the PL/SQL function. In SQL*Plus create a new edition and set it to be in use for this session:

  sqlplus cj/cj
  create edition e2;
  alter session set edition = e2;

Now create the updated version of the procedure. Because the SQL*Plus session is in edition 'e2' this won't affect the PHP application which runs using edition 'ora$base'. The enhanced function can be created with:

  create or replace function 
            vacationdaysleft(p_name in varchar2) return number as
    vdl number;
  begin
   -- For every 30 hours worked, you get 1 day of vacation
   select floor(hoursworked / 30) - daysvacationtaken into vdl 
     from myemp 
     where myemp.name = p_name;
   return vdl;
  end;
  /

Querying it shows the updated values:

  select name, vacationdaysleft(name) from myemp;

  NAME                           VACATIONDAYSLEFT(NAME)
  ------------------------------ ----------------------
  alison                                              4
  kris                                                3
  wenji                                               6

This is all done while users continue to use the PHP application live and get the original results. You can check that the script still returns the "old" value:

  $ php vacation.php
  alison has 5 days vacation left

Now edit vacation.php and change the ALTER SESSION statement to the new edition:

  $s = oci_parse($c, 'alter session set edition = e2');

When run, the script output now shows the updated calcuation of vacation time:

  $ php vacation.php
  alison has 4 days vacation left

You can see that the roll out of application updates can be made faster and have more thorough testing, increasing the availability and reliability of web sites.

Summary

Oracle Database 11g Release 2 Editioning helps PHP web sites meet the goals of minimal downtime with the frequent upgrade cycle needed by modern web applications. It allows PHP applications, their often complex stored business logic, and large data sets to be updated ready for going live in production without impacting the operation of existing users.

After setting up the edition objects, the simple process of rolling out a new PHP script with the updated edition name allows application upgrades to occur rapidly.

The Oracle manuals have more information about using and managing editions.