USOO7447686B2
`
`(12) United States Patent
`Levine
`
`(10) Patent No.:
`(45) Date of Patent:
`
`US 7.447,686 B2
`Nov. 4, 2008
`
`(54) COMPUTER-IMPLEMENTED SYSTEMAND
`METHOD FOR HANDLING DATABASE
`STATEMENTS
`
`(75) Inventor: Frederick J. Levine, Durham, NC (US)
`
`(73) Assignee: SAS Institute Inc., Cary, NC (US)
`(*) Notice:
`Subject to any disclaimer, the term of this
`patent is extended or adjusted under 35
`U.S.C. 154(b) by 921 days.
`
`(21) Appl. No.: 10/303,106
`
`(22) Filed:
`
`Nov. 22, 2002
`
`(65)
`
`Prior Publication Data
`US 2004/O1031 OO A1
`May 27, 2004
`
`(51) Int. Cl.
`(2006.01)
`G06F 7/00
`(2006.01)
`G06F 7/30
`(52) U.S. Cl. ................................ 707/4; 707/3; 707/100
`(58) Field of Classification Search ..................... 707/3,
`707/4, 100
`See application file for complete search history.
`
`(56)
`
`References Cited
`
`U.S. PATENT DOCUMENTS
`5.421,008 A * 5/1995 Banning et al. ................ 7O7/4
`5,590,319 A * 12/1996 Cohen et al. ................... 7O7/4
`5,659,725 A * 8/1997 Levy et al. ..................... 707/3
`6,041,344. A * 3/2000 Bodamer et al. ............ 709/203
`6,941,298 B2 * 9/2005 Chow et al. .................... 707/3
`
`* cited by examiner
`Primary Examiner Neveen Abel-Jalil
`Assistant Examiner Jacob F Bétit
`(74) Attorney, Agent, or Firm Jones Day
`
`(57)
`
`ABSTRACT
`
`A computer-implemented system and method for handling a
`database statement from a first database system. The database
`statement is formatted according to a language format used
`by the first database system. Database language difference
`data is accessed so that a database specific statement may be
`generated which is operational within a different type of
`database system.
`50 Claims, 19 Drawing Sheets
`
`
`
`
`
`NAWE DATABASE
`SYSEM
`
`SQL
`STATEMENTS IN
`NAVE FORMAT
`AVE FORMA
`
`40
`
`32
`
`TEXTUALIZATION PROCESS
`
`
`
`a
`
`area
`
`a
`
`a-
`
`CURY
`SYNTAX
`
`RELATED
`METADAA
`
`r
`
`Y ra
`
`y
`1ROCESSED SQN
`STATEMENTS IN
`HRD PARTY B's
`
`38
`
`as
`
`is a
`
`Y.
`Y-p;6:5 ScLN,
`y
`(
`STATEMENTS IN
`N THIRD PARTY C's
`FoRMAT. - 1
`
`DIFFERENT TYPES
`OF DATABASE
`PLATFORMS
`
`
`
`SC DATABASE
`SPECFC
`TEXUALZAONS
`
`52
`
`
`
`
`
`
`
`42
`
`FROCESSED SC
`STATEMENTS IN
`THRPARTY A's
`FORMAT
`
`
`
`
`
`THR) PARTY A's
`DATABASESYSTEM
`
`THIRD PARTY B's
`DATABASE SYSTEM
`
`THIRD PARTY C's
`DATABASS SYSTEM
`
`World Programming Limited Exhibit 1001
`Page 1 of 28
`
`

`

`U.S. Patent
`
`US 7.447,686 B2
`
`- - - - - - - - - -n
`
`TOS
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`World Programming Limited Exhibit 1001
`Page 2 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 2 of 19
`
`US 7.447,686 B2
`
`----------
`
`j
`
`---
`
`WELLSÅSBSVEV i X/O
`
`
`
`ES WYEWW.LV/GU EALLWN
`
`
`
`
`
`
`
`
`
`
`
`
`
`World Programming Limited Exhibit 1001
`Page 3 of 28
`
`

`

