Re: [gnome-db] Data Types translations.

[Bas Driessen <bas driessen xobas com>]

On Wed, 2006-08-30 at 14:16 +0200, Vivien Malerba wrote:
> On 8/30/06, Bas Driessen <bas driessen xobas com> wrote:
> >
> >  On Wed, 2006-08-30 at 20:44 +1000, Bas Driessen wrote:
> >
> >  Hi Vivien,
> >
> >  Still struggling with the gda_type to provider data type issue....
> >
> >  There is this new function:
> >
> >  const gchar* gda_server_provider_get_default_dbms_type
> > (GdaServerProvider *provider,
> >  GdaConnection *cnc,
> >                                              GType
> > gda_type);
> >
> >
> >  Just looked into it, but for mysql and postgresql it is useless, since it
> > always returns NULL. For the mysql provider there is a file called
> > "utils.c". In there is a function called gda_mysql_type_from_gda(). This is
> > basically the translation table where I want to have access to. There is a
> > also a utils.c for postgres, but appears to be setup differently, but
> > assumes it works similar. How would this all come together?
> >
> >  I just want to be able to put in a G_TYPE_STRING as an argument in some
> > function and get a provider dependent value back like "varchar" for mysql so
> > I can use this in the new method of building a table.
> >
> >
> >
> >  Ah I see. Is it OK if I add in a function like this in
> > gda-postgres-provider.c:
> >
> >  static const gchar
> > *gda_postgres_provider_get_def_dbms_type (GdaServerProvider
> > *provider,
> >
> >
> >   GdaConnection *cnc,
> >
> >   GType gda_type)
> >  {
> >          g_return_val_if_fail (GDA_IS_SERVER_PROVIDER (provider), FALSE);
> >          g_return_val_if_fail (GDA_IS_CONNECTION (cnc), FALSE);
> >
> >
> >          return postgres_name_from_gda_type (gda_type);
> >  }
> >
> >  (and matching declarations etc.) Tested locally and that appears to do the
> > trick. This makes
> > gda_server_provider_get_default_dbms_type() work for
> > postgresql.
> >
> >  Bas.
>
> Please check the latest CVS version where I've implemented those
> functions for MySQL, PostgreSQL and SQLite (I realized they were not
> implemented after I told you to use them!)
Thanks Vivien, That appears to work now (only tested Postgresql at this stage).

Another small issue to fix is to put characters around the column names. For postgresql for instance the following will fail:

CREATE TABLE Test (Id int4 NOT NULL, Desc varchar(10), Price numeric(5,2))

Reason is that 'Desc' is a reserved name. In case of Postgresql it should be surrounded with double quote ("). In case of Mysql it is the back quote (`) I believe. Just a small change in the *-dll.c file for the various providers. Tested locally here and works OK. Let me know if you want me to change this in CVS or if you will do it.

Further to the discussion regarding the creation of the table. There is the Path: /FIELDS_A/@COLUMN_TYPE. Here a complete type must be set. For postgresql that is varchar(30) for instance. As mentioned before I would like to see this part more provider independent. Perhaps we can reach some sort of a compromise. First I tell you what I would like to see:

/FIELDS_A/@COLUMN_GTYPE 
/FIELDS_A/@COLUMN_SIZE 
/FIELDS_A/@COLUMN_SCALE 

example values:
GDA_TYPE_NUMERIC
10
2

This then will be translated by the postgresql provider for instance to numeric(10,2).

To provide both options to the user, we can say that if /FIELDS_A/@COLUMN_TYPE (the current situation), this overrules any situation. If it is not set, the user should set

/FIELDS_A/@COLUMN_GTYPE 
/FIELDS_A/@COLUMN_SIZE  (if applicable)
/FIELDS_A/@COLUMN_SCALE (if applicable)

Then for the various providers we can make this into something that works for the data provider.

Please comment.

Thanks,
Bas.