How to Store Update Queries?

[JrgMyr]

What is the recommended (best practice) way to store UPDATE and CREATE queries?

SELECT queries (read only) can be stored as VIEWS within the database. UPDATE queries cannot be stored that way. Neither parameter queries. Obviously, all of them can be put in separate small text files (*.sql) and placed close to the database into the same folder. That does work but is neither really pretty and nor consistent from a UX point of view.

If there is a recommended or best practice way we could also mention that in the FAQ for others to note and apply.

[pawelsalawa]

Why would you need to store them? I'm asking, because maybe there is a better way to accomplish what you want.

[JrgMyr]

Hi Pawel, thanks for your interest.

There is a set of queries that analyzes accounting data. The set is collectively called "Journal Entry Tresting" or JET. It comprises some 50+ SELECT queries (--> VIEWS), some ten parameterized look-up queries and some ten UPDATE queries. The latter are there to bring data from different sources into a common shape in order to apply the same analysis no matter where the data comes from and how it looked like in the first place. I would like keep the whole bunch together. But I can't.

"Could not commit view changes. Error message: parameters are not allowed in views".
UPDATE queries cannot be stored at all (in sqlite). That is understood.

If there is a commonly accepted (best practice) way on how to store a bunch of queries in one place that would highly be appreciated.

Best regards, Jörg

[pawelsalawa]

You can create your own, custom SQL functions (there is button on toolbar or entry in Tools menu) where you can define these functions. Here's manual for it: https://github.com/pawelsalawa/sqlitestudio/wiki/User_Manual#custom-sql-functions

You can implement such function using SQL, so you can actually execute any queries you want inside of it. It also accepts parameters. Here is documentation on using parameters in SQL-implementation functions: https://github.com/pawelsalawa/sqlitestudio/wiki/Official_plugins#scripting-languages-plugins - there is an example with DELETE statement with parameters.

Note, that such functions are NOT stored in database itself, but rather in SQLiteStudio. The database (SQLite) itself has no capability of storing custom functions. They are always stored in application that uses SQLite (and here it is SQLiteStudio).

[JrgMyr]

That's a very helpful hint. If I can move the entry part, where a pop-up box apperas and the user is prompted to provide a value into a function then a VIEW could pick up the value by calling that JS function.

Other issue: a JS function could probably also provide more numeric formats such as "1.234.567,89 €" to properly present accounting results.

Sounds promising. Thank you.