MySQL INSERT if col != ??
Posted: Thu Feb 08, 2007 4:24 pm
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):
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 ;