55b6e612672f_initial_tables.py
813 lines
| 34.1 KiB
| text/x-python
|
PythonLexer
r0 | # -*- coding: utf-8 -*- | |||
r112 | # Copyright 2010 - 2017 RhodeCode GmbH and the AppEnlight project authors | |||
r0 | # | |||
r112 | # 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 | ||||
r0 | # | |||
r112 | # http://www.apache.org/licenses/LICENSE-2.0 | |||
r0 | # | |||
r112 | # 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. | ||||
r0 | ||||
"""initial tables | ||||
Revision ID: 55b6e612672f | ||||
Revises: None | ||||
Create Date: 2014-10-13 23:47:38.295159 | ||||
""" | ||||
# revision identifiers, used by Alembic. | ||||
r153 | revision = "55b6e612672f" | |||
r0 | down_revision = None | |||
from alembic import op | ||||
import sqlalchemy as sa | ||||
def upgrade(): | ||||
r153 | 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=""), | ||||
) | ||||
r0 | ||||
op.create_table( | ||||
r153 | "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)), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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"], | ||||
) | ||||
r0 | ||||
op.create_table( | ||||
r153 | "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=""), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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"]) | ||||
r0 | ||||
op.create_table( | ||||
r153 | "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="{}"), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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"), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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')")]) | ||||
r0 | ||||
op.create_table( | ||||
r153 | "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), | ||||
) | ||||
r0 | ||||
op.create_table( | ||||
r153 | "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), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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"], | ||||
) | ||||
r0 | ||||
op.create_table( | ||||
r153 | "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), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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"), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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" | ||||
), | ||||
), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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="{}"), | ||||
r0 | ) | |||
op.create_table( | ||||
r153 | "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"), | ||||
), | ||||
) | ||||
r0 | ||||
op.create_table( | ||||
r153 | "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)), | ||||
r0 | ) | |||
r153 | insert = version_table.insert().values(name="es_reports") | |||
r0 | op.execute(insert) | |||
r153 | insert = version_table.insert().values(name="es_reports_groups") | |||
r0 | op.execute(insert) | |||
r153 | insert = version_table.insert().values(name="es_reports_stats") | |||
r0 | op.execute(insert) | |||
r153 | insert = version_table.insert().values(name="es_logs") | |||
r0 | op.execute(insert) | |||
r153 | insert = version_table.insert().values(name="es_metrics") | |||
r0 | op.execute(insert) | |||
r153 | insert = version_table.insert().values(name="es_slow_calls") | |||
r0 | op.execute(insert) | |||
r153 | op.execute( | |||
""" | ||||
r0 | 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; | ||||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | 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 | ||||
$$; | ||||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | CREATE TRIGGER partition_logs BEFORE INSERT ON logs FOR EACH ROW EXECUTE PROCEDURE partition_logs(); | |||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | 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 | ||||
$$; | ||||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | CREATE TRIGGER partition_metrics BEFORE INSERT ON metrics FOR EACH ROW EXECUTE PROCEDURE partition_metrics(); | |||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | 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 | ||||
$$; | ||||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | CREATE TRIGGER partition_reports_stats BEFORE INSERT ON reports_stats FOR EACH ROW EXECUTE PROCEDURE partition_reports_stats(); | |||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | 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 | ||||
$$; | ||||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | CREATE TRIGGER partition_reports_groups BEFORE INSERT ON reports_groups FOR EACH ROW EXECUTE PROCEDURE partition_reports_groups(); | |||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | 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 | ||||
$$; | ||||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | CREATE TRIGGER partition_reports BEFORE INSERT ON reports FOR EACH ROW EXECUTE PROCEDURE partition_reports(); | |||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | 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 | ||||
$$; | ||||
r153 | """ | |||
) | ||||
r0 | ||||
r153 | op.execute( | |||
""" | ||||
r0 | CREATE TRIGGER partition_slow_calls BEFORE INSERT ON slow_calls FOR EACH ROW EXECUTE PROCEDURE partition_slow_calls(); | |||
r153 | """ | |||
) | ||||
r0 | ||||
def downgrade(): | ||||
pass | ||||