sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Which version am I using?

[Aaron R. <aar...@mo...>]

Thanks for the idea,.. sadly this version doesn't define __doc__ either :-(

On Fri, Aug 21, 2009 at 6:41 PM, Simon Cross <hod...@gm...> wrote:

> On Fri, Aug 21, 2009 at 1:18 AM, Aaron
> Robinson<aar...@mo...> wrote:
> > As SQLObject doesn't appear to define a
> > "__version__", how does one tell which version one is using??
>
> Maybe try looking at the built-in documentation? The docstring for the
> module contains the version number (at least in version 0.10.2).
>
> Something like:
>
>   pydoc sqlobject
>
> or
>
>   python -c "import sqlobject; print sqlobject.__doc__"
>
> should display it for you.
>
> Schiavo
> Simon
>

Re: [SQLObject] Which version am I using?

[Simon C. <hod...@gm...>]

On Fri, Aug 21, 2009 at 1:18 AM, Aaron
Robinson<aar...@mo...> wrote:
> As SQLObject doesn't appear to define a
> "__version__", how does one tell which version one is using??

Maybe try looking at the built-in documentation? The docstring for the
module contains the version number (at least in version 0.10.2).

Something like:

   pydoc sqlobject

or

   python -c "import sqlobject; print sqlobject.__doc__"

should display it for you.

Schiavo
Simon

[SQLObject] Which version am I using?

[Aaron R. <aar...@mo...>]

Hi All,

I'm working on a project which is using an older version of SQLObject with
some modifications. I'm wanting to update to the latest version, but I need
to identify what changes were made so I can check them and replicate in the
new version if needed. As SQLObject doesn't appear to define a
"__version__", how does one tell which version one is using??

Thanks,

Re: [SQLObject] from_python

[Oleg B. <ph...@ph...>]

On Wed, Aug 19, 2009 at 07:59:29PM +0400, Oleg Broytmann wrote:
> Now .selectBy(), .deleteBy() and .by*() methods pass
> all values through .from_python(), not only unicode.

   Now - that is, in SVN. Will be in 0.12.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] from_python

[Oleg B. <ph...@ph...>]

On Fri, Mar 23, 2007 at 02:27:18PM -0400, Mathias Stearn wrote:
> the following works for me. In main.py
> around line 1297 add this at the top of the function
> SQLObject._findAlternateID:
>
> from_python = getattr(cls, '_SO_from_python_' + name)
> if from_python:
>    value = from_python(value, cls)

   Shame on me, it took more than two years to resolve the issues. But
finally it is done! Now .selectBy(), .deleteBy() and .by*() methods pass
all values through .from_python(), not only unicode.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] 1)Parametrized queries. 2)Prepared statements. 3)??? 4)WORLD!

[Oleg B. <ph...@ph...>]

On Tue, Aug 18, 2009 at 01:17:22PM -0400, Andrew Peace wrote:
> I think prepare
> statements should be on the todo list. And next...the WORLD!

   Well, my TODO is rather big, and I work on it a bit too slow. So the
world must wait until we'll be ready to conquer it. ;)

> Keep rocking.

   Thank you!

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without return.

Re: [SQLObject] Paramaterized queries

[Andrew P. <ape...@gm...>]

Whoops! Missed the list on that one. Agreed, though. I think prepare
statements should be on the todo list. And next...the WORLD! Keep
rocking.

-Andrew Peace



On Tue, Aug 18, 2009 at 1:12 PM, Oleg Broytmann<ph...@ph...> wrote:
> On Tue, Aug 18, 2009 at 12:50:29PM -0400, Andrew Peace wrote:
>> I don't get in on the list much (though I read every word :)
>
>   So you don't want our conversation to be forwarded to the list?
>
>> but I
>> figured I'd put in my two cents. I see a great reason to include a
>> feature like this: prepared statements.
>> http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
>> http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html
>
>   This requires generating a special PREPARE statement that's hard to
> generate in SQLObject even with parametrized queries. More changes will be
> required. I will add prepared statements to my TODO but at the very end.
>
>> http://sqlite.org/c3ref/stmt.html
>
>   SQLObject uses PySQLite and PySQLite doesn't seem to export
> prepare-related API.
>
> Oleg.
> --
>     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>



>From before:

I don't get in on the list much (though I read every word :) but I
figured I'd put in my two cents. I see a great reason to include a
feature like this: prepared statements.

It doesn't *appear* that SQLObject/SQLBuilder supports prepared
statements, but they can be beneficial. I know in MySQL they help a
great deal with preventing SQL injections so the developer doesn't
have to worry about it (as much). Also, it allows the query to be
parsed only once if it is used repeatedly. This could give a
substantial performance boost for large data sets, especially with
inserts, updates, and the like. In any case, the ability to use
prepared statements in databases that support them would be nice :D

