3

Recently I started using DB Manager to query Spatialite DB. I was happy to see that QGIS DB Manager had a "save query" option, but it seems it saves them only for the current session of QGIS. When I reload the program and reconnect to DB all my saved queries are gone.

enter image description here

So, is this the way it's suppose to work? I was hoping that the queries were being saved inside the DB.

Improve this question
5
  • nah, it depends on the client application to save queries, the DB doesn't care for that...pgAdmin keeps a history in-session, as QGIS does, but to save them, one needs to write them to file (e.g. I keep an editor open and copy-paste frequently, but most clients offer an export to file). the suggested option for in-DB storage would be to create views, e.g. CREATE VIEW <name> AS ( SELECT ...), which effectively stores the query and executes it every time you call the view. they are listed in each shemas Views tab just like physical tables (there's more to it, check the docs)
    – geozelot
    May 10, 2018 at 7:38
  • Copy/paste to notepad. Yeah, that's exactly what I did. Still, it would be nice to have those queries stored inside... I don't know, QGIS project files? Import/export function, like you mentioned, should be implemented by the devs.
    – pg85
    May 11, 2018 at 18:27
  • I'm not sure why a rather obvious feature like the query autosave is not implemented, especially in pgAdmin; might be for a reason I don't get. I tend to use the terminal (and linux does do a good job on keeping a history), but had have cases where I needed to crawl the logs to get back at least parts of my more complex queries. the QGIS DB Manager also misses the cancel option, which forced me to costly restarts a few times in the past. would appreciate to hear a dev point-of-view on this, too. or learn how professional DB admin/power user handle this (e.g. mainly working with .sql scripts)
    – geozelot
    May 11, 2018 at 19:00
  • btw., as you might know, saving your project, naturally will save the queries used to create DB layers...but that's not the point.
    – geozelot
    May 11, 2018 at 19:05
  • Actually, I haven't tried that @ThingumaBob, but as I you can see in my second reply I was unconsciously suspecting it ;) It seems to be a good answer to my question. Post it and I'll pick it. Still, load and save buttons would be an obvious addition, as you pointed.
    – pg85
    May 15, 2018 at 10:54

2 Answers 2

Reset to default
5

Following the comments:

QGIS seems to lack an across-session SQL history storage, and even the store option in the DB Manager does not prevail after reload.

QGIS does however save all layer creation dependencies with the project file; this includes any Web Service definitions as well as all SQL based selections in Virtual Layers, the Execute SQL tool and layers created via the DB Manager (or the equivalent Browser options); on reload and per default (if none are saved), QGIS will prompt for the relevant connection credentials and refreshs the data. The used queries can then be updated, reviewed and altered per the layer context menu (e.g. Edit/Update SQL for DB derived layers).

To store queries in the DB, Views are the suggested method. Apart from that, as you said, an open editor window is your best friend here...

(One more reason to store all queries externally while developing is the missing abort option for query execution...an accidental loop and QGIS [< 3.2] burns...)

Update:
As @Gustry points out, the upcoming QGIS LR 3.2.0 is supposed to have background execution and the Cancel button and, yes, the query history as a widget, see comment below.

Improve this answer
7
  • 2
    > "an accidental loop and QGIS burns...)" Not anymore since QGIS 3.2. There is a cancel button and the query is running in the background.
    – etrimaille
    May 16, 2018 at 0:12
  • @Gustry nice, brand new version to the rescue! I'll add that to the answer...
    – geozelot
    May 16, 2018 at 5:26
  • QGIS 3.2.0, not QGIS 3.0.2 as you mentionned in your answer
    – etrimaille
    May 16, 2018 at 11:24
  • @Gustry 'since' is a bit misleading when we are talking about upcoming releases...
    – geozelot
    May 16, 2018 at 12:24
  • 2
    BTW, also coming in QGIS 3.2: github.com/qgis/QGIS/pull/6749 SQL query history
    – etrimaille
    May 16, 2018 at 13:37
2

Now that QGIS 3.2 is out, options mentioned by @Gustry are available. We have access to project independent query history from within DB Manager window.

enter image description here

Queries are stored per db, so when you use "SQL WINDOW" button on a specific db, you only see those that you executed while being connected to that db in the past. Changing db loads appropriate query history.

PS Canceling queries also works like a charm. Many thanks to the developers!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.