Side Bar WTF
bulb (Bulb) — 2014-06-05T13:29:03-04:00 — #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 - 🐶 🐺 🐱 🐭 🐹 🐰 🐸 🐯 🐨 🐻 🐷 🐮 🐗 🐵 🐒 🐴 🐑) — 2014-06-05T13:46:04-04:00 — #2
Splendid information you've presented to us with this level of detail.
chubertdev (I have read) — 2014-06-05T14:20:45-04:00 — #3
I find that it's fairly common for code to be updated, but not documentation, at least past updating $VersionID.
riking (riking) — 2014-06-05T15:20:07-04:00 — #4
bulb (Bulb) — 2014-06-05T15:48:40-04:00 — #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) — 2014-06-06T06:55:23-04:00 — #6
Isn't that the "database" that has a feature I like to call "nominal typing"?
riking (riking) — 2014-06-06T12:02:42-04:00 — #7
Haven't you heard? SQLite is a replacement for fopen().
bulb (Bulb) — 2014-06-06T13:09:57-04:00 — #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) — 2014-06-06T13:40:12-04:00 — #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; --) — 2014-06-06T13:54:11-04:00 — #10
cvi (0 == ) — 2014-06-06T13:59:29-04:00 — #11
bulb (Bulb) — 2014-06-06T15:24:43-04:00 — #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; --) — 2014-06-06T15:25:58-04:00 — #13
Now I hate you, good job.
dkf (Better living through pedantry) — 2014-06-06T16:50:16-04:00 — #14
Quoted for truth.
dkf (Better living through pedantry) — 2014-06-06T16:59:03-04:00 — #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.