<commit_before>import importlib
import datetime
import time

from sqlalchemy.exc import OperationalError
from sqlalchemy.sql.ddl import DDL

from wopmars.framework.bdd.Base import Base
from sqlalchemy import Column, Integer, String, ForeignKey, DateTime
from sqlalchemy.orm import relationship, reconstructor

from wopmars.framework.bdd.SQLManager import SQLManager
from wopmars.framework.bdd.tables.IOPut import IOPut
from wopmars.framework.bdd.tables.ModificationTable import ModificationTable
from wopmars.framework.bdd.tables.ToolWrapper import ToolWrapper
from wopmars.utils.Logger import Logger
from wopmars.framework.bdd.tables.Type import Type
from sqlalchemy.sql.functions import func

class IODbPut(IOPut, Base):
    """
    This class extends IOPut and is specific to the input or output tables. It is the model which store the references
    to the actual tables needed by the user. The table ``wom_table`` associated with this model contains the
    following fields:

    - id: INTEGER - primary key - autoincrement - arbitrary ID
    - tablename: VARCHAR(255) - foreign key to the associated table: :class:`wopmars.framework.bdd.tables.ModificationTable.ModificationTable` - the name of the referenced table
    - model: VARCHAR(255) - the path to the model (in python notation)
    - rule_id: INTEGER - foreign key to the associated rule ID: :class:`wopmars.framework.bdd.tables.ToolWrapper.ToolWrapper`
    - type_id: INTEGER - foreign key to the associated type ID: :class:`wopmars.framework.bdd.tables.Type.Type`
    - used_at: DATE - date at which the table have been used
    """
    __tablename__ = "wom_table"

    id = Column(Integer, primary_key=True, autoincrement=True)
    tablename = Column(String(255), ForeignKey("wom_modification_table.table_name"))
    model = Column(String(255))
    rule_id = Column(Integer, ForeignKey("wom_rule.id"))
    type_id = Column(Integer, ForeignKey("wom_type.id"))
    used_at = Column(DateTime, nullable=True)

    # One table is in one rule
    rule = relationship("ToolWrapper", back_populates="tables", enable_typechecks=False)
    # One file has One type
    type = relationship("Type", back_populates="tables")

    modification = relationship("ModificationTable", back_populates="tables")

    # all the model names met since the begining of this instance of WopMaRS
    tablemodelnames = set()
    # al the table names met since the begining of this instance of WopMaRS
    tablenames = set()

    def __init__(self, model, tablename):
        """
        self.__table is initialized to None and will contain the model of this IODbPut object.

        :param model: The path to the model
        :type model: str
        :param tablename: The name of the table associated with the model
        :type tablename: str
        """
        # The file containing the table should be in PYTHONPATH
        Base.__init__(self, model=model, tablename=tablename)
        Logger.instance().debug(str(model) + " model loaded. Tablename: " + str(tablename))
        self.__table = None

    @reconstructor
    def init_on_load(self):
        """
        This is used by SQLAlchemy to regenerate the right object when loading it from the database. Here, we need to
        get back the actual Model from the model name and store it in self.__table.
        """
        for table in IODbPut.tablemodelnames:
            mod = importlib.import_module(table)
            try:
                if table == self.model:
                    # todo tabling
                    self.__table = eval("mod." + self.model.split(".")[-1])
            except AttributeError as e:
                raise e
        Logger.instance().debug(self.tablename + " table class reloaded. Model: " + self.model)

    def set_table(self, model):
        self.__table = model

    def get_table(self):
        return self.__table

    @staticmethod
    def create_triggers():
        """
        Create an INSERT, UPDATE, DELETE trigger on the tables created by the user in order to store the modifications date.
        """
        stmt = ["INSERT", "UPDATE", "DELETE"]
        for tablename in Base.metadata.tables:
            if tablename[:4] != "wom_":
                for s in stmt:
                    data={"statement": str(s), "tablename": str(tablename)} 
                    if SQLManager.instance().__dict__['d_database_config']['db_connection'] == 'sqlite':
                        sql_trigger = """
CREATE TRIGGER IF NOT EXISTS modification_%(tablename)s AFTER %(statement)s ON %(tablename)s
BEGIN
UPDATE wom_modification_table SET date = CURRENT_TIMESTAMP WHERE table_name = '%(tablename)s';
END;
    """%data
                    elif SQLManager.instance().__dict__['d_database_config']['db_connection'] == 'mysql':
                        sql_trigger = """
CREATE TRIGGER IF NOT EXISTS modification_%(tablename)s AFTER %(statement)s ON %(tablename)s for each row UPDATE wom_modification_table SET date = CURRENT_TIMESTAMP WHERE table_name = '%(tablename)s';
    """%data
                        obj_ddl = DDL(sql_trigger)
                        SQLManager.instance().create_trigger(Base.metadata.tables[tablename], obj_ddl)
                    elif SQLManager.instance().__dict__['d_database_config']['db_connection'] == 'postgresql':
                        sql_trigger = """
CREATE OR REPLACE FUNCTION modif_%(statement)s_%(tablename)s() RETURNS TRIGGER AS $modif_%(statement)s_%(tablename)s$
BEGIN
UPDATE wom_modification_table SET date = CURRENT_TIMESTAMP WHERE table_name = "%(tablename)s";
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$modif_%(statement)s_%(tablename)s$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS modif_%(statement)s_%(tablename)s ON "%(tablename)s";
CREATE TRIGGER modif_%(statement)s_%(tablename)s AFTER INSERT ON "%(tablename)s" FOR EACH ROW EXECUTE PROCEDURE modif_%(statement)s_%(tablename)s();
    """%data
                        obj_ddl = DDL(sql_trigger)
                        SQLManager.instance().create_trigger(Base.metadata.tables[tablename], obj_ddl)<commit_msg>ENH Improved MySQL and PostgreSQL triggers<commit_after>import importlib
