|
|
# -*- 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
|
|
|
|
