Collection types suck?

There's no way to define foreignkeys on SET?? (There's probably a _very_ good reason for this, but it sucks anyway...) Am I supposed to use triggers for INSERT, UPDATE and DELETE? -And write Function-based indexes for integrity? I guess there's something I don't understand? Thomas

Thomas Parsli wrote: > There's no way to define foreignkeys on SET?? Could you explain this further? > > > (There's probably a _very_ good reason for this, but it sucks anyway...) > > Am I supposed to use triggers for INSERT, UPDATE and DELETE? > -And write Function-based indexes for integrity? > > I guess there's something I don't understand? > > Thomas

Thomas Parsli wrote: > There's no way to define foreignkeys on SET?? > > (There's probably a _very_ good reason for this, but it sucks anyway...) > > Am I supposed to use triggers for INSERT, UPDATE and DELETE? > -And write Function-based indexes for integrity? Well, the thinking goes like this. Referential integrity constraints are relational schema (table) concepts. COLLECTIONs are simply non-first normal form data types (for columns). If you want to take advantage of referential integrity, then using the traditional normalized approach is appropriate. That is, create another table that stores the PK of the originak table and the value of the COLLECTION. Then you can define TRIGGERS, constraints and so on to your hearts content. The pragmatic answer is that things like foreign keys are checked by the code that handled rows and table, not by the code that handled column types. Adding "let's check for any constraints on COLLECTIONS in rows if there are any" would mean that the engine would need to check every type for every column in a table. That said, in many situations you can achieve the same kind of data integrity without using referential integrity. The very best use of COLLECTIONS is when you want to store a small group of values from some small domain. Say, clothing sizes, movie genres, states, star-signs, that kind of thing. Now what you can do in the ORDBMS is to create a data type that maps to that domain, and implement the type so that it does all of the "integrity checking" you need. Rather than having a table for "months of the year" or "days of the week" to ensure that the values in a particular column are all valid, you can create an enumerated type instead. i.e. CREATE DISTINCT TYPE Star_Sign AS VARCHAR(12); GRANT USAGE ON TYPE Star_Sign TO PUBLIC;-- CREATE FUNCTION Star_Sign ( Arg1 lvarchar ) RETURNING Star_Sign DEFINE nvlInternal VARCHAR(12); LET nvlInternal = UPPER ( SUBSTR(Arg1,1,11) ); IF ( NOT ( nvlInternal IN ( 'ARIES', 'TAURUS', 'GEMINI', 'CANCER','LEO', 'VIRGO', 'LIBRA', 'SCORPIO', 'SAGITTARIUS', 'CAPRICORN','AQUARIUS', 'PISCES' ))) THEN RAISE EXCEPTION -746, 0, "ERROR: Unknown Name of Zodiac Sign"; END IF; RETURN nvlInternal::Star_Sign; END FUNCTION; GRANT EXECUTE ON FUNCTION Star_Sign ( lvarchar ) TO PUBLIC;-- CREATE IMPLICIT CAST ( LVARCHAR AS Star_Sign WITH Star_Sign ); -- -- So far, I have created an enumerated type. Whenever you use this new type -- in the database, the only way to get an instance of it (the only way to -- construct a value instance) is to go through the Star_Sign() UDF shown -- above. -- -- How do you use this? Well, to give you a giggle, here is another new type -- that figures out Birthdays. (If you want to know why this is useful, consider -- what happens to those unfortunate souls born 02/29 of a leap year if all you -- do is to extract day/month and compare using that!) -- CREATE ROW TYPE BirthDay ( MMonth INTEGER NOT NULL, DDay INTEGER NOT NULL, FromLeapYear BOOLEAN NOT NULL ); GRANT USAGE ON TYPE BirthDay TO PUBLIC;-- CREATE FUNCTION IsLeapYear( Year INTEGER )RETURNS boolean IF ( YEAR <= 0 ) THEN RAISE EXCEPTION -746, 0, "IsLeapYEar: Negative Year Value"; END IF; IF ( MOD( Year, 4 ) = 0 ) THEN IF ( MOD ( Year, 100 ) = 0 ) THEN IF ( MOD ( Year, 400 ) = 0 ) THEN RETURN 't'::boolean; END IF; RETURN 'f'::boolean; END IF; RETURN 't'::boolean; END IF; RETURN 'f'::boolean; END FUNCTION; GRANT EXECUTE ON FUNCTION IsLeapYear( INTEGER ) TO PUBLIC;-- CREATE FUNCTION BirthDay ( Arg1 date ) RETURNING BirthDay DEFINE nYear INTEGER; LET nYear = YEAR(Arg1); RETURN ROW (MONTH(Arg1), DAY(Arg1),IsLeapYear(nYear))::BirthDay; END FUNCTION; GRANT EXECUTE ON FUNCTION BirthDay ( date ) TO PUBLIC;-- -- OK. Now for some fun. -- CREATE FUNCTION BornUnder ( Arg1 BirthDay, Arg2 Star_Sign ) RETURNING BOOLEAN IF ( (( Arg2::lvarchar = 'AQUARIUS' ) AND ( Arg1 >= BirthDay(1, 20) AND Arg1 <= BirthDay(2, 18))) OR (( Arg2::lvarchar = 'PISCES' ) AND ( Arg1 >= BirthDay(2, 19) AND Arg1 <= BirthDay(3, 20))) OR (( Arg2::lvarchar = 'ARIES' ) AND ( Arg1 >= BirthDay(3, 21) AND Arg1 <= BirthDay(4, 19))) OR (( Arg2::lvarchar = 'TAURUS' ) AND ( Arg1 >= BirthDay(4, 20) AND Arg1 <= BirthDay(5, 20))) OR (( Arg2::lvarchar = 'GEMINI' ) AND ( Arg1 >= BirthDay(5, 21) AND Arg1 <= BirthDay(6, 20))) OR (( Arg2::lvarchar = 'CANCER' ) AND ( Arg1 >= BirthDay(6, 21) AND Arg1 <= BirthDay(7, 22))) OR (( Arg2::lvarchar = 'LEO' ) AND ( Arg1 >= BirthDay(7, 23) AND Arg1 <= BirthDay(8, 22))) OR (( Arg2::lvarchar = 'VIRGO' ) AND ( Arg1 >= BirthDay(8, 23) AND Arg1 <= BirthDay(9, 22))) OR (( Arg2::lvarchar = 'LIBRA' ) AND ( Arg1 >= BirthDay(9, 23) AND Arg1 <= BirthDay(10, 22))) OR (( Arg2::lvarchar = 'SCORPIO' ) AND ( Arg1 >= BirthDay(10, 23) AND Arg1 <= BirthDay(11, 21))) OR (( Arg2::lvarchar = 'SAGITTARIUS' ) AND ( Arg1 >= BirthDay(11, 22) AND Arg1 <= BirthDay(12, 21))) OR (( Arg2::lvarchar = 'CAPRICORN') AND (( Arg1 >= BirthDay(12, 22) AND Arg1 <= BirthDay(12, 32)) OR ( Arg1 >= BirthDay(1, 1) AND Arg1 <= BirthDay( 1, 19)))) ) THEN RETURN 't'::boolean; END IF; RETURN 'f'::boolean; END FUNCTION; GRANT EXECUTE ON FUNCTION BornUnder ( BirthDay, Star_Sign ) TO PUBLIC;-- CREATE FUNCTION BornUnder ( Arg1 DATE, Arg2 Star_Sign ) RETURNING BOOLEAN RETURN BornUnder ( BirthDay ( Arg1 ) , Arg2 );END FUNCTION; GRANT EXECUTE ON FUNCTION BornUnder ( DATE, Star_Sign ) TO PUBLIC;-- -- -- So somewhere, you all have a table that looks like this: -- CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, Name VARCHAR(32) NOT NULL, DOB DATE NOT NULL );-- -- So now, you can do this! -- SELECT C.Name, S.Sign FROM Customers C, TABLE(SET{ 'ARIES', 'TAURUS', 'GEMINI', 'CANCER','LEO', 'VIRGO', 'LIBRA', 'SCORPIO', 'SAGITTARIUS', 'CAPRICORN','AQUARIUS', 'PISCES'}::SET( Star_Sign NOT NULL)) S ( Sign ) WHERE BornUnder ( C.DOB, S.Sign ); Data mining question: any differences among members of the various star signs? So, this will help you where you have, for example, lots of small tables containing codes that aren't very frequently changed or updated. When you want to add a new code, you simply drop and re-create a single "gate

