root / trunk / twisted / enterprise / sqlreflector.py

# -*- test-case-name: twisted.test.test_reflector -*-
# Copyright (c) 2001-2004 Twisted Matrix Laboratories.
# See LICENSE for details.


from twisted.enterprise import reflector
from twisted.enterprise.util import DBError, getKeyColumn, quote, safe
from twisted.enterprise.util import _TableInfo
from twisted.enterprise.row import RowObject

from twisted.python import reflect

class SQLReflector(reflector.Reflector):
    """
    DEPRECATED.
    
    I reflect on a database and load RowObjects from it.

    In order to do this, I interrogate a relational database to
    extract schema information and interface with RowObject class
    objects that can interact with specific tables. 
    """
    populated = 0
    conditionalLabels = {
        reflector.EQUAL       : "=",
        reflector.LESSTHAN    : "<",
        reflector.GREATERTHAN : ">",
        reflector.LIKE        : "like"
        }

    def __init__(self, dbpool, rowClasses):
        """Initialize me against a database.
        """
        reflector.Reflector.__init__(self, rowClasses)
        self.dbpool = dbpool

    def _populate(self):
        self._transPopulateSchema()

    def _transPopulateSchema(self):
        """Used to construct the row classes in a single interaction.
        """
        for rc in self.rowClasses:
            if not issubclass(rc, RowObject):
                raise DBError("Stub class (%s) is not derived from RowObject" % reflect.qual(rc.rowClass))

            self._populateSchemaFor(rc)
        self.populated = 1

    def _populateSchemaFor(self, rc):
        """Construct all the SQL templates for database operations on
        <tableName> and populate the class <rowClass> with that info.
        """
        attributes = ("rowColumns", "rowKeyColumns", "rowTableName" )
        for att in attributes:
            if not hasattr(rc, att):
                raise DBError("RowClass %s must have class variable: %s" % (rc, att))

        tableInfo = _TableInfo(rc)
        tableInfo.updateSQL = self.buildUpdateSQL(tableInfo)
        tableInfo.insertSQL = self.buildInsertSQL(tableInfo)
        tableInfo.deleteSQL = self.buildDeleteSQL(tableInfo)
        self.populateSchemaFor(tableInfo)

    def escape_string(self, text):
        """Escape a string for use in an SQL statement. The default
        implementation escapes ' with '' and \ with \\. Redefine this
        function in a subclass if your database server uses different
        escaping rules.
        """
        return safe(text)

    def quote_value(self, value, type):
        """Format a value for use in an SQL statement.

        @param value: a value to format as data in SQL.
        @param type: a key in util.dbTypeMap.
        """
        return quote(value, type, string_escaper=self.escape_string)

    def loadObjectsFrom(self, tableName, parentRow=None, data=None,
                        whereClause=None, forceChildren=0):
        """Load a set of RowObjects from a database.

        Create a set of python objects of <rowClass> from the contents
        of a table populated with appropriate data members.
        Example::

          |  class EmployeeRow(row.RowObject):
          |      pass
          |
          |  def gotEmployees(employees):
          |      for emp in employees:
          |          emp.manager = "fred smith"
          |          manager.updateRow(emp)
          |
          |  reflector.loadObjectsFrom("employee",
          |                          data = userData,
          |                          whereClause = [("manager" , EQUAL, "fred smith")]
          |                          ).addCallback(gotEmployees)

        NOTE: the objects and all children should be loaded in a single transaction.
        NOTE: can specify a parentRow _OR_ a whereClause.

        """
        if parentRow and whereClause:
            raise DBError("Must specify one of parentRow _OR_ whereClause")
        if parentRow:
            info = self.getTableInfo(parentRow)
            relationship = info.getRelationshipFor(tableName)
            whereClause = self.buildWhereClause(relationship, parentRow)
        elif whereClause:
            pass
        else:
            whereClause = []
        return self.dbpool.runInteraction(self._rowLoader, tableName,
                                          parentRow, data, whereClause,
                                          forceChildren)

    def _rowLoader(self, transaction, tableName, parentRow, data,
                   whereClause, forceChildren):
        """immediate loading of rowobjects from the table with the whereClause.
        """
        tableInfo = self.schema[tableName]
        # Build the SQL for the query
        sql = "SELECT "
        first = 1
        for column, type in tableInfo.rowColumns:
            if first:
                first = 0
            else:
                sql = sql + ","
            sql = sql + " %s" % column
        sql = sql + " FROM %s " % (tableName)
        if whereClause:
            sql += " WHERE "
            first = 1
            for wItem in whereClause:
                if first:
                    first = 0
                else:
                    sql += " AND "
                (columnName, cond, value) = wItem
                t = self.findTypeFor(tableName, columnName)
                quotedValue = self.quote_value(value, t)
                sql += "%s %s %s" % (columnName, self.conditionalLabels[cond],
                                     quotedValue)

        # execute the query
        transaction.execute(sql)
        rows = transaction.fetchall()

        # construct the row objects
        results = []
        newRows = []
        for args in rows:
            kw = {}
            for i in range(0,len(args)):
                ColumnName = tableInfo.rowColumns[i][0].lower()
                for attr, type in tableInfo.rowClass.rowColumns:
                    if attr.lower() == ColumnName:
                        kw[attr] = args[i]
                        break
            # find the row in the cache or add it
            resultObject = self.findInCache(tableInfo.rowClass, kw)
            if not resultObject:
                meth = tableInfo.rowFactoryMethod[0]
                resultObject = meth(tableInfo.rowClass, data, kw)
                self.addToCache(resultObject)
                newRows.append(resultObject)
            results.append(resultObject)

        # add these rows to the parentRow if required
        if parentRow:
            self.addToParent(parentRow, newRows, tableName)

        # load children or each of these rows if required
        for relationship in tableInfo.relationships:
            if not forceChildren and not relationship.autoLoad:
                continue
            for row in results:
                # build where clause
                childWhereClause = self.buildWhereClause(relationship, row)
                # load the children immediately, but do nothing with them
                self._rowLoader(transaction,
                                relationship.childRowClass.rowTableName,
                                row, data, childWhereClause, forceChildren)

        return results

    def findTypeFor(self, tableName, columnName):
        tableInfo = self.schema[tableName]
        columnName = columnName.lower()
        for column, type in tableInfo.rowColumns:
            if column.lower() == columnName:
                return type

    def buildUpdateSQL(self, tableInfo):
        """(Internal) Build SQL template to update a RowObject.

        Returns: SQL that is used to contruct a rowObject class.
        """
        sql = "UPDATE %s SET" % tableInfo.rowTableName
        # build update attributes
        first = 1
        for column, type in tableInfo.rowColumns:
            if getKeyColumn(tableInfo.rowClass, column):
                continue
            if not first:
                sql = sql + ", "
            sql = sql + " %s = %s" % (column, "%s")
            first = 0

        # build where clause
        first = 1
        sql = sql + " WHERE "
        for keyColumn, type in tableInfo.rowKeyColumns:
            if not first:
                sql = sql + " AND "
            sql = sql + " %s = %s " % (keyColumn, "%s")
            first = 0
        return sql

    def buildInsertSQL(self, tableInfo):
        """(Internal) Build SQL template to insert a new row.

        Returns: SQL that is used to insert a new row for a rowObject
        instance not created from the database.
        """
        sql = "INSERT INTO %s (" % tableInfo.rowTableName
        # build column list
        first = 1
        for column, type in tableInfo.rowColumns:
            if not first:
                sql = sql + ", "
            sql = sql + column
            first = 0

        sql = sql + " ) VALUES ("

        # build values list
        first = 1
        for column, type in tableInfo.rowColumns:
            if not first:
                sql = sql + ", "
            sql = sql + "%s"
            first = 0

        sql = sql + ")"
        return sql

    def buildDeleteSQL(self, tableInfo):
        """Build the SQL template to delete a row from the table.
        """
        sql = "DELETE FROM %s " % tableInfo.rowTableName
        # build where clause
        first = 1
        sql = sql + " WHERE "
        for keyColumn, type in tableInfo.rowKeyColumns:
            if not first:
                sql = sql + " AND "
            sql = sql + " %s = %s " % (keyColumn, "%s")
            first = 0
        return sql

    def updateRowSQL(self, rowObject):
        """Build SQL to update the contents of rowObject.
        """
        args = []
        tableInfo = self.schema[rowObject.rowTableName]
        # build update attributes
        for column, type in tableInfo.rowColumns:
            if not getKeyColumn(rowObject.__class__, column):
                args.append(self.quote_value(rowObject.findAttribute(column),
                                             type))
        # build where clause
        for keyColumn, type in tableInfo.rowKeyColumns:
            args.append(self.quote_value(rowObject.findAttribute(keyColumn),
                                         type))

        return self.getTableInfo(rowObject).updateSQL % tuple(args)

    def updateRow(self, rowObject):
        """Update the contents of rowObject to the database.
        """
        sql = self.updateRowSQL(rowObject)
        rowObject.setDirty(0)
        return self.dbpool.runOperation(sql)

    def insertRowSQL(self, rowObject):
        """Build SQL to insert the contents of rowObject.
        """
        args = []
        tableInfo = self.schema[rowObject.rowTableName]        
        # build values
        for column, type in tableInfo.rowColumns:
            args.append(self.quote_value(rowObject.findAttribute(column),type))
        return self.getTableInfo(rowObject).insertSQL % tuple(args)

    def insertRow(self, rowObject):
        """Insert a new row for rowObject.
        """
        rowObject.setDirty(0)
        sql = self.insertRowSQL(rowObject)
        return self.dbpool.runOperation(sql)

    def deleteRowSQL(self, rowObject):
        """Build SQL to delete rowObject from the database.
        """
        args = []
        tableInfo = self.schema[rowObject.rowTableName]        
        # build where clause
        for keyColumn, type in tableInfo.rowKeyColumns:
            args.append(self.quote_value(rowObject.findAttribute(keyColumn),
                                         type))

        return self.getTableInfo(rowObject).deleteSQL % tuple(args)

    def deleteRow(self, rowObject):
        """Delete the row for rowObject from the database.
        """
        sql = self.deleteRowSQL(rowObject)
        self.removeFromCache(rowObject)
        return self.dbpool.runOperation(sql)


__all__ = ['SQLReflector']