US20030200209A1 - System and method for routing database requests to a database and a cache - Google Patents

Abstract

A system and method for routing database requests from application logic to a cache and a database, including receiving a database request from the application logic, determining whether the database request is informational or transactional, routing the database request to the cache if the database request is informational, and routing the database request to the database if the database request is transactional. A first example embodiment includes a database which is accessed using a first driver, a cache, and a second driver which determines whether database requests received from application logic are informational or transactional, where informational database requests are routed to the cache and transactional database requests are routed to the database using the first driver.

Description

BACKGROUND
• 1. Field of the Invention[0001]
• The present invention relates generally to computer databases and more particularly to a system and method for routing database requests from application logic to a cache and a database.[0002]
• 2. Discussion of the Related Art[0003]
• Many computer applications today utilize a database to store, retrieve, and manipulate information. Simply put, a database refers to a collection of information organized in such a way that a computer program can quickly select desired pieces of data. For example, an individual might use a database to store contact information from their rolodex, such as names, addresses, and phone numbers, whereas a business entity might store information tracking inventory or customer orders.[0004]
• Databases include the hardware that physically stores the data, and the software that utilizes the hardware's file system to store the data and provide a standardized method for retrieving or changing the data. A database management system (DBMS) provides access to information in a database. This is a collection of programs that enables a user to enter, organize, and select data in a database. The DBMS accepts requests for data (referred to herein as database requests) from an application program and instructs the operating system to transfer the appropriate data. Database requests can include, for example, read-only requests for database information (referred to herein as informational database requests) and requests to modify database information (referred to herein as transactional database requests). With respect to hardware, database machines are often specially designed computers that store the actual databases and run the DBMS and related software.[0005]
• FIG. 3A depicts a[0006]conventional database configuration300A, wherein acomputer application102 accesses information stored in adatabase104 having a DBMS120.Application102 includesapplication logic110 and adatabase driver114.Application102 anddatabase104 interact in a client/server relationship, whereapplication102 is the client anddatabase104 is the server.Application logic110 establishes aconnection310 to DBMS120 usingdatabase driver114.Database driver114 provides an Application Programming Interface (API) that allowsapplication logic110 to communicate withdatabase104 using function calls included in the API.Conventional database drivers114 typically handle communication between a client (e.g., application logic110) and a single database server, or possibly between multiple servers of the same basic type. Manyconventional database drivers114 make use of a proprietary client/server communication protocol (shown asline310 in FIG. 3A).
• The performance of the conventional client/server database design depicted in FIG. 3A can be improved with the addition of a cache. FIG. 3B depicts a second[0007]conventional database configuration300B wherein acache106 is inserted betweenapplication102 anddatabase104. This configuration is referred to herein as an inline cache.Application logic110 uses acache driver302 to establish a connection withcache106.Cache106 provides rapid access to a subset of the database information stored indatabase104, as will be apparent to those skilled in the art.Cache106 establishes aconnection314 with DBMS120 usingdatabase driver114, where the driver can be integrated within the cache.
• All database requests from[0008]application logic110 are routed first to cache106.Cache106 may handle requests differently depending on the type of operation requested and whether the target data is stored incache106. For example, informational database requests can be handled bycache106 without going todatabase104, so long as the information that is the target of the request (i.e., the target data) in stored incache106. Since the response time ofcache106 is significantly faster than that ofdatabase104, performance is increased as the percentage of information database requests grows in relation to the total number of database requests.
• Transactional database requests are performed in both[0009]cache106 anddatabase104. Consistency betweencache106 anddatabase104 is maintained because transactional requests are performed on the database information stored in both locations. As a result,application logic110 can only utilize those transactional requests that are understood by bothcache106 anddatabase104 for consistency to be maintained. For example, ifapplication logic110 issued a transactional request that was understood bydatabase104 but not bycache106, the information would be changed indatabase104 but not incache106, and the two would no longer be consistent. Therefore, only those requests for whichdatabase104 andcache106 share a common vocabulary are supported in this configuration.
• Many database manufacturers provide for the execution of stored procedures within[0010]database104 which can increase flexibility and performance. The implementation of these database-side capabilities may be proprietary to the database manufacturer. Third party manufacturers of inline caches may not have access to this proprietary information, and may therefore be unable to implement corresponding capabilities within the cache. As a result, consistency cannot be maintained betweencache106 anddatabase104 ininline cache configuration300B ifapplication logic110 is allowed to utilize these database-side capabilities. This limitation forces software vendors to re-code theirapplications102, eliminating any calls to database-side capabilities in favor of those operations within the vocabulary common to both cache and database.
• Also, while the addition of the inline cache results in some performance improvement for the processing of informational requests, performance can degrade for the processing of transactional requests. The client generating the request must wait until the inline cache commits the changes to both the cache and the database. Adding the inline cache can therefore increase the amount of time the client must wait when processing transactional requests as compared to a database configuration without a cache ([0011]300A). Further, the requirement that the inline cache be inserted between the client and server and receive all database requests limits the flexibility of the inline configuration. For example, someapplication logic110 cannot easily be modified to allow for the insertion of the inline cache.
• Finally, the fault tolerance of the inline cache configuration depends upon the fault tolerance of the inline cache. Failure of the inline cache will result in[0012]database104 being cut off from its clients, since all client request must pass through the failedcache106.
• What is needed is an improved system and method for routing database requests from application logic to a cache and a database that is both flexible and fault-tolerant, wherein transactional requests are handled efficiently and the application logic is able to take advantage of proprietary database mechanisms and/or database-side execution capabilities.[0013]
SUMMARY OF THE INVENTION
• The present invention provides a system and method for routing database requests from application logic to a cache and a database, including receiving a database request from the application logic, determining whether the request is informational or transactional, routing the request to the cache if the database request is informational, and routing the request to the database if the request is transactional.[0014]
• A first example embodiment of the present invention includes a database which is accessed using a first driver, a cache, and a second driver which determines whether database requests received from application logic are informational or transactional, where informational requests are routed to the cache and transactional requests are routed to the database using the first driver.[0015]
• An advantage of the present invention is that transactional requests are routed to the database without passing through the cache, thereby enabling the application logic to utilize whatever proprietary database mechanisms and/or database-side execution capabilities are provided the database. Routing transactional requests to the database without passing through the cache also decreases the amount of time the client must remain locked pending completion of the transactional request.[0016]
• Another advantage of the present invention is that informational requests are routed to, and handled by, the cache. As a result, those informational requests that can be handled by the cache need never be forwarded on to the database. The database, freed of the responsibility of handling these informational requests, may more efficiently handle those other requests that are routed to the database.[0017]
• Another advantage of the present invention is that the fault-tolerance of the system does not depend upon the fault-tolerance of the cache. If the cache fails, all requests may be routed directly to the database until the cache is again operational. In other words, according to the present invention, the cache is not a system point of failure.[0018]
• The present invention provides for the efficient processing of database requests, particularly where the requesting application issues informational requests in disproportion to the number of transactional requests. For example, applications which maintain a web site receive many more request for data than requests to change data. As a result, these applications will issue many more informational requests (that are not sensitive to cache consistency) than transactional requests to a backend database. The present invention is particularly well suited to processing database requests in this type of environment.[0019]
• These and other features and advantages of the present invention will become apparent from the following drawings and description.[0020]
BRIEF DESCRIPTION OF THE DRAWINGS
• The present invention is described with reference to the accompanying drawings. In the drawings, like reference numbers indicate identical or functionally similar elements. Additionally, the left-most digit(s) of a reference number identifies the drawing in which the reference number first appears.[0021]
• FIG. 1 depicts a cache configuration according to an example embodiment of the present invention.[0022]
• FIG. 2A depicts a first example hardware configuration, wherein an application runs on a client computer in communication with a database running on a server computer.[0023]
• FIG. 2B depicts a second example hardware configuration, wherein a database utilizes multiple servers.[0024]
• FIG. 2C depicts a third example hardware configuration, wherein multiple applications operate on one or more client computers to access a database.[0025]
• FIG. 2D depicts a fourth example hardware configuration employing two or more caches.[0026]
• FIG. 3A depicts a conventional database configuration, wherein a computer application accesses information stored in a database having a database management system.[0027]
• FIG. 3B depicts an inline cache configuration.[0028]
• FIG. 4 is a flowchart that describes a method for routing database requests according to the present invention.[0029]
• FIG. 5 is a flowchart that describes determining whether a database request is appropriate for cache processing in greater detail according to an example embodiment of the present invention.[0030]
• FIG. 6 depicts an example database request being subject to lexical and semantic analysis.[0031]
• FIG. 7 is a flowchart that describes an alternative example embodiment of the present directed to routing database requests to a remote cache.[0032]
• FIG. 8 is a flowchart that describes routing a database request to a database in greater detail according to an example embodiment of the present invention.[0033]
• FIG. 9 is a flowchart that describes routing a database request to a cache in greater detail according to an example embodiment of the present invention.[0034]
• FIG. 10 depicts a replication component in greater detail according to an example embodiment of the present invention.[0035]
• FIG. 11 is a flowchart that describes the operation of a replication component according to an example embodiment of the present invention.[0036]
DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS
• The present invention provides a system and method for routing database requests from application logic to a cache and a database. Generally speaking, according to the present invention database requests are selectively routed to a cache and/or a database depending upon whether the request is determined to be appropriate for cache processing. Various embodiments of the present invention are described below.[0037]
• Overview[0038]
• FIG. 1 depicts a[0039]cache configuration100 according to an example embodiment of the present invention.Application102 includesapplication logic110, animproved cache driver112, anddatabase driver114.Database104 includesDBMS120 and areplication component122.Application logic110 establishes aconnection134 withcache106 by callingcache driver112.Cache driver112 callsdatabase driver114 to establish aconnection130 withDBMS120.Replication component122 communicates withcache106 viaconnection132.
• [0040]Database104 represents computer software that utilizes the database hardware's file system to store database information and provide a standardized method for retrieving or changing the data. As described above,DBMS120 represents a collection of programs that enables a client to enter, organize, and select database information.DBMS120, in response to a database request, instructs the operating system (not shown) to perform the desired operation(s) on the appropriate data. According to an example embodiment of the present invention, database104 (and cache106) store database information as relational data, based on the well known principles of Relational Database Theory wherein data is stored in the form of related tables. Many database products in use today work with relational data, such as products from INGRES, Oracle, Sybase, and Microsoft. Other alternative embodiments can employ different data models, such as object or object relational data models.
• As described above with respect to[0041]inline cache configuration300B,cache106 provides rapid access to a subset of the database information stored indatabase104.Cache106 processes database requests from a connection established by a client and returns database information corresponding to the database request (target data). The faster response time ofcache106 provides an increase in performance for those database requests that can be handled by the cache. According to an example embodiment of the present invention,cache106 represents a high performance computer application running on a dedicated machine. The cache's primary architecture is preferably based on a main memory database (MMDB). An MMDB provides the ability to process database requests orders of magnitude faster than traditional disk based systems. As will be apparent, other cache architectures may be used. Further,cache106 may also include a secondary disk based cache (not shown) to handle database requests that are too large to fit in main memory.
• [0042]Application102 can represent any computer application that accessesdatabase104, such as a contact manager, order tracking software, or any application executing on an application server connected to the Internet.Application logic110 represents the portion ofapplication102 devoted to implementing the application functionality. For example,application logic110 can include a graphical user interface (GUI) to control user interactions withapplication102, various processing routines for computing items of interest, and other routines for accessing and manipulating database information stored indatabase104.
• [0043]Database driver114 represents software that allowsapplication logic110 to establish a connection (as a client) toDBMS120. As described above,database driver114 provides an API that can include a variety of other function calls for interacting withDBMS120. According to an example embodiment of the present invention,database driver114 represents the driver software that is distributed by the manufacturer ofdatabase104. As a result,connection130 represents a connection established according to the manufacturer's proprietary client/server communication protocol.
• According to an example embodiment of the present invention,[0044]database driver114 andcache driver112 support the Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC) standards. Generally speaking, clients using these types of drivers can generate SQL query requests for the server to process. In another example embodiment,cache106 also supports the ability to respond to Extensible Markup Language Query Language (XQL) queries which do not specify a particular driver type (driverless) and use an open standard mechanism, such as Hypertext Transfer Protocol (HTTP), for its communication protocol.
• [0045]Replication component122 detects changes that occur in database information stored withindatabase104, and insures that these changes propagate tocache106. The operation ofreplication component122 according to the present invention is described in greater detail below.
• [0046]Cache driver112 causes database requests fromapplication logic110 to be routed toDBMS120 and/orcache106.Cache driver112 provides an API that can include a variety of function calls for use byapplication logic110. According to an example embodiment of the present invention, the cache driver API can include one or more of the function calls defined in the database driver API.
• FIG. 2A depicts a first[0047]example hardware configuration200A, whereinapplication102 runs on aclient computer202, in communication withdatabase104 running on a server computer204 (via a communication link210), and in communication with cache106 (via a communication link212). According to an example alternative embodiment,cache106 shares common computer hardware withclient computer202. FIG. 2B depicts a secondexample hardware configuration200B, whereindatabase104 utilizes multiple servers204 (shown as204A through204C). Thatmultiple servers204 are being used can be transparent to the client application whose communications withDBMS120 remain the same regardless of the backend server configuration. FIG. 2C depicts a thirdexample hardware configuration200C, wherein multiple applications102 (shown as102A through102C) operate on one or more client computers202 (shown as202A through202C) toaccess database104. As will be apparent from the principles described herein, many other hardware configurations, including various combinations of the example hardware configurations described above, are contemplated within the scope of the present invention.
• FIG. 2D depicts a fourth[0048]example hardware configuration200D employing two or more caches106 (shown as106A through106C). According to the present invention, load balancing techniques may be used withmultiple cache configuration200D. Database requests fromapplication102 may be directed at the cluster of caches in round-robin fashion, thereby distributing the processing burdens across multiple caches. Alternatively, the database information that would have been stored in a single cache may be partitioned and stored across a cluster of caches. This allows for the storage of larger tables than would otherwise be possible using a single cache.
• [0049]Communication links210 and212 can represent any connection, logical or physical, over which the information described herein can be transmitted. For example,communication links210 and212 can represent a software connection between software modules, cable connection, a local area network (LAN), a wide area network (WAN), the Internet, a satellite communications network, a wireless network, or any combination of the above.
• According to an example embodiment of the present invention,[0050]application logic110 callscache driver112 to establish a connection (as a client) tocache106. Those database requests routed tocache106 may be passed via this connection. With respect to database requests routed todatabase104,cache driver112 callsdatabase driver114 to establish a connection withDBMS120, and then uses the function calls provided by the database driver API. The operation ofcache driver112 is described in greater detail below.
• General Operation[0051]
• The present invention includes one or more computer programs which embody the functions described herein and illustrated in the appended flowcharts. However, it should be apparent that there could be many different ways of implementing the invention in computer programming, and the invention should not be construed as limited to any one set of computer program instructions. Further, a skilled programmer would be able to write such a computer program to implement the disclosed invention without difficulty based on the flowcharts and associated written description included herein. Therefore, disclosure of a particular set of program code instructions is not considered necessary for an adequate understanding of how to make and use the invention. The inventive functionality of the claimed computer program will be explained in more detail in the following description in conjunction with the remaining figures illustrating the program flow.[0052]
• FIG. 4 is a[0053]flowchart400 that describes a method for routing database requests according to the present invention. The operations depicted inflowchart400 are described with respect tocache configuration100 depicted in FIG. 1. However, as will be apparent, the methods described herein are not necessarily limited to operations performed bycache configuration100. Rather, the methods described herein may be accomplished by any database configuration capable of performing the described operations.
• In[0054]operation402, a database request is received fromapplication logic110. The form of the database request can vary, depending upon the particular database language used. For example, SQL queries are typically in the form of a text string. Incache configuration100,application logic110 passes the database request tocache driver112.
• In[0055]operation404, the database request is analyzed to determine whether or not the request is appropriate for processing bycache106. According to an example embodiment of the present invention, those database requests determined to be informational are considered appropriate for cache processing, whereas transactional requests are considered inappropriate for cache processing. As described in greater detail below, other factors may also be considered when making this determination, such as whether the target data is stored within cache106 (if not, then even informational requests are not appropriate for cache processing). Incache configuration100,cache driver112 preferably makes the determination described inoperation404, i.e.,cache driver112 includes a routine for analyzing the database request and making the determination based on some established criteria.
• In[0056]operation406, those requests determined not to be appropriate for cache processing are routed fromapplication102 todatabase104 for processing.Database104 may then process the request, possibly resulting in database information being stored, retrieved, and/or manipulated according to the request.
• According to the example embodiment of[0057]cache configuration100,cache driver112 accomplishes this by callingdatabase driver114 to establish a connection withDBMS120, and then transferring the request todatabase104 for processing.Cache driver112 therefore makes use ofdatabase driver114 as a “slave” driver in the sense that the two operate in a master/slave relationship. Using a slave driver in this way avoids the need to re-implement withincache driver112 the specifics of communicating with databases produced by different manufacturers. This is particularly important wherecache106 anddatabase104 are produced by different manufacturers, and where the manufacturer ofcache106 desires to produce a product that supports databases from multiple manufacturers.
• In[0058]operation408, those requests determined to be appropriate for cache processing are routed fromapplication102 tocache106 for processing.Cache106 may then process the request in much the same manner asdatabase104. Incache configuration100,cache driver112 accomplishes this by establishing a connection tocache106 and transferring the request so that it may be processed by the cache.
• Since transactional requests are routed to[0059]database104 andbypass cache106, there are no vocabulary restrictions onapplication logic110. In other words, there is no requirement thatcache106 duplicate the functionality of database104 (as with the inline cache configuration) because transactional requests are not routed tocache106.Application logic110 may therefore take full advantage of any database-side execution capabilities ofdatabase104. As described below,replication component122 ensures that changes made todatabase104 are replicated incache106.
• FIGS. 1 and 4 are now described in greater detail according to various alternative embodiments of the present invention.[0060]
• Operation of Cache Driver[0061]
• FIG. 5 is a flowchart that describes[0062]operation404 in greater detail according to an example embodiment of the present invention. The determination of whether a request is appropriate for cache processing depends upon whether the request is determined to be informational or transactional. As described below, additional factors may also be considered when determining the appropriateness of cache processing.
• The operations described in FIG. 5 with respect to[0063]operation404 are preferably performed bycache driver112 incache configuration100.Cache driver112 therefore preferably includes the functions necessary to performoperation404. However, as will be apparent, other alternative embodiments are contemplated wherein the functions associated withoperation404 may be performed by other components ofapplication102. For example, one or more of these functions might alternatively be performed byapplication logic110 rather thancache driver112, with only minor modifications.
• As will be apparent, one or more of the operations depicted in FIG. 5 may be performed in an order different than the order depicted. For example, the order of[0064]operations502 and504 may be switched with only minor modifications. Also, one or more of the operations depicted in FIG. 5 may be omitted entirely, or other operations added that reflect different criteria used in the determination of the appropriateness of cache processing.
• The operations of FIG. 5 will be described with respect to an example SQL database request. The example SQL database request (received from[0065]application logic110 in operation402) is a text specified query described as:

  	SELECT expr1 [,expr2, . . . ]
  	FROM table1,[table2, . . . ]
  	[WHERE condition]

