gASQL manual | ||
---|---|---|
<<< Previous | Next >>> |
gASQL is a program which enables the user to work with data stored in a database (using an external database engine) in a convenient way. For this he needs to provide some information that gASQL is unable to obtain from the database (such as the relations between several table's fields). Once such data is provided, the user can design queries in a graphical way, and use these queries to find some data in the database, modify it, insert some new, etc.
In the future, gASQL will allow the user to design widgets which will then be used from within gASQL, and in external applications (provided by gASQL at the runtime of the external applications), sing the Bonobo technology.
Data manipulated in gASQL
gASQL manipulates two kinds of data: data provided by the DBMS and data provided by the user. The association of these two kinds of data enables gASQL to perform some powerfull work. The data provided by the user completes the description of the database given by the DBMS (mainly more details about the structure).
When user defined data is given in place of DBMS data, it will override it (it will be given a higher priority).
Automatically known database informations
As of version 0.6.x, gASQL automatically knows about the following elements in a database (depending on the DBMS provider's degree of completeness and the DBMS' own features, see chapter ???):
users: all the declared users in a given database will be recognized.
tables and views: all the tables and views will appear to the user, with their respective owner, and the structure of each table or view (individual fields and their attributes, and primary keys).
sequences: will also appear (with their owner).
data types: all the data types that can be used in a database will appear. They fall in two cathegories: the "system" ones and the user-defined ones (if the DBMS supports that feature). For some data of a given datatype, the way the user is shown the data, and prompted to modify it will depend on plugins. Except for the most common data types such as date, char, etc. (which in any case can still be overrided), if there is no plugin, then the default methods will be used (data type treated as a string). See chapter ???
functions: all the functions in the database will appear, wether they are functions or aggregates, and "system" or user-defined (if the DBMS supports it). The number and data types of each of the arguments are checked when using those functions.
links as foreign keys: this is planned but not yet implemented.
User provided information
The user is allowed to provide informations about:
links between fields of two tables (see chapter ???). The term relation will also be used in this manual to refer to a link.
comments on tables, or fields of tables: they can sometimes not be stored in the DBMS (either because there is no writing access or because the DBMS does not support it), so gASQL allows the user to give comments on tables, fields, etc.
plugins usage: gASQL can be extended using plugins; the way plugins are used is chosen by the user (see chapter ???)
the connection parameters: except the passwords, they will be saved.
all the information related to queries and the associated forms.
This information is the one which is stored in files for each connection to avoid entering it all each time a connection is reopened.
Making queries
Queries are traditionnaly divided in the categories: DDL (Data Definition Language) ones and DML (Data Manipulation Language) ones.
Data definition queries
gASQL does not yet offer any way to create databases, tables, or any other objects in a database. This will be implemented in a future version.
Data manipulation queries
With gASQL, the only queries the user has to design are selection queries (SELECT statements using the SQL language), so the queries referenced in gASQL are all about selection. Modification, insertion or deletion queries (other DML queries) are then made using the corresponding selection query.
The benefits are when the user wants to update results (or recordsets) of queries where there are some joins, where statements, etc: gASQL will provide the user with a nice interface to help make some modifications to a defined table (in case the selection query is from several tables) where joins are presented as multiple choice lists.
As the user interface to build selection queries is limited (as *any* interface would be), there is still a possibility to manually enter queries which are sent to the DBMS (without checking anything!). However even if a hand made query is a selection query, it cannot be used to make modification queries (hopefully it will be in the future!).
Document files
All the information used by gASQL is stored in a file (as XML). Here is a brief description of how to use those files:
When gASQL is run with no argument, it is needed to choose from the connection preferences the connection to be used, and to give a username and a password (or an empty password if none is required). The user can then open the connection to the DBMS for the specified database. gASQL will automatically load from the DBMS all the information it can get (this may take some time!). From now the user can save his working environment by giving it a name.
When the user saves his environment, all the following is saved: the connection parameters (except for the password), any user defined information given to gASQL, and the information given by the DBMS (to avoid having to load them back when gASQL opens a connection to the same DBMS and database).
After having saved his working environment, the user can disconnect from the database, and quit gASQL. When gASQL is then run and the working file is opened (from the File/Open menu or by giving the file name on the command line), all the stored information is loaded back and the user is prompted with a question about refreshing the database structure from the DBMS; answering yes will then update all the information gASQL can get from the DBMS (that same operation can be performed whenever necessary by the user, for example when another program has modified the database structure).
The contents of the XML files can be modified with a simple text editor (to change the user name for example), but it must be done with care since that file can then quite easily be broken (in that case gASQL will complain). gASQL uses a Document Type Definition to ensure the XML file is correct.
General functionning scheme
Basically, here is how things are organized:
gASQL uses libgda as a unique mean of accessing a database (at no time has gASQL knowledge of what the actual DBMS is), and gnome-db for some of its GUI.
Libgda is composed of a framework, and of several 'providers' which each enable the access to one DBMS. These providers are in different state of usability (consult the gnome-db/libgda site for more informations). There exist a provider for all the most known DBMS (Oracle, Sybase, PostgreSQL, MySQL, etc). To have gASQL working correctly, a version of libgda and gnome-db which is correctly installed and configured is required.
Also, using Bonobo the user will be able to do some tasks specific to the DBMS (managing users and their rights, managing databases, etc), provided that libgda supports it for the DBMS (this is broken at the moment).
Here is a diagram to summarize it all:
A practical example
To help understand gASQL usage, a simple example will be used throughout this documentation. This example comes from the 'examples' directory of the sources distribution of gASQL.
It is the database used for managing the sales of a commercial company: several salesmen have each in charge one or more customers who can make orders which are composed of products stored in several warehouses.
The tables that have been created are:
'salesrep': a table which lists all the sales representatives of the company. For each of them there is an id (a number, key for the table), a name, a yearly salary, and the date of employment. The 'salesrep_id_seq' sequence ensures that each sales man has a unique id.
'customers': this table lists all the customers, with a unique id (using the 'customers_id_seq' sequence), a name, the city the customer comes from, and the id of the sales man who is in charge of the customer.
'orders': a table listing all the orders from all the customers. Each order has a unique id (from the 'orders_id_seq' sequence), the id of the customer who made the order, the date the order was made, the date before which the order must be ready for delivery, and the actual date of delivery.
'order_contents': as its name implies, this table contains all the products that are in an order; more specifically each entry in the table references the order id, the reference of a product, the quantity of that product in the order, and a discount percentage that has been allowed.
'products': a table listing all the products that the company sells. Each product is identified by a a reference, a name, a price, and the id of the warehouse where the product can be found (to simplify things, if a product can be in several warehouses, it is supposed to appear as several different references).
'warehouses': the list of the warehouses the company has. For each warehouse there is a unique id (from the 'warehouses_id_seq' sequence), the name and location of the warehouse.
To help understand the database structure, the figure below is a screenshot of the relations for all the tables'fields and sequences defined in the database:
<<< Previous | Home | Next >>> |
Foreword | Main interface of gASQL |