MySQL INSERT if col != ??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

MySQL INSERT if col != ??

Post 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 ;
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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');
(#10850)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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!
DrTom
Forum Commoner
Posts: 60
Joined: Wed Aug 02, 2006 8:40 am
Location: Las Vegas

Post by DrTom »

Couldn't you make a unique key on the node/text, and then use INSERT IGNORE?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

DrTom: That seems really close to perfect - but how can I enforce unique `id`, `modified` and `wholeText` per `node`?
DrTom
Forum Commoner
Posts: 60
Joined: Wed Aug 02, 2006 8:40 am
Location: Las Vegas

Post by DrTom »

A multi column index maybe?
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

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