• The SELECT clause indicates expressions that will define each column in a table of results. The expressions may indicate fields that belong to one of the tables listed in the FROM clause. Field names might additionally be prefixed with a table name and a dot to resolve ambiguity between fields in different tables with the same name.[0066]
• The FROM clause specifies which tables are to be involved in the request.[0067]
• The WHERE clause specifies conditions and constraints that apply when performing the request. One type of constraint is called a “join condition.” A join condition specifies how records in one table are related to records in another. This is typically specified as follows:[0068]
• WHERE table1.id=table2.id[0069]
• A join condition between two tables may specify either a one-to-one mapping of records or a one-to-many relationship, depending on the nature and content of the two tables. The WHERE clause also serves to specify conditions for records in a table. A condition may also restrict the records returned for a given table to only those records which meet the condition. An example might be:[0070]
• WHERE age>18[0071]
• Multiple instances of both types of conditions can be combined in a WHERE clause using the logical operators AND and OR. WHERE clauses may also incorporate a set of relational operators (=,<,>,<>) and special functions (LIKE,UPPER) defined in conventional SQL specifications.[0072]
• Referring now to FIG. 5, in[0073]operation502 an isolation level and/or a concurrency setting associated with the database request is analyzed to determine whether the request is appropriate for cache processing. Certain driver environments allow a transaction isolation level to be associated with the database request. According to an example embodiment of the present invention, the isolation level is compared to a first threshold. Only those database requests having an isolation level less than or equal to the first threshold are determined to be appropriate for cache processing. Those database requests having an isolation level greater than the first threshold are determined to be inappropriate for cache processing and, as shown in FIG. 5, inoperation406 the request is routed todatabase104.
• Other driver environments may allow a related setting called the concurrency setting. This setting is roughly the inverse of the transaction isolation level. The concurrency setting is compared to a second threshold, wherein those requests that meet or exceed the second threshold are considered appropriate for caching. Still other driver embodiments, such as ODBC, provide for both an isolation level and a concurrency setting. Alternative embodiments of the present invention are contemplated wherein neither of these measures is considered, either one is considered, or both are considered when determining the appropriateness of cache processing.[0074]
• In[0075]operation504, the database request is parsed to extract information that is used to determine the appropriateness of cache processing. According to an example embodiment of the present invention, an SQL database request is parsed, for example, by scanning characters in the SQL text string and performing well known techniques for lexical and semantic analysis. The information extracted as a result of the SQL parsing process can be used for further analysis and for execution of the request. This information is preferably assembled into a data structure that includes basic information such as the tables and fields that are being requested (i.e., the target data). Any expressional elements of the request, such as field expressions and the WHERE condition expression, are preferably stored in the data structure as trees, as will be apparent. An expression tree is an easy data structure to access, navigate and manipulate in subsequent operations.
• FIG. 6 depicts the[0076]example database request602 described above being subject to lexical andsemantic analysis604. The resultingdata structure606 includes several items (Query Type, Fields, Tables) and a WHERE expression tree. If the request is not a SELECT request, then a data structure will preferably be generated with a Query Type field not equal to type SELECT.
• In[0077]operation506, the information parsed from the database request is analyzed to determine whether the database request is informational. As described above, informational requests are read-only queries for database information. In the example SQL embodiment, SELECT requests (or variants thereof) are determined to be informational (as shown above in the example SQL request). Other types of SQL requests are determined to be transactional. As shown in FIG. 5, those requests that are determined not to be informational are routed todatabase104 inoperation406.
• In[0078]operation508, a determination is made whether the request's target data is stored incache106. If the target data is not available incache106, then inoperation406 the request is routed todatabase104 for processing. According to an example embodiment of the present invention, the target data is compared against a cache content data structure that provides information about what database information is stored in the cache, preferably at the table level, since the last time the cache was checked.
• FIG. 7 is a[0079]flowchart700 that describes an alternative example embodiment of the present directed to routing database requests to acache106 that is accessible via a network, referred to herein as a remote cache.Flowchart700 identifies those operations that are performed byapplication102 and those operations that are performed bycache106. As described above,operation404 preferably includes determining whether the target data is stored in cache106 (operation508). However, this operation is predictive in nature whenevercache106 is remote because there is a delay between when the cache content changes and when the cache content data structure is updated to reflect the changes. Typically, routing errors resulting from this latency are not significant because of the relatively low frequency of changes in the cache content.
• As depicted in FIG. 7, requests deemed appropriate for cache processing are sent to the remote cache in[0080]operation408. Inoperation702, a second check is done at the remote cache to ensure that the target data is actually available. This is an authoritative check because it is done locally withincache106, obviating the latency issue encountered with a remote check. If the target data is available, the database request is processed bycache106 inoperation704. If the target data is not available, the database request is sent back toapplication logic110 and rerouted todatabase106 inoperation406.
• Returning now to FIG. 5, in operation[0081]510 the information parsed from the database request is analyzed to determine whether the database request is otherwise suitable for cache processing. The criteria analyzed in this operation may vary according to the driver type and database language used. In the example SQL embodiment, the parsed data structure is checked to see if all SQL features are supported bycache106. The general complexity of the request is also preferably checked for cache support.Cache106 may also decline the request if it is determined thatcache106 cannot handle the request efficiently.
• Routing Database Requests[0082]
• Returning again to FIG. 4, once it is determined in[0083]operation404 whether the database request is appropriate for cache processing, those requests deemed inappropriate are routed todatabase104 inoperation406. Otherwise, those requests deemed appropriate for cache processing are routed tocache106 inoperation408.
• FIG. 8 is a flowchart that describes[0084]operation406 in greater detail according to an example embodiment of the present invention. As described above, transactional requests are preferably routed todatabase104 for processing (as determined in operation404). Inoperation802, the database request is marked as transactional so that the request may be more easily routed through subsequent operations.
• In[0085]operation804,cache driver112 sends the database request todatabase driver114 for processing bydatabase104.Cache driver112 preferably callsdatabase driver114, as described above, according to a master/slave relationship.Cache driver112 is thereby able to establish a connection withdatabase104, and to employ those functions defined with the database driver API for communicating withdatabase104. Any remaining request processing duties (if any) are passed on todatabase driver114. Subsequent communications related to the submitted request fromapplication logic110 are redirected todatabase driver114 for handling.
• FIG. 9 is a flowchart that describes[0086]operation408 in greater detail according to an example embodiment of the present invention. As described above, informational requests are preferably routed tocache106 for processing (as determined in operation404). Inoperation902, the database request is marked as informational so that the request may be more easily routed through subsequent operations. Inoperation904,cache driver112 establishes a connection withcache106 and sends the database request tocache106 for processing. Any remaining request processing duties are preferably performed bycache106.
• Data Replication Component[0087]
• Returning to FIG. 1,[0088]database104 preferably includesreplication component122 that is responsible for detecting changes that occur in the information stored indatabase104 and for propagating those changes tocaches106. FIG. 10 depictsreplication component122 in greater detail according to an example embodiment of the present invention.Replication component122 includes one or more triggers1002 (shown as1002A through1002C), a change table1004 (shown as1004A through1004C) associated with each trigger1002, asignaling routine1006, a master change table1008. Each trigger1002 is associated with a database table1020 (shown as1020A through1020C). Database table1020 represents a table of relational data stored indatabase104.
• Trigger[0089]1002 represents a program in the native database execution environment which is executed when a change occurs on the associated table1020. Trigger1002 has access to the old and new values of each record or set of records that were updated in table1020. In an example embodiment, a different trigger1020 is installed for each table1020 that is selected for caching. Many database environments support multiple triggers per table so that the addition of trigger1002 need not interfere with existing application triggers.
• According to an example embodiment, each change table[0090]1004 is associated with one of the trigger/table pairs. Change table1004 stores information about each change that has occurred in the associated table1020, as detected by trigger1002. Master change table1008 holds information about the update status of those tables1020 that have been selected for caching.
• As will be clear to those skilled in the art, various other configurations of change tables could be used to similar effect. For example, according to a first alternative embodiment, the separate change tables[0091]1004 are eliminated in favor of a single master change table that is updated by all of the triggers. However, where the number of triggers becomes large, contention between the triggers to update the master change table can result in some performance degradation. According to a second alternative embodiment, master change table1008 is eliminated and the separate change tables1004 retained. In this alternative embodiment, signaling routine1006 queries each change table1004 to assemble the information that is preferably stored in master change table1008.
• [0092]Signaling routine1006signals cache106 that an update has occurred in response to receiving an appropriate signal from trigger1002.Signaling routine1006 is preferably implemented as an external routine. External routines are mechanisms provided by the database vendor for executing external third party code within the native database environment.
• FIG. 11 is a flowchart that describes the operation of[0093]replication component122 according to an example embodiment of the present invention. Inoperation1102, one of the triggers1002 is executed upon detecting a change operation being performed on associated table1020. In the example SQL embodiment, operations which could produce a change in table1020 are generally in the form of an SQL INSERT, UPDATE, or DELETE command.
• In[0094]operation1104, trigger1002 updates change table1004 by storing the old record value and the new record value in change table1004. There may be more than one row associated with a change, in which case each record's old and new value are preferably stored in change table1004.
• In[0095]operation1106, trigger1002 calls signaling routine1006 to notifycache106 that a change has occurred. According to an alternative embodiment, signaling routine1006 may also provide additional information about the fields involved in the change and the change data itself.Cache106 can use this additional information to determine whether the database information currently stored withincache106 is affected by the changes.Cache106 can avoid queryingdatabase104 altogether if it is determined thatcache106 is unaffected by the changes. Inoperation1108, trigger1002 ceases execution.
• In[0096]operation1110,cache106 receives notification from signaling routine1006. In response to this notification,cache106 submits a replication request toreplication component122 inoperation1112. The replication request will causereplication component122 to gather all changes that have been stored since the last replication request. The replication request is preferably submitted at the highest isolation level (serializable) to ensure that it executes in isolation of other transactions and therefore captures only completely committed changes. Inoperation1114,replication component122 executes the replication request received fromcache106.
• According to an example embodiment of the present invention, signaling routine[0097]1006 is thereby called at the appropriate time to signalcache106 that a change has occurred, with the result being that the replication request generated in response to the signal is assured of executing after the current transaction has completed. This is because the replication request must wait for all pending transactions (including the signaling transaction) to complete. Signaling in this manner obviates the need forcache106 to perform a polling operation to detect possible changes. However, according to an alternative embodiment of the present invention,cache106 can perform a polling operation to periodically check for changes in those tables1020 that are also stored incache106.
• In[0098]operation1116, master replication table1008 is checked to determine which tables1020 have updates since the last replication request. If it is determined inoperation1118 that a table has been updated, then inoperation1122 the corresponding change table1004 is queried to retrieve the changes. Inoperation1124, the changes retrieved from change table1004 inoperation1122 are then deleted from change table1004, or alternatively, the changes are marked as having been read. Inoperation1126, master change table1008 is updated to show that the current table's changes have been read.
• The process described by[0099]operations1122 through1126 continues until each changed table has been queried. Once all the change data has been gathered, inoperation1120cache106 updates the appropriate tables stored incache106 according to the gathered change data.
Conclusion
• While various embodiments of the present invention have been described above, it should be understood that they have been presented by way of example only, and not limitation. Thus, the breadth and scope of the present invention should not be limited by any of the above-described exemplary embodiments, but should be defined only in accordance with the following claims and their equivalents.[0100]
• The previous description of the preferred embodiments is provided to enable any person skilled in the art to make or use the present invention. While the invention has been particularly shown and described with reference to preferred embodiments thereof, it will be understood by those skilled in the art that various changes in form and details may be made therein without departing from the spirit and scope of the invention.[0101]

