User:Lindenb/Notebook/UMR915/20100930
From OpenWetWare
ok, not anymore sure about mongodb, seems to spoilt a large place ($ref...) for some tiny data. Re-switching to mysql ?
Tables
families:
CREATE TABLE `families` ( `id` int(10) unsigned NOT NULL, `name` varchar(50) NOT NULL, `description` text, `meta` text, `creation` datetime default NULL, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
individuals:
CREATE TABLE `individuals` ( `id` int(10) unsigned NOT NULL, `name` varchar(50) NOT NULL, `father_id` int(10) unsigned default NULL, `mother_id` int(10) unsigned default NULL, `gender` tinyint(4) default NULL, `family_id` int(10) unsigned NOT NULL, `description` text, `meta` text, `creation` datetime default NULL, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `family_id` (`family_id`), CONSTRAINT `individuals_ibfk_1` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
retrieve data from mongo:
var id=1; var array=[]; function findIndiById(id) { for(var i=0;i< array.length;++i) { var indi= array[i]._id; //print("NOw "+indi); if(indi==id) return (i+1); } return "XXXXXXXXXXXXXXXXXXXXXXXXX"; } var c=db.individuals.find(); while(c.hasNext()) { var indi = c.next(); array.push(indi); } for(var i=0;i< array.length;++i) { var indi= array[i]; print("insert into individuals(id,name,gender,father_id,mother_id,family_id) values("+(i+1)+",\""+indi.name+"\","+indi.sex); if(indi.fatherId==null) { print(",NULL"); } else { print(","+findIndiById(indi.fatherId.fetch()._id)); } if(indi.motherId==null) { print(",NULL"); } else { print(","+findIndiById(indi.motherId.fetch()._id)); } print(","+indi.familyId.$id); print(");"); }