Re: Schemas and all

[Rodrigo Moya Piernavieja <rmoya tsai es>]

Vivien Malerba wrote:

> 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?
>

SQL is the SQL command needed for creating the table (or object)

>
> ---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.

No, if not set, this is invalid, you must ALWAYS supply a table (or view) name. Or not?

>
> * 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).
>

precision is the number of digits in the decimal part (for numeric columns only)

I would remove the EXTRA_INFO from this one, so that this schema is just a 'DESC tabename' command. Specially since there are no "big" queries to do (that is, as with the
tables' SQL column, which might be difficult to retrieve for some servers).

>
> ---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.
>

I agree with this one. Stephan?

>
> ---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)
>

I agree also

>
> ---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).
>

Agree as well

>
> 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,...
>

We should maybe add all this "parameters" as schemas as well, so that for example, we can have a schema GDA_Connection_SCHEMA_SUPPORTED which will return all the supported
schemas for a provider. What do you think?

>
> * 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.
>

Yes, that's one of the missing schemas. Oracle has this as well.

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

I can't think of a way to do this. Only the SQL Builder (on which Bruno Mairlot is going to hack) could provide a way of doing this.

>
> 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,...).
>

Or maybe extend the set of GDA_Type's?

>
> * 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.

It's a good idea, specially since we must integrate gnome-db in the gnome-office, so this could be a way to allow retrieval of data from unrelated apps (as gnumeric) without
having to "really" connect to the underlying database. But this needs a very deep thought I think.

Cheers