IIL (Internal Interpretive Language)
In order to understand the IIL it is helpful to understand the motivation that I had to create the IIL.
Initially the SQLite Utility was created as a SQL database browser.
So I did not really need an internal interpretive language.
CVS - extract from Excel:
But then my work involved me in a lot of Excel analysis. I decided that some of the analysis would be easier/quicker if I could get the data into an SQL database. But I did not have a SQL capability on my company’s laptop. So I decided to enhance my SQL browser with the ability to import a csv (comma separated variable) file.
I would load the Excel file to Excel, and then export the required worksheet as a csv file. Then I could load the csv file to the enhanced SQL browser. Since the SQL browser needed no installation, I could run the SQL browser on my company’s laptop without administrator rights.
CVS - return to Excel:
This really did help! But then I started loading multiple csv files to the enhanced browser and started to modify the data in the browser. At that point it stopped being a SQL browser and was renamed SQLite Utility.
Now I was faced with a different issue. My colleagues did not have SQL either, so to share my analysis I needed to export the SQL tables as csv files, which I could then load in to Excel, and save as an Excel file(s) which I could give to my colleagues.
See - CSV - commands to import/export data in the comma separated variable format.
HTML- Flexible display
On some occasions I shared the analysis in a web based video conference, so I was showing the SQLite Utility GUI. At other times I only wanted to extract a screenshot of the results of a SQL Select, and say embedded it in a e-mail.
I soon realised that the standard Windows based listview that I used for holding the results of an SQL Select was not always convenient.
So I added the option to display the results of an SQL Select as a HTML table. I also included the ability to increase/decrease the HTML text font size. I added a few gimmics such as allowing the HTML table to be in its own window. This meant that I could have the output window on the “beamer” whilst the SQLite Utility GUI window was displayed on a second monitor/screen, so the audience could focus on the results without being distracted by the application.
See - HTML - commands to export the results of an SQL SELECT statement to a HTML file, and to insert HTML commands / text into that HTML file.
“Go faster” tools
I had by this point got fed-up of having to retype all of the various commands to achieve an analysis, so I had created a batch mode input i.e. I could place all of the commands in to a text file and simply tell the SQLite Utility tool to take its input from the text file.
I also added in some commands that were effectively extensions to the SQLite SQL language. For example SQLite does not have a command to rename a column in a table. So I created commands in the IIL that, using the SQLite database, could: rename a column, remove a column, change the type of a column,.... (See the SQL section for details).
I also realised that I needed the ability to do conditional command execution e.g. execute the following lines if a certain condition is true. So I extended the IIL to support a simple IF / ELSE /THEN construct.
Since then, the IIL has been enhanced to support: functions, subroutines, WHILE/WEND, BEGIN/LOOP, global variables, local variables, optional arguments,...
See - Conditionals - commands to control the forward (IF/ELSE/THEN ) and backwards (WHILE/WEND, BEGIN/LOOP) execution of commands.
See - Macros - commands to provide functions and subroutines.
The IIL has a syntax that is broadly similar to the syntax of Microsoft’s Visual Basic.
But IIL is not a direct substitute for Microsoft’s Visual Basic or for Microsoft’s Visual Basic for Applications.
Last summer (2015) I was getting fed-up of using csv files as the interface to Excel. Ideally I wanted to import/export directly to Excel files, but I think that trying to read/write XLS, XLSM, XLSB files is too difficult (or atleast I cannot motivate myself to do it), and to be honest I do not fancy trying to handle files in Open Document Format.
So I decided to take a different approach!
Fundamentally, Excel allows a third party application to use OLE techniques to automate Excel.
So, I extended the IIL to include interfaces to the automation API interfaces such as: GetIDsOfNames, Invoke. The user does not “see” these interfaces, but it enables the IIL to accept text that identifies objects and methods that are made available by automation compatible applications - such as Excel.
The OLE Automation interface is not limited to Excel, it can be used with any Windows application that supports OLE Automation.
See Automation - commands to control an 3rd party application that conforms to OLE automation e.g. Microsoft Excel / Powerpoint / Word...
I have created a small set of commands to support the exchange of a worksheet/table between a running Excel application and SQLite Utility . These commands are wrappers around the OLE Automation API.
See Excel - commands to move “tables” between SQLite Utility and a running Excel application.