Introduction and overview

What is gASQL?

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:

General functionning scheme

Basically, here is how things are organized:

Here is a diagram to summarize it all:

Diagram to summarize what has just been said

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:

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:

Diagram to summarize the relations of the example database