Next / Previous / TCC home / NMT home

Source listing for db_helpers.py

logo
"""db_helpers.py:  Database helper functions
    $Revision: 1.13 $  $Date: 2009/06/03 21:46:07 $

    This module contains generic functions that must be supplanted by
    specific code for specific database interfaces.  You must provide:
    - class TheDatabase: See the model below
    - class Database(BasicDatabase):
      -- .__init__()
      -- .execute()
    - class Table(BasicTable):
      -- .exists()

    Exports:
      class DBHelpersError:  Our exception class
      class TheDatabase:  Represents the whole database (singleton)
      class BasicDatabase:  Represents the whole database;
        base class for Database
      class BasicTable:  Represents one table; base class for Table
      class QueryResult:  Represents all results from a query
      class SortSpec:  Helper object for specifying sorting of results
      class TableKey:  Helper object for specifying keys for a table
      class TableInteg:  Helper object to describe an integrity constraint
      class Column:  Represents one column (field) of a table
      class Row:  Represents one row (tuple) of a table
"""

#================================================================
# Imports
#----------------------------------------------------------------

import types            # Standard type names
import string           # Standard string module
import sys              # Standard system functions
import pickle           # For generic save/restore functions


#================================================================
# Manifest constants
#----------------------------------------------------------------

SQL_DEBUG = 0           # If set, SQL commands are displayed to stderr

#--
# Values for the `keyType' argument
#--
UNIQUE   =  "unique"
PRIMARY  =  "primary key"


# - - - - -   c l a s s   D B H e l p e r s E r r o r   - - - - -

class DBHelpersError(Exception):
    def __str__ ( self ):
        return "DBHelpers Error: " + Exception.__str__(self)


# - - -   T h e D a t a b a s e   - - -

def TheDatabase ( options ):
    """Singleton constructor for Database, prevents multiple connects.

        This is an example of the Singleton design pattern, as
        described in "Design Patterns" by Gamma et al.

        Your implementation will need to add this code:
----------------------------------------------------------------
    if  not Database.instance:
        Database.instance  =  Database(options)

    return Database.instance
----------------------------------------------------------------
    """
    raise DBHelpersError, "Caller must implement class TheDatabase"


# - - - - -   C l a s s   B a s i c D a t a b a s e   - - - - -

class BasicDatabase:
    """ Represents an entire database.

      Exports:
        BasicDatabase ( options )   *** VIRTUAL METHOD ***
          [ if options is a value appropriate to the particular
            database's connect method ->
              return a new Database object representing
              that database
            else -> raise DBHelpersError ]
        .execute ( sql ):      *** VIRTUAL METHOD ***
          [ if sql is a string ->
              if sql is a valid SQL command against self's ->
                self  :=  self with that command executed
                return whatever the DBAPI returned
              else ->
                raise an exception ]
        .oneRowQuery ( sql ):
          [ if (sql is a string) ->
              if (sql is a valid SQL query that returns one row) ->
                return that row as a tuple
              else -> return None ]
        .multiRowQuery ( sql ):
          [ if (sql is a string) ->
              if (sql is a valid SQL query that returns multiple rows) ->
                return those rows as a list of tuples
              else -> raise DBHelpersError ]
        .cursorQuery ( sql ):
          [ if (sql is a string) ->
              return a cursor object containing the result of executing sql ]
        .close():     [ close the connection ]

      State:
        .instance:
          [ if self's class has never been instantiated -> None
            else -> the unique instance of self ]
        .connect:  [ self's connection object ]
    """
    instance  =  None


# - - -   D a t a b a s e . _ _ i n i t _ _   - - -

    def __init__ ( self, options ):
        """ Constructor for Database

          VIRTUAL METHOD: The caller must provide the actual
          connection logic.
        """
        #-- 1 --
        self.options  =  options


# - - -   D a t a b a s e . e x e c u t e   - - -

    def execute ( self, sql ):
        """ Executes a line of SQL against self.

          VIRTUAL METHOD:  The caller must provide the actual
          execution logic.
        """
        if  SQL_DEBUG:
            sys.stderr.write("$$$ %s\n" % sql)
            sys.stderr.flush()


# - - -   D a t a b a s e . o n e R o w Q u e r y   - - -

    def oneRowQuery ( self, sql ):
        """Execute a query that returns one row
        """
        #-- 1 --
        # [ result  :=  a list of tuples resulting from query (sql) ]
        c  =  self.cursorQuery ( sql )
        result  =  c.fetchall()
        c.close()

        #-- 2 --
        # [ if result has length one ->
        #     return result[0]
        #   else ->
        #     return None ]
        if  len(result) == 1:
            return result[0]
        else:
            return None


# - - -   D a t a b a s e . m u l t i R o w Q u e r y   - - -

    def multiRowQuery ( self, sql ):
        """Execute a query that returns zero or more rows
        """
        #-- 1 --
        # [ result  :=  a list of tuples resulting from query (sql) ]
        c  =  self.cursorQuery ( sql )
        result  =  c.fetchall()
        c.close()

        #-- 2 --
        return result


# - - -   D a t a b a s e . c u r s o r Q u e r y   - - -

    def cursorQuery ( self, sql ):
        """Execute an sql statement and return its result as a cursor object.
        """
        if  SQL_DEBUG:
            sys.stderr.write("$$$ %s\n" % sql)
            sys.stderr.flush()

        c  =  self.connect.cursor ()
        if  c is None:
            raise DBHelpersError, ( "Database.cursorQuery(): "
              "could not create cursor. SQL is:\n%s" % sql )
            
        c.execute ( sql )
        return c

