Page 1 of 1

Simple database "time machine" in php ?

Posted: Sat Jun 18, 2016 9:46 am
by jaunedoo
Hi All,

I am currently working on a personal project where I automatically transform a database description file to a sqlite database.
This works fine but this is very "basic": each time I modify the description file, I delete and recreate the database file (losing all my previous tests datas).

I want to improve the system in 2 ways:

1) keep as much as possible previous version data into the new database schema

2) be able to go back in time (restore a previous description file and database schema, but still keeping as much data as possible)

So just copying the description file + database file for each schema change is not an option here (because when doing a "go back" if I just restore the previous files I would lose any new data entered afterward).

Possible schema operations are:

* add table
* delete table
* rename table
* add field in table
* delete field
* rename field
* (eventually change field type, but this one is more tricky)

Do you have any idea/suggestion on how to do that with PHP in a "simple" way ? :)

Re: Simple database "time machine" in php ?

Posted: Sat Jun 18, 2016 10:02 am
by requinix
Find a way to describe every change you may want to make to the database with both a forward and a backward operation tha talso preserves as much data as possible. For example, adding a table "forward" is

Code: Select all

CREATE TABLE database.table(columns...)
and "backward" is

Code: Select all

DROP TABLE database.table
Now describe your entire database using those operations from scratch.

The problem is your need to preserve data. You can't actually delete stuff because you might need to undo it at some point. So either you never actually delete data (eg, dropping actually does a rename or create/insert) or you find another place to store the data where it can potentially be retrieved later.

Re: Simple database "time machine" in php ?

Posted: Sat Jun 18, 2016 10:28 am
by jaunedoo
Indeed the idea to define each forward/backward action and use those to create the database is a good one ! :D

Concerning "dropped" data I initially thought to rename the table with some unique name (to avoid conflicts with existing real tables) and keep it in the same database.

But I don't know if it will resist some repetitive back/forwards ...