But, when “going outside” is not desirable or feasible, another solution is required, namely threads. There are those who regard threads as evil and I too would have to admit I generally would not use them without good reason. But sometimes they’re needed, usually to keep a controlling UI (in this case Excel) responsive and capable of monitoring and managing background long-running tasks.
Both of these “lock” the Excel main thread until complete, with hammerThreadEnabled being also dependent on Excel’s dependency graph to determine if threading will happen or not. So I figured I needed to bite the bullet and add a new set of threading commands to better handle long-running tasks, be they tasks that eventually return data or a status to the main Excel thread (such as a long running save to ( or fetch from) a high latency web service end point), or those that run continuously (such as a Excel as a web service). The main new commands are:
- PYTHONTHREAD – like PYTHON but spawns a thread to run to Python script. (NET4.0 runtime required)
Both commands “take ownership” of the HAMMER session’s SQLite database and return the new thread’s ID (in the form PYTHREAD1,PYTHREAD2, JSTHHREAD3 etc), and are likely to be the last command in a HAMMER function call’s sequence.
The thread ID can then be used by the following helper commands to monitor, re-join and clean-up such threads:
- SQLFROMTHREAD – like SQL but takes two arguments, 1st is the thread id, and the 2nd is the SQL to apply against that thread’s SQLite database. e.g. = … “JSTHREAD1″,”Select * from messages”,”SQLFROMTHREAD” … In general, only Select statements would be issued against this database, but SQLite is thread-safe so data updates are possible but with obvious potential for SQLITE_BUSY locks and time outs. (Likewise, the thread itself can communicate back to main thread using an APPDB connection, as APPDB is always attached uniquely to the main Excel thread).
- THREADRETURN – will report on whatever return values (be that an error, or on success, a string or a table value) once the thread completes. Again, it uses the thread ID as its argument. If the thread is still running, the command returns “Waiting”.
As SQLite is thread-safe, SQL can be used as a simple and robust method of inter-thread communication. If the pattern of threads only updating their “own” database while the main Excel thread updates only “APPDB” is followed, very little locking or timeout issues should arise (ha, famous last words – okay, not as famous as Scott’s). There’s also nothing to stop more adventurous inter-thread single database usage if the need or impulse arises (“Do ya feel lucky Punk, well do ya!”). And don’t forget, normal disk-based databases can also be used alongside, or instead of, the more usual in-memory ones.
This use of SQLite falls neatly into my view of SQLite not so much as a database but as means of mapping and managing shared memory using SQL as an API.
It’s often said that SQLite should be viewed not as an alternative to Oracle/MySQL/MSAccess/whateverRDBMS but as an alternative to a file-open statement. But for me, I primarily use SQLite as an alternative to rolling-my-own in-memory shared data structures (be that across threads or application stacks).
Two other new commands added with this version are:
- VERSION – will return the version of the internal HAMMER (XLite) engine is use.
- LISTCOMMANDS – returns the list of commands available.
- db.jsCreateTable(tableName, JSONaLOLorLOOobject) where the 2nd argument can be a “JSON table” in either List of Lists or List of Objects format. A table will be created using either the first “line” of the LOL or the “names” of the objects fields in the LOO to provide the column names (no types applied, as no need most of the time).
- db.jsInsertRows(tableName,JSONaLOLorLOOobject) – as above but will insert/append the pass’d “rows” to the specified table.
- db.jsonSQLQuery(SQLSelectStatement,typeOfJSON) will return a table in List of Lists format if typeOfJSON = “LOL” or as a List of Objects if “LOO”.