sqlobject-discuss

[SQLObject] keyless tables

[Nick <ni...@dd...>]

Question:

How do you create a class that accesses a table with no key?  For
example, a Person table references a PhoneNumber where the only 2
columns are person_id and phone_number (not a reference to another table
but a phone number)?  Currently, you can fudge the values of a
RelatedJoin for the correct query to be generated, e.g.
otherClass=intermediateTable and joinColumn=otherColumn, but are the
returned values correct?  Will operations work correctly?

Nick

Re: [SQLObject] keyless tables

[Luke O. <lu...@me...>]

I'd be mighty surprised if this worked, SQLObject makes some
fundamental assumptions about having a single column integer key
field to uniquely identify objects.  "keyless" tables can more easily
thought of as composite key tables (for me), and supporting such
things in SQLObject would require major modification. (In this case,
a composite of person_id and phone_number uniquely identify a
record.)

Is there a reason you can't add an artificial surrogate key?

(Specifically addressing Joins, the query may work but it will then
attempt to create PhoneNumber objects for every 'id' returned, as
PhoneNumber(id) (really cls(id) ), and this will surely fail. :))

- Luke

> How do you create a class that accesses a table with no key?  For
> example, a Person table references a PhoneNumber where the only 2
> columns are person_id and phone_number (not a reference to another
> table
> but a phone number)?  Currently, you can fudge the values of a
> RelatedJoin for the correct query to be generated, e.g.
> otherClass=intermediateTable and joinColumn=otherColumn, but are
> the
> returned values correct?  Will operations work correctly?

Re: [SQLObject] keyless tables

[Ian B. <ia...@co...>]

On Wed, 2003-04-30 at 15:03, Nick wrote:
> How do you create a class that accesses a table with no key?  For
> example, a Person table references a PhoneNumber where the only 2
> columns are person_id and phone_number (not a reference to another table
> but a phone number)?  Currently, you can fudge the values of a
> RelatedJoin for the correct query to be generated, e.g.
> otherClass=intermediateTable and joinColumn=otherColumn, but are the
> returned values correct?  Will operations work correctly?

I would subclass RelatedJoin, changing the performJoin method.  In
general I think any table that doesn't have a key should be represented
with a join, and rows in that table won't be turned into full objects. 
That means they won't be mutable, but it shouldn't be a problem to just
add and delete rows instead of changing them.

I'd be happy to include any novel joins people make (like this one).

  Ian

Re: [SQLObject] keyless tables

[Nick <ni...@dd...>]

On Wed, 2003-04-30 at 15:25, Luke Opperman wrote:
> Is there a reason you can't add an artificial surrogate key?

Because it's sloppy :)  I prefer to fix the tool to fit the practice
rather than change a perfectly good practice to fit the tool.  In my
case I've got a permissions table that maps groups and files to
permissions.  It would be very messy to create intermediate tables just
so SQLObject can do RelatedJoins.

On Wed, 2003-04-30 at 15:46, Ian Bicking wrote:
> I would subclass RelatedJoin, changing the performJoin method.  In
> general I think any table that doesn't have a key should be represented
> with a join, and rows in that table won't be turned into full objects. 
> That means they won't be mutable, but it shouldn't be a problem to just
> add and delete rows instead of changing them.

Yes, that's pretty much how I did it with my old framework.  However,
this can create a *lot* of write queries if you're changing a bunch of
different columns.  Which goes back to the whole transaction discussion.

Nick

Re: [SQLObject] keyless tables

[Ian B. <ia...@co...>]

On Wed, 2003-04-30 at 16:11, Nick wrote:
> On Wed, 2003-04-30 at 15:46, Ian Bicking wrote:
> > I would subclass RelatedJoin, changing the performJoin method.  In
> > general I think any table that doesn't have a key should be represented
> > with a join, and rows in that table won't be turned into full objects. 
> > That means they won't be mutable, but it shouldn't be a problem to just
> > add and delete rows instead of changing them.
> 
> Yes, that's pretty much how I did it with my old framework.  However,
> this can create a *lot* of write queries if you're changing a bunch of
> different columns.  Which goes back to the whole transaction discussion.

Another option might be an extend method for joins, e.g., addAddress()
and extendAddress().  That could be turned into a single INSERT.  For
deleting, I'm less sure.  Though clearAddresses() might be a
possibility.  

There's also the possibility of making the (example) .addresses into an
object more like what you get from .select().  That is, it would allow
list operations, and batch them.  So you'd get
somePerson.addresses.extend(addressList), and somePerson.addresses would
be an iterator that also had list-like methods (but you'd have to use
list() to actually get a list from it).

  Ian