Re: [gnome-db] Create Index using new ddl functionality.

[Bas Driessen <bas driessen xobas com>]

On Mon, 2006-09-04 at 10:00 +0200, Vivien Malerba wrote:
> On 9/3/06, Bas Driessen <bas driessen xobas com> wrote:
> >
> >  Hi Vivien,
> >
> >  Looking into the Create index situation at the moment. Also here we lost
> > some functionality with the previous implementation. I agree that the new
> > situation is is closer to the syntax of CREATE INDEX of the various
> > providers, but since there is no ALTER TABLE available (yet) through this
> > new ddl interface, there are some limitations introduced with this new
> > stuff.
>
> There won't be a single ALTER TABLE operation, but rather several
> small ones for each type of table alteration - there is already ALTER
> TABLE ADD COLUMN, ALTER TABLE REMOVE COLUMN and ALTER TABLE RENAME TO.
> Other ones will be added later.

OK, fair enough.
> >
> >  In the old situation we had 3 types of index:
> >
> >  -1 Primary
> >  -2 Unique
> >  -3 'Normal'.
> >
> >  In case 1 and 2, for most providers an ALTER TABLE statement was created
> > and only for the 3rd type a CREATE INDEX.
> >
> >  In the current situation, we only have situation 3.
>
> No, for example the Postgres provider's CREATE INDEX spec has a
> INDEX_TYPE value  to specify the type of index. However that value is
> not mandatory because some providers may support specifying the type
> of an index (correct me if I'm wrong here...)

OK, partially correct. It is possible to add a type as UNIQUE. My only comment here would be that again, it is not very provider independent. If there is a data provider that uses a type called DISTINCT for instance rather than UNIQUE, exceptions have to programmed, while it can/should be taken care of libgda. The current INDEX TYPE is just passed on to the provider. We can introduce an additional one calling INDEX_LIBGDA_TYPE (sorry, can't think of a good name at the moment) for instance where we translate to the provider dependent situation.

Primary keys can not be altered with the current situation and only be added as a constraint with a CREATE TABLE statement. In your current situation it means we would need something like CREATE_PRIMARY and DROP_PRIMARY. Would this make sense?

> >
> >  I guess, the best situation would be to introduce an ALTER option next to
> > the existing CREATE and DROP that is already in place. Is this being worked
> > on at the moment? If not are there plans to introduce this? What is the
> > status of this?
>
> Considering my comment above, I don't see why we would need this (but
> again I might be wron here as I'm not a DB expert).