Thread: [SQLObject] Reference to one of multiple tables
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
sqlobject-discuss
|
From: Timo <tim...@gm...> - 2011-12-28 21:30:34
|
I'm stuck at a part of my program, but I'm not sure if this is the right
place to ask. Excuses if it's not.
I have a DVD object which can have different statusses, like "on loan",
"sold", etc... A dvd can only have 1 status at the time, but I'm confused
about how to link it to 1 table only so I can access the correct status
with dvd.status .
Here's an example:
class DVD(SQLObject):
status = ???? # Should be sold or onloan data
# ... special "dvd" data
class Sold(SQLObject):
dvd = ForeignKey('DVD')
# ... special "sold" data
class OnLoan(SQLObject):
dvd = ForeignKey('DVD')
# ... special "on loan" data
Greets,
Timo
|
|
From: Petr J. <pet...@tp...> - 2011-12-28 22:34:30
|
> I'm stuck at a part of my program, but I'm not sure if this is the right
> place to ask. Excuses if it's not.
>
> I have a DVD object which can have different statusses, like "on loan",
> "sold", etc... A dvd can only have 1 status at the time, but I'm confused
> about how to link it to 1 table only so I can access the correct status
> with dvd.status .
>
> Here's an example:
>
> class DVD(SQLObject):
> status = ???? # Should be sold or onloan data
> # ... special "dvd" data
>
> class Sold(SQLObject):
> dvd = ForeignKey('DVD')
> # ... special "sold" data
>
> class OnLoan(SQLObject):
> dvd = ForeignKey('DVD')
> # ... special "on loan" data
>
> Why not just one table and the sqlmeta<http://sqlobject.org/SQLObject.html#using-sqlmeta?>?
connection = connectionForURI('sqlite:/:memory:')
class DVD(SQLObject):
_connection = connection
title = StringCol(default="")
inShelve = BoolCol(default=True)
sold = BoolCol(default=False)
onLoan = BoolCol(default=False)
def _get_statuss(self):
if self.sold:
return "sold"
elif self.onLoan:
return "onLoan"
elif self.inShelf:
return "inShelf"
DVD.createTable()
myFirstDVD = DVD(title="Timo")
print myFirstDVD.statuss
HTH
Petr
|
|
From: Timo <tim...@gm...> - 2011-12-28 23:26:06
|
Sorry, forwarding to list. Overlooked the reply-all button in Gmail
interface.
2011/12/28 Petr Jakeš <pet...@tp...>
>
> I'm stuck at a part of my program, but I'm not sure if this is the right
>> place to ask. Excuses if it's not.
>>
>> I have a DVD object which can have different statusses, like "on loan",
>> "sold", etc... A dvd can only have 1 status at the time, but I'm confused
>> about how to link it to 1 table only so I can access the correct status
>> with dvd.status .
>>
>> Here's an example:
>>
>> class DVD(SQLObject):
>> status = ???? # Should be sold or onloan data
>> # ... special "dvd" data
>>
>> class Sold(SQLObject):
>> dvd = ForeignKey('DVD')
>> # ... special "sold" data
>>
>> class OnLoan(SQLObject):
>> dvd = ForeignKey('DVD')
>> # ... special "on loan" data
>>
>> Why not just one table and the sqlmeta<http://sqlobject.org/SQLObject.html#using-sqlmeta?>?
>
>
> connection = connectionForURI('sqlite:/:memory:')
>
> class DVD(SQLObject):
> _connection = connection
> title = StringCol(default="")
> inShelve = BoolCol(default=True)
> sold = BoolCol(default=False)
> onLoan = BoolCol(default=False)
> def _get_statuss(self):
> if self.sold:
> return "sold"
> elif self.onLoan:
> return "onLoan"
> elif self.inShelf:
> return "inShelf"
> DVD.createTable()
> myFirstDVD = DVD(title="Timo")
> print myFirstDVD.statuss
>
>
The statusses have all kind of information too, like price and buyer for
sold dvd's and return date for example in the loaned status.
But your example got me thinking. Is something like the following valid?
(working example)
from sqlobject import *
connection = connectionForURI('sqlite:/:memory:')
class Sold(SQLObject):
_connection = connection
dvd = ForeignKey('DVD')
buyer = StringCol(default="")
class OnLoan(SQLObject):
_connection = connection
dvd = ForeignKey('DVD')
returnDate = DateCol(default=None)
class DVD(SQLObject):
_connection = connection
title = StringCol(default="")
inShelve = BoolCol(default=True)
sold = SingleJoin('Sold', joinColumn='dvd')
onLoan = SingleJoin('OnLoan', joinColumn='dvd')
def _get_statuss(self):
if self.inShelf:
return
elif self.sold is not None:
return self.sold
elif self.onLoan is not None:
return self.onLoan
DVD.createTable()
Sold.createTable()
OnLoan.createTable()
myFirstDVD = DVD(title="Timo")
s = Sold(dvd=myFirstDVD)
print myFirstDVD.statuss
But then I should handle the inShelve bool value whenever self.sold or
self.onLoan are changed.
Greets and thanks,
Timo
> HTH
>
> Petr
>
|
|
From: Petr J. <pet...@tp...> - 2011-12-29 05:05:33
|
> But your example got me thinking. Is something like the following valid?
> (working example)
> from sqlobject import *
>
> If it is working, then it is (IMHO) valid :D
The DB structure is completely up to you of course. But think twice about
the DB model. When the production data is in the database already, it is a
pain to change the DB structure.
>
>
> But then I should handle the inShelve bool value whenever self.sold or
> self.onLoan are changed.
>
To keep the logic in the database is up to you. Of course you can
automatize it some way (directly in the DB, using DB machinery - computed
columns in the Firebird for example, which is IMHO not the best way to go
but it works)
Or maybe you can try something using:
def _set_soldCarefully(self, value):
self.sold = True
self.onLoan = False
self.inShelve = False
Regards
Petr
|
|
From: Timo <tim...@gm...> - 2011-12-29 14:25:14
|
2011/12/29 Petr Jakeš <pet...@tp...> > > But your example got me thinking. Is something like the following valid? >> (working example) >> from sqlobject import * >> >> If it is working, then it is (IMHO) valid :D > The DB structure is completely up to you of course. But think twice about > the DB model. When the production data is in the database already, it is a > pain to change the DB structure. > Hehe, that was what I meant with "valid". I wrote this application about 2 years ago (without SQLObject), and has become a great mess. So I'm trying to start over and do /the right thing/. > > >> >> >> But then I should handle the inShelve bool value whenever self.sold or >> self.onLoan are changed. >> > > To keep the logic in the database is up to you. Of course you can > automatize it some way (directly in the DB, using DB machinery - computed > columns in the Firebird for example, which is IMHO not the best way to go > but it works) > > Or maybe you can try something using: > I think I'm going for the _set_status() and _get_status() approach. Write some code which gets and sets the correct table in these methods, so I can set/get the status in the program without hassle. Thanks for your help, Timo > > def _set_soldCarefully(self, value): > self.sold = True > self.onLoan = False > self.inShelve = False > > Regards > > Petr > > > > > |
|
From: Frank W. <fra...@no...> - 2011-12-29 15:01:51
|
Hi, i think the way to do this is described in: http://sqlobject.org/Inheritance.html class Status(InheritableSQLObject): # ... general status info class Sold(Status): #... sold-specific info class OnLoan(Status): #... onloan-specific info class DVD(SQLObject): status = ForeignKey("Status") # ... special "dvd" data does this help? Regards, Frank On Thu, Dec 29, 2011 at 15:25, Timo <tim...@gm...> wrote: > > 2011/12/29 Petr Jakeš <pet...@tp...> >> >> >>> But your example got me thinking. Is something like the following valid? >>> (working example) >>> from sqlobject import * >>> >> If it is working, then it is (IMHO) valid :D >> The DB structure is completely up to you of course. But think twice about >> the DB model. When the production data is in the database already, it is a >> pain to change the DB structure. > > Hehe, that was what I meant with "valid". I wrote this application about 2 > years ago (without SQLObject), and has become a great mess. So I'm trying to > start over and do /the right thing/. > >> >> >>> >>> >>> >>> But then I should handle the inShelve bool value whenever self.sold or >>> self.onLoan are changed. >> >> >> To keep the logic in the database is up to you. Of course you can >> automatize it some way (directly in the DB, using DB machinery - computed >> columns in the Firebird for example, which is IMHO not the best way to go >> but it works) >> >> Or maybe you can try something using: > > I think I'm going for the _set_status() and _get_status() approach. Write > some code which gets and sets the correct table in these methods, so I can > set/get the status in the program without hassle. > > Thanks for your help, > Timo > >> >> >> def _set_soldCarefully(self, value): >> self.sold = True >> self.onLoan = False >> self.inShelve = False >> >> Regards >> >> Petr >> >> >> >> >> > > > ------------------------------------------------------------------------------ > Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex > infrastructure or vast IT resources to deliver seamless, secure access to > virtual desktops. With this all-in-one solution, easily deploy virtual > desktops for less than the cost of PCs and save 60% on VDI infrastructure > costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
|
From: Oleg B. <ph...@ph...> - 2011-12-29 18:35:28
|
On Thu, Dec 29, 2011 at 03:48:09PM +0100, Frank Wagner wrote: > http://sqlobject.org/Inheritance.html Please be warned this kind of inheritance in SQLObject is very limited. The page mentioned above lists some limitations. Timo, I'm going to write a more detailed answer a bit later. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
|
From: Timo <tim...@gm...> - 2011-12-30 10:37:52
|
2011/12/29 Oleg Broytman <ph...@ph...> > On Thu, Dec 29, 2011 at 03:48:09PM +0100, Frank Wagner wrote: > > http://sqlobject.org/Inheritance.html > Thanks Frank for pointing that out. I usually go directly to the documentation page on the website, so I overlooked this. > > Please be warned this kind of inheritance in SQLObject is very > limited. The page mentioned above lists some limitations. > > Timo, I'm going to write a more detailed answer a bit later. > That would be great! I'm eager to learn more about SQLObject and create a decent database model for my application. Greets, Timo > > Oleg. > -- > Oleg Broytman http://phdru.name/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > > ------------------------------------------------------------------------------ > Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex > infrastructure or vast IT resources to deliver seamless, secure access to > virtual desktops. With this all-in-one solution, easily deploy virtual > desktops for less than the cost of PCs and save 60% on VDI infrastructure > costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
|
From: Oleg B. <ph...@ph...> - 2011-12-30 12:26:38
|
Well, her I am, sorry for the late answer.
On Wed, Dec 28, 2011 at 10:30:28PM +0100, Timo wrote:
> class DVD(SQLObject):
> status = ???? # Should be sold or onloan data
> # ... special "dvd" data
>
> class Sold(SQLObject):
> dvd = ForeignKey('DVD')
> # ... special "sold" data
>
> class OnLoan(SQLObject):
> dvd = ForeignKey('DVD')
> # ... special "on loan" data
1. If you insist on having a separate table for an every status you
stuck with a setup that ain't supported by SQL. You can emulate joins
using SQLObject but you have to understand once you move operations to
the client side you will always have to do all processing on the client
side. In that case I recommend to add 2 columns and a calculated
attribute:
class DVD(SQLObject):
status_string = StringCol()
status_id = KeyCol() # No, not ForeignKey
# Attribute '.status_row'
def _get_status_row(self):
if self.status_string == 'on loan':
return OnLoan.get(self.status_id)
elif self.status_string == 'sold':
return Sold.get(self.status_id)
...etc...
To simplify the code you can create and use a static dict:
_status_dict = {
'on loan': OnLoan,
'sold': Sold,
}
def _get_status_row(self):
return self._status_dict[self.status_string].get(self.status_id)
2. If you want to use the power of SQL you have to combine all statuses
in one table. For example, make it wide:
class Status(SQLObject):
dvd = ForeignKey('DVD')
# Attributes for status 'on load'
...
# Attributes for status 'sold'
...
class DVD(SQLObject):
status = ForeignKey('DVD')
Here you can do SQL-wise joins and filter them on the server side.
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