`U.S. Patent
`
`US 7.447,686 B2
`
`
`
`
`
`
`
`
`
`
`
`
`
`World Programming Limited Exhibit 1001
`Page 4 of 28
`
`

`

`World Programming Limited Exhibit 1001
`Page 5 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 5 Of 19
`
`US 7.447,686 B2
`
`|----5---- | | |
`
`|----
`
`
`
`|SCJOH, LEWN|
`
`
`
`
`
`SLNE NOCHWOO
`
`(S10ETGO)
`
`NO ISSE!!!XE
`
`SLNE NOCHWOO
`
`ZZ ),
`
`
`
`
`
`0 || ||
`
`World Programming Limited Exhibit 1001
`Page 6 of 28
`
`

`

`U.S. Patent
`
`US 7.447,686 B2
`
`
`
`World Programming Limited Exhibit 1001
`Page 7 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 7 Of 19
`
`US 7.447,686 B2
`
`Z LZ
`
`
`
`3 uue NIOOu35)
`
`0 ?206
`
`00Z
`
`
`
`9uue NIOOu35)
`
`
`
`
`
`
`
`World Programming Limited Exhibit 1001
`Page 8 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 8 of 19
`
`US 7.447,686 B2
`
`
`
`8 "SDI
`
`
`
`
`
`World Programming Limited Exhibit 1001
`Page 9 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 9 Of 19
`
`US 7.447,686 B2
`
`< € 313||AA 8 UUOJ ? LOGITIGIS,
`
`„0
`
`Z0900$
`
`
`
`
`
`
`
`World Programming Limited Exhibit 1001
`Page 10 of 28
`
`

`

`World Programming Limited Exhibit 1001
`Page 11 of 28
`
`

`

`U.S. Patent
`
`US 7.447,686 B2
`
`
`
`Œ007
`
`| 07
`
`World Programming Limited Exhibit 1001
`Page 12 of 28
`
`

`

`U.S. Patent
`
`
`
`US 7.447,686 B2
`
`
`
`World Programming Limited Exhibit 1001
`Page 13 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 13 Of 19
`
`US 7.447,686 B2
`
`|-----------|
`
`Å | | |
`
`|----
`
`
`
`
`
`
`
`
`
`
`
`S_LNE NOdWOC)
`
`
`SLNE NOdWOO
`
`
`NO ISSE? |dXE
`
`90 ||
`
`ZZI,
`
`0,17
`
`World Programming Limited Exhibit 1001
`Page 14 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 14 of 19
`
`US 7.447,686 B2
`
`
`
`?7), "SDI
`
`
`
`3pOU NOI LVNCHLVONOO // (ONOOTTÒS == ºpopº-99 I?º-?Áuðnby) J?
`
`
`
`
`
`}
`
`World Programming Limited Exhibit 1001
`Page 15 of 28
`
`

`

`U.S. Patent
`
`US 7.447,686 B2
`
`World Programming Limited Exhibit 1001
`Page 16 of 28
`
`

`

`World Programming Limited Exhibit 1001
`Page 17 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 17 Of 19
`
`US 7.447,686 B2
`
`
`
`
`
`WELSÅS EISWEW LVCI
`
`07
`
`WELSÅS
`
`009
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`WE_LSAS ESV/EW_LV/C)
`
`World Programming Limited Exhibit 1001
`Page 18 of 28
`
`

`

`U.S. Patent
`
`Nov. 4, 2008
`
`Sheet 18 of 19
`
`US 7.447,686 B2
`
`999
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`Otz
`
`009
`
`World Programming Limited Exhibit 1001
`Page 19 of 28
`
`

`

`U.S. Patent
`
`US 7.447,686 B2
`
`
`
`
`
`
`
`TOS TVO|OEDOTS_LNE NOdWOO
`
`World Programming Limited Exhibit 1001
`Page 20 of 28
`
`

