Hi everyone. I'm currently trying to migrate a legacy oracle database to web2py and on the past weeks I stumbled upon several issues related to how the database was originally designed and how web2py expects to interact with it.
Among them: * I need support for ref cursors * I need to write some complex sql queries that rely on oracle's advanced functions * A "bug" (??) on the web2py oracle's implementation (see http://tinyurl.com/kjs2pk) I wanted to be able to interact freely between DAL objects and ad hocs queries using web2py's ORM syntax. I came up with a subclassed version of SQLDB that so far helped me with this and I wanted to share it with everyone hoping somebody might find it useful. This might be redundant, soon-to-be-or-already-obsolete (I have high hopes on new DALs although I couldn't try it yet)or just a showcase of bad programming but since I already spent a few hours with it, I thought I can share :) Put it simple, what I did was to create a decorator that, applied to a method that returns a list of tuples, will convert the result to a SQLRows object mapped to a "virtual" table. This "virtual" table (one for manual queries, one for ref cursors) will hold the results until a new ad-hoc query is executed. Of course you cannot use them to update, delete or insert any records in the db, but if you have a special query and you don't want to loose DAL's syntax when iteracting with it, this might help you. People using a database other than oracle can make it work with minimal patching. Following, a full example of a refcursor (in case you don't know what it is), an ad-hoc query example. Finally, the proper code to make it work. --- models/db.py db.define_table("Food", db.Field("name", type='string', unique=True), db.Field("fav_food", type='string')) db.Food.insert(name='Mariano', fav_food='spaghettini carbonara') db.Food.insert(name='Gregorio', fav_food='hot dogs') db.Food.insert(name='Justina', fav_food='hot dogs') db.commit() --- store procedure returning a ref cursor: create procedure test_food (food_name in food.fav_food%type, rc_out out sys_refcursor) is begin open rc_out for select * from food where fav_food=food_name; end test_food; / -- in action: KAL> variable rc refcursor KAL> begin test_food('hot dogs', :rc); end; 2 / PL/SQL procedure successfully completed. KAL> print rc 2 Gregorio hot dogs 3 Justina hot dogs -- accesing it from web2py itself (ipython) and getting SQLRows object: In [8]: rows = db.executerc("test_food", ['hot dogs']) In [9]: for row in rows: print(row.NAME, row.FAV_FOOD) ....: ('Gregorio', 'hot dogs') ('Justina', 'hot dogs') -- executing ad hoc queries with executesql and getting SQLRows object: In [10]: rows = db.executesql("Select name, fav_food from food where name like 'Mar%'") In [11]: for row in rows: print row ...: <SQLStorage {'FAV_FOOD': 'spaghettini carbonara', 'NAME': 'Mariano'}> In [12]: type(rows) Out[12]: <class 'gluon.sql.SQLRows'> -- my modified db.py from gluon.sql import SQLDB, SQLRows # not sure why I imported these but here they are from cx_Oracle import BFILE, BINARY, BLOB, CLOB, DATETIME, FIXED_CHAR, LOB from cx_Oracle import LONG_BINARY, LONG_STRING, NATIVE_FLOAT, NCLOB, NUMBER from cx_Oracle import STRING, TIMESTAMP # this is the inverse version of SQL_DIALECTS in gluon/sql.py # we're mapping oracle's datatypes to web2py's oracle_dialect = dict( NUMBER='integer', STRING='string', DATETIME='datetime', BLOB='blob', CLOB='text', FIXED_CHAR='boolean', NATIVE_FLOAT='double', TIMESTAMP='datetime') class mySQLDB(SQLDB): def __init__(self, uri='sqlite://dummy.db', pool_size=0, pools=0): super(mySQLDB, self).__init__(uri, pool_size, pools) def buildSQLRow(fnc): """Decorator to return a SQLRows object. It will receive the result of the query, transform the db native datatypes into ones understood by DAL and return the result""" def wrapper(self, *args): rt1, rt2, rt3 = fnc(self, *args) pargs = [] try: self.pop(rt3) self['tables'].remove(rt3) except KeyError: pass for ev in rt2: pargs.append(SQLField(ev[0], oracle_dialect[ev[1].__name__])) self.define_table(rt3, *tuple(pargs), **dict(migrate=False)) return SQLRows(self, rt1, *map(lambda x: "%s.%s" % (rt3, x[0]), rt2)) return wrapper @buildSQLRow def executesql(self, query, *args): """Execute a query and return the result. If you have two columns with the same name, make sure you create different aliases for them""" self['_lastsql'] = query self._cursor.execute(query, args) return self._cursor.fetchall(), self._cursor.description, 'manquery' @buildSQLRow def executerc(self, st_name, parameters): """Execute a procedure with a ref cursor as result. In the procedure, the ref cursor output parameter must be the last one parameter and the only one out value Parameters: st_name: the name of the [package.]procedure to execute parameters: a list of parameters to pass to the procedure. You don't need to pass a ref cursor variable as the method will append it by default""" rc = self._connection.cursor() parameters.append(rc) self._cursor.callproc(st_name, parameters) return rc.fetchall(), rc.description, 'refcursor' db = mySQLDB('oracle://kal/aldos...@myora') # web2py models stuff... --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