# - - -   D a t a b a s e . c l o s e   - - -

    def close ( self ):
        self.connect.close()


# - - -   s t r i n g i f y   - - -

def stringify ( value ):
    """Format an arbitrary value for SQL

      [ if value is numeric ->
          return value as a string
        if value is a string ->
          return value in double-quotes ]
    """
    #--
    # 2009-06-03: The old logic failed for unicode strings.
    # Test for stringness using 'isinstance(value,basestring)'.
    #--
    if isinstance(value, basestring):
        result = "'%s'" % escape(value)
    else:
        result = str(value)
    return result


# - - -   e s c a p e   - - -

def escape ( s ):
    """Render an arbitrary string s safe for use in SQL.

      [ if s is a string ->
          return s with all double-quote characters replaced with
          two double-quote characters ]
    """
    L  =  string.split ( s, "'" )
    return string.join ( L, "''" )


# - - - - -   C l a s s   B a s i c T a b l e   - - - - -

class BasicTable:
    """ Represents one database table.

      Exports:
        BasicTable ( db, name, columns, keyList=None, tabIntegList=None ):
          [ if (db is a Database object)
            and (name is a valid SQL name string naming the table)
            and (columns is a list of one or more Column objects
            representing the columns of a table)
            and (keyList is a list of TableKey objects representing
            one or more keys on the table as a whole)
            and (tabIntegList is None or a list of TableInteg objects
            representing integrity constraints on a table) ->
              return a Table object representing that table ]
        .db:            [ as passed to the constructor ]
        .name:          [ as passed to the constructor ]
        .columns:       [ as passed to the constructor ]
        .keyList:       [ as passed to the constructor ]
        .tabIntegList:  [ as passed to the constructor ]
        .colMap:
          [ a dictionary mapping N |-> C where N is each column name from
            self.columns and the corresponding C is the Column object
            from self.columns ]
        .colMapNo:
          [ a dictionary mapping N |-> K where N is each column name from
            self.columns and the corresponding K is the index of the column
            with that name in self.columns ]
        .create():
          [ if self's table exists in self.db ->
              raise an exception
            else ->
              create self's table in self.db ]
        .deleteContents():
          [ if self's table exists in self.db ->
              delete self's table contents in self.db
            else -> raise an exception ]
        .drop():
          [ if self's table exists in self.db ->
              delete self's entire table from self.db ]   
        .exists():   *** VIRTUAL METHOD ***
          [ if self's table exists in self.db -> return 1
            else -> return 0 ]
        .insert(valMap):
          [ if (valMap is a dictionary mapping N |-> V where each N
            is a column name in self.columns and each corresponding V
            is a value ->
              if a new row with column names from N and values from V
              can be inserted in self's table ->
                self's table  :=  self's table with that new row inserted
              else -> raise an exception ]
        .update(row,valMap):
          [ if (row is a Row object representing a row in self)
            and valMap is a dictionary mapping N |-> V where each N_i
            is the name of a column in self's table and each corresponding
            V_i is a value appropriate for that column's type ->
              if (self's table has at least one unique key) ->
                self's table  :=  self's table with the values of fields
                                  N in row replaced by the corresponding
                                  values from V
              else -> raise DBHelpersError ]
        .delete(row):
          [ if (row is a Row object representing a row in self)
            and (self's table has at least one column or table key ->
              self's table  :=  self's table with that row removed
            else -> raise DBHelpersError ]
        .queryExact(colPairs):
          [ if (colPairs is a sequence of two-element sequences (N_i,V_i)
            such that each N_i is the name of a column in self and each
            corresponding V_i is a value appropriate for column N_i ->
              if there is exactly one row in self, each of whose columns
              named N_i has the value V_i ->
                return that row as a Row object
              else -> return None ]
        .queryWild(colName, prefix, sortList=None):
          [ if (colName is the name of a string-valued column in self)
            and (prefix is a string)
            and (sortList is a list of SortSpec objects or None) ->
              return a QueryResult object representing the set of rows
              in self whose values in column colName start with prefix,
              ordered according to sortList if given else in random order ]
        .queryAll(sortList=None):
          [ if (sortList is a list of SortSpec objects or None) ->
              return a QueryResult object representing all the rows in self,
              ordered according to sortList if given else in random order ]
        .querySome(colPairs, sortList=None):
          [ if (colPairs is a sequence of two-element sequences (N_i,V_i)
            such that each N_i is the name of a column in self and each
            corresponding V_i is a value appropriate for column N_i)
            and (sortList is a list of SortSpec objects or None) ->
              return a QueryResult object representing the set of rows
              in self all of whose columns N_i have the value V_i,
              ordered according to sortList if given else in random order ]
        .unload ( fileName ):
          [ if (fileName is a string) ->
              if (fileName names a file that can be created new) ->
                that file  :=  self's table contents as a file of pickled
                rows in the format expected by .reload()
              else -> raise IOError ]
        .flatten(rowTuple, f):      # Override this method if you like
          [ if (row is a Row object in self)
            and (f is a writeable file handle) ->
              f  +:=  pickled fields from row ]
        .reload ( fileName ):
          [ if (fileName is a string) ->
              if (fileName names a readable, valid file of pickled rows) ->
                self  :=  self with the table reloaded from that file
              else ->
                self  :=  self + leading valid records from that file, if any
                raise IOError ]
        .inflate(f):                # Override this method if you like
          [ if (f is a readable file) ->
              if (f is at end of file) ->
                raise EOFError
              else if (f starts with a row as a sequence of pickled values) ->
                f  :=  f advanced past that record
                return that row as a tuple of field values
              else ->
                f  :=  f advanced past valid part, if any
                raise ValueError ]
        .mapToRow(rowMap):
          [ if (rowMap is a dictionary mapping N |-> V where each
            N_i is the name of a column in self and each corresponding
            V_i is a value appropriate for that column, and all required
            columns are defined in rowMap) ->
              if self has at least one column that is an index or one key
              on the table as a whole ->
                return a Row object representing the (N_i,V_i) pairs from
                rowMap, with any missing columns set to their default value
              else -> raise KeyError ]

      State/Invariants:
        .__keyCols:
          [ list of all members of self.columns which are unique or
            primary keys ]
        .__nConstr:
          [ number of constraint clauses used in this table so far ]
    """