Paul Brown <paul.NOSPAM.brown@informix.com> writes: > Thomas Parsli wrote: > > > There's no way to define foreignkeys on SET?? > > > > (There's probably a _very_ good reason for this, but it sucks anyway...) > > > > Am I supposed to use triggers for INSERT, UPDATE and DELETE? > > -And write Function-based indexes for integrity? > > Well, the thinking goes like this. > > Referential integrity constraints are relational schema (table) concepts. > COLLECTIONs are simply non-first normal form data types (for columns). If > you want to take advantage of referential integrity, then using the > traditional normalized approach is appropriate. That is, create another > table that stores the PK of the originak table and the value of the > COLLECTION. Then you can define TRIGGERS, constraints and so on to your > hearts content. I've thought of this, but it creates a lot of code/tables/work -and I'm lazy (aren't we all?). I'd like to keep this as simple as possible... This is (more or less) a simplified example of what I'm trying: CREATE TABLE category ( id SERIAL PRIMARY KEY, name VARCHAR(255) ) CREATE TABLE item ( id SERIAL PRIMARY KEY, categories SET (INTEGER NOT NULL) -- belonging to ) Now this would be cool: ALTER TABLE l ADD CONSTRAINT ( FOREIGN KEY (categories) REFERENCES category (id) ); I'd even accept a degraded performance on INSERT, UPDATE and DELETE on tables usings/reffering to these types:) [SNIP] > Hope this helps! To some extent it does:) Thanks Paul! -I'll keep my nose clean and stay away from Oracle if you guys keep up the good work. Thomas

