1) is in trunk... db = DAL(...,driver_options=dict(detect_types=sqlite3.PARSE_DECLTYPES))
you can pass named options to all divers now, not just sqlite3. On Feb 25, 4:52 pm, nick name <[email protected]> wrote: > Hello good people. > > I've recently started a project using web2py, and I find it extremely well > designed. However, within a 2 days of starting, I have already needed to > patch it in 3 places, so either I'm doing something wrong, something > non-standard, or I should find out what is the preferred way of submitting > patches :) > > *1st: date/datetime fields on sqlite3* > > My project will run on PostgreSQL in production, but for the purpose of > communicating with other developers, it is very beneficial to use the > sqlite3 backend for now, which is what I do. By default, storing date or > datetime fields in sqlite3 gets them retrieved again as strings, which kills > my application logic (the dates need to be processed as dates). > > Fortunately, sqlite3 has a provision for solving that - it is called > "detect_types". Unfortunately, web2py does not expose this setting through > the DAL in any user settable way. My patch is as follows (against the tar of > Version 1.91.6 (2011-01-03 17:55:14)) > > ~/web2py/gluon$ diff origdal.py dal.py > 1316c1316 > < self.pool_connection(lambda dbpath=dbpath: > sqlite3.Connection(dbpath, check_same_thread=False)) > ---> self.pool_connection(lambda dbpath=dbpath: > > sqlite3.Connection(dbpath, check_same_thread=False, > detect_types=sqlite3.PARSE_DECLTYPES)) > > This serves my purposes perfectly, and I suspect would be useful for anyone > using sqlite3 - it instructs the dbapi module to convert fields of type DATE > or TIMESTAMP (which are stored as strings) into a datetime.datetime object. > So it might be useful for inclusion in general. However, there are other > options (see e.g. PARSE_COLNAMES, isolation_level - > <http://docs.python.org/library/sqlite3.html#module-functions-and-cons...> > for more) that might be useful for other users. > > Additionally, it might be useful to expose the sqlite3 module or the > connection objects so that the user may register_converter() or > create_aggregation() on them. I'm not yet familiar enough with web2py to > propose a web2py-ish way to pass these options to dal - but if someone else > does, I'll try to implement and provide a patch. > > *2nd: Talking json to a WCF / .NET client > * > web2py has an excellent json interface built in. It's perfect for all my > uses except for one thing: Microsoft, in their usual > embrace-extend-extinguish mentality, have perverted their json so that dates > are encoded in json with the following format: \/Date(unixepoch-hhmm)\/where > unixepoch is the time in seconds since 1/1/1970, and "-hhmm" is a timezone, > e.g. -0500 for Eastern Standard Time and +0100 for London Daylight Saving > Time; the --hhmm component is optional and defaults to 0000=UTC. > > The '\' in \/Date is an escape sequence, which is legal but ignored by every > json parser out there. Microsoft code serializes dates like that (which is > fine, I can parse it in Python well - the backslash escape just gets eaten > away) - but it also expects that to be there when deserializing; I haven't > figured out a way to do that with simplejson, because if I try to put the > backslash there, simplejson will escape it and the result would be > \\/Datewhich is rejected by the MS parser. > > My solution here was to modify simplejson so that it also escapes the '/' > character, and just return '/Date(...)/' as my date value. This works, and > (based on the same reasoning used by MS) is safe for anything. But I don't > like it; Is there any simpler solution for that, that would escape > *only*datetime values? > > Note: replacing jsonencoder's default() function is not enough - I had to > patch ESCAPE, ESCAPE_ASCII and ESCAPE_DCT as well. > > Also, I am planning to modify the decoder to notice this and parse this is a > datetime object directly, but haven't done so yet - if there's a > particularly simple way to do that, please point me in the right direction. > > See <http://weblogs.asp.net/bleroy/archive/2008/01/18/dates-and-json.aspx> > for more details > > *3rd: Generating xml output* > > This one is really small; when I serialize my output as xml, I need the root > object to be called "root", rather than the default "document". I have fixed > that by adding the "key='root'" to generic.xml's serialization call, and if > I needed more flexibility I would have made that read from an application > global setting. > > However, I think this is better served as something programmatically > conifgurable - makes no sense to me that the top element's name gets decided > by a view and every other element's name comes from a dict() I return.
