On 12/5/2013 14:45, Klaas V wrote:
Warren wrote 4 dec 2013:
| There are tiny corners of the programming world (FP) where this is not the
case, but then you get into
| questions of purity, and databases are about as far from side-effect-free
as you can get.
That's a wee bit exaggerated,
To prove my point, I decided to divide the SQLite commands[1] into those
that modify the DB and those that do not:
Has side effects Limited side effects No side effects
~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
ALTER TABLE ATTACH DATABASE ANALYZE
CREATE TRIGGER CREATE TABLE CREATE INDEX
DELETE CREATE VIEW DROP INDEX
DETACH DATABASE[2] CREATE VIRTUAL TABLE EXPLAIN
DROP TABLE DROP TRIGGER[3] PRAGMA
DROP VIEW REINDEX
INSERT SELECT
REPLACE VACUUM
UPDATE
Commands in the first column clearly have side effects: they can affect
the results of another process's SELECT queries.
The second column is for commands that are unlikely to affect another
process's queries, but they do alter the user-visible DB schema, so it's
hard to argue that they're side-effect-free.
The third column looks longer than it really is. You can look at it as
SELECT plus a bunch of "DBA commands." Those latter commands merely
affect how well the SQLite DB engine runs, and they're unlikely to be
used very often once the DB is set up and working well.
If you strip away the setup, DBA, and rarely-used commands from the
other columns, too, you're still left with SELECT on one side vs about
half a dozen SQL commands with side effects on the other. That's what I
was getting at with my quoted comment above.
We should also consider SQLite's "functions".
Most of the core functions[4] are pure, but there are several impure
ones: changes(), last_insert_rowid(), random(), randomblob(), and
total_changes().
The date and time functions[5] are pure, unless you pass 'now', which is
what all the argument here is about, of course.
The aggregate functions[6] are all pure.
I think my point stands: SQL is awfully impure, from an FP/mathematical
standpoint.
-----------------
Footnotes:
[1] From https://www.sqlite.org/lang.html but leaving out the keywords
that aren't independent commands. I also left out the TRANSACTION and
SAVEPOINT commands, since they just group other commands.
[2] I put the DETACH and DROP commands in the first column even though
their corresponding ATTACH and CREATE commands are in the second because
there is nothing stopping these destructive commands from affecting
another process's queries.
[3] DROP TRIGGER is interesting: From the perspective of figuring out
whether it could affect another process through side effects, this
command actually /stops/ future side effects from occurring, even though
it modifies the DB file to do so. Thus, it goes into the second column,
not the first, where all the other DROP commands are.
[4] https://www.sqlite.org/lang_corefunc.html
[5] https://www.sqlite.org/lang_datefunc.html
[6] https://www.sqlite.org/lang_aggfunc.html
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
