sqlobject-discuss — All things SQLObject-related

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] creating tables at run time

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

maxwell hammer wrote:
> Not sure if this is safe but this is what works
> 
> from SQLObject import *
> import new
> import copy
> 
> conn = MySQLConnection(user='test',db='test')
> 
> class TMessageBoard(SQLObject):
>    _connection=conn
>    user_name=StringCol()
>    boardname=StringCol()
>    parent=IntCol()
>    joinColumn="MessageLists"
> 
>    def _getcolumns(self):
>            return [c.kw['name'] for c in self._columns]
> 
>    _getcolumns = classmethod(_getcolumns)
> 
> 
> t = new.classobj(
>            'fredy',
>            (TMessageBoard,),
>            {
> 
>            })
> print dir(t)
> 
> print t
> t.createTable(True)
> and i end up with a table named fredy

Yep, that's safe, and entirely equivalent to the class statement, so all 
these are exactly the same:

class fredy(TMessageBoard):
     pass

fredy = new.classobj('fredy', (TMessageBoard,), {})

fredy = type('fredy', (TMessageBoard,), {})


That type() can be used like this is poorly documented, but you can 
think of it that a class's class is "type", and you're calling the 
constructor for classes.  Well, actually SQLObject's metaclass (class's 
class) is MetaSQLObject, but I believe type is smart enough to figure 
this out by looking at the superclasses (TMessageBoard,).  Confusingly, 
the traditional use of type() differs from the constructor only by 
signature.

   Ian

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

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

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

> 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

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

[SQLObject] creating tables at run time

[maxwell h. <no...@ms...>]

Not sure if this is safe but this is what works

from SQLObject import *
import new
import copy

conn = MySQLConnection(user='test',db='test')

class TMessageBoard(SQLObject):
    _connection=conn
    user_name=StringCol()
    boardname=StringCol()
    parent=IntCol()
    joinColumn="MessageLists"

    def _getcolumns(self):
            return [c.kw['name'] for c in self._columns]

    _getcolumns = classmethod(_getcolumns)


t = new.classobj(
            'fredy',
            (TMessageBoard,),
            {

            })
print dir(t)

print t
t.createTable(True)
and i end up with a table named fredy

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.  
http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca

[SQLObject] Creating new tables at run time.

[maxwell h. <no...@ms...>]

I have searched the previous postings on this list, and have searched the 
docs and maybe i do not under stand but anyway here is what i would like to 
do.

in the example u have

class Person(SQLObject):

    firstName = StringCol(length=100)
    middleInitial = StringCol(length=1, default=None)
    lastName = StringCol(length=100)

and i can create a table named person

but since it is defined as a class the name of the table is "static"

what i would like to do is create many tables at "runtime" which have 
exactly the same properties as class Person, but would have different table 
names

ie
Arizona=Person(TableName)
Arizona.createTable()

and then i could go Arizona.new(.....) which would a insert a row into what 
ever Arizona's Table name is called.

Anyway if this makes sense then i would be greatful :)

Thanx

John

_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca

Re: [SQLObject] request for elaboration on code

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

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

> Secondly, the SQL in the latter case is NOT going to work if the tables have
> been created without oid's.

Yes, I suppose this could be mentioned in the requirements (although I've never
seen a table without oids in my work). Basically, unlike some of the other
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.

If you have a need for an alternative way, there will have to be a subclass of
PostgresConnection, most likely that takes some style object or attributes for
determining how to construct sequence names. (The default for SERIAL columns
is tableName_idName_seq I believe, a style that would be easy to calculate in
the _queryInsertID method.)

Cool?

- Luke

[SQLObject] request for elaboration on code

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

I am trying to use a postgres connection and am having an issue with the
following code from DBconnection.py


    def _queryInsertID(self, conn, table, idName, id, names, values):
        c = conn.cursor()
        if id is not None:
            names = [idName] + names
            values = [id] + values
        q = self._insertSQL(table, names, values)

        if self.debug:
            self.printDebug(conn, q, 'QueryIns')
        c.execute(q)

        if id is None:
        ^^^^^^^^^^^^^^^
            c.execute('SELECT %s FROM %s WHERE oid = %s'
                      % (idName, table, c.lastoid()))
            id = c.fetchone()[0]
        if self.debugOutput:
            self.printDebug(conn, id, 'QueryIns', 'result')
        return id