# - - -   B a s i c T a b l e . _ _ i n i t   _ _   - - -

    def __init__ ( self, db, name, columns, keyList=None,
                   tabIntegList=None ):
        """ Constructor for Table
        """
        #-- 1 --
        self.db            =  db
        self.name          =  name
        self.columns       =  columns
        self.keyList       =  keyList
        self.tabIntegList  =  tabIntegList
        self.__keyCols     =  []
        self.__nConstr     =  0

        #-- 2 --
        # [ self.colMap    :=  as invariant
        #   self.colMapNo  :=  as invariant ]
        self.colMap    =  {}
        self.colMapNo  =  {}
        for i in range(len(columns)):
            col  =  columns[i]
            self.colMap[col.name]    =  col
            self.colMapNo[col.name]  =  i
            if  col.keyType:
                self.__keyCols.append ( col )


# - - -   B a s i c T a b l e . c r e a t e   - - -

    def create(self):
        """ Creates a table (the constructor just stores a description).
        """
        #-- 1 --
        # [ sql  :=  the SQL "create table" command to create self ]
        sql  =  ( "create table %s(%s)" %
                  ( self.name, self.__createBody() ) )

        #-- 2 --
        # [ if sql is a valid SQL command in the context of self ->
        #     self.db  :=  self.db with that sql executed
        #   else -> raise DBHelpersError ]
        self.db.execute ( sql )


# - - -   B a s i c T a b l e . _ _ c r e a t e B o d y   - - -

    def __createBody ( self ):
        """Return the column and constraint descriptions for self in SQL.

          [ return a <create-body> representing self
          ]
          For <create-body> and other syntactic entities, refer to the
          specification, "dbhelpers.tex".
        """

        #-- 1 --
        descList  =  []

        #-- 2 --
        # [ descList  :=  descList + (list of <column-desc> strings
        #                 representing self's columns in order) ]
        for  col in self.columns:
            descList.append ( self.__columnDesc ( col ) )

        #-- 3 --
        # [ descList  :=  descList + (list of <constraint-desc> strings
        #                 representing keys from self.keyList, if any ]
        if  self.keyList:
            for  key in self.keyList:
                descList.append ( self.__keyConstraint ( key ) )

        #-- 4 --
        # [ descList  :=  descList + (list of <constraint-desc> strings
        #                 representing integrity constraints from integList,
        #                 if any ]
        if  self.tabIntegList:
            for  integ in self.tabIntegList:
                descList.append ( self.__integConstraint ( integ ) )

        #-- 5 --
        return  string.join ( descList, ", " )


# - - -   B a s i c T a b l e . _ _ c o l u m n D e s c   - - -

    def __columnDesc ( self, col ):
        """Generate the SQL for describing one column in a "create table..."

          [ if (col is a Column object in self) ->
              return a <column-desc> representing col ]
        """

        #-- 1 --
        L  =  [col.name,col.kind]

        #-- 2 --
        if  not col.keyType:
            if  col.allowNulls and not col.keyType:
                L.append ( "null" )
            else:
                L.append ( "not null" )

        #-- 3 --
        if  col.keyType:
            L.append ( col.keyType )

        #-- 4 --
        # [ if col.colInteg is not None ->
        #     L  :=  L + (the <ref-clause> representing col.colInteg) ]
        #   else -> I ]
        if  col.colInteg:
            L.append ( "references %s(%s)" %
                       ( col.colInteg[0].name, col.colInteg[1] ) )

        #-- 5 --
        return  string.join ( L, " " )


# - - -   B a s i c T a b l e . _ _ k e y C o n s t r a i n t   - - -

    def __keyConstraint ( self, key ):
        """Returns a <key-constraint> representing a TableKey object

          [ if key is a TableKey object ->
              return a <constraint-desc> representing key ]

          Example: Suppose table.name="dancers", key.keyType is "unique", and
          key.colList is [ "panache", "verve", "elan"], and suppose
          also that there have already been 3 constraints defined
          for this table (so that self.__nConstr is initially 3).
          The SQL would be:
              constraint dancers_key_4 unique ( panache, verve, elan )
        """
        self.__nConstr  =  self.__nConstr + 1
        cName           =  "%s_key_%d" % ( self.name, self.__nConstr )
        return ( "constraint %s %s (%s)" %
                 ( cName, key.keyType,
                   string.join ( key.colList, ", " ) ) )


