예제 #1
0
파일: schema.py 프로젝트: pombreda/conary-1
 def __init__(self, showTables = True, driver="sqlite"):
     self.tables = self.views = self.sequences = CaselessDict()
     self.tempTables = CaselessDict()
     self.version = 0
     self.showTables = showTables
     self.statements = []
     self.driver = driver
     if self.driver == "sqlite":
         from conary.dbstore import sqlite_drv as drv
     elif driver == "postgresql":
         from conary.dbstore import postgresql_drv as drv
     else:
         raise AttributeError("unknown driver", driver)
     self.keywords = drv.KeywordDict()
예제 #2
0
 def __init__(self, *args, **kwargs):
     CaselessDict.__init__(self, *args)
     self.update(kwargs)
     if 'config' in self:
         self['config'] = self.__class__(self.get('config', {}))
예제 #3
0
파일: schema.py 프로젝트: pombredanne/mint
class PrintDatabase(object):
    def __init__(self, showTables=True, driver="sqlite"):
        self.tables = self.views = self.sequences = CaselessDict()
        self.tempTables = CaselessDict()
        self.version = 0
        self.showTables = showTables
        self.statements = []
        self.driver = driver
        if self.driver == "sqlite":
            from conary.dbstore import sqlite_drv as drv
        elif driver =="mysql":
            from conary.dbstore import mysql_drv as drv
        elif driver == "postgresql":
            from conary.dbstore import postgresql_drv as drv
        else:
            raise AttributeError("unknown driver", driver)
        self.keywords = drv.KeywordDict()

    def connect(self, *args, **kwargs):
        pass
    def transaction(self):
        pass
    def commit(self):
        pass
    def rollback(self):
        pass
    def cursor(self):
        return self
    def loadSchema(self):
        pass
    # simulate non-existent tables for delete statements
    @staticmethod
    def _skip_delete(sql):
        delfrom = re.compile("(?i)DELETE\s+FROM.*")
        if delfrom.match(sql):
            raise sqlerrors.DatabaseError
        return False
    # ignore create temporary tables
    def _skip_tempTables(self, sql):
        tmptbl = re.compile(
                "(?i)CREATE\s+TEMPORARY\s+TABLE\s+(?P<table>[^ (]+).*")
        match = tmptbl.match(sql)
        if match is not None:
            groups = match.groupdict()
            # remember this temporary table
            self.tempTables.setdefault(groups["table"].strip(), [])
            return True
        return False
    # ignore indexes for temporary tables
    def _skip_Indexes(self, sql, skipAll=False):
        tmpidx = re.compile("(?i)CREATE\s+(UNIQUE\s+)?INDEX\s+\S+\s+"
                "ON\s+(?P<table>[^ (]+).*")
        match = tmpidx.match(sql)
        if match is not None:
            groups = match.groupdict()
            # remember this temporary table
            if skipAll or groups["table"] in self.tempTables:
                return True
        return False
    @staticmethod
    def _skip_Triggers(sql, skipAll=False):
        tmptrg = re.compile("(?i)CREATE\s+(OR\s+REPLACE\s+)?"
                "((DEFINER.*)?TRIGGER|FUNCTION)")
        if tmptrg.match(sql):
            return skipAll
        return False
    def _skip_Tables(self, sql, skipAll = False):
        tbl = re.compile("^(?i)(CREATE|ALTER)\s+(TABLE\s+(?P<table>[^(]+)"
                "|(\s?DEFINER.*)?(\s?SQL SECURITY.*)?VIEW\s+"
                "(?P<view>[^( ]+))\s*([(]|ADD|AS).*")
        match = tbl.match(sql)
        if match is not None:
            groups = match.groupdict()
            if groups["table"]:
                self.tables.setdefault(groups["table"].strip(), [])
            if groups["view"]:
                self.views.setdefault(groups["view"].strip(), True)
            return skipAll
        return False

    def execute(self, sql, *args, **kwargs):
        sql = sql.strip()
        # skip the parametrized schema definitions
        if (args or kwargs) and "?" in sql:
            return
        if (self._skip_delete(sql)
                or self._skip_tempTables(sql)
                or self._skip_Indexes(sql, self.showTables)
                or self._skip_Triggers(sql, self.showTables)
                or self._skip_Tables(sql, not self.showTables)):
            return
        into = re.compile("^(?i)(INSERT INTO).*")
        # we don't do inserts because they're ot part of te schema definition
        if into.match(sql):
            return
        self.statements.append(sql)

    def createTrigger(self, table, column, onAction, pinned=False):
        onAction = onAction.lower()
        name = "%s_%s" % (table, onAction)
        assert(onAction in ["insert", "update"])
        create = "CREATE TRIGGER"
        if self.driver == "postgresql":
            funcName = "%s_func" % name
            if pinned:
                self.execute("""
                CREATE OR REPLACE FUNCTION %s()
                RETURNS trigger
                AS $$
                BEGIN
                    NEW.%s := OLD.%s ;
                    RETURN NEW;
                END ; $$ LANGUAGE 'plpgsql';
                """ % (funcName, column, column))
            else:
                self.execute("""
                CREATE OR REPLACE FUNCTION %s()
                RETURNS trigger
                AS $$
                BEGIN
                    NEW.%s := TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), '99999999999999') ;
                    RETURN NEW;
                END ; $$ LANGUAGE 'plpgsql';
                """ % (funcName, column))
            # now create the trigger based on the above function
            self.execute("""
            CREATE TRIGGER %s
            BEFORE %s ON %s
            FOR EACH ROW
            EXECUTE PROCEDURE %s()
            """ % (name, onAction, table, funcName))
            return
        if self.driver == "sqlite":
            when = "AFTER"
            sql = ("UPDATE %s SET %s = unix_timestamp() "
                   "WHERE _ROWID_ = NEW._ROWID_ ; " %(table, column))
        elif self.driver == "mysql":
            when = "BEFORE"
            # force the current_timestamp into a numeric context
            if pinned:
                sql = "SET NEW.%s = OLD.%s ; " % (column, column)
            else:
                sql = "SET NEW.%s = current_timestamp() + 0 ; " % (column,)
            create = "CREATE DEFINER = 'root'@'localhost' TRIGGER"
        else:
            raise NotImplementedError
        sql = """
        %s %s %s %s ON %s
        FOR EACH ROW BEGIN
        %s
        END
        """ % (create, name, when.upper(), onAction.upper(), table, sql)
        self.execute(sql)
        return True

    def createIndex(self, table, name, columns, unique = False):
        if unique:
            unique = "UNIQUE"
        else:
            unique = ""
        sql = "CREATE %s INDEX %s on %s (%s)" % (
            unique, name, table, columns)
        self.execute(sql)
        return True

    def setVersion(self, version):
        self.version = version
    def getVersion(self):
        return self.version
