'''
File:          ADOdb.py

Author:        Kevin Jacobs (jacobs@theopalgroup.com)

Created:       January 5, 2002

Purpose:       Python DB-API 2.0 interface to Microsoft ADO objects

Compatibility: Python 2.0

Requires:      Python Win32 extensions, ADO 2.5+, mx.DateTime

Revision:      Version 0.10 - Alpha quality code

Copyright (c) 2002 The OPAL Group.

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to
deal in the Software without restriction, including without limitation the
rights to use, copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
IN THE SOFTWARE.

----------------------------------------------------------------------------------
'''

import pywintypes
import win32com.client

apilevel     = '2.0'
threadsafety = 1
paramstyle   = 'pyformat'

class Warning                  (StandardError)       : pass
class Error                    (StandardError)       : pass
class   InterfaceError           (Error)             : pass
class   DatabaseError            (Error)             : pass
class     DataError                (DatabaseError)   : pass
class     OperationalError         (DatabaseError)   : pass
class     IntegrityError           (DatabaseError)   : pass
class     InternalError            (DatabaseError)   : pass
class     ProgrammingError         (DatabaseError)   : pass
class     NotSupportedError        (DatabaseError)   : pass

#---------------------------------------------------------------
# CommandType Attributes

adCmdUnspecified  =  -1           # Does not specify the command type argument.
adCmdText         =   1           # Evaluates CommandText as a textual definition of a command or stored procedure call.
adCmdTable        =   2           # Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
adCmdStoredProc   =   4           # Evaluates CommandText as a stored procedure name.
adCmdUnknown      =   8           # Indicates that the type of command in the CommandText property is not known.
adCmdFile         = 256           # Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
adCmdTableDirect  = 512           # Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect. This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.

#---------------------------------------------------------------
# CursorType Attributes

adOpenUnspecified = -1            # Unspecified type of cursor
adOpenForwardOnly =  0            # Default. A forward-only cursor. This improves performance when you need to make only one pass through a Recordset
adOpenKeyset      =  1            # A keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.
adOpenDynamic     =  2            # A dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed
adOpenStatic      =  3            # A static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.


#---------------------------------------------------------------
# ExecuteOption Attributes

adOptionUnspecified     =  -1     # Indicates that the command is unspecified.
adAsyncExecute          =  16     # Indicates that the command should execute asynchronously. This value cannot be combined with the CommandTypeEnum value adCmdTableDirect.
adAsyncFetch            =  32     # Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be retrieved asynchronously.
adAsyncFetchNonBlocking =  64     # Indicates that the main thread never blocks while retrieving. If the requested row has not been retrieved, the current row automatically moves to the end of the file. If you open a Recordset from a Stream containing a persistently stored Recordset, adAsyncFetchNonBlocking will not have an effect; the operation will be synchronous and blocking. adAsynchFetchNonBlocking has no effect when the adCmdTableDirect option is used to open the Recordset.
adExecuteNoRecords      = 128     # Indicates that the command text is a command or stored procedure that does not return rows (for example, a command that only inserts data). If any rows are retrieved, they are discarded and not returned. adExecuteNoRecords can only be passed as an optional parameter to the Command or Connection Execute method.
adExecuteStream         = 256     # Indicates that the results of a command execution should be returned as a stream. adExecuteStream can only be passed as an optional parameter to the Command Execute method.
adExecuteRecord         = 512     # Indicates that the CommandText is a command or stored procedure that returns a single row which should be returned as a Record object.

#---------------------------------------------------------------
# Type Attributes

adFldMayDefer        = 0x2        # Field values are not retrieved from the data source with the whole record, but only when you explicitly access them.
adFldUpdatable       = 0x4        # You can write to the field.
adFldUnknownUpdatable= 0x8        # The provider cannot determine if you can write to the field.
adFldFixed           = 0x10       # Field contains fixed-length data.
adFldIsNullable      = 0x20       # Field accepts null values.
adFldMayBeNull       = 0x40       # You can read null values from the field.
adFldLong            = 0x80       # Field is a long binary field.
adFldRowID           = 0x100      # Field contains a persistent row identifier that cannot be written to and has no meaningful value except to identify the row (such as a unique id)
adFldRowVersion      = 0x200      # Field contains some kind of time/date stamp used to track updates.
adFldCacheDeferred   = 0x1000     # Provider caches the field values and reads from the cache.
adFldIsChapter       = 0x2000     # Field contains a chapter value that specifies a child recordset.
adFldNegativeScale   = 0x4000     # Field represents a numeric value from a column that supports negative scale values.
adFldIsRowURL        = 0x10000    # Field contains the URL that names the resource from the data store represented by the record.
adFldIsDefaultStream = 0x20000    # Field contains the default stream for the resource represented by the record.
adFldIsCollection    = 0x40000    # The field specifies that the resource represented by the record is a collection of resources
adFldUnspecified     = -1         # Provider does not specify the field attributes.


