"""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" )
