Add simple SQL scripting feature

[githubuser181226]

Hello.

I'm attempting to create a view/query to execute 2 or more SQL statements one after another ie. using begin/end transaction clause.

While it's possible to do it via an external batch script using sqlite.exe (not supplied with SQLiteStudio though), IMO it could be really beneficial to everyone to have the SQL scripting available from inside SQLiteStudio itself.
To my knowledge SQLiteStudio doesn't allow in any way BUT manual (in the SQL editor only) to run multiple transactions.
You cannot create a view to do it, nor you can have a function to do it (it will only run the first statement, probably because you can't call the function on its own like procedures, you have to prefix it with SELECT or make it an argument of another statement).

I imagine an additional branch inside the database sidebar called "Scripts", and a listing of all the scripts right there, which then you can execute by double clicking the name of the script shown there.

The idea is to have really simple feature to store any SQL code (just like SQL snippets) and give it a name, no parameters, just an alias to identify the script for later selection and execution.

This should be stored inside the database itself in a primitive table (alongside other system tables) reserved just for that.

The only thing the program had to do was to:

1. show the list of the scripts inside the database sidebar (where tables and views are shown) under a separate Scripts branch.
2. read and execute the script like any other SQL statement after user double clicks the script inside the sidebar
3. allow for editing of the scripts just like you do with the code snippets

My exmple use cases are:

1. Create a table and fill it with data (usefull for creating and archiving tables containing yearly/monthly summary reports.
2. Remove all rows from and existing table and fill it with new updated data (this is for having an easily updated tables as foreign key sources - you can't use Views for that)

But of course as you could imagine, there are infinite possibilities.

Not to mention, it's possible then to add some sort of automation, eg. run scripts at program startup / exit, run scripts periodically or at a given date/time (eg. create monthly reports as mentioned above), which I'm sure would make many users like myself double happy.

What do you think?

[pawelsalawa]

I tought about something like that. For now we have similar solutions available, but not exactly the same as you described:

1. Code snippets - you can write your script, save it as a snipped and bring it to your SQL editor any time you want by invoking code assistant twice (ctrl+space x2).
2. Custom SQL functions - you can write your script as a function and then invoke it from SQL editor with SELECT your_funtion();

Still, I'm keeping this open to make it available easier through the database tree and double-clicking on it - just like you described. Perhaps linking (optionally) individual code snippets as items in the database tree would do the job.

[githubuser181226]

Thank you very much for even considering this, given how many other things are already selected for the future release.

As I'm writing this now I'm realizing I can achieve everything I described with simple batch scripts. No need to do it in the program at all.

Please, treat this one with lower priority. There are more important things to fix and add before this one. Sorry for the distraction.

Cheers.

[JrgMyr]

The interoperability between the original sqlite3 CLI and the SQLiteStudio GUI is phenomenal.
You can also throw in some script languages that have a DBI interface that can handle the sqlite file format.