Firstly what is 'id' representing? 

Secondly, the SQL in the latter case is NOT going to work if the tables have
been created without oid's.

Re: [SQLObject] SQLObject's inheritance patch version 2a

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

On Thu, Feb 12, 2004 at 11:07:29PM -0500, Daniel Savard wrote:
>    * This patch now correctly retreive old objects from the database

   Now it works. Thank you!

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

[SQLObject] SQLObject's inheritance patch version 2a

[Daniel S. <sa...@gn...>]

Hello,

    You can get this new version of the patch at the following URL:
    http://www.xsoli.com/sqlobject-inheritance-2a.patch

    Here is a new patch that add simple inheritance to SQLObject 0.8.1.
    For those who tried one of my patch, here are the modification from 
the first and second patch:

    * As suggested by Ian Bicking, each child classes now have the same
      ID than the parent class.  No more need for childID column and
      parent foreignKey (with a small speed boost).
    * No more need to call getSubClass as the 'latest' child will always
      be returned when an instance of a class is created.
    * This patch now seems to works correctly with addColumn, delColumn,
      addJoin and delJoin.
    * This patch now correctly retreive old objects from the database
      (thanks to Oleg Broytmann for finding this bug)

    With this patch, the following code:

class Person(SQLObject):
    _inheritable = 1        # I want this class to be inherited
    firstName = StringCol()
    lastName = StringCol()

class Employee(Person):
    _inheritable = 0        # If I don't want this class to be inherited
    position = StringCol()

    will generate the following tables:

CREATE TABLE person (
    id INT PRIMARY KEY,
    child_name TEXT,
    first_name TEXT,
    last_name TEXT
);

CREATE TABLE employee (
    id INT PRIMARY KEY,
    position TEXT
)
 

    A new class attribute '_inheritable' is added.  When this new 
attribute is set to 1, the class is marked 'inheritable' and a new 
colomns will automatically be added: childName (TEXT).

    Each class that inherits from a parent class will get the same ID as 
the parent class.  So, there is no need to keep track of parent ID and 
child ID as they are the same.

    The column childName will contain the name of the child class (for 
exemple 'Employee').  This will permit to a class to always return its 
child class if available (a person that is also an employee will always 
return an instance of the employee class).

    For exemple, the following code:
p = Person(firstName='John', lastName='Doe')
e = Employee(firstName='Jane', lastName='Doe', position='Chief')
p2 = Person(1)

    Will create the following data in the database:
*Person*
id
	child_name
	first_name
	last_name
0
	Null
	John
	Doe
1
	Employee
	Jane
	Doe


*Employee*
id
	position
1
	Chief


    You will still be able to ask for the attribute normally:
e.firstName will return Jane and setting it will write the new value in 
the person table.

    If you use p2, as p2 is a person object, you will get an employee 
object.
person(0) will return a Person instance and will have the following 
attributes: firstName and lastName
person(1) or employee(1) will each return the same Employee instance and 
will have the following attributes: firstName, lastName and position

    Also, deleting a person or an employee that are linked will destroy 
both entries as one would expect.

    The SQLObject q magic also work.  Using this select are valid:
