Using date/datetime fields with sqlite3, and wcf xml/json clients

[nick name]

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-constants> 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 \\/Date which 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.

[Massimo Di Pierro]

Thanks for this suggestion. Strange this never came up.

Give a couple of days and I will have 1 and 3 fixed. 2 involved making
mode changes to simplejson and it will make it more difficult to keep
in sync with the original. Let me give some thoughts to it.
Suggestions are welcome.

Massimo

On Feb 25, 4:52 pm, nick name <i.like.privacy....@gmail.com> 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*

[Massimo Di Pierro]

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 <i.like.privacy....@gmail.com> 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*

[nick name]

On Feb 25, 6:54 pm, Massimo Di Pierro <massimo.dipie...@gmail.com>
wrote:

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

Thanks! Super cool!

[Jonathan Lundell]

On Feb 25, 2011, at 3:15 PM, Massimo Di Pierro wrote:
>
> Give a couple of days and I will have 1 and 3 fixed. 2 involved making
> mode changes to simplejson and it will make it more difficult to keep
> in sync with the original. Let me give some thoughts to it.
> Suggestions are welcome.

FWIW, simplejson.encoder already has a note that you've modified it. So, what the heck. It'd be nice if the changes were more obvious, though.

If you patch it, how about updating to 2.1.4 while you're at it?

[Massimo Di Pierro]

I will take a look tomorrow.

[nick name]

> 2nd: Talking json to a WCF / .NET client

> 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?

In case anyone is having the same problem, the following (inserted in my database model file) does this without having to modify the simplejson files directly:

#PATCH simplejson to output data in the weird microsoft style
from gluon.contrib.simplejson import encoder
import re
encoder.ESCAPE = re.compile(r'[\x00-\x1f\\/"\b\f\n\r\t]')
encoder.ESCAPE_ASCII = re.compile(r'([\\/"]|[^\ -~])')
encoder.ESCAPE_DCT['/'] = r'\/'
del encoder
del re

Note that this escapes every use of slash (/), so it might not be what you want - but for now, it's good enough for me.