Enum operations
Purdy
column. Based on some IRC conversations, it was recommended that I use
an enum column. The MySQL definition of the column is:
`status` enum
('Draft','Issued','Signed','Fulfilled','Paid','Completed','Archived')
NOT NULL default 'Draft',
So everything's humming along and now I'm wishing I can do some
comparison operations, like "is the status greater than signed" or
"get the objects where other_field is something and status is less
than paid". I know from looking at MySQL, you can force enum's into
int's when you're working at that layer, but I'm wondering if there's
anything like that for Rose and QueryBuilder?
Thanks,
Jason
John Siracusa
> So everything's humming along and now I'm wishing I can do some
> comparison operations, like "is the status greater than signed" or
> "get the objects where other_field is something and status is less
> than paid". I know from looking at MySQL, you can force enum's into
> int's when you're working at that layer, but I'm wondering if there's
> anything like that for Rose and QueryBuilder?
Well, what's the syntax for the SQL? Worst case, you can pass literal
SQL snippets to Manager/QueryBuilder to get the filter conditions you
want. See the middle of the "query" parameter documentation for
instructions:
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/QueryBuilder.pm#query
-John
Perrin Harkins
> column. Based on some IRC conversations, it was recommended that I use
> an enum column.
I don't recommend that. The only advantage of ENUM in MySQL is
reduced storage space. It won't prevent you from inserting garbage.
If you want to enforce a set of values, use a lookup table and a
foreign key constraint instead. That WILL prevent garbage from going
into the column.
> So everything's humming along and now I'm wishing I can do some
> comparison operations, like "is the status greater than signed" or
> "get the objects where other_field is something and status is less
> than paid".
If you used a lookup table, you could just have a second column for
the value of a status.
- Perrin
Graham Barr
On Nov 11, 2009, at 1:25 PM, Perrin Harkins wrote:
>
> On Wed, Nov 11, 2009 at 10:58 AM, Purdy
> <jpu...@finebooksmagazine.com> wrote:
>>
>> I'm working on a finite state machine webapp based on a 'status'
>> column. Based on some IRC conversations, it was recommended that I
>> use
>> an enum column.
>
> I don't recommend that. The only advantage of ENUM in MySQL is
> reduced storage space. It won't prevent you from inserting garbage.
Thats not true. If you run mysql with sql_mode='STRICT_ALL_TABLES',
which you
really should, then it will result in an error if you attempt to set
to a
string not in the enum list
Graham.
Perrin Harkins
> Thats not true. If you run mysql with sql_mode='STRICT_ALL_TABLES',
> which you
> really should, then it will result in an error if you attempt to set
> to a
> string not in the enum list
Good to know. Thanks.
- Perrin
Peter Karman
On Nov 11, 2:23 pm, Graham Barr <gb...@pobox.com> wrote:
> If you run mysql with sql_mode='STRICT_ALL_TABLES',
> which you
> really should, then it will result in an error if you attempt to set
> to a
> string not in the enum list
use
postgresql whenever I have a choice, but at present I do not have a
choice. Is
there a recommended set of config/mode/settings to run with mysql so
that it
behaves like postgresql (i.e., sanely)?
STRICT_ALL_TABLES is now on my list.
I always use InnoDB. I always use UTF-8.
Any other mysql-specific strictness/validation settings from those
more
battle-scarred than me?
I want the equivalent of 'use strict; use warnings;' for mysql. :)
Perrin Harkins
> This reminds me of a (likely OT) question I have had for a while. I
> use
> postgresql whenever I have a choice, but at present I do not have a
> choice. Is
> there a recommended set of config/mode/settings to run with mysql so
> that it
> behaves like postgresql (i.e., sanely)?
You could use POSTGRESQL mode:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_postgresql
You'd also want STRICT_ALL_TABLES.
- Perrin
Ben Tilly
Will those options turn off the "feature" where if a field is in the
SELECT but not the GROUP BY it will select a random representative
value rather than letting you know the query makes no sense?
(I was bitten by this one yesterday.)
Ben
Perrin Harkins
> Will those options turn off the "feature" where if a field is in the
> SELECT but not the GROUP BY it will select a random representative
> value rather than letting you know the query makes no sense?
ONLY_FULL_GROUP_BY
- Perrin