Broken migration

[Wonton]

Hello!

I have a web2py application with a sqlite database. The structure of my db.py file is something like this:

db = DAL('sqlite://storage.sqlite', migrate=False)
...
db.define_table('table1',
Field('field1'),
Field('field2'))
db.executesql('CREATE INDEX IF NOT EXISTS table1_field1 ON table1 (field1)')

db.define_table('table2',
Field('field1'),
Field('field2'))
db.executesql('CREATE INDEX IF NOT EXISTS table2_field1 ON table2 (field1)')

...

Each time I want to add a new table or a new field, I remove ", migrate=False", make the change and, after it has been applied I add ", migrate=False" again.
This has been working very well so far.

Inside my database folder I have this files:
storage.sqlite
c8b1837fhj47blahblahblah_table1.table
c8b1837fhj47blahblahblah_table2.table

Well, now my problem. Last time I made an update (I added a new table) in my database, the migration was broken. I don't know why but I guess it was probably because I removed accidentally the .table files and after restore them maybe they were too old. I tried everything and finally the migration worked and the database added the new table.

Now my db.py file is something like this:

db = DAL('sqlite://storage.sqlite', migrate=False)
...
db.define_table('table1',
Field('field1'),
Field('field2'))
db.executesql('CREATE INDEX IF NOT EXISTS table1_field1 ON table1 (field1)')

db.define_table('table2',
Field('field1'),
Field('field2'))
db.executesql('CREATE INDEX IF NOT EXISTS table2_field1 ON table2 (field1)')

db.define_table('newtable',
Field('field1'))
db.executesql('CREATE INDEX IF NOT EXISTS newtable_field1 ON newtable (field1)')

...

but the first strange thing, in my database folder now I have this:
storage.sqlite
c8b1837fhj47blahblahblah_table1.table
c8b1837fhj47blahblahblah_table2.table
newtable.table

Why newtable.table doesn't have the c8b1837fhj47blahblahblah part? Is this important?

The thing is that I need to add a new field to table1 table and I'm getting errors and I don't know how to solve them.

Just removing the ", migrate=False" part I get the error "newtable is already defined" and the application fails.
I've tried to use db = DAL('sqlite://storage.sqlite', fake_migrate=True) and have no errors. Then I add my new field but no changes are applied to the database.

What am i doing wrong? how could I fix this?

Kind regards and thank you very much!


[Wonton]

Wow, sorry for answering myself, but I managed to get it working. I've tried to add c8b1837fhj47blahblahblah_ before newtable.table file and now everything is working.

Could it be a problem add this to my table file?

[黄祥]

just a suggestion, please put the create index in the controller because models is always execute everytime your application is execute, so includeing create index too

e.g.

controllers/install.py

def index:

    db.executesql('CREATE INDEX IF NOT EXISTS table1_field1 ON table1 (field1)')

best regards,

stifan

[pallav]

To use fake_migrate, first you have to revert your model definition to the old version (that matches the existing db), run the app. This will make web2py update the table definition file. Now, you can change your models and set fake_migrate to False, and migrate to True.

[Wonton]

Hello and thank you very much for your answers.

stifan:

I thought that when you set migrate=False the content of db.py file was not executed, but obviously I was wrong. So, if I put the CREATE INDEX in a controller, the performance should be much better?

pallav:

Thank you very much, I will take this into account the next time I have problems.

[黄祥]

the the migrate settings is about the define_table affect on the database side (migrate = True) and on the *.table files (fake_migrate=True). 

there will be a little performance impact, but in terms of efficincy code (because you only need to execute 1 time for create index). So let say about study case in here, imagine that you have to use the other database backend say mysql or postgresql, yet to create index is not support "IF NOT EXIST", yet you will be 'check mate' in the case you put the CREATE INDEX in the models. or put it simple just throw away IF NOT EXIST and keep it with your SQLITE, you will got an error said the the index already created.

best regards,

stifan

[Wonton]

Oks, it's clear to me now. I'll change this. Thank you very much again!