Claims (36)

What is claimed is:

1. A method for processing database requests from application logic, comprising:

(a) receiving a database request;

(b) determining whether said database request is informational or transactional;

(c) routing said database request to a cache if said database request is informational; and

(d) routing said database request to a database if said database request is transactional.

2. The method ofclaim 1, wherein said database request includes a transaction isolation level, said method further comprising determining whether said transaction isolation level is greater than a first threshold, and if so, routing said database request to said database.

3. The method ofclaim 1, wherein said database request includes a concurrency setting, said method further comprising determining whether said concurrency setting is less than a second threshold, and if so, routing said database request to said database.

4. The method ofclaim 1, wherein said database request is routed to said cache using a second driver, said database request is routed to said database using a first driver, and said first driver is a slave to said second driver.

5. The method ofclaim 1, wherein said determining whether said database request is informational or transactional comprises determining whether said database request is read only, and if so, identifying said database request as informational, and if not, identifying said database request as transactional.

6. The method ofclaim 1, wherein said database request includes a reference to target data, and wherein said method further comprises determining whether said target data is stored within said cache, and if not, routing said database request to said database.

7. The method ofclaim 1, wherein said method further comprises determining whether said database request is suitable for processing by said cache, and if not, routing said database request to said database.

8. The method ofclaim 1, wherein said database request includes a reference to target data, and wherein said routing said database request to said cache comprises determining whether said cache is remote from the application logic, and if so, sending said database request to said cache and determining whether said target data is stored within said cache, and if not, routing said database request to said database.