`

`US 7,447,686 B2
`
`1.
`COMPUTER-IMPLEMENTED SYSTEMAND
`METHOD FOR HANDLING DATABASE
`STATEMENTS
`
`TECHNICAL FIELD
`
`The present invention relates generally to computer-imple
`mented database systems and more particularly to database
`statement operations.
`
`BACKGROUND
`
`Data access across different database platforms proves dif
`ficult due to the platforms using varying database commands.
`For example, although the structured query language (SQL)
`is based on a well-documented ANSI standard, in reality most
`database systems, such as those from Oracle, Sybase, Busi
`ness Objects, SAS, or Brio, implement a superset of the ANSI
`standard. Variations in the Superset provide an obstacle in
`cross-platform database operations.
`
`10
`
`15
`
`SUMMARY
`
`In accordance with the teachings provided herein, a system
`and method are provided for handling a database statement
`from a first database system. The database statement is for
`matted according to a language format used by the first data
`base system. Database language difference data is accessed
`so that a database specific statement may be generated which
`is operational within a different type of database system.
`
`25
`
`30
`
`BRIEF DESCRIPTION OF THE DRAWINGS
`
`35
`
`40
`
`FIG. 1 is a block diagram depicting Software and computer
`components that allow database statements to be automati
`cally converted so that they may be used in a different type of
`database system;
`FIG. 2 is a block diagram showing an example where the
`textualization process uses a tree to represent a database
`Statement;
`FIGS. 3-5 are block diagrams illustrating object-oriented
`approaches to creating disparate SQL text for third party data
`acceSS,
`FIG. 6 is a tabular representation depicting phrase compo
`nent examples:
`45
`FIG. 7 is a tabular representation depicting identifier com
`ponent examples;
`FIG. 8 is a tabular representation depicting an example of
`component processing using SELECT and UPDATE com
`mands;
`50
`FIG.9 is a tabular representation listing exemplary expres
`sion components.
`FIG. 10 is a process flow diagram showing exemplary
`processing of SQL statements;
`FIG.11 is a process flow diagram showing processing of an
`example query statement;
`FIG. 12 is a tabular representation illustrating several
`exemplary components that may be involved in processing an
`SQL SELECT statement;
`FIG. 13 is a block diagram illustrating use of an additional
`component that may be used in conjunction with overrides to
`standard components;
`FIGS. 14 and 15 are listings of computer instructions to
`illustrate textualization examples involving different types of
`database systems;
`FIG. 16 is a tabular representation showing an optional
`naming convention for parent components;
`
`55
`
`60
`
`65
`
`2
`FIGS. 17 and 18 are block diagrams depicting software and
`computer components that convert database statements from
`a native system to application programming interfaces (APIs)
`for use in one or more third party systems; and
`FIG. 19 is block diagram illustrating different override
`capabilities for component objects.
`
`DETAILED DESCRIPTION
`
`FIG. 1 depicts a computer-implemented system 30 that
`allows database statements 32 to be automatically converted
`from one database platform format to another. Through their
`conversion, database statements 32 executable within one
`system 40 may be utilized in one or more different types of
`database systems (42, 44, 46). This provides, among other
`things, the ability to transparently manipulate data from Vir
`tually any database system.
`Within the system30, a textualization process 50 addresses
`the complexity of translating a native database statement 32
`dialect into a variety of third party database dialects (34, 36,
`38) by allowing the common parts of the default syntax of
`functionality to be shared between a native database and a
`third party database. The textualization process 50 utilizes
`database specific textualizations 52 to translate the common
`parts to the third party database dialect.
`For example, if a native database system 40 uses an outer
`join Syntax to be specified in an SQL query statement 32 that
`is different from what a third party database system 42 uses,
`then the textualization process 50 creates based upon the
`specific textualizations 52 a processed SQL command 34 for
`the third party database system 42 that employs the third
`party's outer join Syntax. The processed SQL command 34 is
`then able to be executed within the third party database sys
`tem 42. As another example, a native database system 40 and
`a third party database system 42 may both Support a function
`that performs an identical operation but differs in name and/or
`format. Using the specific textualizations 52, the textualiza
`tion process 50 translates the SQL statement 32 having the
`function in the native format into an SQL statement 34 having
`the function in the third party's format. It should be under
`stood that the terms “native' and “third party' are relative
`terms in that what is a native database system for one com
`pany may be a third party database system for another com
`pany. Accordingly, the terms “native' and “third party' data
`base systems may be generalized to a first type of database
`system that generates a database statement that is processed
`by the textualization process 50 and provided to a different
`type of database system. It should be further understood that
`different types of databases refer to database systems that
`contain differences in their respective database statement for
`mat and/or syntax, such as utilizing a different Superset of an
`ANSI database statement standard.
`The textualization system 30 is highly flexible in that a
`third party database system 42 may utilize the textualization
`process 50 to convert and send database commands to the
`native database system 40. It is also noted that a native data
`base system 40 may send database statements 32 to other third
`party database systems (44 and 46). The textualization system
`30 contains textualization information and/or operations 52
`that are specific to each of the third party database systems
`(42, 44, 46). The textualization system 30 has the flexibility of
`providing an SQL statement 32 from the native database
`system 40 to a single third party database system 42, or may
`provide the same native SQL statement 32 to multiple third
`party database systems (42, 44, 46) Substantially concurrently
`or serially.
`
`World Programming Limited Exhibit 1001
`Page 21 of 28
`
`

