Snapshot using insert ignore pattern.
16/11/10 18:22
I’ve just had to implement a scenario where I have
some data entered by an administrator and the same
data can be maintained by the user. I don’t want to
loose the data entered by the administrator and want
her to be able to re-edit the data and overwrite what
the user has entered.
To do this I created a backup table of the table where the user got its data, with some minor modifications:
create table if not exists XXX_person (
id INT(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
...
);
create table if not exists XXX_person_backup (
id INT(11) unsigned NOT NULL PRIMARY KEY,
...
);
The backup table contains all the fields I want to create a snapshot of (in my case not all). Besides that they are similar, except that the XXX_person is the table generating the primary keys.
In my usage the administrator is always creating the first row in XXX_person.
For the update for the user, I can simply do:
"insert ignore into XXX_person_backup (id,firstname,lastname,...)
(select id,firstname,lastname,...from XXX_person where id = ?)"
This way the first update will populate the backup table, and subsequent updates will not as they will silently be ignored due to the primary key constraint. Then if the administrator wants to accept the users change, we just delete the backup entry.
This was for MySQL, but most databases got a similar feature in their insert statements.
To do this I created a backup table of the table where the user got its data, with some minor modifications:
create table if not exists XXX_person (
id INT(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
...
);
create table if not exists XXX_person_backup (
id INT(11) unsigned NOT NULL PRIMARY KEY,
...
);
The backup table contains all the fields I want to create a snapshot of (in my case not all). Besides that they are similar, except that the XXX_person is the table generating the primary keys.
In my usage the administrator is always creating the first row in XXX_person.
For the update for the user, I can simply do:
"insert ignore into XXX_person_backup (id,firstname,lastname,...)
(select id,firstname,lastname,...from XXX_person where id = ?)"
This way the first update will populate the backup table, and subsequent updates will not as they will silently be ignored due to the primary key constraint. Then if the administrator wants to accept the users change, we just delete the backup entry.
This was for MySQL, but most databases got a similar feature in their insert statements.
|