Thread: [SQLObject] SQLObject / Oracle
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: John Dell'A. <de...@ml...> - 2003-09-30 04:50:15
|
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 |
From: Dirk E. <D....@ex...> - 2003-09-30 09:04:58
|
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/ -------------------------------------------------------------------------- |
From: Andy T. <an...@ha...> - 2003-09-30 09:17:27
|
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/ |
From: Ian B. <ia...@co...> - 2003-10-01 02:27:07
|
On a more general note, I'm happy to add Oracle support, but as a backend it won't receive the support from me that the other databases get. I have all the currently supported databases installed on my machine, and regularly do regression tests (and it's not uncommon for me to break something for a single database). But I'm not going to be able to do that with Oracle. So it'll require someone (one of you, or someone) to take up maintenance at least for that specific backend, and I'd imagine that CVS will generally be less stable for that backend (though we should at least be able to sync everything up before a release). The same is true of Sybase, which I think Sidnei is working on some. (And I'm just hoping that no one wants to use SAP DB ;) But anyway, Oracle support would still be cool. On Tuesday, September 30, 2003, at 04:06 AM, 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.) Firebird required this as well. The methods are slightly different now, and might make it more workable. I'm surprised Oracle doesn't have anything to make it easier... but then it's weird the things you get used to without realizing they aren't universally supported. > 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. :-) Yes, that's what Firebird does, and what Postgres used to do. But you probably already figured that out. > 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! |
From: Ian S. <ian...@et...> - 2003-09-30 13:09:04
|
"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). |
From: Ian B. <ia...@co...> - 2003-10-01 02:15:48
|
On Tuesday, September 30, 2003, at 08:08 AM, Ian Sparks wrote: > "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). I assume John is talking about REFERENCES in the automatic table creation -- which would be a nice addition. Cascading deletes are... challenging. Right now I'd suggest just overloading destroySelf(), doing the cascading manually. Maybe there could also be a soft destroySelf -- like, remind the object it's dead (but the object doesn't have to delete itself from the database). Either way, you could do it by adding an attribute to Col/ForeignKey objects to control it, and then handle that in destroySelf. (And, while we're at it, all the foreign key stuff should be moved out of Col and into ForeignKey, though that would require a little refactoring to keep it as general as it is) Ian |
From: John Dell'A. <de...@ml...> - 2003-10-01 03:45:49
|
Ian is correct, I simply added REFERENCES to table creation. I purposely did not add an ON DELETE clause because I want a database error if any funny business happens. "Ian Bicking" <ia...@co...> wrote in message news:2AF...@co...... > On Tuesday, September 30, 2003, at 08:08 AM, Ian Sparks wrote: > > "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). > > I assume John is talking about REFERENCES in the automatic table > creation -- which would be a nice addition. Cascading deletes are... > challenging. > > Right now I'd suggest just overloading destroySelf(), doing the > cascading manually. Maybe there could also be a soft destroySelf -- > like, remind the object it's dead (but the object doesn't have to > delete itself from the database). Either way, you could do it by > adding an attribute to Col/ForeignKey objects to control it, and then > handle that in destroySelf. (And, while we're at it, all the foreign > key stuff should be moved out of Col and into ForeignKey, though that > would require a little refactoring to keep it as general as it is) > > Ian > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf |
From: Dirk E. <D....@ex...> - 2003-09-30 14:18:03
|
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/ -------------------------------------------------------------------------- |
From: John Dell'A. <de...@ml...> - 2003-10-01 03:01:40
|
Very nice, and only one typo (TRUE). I had to query against all_tables though because my tables weren't in all_user_tables. Thank you! - John "Dirk Evers" <D....@ex...> wrote in message news:200...@ag...... > 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/ > -------------------------------------------------------------------------- > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf |
From: John Dell'A. <de...@ml...> - 2003-10-01 03:33:25
|
Correction: all_tab_columns instead of user_tab_columns "John Dell'Aquila" <de...@ml...> wrote in message news:bldg27$5ro$1...@se...... > Very nice, and only one typo (TRUE). I had to query against all_tables > though because my tables weren't in all_user_tables. > > Thank you! > > - John > > "Dirk Evers" <D....@ex...> wrote in message > news:200...@ag...... > > 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/ > > -------------------------------------------------------------------------- > > > > > > ------------------------------------------------------- > > This sf.net email is sponsored by:ThinkGeek > > Welcome to geek heaven. > > http://thinkgeek.com/sf > > > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf |