I'm migrating from sqlite to mysql, and need to create indexes on columns in my database. Annoyingly, mySQL doesn't have a CREATE INDEX IF NOT EXISTS facility. So I was wondering if I could just create a new index when the table itself is created by web2py, and not any other time. But where in the code do I place a routine that is only called when web2py calls 'create table'?
The web2py API does not include a way to determine whether a given table has just been created as part of the current request (if migrations are turned on, the table is created on the first request, and otherwise, it is assumed the table already exists). When a table is created in the database via the web2py migrations mechanism, a *.table file is created in the application's /databases folder. So, to determine whether a table was just created, you would have to determine that no *.table file exists right before db.define_table is called, and that the *.table file does exist right after. You probably don't want to do this on every request, so maybe better to simply handle index creation outside of the application.
A better approach would probably be to manually generate the SQL to check whether the index exists, but again, you would want to avoid that on every request in production.
Note, there has been discussion about adding index creation functionality to the DAL, but it is not there yet.