Beispiel #1
0
    def __init__(self, engine, table, column,
                 aggregats=['count'],
                 database='default',
                 model=None):
        self.verbose = 0
        self.table = table
        self.column = column
        self.aggregats = aggregats
        self.database = database
        self.model = model
        self.functions = {}
        # use the right backend
        if engine == PG_BACKEND:
            from databases.pg import TriggerPostgreSQL
            self.backend = TriggerPostgreSQL()
        else:
            raise DatabaseNotSupported()

        for agg in self.aggregats:
            if isinstance(agg, dict):
                for agg_key, where_clause in self.extract_agg_where_clause(
                        agg).iteritems():
                    for action in ["insert", "update", "delete"]:
                        fname = function_name(table, column,
                                              action, key=agg_key)
                        self.functions[fname] = action
            else:
                for action in ["insert", "update", "delete"]:
                    fname = function_name(table, column, action)
                    self.functions[fname] = action
Beispiel #2
0
class AggTrigger(object):
    """Manage database triggers
    """

    def __init__(self, engine, table, column,
                 aggregats=['count'],
                 database='default',
                 model=None):
        self.verbose = 0
        self.table = table
        self.column = column
        self.aggregats = aggregats
        self.database = database
        self.model = model
        self.functions = {}
        # use the right backend
        if engine == PG_BACKEND:
            from databases.pg import TriggerPostgreSQL
            self.backend = TriggerPostgreSQL()
        else:
            raise DatabaseNotSupported()

        for agg in self.aggregats:
            if isinstance(agg, dict):
                for agg_key, where_clause in self.extract_agg_where_clause(
                        agg).iteritems():
                    for action in ["insert", "update", "delete"]:
                        fname = function_name(table, column,
                                              action, key=agg_key)
                        self.functions[fname] = action
            else:
                for action in ["insert", "update", "delete"]:
                    fname = function_name(table, column, action)
                    self.functions[fname] = action

    @property
    def table_name(self):
        return table_name(self.table, self.column)

    @property
    def triggers_name(self):
        return triggers_name(self.table, self.column, self.functions)

    @property
    def functions_name(self):
        fns = []
        for action in ACTIONS:
            fns.append(function_name(self.table, self.column, action))
        return fns

    def create_objects(self):
        """Create all needed objects
        table (string)
        column (string)
        aggregats (array)
        """
        res = self.create_table()
        res = res + self.create_functions()
        res = res + self.create_triggers()
        return res

    def create_table(self):
        """
        table (string)
        column (string)
        aggregats (array)

        Return :
        - 0 is case of creation + success
        - 1 if table already exists
        """
        if not self.agg_table_ispresent():
            sql = self.sql_create_table()
            return dbcommands.execute_raw(sql, self.database)
        else:
            return 1

    def create_triggers(self):
        """Create all triggers
        """
        res = 0
        for (trigger_name, sql) in self.sql_create_triggers():

            if res == 0:
                if not self.trigger_on_table_is_present(trigger_name):
                    res = self.create_trigger(sql)
        return res

    def create_trigger(self, sql):
        """Create a trigger
        """
        if self.verbose > 2:
            stm = dbcommands.mogrify(sql, database=self.database)
            sys.stdout.write(stm)
        res = dbcommands.execute_raw(sql, self.database)
        return res

    def create_functions(self):
        """Create all functions in the database
        """
        res = 0
        for sql in self.sql_create_functions():
            if res == 0:
                res = dbcommands.execute_raw(sql, self.database)
        return res

    def sql_create_table(self):
        """
        """
        typname = self.column_typname()

        return self.backend.sql_create_table(self.table_name,
                                             (self.column, typname),
                                             self.aggregats)

    def column_typname(self):
        """Lookup for a column type

        Depending on backend.sql_get_column_typname()[1] we need to
        parse the result or not
        """
        (qry, parsefunc) = self.backend.sql_get_column_typname()
        params = (self.table, self.column)
        res = dbcommands.lookup(qry,
                                params=params,
                                database=self.database)
        if parsefunc is None:
            return res
        else:
            return eval("self.backend.%s('%s', '%s')" % (parsefunc,
                                                         res,
                                                         self.column))

    def sql_init(self):
        """
        Remplissage de la table technique avec les données existantes
        """
        aggs = {}
        for agg in self.aggregats:
            if isinstance(agg, dict):
                agg_type = agg.keys()[0]
                for trigger in agg[agg_type]:
                    agg_name = trigger.keys()[0]
                    aggs[agg_name] = {"column": "agg_{}_{}".format(agg_type,
                                                                   agg_name)}

                for agg_key, where_clause in self.extract_agg_where_clause(
                        agg).iteritems():
                    aggs[agg_key]["where"] = where_clause[0] % tuple(
                        where_clause[1])

            else:
                agg_type = agg
                agg_name = "agg_{}".format(agg_type)
                aggs[agg_name] = {"column": agg_name, "where": None}

        sql = self.backend.sql_init_aggtable(self.table,
                                             self.column,
                                             self.table_name,
                                             aggs)
        return sql

    def initialize(self):
        """Initialize the agregate table with values from source table
        May be long on huge tables
        """
        sql = self.sql_init()
        res = dbcommands.execute_raw(sql, database=self.database)
        return res

    def get_nb_tuples(self):
        """
        Remplissage de la table technique avec les données existantes
        """
        sql = self.backend.sql_nb_tuples()
        params = (self.table,)
        if self.verbose > 2:
            msg = "[SQL] %s\n" % (dbcommands.mogrify(sql,
                                                     params=params,
                                                     database=self.database))
            sys.stdout.write(msg)
        res = dbcommands.lookup(sql, params=params, database=self.database)
        return res

    def sql_create_triggers(self):
        """Declaration des triggers

        Return : Array of tuples (triggername, SQL_COMMAND)
        """
        sql = []
        for function, action in self.functions.iteritems():
            sql.append(self.sql_create_trigger(self.table,
                                               action,
                                               function))
        return sql

    def sql_create_trigger(self, table, action, function):
        """
        table (string): table name
        action (string): insert, update or create
        function (string): function's name called by the trigger
        """
        tgname = trigger_name(table, self.column, function, action)
        return (tgname, self.backend.sql_create_trigger(tgname,
                                                        function,
                                                        table,
                                                        action))

    def drop_table(self):
        sql = self.sql_drop_table()
        return dbcommands.execute_raw(sql)

    def drop_functions(self):
        """DROP all related functions
        """
        res = 0
        for sql in self.sql_drop_functions():
            res = res + dbcommands.execute_raw(sql)
        return res

    def sql_drop_functions(self):
        """Return SQL Statements to DROP all functions
        """
        sql = []
        for fname in self.functions.iterkeys():
            sql.append(self.sql_drop_function(fname))
        return sql

    def sql_create_functions(self):
        """Create all functions

        table (string)
        column (string)
        aggregats (array)
        """
        sql = []
        for action in ACTIONS:
            funcs = self.sql_create_function(self.table,
                                             self.column,
                                             self.aggregats,
                                             action)
            for func in funcs:
                sql.append(func)
        return sql

    def extract_agg_where_clause(self, agg):
        """
        For aggregations relying on the ORM extract the where clause from
        Django
        """
        connection = connections["default"]
        result = {}
        for aggregation_type in agg.iterkeys():
            # foreach aggregation type we can have multiple
            # aggregation with different filters
            for aggregation in agg[aggregation_type]:
                # at this level we can create the queryset and
                # extract the where clause
                for agg_key in aggregation:
                    # First we create a queryset with Q objects
                    query = Q()
                    for filter in aggregation[agg_key]:
                        query &= Q(
                            **{filter["field"]: filter["value"]})
                    compiler = self.model.objects.filter(
                        query).query.get_compiler(
                            connection=connection)
                    qn = compiler
                    where_clause = self.model.objects.filter(
                        query).query.where.as_sql(
                            qn,
                            connection
                    )
                    result[agg_key] = where_clause
        return result

    def sql_create_function(self, table, column, aggregats, action):
        """
        table (string)
        column (string)
        aggregats (array)
        action (string)
        """
        fname = function_name(table, column, action)
        tname = table_name(table, column)
        sql = None
        if action not in ["insert", "update", "delete"]:
            raise ActionUnknown(Exception)
        meth = "sql_create_function_{}".format(action)
        sql = []
        for agg in aggregats:
            if isinstance(agg, dict):
                for agg_key, where_clause in self.extract_agg_where_clause(
                        agg).iteritems():
                    fname = function_name(table, column,
                                          action, key=agg_key)
                    sql.append(getattr(self.backend, meth)(
                        fname, table,
                        column, tname, action,
                        where_clause=where_clause,
                        agg_key=agg_key))
            else:
                fname = function_name(table, column,
                                      action)
                resp = getattr(self.backend, meth)(
                    fname, table,
                    column, tname, action)
                sql.append(resp)
        return sql

    def drop_triggers(self, name):
        """DROP related triggers
        """
        res = 0
        for sql in self.sql_drop_triggers(name):
            res = res + dbcommands.execute_raw(sql, self.database)
        return res

    def sql_drop_triggers(self):
        """Return SQL Statements to DROP ALL triggers
        """
        sql = []
        for function, action in self.functions.iteritems():
            tgname = trigger_name(self.table, self.column, function, action)
            sql.append(self.sql_drop_trigger(tgname, self.table))
        return sql

    def sql_drop_function(self, fname):
        """Return SQL statement from backend to DROP a function
        """
        return self.backend.sql_drop_function(fname)

    def sql_drop_trigger(self, name, table):
        """Return SQL statement from backend to DROP a trigger
        """
        return self.backend.sql_drop_trigger(name, table)

    def sql_drop_table(self):
        """Return SQL statement from backend to DROP a table
        """
        return self.backend.sql_drop_table(self.table_name)

    def agg_function(self, fname, agg):
        """Return the aggregate name

        fname (string) : min or max
        """
        return self.backend.agg_fname(agg, fname)

    def agg_table_ispresent(self):
        """Check if the agg table is present
        """
        qry = self.backend.sql_table_exists()
        params = (self.table_name,)
        res = dbcommands.lookup(qry,
                                params=params,
                                database=self.database)
        return res

    def triggers_on_table_are_present(self):
        """Check if the triggers are present in database
        """
        res = []
        qry = self.backend.sql_trigger_on_table_exists()

        for trig in self.triggers_name:
            params = (trig, self.table)
            res.append((trig, dbcommands.lookup(qry,
                                                params=params,
                                                database=self.database)))
        return res

    def trigger_on_table_is_present(self, trgname):
        """Check if a trigger is present on a table

        Return False is the trigger does not exists

        trganme : (string) trigger name
        """

        qry = self.backend.sql_trigger_on_table_exists()
        params = (trgname, self.table)
        res = dbcommands.lookup(qry, params=params, database=self.database)
        return res

    def functions_are_present(self):
        """Check if the functions are present in database
        """
        res = []
        qry = self.backend.sql_trigger_function_on_table_exists()
        functions = []
        for agg in self.aggregats:
            if isinstance(agg, dict):
                for agg_key, where_clause in self.extract_agg_where_clause(
                        agg).iteritems():
                    for action in ["insert", "update", "delete"]:
                        fname = function_name(self.table, self.column,
                                              action, key=agg_key)
                        functions.append(fname)
            else:
                for fname in self.functions_name:
                    functions.append(fname)

        for func in functions:
            params = (func[:-2], self.table)
            res.append((func, dbcommands.lookup(qry,
                                                params=params,
                                                database=self.database)))
        return res