Using SQLite for logging

[Vykaendys]

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.

[jchd]

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.

[Vykaendys]

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?

[jchd]

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.

[orbs]

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.

[willichan]

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

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

[jchd]

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