Re: [gnome-db] Multi column indexes.

[Vivien Malerba <vmalerba gmail com>]

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!

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

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

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?

Thanks,

Vivien