Mark, I agree with your concept. It'd be nice if the DBMS could enforce this for us. I hadn't even thought of creating a more complex constraint based on a larger query/view. Although it doesn't look "pretty", I'd be OK with it if we could ensure it'd get the job done (without a huge performance hit or anything). I doubt this is the only place we aren't properly enforcing constraints on our data at the DBMS level, but it would be nice to plug up one hole.
- Tim On Wednesday, October 26, 2011 4:10:37 PM, Mark H. Wood wrote: > CREATE VIEW BitstreamSequences AS > SELECT item_id, bundle_id sequence_id > FROM Item > JOIN Item2Bundle USING(item_id) > JOIN Bundle2Bitstream USING(bundle_id) > JOIN Bitstream USING(bitstream_id); > > CREATE CONSTRAINT ic_bitstream_sequences CHECK ( > EXISTS (SELECT * from BitstreamSequence AS bs1 WHERE > NOT EXISTS (SELECT select * FROM BitstreamSequences AS bs2 > WHERE bs1.item_id = bs2.item_id AND bs1.bundle_id != bs2.bundle_id > AND bs1.sequence_id = bs2.sequence_id) > ) > ); > > (I think -- not tested, and my SQL-fu is perhaps not yet that strong.) > >> All that being said, obviously Mark Wood found out that we really aren't >> properly checking that a sequence_id is unique per Item. All we are >> doing is attempting to ensure that, when you add bitstreams through the >> DSpace Java API (either via UI or CLI), each of those bitstreams will be >> assigned a sequence_id which is unique within its associated Item. >> >> But, if "bad data" gets in your DB somehow (by bypassing our DSpace Java >> API), we aren't warning the user or telling the user that his/her data >> is "invalid" (which is what Mark Wood discovered the hard way). > > The question, then, is whether to use the DBMS to check this stuff or > build something custom. I think that integrity checks need to be as > close as possible to the data, which suggests the DBMS. Looking at > the monstrosity I wrote above, I'm not so sure. :-/ > > > > ------------------------------------------------------------------------------ > The demand for IT networking professionals continues to grow, and the > demand for specialized networking skills is growing even more rapidly. > Take a complimentary Learning@Cisco Self-Assessment and learn > about Cisco certifications, training, and career opportunities. > http://p.sf.net/sfu/cisco-dev2dev > > > _______________________________________________ > Dspace-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/dspace-devel ------------------------------------------------------------------------------ The demand for IT networking professionals continues to grow, and the demand for specialized networking skills is growing even more rapidly. Take a complimentary Learning@Cisco Self-Assessment and learn about Cisco certifications, training, and career opportunities. http://p.sf.net/sfu/cisco-dev2dev _______________________________________________ Dspace-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-devel
