On Wed, Mar 9, 2011 at 10:59 AM, Reece Hart <reece@harts.net> wrote:
> On Tue, Mar 8, 2011 at 9:41 PM, John R Pierce <pierce@hogranch.com> wrote:
>>
>> why not just have a set of booleans in the table for these individual
>> on/off attributes? wouldn't that be simplest?
>
> I like that approach, but I think it's unlikely to fly in this specific case
> for a couple reasons.
> First, there are actually 8 factors (I edited for clarity... sorry about
> that).
> The original database is actively developed (released apx quarterly). I will
> need an approach that minimizes my burden when they edit the set factors.
> And, I'd like to be compatible with mysql syntax and semantics for sets. If
> you hold your nose for a moment, you'll be able to read the following
> without becoming ill: mysql uses comma delimited strings to assign and query
> set types (but stored internally as bit vectors). So, one does
> validation_status = 'cluster,freq' to set those bits or validation_status
> like '%freq%' to query. Much to my chagrin, emulating this interface will
> make migration easier. However, implementing this string interface to
> set/get boolean columns is just too offensive to whatever modest design
> sensibilities I have. (For more pleasure reading, see
> http://dev.mysql.com/doc/refman/5.0/en/set.html. I particularly like the
> *warning* issued when one tries to add a value that's not part of the set.)
> -Reece
create type validation_flags as
(
cluster bool,
freq bool
);
create function validation_flags_in(
flags text, flags out validation_flags) returns validation_flags as
$$
select row($1 ~ 'cluster', $1 ~ 'freq')::validation_flags
$$ language sql immutable;
create table foo (flags validation_flags);
insert into foo values (validation_flags_in('cluster'));
select * from foo;
select (flags).* from foo;
merlin
