List: | General Discussion | « Previous MessageNext Message » | |

From: | Martin Ramsch | Date: | September 16 1999 11:17am |

Subject: | Re: How to delete a member from a SET column | ||

View as plain text |

NOTE: Below I'm talking about set related functions, which DON'T exist yet in MySQL up to V3.23.3! (Saying this just to make sure that nobody will get confused and tries to use these function ...) On Wed, 1999-09-15 11:02:50 -0400, James Manning wrote: > > If you know all the names of allowed list members, I suggest you first > > get their numerical values with the help of 'DESCRIBE table'. > > (I don't understand your argument above against this approach, though.) > > could you post a little perl to do such? it'd be nice to see how > its done. See the attachment (if it hopefully makes it through the list processor). > > Then, with the numerical values, it's easy to delete/add elements > > from/to a set: > > > > adding an element: > > UPDATE settable SET setfield = setfield | elementvalue; > > I'd rather do > UPDATE settable SET setfield = setfield | to_num('element'); A function like this probably would need to have a second argument: a table field, to have a reference to the needed set definition. UPDATE settable SET setfield = setfield | to_num(setfield,'element'); > or > UPDATE settable SET_BIT(setfield,'element'); > :) Better keep the UPDATE syntax as it is, thus changing to (I prefer other function names): UPDATE settable SET setfield = SET_UNION(setfield,'element'); Where SET_UNION(setval1,setval2) -> result in a set with all elements of both setval1 and setval2 Plus (of course) the counterpart functions: SET_MINUS(setval1,setval2) -> results in a set with all elements of setval1 which are not in setval2 SET_SECTION(setval1,setval2) -> results in a set with only these elements which are in both setval1 and setval2 SET_COMPLEMENT(setval) -> results in a set with all those elements which are not in setval With these function one even could keep both the element name based and the bit value based way to work with. There are tree possibilities for each argument type: an integer number, a set string, a set field And these functions may be called both in numerical context, in string context _This_ really would be fine! If someone actually is to implement such functions, I'd go for this behaviour (read it as the specification of a feature request ... :) Example fields Example values a SET('a','b','c') 'a','c' (= 5 = binary 0101) b SET('c','a','d') 'a','d' (= 6 = binary 0110) Example numbers: 7 (= binary 0111) 12 (= binary 1100) Example strings: 'b','c','d' (= binary 0110 related to a's definition) (= binary 0101 related to b's definition) 'd','a','b' (= binary 0011 related to a's definition) (= binary 0110 related to b's definition) In this table my design guideline is to treat set elements basically as entities defined by their element name. The numerical values are just another more low level representation of these element names. So to speak, a numerical representation is only valid if there's a mapping to element names, a textual representation always is okay. +=+=============+=============+=============================================+ | | setval1 | setval2 | numerical context | string context | +=+=============+=============+=============================================+ |1| number | number | just do bit arithmetic (special case) | | | 7 | 12 | UNION: 15 (= 1111) | '15' | | | | | SECTION: 4 (= 0100) | '4' | +-+-------------+-------------+---------------------------------------------+ |2| number | string | bit values of string elements are undefined | | | 7 | 'd','a','b' | NULL | NULL | +-+-------------+-------------+---------------------------------------------+ |3| number | field | interpret number as set value according to | | | | | field b (would skip undefined bits) | | | 7 | b | UNION: 7 (= 0111) | 'a','b','c' | | | | | SECTION: 3 (= 0101) | 'a','b' | +-+-------------+-------------+---------------------------------------------+ |4| string | number | bit values of string elements are undefined | | | 'b','c','d' | 12 | NULL | NULL | +-+-------------+-------------+---------------------------------------------+ |5| string | string | bit values of string elements are undefined | | | | | but textual element handling is fine | | | 'b','c','d' | 'd','a','b' | UNION: NULL | 'a','b','c','d' | | | | | SECTION: NULL | 'b','d' | +-+-------------+-------------+---------------------------------------------+ |6| string | field | interpret string according to definition of | | | | | field b (skip undefined elements like 'b') | | | 'b','c','d' | b | UNION: 7 (= 0111) | 'c','a','d' | | | | | SECTION: 5 (= 0101) | 'c','d' | +-+-------------+-------------+---------------------------------------------+ |7| field | number | interpret number as set value according to | | | | | field a (skip undefined bits like bit 3) | | | a | 12 | UNION: 5 (= 0101) | 'a','c' | | | | | SECTION: 4 (= 0100) | 'c' | +-+-------------+-------------+---------------------------------------------+ |8| field | string | interpret string according to definition of | | | | | field a (skip undefined elements like 'd') | | | a | 'd','a','b' | UNION: 7 (= 0111) | 'a','b','c' | | | | | SECTION: 3 (= 0011) | 'a','b' | +-+-------------+-------------+---------------------------------------------+ |9| field | field | bit values of calculated set elements | | | | | are undefined, if field definitins don't | | | | | match or are compatible super/subsets | | | a | b | UNION: NULL | 'a','b','c','d' | | | | | SECTION: NULL | 'a','c' | +-+-------------+-------------+---------------------------------------------+ Notes: * In cases 3 and 7 (number and field) one has to decide how to treat numbers that extend the defined bit range of the field. I see two principle posibilities how to handle this: - Strip the undefined bits (this is as in the table above) - In numerical context just do bit arithmetic, in string context return NULL for undefined In case 7, the UNION results would be 12 and NULL instead of 5 and 'a','c'. - In numerical context just do bit arithmentic, in string context return set with newly introduced elements to represent higher bits. In case 7, the UNION results could be 12 and 'a','c','bit4' instead of 5 and 'a','c'. * In cases 6 and 8 (string and field) one has to make the same decision for elements not included in the field definition: - strip undefined element names (this is as in the table above) - In numerical context return NULL for undefined, in string context return the extended set string. In case 6, the UNION result would be NULL and 'c','a','d','b' instead of 7 and 'c','a','d' - In numerical context map new elements to the higher bits in the order they appear in string, in string context return the extended set string. In case 6, the UNION result would be 15 and 'c','a','d','b' instead of 7 and 'c','a','d' * In case 9, if one set definition is an extension of the other, i.e. is has the same element names in the same order but has some more elements, then just use the bigger set definition for numerical values. In all other cases with incompatible set definitions I see this possibilities: - numerical results are undefined (as in the table above). - compute numerical result as bit arithmetic of both numerical representations. Then 'c' of field a is 4 (= 100), 'c' of field b is 1 (= 001), and the UNION would be 5 (= 101). > ... forcing applications to deal with how MySQL chooses to handle > sets sure seems to expose more underlying implementation to the > application than one would/should feel comfortable with :) Yep! Nevertheless I'm not entirely sure, if it would be wise to actually implement such functions as outlined above, because then it would become easier to deal with sets - and this means, more and more people would actually use them and would try to handle databases which are not even in 1st normalform. Maybe not that good an idea ... :) Regards, Martin -- Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ > PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7

Thread | ||
---|---|---|

• How to delete a member from a SET column | Benjamin Pflugmann | 7 Jul |

• Re: How to delete a member from a SET column | Martin Ramsch | 15 Sep |

• Re: How to delete a member from a SET column | James Manning | 15 Sep |

• Re: How to delete a member from a SET column | Martin Ramsch | 16 Sep |

• Re: How to delete a member from a SET column | Martin Ramsch | 16 Sep |

• Re: How to delete a member from a SET column | James Manning | 17 Sep |

• Re: How to delete a member from a SET column | Martin Ramsch | 17 Sep |

• Re: How to delete a member from a SET column | James Manning | 17 Sep |

• Re: How to delete a member from a SET column | Martin Ramsch | 17 Sep |

• placeholders | jice | 17 Sep |

• Re: placeholders | Bob Kline | 17 Sep |

• Re: How to delete a member from a SET column | James Manning | 17 Sep |

• Re: How to delete a member from a SET column | James Manning | 17 Sep |

• Re: How to delete a member from a SET column | Martin Ramsch | 17 Sep |