Sign in to follow this  
Followers 0
Vykaendys

Using SQLite for logging

7 posts in this topic

I have a question that's more "is this good practice?" than being specific to AutoIt.

I've been working on building a suite of scripts that will help my team support a few enterprise applications.  Part of it was to create and use a central database, and the SQLite functionality in AutoIt has been great for this.

My question is this: 

Is it a bad idea to use a SQL database as a log file?  I don't mean logging SQL functions themselves, I'm looking at using it as a central point to contain application events (ie, script "1" performed function "a" at time "x" with a result of "y").

Would this just cause too much extraneous data to be created over using a flat file, and if it's not a bad idea, is there a generally-accepted good way of structuring the data?

Thanks for reading.

Share this post


Link to post
Share on other sites



SQLite is particularly well suited for embedded application data/log store. What makes me wonder is that you're considering a "central" point. You must understand that since SQLite doesn't have a client/server architecture, it isn't recommended for use over a network. The underlying reason is that the engine extensively uses shared locks to enforce ACID properties, but the catch is that none of the existing remote file-locking protocols work flawlessly under moderate to heavy load (whatever OS you use).

If you only use SQLite as local DB, then there is no issue at all for such use.


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

I thought of part of that while designing the database I'm using now.  I've created a separate table that has a record of each other regular table, including a "TableLocked" column that my scripts set to True when they're doing any modifications to the table.  This would overcome that issue, no?

Share this post


Link to post
Share on other sites

Halas, no. I'd rather warn you before you engage fully in this avenue.

A DB busy" flag inside the DB itself won't protect you from conflict issues. The fault is not to SQLite, but to unavoidable flaws hiding deep inside the available protocols.

Use the forum search: someone has posted a simple UDF to overcome that limitation by using an extra file.


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

jchd's comment concerns "heavy load". so no, your addition - although is an extra layer of protection - does not guarantee safe access. under heavy load, it is possible that client1 reads "log is ready for you", but until it gets to actually do the writing, script2 - which gets the same message - has already taken the access, so script1 fails.

the better technique (to my understanding) - still not full-proof, but is more reliable, although has some performance penalty - is to have each of your client scripts do the transaction control, not relying on the SQLite code to do it.

Share this post


Link to post
Share on other sites

You could try using my cooperative semaphores UDF to avoid conflicts.  It works very well with heavy loads.

'?do=embed' frameborder='0' data-embedContent>>

Share this post


Link to post
Share on other sites

That's it. I didn't have time to search when posting but as the author awakes, listen to him and not me.


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

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
Sign in to follow this  
Followers 0