US20060053089A1 - Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database - Google Patents
Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database Download PDFInfo
- Publication number
- US20060053089A1 US20060053089A1 US11/252,939 US25293905A US2006053089A1 US 20060053089 A1 US20060053089 A1 US 20060053089A1 US 25293905 A US25293905 A US 25293905A US 2006053089 A1 US2006053089 A1 US 2006053089A1
- Authority
- US
- United States
- Prior art keywords
- fields
- field
- look
- looked
- selection
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2423—Interactive query statement specification based on a database schema
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99932—Access augmentation or optimizing
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99933—Query processing, i.e. searching
- Y10S707/99934—Query formulation, input preparation, or translation
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99941—Database schema or data structure
- Y10S707/99943—Generating database or data structure, e.g. via user interface
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99951—File or database maintenance
- Y10S707/99952—Coherency, e.g. same view to multiple users
Definitions
- the present invention relates to the field of data processing. More specifically, the present invention relates to data processing techniques associated with data processing operations involving multiple tables of a relational database.
- relational database has arguably become the most widely used database model in database management.
- SQL Structured Query Language
- SQL Structured Query Language
- a software component is equipped to identify looked-up table fields in a data processing statement, and automatically includes with a SQL statement one or more appropriate JOIN clauses joining one or more target tables from which the table fields are to be looked up with a basis table.
- the SQL statement may e.g. be an INSERT, a SELECT, an UPDATE and a DELETE statement.
- the looked-up table fields are expressed in a multi-part form comprising a first part corresponding to a look-up table field, and a second part corresponding to a looked-up table field, concatenated to the first part using a predetermined special character.
- a software component is equipped to automatically expand table fields available for inclusion in a data processing operation to include table fields of a target table of a look-up table field, in response to the selection of the look-up table field.
- the second aspect is practiced in conjunction with the automatic inclusion of appropriate JOIN clauses to a SQL statement of the first aspect.
- FIG. 1 illustrates an overview of the present invention on the context of an application generator, in accordance with one embodiment
- FIG. 2 illustrates the concepts of look-up field and looked-up field, with the looked-up field referenced using the multi-part form of the present invention
- FIG. 3 illustrates the operational flow of the relevant aspects of the SQL generator of FIG. 1 , incorporated with the support for the multi-part looked-up field of the present invention, in accordance with one embodiment
- FIGS. 4 a - 4 b illustrate an example user interface of an example data processing operation, utilizing the multi-part looked-up field of the present invention, in accordance with one example application;
- FIG. 5 illustrates the operational flow of the relevant aspects of the input component of FIG. 1 in support of the user input interface of FIG. 4 a - 4 b, in accordance with one embodiment
- FIG. 6 illustrates an internal component view of a computer system suitable for use to practice the present invention, in accordance with one embodiment.
- the present invention includes a multi-part format for specifying a looked-up table field, and software components equipped with logic in support of the looked-up table field, making it easier for a less skillful user to access and process, or generate applications to access and process data dispersed in multiple tables of a relational database.
- the present invention will be primarily described in the context of an application generator, referencing the SQL SELECT statement.
- the present invention is not so limited, and may be practiced with a number of other SQL statements, such as the INSERT, UPDATE or DELETE statement, and in a variety of other contexts, e.g. a database query facility.
- various aspects of the present invention will be described, specific numbers, materials and configurations will be set forth. However, the present invention may be practiced with only some or all aspects, and/or without some of these specific details. In other instances, well-known features are omitted or simplified in order not to obscure the present invention.
- processor includes microprocessors, micro-controllers, digital signal processors, and the like, that are standalone, adjunct or embedded.
- application generator 102 includes in particular input component 104 associated with a data processing operation, and SQL statement generator 106 .
- application generator 102 In accordance with inputs received from an application developer user, application generator 102 generates applications 108 . Included among applications 108 are SQL statements 110 . SQL statements 110 include Create statements for use by applications 108 to create various tables 116 having a plurality of table fields (or simply fields) to store data, inside relational database 114 , through relational database management system 112 .
- SQL statements 110 also include SELECT statements for use by applications 108 to access created tables 116 for the stored data of the various fields (through relational database management system 112 ). As needed, SQL statements 110 may also include INSERT, UPDATE, DELETE and other statements.
- SQL statement generator 106 is advantageously equipped to support looked-up table fields, expressed in the multi-part form of the present invention.
- the support includes in particular the automatic generation of the appropriate JOIN clauses joining target tables (from which fields are to be looked up) to the basis table (comprising at least one of the look-up fields and typically, though not necessarily, other selected fields).
- input component 104 of the data processing operation is advantageously equipped to present fields of a table for selection by an application developer user for inclusion in the data processing operation. Further, input component 104 is advantageously equipped to expand the list of fields available for selection to include fields of a target table, if a selected field has been previously defined to be a look-up field with the aforementioned target table.
- Data processing operation may be any data processing operation known in art.
- An example of a data processing operation is report generation.
- Another example of a data processing operation is execution of a series of processing operations in the form of a script file.
- input component 104 may be a component of a report generator, a component of a script editor, or other software components of the like.
- the second aspect is practiced in conjunction with the earlier described first aspect. That is, upon assisted an application developer user in selecting the fields, including looked-up fields, for use in a data processing operation, SQL statements, such as SELECT, INSERT, UPDATE and DELETE statements, with appropriate JOIN clauses are generated.
- SQL statements such as SELECT, INSERT, UPDATE and DELETE statements
- the SQL knowledge required of an application developer user of application generator 102 is advantageously reduced, thereby enhancing the usability of generator 102 , as well as the productivity and experience of the application developer user.
- application generator 102 represents a broad range of application generators known in the art, including in particular, known web application generators, e.g. the web application development facilities offered by Westside, Inc. of Seattle, Wash., assignee of the present invention.
- relational database management system 112 may e.g. be the SQL Server offered by Microsoft, Inc. of Redmond, Wash., Oracle Database Management System offered by Oracle Inc of Redwood City, Calif., Database2 (DB2) offered by IBM of Armonk, N.Y. or other relational database management systems (RDBMS) of the like.
- the multi-part looked-up table field of the present invention is expressed in two parts, a first part 222 corresponding to the look-up field in a basis table (also referred to as a foreign key of the table), and a second part corresponding to the looked-up field 224 in a target table (also referred to as a primary key of the table), concatenated to first part 222 using a special character 226 (e.g. “:”).
- a special character 226 e.g. “:”.
- a “customer description” field (to be looked up) may be expressed under the present invention in the form of customer_id:customer_description,
- a “product description” field may be expressed under the present invention in the form of product_id:product_description
- an “employee name” field may be expressed under the present invention in the form of employee_id:employee_name.
- the corresponding look-up field 204 (or foreign key) is a member of a “basis” table 202
- the corresponding looked-up field 214 is a member of a “target” table 204 .
- each table 202 or 204 may comprise other fields 206 and 216 .
- conventions such as a convention involving more than two parts, may be practiced.
- the “conjunction” may be other special characters, such as “ ⁇ ”, “!”, “@”, “#”, “$”, “%”, “ ⁇ circumflex over ( 0 ) ⁇ ”, “&”, “*”, “
- multiple conjunctions are employed, with one conjunction, such as “:” denoting an Outer JOIN, and another conjunction such as“::” denoting an Inner JOIN.
- additional conjunction denoting other types of joins such as a Union JOIN may also be practiced.
- the multi-part looked-up table field of the present invention may be expressed in more than two parts, e.g. three parts, with a first part corresponding to the look-up field in a basis table, a second part corresponding to a first looked-up field in a first target table (which in turn is used as look-up field), and a third part corresponding to a second looked-up field in a second target table.
- the different parts are concatenated to each other using a special character (e.g. “:”).
- product_id:category_id:category_name specifying the looked-up field “category_name”, to be looked up using a look-up field “category_id”, which itself is looked up using a look-up field “product_id”.
- FIG. 3 illustrates the operation flow of the relevant aspects of SQL statement generator 106 of FIG. 1 , in the context of a data access request, in accordance with one embodiment.
- the embodiment assumes SQL statement generator 106 receives a data access request statement in a non-SQL form as input.
- the data access request statement has the syntax of
- the present invention is not so limited, in other embodiments, the present invention may also be practiced with other SQL statements, such as an INSERT, an UPDATE and a DELETE statement, as well as other “request” statement syntaxes may also be practiced.
- the substance of the request may also be communicated to SQL statement generator 106 in a non-statement form, e.g. through a function call or other techniques of parameter passing.
- the relevant operation flow starts at operation 302 , where generator 106 parses the input statement, e.g. to tokenize the elements in the input statement. Thereafter, for the embodiment, generator 106 identifies table field or fields in the input statement, operation 304 . Further, generator 106 identifies whether the fields are “standard” (i.e. non-looked-up) table field or fields or the fields are looked-up fields, operation 306 . In one embodiment, the determination is made based on a predetermined syntax of the multi-part looked-up field.
- generator 106 identifies the table (also referred to earlier as the basis table) of which the “standard” or non-looked-up field or fields are members, and the tables (also referred to earlier as the target tables) from which the specified looked-up fields are to be looked up.
- generator 106 identifies the table membership by accessing a data dictionary (not shown). In some of these embodiments, generator 106 maintains at least a work copy of the data dictionary.
- generator 106 automatically generates a functional equivalent SQL SELECT statement, enumerating the fields to be selected, a From clause, the basis table, and where applicable, the JOIN clauses and the target tables, as well as the associated ON clauses including the condition governing the joining of the rows of the joined tables, block 312 .
- FIGS. 4 a - 4 b illustrate an example user interface for selecting fields for a data processing operation, including usage of the multi-part looked-up field of the present invention, in accordance with one embodiment.
- the embodiment assumes in the course of table definition, a field may be designated as a look-up field, and each look-up field has a target table designated. Any one of a number of user interfaces and supporting logic may be practiced to facilitate such definition. The subject matter is beyond the scope of the present invention, and since it is well within the ability of those skilled in the art, such definitional facilities will not be described.
- FIG. 4 a illustrates a first state 402 a of this user interface, wherein for a list 404 a of eligible table fields 406 a is first initially presented for an application developer user to select for inclusion in a data processing operation, which as earlier described, may e.g. be a report generation operation.
- Fields 406 a may include in particular fields that are pre-defined look-up field 408 a.
- selected fields 416 a are “echoed” and displayed in area 414 a.
- FIG. 4 b illustrates a second state 402 b of this user interface, wherein upon selection of one of the look-up field 408 a, the list 404 b of eligible table fields 406 b is expanded to include table fields of the designated target table of the selected look-up field.
- the added table fields to be looked up are advantageously displayed using the multi-part looked-up field name of the present invention, e.g. look-up_field:lookedup_field.
- selected fields 416 b remained “echoed” and displayed in area 414 b.
- FIG. 5 illustrates the operational flow of the relevant aspect of input component 104 , in accordance with one embodiment.
- input component 104 presents a first list of fields for selection by an application developer user for inclusion in a data processing operation. Then input component 104 awaits for either a user selection of one of the listed fields or an indication of termination of operation, blocks 504 and 512 .
- input component 104 determines if the selected field is a defined look-up field, block 506 . If the selected field is determined to be a defined look-up field, input component 104 retrieves the fields of the pre-designated target table, add the retrieved fields to the list of fields available for user selection, block 510 . Otherwise, input component 104 simply notes the field selected, and the table of which the selected field is member, block 508 .
- the collected information is subsequent provided to SQL generator 106 to automatically generate a functional equivalent SQL SELECT statement, including in particular, the appropriate JOIN and ON clauses.
- the collected information is provided to SQL generator 106 in the syntax of the earlier described Table Select statement. In another embodiment, the collected information is provided to SQL generator 106 through a function call.
- FIG. 6 illustrates an example computer system suitable for use to practice the present invention in accordance with one embodiment.
- computer system 600 includes one or more processors 602 and system memory 604 .
- computer system 600 includes mass storage devices 606 (such as diskette, hard drive, CDROM and so forth), input/output devices 608 (such as keyboard, cursor control and so forth) and communication interfaces 610 (such as network interface cards, modems and so forth).
- the elements are coupled to each other via system bus 612 , which represents one or more buses. In the case of multiple buses, they are bridged by one or more bus bridges (not shown). Each of these elements performs its conventional functions known in the art.
- system memory 604 and mass storage 606 are employed to store a working copy and a permanent copy of the programming instructions implementing the software components (e.g. input component 104 and/or SQL statement generator 106 ) incorporated with the teachings of the present invention.
- the permanent copy of the programming instructions may be loaded into mass storage 606 in the factory, or in the field, as described earlier, through a distribution medium (not shown) or through communication interface 610 (from a distribution server (not shown).
- the constitution of these elements 602 - 612 are known, and accordingly will not be further described.
- the multi-part looked-up field name of the present invention may be used to improve the ease of use of other SQL statements and/or clauses, such as in addition to the aforementioned INSERT, UPDATE and DELETE statements, the WHERE, GROUP BY and SORT clauses.
- the description is to be regarded as illustrative instead of restrictive on the present invention.
Abstract
Description
- This application is a continuation application to U.S. patent application Ser. No. 10/038,412, filed on Oct. 25, 2001.
- The present invention relates to the field of data processing. More specifically, the present invention relates to data processing techniques associated with data processing operations involving multiple tables of a relational database.
- In the course of the last two to three decades, relational database has arguably become the most widely used database model in database management. Along with the growing popularity of relational databases, the Structured Query Language (SQL) has become an indispensable tool for accessing data stored in tables of relational databases.
- However, as those skilled in the art would appreciate, virtually all data accesses of any meaningful application would require access and processing of data resided in multiple tables. Such accesses and processing require the employment of the JOIN clause in a SQL statement (such as a SELECT, an INSERT, an UPDATE and a DELETE statement), joining tables of interest together. Experience has shown that except for professional programmers experienced with SQL, few users fully understand or are totally comfortable with joining tables. Unfortunately, the number of users having a need to access and process data dispersed in multiple tables in an unplanned manner far out number those who are skilled to comfortably do so.
- Recently, advances in integrated circuit, microprocessor, networking and communication technologies, have resulted in the popularization of the World Wide Web (WWW) and Web based applications, making available even a greater reservoir of data for access. In turn, the knowledge or skill gap problem discussed earlier is further magnified.
- Accordingly, an improved approach to accessing and processing data dispersed in multiple tables of relational databases, requiring lower data processing skill, is desired. 0
- In accordance with a first aspect, a software component is equipped to identify looked-up table fields in a data processing statement, and automatically includes with a SQL statement one or more appropriate JOIN clauses joining one or more target tables from which the table fields are to be looked up with a basis table. The SQL statement may e.g. be an INSERT, a SELECT, an UPDATE and a DELETE statement.
- In one embodiment, the looked-up table fields are expressed in a multi-part form comprising a first part corresponding to a look-up table field, and a second part corresponding to a looked-up table field, concatenated to the first part using a predetermined special character.
- In accordance with a second aspect, a software component is equipped to automatically expand table fields available for inclusion in a data processing operation to include table fields of a target table of a look-up table field, in response to the selection of the look-up table field.
- In one embodiment, the second aspect is practiced in conjunction with the automatic inclusion of appropriate JOIN clauses to a SQL statement of the first aspect.
- The present invention will be described by way of exemplary embodiments, but not limitations, illustrated in the accompanying drawings in which like references denote similar elements, and in which:
-
FIG. 1 illustrates an overview of the present invention on the context of an application generator, in accordance with one embodiment; -
FIG. 2 illustrates the concepts of look-up field and looked-up field, with the looked-up field referenced using the multi-part form of the present invention; -
FIG. 3 illustrates the operational flow of the relevant aspects of the SQL generator ofFIG. 1 , incorporated with the support for the multi-part looked-up field of the present invention, in accordance with one embodiment; -
FIGS. 4 a-4 b illustrate an example user interface of an example data processing operation, utilizing the multi-part looked-up field of the present invention, in accordance with one example application; -
FIG. 5 illustrates the operational flow of the relevant aspects of the input component ofFIG. 1 in support of the user input interface ofFIG. 4 a-4 b, in accordance with one embodiment; and -
FIG. 6 illustrates an internal component view of a computer system suitable for use to practice the present invention, in accordance with one embodiment. - The present invention includes a multi-part format for specifying a looked-up table field, and software components equipped with logic in support of the looked-up table field, making it easier for a less skillful user to access and process, or generate applications to access and process data dispersed in multiple tables of a relational database.
- For ease of understanding, the present invention will be primarily described in the context of an application generator, referencing the SQL SELECT statement. However, the present invention is not so limited, and may be practiced with a number of other SQL statements, such as the INSERT, UPDATE or DELETE statement, and in a variety of other contexts, e.g. a database query facility. Further, in the description to follow, various aspects of the present invention will be described, specific numbers, materials and configurations will be set forth. However, the present invention may be practiced with only some or all aspects, and/or without some of these specific details. In other instances, well-known features are omitted or simplified in order not to obscure the present invention.
- The description will be presented in terms of operations performed by a processor based device, using terms such as statements, tables, fields, determining, identifying, generating, and the like, consistent with the manner commonly employed by those skilled in the art to convey the substance of their work to others skilled in the art. As well understood by those skilled in the art, the quantities take the form of electrical, magnetic, or optical signals capable of being stored, transferred, combined, and otherwise manipulated through mechanical, electrical and/or optical components of the processor based device. Moreover, the term processor includes microprocessors, micro-controllers, digital signal processors, and the like, that are standalone, adjunct or embedded.
- Various operations will be described as multiple discrete steps in turn, in a manner that is most helpful in understanding the present invention, however, the order of description should not be construed as to imply that these operations are necessarily order dependent. In particular, these operations need not be performed in the order of presentation.
- The description repeatedly uses the phrase “in one embodiment”, which ordinarily does not refer to the same embodiment, although it may. The terms “comprising”, “including”, “having”, and the like, as used in the present application, are synonymous.
- Overview
- Referring now to
FIG. 1 , wherein a block diagram illustrating an overview of the present invention in the context of an application generator incorporated with the teachings of the present invention, in accordance with one embodiment, is shown. As illustrated,application generator 102 includes inparticular input component 104 associated with a data processing operation, and SQLstatement generator 106. In accordance with inputs received from an application developer user,application generator 102 generatesapplications 108. Included amongapplications 108 are SQLstatements 110. SQLstatements 110 include Create statements for use byapplications 108 to create various tables 116 having a plurality of table fields (or simply fields) to store data, insiderelational database 114, through relationaldatabase management system 112. SQLstatements 110 also include SELECT statements for use byapplications 108 to access created tables 116 for the stored data of the various fields (through relational database management system 112). As needed, SQLstatements 110 may also include INSERT, UPDATE, DELETE and other statements. - As will be described in more details below, in accordance with a first aspect of the present invention, SQL
statement generator 106 is advantageously equipped to support looked-up table fields, expressed in the multi-part form of the present invention. The support includes in particular the automatic generation of the appropriate JOIN clauses joining target tables (from which fields are to be looked up) to the basis table (comprising at least one of the look-up fields and typically, though not necessarily, other selected fields). - As will be also described in more details below, in accordance with a second aspect of the present invention,
input component 104 of the data processing operation is advantageously equipped to present fields of a table for selection by an application developer user for inclusion in the data processing operation. Further,input component 104 is advantageously equipped to expand the list of fields available for selection to include fields of a target table, if a selected field has been previously defined to be a look-up field with the aforementioned target table. - Data processing operation may be any data processing operation known in art. An example of a data processing operation is report generation. Another example of a data processing operation is execution of a series of processing operations in the form of a script file. Accordingly,
input component 104, may be a component of a report generator, a component of a script editor, or other software components of the like. - Further, in one embodiment, the second aspect is practiced in conjunction with the earlier described first aspect. That is, upon assisted an application developer user in selecting the fields, including looked-up fields, for use in a data processing operation, SQL statements, such as SELECT, INSERT, UPDATE and DELETE statements, with appropriate JOIN clauses are generated.
- Accordingly, the SQL knowledge required of an application developer user of
application generator 102, in particular, in the topic area of table joining, is advantageously reduced, thereby enhancing the usability ofgenerator 102, as well as the productivity and experience of the application developer user. - Except for the teachings of the present invention incorporated with
input generator 104 and SQLstatement generator 106,application generator 102 represents a broad range of application generators known in the art, including in particular, known web application generators, e.g. the web application development facilities offered by Westside, Inc. of Seattle, Wash., assignee of the present invention. - Similarly, except of the fact that
applications 108 being the beneficiary of the present invention, i.e. having selected ones of their SQL statements with their appropriate JOIN clauses automatically generated,applications 108, relationaldatabase management system 112 andrelational databases 114 all represent a wide range of these elements known in the art. In particular, relationaldatabase management system 112 may e.g. be the SQL Server offered by Microsoft, Inc. of Redmond, Wash., Oracle Database Management System offered by Oracle Inc of Redwood City, Calif., Database2 (DB2) offered by IBM of Armonk, N.Y. or other relational database management systems (RDBMS) of the like. - Multi-Part Looked-Up Field
- Turning now to
FIG. 2 , wherein the multi-part looked-up table field of the present invention, and the relationship between the various parts to the basis and target tables, in accordance with one embodiment, is illustrated. As shown, for the embodiment, the multi-part looked-up table field of the present invention is expressed in two parts, a first part 222 corresponding to the look-up field in a basis table (also referred to as a foreign key of the table), and a second part corresponding to the looked-up field 224 in a target table (also referred to as a primary key of the table), concatenated to first part 222 using a special character 226 (e.g. “:”). For examples, - 1. a “customer description” field (to be looked up) may be expressed under the present invention in the form of customer_id:customer_description,
- 2. a “product description” field (to be looked up) may be expressed under the present invention in the form of product_id:product_description, or an “employee name” field (to be looked up) may be expressed under the present invention in the form of employee_id:employee_name. As alluded to earlier and illustrated, the corresponding look-up field 204 (or foreign key) is a member of a “basis” table 202, whereas the corresponding looked-up field 214 (or primary key) is a member of a “target” table 204. Of course, each table 202 or 204 may comprise
other fields - In alternate embodiments, other conventions, such as a convention involving more than two parts, may be practiced. Further, the “conjunction” may be other special characters, such as “˜”, “!”, “@”, “#”, “$”, “%”, “{circumflex over (0)}”, “&”, “*”, “|”, “<”, “>”, or “.”, using selected combinations of multiple ones of these special characters, e.g. “<>”, or even non-special characters.
- In one embodiment, multiple conjunctions are employed, with one conjunction, such as “:” denoting an Outer JOIN, and another conjunction such as“::” denoting an Inner JOIN. In other embodiments, additional conjunction denoting other types of joins, such as a Union JOIN may also be practiced.
- Further, in other embodiments, the multi-part looked-up table field of the present invention may be expressed in more than two parts, e.g. three parts, with a first part corresponding to the look-up field in a basis table, a second part corresponding to a first looked-up field in a first target table (which in turn is used as look-up field), and a third part corresponding to a second looked-up field in a second target table. As before, the different parts are concatenated to each other using a special character (e.g. “:”). For example, product_id:category_id:category_name, specifying the looked-up field “category_name”, to be looked up using a look-up field “category_id”, which itself is looked up using a look-up field “product_id”.
- SQL Statement Generation
-
FIG. 3 illustrates the operation flow of the relevant aspects ofSQL statement generator 106 ofFIG. 1 , in the context of a data access request, in accordance with one embodiment. The embodiment assumesSQL statement generator 106 receives a data access request statement in a non-SQL form as input. In one embodiment, the data access request statement has the syntax of - Table Select {field name [, field name [. . . ]]}
-
- where field name may be a conventional field name (e.g. user_id) or
- a multi-part looked_up field name of the present invention (e.g. user_id:username).
- where field name may be a conventional field name (e.g. user_id) or
- However, as alluded to earlier, the present invention is not so limited, in other embodiments, the present invention may also be practiced with other SQL statements, such as an INSERT, an UPDATE and a DELETE statement, as well as other “request” statement syntaxes may also be practiced. In yet other embodiments, the substance of the request may also be communicated to
SQL statement generator 106 in a non-statement form, e.g. through a function call or other techniques of parameter passing. - As illustrated in
FIG. 3 , for the embodiment, the relevant operation flow starts atoperation 302, wheregenerator 106 parses the input statement, e.g. to tokenize the elements in the input statement. Thereafter, for the embodiment,generator 106 identifies table field or fields in the input statement,operation 304. Further,generator 106 identifies whether the fields are “standard” (i.e. non-looked-up) table field or fields or the fields are looked-up fields,operation 306. In one embodiment, the determination is made based on a predetermined syntax of the multi-part looked-up field. Atblocks generator 106 identifies the table (also referred to earlier as the basis table) of which the “standard” or non-looked-up field or fields are members, and the tables (also referred to earlier as the target tables) from which the specified looked-up fields are to be looked up. In various embodiments,generator 106 identifies the table membership by accessing a data dictionary (not shown). In some of these embodiments,generator 106 maintains at least a work copy of the data dictionary. - Thereafter, upon identifying the respective tables of which the standard (non-looked-up) and looked-up fields are members, as described earlier,
generator 106 automatically generates a functional equivalent SQL SELECT statement, enumerating the fields to be selected, a From clause, the basis table, and where applicable, the JOIN clauses and the target tables, as well as the associated ON clauses including the condition governing the joining of the rows of the joined tables, block 312. - For examples,
-
- (a) the input statement Table Select {student_id, class_id:class_name, teacher_id:teacher_name, },
generator 106 generates SELECT enrollment.student_id, class.class_name, teacher.teacher_name FROM enrollment LEFT OUTER JOIN class ON enrollment.class_id=class.class_id LEFT OUTER JOIN teacher ON enrollment.teacher_id=teacher.teacher_id; - (b) the input statement Table Select {order_no, product_id:product_name, product_id:category_id:category_name},
generator 106 generates SELECT order_items.order_no, products.product_name, categories.category_name FROM (order_items LEFT OUTER JOIN products ON order_items.product_id=products.product_id) LEFT OUTER JOIN categories ON products.category_id=categories.category_id (“order_items”, “products” and “categories” are the table names); and - (c) the input statement Table Select {task_name, assignedto:user_name, openedby:user_name}
generator 106 generates SELECT tasks.task_name, users_1.user_name, users_2.user_name FROM tasks LEFT OUTER JOIN users users_1 ON tasks.assignedto=users_1.user_id LEFT OUTER JOIN users users_2 ON tasks.openedby=users_2.user_id.
- (a) the input statement Table Select {student_id, class_id:class_name, teacher_id:teacher_name, },
- Field Selection
-
FIGS. 4 a-4 b illustrate an example user interface for selecting fields for a data processing operation, including usage of the multi-part looked-up field of the present invention, in accordance with one embodiment. The embodiment assumes in the course of table definition, a field may be designated as a look-up field, and each look-up field has a target table designated. Any one of a number of user interfaces and supporting logic may be practiced to facilitate such definition. The subject matter is beyond the scope of the present invention, and since it is well within the ability of those skilled in the art, such definitional facilities will not be described. -
FIG. 4 a illustrates afirst state 402 a of this user interface, wherein for alist 404 a of eligible table fields 406 a is first initially presented for an application developer user to select for inclusion in a data processing operation, which as earlier described, may e.g. be a report generation operation.Fields 406 a may include in particular fields that are pre-defined look-upfield 408 a. For the embodiments, selectedfields 416 a are “echoed” and displayed inarea 414 a. -
FIG. 4 b illustrates asecond state 402 b of this user interface, wherein upon selection of one of the look-upfield 408 a, thelist 404 b of eligible table fields 406 b is expanded to include table fields of the designated target table of the selected look-up field. For the embodiment, the added table fields to be looked up are advantageously displayed using the multi-part looked-up field name of the present invention, e.g. look-up_field:lookedup_field. For the embodiment, selectedfields 416 b remained “echoed” and displayed inarea 414 b. -
FIG. 5 illustrates the operational flow of the relevant aspect ofinput component 104, in accordance with one embodiment. As illustrated and alluded to earlier, initially atblock 502,input component 104 presents a first list of fields for selection by an application developer user for inclusion in a data processing operation. Then inputcomponent 104 awaits for either a user selection of one of the listed fields or an indication of termination of operation, blocks 504 and 512. - Upon receipt of a user selection, yes branch of
block 504,input component 104 determines if the selected field is a defined look-up field, block 506. If the selected field is determined to be a defined look-up field,input component 104 retrieves the fields of the pre-designated target table, add the retrieved fields to the list of fields available for user selection, block 510. Otherwise,input component 104 simply notes the field selected, and the table of which the selected field is member, block 508. - In one embodiment, the collected information is subsequent provided to
SQL generator 106 to automatically generate a functional equivalent SQL SELECT statement, including in particular, the appropriate JOIN and ON clauses. - In one embodiment, the collected information is provided to
SQL generator 106 in the syntax of the earlier described Table Select statement. In another embodiment, the collected information is provided toSQL generator 106 through a function call. - Example Computer System
-
FIG. 6 illustrates an example computer system suitable for use to practice the present invention in accordance with one embodiment. As shown, computer system 600 includes one ormore processors 602 andsystem memory 604. Additionally, computer system 600 includes mass storage devices 606 (such as diskette, hard drive, CDROM and so forth), input/output devices 608 (such as keyboard, cursor control and so forth) and communication interfaces 610 (such as network interface cards, modems and so forth). The elements are coupled to each other via system bus 612, which represents one or more buses. In the case of multiple buses, they are bridged by one or more bus bridges (not shown). Each of these elements performs its conventional functions known in the art. In particular,system memory 604 andmass storage 606 are employed to store a working copy and a permanent copy of the programming instructions implementing the software components (e.g. input component 104 and/or SQL statement generator 106) incorporated with the teachings of the present invention. The permanent copy of the programming instructions may be loaded intomass storage 606 in the factory, or in the field, as described earlier, through a distribution medium (not shown) or through communication interface 610 (from a distribution server (not shown). The constitution of these elements 602-612 are known, and accordingly will not be further described. - Conclusion and Epilog
- Thus, an improved method and apparatus for accessing and processing data disposed in multiple tables of a relational database has been described. While the present invention has been described in terms of the above illustrated embodiments, those skilled in the art will recognize that the invention is not limited to the embodiments described. The present invention can be practiced with modification and alteration within the spirit and scope of the appended claims. For example, the multi-part looked-up field name of the present invention may be used to improve the ease of use of other SQL statements and/or clauses, such as in addition to the aforementioned INSERT, UPDATE and DELETE statements, the WHERE, GROUP BY and SORT clauses. Thus, the description is to be regarded as illustrative instead of restrictive on the present invention.
Claims (24)
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US11/252,939 US7451138B2 (en) | 2001-10-25 | 2005-10-18 | Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database |
Applications Claiming Priority (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US10/038,412 US6988094B2 (en) | 2001-10-25 | 2001-10-25 | Multi-part looked-up table field and its use to in data processing operations involving multiple tables of a relational database |
US11/252,939 US7451138B2 (en) | 2001-10-25 | 2005-10-18 | Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database |
Related Parent Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US10/038,412 Continuation US6988094B2 (en) | 2001-10-25 | 2001-10-25 | Multi-part looked-up table field and its use to in data processing operations involving multiple tables of a relational database |
Publications (2)
Publication Number | Publication Date |
---|---|
US20060053089A1 true US20060053089A1 (en) | 2006-03-09 |
US7451138B2 US7451138B2 (en) | 2008-11-11 |
Family
ID=30113612
Family Applications (2)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US10/038,412 Expired - Lifetime US6988094B2 (en) | 2001-10-25 | 2001-10-25 | Multi-part looked-up table field and its use to in data processing operations involving multiple tables of a relational database |
US11/252,939 Expired - Lifetime US7451138B2 (en) | 2001-10-25 | 2005-10-18 | Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database |
Family Applications Before (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US10/038,412 Expired - Lifetime US6988094B2 (en) | 2001-10-25 | 2001-10-25 | Multi-part looked-up table field and its use to in data processing operations involving multiple tables of a relational database |
Country Status (1)
Country | Link |
---|---|
US (2) | US6988094B2 (en) |
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20080091707A1 (en) * | 2004-12-10 | 2008-04-17 | Aoki-Rice Companies | Method and medium for managing data |
Families Citing this family (11)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
JP4153883B2 (en) * | 2004-03-02 | 2008-09-24 | 株式会社東芝 | Hierarchical database device and product selection method and program in hierarchical database device |
US7512625B2 (en) * | 2005-04-01 | 2009-03-31 | International Business Machines Corporation | Method, system and program for joining source table rows with target table rows |
FR2920897B1 (en) * | 2007-09-11 | 2010-07-30 | Marc Vogel | METHOD FOR QUERYING A DATABASE AND INTERROGATION DEVICE |
US8418084B1 (en) * | 2008-05-30 | 2013-04-09 | At&T Intellectual Property I, L.P. | Single-touch media selection |
US8463818B2 (en) | 2011-10-31 | 2013-06-11 | International Business Machines Corporation | Single command data warehouse table update |
CN105705094B (en) * | 2013-09-02 | 2019-12-31 | 奥斯派克特公司 | Testing and determining threshold values |
US10441717B2 (en) | 2014-04-15 | 2019-10-15 | Insulet Corporation | Monitoring a physiological parameter associated with tissue of a host to confirm delivery of medication |
US10448885B2 (en) | 2015-06-12 | 2019-10-22 | Insulet Corporation | Confirmation of delivery of medication to a host |
US10701038B2 (en) * | 2015-07-27 | 2020-06-30 | Cisco Technology, Inc. | Content negotiation in a content centric network |
EP3380061A4 (en) | 2015-11-24 | 2019-07-24 | Insulet Corporation | Wearable automated medication delivery system |
US11241532B2 (en) | 2018-08-29 | 2022-02-08 | Insulet Corporation | Drug delivery system with sensor having optimized communication and infusion site |
Citations (9)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5291583A (en) * | 1990-12-14 | 1994-03-01 | Racal-Datacom, Inc. | Automatic storage of persistent ASN.1 objects in a relational schema |
US5412804A (en) * | 1992-04-30 | 1995-05-02 | Oracle Corporation | Extending the semantics of the outer join operator for un-nesting queries to a data base |
US5499359A (en) * | 1994-01-18 | 1996-03-12 | Borland International, Inc. | Methods for improved referential integrity in a relational database management system |
US5548755A (en) * | 1995-02-17 | 1996-08-20 | International Business Machines Corporation | System for optimizing correlated SQL queries in a relational database using magic decorrelation |
US5615367A (en) * | 1993-05-25 | 1997-03-25 | Borland International, Inc. | System and methods including automatic linking of tables for improved relational database modeling with interface |
US5619688A (en) * | 1993-09-02 | 1997-04-08 | Microsoft Corporation | Method and system for constructing database queries using a field selection grid |
US5832477A (en) * | 1994-10-20 | 1998-11-03 | International Business Machines Corporation | Method and apparatus for reordering complex SQL queries containing inner and outer join operations |
US6088691A (en) * | 1995-01-30 | 2000-07-11 | International Business Machines Corporation | Enumerating projection in SQL queries containing outer and full outer joins in the presence of inner joins |
US20020013779A1 (en) * | 2000-03-20 | 2002-01-31 | Sridhar Mandayam Andampillai | Reverse foreign key techniques in website development |
Family Cites Families (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US7076482B2 (en) * | 2001-10-25 | 2006-07-11 | Bea Systems, Inc. | Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database |
-
2001
- 2001-10-25 US US10/038,412 patent/US6988094B2/en not_active Expired - Lifetime
-
2005
- 2005-10-18 US US11/252,939 patent/US7451138B2/en not_active Expired - Lifetime
Patent Citations (9)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5291583A (en) * | 1990-12-14 | 1994-03-01 | Racal-Datacom, Inc. | Automatic storage of persistent ASN.1 objects in a relational schema |
US5412804A (en) * | 1992-04-30 | 1995-05-02 | Oracle Corporation | Extending the semantics of the outer join operator for un-nesting queries to a data base |
US5615367A (en) * | 1993-05-25 | 1997-03-25 | Borland International, Inc. | System and methods including automatic linking of tables for improved relational database modeling with interface |
US5619688A (en) * | 1993-09-02 | 1997-04-08 | Microsoft Corporation | Method and system for constructing database queries using a field selection grid |
US5499359A (en) * | 1994-01-18 | 1996-03-12 | Borland International, Inc. | Methods for improved referential integrity in a relational database management system |
US5832477A (en) * | 1994-10-20 | 1998-11-03 | International Business Machines Corporation | Method and apparatus for reordering complex SQL queries containing inner and outer join operations |
US6088691A (en) * | 1995-01-30 | 2000-07-11 | International Business Machines Corporation | Enumerating projection in SQL queries containing outer and full outer joins in the presence of inner joins |
US5548755A (en) * | 1995-02-17 | 1996-08-20 | International Business Machines Corporation | System for optimizing correlated SQL queries in a relational database using magic decorrelation |
US20020013779A1 (en) * | 2000-03-20 | 2002-01-31 | Sridhar Mandayam Andampillai | Reverse foreign key techniques in website development |
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20080091707A1 (en) * | 2004-12-10 | 2008-04-17 | Aoki-Rice Companies | Method and medium for managing data |
Also Published As
Publication number | Publication date |
---|---|
US20040010507A1 (en) | 2004-01-15 |
US7451138B2 (en) | 2008-11-11 |
US6988094B2 (en) | 2006-01-17 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US7451138B2 (en) | Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database | |
US7076482B2 (en) | Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database | |
US6996567B2 (en) | Automatic generation of join graphs for relational database queries | |
US7734657B2 (en) | Containment hierarchy in a database system | |
US5263167A (en) | User interface for a relational database using a task object for defining search queries in response to a profile object which describes user proficiency | |
US6219670B1 (en) | Method and apparatus for filtering a table list before opening with a graphical user interface | |
US6594669B2 (en) | Method for querying a database in which a query statement is issued to a database management system for which data types can be defined | |
US6256627B1 (en) | System and method for maintaining a knowledge base and evidence set | |
US20060116999A1 (en) | Sequential stepwise query condition building | |
US6374252B1 (en) | Modeling of object-oriented database structures, translation to relational database structures, and dynamic searches thereon | |
US6418450B2 (en) | Data warehouse programs architecture | |
US5142674A (en) | Interchange object data base index which eliminates the need for private copies of interchange documents files by a plurality of application programs | |
WO1996023266A1 (en) | End user query facility | |
Stonebraker | Object management in POSTGRES using procedures | |
US20040215612A1 (en) | Semi-boolean arrangement, method, and system for specifying and selecting data objects to be retrieved from a collection | |
KR101201019B1 (en) | Declarative sequenced report parameterization | |
US20060235867A1 (en) | Map and data location provider | |
McAlpine et al. | Integrated information retrieval in a knowledge worker support system | |
US8037092B2 (en) | System and method for merging manual parameters with predefined parameters | |
Schneider et al. | A graphical query language for semantic data models | |
JPH07325835A (en) | Retrieval sentence preparing device | |
Bell et al. | ADT: a toolkit for development of database-centered applications | |
Turner et al. | An introduction to the formal specification of relational query languages | |
Cuthill | Making sense of software engineering environment framework standards | |
JPS63257826A (en) | Data base retrieval device |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
STCF | Information on status: patent grant |
Free format text: PATENTED CASE |
|
FEPP | Fee payment procedure |
Free format text: PAYOR NUMBER ASSIGNED (ORIGINAL EVENT CODE: ASPN); ENTITY STATUS OF PATENT OWNER: LARGE ENTITY |
|
CC | Certificate of correction | ||
AS | Assignment |
Owner name: ORACLE INTERNATIONAL CORPORATION, CALIFORNIA Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNOR:BEA SYSTEMS, INC.;REEL/FRAME:025084/0470 Effective date: 20100929 |
|
FPAY | Fee payment |
Year of fee payment: 4 |
|
FPAY | Fee payment |
Year of fee payment: 8 |
|
MAFP | Maintenance fee payment |
Free format text: PAYMENT OF MAINTENANCE FEE, 12TH YEAR, LARGE ENTITY (ORIGINAL EVENT CODE: M1553); ENTITY STATUS OF PATENT OWNER: LARGE ENTITY Year of fee payment: 12 |