Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)
Stored procedure would be a great addition. But they would be not in the shell, they belong in the library core. Scripts are already a part of the current functionality. Only they have to be external. There no IFs, loops, or whatever other constructs. Those who say soon we would want this and that, it is irrelevant. Because we could want this and that even if scripts remain external to the database. The idea is to simply move scripts inside the db and add parameters (like simple in macro processors.) That's all. Talking about anything more is a much more involved proposal, and when something adds too many complications it will inevitably take forever (if ever) to implement. -Original Message- From: J Decker Sent: Thursday, June 19, 2014 3:45 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Suggestion for SHELL impovement (built-in scripting) Stored procedures variables, a goto(jump/branch) and a conditional so loops can be made; On the topic of shell results; isn't there a result value of like number of records inserted ? So something like var a = insert... select $a; and then test the output sort of? went searching cause I really ended up avoiding stored procedures because of the lack of consistency I don't think SQL standard defines such things... what I saw was very linear top-down sort of things, which makes querying a recursive heirarchical table kinda hard to do in a stored procedure but I guess I was wrong; but they are all different. http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html (mysql does have loop constructs) http://technet.microsoft.com/en-us/library/ms180796(v=sql.105).aspx ( flow control in M$ SQL [tsql]) http://en.wikipedia.org/wiki/SQL#Procedural_extensions (standard?) ya ... almost as many flavors of this as there are databases. sad. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggestion for SHELL impovement (built-in scripting)
Hi all, First of all, this is not about using SQLite as an embedded library from C, or whatever other language. It does not affect the sqlite3.c file at all. It only applies to the shell (so logically, it only affects shell.c) So here’s my suggestion for what (I feel) is a significant improvement for the SHELL version of SQLite without being too much of a programming complication in my view. (Those who usually attack any new concept, please pause a moment and give it some thought, then attack as usual!) Because I primarily use SQLite as a tool (from the shell), and given that SQLite does not (yet, if ever) support stored procedures, I often find myself (as I believe most people on this list) writing scripts that will do a certain task, and then run those using the shell with something like “sqlite3 my.db script.sql” I think we can all agree that the single most important advantage of SQLite is the one-file-holds-everything deal (both for the database file, and for the application or library file). On that principle,... The problem with having all those scripts separate from the database file somehow violate the previous assertion. Plus, there is the problem that scripts cannot be made to have parameters (AFAIK). So, two birds with one stone, ... Wouldn’t it be nice if we could have those scripts somehow saved in the sqlite_master table (or some other new system table, if this one would cause compatibility issues), and then be able to call them very easily from the shell with some special prefix (e.g., :SCRIPTNAME parm1 parm2 parm3 ... – or some similar simple syntax). The : character could be some other special character (except for . used for built in commands.) Then the shell, using the simplest of macro expansion techniques of plain text replacement, would read each line from the saved script, convert occurrence of the each parameter to the text appearing in the invocation and run it as if it was just typed on the keyboard. For example, if my script was: SELECT ~1~ from ~2~ where name like (‘%~3~%’); giving the shell command: :SCRIPTNAME * my_table some_name would be executed as: SELECT * from my_table where name like (‘%some_name%’); I have used ~number~ as a parameter placeholder, but anything that works without ambiguities in the grammar would work. And, then the next line of the script would be executing in a similar manner until the whole script is exhausted. This capability would make the shell capable of supporting very complex script-based command-line applications all stored within the same single database file! Thanks for listening (hopefully). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)
to...@acm.org wrote... Hi all, ... So here’s my suggestion for what (I feel) is a significant improvement for the SHELL version of SQLite without being too much of a programming complication in my view. (Those who usually attack any new concept, please pause a moment and give it some thought, then attack as usual!) Funny... ... ... This capability would make the shell capable of supporting very complex script-based command-line applications all stored within the same single database file! I happen to think that this is a great idea. I can see many ways where I can use it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)
On 06/18/2014 02:47 PM, to...@acm.org wrote: But you need bash, or TCL, or Perl, or Python, or whatever other than sqlite3.exe So, you're suggesting that an innocent SQLite user should install any of those programming packages just to run SQLite. Hmm... no, thanks! Yes. Quite simply you'll start wanting if statements and variables, and loops, and stronger matching primitives etc. That is why I said add puny inadequate incomplete scripting into the C based shell. Unless you can show that what you asked for is the limit of functionality needed for all users of your proposed feature for the foreseeable future, extra scripting stuff would be added. It is far more sensible to use an existing one than invent an arbitrary new one. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)
My suggestion is to have a sqlite_... table in which to start statements to run at DB open time, so as to: - automatically CREATE temp tables, indexes, views - automatically ATTACH related DBs - automatically load extensions (this should require explicit acquiescence from the API caller though) This would allow the use of the sqlite3 shell with apps fully contained in the DB. This is almost what you want, but not quite. This is better because all it does is initial setup. Application logic would still be restricted to being in the schema, but now that could include temp schema that persists -- not entirely an oxymoron. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)
Stored procedures variables, a goto(jump/branch) and a conditional so loops can be made; On the topic of shell results; isn't there a result value of like number of records inserted ? So something like var a = insert... select $a; and then test the output sort of? went searching cause I really ended up avoiding stored procedures because of the lack of consistency I don't think SQL standard defines such things... what I saw was very linear top-down sort of things, which makes querying a recursive heirarchical table kinda hard to do in a stored procedure but I guess I was wrong; but they are all different. http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html (mysql does have loop constructs) http://technet.microsoft.com/en-us/library/ms180796(v=sql.105).aspx ( flow control in M$ SQL [tsql]) http://en.wikipedia.org/wiki/SQL#Procedural_extensions (standard?) ya ... almost as many flavors of this as there are databases. sad. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
