add Index object to schema, add create/drop index functionality to appropriate database modules

[Mike Bayer]

No description provided.

[Former user]

Hello, I'm testing the Index branch with postgresql and it seems to work well.

I think there's just one not-so-intuitive behaviour: in sqlalchemy, whenever you're required to specify multiple columns, you usually pass a list, e.g. something like table.c.column2. The Index constructor, by the way, requires you to specify index columns as multiple arguments, like this:

indexname = Index("indexname", table.c.column1, table.c.column2, ... )

and that's not what I expected when I first installed such branch. Passing a list would be more coherent.

Also: while that sintax may be kept for indexes which should be created/dropped/modified on-the-fly during executing, it would also be nice to have an index (possibly a unique index) to be auto-generated by inserting an appropriate keyword in column definitions, something like:

table = Table("table", engine, Column("column1", Integer, index=True), Column("column2", Integer, index=True) )

should generate an index like:

column1_column2_index = Index("column1_column2_index", table.c.column1, table.c.column2, unique=True)

If you need help or testing, or just doc writing, feel free to contact me.

Alan alan.franzoni "at" gmail.com

[Mike Bayer]

basic index code checked into trunk in changeset:986.

the list of columns being passed is consistent with a Table object....since the list of Columns is the primary thing an index deals with, i dont mind it as an *args list....we could perhaps patch so that it checks the first "column" to be a list itself [], a little overkill perhaps...

im in favor of the Column keyword arguments "indexed" and "unique". need to figure out how you want to name these indexes, also table.create() needs to descend down into those indexes and create those as well. should each new Index object attach itself to its parent Table, so that table.create()/drop() can build/drop indexes as well ?

[Former user]

ok, now I got why you wanted columns passed as an *args... it's not so important whatsoever, once you've figured out, and it doesn't deserve a dual-way constructor.

The idea of having indexed or unique keyword would be great. The index name could just be a concatenation of column names plus a keyword, e.g. if you want an index for 'name' and 'surname' columns, it would be called name_surname_index, I'm not sure about chars limit in column names, by the way.

Also, it's a matter of how the 'indexed'/'unique' keyword should be handled, and how to determine if the programmer wants just that column to be unique, or the whole index? And what if one column is marked as indexed but not as unique?

We'll have to deal with all those... but I admit it might be quite an issue, and the 'external' Index syntax may be easier.

[J Pellerin]

I've just checked in my first cut at supporting inline index definition, in the indexes branch 1048. The indexes branch has had trunk merged in up to 1046 as well.

This adds 2 mutually-exclusive keywords to Column: index and unique. Either may specify {{{True}}} -- which will auto-create the index name, and make a single-column index -- or a string, which will be the name of the index. Multiple columns may specify the same index name; in that case, they will all be added to the index, in order of creation. And indexes defined inline or separately before table.create() is called will be created with the table automatically.

Here's an example (taken from test/indexes.py):

events = Table('events', testbase.db,
               Column('id', Integer, primary_key=True),
               Column('name', String(30), unique=True),
               Column('location', String(30), index=True),
               Column('sport', String(30),
                      unique='sport_announcer'),
               Column('announcer', String(30),
                      unique='sport_announcer'),
               Column('winner', String(30), index='idx_winners'))

Mike -- I can handle merging the branch back to trunk if you want, if you like this implementation. Merging from dev branches that have gotten trunk merges can be pretty hairy.

[Former user]

I've just checked out the code from svn, and I'm gonna try it out these days. Just a couple of questions:

-what if a user mixes up the same index name with unique/index? e.g. something like:

Column('sport', String(30), unique='sport_announcer'),

Column('announcer', String(30), index='sport_announcer')

I think that either an error should be raised, or a default, consistent behaviour (unique or not unique?) should be adopted.

I like the solution you found, btw. I hate being forced to choose an index name when I don't need it, but I sometimes do :-)

Alan <alan.franzoni - gmail.com>

[J Pellerin]

what if a user mixes up the same index name with unique/index?

Oops. Good question. Currently, the first column wins. You're right that it should raise an exception, though. I'll fix that in the next few days (time permitting).

[Mike Bayer]

going to close this, the functionality is there, if further tweaks are wanted they can open a new ticket...