| 00:00.15 | *** join/#storm wallflower (n=wallflow@ip-205-246-113-216.pool.grokthis.net) |
| 00:02.29 | *** join/#storm sigtim (n=tim@ws253-244.sig.msstate.edu) |
| 00:03.08 | sigtim | hello. any idea if storm can handle arrays in PostgreSQL ? |
| 00:03.45 | radix | sigtim: It can |
| 00:04.13 | sigtim | let's say I have a column number integer[] |
| 00:04.27 | sigtim | how do I define it in storm class? |
| 00:06.25 | radix | sigtim: I think just as number = List() |
| 00:06.39 | radix | (where List comes from storm.properties) |
| 00:09.14 | sigtim | radix: blocks = store.find(Block, Block.counties_in_cd[3] == 1) |
| 00:09.14 | sigtim | TypeError: 'PropertyColumn' object is unindexable |
| 00:09.34 | radix | sigtim: counties_in_cd is a postgres array? |
| 00:09.43 | sigtim | yes. integer[] |
| 00:09.52 | radix | sigtim: I don't really understand what SQL you could possibly want to generate from that expression |
| 00:09.59 | sigtim | tried counties_in_cd = List() and List(Int()) - no luck. |
| 00:10.38 | sigtim | radix: each county can be in multiple congressional districts. |
| 00:10.54 | sigtim | in this case, there are only 4 cd's, so I could separate into variables cd1, cd2, etc. I suppose. |
| 00:11.00 | sigtim | but I have a case later where membership is in hundreds. |
| 00:11.19 | radix | sigtim: Can you give an example of the SQL you want to generate? |
| 00:11.20 | sigtim | also would rather not do a join, a few hundred k rows. |
| 00:11.56 | sigtim | sql: select name from blocks where counties_in_cd[3] = 1 |
| 00:13.16 | sigtim | the array tests for membership. I probably should consider a join, but I need to generate a lot of reports out of this later so would prefer to get all the data into a table. |
| 00:13.29 | radix | sigtim: Ok. I don't think Storm supports comparing individual elements of postgres arrays (I wasn't even previously aware that was possible) |
| 00:13.40 | radix | sigtim: indexes are your friend :) |
| 00:15.53 | sigtim | thanks - let me try that. I should stay away from such db specific features anyway, but couldn't resist. |
| 00:16.59 | radix | sigtim: wait, what does "counties_in_cd[3] = 1" do exactly? Does it check to see if the third element in the array is equal to 1? |
| 00:17.29 | radix | Are you sure that's possible? I don't see that kind of thing at http://www.postgresql.org/docs/8.2/static/functions-array.html |
| 00:18.42 | sigtim | see here.http://www.postgresql.org/docs/8.0/interactive/arrays.html |
| 00:18.55 | sigtim | but yeah, that's what it does. sql: select count(*) from sig_map where county_in_cd[3] = 1 |
| 00:18.57 | sigtim | result: 45808 |
| 00:19.20 | radix | ah, ok |
| 00:19.30 | radix | I guess then it would be good for storm to support that |
| 00:19.56 | sigtim | you might also want to look into inheritance as well. I try to stay away from it but one of the nicer things about postgresql. |
| 00:20.16 | radix | what do you mean? |
| 00:20.24 | radix | I don't think storm needs any special support for Postgres inheritance |
| 00:20.25 | sigtim | you can inhert from another table. |
| 00:20.51 | radix | I know about postgres inheritance |
| 00:21.01 | radix | (as an aside, I've found postgres inheritance to be mostly useless) |
| 00:22.25 | sigtim | I don't have an informed opinion on that but I will take your word for it. I am learning python & storm to get away from Perl/DBI. The main attraction is the clean syntax/design for me so a lot of this is new. I've never used inheritance in postgres though so just thought I point out. |
| 00:24.27 | radix | cool :) |
| 00:24.32 | radix | I only recently looked at the inheritance stuff, actually |
| 00:25.23 | radix | it seems that it gives you the ability to treat subclasses and superclasses as homogenous things in only uninteresting ways |
| 00:28.36 | *** join/#storm Key_Gena (i=lok@gateway/tor/x-9be7b17777765114) |
| 00:29.39 | sigtim | Radix: I think you'll lose me in a hurry - I am behind in OO concepts ("old" embedded systems programmer who's now getting into web applications). I did just tried a join and it worked beautifully. Cool - I find that the older I get the lazier I am and storm/python really map into the way I think a lot better. It's almost declarative and to me that's the beauty of it. |
| 00:30.14 | radix | hehe |
| 00:35.00 | sigtim | radix: thanks again. I know I am a newbie web framework wise but I've played with just about every ORM in every language and I think storm and django are by far the best ones. Most of the others I can't even get past the syntax (and that says a lot from a guy who writes a lot of Perl - heh). Can't wait to see storm evolve but I think I've got enough to finish these reports I need to do. Thanks! |
| 00:37.55 | radix | sigtim: sweet! good luck with your project. |
| 01:13.04 | radix | Anyone know of a decent way to represent (and query for) objects in symmetric relationships in SQL? |
| 02:14.43 | jkakar | radix: What do you mean by 'symmetric relationships'? |
| 02:17.32 | radix | http://en.wikipedia.org/wiki/Symmetric_relation :-) |
| 02:18.02 | radix | "# symmetric: for all x and y in X it holds that if xRy then yRx. "Is a blood relative of" is a symmetric relation, because x is a blood relative of y if and only if y is a blood relative of x." |
| 02:19.18 | radix | so if you have a blood_relation table with columns "person1" and "person2", and you want to query for all blood relations of a person, you have a couple options AFAICT |
| 02:20.24 | radix | you can either copy each row to its inverse, so that you can always query on the same column, |
| 02:21.08 | radix | or you can do a query like "SELECT * from blood_relation WHERE person1 = X OR person2 = X;", and then go through the result checking to see which side X came out on, and using the opposite side |
| 02:22.05 | radix | maybe there are more clever things |
| 02:22.53 | radix | hmm, I guess you could split it out further to a "blood_relation" table with columns relation_id, participant |
| 02:23.30 | radix | and do something like SELECT participant FROM blood_relation WHERE relation_id = (SELECT relation_id FROM blood_relation WHERE participant = X) |
| 02:24.45 | radix | (is there a better query than that, given that schema?) |
| 02:25.51 | radix | insertions into that schema could be a minor pain, since you can't just use a SEQUENCE column for relation_id, you have to insert with the same unique ID in pairs |
| 02:26.09 | radix | but UUID should be fine for that |
| 02:27.48 | radix | scratch that last line, brainfart |
| 02:32.02 | jkakar | Huh, neat. |
| 03:23.59 | radix | http://radix.twistedmatrix.com/2007/11/representing-symmetric-relations-in.html |
| 03:24.26 | radix | I wrote a blog post about this to hopefully get some feedback. |
| 03:33.26 | *** join/#storm Key_Gena (i=lok@gateway/tor/x-e7f72f1c2321a883) |
| 03:38.41 | sigtim | radix: this is a more "computer science" approach. http://www.dbmsmag.com/9704d06.html |
| 03:39.13 | sigtim | I am hardly a db person, but I'd try something like create table r (person1, person2, direction) |
| 03:39.32 | sigtim | direction can be one way or two way. |
| 03:40.09 | sigtim | you have to do more work with queries and insertions, but I think you can avoid any explicit transactions. |
| 03:43.53 | radix | yeah, I've read that page several times. It takes the approach of duplicating the data. |
| 03:44.06 | radix | I'm not sure what you mean by "explicit transactions" |
| 03:44.44 | radix | and given that I'm only talking about two-way (symmetric) relationships, I'm not sure how your schema is beneficial |
| 03:45.41 | sigtim | the dbms approach wraps two statements around a transaction. |
| 03:46.21 | sigtim | hmm, I am confused. if your database only contains two-way relationships, then by definition all rows have the symmetric relationship. |
| 03:47.32 | radix | Maybe I don't understand what you mean by "two-way relationship". I assumed it meant the same thing as "symmetric relationship". |
| 03:47.37 | sigtim | I am assuming that your rows contains one way relationships, and you want to find the symmetrical relations out of them. |
| 03:47.55 | sigtim | in your table design, (a,b) only implies a->b, but not b->a |
| 03:48.08 | sigtim | so you are trying to find all cases where a->b and also b->a, two rows. |
| 03:48.12 | sigtim | in first schema anyway. |
| 03:48.18 | radix | sigtim: In a table (a, b), it's possible to treat the relationship as symmetric, if you just write your code and queries in the right way |
| 03:48.22 | radix | sigtim: which I described in the blog post |
| 03:48.29 | radix | " |
| 03:48.29 | radix | "This is the most trivial and straightforward in-database representation of a symmetric relationship between two participants. Actually, this schema could represent an asymmetric relationship as well, but we'll write our queries and application code so that (participant1=X, participant2=Y) is treated the same as (participant1=Y, participant2=X). |
| 03:48.35 | radix | " |
| 03:49.09 | radix | The two options with schema 1 are to EITHER store symmetric relationships as two rows, OR perform a fancy query which allows you to only have one row. |
| 03:49.18 | sigtim | ah I gotcha. |
| 03:49.30 | radix | both of those options are pretty sucky |
| 03:49.45 | radix | the first for the fact that you have to maintain two rows explicitly, the second because the querying and processing is really awkward |
| 04:09.54 | sigtim | personally, I don't feel schema 1 is so bad either way, depends on what you are optimizing for. the only db I know that has problem with multi-column indexes is sqlite - can't imagine mysql, postgresql, having problems with that. but I'll have to defer to those far more knowledgeable on databases than I on this. interesting problem. gotta run - catch you later! |
| 04:10.12 | *** part/#storm sigtim (n=tim@ws253-244.sig.msstate.edu) |
| 05:10.59 | *** join/#storm sigtim (n=Tim@64.234.56.136) |
| 05:11.28 | sigtim | /users |
| 05:12.11 | *** part/#storm sigtim (n=Tim@64.234.56.136) |
| 05:12.52 | *** join/#storm sigtim (n=Tim@64.234.56.136) |
| 07:22.52 | *** join/#storm goschtl (n=goschtl@pD9E5DC95.dip.t-dialin.net) |
| 07:59.37 | *** join/#storm goschtl_ (n=goschtl@pD9E5DC95.dip.t-dialin.net) |
| 09:13.13 | *** join/#storm jukart (n=jukart@85-124-221-45.static.xdsl-line.inode.at) |
| 09:20.44 | *** join/#storm Key_Gena (i=lok@gateway/tor/x-54b85bc30ea5e8e8) |
| 10:45.28 | *** join/#storm goschtl (n=goschtl@pD9E5DC95.dip.t-dialin.net) |
| 12:10.20 | *** join/#storm jukart (n=jukart@85-124-221-45.static.xdsl-line.inode.at) |
| 14:35.10 | *** join/#storm Zenom (n=aj1973@unaffiliated/aj1973) |
| 15:32.43 | *** join/#storm jukart (n=jukart@85-124-221-45.static.xdsl-line.inode.at) |
| 17:15.29 | *** join/#storm niemeyer (n=niemeyer@201-10-91-193.ctame705.dsl.brasiltelecom.net.br) |
| 17:34.55 | *** join/#storm WebMaven_ (n=webmaven@ip72-193-218-163.lv.lv.cox.net) |
| 17:57.56 | *** join/#storm goschtl (n=goschtl@pD9E5EE2E.dip.t-dialin.net) |
| 18:15.10 | *** join/#storm goschtl_ (n=goschtl@pD9E5EE2E.dip.t-dialin.net) |
| 18:15.41 | *** join/#storm bigdog (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |
| 18:38.32 | *** join/#storm goschtl (n=goschtl@pD9E5EE2E.dip.t-dialin.net) |
| 18:51.48 | *** join/#storm goschtl (n=goschtl@pD9E5EE2E.dip.t-dialin.net) |
| 18:57.41 | *** join/#storm goschtl (n=goschtl@pD9E5EE2E.dip.t-dialin.net) |
| 19:04.49 | goschtl | niemeyer: i have created a new version for the storm zope container. I will post the changes to the maillinglist tomorrow. And i try to host the code on googlecode. But i think i need some help on two points. |
| 19:06.18 | niemeyer | goschtl: Hey |
| 19:06.22 | niemeyer | goschtl: Sure, what's up? |
| 19:07.07 | goschtl | Is it possible to get an kind of an *instance_key(obj)* for storm. |
| 19:07.24 | goschtl | in sqlalchemy there is a function like this |
| 19:08.03 | niemeyer | goschtl: What does it do? |
| 19:08.43 | goschtl | you can look on the instance functions here http://svn.sqlalchemy.org/sqlalchemy/trunk/lib/sqlalchemy/orm/mapper.py --> primary_key_from_instance ... |
| 19:11.44 | goschtl | i need this for a kind of traversal in zope. now i interate through the primary_vars in obj_info look here http://paste.plone.org/18125 |
| 19:12.16 | niemeyer | goschtl: This should do it: |
| 19:12.19 | niemeyer | def instance_key(obj): |
| 19:12.19 | niemeyer | <PROTECTED> |
| 19:12.19 | niemeyer | <PROTECTED> |
| 19:12.19 | niemeyer | <PROTECTED> |
| 19:13.08 | *** join/#storm bigdog (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |
| 19:13.13 | goschtl | niemeyer: thx i will try |
| 19:21.46 | *** join/#storm bigdo1 (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |
| 20:40.19 | *** join/#storm dobee (n=dobeee@81-223-53-162.dornbirn.xdsl-line.inode.at) |
| 20:42.35 | *** join/#storm dobee (n=dobeee@81-223-53-162.dornbirn.xdsl-line.inode.at) |
| 20:52.54 | *** part/#storm sigtim (n=Tim@64.234.56.136) |
| 21:13.27 | *** join/#storm bigdog (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |
| 21:14.53 | *** join/#storm bozzo (n=bozzo@BSN-77-50-2.dial-up.dsl.siol.net) |
| 22:42.29 | *** join/#storm jml (n=jml@li2-200.members.linode.com) |
| 22:55.22 | *** join/#storm dotz (n=dotz@chello089078004026.chello.pl) |
| 23:02.54 | *** join/#storm Zenom (n=Zenom@unaffiliated/aj1973) |
| 23:28.52 | *** join/#storm dotz (n=dotz@chello089078004026.chello.pl) |