Last month I was poking around the SQLite docs and came across three things that got me thinking about SQLite as a data exchange format.
Long Term Support: They are aiming to “support SQLite through the year 2050”. Obviously this isn’t a guarantee, unforeseen things can ( and do ) always happen. What this does point to is the aim of their thinking: long term. That leads to making decisions with strong consideration for compatibility issues.
Database File Format: Documentation for the database files themselves. Writing docs is usually at the bottom of the list, so it is nice to see this effort.
US Library of Congress: SQLite is listed as one of the recommended dataset formats by the US Library of Congress. Always nice to see a recommendation from an entity who is in the business of preserving things for the long term. I suspect having SQLite in the public domain helps.
All of this had me wondering about the potential benefits of using SQLite database files for exchanging datasets, especially for importing and exporting.
A couple of things would be very helpful for forward and backwards compatibility. Using VIEWs would make it possible to change the underlying table structures while exposing different versions of that data. You might have a VIEW for each major version of your data. Then code for processing could always deal with the VIEW version it understood.
Even without VIEWs, adding data can be done by adding more tables. If your code doesn’t know anything about that new data yet, no problem, it never queries those tables.
If I were to replace the WordPress import/export format ( WXR ), which is lots of XML, I’d give consideration to SQLite as an option. It is already strongly related to the MySQL tables it uses. Some WordPress plugins create their own tables, and those would be easy to include as well.
I definitely have a soft spot in my heart for plain text, but I can’t deny a certain attraction to SQLite for exchanging data.