#---------------------------------------------------------------
# Type Enum

adEmpty            =   0     # No value
adSmallInt         =   2     # A 2-byte signed integer.
adInteger          =   3     # A 4-byte signed integer.
adSingle           =   4     # A single-precision floating-point value.
adDouble           =   5     # A double-precision floating-point value.
adCurrency         =   6     # A currency value
adDate             =   7     # The number of days since December 30, 1899 + the fraction of a day.
adBSTR             =   8     # A null-terminated character string.
adIDispatch        =   9     # A pointer to an IDispatch interface on a COM object. Note: Currently not supported by ADO.
adError            =  10     # A 32-bit error code
adBoolean          =  11     # A boolean value.
adVariant          =  12     # An Automation Variant. Note: Currently not supported by ADO.
adIUnknown         =  13     # A pointer to an IUnknown interface on a COM object. Note: Currently not supported by ADO.
adDecimal          =  14     # An exact numeric value with a fixed precision and scale.
adTinyInt          =  16     # A 1-byte signed integer.
adUnsignedTinyInt  =  17     # A 1-byte unsigned integer.
adUnsignedSmallInt =  18     # A 2-byte unsigned integer.
adUnsignedInt      =  19     # A 4-byte unsigned integer.
adBigInt           =  20     # An 8-byte signed integer.
adUnsignedBigInt   =  21     # An 8-byte unsigned integer.
adFileTime         =  64     # The number of 100-nanosecond intervals since January 1,1601
adGUID             =  72     # A globally unique identifier (GUID)
adBinary           = 128     # A binary value.
adChar             = 129     # A string value.
adWChar            = 130     # A null-terminated Unicode character string.
adNumeric          = 131     # An exact numeric value with a fixed precision and scale.
adUserDefined      = 132     # A user-defined variable.
adDBDate           = 133     # A date value (yyyymmdd).
adDBTime           = 134     # A time value (hhmmss).
adDBTimeStamp      = 135     # A date/time stamp (yyyymmddhhmmss plus a fraction in billionths).
adChapter          = 136     # A 4-byte chapter value that identifies rows in a child rowset
adPropVariant      = 138     # An Automation PROPVARIANT.
adVarNumeric       = 139     # A numeric value (Parameter object only).
adVarChar          = 200     # A string value (Parameter object only).
adLongVarChar      = 201     # A long string value.
adVarWChar         = 202     # A null-terminated Unicode character string.
adLongVarWChar     = 203     # A long null-terminated Unicode string value.
adVarBinary        = 204     # A binary value (Parameter object only).
adLongVarBinary    = 205     # A long binary value.
AdArray            = 0x2000  # A flag value combined with another data type constant. Indicates an array of that other data type.


#---------------------------------------------------------------
# Type Objects

class DBAPITypeObject:
  def __init__(self,*values):
    self.values = values

  def __cmp__(self,other):
    if other in self.values:
      return 0
    if other < self.values:
      return 1
    else:
      return -1

def Binary(x):
  return str(x)

STRING   = DBAPITypeObject(adBSTR, adChar, adWChar, adVarChar,
                           adLongVarChar, adVarWChar, adLongVarWChar, adGUID)

BINARY   = DBAPITypeObject(adBinary, adVarBinary)

NUMBER   = DBAPITypeObject(adSmallInt, adInteger, adError, adTinyInt,
                           adUnsignedTinyInt, adUnsignedSmallInt,
                           adUnsignedInt, adUnsignedInt, adBigInt,
                           adUnsignedBigInt, adSingle, adDouble, adCurrency,
                           adDecimal, adNumeric, adVarNumeric, adBoolean)

DATETIME = DBAPITypeObject(adFileTime, adDBTime, adDBTimeStamp, adDate,
                           adDBDate)

