Page 1 of 1

MySQL INSERT if col != ??

Posted: Thu Feb 08, 2007 4:24 pm
by Kieran Huggins
I'm no MySQL genius by any means, but I know many of you fine people are ;-)

I know this is a long, scary post - but you really don't need to read the whole thing!

Currently I've built a system where a DOM (xml) document is broken down into it's base components and saved in a MySQL DB.

I'm planning on keeping a complete history in the database, updating `elements`, `attributes` and `text` by simply adding a new row and only returning the latest row when selecting them by node (every element, attribute and text IS a node in XML). I mark nodes as deleted with a timestamp. That way I can specify a "point in the past" and get the exact data set for that time period.

Right now, when "saving" an element, attr or text, I have to first select it, compare it's contents, then finally write a new row if it's been updated. This seems like something MySQL should be doing to save time.. can anyone show me how?

Thanks guys!


v---------- stuff you might want to know ----------v

I have 4 tables (nodes, elements, attributes and text):

Code: Select all

CREATE TABLE `nodes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parentNode` int(11) default NULL,
  `created` int(11) NOT NULL,
  `deleted` int(11) default NULL,
  `nodeType` tinytext NOT NULL,
  `thing` int(11) default NULL,
  `token` tinytext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE `elements` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `node` int(11) NOT NULL,
  `modified` int(11) NOT NULL,
  `name` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE `attributes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `node` int(11) NOT NULL,
  `modified` int(11) NOT NULL,
  `name` tinytext NOT NULL,
  `value` tinytext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE `text` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `node` int(11) NOT NULL,
  `modified` int(11) NOT NULL,
  `wholeText` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

Posted: Thu Feb 08, 2007 4:50 pm
by Christopher
I think you can do it without fetching if you update before you insert:

Code: Select all

// archive any active records for this node
UPDATE table SET modified=NOW() WHERE node='$node' AND modified=NULL;
// insert new active node
INSERT INTO table (node, name) VALUES ('$node', '$name');

Posted: Thu Feb 08, 2007 5:12 pm
by Kieran Huggins
Thanks arborint,

I'm not sure that will get the results I'm looking for, since I actually want to leave the last record untouched. I want to add a new record IFF the last record is different from what the new record would be (with the exception of `id` and `modified` which are only set when the row is created)

so the `text`table would look like this:

Code: Select all

id        node         modified           wholeText
325     530           0187632541      this is my text...
326     530           0187650680      this is my *shiny new* text...
There would be no INSERT if the `wholeText` field remains unchanged, otherwise the new row is inserted but all previous rows remain untouched.

Does that make sense?

Posted: Thu Feb 08, 2007 5:44 pm
by Christopher
It seems like you could handle that in your controller as you need to load the record to edit it anyway. Just do nothing if there are no changes rather trying to do some database shenanigans.

Posted: Fri Feb 09, 2007 12:32 am
by Kieran Huggins
I'm actually loading into the DOM from a file cache - but I'll try to apply the same principle and see how it works.

Thanks!

Posted: Fri Feb 09, 2007 1:29 am
by DrTom
Couldn't you make a unique key on the node/text, and then use INSERT IGNORE?

Posted: Fri Feb 09, 2007 2:42 am
by Kieran Huggins
DrTom: That seems really close to perfect - but how can I enforce unique `id`, `modified` and `wholeText` per `node`?

Posted: Fri Feb 09, 2007 8:19 am
by DrTom
A multi column index maybe?

Posted: Fri Feb 09, 2007 1:01 pm
by daedalus__
Kieran Huggins wrote:DrTom: That seems really close to perfect - but how can I enforce unique `id`, `modified` and `wholeText` per `node`?
You could use PHP for that?