import re
import sys
import yaml
from collections import defaultdict

"""
@brief Wrapper function for ____run_sql_query
"""
def run_sql(sql, portid, con_args):
    from madpack import ____run_sql_query
    return ____run_sql_query(sql, True, portid, con_args)

"""
@brief Get the signature of a UDF/UDA for comparison
"""
def get_signature_for_compare(schema, proname, rettype, argument):
    signature = '%s %s.%s(%s)' % (
        rettype.strip(), schema.strip(), proname.strip(), argument.strip())
    signature = re.sub('\s+', ' ', signature)
    signature = re.sub('"', '', signature)
    return signature.lower()

"""
@brief Base class for handling the upgrade
"""
class UpgradeBase:
    def __init__(self, schema, portid, con_args):
        self._schema = schema.lower()
        self._portid = portid
        self._con_args = con_args
        self._schema_oid = None
        self._get_schema_oid()

    """
    @brief Wrapper function for run_sql
    """
    def _run_sql(self, sql):
        return run_sql(sql, self._portid, self._con_args)

    """
    @brief Get the oids of some objects from the catalog in the current version
    """
    def _get_schema_oid(self):
        self._schema_oid = self._run_sql("""
            SELECT oid FROM pg_namespace WHERE nspname = '{schema}'
            """.format(schema=self._schema))[0]['oid']


    """
    @brief Get the function name, return type, and arguments given an oid
    @note The function can only handle the case that proallargtypes is null,
    refer to pg_catalog.pg_get_function_identity_argument and
    pg_catalog.pg_get_function_result in PG for a complete implementation, which are
    not supported by GP
    """
    def _get_function_info(self, oid):
        row = self._run_sql("""
            SELECT
                max(proname) AS proname,
                max(rettype) AS rettype,
                array_to_string(
                    array_agg(argname || ' ' || argtype order by i), ',') AS argument
            FROM
            (
                SELECT
                    proname,
                    textin(regtypeout(prorettype::regtype)) AS rettype,
                    CASE array_upper(proargtypes,1) WHEN -1 THEN ''
                        ELSE textin(regtypeout(unnest(proargtypes)::regtype))
                    END AS argtype,
                    CASE WHEN proargnames IS NULL THEN ''
                        ELSE unnest(proargnames)
                    END AS argname,
                    CASE array_upper(proargtypes,1) WHEN -1 THEN 1
                        ELSE generate_series(0, array_upper(proargtypes, 1))
                    END AS i
                FROM
                    pg_proc AS p
                WHERE
                    oid = {oid}
            ) AS f
            """.format(oid=oid))
        return {"proname": row[0]['proname'], 'rettype': row[0]['rettype'],
            'argument': row[0]['argument']}

