Schemas and all

[Vivien Malerba <malerba linuxbox com>]

Hi everyone!

Here are my thoughts so far (troubled by the flu I had this week end) on the
SCHEMA stuff:

---GENERAL CONSTRAINTS:
* OBJECT_CATALOG: if not set, the information is for the current DB (the one to which the connection is
made), and if set, the information is for the specified DB.
* OBJECT_SCHEMA:  if not set, no effect, and if set, will show only tables which belong to the specified
user.

---SCHEMA_TABLES:
* OBJECT_NAME: if not set, all the tables will be shown, but if set, only the mentionned table will appear.
* EXTRA_INFO: if not set, shows (name, comments), and if set shows (name, owner, comments,
parent table, SQL) for the table(s). FIXME: what is the SQL info?

---SCHEMA_VIEWS:
* OBJECT_NAME: same as for tables but for views
* EXTRA_INFO: same as for tables.

---SCHEMA_COLUMNS:
* OBJECT_NAME: specify the table or view's name. if not set, columns for ALL the tables and view will
be shown.
* COLUMN_NAME: specify which column, and if not specified, all the columns will be shown.
* EXTRA_INFO: if not set, shows (name, SQL server type, size (FIXME: which unit?), precision
(FIXME: what is it?), nullable (TRUE if can be null)) and if set (name, SQL server type, size, precision,
nullable, is it a key, default value, comments).

---SCHEMA_PROCS:
to show procedures and functions.
* OBJECT_NAME: specify the function's name. if not set, shows all the functions.* EXTRA_INFO: if not set, shows (name, comments) and if set shows (name, owner, comments, SQL
server return type, number of parameters, SQL server type for the parameters, SQL definition), the SQL
definition may or may not be accessible.

---PROV_TYPES:
* OBJECT_NAME: specify the type's name. if not set, shows all the data types.
* EXTRA_INFO: if not set, shows (name, comments) and if set (name, comments, owner, GDA type
if available)

---SCHEMA_CATALOGS:
to shows all the avalaible databases
* OBJECT_NAME: specify the DB's name. if not set, shows all the availables DBs.
* EXTRA_INFO: if not set, shows (name, owner) and if set (name, owner, comments).


Some things to introduce:
* a way to test for functionnalities. For example, under Postgres I do not know how to get information for
other DB than the one to which the connection is opened (and thus the OBJECT_CATALOG is useless for
the postgres provider). There should be a way for the user of knowing this. For
example a function which would return TRUE or FALSE if a particular
functionality is in the DBMS.  Other examples: can a table inherit another one,
can a user create his own data types,... 

* another SCHEMA: for the sequences. Postgres has a kind of object which acts
as a counter and with it you can be sure to have only ONE value at a time. It
surely exists under other DBMS.

* a way for the user to perform some specific tasks such as setting a default
value, setting some comments for any object,...


Some open questions:
* how could we cope with specific data types? Postgres is quite full of data
types (points, circles, IP adresses, arrays of all sorts,...) and any user can
define some new ones. One solution is to not care from gnome-db, but then
gnome-db must give all the clues as to how treat those data types (e.g. how to
create one, delete one, use one,...).

* has anyone thought about storing some information on the DB into the server. 
Let me explain: why for example not allow one user for one given DSN, to store
some SELECT queries. The advantage is that this user could then "execute" those
queries from another program (for example a gnumeric script to have the results
of the query into a sheet). This is just an idea (and I know there are a lot of
things to be done properly before), and give me your thoughs ont it. 


That's quite a lot of text! 
Thanks for giving me your thoughts on it.

Cheers!
Vivien