ROWID    = DBAPITypeObject(adChapter)


#---------------------------------------------------------------
# Date/Time support

DateTime = None

from time import localtime
from mx.DateTime import Date, Time, Timestamp, ISO,      \
                        DateTimeType, DateTimeDeltaType

def DateFromTicks(ticks):
  return Date(*localtime(ticks)[:3])

def TimeFromTicks(ticks):
  return Time(*localtime(ticks)[3:6])

def TimestampFromTicks(ticks):
  return Timestamp(*localtime(ticks)[:6])

xlate_datetime = { adFileTime    : lambda x: x,
                   adDBTime      : lambda x: TimeFromTicks(int(x)),
                   adDBTimeStamp : lambda x: TimestampFromTicks(int(x)),
                   adDate        : lambda x: DateFromTicks(int(x)),
                   adDBDate      : lambda x: DateFromTicks(int(x)) }

#---------------------------------------------------------------

def _xlate_com_exception(com_ex, py_ex):
  if com_ex[2]:
    return py_ex(com_ex[2][2])
  else:
    return py_ex()


class ADOConnection:

  def __init__(self, dsn = '', user = None, password = None, host = None,
               database = None, port = None, driver = None):

    fields = dsn.split(';')

    params = {}
    for field in filter(None,fields):
      attr,val = field.split('=')
      params[attr] = val

    if user is not None:
      params['UID'] = user
    if password is not None:
      params['PWD'] = password
    if host is not None:
      params['SERVER'] = host
    if database is not None:
      params['DATABASE'] = database
    if port is not None:
      params['PORT'] = port
    if driver is not None:
      params['DRIVER'] = driver

    new_dsn = 'DRIVER=%(DRIVER)s;DATABASE=%(DATABASE)s;SERVER=%(SERVER)s;PORT=%(PORT)s;UID=%(UID)s;PWD=%(PWD)s;'
    new_dsn = new_dsn % params

    try:
      self.conn = win32com.client.Dispatch('ADODB.Connection')
      self.conn.Open(new_dsn)

    except pywintypes.com_error, e:
      py_exception = _xlate_com_exception(e, OperationalError)
      raise py_exception

    self.conn.BeginTrans()

  def close():
    if self.conn.State == 0:
      raise InterfaceError, 'Connection already closed'
    self.conn.Close()

  def commit(self):
    if self.conn.State == 0:
      raise InterfaceError, 'Commit failed - Connection is closed'
    self.conn.CommitTrans()
    self.conn.BeginTrans()

  def rollback(self):
    if self.conn.State == 0:
      raise InterfaceError, 'Rollback failed - Connection is closed'
    self.conn.RollbackTrans()
    self.conn.BeginTrans()

  def cursor(self):
    if self.conn.State == 0:
      raise InterfaceError, 'Cannot create cursor - Connection is closed'
    return ADOCursor(self)


