sqlobject-discuss — All things SQLObject-related

[SQLObject] Re: SQLObject / Oracle

[Dirk E. <D....@ex...>]

Hi,

I just hacked together the basic columnsFromSchema implementation for Oracle.
I'll post it here for John. Can't help it, I'm sitting behind a firewall.
I tried out the SQL statement behind it, but not the python code itself,
so there might be some stupid mistakes/typos in there...

The MySQL version of columnsFromSchema and guessClass is much easier to understand
than the PostgreSQL version.

Regards
Dirk

>>> snip

    def columnsFromSchema(self, tableName, soClass):
        # DE: Oracle is nice to us here, for a change!
        colData = self.queryAll("SELECT column_name, data_type, data_length, data_precision, data_scale, nullable, data_default FROM user_tab_columns WHERE table_name = UPPER('%s') ORDER BY column_id"
                                % tableName)
        results = []
        for field, t, dataLength, dataPrec, dataScale, nullAllowed, default, in colData:
            if field == 'ID':
                continue
            
            colClass, kw = self.guessClass(t,dataLength,dataPrec,dataScale)
            kw['name'] = soClass._style.dbColumnToPythonAttr(field)
            if nullAllowed == 'Y':
                # DE: nullable is a 'Y', 'N' column
                kw['notNone'] = False
            else:
                kw['notNone'] = True
            kw['default'] = default
            # @@ skip key...
            # @@ skip extra...
            results.append(colClass(**kw))
        return results

    def guessClass(self, t, dataLength, dataPrec, dataScale):
        # DE: Oracle gave us all, so there's no need to guess
        col = None
        kw = {}
        if t.startswith('NUMBER'):
            # DE: INT is NUMBER in Oracle
            col = Col.IntCol
            if dataPrec:
                kw['precision'] = int(dataPrec)
            if dataScale:
                kw['scale'] = int(dataPrec)
        elif t.startswith('VARCHAR'):
            col = Col.StringCol
            kw['length'] =  int(dataLength)
            kw['varchar'] = TRUE
        elif t.startswith('CHAR'):
            col =  Col.StringCol
            kw['length'] =  int(dataLength)
            kw['varchar'] = False
        elif t.startswith('DATE'):
            col = Col.DateTimeCol
        elif t.startswith('bool'):
            # DE: Hmm, no good idea how to check for bool yet!
            # DE: Need to identify CONSTRAINT CHECK by name maybe...
            col = Col.BoolCol
        else:
            col =  Col.Col

        return col, kw

<<< snip

-- 
--------------------------------------------------------------------------
Dirk Evers                               Bioinformatics Research Scientist
Exelixis Deutschland                              Fon: +49 (0)7071-9655-16
Spemannstr. 35                                    Fax: +49 (0)7071-9655-96
D-72076 Tuebingen, Germany                   URL: http://www.exelixis.com/
--------------------------------------------------------------------------

[SQLObject] Re: SQLObject / Oracle

[Ian S. <ian...@et...>]

"John Dell'Aquila" <de...@ml...> wrote in message
news:blb1e5$p6t$1...@se......
> I don't fully understand the framework yet but I've managed to get all the
> column types working. I also implemented referential integrity constraints
> on ForeignKey columns since I want the tables to be safe for use from the
> database side as well. I also want to get columnsFromSchema implemented if
I
> can figure out what's going on from the Postgres implementation.

I'd be interested in the Referential integrity additions for porting to
Firebird - espcially if you have dealt with the cacheing issue (cascade
deletes removing child objects from the cache).

Re: [SQLObject] SQLObject / Oracle

[Andy T. <an...@ha...>]

Dirk Evers wrote:

> Hi,
> 
> I also started to work on an Oracle version of SQLObject a few days ago,
> also using cx_Oracle.
> 
> Problems I encountered are the slice notation
> access to tables, because Oracle does not have anything like 
> LIMIT and OFFSET. Workarounds need a more complex SQL statement with
> the original statement included as a sub-select. Not very nice!
> This makes it necessary to overload functions at a higher level in SQLObject
> -- i.e. queryForSelect -- because the added statement is not just an added suffix.
> (Haven't implemented this yet.)
> Moreover, Oracle does not know anything like AUTOINCREMENT for the PRIMARY KEY
> statement, which makes things a tad more complicated. I used sequences to
> implement this. I was just about to start on the columnsFromSchema functions
> when I read your email. :-)
> 
> John, let's compare our code changes to SQLObject and take it from there.
> I would be very happy to see an Oracle version of SQLObject come to production
> very fast!
> 
> Regards
> Dirk Evers
> 
> 
> On Mon, Sep 29, 2003 at 11:41:58PM -0500, John Dell'Aquila wrote:
> 
>>I just discovered SQLObject and was excited to kick the tires. Unfortunately
>>I don't use any of the supported databases, so I hacked together an Oracle
>>connection using cx_Oracle.
>>
>>I don't fully understand the framework yet but I've managed to get all the
>>column types working. I also implemented referential integrity constraints
>>on ForeignKey columns since I want the tables to be safe for use from the
>>database side as well. I also want to get columnsFromSchema implemented if I
>>can figure out what's going on from the Postgres implementation.
>>
>>So far SQLObject looks very cool. The metaclass implementation definitely
>>gives me the best "feel" of all the ORMs I've looked at -- very Pythonic.
>>
>>Is any one else workng on an Oracle implementation? Are there any plans to
>>refactor the hooks for adding new backends?
>>
>>- John Dell'Aquila
>>