# - - -   B a s i c T a b l e . _ _ i n t e g C o n s t r a i n t   - - -

    def __integConstraint ( self, integ ):
        """Generate the SQL <ref-constraint> corresponding to integ.

          [ if integ is a TableInteg object ->
              return the <ref-constraint> representing integ ]  

          Example:  Suppose table.name is "dancers", integ.foreignTable
          is "staff", and integ.pairList is [("first","firstName"),
          ("last","lastName")], and self.__nConstr is 5.  The SQL is:
              constraint dancers_ref_6 foreign key (first, last)
              references staff(firstName, lastName)
        """
        #-- 1 --
        self.__nConstr  =  self.__nConstr + 1
        cName           =  "%s_ref_%d" % ( self.name, self.__nConstr )

        #-- 2 --
        # [ ourKeys    :=  list of the first elements from each 2-tuple of
        #                  integ.pairList
        #   theirKeys  :=  list of the second elements from each 2-tuple of
        #                  integ.pairList ]
        ourList    =  []
        theirList  =  []
        for  i in range(len(integ.pairList)):
            pair  =  integ.pairList[i]
            ourList.append ( pair[0] )
            theirList.append ( pair[1] )

        #-- 3 --
        return ( "constraint %s foreign key (%s) references %s(%s)" %
                 ( cName,
                   string.join(ourList, ", "),
                   integ.foreignTable.name,
                   string.join(theirList, ", ") ) )


# - - -   B a s i c T a b l e . d e l e t e C o n t e n t s   - - -

    def deleteContents ( self ):
        """ Delete contents, but not structure, of a Table.
        """
        self.db.execute ( "truncate table %s" % self.name )


# - - -   B a s i c T a b l e . d r o p   - - -

    def drop ( self ):
        """ Delete self's table from the database.
        """
        self.db.execute ( "drop table %s" % self.name )


# - - -   B a s i c T a b l e . e x i s t s   - - -

    def exists ( self ):
        """Does the table exist in the db?

          VIRTUAL METHOD:  You must provide code to detect table
          existence for the actual database used.
        """
        raise DBHelpersError, "Unimplemented function: Table.exists()"


# - - -   B a s i c T a b l e . i n s e r t   - - -

    def insert ( self, valMap ):
        """Insert a new row of name-value pairs from valMap
        """

        #-- 1 --
        # [ sql  :=  the sql to insert a new row of name-value pairs
        #            from valMap into self ]
        L       =  ["insert into %s(" % self.name]
        V       =  []       # Will be a list "v1, v2, ..., vn"
        prefix  =  ""
        
        for colName in valMap.keys():
            L.append ( prefix )
            L.append ( colName )
            V.append ( prefix )
            V.append ( stringify ( valMap[colName] ) )
            prefix  =  ", "

        L.append ( ") values (" )
        L.append ( string.join ( V, "" ) )
        L.append ( ")" )
        sql  =  string.join ( L, "" )

        #-- 2 --
        # [ self.db  :=  self.db with sql executed against it ]
        try:
            result = self.db.execute ( sql )
        except Exception, detail:
            raise DBHelpersError, ( "Table.insert() failed\n%s" %
                                    str(detail) )


# - - -   B a s i c T a b l e . u p d a t e   - - -

    def update ( self, row, valMap ):
        """Update an existing row in self's table.

          Notes:
            1. SQL syntax is:
                 update <table-name>
                   set {<col-name>=<value>}[, ...]
                   where {<x-col-name>=<x-value>}[, ...]
            2. To insure that only one row is modified, the x-col-names
               are taken from keys to self's table.  If there is no key,
               we raise an exception.
        """

        #-- 1 --
        # [ setClause  :=  the SQL "set..." clause for names and values
        #                  from colMap ]
        setClause  =  self.__setClause ( valMap )

        #-- 2 --
        # [ whereClause  :=  the SQL "where..." clause that uniquely
        #                    identifies self's row in its table ]
        whereClause  =  self.__rowWhereClause ( row )

        #-- 3 --
        sql  =  ( 'update %s %s %s' %
                  ( self.name, setClause, whereClause ) )

        #-- 4 --
        self.db.execute ( sql )


# - - -   T a b l e . _ _ s e t C l a u s e   - - -

    def __setClause ( self, colMap ):
        """Builds the SQL "set..." clause from a name |-> value map

          [ if (colMap is a dictionary mapping N |-> V where each N_i
            is a name of a column in self's table and each corresponding
            V_i is a value with a type appropriate for that column ->
              return the SQL "set..." clause for names and values
              from colMap ]
        """
        L       =  [ "set" ]
        prefix  =  ""

        for  n, v in colMap.items():
            L.append ( prefix )
            prefix  =  ", "

            L.append ( "%s=%s" % ( n, stringify ( v ) ) )

        return string.join ( L, " " )



# - - -   B a s i c T a b l e . d e l e t e   - - -

    def delete ( self, row ):
        """Delete a row from self.

          SQL syntax:
            delete <table-name> where {<x-col-name>=<x-value>}[, ...]
        """
        #-- 1 --
        whereClause  =  self.__rowWhereClause ( row )

        #-- 2 --
        sql  =  ( 'delete from %s %s' %
                  ( self.name, whereClause ) )

        #-- 3 --
        self.db.execute ( sql )


# - - -   B a s i c T a b l e . q u e r y E x a c t   - - -

    def queryExact ( self, colPairs ):
        """Issue a query for exact matches in one or more column.
        """
        #-- 1 --
        # [ whereClause  :=  a <sel-cond> representing colPairs ]
        whereClause  =  self.__buildWhereClause ( colPairs )

        #-- 2 --
        # [ sql  :=  an SQL select command with whereClause ]
        sql  =  ( "select * from %s %s" % ( self.name, whereClause ) )

        #-- 3 --
        # [ if sql results in exactly one row ->
        #     rowTuple  :=  the result of that query as a row tuple 
        #   else -> return None ]
        rowTuple  =  self.db.oneRowQuery ( sql )
        if  rowTuple is None:
            return None

        #-- 4 --
        # [ return rowTuple made into a Row object ]
        return Row ( self, rowTuple )


# - - -   B a s i c T a b l e . _ _ b u i l d W h e r e C l a u s e   - - -

    def __buildWhereClause ( self, colPairs ):
        """Construct a <sel-cond> from a list of (name,value) pairs

          [ if (colPairs is a sequence of two-element sequences (N_i,V_i)
            such that each N_i is the name of a column in self and each
            corresponding V_i is a value appropriate for column N_i ->
              return a <sel-cond> representing colPairs ]
        """
        #-- 1 --
        L       =  [ "where" ]
        prefix  =  ""

        #-- 2 --
        # [ L  +:=  strings of the form "name=value" from colPairs ]
        for  colName, colValue in colPairs:
            L.append ( prefix )
            prefix  =  "and"
            L.append ( "%s=%s" %
                       ( colName, stringify ( colValue ) ) )

        #-- 3 --
        return string.join ( L, " " )


# - - -   B a s i c T a b l e . q u e r y W i l d   - - -

    def queryWild ( self, colName, prefix, sortList=None ):
        """Do a wild-card query, returning a QueryResult object
        """
        #-- 1 --
        # [ col  :=  the Column object corresponding to name colName in self ]
        col  =  self.colMap[colName]

        #-- 2 --
        # [ if sortList is a list of SortSpec objects describing a sort
        #   order ->
        #     orderClause  :=  the SQL "order by..." clause corresponding
        #                      to sortList
        #   else ->
        #     orderClause  :=  "" ]
        orderClause  =  self.__buildOrderClause ( sortList )

        #-- 3 --
        sql  =  ( "select * from %s where %s like '%s%%' %s" %
                  ( self.name, colName, prefix, orderClause ) )

        #-- 4 --
        cursor  =  self.db.cursorQuery ( sql )

        #-- 5 --
        return QueryResult ( self, cursor )


# - - -   B a s i c T a b l e . _ _ b u i l d O r d e r C l a u s e   - - -

    def __buildOrderClause ( self, sortList ):
        """Build an SQL "order by..." clause from sortList

          [ if sortList is a list of SortSpec objects describing a sort
            sequence ->
              return an SQL "order by..." clause representing sortList
            else if sortList is false ->
              return "" ]
        """
        #-- 1 --
        if  not sortList:
            return ""

        #-- 2 --
        # [ L  :=  list of words that, when joined without intervening
        #          spaces, form the desired SQL clause ]
        L       =  [ "order by " ]
        prefix  =  ""

        for  sortSpec in sortList:
            L.append ( prefix )
            prefix  =  ", "
            L.append ( sortSpec.colName )
            if  sortSpec.desc:
                L.append ( sortSpec.colName )

        #-- 3 --
        return string.join ( L, "" )


# - - -   B a s i c T a b l e . q u e r y A l l   - - -

    def queryAll ( self, sortList=None ):
        """Return all rows of self's table as a QueryResult
        """

        #-- 1 --
        # [ if sortList is a list of SortSpec objects specifying a
        #   sort order ->
        #     orderClause  :=  the SQL "order by..." clause representing
        #                      sortList
        #   else ->
        #     orderClause  :=  "" ]
        orderClause  =  self.__buildOrderClause ( sortList )

        #-- 2 --
        # [ sql  :=  the SQL query to select all rows from self, with
        #            orderClause ]
        sql  =  ( 'select * from %s %s' %
                  ( self.name, orderClause ) )

        #-- 3 --
        # [ cursor  :=  a DBAPI Cursor object representing sql ]
        cursor  =  self.db.cursorQuery ( sql )

        #-- 4 --
        return QueryResult ( self, cursor )


# - - -   B a s i c T a b l e . q u e r y S o m e   - - -

    def querySome ( self, colPairs, sortList=None ):
        """Multi-row query by column value(s).

          SQL generated:
            select * from <table-name>
            where <col>=<value> [, <col>=<value> ]...
            [ order by <col> [desc] [, <col> desc] ... ]
        """
        #-- 1 --
        # [ whereClause  :=  a <sel-cond> representing colPairs ]
        whereClause  =  self.__buildWhereClause ( colPairs )

        #-- 2 --
        # [ orderClause  :=  a <sort-clause> representing sortList,
        #                    or "" if sortList is None ]
        sortClause  =  self.__buildOrderClause ( sortList )

        #-- 3 --
        # [ sql  :=  the SQL query as described above ]
        sql  =  ( 'select * from %s %s %s' %
                  ( self.name, whereClause, sortClause ) )

        #-- 4 --
        # [ return a QueryResult object containing the results of sql ]
        cursor  =  self.db.cursorQuery ( sql )
        return QueryResult ( self, cursor )


# - - -   B a s i c T a b l e . _ _ r o w W h e r e C l a u s e   - - -

    def __rowWhereClause ( self, row ):
        """Return an SQL "where..." clause uniquely identifying row

          [ if (row is a Row object in self's table) ->
              if self has at least one column or table key ->
                return an SQL "where k_0=v_0[, k_1=v_1]..." clause with the
                k_i taken from some index and the corresponding v_i
                taken from row
              else ->
                raise DBHelpersError ]
        """

        #-- 1 --
        # [ if self.__keyCols is nonempty ->
        #     pairList  :=  [(k_0, v_0)] where k_0 is the name from
        #                   the first column in self.__keyCols and v_0
        #                   is the value of that column from row
        #   else if self.keyList is a nonempty list ->
        #     pairList  :=  [(k_0, v_0), (k_1, v_1), ...] where the k_i
        #                   are taken from the first element of self.keyList
        #                   and the corresponding v_i are taken from row
        #   else -> raise DBHelpersError ]
        if  self.__keyCols:
            pairList  =  [ self.__buildColWhere ( self.__keyCols[0], row ) ]
        elif self.keyList:
            pairList  =  self.__buildTableWhere ( self.keyList[0], row )
        else:
            raise DBHelpersError, ( "Table %s must have at least one key "
              "in order to update or delete a row." % self.name )

        #-- 2 --
        # [ L  :=  a list of strings which, when joined with one space
        #          between each element, is the SQL "where..." clause
        #          representing pairList ]
        prefix  =  ""
        L       =  [ "where " ]

        for  name, value in pairList:
            L.append ( prefix )
            prefix  =  " and "

            L.append ( "%s=%s" % ( name, stringify ( value ) ) )

        #-- 3 --
        return  string.join ( L, "" )


