How to add a column?

[Bill Thayer]

Can't beleive this question does not come up in the search box. How do i add a column to a table using SQLlite? Simply adding hte field to the define table statement throws an error.

Using SQLlite I cannot add a Field. I get an error message:

OperationalError: no such column: source_via.substrate

No Joke Really? 

When I set migrate_enabled=True then the message changes to something like "the table sourc_via does not exist"

Of course it does since I just added the column that I'm being told does not exist either to it.

On top of all that I am back to the Rocket server locking up. It likes to do that so I have to use Task manager to close it. Could be that session.connect +SQLlite is not a good combinnation.

[Niphlod]

in the "normal" way (i.e. a new Field(), with of course nothing set to prevent migrations, such as migrate=False in the table def, migrate=False, fake_migrate=False, migrate_enabled=False, fake_migrate_all=True in the DAL, etc).
There absolutely no problem on doing that.
With the first error it seems that you added a Field but you didn't let the table to migrate (i.e. web2py expected a column to be there already, and it didn't find that)
The second instead seems to point to a table that existed in the db and was dropped manually (assuming that you defined the table 'source_via' and you're trying to access it as 'source_via', your error seems like a typo 'sourc_via' without the 'e').

PS: let me explain for others stumbling on this post on how migrations work. database/*.table files are there to "hold" the situation on the current database status.
With default settings (i.e., migrations turned on):
- the database/ folder holds all the table definitions as they are on the database (the *.table files)
- whenever a request comes in and your db.py is executed, web2py "spots" the differences between your .table files and the db.define_table() statements (table files "holds a photography" on what was the model the last time you executed db.py): if there are differencies (like a new column, or a new table) the table is created on the db (check sql.log for the statements used), and the .table file is updated to reflect what there is in the db

What happens if you set migrate=False in the table definition ? the check between the model in db.py and the .table file is skipped, and web2py assumes that on the db the table reflect exactly what there is in the model

What happens if you set fake_migrate=True in the table definition ? web2py assumes that on the db the table reflect exactly what there is in the model, the .table files are recreated

What happens if you set fake_migrate_all=True in the DAL ? all .table files are recreated, and web2py assumes that on the db the db tables are reflecting the model.
 
What happens if you set migrate=False in the DAL? whatever table has no a specific "migrate" parameter, the migrate=False is applied to every table

This kind of errors can happen only if you messed with this logic, e.g.

db.define_table('test', Field('test1'), migrate=True) #web2py create the test table with the columns id and test1

then

db.define_table('test', Field('test1'), Field('test2'), migrate=False, fake_migrate=True) # web2py assumes that you created manually on the db the column test2, and updates the .table file

then

db.define_table('test', Field('test1'), Field('test2')) # web2py sees no change between the .table file and the model, but if there's not the column on the db, when you try to use it you'll get the "no such column" error

then you delete the 'test' .table file manually, then

db.define_table('test', Field('test1'), Field('test2'), migrate=True) #web2py assumes that the 'test' table is not on the db, because the corresponding .table file is not there, so it tries to create it, and you get the error "table 'test' already exists on the db"

or, you delete the 'test' .table file manually, and drop the table manually on the db then

db.define_table('test', Field('test1'), migrate=False) #web2py assumes that the table is already there, and when you try to use it you get the error "table 'test' does not exist"

PS: session.connect(db) where db is SQLite is bad on every possible level. Everytime a session changes you won't be able to write to the database (SQLite by default doesn't allow simultaneous writes). session.connect(db) is there to allow several web2py instances on different machines to NOT have the sessions/ folder shared among them (or when you don't have write access to the disk), and to be used with "professional" db (postgres, mssql, mysql, oracle, etc)
Even better is to use memcached or redis for sessions, if you have those available, or stick to cookie based sessions if your session data is little.

PS2: what's wrong with default file-based sessions anyway? Unless you have 100 concurrent requests AND ~40K separate sessions there is absolutely no performance gain observable using other methods (db, redis, memcache, cookies). The only "added feature" is being able to have separate web2py instances on different servers without configuring a load balancer with sticky sessions in front of those.

[Bill Thayer]

Very Interesting. Thank you!

[peter]

Thanks Niphlod this is a really clear explanation. It seems to me somewhat clearer than the section in the book on migrations. Maybe you could refresh the bit in the book with some of this post.

Peter

[Adrian Zhang]

The explanation is really help. Especially to me, a newbie of web2py and even development.

[Alex Glaros]

helpful explanation

thanks!

[黄祥]

very detail and clear explaination with the examples, why not put this on the book on dal chapter?

best regards,

stifan

[Manuele Pesenti]

Il 17/01/14 00:18, 黄祥 ha scritto:

+1

M.