#177 None in enumValues fails

closed-accepted
MySQL (29)
5
2006-10-11
2006-05-27
Neil Muller
No

SQLObject-0.7.1b1

MySQL does not allow NULL inside the ENUM statemnt, so
the construction
EnumCol(enumValues=['a','b',None],default=None) will fail.

Since this construction works fine with sqlite and
postgresql, this is an issue. The attached patch fixes
the problem here.

Discussion

  • Neil Muller

    Neil Muller - 2006-05-29

    Logged In: YES
    user_id=698097

    I was pointed at
    http://archives.postgresql.org/pgsql-sql/2004-12/msg00065.php,
    which illustrates that NULL in the check constraint approach
    used to implement EnumCol for postges and other databases
    behave in an unexpected way due to the three state logic SQL
    uses. Thus a believe this second patch, which excludes Nones
    from all the database backends should be prefferred.

     
  • Oleg Broytman

    Oleg Broytman - 2006-09-05

    Logged In: YES
    user_id=4799

    This contradicts to Python philoshy "We all are grown-ups
    people". If you know None (NULL) in enums is such a bad idea
    - just dont use it. But forcibly remove it when the user
    clearly states "I want my Nones here" is too much. Instead
    of such removing write a documentation patch and clearly
    explain the caveats.

     
  • Neil Muller

    Neil Muller - 2006-09-05

    Logged In: YES
    user_id=698097

    I've unfortunately combined two issues here, and I'll agree
    that the second patch is incorrect.

    There is an actual bug here. The current sqlobject logic
    breaks on mysql:
    The construction:
    EnumCol(enumValues=['a','b',None],default=None)
    maps to
    CREATE TABLE t (c1 ENUM ('a','b',NULL));
    which is not valid for MySQL, which wants NULL's for ENUM
    columns handled as NULL constraints. This makes using None
    in the EnumCol impossible for MySQL. The mysql_enum patch
    attached aims to create a suitable constraint if None is
    present EnumCol.

     
  • Neil Muller

    Neil Muller - 2006-09-05

    Updated mysql enum patch

     
  • Neil Muller

    Neil Muller - 2006-09-05

    Logged In: YES
    user_id=698097

    The second issue only applies if a postgresql database is
    being accessed without using sqlobject as well.

    he check constraint approach used doesn't work as expected
    for postgresql.
    EnumCol(enumValues=['a','b',None],default=None)
    produces something like:
    create table t (c1 varchar(2) check (c1 in ('a','b',NULL)));
    However:
    insert into t values ('1');
    will succeed, because of how postgresql interprets
    conditions involving NULL, as discussed in the message I
    linked to. Similiar issues may arise with other databases.

    However, since the call succeeds, a documentation note is
    probably adequate. I've attached a possible patch to
    SQLObject.txt as enum_doc_patch, although this probably
    needs furtehr work.

     
  • Neil Muller

    Neil Muller - 2006-09-05

    Proposed patch to SQLObject.txt to document Postgresql issues with EnumCol

     
  • Oleg Broytman

    Oleg Broytman - 2006-09-07

    Logged In: YES
    user_id=4799

    But what if a user wrtes

    EnumCol(enumValues=['a','b',None],default=None)

    and got

    create table t (c1 varchar(2) check (c1 in
    ('a','b')));

    ??? Wouldn't it surpize him/her? Wouldn't it be better to
    raise an error instead of silently hiding it?

     
  • Neil Muller

    Neil Muller - 2006-09-07

    Logged In: YES
    user_id=698097

    I've already conceded that the earlier proposed patch was
    incorrect, so that currently won't happen.

    With the mysql_enum patch I'm currently proposing, the
    result for databases other than MySQL shouldn't change from
    what currently happens. The mysql_enum patch aims to map the
    the present of None in the EnumCol to a NULL constraint on
    the column as this seems, based on the MySQL documentation,
    the only way of specifying wether NULL's are accepted or not
    for ENUM's.

    The obvious alternative is to disallow None's in EnumCol's
    when using MySQL entirely, but that seems to be the sort of
    database specific behaviour SQLObject should be hiding from
    the user.

    The proposed enum_doc_patch tries to describe the potential
    pitfall of using the EnumCols('a','b',None) construction
    with postgresql, but doesn't change the behaviour at all.
    I'd be happy to add a warning to the code as well if that
    would be preffered. I've left the other databases alone
    since I can't readily test them.

     
  • Oleg Broytman

    Oleg Broytman - 2006-10-11

    Logged In: YES
    user_id=4799

    There are many minor problems with the patch - wrong indent,
    forgotten ':' after 'else', etc. I fixed all of them,
    applied and committed in the revisions 2006-2008. Thank you!

     
  • Oleg Broytman

    Oleg Broytman - 2006-10-11
    • assigned_to: nobody --> phd
    • status: open --> closed-accepted
     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks