natwolf

Proper usage of sqlite commands?

9 posts in this topic

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?

Share this post


Link to post
Share on other sites



_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)

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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!


I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

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).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

:) thx for the info @jchd


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

thanks @JCHD !

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

merci, bonne soirée.

Nicolas.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now