def prepare(self): """Prepares needed triggers and functions for those triggers""" self.cursor.execute(""" -- We need to create a before insert function CREATE OR REPLACE FUNCTION {parent_table}_insert_child() RETURNS TRIGGER AS $$ {partition_function} $$ LANGUAGE plpgsql; -- Then we create a trigger which calls the before insert function DO $$ BEGIN IF NOT EXISTS( SELECT 1 FROM information_schema.triggers WHERE event_object_table = '{parent_table}' AND trigger_name = 'before_insert_{parent_table}_trigger' ) THEN CREATE TRIGGER before_insert_{parent_table}_trigger BEFORE INSERT ON {parent_table} FOR EACH ROW EXECUTE PROCEDURE {parent_table}_insert_child(); END IF; END $$; -- Then we create a function to delete duplicate row from the master table after insert CREATE OR REPLACE FUNCTION {parent_table}_delete_master() RETURNS TRIGGER AS $$ BEGIN DELETE FROM ONLY {parent_table} WHERE {pk} = NEW.{pk}; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Lastly we create the after insert trigger that calls the after insert function DO $$ BEGIN IF NOT EXISTS( SELECT 1 FROM information_schema.triggers WHERE event_object_table = '{parent_table}' AND trigger_name = 'after_insert_{parent_table}_trigger' ) THEN CREATE TRIGGER after_insert_{parent_table}_trigger AFTER INSERT ON {parent_table} FOR EACH ROW EXECUTE PROCEDURE {parent_table}_delete_master(); END IF; END $$; """.format( pk=self.partition_pk.column, parent_table=self.table, partition_function=self._get_partition_function() )) transaction.commit_unless_managed()
def prepare(self): """Converts original table to partitioned one""" self.cursor.execute(""" -- We need to rebuild primary key for our partitioning to work ALTER table {parent_table} DROP PRIMARY KEY, add PRIMARY KEY (id, {partition_column}); """.format( parent_table=self.table, partition_column=self.partition_column, )) transaction.commit_unless_managed()
def prepare(self): """Converts original table to partitioned one""" self.cursor.execute(""" -- We need to rebuild primary key for our partitioning to work ALTER table {parent_table} DROP PRIMARY KEY, add PRIMARY KEY ({pk}, {partition_column}); """.format( pk=self.partition_pk.column, parent_table=self.table, partition_column=self.partition_column, )) transaction.commit_unless_managed()
def prepare(self): """Prepares needed triggers and functions for those triggers""" self.cursor.execute(""" -- We need to create a before insert function CREATE OR REPLACE FUNCTION {parent_table}_insert_child() RETURNS TRIGGER AS $$ {partition_function} $$ LANGUAGE plpgsql; -- Then we create a trigger which calls the before insert function DO $$ BEGIN IF NOT EXISTS( SELECT 1 FROM information_schema.triggers WHERE event_object_table = '{parent_table}' AND trigger_name = 'before_insert_{parent_table}_trigger' ) THEN CREATE TRIGGER before_insert_{parent_table}_trigger BEFORE INSERT ON {parent_table} FOR EACH ROW EXECUTE PROCEDURE {parent_table}_insert_child(); END IF; END $$; -- Then we create a function to delete duplicate row from the master table after insert CREATE OR REPLACE FUNCTION {parent_table}_delete_master() RETURNS TRIGGER AS $$ BEGIN DELETE FROM ONLY {parent_table} WHERE {pk} = NEW.{pk}; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Lastly we create the after insert trigger that calls the after insert function DO $$ BEGIN IF NOT EXISTS( SELECT 1 FROM information_schema.triggers WHERE event_object_table = '{parent_table}' AND trigger_name = 'after_insert_{parent_table}_trigger' ) THEN CREATE TRIGGER after_insert_{parent_table}_trigger AFTER INSERT ON {parent_table} FOR EACH ROW EXECUTE PROCEDURE {parent_table}_delete_master(); END IF; END $$; """.format(pk=self.partition_pk.column, parent_table=self.table, partition_function=self._get_partition_function())) transaction.commit_unless_managed()
def create(self): """Creates new partition""" self.cursor.execute(""" ALTER TABLE {parent_table} ADD PARTITION ( PARTITION {child_table} VALUES LESS THAN ({function}('{period_end}') + {addition}) ); """.format( child_table=self._get_name(), parent_table=self.table, function=self._get_partition_function(), period_end=self.datetime.get_period()[1], addition='86400' if self._get_column_type() == 'timestamp' else '1', )) transaction.commit_unless_managed()
def prepare(self): """Converts original table to partitioned one""" super(RangePartition, self).prepare() self.cursor.execute(""" -- We need to create zero partition to speed up things due to the partitioning -- implementation in the early versions of MySQL database (see bug #49754) ALTER TABLE {parent_table} PARTITION BY RANGE ({function}({partition_column}))( PARTITION {partition_pattern} VALUES LESS THAN (0) ); """.format( parent_table=self.table, partition_column=self.partition_column, partition_pattern=self._get_name(), function=self._get_partition_function(), )) transaction.commit_unless_managed()
def prepare(self): """Converts original table to partitioned one""" super(RangePartition, self).prepare() self.datetime.now = None self.cursor.execute(""" -- We need to create zero partition to speed up things due to the partitioning -- implementation in the early versions of MySQL database (see bug #49754) ALTER TABLE {parent_table} PARTITION BY RANGE ({function}({partition_column}))( PARTITION {partition_pattern} VALUES LESS THAN (0) ); """.format( parent_table=self.table, partition_column=self.partition_column, partition_pattern=self._get_name(), function=self._get_partition_function(), )) transaction.commit_unless_managed()