really? mutt + imap + gmail: a recipe for desaster!! :)
2009/6/13 mdipierro <mdipie...@cs.depaul.edu> > > the you email it to me as an attachament . the indentation is messed > up and hard to read. > > Massimo > > On Jun 13, 12:21 am, Mariano Mara <mariano.m...@gmail.com> wrote: > > 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 web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
