As we all know, the results of cache database queries can significantly shorten the script execution time and minimize the load on the database server. This technology is very effective if the data to be processed is basically static. This is because many data requests to the remote database can be obtained from the local cache, this eliminates the need to connect to the database, perform queries, and obtain results. However, when the database you are using is located on a different computer from the Web server, it is always a good way to cache the database result set. However, it is difficult to determine the best cache policy based on your situation. For example, for applications that use the latest database result set, the time-triggered cache method (a common method used by the cache system, which assumes that the cache is regenerated every time the expiration timestamp is reached) it may not be a satisfactory solution. In this case, you need to adopt a mechanism that notifies the application whenever the database data to be cached by the application changes, so that the application can keep the cached expired data consistent with the database. In this case, it is very convenient to use "database Change Notification" (a new Oracle database version 10g 2nd feature.
Getting started with "database change notification"
The usage of the "database change notification" feature is very simple: Create a notification handler for notification execution-a PL/SQL Stored procedure or a client OCI callback function. Then, register a query for the database object whose change notification is to be received so that the notification handler is called whenever the transaction changes any of the objects and commits them. Generally, the notification handler sends the modified table name, modified type, and row ID (optional) to the client listener, so that the client application can perform corresponding processing in the response.
To learn how the "database change notification" feature works, consider the following example. Assume that your PHP application accesses the ORDERS stored in the OE. ORDERS table and the order items stored in OE. ORDER_ITEMS. Since the order information is rarely changed, you may want the application to cache query result sets for both ORDERS and ORDER_ITEMS tables. To avoid access to expired data, you can use the "database change notification", which allows your application to conveniently learn the changes to the data stored in the preceding two tables.
You must grant the change notification system permission and the execute on DBMS_CHANGENOTIFICATION permission to the OE user before registering the query for the ORDERS and ORDER_ITEMS tables, to receive notifications and respond to DML or DDL changes made to these two tables. Therefore, you can run the following commands from SQL command line tools (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 non-zero value to receive PL/SQL notifications. Alternatively, you can use the following alter system command:
Alter system set "job_queue_processes" = 2; then, after connecting with OE/OE, you can create a notification handler. First, you must create a database object that will be used by the notification handler. For example, you may need to create one or more database tables to notify the handler to record registry changes. In the following example, you will create an nfresults table to record the following information: the date and time when the change occurred, the name of the modified table, and a message (indicating whether the notification handler successfully sends 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 notification events and the row ID of the modified row. However, in this article, the nfresults table can fully meet your needs.
Use UTL_HTTP to send notifications to clients
You may also create one or more PL/SQL Stored procedures and call them from the notification handler to implement a more maintainability and flexibility solution. For example, you may want to create a stored procedure to send notification messages to the client. "Listing 1" is the sendNotification for PL/SQL processes. In this process, the UTL_HTTPPL package is used to send a change notification to the client application.
Listing 1. use UTL_HTTP to send notifications to the client
The code is as follows:
Create or replace procedure sendNotification (url IN VARCHAR2,
Tblname IN VARCHAR2, order_id IN 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 "List 1", sendNotification sends a notification message to the client in the form of an HTTP request sent by the UTL_HTTP.BEGIN_REQUEST function. This URL contains the order_id of the changed row in the ORDERS table. Then, it uses UTL_HTTP.GET_RESPONSE to obtain the response information sent by the client. In fact, sendNotification does not need to process the entire response returned by the client. Instead, it only obtains a short message (describing the status code) stored in the reason_phrase field of the RESP record ).
Create a notification handler
Now you can create a notification handler that sends a change notification to the client using the sendNotification process described above. Let's take a look at the PL/SQL process orders_nf_callback in listing 2.
Listing 2. notification handler that processes the notifications for changes to the OE. ORDERS table
The code is 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_icationication.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 "list 2", this notification handler uses the SYS. CHNF $ _ DESC object as a parameter and uses its properties to obtain the details of the change. In this example, the notification handler only processes the notifications published by the database in response to DML or DDL changes to the registered object (that is, only when the notification type is EVENT_OBJCHANGE, and ignore notifications about other database events (such as instance startup or instance shutdown. From a later version, the handler can handle change notifications for each affected row in the OE. ORDERS table. In the "Add table to existing registration" section after this article, you will add several lines of code to the handler so that it can process. notification of modified rows in the ORDER_ITEMS table.
Create registration for Change Notification
After creating a notification handler, you must create a query registration for it. In this example, you must query the OE. ORDER table during registration and specify 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 notification messages. "Listing 3" is a PL/SQL block that creates a query registration for the orders_nf_callback notification handler.
Listing 3. create query registration for the notification handler
The code is as follows:
DECLARE
Regds sys. CHNF $ _ REG_INFO;
Regid NUMBER;
Ord_id NUMBER;
Qosflags NUMBER;
BEGIN
Qosflags: = dbms_change_icationication.qos_reliable +
Dbms_change_icationication.qos_rowids;
REGDS: = SYS. CHNF $ _ REG_INFO ('orders _ nf_callback', qosflags, 0, 0 );
Regid: = dbms_change_icationication.new_reg_start (REGDS );
SELECT order_id INTO ord_id FROM orders where rownum <2;
Dbms_change_icationication.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 DML or DDL statements to modify the ORDERS table and submit transactions, the orders_nf_callback function is automatically called. For example, you may execute the following UPDATE statement for the ORDERS table and submit 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 has published a notification to respond to the preceding 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
From the above results, we can clearly see that orders_nf_callback is working normally, but no client script is found. This is not surprising in this example, because you have not created the dropResults. php script specified in the URL.
Add a table to an existing registration
The previous section describes how to use the change notification service to notify the database when the registered object (ORDERS table in the preceding example) is changed. However, in terms of performance, the client application may prefer to cache the ORDER_ITEMS table rather than the query result set of the ORDERS table itself, because it accesses the order every time, you have to retrieve only one row from the ORDERS table, but you must also retrieve multiple rows from the ORDER_ITEMS table. In practice, an order may contain dozens or even hundreds of orders.
Because you have already registered a query for the ORDERS table, you do not have to create another registration to register a query for the ORDER_ITEMS table. Instead, you can use existing registration. To do this, you must first retrieve the existing registered ID. You can perform the following query to complete this task:
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 the dbms_change_icationication.enable_reg function to add a new object to the registration, as shown below:
The code is as follows:
DECLARE
Ord_id NUMBER;
BEGIN
Dbms_change_icationication.enable_reg (241 );
SELECT order_id INTO ord_id FROM order_items where rownum <2;
Dbms_change_icationication.reg_end;
END;
Finished! From now on, the database will generate a notification to respond to any changes made to ORDERS and ORDER_ITEMS, and call the orders_nf_callback process to process the notification. Therefore, the next step is to edit orders_nf_callback so that it can process the notifications generated by performing the DML operation on the ORDER_ITEMS table. However, before re-creating the orders_nf_callback process, you must create the following table types that will be referenced during the update process:
Create type rdesc_tab as table of sys. CHNF $ _ RDESC; then, return to the list, after the following code line:
The code is 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:
The code is 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 recreate orders_nf_callback, you need to test whether it works normally. Therefore, you can execute the following UPDATE statement for the ORDER_ITEMS table and submit 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 may be as follows:
OPERDATE RSLT_MSG
---------------------------------
03-mar-06 12:32:27 Not Found
You may be wondering why only one row is inserted into the nfresults table-after all, you have updated the two rows in the ORDER_ITEMS table. Actually, the two updated rows have the same order_id-that is, 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 been changed for an order. On the contrary, the client needs to know the order ID of at least one order item modified, deleted, or inserted.
Build a client
Now you have created registration for the ORDERS and ORDER_ITEMS tables. next we will learn how to use the change notification for the client applications that access the ORDERS stored in these tables and their order items. Therefore, you can build a PHP application that caches the query results for the above tables, and take appropriate actions to respond to notifications about changes to these tables (these notifications are received from the database server ). A simple method is to use the PEAR: Cache_Lite package, which provides you with a reliable mechanism to keep the cached data up-to-date. In particular, you can use the Cache_Lite_Function class (part of the PEAR: Cache_Lite package) to cache function calls.
For example, you can create a function to perform the following tasks: establish a database connection, execute select statements for the database, obtain the search results, and finally return results in an array. Then, you can cache the result array returned by the function through the call method of the Cache_Lite_Function instance, so that you can read these arrays from the local cache rather than from the back-end database, which can significantly improve the application performance. Then, when you receive a notification of changes to the cache data, you will use the drop method of the Cache_Lite_Function instance to delete the expired data in the cache.
Looking back at the example in this article, you may want to create two functions for the application to interact with the database: the first function will query the ORDERS table and return the order with the specified ID, another function queries the ORDER_ITEMS table and returns the order items of the order. "Listing 4" shows the getOrderFields. php script that contains the getOrderFields function (this function accepts the order ID and returns an associated array containing some fields of the retrieved order.
Listing 4. retrieving the fields of a specified order
The code is as follows:
// 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 the getOrderItems. php script. This script contains the getOrderItems function, which accepts the order ID and returns a two-dimensional array containing the rows that represent the order items.
Listing 5. obtain the order items of a specified order
The code is as follows:
// 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 the above two functions require the connect. php script, which should contain the GetConnection function that returns the database connection. Listing 6 is the connect. php script:
Listing 6. getting database connections
The code is as follows:
// 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 you have created all the functions required to communicate with the database. The following describes how the Cache_Lite_Function class works. Listing 7 is the testCache. php script, which uses the Cache_Lite_Function class to cache the results of the above functions.
Listing 7. using PEAR: Cache_Lite cache
The code is as follows:
// 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 "ORDER # $ order_no \ n ";
Print"
";Print"
| DATE: |
". $ Orderfields ['Order _ date']." |
";Print"
| CUST_ID: |
". $ Orderfields ['customer _ id']." |
";Print"
| TOTAL: |
". $ Orderfields ['Order _ total']." |
";Print"
";
} 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 "line items in order # $ order_no ";
Print"
";Print"
\ N ";While (list ($ key, $ value) = each ($ orderitems )){Print"
| $ Key | \ N ";}Print"
\ N ";For ($ I = 0; $ I <$ nrows; $ I ++ ){Print"
";Print"
| ". $ Orderitems ['Order _ id'] [$ I]." | ";Print"
". $ Orderitems ['line _ ITEM_ID '] [$ I]." | ";Print"
". $ Orderitems ['product _ id'] [$ I]." | ";Print"
". $ Orderitems ['unit _ price'] [$ I]." | ";Print"
". $ Orderitems ['quantity '] [$ I]." | ";Print"
";}Print"
";
} Else {
Print "Some problem occurred while getting order line items ";
$ Cache-> drop ('getorderitems ', $ order_no );
}
?>
The testCache. php script in "listing 7" should be called together with the order_no URL parameter (representing the order id stored in the OE. ORDER table. For example, to retrieve information related to an order with ID 2408, enter the following URL in the browser:
Http: // webserverhost/phpcache/testCache. php? Order_no = 2408 result, the browser will generate 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 call the getOrderFields and getOrderItems functions again. Instead, it reads their results from the local cache. Therefore, the local cache within 24 hours (because the lifeTime is set to 86400 seconds) from now on can meet the needs of calling each getOrderFields or getOrderItems with order_no = 2108. However, note that the Cache_Lite_Function class does not provide APIs to test whether a given function with a given parameter has an available cache. Therefore, it may be tricky to determine whether the application actually reads the cache or still executes the function each time it calls a function using the same parameter. For example, in the above example, to ensure that the cache mechanism works properly, you can temporarily change connect. the connection information specified in the php script so that it cannot establish a database connection. for example, specify a wrong database server host name and run testCache again using order_no = 2108. php script. If the cache works properly, the browser output should be the same as the previous one.
You can also check the cache directory, which is passed to the Cache_Lite_Function class constructor as the value of the cacheDir option (/tmp in this example. In this directory, you will find two newly created cache files, whose names are similar to: cached. Note: If you are a Windows user, you may need to use the % SystemDrive % \ temp directory to save the cache file. If so, you must set the cacheDir option to/temp /.
After verifying that the cache mechanism works properly, you can create a PHP file to handle the change notifications received from the database server. "Listing 8" is the dropResult. php script. The database server will call this script to respond to changes to the ORDERS and ORDER_ITEMS tables.
Listing 8. handling change notifications received from the database server
The code is as follows:
// 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 creating the dropResult. php script, make sure that the URL specified in the notification handler (as shown in listing 2) is correct. Then, connect with OE/OE in SQL * Plus or similar tools and execute the UPDATE statement. these statements will affect the previous use of testCache. the same order accessed by the php script (here is the 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 updates, the notification handler described earlier in this article will run the dropResults. php script twice one by one 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
From "list 8", you can clearly see that the dropResult. php script does not refresh the cache after receiving the change notification from the database server. It only deletes cache files that contain expired data. Therefore, if you check the cache directory, you will see that the cache file created when you run the testCache. php script using order_no = 2408 has disappeared. This actually means that testCache. php will retrieve the data from the backend database instead of the local cache in the next request for data related to the order ID 2408.
You will find that this method is useful when the result set requested by the application is likely to be changed before the application uses it. This means that data related to a specific order may be changed multiple times before testCache. php accesses the order. In this way, the application will do a lot of unnecessary work because it will immediately refresh its cache after receiving the change notification from the database server.
But if you want dropResult. when the php script refreshes the cache immediately after receiving the change notification, it can call the call method of the Cache_Lite_Function instance after calling the drop method and specify the same parameters for the two calls. In this case, ensure that the getOrderFields. php and getOrderItems. php scripts are included so that dropResults. php can call the getOrderFields and getOrderItems functions to refresh the cache. "Listing 9" is the modified dropResult. php script.
Listing 9. refresh the cache immediately after receiving the change notification
The code is as follows:
// 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']);
}
}
?>
If the data stored in the ORDERS and ORDER_ITEMS tables is rarely changed and frequently accessed by applications, the preceding method may be useful.
Summary
If the PHP application interacts with the Oracle database version 10g 2nd, you can use "database change notification feature ", with this feature, applications can receive notifications to respond to DML changes to the objects associated with the sent requests. With this feature, you do not have to update the cache in the application within a specific period of time. On the contrary, this operation is performed only when the registration query result set has been changed.