`

`US 7,447,686 B2
`
`10
`
`15
`
`30
`
`35
`
`40
`
`25
`
`3
`FIG. 2 shows an example where an SQL tree 60 is used by
`the textualization process 50 to process an SQL statement 32.
`The SQL tree 60 represents the syntax of a native database's
`SQL statement 32 and its related metadata (e.g., table names,
`column names, etc.). The tree 60 may contain a hierarchical
`arrangement of nodes representative of the SQL syntax and
`metadata to be processed. If for example the SQL statement
`32 specified that the values from two different columns are to
`be concatenated, then the SQL tree 60 would contain a node
`that specifies that a concatenation operation is to be per
`formed.
`The textualization process 50 compartmentalizes an SQL
`statement 32 into logical text pieces or components which are
`initially provided based on a default SQL dialect. The logical
`text pieces are represented in the SQL tree 60. Any of these
`text pieces can be overridden by a third party SQL provider
`that utilizes a different SQL dialect than the default, hence
`allowing for granular customization and code reuse. As an
`illustration, a database system from SAS Institute Inc. has an
`SQL language which has differences from other vendor's
`SQL. The textualization process 50 allows a SAS SQL state
`ment to be converted into a third party vendor-specific SQL in
`order to successfully submita table request to the third party's
`relational database system (RDBMS). This is accomplished
`by representing the SAS SQL statement as an SQL tree 60.
`The SQL tree 60 is passed to the textualization process 50 to
`convert the tree 60 into the text of the third party vendor
`specific SQL query, taking into account any DBMS-specific
`SQL. The textualization operation happens in this example
`just prior to the call to a prepare() or executedirect() routine.
`These standardized routines then pass the SQL query to an
`RDBMS in the form of text. It is noted that in an SQL-centric
`table services model, an SQL query typically gets passed to
`either the prepare() or executedirect() routines (depending
`on context). A call to either of these routines, therefore, con
`stitutes a request to an RDBMS.
`An application or program that operates within a native
`database system may wish to access data in a remote third
`party database system under Such situations as when the
`application is a data mining application that needs data from
`the third party system for operations to be performed within
`the data mining application. Other application examples
`include requests for metadata that are stored in a different
`type of database system. It should be understood that any
`system that uses SQL or a similar type of database technique
`may utilize the textualization system.
`The textualization system may be implemented in many
`ways and through different types of programming languages.
`FIG. 3 illustrates an object-oriented approach 100 to creating
`disparate SQL text for third party data access. The object
`oriented approach 100 contains SQL component objects 102
`where each component corresponds to a logical “piece 104
`of an SQL statement (as may be found in an SQL tree). An
`SQL component defaults to a provided base or default native
`55
`SQL text method 106. However, when there are third party
`specific differences for a particular component, then the com
`ponent utilizes the third party specific textualization
`method(s) 108 to handle the differences.
`As shown in FIG. 4, when there are third party-specific
`differences for a particular component, a driver object 110 is
`responsible for creating an “override” 112 to the default
`method 106. The driver object 110 specifies to a component
`object 102 when a component object 102 is to point to specific
`textualization method 112 instead of its base textualization
`methods. Optionally, only the driver 110 knows about its
`dataSource-specific SQL syntax 112.
`
`45
`
`50
`
`60
`
`65
`
`4
`In order to textualize for multiple different types of data
`base systems, different drivers (110, 112) are associated with
`different third party platforms. For example, a first driver
`object 110 might point a component object 102 to use an
`“override” 108 to the default method 106 so that the compo
`nent object 102 may textualize an SQL statement that can be
`used within a Sybase dataSource system. A second driver
`object 114 might point the component object 102 to use an
`“override” 116 to the default method 106 so that the compo
`nent object 102 may textualize an SQL statement that can be
`used within an Oracle dataSource system.
`The net effect of this object-oriented mechanism 100 is a
`driver-customized set of components where the driver need
`only Supply an override method when a specific SQL con
`struct differs from the default SAS SQL syntax. This design
`maximizes code reusability while pushing any DBMS-spe
`cific SQL text processing down to the driver, thereby distrib
`uting functionality more equitably.
`With reference to FIG. 5, components may be assigned to
`handle different pieces of the native SQL statement. The
`different components may be: phrase components 120; iden
`tifier components 122; and expression components 124.
`Phrase components 120 handle textualization of SQL state
`ment clauses or phrases, such as WHERE clauses or FROM
`clauses found in SQL Select Statements. To understand phrase
`components 120, the following exemplary SQL query state
`ment is dissected:
`select a...empid, b. sal from empa, hr b where b.hdat >
`*01jan1998'd order by b. sal;
`At the highest level we can think of this query as a group of
`ordered phrases (140, 142, 144, 146) as shown in FIG. 6.
`Phrase 140 of the query is a SELECT phrase; phrase 142 is a
`FROM phrase; phrase 144 is a WHERE phrase; and phrase
`146 is an ORDER BY phrase. These phrases (140, 142,144,
`146) provide the high-level context for the query. Eachphrase
`is represented and processed by a component object (150,
`152, 154, 156) whose default text method can be overridden
`by a driver at this “high level. The phrase components (150,
`152, 154, 156) textualize large pieces of a query that corre
`spond to high-level SQL operations, e.g., FROM clauses,
`WHERE clauses, ORDER BY clauses, etc.
`Typically, a driver would not have to override phrase com
`ponent methods because the general syntactical layout of
`phrases tends to be relatively standard across RDBMSs.
`However, exceptions may exist, such as those involving
`RDBMSs that support non-standard outer joins. In addition,
`Some ERP (enterprise resource planning) systems may have
`the need for phrase overrides since their “SQL' tends to be
`proprietary and very non-standard.
`Identifier components are next discussed in reference to
`FIG.7 using the query example above. We can further break
`down the phrases as collections of low-level “physical enti
`ties. These low-level “entities’ can be thought of as identifiers
`because they represent a real physical entity in an SQL query.
`Fully qualified table names, column names, literals, and
`aliases fall into this category of components. As shown in
`FIG. 7, the identifier component GenColName 160 textual
`izes the column names a...empid 162 and b.sal 164 within the
`SELECT phrase; the identifier component GenTableName
`170 textualizes the table names emp 172 and hr 174 within the
`FROM phrase; the identifier component GenAlias textualizes
`the aliases a 182 and b 184 within the FROM phrase. It is
`noted that aliases are a unique class of identifiers in that they
`are only valid in GenSelectList and GenFrom (and typically
`not allowed in filter clauses). The identifier component Gen
`ColName 190 textualizes the column name b.hdat 192 within
`
`World Programming Limited Exhibit 1001
`Page 22 of 28
`
`