"""
@brief This class reads changes from the configuration file and handles
the dropping of objects
"""
class ChangeHandler(UpgradeBase):
    def __init__(self, schema, portid, con_args, maddir, mad_dbrev):
        UpgradeBase.__init__(self, schema, portid, con_args)
        self._opr_ind_svec = None
        self._get_opr_indepent_svec()
        self._maddir = maddir
        self._mad_dbrev = mad_dbrev
        self._newmodule = None
        self._udt = None
        self._udf = None
        self._uda = None
        self._udc = None
        self._load()


    """
    @brief Get the UDOps which are independent of svec in the current version
    """
    def _get_opr_indepent_svec(self):
        rows = self._run_sql("""
            SELECT
                oprname,
                tl.typname AS typ_left,
                nspl.nspname AS nsp_left,
                tr.typname AS typ_right,
                nspr.nspname AS nsp_right
            FROM
                pg_operator AS o,
                pg_type AS tl,
                pg_type AS tr,
                pg_namespace AS nspl,
                pg_namespace AS nspr
            WHERE
                oprnamespace = {schema_oid} AND
                (
                    oprleft <> '{schema_madlib}.svec'::regtype AND
                    oprright <> '{schema_madlib}.svec'::regtype AND
                    oprleft = tl.oid AND
                    oprright = tr.oid AND
                    tl.typnamespace = nspl.oid AND
                    tr.typnamespace = nspr.oid
                )
            """.format(schema_madlib=self._schema, schema_oid=self._schema_oid))
        self._opr_ind_svec = {}
        for row in rows:
            self._opr_ind_svec[row['oprname']] = row

    """
    @brief Load the configuration file
    """
    def _load(self):
        if float(self._mad_dbrev) < 0.6:
            filename = self._maddir + '/madpack/changelist_v0.5.yaml'
        else:
            filename = self._maddir + '/madpack/changelist.yaml'
        config = yaml.load(open(filename))

        self._newmodule = config['new module']
        if self._newmodule is None:
            self._newmodule = {}

        self._udt = config['udt']
        if self._udt is None:
            self._udt = {}

        self._udc = config['udc']
        if self._udc is None:
            self._udc = {}

        self._udf = defaultdict(list)
        items = config['udf']
        if items is not None:
            for item in items:
                for udf in item:
                    rettype = item[udf]['rettype'].lower().replace(
                        'schema_madlib', self._schema)
                    argument = ''
                    if item[udf]['argument'] is not None:
                        argument = item[udf]['argument'].lower().replace(
                            'schema_madlib', self._schema)
                    self._udf[udf].append(
                        {'rettype': rettype, 'argument': argument})

        self._uda = defaultdict(list)
        items = config['uda']
        if items is not None:
            for item in items:
                for uda in item:
                    rettype = item[uda]['rettype'].lower().replace(
                        'schema_madlib', self._schema)
                    argument = ''
                    if item[uda]['argument'] is not None:
                        argument = item[uda]['argument'].lower().replace(
                            'schema_madlib', self._schema)
                    self._uda[uda].append(
                        {'rettype': rettype, 'argument': argument})

    """
    @brief Get the list of new modules
    """
    def get_newmodule(self):
        return self._newmodule

    """
    @brief Get the list of changed UDTs
    """
    def get_udt(self):
        return self._udt

    """
    @brief Get the list of changed UDAs
    """
    def get_uda(self):
        return self._uda

    """
    @brief Get the list of changed UDCs
    @note This is a UDC in utilities module
    """
    def get_udc(self):
        return self._udc

    """
    @brief Get the list of UDF signatures for comparison
    """
    def get_udf_signature(self):
        res = defaultdict(bool)
        for udf in self._udf:
            for item in self._udf[udf]:
                signature = get_signature_for_compare(
                    self._schema, udf, item['rettype'], item['argument'])
                res[signature] = True
        return res

    """
    @brief Get the list of UDA signatures for comparison
    """
    def get_uda_signature(self):
        res = defaultdict(bool)
        for uda in self._uda:
            for item in self._uda[uda]:
                signature = get_signature_for_compare(
                    self._schema, uda, item['rettype'], item['argument'])
                res[signature] = True
        return res

    """
    @brief Drop all types that were updated/removed in the new version
    @note It is dangerous to drop a UDT becuase there might be many
    dependencies
    """
    def drop_changed_udt(self):
        # Note that we use CASCADE option here. This might be dangerous because
        # it may drop some undetected dependent objects (eg. UDCast, UDOp, etc)
        for udt in self._udt:
            # Newly added type
            if self._udt[udt] == 'i':
                continue
            self._run_sql("""
                DROP TYPE IF EXISTS {schema}.{udt} CASCADE
                """.format(schema=self._schema, udt=udt))
            if udt == 'svec':
                # Drop operators defined in the svec module which do not
                # depend on svec. We will run the whole svec.sql without
                # filtering once svec changed
                for opr in self._opr_ind_svec:
                    self._run_sql("""
                        DROP OPERATOR IF EXISTS {schema}.{oprname}
                        ({nsp_left}.{typ_left}, {nsp_left}.{typ_left})
                        """.format(
                            schema=self._schema, oprname=opr,
                            nsp_left=self._opr_ind_svec[opr]['nsp_left'],
                            typ_left=self._opr_ind_svec[opr]['typ_left'],
                            nsp_right=self._opr_ind_svec[opr]['nsp_right'],
                            typ_right=self._opr_ind_svec[opr]['typ_right']
                        ))
    """
    @brief Drop all functions (UDF) that were removed in new version
    """
    def drop_changed_udf(self):
        for udf in self._udf:
            for item in self._udf[udf]:
                self._run_sql("""
                    DROP FUNCTION IF EXISTS {schema}.{udf}({arg})
                    """.format(schema=self._schema,
                        udf=udf,
                        arg=item['argument']))

    """
    @brief Drop all aggregates (UDA) that were removed in new version
    """
    def drop_changed_uda(self):
        for uda in self._uda:
            for item in self._uda[uda]:
                self._run_sql("""
                    DROP AGGREGATE IF EXISTS {schema}.{uda}({arg})
                    """.format(schema=self._schema,
                        uda=uda,
                        arg=item['argument']))

    """
    @brief Drop all casts (UDC) that were updated/removed in new version
    @note We have special treatment for UDCs defined in the svec module
    """
    def drop_changed_udc(self):
        for udc in self._udc:
            self._run_sql("""
                DROP CAST IF EXISTS ({sourcetype} AS {targettype})
                """.format(
                    sourcetype=self._udc[udc]['sourcetype'],
                    targettype=self._udc[udc]['targettype']))

