Bug-to-bug compatibility



  • So I wanted to do something with sqlite yesterday that involved some logic and thus couldn't be just thrown to the shell's standard input. So I thought, fine, let's wrap it in a bit of python. So I opened the documentation:

    [quote=11.13.6. Controlling Transactions]
    So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).
    [/quote]

    Mind you, the first reason is a blatant lie. These commands (except one pragma that has to be set before transaction to have effect) work in transactions. I've been dropping and creating tables in transactions in SQLite 3 (from C++) for last four years.

    And the second is not a reason. SQLite 3 will happily tell you whether transaction is active. Just ask it whether it is in autocommit mode (sqlite3_get_autocommit).

    So the only reasons I can think of are:

    • The author thought SQLite is as brain-damaged as MySQL.
    • The author wanted the module to behave like MySQL though it is brain-damaged and other databases like Postgress, FireBird or SQL Server don't do it.
    • Some ancient version of SQLite (older than 3) behaved like that and the author didn't realize it was since fixed.

    I don't know which is worse.



  • Splendid information you've presented to us with this level of detail.



  • @Bulb said:

    Some ancient version of SQLite (older than 3) behaved like that and the author didn't realize it was since fixed.

    I find that it's fairly common for code to be updated, but not documentation, at least past updating $VersionID.



  • Yup, SQLite has transactional DDL:



  • @chubertdev said:

    I find that it's fairly common for code to be updated, but not documentation

    I looked through the SQLite history. It does not mention adding support for create/drop under transactions and mentions create/drop under transaction as far back as 2.2.1 released on 2002-01-09. The python binding for sqlite version 3 is separate from sqlite version 2.

    @riking said:

    Yup, SQLite has transactional DDL

    Sure it does. Most databases do. MySQL and until rather recently (~2007) Oracle seem to be the only major offenders.



  • @Bulb said:

    SQLite

    Isn't that the "database" that has a feature I like to call "nominal typing"?



  • Haven't you heard? SQLite is a replacement for fopen().



  • @dhromed said:

    Isn't that the "database" that has a feature I like to call "nominal typing"?

    You mean 🐤-typing1? Yes, that's it.

    1Damned Unicode, it does not even have a proper duck‽2

    2It does not have a screw either, though it does have a 🔩.



  • @Bulb said:

    🔩

    For everybody else:

    🔩 is NUT AND BOLT [Code: 128297 (0x1f529), UTF-8 bytes: \xf0\x9f\x94\xa9 | lua: \240\159\148\169]





  • Filed under: let's play charades



  • @riking said:

    Haven't you heard? SQLite is a replacement for fopen().

    QFT, unfortunately.



  • @cvi said:

    QFT, unfortunately.

    Good luck implementing the searching and general data slicing and dicing functionality manually and with comparable performance.

    At work we have application that does some non-trivial data processing. The databases of various intermediate results are up to hundreds of gibibytes. The guy who started it had a very nasty case of NIH syndrome and did everything by hand. It was a nightmare trying to get anywhere that way. It wouldn't have been possible to get to state where we are now without rewriting most of the thing to SQL queries.

    Of course this is application that needs to slice and dice a lot of data, in many datasets, and does not need concurency. SQLite sucks at concurrency. MySQL sucks at transaction, PostgreSQL at speed (it improved a lot lately) and Oracle sucks at many things. You can't have everything.



  • @Bulb said:

    gibibytes

    Now I hate you, good job.


  • Discourse touched me in a no-no place

    @Bulb said:

    Oracle sucks

    Quoted for truth.


  • Discourse touched me in a no-no place

    @riking said:

    Haven't you heard? SQLite is a replacement for fopen().

    It's a lot better than hand rolling your own serialization code (especially if you actually want transactional safety, which I'd recommend) and its concurrency handling is better than it used to be (about at the limit of what you can achieve without a DB server, and providing you use the right compile options IIRC). It's also good at semi-structured data handling (e.g., full text searching over a database with long textual data in one column). I wish I could use it in my Java projects; Apache Derby is nowhere near as stable or good at not corrupting my data, but by policy I'm avoiding native code.

    But some people see “it doesn't force every column to have a rigid type” and freak out. The wusses.


Log in to reply