`

`US 7,447,686 B2
`
`10
`
`15
`
`25
`
`30
`
`35
`
`40
`
`50
`
`55
`
`60
`
`5
`the WHERE phrase; the identifier component GenDateValue
`200 textualizes the date value 202 within the WHEREphrase:
`the identifier component GenColName 210 textualizes the
`column name b. sal 212 within the ORDER BY phrase.
`Components may use overrides to handle third party iden
`tifier differences, such as differences with respect to the date
`literal components. As another example, drivers may be used
`for several third party datasources to provide overrides for
`numeric literal components.
`Expression components are discussed next. So far we have
`seen components that operate on high-level SQL phrases and
`low-level physical identifiers. The SQL “entities” in the query
`example above have, thus far, been limited to column names,
`table names, and literals—all of which are physical entities. It
`should be noted, however, that the SQL syntax allows for
`much greater complexity in its entities—all or a portion of
`which the textualization system may handle. An SQL “entity”
`can be:
`a physical column
`a derived column
`a literal
`a physical table
`a subquery
`a stored procedure
`a function
`To further illustrate the breadth of SQL statements that the
`textualization system may handle, the following queries that
`conform to ANSI syntax are shown:
`Select X+1 from a;
`Select X+1 asXX1 from a;
`select max(a), (selectX from b), X from (select * from emp
`where empidd 10) as Subemp where 2:
`select tom as brian from emp where (empid+1)>100:
`select min(sal--20000) from emp where (select dept from
`emp)=SALES:
`In order to efficiently process Such SQL syntaxes and entities,
`expression components are used as a more abstract type of
`component. Optionally, expression components provide a
`common entry point into all forms of an SQL entity and to this
`end, SQL entities in a query are initially processed as expres
`sions. (Note that aliases may be an exception since they are
`only valid within the GenSelectList and GenFrom phrase
`components as described later in reference to FIG. 11). A
`generic expression component, GenExpression, may be used
`45
`which is the "catch-all” expression method through which
`SQL entities are initially processed. At the point where the
`GenExpression component is called, the SQL entity can be an
`identifier (e.g., column, table, literal) or it can be another,
`more granular expression that has more context than the
`generic GenExpression. Such 'granular expressions pro
`cessed by GenExpression are SQL functions, compound
`expressions, third party (or native) functions (e.g., SAS func
`tions), and SQL commands. Function expressions treat each
`function argument as a generic expression (since arguments
`can typically be any form of SQL entity). A compound
`expression includes an SQL keyword or operator combined
`with one or more other expressions, i.e., "empid is null'.
`“a+b'. IN clauses. It is noted that if an SQL is used, then the
`tree node type representing the keyword or operator may
`identify an expression as compound. Phrase components cor
`respond to the clauses (or phrases) of a command and may be
`processed in an order of precedence.
`An example of component processing is shown in FIG. 8
`using SELECT and UPDATE commands. Two SQL com
`65
`mands are shown in column 250—a SELECT command and
`an UPDATE command. Column 252 lists that the command
`
`6
`expression component GenQuery is used to determine what
`command phrase components are needed to textualize the
`SELECT command. Column 254 lists the order in which the
`phrases are processed, and column 256 lists the command
`phrase components that deal with a command phrase. As an
`illustration, the select list command phrase is textualized by
`its corresponding phrase component GenSelectList.
`The expression components discussed so far are conve
`niently categorized in FIG. 9. Column 300 denotes the
`expression components which operate upon their respective
`expression types shown in column 302. Column 304 shows an
`example of the expression types listed in column 302. How
`ever, it should be noted that command expressions can be
`extended to include other SQL statements.
`FIG.10 shows at 350 an exemplary component operational
`flow for processing input SQL statements. SQL entities are
`initially treated as generic expressions which are processed
`by the GenExpression expression component 352. The text
`method pointed to by the GenExpression component 352
`calls identifier components 360 or more granular expression
`components (354, 356, 358) depending on what the SQL
`entity is. Block 368 illustrates several identifier components
`that could be invoked, such as the GenDateValue component
`which textualizes date values or the GenTableName compo
`nent which textualizes table names.
`The granular expression components (354,356,358) have
`more context than GenExpression 352, that is, they represent
`a specific type of expression like a compound expression 354.
`function expression 356, or command expression 358. Block
`362 illustrates several compound situations where the Gen
`CompoundExpr component would be used, such as to handle
`a concatenation operation AB” or an addition operation
`“X+i’. Block 364 illustrates several function expression com
`ponents that could be compound situations where the Gen
`CompoundExpr component would be used, such as to handle
`a concatenation operation AB” or an addition operation
`“X+1. Block 366 illustrates several command expression
`components, such as a SELECT or UPDATE command
`expression. Because commands contain phrases, command
`expression components 358 invoke phrase components 370
`to textualize phrases as shown in block 372.
`Similar to GenExpression 352, granular expression com
`ponents (354, 356, 358) also call identifier components 360
`and other expression components, but within a more specific
`context. For example, the GenSASFunction component and
`the GenSQLFunction component (shown in block 364) have
`the context to know the type of function and how many
`arguments to process. They then call the GenExpression com
`ponent 352 for each function argument.
`To further illustrate the exemplary component operational
`flow, the example query described above is used and is as
`follows:
`
`al,
`
`hr b where
`
`select a.empid, b.sal from emp
`b.hdat-'01jan1998'd order by b. sal;
`We can see that at the highest level the entire query is a
`command expression that will be processed by the GenQuery
`expression component 358 shown in FIG. 10. Since Gen
`Query is itself an expression (as are all SQL commands),
`GenExpression 352 is the entry point into the text component
`system. It should be understood that the system may have
`different entry points, such as an entry point where the con
`verted tree calls directly other component objects or the entry
`point is to a program that checks the syntax of the input SQL
`command with respect to the native database system's query
`language format.
`
`World Programming Limited Exhibit 1001
`Page 23 of 28
`
`

`

