Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to Store Update Queries? #3535

Open
JrgMyr opened this issue Jun 18, 2019 · 4 comments

Comments

@JrgMyr
Copy link

commented Jun 18, 2019

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

This comment has been minimized.

Copy link
Owner

commented Jun 18, 2019

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

@JrgMyr

This comment has been minimized.

Copy link
Author

commented Jun 19, 2019

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

This comment has been minimized.

Copy link
Owner

commented Jun 19, 2019

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

This comment has been minimized.

Copy link
Author

commented Jun 19, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.