# - - -   B a s i c T a b l e . _ _ b u i l d C o l W h e r e   - - -

    def __buildColWhere ( self, col, row ):
        """Returns a pair (col.name,value) where the value comes from row

          [ if (col is a Column in self) and (row is a Row in self) ->
              return (n,v) where n is the name from col and v is the
              value from row corresponding to column n ]
        """
        return ( col.name, row.get ( col.name ) )


# - - -   B a s i c T a b l e . _ _ b u i l d T a b l e W h e r e   - - -

    def __buildTableWhere ( self, tableKey, row ):
        """Build a list of (name,value) pairs from a TableKey and a Row

          [ if (tableKey is a TableKey object from self.keyList)
            and (row is a Row object in self) ->
              return a list [(n_0,v_0), (n_1,v_1), ...] where the n_i
              are the names from tableKey.colList and each corresponding
              v_i is the value from row for the column with that name ]
        """

        #-- 1 --
        L  =  []

        #-- 2 --
        for  colName in tableKey.colList:
            L.append ( ( colName, row.get ( colName ) ) )

        #-- 3 --
        return L


# - - -   B a s i c T a b l e . u n l o a d   - - -

    def unload ( self, fileName ):
        """Write contents of self's table to a file, using Python pickling
        """

        #-- 1 --
        # [ if (fileName can be created new) ->
        #     f  :=  a writeable file handle for fileName
        #   else -> raise IOError ]
        f  =  open ( fileName, "w" )

        #-- 2 --
        # [ self.db  :=  self.db with a query executed that returns
        #                all rows ]
        sql  =  'select * from %s' % self.name
        c    =  self.db.cursorQuery ( sql )

        #-- 3 --
        # [ f  +:=  pickled rows from c ]
        rowTuple  =  c.fetchone()

        while  rowTuple is not None:
            self.flatten ( rowTuple, f )
            rowTuple  =  c.fetchone()

        #-- 4 --
        c.close()


# - - -   B a s i c T a b l e . f l a t t e n   - - -

    def flatten ( self, rowTuple, f ):
        """Write a pickled row to the file.
        """
        p  =  pickle.Pickler ( f )

        for field in rowTuple:
            p.dump ( field )


# - - -   B a s i c T a b l e . r e l o a d   - - -

    def reload ( self, fileName ):
        """Reload contents of self's table from a pickled file
        """

        #-- 1 --
        # [ if (fileName names a file that can be opened for reading) ->
        #     f  :=  a readable file object pointing to fileName
        #   else -> raise IOError ]
        f  =  open ( fileName )

        #-- 2 --
        # [ if  f consists of valid pickled rows ->
        #     f     :=  f advanced to end of file
        #     self  :=  self with records appended from that file
        #   else ->
        #     f     :=  f advanced past valid-looking part
        #     self  :=  self with leading valid records appended
        #     raise IOError ]
        self.__unpickleFile ( f )


# - - -   B a s i c T a b l e . _ _ u n p i c k l e F i l e   - - -

    def __unpickleFile ( self, f ):
        """Load self's table from a pickled file

          [ if  f consists of valid pickled rows ->
              f     :=  f advanced to end of file
              self  :=  self with records appended from that file
            else ->
              f     :=  f advanced past valid-looking part
              self  :=  self with leading valid records appended
              raise IOError ]
        """
        done  =  0

        while  not done:
            # [ if f is at EOF ->
            #     done  :=  1
            #   else if f starts with a valid pickled row ->
            #     f     :=  f advanced past that row
            #     self  :=  self with a new row added from f ]
            #   else ->
            #     f  :=  f possibly advanced
            #     raise IOError ]
            try:
                self.inflate ( f )
            except EOFError:
                done  =  1


# - - -   B a s i c T a b l e . i n f l a t e   - - -

    def inflate ( self, f ):
        """Read and add one pickled row

          [ if f is at EOF ->
              raise EOFError
            else if f starts with a valid pickled row ->
              f     :=  f advanced past that row
              self  :=  self with that row added
            else ->
              f  :=  f possibly advanced
              raise IOError ]
        """

        #-- 1 --
        # [ if f is at EOF ->
        #     raise EOFError
        #   else if f starts with as many pickled values as columns
        #   in self ->
        #     f       :=  f advanced past those pickled values
        #     rowMap  :=  a dictionary mapping column names in self |->
        #                 corresponding pickled values from f
        #   else ->
        #     f  :=  f possibly advanced
        #     raise IOError ]
        rowMap  =  self.__readPickles ( f )

        #-- 2 --
        # [ self  :=  self with a new row added made from rowMap ]
        self.insert ( rowMap )


