Re: [gnome-db] Multi column indexes.

[Rodrigo Moya <rodrigo gnome-db org>]

On Tue, 2005-02-01 at 11:39 +0100, Vivien Malerba wrote:
> On Tue, 01 Feb 2005 20:22:53 +1000, Bas Driessen <bas driessen xobas com> wrote:
> > Hello,
> > 
> > I would like to implement the ability to create multi column indexes in
> > libgda through the API. I don't believe it is a good idea to expand the
> > structure GdaDataModelColumnAttributes to cater for this. Also it will
> > be complex to do so as it is a multi level situation. Therefore I have
> > come up with the following idea. Let's use this as a starting point for
> > discussion and let me know what you think:
> > 
> > typedef enum {
> >         ASCENDING,
> >         DESCENDING
> > } GdaSorting;
> > 
> > struct GdaDataModelIndexAttributes {
> > 
> >   gchar *column_name;
> >   gint size;
> >   GdaSorting sorting;
> >   gchar *col_references;
> > };
> > 
> > struct GdaDataModelIndex {
> > 
> >   gchar *name;
> >   gchar *table_name;
> >   gboolean primary_index;
> >   gboolean unique_index;
> >   gchar *idx_references;
> >   GList *index_attr_list; /* list of GdaDataModelIndexAttributes */
> > };
> > 
> > This will cater for the basics and we can create statements like:
> > 
> > CREATE UNIQUE INDEX title_idx ON films (title);
> > 
> > CREATE INDEX part_of_name ON customer (last_name,first_name DESC);
> > 
> > CREATE INDEX part_of_name ON customer (name(10));
> > 
> > etc etc
> 
> Looks great!
> 
yeah, sounds like a good idea

> > 
> > Also, do we want a separate "create_index" method or pass on an extra
> > parameter to the "create_table" method or do both?
> 
> I have no opinion; Rodrigo probably has a better idea than me about that.
> 
I guess we can have both

> > 
> > Please respond. If you have other/better ideas/suggestions or if you
> > think some basics are missing, let me know.
> > 
> 
> I was wondering if we could add a method to the GdaDataModel to allow
> for efficient retreival of a row given one or more GdaValues using the
> index structure you just described.
> 
> Let me explain: I have a GdaDataModel (for example the result of a
> "SELECT * from table"), and I need to find the row corresponding to a
> specific value of the primary key of that table. At the moment, I need
> to parse all the rows of the data model and see if each row is the one
> I'm looking for.
> 
> We could have a 
> GdaRow *gda_data_model_locate_row (GdaDataModel *model,
> GdaDataModelIndex *index, GList *values)
> which would locate the correct row in @model using @index and for the
> GdaValues in @values.
> 
@values would be all the fields you want to use for the search?

> This method could then be implemented by each data model class for
> better performances, and there could be a default fallback to the
> complete data model parsing if that particular method has not been
> implemented for a data model.
> 
> What do you think? Do you think the GdaDataModelIndex structure as it
> is now could be used for that purpose?
> 
I think it is a nice idea, and indeed something we need. I don't like
though, or don't understand very well, the function signature you've
used. Wouldn't it probably be better to just use something like:

	gda_data_model_locate_row (model, const char *field | int field,
GdaValue *value_to_look)

also, apart from that, it would be nice to add iterator functions for
the data model, like:

	GdaRow *gda_data_model_get_next_row (model, GdaRow *row_to_start_from,
args_for_search)
	GdaRow *gda_data_model_get_previous_row (...)

thus, we can use locate_row and get_next/previous_row to navigate though
the model easily. When the args_for_search are NULL, get_*_row would
just return the next/previous row in the model.
-- 
Rodrigo Moya <rodrigo gnome-db org>