Next / Previous / TCC home / NMT home

Source listing for my_db.py

logo
"""my_db.py:  MySQL version of `dbhelpers' database interface

    Exports all functions from `db_helpers.py', which see.

    Contents:
      class DBRefIntError(DBHelpersError):  Referential integrity exception
      class TheDatabase:  Connection object
      class HalfInteg:  Represents one end of a referential integrity
      class FullInteg:  Represents both ends of a ref-int
      class Table(BasicTable):  Customization for MySQL
"""

VERSION = "$Revision: 1.5 $  $Date: 2000/11/16 22:08:36 $"

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

import string, copy         # Standard Python modules
from db_helpers import *    # Basic database interface functions
import MySQLdb              # MySQL interface

SQL_DEBUG  =  0             # Display SQL commands if set


#================================================================
# Verification functions
#----------------------------------------------------------------
# link-columns(halfInteg, colMap) ==
#   the sequence [colMap[halfInteg.colList[0]],
#   colMap[halfInteg.colList[1]], ...]
#--
#   At either end of a referential integrity constraint there is
#   a table, and a set of columns whose values are compared
#   against a set of columns from the table at the other end.
#   This function describes how to assemble the sequence of
#   column values given a HalfInteg object `halfInteg' and
#   a dictionary mapping column names |-> column values.  Such a
#   dictionary is used to represent a new row for Table.insert()
#   and also a set of changed field values for Table.update().
#----------------------------------------------------------------


# - - - - -   c l a s s   D B R e f I n t E r r o r   - - - - -

class DBRefIntError(DBHelpersError):
    """New exception for referential integrity failure
    """
    def __str__ ( self ):
        return "DBRefIntError: " + Exception.__str__(self)


# - - - - -   c l a s s   H a l f I n t e g   - - - - -

class HalfInteg:
    """One of the two tables linked by a referential-integrity constraint.

      Exports:
        HalfInteg ( table, colList ):
          [ if (table is a Table object in TheDatabase)
            and (colList is a list of names of columns in that table) ->
              return a new HalfInteg object representing that table
              and those columns ]
        .table:     [ as passed to constructor ]
        .colList:   [ as passed to constructor ]
        .linkValues(colMap):
          [ if (colMap is dictionary mapping column names in self.table
            |-> appropriate values for those columns) ->
              return a sequence containing the field values from
              the colMap corresponding to self.colList, in the same order ]
        str():      [ return self in string form ]
    """

# - - -   H a l f I n t e g . _ _ i n i t _ _   - - -

    def __init__ ( self, table, colList ):
        """Constructor for a HalfInteg object
        """
        self.table    =  table
        self.colList  =  colList


# - - -   H a l f I n t e g . l i n k V a l u e s   - - -

    def linkValues ( self, colMap ):
        """Returns the values from colMap corresponding to self.colList
        """
        result  =  []
        for colName in self.colList:
            result.append ( colMap[colName] )

        return result


# - - -   H a l f I n t e g . _ _ s t r _ _   - - -

    def __str__ ( self ):
        """Return self as a string
        """
        return ( "%s(%s)" %
                 ( self.table.name,
                   string.join ( self.colList, "," ) ) )


# - - - - -   c l a s s   F u l l I n t e g   - - - - -

class FullInteg:
    """Represents both ends of a referential-integrity constraint

      Exports:
        FullInteg ( parent, child ):
          [ if (parent is a HalfInteg describing the parent end of
            a ref-int constraint) and (child is a HalfInteg describing
            the child end) ->
              return a new FullInteg representing both ends ]
        .parent:    [ as passed to constructor ]
        .child:     [ as passed to constructor ]
        str():      [ return self in string form ]
    """


# - - -   F u l l I n t e g . _ _ i n i t _ _   - - -

    def __init__ ( self, parent, child ):
        """Constructor for a FullInteg
        """
        self.parent  =  parent
        self.child   =  child


# - - -   F u l l I n t e g . _ _ s t r _ _   - - -

    def __str__ ( self ):
        """Return self in displayable form
        """
        return ( "RefInt: parent=%s; child=%s" %
                 ( str(self.parent), str(self.child) ) )


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

def TheDatabase ( options ):
    """Singleton database connection object
    """
    if  not Database.instance:
        Database.instance  =  Database(options)

    return Database.instance


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

