sqlobject-discuss

RE: [SQLObject] request for elaboration on code

[<jws...@ra...>]

>> Firstly what is 'id' representing?
>
>It is an optional to give the ID parameter when creating an object, this is
the
>value to use if not expected to be auto-generated.

So when I see 'id' I can think 'primary key'?

I have specified the table name and primary key of my main table with
_table and _idName, but I also have a foreign key to a validation table. Do
I
need to specify the primary key of the foreign table? What does that look
like?
Unlike the examples in the docs this is a many-to-one relationship. The
validation 
table will not have an associated object.

>DBs, Postgres provides no cursor-level way to retrieve auto-generated IDs.
>There are alternatives to doing it by oid, such as pre-selecting the
nextval
>on the sequence, but using oids seemed to be the least intrusive to
>SQLObject's design.

I'm having a hard time getting my head around why SQLOject needs to do a
select at 
all. I am trying to create an object- i.e. write a row. My data gets written
to 
postgres as I expect, then I get this other error complaining about oid's
not 
existing. 

RE: [SQLObject] request for elaboration on code

[<jws...@ra...>]

>Yes, 'id' (or whatever field '_idName' is for you) is the PK and the value
>SQLObject uses to uniquely identify objects in its cache etc.

So, for posterity, _idName is the name of the primary key column,
and id is the unique value that is associated with a new row.

>SQLObject needs to know the ID value for any object, at least so that
future
>updates and selects can be made (where idName = idValue), and for the
>SQLObject cache. So with oids you will still definitely need a way to get
any
>auto-generated id column values in this method.

Based on what I see in DBconnection.py, I understand that the idiom is that
we format the SQL, write out the record, then read back and return the
unique
id to be stored in the in-memory object we are creating. 

>There are alternatives to doing it by oid, such as pre-selecting the
nextval
>on the sequence, but using oids seemed to be the least intrusive to
>SQLObject's design.

I think it would be reasonable to either derive the standard sequence name 
as you described or accept it in the class definition like _seqName or 
similar. Nextval could then operate in the (somewhat)same way( that we are 
currently using oid. Is there organizational resistance to doing it that
way? 

In other words, if I do it myself, will it be accepted into the project?

RE: [SQLObject] request for elaboration on code

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

>> There are alternatives to doing it by oid, such as pre-selecting the
> nextval
>> on the sequence, but using oids seemed to be the least intrusive to
>> SQLObject's design.
>
> I think it would be reasonable to either derive the standard sequence name
> as you described or accept it in the class definition like _seqName or
> similar. Nextval could then operate in the (somewhat)same way( that we are
> currently using oid. Is there organizational resistance to doing it that
> way?
>
> In other words, if I do it myself, will it be accepted into the project?

I don't see any organizational resistance to it. The only reason an alternate
PostgresConnection (PostgresSeqConnection?) would be problematic is if it
altered the signature of _queryInsertID. That's why a style-derived system
will be easiest. (This means that the special case ones where _seqName would
be necessary still won't be feasible.)

Just so we're on the same page, _queryInsertID in this version will:

if not id: select nextval
 |
create insert SQL
 |
execute insert


- Luke

RE: [SQLObject] request for elaboration on code

[<jws...@ra...>]

>SQLObject originally used the more traditional technique of doing 
>"SELECT nextval(seq_name)" then using the result to insert into the 

We can either do nextval up front or do the insert,let postgres
fill in the id and call currval to read it back.

>Anyway, I was planning on going back to using sequences, or allowing for 
>some other configurable behavior, I just haven't done so yet.

I will be working on this myself, as I need it to proceed with my 
project and I have Big Plans(tm) for SQLObject. I will post my
findings.

RE: [SQLObject] request for elaboration on code

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

> So when I see 'id' I can think 'primary key'?

Yes, 'id' (or whatever field '_idName' is for you) is the PK and the value
SQLObject uses to uniquely identify objects in its cache etc.

> I have specified the table name and primary key of my main table with
> _table and _idName, but I also have a foreign key to a validation table. Do
> I need to specify the primary key of the foreign table? What does that look
> like? Unlike the examples in the docs this is a many-to-one relationship. The
> validation table will not have an associated object.

If it really won't have an SQLObject class for it, then you don't need to do
anything more than include the field that is in the main table as whatever
datatype it is. (foreign_id = IntCol() or similar).

If you do decide to make in an object in order to use it through SQLObject's
interface, it would fit the example of ForeignKey() Column in the main table
class and MultipleJoin('MainTableName') in the validation table class.

> I'm having a hard time getting my head around why SQLOject needs to do a
> select at
> all. I am trying to create an object- i.e. write a row. My data gets written
> to postgres as I expect, then I get this other error complaining about oid's
> not existing.

SQLObject needs to know the ID value for any object, at least so that future
updates and selects can be made (where idName = idValue), and for the
SQLObject cache. So with oids you will still definitely need a way to get any
auto-generated id column values in this method.

- Luke

Re: [SQLObject] request for elaboration on code

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

jws...@ra... wrote:
> I'm having a hard time getting my head around why SQLOject needs to
> do a select at all. I am trying to create an object- i.e. write a
> row. My data gets written to postgres as I expect, then I get this
> other error complaining about oid's not existing.

SQLObject originally used the more traditional technique of doing 
"SELECT nextval(seq_name)" then using the result to insert into the 
table.  There was a change to using OIDs that meant that we did't have 
to depend on specific sequence names.  But since then there were some 
concerns raised that (as you're finding) some tables don't have OIDs, 
and they aren't indexed by default in all PostgreSQL versions, which 
could lead to some bad performance.

Anyway, I was planning on going back to using sequences, or allowing for 
some other configurable behavior, I just haven't done so yet.

   Ian