Vykaendys Posted June 20, 2014 Share Posted June 20, 2014 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. Link to comment Share on other sites More sharing options...
jchd Posted June 20, 2014 Share Posted June 20, 2014 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Vykaendys Posted June 20, 2014 Author Share Posted June 20, 2014 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? Link to comment Share on other sites More sharing options...
jchd Posted June 20, 2014 Share Posted June 20, 2014 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
orbs Posted June 20, 2014 Share Posted June 20, 2014 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. Signature - my forum contributions: Spoiler UDF: LFN - support for long file names (over 260 characters) InputImpose - impose valid characters in an input control TimeConvert - convert UTC to/from local time and/or reformat the string representation AMF - accept multiple files from Windows Explorer context menu DateDuration - literal description of the difference between given dates Apps: Touch - set the "modified" timestamp of a file to current time Show For Files - tray menu to show/hide files extensions, hidden & system files, and selection checkboxes SPDiff - Single-Pane Text Diff Link to comment Share on other sites More sharing options...
willichan Posted June 21, 2014 Share Posted June 21, 2014 You could try using my cooperative semaphores UDF to avoid conflicts. It works very well with heavy loads. '?do=embed' frameborder='0' data-embedContent>> My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash Link to comment Share on other sites More sharing options...
jchd Posted June 21, 2014 Share Posted June 21, 2014 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now