[Zope-CVS] CVS: Products3/PsycopgDA - Adapter.py:1.2

Viktorija Zaksiene ryzaja@codeworks.lt
Mon, 2 Dec 2002 06:12:38 -0500


Update of /cvs-repository/Products3/PsycopgDA
In directory cvs.zope.org:/tmp/cvs-serv4376

Modified Files:
	Adapter.py 
Log Message:
Type conversion for datetime fields


=== Products3/PsycopgDA/Adapter.py 1.1 => 1.2 ===
--- Products3/PsycopgDA/Adapter.py:1.1	Wed Nov 13 05:13:22 2002
+++ Products3/PsycopgDA/Adapter.py	Mon Dec  2 06:12:37 2002
@@ -16,19 +16,277 @@
 $Id$
 """
 
-import psycopg
-
 from Persistence import Persistent
 from Zope.App.RDB.ZopeDatabaseAdapter import ZopeDatabaseAdapter, parseDSN
 from Zope.App.RDB.ZopeConnection import ZopeConnection
 
-dsn2option_mapping = {'dbname': 'dbname',
+from datetime import date, time, timetz, datetime, datetimetz, timedelta
+import psycopg
+
+# These OIDs are taken from pg_types.h from PostgreSQL headers.
+# Unfortunatelly psycopg does not export them as constants, and
+# we cannot use psycopg.FOO.values because they overlap.
+DATE_OID        = 1082
+TIME_OID        = 1083
+TIMETZ_OID      = 1266
+TIMESTAMP_OID   = 1114
+TIMESTAMPTZ_OID = 1184
+INTERVAL_OID    = 1186
+# The following ones are obsolete and we don't handle them
+#ABSTIME_OID     = 702
+#RELTIME_OID     = 703
+#TINTERVAL_OID   = 704
+
+# Date/time parsing functions
+def parse_date(s):
+    """Parses ISO-8601 compliant dates and returns a tuple (year, month,
+    day).
+
+    The following formats are accepted:
+        YYYY-MM-DD  (extended format)
+        YYYYMMDD    (basic format)
+    """
+    if len(s) == 8:
+        return (int(s[:4]), int(s[4:6]), int(s[6:]))    # YYYYMMDD
+    elif len(s) == 10 and s[4] == s[7] == '-':
+        return (int(s[:4]), int(s[5:7]), int(s[8:]))    # YYYY-MM-DD
+    else:
+        raise ValueError, 'invalid date string: %s' % s
+
+def parse_time(s):
+    """Parses ISO-8601 compliant times and returns a tuple (hour, minute,
+    second).
+    
+    The following formats are accepted:
+        HH:MM:SS.ssss or HHMMSS.ssss
+        HH:MM:SS,ssss or HHMMSS,ssss
+        HH:MM:SS      or HHMMSS
+        HH:MM         or HHMM
+        HH
+    """
+    if len(s) == 2:
+        return (int(s[:2]), 0, 0)                       # HH
+    elif len(s) == 4:
+        return (int(s[:2]), int(s[2:]), 0)              # HHMM
+    elif len(s) == 5 and s[2] == ':':
+        return (int(s[:2]), int(s[3:]), 0)              # HH:MM
+    elif len(s) == 6:
+        return (int(s[:2]), int(s[2:4]), int(s[4:]))    # HHMMSS
+    elif len(s) == 8 and s[2] == s[5] == ':':
+        return (int(s[:2]), int(s[3:5]), int(s[6:]))    # HH:MM:SS
+    elif len(s) > 9 and s[2] == s[5] == ':' and s[8] == '.':
+        return (int(s[:2]), int(s[3:5]), float(s[6:]))  # HH:MM:SS.ssss
+    elif len(s) > 9 and s[2] == s[5] == ':' and s[8] == ',':
+        return (int(s[:2]), int(s[3:5]), float(s[6:8] + "." + s[9:]))
+                                                        # HH:MM:SS,ssss
+    elif len(s) > 7 and s[6] == '.':
+        return (int(s[:2]), int(s[2:4]), float(s[4:]))  # HHMMSS.ssss
+    elif len(s) > 7 and s[6] == ',':
+        return (int(s[:2]), int(s[2:4]), float(s[4:6] + "." + s[7:]))
+                                                        # HHMMSS,ssss
+    else:
+        raise ValueError, 'invalid time string: %s' % s
+
+def parse_tz(s):
+    """Parses ISO-8601 timezones and returns the offset east of UTC in
+    minutes.
+    
+    The following formats are accepted:
+        +/-HH:MM
+        +/-HHMM
+        +/-HH
+        Z           (equivalent to +0000)
+    """
+    if s == "Z":
+        return 0
+    if len(s) in (3, 5, 6) and s[0] in ('+', '-'):
+        hoff = int(s[1:3])
+        if len(s) > 3:
+            moff = int(s[-2:])
+            if len(s) == 6 and s[3] != ':':
+                raise ValueError, 'invalid time zone: %s' % s
+        else:
+            moff = 0
+        if s[0] == '-':
+            return - hoff * 60 - moff
+        else:
+            return hoff * 60 + moff
+    else:
+        raise ValueError, 'invalid time zone: %s' % s
+
+def parse_timetz(s):
+    """Parses ISO-8601 compliant times that may include timezone information
+    and returns a tuple (hour, minute, second, tzoffset).
+
+    tzoffset is the offset east of UTC in minutes.  It will be None if s does
+    not include time zone information.
+
+    Formats accepted are those listed in the descriptions of parse_time() and
+    parse_tz().  Time zone should immediatelly follow time without intervening
+    spaces.
+    """
+    idx = s.find('+')
+    if idx < 0: idx = s.find('-')
+    if idx < 0: idx = s.find('Z')
+    if idx < 0:
+        return parse_time(s) + (None,)
+    else:
+        return parse_time(s[:idx]) + (parse_tz(s[idx:]),)
+
+def parse_datetime(s):
+    """Parses ISO-8601 compliant timestamp and returns a tuple (year, month,
+    day, hour, minute, second).
+
+    Formats accepted are those listed in the descriptions of parse_date() and
+    parse_time() with ' ' or 'T' used to separate date and time parts.
+    """
+    idx = s.find('T')
+    if idx < 0: idx = s.find(' ')
+    if idx < 0:
+        raise ValueError, 'time part of datetime missing: %s' % s
+    return parse_date(s[:idx]) + parse_time(s[idx + 1:])
+
+def parse_datetimetz(s):
+    """Parses ISO-8601 compliant timestamp that may include timezone
+    information and returns a tuple (year, month, day, hour, minute, second,
+    tzoffset).
+
+    tzoffset is the offset east of UTC in minutes.  It will be None if s does
+    not include time zone information.
+
+    Formats accepted are those listed in the descriptions of parse_date() and
+    parse_timetz() with ' ' or 'T' used to separate date and time parts.
+    """
+    idx = s.find('T')
+    if idx < 0: idx = s.find(' ')
+    if idx < 0:
+        raise ValueError, 'time part of datetime missing: %s' % s
+    return parse_date(s[:idx]) + parse_timetz(s[idx + 1:])
+
+
+def parse_interval(s):
+    """Parses PostgreSQL interval notation and returns a tuple (years, months,
+    days, hours, minutes, seconds).
+
+    Values accepted:
+        interval  ::= date
+                   |  time
+                   |  date time
+        date      ::= date_comp
+                   |  date date_comp
+        date_comp ::= 1 'day'
+                   |  number 'days'
+                   |  1 'month'
+                   |  number 'months'
+                   |  1 'year'
+                   |  number 'years'
+        time      ::= number ':' number
+                   |  number ':' number ':' number
+                   |  number ':' number ':' number '.' fraction
+    """
+    years = months = days = 0
+    hours = minutes = seconds = 0
+    elements = s.split()
+    for i in range(0, len(elements) - 1, 2):
+        count, unit = elements[i:i+2]
+        if unit == 'day' and count == '1':
+            days += 1
+        elif unit == 'days':
+            days += int(count)
+        elif unit == 'month' and count == '1':
+            months += 1
+        elif unit == 'months':
+            months += int(count)
+        elif unit == 'year' and count == '1':
+            years += 1
+        elif unit == 'years':
+            years += int(count)
+        else:
+            raise ValueError, 'unknown time interval %s %s' % (count, unit)
+    if len(elements) % 2 == 1:
+        hours, minutes, seconds = parse_time(elements[-1])
+    return (years, months, days, hours, minutes, seconds)
+
+
+# Type conversions
+def _conv_date(s):
+    if s:
+        return date(*parse_date(s))
+
+def _conv_time(s):
+    if s:
+        hr, mn, sc = parse_time(s)
+        sc, micro = divmod(sc, 1.0)
+        micro = round(micro * 1000000)
+        return time(hr, mn, sc, micro)
+
+def _conv_timetz(s):
+    if s:
+        from Zope.Misc.DateTimeParse import tzinfo
+        hr, mn, sc, tz = parse_timetz(s)
+        sc, micro = divmod(sc, 1.0)
+        micro = round(micro * 1000000)
+        if tz: tz = tzinfo(tz)
+        return timetz(hr, mn, sc, micro, tz)
+
+def _conv_timestamp(s):
+    if s:
+        y, m, d, hr, mn, sc = parse_datetime(s)
+        sc, micro = divmod(sc, 1.0)
+        micro = round(micro * 1000000)
+        return datetime(y, m, d, hr, mn, sc, micro)
+
+def _conv_timestamptz(s):
+    if s:
+        from Zope.Misc.DateTimeParse import tzinfo
+        y, m, d, hr, mn, sc, tz = parse_datetimetz(s)
+        sc, micro = divmod(sc, 1.0)
+        micro = round(micro * 1000000)
+        if tz: tz = tzinfo(tz)
+        return datetimetz(y, m, d, hr, mn, sc, micro, tz)
+
+def _conv_interval(s):
+    if s:
+        y, m, d, hr, mn, sc = parse_interval(s)
+        if (y, m) != (0, 0):
+            # XXX: Currently there's no way to represent years and months as
+            # timedeltas
+            return s
+        else:
+            return timedelta(days=d, hours=hr, minutes=mn, seconds=sc)
+
+
+# User-defined types
+DATE = psycopg.new_type((DATE_OID,), "ZDATE", _conv_date)
+TIME = psycopg.new_type((TIME_OID,), "ZTIME", _conv_time)
+TIMETZ = psycopg.new_type((TIMETZ_OID,), "ZTIMETZ", _conv_timetz)
+TIMESTAMP = psycopg.new_type((TIMESTAMP_OID,), "ZTIMESTAMP", _conv_timestamp)
+TIMESTAMPTZ = psycopg.new_type((TIMESTAMPTZ_OID,), "ZTIMESTAMPTZ",
+                                _conv_timestamptz)
+INTERVAL = psycopg.new_type((INTERVAL_OID,), "ZINTERVAL", _conv_interval)
+
+
+dsn2option_mapping = {'host': 'host',
+                      'port': 'port',
+                      'dbname': 'dbname',
                       'username': 'user',
                       'password': 'password'}
 
 
 class PsycopgAdapter(ZopeDatabaseAdapter):
-    """A PsycoPG adapter for Zope3"""
+    """A PsycoPG adapter for Zope3.
+
+    The following type conversions are performed:
+
+        DATE -> datetime.date
+        TIME -> datetime.time
+        TIMETZ -> datetime.timetz
+        TIMESTAMP -> datetime.datetime
+        TIMESTAMPTZ -> datetime.datetimetz
+
+    XXX: INTERVAL cannot be represented exactly as datetime.timedelta since
+    it might be something like '1 month', which is a variable number of days.
+    """
     
     __implements__ = ZopeDatabaseAdapter.__implements__
     
@@ -41,11 +299,15 @@
                 conn_list.append('%s=%s' %(dsn2option_mapping[option],
                                            conn_info[option]))
         conn_str = ' '.join(conn_list)
+        self._registerTypes()
         return psycopg.connect(conn_str)
 
-
-
-
-
-
+    def _registerTypes(self):
+        """Register type conversions for psycopg"""
+        psycopg.register_type(DATE)
+        psycopg.register_type(TIME)
+        psycopg.register_type(TIMETZ)
+        psycopg.register_type(TIMESTAMP)
+        psycopg.register_type(TIMESTAMPTZ)
+        psycopg.register_type(INTERVAL)