[go: up one dir, main page]

WO2008131465A1 - Procédé de commande d'un système de banque de données relationnelle - Google Patents

Procédé de commande d'un système de banque de données relationnelle Download PDF

Info

Publication number
WO2008131465A1
WO2008131465A1 PCT/AT2007/000195 AT2007000195W WO2008131465A1 WO 2008131465 A1 WO2008131465 A1 WO 2008131465A1 AT 2007000195 W AT2007000195 W AT 2007000195W WO 2008131465 A1 WO2008131465 A1 WO 2008131465A1
Authority
WO
WIPO (PCT)
Prior art keywords
decision
companies
sql statement
statement
superordinate
Prior art date
Application number
PCT/AT2007/000195
Other languages
German (de)
English (en)
Inventor
Matthias MÖSTL
Original Assignee
Mediareif Möstl & Reif Kommunikations- Und Informationstechnologien Oeg
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Mediareif Möstl & Reif Kommunikations- Und Informationstechnologien Oeg filed Critical Mediareif Möstl & Reif Kommunikations- Und Informationstechnologien Oeg
Priority to PCT/AT2007/000195 priority Critical patent/WO2008131465A1/fr
Publication of WO2008131465A1 publication Critical patent/WO2008131465A1/fr

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; 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/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • G06F16/24539—Query rewriting; Transformation using cached or materialised query results
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; 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/2433—Query languages
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; 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/245—Query processing
    • G06F16/2452—Query translation
    • G06F16/24524—Access plan code generation and invalidation; Reuse of access plans
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; 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/245—Query processing
    • G06F16/2452—Query translation
    • G06F16/24526—Internal representations for queries
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; 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/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation

Definitions

  • the invention relates to a method for controlling a relational database system by executing a database query in a relational database containing as associated data structure a plurality of data tables linked by relations as well as a table of relations, using a database language.
  • the starting point for the invention is the occurrence in practice of similar requests to a relational database. Despite the apparent relationship similar
  • a further object of the invention is to enable the non-specialist to easily and intuitively formulate statements which are subsequently automatically converted into statements that are sometimes very complex and even difficult to formulate for the person skilled in the art.
  • the object of the invention is to achieve the greatest possible independence from formulated statements and underlying data structures by means of a short notation, which focuses on the nature of a problem. According to the invention, this is achieved in a method mentioned in the introduction by comprising the following steps:
  • Value fields is defined and each has at least one decision path
  • each decision path at least one of the decision positions can always be reached, which specifies by means of an associated flag that it contains at least one table field which is unambiguously assignable to one of the data tables, possibly by means of supplementary specification of a data table name, to the specific underlying data structure,
  • Creating a user SQL statement by a user in a formulating step in which at least one of the table-superordinate value fields is selected from the set of table-superordinate value fields by the user and optionally with parameters, in at least one of the processing areas in at least one of the hierarchy levels, but independent of the data tables used in the user SQL statement, used in the formulation of the user SQL statement, and in which, where appropriate, relationship-based operations are not included
  • one or more table-superordinated value fields are created by the user as required, the inner, directed decision graph of the same depending on the existing tables and queries of the given data structure.
  • a semi-automated creation of the table-superordinated value fields can be provided.
  • table-superordinate value field in the context of the present application, an element is understood which is treated as a table field or as a table in the specification of SQL statements, thus to be treated as such in the statements for the user.
  • a value or a set of values can be expected from each table-superordinate value field at the respective position of the statement.
  • tablette superordinate refers to the independence of the value fields from the defined tables and queries in the respective data structure
  • table parent value fields sometimes return one or more tables after their call, which are not in any relational relationship specified by a user in the statement to the tables or queries associated with them according to an SQL statement, a completion of the missing relational operations is under Application preferably a table of relations and gradually described method in these cases necessary.
  • the table of relations contains at least all relationships between all the tables and queries of the respective underlying data structure, and it can also contain freely generated relations.
  • a directed decision graph of a table-superordinated value field denotes a directed graph according to the prior art, the nodes of the graph representing decision positions.
  • a decision path denotes a possible path by the directed decision graph from a first reachable decision position within it to a last decision position defined. The sum of all decision paths thus gives rise to all possibilities of traversing a decision graph from first decision positions to last decision positions, the superimposition of these different pass possibilities over the decision paths is graphically represented such that branch points arise at certain points of the resulting decision graph.
  • the statement resolution step which calls and uses all table-superordinated value fields used in a statement with parameters specified according to the statement
  • the decision paths that are affected by the given parameters and their pre-definable evaluation are displayed in each table-superordinate value field until the end of the table-superordinate value field Value field tracked.
  • all decision positions are collected and returned as a result, which by means of an associated flag determine that they contain at least one table field that can be uniquely assigned to a table or set of the concrete underlying data structure, as well as all results of those Decision items on which a table-superordinate value field with specified parameters is traversed.
  • the result of the passage of a table-superordinate value field is created either as a criterion, grouping, additional quantity and / or lower hierarchy level - SUB-SELECT etc.
  • the query language SQL consists of predefined processing areas and processing sequences, such as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, UNION.
  • Table superordinate value fields can sometimes be used in more than one processing area of a specified SQL statement.
  • the main element of an SQL query is formed by relationship-based operations, such as projection, join, or selection, or set-based operations, such as set union, set intersection, or set difference.
  • relationship-based operations such as projection, join, or selection
  • set-based operations such as set union, set intersection, or set difference.
  • the invention relates to relational database systems that allow access to the data stored in a database using a database language.
  • the widely used database language SQL is used, but the invention is not limited to the use thereof.
  • the main tasks of a database system are attaching, modifying, deleting, and managing data and providing it through database queries.
  • a relational database system which comprises a computer system with a relational database, a data processing unit and a memory, wherein the data processing unit operates according to the inventive method.
  • a computer program may be stored on a computer-readable medium, such as a floppy disk, CD or DVD, having computer program code means, upon loading the computer program, a computer through the program for carrying out the method of producing a data carrier or electronic carrier signal according to the invention is caused.
  • a computer program product which has a computer program on an electronic carrier signal in which, after the computer program has been loaded, a computer is caused by the program for carrying out the method according to the invention.
  • the invention relates to a computer program having instructions that are set up for carrying out the method according to the invention.
  • the invention relates to a computer program product which has a computer-readable medium with computer program code means, in which, after loading the computer program, a computer is caused by the program for carrying out the method according to the invention.
  • the invention also relates to a computer program product which has a computer program on an electronic carrier signal, in which, in each case after loading the computer program, a computer is caused by the program for carrying out the method according to the invention.
  • 1 is a schematic representation of hierarchy levels within an SQL statement in the form of an ordered tree for the application of an embodiment of the method according to the invention to a query example; 2 shows a schematic representation of Hierarcliiecut within an SQL statement in the form of an ordered tree for the application of an embodiment of the method according to the invention to a further query example; 3 shows a schematic representation of an SQL RTN according to the prior art; 4, 5, 6 each show a schematic representation of tabular value fields according to the invention with their inner directed decision graphs and their decision positions.
  • relations-related operations all the required relations of a particular statement are referred to collectively as relations-related operations.
  • each relational database there is an associated data structure in the form of a plurality of data tables linked by relations. These are understood to mean data organized in columns and rows, as given below by way of example in Tables 1 to 10.
  • the term "row relation” is understood to mean in each case the rows of a table, that is to say, for example, Table 1 companies (company ID, name, street, postcode, city, country, employees, supervisor) and for table 2 departments (department ID, company no "Relationships", however, the connections between each two data tables on each at least one key field, eg 1: 1, 1: n, n: m - 1: n applies to: Companies (Firmald) ⁇ -> Departments (CompanyNr).
  • the Firmald column in the Companies table (Table 1) is a primary key for which there can be any number of values in the Departments table (Table 2) in the CompanyNr column.
  • each data structure there is a table of relations in which all the data structures associated with the queried database are contained, as well as optionally freely generated relations.
  • Sales of certain items in a given period Sales of certain groups of items in a given period
  • Each of these sales or count values to be calculated can be formulated via a particular query by a user in SQL. This effort must be provided by the user for each sales query. For all these cases, the SQL queries look fundamentally different and must be reformulated depending on the case. This is possible for the normal user sometimes only with great time and effort. Especially if the underlying data structure, for example due to a Enlargement of the system, all requests must be redesigned.
  • a table-superordinate value field and thus also the specifically used table superordinate value field "turnover" in a user SQL statement can be used independently of the data tables and the processing areas defined in this user SQL statement in respective hierarchy levels.
  • a concrete question may be:
  • the application of the statement resolution step will be shown later in the description to show the focus in this part of the introduction on the basic advantages of the simple, short and data structure independent notation by formulation of user SQL statements according to the invention.
  • the used table-superordinate value field returns the turnover taking into account the parameters specified in each case, but at the positions used after the resolution step and the completion of the relationship-related operations in the completion step, the turnover per data set company is calculated taking into account the relation-related operations.
  • Another specific question posed by a user may be:
  • a simple user SQL statement is specified, which contains the table-superordinate value field turnover with a parameter, whereby the value of this value field delivers the turnover per contact person per company through the statement resolution step and after completing the relationship-related operations in the completion step.
  • the inner, directed decision graph of the table-superordinate value field is, according to the underlying data structure, taken into account by the optional parameter "gross "extended by at least one further decision position.
  • table-superordinate value field SalesBrutto can contain the same optional parameters as the table-superordinate value field Sales, whereby the inner, directed decision graph of the table-superordinate value field
  • SalesBrutto contains only one decision item. This one decision item in the table-superordinate value field SalesBrutto calls the table-superordinate value field Sales with all its optional parameters, with the parameter "Gross" as
  • table-superordinate value fields such as these three table-superordinate value fields:
  • these table-superordinate value fields use the same parameters as the table-superordinate value field Sales and set these as parameters for their single decision position, which contains the table-superordinate value field Sales.
  • the advantage of using table-superordinate value fields at decision positions of an inner, directed decision graph of another table-superordinated value field is that a change of the inner, directed decision graph of the inserted table-superordinate value field does not influence the table-superordinated value field containing this table-superordinate value field at at least one of its decision positions.
  • Another example is to supply all the columns of the table companies, the number of events of the last 3 years and the turnover of the last 3 years of the companies that booked more than 10 events in 2006 and more than 1000 euros in 2006:
  • the following user SQL statement presents this question:
  • This example shows the multiple usage of two different table-superordinate value fields, each with different processing areas.
  • the application of the statement resolution step as well as the application of the completion step will be shown later in the description in detail for a variety of examples.
  • An RTN is the abbreviation for the well-known term "recursive transition network.”
  • An RTN defines the expressive power of a language in which syntactically formable options are defined in it.An extension of the RTN of a language leads to a stronger expressiveness of the same.
  • First-to-fourth-generation languages, including SQL, are turing-complete, meaning a language's increased expressiveness means that complex questions can be defined more easily and more accurately, especially by a non-specialist given statements.
  • Each request request is from a fixed predetermined sequence of compulsory sequentially arranged processing areas 340, 341, 342, 343, 344, 345 with keywords 20, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 331
  • decision positions 310, 311, 312, 313 - for the sake of simplicity only for the introductory keywords 324, 326, 328, 330 of the processing areas 342, 343, 344 and 345 - a very specific selection of keywords can be made to receive the desired inquiry result.
  • a particular grouping of keywords each form a processing area, eg, FROM (keywords 322, 323), WHERE (keywords 324, 325), GROUP BY (keywords 326, 327), etc., each processing area performing a set processing function associated with it, eg, forming FROM Cartesian products with the possibility of special JOINs.
  • SQL and related query languages are relatively limited and there are a number of queries which can only be described in a very cumbersome and tedious manner with the given processing areas in the given processing order and therefore become more complex and complex slower access plan is generated than actually required.
  • Table-higher value fields are created in a preset step.
  • the definition of table-superordinate value fields with possibly optional parameters as well as their inner, directed decision graph can be done, for example, in a system table, in a text or XML file.
  • the table-superordinated value fields are preferably read into the memory once prior to the analysis of SQL statements, so that they can be accessed quickly at any time.
  • table-superimposed value fields are created by means of the introductory string "Define ConnectionField” followed by a freely selectable name corresponding to the function of this field.
  • the name of the table-superordinate value field is followed by an open parenthesis and a list of the optional parameters Listing is terminated with a closed parenthesis
  • the end of a table-superordinate value field is identified in the description by means of the string “End Define”, which concludes a table-superordinate value field.
  • the inner, directed decision graph of a table-superordinate value field is defined within the introductory string and the string terminating this table-superordinate value field.
  • each decision path at least one decision position can always be reached, which specifies by means of an associated flag that it contains at least one table field which is unambiguously assignable to one of the data tables, possibly by means of supplementary specification of a data table name, of the specific underlying data structure.
  • a table-superordinate value field is created whose inner, directed decision graph has a single decision path with only one decision position.
  • a decision position is reached which, regardless of a given data structure, reaches a table field.
  • This decision position also contains a processing function and the specification of the data table to which the table field used refers.
  • a first table-superordinate value field is created, which is added to the entirety of the table-superordinate value fields and, for the sake of simplicity, contains no optional parameters: Define ConnectionField Count () Count (Distinct Fi ⁇ nen.FirmalD) End Define
  • the inner directional decision graph which exists on the single decision path 1051, becomes a table-superordinate value field 1001 is shown in FIG.
  • the decision position 1101 contains a value which, upon reaching this decision position 1101, is added to the result of the passage of the table-superordinate value field 1001.
  • the inner, directed decision graph of this table-superordinate value field has no influence on the use in a user SQL statement, so for a different data structure a table-like value field of the same name can be created, which has the following inner, directed decision graphs with possibly only one Decision position contains:
  • FIG. 5 shows the inner, directed decision path 1051 and its decision position 1101 of the table-superordinated value field 1001 adapted for another data structure.
  • At least one data table to be reached is defined, in concrete terms, the data table companies or table_companies. On the basis of a specified statement, it is searched from which data table or set of these at least one table to be reached is to be reached.
  • SELECT Count Distinct Company.CompanyID AS Number of companies or SELECT Count (Table_Firmen.PK_Firma) AS Number of companies
  • SELECT Count Distinct Company.CompanyID AS Number of Companies FROM Companies or SELECT Count (Distinct Table_Firmen.PK_Firma) AS Number of Companies FROM Table_Companies
  • This final statement contains all the necessary relationship-based operations and can thus be parsed by a SQL engine.
  • all SQL statements are user SQL statements in which at least one of the table-superordinate value fields from the entirety of the table-superordinate value fields is selected by the user and optionally with parameters, in at least one of the processing areas in at least one of the hierarchy levels, but independent of the User SQL statement used in the formulation of the user SQL statement, and in which, where appropriate, relationship-based operations are not included.
  • This transient SQL statement like all subsequent transient SQL statements as well, can be augmented with the necessary relational operations as shown below.
  • the following final SQL statement is generated and passed to an SQL processing engine for analysis, optimization, and execution:
  • the converted transitional SQL statement automatically creates the following new and final SQL statement into which the relationship-related operations are already inserted by means of the procedures shown below:
  • the converted transitional SQL statement automatically creates the following new and final SQL statement into which the relationship-related operations are already inserted by means of the methods shown below:
  • the principle shown above can also be used to omit the GROUP BY processing area in the user SQL statement, since an identical statement can be generated in this case.
  • the transitional SQL statement automatically converted in the statement resolution step is as follows:
  • This transient SQL statement contains all the necessary relationship-based operations, so the final SQL statement in this case equals the transient SQL statement.
  • An algorithm can detect that there is an aggregate function in the SQL processing area WHERE, which is why this processing information is moved to the HAVING processing area and the final SQL statement therefore has the following syntax, which is extended by the missing relationship-based operations:
  • a table-superordinate value field can also be set in the FROM processing area of a user SQL statement, which creates a separate quantity for this table-superordinate value field in the FROM processing area:
  • Processing range FROM is used and can generate a corresponding, simple final SQL statement:
  • An advanced RTN based on which SQL statements can be created, can transform the following question into a state-of-the-art SQL statement: First, a simple table-superordinate value field is created, which returns the revenue in each case in connection with the tables used in an SQL statement:
  • the table-superordinate value field Umatz_Ge Scheme which is specified in the processing area SELECT of the user SQL statement, refers to the individual data records of the Cartesian product, ie the total turnover per company and contact person is calculated using this table-superordinate value field.
  • a simple algorithm recognizes that the result of the table-superordinate value field Sales_Total () at the two positions used in the respective processing areas in the user SQL statement contains at least - in the specific case exactly - a data table which is not stored in the user SQL Statement specified in the data tables or Cartesian products of the respective hierarchy level.
  • an intermediate set or a SUB-SELECT is formed in the resolution step for the conversion of this user SQL statement into the transitional SQL statement.
  • This final SQL statement is passed to a SQL engine which analyzes, optimizes and calculates this state-of-the-art statement.
  • a simple algorithm recognizes that, in the processing area WHERE, a table is connected to a table-superordinate value field via a point, and therefore this information must be transferred to the processing area FROM.
  • This table-superordinate value field 1002 is shown in FIG.
  • This table-superordinated value field 1002 has an inner, directed decision graph 1051, from which, depending on the evaluation of the parameters 1102, partial decision paths are traversed, whereby when passing through this table-superordinate value field 1002, each time results in a tracked decision path.
  • the product-formation processing area which contains the data tables to be used in the respective hierarchy level and describes the formation of a Cartesian product for them, is defined as an optional decision path only, and in those cases where this product-building processing area is not specified in the user SQL statement, it is automatically created in a later step and, for example, by means of a table of relations, determines all the data tables needed to resolve the user's SQL statement be inserted in this processing area and
  • the SQL RTN can be extended to extend those decision positions in the RTN of SQL to which a table name can be specified independently of the processing areas, such that instead of a table name, a table-superordinate value field is allowed at those decision locations, with a table-topping Value field does not necessarily have to follow further decision positions, and the user SQL statements concerned by this RTN extension are first transformed in the resolution step into a transitional SQL statement and subsequently into a final SQL statement
  • the following simple user SQL statement shows two table-superordinated value fields, which are specified in each case at a decision position in the user SQL statement on which a data table name is also permitted:
  • RTN of SQL to which a table field name can be specified independently of the processing areas, are extended in such a way that instead of a
  • Table field name a table-superordinate value field is permitted at these decision positions, whereby the table-superordinate value field does not necessarily require more
  • User SQL statements in the resolution step are first converted into a transient SQL statement and subsequently into a final SQL statement, and
  • table-superordinate value field is inserted in the statement resolution step in a directly subordinate hierarchy level containing this table-superordinate value field.
  • the user SQL statement shows two table-superordinated value fields, which are used at decision positions in the SQL RTN, to which a table field name is also permitted.
  • This user SQL statement returns the company name, department name, and revenue per department, with results sorted by company sales.
  • the SQL RTN can also be extended by extending those decision positions in the RTN of SQL to which a table name can be specified independently of the processing areas such that these table names optionally have a connection character, preferably a period followed by a table, a table field, a table-superordinate value field, a comparison operation, or an aggregate function.
  • the following example shows a user SQL statement in which an aggregate function, a table name with a following aggregate function and a table name with a table-superordinate value field.
  • This user SQL statement is converted into this final SQL statement in the following statement analysis step and then in the completion step:
  • the SQL RTN can also be extended such that those decision positions in the RTN of SQL to which a table field name can be specified independently of the processing areas are extended in such a way that optionally a connection character, preferably A point followed by a table, a table field, a table-top value field, a comparison operation, or an aggregate function is allowed.
  • AVG Firmen.PLZ.AniereKontaktpersonen ()
  • AVG Companies.PLZ.Abricen.Count
  • the SQL RTN can also be extended by expanding those decision positions at which a table-superordinate value field can be specified independently of the processing areas in such a way that a connection character, preferably a point, is optionally added to these table-superordinate value fields.
  • a connection character preferably a point
  • the Supervisor table field in the Firms table will not be included as a text field but as a numeric field and contain a relation to a separate table, such as the Employees table ,
  • the text field Maintainer in the Company table is converted to the Number field MaintainerNo and another table Employee (employee), Last name, First name, ...) is created, which contains the following relation to the Company table:
  • this user SQL statement is converted into:
  • this automatically generated SQL statement will be augmented by the relational operations, as shown later in the application. This results in the following final SQL statement for the currently underlying data structure:
  • This statement given by a user, is automatically converted into the following statement via a table of relations after the statement resolution step and the completion of missing relationship-related operations, for example, as shown below:
  • ConnectionField companies NumberContact Persons () Count (Distinct Contacts.ContactID) End Define
  • ConnectionField Company_GetPLZsOfOrt (OfOrt) SELECT Location_PLZ.PLZ WHERE LocationJPLZ.loc IN (Param: OfOrt) End Define
  • ConnectionField Company_GetOrtOfPLZ (OfPLZ) SELECT Location_PLZ.Location WHERE Location_PLZ .PLZ IN (ParamOfPLZ) End Define
  • an additional table-superordinate value field Firmen.Ort can be created which calls the table-related value field GetOrtOfPLZ () at its single decision position of its inner, directed decision graph and the PLZ as parameter the current company hands over:
  • the inner, directed decision graph of the table superordinate value field FirmenMitParametern () can be changed as follows, without already existing SQL statements using this table-top value field in at least one of its processing areas need to change:
  • Equivalent can be the inner, directed decision graph of the table-superordinate value field Companies. Have the following single decision position in its single decision path:
  • table-superordinated value fields are superior to all tables present in a data structure. This means that all tables and all sets can access each table-superordinate value field from the set of table-superordinate value fields and use it in SQL statements.
  • ConnectionField Number of events time period, supervisor, ActEventStatus
  • Param Period THEN Events.
  • V ADatum Param: Period IF Param: Supervisor THEN Company Supervisor IN (Param: Supervisor) IF Param: Act EventStatus THEN
  • Last (auxiliary status code) Param: AktVerwegungStatus END IF
  • the table-superordinate value field returns in its result processing information which relates to the table Events.
  • the table-superordinate value field is inserted, for example, as a SUB-SELECT:
  • the following step in which it is determined that the relationship-related operations are incomplete in this automatically generated statement, completes this example by the relation-related operations according to the method shown below.
  • the final statement is not shown due to its immense length. Only the first analysis step is shown, in which the algorithm recognizes that the table Articles is used in the first hierarchy level and the tables Events, Employees in the second and third hierarchy levels (the two SUB-SELECTS Müller 2006 and Müller2005) and companies are present.
  • the relational connections between these tables are automatically inserted taking into account the respective hierarchy levels by means of the method shown below, whereby a final, optimizable and processable SQL statement that can be analyzed by a SQL engine is automatically made available.
  • This user SQL statement is converted into the following final SQL statement by means of the resolution step and the completion step:
  • Sub_Ort_PLZ.Location WHERE Sub_Firmen.PLZ Sub_Ort_PLZ.PLZ GROUP BY Sub_Ort_PLZ.Location HAVING Count (*)> 100)
  • This statement is automatically updated in a subsequent step around the missing relation-related operations automatically, for example by means of a table of relations and following the procedure shown below, resulting in this new, definitive SQL statement:
  • the algorithm which automatically inserts the relationship-related operations in a new, final SQL statement, taking into account the hierarchy levels and the table of relations, is given the information by the information "Sub_" & table name
  • ConnectionField sales (period, seller, net) IF Param: Room THEN
  • VADatum Param: Period END IF
  • statement resolution step is further applied, resulting in a final processable SQL statement according to the prior art.
  • This statement resolution step as well as the subsequent step, in which the relationship-related operations are inserted in a new statement, has already been shown many times and is therefore no longer executed here.
  • the following example shows the number of contact persons per location by abstracting the contact's data tables as a table-topped value field for the aggregated Company.Order table field.
  • This statement will be changed after applying the resolution step in subsequent SQL statement, which contains no relation-related operations, and extended in a further step to the missing relationship-related operations.
  • the resolution of the table-superordinate value field Companies. Place is not shown in this step to focus the example.
  • Event.VADatum Param: Minimum_Turn_Time END IF END IF
  • a decision position is reached in each of the five defined decision paths, which contains at least one table field that can be uniquely assigned to a table.
  • the processing command DISTINCT often follows the keyword COUNT in the examples shown. This processing command can always be inserted and the need for it can be determined by a subsequent SQL optimizer. Likewise, it can be automatically recognized that the processing command DISTINCT in combination with the processing command COUNT is only required if in a hierarchy level the processing function COUNT is to be applied to a table of the defined quantity, which table is not the last level in this hierarchy level.
  • This user-created statement is automatically converted as follows:
  • processing function Count in combination with the table companies does not refer to the last level of the defined quantity, ie the Cartesian product of the two tables companies and departments. It is also recognized that the
  • Processing function Count in combination with the table Departments refers to last level of the defined quantity. Accordingly, the keyword DISTINCT is only inserted in combination with COUNT companies.FirmalD.
  • Table superordinate value fields are also very suitable for use in a graphical user interface. If a table is selected, the totality of all existing table-superordinate value fields can also be displayed for selection. Likewise, those table-superordinate value fields whose names are made up of a table name, a dot, and a table-field-naming name can be displayed as table fields of a respective table.
  • Table superordinate value fields have the same meaning for the use of all relational data structures and SQL dialects, such as OLAP, Geo-SQL, T-SQL, etc. Similarly, the method according to the invention can be applied to all other other request languages such as e.g. OQL be used.
  • ad 1) specify processing functions and data tables and their columns to which the processing functions are to be applied and the order and hierarchy level with which the processing is to be performed.
  • This request contains only the tables without relations to which processing functions are to be applied. This provides independence from any data structure and at the same time results in a shortening of the spelling and thus an increase in clarity.
  • otherwise usual Cartesian product formations which are restricted by relations, away, because in the statement all information is present, which can be used in combination with the table of relations for the determination of an access path of the respective data structure, so given on the one hand independence of the concrete data structure and on the other hand the input of the request for the user is shortened and limited only to the essentials, which in turn increases the clarity.
  • the query results in a natural order of the data tables used as well as hierarchy levels, which, using the table of relations (Table 11), result in sub-access paths for the generation of an SQL query. Statements are determined for the respective data structure.
  • the above-mentioned order and the hierarchical levels of the data tables used are preferably represented in the form of an ordered tree 7 (FIG. 1) containing a root 10 and nodes 11, 12, 13 and 14, the node 14 being a subnode to the node 13 ,
  • the root 10 of the ordered tree 7 contains as information field 21 all names of the data tables only of the higher-level request, for example in the form of a list or an array, conveniently in the order given in the statement, optionally without duplicate data table names.
  • the information field 21 includes the data table COMPANIES (Table 1).
  • Subordinate queries (SUBSELECT) and their subordinate subqueries (SUBSELECT in SUBSELECT) 3 are subordinate to the root 10 and are entered as nodes 11, 12, 13, 14 in the tree structure.
  • the information fields 22, 23, 24, 25 of the nodes 11, 12, 13, 14 contain the sub-queries assigned names of the data tables that were specified in the respective SUBSELECT, also in the form of a list or an array.
  • node 13 For node 13, these are the data tables EVENTS (Table 5), DEPARTMENTS (Table 2) and EVENTS (Table 5) in information field 24.
  • the SUBSELECT of node 13 contains, according to the question, another SUBSELECT by node 14, which in its turn Information field the data tables INVOICE POSITIONS (Table 9) and
  • This tree 7 is traversed in a preorder pass: node 10, node 11, node 12, node 13 and node 14.
  • the access path determination which specifies an access order to the database concretely, by using the table of relations between each two consecutive selected data tables, a partial access path based on the is calculated between the successive data tables existing relations, and composed of all calculated partial access paths of the access path.
  • Example B Based on the access paths determined with the table of relations given above (Table 11), it is now possible to use relationship-oriented operations in the database statement specified in accordance with the invention (Example B), whereby the following SQL statement emerges (the operations used are in italics) which of each one SQL standard supporting database system can be processed. A possible form of calculation will be explained below.
  • the notation according to the invention and the generated notation can be adopted and a recalculation is omitted. This is only done when the data structure changes or at the same or the statement itself. As a result, the very short period of time for the conversion falls into place
  • the access path refers step by step to the data tables. These references are followed step by step to access the database.
  • a root of the ordered tree includes, for example: A, C, F, B. Sub-paths thus arise
  • Subpath AC A B C
  • Subpath CF C D E
  • Subpath FB F E D C B
  • Root (node) 101 with information field 201 which contains C 5 A 5 D node 102 with information field 202, which contains G 5 E node 103 with information field 203, which contains F, I, G node 104 with information field 204, which K 3 H 3 K 5 B contains node 105 with information field 205, which contains G 5 D.
  • the table of relations belonging to the selected data structure simplifies the representation by connecting alphabetically successive data tables in each case by a common relation field, thus A ⁇ -> B 5 B ⁇ -> C 5 C ⁇ -> D,
  • the ordered tree created for a particular query is traversed in a so-called preorder order by traversing all other nodes 102, 103, 104, 105 corresponding to the hierarchy levels, starting from a first node, the root 101 of the ordered tree 107.
  • FIG. 2 shows a tree representation of a request that is not specified in more detail.
  • the root 101 (top node) contains a list or array or the like in the information field 201. all of the data table names contained in the hierarchical level 1 of the higher-level request, wherein in the query not specified-as described above-only the data tables to which processing functions are applied are given.
  • the order of the given data tables is C, A, D.
  • a node 102, 103, 105 has been created for each subquery of the superordinate request, which contains as information field 202, 203, 205 the data table names used in this subquery as list, array or the like.
  • the order is for 102 eg G 5 E, for 103 eg F 5 I 5 G etc
  • the hierarchy level 1.2.1 represents the subquery to subquery 1.2 (node 103), which in turn contains as information field 204 the data table names K 5 H 5 K, B used therefor.
  • the list of data tables is e.g. In the information field 201, the data tables C, A, D are traversed, and for each two consecutive data tables in the list, the partial access paths are calculated, as already described above, multiple passes are eliminated to unnecessary product formations, which result in a false product , to avoid.
  • the next node in the preorder sequence is searched for. Between the first data table of the list of the information field of this next node and the last data table of the list of the information field of its direct predecessor, the sub-access path is determined, then the list of this next node is traversed to the last data table as described above. For example,
  • Node 104 Passing the List of Data Tables in Information Field 204: K, H, K, B
  • next node is node 105, but its direct predecessor is node 101, therefore the connection between G (node 105) and D (node 101) is searched for and the sub-access path computed. Thereafter, the passage of the data tables G, D in the information field 205 in the node 105 of the tree 107th
  • duplicate sub-paths are removed, discarding any duplicate or multiple data table names and relations for the particular node or any of its predecessors, up to and including the root.
  • the table of relations (Table 12) in e.g. read in a graph, wherein the edges of the graph also each contain the relations conditions to determine using the graph theory connections between two data tables on the relations.
  • C ⁇ A results as a path between C and A as a result of e.g. shortest route calculation following list:
  • a -> D is the path A, B 5 C, D
  • This one is at the end of the list of the current node, here the root, arrived.
  • the next step is to determine if there are any previous nodes. In the case of the root this is by definition not true.
  • Next node 103, predecessor node 101 first data table current node and last data table predecessor node

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

Procédé de commande d'un système de banque de données relationnelle, par exécution d'une interrogation de banque de données dans une banque de données relationnelle, procédé comprenant les étapes suivantes : établissement d'au moins un champ de valeurs subordonné à des tables, renfermant un graphe de décision orienté interne, lors d'une étape de préréglage; établissement d'une instruction SQL utilisateur, lors d'une étape de formulation, dans laquelle au moins l'un des champs de valeurs subordonnés aux tables est utilisé; exécution d'une étape de résolution d'instruction automatique subséquente, au cours de laquelle une nouvelle instruction SQL transition est établie en recherchant et en traitant les champs de valeurs qui sont subordonnés aux tables et utilisés dans l'instruction SQL client, éventuellement en tenant compte de paramètres supplémentaires, et le résultat de ces traitements est utilisé dans l'instruction SQL initiale, à la place du champ de valeurs subordonné aux tables; complétion automatique de l'instruction SQL transition, par addition, lors d'une étape de complétion, éventuellement d'opérations erronées et/ou incomplètes, de manière à fournir une instruction SQL finale, pouvant être analysée, optimisée et traitée par tout moteur gérant le standard SQL.
PCT/AT2007/000195 2007-04-27 2007-04-27 Procédé de commande d'un système de banque de données relationnelle WO2008131465A1 (fr)