9. The method ofclaim 1, wherein said database stores a memory portion and said cache stores a subset of said memory portion, wherein said method further comprises determining whether said database request changes said subset stored in said database, and if so, updating said subset stored in said cache.

10. The method ofclaim 9, wherein said database request comprises an SQL request, and wherein said subset is changed if said SQL request is an INSERT, UPDATE, or DELETE command.

11. The method ofclaim 1, wherein said database stores a memory portion and said cache stores a subset of said memory portion, wherein said method further comprises polling said database to determine whether said subset stored in said database has changed, and if so, updating said subset stored in said cache.

12. The method ofclaim 1, further comprising:

(e) detecting changes to said database; and

(f) replicating said changes in said cache.

13. A system for processing a database request from application logic, wherein target data associated with the database request is stored in a database, and wherein the database is accessed using a first driver, said system comprising:

a cache; and

a second driver to determine whether the database request is informational or transactional,

wherein informational database requests are routed to said cache using said second driver and transactional database requests are routed to the first driver.

14. The system ofclaim 13, wherein said cache comprises a main memory database.

15. The system ofclaim 14, wherein said cache further comprises a secondary disk cache coupled to said main memory database.

16. The system ofclaim 13, wherein said cache stores relational data.

17. The system ofclaim 13, wherein the application logic loads said second driver, and said second driver provides an application programming interface (API) for the application logic.