import datetime
import time

from sqlalchemy.exc import OperationalError
from sqlalchemy.sql.ddl import DDL

from wopmars.framework.bdd.Base import Base
from sqlalchemy import Column, Integer, String, ForeignKey, DateTime
from sqlalchemy.orm import relationship, reconstructor

from wopmars.framework.bdd.SQLManager import SQLManager
from wopmars.framework.bdd.tables.IOPut import IOPut
from wopmars.framework.bdd.tables.ModificationTable import ModificationTable
from wopmars.framework.bdd.tables.ToolWrapper import ToolWrapper
from wopmars.utils.Logger import Logger
from wopmars.framework.bdd.tables.Type import Type
from sqlalchemy.sql.functions import func

class IODbPut(IOPut, Base):
    """
    This class extends IOPut and is specific to the input or output tables. It is the model which store the references
    to the actual tables needed by the user. The table ``wom_table`` associated with this model contains the
    following fields:

    - id: INTEGER - primary key - autoincrement - arbitrary ID
    - tablename: VARCHAR(255) - foreign key to the associated table: :class:`wopmars.framework.bdd.tables.ModificationTable.ModificationTable` - the name of the referenced table
    - model: VARCHAR(255) - the path to the model (in python notation)
    - rule_id: INTEGER - foreign key to the associated rule ID: :class:`wopmars.framework.bdd.tables.ToolWrapper.ToolWrapper`
    - type_id: INTEGER - foreign key to the associated type ID: :class:`wopmars.framework.bdd.tables.Type.Type`
    - used_at: DATE - date at which the table have been used
    """
    __tablename__ = "wom_table"

    id = Column(Integer, primary_key=True, autoincrement=True)
    tablename = Column(String(255), ForeignKey("wom_modification_table.table_name"))
    model = Column(String(255))
    rule_id = Column(Integer, ForeignKey("wom_rule.id"))
    type_id = Column(Integer, ForeignKey("wom_type.id"))
    used_at = Column(DateTime, nullable=True)

    # One table is in one rule
    rule = relationship("ToolWrapper", back_populates="tables", enable_typechecks=False)
    # One file has One type
    type = relationship("Type", back_populates="tables")

    modification = relationship("ModificationTable", back_populates="tables")

    # all the model names met since the begining of this instance of WopMaRS
    tablemodelnames = set()
    # al the table names met since the begining of this instance of WopMaRS
    tablenames = set()

    def __init__(self, model, tablename):
        """
        self.__table is initialized to None and will contain the model of this IODbPut object.

        :param model: The path to the model
        :type model: str
        :param tablename: The name of the table associated with the model
        :type tablename: str
        """
        # The file containing the table should be in PYTHONPATH
        Base.__init__(self, model=model, tablename=tablename)
        Logger.instance().debug(str(model) + " model loaded. Tablename: " + str(tablename))
        self.__table = None

    @reconstructor
    def init_on_load(self):
        """
        This is used by SQLAlchemy to regenerate the right object when loading it from the database. Here, we need to
        get back the actual Model from the model name and store it in self.__table.
        """
        for table in IODbPut.tablemodelnames:
            mod = importlib.import_module(table)
            try:
                if table == self.model:
                    # todo tabling
                    self.__table = eval("mod." + self.model.split(".")[-1])
            except AttributeError as e:
                raise e
        Logger.instance().debug(self.tablename + " table class reloaded. Model: " + self.model)

    def set_table(self, model):
        self.__table = model

    def get_table(self):
        return self.__table

    @staticmethod
    def create_triggers():
        """
        Create an INSERT, UPDATE, DELETE trigger on the tables created by the user in order to store the modifications date.
        """
        stmt = ["INSERT", "UPDATE", "DELETE"]
        for tablename in Base.metadata.tables:
            if tablename[:4] != "wom_":
                for s in stmt:
                    data={"statement": str(s), "tablename": str(tablename)} 
                    if SQLManager.instance().__dict__['d_database_config']['db_connection'] == 'sqlite':
                        sql_trigger = """
CREATE TRIGGER IF NOT EXISTS modification_%(tablename)s AFTER %(statement)s ON %(tablename)s
BEGIN
UPDATE wom_modification_table SET date = CURRENT_TIMESTAMP WHERE table_name = '%(tablename)s';
END;
    """%data
                    elif SQLManager.instance().__dict__['d_database_config']['db_connection'] == 'mysql':
                        sql_trigger = """
CREATE TRIGGER IF NOT EXISTS modification_%(tablename)s_%(statement)s AFTER %(statement)s ON %(tablename)s for each row UPDATE wom_modification_table SET date = CURRENT_TIMESTAMP WHERE table_name = '%(tablename)s';
    """%data
                        obj_ddl = DDL(sql_trigger)
                        SQLManager.instance().create_trigger(Base.metadata.tables[tablename], obj_ddl)
                    elif SQLManager.instance().__dict__['d_database_config']['db_connection'] == 'postgresql':
                        sql_trigger = """
CREATE OR REPLACE FUNCTION modification_%(statement)s_%(tablename)s() RETURNS TRIGGER AS $modification_%(statement)s_%(tablename)s$
BEGIN
UPDATE wom_modification_table SET date = CURRENT_TIMESTAMP WHERE table_name = "%(tablename)s";
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$modification_%(statement)s_%(tablename)s$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS modification_%(statement)s_%(tablename)s ON "%(tablename)s";
CREATE TRIGGER modification_%(statement)s_%(tablename)s AFTER INSERT ON "%(tablename)s" FOR EACH ROW EXECUTE PROCEDURE modification_%(statement)s_%(tablename)s();
    """%data
                        obj_ddl = DDL(sql_trigger)
                        SQLManager.instance().create_trigger(Base.metadata.tables[tablename], obj_ddl)
{'fraction_non_alphanumeric': 0.09400310889307044, 'fraction_numerical': 0.0022907633150617687, 'mean_word_length': 3.200687285223368, 'pattern_counts': {'":': 6, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3506119', 'n_tokens_mistral': 3650, 'n_tokens_neox': 3467, 'n_words': 1060}