Modifying SQL table schema with web2py

Question

I'm having problems modifying SQL table schema in web2py.

1. If I change the field type of an existing field in db.define_table, the SQL column's new type seems to register in web2py, but doesn't actually change anything in the database, which leads to errors.
2. If I drop a field from db.define_table, it doesn't go away in SQL.

---

Example 1: Changing field types

I start out with a table like so:

db.define_table('dummy',
    Field('name', 'string'),
    Field('created', 'date')
    )

If I inspect the table's create statement in SQLite manager, it looks like this:

CREATE TABLE style(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name CHAR(512),
    created DATE)

I've plugged in a few records, though left created blank. Then I realize that I want created to be a datetime, and that I want to add another field, modified that is also a datetime. So I change db.py to the following:

db.define_table('dummy',
    Field('name', 'string'),
    Field('created', 'datetime'),
    Field('modified', 'datetime')
    )

I get no errors initially. However, if I glance at the create statement, I see the following:

CREATE TABLE style(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name CHAR(512),
    created DATE,
    modified TIMESTAMP)

So according to SQLiteManager, the new modified column is the right type, but the old created column's type has not changed.

If I then go into web2py's db admin and edit a row, it will pretend that both created and modified are datetime- when I edit those values it gives me a datetime popup editor. Editing the modified column works fine, but if I edit created, then the db admin screen is no longer able to perform basic select queries, and instead gives me the error:

Invalid Query invalid literal for int() with base 10: '19 11:33:39'

---

Example 2: Dropping fields

Finally, let's say I want to drop the created column and start over, so I exclude it in my code:

db.define_table('dummy',
    Field('name', 'string'),
    Field('modified', 'datetime')
    )

But nothing changes- I still see the created column in SQLite manager, and the above errors persist.

---

The last thing I want to do is edit the table directly in SQLite manager- I tried this once and it turned into a nightmare as web2py was completely out of sync with the database.

How can I force web2py to redefine tables, by deleting old columns and updating types of existing columns if necessary?

Answer 1

SQLite is a special case -- read here about fixing broken migrations.