gASQL manual | ||
---|---|---|
<<< Previous | Next >>> |
The purpose of having a database is to make queries to manage the data inside the database. The queries can be of two basic different types: Data Definition Language (DDL) to manage the structure of the database, and Data Manipulation Language (DML) to actually manage the data within the database. This chapter deals with DML queries only.
Among DML queries are the ones which will interrogate the database (SELECT queries), and the ones to modify the data (INSERT, UPDATE and DELETE queries). gASQL allows the user to graphically create SELECT queries, and then automatically converts theses SELECT queries into INSERT, UPDATE or DELETE queries when needed.
So, the basic task is to make SELECT queries. The list of such queries is present in the 'Queries' tab of the main interface. Creation of a new query and modification or deletion of an existing query is done through that interface. There is then a dialog to edit the contents of a query. The usage of this dialog is discussed below.
The query properties dialog is composed of a top part with the query name and its description, a middle part where some tables and relations can be displayed (behaves the same way as the global dialog for the relations), and a lower part where most of the properties are edited. The Lower part is a notebook with several tabs, which are described in the following sections. Below is a screenshot of a query properties dialog.
This tab maintains a list of all the 'objects' within the query. An 'object' here can represent several things: a table's field, a constant values, a function applied to another object, or an aggregate applied to another object. Objects can be given names, and a print name if they will appear in the list of 'SELECTED' items.
The objects reflect what the SELECT query will return. Here are some examples of queries, the list of objects would be required, and some rules to help understand objects in queries:
"SELECT id, name FROM persons ...": the objects would simply be the fields 'persons.name' and 'persons.id' (with print names of "id" and "name" respectively); for every field there needs to be a corresponding object.
"SELECT id, name, tochar(num), now() FROM persons, phones ...": the objects would still be the fields 'persons.name' and 'persons.id', along with the 'phones.num' field and the 'tochar(phones.num)' function (function 'tochar' applied to the field 'phones.num') and 'now()' function; for every function, there needs to be an object (which is related to the object representing the argument(s) of the function if any). Also important to note is that the 'phones.num' object does not have a print name (it is null) as it is not among the list of items after the 'SELECT' statement.
"SELECT max(age) as oldest, 'Max' AS MAX FROM persons ...": the objects here would be 'person.age' (with a null print name), 'max(person.age)' where max is an aggregate (with a print name of "oldest"), and the object constant 'Max' (with a print name of "MAX");
"SELECT min(age) as youngest, max(age) as oldest FROM persons ...": the objects here would be 'person.age' (with a null print name), and the 'min(person.age)' and 'max(person.age)' aggregate objects; an object can be used more than once by other objects, no need to duplicate objects if they appear more than once.
The objects tab allows for the creation, edition (only name and print name), and removal of objects, and to manage the order in which the objects will appear in the result of the query if their print name set. If an object is deleted, all the objects which depend on it will also be deleted (recursively), and the other tabs of the query properties dialog will also be updated.
It is possible, at any time to add, edit and remove objects.
In this tab, the user will select which of the declared relations he wants to use as joins between tables in the query being edited (the different types of joins such as 'inner', 'outer left' and 'outer right' are not yet supported, and for now all are considered as inner joins).
The list displays some relations, and wether they will be used, or ignored in the query. The relations being displayed are the ones which link two tables which are displayed in the middle vertical part of the dialog. So to make some relations appear od disappear, is as simple as adding or removing tables to the middle part of the dialog.
A relation which is checked as not being used is the same as not displaying the relation at all in the tab, so it is not necessary to display all the relations and check them off if they are not to be used.
This tab allows to manage conditions on the what will be selected. Note that the relations which are taken into account (see previous section) are already listed in this tab (and cannot be edited from there).
In the main part of the tab is a list with all the conditions listed, organized in a tree structure where the nodes are logical operators (AND, OR and NOT) and the leaves the conditions themselves (for example on objects defined in the 'Objects tab').
Conditions are added and removed with the corresponding buttons, and the logical structure is established by moving the conditions UP and DOWN, RIGHT (to create a new level of logic, and edit the the default AND node created later), and LEFT to move the condition a level up in the structure. If several conditions are on the same level, then the default logical operator linking the conditions is an AND.
This tab is not yet finished, but will hold the informations to sort the results, and to make some regrouping (GROUP BY clause in SQL language).
This tab allows the user to enter direct SQL queries. For now if SQL text is entered, then gASQL won't be able to make other DML queries from the SELECT one (it will only be possible to view the result of the selection). This will probably change in the future.
If the query is designed using the tools mentionned so far, the SQL tab will display the SQL query as it is at any given time (any modification will update this display), with some little differences regarding values which are asked when the query is run.
If the query is an SQL only one, then none of the other tabs will be usable.
With the above explanations, the user should be able to make most of the queries he wants. There are however some few cases not yet covered by the query properties dialog which are, in no particular order: sorting of the resultset (ORDER BY clauses), groupings in the resultset (GROUP BY clauses), queries composed of other queries (UNION, INTERSECT, etc queries), nested queries, and operators directely in the SELECT statement (such as in "SELECT a+b from table") plus several small other cases. It will probably be possible in the future to cover some or all of the aforementionned cases.
<<< Previous | Home | Next >>> |
Tables relations | Other kind of DML queries: INSERT, UPDATE and DELETE |