18. The system ofclaim 17, wherein the database requests comprise Structured Query Language (SQL) requests.

19. The system ofclaim 18, wherein said second driver supports the Open Database Connectivity (ODBC) standard.

20. The system ofclaim 18, wherein said second driver supports the Java Database Connectivity (JDBC) standard.

21. The system ofclaim 13, wherein said cache communicates with said second driver according to Hypertext Transfer Protocol (HTTP).

22. The system ofclaim 13, wherein said cache supports XML Query Language (XQL).

23. The system ofclaim 13, wherein said second driver calls the first driver to establish a connection with the database.

24. The system ofclaim 13, further comprising:

means for detecting changes to said database; and

means for replicating said changes in said cache.

25. Computer executable software code, comprising:

code to receive a database request from application logic;

code to determine whether said database request is informational or transactional;

code to route said database request to a cache if said database request is informational; and

code to route said database request to a database if said database request is transactional.

26. The software code ofclaim 25, wherein said database request includes a transaction isolation level, said software code further comprising code to determine whether said transaction isolation level is greater than a first threshold, and if so, to route said database request to said database.

27. The software code ofclaim 25, wherein said database request includes a concurrency setting, said software code further comprising code to determine whether said concurrency setting is less than a second threshold, and if so, to route said database request to said database.

