Oleg Broytman | 5 May 21:08 2010
X-Face
Picon

SQLObject 0.11.6

Hello!

I'm pleased to announce version 0.11.6, a minor bugfix release of 0.11 branch
of SQLObject.

What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).

Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

(Continue reading)

Oleg Broytman | 5 May 21:10 2010
X-Face
Picon

SQLObject 0.12.4

Hello!

I'm pleased to announce version 0.12.4, a minor bugfix release of branch 0.12
of SQLObject.

What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).

Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

(Continue reading)

Imri Goldberg | 6 May 19:46 2010
Picon

An idea regarding deleteMany

Heya
In the past I've run into problems with cascade actions not happening in sqlite when using deleteMany. Since then, my code doesn't rely on cascade actions. (even when not using sqlite).
In the last few days I've started using helper "smart" deleteMany functions which given a query, call deleteMany on all affected classes.

So for example, if I have a many to many relationship between Foo and Bar, and the join-table is called FooBar, smart delete many will do something like:
Foo.deleteMany(foo_query)
FooBar.deleteMany((FooBar.q.foo == Foo.q.id) & foo_query)

I was thinking that this could be generalized to a generic smartDeleteMany function (or whatever name you choose), which automatically does all the cascade actions, without specifying it manualy for each class.

What do you think? Is this useful?

Cheers,
Imri

--
Imri Goldberg
--------------------------------------
http://plnnr.com/ - automatic trip planning
http://www.algorithm.co.il/blogs/
--------------------------------------
-- insert signature here ----
------------------------------------------------------------------------------
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Oleg Broytman | 6 May 20:24 2010
X-Face
Picon

Re: An idea regarding deleteMany

On Thu, May 06, 2010 at 08:46:55PM +0300, Imri Goldberg wrote:
> In the last few days I've started using helper "smart" deleteMany functions
> which given a query, call deleteMany on all affected classes.
> 
> So for example, if I have a many to many relationship between Foo and Bar,
> and the join-table is called FooBar, smart delete many will do something
> like:
> Foo.deleteMany(foo_query)
> FooBar.deleteMany((FooBar.q.foo == Foo.q.id) & foo_query)
> 
> I was thinking that this could be generalized to a generic smartDeleteMany
> function (or whatever name you choose), which automatically does all the
> cascade actions, without specifying it manualy for each class.
> 
> What do you think? Is this useful?

   Useful, certainly.

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------
Imri Goldberg | 6 May 20:36 2010
Picon

Re: An idea regarding deleteMany

Heya
It seems I was incorrect in my wording. In the last few days I've been considering using these functions, and have just now started implementing/using them.
Working on it now I'm running into some problems, I will write more once I have this worked out.

-Imri

On Thu, May 6, 2010 at 9:24 PM, Oleg Broytman <phd <at> phd.pp.ru> wrote:
On Thu, May 06, 2010 at 08:46:55PM +0300, Imri Goldberg wrote:
> In the last few days I've started using helper "smart" deleteMany functions
> which given a query, call deleteMany on all affected classes.
>
> So for example, if I have a many to many relationship between Foo and Bar,
> and the join-table is called FooBar, smart delete many will do something
> like:
> Foo.deleteMany(foo_query)
> FooBar.deleteMany((FooBar.q.foo == Foo.q.id) & foo_query)
>
> I was thinking that this could be generalized to a generic smartDeleteMany
> function (or whatever name you choose), which automatically does all the
> cascade actions, without specifying it manualy for each class.
>
> What do you think? Is this useful?

  Useful, certainly.

Oleg.
--
    Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
          Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss



--
Imri Goldberg
--------------------------------------
http://plnnr.com/ - automatic trip planning
http://www.algorithm.co.il/blogs/
--------------------------------------
-- insert signature here ----
------------------------------------------------------------------------------
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Oleg Broytman | 6 May 20:43 2010
X-Face
Picon

Re: An idea regarding deleteMany

On Thu, May 06, 2010 at 09:36:54PM +0300, Imri Goldberg wrote:
> Working on it now I'm running into some problems, I will write more once I
> have this worked out.

   Ok, we're waiting...

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------
Imre Horvath | 14 May 15:37 2010
Picon