> > Referential integrity constraints are relational schema (table) concepts. > > COLLECTIONs are simply non-first normal form data types (for columns). If > > you want to take advantage of referential integrity, then using the > > traditional normalized approach is appropriate. That is, create another > This is (more or less) a simplified example of what I'm trying: > > CREATE TABLE category > ( > id SERIAL PRIMARY KEY, > name VARCHAR(255) > )> > CREATE TABLE item > ( > id SERIAL PRIMARY KEY,> categories SET (INTEGER NOT NULL) -- belonging to > ) > > > Now this would be cool: > > ALTER TABLE l ADD CONSTRAINT > ( > FOREIGN KEY (categories) REFERENCES category (id) > );> I would also suggest that Informix consider this. After all, the concept of "referential integrity", though frequently associated, is not inherently connected with "normalization" (other than 1NF, without which a mathematical relation does not exist). Remember, "normalization" is not a synonym for "relational." Recall that the term "relational" as in "The Relational Model" has nothing to do with relationships, but refers to the mathematical Relations, as in Set Theory. But, I, too, could be missing something, and, if so, would immediately withdraw my comments. I hold Mr. Brown in the highest regard, and if he says there's good reason why this can't/shouldn't be done, I would be inclined to accept his opinion. David Grove Alaska Dept. Health & Social Services