class ADOCursor:

  description = None
  rowcount    = -1
  arraysize   = -1

  def __init__(self, dbconn):
    self.closed = 0
    self.dbcon  = dbcon
    self.conn   = dbcon.conn
    self._input_sizes = None

  def _describe_results(self):
    fields = len(cursor._results.Fields)
    self.description = [None]*fields
    self._xlate_fields = []

    for i in range(fields):
      field  = self._results.Fields[i]
      name   = field.Name
      type   = field.Type
      isize  = field.DefinedSize
      dsize  = None

      if type == NUMBER:
        prec  = field.Precision
        scale = field.NumericScale
      else:
        prec  = None
        scale = None

      if type == STRING:
        dsize = isize

      if field.Attributes != adFldUnspecified:
        null_ok = field.Attributes & (adFldMayBeNull | adFldIsNullable) != 0
      else:
        null_ok = -1

      self.description[i] = (name, type, dsize, isize, prec, scale, null_ok)

      if type == DATETIME:
        self._xlate_fields.append( (i, xlate_datetime[type]) )

  def _prepare_query(self, operation, sizes):
    command = win32com.client.Dispatch('ADODB.Command')
    command.ActiveConnection = self.conn
    command.CommandText      = operation
    command.CommandType      = adCmdText
    command.Prepared         = 1

    # Create parameters
    for i in range(len(sizes)):
      name  = 'param%d' % i
      ctype = sizes[i][0]
      size  = sizes[i][1] or 0
      param = command.CreateParameter(name, ctype, adParamInput, size)
      command.Parameters.Append(param)

  def callproc(self, procname, *parameters):
    if self.closed or self.conn.State == 0:
      raise InterfaceError, 'Cannot call procedure - cursor or connection is closed'

  def close(self):
    if self.closed:
      raise InterfaceError, 'Cursor already closed'

    self.closed = 1
    self.dbcon  = None
    self.conn   = None

  def execute(self, operation, *parameters):

    if self.closed or self.conn.State == 0:
      raise InterfaceError, 'Cannot execute operation  - cursor or connection is closed'

    if parameters:
      raise NotSupportedError, 'Parameter binding is currently not supported'

    try:
      self._results,self.rowcount = self.conn.Execute(operation)

    except pywintypes.com_error, e:
      py_exception = _xlate_com_exception(e, ProgrammingError)
      raise py_exception

    self._describe_results()

  def executemany(self, operation, param_sequence):

    if self.closed or self.conn.State == 0:
      raise InterfaceError, 'Cannot execute operation  - cursor or connection is closed'

    raise NotSupportedError, 'Parameter binding is currently not supported'

    try:
      command = self._prepare_query(operation, None)

    except pywintypes.com_error, e:
      py_exception = _xlate_com_exception(e, ProgrammingError)
      raise py_exception

    for parameters in param_sequence:
      for param,value in zip(command.Parameters,parameters):
        param.Value = value
      self._results,self.rowcount = command.Execute()

  def fetchone(self):
    return self.fetchmany(1)[0]

  def fetchmany(self, size = None):
    if self.closed or self.conn.State == 0:
      raise InterfaceError, 'Cannot fetch rows - cursor or connection is closed'

    if size:
      rows = self._results.GetRows(size)
    else:
      rows = self._results.GetRows()

    return self._transposeResults(rows)

  def fetchall(self):
    return self.fetchmany()

  def nextset(self):
    if self.closed or self.conn.State == 0:
      raise InterfaceError, 'Cannot get next record set - cursor or connection is closed'

    self._results.NextRecordset()

  def setinputsizes(self, sizes):
    if self.closed or self.conn.State == 0:
      raise InterfaceError, 'Cursor or connection is closed'

    self._input_sizes = sizes

  def setoutputsize(size, column = None):
    if self.closed or self.conn.State == 0:
      raise InterfaceError, 'Cursor or connection is closed'

  def _transposeResults(self, rows):
    if not rows or not rows[0]:
      return []

    xlate = self._xlate_fields

    n = len(rows)
    m = len(rows[0])
    new_rows = [None]*m
    for i in range(m):
      row = [None]*n
      for j in range(n):
        row[j] = rows[j][i]

      for j,fun in xlate:
        row[j] = fun(row[j])

      new_rows[i] = tuple(row)

    return new_rows


def connect(dsn = '', **kwargs):
  return ADOConnection(dsn = '', **kwargs)


def main():
  import win32api
  import getpass

  driver = 'PostgreSQL'
  host = 'penguin'
  port = 5433
  db   = 'tracking_test'
  user = win32api.GetUserName()

  host   = raw_input('Database host   [%s]: ' % host)   or host
  port   = raw_input('Database port   [%s]: ' % port)   or port
  driver = raw_input('Database driver [%s]: ' % driver) or driver
  db     = raw_input('Database name   [%s]: ' % db)     or db
  user   = raw_input('User name       [%s]: ' % user) or user
  passwd = getpass.getpass('Password: ')

  dbcon = connect(driver = driver, database = db,
                    host = host, port = port,
                    user = user, password = passwd)

  cursor = dbcon.cursor()
  cursor.execute('SELECT * from orderdates;')

  print cursor.rowcount
  print cursor.fetchone()
  print cursor.description

  print 'Inserting:'
  cursor.execute('''INSERT INTO _Users (UName, LName, FName, EMail, Comments)
                                VALUES ('xdg', '', '', '', '');''')

  print cursor.rowcount

  print 'Updating:'
  cursor.execute('''UPDATE _Users SET LName='blah' WHERE UName='xdg';''')
  print cursor.rowcount

  dbcon.commit()

  print 'Deleting:'
  cursor.execute('''DELETE FROM _Users WHERE UName='xdg';''')
  print cursor.rowcount

  dbcon.commit()

if __name__ == '__main__':
  main()