Hello Jeroen,
On 10 November 2011 09:37, Jeroen De Dauw jeroendedauw@gmail.com wrote:
How would you go about creating a generic solution for storing revisioned configuration?
The term commonly used for this is a database audit trail. Of course, mediawiki already implements this for one table (Page, with Revision as audit table). In general, I think the important thing to keep in mind is that you want a log of what happens - you could even consider dumping it to a text file, for instance. You would need to revert changes manually, but it's still better than nothing.
In general, I think there are three options that are in general use (when storing the data in a database): a temporal database, an oldversion table, or a changes table. The last two can be in two forms: one for the entire database, storing data as JSON or XML, or with one audit table per 'real' table).
A temporal database just adds two columns: valid_from, valid_to, and maybe another to actually store data on who changed data. If data is changed, the old value get valid_to set to the current date & time, and the new value has valid_from set to the current date & time.
An oldversion table essentially is mediawiki is doing for Page objects: you have a list of 'revisions' of the objects with related information. Page/Revision is an example of having one audit table for one real table. The JSON alternative is (in terms of database design) ugly, but it means you don't have twice the number of tables floating around.
The last option is storing 'actions': essentially storing the SQL queries that are run on the table. You would thus have an entry 'INSERT' with certain values, 'UPDATE', and 'DELETE'. Again, you can use this in a nice database form or a serialized form.
On the question of which one is the best to use in this context -- I don't know. In general, I think it makes some sense to use the tools you already have (i.e. store it in page text), but as you noted this is inconvenient if you don't want configuration public.
Anyway, I hope this was sort of useful, at least to help you find more information.
Good luck, and best regards, Merlijn van Deen