4 ms·
Hm, the language does not enforce it, but you _can_ separate things neatly for sure I may have missed something, though, are you thinking of something other th
by gota 4y ago
Hm, the language does not enforce it, but you _can_ separate things neatly for sure
I may have missed something, though, are you thinking of something other than asserting and retracting?
- wizofaus 4y agoCan you syntactically write a SELECT query that also does an UPDATE/INSERT/DELETE? And Bobby Tables doesn't count - using a ; clearly indicates the start of a new query/ statement.
- triska 4y agoAs I see it, the fact that this (legitimate!) question even arises is saying a lot about SQL syntax: Can we syntactically write a SELECT query that also does an UPDATE/INSERT/DELETE? It may be possible, or it may be not possible, and to find out which it is, one has to have a good grasp of a quite complex syntax specification. For comparison, in Prolog, queries have a very uniform and simple syntax, and a small set of predicates is used to modify the database. You can find out whether a query performs any of these operations by meta-interpreting the query. It is easy to write a meta-interpreter for Prolog queries, and to allow only calls of predicates that you know do not affect the database. In comparison, it is extremely hard to analyze and interpret SQL queries, also because the syntax of SQL is so complex that we cannot easily answer such basic questions about the syntax.
- thesz 4y agoThe meta-interpretation will not say anything more than "this query may modify facts' database". Anything above that requires actual interpretation with rollback of changes. Because, you know, of halting theorem. Last time I checked, rollback of changes made into facts database was not even a notion in Prolog language. There was no notion of transaction in Prolog. "meta-interpretation" of SQL statement amounts to looking at the statement type, whether it is an INSERT/DELETE/UPDATE or just a SELECT query. There is no much complexity in the SQL syntax. My first experience writing more or less non-toy parser was to parse SQL schema, it was 20 years ago. SQL is not harder than VHDL with its context-sensitive lexing phase, where you can't answer is '(' a character literal or is it a part record type value construction. Maybe, DELIMITER can bite you, but, again, it is not hard to parse with parsing combinators.
- samatman 4y agoNo, but you can write an UPDATE which uses a SELECT in all of SET, FROM, and WHERE, which broadly speaking will do the same thing. The difficulty in figuring out all this syntactic weirdness is probably overstated for small queries, but it's there. Most attempts to write a better SQL I've seen succeed in narrow terms, but if the goal is to replace SQL rather than write a better syntax which (almost) no one uses, that's not enough.
- wizofaus 4y agoThat's what I had always assumed - i.e. if the first keyword is SELECT it can't possibly modify the data, which does match the OP's claim that SQL separates queries from data manipulation commands.
- samatman 4y agoI see a small impedance mismatch between "SQL prohibits updating in queries" and "SQL requires you to write query updates a certain way", though I see where OP is coming from. One of the reasons mini/microKanren has taken off in research is the use of immutable data structures to prevent the morass you can get with self-modifying Prolog queries.
- dragonwriter 4y agoExcept that all DML commands can also be queries by way of RETURNING.
- ElectricalUnion 4y agoSELECT drop_table_procedure() ;
- wizofaus 4y agoAh, yep, good one, definitely see a danger there.
- dragonwriter 4y ago> Can you syntactically write a SELECT query that also does an UPDATE/INSERT/DELETE? No, but you can write an UPDATE/INSERT/DELETE that returns a result set like a SELECT.