"""
@brief This class detects the direct/recursive view dependencies on MADLib
UDFs/UDAs defined in the current version
"""
class ViewDependency(UpgradeBase):
    def __init__(self, schema, portid, con_args):
        UpgradeBase.__init__(self, schema, portid, con_args)
        self._view2proc = None
        self._view2view = None
        self._view2def = None
        self._detect_direct_view_dependency()
        self._detect_recursive_view_dependency()
        self._filter_recursive_view_dependency()

    """
    @brief  Detect direct view dependencies on MADLib UDFs/UDAs
    """
    def _detect_direct_view_dependency(self):
        rows = self._run_sql("""
            SELECT
                view, nsp.nspname AS schema, procname, procoid, proisagg
            FROM
                pg_namespace nsp,
                (
                    SELECT
                        c.relname AS view,
                        c.relnamespace AS namespace,
                        p.proname As procname,
                        p.oid AS procoid,
                        p.proisagg AS proisagg
                    FROM
                        pg_class AS c,
                        pg_rewrite AS rw,
                        pg_depend AS d,
                        pg_proc AS p
                    WHERE
                        c.oid = rw.ev_class AND
                        rw.oid = d.objid AND
                        d.classid = 'pg_rewrite'::regclass AND
                        d.refclassid = 'pg_proc'::regclass AND
                        d.refobjid = p.oid AND
                        p.pronamespace = {schema_madlib_oid}
                ) t1
            WHERE
                t1.namespace = nsp.oid
        """.format(schema_madlib_oid=self._schema_oid))

        self._view2proc = defaultdict(list)
        for row in rows:
            key= (row['schema'], row['view'])
            self._view2proc[key].append(
                (row['procname'], row['procoid'],
                    True if row['proisagg'] == 't' else False))

    """
    @brief  Detect recursive view dependencies (view on view)
    """
    def _detect_recursive_view_dependency(self):
        rows = self._run_sql("""
            SELECT
                nsp1.nspname AS depender_schema,
                depender,
                nsp2.nspname AS dependee_schema,
                dependee
            FROM
                pg_namespace AS nsp1,
                pg_namespace AS nsp2,
                (
                    SELECT
                        c.relname depender,
                        c.relnamespace AS depender_nsp,
                        c1.relname AS dependee,
                        c1.relnamespace AS dependee_nsp
                    FROM
                        pg_rewrite AS rw,
                        pg_depend AS d,
                        pg_class AS c,
                        pg_class AS c1
                    WHERE
                        rw.ev_class = c.oid AND
                        rw.oid = d.objid AND
                        d.classid = 'pg_rewrite'::regclass AND
                        d.refclassid = 'pg_class'::regclass AND
                        d.refobjid = c1.oid AND
                        c1.relkind = 'v' AND
                        c.relname <> c1.relname
                    GROUP BY
                        depender, depender_nsp, dependee, dependee_nsp
                ) t1
            WHERE
                t1.depender_nsp = nsp1.oid AND
                t1.dependee_nsp = nsp2.oid
        """)

        self._view2view = defaultdict(list)
        for row in rows:
            key = (row['depender_schema'], row['depender'])
            val = (row['dependee_schema'], row['dependee'])
            self._view2view[key].append(val)

    """
    @brief  Filter out recursive view dependencies which are independent of
    MADLib UDFs/UDAs
    """
    def _filter_recursive_view_dependency(self):
        # Get recursive dependee list
        dependeelist = []
        checklist = self._view2proc
        while True:
            dependeelist.extend(checklist.keys())
            new_checklist = defaultdict(bool)
            for depender in self._view2view.keys():
                for dependee in self._view2view[depender]:
                    if dependee in checklist:
                        new_checklist[depender] = True
                        break
            if len(new_checklist) == 0:
                break
            else:
                checklist = new_checklist

        # Filter recursive dependencies not related with MADLib UDF/UDAs
        fil_view2view = defaultdict(list)
        for depender in self._view2view:
            dependee = self._view2view[depender]
            dependee = [r for r in dependee if r in dependeelist]
            if len(dependee) > 0:
                fil_view2view[depender] = dependee

        self._view2view = fil_view2view

    """
    @brief  Build the dependency graph (depender-to-dependee adjacency list)
    """
    def _build_dependency_graph(self, hasProcDependency = False):
        der2dee = self._view2view.copy()
        for view in self._view2proc:
            if view not in self._view2view:
                der2dee[view] = []
            if hasProcDependency:
                der2dee[view].extend(self._view2proc[view])

        graph = der2dee.copy()
        for der in der2dee:
            for dee in der2dee[der]:
                if dee not in graph:
                    graph[dee] = []
        return graph

    """
    @brief Check dependencies
    """
    def has_dependency(self):
        return len(self._view2proc) > 0

    """
    @brief Get the ordered views for creation
    """
    def get_create_order_views(self):
        graph = self._build_dependency_graph()
        ordered_views = []
        while True:
            remove_list = []
            for depender in graph:
                if len(graph[depender]) == 0:
                    ordered_views.append(depender)
                    remove_list.append(depender)
            for view in remove_list:
                del graph[view]
            for depender in graph:
                graph[depender] = [r for r in graph[depender]
                    if r not in remove_list]
            if len(remove_list) == 0:
                break
        return ordered_views

    """
    @brief Get the ordered views for dropping
    """
    def get_drop_order_views(self):
        ordered_views = self.get_create_order_views()
        ordered_views.reverse()
        return ordered_views

    """
    @brief Get the depended UDF/UDA signatures for comparison
    """
    def get_depended_func_signature(self, isagg = True):
        res = {}
        for procs in self._view2proc.values():
            for proc in procs:
                if proc[2] != isagg:
                    continue
                if (self._schema, proc) not in res:
                    funcinfo = self._get_function_info(proc[1])
                    signature = get_signature_for_compare(
                        self._schema, proc[0], funcinfo['rettype'], funcinfo['argument'])
                    res[signature] = True
        return res

    """
    @brief Get dependent UDAs
    """
    def get_depended_uda(self):
        res = []
        for procs in self._view2proc.values():
            for proc in procs:
                if proc[2] == False:
                    continue
                if (self._schema, proc) not in res:
                    res.append((self._schema, proc))
        res.sort()
        return res

    """
    @brief Get dependent UDFs
    """
    def get_depended_udf(self):
        res = []
        for procs in self._view2proc.values():
            for proc in procs:
                if proc[2] == True:
                    continue
                if (self._schema, proc) not in res:
                    res.append((self._schema, proc))
        res.sort()
        return res

    """
    @brief Save and drop the dependent views
    """
    def save_and_drop(self):
        self._view2def = {}
        ordered_views = self.get_drop_order_views()
        # Save views
        for view in ordered_views:
            row = self._run_sql("""
                SELECT
                    schemaname, viewname, viewowner, definition
                FROM
                    pg_views
                WHERE
                    schemaname = '{schemaname}' AND
                    viewname = '{viewname}'
            """.format(schemaname=view[0], viewname=view[1]))
            self._view2def[view] = row[0]

        # Drop views
        for view in ordered_views:
            self._run_sql("""
                DROP VIEW IF EXISTS {schema}.{view}
                """.format(schema=view[0], view=view[1]))

    """
    @brief Restore the dependent views
    """
    def restore(self):
        ordered_views = self.get_create_order_views()
        for view in ordered_views:
            row = self._view2def[view]
            schema = row['schemaname']
            view = row['viewname']
            owner = row['viewowner']
            definition = row['definition']
            self._run_sql("""
                --Alter view not supported by GP, so use set/reset role as a
                --workaround
                --ALTER VIEW {schema}.{view} OWNER TO {owner}
                SET ROLE {owner};
                CREATE OR REPLACE VIEW {schema}.{view} AS {definition};
                RESET ROLE
                """.format(
                    schema=schema, view=view,
                    definition=definition, owner=owner))

    def _node_to_str(self, node):
        res = ''
        if len(node) == 2:
            res = '%s.%s' % (node[0], node[1])
        else:
            res = '%s.%s{oid=%s,isagg=%s}' % (
                self._schema, node[0], node[1], node[2])
        return res

    def _nodes_to_str(self, nodes):
        res = []
        for node in nodes:
            res.append(self._node_to_str(node))
        return res

    """
    @brief Get the dependency graph string for print
    """
    def get_dependency_graph_str(self):
        graph = self._build_dependency_graph(True)
        nodes = graph.keys()
        nodes.sort()
        res = '\t\tDependency Graph (Depender-Dependee Adjacency List):\n'
        for node in nodes:
            res += "\t\t%s -> %s\n" % (
                self._node_to_str(node), self._nodes_to_str(graph[node]))
        return res[:-1]