# - - -   B a s i c T a b l e . _ _ r e a d P i c k l e s   - - -

    def __readPickles ( self, f ):
        """Read a row's worth of pickled values from f.

          [ if f is at EOF ->
              raise EOFError
            else if f starts with as many pickled values as columns
            in self ->
              f       :=  f advanced past those pickled values
              rowMap  :=  a dictionary mapping column names in self |->
                          corresponding pickled values from f
            else ->
              f  :=  f possibly advanced
              raise IOError ]
        """
        #-- 1 --
        # [ rowMap     :=  a new, empty dictionary
        #   unpickler  :=  a new Unpickler object reading from f ]
        rowMap     =  {}
        unpickler  =  pickle.Unpickler(f)

        #-- 2 --
        # [ if unpickler is at EOF ->
        #     raise EOFError
        #   else if unpickler starts with as many pickled values as
        #   columns in self ->
        #     unpickler  :=  unpickler advanced by that many values
        #     rowMap     :=  rowMap with entries added whose keys are
        #       the column names in self and whose values are the
        #       corresponding unpickled objects from unpickler
        #   else ->
        #     unpickler  :=  unpickler possibly advanced
        #     raise IOError ]
        for colx in range ( len ( self.columns ) ):
            self.__unpickleValue ( unpickler, rowMap, colx )

        #-- 3 --
        return rowMap


# - - -   B a s i c T a b l e . _ _ u n p i c k l e V a l u e   - - -

    def __unpickleValue ( self, unpickler, rowMap, colx ):
        """Unpickle a single field from a saved row.

          [ if (unpickler is an Unpickler object)
            and (rowMap is a dictionary)
            and (colx is a column number in self) ->
              if (unpickler is at EOF) and (colx is 0) ->
                raise EOFError
              else if unpickler is at EOF ->
                raise IOError
              else if unpickler does not start with a valid pickled value ->
                raise IOError
              else ->
                unpickler  :=   unpickler advanced past the next value
                rowMap     +:=  a new entry whose key is the (colx)th
                  column name in self and whose value is the next
                  pickled value from unpickler ]
        """
        try:
            value    =  unpickler.load()
            colName  =  self.columns[colx].name
            rowMap[colName]  =  value
        except pickle.PicklingError, detail:
            raise IOError, "Unpickling error: %s" % detail
        except EOFError:
            if  colx == 0:
                raise EOFError
            else:
                raise IOError, ( "Last row has %d pickles, "
                    "should have %d" %
                    ( colx, len ( self.columns ) ) )


# - - -   B a s i c T a b l e . m a p T o R o w   - - -

    def mapToRow ( self, rowMap ):
        """Convert a dictionary of colName |-> value mappings to a Row object
        """

        #-- 1 --
        # [ rowList  :=  a list containing as many None values as columns
        #                in self ]
        rowList  =  [None] * len(self.columns)

        #-- 2 --
        # [ rowList  :=  rowList with each element corresponding to a column
        #                whose name is a key in rowMap replaced by the
        #                corresponding value from rowMap ]
        for  colName in rowMap.keys():

            #-- 2.1 --
            # [ colx  :=  the index in self.columns for column `colName'
            colx  =  self.colMapNo[colName]

            #-- 2.2 --
            rowList[colx]  =  rowMap[colName]

        #-- 3 --
        return Row ( self, rowList )


# - - - - -   c l a s s   Q u e r y R e s u l t   - - - - -

class QueryResult:
    """Represents a query result from a table, with lazy evaluation.

      Exports:
        QueryResult ( table, cursor ):
          [ if (table is a Table object)
            and (cursor is a DBAPI cursor object for a query against
            that table) ->
              return a new QueryResult object representing the results
              in that cursor ]
        .next ( ):
          [ if self contains at least one more row ->
              self  :=  self - the next row
              return the next row as a Row object
            else ->
              raise DBHelpersError ]
        .close ( ):
          [ if self is exhausted -> I
            else ->
              self.cursor  :=  self.cursor, closed ]

      State/Invariants:
        .table:       [ as passed to constructor ]
        .cursor:
          [ if self is still active ->
              the cursor passed to the constructor, modified by
              retrievals caused by .next() calls since instantation
            else -> None ]
    """

# - - -   Q u e r y R e s u l t . _ _ i n i t _ _   - - -

    def __init__ ( self, table, cursor ):
        """Constructor for a QueryResult
        """
        self.table   =  table
        self.cursor  =  cursor


# - - -   Q u e r y R e s u l t . n e x t   - - -

    def next ( self ):
        """Returns the next result from self, if any
        """
        if  self.cursor is None:
            raise DBHelpersError, ( "QueryResult.next() called after "
              "results exhausted." )

        try:
            rowTuple  =  self.cursor.fetchone()
        except IndexError:
            return None

        if  rowTuple:
            return Row ( self.table, rowTuple )
        else:
            self.cursor  =  None
            return None


# - - -   Q u e r y R e s u l t . _ _ d e l _ _   - - -

    def __del__ ( self ):
        """Destructor for the QueryResult object
        """
        self.close()


# - - -   Q u e r y R e s u l t . c l o s e   - - -

    def close ( self ):
        if  self.cursor:
            self.cursor.close()
            self.cursor  =  None


# - - - - -   c l a s s   S o r t S p e c   - - -

class SortSpec:
    """Represents one sort key for ordering results from a `select...'

      Exports:
        SortSpec ( colName, desc=0 ):
          [ if (colName is a string containing a column name)
            and (desc is true for descending order, false for ascending) ->
              return a new SortSpec object with those values ]
        .colName:       [ as passed to constructor ]
        .desc:          [ as passed to constructor ]
    """

    def  __init__ ( self, colName, desc=0 ):
        """Constructor for SortSpec 
        """
        self.colName  =  colName

        if  desc:   self.desc  =  1
        else:       self.desc  =  0


# - - - - -   c l a s s   T a b l e K e y   - - - - -

