| 00:00.21 | *** join/#storm _mup_ (~mup@li37-90.members.linode.com) |
| 00:30.22 | *** join/#storm munderwo-work (~markl@203-206-133-216.perm.iinet.net.au) |
| 00:52.04 | *** join/#storm jamesh (~james@canonical/launchpad/jamesh) |
| 00:59.07 | munderwo-work | hey jamesh! |
| 01:00.56 | munderwo-work | jamesh: is there a way apart from executing direct SQL to do left outer joins in storm? i've actually fine to do that, just thought i'd ask |
| 01:02.10 | jamesh | munderwo-work: sure |
| 01:02.49 | jamesh | You can do something like: |
| 01:03.27 | jamesh | store.using(LeftJoin(A, B, A.b_id == B.id)).find(...) |
| 01:03.34 | jamesh | where LeftJoin comes from storm.expr |
| 01:04.31 | munderwo-work | oh nice.. and can I do store.using(LeftJoin(A, B, A.b_id == B.id), LeftJoin(C, D, C.d_id == D.id)).find(...) ? |
| 01:05.21 | jamesh | yep |
| 01:05.34 | munderwo-work | awse! |
| 01:05.37 | jamesh | or nest your joins if you are linking a bunch of tables off the first |
| 01:05.38 | munderwo-work | thanks! |
| 01:06.02 | jamesh | LeftJoin(LeftJoin(A, B, A.b_id == B.id), C, A.c_id == C.id) |
| 01:06.23 | munderwo-work | ahh right.. more efficient? |
| 01:07.00 | jamesh | no. It just depends on what you want from your query |
| 01:07.32 | jamesh | the above is equivalent to "A LEFT JOIN B ON (A.b_id = B.id) LEFT JOIN C ON (A.c_id = C.id)" |
| 01:08.11 | munderwo-work | right... yup, thats what I want... I've got thee mapping tables hanging of the one, so I need to join them all up/. |
| 01:08.42 | jamesh | if you are doing unrelated left joins where you want the cross product of the results, then you can do it as two arguments to using() |
| 01:09.34 | munderwo-work | this is the SQL that I want to end up with http://dpaste.org/uh0C9/ |
| 01:10.27 | munderwo-work | they have ReferenceSets defined between the main table and the three mapping tables |
| 01:11.41 | jamesh | So you could do three nested LeftJoin()s in the using() clause, then include the tables you want in the output in the find() clause, along with those other conditions |
| 01:12.08 | jamesh | and you'd need to use Or() around those conditions, since find() uses And() to combine its arguments |
| 01:12.58 | munderwo-work | nice!, I think i've got enough info to get on with it.. Thanks jamesh, Im guessing you work for canonical? and are one of the devs? |
| 01:14.03 | jamesh | munderwo-work: yep. I've done a fair bit of work on Storm, but most of the initial design and code was written by Gustavo Niemeyer |
| 01:14.33 | munderwo-work | nice! well thanks for a great ORM. much nicer and simpler than some of the other ones out there :) |
| 01:32.19 | *** join/#storm jamesh (~james@canonical/launchpad/jamesh) |
| 01:40.42 | munderwo-work | hey jamesh: I've having a bit of trouble with the joins. this is what i've got so far.. but I need to nest them i think, but I keep getting SQL errors? and the sql isnt coming out right.. |
| 01:41.06 | munderwo-work | I separated them into different lines for readability |
| 01:43.35 | jamesh | If you want to see what SQL is being generated try adding the following commands: |
| 01:43.40 | jamesh | from storm.tracer import debug |
| 01:43.42 | jamesh | debug(True) |
| 01:44.17 | jamesh | that will give you a statement log on stderr that might help you figure out what's going on |
| 01:44.23 | munderwo-work | I've got my postgres logs logging the SQL so thats fine. |
| 01:44.50 | munderwo-work | I think I just dont understand which bit is creating the joins... |
| 01:45.10 | jamesh | so what does the storm code look like for your query? |
| 01:45.11 | munderwo-work | well to be more precise how they are interacting and how using puts them together |
| 01:45.23 | munderwo-work | sorry http://dpaste.org/ke83y/ |
| 01:45.34 | munderwo-work | I made it just didnt put it in irc ;) |
| 01:46.53 | jamesh | That looks like the joins are going in the opposite direction to the SQL paste you did earlier |
| 01:47.31 | jamesh | is that intentional? |
| 01:48.24 | munderwo-work | ahh no.. I think that might be my first problem... another set of eyes and all that.. |
| 01:49.34 | jamesh | maybe you want something more like this? http://dpaste.org/iiqiQ/ |
| 01:51.21 | jamesh | if you had wanted the joins around the way you had them, you'd probably have wanted to alias Update with ClassAlias() so you could use the table multiple times in the query |
| 01:54.55 | munderwo-work | nah, I think the paste I sent to you was just me being n00b.. |
| 01:55.57 | munderwo-work | Thanks so much, I dont think I wouldve worked out that nested left join syntax... |
| 01:57.32 | munderwo-work | I think the way I thought about it was that if I put a bunch of left joins in the using, then it should come out with the SQL that I got with the nested leftjoins(), but now I think about it I dont think i thought that hard about how a left join actually works ;) |
| 02:04.16 | *** join/#storm rodrigods_ (~rodrigods@177.98.215.73) |
| 02:04.47 | jamesh | yeah. When you chain left joins in SQL, you are taking the result of one join as the first argument of the next join |
| 02:05.09 | jamesh | so nesting the LeftJoin calls is the equivalent of that |
| 02:25.56 | *** join/#storm fcorrea (~fcorrea@187.47.69.190) |
| 02:29.20 | munderwo-work | jamesh: makes sense. once you have a resultset is there a way to remove/replace a referenceset with some other data? I keep getting a FeatureError: Assigning to ResultSets not supported ? |
| 02:30.34 | jamesh | munderwo-work: Perhaps the ResultSet.set() method will do what you're after |
| 02:30.41 | munderwo-work | ahh cool |
| 02:31.17 | jamesh | it essentially generates an UPDATE statement to change the records in the result set |
| 02:31.34 | jamesh | it won't remove the records currently showing up in that ReferenceSet though |
| 02:35.37 | munderwo-work | Hmm.. I dont seem to have a set() method on the model in the resultSet or the ReferenceSet, and as Im using a tuple in the find, to select all the tables that I want, the ResultSet is actually a tuple... |
| 02:35.54 | munderwo-work | does that make sense |
| 02:37.02 | munderwo-work | http://dpaste.org/vVer2/ |
| 02:37.16 | jamesh | there is a set() method on the resultset itself, which updates all objects in that result set |
| 02:37.36 | jamesh | that link doesn't seem to work |
| 02:37.44 | munderwo-work | soz, http://dpaste.org/sqpJv/ |
| 02:38.35 | jamesh | ah. We don't have set() working on ResultSets over multiple objects like that |
| 02:38.48 | munderwo-work | yeah, thats not really gonna work.. I need to get the results out, and then mess around with them, because I then put them through a template.. |
| 02:39.37 | jamesh | standard SQL only supports single table UPDATEs, and we haven't done anything to try and support MySQL and PostgreSQL's extensions to do multi table updates |
| 02:40.25 | munderwo-work | hrmrm... ahh well.. leave it with me.. i'll sort something out... thanks for all your help.. I think i've done something like this before.. so dont worry.. |
| 05:00.39 | *** join/#storm jamesh (~james@canonical/launchpad/jamesh) |
| 06:12.28 | *** join/#storm ant384 (~ghoul@unaffiliated/ant384) |
| 06:13.24 | *** join/#storm jamesh (~james@58-7-149-11.dyn.iinet.net.au) |
| 06:13.39 | *** join/#storm jamesh (~james@canonical/launchpad/jamesh) |
| 06:45.25 | *** join/#storm jamesh (~james@canonical/launchpad/jamesh) |
| 06:48.34 | *** join/#storm stub (~stub@node-rsa.pool-125-24.dynamic.totbb.net) |
| 06:48.34 | *** join/#storm stub (~stub@canonical/launchpad/stub) |
| 06:52.02 | *** join/#storm rrerolle (~rrerolle@neonex.fr) |
| 07:07.42 | *** join/#storm jrydberg_ (~jrydberg@94.127.35.115.c.fiberdirekt.net) |
| 07:16.36 | *** join/#storm yosafbridge (~yosafbrid@li125-242.members.linode.com) |
| 07:22.36 | *** join/#storm jkakar (~jkakar@d173-180-215-52.bchsia.telus.net) |
| 08:07.50 | *** join/#storm esteve (~quassel@91.126.88.6) |
| 08:51.28 | *** join/#storm niemeyer (~niemeyer@nat/canonical/x-mowfclinbunjixdh) |
| 08:51.29 | *** mode/#storm [+o niemeyer] by ChanServ |
| 09:36.24 | *** join/#storm bigjools (~quassel@60-241-216-21.static.tpgi.com.au) |
| 09:36.26 | *** join/#storm bigjools (~quassel@canonical/launchpad/bigjools) |
| 09:39.16 | *** join/#storm esteve (~quassel@91.126.88.6) |
| 09:49.55 | *** join/#storm bigjools (~quassel@canonical/launchpad/bigjools) |
| 10:07.52 | *** join/#storm _mup_ (~mup@li37-90.members.linode.com) |
| 11:21.01 | *** join/#storm stub (~stub@canonical/launchpad/stub) |
| 11:32.48 | *** join/#storm milosn (~milosn@5ac78e9a.bb.sky.com) |
| 11:35.54 | *** join/#storm lifeless (~robertc@122.58.129.196) |
| 11:57.20 | *** join/#storm webm0nk3y (~jdo@bip.webm0nk3y.com) |
| 11:57.36 | *** join/#storm jdobrien (~quassel@184.88.20.142) |
| 12:04.36 | *** join/#storm webm0nk3y (~jdo@bip.webm0nk3y.com) |
| 12:04.38 | *** join/#storm niemeyer (~niemeyer@nat/canonical/x-ohkvwmrrpwlcxdgo) |
| 12:04.39 | *** mode/#storm [+o niemeyer] by ChanServ |
| 12:06.33 | *** join/#storm webm0nk3y (~jdo@bip.webm0nk3y.com) |
| 12:10.56 | *** join/#storm fcorrea (~fcorrea@177.29.148.138) |
| 12:12.59 | *** join/#storm webm0nk3y (~jdo@bip.webm0nk3y.com) |
| 12:50.22 | *** part/#storm ant384 (~ghoul@unaffiliated/ant384) |
| 13:14.30 | *** join/#storm fcorrea (~fcorrea@177.120.221.25) |
| 13:48.50 | *** join/#storm awarner (~andrew@38.117.157.28) |
| 13:57.32 | *** join/#storm jml (~jml@mumak.net) |
| 13:57.32 | jml | hello |
| 13:58.17 | jml | I'm trying to make some code work with two different versions of a database (I'm renaming a column) |
| 13:58.29 | jml | the code currently uses storm.sqlobject |
| 14:00.40 | therve | step one: switch from storm.sqlobject |
| 14:02.48 | jml | therve: if you can tell me the further steps, then I might be persuaded |
| 14:03.03 | therve | :) |
| 14:03.12 | therve | I don't really know your problem |
| 14:03.27 | therve | but I can't help your if you use sqlobject |
| 14:04.23 | jml | therve: it's just a layer on top |
| 14:04.45 | jml | anyway, the problem is that I have some code like this: |
| 14:04.46 | jml | <PROTECTED> |
| 14:04.46 | jml | <PROTECTED> |
| 14:04.46 | jml | <PROTECTED> |
| 14:05.20 | jml | And I want to rename the database column from 'commercial' to 'suppress_subscription_notifications' so the DB matches the code |
| 14:05.31 | jml | But we don't have lockstep rollouts of code & data |
| 14:05.53 | jml | so I have to have a code base that works before and after the rename |
| 14:06.09 | jml | therve: if you can tell me how to do that without sqlobject, I can figure out the sqlobject bit |
| 14:06.18 | therve | probably not |
| 14:07.25 | therve | I guess you need a bootstrap phase in your app, looking at the db? |
| 14:10.43 | jml | therve: that's one approach |
| 14:11.17 | *** join/#storm stub (~stub@canonical/launchpad/stub) |
| 14:11.21 | jml | therve: another approach would be finding a chokepoint for when storm executes queries with this object, catch errors there and then update the declaration if there's a programming error |
| 14:11.48 | jml | therve: wgrant just pointed out another approach in #launchpad-dev, which is to not declare the thing using storm at all, but to provide the property manually with sql |
| 14:26.54 | *** join/#storm stub (~stub@canonical/launchpad/stub) |
| 15:02.45 | jkakar | jml: Hiya. :) |
| 15:03.03 | jml | jkakar: hello :) |
| 15:03.22 | jkakar | jml: It's not so easy because Storm compiles a ClassInfo whenever your object is first discovered (ie, when the module it's in is loaded, assuming you subclass the Storm metaclass, which I guess you do). |
| 15:03.34 | jml | jkakar: we do. |
| 15:04.27 | jkakar | jml: I wonder if something like this would work: class ObjectV1(Storm): ..., class ObjectV2(Storm): ..., Object = ObjectV1 if database_version is X else ObjectV2 |
| 15:04.52 | jml | jkakar: Hmm. |
| 15:05.05 | jml | jkakar: I'm not sure if there's a DB version available to us in code. |
| 15:05.15 | jml | jkakar: also, the thought of duplicating Archive scares me. |
| 15:06.53 | jkakar | jml: Yeah, I'm not sure that monkey patching will work either... I doubt the internal ClassInfo will work properly if you do that. |
| 15:07.03 | jml | jkakar: ah. |
| 15:17.31 | jml | hmm. |
| 15:17.52 | jml | so I'm trying wgrant's suggestion of having a manual property to do the select and update just for this column |
| 15:18.14 | jml | however, when I catch the ProgrammingError and try to run another query, I get "InternalError: current transaction is aborted, commands ignored until end of transaction block" |
| 15:19.09 | mgedmin | jml, you need to rollback the current transaction (and lose all previous changes), or switch to a look-before-you-leap idiom |
| 15:19.23 | jml | mgedmin: store.rollback() doesn't seem to have any effect. |
| 15:19.28 | jml | tries again |
| 15:19.44 | jml | oh, hmm, probably forgot to add the second rollback |
| 15:20.01 | mgedmin | in the try/except ProgrammingError blog it should work... |
| 15:20.46 | jml | yeah. I only had the rollback in the getter, which was the problem. |
| 15:20.52 | mgedmin | as for look-before-you-leap, you could look up column names by doing a SELECT attname FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid WHERE relname = 'yourtablename' AND attnum > 0 AND NOT attisdropped" |
| 15:21.14 | mgedmin | personally, doing random rollbacks in the middle of who-knows-what doesn't sound very safe |
| 15:22.00 | jml | mgedmin: yeah, that thought had occurred. |
| 15:22.27 | mgedmin | wonders if subtransactions would help here and realizes he doesn't know enough about subtransactions |
| 15:26.14 | jkakar | The thing about checkpointing is that Storm maintains an internal cache which it invalidates at transactions boundaries. It doesn't know about subtransactions, so it'd be some work to add the necessary tooling for them to be a properly supported feature. |
| 15:28.29 | jml | yeah, a rollback just messes things up |
| 15:33.59 | jml | I don't suppose storm has a db agnostic way of asking if a column exists? |
| 15:35.49 | *** join/#storm fcorrea (~fcorrea@177.120.59.89) |
| 15:36.42 | therve | jml, nope :/ |
| 15:42.01 | jml | :\ |
| 15:43.55 | *** join/#storm SuperShabam (~quassel@24-104-129.146.hfc.mediarain.com) |
| 16:04.52 | *** join/#storm fcorrea_ (~fcorrea@177.28.82.61) |
| 16:59.02 | *** join/#storm patricioe (~patricioe@108-68-106-29.lightspeed.sntcca.sbcglobal.net) |
| 17:31.23 | *** join/#storm awarner (~andrew@38.117.157.28) |
| 17:38.36 | *** join/#storm fcorrea (~fcorrea@187.3.14.135) |
| 17:58.52 | *** join/#storm fbru02 (~fbru02@vz144.citysearch.com) |
| 17:59.40 | *** join/#storm fcorrea (~fcorrea@187.3.134.37) |
| 18:04.55 | *** join/#storm fcorrea (~fcorrea@187.3.134.37) |
| 20:00.31 | *** join/#storm m4v (~znc@unaffiliated/m4v) |
| 21:02.43 | *** join/#storm fennokin (f@nat/theladders/x-agdzqjvxwydrcjrh) |
| 21:06.20 | *** part/#storm fennokin (f@nat/theladders/x-agdzqjvxwydrcjrh) |
| 21:35.17 | *** join/#storm timsmith (~timsmith@216.64.169.240) |
| 21:36.26 | *** join/#storm timsmith (~timsmith@216.64.169.240) |
| 22:30.45 | *** join/#storm patricioe (~patricioe@108-68-106-29.lightspeed.sntcca.sbcglobal.net) |
| 22:39.58 | *** join/#storm awarner (~andrew@38.117.157.28) |
| 23:06.30 | *** join/#storm bigjools (~quassel@60-241-216-21.static.tpgi.com.au) |
| 23:06.30 | *** join/#storm bigjools (~quassel@canonical/launchpad/bigjools) |
| 23:58.50 | *** join/#storm patricioe (~patricioe@108-68-106-29.lightspeed.sntcca.sbcglobal.net) |