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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.