Re: [Gnumed-devel] Gnumed on Windows: the pain of pyPgSQL

[Tim Churches]

Ian Haywood wrote:

> Tim Churches wrote:
>  
>
> > Karsten Hilbert wrote:
> >
> >    
> >
> > > On Fri, Sep 09, 2005 at 02:07:11PM +0800, address@hidden wrote:
> > >
> > > Good things are heard of psycopg. I am currently unsure of
> > > it's Windows support, though. If so I'd consider that one
> > > when switching GNUmed.
> > >
> > >
> > >
> > >      
> > >
> > > > Or should I have a another bash at my pure-python adaptor
> > > > (as this is easier to support)
> > > >        
> > > I do have to admit a pure Python one sounds intriguing and
> > > useful. I wonder why there isn't any. The one relevant
> > > question probably is: Can it be made performant ?
> > >      
> > Look, GNUmed is (collectively) your project - I am just an interested
> > observer - but I really don't understand why you need to change
> > Python-PostgreSQL database adaptors. 
> >    
> I have a number of criticisms of PyPgSQL besides lack of windows binaries,
> particularly:
>         - gratuitious dependency on mxDateTime (and that is a lot of work, as 
> this
> package is slowly vanishing from the distros.)
>  
I agree that the dependency on MxDateTime is an annoyance, but it is not 
gratuitous: pyPgSQL pre-dates the addition of the native datetime type 
to Python - so it had to use something as a dat/time library, and 
mxDateTime was by far the best choice. The annoying thing is taht teh 
dependence has not yet been removed. Last time I looked, all references 
to mxDateTime were in the main PgSQL.py module, and perhaps several 
hours Python coding would be needed to convert from mxDateTime to native 
Python datetime, plus several more hours to add a more complete set of 
datetime units tests - there are some but not very many. However, I am 
pretty sure that some of the pyPgSQL date/time functions arithmetic 
requires date/time arithmetic which is not supported in the standard 
Python datetime library, but which are in mxDateTime. In other words, 
teh native python datetime library is not a complete replacement for 
mxDateTime. Thus you would need to replicate date/time arithmetic 
functions (and write tests for them), or use Gustavo Niemeyer's 
excellent dateutils package, which provides all the necessary functions 
and then some. I am told that the dateutils package will probably be 
added to the Python standard library in version 2.5, some time in 2006. 
However, until then, the dependency on mxDateTime could not be replaced 
without creating a new dependency on dateutils, or without some work 
writing your own date arithmetic functins, or maybe grafting the 
necessary bits of dateutils into pyPgSQL if the licenses allow.

>         -forces use of cursors, which imposes a speed penalty (I don't know how 
> much of course)
> and requires various hacks as not all postgres commands work inside a 
> transaction.
>  
That's a feature (or failing) of the Python DB-API v2.0, isn't it? 
However, when wouldn't you want to use transactions? I agree pyPgSQL is 
rather slow when handling very large queries (100,000s of records), but 
is that really a headline problem with respect to GNUmed? Curiously, 
update speed seems quite good.

>  
>
> > I sent Ian binaries of pyPgSQL for
> > Win32 and Python2.4 obtained from www.haering.de
> >    
> Thanks, will test on Monday.
>  
>
> > - unfortunately that
> > Web site now seems to be some other sort of company. Nevertheless, the
> > maintained pyPgSQL code can be checked out of the SourceForge CVS and a
> > Win32 binary compiled. Sure that is extra work, but is it more or less
> > extra work than a) writing your own Python-PostgreSQL adaptor 
> >    
> That's the point, I'm honestly not sure, particularly with mxDateTime.
>  
S'up to you...

>  
>
> > operations will detect any problems early on, but it seems like you are
> > making a rod for your collective backs, given that there seem to be
> > more pressing tasks with respect to finishing GNUmed. 
> >    
> Quite true, Given your provision of windows binaries there certainly isn't
> any compelling need to change.
>
>  
>
> > I would have
> > thought that there is at least several (very tedious) person-weeks work
> > in just writing unit tests for a new adaptor 
> >    
> So about 10x the time taken to actually develop the adaptor??
>  
Possibly. Writing comprehensive unit tests is very tedious.

> I'll have a look at the pyPgSQL unit tests to get a better idea.
>  
There aren't very many, but at least there are some...

Tim C