Database field length is not enforced

Question

I am using web2py (python) with sqlite3 database (test flowers database :) ). Here is the declaration of the table:

db.define_table('flower',
        Field('code', type='string', length=4, required=True, unique=True),
        Field('name', type='string', length=100, required=True),
        Field('description', type='string', length=250, required=False),
        Field('price',  type='float', required=True),
        Field('photo', 'upload'));

Which translates into correct SQL in sql.log:

CREATE TABLE flower(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code CHAR(4),
    name CHAR(200),
    description CHAR(250),
    price CHAR(5),
    photo CHAR(512)
);

But when I insert a value of "code" field that's greater than 4 chars, it still inserts. I tried setting to CHAR(10) (simple test, I guess) with the same result.

>>>db.flower.insert(code="123456789999", name="flower2", description="test flower 2", price="5.00");
>>>1L;

The same problem applies to all field where I set the length. I also tried validation (although, I am not 100% on correct use of it). This is also within flower model flowers.py where the table is defined and follows table declaration:

db.flower.code.requires = [ IS_NOT_EMPTY(), IS_LENGTH(4), IS_NOT_IN_DB(db, 'flower.code')]

Documentation on this is here, but I can't find anything that's limiting SQLite3 or web2py length check of the string. I would expect to see an error on insert.

Would appreciate some help on this? What did I miss in the documentation? I used symphony2 with PHP and MySQL before and would expect similar behaviour here.

Answer 1

SQLite is not like other databases. For all (most) practical purposes columns are untyped and INSERTs will always succeed and not lose data or precision (meaning, you can INSERT a text value into a REAL field if you want).

The declared type of the column is used for a system called "type affinity", which is described here: https://www.sqlite.org/datatype3.html.

Once you get used to it, it's kind of fun -- but definitely not what you'd expect!

You have to perform length checking in your code before issuing the INSERT.

Answer 2

As already mentioned, SQLite does not enforce character field length declarations (see https://www.sqlite.org/faq.html#q9). Furthermore, the IS_LENGTH validator is only applied if you do the insert via a SQLFORM submission or via the .validate_and_insert method -- if you just use the .insert method, the validators stored in the requires attribute are not applied, so you will get no error.