class Database(BasicDatabase):
    """MySQL implementation of db_helpers.Database

      Exports everything from BasicDatabase, except:
        Database ( options )
          [ if (options is a dictionary with keys "host", "user",
            "password", and "db" ->
              if MySQL will accept that connection ->
                return a new Database object representing
                that database
              else -> raise DBHelpersError ]

      Additional state/invariants:
        - All records involved in referential constraints (as parent
          or child) do not violate the referential integrity relative
          to the parent or child file.
    """


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

    def __init__ ( self, options ):
        """ Constructor for Database
        """
        #-- 1 --
        # [ call the parent constructor ]
        BasicDatabase.__init__ ( self, options )

        #-- 2 --
        # [ if options is a valid dictionary for a MySQL connect ->
        #     self.connect  :=  a MySQLdb connect object for that connection
        #   else ->
        #     raise DBHelpersError ]
        try:
            self.connect  =  apply(MySQLdb.connect, tuple([]), options)
        except Exception, detail:
            raise DBHelpersError, ( "MySQL error: %s" % detail )


# - - -   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.
        """
        if  SQL_DEBUG:
            sys.stderr.write("$$$ %s\n" % sql)

        # NB: Some operations can be executed from the connection
        # object and some must be executed from cursors.  Check
        # your database's documentation.
        c  =  self.connect.cursor()
        result  =  c.execute ( sql )
        c.close()
        return result


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

class Table(BasicTable):
    """MySQL implementation of BasicTable

      Exports: all functions of BasicTable.

      Additional state/invariants:
        .integParents:
          [ list of FullInteg objects representing referential integrity
            constraints for which self is the child ]
        .integChildren:
          [ list of FullInteg objects representing referential integrity
            constraints for which self is the parent ]
    """


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

    def exists ( self ):
        """Does the table exist in the db?
        """
        #-- 1 --
        # [ tableList  :=  a list of all tables in self's db ]
        c          =  self.db.cursorQuery ( "show tables" )
        tableList  =  c.fetchall()

        #-- 2 --
        # [ if self.name is in tableList -> return 1
        #   else -> return 0 ]
        for row in tableList:
            if row[0] == self.name:
                return 1

        return 0


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

    def deleteContents ( self ):
        """Delete the contents of a table, but leave the table structure.

            This overrides the stock version that uses the syntax:
                TRUNCATE TABLE tableName
            Ref: "MySQL Reference", section 7.10, says that the syntax
                DELETE FROM tableName [WHERE ...]
            will quickly recreate the table as empty if there is
            no WHERE clause.
        """
        self.db.execute ( "delete from %s" % self.name )


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

    def __init__ ( self, db, name, columns, keyList=None,
                   tabIntegList=None ):
        """Additional initialization for referential integrity
        """
        #-- 1 --
        # [ call parent constructor ]
        BasicTable.__init__ ( self, db, name, columns, keyList,
                              tabIntegList)

        #-- 2 --
        self.integParents   =  []
        self.integChildren  =  []

        #-- 3 --
        # [ if tabIntegList is None ->
        #     I
        #   else ->
        #     add ref-ints from tabIntegList to all foreign tables
        #     as parents and to self as children ]
        if  tabIntegList:
            for  tabInteg in tabIntegList:
                # [ add tabInteg to its foreign table as a parent
                #   and to self as a child ]
                self.__addTabInteg ( tabInteg )

        #-- 4 --
        # [ add ref-ints from columns, if any, to foreign tables as
        #   parents and to self as children ]
        for  col in columns:
            if  col.colInteg:
                # [ add col.colInteg to its foreign table as a parent
                #   and to self as a child ]
                self.__addColInteg ( col )


# - - -   T a b l e . _ _ a d d T a b I n t e g   - - -

    def __addTabInteg ( self, tabInteg ):
        """Add a table-wide referential integrity to self and parent

          [ if (tabInteg is a TabInteg object) ->
              add a ref-int representing tabInteg to its foreign table
              as a parent and to self as a child ]
        """
        #-- 1 --
        # [ parentCols  :=  list of parent's ref-int column names
        #   childCols   :=  list of child's ref-int column names ]
        parentCols  =  []
        childCols   =  []

        for  c, p in tabInteg.pairList:
            parentCols.append ( p )
            childCols.append ( c )

        #-- 2 --
        full =  FullInteg ( HalfInteg ( tabInteg.foreignTable, parentCols ),
                            HalfInteg (self, childCols ) )

        #-- 3 --
        # [ add full to tabInteg.foreignTable as a parent and to
        #   self as a child ]
        tabInteg.foreignTable.integChildren.append ( full )
        self.integParents.append ( full )


# - - -   T a b l e . _ _ a d d C o l I n t e g   - - -

    def __addColInteg ( self, col ):
        """Add a single-column referential integrity to self and parent

          [ if col is a Column object in self with a nonempty
            .colInteg ->
              add a ref-int representing col.colInteg to its foreign
              table as a parent and to self as a child ]
        """
        #-- 1 --
        parentTable, parentCol  =  col.colInteg

        #-- 2 --
        full  =  FullInteg ( HalfInteg ( parentTable, [parentCol] ),
                             HalfInteg ( self, [col.name] ) )

        #-- 3 --
        # [ add full to parentTable as a parent and self as a child ]
        parentTable.integChildren.append ( full )
        self.integParents.append ( full )

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

    def insert ( self, valMap ):
        """Row insertion, MySQL version.  Checks ref-int parents.
        """

        #-- 1 --
        # [ if valmap, made into a row, would violate any ref-int
        #   in self.integParents ->
        #     raise DBRefIntError
        #   else -> I ]
        for  refInt in self.integParents:
            self.__searchParentRefInt ( refInt, valMap )

        #-- 2 --
        BasicTable.insert ( self, valMap )


# - - -   T a b l e . d e l e t e   - - -

    def delete ( self, row ):
        """Row deletion, MySQL version.  Checks ref-int children.
        """

        #-- 1 --
        # [ if row is the parent of any ref-int in self.integChildren ->
        #     rasie DBRefIntError
        #   else -> I ]
        for  refInt in self.integChildren:
            self.__searchChildRefInt ( refInt, row.map )

        #-- 2 --
        BasicTable.delete ( self, row )


# - - -   T a b l e . u p d a t e   - - -

    def update ( self, row, valMap ):
        """Row update.  Checks ref-int parents and children both.
        """

        #-- 1 --
        # [ newMap  :=  a dictionary mapping column names |-> values
        #               with values taken from valMap where supplied,
        #               otherwise taken from row.map ]
        newMap  =  copy.copy(row.map)
        newMap.update(valMap)

        #-- 2 --
        # [ if changing row's row in the table from the values in row.map
        #   to the values in newMap would violate any ref-int in
        #   self.integParents ->
        #     raise DBRefIntError
        #   else -> I ]
        for  refInt in self.integParents:
            self.__updateParentRefInt ( refInt, row, newMap )

        #-- 3 --
        # [ if changing row's row in the table from the values in row.map
        #   to the values in newMap would violate any ref-int in
        #   self.integChildren ->
        #     raise DBRefIntError
        #   else -> I ]
        for  refInt in self.integChildren:
            self.__updateChildRefInt ( refInt, row, newMap )
    
        #-- 4 --
        BasicTable.update ( self, row, valMap )


# - - -   T a b l e . _ _ u p d a t e P a r e n t R e f I n t   - - -

    def __updateParentRefInt ( self, refInt, row, newMap ):
        """See if changing row.map -> newMap violates a parent ref-int

          [ if (refInt is a FullInteg representing a ref-int for which
            self is the child)
            and (row is a Row object representing a row in self)
            and (newMap is a dictionary mapping row names in self |->
            values) ->
              if changing row's row in the table from the values in
              row.map to the values in newMap would violate refInt ->
                raise DBRefIntError
              else -> I ]
        """
        #-- 1 --
        # [ c    :=  link-columns(refInt.child, newmap)
        #   old  :=  link-columns(refInt.child, row.map) ]
        c    =  refInt.child.linkValues ( newMap )
        old  =  refInt.child.linkValues ( row.map )

        #-- 2 --
        # NB: If none of the fields involved in the ref-int is
        # changing, and assuming the existing row was checked for
        # integrity when it went in, then the new can't violate
        # this ref-int.
        #--
        if  c == old:
            return

        #-- 3 --
        # [ if refInt.parent.table contains a row whose columns
        #   named by refInt.parent.colList correspond to
        #   link-columns(refInt.child, newMap) ->
        #     I
        #   else -> raise DBRefIntError ]
        self.__searchParentRefInt ( refInt, newMap )


# - - -   T a b l e . _ _ u p d a t e C h i l d R e f I n t   - - -

    def __updateChildRefInt ( self, refInt, row, newMap ):
        """See if changing row.map -> newMap violates a child ref-int

          [ if (refInt is a FullInteg representing a ref-int for which
            self is the child)
            and (row is a Row object representing a row in self)
            and (newMap is a dictionary mapping row names in self |->
            values) ->
              if changing row's row in the table from the values in
              row.map to the values in newMap would violate refInt ->
                raise DBRefIntError
              else -> I ]
        """
        #-- 1 --
        # [ p    :=  link-columns(refInt.parent, newmap)
        #   old  :=  link-columns(refInt.parent, row.map) ]
        p    =  refInt.parent.linkValues ( newMap )
        old  =  refInt.parent.linkValues ( row.map )

        #-- 2 --
        if  p == old:
            return

        #-- 3 --
        # [ if refInt.child.table contains a row whose columns
        #   named by refInt.child.colList correspond to
        #   link-columns(refInt.parent, row.map) ->
        #     I
        #   else -> raise DBRefIntError ]
        self.__searchChildRefInt ( refInt, row.map )


# - - -   T a b l e . _ _ s e a r c h P a r e n t R e f I n t   - - -

    def __searchParentRefInt ( self, refInt, colMap ):
        """Insure new child values relate to an existing parent row

          [ if (refInt is a FullInteg representing a ref-int constraint
            for which self is a child)
            and (colMap is a dictionary mapping column names in self
            |-> appropriate values) ->
              if refInt.parent.table contains at least one row
              whose columns named by refInt.parent.colList match
              link-columns(refInt.child, colMap) ->
                return
              else -> raise DBRefIntError ]
        """

        #-- 1 --
        # [ colPairs  :=  a sequence of two-element sequences (name, value)
        #       representing the names of parent columns from refInt
        #       and the corresponding child column values from colMap ]
        colPairs  =  []

        for  i in range(len(refInt.parent.colList)):
            colPairs.append ( ( refInt.parent.colList[i],
                                colMap[refInt.child.colList[i]] ) )

        #-- 2 --
        # [ q  :=  a QueryResult representing the set of rows from 
        #          refInt.parent whose column names and values are
        #          given by colPairs ]
        q  =  refInt.parent.table.querySome ( colPairs )

        #-- 3 --
        # [ if q contains at least one row ->
        #     return
        #   else -> raise DBRefIntError ]
        parentRow  =  q.next()
        if  parentRow is None:
            raise DBRefIntError, ( "insert or update violates parent "
                                   "ref-int constraint\n%s" % str(colMap) )


# - - -   T a b l e . _ _ s e a r c h C h i l d R e f I n t   - - -

    def __searchChildRefInt ( self, refInt, colMap ):
        """Insure deleted parent values are not the parent of any child row

          [ if (refInt is a FullInteg representing a ref-int constraint
            for which self is a parent)
            and (colMap is a dictionary mapping column names in self
            |-> appropriate values) ->
              if refInt.child.table contains any rows
              whose columns named by refInt.child.colList match
              link-columns(refInt.parent, colMap) ->
                raise DBRefIntError
              else -> return ]
        """

        #-- 1 --
        # [ colPairs  :=  a sequence of two-element sequences (name, value)
        #       representing the names of child columns from refInt
        #       and the corresponding parent column values from colMap ]
        colPairs  =  []

        for  i in range(len(refInt.child.colList)):
            colPairs.append ( ( refInt.child.colList[i],
                                colMap[refInt.parent.colList[i]] ) )

        #-- 2 --
        # [ q  :=  a QueryResult representing the set of rows from 
        #          refInt.child whose column names and values are
        #          given by colPairs ]
        q  =  refInt.child.table.querySome ( colPairs )

        #-- 3 --
        # [ if q contains at least one row ->
        #     raise DBRefIntError
        #   else -> return ]
        childRow  =  q.next()
        if  childRow is not None:
            raise DBRefIntError, ( "delete or update violates child "
                                   "ref-int constraint" )

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

Last updated: 2014-11-23 04:04 MST