Re: [gnome-db] get_schema call - a couple of queries about information returned.

[Rodrigo Moya <rodrigo gnome-db org>]

On Sat, 2003-04-19 at 04:31, Steve Fosdick wrote: 
> On 11 Feb 2003 00:46:09 +0100
> Rodrigo Moya <rodrigo gnome-db org> wrote:
> 
> Rodrigo,
> 
> Sorry it has taken me so long to reply to this...
> 
> > it is acceptable to return only fundamental types, although for the
> > non-existant-yet UI to create tables, it might be good to have all
> > available types to the user.
> 
> OK, as an example Oracle has 5 types for holding strings within the
> database - it has separate types for fixed and variable width, separate
> types for national-language-specific behaviour and another type for very
> big strings.
> 
> I can see that a user would probably want to choose between these types
> when defining the table, but once the table is defined most applicatons
> wouldn't care which of these types a column had - they are all just
> kinds of string.
> 
> So, should there be separate GDA types for these?
>
yes. We even support Postgres' Point type, so it makes a lot of sense to
add as many specific types like these ones to GDA. Providers will just
use whichever they want to, but we should have all available for
providers to use whatever they fit best.

> Another way to handle this would be have the provider return the type as
> a provider specific string which is opaque to the application (such as
> mergeant) - the application would just display it or let the user choose
> it from a list.  If the application wanted to know what it could do with
> data of that type it would ask the provider for the corresponding GDA
> type - the get_gda_type method you mention later on.
> 
well, those strings are already available in the TYPES schema, along
with other info (which we could extend, if needed). So applications
should be able to manage *any* type via the available information,
included the type->GDA type mapping, which makes this even easier.

> What do you think?
> 
> > hmm, if they are aggregates (like TO_DATE, etc), I think they should
> > be in AGGREGATES schema. Probably we want to change the AGGREGATES
> > schema to return a list (maybe a GDA_VALUE_TYPE_LIST?) of all the
> > types for arguments.
> 
> Perhaps I was not being very clear in my original question.
> 
> The TO_DATE function is not an aggregate.  It converts one string value
> into one date value with the aid of a format string.  It can be used in
> any SQL expression where a date is required, e.g.:
> 
> INSERT INTO items (code,date_added)
> VALUES (123, TO_DATE('04-Dec-2002','DD-MON-YYYY'));
> 
> Aggregates are things like SUM, MIN, MAX, AVG, COUNT - they are
> aggregates because they apply to a whole list of values, one from each
> row of a database query, and return a single value.  Generally speaking
> these are used in conjunction with a 'GROUP BY' clause in the query.  An
> example would be:
> 
> SELECT   department,min(salary),max(salary),avg(salary)
> FROM     employees
> GROUP BY department;
> 
right, then it's a procedure/function, and so should be in that schema.
If it can be used both as an aggregate and as a function, I think it
should be in both schemas.

> >From the perspective of an application that will allow a user to build
> queries graphically the distiction will be important to ensure the user
> doesn't build queries that then get rejected.  For example:
> 
> SELECT   department,min(salary),max(salary),avg(salary)
> FROM     employees;
> 
> would be rejected by the database - it doesn't know how many rows to
> return.  If the functions applied to the salary column were non-agregate
> functions, e.g.:
> 
> SELECT   department,to_char(salary, '$99,999')
> FROM     employees;
> 
> the the query would work correctly:
> 
> So, I think we should enhance the API to take account of this.  One
> possibility would be to have two separate schema categories, one called
> AGGREGATES and the other called FUNCTIONS.  The other possibility would
> be to add another column to the existing AGGREGATES category called
> Aggregate which is a boolean, true for those functions which are real
> aggregates.
> 
hmm, that's what we have right now, since we have a schema for
aggregates and another one for procedures/functions. Do you see anything
wrong in the way things are right now?

> Whichever we do, we will need to change the agrument column into a list
> as you suggest.
> 
> > > Are the type names specified in the AGGREGATES category strings and
> > > are they supposed to be drawn from the same set as returned by the
> > > TYPES category?
> > > 
> > postgres provider returns the postgres type name. I guess this is ok,
> > provided that we add a get_gda_type method on providers, so that we
> > can easily convert from the types returned by the DBMS to GDA types.
> 
> Going back to what I was saying earlier about types, I think there will
> be times when we will want to use provider specific types and times when
> we will want to use a GDA type, so perhaps should have functions to
> convert between the the two as part of the provider API.  Everyone must
> understand though that the mapping is not 1:1 and therefore to avoid
> unecessary conversions.
> 
we have the type->GDA type mapping in the types schema, AFAIR. This
should be enough for apps to know how to display the data from the
provider.

cheers