예제 #4
0
파일: schema.py 프로젝트: pombreda/conary-1
class PrintDatabase:
    def __init__(self, showTables = True, driver="sqlite"):
        self.tables = self.views = self.sequences = CaselessDict()
        self.tempTables = CaselessDict()
        self.version = 0
        self.showTables = showTables
        self.statements = []
        self.driver = driver
        if self.driver == "sqlite":
            from conary.dbstore import sqlite_drv as drv
        elif driver == "postgresql":
            from conary.dbstore import postgresql_drv as drv
        else:
            raise AttributeError("unknown driver", driver)
        self.keywords = drv.KeywordDict()

    def connect(self, *args, **kwargs):
        pass
    def transaction(self):
        pass
    def commit(self):
        pass
    def rollback(self):
        pass
    def cursor(self):
        return self
    def loadSchema(self):
        pass
    # simulate non-existent tables for delete statements
    def __skip_delete(self, sql):
        delfrom = re.compile("(?i)DELETE\s+FROM.*")
        if delfrom.match(sql):
            raise sqlerrors.DatabaseError
        return False
    # ignore create temporary tables
    def __skip_tempTables(self, sql):
        tmptbl = re.compile("(?i)CREATE\s+TEMPORARY\s+TABLE\s+(?P<table>[^ (]+).*")
        m = tmptbl.match(sql)
        if m is not None:
            d = m.groupdict()
            # remember this temporary table
            self.tempTables.setdefault(d["table"].strip(), [])
            return True
        return False
    # ignore indexes for temporary tables
    def __skip_Indexes(self, sql, skipAll = False):
        tmpidx = re.compile("(?i)CREATE\s+(UNIQUE\s+)?INDEX\s+\S+\s+ON\s+(?P<table>[^ (]+).*")
        m = tmpidx.match(sql)
        if m is not None:
            d = m.groupdict()
            # remember this temporary table
            if skipAll or d["table"] in self.tempTables:
                return True
        return False
    def __skip_Triggers(self, sql, skipAll = False):
        tmptrg = re.compile("(?i)CREATE\s+(OR\s+REPLACE\s+)?((DEFINER.*)?TRIGGER|FUNCTION)")
        if tmptrg.match(sql):
            return skipAll
        return False
    def __skip_Tables(self, sql, skipAll = False):
        tbl = re.compile(
            "^(?i)(CREATE|ALTER)\s+(TABLE\s+(?P<table>[^(]+)|(\s?DEFINER.*)?(\s?SQL SECURITY.*)?VIEW\s+(?P<view>[^( ]+))\s*([(]|ADD|AS).*"
            )
        m = tbl.match(sql)
        if m is not None:
            d = m.groupdict()
            if d["table"]: self.tables.setdefault(d["table"].strip(), [])
            if d["view"]: self.views.setdefault(d["view"].strip(), True)
            return skipAll
        return False

    def execute(self, sql, *args, **kwargs):
        sql = sql.strip()
        # skip the parametrized schema definitions
        if args and "?" in sql:
            return
        if self.__skip_delete(sql):
            return
        if self.__skip_tempTables(sql):
            return
        if self.__skip_Indexes(sql, self.showTables):
            return
        if self.__skip_Triggers(sql, self.showTables):
            return
        if self.__skip_Tables(sql, not self.showTables):
            return
        into = re.compile("^(?i)(INSERT INTO).*")
        # we don't do inserts because they're ot part of te schema definition
        if into.match(sql):
            return
        self.statements.append(sql)

    def createTrigger(self, table, column, onAction, pinned=False):
        onAction = onAction.lower()
        name = "%s_%s" % (table, onAction)
        assert(onAction in ["insert", "update"])
        create = "CREATE TRIGGER"
        if self.driver == "postgresql":
            funcName = "%s_func" % name
            if pinned:
                self.execute("""
                CREATE OR REPLACE FUNCTION %s()
                RETURNS trigger
                AS $$
                BEGIN
                    NEW.%s := OLD.%s ;
                    RETURN NEW;
                END ; $$ LANGUAGE 'plpgsql';
                """ % (funcName, column, column))
            else:
                self.execute("""
                CREATE OR REPLACE FUNCTION %s()
                RETURNS trigger
                AS $$
                BEGIN
                    NEW.%s := TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), '99999999999999') ;
                    RETURN NEW;
                END ; $$ LANGUAGE 'plpgsql';
                """ % (funcName, column))
            # now create the trigger based on the above function
            self.execute("""
            CREATE TRIGGER %s
            BEFORE %s ON %s
            FOR EACH ROW
            EXECUTE PROCEDURE %s()
            """ % (name, onAction, table, funcName))
            return
        if self.driver == "sqlite":
            when = "AFTER"
            sql = ("UPDATE %s SET %s = unix_timestamp() "
                   "WHERE _ROWID_ = NEW._ROWID_ ; " %(table, column))
        elif self.driver == "mysql":
            when = "BEFORE"
            # force the current_timestamp into a numeric context
            if pinned:
                sql = "SET NEW.%s = OLD.%s ; " % (column, column)
            else:
                sql = "SET NEW.%s = current_timestamp() + 0 ; " % (column,)
            create = "CREATE DEFINER = 'root'@'localhost' TRIGGER"
        # XXX: FIXME!!! ingres does not support triggers yet
        elif self.driver == "ingres":
            return
        else:
            raise NotImplementedError
        sql = """
        %s %s %s %s ON %s
        FOR EACH ROW BEGIN
        %s
        END
        """ % (create, name, when.upper(), onAction.upper(), table, sql)
        self.execute(sql)
        return True

    def createIndex(self, table, name, columns, unique = False):
        if unique:
            unique = "UNIQUE"
        else:
            unique = ""
        sql = "CREATE %s INDEX %s on %s (%s)" % (
            unique, name, table, columns)
        self.execute(sql)
        return True

    def setVersion(self, version):
        self.version = version
    def getVersion(self):
        return self.version