Side Bar WTF
bulb (Bulb) at June 5th, 2014 13:29 — #1
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:
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 (
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.
nagesh (Nagesh - 🐶 🐺 🐱 🐭 🐹 🐰 🐸 🐯 🐨 🐻 🐷 🐮 🐗 🐵 🐒 🐴 🐑) at June 5th, 2014 13:46 — #2
Splendid information you've presented to us with this level of detail.
chubertdev (Ctrl + K, Ctrl + D) at June 5th, 2014 14:20 — #3
I find that it's fairly common for code to be updated, but not documentation, at least past updating $VersionID.
riking (riking) at June 5th, 2014 15:20 — #4
bulb (Bulb) at June 5th, 2014 15:48 — #5
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.
Sure it does. Most databases do. MySQL and until rather recently (~2007) Oracle seem to be the only major offenders.
dhromed (derp) at June 6th, 2014 06:55 — #6
Isn't that the "database" that has a feature I like to call "nominal typing"?
riking (riking) at June 6th, 2014 12:02 — #7
Haven't you heard? SQLite is a replacement for fopen().
bulb (Bulb) at June 6th, 2014 13:09 — #8
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 🔩.
riking (riking) at June 6th, 2014 13:40 — #9
For everybody else:
🔩 is NUT AND BOLT [Code: 128297 (0x1f529), UTF-8 bytes: \xf0\x9f\x94\xa9 | lua: \240\159\148\169]
maciejasjmj (' OR 1=1; DROP TABLE TDWTF; --) at June 6th, 2014 13:54 — #10
cvi (ω⋅∇L(x̲,ω) = -βL(x̲,ω) + σ ∫p(ω,ω')L(x̲,ω')dω') at June 6th, 2014 13:59 — #11
bulb (Bulb) at June 6th, 2014 15:24 — #12
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.
maciejasjmj (' OR 1=1; DROP TABLE TDWTF; --) at June 6th, 2014 15:25 — #13
Now I hate you, good job.
dkf (Better living through pedantry) at June 6th, 2014 16:50 — #14
Quoted for truth.
dkf (Better living through pedantry) at June 6th, 2014 16:59 — #15
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.