From: Eduardo E. <eel...@na...> - 2004-05-10 21:50:29
|
Hi All, I have some basic audit fields in my tables, that I used to update in my rudimentary ORM superclass. I've seen in the FAQ/docs that SQLObject classes are not meant to be subclassed, so I was thinking that may be there is some event that I can hook into, but this related post (news://news.gmane.org:119/1080605589.21688.13.camel@gandalf) received no answer. That only leaves me the option to find the method where sql statements are passed to the db driver and intercept/modify this statements. Is this feasible? If so, where should I look? TIA. Ed. |
From: Oleg B. <ph...@ph...> - 2004-05-11 07:59:47
|
On Mon, May 10, 2004 at 05:37:21PM -0400, Eduardo Elgueta wrote: > I have some basic audit fields in my tables, that I used to update in my > rudimentary ORM superclass. > > I've seen in the FAQ/docs that SQLObject classes are not meant to be > subclassed There was a Daniel Savard's inheritance patch. I cannot find it on the web, but I have patches for SQLObject 0.5.1 and 0.5.2. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Eduardo E. <eel...@na...> - 2004-05-11 13:16:52
|
Oleg, Thank you for your answer. I'm not sure inheritance is what I need, but I'll certainly give it a try. Where can I get that patch? Ed. Oleg Broytmann wrote: > On Mon, May 10, 2004 at 05:37:21PM -0400, Eduardo Elgueta wrote: > >>I have some basic audit fields in my tables, that I used to update in my >>rudimentary ORM superclass. >> >>I've seen in the FAQ/docs that SQLObject classes are not meant to be >>subclassed > > > There was a Daniel Savard's inheritance patch. I cannot find it on > the web, but I have patches for SQLObject 0.5.1 and 0.5.2. > > Oleg. |
From: Eduardo E. <eel...@na...> - 2004-05-11 20:56:38
|
Oleg and anyone else, I tried Daniel Savard's patch and I can see I could use it to achieve what I want, but creating an unnatural hierarchy. What I would like to have is something like this: def MyOnUpdate (sqlObj): sqlObj.addColumn(StringCol("updateBy", "me"); sqlObj.addColumn(DateTimeCol("updateOn", now()); def MyOnInsert (sqlObj): sqlObj.addColumn(StringCol("createdBy", "me"); sqlObj.addColumn(DateTimeCol("createdOn", now()); MyOnUpdate(sqlObj) def MySQLObject1 (SQLObject): myField = StringCol() onInsert = MyOnInsert(self) onUpdate = MyOnUpdate(self) def MySQLObject2 (SQLObject): myField = IntCol() onInsert = MyOnInsert(self) onUpdate = MyOnUpdate(self) What I need is to find the place to insert the self.onUpdate(self) call. Any suggestion? TIA, Ed. PS: In other words, I need triggers which MySQL (my database backend) does not provide. Eduardo Elgueta wrote: > Oleg, > > Thank you for your answer. > > I'm not sure inheritance is what I need, but I'll certainly give it a > try. Where can I get that patch? > > Ed. > > Oleg Broytmann wrote: > >> On Mon, May 10, 2004 at 05:37:21PM -0400, Eduardo Elgueta wrote: >> >>> I have some basic audit fields in my tables, that I used to update in >>> my rudimentary ORM superclass. >>> >>> I've seen in the FAQ/docs that SQLObject classes are not meant to be >>> subclassed >> >> >> >> There was a Daniel Savard's inheritance patch. I cannot find it on >> the web, but I have patches for SQLObject 0.5.1 and 0.5.2. >> >> Oleg. > > > > > ------------------------------------------------------- > This SF.Net email is sponsored by Sleepycat Software > Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to > deliver higher performing products faster, at low TCO. > http://www.sleepycat.com/telcomwpreg.php?From=osdnemail3 |
From: Oleg B. <ph...@ma...> - 2004-05-11 21:07:41
|
On Tue, May 11, 2004 at 04:56:33PM -0400, Eduardo Elgueta wrote: > What I would like to have is something like this: > > def MyOnUpdate (sqlObj): > sqlObj.addColumn(StringCol("updateBy", "me"); > sqlObj.addColumn(DateTimeCol("updateOn", now()); > > def MyOnInsert (sqlObj): > sqlObj.addColumn(StringCol("createdBy", "me"); > sqlObj.addColumn(DateTimeCol("createdOn", now()); > MyOnUpdate(sqlObj) > > def MySQLObject1 (SQLObject): > myField = StringCol() > onInsert = MyOnInsert(self) > onUpdate = MyOnUpdate(self) > > def MySQLObject2 (SQLObject): > myField = IntCol() > onInsert = MyOnInsert(self) > onUpdate = MyOnUpdate(self) > > What I need is to find the place to insert the self.onUpdate(self) call. > > PS: In other words, I need triggers which MySQL (my database backend) > does not provide. I do not understand what you really want. May be all you need is jsut an accessor? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Eduardo E. <eel...@na...> - 2004-05-11 21:24:13
|
Oleg, My mistake. I meant this: def MyOnUpdate (sqlObj): sqlObj.addColumn(StringCol("updateBy", "me"); sqlObj.addColumn(DateTimeCol("updateOn", now()); def MyOnInsert (sqlObj): sqlObj.addColumn(StringCol("createdBy", "me"); sqlObj.addColumn(DateTimeCol("createdOn", now()); MyOnUpdate(sqlObj) class MySQLObject1 (SQLObject): myField = StringCol() onInsert = MyOnInsert(self) onUpdate = MyOnUpdate(self) class MySQLObject2 (SQLObject): myField = IntCol() onInsert = MyOnInsert(self) onUpdate = MyOnUpdate(self) Ed. Oleg Broytmann wrote: > On Tue, May 11, 2004 at 04:56:33PM -0400, Eduardo Elgueta wrote: > >>What I would like to have is something like this: >> >>def MyOnUpdate (sqlObj): >> sqlObj.addColumn(StringCol("updateBy", "me"); >> sqlObj.addColumn(DateTimeCol("updateOn", now()); >> >>def MyOnInsert (sqlObj): >> sqlObj.addColumn(StringCol("createdBy", "me"); >> sqlObj.addColumn(DateTimeCol("createdOn", now()); >> MyOnUpdate(sqlObj) >> >>def MySQLObject1 (SQLObject): >> myField = StringCol() >> onInsert = MyOnInsert(self) >> onUpdate = MyOnUpdate(self) >> >>def MySQLObject2 (SQLObject): >> myField = IntCol() >> onInsert = MyOnInsert(self) >> onUpdate = MyOnUpdate(self) >> >>What I need is to find the place to insert the self.onUpdate(self) call. >> >>PS: In other words, I need triggers which MySQL (my database backend) >>does not provide. > > > I do not understand what you really want. May be all you need is jsut > an accessor? > > Oleg. |
From: Oleg B. <ph...@ma...> - 2004-05-11 22:07:04
|
On Tue, May 11, 2004 at 05:24:05PM -0400, Eduardo Elgueta wrote: > My mistake. I meant this: Too much code. But what do you want to achieve? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Eduardo E. <eel...@na...> - 2004-05-11 22:36:16
|
Oleg, I want to achieve trigger functionality: to call some function when data is about to be committed to the database, and have the chance to modify some values. See Luke's response. Thanks. Ed. Oleg Broytmann wrote: > On Tue, May 11, 2004 at 05:24:05PM -0400, Eduardo Elgueta wrote: > >>My mistake. I meant this: > > > Too much code. But what do you want to achieve? > > Oleg. |
From: Luke O. <lu...@me...> - 2004-05-11 21:50:49
|
It's been a while since I've done this (ie, a couple revs of SQLObject ago), but the limitations on inheritance in SQLObject have always been about concrete classes, and since 0.4 or so, abstract columns could be inherited. It is quite possible to define common elements in abstract base classes, I use it regularly to have a common _connection. Again, the subclassing limitation is that every subclass table will be expected to actually have those columns, but I think that's exactly your situation. So (untested) I think this will do what you want for *create*: class MainObject(SQLObject): createdBy = StringCol(default="me") createdOn = DateTimeCol(default=now) class MyObjOne(MainObject): otherColumn = ... class MyObjTwo(MainObject): someColumn = ... (Note the default=now, not default=now() - SQLObject will use it as a callback since you don't want the value of now() as of creation time, but at insert). Shouldn't have to override anything else, the default will be inserted if you ignore those column names for your subclasses. Now, for update you'd have to override _SO_setValue() and set(). (Again, untested, and based on 0.5 that I have in front of me.) I'm going to treat all _SO_setValue calls as effectively set() calls, since we now have multiple values. class MainObject(SQLObject): createdBy = StringCol(default="me") createdOn = DateTimeCol(default=now) updateBy = StringCol(default="me") updateOn = DateTimeCol(default=now) def _SO_setValue(self, name, value, fromPython): if fromPython: value = fromPython(value, self._SO_validatorState) updates = {'updateBy': "me", 'updateOn': now(), name: value} self.set(**updates) def set(self, **kw): if not kw.get('updateOn',None): kw['updateOn'] = now() if not kw.get('updateBy',None): kw['updateBy'] = "me" super(MainObject, self).set(**kw) Disclaimer again: this is looking at SO 0.5, I'm not sure I really like it, it would be nice to write a database-agnostic trigger system as part of SQLObject. But I certainly don't have the time for it (it was was mentioned on the list a long time ago, nothing really came of it). And my example may not work at all because of something I've forgotten. Hopefully it gets you on a workable track though. - Luke Quoting Eduardo Elgueta <eel...@na...>: > Oleg and anyone else, > > I tried Daniel Savard's patch and I can see I could use it to achieve > what I want, but creating an unnatural hierarchy. > > What I would like to have is something like this: > > def MyOnUpdate (sqlObj): > sqlObj.addColumn(StringCol("updateBy", "me"); > sqlObj.addColumn(DateTimeCol("updateOn", now()); > > def MyOnInsert (sqlObj): > sqlObj.addColumn(StringCol("createdBy", "me"); > sqlObj.addColumn(DateTimeCol("createdOn", now()); > MyOnUpdate(sqlObj) > > def MySQLObject1 (SQLObject): > myField = StringCol() > onInsert = MyOnInsert(self) > onUpdate = MyOnUpdate(self) > > def MySQLObject2 (SQLObject): > myField = IntCol() > onInsert = MyOnInsert(self) > onUpdate = MyOnUpdate(self) > > What I need is to find the place to insert the self.onUpdate(self) call. > Any suggestion? > > TIA, > > Ed. > > PS: In other words, I need triggers which MySQL (my database backend) > does not provide. > > Eduardo Elgueta wrote: >> Oleg, >> >> Thank you for your answer. >> >> I'm not sure inheritance is what I need, but I'll certainly give it >> a try. Where can I get that patch? >> >> Ed. >> >> Oleg Broytmann wrote: >> >>> On Mon, May 10, 2004 at 05:37:21PM -0400, Eduardo Elgueta wrote: >>> >>>> I have some basic audit fields in my tables, that I used to update >>>> in my rudimentary ORM superclass. >>>> >>>> I've seen in the FAQ/docs that SQLObject classes are not meant to >>>> be subclassed >>> >>> >>> >>> There was a Daniel Savard's inheritance patch. I cannot find it on >>> the web, but I have patches for SQLObject 0.5.1 and 0.5.2. >>> >>> Oleg. >> >> >> >> >> ------------------------------------------------------- >> This SF.Net email is sponsored by Sleepycat Software >> Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to >> deliver higher performing products faster, at low TCO. >> http://www.sleepycat.com/telcomwpreg.php?From=osdnemail3 > > > > ------------------------------------------------------- > This SF.Net email is sponsored by Sleepycat Software > Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to > deliver higher performing products faster, at low TCO. > http://www.sleepycat.com/telcomwpreg.php?From=osdnemail3 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss -- The Pursuit of Counterfactual Histories |
From: Eduardo E. <eel...@na...> - 2004-05-11 22:50:39
|
Luke Opperman wrote: > It's been a while since I've done this (ie, a couple revs of SQLObject=20 > ago), > but the limitations on inheritance in SQLObject have always been about > concrete classes, and since 0.4 or so, abstract columns could be=20 > inherited. It > is quite possible to define common elements in abstract base classes,=20 > I use it > regularly to have a common _connection. Again, the subclassing=20 > limitation is > that every subclass table will be expected to actually have those=20 > columns, but > I think that's exactly your situation. That's right. > > So (untested) I think this will do what you want for *create*: > > class MainObject(SQLObject): > createdBy =3D StringCol(default=3D"me") > createdOn =3D DateTimeCol(default=3Dnow) > > class MyObjOne(MainObject): > otherColumn =3D ... > > class MyObjTwo(MainObject): > someColumn =3D ... > > > (Note the default=3Dnow, not default=3Dnow() - SQLObject will use it as= a=20 > callback > since you don't want the value of now() as of creation time, but at=20 > insert). > Shouldn't have to override anything else, the default will be inserted=20 > if you > ignore those column names for your subclasses. Ok. > > Now, for update you'd have to override _SO_setValue() and set(). (Again= , > untested, and based on 0.5 that I have in front of me.) I'm going to=20 > treat all > _SO_setValue calls as effectively set() calls, since we now have multip= le > values. > > > class MainObject(SQLObject): > createdBy =3D StringCol(default=3D"me") > createdOn =3D DateTimeCol(default=3Dnow) > updateBy =3D StringCol(default=3D"me") > updateOn =3D DateTimeCol(default=3Dnow) > > def _SO_setValue(self, name, value, fromPython): > if fromPython: > value =3D fromPython(value, self._SO_validatorState) > updates =3D {'updateBy': "me", 'updateOn': now(), name: value} > self.set(**updates) > > def set(self, **kw): > if not kw.get('updateOn',None): > kw['updateOn'] =3D now() > if not kw.get('updateBy',None): > kw['updateBy'] =3D "me" > super(MainObject, self).set(**kw) > > > Disclaimer again: this is looking at SO 0.5, I'm not sure I really=20 > like it, it > would be nice to write a database-agnostic trigger system as part of > SQLObject. But I certainly don't have the time for it (it was was=20 > mentioned on > the list a long time ago, nothing really came of it). > I understand this may not work. I'll see if it applies to the release I=20 have. However, I'm wondering why you have to override _SO_setValue and=20 set. I think set does all the job. And second, from that code It seems to me that the call to self.onUpdate=20 should be made in the set method, because (I think) every field update=20 goes through this code. Obviously, it'd be better to have that event=20 function called once, right before sending the sql statment to the=20 database, but this would be enough for me, at least by now. > And my example may not work at all because of something I've forgotten. > Hopefully it gets you on a workable track though. > > - Luke > Thank you very much. Ed. > Quoting Eduardo Elgueta <eel...@na...>: > >> Oleg and anyone else, >> >> I tried Daniel Savard's patch and I can see I could use it to achieve >> what I want, but creating an unnatural hierarchy. >> >> What I would like to have is something like this: >> >> def MyOnUpdate (sqlObj): >> sqlObj.addColumn(StringCol("updateBy", "me"); >> sqlObj.addColumn(DateTimeCol("updateOn", now()); >> >> def MyOnInsert (sqlObj): >> sqlObj.addColumn(StringCol("createdBy", "me"); >> sqlObj.addColumn(DateTimeCol("createdOn", now()); >> MyOnUpdate(sqlObj) >> >> def MySQLObject1 (SQLObject): >> myField =3D StringCol() >> onInsert =3D MyOnInsert(self) >> onUpdate =3D MyOnUpdate(self) >> >> def MySQLObject2 (SQLObject): >> myField =3D IntCol() >> onInsert =3D MyOnInsert(self) >> onUpdate =3D MyOnUpdate(self) >> >> What I need is to find the place to insert the self.onUpdate(self) cal= l. >> Any suggestion? >> >> TIA, >> >> Ed. >> >> PS: In other words, I need triggers which MySQL (my database backend) >> does not provide. >> >> Eduardo Elgueta wrote: >> >>> Oleg, >>> >>> Thank you for your answer. >>> >>> I'm not sure inheritance is what I need, but I'll certainly give it=20 >>> a try. Where can I get that patch? >>> >>> Ed. >>> >>> Oleg Broytmann wrote: >>> >>>> On Mon, May 10, 2004 at 05:37:21PM -0400, Eduardo Elgueta wrote: >>>> >>>>> I have some basic audit fields in my tables, that I used to update=20 >>>>> in my rudimentary ORM superclass. >>>>> >>>>> I've seen in the FAQ/docs that SQLObject classes are not meant to=20 >>>>> be subclassed >>>> >>>> >>>> >>>> >>>> There was a Daniel Savard's inheritance patch. I cannot find it o= n >>>> the web, but I have patches for SQLObject 0.5.1 and 0.5.2. >>>> >>>> Oleg. >>> >>> >>> >>> >>> >>> ------------------------------------------------------- >>> This SF.Net email is sponsored by Sleepycat Software >>> Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to=20 >>> deliver higher performing products faster, at low TCO. >>> http://www.sleepycat.com/telcomwpreg.php?From=3Dosdnemail3 >> >> >> >> >> ------------------------------------------------------- >> This SF.Net email is sponsored by Sleepycat Software >> Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to >> deliver higher performing products faster, at low TCO. >> http://www.sleepycat.com/telcomwpreg.php?From=3Dosdnemail3 >> _______________________________________________ >> sqlobject-discuss mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > > > > --=20 > The Pursuit of Counterfactual Histories > --=20 Eduardo Elgueta Senior Consultant Navix correo/email: eel...@na... tel=E9fono/phone: +56 (2) 204-5823 celular/mobile: +56 (9) 821-0033 web: www.navix.cl Av. Pedro de Valdivia 555 Of. 216 Providencia 7500913 Santiago, Chile |
From: David W. <da...@su...> - 2004-05-11 23:14:46
|
Ed, > I have some basic audit fields in my tables, that I used to update in my > rudimentary ORM superclass. It seems to me that this type of logic is best (for performance and so that all database updates are audited) and simplest written as triggers in the database. Why not? There are several free databases that support triggers such as Firebird and Postgesql. Regards Dave -- David Warnock, Sundayta Ltd. http://www.sundayta.com iDocSys for Document Management. VisibleResults for Fundraising. Development and Hosting of Web Applications and Sites. |
From: Luke O. <lu...@me...> - 2004-05-12 00:21:38
|
I agree with you that currently his best bet is in-database triggers. And if you use PostgreSQL, your triggers can even be written in python. But a large part of the draw of SQLObject is database independence. Some databases do not support triggers, and most that do having varying levels of support. Instead, what if SQLObject (or an agnostic module) provided an object-level concept of triggers? Like the idea of putting constraints on the python side, this would both allow us to implement functionality that is missing from most of the databases we work with, and be able to transport between database systems. There are other areas to improve as well in becoming a completely database-agnostic data management system, and I'd like to make it act more relational than network-based where reasonable. But triggers is as good a place to start as any. Triggers and constraints are part of the same ideas, and contraints may be a logical place to add generic triggers. Single column constraints Inter-column constraints Single table constraints Inter-table constraints And a few others I can't think of right now. But it starts to illustrate why simply adding insert/update/delete triggers doesn't satisfy. Thoughts to think about, I won't make any commitments to starting to implement these things, but maybe someone else is interested, I'll certainly carry on discussion. - Luke Quoting David Warnock <da...@su...>: > It seems to me that this type of logic is best (for performance and so > that all database updates are audited) and simplest written as triggers > in the database. > > Why not? There are several free databases that support triggers such as > Firebird and Postgesql. |
From: David W. <da...@su...> - 2004-05-12 02:04:17
|
Luke, > But a large part of the draw of SQLObject is database independence. Some > databases do not support triggers, and most that do having varying > levels of > support. H'mm, yes that is one aspect of a tool like SQLObject, but there are others such as ease of updating application to revised database structure. A lot depends on the problem domain. In a corporate environment the database is often used by multiple applications and has a longer life than individual applications. Therefore logic in the database is cheaper. Anyway, if you dbms does not have triggers choose a proper dbms ;-) > Instead, what if SQLObject (or an agnostic module) provided an object-level > concept of triggers? Like the idea of putting constraints on the python > side, > this would both allow us to implement functionality that is missing from > most > of the databases we work with, and be able to transport between database > systems. There are other areas to improve as well in becoming a completely > database-agnostic data management system, and I'd like to make it act more > relational than network-based where reasonable. But triggers is as good a > place to start as any. I would agree. Generic places to put code for for validation, calculation, integrrity checks, security checks, cross db updates, mailing updates, replication, auditing etc are all essential. Maybe one day there could even be a deployment option to choose which SQLObject code runs in the client and which in the dbms as triggers ;-) Dave -- David Warnock, Sundayta Ltd. http://www.sundayta.com iDocSys for Document Management. VisibleResults for Fundraising. Development and Hosting of Web Applications and Sites. |
From: Eduardo E. <eel...@na...> - 2004-05-12 15:35:42
|
David Warnock wrote: > Luke, > > A lot depends on the problem domain. In a corporate environment the > database is often used by multiple applications and has a longer life > than individual applications. Therefore logic in the database is cheaper. > > Anyway, if you dbms does not have triggers choose a proper dbms ;-) I don't agree. In my case, it used to be so two or three years ago. Nowadays, most of my customers want business objects residing in an application server and I've been relatively successful implementing this concept with Webware/Python (and this is why I'm starting to use SQLObejct). In fact, thats the whole idea in Java EE and Web Services, in which I've completely achieved that: various applications/clients using an application object implementing business rules. Part of the explanation for this is databases are not that "stable" any more. For instance, one of my customers (a large company, by my country standards) is favoring MS SQL Server, because of Oracle licensing price changes. Now they find they have to migrate database code if they want to get rid of Oracle. Other customers are migrating to OS database systems (MySQL, Postgres). Yet, other customers complain when the find their application code is not "centralized", but scattered in application programs, database code and batch tasks. The new rationale is: let's put the code in application code, so we get a single application source which we don't have to migrate/rewrite the next time we upgrade/switch DBMS (Oracle/SQL Server to OSDMBS) or client (windows, html/javascript, WAP, etc). > Maybe one day there could even be a deployment option to choose which > SQLObject code runs in the client and which in the dbms as triggers ;-) > > Dave By the time being, I would be very grateful if someone can give some pointers on how to implement this trigger functionality for SQLObject. Luke has given me some. May be later I could post a patch or something in return for the help. Regards. Ed. |