Some links:

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html
http://sqlite.org/c3ref/stmt.html

Re: [SQLObject] Paramaterized queries

[Oleg B. <ph...@ph...>]

On Tue, Aug 18, 2009 at 05:51:13PM +0200, Frank Wagner wrote:
> I'd love to see parameterized queries in SQLObject

   BTW, why? From the user point of view there would be no API change, so
what makes parameterized queries so attractive?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Paramaterized queryAll...

[Frank W. <fra...@no...>]

I absolutely second that!
I'd love to see parameterized queries in SQLObject, but am unfortunately not
a skilled enough programmer to do this kind of work ...

Frank




2009/8/16 Sam's Lists <sam...@gm...>

> Oleg....
>
> I'm not sure I'm the one to do the merge, you wrote the code, and you seem
> to know more about SQLObject than anyone else in the world, including I'd
> guess Ian at this point.
>
> But it does seem like a really cool, and important feature.  So if you do
> decide to merge it at some point, thank you!  I know I'll benefit from it
> and so will many other SQLObject users.
>
> Thanks
> Sam
>
>
> On Sat, Aug 15, 2009 at 7:48 AM, Oleg Broytmann <ph...@ph...> wrote:
>
>> On Fri, Aug 14, 2009 at 10:19:23PM -0700, Sam's Lists wrote:
>> > QueryAll and QueryOne work okay with strings.
>> >
>> > But I'd like to be able to pass parameterized queries to them.  Am I
>> correct
>> > that this is not supported?  What are my options?
>> >
>> > I.e. something like this:
>> >
>> > queryAll('select * from stocks where symbol=?', (symbol,))
>> >
>> > (My actual queries are much more complicated, of course).
>>
>>    The best thing would be to merge my work on the parameterized queries
>> at
>> http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/ . Look at
>> sqlobject/include/DBSingleStyle.py and sqlobject/dbconnection.py. I was
>> working on the branch and wanted to merge it into mainline, but at that
>> time Luke Opperman merged his work (all SELECT implementations moved to
>> SQLBuilder, and all implementations use unified sqlbuilder.Select) and the
>> paramstyles branch became too much incompatible with the mainline, so I
>> dropped it. Now it requires a lot of work to be merged; actually I think
>> most of the work needs to be redone almost from the beginning; the work
>> must change SQLBuilder instead of DBConnection.
>>
>>   The second option is to create query strings yourself using
>> SQLBuilder.Select or just string interpolation.
>>
>> 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
>>
>
>
>
> ------------------------------------------------------------------------------
> 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
>
>

Re: [SQLObject] Paramaterized queryAll...

[Sam's L. <sam...@gm...>]

Oleg....

I'm not sure I'm the one to do the merge, you wrote the code, and you seem
to know more about SQLObject than anyone else in the world, including I'd
guess Ian at this point.

But it does seem like a really cool, and important feature.  So if you do
decide to merge it at some point, thank you!  I know I'll benefit from it
and so will many other SQLObject users.

Thanks
Sam

On Sat, Aug 15, 2009 at 7:48 AM, Oleg Broytmann <ph...@ph...> wrote:

> On Fri, Aug 14, 2009 at 10:19:23PM -0700, Sam's Lists wrote:
> > QueryAll and QueryOne work okay with strings.
> >
> > But I'd like to be able to pass parameterized queries to them.  Am I
> correct
> > that this is not supported?  What are my options?
> >
> > I.e. something like this:
> >
> > queryAll('select * from stocks where symbol=?', (symbol,))
> >
> > (My actual queries are much more complicated, of course).
>
>    The best thing would be to merge my work on the parameterized queries at
> http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/ . Look at
> sqlobject/include/DBSingleStyle.py and sqlobject/dbconnection.py. I was
> working on the branch and wanted to merge it into mainline, but at that
> time Luke Opperman merged his work (all SELECT implementations moved to
> SQLBuilder, and all implementations use unified sqlbuilder.Select) and the
> paramstyles branch became too much incompatible with the mainline, so I
> dropped it. Now it requires a lot of work to be merged; actually I think
> most of the work needs to be redone almost from the beginning; the work
> must change SQLBuilder instead of DBConnection.
>
>   The second option is to create query strings yourself using
> SQLBuilder.Select or just string interpolation.
>
> 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
>

Re: [SQLObject] Paramaterized queryAll...

[Oleg B. <ph...@ph...>]

On Fri, Aug 14, 2009 at 10:19:23PM -0700, Sam's Lists wrote:
> QueryAll and QueryOne work okay with strings.
> 
> But I'd like to be able to pass parameterized queries to them.  Am I correct
> that this is not supported?  What are my options?
> 
> I.e. something like this:
> 
> queryAll('select * from stocks where symbol=?', (symbol,))
> 
> (My actual queries are much more complicated, of course).

   The best thing would be to merge my work on the parameterized queries at
http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/ . Look at
sqlobject/include/DBSingleStyle.py and sqlobject/dbconnection.py. I was
working on the branch and wanted to merge it into mainline, but at that
time Luke Opperman merged his work (all SELECT implementations moved to
SQLBuilder, and all implementations use unified sqlbuilder.Select) and the
paramstyles branch became too much incompatible with the mainline, so I
dropped it. Now it requires a lot of work to be merged; actually I think
most of the work needs to be redone almost from the beginning; the work
must change SQLBuilder instead of DBConnection.

   The second option is to create query strings yourself using
SQLBuilder.Select or just string interpolation.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Paramaterized queryAll...

[Sam's L. <sam...@gm...>]

QueryAll and QueryOne work okay with strings.

But I'd like to be able to pass parameterized queries to them.  Am I correct
that this is not supported?  What are my options?

I.e. something like this:

queryAll('select * from stocks where symbol=?', (symbol,))

(My actual queries are much more complicated, of course).

I am using PostgreSQL, if it matters.

Thanks
Sam

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

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
>

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

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
>

Re: [SQLObject] Intermediate table in RelatedJoins

[Oleg B. <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.

Re: [SQLObject] Intermediate table in RelatedJoins

[Oleg B. <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.

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

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
>

Re: [SQLObject] Intermediate table in RelatedJoins

[Neil M. <drn...@gm...>]

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?

Re: [SQLObject] Intermediate table in RelatedJoins

[Neil M. <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?

Re: [SQLObject] RealatedJoin

[Tom C. <su...@ic...>]

Hi Oleg,

On Wednesday 12 August 2009 10:06:30 Oleg Broytmann wrote:
> On Tue, Aug 11, 2009 at 07:40:12AM +0200, Tom Coetser wrote:
> > class Member(SQLObject):
> >     name = StringCol(alternateID=True)
> >     roles = SQLRelatedJoin('Role', intermediateTable='member_role',
> >                                createRelatedTable=False)
> >
> > class Role(SQLObject):
> >     name = StringCol(alternateID=True)
> >     members = SQLRelatedJoin('Member', intermediateTable='member_role',
> >                                createRelatedTable=False)
> >
> > class MemberRole(SQLObject):
> >     member = ForeignKey('Member', notNull=True, cascade=False)
> >     role = ForeignKey('Role', notNull=True, cascade=False)
> >
> > When I call destroySelf() on a member though, I would like to **not**
> > have that member deleted if it still has any roles assigned to it. I
> > tried setting the cascade=False option in the intermediate table, but
> > this does not help because it looks like the base destroySelf() method
> > automatically deletes any RelatedJoins.
>
>    First thing I can guess your are using a backend that doesn't support
> CASCADE (SQLite?) With SQLite, you can CREATE a TRIGGER to prevent deletion

No, I am using Postgres after burning my fingers during the testing stages 
with exactly this using SQLite.

> from the intermediate table, but you have to do it yourself.
>    .destroySelf() actually doesn't rely on the backend support for CASCADE;
> instead it uses it itself, but not on the RelatedJoin's intermediate table
> even if the table is declared explicitly. The simplest way I see is to
> override .destroySelf:
>
> class Member(SQLObject):
>     name = StringCol(alternateID=True)
>     roles = SQLRelatedJoin('Role', intermediateTable='member_role',
>                                createRelatedTable=False)
>
>     def destroySelf(self):
>         if list(self.roles): # or self.roles.count()
>             raise RuntimeError('Cannot delete a member (%s) that has roles'
> % self.id)
>         super(Member, self).destroySelf()
>
> class Role(SQLObject):
>     name = StringCol(alternateID=True)
>     members = SQLRelatedJoin('Member', intermediateTable='member_role',
>                                createRelatedTable=False)
>
>     def destroySelf(self):
>         try:
>             self.members[0]
>         except IndexError:
>             super(Role, self).destroySelf()
>         else:
>             raise RuntimeError('Cannot delete a role (%s) that has members'
> % self.id)
>
>    I showed three different ways to check for RelatedJoin. This of course
> only works with .destroySelf - other means will happily delete rows.

Thanks. Yes, I did end up overriding .destroySelf() and that seems to work 
fine. The other methods of deleting records are handled directly by the 
backend via the cascade=False on the ForeignKeys on the intermediate table.

The only awkward part is that exception handling is a bit weird due to 
different exceptions being raised for essentially the same reason but using 
different delete methods. But for now I can live with this :-)

Thanks for the help.


Cheers,
  Tom

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

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
>

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

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
>

Re: [SQLObject] Intermediate table in RelatedJoins

[Oleg B. <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.

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

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.
>

Re: [SQLObject] Intermediate table in RelatedJoins

[Simon C. <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