Thread: [SQLObject] Having a modified column filled in automagically...
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Sam's L. <sam...@gm...> - 2008-08-17 10:46:20
|
Hi... I'd like to be able to create a timestamp column called "mtime" in some of my columns. This would hold a timestamp of when the row was last modified. But I'd like the timestamp to be updated automatically whenever sqlobject updates anything else in the row. How can I do this? I'd like a solution that is easy to add to any table....maybe so easy that the table only has to have a column called mtime and then everything happens without anything extra on my part. Thanks |
From: P. J. <pet...@tp...> - 2008-08-17 11:45:25
|
> > > I'd like to be able to create a timestamp column called "mtime" in some of > my columns. This would hold a timestamp of when the row was last modified. > > But I'd like the timestamp to be updated automatically whenever sqlobject > updates anything else in the row. > > How can I do this? I'd like a solution that is easy to add to any > table....maybe so easy that the table only has to have a column called mtime > and then everything happens without anything extra on my part. > > Thanks > from datetime import datetime tStamp = DateTimeCol(default=datetime.now) HTH Petr Jakes |
From: Golemon, B. <Buc...@am...> - 2008-08-18 23:13:53
|
I'm considering using SQLObject for a new big project (and submitting patches to make the tests pass under mysql), but I want to convince myself that SQLObject would have worked for my old project first. I built my own quasi-ORM (before I knew that ORM's existed) that allowed a user to specify a filter on any column in the database, and the system would join it into the query. All that needed to be specified was the fields to query and a set of filters. The resulting query was something like this: SELECT DISTINCT flows.FlowSubTarget, MAX(projects.FCassy) AS MAX, projects.IsRelease FROM files, names, projects, flows, values_i WHERE names.MetricType = 'i' AND names.FlowId = flows.id AND projects.id = files.ProjectId AND projects.MaxMTime > DATE_SUB(NOW(), INTERVAL 10 DAY) AND flows.FlowSubTarget RLIKE BINARY '^Sort.*(Stp|Setup)' AND projects.Project = 'peach' AND files.id = values_i.FileId AND projects.FCassy >= 0 AND projects.Tapeout = 'sa15' AND values_i.NameId = names.id GROUP BY flows.FlowSubTarget, projects.IsRelease; Note that the query returns values from various tables. The most bothersome thing to me about ORM's is that they seem to only return data from one specific table at a time. The code that generated this query went something like the following. (Not exactly because parts were from configuration files and parts were from previous queries, making the whole thing dynamic). q = db.select("FlowSubTarget", alias(max("FCassy"), "MAX"), "IsRelease") q.filters( MetricType = 'i', Project = 'mario', Tapeout = 'sa11' ) q.add_filter(MaxMTime, ">", "DATE_SUB(NOW(), INTERVAL 10 DAY)") q.add_filter(FlowSubTarget, "RLIKE BINARY", "'^Sort.*(Stp|Setup)'") q.add_filter(FCassy, ">=", 0) q.group_by("FlowSubTarget", "IsRelease") print q I understand that automatic joining like this is not generally a good idea, but it's necessary in this case because the fields to filter are not known till run time. The result is deterministic because the database has no cycles (aka. tree topology). My question is: Could I have used SQLObject to do this (reasonably)? Thanks ahead of time for any light you can shed on this subject. --Buck -----Original Message----- From: sql...@li... [mailto:sql...@li...] On Behalf Of Sam's Lists Sent: Sunday, August 17, 2008 3:46 AM To: sqlobject-discuss Subject: [SQLObject] Having a modified column filled in automagically... Hi... I'd like to be able to create a timestamp column called "mtime" in some of my columns. This would hold a timestamp of when the row was last modified. But I'd like the timestamp to be updated automatically whenever sqlobject updates anything else in the row. How can I do this? I'd like a solution that is easy to add to any table....maybe so easy that the table only has to have a column called mtime and then everything happens without anything extra on my part. Thanks |
From: Oleg B. <ph...@ph...> - 2008-08-19 09:29:14
|
Hello! On Mon, Aug 18, 2008 at 04:13:35PM -0700, Golemon, Buck wrote: > The most > bothersome thing to me about ORM's is that they seem to only return data > from one specific table at a time. Well, SQLObject is not better in this aspect - its main .select() method only returns columns from one table. Well, the table could be actually a virtual table - that is, a VIEW, but you have to create the VIEW in advance and describe it in Python using SQLObject's language. > q = db.select("FlowSubTarget", alias(max("FCassy"), "MAX"), "IsRelease") > q.filters( > MetricType = 'i', > Project = 'mario', > Tapeout = 'sa11' > ) > q.add_filter(MaxMTime, ">", "DATE_SUB(NOW(), INTERVAL 10 DAY)") > q.add_filter(FlowSubTarget, "RLIKE BINARY", "'^Sort.*(Stp|Setup)'") > q.add_filter(FCassy, ">=", 0) > q.group_by("FlowSubTarget", "IsRelease") > print q SQLObject classes do not support GROUP BY and a list of columns from different tables (both limitations are for one reason, actually). But there is a lower level mechanism in SQLObject - called SQLBuilder - that can do both. You query would be roughly translated to SQLBuilder's language like this: from sqlobject.sqlbuilder import * columns = ("FlowSubTarget", func.max("FCassy"), "IsRelease") q = [] q.append(Flow.q.id == Names.q.flow) # Do the join q.append(SQLOp(MaxMTime, ">", "DATE_SUB(NOW(), INTERVAL 10 DAY)")) q.append(SQLOp(FlowSubTarget, "RLIKE BINARY", "'^Sort.*(Stp|Setup)'")) q.append(SQLOp(FCassy, ">=", 0)) query = AND(*q) for row in connection.query(connection.sqlrepr( Select(columns, where=query, groupBy=("FlowSubTarget", "IsRelease")) )): print row Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Golemon, B. <Buc...@am...> - 2008-08-19 18:17:22
|
Thanks very much for your consideration. There were actually four joins that were done automatically. It looks like I'd have to use your sqlbuilder library and write a good amount of code on top to do the auto-joining. I'd also have to do without the ORM component completely. Am I understanding correctly? I'm trying to avoid the NIH syndrome, but seem to be failing... Thanks again! --Buck -----Original Message----- From: sql...@li... [mailto:sql...@li...] On Behalf Of Oleg Broytmann Sent: Tuesday, August 19, 2008 2:29 AM To: sql...@li... Subject: Re: [SQLObject] Can SQLObject do this? Hello! On Mon, Aug 18, 2008 at 04:13:35PM -0700, Golemon, Buck wrote: > The most > bothersome thing to me about ORM's is that they seem to only return data > from one specific table at a time. Well, SQLObject is not better in this aspect - its main .select() method only returns columns from one table. Well, the table could be actually a virtual table - that is, a VIEW, but you have to create the VIEW in advance and describe it in Python using SQLObject's language. > q = db.select("FlowSubTarget", alias(max("FCassy"), "MAX"), "IsRelease") > q.filters( > MetricType = 'i', > Project = 'mario', > Tapeout = 'sa11' > ) > q.add_filter(MaxMTime, ">", "DATE_SUB(NOW(), INTERVAL 10 DAY)") > q.add_filter(FlowSubTarget, "RLIKE BINARY", "'^Sort.*(Stp|Setup)'") > q.add_filter(FCassy, ">=", 0) > q.group_by("FlowSubTarget", "IsRelease") > print q SQLObject classes do not support GROUP BY and a list of columns from different tables (both limitations are for one reason, actually). But there is a lower level mechanism in SQLObject - called SQLBuilder - that can do both. You query would be roughly translated to SQLBuilder's language like this: from sqlobject.sqlbuilder import * columns = ("FlowSubTarget", func.max("FCassy"), "IsRelease") q = [] q.append(Flow.q.id == Names.q.flow) # Do the join q.append(SQLOp(MaxMTime, ">", "DATE_SUB(NOW(), INTERVAL 10 DAY)")) q.append(SQLOp(FlowSubTarget, "RLIKE BINARY", "'^Sort.*(Stp|Setup)'")) q.append(SQLOp(FCassy, ">=", 0)) query = AND(*q) for row in connection.query(connection.sqlrepr( Select(columns, where=query, groupBy=("FlowSubTarget", "IsRelease")) )): print row Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. ------------------------------------------------------------------------ - This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ sqlobject-discuss mailing list sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Oleg B. <ph...@ph...> - 2008-08-19 18:25:23
|
On Tue, Aug 19, 2008 at 11:17:00AM -0700, Golemon, Buck wrote: > Thanks very much for your consideration. There were actually four joins > that were done automatically. SQLObject can do joins only when the user declares what (s)he wants using ForeignKey/MultipleJoin/RelatedJoin, but it cannot combine these automatic joins with GROUP BY. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Golemon, B. <Buc...@am...> - 2008-08-20 18:43:30
|
Thanks for being so helpful. Neglecting the 'group by', how would you do this? If you want, I can look into submitting a patch to add 'group by' capability. --Buck -----Original Message----- From: sql...@li... [mailto:sql...@li...] On Behalf Of Oleg Broytmann Sent: Tuesday, August 19, 2008 11:25 AM To: sql...@li... Subject: Re: [SQLObject] Can SQLObject do this? On Tue, Aug 19, 2008 at 11:17:00AM -0700, Golemon, Buck wrote: > Thanks very much for your consideration. There were actually four joins > that were done automatically. SQLObject can do joins only when the user declares what (s)he wants using ForeignKey/MultipleJoin/RelatedJoin, but it cannot combine these automatic joins with GROUP BY. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. ------------------------------------------------------------------------ - This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ sqlobject-discuss mailing list sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Oleg B. <ph...@ph...> - 2008-08-20 18:57:12
|
On Wed, Aug 20, 2008 at 11:42:56AM -0700, Golemon, Buck wrote: > Neglecting the 'group by', how would you do this? If you want, I can Two-tables join is done automatically - just declare relationship using ForeigmKey/MultipleJoin/RelatedJoin. Three (or more) tables joins, unfortunately, require more work. IWBN to solve this. > look into submitting a patch to add 'group by' capability. It is not that easy. Look, when you declare a table you also declare the names and types of the columns: class MyTable(SQLObject): col1 = Type1Col() col2 = Type2Col() and when you call MyTable.select() SQLObject knows the list of columns. With GROUP BY you have to pass a different list of columns; what would be an API? sqlbuilder.Select() can do groupBy because it knows the list of columns and ignores their types altogether. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Golemon, B. <Buc...@am...> - 2008-08-20 20:54:07
|
To do multiple-table joins needs to do a graph search. More specifically a Steiner graph search. I have an implementation that I was planning to put on top of SQLObject to achieve this. If you want to think about adding this as a feature to SQLO, I'd be very happy to talk about it. For the groupby api, I was thinking something like: MyTable.select().groupby("col1", "col2") or maybe MyTable.select().groupby(MyTable.col1, MyTable.col2) or even MyTable.select(..., groupby=("col1", "col2")) Whichever fits the existing style best. Assuming select() returns some query object, that object would need to grow a groupby(*argv) method (in the first two options). I'm not sure how the column types are relevant here. --Buck -----Original Message----- From: sql...@li... [mailto:sql...@li...] On Behalf Of Oleg Broytmann Sent: Wednesday, August 20, 2008 11:57 AM To: sql...@li... Subject: Re: [SQLObject] Can SQLObject do this? On Wed, Aug 20, 2008 at 11:42:56AM -0700, Golemon, Buck wrote: > Neglecting the 'group by', how would you do this? If you want, I can Two-tables join is done automatically - just declare relationship using ForeigmKey/MultipleJoin/RelatedJoin. Three (or more) tables joins, unfortunately, require more work. IWBN to solve this. > look into submitting a patch to add 'group by' capability. It is not that easy. Look, when you declare a table you also declare the names and types of the columns: class MyTable(SQLObject): col1 = Type1Col() col2 = Type2Col() and when you call MyTable.select() SQLObject knows the list of columns. With GROUP BY you have to pass a different list of columns; what would be an API? sqlbuilder.Select() can do groupBy because it knows the list of columns and ignores their types altogether. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. ------------------------------------------------------------------------ - This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ sqlobject-discuss mailing list sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Oleg B. <ph...@ph...> - 2008-08-20 21:20:57
|
On Wed, Aug 20, 2008 at 01:52:51PM -0700, Golemon, Buck wrote: > To do multiple-table joins needs to do a graph search. More specifically > a Steiner graph search. I have an implementation that I was planning to > put on top of SQLObject to achieve this. If you want to think about > adding this as a feature to SQLO, I'd be very happy to talk about it. SQLObject-style thinking is, as far as I understand it, to declare relationships explicitly. So if SQLObject know there are Table1, Table2 and Table3, and Table1 linked to Table2 with a straight join Table1.q.t2_id == Table2.q.id Table2 is joined with Table3 Table2.q.t3_id == Table3.q.id then SQLObject can construct 3-tables join just AND'ing these conditions, right? (Table1.q.t2_id == Table2.q.id) & (Table2.q.t3_id == Table3.q.id) Isn't it enough? > For the groupby api, I was thinking something like: > > MyTable.select().groupby("col1", "col2") > or maybe > MyTable.select().groupby(MyTable.col1, MyTable.col2) > or even > MyTable.select(..., groupby=("col1", "col2")) > > Whichever fits the existing style best. GROUP BY is used with aggregate functions. Your examples lists columns to GROUP BY by, but where is an aggregate? > I'm not sure how the column types are relevant here. For MyTable.select() SQLObject knows what columns to return and how to convert them from SQL to Python. With your proposed API SQLObject doesn't know what columns to return, how to name them in SQL and in Python, how to convert values from SQL to Python... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sam's L. <sam...@gm...> - 2008-08-19 00:55:30
|
Okay, unless I'm missing something your answer only works for creation time. I want it to record modification time, which is different then creation time (except of course at the time of creation, in which case they'd be the same thing.) Thanks Sam On Sun, Aug 17, 2008 at 4:45 AM, Petr Jakeš <pet...@tp...> wrote: > >> I'd like to be able to create a timestamp column called "mtime" in some of >> my columns. This would hold a timestamp of when the row was last modified. >> >> But I'd like the timestamp to be updated automatically whenever sqlobject >> updates anything else in the row. >> >> How can I do this? I'd like a solution that is easy to add to any >> table....maybe so easy that the table only has to have a column called mtime >> and then everything happens without anything extra on my part. >> >> Thanks >> > > from datetime import datetime > tStamp = DateTimeCol(default=datetime.now) > > HTH > > Petr Jakes > |
From: Oleg B. <ph...@ph...> - 2008-08-19 09:08:20
|
On Mon, Aug 18, 2008 at 05:55:28PM -0700, Sam's Lists wrote: > Okay, unless I'm missing something your answer only works for creation > time. I want it to record modification time, which is different then > creation time (except of course at the time of creation, in which case > they'd be the same thing.) For record modification override .set() method and modify your program to always call .set(); never modify a single attribute via assignment. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-08-19 14:51:21
|
On Tuesday 19 August 2008 04:08:13 Oleg Broytmann wrote: > On Mon, Aug 18, 2008 at 05:55:28PM -0700, Sam's Lists wrote: > > Okay, unless I'm missing something your answer only works for creation > > time. I want it to record modification time, which is different then > > creation time (except of course at the time of creation, in which case > > they'd be the same thing.) > > For record modification override .set() method and modify your program > to always call .set(); never modify a single attribute via assignment. You could also try overriding column attributes: http://www.sqlobject.org/SQLObject.html#overriding-column-attributes e.g. """ class Foo(SQLObject): lastModified = sqlobject.DateTimeCol() number = sqlobject.IntCol() def _set_number(self, value): self.lastModified = datetime.datetime.now() self._SO_set_number(value) """ cs |
From: Sam's L. <sam...@gm...> - 2008-08-26 08:59:59
|
A belated thank you to Oleg and Christopher...but it seems like neither of these suggestions really are universal enough. Oleg's suggestion to override set is good, but I don't want to lose the ability to assign directly to columns. Christopher's suggestion is also good, but I have too many columns to override the column attributes for each of them. I read through some of the source and found RowUpdateSignal. It seems like the best thing will be to catch this, and then just set any column called m_time with the current time. As soon as I have some time I'll try it, and I'll report back. In the meantime if anyone knows why this is the wrong track or has a suggestion, please let me know. Thanks On Tue, Aug 19, 2008 at 7:51 AM, Christopher Singley <csi...@gm...>wrote: > On Tuesday 19 August 2008 04:08:13 Oleg Broytmann wrote: > > On Mon, Aug 18, 2008 at 05:55:28PM -0700, Sam's Lists wrote: > > > Okay, unless I'm missing something your answer only works for creation > > > time. I want it to record modification time, which is different then > > > creation time (except of course at the time of creation, in which case > > > they'd be the same thing.) > > > > For record modification override .set() method and modify your program > > to always call .set(); never modify a single attribute via assignment. > > You could also try overriding column attributes: > http://www.sqlobject.org/SQLObject.html#overriding-column-attributes > > e.g. > > """ > class Foo(SQLObject): > lastModified = sqlobject.DateTimeCol() > number = sqlobject.IntCol() > > def _set_number(self, value): > self.lastModified = datetime.datetime.now() > self._SO_set_number(value) > """ > > |
From: Andres F. <an...@an...> - 2008-08-26 10:46:44
|
Hi, On Sunday 17 August 2008, Sam's Lists wrote in "[SQLObject] Having a modified column filled in automagically...": > I'd like to be able to create a timestamp column called "mtime" in some of > my columns. This would hold a timestamp of when the row was last modified. Triggers on the database side? Andres |
From: Sam's L. <sam...@gm...> - 2008-08-27 01:15:21
|
Triggers might work, if the only thing I wanted was the modification time. But I want to be able to fill in things like the user as well. Here's what I came up with...it seems to work well: def m_update_listener(instance, kwargs, **kw): kwargs['m_user_id'] = turbogears.identity.current.user.id kwargs['m_time'] = datetime.datetime.utcnow() sqlobject.events.listen(m_update_listener, PeopleRecord, sqlobject.events.RowUpdateSignal) The only issue is that I originally had the m_user_id as a foreign key to a user table. I tried every combination I could think of to make this work, but it always just left TurboGears in a paused state (no error messages...it just didn't continue on after leaving the update_listener.) I tried using m_user---I tried using m_user_id, I tried dropping the '.id' on the right hand side...no dice! Finally I got rid of the ForeignKey in my model (but kept it in the database). Instead of a ForeignKey I used a simple IntCol---and success. Thanks! On Tue, Aug 26, 2008 at 3:46 AM, Andres Freund <an...@an...> wrote: > Hi, > > On Sunday 17 August 2008, Sam's Lists wrote in "[SQLObject] Having a > modified > column filled in automagically...": > > I'd like to be able to create a timestamp column called "mtime" in some > of > > my columns. This would hold a timestamp of when the row was last > modified. > Triggers on the database side? > > Andres > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win great > prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > |
From: Buck G. <buc...@am...> - 2008-08-27 17:48:53
|
In mySQL the first two TIMESTAMP-type columns have special meanings like this (one is creation, other is modification, not sure which). http://dev.mysql.com/doc/refman/5.0/en/timestamp.html Andres Freund wrote: > Hi, > > On Sunday 17 August 2008, Sam's Lists wrote in "[SQLObject] Having a modified > column filled in automagically...": > >> I'd like to be able to create a timestamp column called "mtime" in some of >> my columns. This would hold a timestamp of when the row was last modified. >> > Triggers on the database side? > > Andres > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > ------------------------------------------------------------------------ > > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |