Excellent, that's done the trick, thanks a lot.
I hadn't thought about the interaction between INSERTs, default
values, and attributes being set to None. I think I would have assumed
that if I explicitly set an attribute to None, it would be INSERTed
into the database as NULL, and the default would only be used if I
never set the attribute at all. Does this mean that there's no way
through the ORM to insert a new row with NULLs for columns that have
been assigned a default value? (Just an idle question, not something I
need).
Thanks again,
Simon
On Mon, Feb 24, 2014 at 5:31 PM, Michael Bayer <mike...@zzzcomputing.com> wrote:
> well what's happening here is that the "insert" default on SomeClass.somebool
> is being copied out to SomeHistoryClass.somebool, and the history operation
> uses INSERT. So the INSERT with "None" as the value hits the default and
> puts in None instead.
>
> Two ways this wouldn't happen:
>
> 1. history meta doesn't copy column defaults when it copies columns.
>
> 2. history meta does a translation of None to NULL when it does an INSERT.
>
> I think #1 is right, while I want to support INSERT defaults on history
> tables, those would be on columns that were added in addition to the columns
> that are "copied" over from the original. The columns we copy from the
> original should never need to have defaults since there's always a value.
>
> here's a patch:
>
> --- a/examples/versioned_history/history_meta.py
> +++ b/examples/versioned_history/history_meta.py
> @@ -32,14 +32,19 @@ def _history_mapper(local_mapper):
> polymorphic_on = None
> super_fks = []
>
> + def _col_copy(col):
> + col = col.copy()
> + col.unique = False
> + col.default = col.server_default = None
> + return col
> +
> if not super_mapper or local_mapper.local_table is not
> super_mapper.local_table:
> cols = []
> for column in local_mapper.local_table.c:
> if _is_versioning_col(column):
> continue
>
> - col = column.copy()
> - col.unique = False
> + col = _col_copy(column)
>
> if super_mapper and col_references_table(column,
> super_mapper.local_table):
> super_fks.append((col.key,
> list(super_history_mapper.local_table.primary_key)[0]))
> @@ -80,8 +85,7 @@ def _history_mapper(local_mapper):
> # been added and add them to the history table.
> for column in local_mapper.local_table.c:
> if column.key not in super_history_mapper.local_table.c:
> - col = column.copy()
> - col.unique = False
> + col = _col_copy(column)
> super_history_mapper.local_table.append_column(col)
> table = None
>
>
>
>
>
>
> On Feb 24, 2014, at 7:01 AM, Simon King <si...@simonking.org.uk> wrote:
>
>> Hi,
>>
>> I'm using the Versioned class from the examples, and I noticed that
>> when I set a Boolean column to None (NULL in the db), the eventual
>> history row corresponding to that change gets a 0 rather than a NULL.
>>
>> In the test script below, I create an instance of a versioned class,
>> with a boolean property initially set to False. I set it to None, and
>> check that the history table records the old False value, then I set
>> it to True and check that the history table records the old None
>> value. The final assertion fails because None was actually inserted as
>> 0. I've tested using 0.8.2 and 0.9.3, against sqlite and mysql, and
>> get the same results.
>>
>> I've instrumented the create_version function in history_meta.py, and
>> as far as I can tell everything looks fine. The boolean column in the
>> history table is nullable, the bind processor for the column is
>> boolean_to_int, and calling that function with None returns None as
>> expected. But the INSERT statement is inserting a 0:
>>
>> sqlalchemy.engine.base.Engine INSERT INTO sometable_history (id, name,
>> somebool, version, changed) VALUES (?, ?, ?, ?, ?)
>> sqlalchemy.engine.base.Engine (1, u'sc1modified', 0, 2, '2014-02-24
>> 11:56:31.730591')
>>
>> Here's the test script:
>>
>> from sqlalchemy.ext.declarative import declarative_base
>> import sqlalchemy as sa
>> import sqlalchemy.orm as saorm
>>
>> from history_meta import Versioned, versioned_session
>>
>> Base = declarative_base()
>> Session = saorm.sessionmaker()
>> versioned_session(Session)
>>
>> class SomeClass(Versioned, Base):
>> __tablename__ = 'sometable'
>> id = sa.Column(sa.Integer, primary_key=True)
>> name = sa.Column(sa.String(50))
>> somebool = sa.Column(sa.Boolean, default=False)
>>
>> def test():
>> engine = sa.create_engine('sqlite://', echo='debug')
>> Base.metadata.create_all(engine)
>> session = Session(bind=engine)
>>
>> def someboolhistory():
>> SomeClassHistory = SomeClass.__history_mapper__.class_
>> history = (session.query(SomeClassHistory)
>> .order_by(SomeClassHistory.version)
>> .all())
>> return [h.somebool for h in history]
>>
>> def checkhistory(expected):
>> actual = someboolhistory()
>> assert actual == expected, ('%s != %s' % (expected, actual))
>>
>> sc = SomeClass(name='sc1', somebool=False)
>> session.add(sc)
>> session.commit()
>>
>> sc.name = 'sc1modified'
>> sc.somebool = None
>> session.commit()
>>
>> checkhistory([False])
>>
>> sc.name = 'sc1modified2'
>> sc.somebool = True
>> session.commit()
>>
>> checkhistory([False, None])
>>
>> if __name__ == '__main__':
>> test()
>>
>>
>> Thanks in advance for any help,
>>
>> Simon
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/groups/opt_out.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
