If you're a database user, you'll want to read this to learn more about what we can do for you. If you're a developer, you'll be interested in the Audit Log and the open source code!

Sometime in the near future, we'll add a web page specifically for Xanadu like we have for our FileMaker Template: FMSBFoundation. We wish we could make Xanadu Open Source. It's really amazing so far, but there are just too many bits of open source and purchased code from other developers. The great news is that everything we use in Xanadu can be included with the apps we develop. That means that if you need an app built, we can build it and you won't need to pay for all the add ons!

Xojo SQLite Audit Log

That said, we can make some things available as open source. We're incredibly appreciative of the Xojo community and love to give back when we can. So, we're making our Audit Log code that uses SQLite Triggers away!

The idea is simple. If you have a SQLite database all you need to do is add one table and one method to add SQLite Triggers to automatically create a log entry for each INSERT, UPDATE, and DELETE for the tables you care about. Check out the video below and the source code below. You'll see that it's really easy to implement.

Once you have it up and running, keep an eye on how much storage the LogAudit takes up. From the start, we wanted to store the Audit Log records in a separate but attached SQLite database file, but that didn't work as SQLite records that are inserted from triggers must be in the same file as the trigger. So, we plan to create a 'dbAuditLogTransporter' method to move the Log records to an attached SQLite database file. We'll make that available sometime, but we need to write it first!

The 'dbAuditLogTriggers' method is close to magical! The method will create three triggers for every table in your database file except for the tables you don't want to log! Our 'People_Insert_TG' looks like this:



While that looks like a hot mess, it's really simple. First we, define the trigger name and for this trigger, we want it to run AFTER INSERTS. Then between the BEGIN and END, we first update the record to set the Modification Timestamp and Modification Seconds expressed in a fraction of a second. Next, we add a log record to the LogAudit Table. Most of the trigger easy to read, but the value to be stored in the 'FieldValues' is messy. All it does is takes the field name and value and wraps them in an xml like tag. We mixed square and curly brackets on purpose to reduce the chance of a conflict with the embedded data.

Watch How it Works, Source Code Below!



Code

Automatically create Audit Log Triggers in your SQLite Database - Defining the Triggers for the Audit Log is the hard part, but this method makes it really easy! - Download

View the Audit Log in a HTMLViewer - We're showing the Audit Log on a WebDialog to be displayed as a sheet. Just add a sheet and drop a HTMLViewer on the sheet, add this method and call it when you want to see the Audit Log. - Download