|
From: José M. P. T. <mig...@gm...> - 2009-08-12 08:28:54
|
Hi all!
I'm quite new to sqlobject, so forgive me if I'm raising a issue that
has already been discussed (as excuse I present only a technical issue:
sourceforge is not cooperating when trying to search the list archives).
When trying to create a Many-to-Many relationship between two classes I
came across the following bug (I presume): when checking for the join
tables that need to be created the following lines are run:
sqlobject/main.py:
1469 if join.soClass.__name__ > join.otherClass.__name__:
1470 continue
1471 joins.append(join)
What this lines seam to do is stop any Many-to-Many relations where the
first class as a "lower" name than the second on.
Is this intended? If so why?
Example snipet:
##############################################################################
class Capability (sqlobject.SQLObject):
"""
A Hardware capability (or profile). Groups virtual machines.
"""
name = sqlobject.StringCol ()
description = sqlobject.StringCol ()
vm = sqlobject.RelatedJoin ('VirtualMachine')
targets = sqlobject.RelatedJoin ('Target')
class Target (sqlobject.SQLObject):
"""
A physical host, capable of running one or more of the defined
capabilities.
"""
name = sqlobject.StringCol ()
IP = sqlobject.StringCol ()
capabilities = sqlobject.RelatedJoin ('Capability')
##############################################################################
The table target_capability is not created. I believe it should be but
the check in main.py:1469 stop it from happening.
Thanks in advance for your time,
Jose Miguel Pereira Tavares
|
|
From: Oleg B. <ph...@ph...> - 2009-08-12 08:40:34
|
On Wed, Aug 12, 2009 at 09:28:33AM +0100, Jos?? Miguel Pereira Tavares wrote:
> sqlobject/main.py:
> 1469 if join.soClass.__name__ > join.otherClass.__name__:
> 1470 continue
> 1471 joins.append(join)
>
> What this lines seam to do is stop any Many-to-Many relations where the
> first class as a "lower" name than the second on.
>
> Is this intended? If so why?
To prevent the intermediate table to be created twice.
> Example snipet:
Works for me (without VirtualMachine class):
Capability.createTable()
Target.createTable()
prints
1/QueryR : CREATE TABLE capability (
id INTEGER PRIMARY KEY,
name TEXT,
description TEXT
)
2/Query : CREATE TABLE capability_target (
capability_id INT NOT NULL,
target_id INT NOT NULL
)
2/QueryR : CREATE TABLE capability_target (
capability_id INT NOT NULL,
target_id INT NOT NULL
)
3/Query : CREATE TABLE target (
id INTEGER PRIMARY KEY,
name TEXT,
i_p TEXT
)
Target.createTable()
Capability.createTable()
prints
1/QueryR : CREATE TABLE target (
id INTEGER PRIMARY KEY,
name TEXT,
i_p TEXT
)
2/QueryR : CREATE TABLE capability (
id INTEGER PRIMARY KEY,
name TEXT,
description TEXT
)
3/Query : CREATE TABLE capability_target (
capability_id INT NOT NULL,
target_id INT NOT NULL
)
Ok?
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Simon C. <hod...@gm...> - 2009-08-12 08:49:47
|
2009/8/12 José Miguel Pereira Tavares <mig...@gm...>: > When trying to create a Many-to-Many relationship between two classes I > came across the following bug (I presume): when checking for the join > tables that need to be created the following lines are run: > > sqlobject/main.py: > 1469 if join.soClass.__name__ > join.otherClass.__name__: > 1470 continue > 1471 joins.append(join) > > What this lines seam to do is stop any Many-to-Many relations where the > first class as a "lower" name than the second on. > > Is this intended? If so why? It is intended. It's there to stop SQLObject attempting to create the join table twice when the tables for the both the classes in the join are created. Without it, Capability.createTable() Target.createTable() would attempt to create the mapping table twice. Schiavo Simon |
|
From: Miguel T. <mig...@gm...> - 2009-08-12 09:14:53
|
Hi Simon, But creating the two table might be exactly was is intended, or it might make more sense to have class Z have a reference to A than the other way around. What do you think? Best regards, Miguel Tavares 2009/8/12 Simon Cross <hod...@gm...> > 2009/8/12 José Miguel Pereira Tavares <mig...@gm...>: > > When trying to create a Many-to-Many relationship between two classes I > > came across the following bug (I presume): when checking for the join > > tables that need to be created the following lines are run: > > > > sqlobject/main.py: > > 1469 if join.soClass.__name__ > join.otherClass.__name__: > > 1470 continue > > 1471 joins.append(join) > > > > What this lines seam to do is stop any Many-to-Many relations where the > > first class as a "lower" name than the second on. > > > > Is this intended? If so why? > > It is intended. It's there to stop SQLObject attempting to create the > join table twice when the tables for the both the classes in the join > are created. > > Without it, > > Capability.createTable() > Target.createTable() > > would attempt to create the mapping table twice. > > Schiavo > Simon > > |
|
From: Miguel T. <mig...@gm...> - 2009-08-12 09:20:54
|
Hi Oleg, Hum... shouldn't it create a target_capability too? Aren't there scenarios where the two relation might be independent? Like A having several B and B having several different A? Sorry for the missing VirtualMachine class. ############################################################################## class VirtualMachine (sqlobject.SQLObject): """ Virtual machine (with it's DB representation) """ name = sqlobject.StringCol (unique=True) script = sqlobject.StringCol () IP = sqlobject.StringCol () username = sqlobject.StringCol () rpms = sqlobject.IntCol () ############################################################################## Best regards, Miguel Tavares 2009/8/12 Oleg Broytmann <ph...@ph...> > > On Wed, Aug 12, 2009 at 09:28:33AM +0100, Jos?? Miguel Pereira Tavares wrote: > > sqlobject/main.py: > > 1469 if join.soClass.__name__ > join.otherClass.__name__: > > 1470 continue > > 1471 joins.append(join) > > > > What this lines seam to do is stop any Many-to-Many relations where the > > first class as a "lower" name than the second on. > > > > Is this intended? If so why? > > To prevent the intermediate table to be created twice. > > > Example snipet: > > Works for me (without VirtualMachine class): > > Capability.createTable() > Target.createTable() > > prints > > 1/QueryR : CREATE TABLE capability ( > id INTEGER PRIMARY KEY, > name TEXT, > description TEXT > ) > 2/Query : CREATE TABLE capability_target ( > capability_id INT NOT NULL, > target_id INT NOT NULL > ) > 2/QueryR : CREATE TABLE capability_target ( > capability_id INT NOT NULL, > target_id INT NOT NULL > ) > 3/Query : CREATE TABLE target ( > id INTEGER PRIMARY KEY, > name TEXT, > i_p TEXT > ) > > > Target.createTable() > Capability.createTable() > > prints > > 1/QueryR : CREATE TABLE target ( > id INTEGER PRIMARY KEY, > name TEXT, > i_p TEXT > ) > 2/QueryR : CREATE TABLE capability ( > id INTEGER PRIMARY KEY, > name TEXT, > description TEXT > ) > 3/Query : CREATE TABLE capability_target ( > capability_id INT NOT NULL, > target_id INT NOT NULL > ) > > Ok? > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > ------------------------------------------------------------------------------ > Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day > trial. Simplify your report design, integration and deployment - and focus on > what you do best, core application coding. Discover what's new with > Crystal Reports now. http://p.sf.net/sfu/bobj-july > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
|
From: Oleg B. <ph...@ph...> - 2009-08-12 09:34:12
|
On Wed, Aug 12, 2009 at 10:14:38AM +0100, Miguel Tavares wrote:
> But creating the two table might be exactly was is intended
> Hum... shouldn't it create a target_capability too?
No. SQLObject can use only one intermediate table. Wanna extend this?
Patches (with tests) should be put to the SF tracker.
But I wonder - what could be the use for a second intermediate table?
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Oleg B. <ph...@ph...> - 2009-08-12 09:35:34
|
On Wed, Aug 12, 2009 at 10:20:43AM +0100, Miguel Tavares wrote:
> Like A having several B and B having several different A?
RelatedJoins do exactly that. It is a many-to-many relation.
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Miguel T. <mig...@gm...> - 2009-08-12 10:26:14
|
Hi Oleg.
I think I didn't explain myself well. The example I provided has two
relationships. A->B, B->A, both different - so they can't (or
shouldn't) use the same table.
I guess with sqlobject it would something like
class A (SQLObject):
Bs = sqlobject.RelatedJoin ('B')
class B (SQLObject):
As = sqlobject.RelatedJoin ('A')
after the corresponding create tables was called then the tables "a",
"b", "a_b" and "b_a" should (I think) be created.
You stated in a previous email (one minute before this one) that
sqlobject doesn't support multiple intermediate tables. Maybe that's
what missing. I guess I'll can try to have a go at it, but my guess is
that I'll need some coaching (so I might come to annoy you all some
more :) ).
Best regards,
Miguel Tavares
2009/8/12 Oleg Broytmann <ph...@ph...>:
> On Wed, Aug 12, 2009 at 10:20:43AM +0100, Miguel Tavares wrote:
>> Like A having several B and B having several different A?
>
> RelatedJoins do exactly that. It is a many-to-many relation.
>
> Oleg.
> --
> Oleg Broytmann http://phd.pp.ru/ ph...@ph...
> Programmers don't die, they just GOSUB without RETURN.
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
|
|
From: Simon C. <hod...@gm...> - 2009-08-12 10:35:35
|
On Wed, Aug 12, 2009 at 12:25 PM, Miguel Tavares<mig...@gm...> wrote: > I think I didn't explain myself well. The example I provided has two > relationships. A->B, B->A, both different - so they can't (or > shouldn't) use the same table. You're looking for the intermediateTable option to RelatedJoin. See [1]. [1] http://www.sqlobject.org/SQLObject.html#relatedjoin-and-sqlrelatedjoin-many-to-many Schiavo Simon |
|
From: Miguel T. <mig...@gm...> - 2009-08-12 10:36:36
|
Hi Oleg!
A second intermediate table can be used to state a second many-to-many
relationship and it might make more sense to have it in reverse
order.
Imagine this scenario:
class Address(SQLObject):
Address = sqlobject.StringCol ()
People = sqlobject.RelatedJoin ('Person')
class Person(SQLObject):
Name = sqlobject.StringCol ()
Addresses = sqlobject.RelatedJoin ('Address')
So a Person can have several addresses (House, Work, Beach House, etc)
and each Address can be for several Persons (all the member of the
family for example).
Does this makes any sense?
Now, if I wanted to implement the support for several intermediate
tables where should I begin? Any ideas?
Best regards,
Miguel Tavares
2009/8/12 Oleg Broytmann <ph...@ph...>:
> On Wed, Aug 12, 2009 at 10:14:38AM +0100, Miguel Tavares wrote:
>> But creating the two table might be exactly was is intended
>> Hum... shouldn't it create a target_capability too?
>
> No. SQLObject can use only one intermediate table. Wanna extend this?
> Patches (with tests) should be put to the SF tracker.
>
> But I wonder - what could be the use for a second intermediate table?
>
> Oleg.
> --
> Oleg Broytmann http://phd.pp.ru/ ph...@ph...
> Programmers don't die, they just GOSUB without RETURN.
>
|
|
From: Oleg B. <ph...@ph...> - 2009-08-12 10:38:14
|
On Wed, Aug 12, 2009 at 11:25:58AM +0100, Miguel Tavares wrote:
> You stated in a previous email (one minute before this one) that
> sqlobject doesn't support multiple intermediate tables.
I meant - for a single RelatedJoin. But you can crate as many different
joins - just name the table explicitly:
class A (SQLObject):
B1 = sqlobject.RelatedJoin ('B', intermediateTable='a1_b1')
B2 = sqlobject.RelatedJoin ('B', intermediateTable='a2_b2')
class B (SQLObject):
A1 = sqlobject.RelatedJoin ('A', intermediateTable='a1_b1')
A2 = sqlobject.RelatedJoin ('A', intermediateTable='a2_b2')
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Miguel T. <mig...@gm...> - 2009-08-12 10:55:42
|
Hi Simon!
I think it doesn't work. But I might be using a old version of the
sqlobjects (the one provided by ubuntu 9.04).
Example:
######################################################
import sqlobject
class A (sqlobject.SQLObject):
name = sqlobject.StringCol (unique=True)
class B (sqlobject.SQLObject):
name = sqlobject.StringCol (unique=True)
a = sqlobject.RelatedJoin ('A')
if __name__ == "__main__":
#from optparse import OptionParser, OptionGroup
# Use the same DB connection for all the thread.
sqlobject.sqlhub.threadConnection = sqlobject.connectionForURI
('sqlite:///tmp/test.db')
A.createTable()
B.createTable ()
######################################################
after running the example this is the schema on the database:
CREATE TABLE a (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE b (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
Not quite what I would expect (I'm missing the table "b_a").
Is it now clear?
I'm going to try with a newer version. This tests were run with version 0.10.2.
Best regards,
Miguel Tavares
2009/8/12 Simon Cross <hod...@gm...>:
> On Wed, Aug 12, 2009 at 12:25 PM, Miguel Tavares<mig...@gm...> wrote:
>> I think I didn't explain myself well. The example I provided has two
>> relationships. A->B, B->A, both different - so they can't (or
>> shouldn't) use the same table.
>
> You're looking for the intermediateTable option to RelatedJoin. See [1].
>
> [1] http://www.sqlobject.org/SQLObject.html#relatedjoin-and-sqlrelatedjoin-many-to-many
>
> Schiavo
> Simon
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
|
|
From: Neil M. <drn...@gm...> - 2009-08-12 11:27:33
|
On Wed, Aug 12, 2009 at 12:55 PM, Miguel Tavares<mig...@gm...> wrote:
> Example:
>
> ######################################################
> import sqlobject
>
> class A (sqlobject.SQLObject):
> name = sqlobject.StringCol (unique=True)
>
> class B (sqlobject.SQLObject):
> name = sqlobject.StringCol (unique=True)
> a = sqlobject.RelatedJoin ('A')
>
There isn't a corresponding RelatedJoin definition in class A - since
RelatedJoin is intended to express a Many-to-Many relationship, and
this class structure is one-to-many (B can point to many A's, but A is
unable to point to any B's), that sqlobject doesn't figure out what
you expect it to do here is not that surprising.
It may be worthwhile trying to improve the checks on RelatedJoin to
warn about this case.
--
Neil Muller
drn...@gm...
I've got a gmail account. Why haven't I become cool?
|
|
From: Miguel T. <mig...@gm...> - 2009-08-12 10:58:50
|
Hi Oleg,
Running your example this is the schema on the database:
CREATE TABLE a (
id INTEGER PRIMARY KEY
);
CREATE TABLE a1_b1 (
a_id INT NOT NULL,
b_id INT NOT NULL
);
CREATE TABLE a2_b2 (
a_id INT NOT NULL,
b_id INT NOT NULL
);
CREATE TABLE b (
id INTEGER PRIMARY KEY
);
I'll try with a newer version of sqlobject and then I'll come back
with the status.
Best regards,
Miguel Tavares
2009/8/12 Oleg Broytmann <ph...@ph...>:
> On Wed, Aug 12, 2009 at 11:25:58AM +0100, Miguel Tavares wrote:
>> You stated in a previous email (one minute before this one) that
>> sqlobject doesn't support multiple intermediate tables.
>
> I meant - for a single RelatedJoin. But you can crate as many different
> joins - just name the table explicitly:
>
> class A (SQLObject):
> B1 = sqlobject.RelatedJoin ('B', intermediateTable='a1_b1')
> B2 = sqlobject.RelatedJoin ('B', intermediateTable='a2_b2')
>
> class B (SQLObject):
> A1 = sqlobject.RelatedJoin ('A', intermediateTable='a1_b1')
> A2 = sqlobject.RelatedJoin ('A', intermediateTable='a2_b2')
>
> Oleg.
> --
> Oleg Broytmann http://phd.pp.ru/ ph...@ph...
> Programmers don't die, they just GOSUB without RETURN.
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
|
|
From: Neil M. <drn...@gm...> - 2009-08-12 11:18:39
|
On Wed, Aug 12, 2009 at 12:36 PM, Miguel Tavares<mig...@gm...> wrote:
> Hi Oleg!
>
> A second intermediate table can be used to state a second many-to-many
> relationship and it might make more sense to have it in reverse
> order.
>
> Imagine this scenario:
>
> class Address(SQLObject):
> Address = sqlobject.StringCol ()
> People = sqlobject.RelatedJoin ('Person')
>
> class Person(SQLObject):
> Name = sqlobject.StringCol ()
> Addresses = sqlobject.RelatedJoin ('Address')
>
> So a Person can have several addresses (House, Work, Beach House, etc)
> and each Address can be for several Persons (all the member of the
> family for example).
>
> Does this makes any sense?
With this example, you'll create an intermediate table which looks
something like:
id1 : idPerson1 : idAddress1
id2 : idPerson1 : idAddress2
id3 : idPerson2 : idAddress1
id4 : idPerson2 : idAddress3
etc.
where idPerson & idAddress are the identifiers for the respective
entries in the Person and Address tables [1].
This captures the entire relationship - selecting entries based on the
address will give you all the Person records, and selecting on the
Person will give you all the address records.
I'm not seeing why you think you need multiple intermediate tables?
[1] I've swapped the order of the two columns from how sqlobject will
actually create the table, but that doesn't really matter.
--
Neil Muller
drn...@gm...
I've got a gmail account. Why haven't I become cool?
|
|
From: Miguel T. <mig...@gm...> - 2009-08-12 12:16:46
|
Hi Neil!
I'm also not sure if what I think to be correct is right. In the
example I provided you are right but take notice that it's a
bidirectional relationship. What if it was two unidirectional
relationships?
A->B
B->A
A1 -> {B1, B2, B3}
B1 -> {A2, A3}
I think this would require two intermediate tables (or only one table
with a "direction" column).
Is my reasoning correct?
Thanks,
Miguel Tavares
2009/8/12 Neil Muller <drn...@gm...>:
> On Wed, Aug 12, 2009 at 12:36 PM, Miguel Tavares<mig...@gm...> wrote:
>> Hi Oleg!
>>
>> A second intermediate table can be used to state a second many-to-many
>> relationship and it might make more sense to have it in reverse
>> order.
>>
>> Imagine this scenario:
>>
>> class Address(SQLObject):
>> Address = sqlobject.StringCol ()
>> People = sqlobject.RelatedJoin ('Person')
>>
>> class Person(SQLObject):
>> Name = sqlobject.StringCol ()
>> Addresses = sqlobject.RelatedJoin ('Address')
>>
>> So a Person can have several addresses (House, Work, Beach House, etc)
>> and each Address can be for several Persons (all the member of the
>> family for example).
>>
>> Does this makes any sense?
>
> With this example, you'll create an intermediate table which looks
> something like:
>
> id1 : idPerson1 : idAddress1
> id2 : idPerson1 : idAddress2
> id3 : idPerson2 : idAddress1
> id4 : idPerson2 : idAddress3
> etc.
>
> where idPerson & idAddress are the identifiers for the respective
> entries in the Person and Address tables [1].
>
> This captures the entire relationship - selecting entries based on the
> address will give you all the Person records, and selecting on the
> Person will give you all the address records.
>
> I'm not seeing why you think you need multiple intermediate tables?
>
> [1] I've swapped the order of the two columns from how sqlobject will
> actually create the table, but that doesn't really matter.
>
> --
> Neil Muller
> drn...@gm...
>
> I've got a gmail account. Why haven't I become cool?
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
|
|
From: Oleg B. <ph...@ph...> - 2009-08-12 12:58:23
|
On Wed, Aug 12, 2009 at 11:58:38AM +0100, Miguel Tavares wrote:
> Running your example this is the schema on the database:
> CREATE TABLE a (
> id INTEGER PRIMARY KEY
> );
> CREATE TABLE a1_b1 (
> a_id INT NOT NULL,
> b_id INT NOT NULL
> );
> CREATE TABLE a2_b2 (
> a_id INT NOT NULL,
> b_id INT NOT NULL
> );
> CREATE TABLE b (
> id INTEGER PRIMARY KEY
> );
I don't see any problem with the schema.
class A (SQLObject):
B1 = RelatedJoin ('B', addRemoveName='B1', intermediateTable='a1_b1')
B2 = RelatedJoin ('B', addRemoveName='B2', intermediateTable='a2_b2')
class B (SQLObject):
A1 = RelatedJoin ('A', addRemoveName='A1', intermediateTable='a1_b1')
A2 = RelatedJoin ('A', addRemoveName='A2', intermediateTable='a2_b1')
A.createTable()
B.createTable()
a = A()
b1 = B()
b2 = B()
a.addB1(b1)
a.addB2(b2)
print a.B1
print a.B2
1/QueryR : CREATE TABLE a (
id INTEGER PRIMARY KEY
)
2/QueryR : CREATE TABLE a1_b1 (
a_id INT NOT NULL,
b_id INT NOT NULL
)
3/QueryR : CREATE TABLE a2_b2 (
a_id INT NOT NULL,
b_id INT NOT NULL
)
4/QueryR : CREATE TABLE b (
id INTEGER PRIMARY KEY
)
5/QueryR : INSERT INTO a VALUES (NULL)
6/QueryR : SELECT NULL FROM a WHERE ((a.id) = (1))
7/QueryR : INSERT INTO b VALUES (NULL)
8/QueryR : SELECT NULL FROM b WHERE ((b.id) = (1))
9/QueryR : INSERT INTO b VALUES (NULL)
10/QueryR : SELECT NULL FROM b WHERE ((b.id) = (2))
11/QueryR : INSERT INTO a1_b1 (a_id, b_id) VALUES (1, 1)
12/QueryR : INSERT INTO a2_b2 (a_id, b_id) VALUES (1, 2)
13/QueryR : SELECT b_id FROM a1_b1 WHERE a_id = (1)
[<B 1 >]
14/QueryR : SELECT b_id FROM a2_b2 WHERE a_id = (1)
[<B 2 >]
Ok?
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Miguel T. <mig...@gm...> - 2009-08-12 14:09:01
|
Hi Oleg!
You are right, there is no problem because you defined the intermediate tables.
The issue comes from this:
> a.addB1(b1)
> a.addB2(b2)
what if it makes more sense to have
a1 = A ()
a2 = A ()
a3 = A ()
b1 = B ()
a1.add (b1)
b1.add (a2)
b1.add (a3)
Notice that the relationship is not symmetric.
Am I making any sense? Or should I try to get some sleep? :)
Regards,
Miguel Tavares
2009/8/12 Oleg Broytmann <ph...@ph...>:
> On Wed, Aug 12, 2009 at 11:58:38AM +0100, Miguel Tavares wrote:
>> Running your example this is the schema on the database:
>> CREATE TABLE a (
>> id INTEGER PRIMARY KEY
>> );
>> CREATE TABLE a1_b1 (
>> a_id INT NOT NULL,
>> b_id INT NOT NULL
>> );
>> CREATE TABLE a2_b2 (
>> a_id INT NOT NULL,
>> b_id INT NOT NULL
>> );
>> CREATE TABLE b (
>> id INTEGER PRIMARY KEY
>> );
>
> I don't see any problem with the schema.
>
> class A (SQLObject):
> B1 = RelatedJoin ('B', addRemoveName='B1', intermediateTable='a1_b1')
> B2 = RelatedJoin ('B', addRemoveName='B2', intermediateTable='a2_b2')
>
> class B (SQLObject):
> A1 = RelatedJoin ('A', addRemoveName='A1', intermediateTable='a1_b1')
> A2 = RelatedJoin ('A', addRemoveName='A2', intermediateTable='a2_b1')
>
> A.createTable()
> B.createTable()
>
> a = A()
> b1 = B()
> b2 = B()
> a.addB1(b1)
> a.addB2(b2)
> print a.B1
> print a.B2
>
> 1/QueryR : CREATE TABLE a (
> id INTEGER PRIMARY KEY
> )
> 2/QueryR : CREATE TABLE a1_b1 (
> a_id INT NOT NULL,
> b_id INT NOT NULL
> )
> 3/QueryR : CREATE TABLE a2_b2 (
> a_id INT NOT NULL,
> b_id INT NOT NULL
> )
> 4/QueryR : CREATE TABLE b (
> id INTEGER PRIMARY KEY
> )
> 5/QueryR : INSERT INTO a VALUES (NULL)
> 6/QueryR : SELECT NULL FROM a WHERE ((a.id) = (1))
> 7/QueryR : INSERT INTO b VALUES (NULL)
> 8/QueryR : SELECT NULL FROM b WHERE ((b.id) = (1))
> 9/QueryR : INSERT INTO b VALUES (NULL)
> 10/QueryR : SELECT NULL FROM b WHERE ((b.id) = (2))
> 11/QueryR : INSERT INTO a1_b1 (a_id, b_id) VALUES (1, 1)
> 12/QueryR : INSERT INTO a2_b2 (a_id, b_id) VALUES (1, 2)
> 13/QueryR : SELECT b_id FROM a1_b1 WHERE a_id = (1)
> [<B 1 >]
> 14/QueryR : SELECT b_id FROM a2_b2 WHERE a_id = (1)
> [<B 2 >]
>
> Ok?
>
> Oleg.
> --
> Oleg Broytmann http://phd.pp.ru/ ph...@ph...
> Programmers don't die, they just GOSUB without RETURN.
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
|
|
From: Oleg B. <ph...@ph...> - 2009-08-12 14:02:45
|
On Wed, Aug 12, 2009 at 02:43:50PM +0100, Miguel Tavares wrote:
> a1 = A ()
> a2 = A ()
> a3 = A ()
> b1 = B ()
>
> a1.add (b1)
> b1.add (a2)
> b1.add (a3)
a1 = A()
a2 = A()
a3 = A()
b1 = B()
a1.addB1(b1)
b1.addA1(a1)
b1.addA2(a2)
5/QueryR : INSERT INTO a VALUES (NULL)
6/QueryR : SELECT NULL FROM a WHERE ((a.id) = (1))
7/QueryR : INSERT INTO a VALUES (NULL)
8/QueryR : SELECT NULL FROM a WHERE ((a.id) = (2))
9/QueryR : INSERT INTO a VALUES (NULL)
10/QueryR : SELECT NULL FROM a WHERE ((a.id) = (3))
11/QueryR : INSERT INTO b VALUES (NULL)
12/QueryR : SELECT NULL FROM b WHERE ((b.id) = (1))
13/QueryR : INSERT INTO a1_b1 (a_id, b_id) VALUES (1, 1)
14/QueryR : INSERT INTO a1_b1 (b_id, a_id) VALUES (1, 1)
15/QueryR : INSERT INTO a2_b2 (b_id, a_id) VALUES (1, 2)
M?
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Miguel T. <mig...@gm...> - 2009-08-12 15:26:30
|
Hi again Oleg.
This must be a problem between my chair and my keyboard. :/
Anyway, I managed to have the results I wanted with the following code:
#############################
# -*- coding: utf-8 -*-
import sqlobject
class A (sqlobject.SQLObject):
name = sqlobject.StringCol ()
b = sqlobject.RelatedJoin ('B')
class B (sqlobject.SQLObject):
name = sqlobject.StringCol ()
a = sqlobject.RelatedJoin ('A', intermediateTable='b_a')
aa = sqlobject.ManyToMany ('A', intermediateTable='b_a')
if __name__ == "__main__":
# Use the same DB connection for all the thread.
sqlobject.sqlhub.threadConnection = sqlobject.connectionForURI
('sqlite:///tmp/testa.db')
A.createTable ()
B.createTable ()
a1 = A (name='a1')
a2 = A (name='a2')
a3 = A (name='a2')
b1 = B (name='b1')
a1.addB (b1)
b1.addA (a2)
b1.addA (a3)
b = B.selectBy (name='b1').getOne ()
print ("a1 = " + str (a1))
print ("b = " + str (b))
print ("b.a = " + str (b.a))
print ("b.aa = " + str (b.aa))
#############################
Running it results in the following output:
a1 = <A 1 name='a1'>
b = <B 1 name='b1'>
b.a = [<A 2 name='a2'>, <A 3 name='a2'>]
b.aa = SELECT a.id, a.name FROM a, b_a WHERE (((a.id) = (b_a.a_id))
AND ((b_a.b_id) = (1)))
The scheme in the database is:
CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE a_b (a_id INT NOT NULL, b_id INT NOT NULL);
CREATE TABLE b (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b_a (b_id INT NOT NULL, a_id INT NOT NULL);
Which is what I expected it to be.
The trick I'm using is defining ManyToMany and RelatedJoin to use the
same intermediate table. It's not pretty, but it works.
Do I really have to use such trick? Or am I miss reading the documentation?
Thanks in advance,
Miguel Tavares
2009/8/12 Oleg Broytmann <ph...@ph...>:
> On Wed, Aug 12, 2009 at 02:43:50PM +0100, Miguel Tavares wrote:
>> a1 = A ()
>> a2 = A ()
>> a3 = A ()
>> b1 = B ()
>>
>> a1.add (b1)
>> b1.add (a2)
>> b1.add (a3)
>
> a1 = A()
> a2 = A()
> a3 = A()
> b1 = B()
>
> a1.addB1(b1)
> b1.addA1(a1)
> b1.addA2(a2)
>
> 5/QueryR : INSERT INTO a VALUES (NULL)
> 6/QueryR : SELECT NULL FROM a WHERE ((a.id) = (1))
> 7/QueryR : INSERT INTO a VALUES (NULL)
> 8/QueryR : SELECT NULL FROM a WHERE ((a.id) = (2))
> 9/QueryR : INSERT INTO a VALUES (NULL)
> 10/QueryR : SELECT NULL FROM a WHERE ((a.id) = (3))
> 11/QueryR : INSERT INTO b VALUES (NULL)
> 12/QueryR : SELECT NULL FROM b WHERE ((b.id) = (1))
> 13/QueryR : INSERT INTO a1_b1 (a_id, b_id) VALUES (1, 1)
> 14/QueryR : INSERT INTO a1_b1 (b_id, a_id) VALUES (1, 1)
> 15/QueryR : INSERT INTO a2_b2 (b_id, a_id) VALUES (1, 2)
>
> M?
>
> Oleg.
> --
> Oleg Broytmann http://phd.pp.ru/ ph...@ph...
> Programmers don't die, they just GOSUB without RETURN.
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
|