Priority Applications (1)

Application Number Priority Date Filing Date Title
PCT/AT2007/000195 WO2008131465A1 (fr) 2007-04-27 2007-04-27 Procédé de commande d'un système de banque de données relationnelle

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
PCT/AT2007/000195 WO2008131465A1 (fr) 2007-04-27 2007-04-27 Procédé de commande d'un système de banque de données relationnelle

Publications (1)

Publication Number Publication Date
WO2008131465A1 true WO2008131465A1 (fr) 2008-11-06

Family

ID=38121941

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/AT2007/000195 WO2008131465A1 (fr) 2007-04-27 2007-04-27 Procédé de commande d'un système de banque de données relationnelle

Country Status (1)

Country Link
WO (1) WO2008131465A1 (fr)

Cited By (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN110222128A (zh) * 2019-06-12 2019-09-10 浪潮软件集团有限公司 一种生成数据预置sql的方法及装置
CN114397967A (zh) * 2022-01-07 2022-04-26 山东浪潮科学研究院有限公司 一种数据库的辅助输入关键字自动联想方法
CN114397966A (zh) * 2022-01-06 2022-04-26 山东浪潮科学研究院有限公司 一种数据库的关键字纠错提示方法及装置
CN114548794A (zh) * 2022-02-25 2022-05-27 首约科技(北京)有限公司 一种基于动调的提高表处理速度的方法

Citations (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO1995006292A1 (fr) * 1993-08-25 1995-03-02 Asymetrix Corporation Procede et appareil pour la modelisation et l'interrogation de structures de base de donnees a l'aide de constructions semblables au langage naturel
US5987455A (en) * 1997-06-30 1999-11-16 International Business Machines Corporation Intelligent compilation of procedural functions for query processing systems
WO2000063798A1 (fr) * 1999-04-16 2000-10-26 Targit A/S Procede et appareil de traitement des demandes vers une base de donnees
US20040073539A1 (en) * 2002-10-10 2004-04-15 International Business Machines Corporation Query abstraction high level parameters for reuse and trend analysis
EP1585036A2 (fr) * 2004-04-08 2005-10-12 International Business Machines Corporation Gestion de requêtes paramètriquées à une base de données

Patent Citations (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO1995006292A1 (fr) * 1993-08-25 1995-03-02 Asymetrix Corporation Procede et appareil pour la modelisation et l'interrogation de structures de base de donnees a l'aide de constructions semblables au langage naturel
US5987455A (en) * 1997-06-30 1999-11-16 International Business Machines Corporation Intelligent compilation of procedural functions for query processing systems
WO2000063798A1 (fr) * 1999-04-16 2000-10-26 Targit A/S Procede et appareil de traitement des demandes vers une base de donnees
US20040073539A1 (en) * 2002-10-10 2004-04-15 International Business Machines Corporation Query abstraction high level parameters for reuse and trend analysis
EP1585036A2 (fr) * 2004-04-08 2005-10-12 International Business Machines Corporation Gestion de requêtes paramètriquées à une base de données

Non-Patent Citations (4)

* Cited by examiner, † Cited by third party
Title
JARKE M ED - KERSCHBERG L UNIVERSITY OF SOUTH CAROLINA: "EXTERNAL SEMANTIC QUERY SIMPLIFICATION: A GRAPH-THEORETIC APPROACH AND ITS IMPLEMENTATION IN PROLOG", EXPERT DATABASE SYSTEMS. KIAWAH ISLAND, SOUTH CAROLINA, OCT. 24 - 27, 1984, PROCEEDINGS OF THE INTERNATIONAL WORKSHOP ON EXPERT DATABASE SYSTEMS, MENLO PARK, CA., BENJAMIN/CUMMINGS, US, vol. WORKSHOP 1, October 1984 (1984-10-01), pages 675 - 692, XP000745618 *
MITSCHANG B ET AL: "SQL/XNF-Processing composite objects as abstractions over relational data", PROCEEDINGS OF THE INTERNATIONAL CONFERENCE ON DATA ENGINEERING. VIENNA, APR. 19 - 23, 1993, LOS ALAMITOS, IEEE COMP. SOC. PRESS, US, vol. CONF. 9, 19 April 1993 (1993-04-19), pages 272 - 282, XP010095515, ISBN: 0-8186-3570-3 *
STONEBRAKER M ET AL: "EXTENDING A DATABASE SYSTEM WITH PROCEDURES", ACM TRANSACTIONS ON DATABASE SYSTEMS, ACM, NEW YORK, NY, US, vol. 12, no. 3, September 1987 (1987-09-01), pages 350 - 376, XP000718766, ISSN: 0362-5915 *
YANNAKOUDAKIS E J ET AL: "A DOMAIN-ORIENTED APPROACH TO IMPROVE THE USER-FRIENDLINESS OF SQL", COMPUTER STANDARDS AND INTERFACES, ELSEVIER SEQUOIA. LAUSANNE, CH, vol. 9, no. 2, January 1989 (1989-01-01), pages 127 - 141, XP000083601, ISSN: 0920-5489 *

Cited By (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN110222128A (zh) * 2019-06-12 2019-09-10 浪潮软件集团有限公司 一种生成数据预置sql的方法及装置
CN110222128B (zh) * 2019-06-12 2022-10-14 浪潮软件集团有限公司 一种生成数据预置sql的方法及装置
CN114397966A (zh) * 2022-01-06 2022-04-26 山东浪潮科学研究院有限公司 一种数据库的关键字纠错提示方法及装置
CN114397967A (zh) * 2022-01-07 2022-04-26 山东浪潮科学研究院有限公司 一种数据库的辅助输入关键字自动联想方法
CN114548794A (zh) * 2022-02-25 2022-05-27 首约科技(北京)有限公司 一种基于动调的提高表处理速度的方法

Similar Documents

Publication Publication Date Title
DE69602364T2 (de) Rechnersystem um semantische objektmodelle von existierenden relationellen datenbanksystemen herzustellen
DE3855706T2 (de) Automatisierte Rechnung von Materialien
DE69424586T2 (de) Verfahren und System zum formulieren interaktiver Abfragen
DE69418474T2 (de) Semantisches objektmodellierungssystem und verfahren um relationelle datenbankschemata herzustellen
DE10028688B4 (de) Methode, System und Programm für eine Verbindungsoperation in einer mehrspaltigen Tabelle sowie in Satellitentabellen mit doppelten Werten
DE69509118T2 (de) Implementierungsunabhängige erweiterbare abfragearchitektur für systeme zur informationswiederauffindung
DE60002876T2 (de) Darstellung, verwaltung und synthese von technischen inhalten
DE69408337T2 (de) Steuersystem einer relationellen datenbank gemäss einer objekt-orientierten logik, die die zugriffe auf die datenbank verringert, und entsprechendes verfahren
DE60004385T2 (de) Verfahren und systeme um olap hierarchien zusammenfassbar zu machen
DE60208778T2 (de) Datenstruktur für informationssysteme
DE69904588T2 (de) Datenbankzugangswerkzeug
DE3688529T2 (de) Verfahren zur Auffrischung von Mehrspaltentabellen in einer relationellen Datenbank mit Mindestinformation.
EP0910829B1 (fr) Systeme de banque de donnees
DE69232542T2 (de) Definitionsänderungssprache für ein Datenbankrechnersystem
DE68929132T2 (de) Datenbankverwaltungssystem und Verfahren hierfür
DE69514123T2 (de) Datenbanksuchsystem
WO1997015015A2 (fr) Systeme d'informations et procede de memorisation de donnees dans un systeme d'informations
DE10103574A1 (de) Aggregierte Prädikate und Suche in einem Datenbankverwaltungssystem
DE60300984T2 (de) Methode und Computersystem für die Optimierung eines Boolschen Ausdrucks für Anfragebearbeitung
DE10056763B4 (de) Generieren von Einschränkungsabfragen mit Hilfe von Tensordarstellungen
EP2021951B1 (fr) Procede de commande d'un systeme de gestion de banque de donnees relationnelle
DE102004043788A1 (de) Programm Generator
DE102012100113A1 (de) Verfahren, Software und Computersystem zur Handhabung von angesammelten Daten
WO2008131465A1 (fr) Procédé de commande d'un système de banque de données relationnelle
DE69517887T2 (de) Verfahren und System zum Herstellen von Verbindungen in einem Datenbanksystem

Legal Events

Date Code Title Description
121 Ep: the epo has been informed by wipo that ep was designated in this application

Ref document number: 07718411

Country of ref document: EP

Kind code of ref document: A1

DPE2 Request for preliminary examination filed before expiration of 19th month from priority date (pct application filed from 20040101)
NENP Non-entry into the national phase

Ref country code: DE

122 Ep: pct application non-entry in european phase

Ref document number: 07718411

Country of ref document: EP

Kind code of ref document: A1