Employee.select(Employee.q.firstName == 'Jane' & Employee.q.position == 
'Chief') will return Jane Doe
Employee.select(Person.q.firstName == 'Jane' & Employee.q.position == 
'Chief') will return Jane Doe
Employee.select(Employee.q.lastName == 'Doe') will only return Jane Doe 
(as Joe isn't an employee)
Person.select(Person.q.lastName == 'Doe') will return both entries

    The SQL where clause will contain additional clauses when used with 
'inherited' classes.  These clauses are the link between the id and the 
parent id.  This will look like the following request:
    SELECT employee.id, employee.id, employee.first_name, 
employee.last_name, from employee FROM person, employee WHERE 
person.first_name = 'Jane' AND employee.position = 'Chief' AND person.id 
= employee.id

    Some limitation or notice about this patch:

    * Only simple inheritance will work.  It is not possible to inherits
      from multiple SQLObject classes.
    * It is possible to inherits from an inherited class and this will
      works well.  In the above exemple, you can have a Chief class that
      inherits from Employee and all parents attributes will be
      available through the Chief class.
    * You may not redefine a parent column in a inherited class (this
      will raise an exception).
    * If you don't want 'childName' columns in your last class (one that
      will never be inherited), you must set '_inheritable' to 0 in this
      class.
    * I made this patch because I needed to be able to have automatic
      inheritance with linked table.
    * This patch works for me, it may not works for you.  I tried to do
      my best but it is possible that I broke some things... So, there
      is no warranty that this patch will work.
    * This patch is released under the LGPL (GNU Lesser General Public
      License) as the original SQLObject code.
    * Thanks to Ian Bicking for SQLObject, this is a wonderful python
      module.
    * If you have suggestion, bugs, or patch to this patch, you can
      contact me at <sqlobject xsoli.com>


Thanks,
Daniel Savard
XSOLI Inc.

[SQLObject] Re: SQLObject inheritance patch 2

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

On Thu, Feb 12, 2004 at 08:51:06AM -0500, Daniel Savard wrote:
>    I sent you a little test (your test with very smalls modifications) 
> and the complete SQLObject.py file patched with my patch.

   Your SQLObject.py is exactly like my - byte to byte. No difference.

>    It is not needed to create and drop table each time.  Only to be 
> sure they are created.
>    Can you try it and tell me if its works now ?

   Your test works ok. But the following program does not:

import SQLObject
db_conn = SQLObject.PostgresConnection(db = 'test')

class Woman(SQLObject.SQLObject):
   _inheritable = 1
   _connection = db_conn
   name = SQLObject.StringCol()

class Wilma(Woman):
   smile = SQLObject.IntCol()

woman = Woman(1)
print woman # prints <Wilma 1 childName=None smile=1>, ok
print woman.smile # => 1
print woman.name

   Output:

<Wilma 1 childName=None smile=1>
1
Traceback (most recent call last):
  File "w2", line 15, in ?
    print woman.name
  File "<string>", line 1, in <lambda>
AttributeError: 'Wilma' object has no attribute '_parent'

>    As for assigning the _parent variable only in new, it is needed 
> because we must link to the true instance of the parent class at this 
> time to be able to get the good properties.

   Aha, I see the problem now. Your patch works only when a program
creates a new object. Than the class got _parent attribute. If the
program does not calls new() - oops, attribute error.

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

[SQLObject] Re: SQLObject inheritance patch 2

[Daniel S. <sa...@gn...>]

Hello Oleg,

    I sent you a little test (your test with very smalls modifications) 
and the complete SQLObject.py file patched with my patch.
    It is not needed to create and drop table each time.  Only to be 
sure they are created.

    Can you try it and tell me if its works now ?

    As for assigning the _parent variable only in new, it is needed 
because we must link to the true instance of the parent class at this 
time to be able to get the good properties.

thanks,
Daniel Savard

Oleg Broytmann wrote:

>On Wed, Feb 11, 2004 at 10:40:31PM -0500, Daniel Savard wrote:
>  
>
>>   I tried your test with MySQL and PostGreSQL under Python 2.2.3 and 
>>2.3.3 and it worked well. 
>>   What database are you using ?
>>    
>>
>
>   Python 2.3.3, Postgres, SQLObject 0.5.1 with your patch.
>  
>
>>   Did you create the table (createTable method) ?
>>    
>>
>
>   But of course! :)
>  
>
>>   You should add theses two lines after class creation:
>>Woman.createTable(1)
>>Wilma.createTable(1)
>>    
>>
>
>   I did it this way:
>
>Woman.dropTable(ifExists=True)
>Woman.createTable()
>
>Wilma.dropTable(ifExists=True)
>Wilma.createTable()
>
>   Of course I do not do it on every invocation of the program.
>
>  
>
>>   I tried your sample without creating the table and get the same 
>>problem as your.
>>   Adding theses two lines just after the class creation corrected it.
>>    
>>
>
>   Still don't work. Well, at least I know it is possible.
>
>   Where the _parent attribute should be created? May be I applied the
>patch wrong way (though I didn't see any errors/rejects).
>
>Oleg.
>  
>

[SQLObject] Re: SQLObject inheritance patch 2

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

On Wed, Feb 11, 2004 at 10:40:31PM -0500, Daniel Savard wrote:
>    I tried your test with MySQL and PostGreSQL under Python 2.2.3 and 
> 2.3.3 and it worked well. 
>    What database are you using ?

   Python 2.3.3, Postgres, SQLObject 0.5.1 with your patch.

>    Did you create the table (createTable method) ?

   But of course! :)

