The other week, I wrote about introducing SQLite support to Known.

Seemingly, I was on a role, and because a client asked me to look in to Postgres support for a separate project, I thought I’d have a crack at implementing support for Known, which I’m glad has been merged in.

This turns out to have been useful to a bunch of folk, for which I am glad.

Installation

Postgres support is still in Beta, and MySQL is still the recommended database engine, so currently installation is a bit of a manual process.

First, you need to make sure you’ve got Postgres support built in to PHP:

Then, you need to modify your config.ini

Once you’ve done that, you need to install the schema, which currently needs to be done from the command line.

Assuming you got no errors, you should now be good to go!

SQLite is a SQL powered relational database that is implemented using a file on disk, rather than a client server. This means that, among other things, you don’t need a standalone process to run a database, and it also makes backups easier.

A few weeks ago, support for sqlite landed into Known.

I put together this patch primarily to scratch my own itch; I was having to set up a lot of Known based for testing as part of client projects, and I needed a way to quickly swap around configuration and data sets. I also figured it’d help out the number of folk out there who run their Known sites on devices like the Raspberry Pi.

Usage

Firstly, make sure you’ve got SQLite support built in to php:

Then, you need to modify your config.ini

The first two lines tell Known to use SQLite for the backend database, and where this database lives. Note, that this location needs to be writeable by your web server user (usually www-data) but, for obvious security reasons, should be somewhere outside of your Known docroot, so that it can’t be downloaded!

The last two lines tell Known to use a disk based file store for pictures and other uploads. This is important as the SQLite engine currently doesn’t support file storage in database like the Mongo engine does.

That’s it! Fire up your browser and visit your site. All being well, Known will automatically create the database and set up the schema.

Bug reports and patches to the usual place!

If you’re anything like me, you often find yourself needing to add create and modified timestamps to data stored in a MySQL database. Typically, I’d do this programmatically from within the application without giving it a second thought, wrapping up time(); in an SQL statement and firing it over.

Recently, while doing some development work for one of my clients, I was given the requirement to add Created and Modified timestamps to a whole bunch of existing data tables. These tables were referenced by hundreds of different MySQL queries, and to complicate matters further, we were in the middle of migrating the code over to a new database library.

I didn’t much fancy rewriting all of the SQL queries, potentially twice, just to add a couple of timestamps, so modifying code wasn’t much of an option. Thankfully, a couple of MySQL’s internal features came to the rescue.

Adding a ModifiedTime

Adding a modified timestamp to a table is the most straight forward. All your have to do is create the field of type TIMESTAMP, and by default, MySQL will automatically update the field when the row is modified.

There are a couple of things to be aware of:

  • While you can have multiple TIMESTAMP fields in a row, only one of these can be automatically updated with the current time on update.
  • If your UPDATE query contains a value for your ModifiedTime field, this value will be used.

So, to add your modified timestamp field to an existing table, all you need is:

ALTER TABLE my_table ADD ModifiedTime TIMESTAMP;

Adding a CreatedTime

Adding a CreateTime value is a little more involved.

On the latest versions of MySQL it is apparently possible to create a DateTime field with a default value of CURRENT_TIMESTAMP. This wasn’t an option for me as I was having to support a somewhat older version, besides, even on the newer versions of MySQL it is not possible to have more than one field using CURRENT_TIMESTAMP, which of course we are in order to get ModifiedTime working.

So, in order to get a created timestamp, firstly we must add a DATETIME field to the table.

ALTER TABLE my_table ADD CreatedTime datetime NOT NULL;

Note, that this must be created as NOT NULL in order for the next part to work (this is because setting NOT NULL forces an automatic all zeros default).

Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.

DELIMITER //
DROP TRIGGER IF EXISTS my_table_insert_trigger//
CREATE TRIGGER my_table_insert_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN
SET NEW.CreatedTime = NOW();
END IF;
END;//
DELIMITER ;

Now, when you insert a value into the table, this trigger will fire and, if you’ve not provided a CreatedTime field in your insert query, it will be set to the current time stamp.

Conclusion

Since all the queries in the application code specified the columns they were updating on insert, I was able to use this method to add created and modified time stamp fields to all the existing object tables in the database, without needing to modify any of the existing application code.

This simplified my life greatly, but also suggests to me that this method might be somewhat more efficient than the methods I’d previously used.