`US 7,447,686 B2
`
`7
`After the driver loads the Software appendage, exports its
`overrides, and does setup operations, it calls GenExpression
`352 once to produce the entire SQL text from a provided SQL
`tree. In this example, this is true for all SQL commands, and
`all driver-provided overrides automatically get applied as
`needed because components utilize a common call interface.
`Drivers may also be free to perform setup operations. It
`should be noted that a query may also appearasan SQL entity
`within a query (referred to as a Subquery or inline view), so
`GenQuery 358 may be called multiple times.
`FIG. 11 shows in greater detail the order in which different
`components are invoked in processing the example query.
`The driver calls GenExpression 380 to textualize the query.
`GenExpression 380 calls GenQuery 390 to process the
`SELECT statement. GenQuery 390 then calls the following
`phrase components: GenSelectList 400A; GenFrom 404A:
`GenWhere 410A: GenOrderBy 420A. The phrase compo
`nents (400A, 404A, 410A, 420A) call GenExpression (401,
`405, 411,421) which, in turn, calls the appropriate identifier/
`expression components: GenExpression 401 calls GenCol
`Name 402A; GenExpression 405 calls GenTableName 406A:
`GenExpression 411 calls GenCompoundExpr 412A which
`calls GenColName 414A, and GenColName 414A calls Gen
`Datevalue 416A: GenExpression 421 calls GenColName
`422A. Note that GenFrom 404A calls GenAlias 408A
`directly (because aliases are only valid for select list items and
`result sets). As illustrated in FIG. 11, recursion is used as a
`mechanism of textualization.
`The following table lists what statement portions are tex
`tualized by which components (shown in FIG. 11):
`
`5
`
`10
`
`15
`
`25
`
`30
`
`8
`FIG. 12 provides an exemplary component listing of dif
`ferent phrase components 450, expression components 452,
`and identifier components 454 that a textualization system
`might wish to use for a select SQL statement. It should be
`understood that this list may be extended for non-SELECT
`components.
`While examples have been used to disclose the invention,
`including the best mode, and also to enable any person skilled
`in the art to make and use the invention, the patentable scope
`of the invention is defined by the claims, and may include
`other examples that occur to those skilled in the art. For
`example, different component objects may be used instead of
`or in addition to the above-listed component objects. As an
`illustration and with reference to FIG. 13, a “parent compo
`nent type 470 may be used in conjunction with overrides to
`other components. A "parent component 470 is a static com
`ponent that always points to a specific default method and is
`called from a corresponding override method when neces
`sary. That is, when a driver exports an override to a given
`method, the parent component 470 gives the driver a mecha
`nism to call back to the overridden default method. This may
`be used when an override method wishes to callback into the
`corresponding default (or parent) method to do the work
`when an override method does not require driver-specific
`processing for all cases of that method.
`To illustrate this point, consider the concatenation situation
`in the GenCompoundExpr component. Suppose a driver does
`not support the default 'ab' construct but instead requires
`“concat(a,b) or “a--b’. Suppose further that concatenation is
`the only compound expression that differs from the default
`(given the assumption that there are many types of compound
`expressions). Although the driver will write an override to
`GenCompoundExpr to handle concatenation, the driver
`should not have to code for the other compound expression
`types since the default method already does that. Calling the
`parent method from the driver's GenCompoundExpraccom
`plishes this. The Sybase database system has this concatena
`tion difference.
`A driver for the Sybase database system may handle the
`concatenation override in a manner shown in FIG. 14. If it is
`determined at 500 that an SQL tree node is a concatenation
`node that requires Sybase-specific textualization, then code is
`executed at 510 to textualize the Sybase-specific concatena
`tion. If it is not a node that requires special textualization, then
`the parent component of the GenCompoundExpr component
`is executed at 520.
`As another example involving parent components, the han
`dling of outer joins is described. Some RDBMSs do not
`Support standard outer join syntax in their older versions.
`Oracle presently is one of these RDBMSs. A driver for such
`an Oracle database system provides overrides to GenFrom
`and GenWhere to allow for these syntactical differences.
`With reference to FIG. 15, the GenWhere component 550
`would be required at 560 to put (+) outer join operators on
`WHERE conditions. Suppose the query only references
`INNER joins or we are dealing with a newer version of Oracle
`that does support standard outer join Syntax. The default
`GenWhere may be used at 570 for those cases.
`From the above examples we can see how the parent com
`ponents are named with respect to the standard components.
`Optionally, a parent component has the same name as its
`corresponding standard component but prefixed with the term
`"Parent’, and standard components have a corresponding
`parent as shown in FIG. 16.
`We can now see how the parent components allow the
`drivers to be very granular in how they implement their over
`ride methods. Using this override mechanism combined with
`
`COMPONENT
`
`STATEMENT PIECE
`
`GenSelectList 400A
`GenColName 402A
`
`GenFrom 404A
`GenTableName 406A
`
`GenAlias 408A
`
`GenWhere 410A
`GenCompoundExpr 412A
`GenColName 414A
`GenDate Value 416A
`GenOrderBy 420A
`GenColName 422A
`
`Select 400B
`a...empid 402B
`b.sal 402C
`from 404B
`emp 406B
`hr 406C
`a 408B
`b 408C
`where 410B
`(...) 412B and 412C
`b.hdat 414B
`*01jan98 d 41.6B
`order by 420B
`b.sal 422B
`
`35
`
`40
`
`45
`
`50
`
`55
`
`A driver may have no need to override either GenExpres
`sion 380 or GenQuery 390 because they are both high-level
`expression components that utilize components common to
`all standard SQL implementations.
`It is noted that GenSASFunct