Whilst I don't have much time to help develop the code I'd be more than 
happy to help you test it. Mail me off list if/when you need a guinea pig.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

Re: [SQLObject] SQLObject / Oracle

[Dirk E. <D....@ex...>]

Hi,

I also started to work on an Oracle version of SQLObject a few days ago,
also using cx_Oracle.

Problems I encountered are the slice notation
access to tables, because Oracle does not have anything like 
LIMIT and OFFSET. Workarounds need a more complex SQL statement with
the original statement included as a sub-select. Not very nice!
This makes it necessary to overload functions at a higher level in SQLObject
-- i.e. queryForSelect -- because the added statement is not just an added suffix.
(Haven't implemented this yet.)
Moreover, Oracle does not know anything like AUTOINCREMENT for the PRIMARY KEY
statement, which makes things a tad more complicated. I used sequences to
implement this. I was just about to start on the columnsFromSchema functions
when I read your email. :-)

John, let's compare our code changes to SQLObject and take it from there.
I would be very happy to see an Oracle version of SQLObject come to production
very fast!

Regards
Dirk Evers


On Mon, Sep 29, 2003 at 11:41:58PM -0500, John Dell'Aquila wrote:
> I just discovered SQLObject and was excited to kick the tires. Unfortunately
> I don't use any of the supported databases, so I hacked together an Oracle
> connection using cx_Oracle.
> 
> I don't fully understand the framework yet but I've managed to get all the
> column types working. I also implemented referential integrity constraints
> on ForeignKey columns since I want the tables to be safe for use from the
> database side as well. I also want to get columnsFromSchema implemented if I
> can figure out what's going on from the Postgres implementation.
> 
> So far SQLObject looks very cool. The metaclass implementation definitely
> gives me the best "feel" of all the ORMs I've looked at -- very Pythonic.
> 
> Is any one else workng on an Oracle implementation? Are there any plans to
> refactor the hooks for adding new backends?
> 
> - John Dell'Aquila
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

-- 
--------------------------------------------------------------------------
Dirk Evers                               Bioinformatics Research Scientist
Exelixis Deutschland                              Fon: +49 (0)7071-9655-16
Spemannstr. 35                                    Fax: +49 (0)7071-9655-96
D-72076 Tuebingen, Germany                   URL: http://www.exelixis.com/
--------------------------------------------------------------------------

[SQLObject] SQLObject / Oracle

[John Dell'A. <de...@ml...>]

I just discovered SQLObject and was excited to kick the tires. Unfortunately
I don't use any of the supported databases, so I hacked together an Oracle
connection using cx_Oracle.

I don't fully understand the framework yet but I've managed to get all the
column types working. I also implemented referential integrity constraints
on ForeignKey columns since I want the tables to be safe for use from the
database side as well. I also want to get columnsFromSchema implemented if I
can figure out what's going on from the Postgres implementation.

So far SQLObject looks very cool. The metaclass implementation definitely
gives me the best "feel" of all the ORMs I've looked at -- very Pythonic.

Is any one else workng on an Oracle implementation? Are there any plans to
refactor the hooks for adding new backends?

- John Dell'Aquila

Re: [SQLObject] Re: SQLObject / FireBird / WebWare

[Randall R. <ra...@ra...>]

On Monday, September 29, 2003, at 09:46 AM, Ian Sparks wrote:

> "Randall Randall" <ra...@ra...> wrote in message
> news:B6C...@ra......
> I'm guessing that something like :
>
> #mypage.py:
> from WebKit.Page import Page
> import MySOClasses

This is what I did, yes, though it was actually
from SiteSQL import *
since I have lots of SO objects in different files.

> class mypage(Page):
>   ...
>   def writeContent...
>     test = MySoClasses.MyObject(1)
>     test.name = 'Fred'
>
> I'm assuming that if I have another servlet that has similar code that 
> the
> two servlets share :
>
> 1. A database connection.
> 2. The same SQLObject cache manager
>
> Overall my assumption is that in MySOClasses.py I define a database
> connection and the SQLObject classes I want to use and then I'm free 
> to use
> them in any servlet just by doing an "import MySOClasses" and that any
> threading and cache management issues are handled for me?

That's been my assumption, and so far has been working. :)

If you expect to have issues with threading and flush caching,
then you'd want to use transactions, but currently I just restart
the appserver for major changes (it's not a financial site, just
articles and such).

I have a DBObject.py , which all my SQLObjects inherit from:
------------------------
from SQLObject import *

conn = PostgresConnection("user=myuser dbname=mydb password=mypass")
#trans = conn.transaction()

class DBObject(SQLObject):
   '''Base SQLObject for building database objects'''

   #_cacheValues = False
   _connection = conn
--------------------------
but which doesn't define any columns or anything, since those
wouldn't be inherited, per Ian's discussions on subclassing SO.
I *believe* that using transactions would be as simple as
an uncomment and beginning to use trans.commit() .  This might
not be the case.

It's also the case that I don't call any SOs in any __init__,
and don't leave them hanging around.  Some are called in awake,
and some in buildTemplate, etc (I use tplCompiler as well), so
it seems to me that I'm not getting any caching at all, due to
lack of reuse during development. However, I believe that having
all my SQLObjects inherit from DBObject, above, does give me
pooling during a request.

If I wrong, I hope someone corrects me as I spread disinformation. :)

-- 
Randall Randall <ra...@ra...>
"When you advocate any government action, you must first
believe that violence is the best answer to the question
at hand." -- Allen Thornton

[SQLObject] Re: SQLObject / FireBird / WebWare

[Ian S. <ian...@et...>]

"Randall Randall" <ra...@ra...> wrote in message
news:B6C...@ra......

> If you can be more specific about something you'd like
> to see an example of, I can just pull out some of my
> current code (assuming I'm doing whatever you want to
> see).

I'm guessing that something like :

#mypage.py:
from WebKit.Page import Page
import MySOClasses

class mypage(Page):
  ...
  def writeContent...
    test = MySoClasses.MyObject(1)
    test.name = 'Fred'

I'm assuming that if I have another servlet that has similar code that the
two servlets share :

1. A database connection.
2. The same SQLObject cache manager

Overall my assumption is that in MySOClasses.py I define a database
connection and the SQLObject classes I want to use and then I'm free to use
them in any servlet just by doing an "import MySOClasses" and that any
threading and cache management issues are handled for me?

Thanks.

Re: [SQLObject] Re: Know bugs in Firebird

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

Quoting Ian Bicking <ia...@co...>:

> It's probably easier if we just start quoting all the columns.  That's
> pretty easy with automatic SQL generation anyway.  Postgres uses
> "column", MySQL `column`, what does Firebird use?  (SQLite?)

Just a caution, in PostgreSQL quoting column/table names makes the name
case-sensitive. So "Users" doesn't match dbname users but Users does. :) Not a
big issue, although it may break some existing schema (including ones created
by SQLObject with a not-all-lowercase db style).

> That doesn't fix the 31 character limit.  Raising an exception would be
> best in that case.  It confuses me terribly when I hit that limit (I
> think Postgres has the same limit) and names get truncated.

31 chars in Postgres 7.2, 63 chars in 7.3+.

I believe it's a silent truncation though, so SO will need to be aware of these
things beforehand to make an exception, and also keep in mind that the limit
is actually less when you consider that automatic sequence and constraint
names need to fit in the limit (tablename_colname_seq is the default sequence
name template).

Personally I'm not sure SQLObject needs to deal with all these cases, they're
just part of the responsibility of the developer in choosing a database
platform. But it would be reasonable to make a db-specific faq, detailing
these things to watch out for that SO does behind the scenes.

- Luke

Re: [SQLObject] MySQL and InnoDB

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

Sorry.

No, there's no way to specify InnoDB to SQLObject.  Maybe that can fit 
in somewhere, but it's not there now.  However, it is very easy to 
write the create statement yourself -- SQLObject doesn't make any 
particular requirements on your schema.  Just be sure to use "id INT 
PRIMARY KEY AUTO_INCREMENT" for your id, and the rest should just be 
like a normal create statement.

On Sunday, September 28, 2003, at 04:15 PM, Lawrence Oluyede wrote:
> Nobody knows how can I do what I asked in my previous message?
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] MySQL and InnoDB

[Lawrence O. <l.o...@vi...>]

Nobody knows how can I do what I asked in my previous message?

Re: [SQLObject] Re: Know bugs in Firebird

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

On Saturday, September 27, 2003, at 09:41 PM, Ian Sparks wrote:
>> * NamesTest doesn't create the table correctly.  Is "names" a reserved
>> word?
>
> Yes.
>
> It occured to me in the few hours working with SQLObject that there 
> are a
> number of simple ways to break it. Two things that would help novices 
> would
> be a list of reserved words for each DB and a "__MaxIdentifierLength"
> property (31chars for Interbase as I recall, James?). Remedy could be
> failure or name-mangling. Whatever, some warning would be nice.
>
> The complete list of keywords can be found in the IB 6 manuals 
> starting at
> page 184 Section "Keywords". Firebird has added some more but I'm not 
> sure
> this is well documented. If you decide you want to add reserved word 
> support
> just say how you want this formatted and I'll contribute it.

It's probably easier if we just start quoting all the columns.  That's 
pretty easy with automatic SQL generation anyway.  Postgres uses 
"column", MySQL `column`, what does Firebird use?  (SQLite?)

That doesn't fix the 31 character limit.  Raising an exception would be 
best in that case.  It confuses me terribly when I hit that limit (I 
think Postgres has the same limit) and names get truncated.

   Ian

Re: [SQLObject] Know bugs in Firebird

[James R. <jcr...@ya...>]

--- Ian Bicking <ia...@co...> wrote:
> I sent this to James, but then I though Ian or
> someone else might want 
> to look at these.

I got it, thanks.
 
> Currently these are the known issues.  James may
> have fixes for some of 
> these that I lost.
> 
> * kinterbasdb seems to rollback if you don't
> explicitly commit.  I 
> don't think there's any autocommit mode, which is
> how a lot of the 
> tests are meant to be run.  I've had this problem
> with SQLite as well,  
> I think.

I haven't run the test suite in the "mostly live"
version within the last two weeks.  But, when I
changed the supportTransaction attribute in
FirebirdConnection to true, but set the
SQLObjectTest.supportTransaction to false, many of the
test failures went away.  Go figure.

FB wants to put everything in a transaction (even a
select).

> Anyway, I think if _runWithConnection does a
> begin/commit, that should 
> simulate autocommit, but I was getting problems with
> that.  So now it 
> tries to begin/commit but ignores errors.  It's
> dumb.  Maybe you have a 
> better idea.

Because it's so transactional, my idea (which I don't
even like) is to add (some sort of) a
requiresTransaction logic to SO.

> * NamesTest doesn't create the table correctly.  Is
> "names" a reserved 
> word?

Yes. 

> * DeleteSelectTest gives an odd error, "Cursor
> unknown".  Not sure 
> what's up with that.

That particular error is new to the cvs version (it
worked fine in .4).  I think when the _iterSelect
added the 'yield' call, things went wierd. 

> 
> * ValidationText, I get a bunch of 'conversion error
> from string 
> "BLOB".'
That error (while cryptic) is caused by FB not doing a
implicit convert for an int value sent (by the
Validator).  If you change the 'default' value (kw arg
in test.py to '100' instead of just 100) that one
should go away also.

> * SliceTest -- I think you had some changes with
> this which I may have 
> missed in the files you sent me.

The changes were in DBConnection.py.  Since FB puts
the limit clause directly after the 'select'
statement, I had to change a couple of your calls (see
below):

 
    def queryForSelect(self, select):
        ops = select.ops
        cls = select.sourceClass
        if ops.get('lazyColumns', 0):
            q = "SELECT %s.%s FROM %s WHERE " % \
                (cls._table, cls._idName,
                 ", ".join(select.tables))
        else:
            q = "SELECT %s.%s, %s FROM %s WHERE " % \
                (cls._table, cls._idName,
                 ", ".join(["%s.%s" % (cls._table,
col.dbName)
                            for col in
cls._SO_columns]),
                 ", ".join(select.tables))
########### changes start###################          
      
        select.selectBegin = q

        return self.whereClauseForSelect(select)
############changes end######################




    def whereClauseForSelect(self, select, limit=1,
order=1):

        q = str(select.clause)
        ops = select.ops

        def clauseList(lst, desc=False):
            if type(lst) not in (type([]), type(())):
                lst = [lst]
            lst = [clauseQuote(i) for i in lst]
            if desc:
                lst = [SQLBuilder.DESC(i) for i in
lst]
            return ', '.join([SQLBuilder.sqlRepr(i)
for i in lst])

        def clauseQuote(s):
            if type(s) is type(""):
                if s.startswith('-'):
                    desc = True
                    s = s[1:]
                else:
                    desc = False
                assert SQLBuilder.sqlIdentifier(s),
"Strings in clauses are expected to be column
identifiers.  I got: %r" % s
                if
select.sourceClass._SO_columnDict.has_key(s):
                    s =
select.sourceClass._SO_columnDict[s].dbName
                if desc:
                    return
SQLBuilder.DESC(SQLBuilder.SQLConstant(s))
                else:
                    return SQLBuilder.SQLConstant(s)
            else:
                return s

        if order and ops.get('groupBy'):
            q = "%s GROUP BY %s" % (q,
clauseList(ops['groupBy']))
        if order and ops.get('dbOrderBy'):
            q = "%s ORDER BY %s" % (q,
clauseList(ops['dbOrderBy'], ops.get('reversed',
False)))

        start = ops.get('start', 0)
        end = ops.get('end', None)
#####################changes start#############       

        if hasattr(select, 'selectBegin'):
            q = ' '.join([select.selectBegin, q])
#####################changes end ##############
        if limit and (start or end):
            # @@: Raising an error might be an
annoyance, but some warning is
            # in order.
            #assert ops.get('orderBy'), "Getting a
slice of an unordered set is unpredictable!"
            q = self._queryAddLimitOffset(q, start,
end)

        return q


I will double check all of this against the latest
version.

James

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

Re: [SQLObject] Re: Know bugs in Firebird

[James R. <jcr...@ya...>]

--- Ian Sparks <ia...@et...> wrote:
> It occured to me in the few hours working with
> SQLObject that there are a
> number of simple ways to break it. Two things that
> would help novices would
> be a list of reserved words for each DB and a
> "__MaxIdentifierLength"
> property (31chars for Interbase as I recall,
> James?). Remedy could be
> failure or name-mangling. Whatever, some warning
> would be nice.
That is correct.  The RDB$RELATIONS table has
RDB$RELATION_NAME (the field where the field name is
stored)  defined as char(31).  I think this may have
changed in 1.5, though.


James

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

[SQLObject] Re: Know bugs in Firebird

[Ian S. <ia...@et...>]

"Ian Bicking" <ia...@co...> wrote in message
news:A6B...@co......
> I sent this to James, but then I though Ian or someone else might want
> to look at these.

Thanks.

> * NamesTest doesn't create the table correctly.  Is "names" a reserved
> word?

Yes.

It occured to me in the few hours working with SQLObject that there are a
number of simple ways to break it. Two things that would help novices would
be a list of reserved words for each DB and a "__MaxIdentifierLength"
property (31chars for Interbase as I recall, James?). Remedy could be
failure or name-mangling. Whatever, some warning would be nice.

The complete list of keywords can be found in the IB 6 manuals starting at
page 184 Section "Keywords". Firebird has added some more but I'm not sure
this is well documented. If you decide you want to add reserved word support
just say how you want this formatted and I'll contribute it.

- Ian Sparks.

Re: [SQLObject] Re: Re: FireBird / Interbase

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

On Saturday, September 27, 2003, at 05:25 PM, Ian Sparks wrote:
> "James Ralston" <jcr...@ya...> wrote in message
> news:200...@we......
>
>> "Mostly" is an important word here.  The initial cut
>> at ib/fb support was done against the .4 release of SO
>> instead of the cvs version <doh!>.  That has been
>> corrected but Ian (Bicking) has been buried (I hope
>
> It doesn't look like your changes are in the SF CVS - are you keeping 
> them
> somewhere else? Accessible? I'd rather not have to find all the known 
> issues
> myself.

That's my fault actually, I've been sitting on those changes for much 
too long.  I'm just at this minute testing some of the changes for 
Firebird, after finally having gotten it set up to do testing.  Runs 
tests very slowly... I imagine it must be slow at table creation and 
dropping, which the tests do a lot of.

Anyway, I'll post a followup when I have it applied and uploaded.

   Ian

[SQLObject] Know bugs in Firebird

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

I sent this to James, but then I though Ian or someone else might want 
to look at these.

Currently these are the known issues.  James may have fixes for some of 
these that I lost.

* kinterbasdb seems to rollback if you don't explicitly commit.  I 
don't think there's any autocommit mode, which is how a lot of the 
tests are meant to be run.  I've had this problem with SQLite as well,  
I think.

Anyway, I think if _runWithConnection does a begin/commit, that should 
simulate autocommit, but I was getting problems with that.  So now it 
tries to begin/commit but ignores errors.  It's dumb.  Maybe you have a 
better idea.

* NamesTest doesn't create the table correctly.  Is "names" a reserved 
word?

* DeleteSelectTest gives an odd error, "Cursor unknown".  Not sure 
what's up with that.

* ValidationText, I get a bunch of 'conversion error from string 
"BLOB".'

* SliceTest -- I think you had some changes with this which I may have 
missed in the files you sent me.

Then, like SQLite, the string quoting has to be fixed.  But I'll have 
to think about that some.

   Ian

[SQLObject] Firebird fixes

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

Some Firebird fixes have been applied.  There are still outstanding 
issues, but at least this is a better starting point.  Or maybe I'm 
just at a better starting point.

Anyway, like usual CVS is slow (SourceForge says this will be fixed 
soon), so you can get the updates from 
http://colorstudy.com/ianb/SQLObject-cvs.tar.bz2

   Ian

[SQLObject] Re: Re: FireBird / Interbase

[Ian S. <ia...@et...>]

"James Ralston" <jcr...@ya...> wrote in message
news:200...@we......

> "Mostly" is an important word here.  The initial cut
> at ib/fb support was done against the .4 release of SO
> instead of the cvs version <doh!>.  That has been
> corrected but Ian (Bicking) has been buried (I hope

It doesn't look like your changes are in the SF CVS - are you keeping them
somewhere else? Accessible? I'd rather not have to find all the known issues
myself.

> Nice feature.  Although, in my experience, most db
> optimizations are done after the fact...when the
> "great dba in the sky" does his cypherin' (wink to
> Jethro from the Beverly Hillbillies) on which fields
> will most greatly benefit from index additions.

True, but once that has happened you really want to feed that information
back into the model. At least I do.

I don't know of a way to extract the Query Plan from kinterbasdb but if this
exists then debug mode could extract the plan, do a count of NATURAL and
give you a list of queries that require optimization. This at least makes
the DBA's job easier. Most of us can't afford to have a truly great DBA on
staff but with the help of PLAN we can add indexes to remove all the natural
joins and at least make them much more efficient.

Thanks James, Firebird support is already usable. If I can help in any way
to improve the FB support I'd be glad to.

- Ian Sparks.

Fwd: Re: [SQLObject] FireBird / Interbase

[James R. <jcr...@ya...>]

Oops...perhaps it would be helpful to actually include
my response :-)

 
--- Ian Sparks <ia...@et...> wrote:
> I've been playing with the people.py example under
> Firebird 1.0 (and IB 6.01
> with some tweaks to SO) using the Mostly live CVS
> tarball from 2 days ago.
 
"Mostly" is an important word here.  The initial cut
at ib/fb support was done against the .4 release of SO
instead of the cvs version <doh!>.  That has been
corrected but Ian (Bicking) has been buried (I hope
I can speak for him here) with other (real?) work,
changes and patches.  And, as any (good) steward of an
OS project, he'd prefer to review changes before
dumping them in.

With that in mind, see my responses below.

> Observations :
> 
> * people.py defines a SQLObject class Role. ROLE is
> an IB/Firebird reserved
> word. Changing the role references to userRole
> worked for me.

True.  I also wrestle with the test suite for similar
reasons.  Name and names are reserved words in fb, but
are used frequently as field and class/table names in
test.py

> * I'm not sure the supportTransactions setting is
> right for Firebird :
> 
> class FirebirdConnection(DBAPI):
>     supportTransactions = False
> 
> should be :
> 
> class FirebirdConnection(DBAPI):
>     supportTransactions = True
> 
> at least, it does for me if I want any commits to
> occur.

Right again.  And I would add that one should use the
explicit Transaction if using pooled connections in a
threaded application.  If not, you risk facing the
infamous "phantom record" when you add or change a
record. BTW, supportTransation wasn't in .4 ;-)

If someone can point me in the right direction for
turning on 'auto-commit' for kinterbasdb, I'd be
eternally greatful.  The setAutoCommit (required when
supportTransations = True) in the patched version does
nothing.
 
> * The current Firebird support doesn't have anything
> for Enum fields which are part of the people.py
tests. Here is something
> that will at least work :
> 
> Col.py :
> 
> class SOEnumCol(SOCol):
>     def _firebirdType(self):
>         length = max(map(len, self.enumValues))
>         return "VARCHAR(%i)" % (length,)
> 
> In general the postgresql approach to adding check
> constraints isn't going
> to work for Firebird. It appears you can't do :
> 
> CREATE TABLE FRED (
>   COL1 VARCHAR(10) CHECK (VALUE IN
> ('one','two','three')) NOT NULL
> )

There are 2 problems here:
 - the placement of the "NOT NULL" clause.  FB is VERY
picky about the order of constaints in a create table
statement.  
 - the referenced column in the check constraint must
be explicitly given.

This should work (which is what the SOEnumCol
generates for postgres...the pending version of Col.py
simply calls self._postgresType()):

   CREATE TABLE FRED (
     COL1 VARCHAR(10) NOT NULL CHECK (COL1 IN ('one',
'two', 'three'))   
  ) 

 
 as does this:
 
 alter table fred add check (col1 in
('one','two','three'));

> * It might be useful to allow additional Indexes to
> be created for a table.
> So :

<snip>
 
Nice feature.  Although, in my experience, most db
optimizations are done after the fact...when the
"great dba in the sky" does his cypherin' (wink to
Jethro from the Beverly Hillbillies) on which fields
will most greatly benefit from index additions. 

James


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

Re: [SQLObject] FireBird / Interbase

[James R. <jcr...@ya...>]

--- Ian Sparks <ia...@et...> wrote:
> I've been playing with the people.py example under
> Firebird 1.0 (and IB 6.01
> with some tweaks to SO) using the Mostly live CVS
> tarball from 2 days ago.
> 
> Observations :
> 
> * people.py defines a SQLObject class Role. ROLE is
> an IB/Firebird reserved
> word. Changing the role references to userRole
> worked for me.
> 
> * I'm not sure the supportTransactions setting is
> right for Firebird :
> 
> class FirebirdConnection(DBAPI):
>     supportTransactions = False
> 
> should be :
> 
> class FirebirdConnection(DBAPI):
>     supportTransactions = True
> 
> at least, it does for me if I want any commits to
> occur.
> 
> * The current Firebird support doesn't have anything
> for Enum fields which
> are part of the people.py tests. Here is something
> that will at least work :
> 
> Col.py :
> 
> class SOEnumCol(SOCol):
>     def _firebirdType(self):
>         length = max(map(len, self.enumValues))
>         return "VARCHAR(%i)" % (length,)
> 
> In general the postgresql approach to adding check
> constraints isn't going
> to work for Firebird. It appears you can't do :
> 
> CREATE TABLE FRED (
>   COL1 VARCHAR(10) CHECK (VALUE IN
> ('one','two','three')) NOT NULL
> )
> 
> you have to do :
> 
> CREATE DOMAIN FRED_COL1_DOM VARCHAR(1) CHECK (VALUE
> IN
> ('one','two','three'));
> 
> CREATE TABLE FRED (
>   COL1  FRED_COL1_DOM NOT NULL
>  )
> 
> which means some up-front shenanigins to write the
> domains you need before
> creating the columns. Not too hard I'm sure but I'm
> not much of a SQLObject
> hacker (yet?).
> 
> * It might be useful to allow additional Indexes to
> be created for a table.
> So :
> 
> class Person(SQLObject):
> 
>     _columns = [StringCol('username', length=20,
> alternateID=True,
>                           notNull=1),
>                 StringCol('firstName', length=30,
>                           notNull=1),
>                 StringCol('middleInitial', length=1,
> default=None),
>                 StringCol('lastName', length=50,
> notNull=1)]
>     _joins = [RelatedJoin('UserRole'),
> MultipleJoin('PhoneNumber')]
> 
>     idx1 = UniqueIndex(['firstName','lastName'])
>     idx2 = Index(['lastName'])
> 
>     _indexes = [idx1,idx2]
> 
> would lead to :
> 
> CREATE UNIQUE INDEX firstNameLastName ON PERSON
> (first_Name,last_Name)
> CREATE INDEX last_name ON PERSON (last_name)
> 
> Only if the db supported indexing of course.
> 
> Hope this is useful feedback.
> 
> - Ian Sparks.
> 
> 
> 
> 
> 
>
-------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
>
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

[SQLObject] MySQL and InnoDB

[Lawrence O. <l.o...@vi...>]

I have a simple (maybe) problem:

how could I tell SQLObject to generate InnoDB tables (with TYPE=INNODB at
the end of the table creation statement) instead of MyISAM? I want to use
foreign keys on my tables..

...the other option is to create manually the tables but I don't get how 
to map
SQLObject classes on existing tables.

Thanks in advance

-- 
Lawrence "Rhymes" Oluyede
http://loluyede.blogspot.com
rh...@NO...

[SQLObject] FireBird / Interbase

[Ian S. <ia...@et...>]

I've been playing with the people.py example under Firebird 1.0 (and IB 6.01
with some tweaks to SO) using the Mostly live CVS tarball from 2 days ago.

Observations :

* people.py defines a SQLObject class Role. ROLE is an IB/Firebird reserved
word. Changing the role references to userRole worked for me.

* I'm not sure the supportTransactions setting is right for Firebird :

class FirebirdConnection(DBAPI):
    supportTransactions = False

should be :

class FirebirdConnection(DBAPI):
    supportTransactions = True

at least, it does for me if I want any commits to occur.

* The current Firebird support doesn't have anything for Enum fields which
are part of the people.py tests. Here is something that will at least work :

Col.py :

class SOEnumCol(SOCol):
    def _firebirdType(self):
        length = max(map(len, self.enumValues))
        return "VARCHAR(%i)" % (length,)

In general the postgresql approach to adding check constraints isn't going
to work for Firebird. It appears you can't do :

CREATE TABLE FRED (
  COL1 VARCHAR(10) CHECK (VALUE IN ('one','two','three')) NOT NULL
)

you have to do :

CREATE DOMAIN FRED_COL1_DOM VARCHAR(1) CHECK (VALUE IN
('one','two','three'));

CREATE TABLE FRED (
  COL1  FRED_COL1_DOM NOT NULL
 )

which means some up-front shenanigins to write the domains you need before
creating the columns. Not too hard I'm sure but I'm not much of a SQLObject
hacker (yet?).

* It might be useful to allow additional Indexes to be created for a table.
So :

class Person(SQLObject):

    _columns = [StringCol('username', length=20, alternateID=True,
                          notNull=1),
                StringCol('firstName', length=30,
                          notNull=1),
                StringCol('middleInitial', length=1, default=None),
                StringCol('lastName', length=50, notNull=1)]
    _joins = [RelatedJoin('UserRole'), MultipleJoin('PhoneNumber')]

    idx1 = UniqueIndex(['firstName','lastName'])
    idx2 = Index(['lastName'])

    _indexes = [idx1,idx2]

would lead to :

CREATE UNIQUE INDEX firstNameLastName ON PERSON (first_Name,last_Name)
CREATE INDEX last_name ON PERSON (last_name)

Only if the db supported indexing of course.

Hope this is useful feedback.

- Ian Sparks.

Re: [SQLObject] Boolean

[Sidnei da S. <si...@pl...>]

On Fri, Sep 26, 2003 at 02:13:48AM -0500, Ian Bicking wrote:
| On Thursday, September 25, 2003, at 05:03 PM, Sidnei da Silva wrote:
| >I've just checked in a BoolCol, and later I've figured out that it
| >only works reliably on python 2.3. When I run the tests on python 2.2
| >I get 16 errors on my box, but none on 2.3. Can anyone confirm which
| >one is the target version?
| 
| They both are supported.  2.2 is the "gold" Python release, i.e., the 
| new lowest common denominator.  Of course, 2.3 is the "best" Python 
| release, i.e., if you can you should use it.

