Hybrid Static/Dynamic content with SQLite
I’ve been reading Deane’s posts on Gadgetopia a lot recently and one of them refers to the notions of ‘ Data Push’ and ‘Template Pull’ in terms of how content gets to the page driven by a CMS. This was an interesting post for me as I’ve recently found that a combination of these models provided a solution to a problem I was having.
As part of my work for Golley Slater Digital I’ve been architecting a PHP5-based CMS that uses the ‘Push’ model where content authored on a private ‘admin’ server is published to a public ‘live’ server to be viewed by end-users. Essentially, the CMS stores content in MySQL, writing it out as a hierarchy of static HTML (well PHP really) pages when the user elects to publish. These static pages, along with the other assets are ‘pushed’ live using RSync which copies them from one server to the other.
In addition to regular page content, there are other types of content such as ‘Collections’ that are used in the CMS and that need to be copied to the live server. A Collection is a list of structured data types, Collection Items, that are managed independently from page content. Collection Items have their own approval workflow, go-live dates etc. and can be bound to pages through the admin UI. Typically, Collections are used for News, Events, FAQ’s etc. but really any content type that can be modeled with a simple structure is a candidate.
Now up to last month I had stored published Collection Items in XML which was parsed at runtime on the live site (The XML would get conveniently pushed live with RSync along with the pages). With PHP 5’s improved XML support this was a reasonably performant solution and provided a basic storage mechanism for the structured data. But what if I had thousands of items in a Collection and wanted to perform a complex search to display the results over a few pages? Hmmmm. It was achievable with XML but was going to get tricky with XPath expressions and server resources would definitely suffer when parsing the XML and sorting arrays of data. I needed another way.
Enter SQLite! This database engine is embedded into PHP5, and was the answer to my woes. SQLite databases require no setup, passwords and are nothing more than regular files so they play very nicely with the RSync deployment scheme I use. On top of that since the vast majority of SQL can be used to query SQLite databases, searching and paging of results was going to be a breeze. So I made a change to the CMS that saw published collection items going into a local database rather than the XML.
Using SQLite means that the CMS can effectively queue-up content ahead of a display date and expire content at some point in the future. What’s even better is that the published sites are still standalone in nature and do not need any knowledge of the CMS which created them.
O.k. so using SQLite is nothing groundbreaking but the application of it in this context made me stop thinking of a database as something at the bottom of a technology stack and as an portable and intelligent file format that can be shared between applications. Revelation!
If you’re planning a CMS then I recommend you look at SQLite for creating hybrid static/dynamic pages. It certainly opened some doors for me.
Disclaimer: Yes, I used the word ‘performant’ in this post. Apparently it’s not a word but I like it. So Sue me.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
[...] And so, the XML simply defines a form to be rendered but also, the fact that we have a record of the structure of the stored content means that we can use this to publish content from the database into HTML (by means of templating), XML (by using the ‘id’ attribute of each property node as the tag names) and even to an SQLite database. Since SQLite supports only a few simple data types I was able to simply map the property types (taking into account their validation rules) and create an SQLite database for each Collection on the fly. This proved to be an ideal solution for reasons I’ve mentioned before. [...]