Thread: [SQLObject] cross database (and so cross platform) joins with 1-instruction refactoring ?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: gian p. c. <gp....@su...> - 2005-02-06 11:19:24
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi all, suppose I've the following setup: two joined classes but whose underlying tables are in two different DBs. - ---- conn = 'mysql://test@localhost/testdb?debug=1' conn2 = 'mysql://test@localhost/test?debug=1' class Person(SQLObject): ~ _connection = conn ~ firstName = StringCol() ~ middleInitial = StringCol(length=1, default=None) ~ lastName = StringCol() ~ addresses = MultipleJoin('Address') class Address(SQLObject): ~ _connection = conn2 ~ street = StringCol() ~ city = StringCol() ~ state = StringCol(length=2) ~ zip = StringCol(length=9) ~ person = ForeignKey('Person') print Person._connection.db print Address._connection.db #p = Person(firstName='John', lastName='Doe') p = Person.get(1) a1 = Address(street='123', city='Smallsville', ~ state='IL', zip='50484', person=p) print p.addresses - ---- This program actually fails because "address" table is on test db but it is searched on testdb. But if you change this assignment in joins.py ~ def performJoin(self, inst): ~ ids = self.otherClass._connection._SO_selectJoin( ~ self.otherClass, ~ self.joinColumn, ~ inst.id) That is, you point to the connection of the referenced class (instead if the referencing one) it works (and the regression tests seem still to work). I don't think this is all what we need to say that SQLObjects does support "cross-platform" joins, but perhaps this could be the beginning of a discussion. Cheers /gp p.s.: of course is someone cares about this kind of functionalities. - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCBf0r5CXmEF0gaHgRAj6PAJ9hklKPNCtsklUOrBENb6C6bDA80gCgv1Ld 0apaTbJBm4Jg1CdRsZ8z4q4= =opCq -----END PGP SIGNATURE----- |
From: Oleg B. <ph...@ma...> - 2005-02-06 12:00:29
|
On Sun, Feb 06, 2005 at 12:19:07PM +0100, gian paolo ciceri wrote: > I don't think this is all what we need to say > that SQLObjects does support "cross-platform" joins, > but perhaps this could be the beginning of a discussion. There is no magic in SQLObject. SQLObject maps relational algebra to Python operations. How do you suppose to join two tables from two different connections in SQL? SELECT *WHAT* FROM *WHERE* ??? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: gian p. c. <gp....@su...> - 2005-02-06 16:06:53
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Sun, Feb 06, 2005 at 12:19:07PM +0100, gian paolo ciceri wrote: | |>I don't think this is all what we need to say |>that SQLObjects does support "cross-platform" joins, |>but perhaps this could be the beginning of a discussion. | | | There is no magic in SQLObject. SQLObject maps relational algebra to | Python operations. How do you suppose to join two tables from two | different connections in SQL? | SELECT *WHAT* FROM *WHERE* ??? | | Oleg. Thanks Oleg for the quick reply. As you certainly know, if you look at the raw sql that SQLObjects generates in the example I've used you can see that it is rather compatible with a situation where the tables that holds classes' data are on two different databases (of course sqlobjects is not (yet ?) able to resolve an sql-select with joined tables belonging to different databases intelligently splitting the query and re-assembling the different answer sets afterwards). My point is only to explore what is (easily) achiveable with simple modifications to sqlobjects in term to map different dbms' tables to python objects - since in the example SQLObjects resolves the address lookup with a query to the joined table it seems to me quite feasible. IMHO it's not unlogical that in a simple, two table lookup relation (that is an inner join with a master/slave model) you should use the connection to the looked table (the slave) to fetch its data instead of the master connection one. Best regards, have a nice day. /gp - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCBkCU5CXmEF0gaHgRAmnnAKDQ61wKNzQXe4ew2zMi7F2QIbfvfQCcCE7i +eRiZL/dZJnV2sd6jz3kzZw= =V1J8 -----END PGP SIGNATURE----- |
From: Oleg B. <ph...@ph...> - 2005-02-06 16:24:43
|
On Sun, Feb 06, 2005 at 05:06:44PM +0100, gian paolo ciceri wrote: > As you certainly know, if you look at the raw sql that SQLObjects > generates in the example I've used you can see that it is rather > compatible with a situation where the tables that holds classes' data > are on two different databases In the same connection. > (of course sqlobjects is not (yet ?) > able to resolve an sql-select with joined tables belonging to different > databases intelligently splitting the query and re-assembling the > different answer sets afterwards). It, probably, wouldn't. Ian don't want to develop SQLObject in directions that are so far from real SQL. AFAIU SQLObject should be only a pythonic wrapper on top of a database driver. > My point is only to explore what is (easily) achiveable > with simple modifications to sqlobjects in term to map different > dbms' tables to python objects Try to modify _tableName attribute - add the name of the database. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: gian p. c. <gp....@su...> - 2005-02-06 17:26:59
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Sun, Feb 06, 2005 at 05:06:44PM +0100, gian paolo ciceri wrote: | |>As you certainly know, if you look at the raw sql that SQLObjects |>generates in the example I've used you can see that it is rather |>compatible with a situation where the tables that holds classes' data |>are on two different databases | | | In the same connection. | Mmmh. No I don't understand. If I change the _connection attribute in each class definition I'm quite free to let different objects point to different dbms. I've just re-tested putting persons table on mysql and addresses table on sqlite and it seems to work. Of course I can see problems for transactions, but not for simple attribute lookup like "find all addresses for a person". Perhaps somewhere in the documentation it's stated that all objects that are somewhat joined must belong to the same dbms, but I'm not able to see where this is said. | |>(of course sqlobjects is not (yet ?) |>able to resolve an sql-select with joined tables belonging to different |>databases intelligently splitting the query and re-assembling the |>different answer sets afterwards). | | | It, probably, wouldn't. Ian don't want to develop SQLObject in | directions that are so far from real SQL. AFAIU SQLObject should be only | a pythonic wrapper on top of a database driver. | Well, of course 80% of the application uses only one dbms at a time so the need to see data belonging to more than one db is not so common, but since it seems so simple to cross-reference objects backed in different dbms with SQLObjects (it's a matter to have a common value in an integer column to solve the join) I think it's worth the effort to see where this tecnique can bring. In my experience it's sometimes needed to fetch data from two different databases at a time: of course I can write the queries by hand and play with the connections without any Orm at all, but I've found the SQLObject approach rather convenient and so I like to see if it can easily be extended to handle such a task. | |>My point is only to explore what is (easily) achiveable |>with simple modifications to sqlobjects in term to map different |>dbms' tables to python objects | | | Try to modify _tableName attribute - add the name of the database. | I'm sorry but I don't see the point to qualify the tablename with the database name when I can simply point the query to the right connection. | Oleg. Oleg, I thank you for your support and I apologize if I'm wasting your time - I don't want to bother people with problems that are interesting only to me. Regards /gp - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCBlNY5CXmEF0gaHgRAuW+AKCRVVD1u2M7lzy1qF7P5MxTcgvAFQCfS4Zm Q5vF90p7qvhh742/mIgHabA= =SGGt -----END PGP SIGNATURE----- |
From: Oleg B. <ph...@ma...> - 2005-02-06 18:13:04
|
On Sun, Feb 06, 2005 at 06:26:48PM +0100, gian paolo ciceri wrote: > |>As you certainly know, if you look at the raw sql that SQLObjects > |>generates in the example I've used you can see that it is rather > |>compatible with a situation where the tables that holds classes' data > |>are on two different databases > | > | In the same connection. > > Mmmh. No I don't understand. I talked about joins. In the "real" SQL you can join tables from two different databases on the same DB server, but only in one connection. > In my experience it's sometimes needed to fetch data from two different > databases at a time: of course I can write the queries by hand and play > with the connections without any Orm at all, but I've found the > SQLObject approach rather convenient and so I like to see if it can > easily be extended to handle such a task. You can do it even with SQLObject. But you cannot do joins in different connections. > |>My point is only to explore what is (easily) achiveable > |>with simple modifications to sqlobjects in term to map different > |>dbms' tables to python objects > | > | Try to modify _tableName attribute - add the name of the database. > > I'm sorry but I don't see the point to qualify the tablename > with the database name To join the tables (again, in one connection.) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: gian p. c. <gp....@su...> - 2005-02-06 19:30:26
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Sun, Feb 06, 2005 at 06:26:48PM +0100, gian paolo ciceri wrote: | |>|>As you certainly know, if you look at the raw sql that SQLObjects |>|>generates in the example I've used you can see that it is rather |>|>compatible with a situation where the tables that holds classes' data |>|>are on two different databases |>| |>| In the same connection. |> |>Mmmh. No I don't understand. | | | I talked about joins. In the "real" SQL you can join tables from two | different databases on the same DB server, but only in one connection. | | I suppose you refer to mysql and to sqlite (with the "attach" command): here you can prefix the table with the "dbname." and join tables (now I've finally understood your _tableName suggestion), but this is limited (as you correctly state) to db deployed on the same server. And this tecnique doesn't work at all for postgreSQL, AFAIK. I'm interested into fetching data from two different mysql db placed on two different physical boxes (or hybrid situations where you have some entities on mysql and others on sqlite), and choosing carefully the connection in SQLObjects I'm able to do this master/slave cross-platform inner join like in the person/address sample. I probably will not able to do cross transactions (since underlying dbms doesn't implement something like two-phase commit protocol and all I could have is a sort of "broadcast commit" where each engine do the commit by itself) and now I'm figuring how to do cross many-to-many relationship. Anyway, thank you for your support. I really appreciate it. Regards /gp - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCBnBI5CXmEF0gaHgRAvhYAJ92O7q9NaiiQyAGsrZXPMGsa+jOFgCggYHm 6MOUMiHq/Z/6SRFqIopoa7A= =jeyM -----END PGP SIGNATURE----- |
From: Oleg B. <ph...@ma...> - 2005-02-06 20:10:02
|
On Sun, Feb 06, 2005 at 08:30:16PM +0100, gian paolo ciceri wrote: > And this tecnique doesn't work at all for postgreSQL, AFAIK. It should work with namespaces. There was a discussion of them in the SQLObject mailing list not so long ago. > I'm interested into fetching data from two different mysql db placed > on two different physical boxes SQLObject currently can offer you only limited support in this area. You can define different tables with different connections, but you have to do all queries by yourself. Fetch data from one table, loop over it and fetch data from the other table... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: gian p. c. <gp....@su...> - 2005-02-06 21:13:52
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Sun, Feb 06, 2005 at 08:30:16PM +0100, gian paolo ciceri wrote: | |>And this tecnique doesn't work at all for postgreSQL, AFAIK. | | | It should work with namespaces. There was a discussion of them in the | SQLObject mailing list not so long ago. | But this will have the same limitation of the same physical box, of course. | |>I'm interested into fetching data from two different mysql db placed |>on two different physical boxes | | | SQLObject currently can offer you only limited support in this area. | You can define different tables with different connections, but you have | to do all queries by yourself. Fetch data from one table, loop over it | and fetch data from the other table... | | Oleg. Well, for the many-to-many relationships I've tweaked a little more joins.py and main.py to check if the db connection of the "slave" table is different from the "master" one and then I play with intermediate table (i.e. I create two copies of the intermediate table, one for each database) accordingly. Perhaps I could be less dumb, check for the first intermediate table available and simply use it, but it is a beginning: the regression test works (even if I've not written some specific test cases, the samples I'm using could be easily integrated). I'd like to contribute some patches, if there is a sandbox (and if someone cares). Cheers /gp - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCBoiH5CXmEF0gaHgRAjjHAJ9nYXGW+VN9xP/aptvSZLyGEwKHpwCgu4gm MQqDlnpR+V48Cnvtn65rAY0= =fJc9 -----END PGP SIGNATURE----- |
From: Oleg B. <ph...@ma...> - 2005-02-06 21:51:30
|
On Sun, Feb 06, 2005 at 10:13:43PM +0100, gian paolo ciceri wrote: > I'd like to contribute some patches, if there is a sandbox There is no, currently. You can play privately, and then submit patch(es) to the SourceForge tracker. Don't forget to include tests and documentation. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |