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