Proper usage of sqlite commands?

[natwolf]

general usage question....what would be the proper way to use the sqlite commands if they are going to be used throughout a script?

 

would it be to use _sqlite_startup at the start of the script and then not use _sqlite_shutdown until exiting the script or would it be to use _sqlite_startup do sqlite functions needed at the time then use _sqlite_shutdown and use startup again when needed? would there be any performance differences in between the two?

[InunoTaishou]

_SQLite_Startup when your script starts, _SQLite_Shutdown when your script exits, use _SQLite_Open when you want to access a database. Technically you don't need to close the database since SQLite works with a shared database but if anyone, or something else, wants to delete your database, it will fail.

All _SQLite_Startup and _SQLite_Shutdown do are open and close the dll file (DLLOpen/DLLClose, startup will do some string manipulation to find the dll if you don't specify the path for it, which can cause some overhead)

[jchd]

3 hours ago, natwolf said:

would it be to use _sqlite_startup at the start of the script and then not use _sqlite_shutdown until exiting the script

Definitely yes. Startup/shutdown involve a lot of thing under the hood, so do that only once per process.

Also *_Open your DBs and *_Close it/them for as long as possible, or rather for as long as your application may have to use the DB(s), for the same reason: Open does a lot under your feet so don't open/close for every operation but only once per process run.

OTOH try hard to keep your transactions short, to maximise concurrency between processes that may need to access the same DB.

[Myicq]

You don't specify your application, but if you have to enter a lot of data (as in 1000s or millions), you definately want to study on "transaction" syntax. This is part of SQL syntax, and not as such AutoIT related. But just a good practice. Otherwise, the database will be updated after every write, which is slowing your queries a lot after some number.

I have started to us SQLite a lot with just the :memory: database, since it makes data structure handling trivial in some cases, where the alternative would be arrays. Especially where you need to select / update / delete the data.

Just my 2c. Corrections welcomed if I am wrong!

[jchd]

SQlite is not only a magic wand for :memory: databases, but for general application (meaning up to several concurrent processes) data storage in "almost all" use cases. In one single DB you can store per-user, per-process settings (traditionally spread in one .INI per user and per process),  user access rights, every single data file previously on its own, and over the top gain ACID transactions over the lot with read/write concurency. Add customizable functions and collations, virtual tables, JSON support ... and so much more that my keyboard is out of fingers.

One cherry on the cake is that your DB is bitwise portable over any hardware/software platform you can dream of (even when you're on illicit substances).

And ... TADA! ... row values, coming with pre-release 3.15.0 (scheduled for release on 2016-10-14).

[Skysnake]

 thx for the info @jchd

[satanico64]

On 22/09/2016 at 11:45 PM, jchd said:

SQlite is not only a magic wand for :memory: databases, but for general application (meaning up to several concurrent processes) data storage in "almost all" use cases. In one single DB you can store per-user, per-process settings (traditionally spread in one .INI per user and per process),  user access rights, every single data file previously on its own, and over the top gain ACID transactions over the lot with read/write concurency. Add customizable functions and collations, virtual tables, JSON support ... and so much more that my keyboard is out of fingers.

hi guys, et bonjour les frenchy

i actually use both json and sqlite but i don't save my json for this time, it's just temporary use.

So how do you save your Json in sqlite ?

For sure it's not encoding json and copying in a database field... so what is the method to use ?

Thanks for your great work.

Nicolas. Bordeaux, France, 1er étage.

 

[jchd]

Store json in a text column, then use json1 set of functions to do whatever you want. json1 is a relatively recent loadable extension but works like a charm.

[satanico64]

thanks @JCHD !

i'll check that as soon as my proxy will allow access to https://sqlite.org/  :\ 

merci, bonne soirée.

Nicolas.