However, when you use a database that is on a different computer than the WEB server, caching a database result set is usually a good idea. However, determining the best caching strategy based on your situation is a challenge. For example, for applications that use the most recent database result set, the time-triggered caching method (the common method of caching systems, which assumes that the cache is regenerated each time the expiration timestamp is reached) may not be a satisfactory solution. In this case, you need to adopt a mechanism that notifies the application whenever the application needs to change the cached database data so that the application keeps cached out-of-date data in line with the database. In this case, it is convenient to use the database change notification (a new Oracle database, version 2nd feature).
Getting Started with "Database change notifications"
The usage of the database change notification attribute is very simple: Create a notification handler for the notification execution-a Pl/sql stored procedure or client OCI callback function. Then, register a query for the database object to which you want to receive change notifications, so that the notification handler is invoked whenever the transaction changes any of its objects and commits. In general, the notification handler sends the name of the modified table, the type of the change, and the row ID (optional) of the changed line to the client listener, so that the client application can perform the appropriate processing in the response.
To see how the Database Change notification feature works, consider the following example. Suppose your PHP application accesses the OE. Orders stored in the Orders table as well as OE. The order items stored in the Order_items. Since it is rare to change the information that has been placed on orders, you may want the application to simultaneously cache query result sets for orders and Order_items tables. To avoid accessing expired data, you can use database change notification, which makes it easy for your application to learn about changes to the data stored in the two tables above.
You must first grant the change NOTIFICATION system permissions and EXECUTE on Dbms_changenotification permissions to the OE user to register queries against ORDERS and Order_items tables in order to receive notifications and responses to this A DML or DDL change made by two tables. To do this, you can execute the following commands from the SQL command-line tool, such as Sql*plus.
Connect/as SYSDBA;
GRANT change NOTIFICATION to OE;
GRANT EXECUTE on dbms_change_notification to OE;
Make sure that the Init.ora parameter job_queue_processes is set to a value other than 0 to receive pl/sql notifications. Alternatively, you can use the following ALTER SYSTEM command:
ALTER SYSTEM SET "job_queue_processes" = 2; Then, after you connect with Oe/oe, you can create a notification handler. But first, you must create a database object that will be used by the notification handler. For example, you might want to create one or more database tables to notify the handler to log changes to the registry. In the following example, you will create a nfresults table that records the date and time the change occurred, the name of the table that was modified, and a message stating whether the notification handler successfully sent the notification message to the client.
CONNECT Oe/oe;
CREATE TABLE Nfresults (
Operdate DATE,
Tblname VARCHAR2 (60),
Rslt_msg VARCHAR2 (100)
);
In practice, you may need to create more tables to record information such as notification events and row IDs for the rows that are changed, but for this article, the Nfresults table is perfectly up to suit.
Using Utl_http to send notifications to clients
You may also want to create one or more pl/sql stored procedures and call these stored procedures from the notification handlers to achieve a more maintainable and flexible solution. For example, you might want to create a stored procedure that sends notification messages to clients. "Listing 1" is the Pl/sql process sendnotification. This procedure uses the UTL_HTTPPL package to send a change notification to the client application.
Listing 1. Using Utl_http to send notifications to clients
Copy Code code as follows:
CREATE OR REPLACE PROCEDURE sendnotification (url in VARCHAR2,
Tblname in VARCHAR2, order_id into VARCHAR2) is
Req Utl_http. REQ;
Resp Utl_http. RESP;
Err_msg VARCHAR2 (100);
TBL VARCHAR (60);
BEGIN
Tbl:=substr (Tblname, INSTR (tblname, '. ', 1, 1) +1, 60);
BEGIN
Req: = Utl_http. Begin_request (url| | order_id| | ' & ' | | ' Table= ' | | TBL);
RESP: = utl_http. Get_response (req);
INSERT into Nfresults VALUES (sysdate, Tblname, resp.reason_phrase);
Utl_http. End_response (RESP);
EXCEPTION when others THEN
Err_msg: = SUBSTR (SQLERRM, 1, 100);
INSERT into Nfresults VALUES (sysdate, Tblname, err_msg);
End;
COMMIT;
End;
/
As shown in Listing 1, sendnotification to Utl_http. Sends a notification message to the client in the form of an HTTP request issued by the Begin_request function. This URL contains the order_id that has changed the row in the ORDERS table. Then, it uses utl_http. Get_response gets the response information sent by the client. In fact, SendNotification does not need to handle the entire response returned by the client, but instead gets only a short message stored in the Reason_phrase field of the RESP record (describes the status code).
To create a notification handler
You can now create a notification handler that will send a change notification to the client with the help of the sendnotification procedure described above. Take a look at the Pl/sql process orders_nf_callback in Listing 2.
Listing 2. Processing to OE. Notification handlers for notifications of changes made to the ORDERS table
Copy Code code as follows:
CREATE OR REPLACE PROCEDURE orders_nf_callback (ntfnds in SYS. CHNF$_DESC) is
Tblname VARCHAR2 (60);
Numtables number;
Event_type number;
row_id VARCHAR2 (20);
NumRows number;
ord_id VARCHAR2 (12);
URL VARCHAR2 (256): = ' http://webserverhost/phpcache/dropResults.php?order_no= ';
BEGIN
Event_type: = Ntfnds.event_type;
Numtables: = Ntfnds.numtables;
IF (event_type = dbms_change_notification. Event_objchange) THEN
For I in 1..numtables loop
Tblname: = Ntfnds.table_desc_array (i). table_name;
IF (Bitand (Ntfnds.table_desc_array (i). Opflags,
Dbms_change_notification. All_rows) = 0) THEN
NumRows: = Ntfnds.table_desc_array (i). NumRows;
ELSE
NumRows: = 0;
End IF;
IF (tblname = ' OE. ORDERS ') THEN
For J in 1..numrows LOOP
ROW_ID: = Ntfnds.table_desc_array (i). Row_desc_array (j). row_id;
SELECT order_id into ord_id from orders WHERE rowid = row_id;
SendNotification (URL, tblname, ord_id);
End LOOP;
End IF;
End LOOP;
End IF;
COMMIT;
End;
/
As shown in Listing 2, this notification handler will be SYS. The Chnf$_desc object is used as a parameter and then uses its properties to get the details of the change. In this example, this notification handler will only handle notifications published by the database in response to a DML or DDL change to a registered object (that is, only when the notification type is Event_objchange) and ignores notifications about other database events, such as instance startup or instance shutdown. Starting with the above version, the handler can handle the OE. Notification of changes issued by each affected row in the ORDERS table. In the "adding tables to Existing Registrations" section later in this article, you will add a few lines of code to the handler so that it can handle the OE. A notification that is issued by a modified row in the Order_items table.
Create registration for change notification
After you create a notification handler, you must create a query registration for it. For the purposes of this example, you must be in the process of registering for OE. The order table executes the query and designates orders_nf_callback as the notification handler. You also need to specify the Qos_rowids option in the Dbms_change_notification package to enable ROWID level granularity in the notification message. Listing 3 is a pl/sql block that creates a query registration for the ORDERS_NF_CALLBACK notification handler.
Listing 3. To create a query registration for an alert handler
Copy Code code as follows:
DECLARE
Regds SYS. Chnf$_reg_info;
Regid number;
ORD_ID number;
Qosflags number;
BEGIN
Qosflags: = dbms_change_notification. Qos_reliable +
Dbms_change_notification. Qos_rowids;
Regds: = SYS. Chnf$_reg_info (' Orders_nf_callback ', Qosflags, 0,0,0);
Regid: = dbms_change_notification. New_reg_start (REGDS);
SELECT order_id into ord_id from orders WHERE rownum<2;
Dbms_change_notification. Reg_end;
End;
/
This example creates a registration for the ORDERS table and uses orders_nf_callback as the notification handler. Now, if you use a DML or DDL statement to modify the ORDERS table and commit the transaction, the Orders_nf_callback function is called automatically. For example, you might execute the following UPDATE statement against the ORDERS table and commit the transaction:
UPDATE ORDERS SET order_mode = ' direct ' WHERE order_id=2421;
UPDATE ORDERS SET order_mode = ' direct ' WHERE order_id=2422;
COMMIT;
To ensure that the database publishes notifications to respond to the above transactions, you can check the Nfresults table:
SELECT to_char (operdate, ' dd-mon-yy hh:mi:ss ') operdate,
Tblname, rslt_msg from Nfresults;
The results should be as follows:
Operdate tblname rslt_msg
--------------------- ----------- ---------
02-mar-06 04:31:28 OE. ORDERS not Found
02-mar-06 04:31:29 OE. ORDERS not Found
As you can clearly see from the results above, Orders_nf_callback is working, but client script is not found. This is not surprising in this example, because you did not create the dropresults.php script specified in the URL.
To add a table to an existing registration
The previous section describes how to use the Change Notification service to notify the database when changes are made to the registered object (in the previous example, the ORDERS table). However, from a performance perspective, the client application might prefer to cache the Order_items table rather than the order table itself, because it has to retrieve only one row from the Orders table each time it accesses the orders, but it must also retrieve multiple rows from the Order_items table. In practice, an order may contain dozens of or even hundreds of order items.
Since you have registered a query with the ORDERS table, you do not have to create a registration to register the query for the Order_items table. Instead, you can use an existing registration. To do this, you first need to retrieve the ID of an existing registration. You can do this by executing the following query:
SELECT Regid, table_name from User_change_notification_regs; The result may be as follows:
REGID table_name
----- --------------
241 OE. ORDERS
After obtaining the registration ID, you can use Dbms_change_notification. The Enable_reg function adds a new object to the registration as follows:
Copy Code code as follows:
DECLARE
ORD_ID number;
BEGIN
Dbms_change_notification. Enable_reg (241);
SELECT order_id into ord_id from Order_items WHERE rownum < 2;
Dbms_change_notification. Reg_end;
End;
It's done! From now on, the database generates a notification to respond to any changes made to ORDERS and Order_items, and calls the Orders_nf_callback procedure to process the notification. Therefore, the next step is to edit the orders_nf_callback so that it can handle notifications generated by DML operations on the Order_items table. However, before recreating the orders_nf_callback procedure, you need to create the following table types that will be referenced during the update process:
The CREATE TYPE Rdesc_tab as TABLE of SYS. Chnf$_rdesc; Then, return to the list after the following line of code:
Copy Code code as follows:
IF (tblname = ' OE. ORDERS ') THEN
For J in 1..numrows LOOP
ROW_ID: = Ntfnds.table_desc_array (i). Row_desc_array (j). row_id;
SELECT order_id into ord_id from orders WHERE rowid = row_id;
SendNotification (URL, tblname, ord_id);
End LOOP;
End IF;
Insert the following code:
Copy Code code as follows:
IF (tblname = ' OE. Order_items ') THEN
For rec in (SELECT DISTINCT (o.order_id) o_id from
TABLE (CAST (Ntfnds.table_desc_array (i). Row_desc_array as Rdesc_tab)) T,
Orders O, Order_items d where t.row_id = D.rowid and d.order_id=o.order_id)
LOOP
SendNotification (URL, tblname, rec.o_id);
End LOOP;
End IF;
After you re-create the orders_nf_callback, you need to test that it works. To do this, you can execute the following UPDATE statement against the Order_items table and commit the transaction:
UPDATE order_items SET quantity = 160 WHERE order_id=2421 and line_item_id=1;
UPDATE order_items SET quantity = 160 WHERE order_id=2421 and line_item_id=2;
COMMIT;
Then, check the Nfresults table as follows:
SELECT to_char (operdate, ' dd-mon-yy hh:mi:ss ') operdate,
Rslt_msg from nfresults WHERE tblname = ' OE. Order_items '; The output might look like the following:
Operdate rslt_msg
------------------- --------------
03-mar-06 12:32:27 not Found
You may be wondering why you inserted only one row into the Nfresults table-after all, you updated the two rows in the Order_items table. In fact, the two updated rows have the same order_id– that they belong to the same order. Here, we assume that the client application will use a statement to select all order items for the order, so it does not need to know exactly which order items have changed for an order. Instead, the client needs to know the order ID of at least one order item that was modified, deleted, or inserted.
Building the Client
Now that you have created registrations for orders and Order_items tables, we will look at how client applications that access the orders and their order items stored in those tables use change notifications. To do this, you can build a PHP application that caches query results for the above tables and takes appropriate action to respond to notifications about changes to those tables (received from the database server). An easy way to do this is to use the Pear::cache_lite package, which gives you a reliable mechanism for keeping cached data up to date. In particular, you can use the Cache_lite_function class (a part of the Pear::cache_lite package), through which you can cache function calls.
For example, you can create a function that establishes a database connection, executes a SELECT statement against the database, obtains the retrieved results, and eventually returns the results as an array. You can then cache the array of results returned by the function by using the call method of the Cache_lite_function instance, so that you can read the arrays from the local cache rather than from the back-end database, which can significantly improve the performance of your application. Then, when you receive notification of cached data changes, you will use the drop method of the Cache_lite_function instance to delete out-of-date data in the cache.
Looking back at the example in this article, you might want to create two functions for your application to interact with the database: The first function will query the Orders table and return the order with the specified ID, while another function will query the Order_items table and return the order item for that order. Listing 4 shows a getorderfields.php script that contains the Getorderfields function that accepts the order ID and returns an associative array that contains some fields retrieved for the order.
Listing 4. Get the field for the specified order
Copy Code code as follows:
<?php
File:getOrderFields.php
Require_once ' connect.php ';
function Getorderfields ($order _no) {
if (! $rsConnection = getconnection ()) {
return false;
}
$strSQL = "Select To_char (order_date) order_date, customer_id,
Order_total from ORDERS WHERE order_id =:order_no ";
$rsStatement = Oci_parse ($rsConnection, $strSQL);
Oci_bind_by_name ($rsStatement, ": Order_no", $order _no, 12);
if (!oci_execute ($rsStatement)) {
$err = Oci_error ();
Print $err [' message '];
Trigger_error (' Query failed: '. $err [' message ']);
return false;
}
$results = Oci_fetch_assoc ($rsStatement);
return $results;
}
?>
"Listing 5" is a getorderitems.php script. The script contains the Getorderitems function, which accepts the order ID and returns a two-dimensional array that contains the lines that represent the order items for the order.
Listing 5. Get order items for a specified order
Copy Code code as follows:
<?php
File:getOrderItems.php
Require_once ' connect.php ';
function Getorderitems ($order _no) {
if (! $rsConnection = getconnection ()) {
return false;
}
$strSQL = "SELECT * FROM Order_items WHERE
order_id =:order_no ORDER by line_item_id ";
$rsStatement = Oci_parse ($rsConnection, $strSQL);
Oci_bind_by_name ($rsStatement, ": Order_no", $order _no, 12);
if (!oci_execute ($rsStatement)) {
$err = Oci_error ();
Trigger_error (' Query failed: '. $err [' message ']);
return false;
}
$nrows = Oci_fetch_all ($rsStatement, $results);
Return Array ($nrows, $results);
}
?>
Note that all two functions require a connect.php script that contains the Getconnection function that returns a database connection. Listing 6 is the connect.php script:
Listing 6. Getting a database connection
Copy Code code as follows:
<?php
File:connect.php
function getconnection () {
$dbHost = "Dbserverhost";
$dbHostPort = "1521";
$dbServiceName = "OrclR2";
$USR = "OE";
$PSWD = "OE";
$DBCONNSTR = "(Description= (address= (protocol=tcp) (host=". $dbHost. ")
(port= ". $dbHostPort.")) (Connect_data= (service_name= ". $dbServiceName.")) ";
if (! $dbConn = Oci_connect ($usr, $PSWD, $dbConnStr)) {
$err = Oci_error ();
Trigger_error (' Failed to connect ', $err [' message ']);
return false;
}
return $dbConn;
}
?>
Now that you have created all the functions required to communicate with the database, we'll look at how the Cache_lite_function class works. Listing 7 is the testcache.php script that uses the Cache_lite_function class to cache the results of the above functions.
Listing 7. Using the Pear::cache_lite cache
Copy Code code as follows:
<?php
File:testCache.php
Require_once ' getorderitems.php ';
Require_once ' getorderfields.php ';
Require_once ' cache/lite/function.php ';
$options = Array (
' Cachedir ' => '/tmp/',
' LifeTime ' => 86400
);
if (!isset ($_get[' order_no ')) {
Die (' The order_no parameter is required ');
}
$order _no=$_get[' order_no '];
$cache = new Cache_lite_function ($options);
if ($orderfields = $cache->call (' Getorderfields ', $order _no)) {
Print "print "<table>";
Print "<tr><td>DATE:</td><td>" $orderfields [' order_date ']. " </td></tr> ";
Print "<tr><td>CUST_ID:</td><td>" $orderfields [' customer_id ']. " </td></tr> ";
Print "<tr><td>TOTAL:</td><td>" $orderfields [' order_total ']. " </td></tr> ";
print "</table>";
} else {
Print "Some problem occurred while getting order fields!\n";
$cache->drop (' Getorderfields ', $order _no);
}
if (list ($nrows, $orderitems) = $cache->call (' Getorderitems ', $order _no)) {
Print "Print "<table border=1>";
print "<tr>\n";
while (the list ($key, $value) = each ($orderitems)) {
Print "<th> $key </th>\n";
}
print "</tr>\n";
for ($i = 0; $i < $nrows; $i + +) {
print "<tr>";
Print "<td>" $orderitems [' order_id '] [$i]. " </td> ";
Print "<td>" $orderitems [' line_item_id '] [$i]. " </td> ";
Print "<td>" $orderitems [' product_id '] [$i]. " </td> ";
Print "<td>" $orderitems [' unit_price '] [$i]. " </td> ";
Print "<td>" $orderitems [' QUANTITY '] [$i]. " </td> ";
print "</tr>";
}
print "</table>";
} else {
Print "Some problem occurred while getting the order line items";
$cache->drop (' Getorderitems ', $order _no);
}
?>
The testcache.php script in Listing 7 should be associated with the Order_no URL parameter (representing the OE.) Order IDs stored in the Orders table) are called together. For example, to retrieve information related to an order with ID 2408, you need to enter the URL as follows in the browser:
http://webserverhost/phpcache/testCache.php?order_no=2408 results, the browser produces the following output:
Order #2408
date:29-jun-99 06.59.31.333617 AM
cust_id:166
total:309
order_id line_item_id product_id Unit_price QUANTITY
2408 1 2751 61 3
2408 2 2761 26 1
2408 3 2783 10 10
Now, if you click the Reload button in the browser, the testcache.php script will not invoke the Getorderfields and Getorderitems functions again. Instead, it will read their results from the local cache. Therefore, the local cache can meet the needs of each getorderfields or Getorderitems invocation using order_no=2108 from the current 24 hours (because the lifeTime is set to 86,400 seconds). Note, however, that the Cache_lite_function class does not provide an API to test whether a given function with a given parameter has an available cache. Therefore, it may be tricky to determine whether the application is actually reading the cache or still executing the function each time the function is invoked with the same parameters. For example, in the above example, to ensure that the caching mechanism works correctly, you can temporarily change the connection information specified in the connect.php script so that it cannot establish a database connection, such as specifying an incorrect database server host name and then using order_no=2108 again to run testcache.php script. If the cache is working correctly, the browser's output should be the same as before.
In addition, you can check the cache directory, which is passed to the constructor of the Cache_lite_function class as the value of the Cachedir option (in this example,/tmp). In this directory, you will find two newly created cache files with names similar to the following: cache_7b181b55b55aee36ad5e7bd9d5a091ec_3ad04d3024f4cd54296f75c92a359154. Note that if you are a Windows user, you may want to save the cached file using the%systemdrive%\temp directory. If this is the case, you must set the Cachedir option to/temp/.
After verifying that the caching mechanism is working properly, you can then create a PHP to handle the change notifications received from the database server. "Listing 8" is a dropresult.php script. The database server calls the script to respond to changes to ORDERS and Order_items tables.
Listing 8. Processing notification of changes received from the database server
Copy Code code as follows:
<?php
File:dropResults.php
Require_once ' cache/lite/function.php ';
$options = Array (
' Cachedir ' => '/tmp/'
);
$cache = new Cache_lite_function ($options);
if (Isset ($_get[' order_no ')) && isset ($_get[' table ')) {
if ($_get[' table ']== ' Order_items ') {
$cache->drop (' Getorderitems ', $_get[' order_no '));
}
if ($_get[' table ']== ' ORDERS ') {
$cache->drop (' Getorderfields ', $_get[' order_no '));
}
}
?>
After you create the dropresult.php script, make sure that the URL specified in the notification handler, as shown in Listing 2, is correct. Then, in Sql*plus or similar tools, connect in Oe/oe and execute an UPDATE statement that affects the same order that was previously accessed by the Testcache.php script in this section (here is an order with ID 2408):
UPDATE ORDERS SET order_mode = ' direct ' WHERE order_id=2408;
UPDATE order_items SET quantity = 3 WHERE order_id=2408 and line_item_id=1;
UPDATE order_items SET quantity = 1 WHERE order_id=2408 and line_item_id=2;
COMMIT;
In response to the above update, the notification handler described earlier in this article will run the dropresults.php script two times at a time using the following URLs:
Http://webserverhost/phpcache/dropResults.php?order_no=2408&table=ORDERS
Http://webserverhost/phpcache/dropresults.php?order_no=2408&table=ORDER_ITEMS
As you can see clearly in Listing 8, the dropresult.php script does not flush the cache after receiving the change notification from the database server. It simply deletes the cached file that contains the expired data. Therefore, if you check the cache directory now, you will see that the cached file that you created when you run the testcache.php script using order_no=2408 has disappeared. This actually means that testcache.php will fetch the data from the back-end database instead of the local cache the next time it requests data related to an order with ID 2408.
You will find this method useful when the application requests a result set that is likely to change before the application uses it. For the example in this article, this means that the data associated with a particular order may be changed several times before testcache.php accesses the order. In this way, the application does a lot of unnecessary work by refreshing its cache immediately after receiving change notification from the database server.
However, if you want the dropresult.php script to flush the cache immediately after you receive the change notification, you can call the call method of the Cache_lite_function instance after calling the drop method and specify the same parameters for both calls. In this case, you should also ensure that the getorderfields.php and getorderitems.php scripts are included so that dropresults.php can invoke the Getorderfields and Getorderitems functions to flush the cache. "Listing 9" is the modified dropresult.php script.
Listing 9. Refreshes the cache immediately after receiving change notification
Copy Code code as follows:
<?php
File:dropResults.php
Require_once ' cache/lite/function.php ';
Require_once ' getorderitems.php ';
Require_once ' getorderfields.php ';
$options = Array (
' Cachedir ' => '/tmp/',
' LifeTime ' => 86400
);
$cache = new Cache_lite_function ($options);
if (Isset ($_get[' order_no ')) && isset ($_get[' table ')) {
if ($_get[' table ']== ' Order_items ') {
$cache->drop (' Getorderitems ', $_get[' order_no '));
$cache->call (' Getorderitems ', $_get[' order_no '));
}
if ($_get[' table ']== ' ORDERS ') {
$cache->drop (' Getorderfields ', $_get[' order_no '));
$cache->call (' Getorderfields ', $_get[' order_no '));
}
}
?>
The above method can be useful if the data stored in ORDERS and Order_items tables is rarely changed and the application accesses it frequently.
Summary
If your PHP application interacts with Oracle database version 2nd 10g, you can take advantage of the database change notification attribute, which enables applications to receive notifications in response to DML changes to objects associated with the issued request. With this feature, you do not have to update the cache in your application for a specific period of time. Instead, the operation is performed only if the result set of the registered query has changed.
