DAL Bug?

[Leonel Câmara]

I noticed I was getting zero returned from my deletes. After reading the python docs I found this:

With SQLite versions before 3.6.5, rowcount is set to 0 if you make a DELETE FROM table without any condition.

Available here: https://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.rowcount

Shouldn't the DAL handle this better? Maybe in SQLiteAdapter which should already have a delete method overload anyway since there's special case code for it in the BaseAdapter which shouldn't be there as special cases should be in the specific implementations.

 

[Derek]

What's buggy about it? 

[Leonel Câmara]

It should not return zero, it should return the number of deleted rows. The purpose of using a DAL is to abstract away this kind of differences.

[Anthony]

Can you show your delete code that generated SQL without any condition?

[Leonel Câmara]

The code was literally just this line:

        count = db(db.static_page.id == request.args(0)).delete()

I have no idea why the sql is going without a condition, the static page does get deleted. I only noticed the problem because the next lines were:

        if not count:

            raise HTTP(404)

And I was constantly getting a 404.

I tested this on the command line after inserting a static page

>>> db(db.static_page.id > 0).select().first()

<Row {'body': '<p>vefvefv</p>', 'lang': 'pt', 'name': 'vevefv', 'is_active': Tru

e, 'created_by': 1L, 'created_on': datetime.datetime(2014, 7, 9, 16, 8, 51), 'mo

dified_on': datetime.datetime(2014, 7, 9, 16, 8, 51), 'modified_by': 1L, 'id': 1

L}>

>>> count = db(db.static_page.id == 1).delete()

>>> count

0

>>> db(db.static_page.id > 0).select().first()

>>>

So as you can see it's returning zero when in fact it's deleting one record, as one can see in the final query that nothing is returned.

[Leonel Câmara]

Humm wait a minute, could this be because I enabled versioning and nothing is in fact deleted?

[Massimo Di Pierro]

yes/.That is it. Please open a ticket about this, it may be considered a bug even if we should discuss it on web2py-developers. Not so clear cut.

[Leonel Câmara]

Thanks everyone for helping me sort this out, issue submitted:

https://code.google.com/p/web2py/issues/detail?id=1948

[lyn2py]

I ran into something similar. If you use versioning, you need to create your own function to "delete" the item, which is in fact, to just "disable" the entry and then filter all searches by excluding the "deleted" entries, otherwise it would break your foreign key links to the other (versioning) databases.

[Leonel Câmara]

But lyn2pi that's what the delete is doing (marking them inactive), my only peeve is with the count returned from the delete which in my opinion should be the number of rows marked inactive.