Simple database "time machine" in php ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jaunedoo
Forum Newbie
Posts: 2
Joined: Sat Jun 18, 2016 9:14 am

Simple database "time machine" in php ?

Post 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 ? :)
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Simple database "time machine" in php ?

Post 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.
jaunedoo
Forum Newbie
Posts: 2
Joined: Sat Jun 18, 2016 9:14 am

Re: Simple database "time machine" in php ?

Post 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 ...
Post Reply