|
|
# -*- coding: utf-8 -*-
|
|
|
|
|
|
# Copyright 2010 - 2017 RhodeCode GmbH and the AppEnlight project authors
|
|
|
#
|
|
|
# Licensed under the Apache License, Version 2.0 (the "License");
|
|
|
# you may not use this file except in compliance with the License.
|
|
|
# You may obtain a copy of the License at
|
|
|
#
|
|
|
# http://www.apache.org/licenses/LICENSE-2.0
|
|
|
#
|
|
|
# Unless required by applicable law or agreed to in writing, software
|
|
|
# distributed under the License is distributed on an "AS IS" BASIS,
|
|
|
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
|
|
# See the License for the specific language governing permissions and
|
|
|
# limitations under the License.
|
|
|
|
|
|
"""initial tables
|
|
|
|
|
|
Revision ID: 55b6e612672f
|
|
|
Revises: None
|
|
|
Create Date: 2014-10-13 23:47:38.295159
|
|
|
|
|
|
"""
|
|
|
|
|
|
# revision identifiers, used by Alembic.
|
|
|
revision = '55b6e612672f'
|
|
|
down_revision = None
|
|
|
|
|
|
from alembic import op
|
|
|
import sqlalchemy as sa
|
|
|
|
|
|
|
|
|
def upgrade():
|
|
|
op.add_column('users', sa.Column('first_name', sa.Unicode(25)))
|
|
|
op.add_column('users', sa.Column('last_name', sa.Unicode(50)))
|
|
|
op.add_column('users', sa.Column('company_name', sa.Unicode(255)))
|
|
|
op.add_column('users', sa.Column('company_address', sa.Unicode(255)))
|
|
|
op.add_column('users', sa.Column('phone1', sa.Unicode(25)))
|
|
|
op.add_column('users', sa.Column('phone2', sa.Unicode(25)))
|
|
|
op.add_column('users', sa.Column('zip_code', sa.Unicode(25)))
|
|
|
op.add_column('users', sa.Column('default_report_sort', sa.Unicode(20), nullable=False, server_default="newest"))
|
|
|
op.add_column('users', sa.Column('city', sa.Unicode(128)))
|
|
|
op.add_column('users', sa.Column('notes', sa.UnicodeText, server_default=''))
|
|
|
op.add_column('users', sa.Column('notifications', sa.Boolean(), nullable=False, server_default='true'))
|
|
|
op.add_column('users', sa.Column('registration_ip', sa.Unicode(40), nullable=False, server_default=''))
|
|
|
|
|
|
op.create_table(
|
|
|
'integrations',
|
|
|
sa.Column('id', sa.Integer(), primary_key=True),
|
|
|
sa.Column('resource_id', sa.Integer(),
|
|
|
sa.ForeignKey('resources.resource_id', onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
sa.Column('integration_name', sa.Unicode(64)),
|
|
|
sa.Column('config', sa.dialects.postgresql.JSON, nullable=False),
|
|
|
sa.Column('modified_date', sa.DateTime(), nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('external_id', sa.Unicode(255)),
|
|
|
sa.Column('external_id2', sa.Unicode(255))
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'alert_channels',
|
|
|
sa.Column('owner_id', sa.Integer(),
|
|
|
sa.ForeignKey('users.id', onupdate='cascade',
|
|
|
ondelete='cascade'), nullable=False),
|
|
|
sa.Column('channel_name', sa.Unicode(25), nullable=False),
|
|
|
sa.Column('channel_value', sa.Unicode(80), nullable=False),
|
|
|
sa.Column('channel_json_conf', sa.dialects.postgresql.JSON, nullable=False),
|
|
|
sa.Column('channel_validated', sa.Boolean, nullable=False, server_default='False'),
|
|
|
sa.Column('send_alerts', sa.Boolean, nullable=False, server_default='True'),
|
|
|
sa.Column('notify_only_first', sa.Boolean, nullable=False, server_default='False'),
|
|
|
sa.Column('daily_digest', sa.Boolean, nullable=False, server_default='True'),
|
|
|
sa.Column('pkey', sa.Integer(), primary_key=True),
|
|
|
sa.Column('integration_id', sa.Integer,
|
|
|
sa.ForeignKey('integrations.id', onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
)
|
|
|
op.create_unique_constraint('uq_alert_channels', 'alert_channels',
|
|
|
["owner_id", "channel_name", "channel_value"])
|
|
|
|
|
|
op.create_table(
|
|
|
'alert_channels_actions',
|
|
|
sa.Column('owner_id', sa.Integer(), nullable=False),
|
|
|
sa.Column('resource_id', sa.Integer(),
|
|
|
sa.ForeignKey('resources.resource_id', onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
sa.Column('pkey', sa.Integer(), primary_key=True),
|
|
|
sa.Column('action', sa.Unicode(10), nullable=False, server_default='always'),
|
|
|
sa.Column('rule', sa.dialects.postgresql.JSON),
|
|
|
sa.Column('type', sa.Unicode(10), index=True),
|
|
|
sa.Column('other_id', sa.Unicode(40), index=True),
|
|
|
sa.Column('config', sa.dialects.postgresql.JSON),
|
|
|
sa.Column('name', sa.Unicode(255), server_default='')
|
|
|
)
|
|
|
|
|
|
|
|
|
op.create_table(
|
|
|
'application_postprocess_conf',
|
|
|
sa.Column('pkey', sa.Integer(), primary_key=True),
|
|
|
sa.Column('do', sa.Unicode(25), nullable=False),
|
|
|
sa.Column('new_value', sa.UnicodeText(), nullable=False, server_default=''),
|
|
|
sa.Column('resource_id', sa.Integer(),
|
|
|
sa.ForeignKey('resources.resource_id',
|
|
|
onupdate='cascade',
|
|
|
ondelete='cascade'), nullable=False),
|
|
|
sa.Column('rule', sa.dialects.postgresql.JSON),
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'applications',
|
|
|
sa.Column('resource_id', sa.Integer(),
|
|
|
sa.ForeignKey('resources.resource_id', onupdate='cascade',
|
|
|
ondelete='cascade'), nullable=False,
|
|
|
primary_key=True, autoincrement=False),
|
|
|
sa.Column('domains', sa.UnicodeText, nullable=False),
|
|
|
sa.Column('api_key', sa.Unicode(32), nullable=False, index=True),
|
|
|
sa.Column('default_grouping', sa.Unicode(20), nullable=False, server_default='url_type'),
|
|
|
sa.Column('public_key', sa.Unicode(32), nullable=False, index=True),
|
|
|
sa.Column('error_report_threshold', sa.Integer(), server_default='10', nullable=False),
|
|
|
sa.Column('slow_report_threshold', sa.Integer(), server_default='10', nullable=False),
|
|
|
sa.Column('apdex_threshold', sa.Float(), server_default='0.7', nullable=False),
|
|
|
sa.Column('allow_permanent_storage', sa.Boolean(), server_default="false", nullable=False),
|
|
|
)
|
|
|
op.create_unique_constraint(None, 'applications',
|
|
|
["public_key"])
|
|
|
op.create_unique_constraint(None, 'applications',
|
|
|
["api_key"])
|
|
|
|
|
|
op.create_table(
|
|
|
'metrics',
|
|
|
sa.Column('pkey', sa.types.BigInteger, nullable=False, primary_key=True),
|
|
|
sa.Column('resource_id', sa.Integer(),
|
|
|
sa.ForeignKey('resources.resource_id',
|
|
|
onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
sa.Column('timestamp', sa.DateTime),
|
|
|
sa.Column('namespace', sa.Unicode(255)),
|
|
|
sa.Column('tags', sa.dialects.postgresql.JSON, server_default="{}")
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'events',
|
|
|
sa.Column('id', sa.Integer, nullable=False, primary_key=True),
|
|
|
sa.Column('start_date', sa.DateTime, nullable=False, index=True),
|
|
|
sa.Column('end_date', sa.DateTime),
|
|
|
sa.Column('status', sa.Integer(), nullable=False, index=True),
|
|
|
sa.Column('event_type', sa.Integer(), nullable=False, index=True),
|
|
|
sa.Column('origin_user_id', sa.Integer()),
|
|
|
sa.Column('target_user_id', sa.Integer()),
|
|
|
sa.Column('resource_id', sa.Integer(), index=True),
|
|
|
sa.Column('text', sa.UnicodeText, server_default=''),
|
|
|
sa.Column('values', sa.dialects.postgresql.JSON),
|
|
|
sa.Column('target_id', sa.Integer()),
|
|
|
sa.Column('target_uuid', sa.Unicode(40), index=True)
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'logs',
|
|
|
sa.Column('log_id', sa.types.BigInteger, nullable=False, primary_key=True),
|
|
|
sa.Column('resource_id', sa.Integer(),
|
|
|
sa.ForeignKey('resources.resource_id',
|
|
|
onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
sa.Column('log_level', sa.SmallInteger(), nullable=False),
|
|
|
sa.Column('primary_key', sa.Unicode(128), nullable=True),
|
|
|
sa.Column('message', sa.UnicodeText, nullable=False, server_default=''),
|
|
|
sa.Column('timestamp', sa.DateTime),
|
|
|
sa.Column('namespace', sa.Unicode(255)),
|
|
|
sa.Column('request_id', sa.Unicode(40)),
|
|
|
sa.Column('tags', sa.dialects.postgresql.JSON, server_default="{}"),
|
|
|
sa.Column('permanent', sa.Boolean(), server_default="false",
|
|
|
nullable=False)
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'reports_groups',
|
|
|
sa.Column('id', sa.types.BigInteger, primary_key=True),
|
|
|
sa.Column('resource_id', sa.Integer,
|
|
|
sa.ForeignKey('resources.resource_id', onupdate='cascade',
|
|
|
ondelete='cascade'), nullable=False),
|
|
|
sa.Column('priority', sa.Integer, nullable=False, server_default="5"),
|
|
|
sa.Column('first_timestamp', sa.DateTime(), nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('last_timestamp', sa.DateTime()),
|
|
|
sa.Column('error', sa.UnicodeText, nullable=False, server_default=""),
|
|
|
sa.Column('grouping_hash', sa.Unicode(40), nullable=False, server_default=""),
|
|
|
sa.Column('triggered_postprocesses_ids', sa.dialects.postgresql.JSON, nullable=False, server_default="[]"),
|
|
|
sa.Column('report_type', sa.Integer, nullable=False, server_default="0"),
|
|
|
sa.Column('total_reports', sa.Integer, nullable=False, server_default="0"),
|
|
|
sa.Column('last_report', sa.Integer, nullable=False, server_default="0"),
|
|
|
sa.Column('occurences', sa.Integer, nullable=False, server_default="1"),
|
|
|
sa.Column('average_duration', sa.Float(), nullable=False, server_default="0"),
|
|
|
sa.Column('summed_duration', sa.Float(), nullable=False, server_default="0"),
|
|
|
sa.Column('notified', sa.Boolean, nullable=False, server_default="False"),
|
|
|
sa.Column('fixed', sa.Boolean, nullable=False, server_default="False"),
|
|
|
sa.Column('public', sa.Boolean, nullable=False, server_default="False"),
|
|
|
sa.Column('read', sa.Boolean, nullable=False, server_default="False"),
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'reports',
|
|
|
sa.Column('id', sa.types.BigInteger, primary_key=True),
|
|
|
sa.Column('group_id', sa.types.BigInteger,
|
|
|
sa.ForeignKey('reports_groups.id', onupdate='cascade',
|
|
|
ondelete='cascade'), nullable=False, index=True),
|
|
|
sa.Column('resource_id', sa.Integer, nullable=False, index=True),
|
|
|
sa.Column('report_type', sa.Integer, nullable=False, server_default="0"),
|
|
|
sa.Column('error', sa.UnicodeText, nullable=False, server_default=""),
|
|
|
sa.Column('extra', sa.dialects.postgresql.JSON, nullable=False, server_default="{}"),
|
|
|
sa.Column('request', sa.dialects.postgresql.JSON, nullable=False, server_default="{}"),
|
|
|
sa.Column('tags', sa.dialects.postgresql.JSON, nullable=False, server_default="{}"),
|
|
|
sa.Column('ip', sa.Unicode(39), nullable=False, server_default=""),
|
|
|
sa.Column('username', sa.Unicode(255), nullable=False, server_default=""),
|
|
|
sa.Column('user_agent', sa.Unicode(512), nullable=False, server_default=""),
|
|
|
sa.Column('url', sa.UnicodeText, nullable=False, server_default=""),
|
|
|
sa.Column('request_id', sa.Unicode(40), nullable=False, server_default=""),
|
|
|
sa.Column('request_stats', sa.dialects.postgresql.JSON, nullable=False, server_default="{}"),
|
|
|
sa.Column('traceback', sa.dialects.postgresql.JSON, nullable=False, server_default="{}"),
|
|
|
sa.Column('traceback_hash', sa.Unicode(40), nullable=False, server_default=""),
|
|
|
sa.Column('start_time', sa.DateTime(), nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('end_time', sa.DateTime()),
|
|
|
sa.Column('report_group_time', sa.DateTime, index=True, nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('duration', sa.Float(), nullable=False, server_default="0"),
|
|
|
sa.Column('http_status', sa.Integer, index=True),
|
|
|
sa.Column('url_domain', sa.Unicode(128)),
|
|
|
sa.Column('url_path', sa.UnicodeText),
|
|
|
sa.Column('language', sa.Integer, server_default="0"),
|
|
|
)
|
|
|
op.create_index(None, 'reports',
|
|
|
[sa.text("(tags ->> 'server_name')")])
|
|
|
op.create_index(None, 'reports',
|
|
|
[sa.text("(tags ->> 'view_name')")])
|
|
|
|
|
|
op.create_table(
|
|
|
'reports_assignments',
|
|
|
sa.Column('group_id', sa.types.BigInteger, nullable=False, primary_key=True),
|
|
|
sa.Column('owner_id', sa.Integer,
|
|
|
sa.ForeignKey('users.id', onupdate='cascade',ondelete='cascade'),
|
|
|
nullable=False, primary_key=True),
|
|
|
sa.Column('report_time', sa.DateTime, nullable=False)
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'reports_comments',
|
|
|
sa.Column('comment_id', sa.Integer, primary_key=True),
|
|
|
sa.Column('body', sa.UnicodeText, nullable=False, server_default=''),
|
|
|
sa.Column('owner_id', sa.Integer,
|
|
|
sa.ForeignKey('users.id', onupdate='cascade',
|
|
|
ondelete='set null'), nullable=True),
|
|
|
sa.Column('created_timestamp', sa.DateTime, nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('report_time', sa.DateTime, nullable=False),
|
|
|
sa.Column('group_id', sa.types.BigInteger, nullable=False)
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'reports_stats',
|
|
|
sa.Column('resource_id', sa.Integer, nullable=False, index=True),
|
|
|
sa.Column('start_interval', sa.DateTime, nullable=False, index=True),
|
|
|
sa.Column('group_id', sa.types.BigInteger, index=True),
|
|
|
sa.Column('occurences', sa.Integer, nullable=False, server_default='0', index=True),
|
|
|
sa.Column('owner_user_id', sa.Integer),
|
|
|
sa.Column('type', sa.Integer, index=True, nullable=False),
|
|
|
sa.Column('duration', sa.Float(), server_default='0'),
|
|
|
sa.Column('server_name', sa.Unicode(128),
|
|
|
server_default=''),
|
|
|
sa.Column('view_name', sa.Unicode(128),
|
|
|
server_default=''),
|
|
|
sa.Column('id', sa.BigInteger(), nullable=False, primary_key=True),
|
|
|
)
|
|
|
op.create_index('ix_reports_stats_start_interval_group_id', 'reports_stats',
|
|
|
["start_interval", "group_id"])
|
|
|
|
|
|
op.create_table(
|
|
|
'slow_calls',
|
|
|
sa.Column('id', sa.types.BigInteger, primary_key=True),
|
|
|
sa.Column('report_id', sa.types.BigInteger, sa.ForeignKey('reports.id', onupdate='cascade', ondelete='cascade'),
|
|
|
nullable=False, index=True),
|
|
|
sa.Column('duration', sa.Float(), nullable=False, server_default="0", index=True),
|
|
|
sa.Column('timestamp', sa.DateTime, nullable=False, server_default=sa.func.now(), index=True),
|
|
|
sa.Column('report_group_time', sa.DateTime, index=True, nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('type', sa.Unicode(16), nullable=False, index=True),
|
|
|
sa.Column('statement', sa.UnicodeText, nullable=False, server_default=''),
|
|
|
sa.Column('parameters', sa.dialects.postgresql.JSON, nullable=False),
|
|
|
sa.Column('location', sa.UnicodeText, server_default=''),
|
|
|
sa.Column('subtype', sa.Unicode(16), nullable=False, index=True),
|
|
|
sa.Column('resource_id', sa.Integer, nullable=False, index=True),
|
|
|
sa.Column('statement_hash', sa.Unicode(60), index=True)
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'tags',
|
|
|
sa.Column('id', sa.types.BigInteger, primary_key=True),
|
|
|
sa.Column('resource_id', sa.Integer,
|
|
|
sa.ForeignKey('resources.resource_id', onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
sa.Column('first_timestamp', sa.DateTime, nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('last_timestamp', sa.DateTime, nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('name', sa.Unicode(32), nullable=False),
|
|
|
sa.Column('value', sa.dialects.postgresql.JSON, nullable=False),
|
|
|
sa.Column('times_seen', sa.Integer, nullable=False, server_default='1')
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'auth_tokens',
|
|
|
sa.Column('id', sa.Integer, nullable=False, primary_key=True),
|
|
|
sa.Column('token', sa.Unicode),
|
|
|
sa.Column('creation_date', sa.DateTime, nullable=False, server_default=sa.func.now()),
|
|
|
sa.Column('expires', sa.DateTime),
|
|
|
sa.Column('owner_id', sa.Integer,
|
|
|
sa.ForeignKey('users.id', onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
sa.Column('description', sa.Unicode),
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'channels_actions',
|
|
|
sa.Column('channel_pkey', sa.Integer,
|
|
|
sa.ForeignKey('alert_channels.pkey',
|
|
|
ondelete='CASCADE', onupdate='CASCADE')),
|
|
|
sa.Column('action_pkey', sa.Integer,
|
|
|
sa.ForeignKey('alert_channels_actions.pkey',
|
|
|
ondelete='CASCADE', onupdate='CASCADE'))
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'config',
|
|
|
sa.Column('key', sa.Unicode(128), primary_key=True),
|
|
|
sa.Column('section', sa.Unicode(128), primary_key=True),
|
|
|
sa.Column('value', sa.dialects.postgresql.JSON,
|
|
|
server_default="{}")
|
|
|
)
|
|
|
|
|
|
op.create_table(
|
|
|
'plugin_configs',
|
|
|
sa.Column('id', sa.Integer, primary_key=True),
|
|
|
sa.Column('plugin_name', sa.Unicode(128)),
|
|
|
sa.Column('section', sa.Unicode(128)),
|
|
|
sa.Column('config', sa.dialects.postgresql.JSON,
|
|
|
server_default="{}"),
|
|
|
sa.Column('resource_id', sa.Integer(),
|
|
|
sa.ForeignKey('resources.resource_id', onupdate='cascade',
|
|
|
ondelete='cascade')),
|
|
|
sa.Column('owner_id', sa.Integer(),
|
|
|
sa.ForeignKey('users.id', onupdate='cascade',
|
|
|
ondelete='cascade')))
|
|
|
|
|
|
op.create_table(
|
|
|
'rc_versions',
|
|
|
sa.Column('name', sa.Unicode(40), primary_key=True),
|
|
|
sa.Column('value', sa.Unicode(40)),
|
|
|
)
|
|
|
version_table = sa.table('rc_versions',
|
|
|
sa.Column('name', sa.Unicode(40)),
|
|
|
sa.Column('value', sa.Unicode(40)))
|
|
|
|
|
|
insert = version_table.insert().values(name='es_reports')
|
|
|
op.execute(insert)
|
|
|
insert = version_table.insert().values(name='es_reports_groups')
|
|
|
op.execute(insert)
|
|
|
insert = version_table.insert().values(name='es_reports_stats')
|
|
|
op.execute(insert)
|
|
|
insert = version_table.insert().values(name='es_logs')
|
|
|
op.execute(insert)
|
|
|
insert = version_table.insert().values(name='es_metrics')
|
|
|
op.execute(insert)
|
|
|
insert = version_table.insert().values(name='es_slow_calls')
|
|
|
op.execute(insert)
|
|
|
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE OR REPLACE FUNCTION floor_time_5min(timestamp without time zone)
|
|
|
RETURNS timestamp without time zone AS
|
|
|
$BODY$SELECT date_trunc('hour', $1) + INTERVAL '5 min' * FLOOR(date_part('minute', $1) / 5.0)$BODY$
|
|
|
LANGUAGE sql VOLATILE;
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE OR REPLACE FUNCTION partition_logs() RETURNS trigger
|
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
|
AS $$
|
|
|
DECLARE
|
|
|
main_table varchar := 'logs';
|
|
|
partitioned_table varchar := '';
|
|
|
BEGIN
|
|
|
|
|
|
IF NEW.permanent THEN
|
|
|
partitioned_table := main_table || '_p_' || date_part('year', NEW.timestamp)::TEXT || '_' || DATE_part('month', NEW.timestamp);
|
|
|
ELSE
|
|
|
partitioned_table := main_table || '_p_' || date_part('year', NEW.timestamp)::TEXT || '_' || DATE_part('month', NEW.timestamp) || '_' || DATE_part('day', NEW.timestamp);
|
|
|
END IF;
|
|
|
|
|
|
BEGIN
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
EXCEPTION
|
|
|
WHEN undefined_table THEN
|
|
|
RAISE NOTICE 'A partition has been created %', partitioned_table;
|
|
|
IF NEW.permanent THEN
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK( timestamp >= DATE %s AND timestamp < DATE %s)) INHERITS (%s)',
|
|
|
partitioned_table,
|
|
|
quote_literal(date_trunc('month', NEW.timestamp)::date) ,
|
|
|
quote_literal((date_trunc('month', NEW.timestamp)::date + interval '1 month')::text),
|
|
|
main_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(log_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s (timestamp);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_namespace_resource_id ON %s (namespace, resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s (resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_pkey_namespace ON %s (primary_key, namespace);', partitioned_table, partitioned_table);
|
|
|
ELSE
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK( timestamp >= DATE %s AND timestamp < DATE %s)) INHERITS (%s)',
|
|
|
partitioned_table,
|
|
|
quote_literal(date_trunc('day', NEW.timestamp)::date) ,
|
|
|
quote_literal((date_trunc('day', NEW.timestamp)::date + interval '1 day')::text),
|
|
|
main_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s_ PRIMARY KEY(log_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s (timestamp);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_namespace_resource_id ON %s (namespace, resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s (resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_primary_key_namespace ON %s (primary_key,namespace);', partitioned_table, partitioned_table);
|
|
|
END IF;
|
|
|
|
|
|
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
END;
|
|
|
|
|
|
|
|
|
RETURN NULL;
|
|
|
END
|
|
|
$$;
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE TRIGGER partition_logs BEFORE INSERT ON logs FOR EACH ROW EXECUTE PROCEDURE partition_logs();
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE OR REPLACE FUNCTION partition_metrics() RETURNS trigger
|
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
|
AS $$
|
|
|
DECLARE
|
|
|
main_table varchar := 'metrics';
|
|
|
partitioned_table varchar := '';
|
|
|
BEGIN
|
|
|
|
|
|
partitioned_table := main_table || '_p_' || date_part('year', NEW.timestamp)::TEXT || '_' || DATE_part('month', NEW.timestamp) || '_' || DATE_part('day', NEW.timestamp);
|
|
|
|
|
|
BEGIN
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
EXCEPTION
|
|
|
WHEN undefined_table THEN
|
|
|
RAISE NOTICE 'A partition has been created %', partitioned_table;
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK( timestamp >= DATE %s AND timestamp < DATE %s)) INHERITS (%s)',
|
|
|
partitioned_table,
|
|
|
quote_literal(date_trunc('day', NEW.timestamp)::date) ,
|
|
|
quote_literal((date_trunc('day', NEW.timestamp)::date + interval '1 day')::text),
|
|
|
main_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(pkey);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s (timestamp);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s (resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
END;
|
|
|
|
|
|
RETURN NULL;
|
|
|
END
|
|
|
$$;
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE TRIGGER partition_metrics BEFORE INSERT ON metrics FOR EACH ROW EXECUTE PROCEDURE partition_metrics();
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE FUNCTION partition_reports_stats() RETURNS trigger
|
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
|
AS $$
|
|
|
DECLARE
|
|
|
main_table varchar := 'reports_stats';
|
|
|
partitioned_table varchar := '';
|
|
|
BEGIN
|
|
|
|
|
|
partitioned_table := main_table || '_p_' || date_part('year', NEW.start_interval)::TEXT || '_' || DATE_part('month', NEW.start_interval);
|
|
|
|
|
|
BEGIN
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
EXCEPTION
|
|
|
WHEN undefined_table THEN
|
|
|
RAISE NOTICE 'A partition has been created %', partitioned_table;
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK( start_interval >= DATE %s AND start_interval < DATE %s )) INHERITS (%s)',
|
|
|
partitioned_table,
|
|
|
quote_literal(date_trunc('month', NEW.start_interval)::date) ,
|
|
|
quote_literal((date_trunc('month', NEW.start_interval)::date + interval '1 month')::text),
|
|
|
main_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_start_interval ON %s USING btree (start_interval);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_type ON %s USING btree (type);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s USING btree (resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
END;
|
|
|
RETURN NULL;
|
|
|
END
|
|
|
$$;
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE TRIGGER partition_reports_stats BEFORE INSERT ON reports_stats FOR EACH ROW EXECUTE PROCEDURE partition_reports_stats();
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE OR REPLACE FUNCTION partition_reports_groups() RETURNS trigger
|
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
|
AS $$
|
|
|
DECLARE
|
|
|
main_table varchar := 'reports_groups';
|
|
|
partitioned_table varchar := '';
|
|
|
BEGIN
|
|
|
|
|
|
partitioned_table := main_table || '_p_' || date_part('year', NEW.first_timestamp)::TEXT || '_' || DATE_part('month', NEW.first_timestamp);
|
|
|
|
|
|
BEGIN
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
EXCEPTION
|
|
|
WHEN undefined_table THEN
|
|
|
RAISE NOTICE 'A partition has been created %', partitioned_table;
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK( first_timestamp >= DATE %s AND first_timestamp < DATE %s )) INHERITS (%s)',
|
|
|
partitioned_table,
|
|
|
quote_literal(date_trunc('month', NEW.first_timestamp)::date) ,
|
|
|
quote_literal((date_trunc('month', NEW.first_timestamp)::date + interval '1 month')::text),
|
|
|
main_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
END;
|
|
|
RETURN NULL;
|
|
|
END
|
|
|
$$;
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE TRIGGER partition_reports_groups BEFORE INSERT ON reports_groups FOR EACH ROW EXECUTE PROCEDURE partition_reports_groups();
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE OR REPLACE FUNCTION partition_reports() RETURNS trigger
|
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
|
AS $$
|
|
|
DECLARE
|
|
|
main_table varchar := 'reports';
|
|
|
partitioned_table varchar := '';
|
|
|
partitioned_parent_table varchar := '';
|
|
|
BEGIN
|
|
|
|
|
|
partitioned_table := main_table || '_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);
|
|
|
partitioned_parent_table := 'reports_groups_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);
|
|
|
|
|
|
BEGIN
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
EXCEPTION
|
|
|
WHEN undefined_table THEN
|
|
|
RAISE NOTICE 'A partition has been created %', partitioned_table;
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK( report_group_time >= DATE %s AND report_group_time < DATE %s )) INHERITS (%s)',
|
|
|
partitioned_table,
|
|
|
quote_literal(date_trunc('month', NEW.report_group_time)::date) ,
|
|
|
quote_literal((date_trunc('month', NEW.report_group_time)::date + interval '1 month')::text),
|
|
|
main_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_group_id FOREIGN KEY (group_id) REFERENCES %s (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table, partitioned_parent_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_report_group_time ON %s USING btree (report_group_time);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_group_id ON %s USING btree (group_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s USING btree (resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
END;
|
|
|
RETURN NULL;
|
|
|
END
|
|
|
$$;
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE TRIGGER partition_reports BEFORE INSERT ON reports FOR EACH ROW EXECUTE PROCEDURE partition_reports();
|
|
|
''')
|
|
|
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE OR REPLACE FUNCTION partition_slow_calls() RETURNS trigger
|
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
|
AS $$
|
|
|
DECLARE
|
|
|
main_table varchar := 'slow_calls';
|
|
|
partitioned_table varchar := '';
|
|
|
partitioned_parent_table varchar := '';
|
|
|
BEGIN
|
|
|
|
|
|
partitioned_table := main_table || '_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);
|
|
|
partitioned_parent_table := 'reports_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);
|
|
|
|
|
|
BEGIN
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
EXCEPTION
|
|
|
WHEN undefined_table THEN
|
|
|
RAISE NOTICE 'A partition has been created %', partitioned_table;
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK( report_group_time >= DATE %s AND report_group_time < DATE %s )) INHERITS (%s)',
|
|
|
partitioned_table,
|
|
|
quote_literal(date_trunc('month', NEW.report_group_time)::date) ,
|
|
|
quote_literal((date_trunc('month', NEW.report_group_time)::date + interval '1 month')::text),
|
|
|
main_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_report_id FOREIGN KEY (report_id) REFERENCES %s (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table, partitioned_parent_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s USING btree (resource_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_report_id ON %s USING btree (report_id);', partitioned_table, partitioned_table);
|
|
|
EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s USING btree (timestamp);', partitioned_table, partitioned_table);
|
|
|
EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
|
|
|
END;
|
|
|
RETURN NULL;
|
|
|
END
|
|
|
$$;
|
|
|
''')
|
|
|
|
|
|
op.execute('''
|
|
|
CREATE TRIGGER partition_slow_calls BEFORE INSERT ON slow_calls FOR EACH ROW EXECUTE PROCEDURE partition_slow_calls();
|
|
|
''')
|
|
|
|
|
|
def downgrade():
|
|
|
pass
|
|
|
|