>    You should add theses two lines after class creation:
> Woman.createTable(1)
> Wilma.createTable(1)

   I did it this way:

Woman.dropTable(ifExists=True)
Woman.createTable()

Wilma.dropTable(ifExists=True)
Wilma.createTable()

   Of course I do not do it on every invocation of the program.

>    I tried your sample without creating the table and get the same 
> problem as your.
>    Adding theses two lines just after the class creation corrected it.

   Still don't work. Well, at least I know it is possible.

   Where the _parent attribute should be created? May be I applied the
patch wrong way (though I didn't see any errors/rejects).

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

[SQLObject] Re: SQLObject inheritance patch 2

[Daniel S. <sa...@gn...>]

Hello Oleg,

    I tried your test with MySQL and PostGreSQL under Python 2.2.3 and 
2.3.3 and it worked well. 
    What database are you using ?

    Did you create the table (createTable method) ?

    You should add theses two lines after class creation:
Woman.createTable(1)
Wilma.createTable(1)

    I tried your sample without creating the table and get the same 
problem as your.
    Adding theses two lines just after the class creation corrected it.

Daniel Savard

Oleg Broytmann wrote:

>Hi!
>
>   I tried a simplest test.
>
>import SQLObject
>from db import db_conn
>
>
>class Woman(SQLObject.SQLObject):
>   _inheritable = 1
>   _connection = db_conn
>
>   name = SQLObject.StringCol()
>
>
>class Wilma(Woman):
>   smile = SQLObject.IntCol()
>
>
>wilma = Wilma.new(name="Wilma", smile=1)
>barney = Barney.new(passion=wilma)
>
>woman = Woman(1)
>print woman # prints <Wilma 1 childName=None smile=1>, ok
>print woman.smile # => 1
>print woman.name
>
>Traceback (most recent call last):
>  File "test.py", line 10, in ?
>    print woman.name
>  File "<string>", line 1, in <lambda>
>AttributeError: 'Wilma' object has no attribute '_parent'
>
>   Oops. I found many places in the code where _parent attribute is
>used, but only one where it is assigned - in new(). Shouldn't it be
>assigned in __new__ too?
>
>Oleg.
>  
>

[SQLObject] Re: SQLObject inheritance patch 2

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

Hi!

   I tried a simplest test.

import SQLObject
from db import db_conn


class Woman(SQLObject.SQLObject):
   _inheritable = 1
   _connection = db_conn

   name = SQLObject.StringCol()


class Wilma(Woman):
   smile = SQLObject.IntCol()


wilma = Wilma.new(name="Wilma", smile=1)
barney = Barney.new(passion=wilma)

woman = Woman(1)
print woman # prints <Wilma 1 childName=None smile=1>, ok
print woman.smile # => 1
print woman.name

Traceback (most recent call last):
  File "test.py", line 10, in ?
    print woman.name
  File "<string>", line 1, in <lambda>
AttributeError: 'Wilma' object has no attribute '_parent'

   Oops. I found many places in the code where _parent attribute is
used, but only one where it is assigned - in new(). Shouldn't it be
assigned in __new__ too?

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

[SQLObject] Re: SQLObject inheritance patch 2

[Daniel S. <sa...@gn...>]

Hello,

    For those that need a downloadable version of my inheritance patch, 
here is the URL:
http://www.xsoli.com/sqlobject-inheritance-2.patch

Daniel Savard

Oleg Broytmann wrote:

>Hello!
>
>   I cannot find a downloadable version of the patch. ianbicking.org is
>down, and mail archive on the sf.net is not suitable to download
>attachments.
>   Can you send the latest version directly to me, please? Thank you in
>advance. Is the patch for SQLObject 2.5.1? Mail archive mentioned 2.8.1,
>but I suppose it's just a typo? :) Or should I download the latest
>version from CVS?
>
>Oleg.
>  
>

[SQLObject] Unknow attribute _SO_class_%s

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

Hello!

   If I have a table that references another table, and during the
program execution I am trying to dereference the field, but the class
for the field is unknown (the apropriate module had not been imported
yet) I get AttributeError: instance has no attribute _SO_class_Name. For
example:

import SQLObject
from db import db_conn

class Barney(SQLObject.SQLObject):
   _connection = db_conn

   passion = SQLObject.ForeignKey("Wilma")

barney = Barney(1)
print barney.passion

AttributeError: Barney instance has no attribute _SO_class_Wilma. I
have spent entire day reading SQLObject sources trying to understand
what is going on. To make things much more funny, in the real program
Barney has __getattr__ method that caused infinite recursion, but that's
another story...

   It would be helpful if SQLObject catch the AttributeError and reraise
it with a more meaningful message, something like "cannot make passion -
unknown class Willma".

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

[SQLObject] postgres varchar

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

SQLObject does not seem to care for varchar fields in postgres when
reverse-engineering an existing schema using "_fromDatabase = True" ?

return Col.StringCol, {'length': int(t[t.index('(')+1:-1])}
ValueError: substring not found in string.index

Is this an oversight or a policy decision?

[SQLObject] longID style

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

I would like to use the default style with the primary key column like
person_id and standard column names like person_name. Does the default style
have a name?

This-

	_style = Default(long_id=True)

Does not work, obviously.

Re: [SQLObject] Using fulltext indexes on Postgres

[Frank B. <fb...@fo...>]

Hallo,
Frank Barknecht hat gesagt: // Frank Barknecht wrote:

> I'd like to use a full text index on some of my SQLObject tables in
> PostgreSQL to speed things up a bit.

Okay, I decided to use tsearch now and implemented it. Maybe my
approach is interesting for others as well, so here's how I did it: 

I added the indexes and stuff as described in the tsearch docs. Then
it was a bit tricky to get the select right, because I use a complex
autogeneration of queries using SQLBuilder.

So I created a new SQLOp like this: 

from SQLObject import *
from SQLObject.SQLBuilder import SQLOp
def TSEARCH(expr, string):
	return SQLOp("##", expr, string)

Now this can be used with the "const" or "func" keyword: 

# colName is the name of an SQLObject Col(), op is the search word,
# and artist_tsearch, title_tsearch are the indexed tsearch columns.
# They get used instead of the SQLObject Col()s in queries:

if colName == "artist":
	subquery = TSEARCH(const.artist_tsearch, op)
	# this creates a query like: where artist_tsearch ## 'beatles' 
if colName == "title":
	subquery = TSEARCH(const.title_tsearch, op)
else: 
	# search in field without full text index:
	subquery = LIKE(someOtherColum, op)

This actually works rather well, but made my head spin last night.
Also the indexes make the db really huge. The data, which is imported
from a 16 MB csv file results in 561 MB data in the db directory of
PostgreSQL. I stripped all stop-words from the tsearch dictionary,
though, because I need to find artists like "the who", too. 

Searching is fast now.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

[SQLObject] SQLObject's inheritance patch version 2

[Daniel S. <sa...@gn...>]

--- SQLObject.orig/SQLObject.py	2003-11-12 12:03:45.000000000 -0500
+++ SQLObject/SQLObject.py	2004-02-07 00:57:58.000000000 -0500
@@ -4,6 +4,10 @@
 SQLObject is a object-relational mapper.  See SQLObject.html or
 SQLObject.txt for more.
 
+Modified by
+    Daniel Savard, Xsoli Inc <sqlobject xsoli.com> 7 Feb 2004
+    - Added support for simple table inheritance.
+
 This program is free software; you can redistribute it and/or modify
 it under the terms of the GNU Lesser General Public License as
 published by the Free Software Foundation; either version 2.1 of the
@@ -155,10 +159,37 @@
             classRegistry[registry] = {}
         classRegistry[registry][className] = newClass
 
+        #DSM: Need to keep the name of the class for easy access later
+        newClass._className = className
+        newClass._childClasses = {}
+        #DSM: Need to know very soon if the class is a children of an inheritable class.  
+        #DSM: If so, we keep a link to our parent class.
+        for cls in bases:
+            if hasattr(cls, '_inheritable') and cls._inheritable:
+                newClass._parentClass = cls
+                cls._childClasses[className] = newClass
+
         # We append to _columns, but we don't want to change the
         # superclass's _columns list, so we make a copy if necessary
         if not d.has_key('_columns'):
             newClass._columns = newClass._columns[:]
+        #DSM: If this class is a child of a parent class, we need to do some 
+        #DSM: attribute check and a a foreign key to the parent.
+        if newClass._parentClass:
+            #DSM: First, look for invalid column name: reserved ones or same as a parent
+            parentCols = [column.kw['name'] for column in newClass._columns]
+            for col in implicitColumns:
+                cname = col.kw['name']
+                if cname in ['childName']: 
+                    raise AttributeError, "The column name '%s' is reserved" % cname
+                if cname in parentCols: 
+                    raise AttributeError, "The column '%s' is already defined in an inheritable parent" % cname
+            #DSM: Remove columns if inherited from an inheritable class as we don't want them
+            #DSM: All we want is a foreign key that will link to our parent
+            newClass._columns = []
+        #DSM: If this is inheritable, add some default columns to be able to link to children
+        if hasattr(newClass, '_inheritable') and newClass._inheritable:
+            newClass._columns.append(Col.StringCol(name='childName',default=None))
         newClass._columns.extend(implicitColumns)
         if not d.has_key('_joins'):
             newClass._joins = newClass._joins[:]
@@ -218,6 +249,13 @@
         # SQL where-clause generation.  See the sql module for
         # more.
         newClass.q = SQLBuilder.SQLObjectTable(newClass)
+        #DSM: If we are a child, get the q magic from the parent
+        currentClass = newClass
+        while currentClass._parentClass:
+            currentClass = currentClass._parentClass
+            for col in currentClass._columns:
+                if type(col) == Col.ForeignKey: continue
+                setattr(newClass.q, col.kw['name'], getattr(currentClass.q, col.kw['name']))
 
         for column in newClass._columns[:]:
             newClass.addColumn(column)
@@ -349,6 +387,13 @@
     # it's set (default 1).
     _cacheValues = True
 
+    #DSM: The _inheritable attribute controls wheter the class can by
+    #DSM: inherited 'logically' with a foreignKey and back reference.
+    _inheritable = False	# Does this class is inheritable
+    _parentClass = None		# A reference to the parent class
+    _childClasses = {}		# Reference to child classes
+    childName = None		# Children name (to be able to get a subclass)
+
     # The _defaultOrder is used by SelectResults
     _defaultOrder = None
 
@@ -408,9 +453,29 @@
                 cache.put(id, cls, val)
             finally:
                 cache.finishPut(cls)
+        #DSM: If this class has a child, return the child, if not
+        if hasattr(val, 'childName'):
+             childName = val.childName
+             if childName is not None: 
+                 return val._childClasses[childName](id)
+        #DSM: This class doesn't have a child... return this class
         return val
 
-    def addColumn(cls, columnDef, changeSchema=False):
+    def addColumn(cls, columnDef, changeSchema=False, childUpdate=False):
+        #DSM: Try to add parent properties to the current class
+        #DSM: Only do this once if possible at object creation and once for 
+        #DSM: each new dynamic column to refresh the current class
+        if childUpdate or cls._parentClass:
+            for col in cls._parentClass._columns:
+                cname = col.kw['name']
+                if cname == 'childName': continue
+                setattr(cls, getterName(cname), eval('lambda self: self._parent.%s' % cname))
+                if not col.kw.has_key('immutable') or not col.kw['immutable']:
+                    setattr(cls, setterName(cname), eval('lambda self, val: setattr(self._parent, %s, val)' % repr(cname)))
+            if childUpdate:
+                makeProperties(cls)
+                return
+
         column = columnDef.withClass(cls)
         name = column.name
         assert name != 'id', "The 'id' column is implicit, and should not be defined as a column"
@@ -442,7 +507,7 @@
         # Here if the _get_columnName method isn't in the
         # definition, we add it with the default
         # _SO_get_columnName definition.
-        if not hasattr(cls, getterName(name)):
+        if not hasattr(cls, getterName(name)) or name == 'childName':
             setattr(cls, getterName(name), getter)
             cls._SO_plainGetters[name] = 1
 
@@ -459,7 +524,7 @@
             setattr(cls, '_SO_fromPython_%s' % name, column.fromPython)
             setattr(cls, rawSetterName(name), setter)
             # Then do the aliasing
-            if not hasattr(cls, setterName(name)):
+            if not hasattr(cls, setterName(name)) or name == 'childName':
                 setattr(cls, setterName(name), setter)
                 # We keep track of setters that haven't been
                 # overridden, because we can combine these
@@ -517,6 +582,11 @@
         if cls._SO_finishedClassCreation:
             makeProperties(cls)
 
+        #DSM: Update each child class if needed and existing (only for new 
+        #DSM: dynamic column as no child classes exists at object creation)
+        for c in cls._childClasses.values():
+            c.addColumn(columnDef, childUpdate=True)
+
     addColumn = classmethod(addColumn)
 
     def addColumnsFromDatabase(cls):
@@ -563,11 +633,30 @@
             cls._connection.delColumn(cls._table, column)
 
         if cls._SO_finishedClassCreation:
-            unmakeProperties(cls)
+            delattr(cls, name)
+        
+        #DSM: Update each child class if needed and delete properties for this column
+        for c in cls._childClasses.values():
+            delattr(c, name)
 
     delColumn = classmethod(delColumn)
 
-    def addJoin(cls, joinDef):
+    def addJoin(cls, joinDef, childUpdate=False):
+        #DSM: Try to add parent properties to the current class
+        #DSM: Only do this once if possible at object creation and once for 
+        #DSM: each new dynamic join to refresh the current class
+        if childUpdate or cls._parentClass:
+            for jdef in cls._parentClass._joins:
+                join = jdef.withClass(cls)
+                jname = join.joinMethodName
+                jarn  = join.addRemoveName
+                setattr(cls, getterName(jname), eval('lambda self: self._parent.%s' % jname))
+                if hasattr(join, 'remove'): setattr(cls, 'remove' + jarn, eval('lambda self,o: self._parent.remove%s(o)' % jarn))
+                if hasattr(join, 'add'): setattr(cls, 'add' + jarn, eval('lambda self,o: self._parent.add%s(o)' % jarn))
+            if childUpdate:
+                makeProperties(cls)
+                return
+
         # The name of the method we'll create.  If it's
         # automatically generated, it's generated by the
         # join class.
@@ -615,6 +704,11 @@
 
         if cls._SO_finishedClassCreation:
             makeProperties(cls)
+        
+        #DSM: Update each child class if needed and existing (only for new 
+        #DSM: dynamic join as no child classes exists at object creation)
+        for c in cls._childClasses.values():
+            c.addJoin(joinDef, childUpdate=True)
 
     addJoin = classmethod(addJoin)
 
@@ -641,7 +735,11 @@
                 delattr(cls, 'add' + join.addRemovePrefix)
 
         if cls._SO_finishedClassCreation:
-            unmakeProperties(cls)
+            delattr(cls, meth)
+        
+        #DSM: Update each child class if needed and delete properties for this join
+        for c in cls._childClasses.values():
+            delattr(c, meth)
 
     delJoin = classmethod(delJoin)
 
@@ -835,6 +933,20 @@
         else:
             id = None
 
+        #DSM: If we were called by a children class, we must retreive the properties dictionnary.
+        #DSM: Note: we can't use the ** call paremeter directly as we must be able to delete items from
+        #DSM: the dictionary (and our children must know that the items were removed!)
+        fromChild = False
+        if kw.has_key('kw'):
+            kw = kw['kw']
+            fromChild = True
+        #DSM: If we are the children of an inheritable class, we must first create our parent
+        if cls._parentClass:
+            #kw['childName'] = inst._className
+            inst._parent = cls._parentClass.new(kw=kw) 
+            inst._parent.childName = inst._className
+            id = inst._parent.id
+
         # First we do a little fix-up on the keywords we were
         # passed:
         for column in inst._SO_columns:
@@ -864,7 +976,10 @@
         for name, value in kw.items():
             if name in inst._SO_plainSetters:
                 forDB[name] = value
-            else:
+                #DSM: If this is a call from the child, we must remove the parameter for the database
+                if fromChild: del kw[name]
+            elif not fromChild:
+                #DSM: Only use other items if this isn't a call from the child
                 others[name] = value
 
         # We take all the straight-to-DB values and use set() to
@@ -1008,6 +1123,8 @@
     clearTable = classmethod(clearTable)
 
     def destroySelf(self):
+        #DSM: If this object has parents, recursivly kill them
+        if hasattr(self, '_parent') and self._parent: self._parent.destroySelf()
         # Kills this object.  Kills it dead!
         self._SO_obsolete = True
         self._connection._SO_delete(self)
@@ -1066,6 +1183,33 @@
         self.clause = clause
         tablesDict = SQLBuilder.tablesUsedDict(self.clause)
         tablesDict[sourceClass._table] = 1
+        #DSM: if this class has a parent, we need to link it and be sure the parent is in the table list
+        #DSM: the following code is before clauseTables because if the user uses clauseTables (and normal string SELECT), he
+        #DSM: must know what he wants and will do himself the relationship between classes.
+        if type(self.clause) is not str:
+            tableRegistry = {}
+            for registryClass in classRegistry[None].values():
+                if registryClass._table in tablesDict:
+                    #DSM: By default, no parent are needed for the clauses
+                    tableRegistry[registryClass] = registryClass
+                    currentClass = registryClass
+                    while currentClass._parentClass:
+                        currentClass = currentClass._parentClass
+                        if tableRegistry.has_key(currentClass):
+                            #DSM: Must keep the last parent needed (to limit the number of join needed)
+                            tableRegistry[registryClass] = currentClass
+                            #DSM: Remove this class as it is a parent one of a needed children
+                            del tableRegistry[currentClass]
+            #DSM: Table registry contains only the last children or standalone classes
+            parentClause = []
+            for (currentClass, minParentClass) in tableRegistry.items():
+                while currentClass != minParentClass and currentClass._parentClass:
+                    parentClass = currentClass._parentClass
+                    parentClause.append(currentClass.q.id == parentClass.q.id)
+                    currentClass = parentClass
+                    tablesDict[currentClass._table] = 1
+            self.clause = reduce(SQLBuilder.AND, parentClause, clause)
+
         if clauseTables:
             for table in clauseTables:
                 tablesDict[table] = 1

[SQLObject] Using fulltext indexes on Postgres

[Frank B. <fb...@fo...>]

Hallo,

I'd like to use a full text index on some of my SQLObject tables in
PostgreSQL to speed things up a bit.

I considered two alternatives. One is the fti module. fti requires to
use queries like the following which access a second table carrying
the fulltext index(es): 

 select a.id from normal_fti f1, normal a where f1.string ~ '^clone'
 and a.oid = f1.id ;

"normal a" here is the table managed by SQLObject, "normal_fti" has
the fulltext index and looks like this: 
 
 CREATE TABLE normal_fti (
  string varchar(200),
  id oid
 );

Another approach is the GiST-based tsearch module (I cannot use
tsearch2 because I'm stuck with PSQL 7.1 for now) from:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/

tsearch has a nicer query layout, but needs a new column inside the
SQLObject table of type "txtidx". 

Given an index column called "titleidx" this then gives queries like
this: 

 select * from normal where titleidx @@ 'something';

This looks easier to incorporate into SQLObjects .select syntax to me,
but will it disturb my SQLObject classes? I am aware, that I might not
be able to use automatic table generation.

Now my question is: Say, that both are fast enough for me, what kind
of index would you use in a SQLObject application? And I'd love to
hear, if someone has any experiences with one of these or even some
example code, as I'm not sure yet, how to implement the queries? 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__