left join with subquery and WHERE

Hi!

I wrote a small program to test this problem.
I have 3 tables:

CREATE TABLE pricegroups (id integer primary key);
CREATE TABLE products (id integer primary key, name text);
CREATE TABLE products_pricegroups (id integer primary key, product_id
integer, pricegroup_id integer, percent integer);

I would like to perform the following query:

select * from pricegroups 
left join (select * from products_pricegroups
where products_pricegroups.product_id=1) as a 
on (pricegroups.id=a.pricegroup_id)

How can I accomplish it in mysql?
This is where i am now:

#!/usr/bin/env python

import os
import sqlobject

class Product(sqlobject.SQLObject):

	class sqlmeta:
		table = 'products'

	name = sqlobject.StringCol()

class PriceGroup(sqlobject.SQLObject):

	class sqlmeta:
		table = 'pricegroups'

	percents = sqlobject.MultipleJoin('ProductPriceGroup', joinColumn =
'pricegroup_id')
	
class ProductPriceGroup(sqlobject.SQLObject):

	class sqlmeta:
		table = 'products_pricegroups'

	productId = sqlobject.ForeignKey('Product', dbName = 'product_id')
	pricegroupId = sqlobject.ForeignKey('PriceGroup', dbName =
'pricegroup_id')
	percent = sqlobject.IntCol()

if __name__ == '__main__':

	builder = sqlobject.sqlite.builder()
	connection = builder('join.db')
	connection.debug = 1
	sqlobject.sqlhub.processConnection = connection

	pgl = list(PriceGroup.select(
		join = sqlobject.sqlbuilder.LEFTJOINOn(
			PriceGroup, ProductPriceGroup,
			PriceGroup.q.id == ProductPriceGroup.q.pricegroupId,
			),
		))
	
	for pg in pgl:
		print pg

The next step is to apply the WHERE clause, but I have no idea how to do
it...

Thanks for any help:
Imre Horvath

------------------------------------------------------------------------------
Oleg Broytman | 14 May 16:22 2010
X-Face
Picon

Re: left join with subquery and WHERE

On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> select * from pricegroups 
> left join (select * from products_pricegroups
> where products_pricegroups.product_id=1) as a 
> on (pricegroups.id=a.pricegroup_id)

   Currently it's impossible to do with SQLObject as it assumes both sides
of a JOIN are table names, not a subselect. That could be fixed.

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------
Oleg Broytman | 14 May 16:24 2010
X-Face
Picon

Re: left join with subquery and WHERE

On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> > select * from pricegroups 
> > left join (select * from products_pricegroups
> > where products_pricegroups.product_id=1) as a 
> > on (pricegroups.id=a.pricegroup_id)

   Does the query work at all in MySQL?

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------
Imre Horvath | 14 May 16:43 2010
Picon

Re: left join with subquery and WHERE

On Fri, 2010-05-14 at 18:24 +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote:
> > On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> > > select * from pricegroups 
> > > left join (select * from products_pricegroups
> > > where products_pricegroups.product_id=1) as a 
> > > on (pricegroups.id=a.pricegroup_id)
> 
>    Does the query work at all in MySQL?
> 
> Oleg.

It's in sqlite, but it works.
It's also works in postgres.
The goal is to select all pricegroups for a product, even if it's not
set for the given product.
this is my test db:

sqlite> .schema
CREATE TABLE pricegroups (id integer primary key);
CREATE TABLE products (id integer primary key, name text);
CREATE TABLE products_pricegroups (id integer primary key, product_id
integer, pricegroup_id integer, percent integer);

sqlite> select * from products;
1|a
2|b
3|v

sqlite> select * from pricegroups;
1
2
3
4
5
6
7
8

sqlite> select * from products_pricegroups;
1|1|1|10
2|1|2|20
3|2|4|20

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=1) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1|1|1|1|10
2|2|1|2|20
3||||
4||||
5||||
6||||
7||||
8||||

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=2) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1||||
2||||
3||||
4|3|2|4|20
5||||
6||||
7||||
8||||

I hope i was clear

Regards:
Imre Horvath

------------------------------------------------------------------------------

Gmane