[sqlalchemy] Re: Database Referential Integrity Constraints Puzzle
Thanks Alex! Jerry On Dec 16, 2:18 am, alex bodnaru [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi jerryji, this database is not normalized. you may consider switching to: things(thingid INTEGER, thing_weight etc.) and things_translations(thingid INTEGER, thing_name VARCHAR, language CHAR(5)) jerryji wrote: Dear Kind Soul, I am stuck with the following database referential integrity constraint problem. I have two tables: things(thingid INTEGER, thing_name VARCHAR, language CHAR(5)) and thing_relations(thingid INTEGER, thing_parentid INTEGER) things hosts items in different languages, e.g.: things(1, 'car', 'en_US'); things(1, 'voiture', 'fr_FR'); things(2, 'engine', 'en_US'); things(2, 'moteur', 'fr_FR'); things(3, 'brake', 'en_US'); things(3, 'freins', 'fr_FR'); while thing_relations describes the consists of relationship between things, e.g.: thing_relations(2, 1); thing_relations(3, 1) as engine and brake are parts of a car but since things(thingid) is not unique hence it can't be the primary key in things and can't be the foreign key in thing_relations, how can the referential integrity constraints between thing_relations(thingid) and things(thingid) be described? Or my design is not making sense? Many thanks in advance. Jerry -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org iQCVAwUBR2TRNNpwN1sq38njAQLJPgP/diPRHpnH7s/auHx/LTXF5Y2Hx7npdLc/ yLuhvjx6Qatm4eU8sASFLLcTEYv1EZsOMRNVaxBEq7Aq89DeSPGVjIl7iy4egjKv rKIyCA4W5nhB37vFbdjMpd1fOmLiUqdtm/ObvSBxb6x3Hd3JDXYwcr86ve5j0XtT hlMT5X2uG4g= =WTdr -END PGP SIGNATURE- --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using ORM Object as an intermadiate holder
the expire() is requesting a reload. try moving that after the sending back stuff to user. Utku Altinkaya wrote: Hi, I am using SQLAlchemy on a web application, I have used a base class for ORM clases which provides soem web related things like validation and loading data from forms etc. When the form is submitted: Here is the current life cycle: object.loadFromForm() if object.Validate(): session.save_or_update(object) else render_form(object) session.expire(object) session.commit() I do not want to lose invalid values, becouse I want to send them to the user again, so while using object as intermediate holder I have to set attributes invalid values... So Autoflush = False, and if invalid values are existed the object is reloaded from DB before commit step of web request cycle. But while using SQLAlchemy I had the impression that it is not designed to to that, It designers thought objects are direct representation of the data in the database. I can do this ofcouse some kind of holder class copies attributes from data objects etc, but it is cumbersome, and will force me to write longer code. I am expecting someone to tell me the way I am doing is perfectly valid, or another advice regards --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: some error around trunk
On Dec 16, 2007, at 2:40 AM, [EMAIL PROTECTED] wrote: from sqlalchemy import * m= MetaData() trans =Table( 'trans', m, Column( 'date', Date), ) balance=Table( 'balance', m, Column( 'finaldate', Date), ) b = balance.alias('b') sprev = select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ) #correlate is non-generative in 0.3 (ret None) but generative in 0.4 sprev = sprev.correlate( balance) or sprev r = trans.c.date func.coalesce( sprev,0 ) #, as_scalar=True ) with or without all the same r = trans.c.date func.coalesce( sprev.as_scalar(),0 ) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: some error around trunk
ok; see mapper.py line 1134, calling after_update with state instead of state.obj() Michael Bayer wrote: On Dec 16, 2007, at 2:40 AM, [EMAIL PROTECTED] wrote: from sqlalchemy import * m= MetaData() trans =Table( 'trans', m, Column( 'date', Date), ) balance=Table( 'balance', m, Column( 'finaldate', Date), ) b = balance.alias('b') sprev = select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ) #correlate is non-generative in 0.3 (ret None) but generative in 0.4 sprev = sprev.correlate( balance) or sprev r = trans.c.date func.coalesce( sprev,0 ) #, as_scalar=True ) with or without all the same r = trans.c.date func.coalesce( sprev.as_scalar(),0 ) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] obtaining previous value in mapper.extension.after_*
i used to get the original (before change) value of some attribute via state.commited_state[key]... but seems now that dict is empty at the time when ext.after_* are called. any way to get that? storing copies at ext.before_* is not good alternative... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: obtaining previous value in mapper.extension.after_*
[EMAIL PROTECTED] wrote: i used to get the original (before change) value of some attribute via state.commited_state[key]... but seems now that dict is empty at the time when ext.after_* are called. any way to get that? storing copies at ext.before_* is not good alternative... found some workaround but not sure if it's proper thing: r = getattr( instance.__class__, attribute).get_history( instance) r = r[-1] or r[-2] return r and r[0] or None #should never be None ??? not sure what the three get_history sublists are for... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: obtaining previous value in mapper.extension.after_*
On Dec 16, 2007, at 2:33 PM, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: i used to get the original (before change) value of some attribute via state.commited_state[key]... but seems now that dict is empty at the time when ext.after_* are called. any way to get that? storing copies at ext.before_* is not good alternative... found some workaround but not sure if it's proper thing: r = getattr( instance.__class__, attribute).get_history( instance) r = r[-1] or r[-2] return r and r[0] or None#should never be None ??? not sure what the three get_history sublists are for... (added, unchanged, deleted) = attributes.get_history(myinstance._state, 'someattribute') three lists will never be None unless you call get_history() with passive=True and lazyload would be needed. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using ORM Object as an intermadiate holder
On 16 Aralık, 17:46, [EMAIL PROTECTED] wrote: the expire() is requesting a reload. try moving that after the sending back stuff to user. The documents says it does not reload until it is accessed if the object is expired, I think what does the thing you have mantioned is the refresh method of session. http://www.sqlalchemy.org/docs/04/session.html#unitofwork_using_refreshing Utku Altinkaya wrote: Hi, I am using SQLAlchemy on a web application, I have used a base class for ORM clases which provides soem web related things like validation and loading data from forms etc. When the form is submitted: Here is the current life cycle: object.loadFromForm() if object.Validate(): session.save_or_update(object) else render_form(object) session.expire(object) session.commit() I do not want to lose invalid values, becouse I want to send them to the user again, so while using object as intermediate holder I have to set attributes invalid values... So Autoflush = False, and if invalid values are existed the object is reloaded from DB before commit step of web request cycle. But while using SQLAlchemy I had the impression that it is not designed to to that, It designers thought objects are direct representation of the data in the database. I can do this ofcouse some kind of holder class copies attributes from data objects etc, but it is cumbersome, and will force me to write longer code. I am expecting someone to tell me the way I am doing is perfectly valid, or another advice regards- Alıntıyı gizle - - Alıntıyı göster - --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using ORM Object as an intermadiate holder
expiring the obj has the effect that any further access to the object will auto-refresh it. so if u expire(x) and then say x.a. x will be reloaded first then u get x.a Utku Altinkaya wrote: On 16 Aralık, 17:46, [EMAIL PROTECTED] wrote: the expire() is requesting a reload. try moving that after the sending back stuff to user. The documents says it does not reload until it is accessed if the object is expired, I think what does the thing you have mantioned is the refresh method of session. http://www.sqlalchemy.org/docs/04/session.html#unitofwork_using_refreshing Utku Altinkaya wrote: Hi, I am using SQLAlchemy on a web application, I have used a base class for ORM clases which provides soem web related things like validation and loading data from forms etc. When the form is submitted: Here is the current life cycle: object.loadFromForm() if object.Validate(): session.save_or_update(object) else render_form(object) session.expire(object) session.commit() I do not want to lose invalid values, becouse I want to send them to the user again, so while using object as intermediate holder I have to set attributes invalid values... So Autoflush = False, and if invalid values are existed the object is reloaded from DB before commit step of web request cycle. But while using SQLAlchemy I had the impression that it is not designed to to that, It designers thought objects are direct representation of the data in the database. I can do this ofcouse some kind of holder class copies attributes from data objects etc, but it is cumbersome, and will force me to write longer code. I am expecting someone to tell me the way I am doing is perfectly valid, or another advice regards- Alıntıyı gizle - - Alıntıyı göster - --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using ORM Object as an intermadiate holder
On Dec 15, 11:02 pm, Utku Altinkaya [EMAIL PROTECTED] wrote: I do not want to lose invalid values, becouse I want to send them to the user again, so while using object as intermediate holder I have to set attributes invalid values... So Autoflush = False, and if invalid values are existed the object is reloaded from DB before commit step of web request cycle. But while using SQLAlchemy I had the impression that it is not designed to to that, It designers thought objects are direct representation of the data in the database. I can do this ofcouse some kind of holder class copies attributes from data objects etc, but it is cumbersome, and will force me to write longer code. I am expecting someone to tell me the way I am doing is perfectly valid, or another advice the usual way people handle form validation is using a package like FormEncode: http://formencode.org/Validator.html i.e. you wouldn't have the invalid data on your ORM object at any point. theres nothing wrong with how you're doing it, it just has disadvantages. namely, that your ORM objects have to look just like your web forms (such as, a web form with three dropdowns, month, day and year...but your ORM object only has date on it), and you also have to ensure the ORM objects dont get flushed with the invalid data (easily breakable). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: obtaining previous value in mapper.extension.after_*
and another issue around attribute.get_history... i have a descriptor that is autosetting some defaultvalue at first get. before r3935 it was ok; now the atribute is not updated anymore (in exact case, another object has to be inserted but it is not) as it seems that ScalarObjectAttributeImpl never knows that the attribute has been missing at start - dict.get(key,NOVALUE) will never return NOVALUE, as the descriptor machinery is called instead of __dict__[key] / haskey etc. i am looking at _create_history() and the way it is used but see no light... as i do not know either was there a value or not... well the object is brand new so it has to have nothing... Any way to hint it? so some just-created object would have an initialy empty history. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: obtaining previous value in mapper.extension.after_*
On Dec 16, 2007, at 3:16 PM, [EMAIL PROTECTED] wrote: not sure what the three get_history sublists are for... (added, unchanged, deleted) = attributes.get_history(myinstance._state, 'someattribute') three lists will never be None unless you call get_history() with passive=True and lazyload would be needed. in a scalar context, what (added, unchanged, deleted) mean? setattr-added delattr-deleted ? so the old value is (deleted or unchanged)[0]? i have really nice unit tests now that illustrate the whole thing in test/orm/attributes.py HistoryTest: attributes.register_class(Foo) attributes.register_attribute(Foo, 'someattr', uselist=False, useobject=False) f = Foo() self.assertEquals(attributes.get_history(f._state, 'someattr'), ([], [], [])) f.someattr = hi self.assertEquals(attributes.get_history(f._state, 'someattr'), (['hi'], [], [])) f._state.commit(['someattr']) self.assertEquals(attributes.get_history(f._state, 'someattr'), ([], ['hi'], [])) f.someattr = 'there' self.assertEquals(attributes.get_history(f._state, 'someattr'), (['there'], [], ['hi'])) f._state.commit(['someattr']) self.assertEquals(attributes.get_history(f._state, 'someattr'), ([], ['there'], [])) del f.someattr # oops, svn up to r3952 self.assertEquals(attributes.get_history(f._state, 'someattr'), ([], [], ['there'])) if the attribute is an object reference, you might have [None] instead of [] for added/unchanged if theres no value. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---