sqlite pragma

[ingoogni]

It seems that on every 'action' the database connection is opened and closed once done. For SQLite it means that some configurations have to be sent along every time[*]. For example the foreign_keys PRAGMA. I tried adding it to the the settings file:

  "cli_options" : {
    "sqlite": {
        "options": [],
        "before": [],
        "after": [          
          {"query": "PRAGMA foreign_keys=ON;"},
        ],
        "args": "\"{database}\"",
        "queries": {.........

and various other places but it does not seem to work that way. Adding it to the query file it self does, but that's rather cumbersome. So, where should the pragmas be added to configure the connection?

[*] Would it be possible to have an option for a permanent connection to the db (or is there?), without it using an in memory db is not very usefull.

[tkopets]

To solve your immediate problem:

1. Edit your SQLTools user settings ST: Settings and add the following lines (see below). Don't modify the original settings file. If you have already modified it, restore it to its original state.

   {
   ....
   
   "cli_options": {
       "sqlite": {
           "before": [
               "PRAGMA foreign_keys=ON;"
           ]
       }
   }
   
   ....
   }

2. Select your SQLite connection and execute the query to confirm it worked as expected (it worked just fine for me).

   PRAGMA foreign_keys;

   Gives me:

   foreign_keys
   ------------
   1           

Re: connection per "action"

Indeed, for every "action", a brand-new connection is opened. At this time there is no way to get a persistent connection with this plugin. If a persistent connection is a must - take a look if you can use a REPL plugin for that (e.g. Sublime​REPL). Given the number of supported database engines it would require a significant effort to redesign this plugin to support persistent connection as we would also have to start dealing with connection state management as well. Right now it is simple - fire off process and if something bad happens you get stderr, if all is good stdout goes to the user. If the process takes too long - just kill the process. In any case, the DB connection is closed automatically. Feel free to contribute!

[ingoogni]

Thank. I'll give it a go once the rest works again, I'll ask about that later in Gitter.