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