SQL GENERATION

• US 20110161371A1
• Filed: 12/29/2009
• Published: 06/30/2011
• Est. Priority Date: 12/29/2009
• Status: Abandoned Application

• Current Assignee: Aptitude Software Ltd. (Aptitude Software Group Plc)
• Original Assignee: Aptitude Software Group Plc
• Inventors: Thomson, Neil, Pusz, Grzegorz Roman
• Application Number:
  US12/648,660
• Publication Number:
  US 20110161371A1
• Time in Patent Office: Days
• US Class Current: 707/792
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence

Abstract

A method of creating SQL instructions defining a process to be performed on a relational database, the method using a computing device having at least a processor, a memory, a display device and an input device, and the method including:

• • retrieving from the memory and providing on the display device a number of operation icons each representing at least one operation to be performed on data in a relational database;
  • enabling a user of the computing device to select and arrange at least some of the operation icons on the display device using the input device;
  • enabling the user to arrange graphical links between the selected operation icons to define the order of execution of the operations represented by the selected operation icons, wherein the selected operation icons and the data icons and the graphical links together from a graphical representation of the process;
  • generating, at the processor, SQL instructions for executing the operations represented by the selected operation icons, in accordance with the graphical representation of the process.

Claims

1. A method of creating SQL instructions defining a process to be performed on a relational database, the method using a computing device having at least a processor, a memory, a display device and an input device, and the method comprising:

retrieving from said memory and providing on said display device a number of operation icons each representing at least one operation to be performed on data in a relational database;

enabling a user of said computing device to select and arrange at least some of said operation icons on said display device using said input device;

enabling said user to arrange graphical links between the selected operation icons to define the order of execution of the operations represented by the selected operation icons, wherein said selected operation icons and said data icons and said graphical links together from a graphical representation of said process; and

generating, at said processor, SQL instructions for executing the operations represented by said selected operation icons, in accordance with said graphical representation of said process.

2. A method as claimed in claim 1, which further comprises data icons representing data in a relational database.

3. A method as claimed in claim 1, which further comprises allowing said user to select from a number of database types, and wherein said generating step includes generating SQL instructions in a format compatible with the selected database type.

4. A method as claimed in claim 3, wherein said database types include any or all of the following:

MS SQL Server, Teradata, Sybase and Oracle.

5. A method as claimed in claim 3, wherein the user can define a graphical region containing icons corresponding to a plurality of SQL instructions specific to a database type within said region.

6. A method as claimed in claim 1, wherein said operation icons include a throw error icon representing SQL instructions for defining certain operations or certain data as erroneous, detecting occurrence of those operations or data and returning a message as a result of said detection.

7. A method as claimed in claim 5, wherein said operation icons include a catch error icon representing SQL instructions for receiving said message returned by the throw error icon.

8. A method as claimed in claim 1, wherein the relational database contains at least one table, and wherein a trigger is provided to initiate an operation in response to a change to said table.

9. A method as claimed in claim 1, wherein said operation icons include a begin icon to initiate an operation and to define the parameters, variables and errors required for the operation.

10. A method as claimed in claim 1, wherein said operation icons include an end icon to terminate an operation, which operation is graphically linked to the end icon.

11. A method as claimed in claim 1, wherein said operation icons include a loop icon arranged to define a loop area containing one or more icons, and to set a condition for the repeated execution of operations corresponding to icons contained inside the loop icon.

12. A method as claimed in claim 11, wherein said loop icon includes a functionality to reduce the graphical representation of said loop area in size.

13. A method as claimed in claim 11, wherein said operation icons include a break icon to terminate the repeated execution of operations inside said loop area.

14. A method as claimed in claim 13, wherein said operation icons include a continue icon to continue the repeated execution of operations inside said loop area.

15. A method as claimed in claim 1, wherein said operation icons include a begin transaction icon to initiate an operation inside a user defined region of said graphical representation of a process and to store the data on which said operation is performed in memory.

16. A method as claimed in claim 1, wherein said operation icons include a commit transaction icon representing instructions to commit the results of a transaction to memory after the transaction is successfully completed.

17. A method as claimed in claim 1, wherein said operation icons include a rollback transaction icon to undo the results of an operation after a transaction has failed.

18. A method as claimed in claim 1, wherein said operation icons include a select icon to select data from said relational database.

19. A method as claimed in claim 1, wherein said operation icons include an insert icon to insert data from said relational database.

20. A method as claimed in claim 1, wherein said operation icons include an update icon to update said relational database in said memory

