Sometimes, like Scott of the Antarctic, your data shaping activities may need to “go outside” of Excel and it ” may be some time” before you return. That’s primarily why the HAMMER.exe command-line version exists; data can be packed up via the DELEGATE or SUBMIT commands; picked up by external HAMMER processes (I often use JSDB’s Grid micro-messaging JavaScript toolkit to orchestrate such workflows, but perhaps now I’ll use this – Amazon’s latest gem - AWS SWF !); then returned safely (unlike Scott) to Excel for further processing.
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.
I’ve already added threading capability to HAMMER via the hammerThreadEnabled function and via “internal HAMMER threads” (using NET4.0′s latest parallel programming additions).
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)
- JAVASCRIPTTHREAD – as above, but using JAVASCRIPT plus no need for NET 4.0.
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”.
- DELETETHREAD – will attempt to terminate an already running thread. It will then, or if already terminated, clean-up and close any resources associated with the thread, mainly its database, but also its Python and/or JavaScript engine instances.
It’s also possible to issue a HAMMER call that will re-join a completed thread’s database, this will happen if a HAMMER function call’s 1st argument is a valid thread-name e.g. =HAMMER(“JSTHREAD2″,”Select * from results”,”SQL”). If the thread is still running, the HAMMER function will exit with a return value of “Waiting”. With this, it’s possible to not only access the thread’s database but also its Python and/or JavaScript engine context. Multiple such HAMMER calls can be made until a DELETETHREAD command is issued or the Excel session is terminated.
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.
- LISTCOMMAND – returns the list of commands available.
There’s also been a small breaking change to the JAVASCRIPT command. Scripts will now only return a table if the return value is a JavaScript Array of Arrays (List of Lists) object or a JSON string representing a LOL. All other objects will be converted to a JSON string representation or scalar string value.
A few extra JavaScript focused helper methods have also been added to the passed-in “db” object:
- 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”.
There’s also a new JavaScript command: loadAssembly(dotNetAssemblyName) – which will load a .NET assembly allowing the use of any .NET library within JAVASCRIPT.
Both the JAVASCRIPT and threading commands are at a very early stage, only minimal testing so far, so use with care.
To download the latest version of HAMMER, use this link.