28. The software code ofclaim 25, wherein code to determine whether said database request is informational or transactional comprises code to determine whether said database request is read only, and if so, to identify said database request as informational, and if not, to identify said database request as transactional.

29. The software code ofclaim 25, wherein said database request includes a reference to target data, and wherein said software code further comprises code to determine whether said target data is stored within said cache, and if not, to route said database request to said database.

30. The software code ofclaim 25, wherein said software code further comprises code to determine whether said database request is suitable for processing by said cache, and if not, to route said database request to said database.

31. The software code ofclaim 25, wherein said database request includes a reference to target data, and wherein said code to route said database request to said cache comprises code to determine whether said cache is remote from the application logic, and if so, to send said database request to said cache and to determine whether said target data is stored within said cache, and if not, to route said database request to said database.

32. The software code ofclaim 25, wherein said database stores a memory portion and said cache stores a subset of said memory portion, wherein said software code further comprises code to determine whether said database request changes said subset stored in said database, and if so, to update said subset stored in said cache.

33. The software code ofclaim 32, wherein said database request comprises an SQL request, and wherein said subset is changed if said SQL request is an INSERT, UPDATE, or DELETE command.

34. The software code ofclaim 25, wherein said database stores a memory portion and said cache stores a subset of said memory portion, wherein said software code further comprises code to poll said database to determine whether said subset stored in said database has changed, and if so, to update said subset stored in said cache.

35. The software code ofclaim 25, further comprising:

code to detect changes to said database; and

code to replicate said changes in said cache.

36. The software code ofclaim 35, wherein said code to replicate said changes in said cache comprises code to notify said cache of said changes.

Images