"""
@brief This class detects the table dependencies on MADLib UDTs defined in the
current version
"""
class TableDependency(UpgradeBase):
    def __init__(self, schema, portid, con_args):
        UpgradeBase.__init__(self, schema, portid, con_args)
        self._table2type = None
        self._detect_table_dependency()

    """
    @brief Detect the table dependencies on MADLib UDTs
    """
    def _detect_table_dependency(self):
        rows = self._run_sql("""
            SELECT
                nsp.nspname AS schema,
                relname AS relation,
                attname AS column,
                typname AS type
            FROM
                pg_attribute a,
                pg_class c,
                pg_type t,
                pg_namespace nsp
            WHERE
                t.typnamespace = {schema_madlib_oid}
                AND a.atttypid = t.oid
                AND c.oid = a.attrelid
                AND c.relnamespace = nsp.oid
                AND c.relkind = 'r'
            ORDER BY
                nsp.nspname, relname, attname, typname
            """.format(schema_madlib_oid=self._schema_oid))

        self._table2type = defaultdict(list)
        for row in rows:
            key= (row['schema'], row['relation'])
            self._table2type[key].append(
                (row['column'], row['type']))

    """
    @brief Check dependencies
    """
    def has_dependency(self):
        return len(self._table2type) > 0

    """
    @brief Get the list of depended UDTs
    """
    def get_depended_udt(self):
        res = defaultdict(bool)
        for table in self._table2type:
            for (col, typ) in self._table2type[table]:
                if typ not in res:
                    res[typ] = True
        return res

    """
    @brief Get the dependencies in string for print
    """
    def get_dependency_str(self):
        res = '\t\tTable Dependency (schema.table.column -> type):\n'
        for table in self._table2type:
            for (col, udt) in self._table2type[table]:
                res += "\t\t%s.%s.%s -> %s\n" % (table[0], table[1], col, udt)
        return res[:-1]

