Contact: fumanchu@aminus.org

Log in as guest/geniusql to create tickets

Changeset 47

Show
Ignore:
Timestamp:
03/24/07 23:54:40
Author:
fumanchu
Message:

Fixed SQL Server binary ops.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • trunk/geniusql/adapters.py

    r46 r47  
    536536    # Use decimal instead of float to avoid rounding errors. 
    537537    def coerce_datetime_timedelta(self, hints): 
     538        # This is a fallback for DB's which do not have an INTERVAL data type. 
     539        # If your DB has an INTERVAL datatype, you should override this and 
     540        # use the native INTERVAL type instead. You'll usually also have to 
     541        # update the date arithmetic inside the decompiler. 
    538542        return self.int_type(self.numeric_max_bytes) 
    539543     

  • trunk/geniusql/decompile.py

    r46 r47  
    472472    #                           Binary operations                         # 
    473473     
    474     # So, the question is, do we really want to support this level of detail 
    475     # in attribute expressions or do we want to tell users to move all that 
    476     # to Python? I think we do want it in SQL, because that's the only way 
    477     # to efficiently support INSERT INTO. But it means a lot of work and 
    478     # a lot of database-specific adaptation to deal with implicit typing. :( 
    479      
    480474    # Resultant type for a binary operation between two types. 
    481475    result_type = {} 

  • trunk/geniusql/providers/ado.py

    r46 r47  
    5858adUseClient = 3 
    5959 
    60 # 12/30/1899, the zero-Date for ADO = 693594 
    61 # "Sure, there are two 4-byte integers stored. But they are 
    62 # packed together into a BINARY(8). The first 4-byte being 
    63 # the elapsed number days since SQL Server's base date of 
    64 # 1900-01-01. The Second 4-bytes Store the Time of Day 
    65 # Represented as the Number of Milliseconds After Midnight." 
    66 # http://www.sql-server-performance.com/fk_datetime.asp 
    67 zeroHour = datetime.datetime(1899, 12, 30) 
    68  
    69 # Note also that SQL Server allows DATETIME in the range: 
    70 # "1753-01-01 00:00:00.0" to "9999-12-31 23:59:59.997". 
    71  
    72 dbtypes = { 
    73     0: 'EMPTY',                     2: 'SMALLINT', 
    74     3: 'INTEGER',                   4: 'SINGLE', 
    75     5: 'DOUBLE',                    6: 'CURRENCY', 
    76     7: 'DATE',                      8: 'BSTR', 
    77     9: 'IDISPATCH',                 10: 'ERROR', 
    78     11: 'BOOLEAN',                  12: 'VARIANT', 
    79     13: 'IUNKNOWN',                 14: 'DECIMAL', 
    80     16: 'TINYINT',                  17: 'UNSIGNEDTINYINT', 
    81     18: 'UNSIGNEDSMALLINT',         19: 'UNSIGNEDINT', 
    82     20: 'BIGINT',                   21: 'UNSIGNEDBIGINT', 
    83     72: 'GUID',                     128: 'BINARY', 
    84     129: 'CHAR',                    130: 'WCHAR', 
    85     131: 'NUMERIC',                 132: 'USERDEFINED', 
    86     133: 'DBDATE',                  134: 'DBTIME', 
    87     135: 'DBTIMESTAMP',             200: 'VARCHAR', 
    88     201: 'LONGVARCHAR',             202: 'VARWCHAR', 
    89     203: 'LONGVARWCHAR',            204: 'VARBINARY', 
    90     205: 'LONGVARBINARY' 
     60# See http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx 
     61dbtypes = {# ADO Name           SQL Server              MS Access 
     62        0: 'EMPTY', 
     63        2: 'SMALLINT',        # SMALLINT                INTEGER 
     64        3: 'INTEGER',         # IDENTITY (6.5), INT     AUTONUMBER, LONG 
     65        4: 'SINGLE',          # REAL                    SINGLE 
     66        5: 'DOUBLE',          # FLOAT                   DOUBLE 
     67        6: 'CURRENCY',        # MONEY, SMALLMONEY       CURRENCY 
     68        7: 'DATE',            #                         DATETIME (Access 97) 
     69        8: 'BSTR', 9: 'IDISPATCH', 10: 'ERROR', 
     70        11: 'BOOLEAN',        # BIT                     YESNO 
     71        12: 'VARIANT',        # SQL_VARIANT (2000 +) 
     72        13: 'IUNKNOWN', 14: 'DECIMAL', 16: 'TINYINT', 
     73        17: 'UNSIGNEDTINYINT',# TINYINT                 BYTE 
     74        18: 'UNSIGNEDSMALLINT', 19: 'UNSIGNEDINT', 
     75        20: 'BIGINT',         # BIGINT 
     76        21: 'UNSIGNEDBIGINT', 
     77        72: 'GUID', 
     78        128: 'BINARY',        # BINARY, TIMESTAMP 
     79        129: 'CHAR',          # CHAR 
     80        130: 'WCHAR',         # NCHAR (7.0+) 
     81        131: 'NUMERIC',       # DECIMAL, NUMERIC        DECIMAL (Access 2000) 
     82        132: 'USERDEFINED', 
     83        133: 'DBDATE', 134: 'DBTIME', 
     84        135: 'DBTIMESTAMP',   # DATETIME, SMALLDATETIME   DATETIME (ODBC 97) 
     85        200: 'VARCHAR',       # VARCHAR                 TEXT (Access 97) 
     86        201: 'LONGVARCHAR',   # TEXT                    MEMO (Access 97) 
     87        202: 'VARWCHAR',      # NVARCHAR                TEXT (Access 2000) 
     88        203: 'LONGVARWCHAR',  # NTEXT (7.0+)            MEMO (Access 2000+) 
     89        204: 'VARBINARY',     # VARBINARY 
     90        205: 'LONGVARBINARY', # IMAGE                   OLEOBJECT 
    9191} 
    9292 
    …… 
    102102 
    103103 
    104 def timedelta_from_com(com_date): 
    105     """Return a valid datetime.timedelta from a COM date/time object.""" 
    106     com_date = float(com_date) 
    107      
    108     # MS Access represents dates and times as floats. If the value is 
    109     # before the epoch (12/30/1899), the seconds will be SUBTRACTED 
    110     # from the float. For example, -2.01 is in the morning and -2.99 
    111     # is in the evening of the same day. Therefore, when we split off 
    112     # our seconds we must use the abs value of the fractional portion. 
    113     neg = (com_date < 0) 
    114     com_date = abs(com_date) 
    115      
    116     days = int(com_date) 
    117     # Must do both int() and round() or we'll be up to 1 second off. 
    118     secs = int(round(86400 * (com_date - days))) 
    119      
    120     result = datetime.timedelta(days, secs) 
    121     if neg: 
    122         return -result 
    123     else: 
    124         return result 
    125  
    126  
    127 def datetime_from_com(com_date): 
    128     """Return a valid datetime.datetime from a COM date/time object.""" 
    129     com_date = float(com_date) 
    130      
    131     # MS Access represents dates and times as floats. If the value is 
    132     # before the epoch (12/30/1899), the seconds will be SUBTRACTED 
    133     # from the float. For example, -2.01 is in the morning and -2.99 
    134     # is in the evening of the same day. Therefore, when we split off 
    135     # our seconds we must use the abs value of the fractional portion. 
    136     # Note that we do this differently from timedelta_from_com, 
    137     # because there we need to subtract seconds, and here we add them. 
    138     days = int(com_date) 
    139      
    140     # Must do both int() and round() or we'll be up to 1 second off. 
    141     secs = int(round(86400 * abs(com_date - days))) 
    142      
    143     return zeroHour + datetime.timedelta(days, secs) 
    144  
    145  
    146104class AdapterFromADO(adapters.AdapterFromDB): 
    147105    """Coerce incoming values from ADO to Python datatypes.""" 
    148106     
    149107    encoding = 'ISO-8859-1' 
     108    epoch = datetime.datetime(1899, 12, 30) 
     109     
     110    def timedelta_from_com(self, com_date): 
     111        """Return a valid datetime.timedelta from a COM date/time object.""" 
     112        com_date = float(com_date) 
     113         
     114        # MS Access represents dates and times as floats. If the value is 
     115        # before the epoch (12/30/1899), the seconds will be SUBTRACTED 
     116        # from the float. For example, -2.01 is in the morning and -2.99 
     117        # is in the evening of the same day. Therefore, when we split off 
     118        # our seconds we must use the abs value of the fractional portion. 
     119        neg = (com_date < 0) 
     120        com_date = abs(com_date) 
     121         
     122        days = int(com_date) 
     123        # Must do both int() and round() or we'll be up to 1 second off. 
     124        secs = int(round(86400 * (com_date - days))) 
     125         
     126        result = datetime.timedelta(days, secs) 
     127        if neg: 
     128            return -result 
     129        else: 
     130            return result 
    150131     
    151132    def coerce_any_to_datetime_timedelta(self, value): 
    152133        # Assume pywintypes.TimeType 
    153         return timedelta_from_com(value) 
     134        return self.timedelta_from_com(value) 
     135     
     136    def coerce_any_to_datetime_time(self, value): 
     137        t = self.timedelta_from_com(value) 
     138        if t.days: 
     139            raise ValueError("Time values greater than 23:59:59 not allowed.") 
     140        h, m = divmod(t.seconds, 3600) 
     141        m, s = divmod(m, 60) 
     142        return datetime.time(int(h), int(m), int(s)) 
     143     
     144    def datetime_from_com(self, com_date): 
     145        """Return a valid datetime.datetime from a COM date/time object.""" 
     146        com_date = float(com_date) 
     147         
     148        # MS Access represents dates and times as floats. If the value is 
     149        # before the epoch (12/30/1899), the seconds will be SUBTRACTED 
     150        # from the float. For example, -2.01 is in the morning and -2.99 
     151        # is in the evening of the same day. Therefore, when we split off 
     152        # our seconds we must use the abs value of the fractional portion. 
     153        # Note that we do this differently from timedelta_from_com, 
     154        # because there we need to subtract seconds, and here we add them. 
     155        days = int(com_date) 
     156         
     157        # Must do both int() and round() or we'll be up to 1 second off. 
     158        secs = int(round(86400 * abs(com_date - days))) 
     159         
     160        return self.epoch + datetime.timedelta(days, secs) 
    154161     
    155162    def coerce_any_to_datetime_datetime(self, value): 
    …… 
    167174            # value.Format(). Therefore, grab the float value and figure 
    168175            # the date ourselves. Use 1-second resolution only. 
    169             return datetime_from_com(value) 
     176            return self.datetime_from_com(value) 
    170177     
    171178    def coerce_any_to_datetime_date(self, value): 
    …… 
    182189            value = float(value) 
    183190            days = int(value) 
    184             return zeroHour.date() + datetime.timedelta(days) 
    185      
    186     def coerce_any_to_datetime_time(self, value): 
    187         # See coerce_any_to_datetime 
    188         t = timedelta_from_com(value) 
    189         h, m = divmod(t.seconds, 3600) 
    190         m, s = divmod(m, 60) 
    191         return datetime.time(int(h) + (t.days * 24), int(m), int(s)) 
     191            return self.epoch.date() + datetime.timedelta(days) 
    192192     
    193193    def coerce_any_to_decimal_Decimal(self, value): 
    …… 
    325325                    newsql = self.DATEADD(op1.sql, op2.sql) 
    326326                elif op == "-": 
    327                     newsql = self.DATEDIFF(op1.sql, op2.sql) 
     327                    newsql = self.DATESUB(op1.sql, op2.sql) 
    328328        elif t1 is datetime.datetime: 
    329329            if t2 is datetime.datetime: 
    …… 
    334334                    newsql = self.DATETIMEADD(op1.sql, op2.sql) 
    335335                elif op == "-": 
    336                     newsql = self.DATETIMEDIFF(op1.sql, op2.sql) 
     336                    newsql = self.DATETIMESUB(op1.sql, op2.sql) 
    337337        elif t1 is datetime.timedelta: 
    338338            if t2 is datetime.timedelta: 
    …… 
    402402        # = is already case-insensitive in ADO. 
    403403        return self.get_expr(x.sql + " = " + y.sql, bool) 
    404      
    405     def builtins_now(self): 
    406         return self.get_expr("GETDATE()", datetime.datetime) 
    407      
    408     def builtins_today(self): 
    409         return self.get_expr("DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)", 
    410                              datetime.date) 
    411      
    412     def builtins_year(self, x): 
    413         return self.get_expr("DATEPART(year, " + x.sql + ")", int) 
    414      
    415     def builtins_month(self, x): 
    416         return self.get_expr("DATEPART(month, " + x.sql + ")", int) 
    417      
    418     def builtins_day(self, x): 
    419         return self.get_expr("DATEPART(day, " + x.sql + ")", int) 
    420404     
    421405    def func__builtin___len(self, x): 
    …… 
    839823 
    840824 
     825# "Sure, there are two 4-byte integers stored. But they are 
     826# packed together into a BINARY(8). The first 4-byte being 
     827# the elapsed number days since SQL Server's base date of 
     828# 1900-01-01. The Second 4-bytes Store the Time of Day 
     829# Represented as the Number of Milliseconds After Midnight." 
     830# http://www.sql-server-performance.com/fk_datetime.asp 
     831 
     832# Note also that SQL Server allows DATETIME in the range: 
     833# "1753-01-01 00:00:00.0" to "9999-12-31 23:59:59.997". 
     834 
    841835 
    842836class ADOSQLDecompiler_SQLServer(ADOSQLDecompiler): 
    …… 
    855849     
    856850    def DATEADD(dt, td): 
    857         """Return the SQL to add a number a timedelta to a date.""" 
    858         return "DATEADD(dd, %s, %s)" % (dt, td) 
     851        """Return the SQL to add a timedelta to a date.""" 
     852        # Days, seconds seems like a good way to avoid overflow. 
     853        return ("DATEADD(dd, FLOOR(%s / 86400), " 
     854                "DATEADD(ss, (%s %% 86400), %s))" 
     855                % (td, td, dt)) 
    859856    DATEADD = staticmethod(DATEADD) 
     857     
     858    def DATESUB(dt, td): 
     859        """Return the SQL to subtract a timedelta from a date.""" 
     860        return "(%s - FLOOR(%s / 86400.0))" % (dt, td) 
     861    DATESUB = staticmethod(DATESUB) 
    860862     
    861863    def DATEDIFF(d1, d2): 
    862864        """Return the SQL to subtract one date from another.""" 
    863865        # Amazing what a difference a little ".0" can make. 
    864         return "(DATEDIFF(dd, %s, %s) * 86400.0)" % (d2, d1) 
     866        return "CAST(DATEDIFF(dd, %s, %s) * 86400.0 AS NUMERIC)" % (d2, d1) 
    865867    DATEDIFF = staticmethod(DATEDIFF) 
    866868     
    867869    def DATETIMEADD(dt, td): 
    868870        """Return the SQL to add a timedelta to a datetime.""" 
    869         return ("DATEADD(dd, %s, DATEADD(s, %s, DATEADD(ms, %s, %s)))" 
    870                 % (td.days, td.seconds, td.microseconds, dt)) 
     871        return "(%s + (%s / 86400.0))" % (dt, td) 
    871872    DATETIMEADD = staticmethod(DATETIMEADD) 
    872873     
    873874    def DATETIMEDIFF(d1, d2): 
    874875        """Return the SQL to subtract one datetime from another.""" 
    875         return ("(DATEDIFF(dd, %(d2)s, %(d1)s) * 86400.0)" 
    876                 " + ((DATEPART(hh, %(d1)s) - DATEPART(hh, %(d2)s)) * 3600.0)" 
    877                 " + ((DATEPART(n, %(d1)s) - DATEPART(n, %(d2)s)) * 60)" 
    878                 " + (DATEPART(s, %(d1)s) - DATEPART(s, %(d2)s))" 
    879                 % {'d1': d1, 'd2': d2}) 
     876        return "CAST(CAST(%s - %s AS FLOAT) * 86400 AS NUMERIC)" % (d1, d2) 
    880877    DATETIMEDIFF = staticmethod(DATETIMEDIFF) 
     878     
     879    def DATETIMESUB(dt, td): 
     880        """Return the SQL to subtract a timedelta from a datetime.""" 
     881        return "(%s - (%s / 86400.0))" % (dt, td) 
     882    DATETIMESUB = staticmethod(DATETIMESUB) 
     883     
     884    def TIMEDELTAADD(op1, op, op2): 
     885        return "(%s %s %s)" % (op1.sql, op, op2.sql) 
     886    TIMEDELTAADD = staticmethod(TIMEDELTAADD) 
     887     
     888    def builtins_now(self): 
     889        return self.get_expr("GETDATE()", datetime.datetime) 
     890     
     891    def builtins_today(self): 
     892        return self.get_expr("DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)", 
     893                             datetime.date) 
     894     
     895    def builtins_year(self, x): 
     896        return self.get_expr("DATEPART(year, " + x.sql + ")", int) 
     897     
     898    def builtins_month(self, x): 
     899        return self.get_expr("DATEPART(month, " + x.sql + ")", int) 
    881900     
    882901    def builtins_day(self, x): 
    …… 
    909928        return ("(CASE WHEN ISNUMERIC(%s)=1 THEN CAST(%s AS int) END)" 
    910929                % (colref, colref)) 
     930 
     931 
     932class AdapterFromADOSQL_SQLServer(AdapterFromADO): 
     933     
     934    def coerce_any_to_datetime_time(self, value): 
     935        # Floats returned from SQL Server will be 2 days off 
     936        # because its epoch is 2 days later than MS Access. 
     937        return AdapterFromADO.coerce_any_to_datetime_time(self, float(value) - 2) 
     938     
     939    def coerce_any_to_datetime_timedelta(self, value): 
     940        # We're using the fallback type for timedelta (secs * 86400). 
     941        days, secs = divmod(long(value), 86400) 
     942        return datetime.timedelta(int(days), int(secs)) 
    911943 
    912944 
    …… 
    10321064    decompiler = ADOSQLDecompiler_SQLServer 
    10331065    adaptertosql = AdapterToADOSQL_SQLServer() 
     1066    adapterfromdb = AdapterFromADOSQL_SQLServer() 
    10341067    typeadapter = TypeAdapter_SQLServer() 
    10351068    connectionmanager = SQLServerConnectionManager 
    …… 
    10761109class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler): 
    10771110    sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in') 
     1111     
     1112    epoch = datetime.datetime(1899, 12, 30) 
    10781113     
    10791114    def _compare_strings(self, op1, op, op2): 
    …… 
    11181153        return "CDate(Fix(%s) - Fix(%s))" % (d1, d2) 
    11191154    DATEDIFF = staticmethod(DATEDIFF) 
     1155    DATESUB = DATEDIFF 
    11201156     
    11211157    def DATETIMEADD(dt, td): 
    …… 
    11281164        return "CDate(%s - %s)" % (d1, d2) 
    11291165    DATETIMEDIFF = staticmethod(DATETIMEDIFF) 
     1166    DATETIMESUB = DATETIMEDIFF 
    11301167     
    11311168    def TIMEDELTAADD(op1, op, op2): 

  • trunk/geniusql/test/zoo_fixture.py

    r46 r47  
    175175        lion = schema['Animal'].insert(Species='Lion', ZooID=wap)['ID'] 
    176176        schema['Animal'].insert(Species='Slug', Legs=1, Lifespan=.75, 
    177                   # Test our 8000-byte limit 
    178                   PreviousZoos=["f" * (8000 - 14)]) 
     177                                # Test our 8000-byte limit (ok, 7900) 
     178                                PreviousZoos=["f" * (7900 - 14)]) 
    179179         
    180180        tiger = schema['Animal'].insert(Species='Tiger', ZooID=sdz, 
    …… 
    302302        self.assertEqual(tiger['PreviousZoos'], ["animal\\universe"]) 
    303303         
    304         # Test our 8000-byte limit. 
    305         # len(pickle.dumps(["f" * (8000 - 14)]) == 8000 
     304        # Test our 8000-byte limit (ok, 7900; the row is too long) 
     305        # len(pickle.dumps(["f" * (7900 - 14)]) == 7900 
    306306        slug = schema['Animal'].select(Species='Slug') 
    307         self.assertEqual(len(slug['PreviousZoos'][0]), 8000 - 14) 
     307        self.assertEqual(len(slug['PreviousZoos'][0]), 7900 - 14) 
    308308         
    309309        # Exhibits