Hi Timo, On Fri, 28 Mar 2008, Timo Baur wrote:
Hello Felix,
we here at DMON now try to feed first data into our Glue2.0 based DB. We have some issues regarding the unique IDs, while trying to model relationships between ComputingResources, Locations and AdminDomains.
What we - still unsucessfully - tried to do is:
REPLACE INTO Location (...) VALUES (...); SELECT @locationID:=id FROM Location WHERE localID="..."; REPLACE INTO AdminDomain (...,locationID) VALUES (...,@locationID);
mysql> SELECT uniqueID,locationID FROM AdminDomain; +--------------------------------------+------------+ | uniqueID | locationID | +--------------------------------------+------------+ | https://dmon-unic.fz-juelich.de:9115 | 197 | +--------------------------------------+------------+
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`GLUE20/AdminDomain`, CONSTRAINT `fk_AdminDomainlocationID` FOREIGN KEY (`locationID`) REFERENCES `Location` (`id`))
It seems to me that you didn't specify a ParentTypeID which must exist in the EntryTable when you do the REPLACE INSERT. This is mandatory to identify of which type the parentID is. That could cause the constraint error.I'll recheck the cases with the schema.
We are wondering if we really should ask the DB for the id in the RS or if there is another way to relate the tables by only using the unique id of the DS.
I also thought of dropping the numerical presentation of the id and instead using the document schema ID. But then table joins with several key elements would get quite complicated. Example : If you want to find the Endpoint which serves a certain Share then you would need the localId and parentID from the Share in the EndpointShareLNK table. With the current solution you only have one id you need to join. Another reason I have in mind is the fact when doing joins string comparison operations are more heavy-weight than number comparisons. Furthermore, modern database systems also support partitioning which is an effective way of speeding up search access in large datasets. However, MySQL for example supports currently (5.1) only the datatype integer. Generally, I do agree with you that this argumentation looses it importance if you have exactly one uniqueID for a table (Service, Endpoint, etc.). I am currently updating the schema to the latest (draft 33) of GLUE 2.0. There isn't much change for the main enties and Computing but for the storage part. I'll check again the 'numerical id issue' and sent you an update as soon as I have finished it. Cheerio, Felix