Is there any reason then for the failures I've seen on 2.2? I can post
the errors here.

| In this case the issue was that type(True) == int for 2.2, but not for 
| 2.3.  That's easy enough to fix -- basically you don't need 
| BoolConverter in 2.2, so you don't install it in that case.  BoolCol 
| has to be a bit more sophisticated than using a converter.  I've added 
| a validator that will do the trick.

Amazing! Thanks!

| Unfortunately this has raised a new problem.  Booleans are represented 
| differently depending on what database you are using.  The obvious 
| choice for booleans in MySQL is TINYINT.  In Postgres BOOLEAN.  MySQL 
| thus likes 0 and 1.  Postgres likes 't' and 'f' or other things, but 
| not integers.  Annoying.  Okay, we could make boolean columns into 
| ENUM('t', 'f') in MySQL, but I'm afraid that's dumb.  Though not 
| actually any less efficient, probably.  I don't know.

I think that ENUM is fine for MySQL. I had misread the manual and
understood that there was already support for BOOL, but now I see I
was wrong.

| But really I need to bite the bullet and put in database compatibility 
| code into SQLBuilder and Converters.
| 
| I've committed some changes to Converters and Col that fix this, and a 
| number of other fixes to validation in general.  For now MySQL does 
| BoolCol with the ENUM.

Great! Thank you very much for this.

-- 
Sidnei da Silva <si...@pl...>
dreamcatching :: making your dreams come true
http://dreamcatcher.homeunix.org

Sendmail may be safely run set-user-id to root.
		-- Eric Allman, "Sendmail Installation Guide"

Re: [SQLObject] releaseConnection and Transactions

[Sidnei da S. <si...@pl...>]

On Fri, Sep 26, 2003 at 01:03:23AM -0500, Ian Bicking wrote:
| Looking at that again, I don't know that the code is really correct.  
| The difficulty is that objects should know when they are rolled
| back.  
| I know ZODB handles this as well; I'm not as clear about how that fits 
| into SQLObject.  Specifically, Transaction.rollback expires all the 
| instances so that invalid cached values are removed.

Humm.. So thats why it needs to know when its rolled back. I haven't
really really looked under the hood to see if its Connection.rollback
or Transaction.rollback which is being called by the Zope
machinery. But we can make it call Transaction.rollback if that's not
being done yet.

| So, I don't know if the current behavior is really right, but I don't 
| think removing it is quite right either.  Though it may be appropriate 
| to set supportTransactions to false, because in a Zope environment the 
| transactions are being handled by Zope, not by SQLObject.  Though Zope 
| is not always the final work on transactions either -- it's sometimes 
| necessary to commit or rollback transactions in the middle of a 
| request.  So that has to be factored in too.  So, in summary, I'm not 
| sure.

AFAICT, its not needed (nor desired) to commit or rollback a
transaction in the middle of a request, unless an exception occurs, in
which case you *must* rollback. Setting supportTransactions to false
has worked very smoothly for me. The SQLObject is being registered
with the Zope transaction manager, so when Zope calls
Transaction.commit() or Transaction.rollback(), the call is propagated
to SQLObject.

[]'s
-- 
Sidnei da Silva <si...@pl...>
dreamcatching :: making your dreams come true
http://dreamcatcher.homeunix.org

Computers don't actually think.
	You just think they think.
		(We think.)

Re: [SQLObject] Boolean

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

On Thursday, September 25, 2003, at 05:03 PM, Sidnei da Silva wrote:
> I've just checked in a BoolCol, and later I've figured out that it
> only works reliably on python 2.3. When I run the tests on python 2.2
> I get 16 errors on my box, but none on 2.3. Can anyone confirm which
> one is the target version?

They both are supported.  2.2 is the "gold" Python release, i.e., the 
new lowest common denominator.  Of course, 2.3 is the "best" Python 
release, i.e., if you can you should use it.

In this case the issue was that type(True) == int for 2.2, but not for 
2.3.  That's easy enough to fix -- basically you don't need 
BoolConverter in 2.2, so you don't install it in that case.  BoolCol 
has to be a bit more sophisticated than using a converter.  I've added 
a validator that will do the trick.

Unfortunately this has raised a new problem.  Booleans are represented 
differently depending on what database you are using.  The obvious 
choice for booleans in MySQL is TINYINT.  In Postgres BOOLEAN.  MySQL 
thus likes 0 and 1.  Postgres likes 't' and 'f' or other things, but 
not integers.  Annoying.  Okay, we could make boolean columns into 
ENUM('t', 'f') in MySQL, but I'm afraid that's dumb.  Though not 
actually any less efficient, probably.  I don't know.

But really I need to bite the bullet and put in database compatibility 
code into SQLBuilder and Converters.

I've committed some changes to Converters and Col that fix this, and a 
number of other fixes to validation in general.  For now MySQL does 
BoolCol with the ENUM.

   Ian