Index on a Field

[Michele Comitini]

I wonder if we could have an argument to hint the DAL in building a database index on a certain Field?

Field('uuid', create_index=True)

What is the reason for not having it yet?

mic

[Massimo DiPierro]

SQLIte can do CREATE INDEX [IF NOT EXIST]. Postgresql does not support IS NOT EXIST. How would you handle this?

--
-- mail from:GoogleGroups "web2py-developers" mailing list
make speech: web2py-d...@googlegroups.com
unsubscribe: web2py-develop...@googlegroups.com
details : http://groups.google.com/group/web2py-developers
the project: http://code.google.com/p/web2py/
official : http://www.web2py.com/
---
You received this message because you are subscribed to the Google Groups "web2py-developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py-develop...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Marin Pranjić]

Index information could be stored in .table files, just as other info.

Marin

[Massimo DiPierro]

I think migrations with indices are much more complex. Moreover indices may involve more than one field.

[Richard Vézina]

Maybe this could help with postgres : http://dba.stackexchange.com/questions/35616/create-index-if-it-does-not-exist

It seems that there is a auto-name feature in postgres...

Richard

[Jonathan Lundell]

On 4 Sep 2013, at 7:37 AM, Massimo DiPierro <massimo....@gmail.com> wrote:

SQLIte can do CREATE INDEX [IF NOT EXIST]. Postgresql does not support IS NOT EXIST. How would you handle this?

try/except?

[Massimo DiPierro]

Let's open a ticket about this and table it for web2py 2.7. ;-)

[Vinicius Assef]

\o/

Maybe some syntax like db.create_index(table_name, [field1, field2])
would be nice, too.

[Michele Comitini]

or db.create_table('t1', Field('f1'), Field('f2'), indexes=['f1', ('f1','f2')])

there are many subtleties here... but it is not mandatory to have indexes we can issue a  warning while an adapter does not support them.

2013/9/4 Vinicius Assef <vinic...@gmail.com>

[Michele Comitini]

Ticket here:

https://code.google.com/p/web2py/issues/detail?id=1665

2013/9/4 Michele Comitini <michele....@gmail.com>

[Niphlod]

I can work on them while dal will undergo a major review in the next web2py version (real fields names and table names, with proper quoting is a feature that I proposed to add).
I just think that migrations will not be easy to achieve, there are some problems with migrations with the current dal as it is right now....
However, I think that those will have to be managed at Table level and not at Field level, 'cause of the possibility among most of dbs to work on multiple columns.
BTW, I just see a few "global" options for creating an index (meaning, something that covers most of index creation needs in most of the cases in most of the databases):
- field(s) (so a list of fields)
- clustered (a boolean)
- asc/desc
- unique/not unique

[Michele Comitini]

> - field(s) (so a list of fields)
> - clustered (a boolean)
> - asc/desc
> - unique/not unique

+1

there are also indexes on expressions and partial indexes that can be interesting.

Maybe an Index() object is needed?

Also on introspection.  I.e. migrage/fake_migrate should use some hints from system catalogs to reduce the handwork caused by a failed migration whether related to indexes or field and table names.

A common technique to identify schema objects in postgresql, is using comments relative to each created db object for finding them even when renamed.  The comment in DAL case, could contain the hash generated by web2py to identify the object (i.e. Table, Field, Index, Sequence, etc.) used in the metainformation files stored in app/databases.  Indeed comments could be used to replace on disk files in the long term.  Too bad that COMMENT is not a standard which make this path not viable.

2013/9/4 Niphlod <nip...@gmail.com>

[Niphlod]

introspection is cool, but it got 7 years to sqlalchemy with lots of manpower to achieve it (and there are still issues). Having it on a bunch of dbs ain't gonna be fun.
I think that we should instead spend time on supporting something else, like composite pkeys ^_^

[Michele Comitini]

sqla does use introspection for creating mapping classes. I mean DAL would use only for making sure that metadata in files is matched by reality...

2013/9/4 Niphlod <nip...@gmail.com>

[mcm]

@Niphlod

About the missing "IF NOT EXISTS" of postgresql do you think that SAVEPOINT can be used?

ex.

<... IN THE TRANSACTION ...>

-> SAVEPOINT d_like_to_create_index

-> CREATE INDEX t1_c1_idx ON t1(c1)

<- ERROR: index exists

-> ROLLBACK TO d_like_to_create_index

<... BACK TO TRANSACTION AS NOTHING EVER HAPPENED ...>

What do you think?

mic

[Niphlod]

I still think that we need to figure out what we're going to do when we have to deal with migrations.

It's one thing to code out the creation of a table and/or the addition of an index, but if we don't find a consistent way to handle migrations for existing indices at least for sqlite, mysql, postgres, oracle and mssql then we must decide what to support.

Moreover, I still think that web2py's migrations on tables with zillions of rows is not something to be managed by a web2py process (that will result in a timeout, and then the transaction will be rolled back, with all the problems that it might rise (blocked transactions due to ddl changes)).

[massimo....@gmail.com]

i agree


From my Android phone on T-Mobile. The first nationwide 4G network.

-------- Original message -------- Subject: Re: [web2py-dev] Index on a Field From: Niphlod To: web2py-d...@googlegroups.com CC:

[Michele Comitini]

One thing missing or hidden is the chance to have pre and post creation hook in define table.

For instance there is an example in the book to create and index with sqlite with the following in a model:

db.executesql('CREATE INDEX .... IF NOT EXISTS...') while that does not rise an error it is a statement that gets executed every time the model is executed!

It would be nice to have it executed only once... but once when? when the table is created or when it is altered... very complex...

2013/9/9 massimo....@gmail.com <massimo....@gmail.com>