"""
@brief This class removes sql statements from a sql script which should not be
executed during the upgrade
"""
class ScriptCleaner(UpgradeBase):
    def __init__(self, schema, portid, con_args, change_handler):
        UpgradeBase.__init__(self, schema, portid, con_args)
        self._sql = None
        self._existing_uda = None
        self._existing_udt = None
        self._get_existing_uda()
        self._get_existing_udt()
        self._ch = change_handler

    """
    @breif Get the existing UDAs in the current version
    """
    def _get_existing_uda(self):
        rows = self._run_sql("""
            SELECT
                max(proname) AS proname,
                max(rettype) AS rettype,
                array_to_string(array_agg(argtype order by i), ',') AS argument
            FROM
            (
                SELECT
                    p.oid AS procoid,
                    proname,
                    textin(regtypeout(prorettype::regtype)) AS rettype,

                    CASE array_upper(proargtypes,1) WHEN -1 THEN ''
                        ELSE textin(regtypeout(unnest(proargtypes)::regtype))
                    END AS argtype,

                    CASE array_upper(proargtypes,1) WHEN -1 THEN 1
                        ELSE generate_series(0, array_upper(proargtypes, 1))
                    END AS i
                FROM
                    pg_proc AS p,
                    pg_namespace AS nsp
                WHERE
                    p.pronamespace = nsp.oid AND
                    p.proisagg = true AND
                    nsp.nspname = '{schema}'
            ) AS f
            GROUP BY
                procoid
            """.format(schema=self._schema))
        self._existing_uda = {}
        for row in rows:
            # Consider about the overloaded aggregates
            if row['proname'] not in self._existing_uda:
                self._existing_uda[row['proname']] = []
            self._existing_uda[row['proname']].append({
                'rettype': ['rettype'],
                'argument': row['argument']})

    """
    @brief Get the existing UDTs in the current version
    """
    def _get_existing_udt(self):
        rows = self._run_sql("""
            SELECT
                typname
            FROM
                pg_type AS t,
                pg_namespace AS nsp
            WHERE
                t.typnamespace = nsp.oid AND
                nsp.nspname = '{schema}'
            """.format(schema=self._schema))
        self._existing_udt = []
        for row in rows:
            self._existing_udt.append(row['typname'])

    """
    @note The changer_handler is needed for deciding which sql statements to
    remove
    """
    def get_change_handler(self):
        return self._ch

    """
    @brief Remove comments in the sql script
    """
    def _clean_comment(self):
        pattern = re.compile(r"""(/\*(.|[\r\n])*?\*/)|(--(.*|[\r\n]))""")
        self._sql = re.sub(pattern, '', self._sql).strip()

    """
    @breif Remove "drop/create type" statements in the sql script
    """
    def _clean_type(self):
        # remove 'drop type'
        pattern = re.compile('DROP(\s+)TYPE(.*?);', re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, '', self._sql)

        # remove 'create type'
        udt_str = ''
        for udt in self._existing_udt:
            if udt in self._ch.get_udt():
                continue
            if udt_str == '':
                udt_str += udt
            else:
                udt_str += '|' + udt
        p_str = 'CREATE(\s+)TYPE(\s+)%s\.(%s)(.*?);' % (self._schema.upper(), udt_str)
        pattern = re.compile(p_str, re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, '', self._sql)

    """
    @brief Remove "drop/create cast" statements in the sql script
    """
    def _clean_cast(self):
        # remove 'drop cast'
        pattern = re.compile('DROP(\s+)CAST(.*?);', re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, '', self._sql)

        # remove 'create cast'
        udc_str = ''
        for udc in self._ch.get_udc():
            if udc_str == '':
                udc_str += '%s\s+AS\s+%s' % (
                    self._ch.get_udc()[udc]['sourcetype'], self._ch.get_udc()[udc]['targettype'])
            else:
                udc_str += '|' + '%s\s+AS\s+%s' % (
                    self._ch.get_udc()[udc]['sourcetype'], self._ch.get_udc()[udc]['targettype'])

        pattern = re.compile('CREATE\s+CAST(.*?);', re.DOTALL | re.IGNORECASE)
        if udc_str != '':
            pattern = re.compile('CREATE\s+CAST\s*\(\s*(?!%s)(.*?);' % udc_str , re.DOTALL | re.IGNORECASE)
        self._sql = re.sub(pattern, '', self._sql)

    """
    @brief Remove "drop/create operator" statements in the sql script
    """
    def _clean_operator(self):
        # remove 'drop operator'
        pattern = re.compile('DROP(\s+)OPERATOR(.*?);', re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, '', self._sql)

        # remove 'create operator'
        pattern = re.compile(r"""CREATE(\s+)OPERATOR(.*?);""", re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, '', self._sql)

    """
    @brief Rewrite the type
    """
    def _rewrite_type_in(self, arg):
        type_mapper = {
            'smallint':'(int2|smallint)',
            'integer':'(int|int4|integer)',
            'bigint':'(int8|bigint)',
            'double precision':'(float8|double precision)',
            'real':'(float4|real)',
            'character varying':'(varchar|character varying)'
        }
        for typ in type_mapper:
            arg = arg.replace(typ, type_mapper[typ])
        return arg.replace('[', '\[').replace(']', '\]')

    """
    @brief Remove "drop/create aggregate" statements in the sql script
    """
    def _clean_aggregate(self):
        # remove 'drop aggregate'
        pattern = re.compile('DROP(\s+)AGGREGATE(.*?);', re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, '', self._sql)

        # remove 'create aggregate'
        uda_str = ''
        for uda in self._existing_uda:
            for item in self._existing_uda[uda]:
                if uda in self._ch.get_uda():
                    items = self._ch.get_uda()[uda]
                    if item in items:
                        continue
                p_arg_str = ''
                argument = item['argument']
                args = argument.split(',')
                for arg in args:
                    arg = self._rewrite_type_in(arg.strip())
                    if p_arg_str == '':
                        p_arg_str += '%s\s*' % arg
                    else:
                        p_arg_str += ',\s*%s\s*' % arg
                p_str = 'CREATE\s+(ORDERED\s)*\s*AGGREGATE\s+%s\.(%s)\s*\(\s*%s\)(.*?);' % (
                    self._schema.upper(), uda, p_arg_str)
                pattern = re.compile(p_str, re.DOTALL | re.IGNORECASE);
                self._sql = re.sub(pattern, '', self._sql)

    """
    @brief Remove "drop function" statements and rewrite "create function"
    statements in the sql script
    @note We don't drop any function
    """
    def _clean_function(self):
        # remove 'drop function'
        pattern = re.compile(r"""DROP(\s+)FUNCTION(.*?);""", re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, '', self._sql)
        # replace 'create function' with 'create or replace function'
        pattern = re.compile(r"""CREATE(\s+)FUNCTION""", re.DOTALL | re.IGNORECASE);
        self._sql = re.sub(pattern, 'CREATE OR REPLACE FUNCTION', self._sql)

    """
    @brief Entry function for cleaning the sql script
    """
    def cleanup(self, sql):
        self._sql = sql
        self._clean_comment()
        self._clean_type()
        self._clean_cast()
        self._clean_operator()
        self._clean_aggregate()
        self._clean_function()
        return self._sql