Let us get some Gotchas

Introduction

Sometimes when we miss a teeny-tiny detail, fact or point, then a situation arises, and the program results in an output that is quite different from what was anticipated. These situations, cases or scenarios are "Gotchas".

Let us learn about gotchas in web2py.

Gotchas

The gotcha is not an error or an exception. It can be considered a commonly made mistake while writing codes.

Now let us see some gotchas of the database abstraction layer in web2py.

SQLite

In SQLite, columns cannot be deleted or altered. This indicates that web2py migrations will function in some limited way. A column that has been deleted from a table will still be present in the database but won't be visible to web2py. Web2py will attempt to recreate the column and fail if you want to reinstate it. In this scenario, you must set fake_migrate=True in order to rebuild metadata without attempting to add the column again.

A boolean type is not available in SQLite. Internally, web2py converts booleans to a one-character string with the characters "T" and "F" standing in for True and False. The Database Abstraction Layer handles this completely; the abstraction of a true boolean value works well. But if you are directly updating the SQLite table with SQL, avoid using 0 and 1 values and be careful of the web2py implementation.

MySQL

In a single transaction, MySQL does not support multiple ALTER TABLE statements. Any migration operation is thus divided into a number of commits. It is possible to break a migration if something occurs that results in a failure (in the database, the web2py metadata are no longer in sync with the actual table structure).

This is undesirable, but it can be avoided (migrate one table at a time), or it can be fixed a posteriori (revert the web2py model to what matches the table structure in the database, set fake_migrate=True and when the metadata has been rebuilt, set fake_migrate=False and migrate the table again).

Google SQL

Similar issues exist with Google SQL, just like with MySQL and a few more. Specifically, table metadata itself has to be stored in the database in a table that web2py does not migrate. This is due to the read-only file system that Google App Engine has. The above-mentioned MySQL problem in conjunction with Web2py migrations in Google SQL can lead to metadata corruption.

This can also be prevented (by migrating the table at once and then setting migrate=False to prevent access to the metadata table), or it can fixed a posteriori (by logging into the database using the Google dashboard and removing any corrupted entry from web2py_filesystem table.

MSSQL

The SQL OFFSET keyword is not supported by MSSQL (Microsoft SQL Server) < 2012. Hence database cannot do pagination. When doing a limitby=(x, y), web2py will fetch the first x + y rows and discard the first x. When compared to other database engines, this could result in considerable overhead.

Note - 

• The suggested prefix to use if you're using MSSQL >= 2005 is mssql3://, which offers a solution to prevent the issue of fetching the whole non-paginated resultset.
• Use mssql4://, if you are using MSSQL >= 2012, which uses the OFFSET ... ROWS ... FETCH NEXT ... ROWS ONLY constructs to provide native pagination without performance hits like in other backends.

Circular references in tables with ONDELETE CASCADE are a concern for MSSQL. Setting the ondelete attribute to "NO ACTION" for all reference fields will fix this MSSQL problem. This is an MSSQL bug.

db = DAL('mssql://....')
for key in db._adapter.types:
    if ' ON DELETE %(on_delete_action)s' in db._adapter.types[key]:
        db._adapter.types[key] = db._adapter.types[key].replace('%(on_delete_action)s', 'NO ACTION')

You can also try this code with Online Python Compiler

Run Code

MSSQL also has issues with arguments supplied to the DISTINCT keyword.

This works -

db(query).select(distinct=True)

You can also try this code with Online Python Compiler

Run Code

And this does not -

db(query).select(distinct=db.mytable.myfield)

You can also try this code with Online Python Compiler

Run Code

Oracle

Pagination is not supported by Oracle either. Both the OFFSET and LIMIT keywords are not supported. Web2py implements pagination by converting a database (...).

 select(limitby=(a, b)) into a complex 3-way nested select. This is effective for simple select but might not work for complex selects involving aliased fields and or joins.

Google NoSQL

Joins, left joins, aggregates, expressions, OR involving more than one table, and searches using the "like" operator in "text" fields are not supported by Google NoSQL (Datastore).

Transactions are limited and not provided by web2py automatically (you must utilise the Google API run_in_transaction, which is described in the online Google App Engine documentation).

Additionally, Google has a cap on how many records you may get from each query (1000 at the time of writing). Although record IDs in the Google datastore are integers, they are not sequential. The "list:string" type is mapped into a "text" type in SQL, but "list:string"  is mapped to ListStringProperty in the Google Datastore. In a similar manner, "list:reference" and "list:integer" are mapped to ListProperty. Due to this, Google NoSQL searches for content within these field types are more effective than those performed on SQL databases.