However, when you use a database that is located on a different computer than the WEB server, it is often a good idea to cache the database result set. 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, a time-triggered caching method (a common method of caching systems that assumes that the cache is regenerated every time the failure timestamp is reached) may not be a satisfactory solution. In this case, you need to adopt a mechanism that notifies the application whenever the database data that the application needs to cache is changed so that the application will keep the cached stale data consistent with the database. In this case, it is convenient to use database change notification (a new version of Oracle database 10g 2nd feature).
Getting Started with database change notification
The use of the database change notification attribute is simple: Create a notification handler for notification execution – a PL/SQL stored procedure or a client OCI callback function. Then, register a query against the database object for which you want to receive change notifications so that the notification handler is invoked whenever the transaction changes any of its objects and commits. Typically, the notification handler sends the name of the modified table, the type of change that was made, and optionally the row ID of the changed line to the client listener so that the client application can perform the appropriate processing in the response.
To understand how the database change notification feature works, consider the following example. Assume that your PHP application accesses OE. Orders, as well as OE, stored in the Orders table. The order items stored in the Order_items. Because you rarely change the information that has been placed on an order, you may want the application to cache query result sets for both orders and Order_items tables at the same time. To avoid accessing outdated data, you can use database change notification, which allows your application to easily learn about changes to the data stored in the two tables above.
You must first grant the change NOTIFICATION system permission and the EXECUTE on Dbms_changenotification permission 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 also use the ALTER SYSTEM command below:
ALTER SYSTEM SET "job_queue_processes" = 2; Then, after you connect to 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 so that the notification handler logs changes to the registry. In the following example, you will create a nfresults table to record the date and time the change occurred, the name of the table that was modified, and a message that indicates 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 changed rows, but for the purposes of this article, the Nfresults table is perfectly enough.
Using Utl_http to send notifications to clients
You may also want to create one or more PL/SQL stored procedures and invoke them from the notification handlers to achieve a more maintainable and flexible solution. For example, you might want to create a stored procedure that implements sending notification messages to clients. Listing 1 is the PL/SQL process sendnotification. This procedure uses the UTL_HTTPPL package to send change notifications to the client application.
Listing 1. Using Utl_http to send notifications to clients
Copy CodeThe code is as follows:
CREATE OR REPLACE PROCEDURE sendnotification (url in VARCHAR2,
Tblname in VARCHAR2, order_id on 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, the sendnotification is utl_http. The Begin_request function sends a notification message to the client in the form of an HTTP request. This URL contains the order_id for a changed 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 rather to get a short message (describing the status code) stored in the Reason_phrase field of the RESP record.
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. Handle to OE. Notification handler for notification of changes made by the ORDERS table
Copy CodeThe 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: = ' 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 process notifications that are published by the database in response to DML or DDL changes to registered objects (that is, when the notification type is event_objchange only), and ignore notifications about other database events, such as instance initiation or instance shutdown. Starting with the previous version, the handler can handle the OE. Notification of changes made to each affected row in the ORDERS table. In the "Add 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. Order_items Notification of modified rows in the table.
Create registrations for change notifications
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 the 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 granularity at the ROWID level 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. Create a query registration for a notification handler
Copy CodeThe code is 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 has published a notification in response to the above transaction, you can examine the Nfresults table:
SELECT to_char (operdate, ' dd-mon-yy hh:mi:ss ') operdate,
Tblname, rslt_msg from Nfresults;
The result 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
It is clear from the above results that Orders_nf_callback has worked correctly, but no client script was found. This is not an unexpected occurrence 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 cause a database to be notified when a change is made to a registered object (in the above example, the ORDERS table). However, from a performance standpoint, the client application may prefer to cache the Order_items table rather than the query result set of the Orders table itself, because it has to retrieve only one row from the Orders table each time it accesses an order, but it must also retrieve multiple rows from the Order_items table. In the actual situation, the order may contain dozens of or even hundreds of order items.
Because you have registered a query on 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 results might look like this:
REGID table_name
----- --------------
241 OE. ORDERS
After you get the registration ID, you can use Dbms_change_notification. The Enable_reg function adds a new object to the registration as follows:
Copy CodeThe code is as follows:
DECLARE
ORD_ID number;
BEGIN
Dbms_change_notification. Enable_reg (241);
SELECT order_id to 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 orders_nf_callback so that it can handle notifications generated by DML operations on the Order_items table. Before recreating the Orders_nf_callback procedure, however, you need to 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 the manifest after the following line of code:
Copy CodeThe 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:
Copy CodeThe 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 re-create the orders_nf_callback, you need to test whether it works correctly. To do this, you can execute the following UPDATE statement against the Order_items table and commit the transaction:
UPDATE order_items SET quantity = WHERE order_id=2421 and line_item_id=1;
UPDATE order_items SET quantity = 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 resemble the following:
Operdate rslt_msg
------------------- --------------
03-mar-06 12:32:27 not Found
You might be wondering why only one row was inserted into the Nfresults table – after all, you have updated 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 single statement to select all order items for an 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 in which at least one of the order items has been modified, deleted, or inserted.
Building the Client
Now that you've created registrations for orders and Order_items tables, we'll look at how client applications that access the orders stored in these tables and their order items use change notifications. To do this, you can build a PHP application that caches query results against the tables above and takes action to respond to notifications about changes made to these tables (which are 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 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), through which you can cache function calls.
For example, you can create a function that creates a database connection, executes a SELECT statement against the database, obtains the results of the search, and eventually returns the results as an array. You can then cache the result array returned by the function by using the call method of the Cache_lite_function instance so that the arrays can be read 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 changes to the cached data, you will use the drop method of the Cache_lite_function instance to delete the stale data in the cache.
Looking back at the example of this article, you might want to create two functions for your application to interact with the database: The first function queries the Orders table and returns an order with the specified ID, and another function queries the Order_items table and returns the order item for that order. Listing 4 shows the getorderfields.php script that contains the Getorderfields function that takes an order ID and returns an associative array containing some of the fields retrieved to the order.
Listing 4. Gets the field for the specified order
Copy CodeThe 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 a getorderitems.php script. The script contains the Getorderitems function, which takes the order ID and returns a two-dimensional array that contains the lines that represent the order items for the order.
Listing 5. Gets the order item for the specified order
Copy CodeThe 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 a connect.php script that contains the Getconnection function that returns the database connection. Listing 6 is the connect.php script:
Listing 6. Get database connection
Copy CodeThe 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 that you've created all the functions you need 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 CodeThe 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 associated with the Order_no URL parameter (on behalf of OE. The order ID stored in the order table) is called together. For example, to retrieve information related to an order with an ID of 2408, you need to enter the URL as shown in the browser:
http://webserverhost/phpcache/testCache.php?order_no=2408 results, the browser generates 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, within 24 hours from now (because LifeTime is set to 86,400 seconds), the local cache satisfies the need for each getorderfields or Getorderitems call that uses order_no=2108. Note, however, that the Cache_lite_function class does not provide an API to test for the existence of available caches for a given function with a given parameter. 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 called with the same parameters. For example, in the example above, to ensure that the caching mechanism works, 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 works correctly, the browser's output should be the same as the previous one.
In addition, you can examine the cache directory, which is passed to the constructor of the Cache_lite_function class as the value of the CACHEDIR option (/tmp in the example). In this directory, you will find two newly created cache files with names similar to: cache_7b181b55b55aee36ad5e7bd9d5a091ec_3ad04d3024f4cd54296f75c92a359154. Note that if you are a Windows user, you may want to save the cache 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 notification of changes received from the database server. "Listing 8" is a dropresult.php script. The database server calls the script in response to changes to ORDERS and Order_items tables.
Listing 8. Handling change notifications received from the database server
Copy CodeThe 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 you create 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, which affects the same order that was previously accessed by 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 using one of the following URLs:
Http://webserverhost/phpcache/dropResults.php?order_no=2408&table=ORDERS
Http://webserverhost/phpcache/dropresults.php?order_no=2408&table=ORDER_ITEMS
You can clearly see from listing 8 that the dropresult.php script did not flush the cache after receiving change notifications from the database server. It simply deletes the cached file that contains the stale data. Therefore, if you check the cache directory now, you will see that the cache file created when you run the testcache.php script with order_no=2408 has disappeared. This actually means that testcache.php will fetch the data from the back-end database rather than the local cache the next time it requests data related to an order with ID 2408.
You will find that the result set requested by the application is most likely to be useful in situations where the application has changed before it uses it. For the example in this article, this means that the data associated with a particular order may change several times before testcache.php accesses the order. In this way, the application does a lot of unnecessary work because it refreshes its cache immediately after it receives the change notification from the database server.
However, if you want the dropresult.php script to flush the cache as soon as the change notification is received, you can call the call method of the Cache_lite_function instance after the drop method is called and specify the same parameters for both calls. In this case, you should also make sure that you include the getorderfields.php and getorderitems.php scripts so that dropresults.php can invoke Getorderfields and Getorderitems functions to flush the cache. "Listing 9" is the modified dropresult.php script.
Listing 9. Refresh the cache immediately after you receive the change notification
Copy CodeThe 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 ');
}
}
?>
The above method may be useful if the data stored in the ORDERS and Order_items tables is rarely changed and the application frequently accesses it.
Summary
If your PHP application interacts with Oracle database 10g version 2nd, you can take advantage of the database Change notification feature, which allows the application to receive notifications in response to DML changes to the objects associated with the request being made. With this feature, you do not have to update the cache in your application at a specific time period. Instead, the operation is performed only if the result set of the registered query has changed.
http://www.bkjia.com/PHPjc/325869.html www.bkjia.com true http://www.bkjia.com/PHPjc/325869.html techarticle However, when you use a database that is located on a different computer than the WEB server, it is often a good idea to cache the database result set. However, depending on your situation, determine the best cache policy ...