> David Grove wrote: > After all, the concept of "referential integrity", though frequently associated, is not > inherently connected with "normalization" (other than 1NF, without which a > mathematical relation does not exist). Remember,"normalization" is not a synonym for > "relational." Recall that the term "relational" as in "The Relational Model" has nothing to > do with relationships, but refers to the mathematical Relations, as in Set Theory. There is a very long, ivory tower BS discussion to be had here that would take a vector into things like functional dependencies, definitions of keys, and so on. But in the end, data models are what you make of them. And constraints over COLLECTIONs are a very good idea because they would help in practice. The *real* reason this isn't there relates to the way it's kind of hard to come up with a practical way of doing it efficiently. When you muck with rows, tables and data pages there are fairly obvious places in the code to plonk the constraint checks and hide the overhead. But you don't want the overhead of having the engine check each column in each row to see if it's a COLLECTION, and then checking the constraint. It would slow down the general case in order to add the feature. In other words, the fear is that you lose 5% (and that figure is a WAG) on TPC-C in order to enhance a feature no one else has in the first place. Kind of a tough sell. There is always something else (distributed DBMS, Java, blah blah blah ) with more squeak. KR Pb

Thank you, Paul. I certainly don't want to launch any bunny trails, or ignite any flames. Normally, I consider myself Relatively inadequate to dabble in any deep debate (puns intended). As we all know, the water becomes class 6, rapidly (ouch). I inferred that Mr. Brown was appealing to relational principles in his earlier response, and I was trying to think about whether relational principles really might preclude the suggested feature (COLLECTION constraints). I came to the conclusion that there was no problem, in principle. I intended to hint, very obliquely that the two ideas ("relational database" and "COLLECTION constraints") are actually orthogonal, and thus one doesn't necessarily proscribe or prohibit the other. Thus, as Mr. Brown suggested (my paraphrase), it is really an implementation issue. I say this with true respect (for Informix, generally, and Paul Brown, personally), not intending, in any way, to trivialize the challenge of implementation. It is a Herculean task to actually make this stuff work, and Informix is really the only commercial product that does it right, in my opinion. Now, here follows a sincere question, which will no doubt display my lack of IDS understanding. If, in the paragraph below in which Mr. Brown discusses the "*real* reason" for not implementing COLLECTION constraints, would the semantics change if I substituted INTEGER for COLLECTION? I mean, isn't this overhead of checking already incurred for constraints on other data types, say, an integer which is a declared foreign key? Finally, might a "feature no one else has in the first place" (see below) not be a marketing opportunity, rather than a burden. I might cite as an example Object-Relational technology :-) Also, would it be practical to implement COLLECTION constraints with a switch set in onconfig? Then whether users preferred absolute performance or COLLECTION constraints (such as declarative referential integrity), they would all be happy. Best Regards, David Grove Paul Brown <paul.NOSPAM.brown@informix.com> wrote in message news:38A31580.8B16C2B2@informix.com... > > > > David Grove wrote: > > After all, the concept of "referential integrity", though frequently > associated, is not > > inherently connected with "normalization" (other than 1NF, without which a > > mathematical relation does not exist). Remember,"normalization" is not a > synonym for > > "relational." Recall that the term "relational" as in "The Relational Model" > has nothing to > > do with relationships, but refers to the mathematical Relations, as in Set > Theory. > > There is a very long, ivory tower BS discussion to be had here that would > take a vector into things like functional dependencies, definitions of keys, > and so on. But in the end, data models are what you make of them. And > constraints over COLLECTIONs are a very good idea because they would help in > practice. > > The *real* reason this isn't there relates to the way it's kind of hard to > come up with a practical way of doing it efficiently. When you muck with rows, > tables and data pages there are fairly obvious places in the code to plonk the > constraint checks and hide the overhead. But you don't want the overhead of > having the engine check each column in each row to see if it's a COLLECTION, > and then checking the constraint. It would slow down the general case in order > to add the feature. > > In other words, the fear is that you lose 5% (and that figure is a WAG) on > TPC-C in order to enhance a feature no one else has in the first place. Kind of > a tough sell. There is always something else (distributed DBMS, Java, blah blah > blah ) with more squeak. > > KR > > Pb > > > > > > >