21. A method as claimed in claim 1, wherein said operation icons include a delete statement to delete data from said relational database.

22. A method as claimed in claim 1, wherein said operation icons include a merge icon to merge data from said relational database

23. A method as claimed in claim 1, wherein said operation icons include a procedure icon to execute SQL instructions stored in memory.

24. A method as claimed in claim 1, wherein said operation icons include an assignment icon to enable a user to define operations on said relational database.

25. A method as claimed in claim 1, wherein said operation icons include an SQL code icon to enable a user to define SQL code.

26. A method as claimed in claim 1, wherein said operation icons include a decision block to evaluate a Boolean input expression and directing the control flow according to the value of the expression.

27. A method as claimed in claim 1, wherein said operation icons include a case icon to evaluate a list of conditions and directing the control flow according to the outcome of the evaluation.

28. A method as claimed in claim 1, wherein said operation icons include a region icon to graphically enclose one or more other operation icons to execute said enclosed operation icons collectively.

29. A method as claimed in claim 1, wherein said operation icons include a database specific icon to graphically enclose one or more other operation icons to execute said enclosed operation icons collectively depending on the sort of database.

30. A method as claimed in claim 1, wherein said data icons include one of a table and view to represent a set of data from a relational database.

31. A method as claimed in claim 1, wherein said data icons include an input parameter icon to represent an input parameter to said process.

32. A method as claimed in claim 1, wherein said operation icons include a join icon to represent an operation of joining two sets of data according to a logical relation between said two sets.

33. A method as claimed in claim 32, wherein the logical relation includes taking all elements from the one of said two sets that are not part of the other of said two sets.

34. A method as claimed in claim 1, wherein said operation icons include a union icon to represent an operation on two sets of data corresponding to producing a new set of data of all distinct elements in said two sets or producing a new set of data that contains all elements of the first of two sets of data that also belong to the second of two sets of data or producing a new set of data that contains all elements of said two sets except a number of user specified elements.

35. A method as claimed in claim 1, wherein said operation icons include a filter icon to represent an operation of selecting a subset from a set of data.

36. A method as claimed in claim 1, wherein said operation icons include a grouping icon to represent an operation of grouping data according to attributes of said data.

37. A method as claimed in claim 1, wherein said operation icons include a sorting icon to represent an operation of ordering data according to attributes of said data.

38. A method as claimed in claim 1, wherein said operation icons include an expression icon to enable said user to define one of a number of expressions for processing of said data,

wherein said expressions in said expression icon may be defined by the user placing one or more functional icons inside the expression icon.

39. A method as claimed in claim 38, wherein said functional icons include any of an input icon representing the definition of the input attribute of the data, an output icon representing the definition of the output attribute of the data generated by the expression, an and icon representing the conditional and operation on the data, an or icon representing the conditional or operation on the data, a case icon representing conditional operations on the data, and an aggregation icon representing a number of analytical functions

40. A method as claimed in claim 1, wherein said operation icons include a match icon to represent an operation of matching tables of data according to user defined matching conditions.

41. A method as claimed in claim 1, wherein said operation icons include a sub-query icon to represent an operation of a sub-query to a database.

42. A programmed computer comprising at least a processor, a memory, a display device and an input device, wherein said computer is programmed to perform a method of creating SQL instructions defining a process to be performed on a relational database, the method comprising:

retrieving from said memory and providing on said display device a number of operation icons each representing at least one operation to be performed on data in a relational database;

enabling a user of said computing device to select and arrange at least some of said operation icons on said display device using said input device;

enabling said user to arrange graphical links between the selected operation icons to define the order of execution of the operations represented by the selected operation icons, wherein said selected operation icons and said data icons and said graphical links together from a graphical representation of said process; and

generating, at said processor, SQL instructions for executing the operations represented by said selected operation icons, in accordance with said graphical representation of said process.

43. A computer-readable medium containing computer-readable instructions for performing a method of creating SQL instructions defining a process to be performed on a relational database, the method using a computing device having at least a processor, a memory, a display device and an input device, and the method comprising:

retrieving from said memory and providing on said display device a number of operation icons each representing at least one operation to be performed on data in a relational database;

enabling a user of said computing device to select and arrange at least some of said operation icons on said display device using said input device;

enabling said user to arrange graphical links between the selected operation icons to define the order of execution of the operations represented by the selected operation icons, wherein said selected operation icons and said data icons and said graphical links together from a graphical representation of said process; and

generating, at said processor, SQL instructions for executing the operations represented by said selected operation icons, in accordance with said graphical representation of said process.