MoneyWorks

MoneyWorks 9.2 introduces bindings to the Sqlite3 API. This means you can:

• Create and use temporary in-memory sqlite databases in MWScript for data analysis and tabulation (as a more powerful alternative to TableAccumulate etc)
• Create and/or open local sqlite databases from MWScript on a MoneyWorks client for storing client-local data, or for importing data from a sqlite.db
• Create and use shared sqlite databases that reside on a MoneyWorks Datacentre server and access the data from all MoneyWorks clients. This allows MWScript to be used to create bespoke databases and user interfaces for them within MoneyWorks

Sqlite3 databases thus created are entirely independent of the MoneyWorks database.

The three sqlite database functions in MWScript are Sqlite3_Open, Sqlite3_Close, and Sqlite3_Exec.

Sqlite3_Open

let dbref = Sqlite3_Open(databaseFileName)

Opens a connection to a new or existing database.

• Temporary in-memory sqlite database

  You can create a temporary database (that will be discarded when you close it) by using Sqlite3_Open("") or Sqlite3_Open(":memory:"). You can run queries like CREATE TABLE, INSERT INTO, and SELECT. The results of SELECT will be tab and newline delimited records that you can further process with Slice/Dice etc. Sqlite3_Close will discard the database.

• Client-local sqlite database file access

  Using Sqlite3_Open("filename.db") will open an existing—or create a new—database file in the Automation Files/Sqlite directory of the MoneyWorks client. Using Sqlite3_Open("/full/path/to/filename.db") will open an existing—or create a new—database file on the local client, provided that the path is allowed by the safe scripting paths in the app preferences. You can call Sqlite3_Open on the same file more than once to get additional connections to the database file (this might happen if different scripts access the same database for example). You need to close each handle thus allocated when you are finished with it.

• Shared sqlite databases

  Using Sqlite3_Open("shared:filename.db") or Sqlite3_Open("shared-public:filename.db") will open an existing—or create a new—database file in the MoneyWorks Custom Plugins/Sqlite directory on the MoneyWorks Datacentre Server (or, if you are not connected to a server, in the MoneyWorks Custom Plugins/Sqlite directory for the MoneyWorks file you have open (this mode will fail if running a MWScript outside of a MoneyWorks document). All MoneyWorks files that share the custom plugins folder can access the same .db file. Using Sqlite3_Open("shared-private:filename.db") will open an existing—or create a new—database file in the MoneyWorks Custom Plugins/Pictures/company-name/Sqlite directory on the MoneyWorks Datacentre Server (or, if you are not connected to a server, in the MoneyWorks Custom Plugins/Pictures/company-name/Sqlite directory for the MoneyWorks file you have open. Only that company file will have access to such .db files. Note that in this case the sqlite .db will be backed up with the Pictures folder (may be a separate backup depending on server prefs).

  A script running on multiple logged-in clients can access the same sqlite database simultaneously.

  For extra robustness, write-ahead logging mode can be used, with:

  Sqlite3_Exec(dbref, "PRAGMA journal_mode=WAL;")
  

  The MoneyWorks Custom Plugins/Sqlite directory will be backed-up as part of the normal Datacentre backup rotation regime.

• Shared-but-ephemeral in-memory databases in MoneyWorks Datacentre

  It is also possible to share data between clients using an in-memory database on the server, using a filename in the form: "shared:file:MyMemDBName?mode=memory&cache=shared". An ephemeral database like this will exist as long as at least one client has not yet closed its connection.

Sqlite3_Close

Closes the connection to the database. You should always do this when finished with querying the database.

Sqlite3_Close(dbref)

Sqlite3_Exec

Executes a SQL query on the database. Any SQL supported by Sqlite3 may be used (CREATE TABLE, INSERT, SELECT, UPDATE, PRAGMA, etc)

let result = Sqlite3_Exec(dbref, query, [bindargs...])

The Sqlite3_Exec function allows parameter binding, binding the values passed in as additional parameters into the query wherever it contains a ?. This removes the need to deal with escaping special characters in the data to avoid accidental SQL injection from user-supplied data.

e.g.:

let n = "Some_val"
let id = 123
let sub = Sqlite3_Exec(s, "SELECT subject from templates WHERE name = ? and id = ?;", n, id)

Code sample

on Load
    let s = sqlite3_open("") // temporary in-memory  DB
    let r = sqlite3_exec(s, "CREATE TABLE cars ( make TEXT, model TEXT, colour TEXT, registration TEXT, mileage REAL);")
    let sql = "INSERT INTO cars VALUES ( 'Subaru', 'Outback', 'Gold', 'XYZ999', 36000);
               INSERT INTO cars VALUES ( 'Toyota', 'Corolla', 'White', 'ABC123', 46899);
               INSERT INTO cars VALUES ( 'BYD', 'Shark', 'Grey', 'ZZZ000', 1);"
    let r = sqlite3_exec(s, sql)
    let r = sqlite3_exec(s, "SELECT * FROM cars ORDER BY mileage DESC;")
    syslog(r)
    sqlite3_close(s)
end

Output format

By default, the output format is the same as the Sqlite default format: tab separated column values and newline terminated rows. You can modify the output format mode using a .separator directive.

.separator colsep opt_rowsep

Specify the column separator string and optionally the row terminator string. The parameters can just be space separated or you can optionally quote them with single or double quotes. Do not append a semicolon.

Examples:

   Sqlite3_Exec(s, ".separator |") // just set the column separator
   Sqlite3_Exec(s, ".separator '\t' 'END'") // set both separators
   Sqlite3_Exec(s, ".separator + END")  // set both separators
   Sqlite3_Exec(s, ".separator \t \n") // set to defaults
   Sqlite3_Exec(s, ".separator ''") // set column separator to empty string
   Sqlite3_Exec(s, ".separator '' ''") // no separators
   Sqlite3_Exec(s, ".separator '\t' '\n'") // set to defaults