class TableKey:
    """Represents one key on a table as a whole (involving multiple columns)

      Exports:
        TableKey ( keyType, colList ):
          [ if (keyType is UNIQUE or PRIMARY)
            and (colList is a list of field names as strings) ->
              return a new TableKey object with key type keyType and
              fields as named in colList in the same order ]
        .keyType:   [ as passed to constructor ]
        .colList:   [ as passed to constructor ]
    """

    def __init__ ( self, keyType, colList ):
        """Constructor for a TableKey
        """
        self.keyType  =  keyType
        self.colList  =  colList

    
# - - - - -   c l a s s   T a b l e I n t e g   - - - - -

class TableInteg:
    """Represents one foreign key integrity constraint on a table.

      Exports:
        TableInteg ( foreignTable, pairList ):
          [ if (foreignTable is a Table object)
            and (pairList is a sequence of 2-element sequences (l_i, f_i)
            where each l_i is a local field name and each corresponding
            f_i is a field name in foreignTable) ->
              return a new TableInteg object with those values ]
        .foreignTable:  [ as passed to constructor ]
        .pairList:      [ as passed to constructor ]
    """

    def __init__ ( self, foreignTable, pairList ):
        """Constructor for a TableInteg
        """
        self.foreignTable  =  foreignTable
        self.pairList      =  pairList


# - - - - -   c l a s s   C o l u m n   - - - - -

class Column:
    """ Represents one column (field) of a table.

      Exports:
        Column ( name, kind, allowNulls=0, keyType=None, colInteg=None ):
          [ if (name is a field name as a string)
            and (kind is an SQL type as a string)
            and (allowNulls is true if nulls are allowed, else false)
            and (keyType is None if not a key, or UNIQUE or PRIMARY if a key)
            and (colInteg is None if there are no referential integrity
            constraints, or a two-element sequence (t,f) where t is
            a foreign table and f is the name of a field in that table) ->
              return a Column object representing those values ]
        .name:          [ as passed to constructor ]
        .kind:          [ as passed to constructor ]
        .allowNulls:    [ as passed to constructor ]
        .keyType:       [ as passed to constructor ]
        .colInteg:      [ as passed to constructor ]
    """


# - - -   C o l u m n . _ _ i n i t _ _   - - -

    def __init__ ( self, name, kind, allowNulls=0, keyType=None,
                   colInteg=None ):
        """ Constructor for a Column
        """
        self.name        =  name
        self.kind        =  kind
        self.allowNulls  =  allowNulls
        self.keyType     =  keyType
        self.colInteg    =  colInteg


# - - - - -   c l a s s   R o w   - - - - -

class Row:
    """Represents one row of a table.

      Exports:
        Row ( table, rowTuple ):
          [ if (table is a Table object)
            and (rowTuple is a sequence of field values in the same order as
            the columns in that table ->
              return a new Row object representing those values ]
        .table:     [ as passed to constructor ]
        .get ( colName ):
          [ if colName is a string ->
              if colName is the name of a column in self's table ->
                return the value of that column of self
             else -> raise DBHelpersError ]
        .update ( colMap ):
          [ if (colMap is a dictionary mapping N |-> V where each N_i
            is the name of a column in self's table and each corresponding
            V_i is a value appropriate for that column's type ->
              if (self's table has at least one unique key) ->
                self's table  :=  self's table with the values of fields
                                  N in self's row replaced by the
                                  corresponding values from V
              else -> raise DBHelpersError ]
        .delete ( ):
          [ if self's table has at least one column or table key ->
              self's table  :=  self's table with self's row removed
            else -> raise DBHelpersError ]
        .__getitem__ ( self, colName ):
          [ if colName is the name of a column in self's table ->
              return the value of that column from self
            else -> raise DBHelpersError ]
        .__str__ ( self ):
          [ return a stringified form of self ]

      State/invariants:
        .map:
          [ a dictionary mapping N |-> V where N is every column's name
            and V is the corresponding value ]
    """

# - - -   R o w . _ _ i n i t _ _   - - -

    def __init__ ( self, table, rowTuple ):
        """Constructor for a Row object
        """
        #-- 1 --
        self.table  =  table
        self.map    =  {}

        #-- 2 --
        # [ self.map  :=  self.map with entries N |-> V added, where
        #                 N is each column name from table, and V
        #                 is the corresponding value from rowTuple ]
        for colNo in range(len(table.columns)):
            column   =  table.columns[colNo]
            colName  =  column.name
            self.map[colName]  =  rowTuple[colNo]


# - - -   R o w . g e t   - - -

    def get ( self, colName ):
        """Retrieve the named field from self.
        """
        return self[colName]


# - - -   R o w . u p d a t e   - - -

    def update ( self, colMap ):
        """Modify an existing row in the table.
        """
        self.table.update ( self, colMap )



# - - -   R o w . d e l e t e   - - -

    def delete ( self ):
        """Deletes self's row from the table.
        """
        self.table.delete ( self )


# - - -   R o w . _ _ g e t i t e m _ _   - - -

    def __getitem__ ( self, key ):
        """Retrieve a column by column name expressed as self[colName]
        """
        try:
            value  =  self.map[key]
            return value
        except KeyError:
            raise DBHelpersError, ( "Row[]: Unknown column name `%s'" % key )


# - - -   R o w . _ _ s t r _ _   - - -

    def __str__ ( self ):
        """Display self as a string
        """
        L  =  []

        keyList  =  self.map.keys()
        keyList.sort()

        for key in keyList:
            L.append ( "%s=<%s>" %
                       ( string.rstrip(key),
                         string.rstrip(str(self.map[key])) ) )

        return string.join ( L, "  " )

See also: Python database helper functions
TCC home: TCC home
NMT home: NMT home

Last updated: 2014-12-21 12:28 MST