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 columnBenjamin Pflugmann7 Jul
  • Re: How to delete a member from a SET columnMartin Ramsch15 Sep
    • Re: How to delete a member from a SET columnJames Manning15 Sep
      • Re: How to delete a member from a SET columnMartin Ramsch16 Sep
        • Re: How to delete a member from a SET columnMartin Ramsch16 Sep
          • Re: How to delete a member from a SET columnJames Manning17 Sep
            • Re: How to delete a member from a SET columnMartin Ramsch17 Sep
              • Re: How to delete a member from a SET columnJames Manning17 Sep
                • Re: How to delete a member from a SET columnMartin Ramsch17 Sep
                  • placeholdersjice17 Sep
                    • Re: placeholdersBob Kline17 Sep
                  • Re: How to delete a member from a SET columnJames Manning17 Sep
        • Re: How to delete a member from a SET columnJames Manning17 Sep
          • Re: How to delete a member from a SET columnMartin Ramsch17 Sep