From a1882b67c41fe9901a0cd8059b5cc78a5beadec0 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 26 Nov 2024 09:28:28 +0100 Subject: Adding upstream version 2.0.0~beta.0+dfsg. Signed-off-by: Daniel Baumann --- db/knex_init_db.js | 565 +++++++++++++++++++++ .../2023-08-16-0000-create-uptime.js | 41 ++ db/knex_migrations/2023-08-18-0301-heartbeat.js | 16 + .../2023-09-29-0000-heartbeat-retires.js | 15 + db/knex_migrations/2023-10-08-0000-mqtt-query.js | 16 + .../2023-10-11-1915-push-token-to-32.js | 14 + .../2023-10-16-0000-create-remote-browsers.js | 21 + .../2023-12-20-0000-alter-status-page.js | 12 + .../2023-12-21-0000-stat-ping-min-max.js | 24 + .../2023-12-22-0000-hourly-uptime.js | 26 + db/knex_migrations/2024-01-22-0000-stats-extras.js | 26 + db/knex_migrations/2024-04-26-0000-snmp-monitor.js | 16 + .../2024-08-24-000-add-cache-bust.js | 13 + db/knex_migrations/2024-08-24-0000-conditions.js | 12 + .../2024-10-1315-rabbitmq-monitor.js | 17 + .../2024-11-27-1927-fix-info-json-data-type.js | 13 + db/knex_migrations/README.md | 56 ++ db/kuma.db | Bin 0 -> 61440 bytes db/old_migrations/README.md | 3 + .../patch-2fa-invalidate-used-token.sql | 7 + db/old_migrations/patch-2fa.sql | 10 + .../patch-add-certificate-expiry-status-page.sql | 7 + .../patch-add-clickable-status-page-link.sql | 7 + .../patch-add-description-monitor.sql | 7 + db/old_migrations/patch-add-docker-columns.sql | 18 + db/old_migrations/patch-add-gamedig-given-port.sql | 7 + db/old_migrations/patch-add-gamedig-monitor.sql | 7 + .../patch-add-google-analytics-status-page-tag.sql | 7 + db/old_migrations/patch-add-invert-keyword.sql | 7 + db/old_migrations/patch-add-other-auth.sql | 18 + db/old_migrations/patch-add-parent-monitor.sql | 7 + db/old_migrations/patch-add-radius-monitor.sql | 19 + .../patch-add-retry-interval-monitor.sql | 7 + db/old_migrations/patch-add-sqlserver-monitor.sql | 10 + db/old_migrations/patch-add-timeout-monitor.sql | 7 + db/old_migrations/patch-added-json-query.sql | 10 + db/old_migrations/patch-added-kafka-producer.sql | 22 + db/old_migrations/patch-added-mqtt-monitor.sql | 16 + db/old_migrations/patch-api-key-table.sql | 15 + .../patch-fix-kafka-producer-booleans.sql | 34 ++ db/old_migrations/patch-group-table.sql | 30 ++ db/old_migrations/patch-grpc-monitor.sql | 25 + db/old_migrations/patch-http-body-encoding.sql | 12 + .../patch-http-monitor-method-body-and-headers.sql | 13 + db/old_migrations/patch-improve-performance.sql | 10 + db/old_migrations/patch-incident-table.sql | 18 + db/old_migrations/patch-maintenance-cron.sql | 11 + db/old_migrations/patch-maintenance-table2.sql | 83 +++ .../patch-monitor-add-resend-interval.sql | 10 + db/old_migrations/patch-monitor-basic-auth.sql | 10 + .../patch-monitor-expiry-notification.sql | 7 + db/old_migrations/patch-monitor-oauth-cc.sql | 19 + db/old_migrations/patch-monitor-push_token.sql | 7 + .../patch-monitor-tls-info-add-fk.sql | 18 + db/old_migrations/patch-monitor-tls.sql | 13 + db/old_migrations/patch-notification-config.sql | 10 + .../patch-notification_sent_history.sql | 18 + db/old_migrations/patch-ping-packet-size.sql | 7 + db/old_migrations/patch-proxy.sql | 23 + db/old_migrations/patch-setting-value-type.sql | 21 + db/old_migrations/patch-status-page-footer-css.sql | 11 + db/old_migrations/patch-status-page.sql | 31 ++ db/old_migrations/patch-timeout.sql | 7 + db/old_migrations/patch1.sql | 37 ++ db/old_migrations/patch10.sql | 19 + db/old_migrations/patch2.sql | 10 + db/old_migrations/patch3.sql | 37 ++ db/old_migrations/patch4.sql | 40 ++ db/old_migrations/patch5.sql | 70 +++ db/old_migrations/patch6.sql | 74 +++ db/old_migrations/patch7.sql | 10 + db/old_migrations/patch8.sql | 7 + db/old_migrations/patch9.sql | 7 + db/patch-monitor-tls-info-add-fk.sql | 18 + 74 files changed, 1898 insertions(+) create mode 100644 db/knex_init_db.js create mode 100644 db/knex_migrations/2023-08-16-0000-create-uptime.js create mode 100644 db/knex_migrations/2023-08-18-0301-heartbeat.js create mode 100644 db/knex_migrations/2023-09-29-0000-heartbeat-retires.js create mode 100644 db/knex_migrations/2023-10-08-0000-mqtt-query.js create mode 100644 db/knex_migrations/2023-10-11-1915-push-token-to-32.js create mode 100644 db/knex_migrations/2023-10-16-0000-create-remote-browsers.js create mode 100644 db/knex_migrations/2023-12-20-0000-alter-status-page.js create mode 100644 db/knex_migrations/2023-12-21-0000-stat-ping-min-max.js create mode 100644 db/knex_migrations/2023-12-22-0000-hourly-uptime.js create mode 100644 db/knex_migrations/2024-01-22-0000-stats-extras.js create mode 100644 db/knex_migrations/2024-04-26-0000-snmp-monitor.js create mode 100644 db/knex_migrations/2024-08-24-000-add-cache-bust.js create mode 100644 db/knex_migrations/2024-08-24-0000-conditions.js create mode 100644 db/knex_migrations/2024-10-1315-rabbitmq-monitor.js create mode 100644 db/knex_migrations/2024-11-27-1927-fix-info-json-data-type.js create mode 100644 db/knex_migrations/README.md create mode 100644 db/kuma.db create mode 100644 db/old_migrations/README.md create mode 100644 db/old_migrations/patch-2fa-invalidate-used-token.sql create mode 100644 db/old_migrations/patch-2fa.sql create mode 100644 db/old_migrations/patch-add-certificate-expiry-status-page.sql create mode 100644 db/old_migrations/patch-add-clickable-status-page-link.sql create mode 100644 db/old_migrations/patch-add-description-monitor.sql create mode 100644 db/old_migrations/patch-add-docker-columns.sql create mode 100644 db/old_migrations/patch-add-gamedig-given-port.sql create mode 100644 db/old_migrations/patch-add-gamedig-monitor.sql create mode 100644 db/old_migrations/patch-add-google-analytics-status-page-tag.sql create mode 100644 db/old_migrations/patch-add-invert-keyword.sql create mode 100644 db/old_migrations/patch-add-other-auth.sql create mode 100644 db/old_migrations/patch-add-parent-monitor.sql create mode 100644 db/old_migrations/patch-add-radius-monitor.sql create mode 100644 db/old_migrations/patch-add-retry-interval-monitor.sql create mode 100644 db/old_migrations/patch-add-sqlserver-monitor.sql create mode 100644 db/old_migrations/patch-add-timeout-monitor.sql create mode 100644 db/old_migrations/patch-added-json-query.sql create mode 100644 db/old_migrations/patch-added-kafka-producer.sql create mode 100644 db/old_migrations/patch-added-mqtt-monitor.sql create mode 100644 db/old_migrations/patch-api-key-table.sql create mode 100644 db/old_migrations/patch-fix-kafka-producer-booleans.sql create mode 100644 db/old_migrations/patch-group-table.sql create mode 100644 db/old_migrations/patch-grpc-monitor.sql create mode 100644 db/old_migrations/patch-http-body-encoding.sql create mode 100644 db/old_migrations/patch-http-monitor-method-body-and-headers.sql create mode 100644 db/old_migrations/patch-improve-performance.sql create mode 100644 db/old_migrations/patch-incident-table.sql create mode 100644 db/old_migrations/patch-maintenance-cron.sql create mode 100644 db/old_migrations/patch-maintenance-table2.sql create mode 100644 db/old_migrations/patch-monitor-add-resend-interval.sql create mode 100644 db/old_migrations/patch-monitor-basic-auth.sql create mode 100644 db/old_migrations/patch-monitor-expiry-notification.sql create mode 100644 db/old_migrations/patch-monitor-oauth-cc.sql create mode 100644 db/old_migrations/patch-monitor-push_token.sql create mode 100644 db/old_migrations/patch-monitor-tls-info-add-fk.sql create mode 100644 db/old_migrations/patch-monitor-tls.sql create mode 100644 db/old_migrations/patch-notification-config.sql create mode 100644 db/old_migrations/patch-notification_sent_history.sql create mode 100644 db/old_migrations/patch-ping-packet-size.sql create mode 100644 db/old_migrations/patch-proxy.sql create mode 100644 db/old_migrations/patch-setting-value-type.sql create mode 100644 db/old_migrations/patch-status-page-footer-css.sql create mode 100644 db/old_migrations/patch-status-page.sql create mode 100644 db/old_migrations/patch-timeout.sql create mode 100644 db/old_migrations/patch1.sql create mode 100644 db/old_migrations/patch10.sql create mode 100644 db/old_migrations/patch2.sql create mode 100644 db/old_migrations/patch3.sql create mode 100644 db/old_migrations/patch4.sql create mode 100644 db/old_migrations/patch5.sql create mode 100644 db/old_migrations/patch6.sql create mode 100644 db/old_migrations/patch7.sql create mode 100644 db/old_migrations/patch8.sql create mode 100644 db/old_migrations/patch9.sql create mode 100644 db/patch-monitor-tls-info-add-fk.sql (limited to 'db') diff --git a/db/knex_init_db.js b/db/knex_init_db.js new file mode 100644 index 0000000..46bff4b --- /dev/null +++ b/db/knex_init_db.js @@ -0,0 +1,565 @@ +const { R } = require("redbean-node"); +const { log } = require("../src/util"); + +/** + * ⚠️⚠️⚠️⚠️⚠️⚠️ DO NOT ADD ANYTHING HERE! + * IF YOU NEED TO ADD FIELDS, ADD IT TO ./db/knex_migrations + * See ./db/knex_migrations/README.md for more information + * @returns {Promise} + */ +async function createTables() { + log.info("mariadb", "Creating basic tables for MariaDB"); + const knex = R.knex; + + // TODO: Should check later if it is really the final patch sql file. + + // docker_host + await knex.schema.createTable("docker_host", (table) => { + table.increments("id"); + table.integer("user_id").unsigned().notNullable(); + table.string("docker_daemon", 255); + table.string("docker_type", 255); + table.string("name", 255); + }); + + // group + await knex.schema.createTable("group", (table) => { + table.increments("id"); + table.string("name", 255).notNullable(); + table.datetime("created_date").notNullable().defaultTo(knex.fn.now()); + table.boolean("public").notNullable().defaultTo(false); + table.boolean("active").notNullable().defaultTo(true); + table.integer("weight").notNullable().defaultTo(1000); + table.integer("status_page_id").unsigned(); + }); + + // proxy + await knex.schema.createTable("proxy", (table) => { + table.increments("id"); + table.integer("user_id").unsigned().notNullable(); + table.string("protocol", 10).notNullable(); + table.string("host", 255).notNullable(); + table.smallint("port").notNullable(); // TODO: Maybe a issue with MariaDB, need migration to int + table.boolean("auth").notNullable(); + table.string("username", 255).nullable(); + table.string("password", 255).nullable(); + table.boolean("active").notNullable().defaultTo(true); + table.boolean("default").notNullable().defaultTo(false); + table.datetime("created_date").notNullable().defaultTo(knex.fn.now()); + + table.index("user_id", "proxy_user_id"); + }); + + // user + await knex.schema.createTable("user", (table) => { + table.increments("id"); + table.string("username", 255).notNullable().unique().collate("utf8_general_ci"); + table.string("password", 255); + table.boolean("active").notNullable().defaultTo(true); + table.string("timezone", 150); + table.string("twofa_secret", 64); + table.boolean("twofa_status").notNullable().defaultTo(false); + table.string("twofa_last_token", 6); + }); + + // monitor + await knex.schema.createTable("monitor", (table) => { + table.increments("id"); + table.string("name", 150); + table.boolean("active").notNullable().defaultTo(true); + table.integer("user_id").unsigned() + .references("id").inTable("user") + .onDelete("SET NULL") + .onUpdate("CASCADE"); + table.integer("interval").notNullable().defaultTo(20); + table.text("url"); + table.string("type", 20); + table.integer("weight").defaultTo(2000); + table.string("hostname", 255); + table.integer("port"); + table.datetime("created_date").notNullable().defaultTo(knex.fn.now()); + table.string("keyword", 255); + table.integer("maxretries").notNullable().defaultTo(0); + table.boolean("ignore_tls").notNullable().defaultTo(false); + table.boolean("upside_down").notNullable().defaultTo(false); + table.integer("maxredirects").notNullable().defaultTo(10); + table.text("accepted_statuscodes_json").notNullable().defaultTo("[\"200-299\"]"); + table.string("dns_resolve_type", 5); + table.string("dns_resolve_server", 255); + table.string("dns_last_result", 255); + table.integer("retry_interval").notNullable().defaultTo(0); + table.string("push_token", 20).defaultTo(null); + table.text("method").notNullable().defaultTo("GET"); + table.text("body").defaultTo(null); + table.text("headers").defaultTo(null); + table.text("basic_auth_user").defaultTo(null); + table.text("basic_auth_pass").defaultTo(null); + table.integer("docker_host").unsigned() + .references("id").inTable("docker_host"); + table.string("docker_container", 255); + table.integer("proxy_id").unsigned() + .references("id").inTable("proxy"); + table.boolean("expiry_notification").defaultTo(true); + table.text("mqtt_topic"); + table.string("mqtt_success_message", 255); + table.string("mqtt_username", 255); + table.string("mqtt_password", 255); + table.string("database_connection_string", 2000); + table.text("database_query"); + table.string("auth_method", 250); + table.text("auth_domain"); + table.text("auth_workstation"); + table.string("grpc_url", 255).defaultTo(null); + table.text("grpc_protobuf").defaultTo(null); + table.text("grpc_body").defaultTo(null); + table.text("grpc_metadata").defaultTo(null); + table.text("grpc_method").defaultTo(null); + table.text("grpc_service_name").defaultTo(null); + table.boolean("grpc_enable_tls").notNullable().defaultTo(false); + table.string("radius_username", 255); + table.string("radius_password", 255); + table.string("radius_calling_station_id", 50); + table.string("radius_called_station_id", 50); + table.string("radius_secret", 255); + table.integer("resend_interval").notNullable().defaultTo(0); + table.integer("packet_size").notNullable().defaultTo(56); + table.string("game", 255); + }); + + // heartbeat + await knex.schema.createTable("heartbeat", (table) => { + table.increments("id"); + table.boolean("important").notNullable().defaultTo(false); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.smallint("status").notNullable(); + + table.text("msg"); + table.datetime("time").notNullable(); + table.integer("ping"); + table.integer("duration").notNullable().defaultTo(0); + table.integer("down_count").notNullable().defaultTo(0); + + table.index("important"); + table.index([ "monitor_id", "time" ], "monitor_time_index"); + table.index("monitor_id"); + table.index([ "monitor_id", "important", "time" ], "monitor_important_time_index"); + }); + + // incident + await knex.schema.createTable("incident", (table) => { + table.increments("id"); + table.string("title", 255).notNullable(); + table.text("content", 255).notNullable(); + table.string("style", 30).notNullable().defaultTo("warning"); + table.datetime("created_date").notNullable().defaultTo(knex.fn.now()); + table.datetime("last_updated_date"); + table.boolean("pin").notNullable().defaultTo(true); + table.boolean("active").notNullable().defaultTo(true); + table.integer("status_page_id").unsigned(); + }); + + // maintenance + await knex.schema.createTable("maintenance", (table) => { + table.increments("id"); + table.string("title", 150).notNullable(); + table.text("description").notNullable(); + table.integer("user_id").unsigned() + .references("id").inTable("user") + .onDelete("SET NULL") + .onUpdate("CASCADE"); + table.boolean("active").notNullable().defaultTo(true); + table.string("strategy", 50).notNullable().defaultTo("single"); + table.datetime("start_date"); + table.datetime("end_date"); + table.time("start_time"); + table.time("end_time"); + table.string("weekdays", 250).defaultTo("[]"); + table.text("days_of_month").defaultTo("[]"); + table.integer("interval_day"); + + table.index("active"); + table.index([ "strategy", "active" ], "manual_active"); + table.index("user_id", "maintenance_user_id"); + }); + + // status_page + await knex.schema.createTable("status_page", (table) => { + table.increments("id"); + table.string("slug", 255).notNullable().unique().collate("utf8_general_ci"); + table.string("title", 255).notNullable(); + table.text("description"); + table.string("icon", 255).notNullable(); + table.string("theme", 30).notNullable(); + table.boolean("published").notNullable().defaultTo(true); + table.boolean("search_engine_index").notNullable().defaultTo(true); + table.boolean("show_tags").notNullable().defaultTo(false); + table.string("password"); + table.datetime("created_date").notNullable().defaultTo(knex.fn.now()); + table.datetime("modified_date").notNullable().defaultTo(knex.fn.now()); + table.text("footer_text"); + table.text("custom_css"); + table.boolean("show_powered_by").notNullable().defaultTo(true); + table.string("google_analytics_tag_id"); + }); + + // maintenance_status_page + await knex.schema.createTable("maintenance_status_page", (table) => { + table.increments("id"); + + table.integer("status_page_id").unsigned().notNullable() + .references("id").inTable("status_page") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + + table.integer("maintenance_id").unsigned().notNullable() + .references("id").inTable("maintenance") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + }); + + // maintenance_timeslot + await knex.schema.createTable("maintenance_timeslot", (table) => { + table.increments("id"); + table.integer("maintenance_id").unsigned().notNullable() + .references("id").inTable("maintenance") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.datetime("start_date").notNullable(); + table.datetime("end_date"); + table.boolean("generated_next").defaultTo(false); + + table.index("maintenance_id"); + table.index([ "maintenance_id", "start_date", "end_date" ], "active_timeslot_index"); + table.index("generated_next", "generated_next_index"); + }); + + // monitor_group + await knex.schema.createTable("monitor_group", (table) => { + table.increments("id"); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("group_id").unsigned().notNullable() + .references("id").inTable("group") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("weight").notNullable().defaultTo(1000); + table.boolean("send_url").notNullable().defaultTo(false); + + table.index([ "monitor_id", "group_id" ], "fk"); + }); + // monitor_maintenance + await knex.schema.createTable("monitor_maintenance", (table) => { + table.increments("id"); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("maintenance_id").unsigned().notNullable() + .references("id").inTable("maintenance") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + + table.index("maintenance_id", "maintenance_id_index2"); + table.index("monitor_id", "monitor_id_index"); + }); + + // notification + await knex.schema.createTable("notification", (table) => { + table.increments("id"); + table.string("name", 255); + table.boolean("active").notNullable().defaultTo(true); + table.integer("user_id").unsigned(); + table.boolean("is_default").notNullable().defaultTo(false); + table.text("config", "longtext"); + }); + + // monitor_notification + await knex.schema.createTable("monitor_notification", (table) => { + table.increments("id").unsigned(); // TODO: no auto increment???? + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("notification_id").unsigned().notNullable() + .references("id").inTable("notification") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + + table.index([ "monitor_id", "notification_id" ], "monitor_notification_index"); + }); + + // tag + await knex.schema.createTable("tag", (table) => { + table.increments("id"); + table.string("name", 255).notNullable(); + table.string("color", 255).notNullable(); + table.datetime("created_date").notNullable().defaultTo(knex.fn.now()); + }); + + // monitor_tag + await knex.schema.createTable("monitor_tag", (table) => { + table.increments("id"); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("tag_id").unsigned().notNullable() + .references("id").inTable("tag") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.text("value"); + }); + + // monitor_tls_info + await knex.schema.createTable("monitor_tls_info", (table) => { + table.increments("id"); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.text("info_json"); + }); + + // notification_sent_history + await knex.schema.createTable("notification_sent_history", (table) => { + table.increments("id"); + table.string("type", 50).notNullable(); + table.integer("monitor_id").unsigned().notNullable(); + table.integer("days").notNullable(); + table.unique([ "type", "monitor_id", "days" ]); + table.index([ "type", "monitor_id", "days" ], "good_index"); + }); + + // setting + await knex.schema.createTable("setting", (table) => { + table.increments("id"); + table.string("key", 200).notNullable().unique().collate("utf8_general_ci"); + table.text("value"); + table.string("type", 20); + }); + + // status_page_cname + await knex.schema.createTable("status_page_cname", (table) => { + table.increments("id"); + table.integer("status_page_id").unsigned() + .references("id").inTable("status_page") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.string("domain").notNullable().unique().collate("utf8_general_ci"); + }); + + /********************* + * Converted Patch here + *********************/ + + // 2023-06-30-1348-http-body-encoding.js + // ALTER TABLE monitor ADD http_body_encoding VARCHAR(25); + // UPDATE monitor SET http_body_encoding = 'json' WHERE (type = 'http' or type = 'keyword') AND http_body_encoding IS NULL; + await knex.schema.table("monitor", function (table) { + table.string("http_body_encoding", 25); + }); + + await knex("monitor") + .where(function () { + this.where("type", "http").orWhere("type", "keyword"); + }) + .whereNull("http_body_encoding") + .update({ + http_body_encoding: "json", + }); + + // 2023-06-30-1354-add-description-monitor.js + // ALTER TABLE monitor ADD description TEXT default null; + await knex.schema.table("monitor", function (table) { + table.text("description").defaultTo(null); + }); + + // 2023-06-30-1357-api-key-table.js + /* + CREATE TABLE [api_key] ( + [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + [key] VARCHAR(255) NOT NULL, + [name] VARCHAR(255) NOT NULL, + [user_id] INTEGER NOT NULL, + [created_date] DATETIME DEFAULT (DATETIME('now')) NOT NULL, + [active] BOOLEAN DEFAULT 1 NOT NULL, + [expires] DATETIME DEFAULT NULL, + CONSTRAINT FK_user FOREIGN KEY ([user_id]) REFERENCES [user]([id]) ON DELETE CASCADE ON UPDATE CASCADE + ); + */ + await knex.schema.createTable("api_key", function (table) { + table.increments("id").primary(); + table.string("key", 255).notNullable(); + table.string("name", 255).notNullable(); + table.integer("user_id").unsigned().notNullable() + .references("id").inTable("user") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.dateTime("created_date").defaultTo(knex.fn.now()).notNullable(); + table.boolean("active").defaultTo(1).notNullable(); + table.dateTime("expires").defaultTo(null); + }); + + // 2023-06-30-1400-monitor-tls.js + /* + ALTER TABLE monitor + ADD tls_ca TEXT default null; + + ALTER TABLE monitor + ADD tls_cert TEXT default null; + + ALTER TABLE monitor + ADD tls_key TEXT default null; + */ + await knex.schema.table("monitor", function (table) { + table.text("tls_ca").defaultTo(null); + table.text("tls_cert").defaultTo(null); + table.text("tls_key").defaultTo(null); + }); + + // 2023-06-30-1401-maintenance-cron.js + /* + -- 999 characters. https://stackoverflow.com/questions/46134830/maximum-length-for-cron-job + DROP TABLE maintenance_timeslot; + ALTER TABLE maintenance ADD cron TEXT; + ALTER TABLE maintenance ADD timezone VARCHAR(255); + ALTER TABLE maintenance ADD duration INTEGER; + */ + await knex.schema + .dropTableIfExists("maintenance_timeslot") + .table("maintenance", function (table) { + table.text("cron"); + table.string("timezone", 255); + table.integer("duration"); + }); + + // 2023-06-30-1413-add-parent-monitor.js. + /* + ALTER TABLE monitor + ADD parent INTEGER REFERENCES [monitor] ([id]) ON DELETE SET NULL ON UPDATE CASCADE; + */ + await knex.schema.table("monitor", function (table) { + table.integer("parent").unsigned() + .references("id").inTable("monitor") + .onDelete("SET NULL") + .onUpdate("CASCADE"); + }); + + /* + patch-add-invert-keyword.sql + ALTER TABLE monitor + ADD invert_keyword BOOLEAN default 0 not null; + */ + await knex.schema.table("monitor", function (table) { + table.boolean("invert_keyword").defaultTo(0).notNullable(); + }); + + /* + patch-added-json-query.sql + ALTER TABLE monitor + ADD json_path TEXT; + + ALTER TABLE monitor + ADD expected_value VARCHAR(255); + */ + await knex.schema.table("monitor", function (table) { + table.text("json_path"); + table.string("expected_value", 255); + }); + + /* + patch-added-kafka-producer.sql + + ALTER TABLE monitor + ADD kafka_producer_topic VARCHAR(255); + +ALTER TABLE monitor + ADD kafka_producer_brokers TEXT; + +ALTER TABLE monitor + ADD kafka_producer_ssl INTEGER; + +ALTER TABLE monitor + ADD kafka_producer_allow_auto_topic_creation VARCHAR(255); + +ALTER TABLE monitor + ADD kafka_producer_sasl_options TEXT; + +ALTER TABLE monitor + ADD kafka_producer_message TEXT; + */ + await knex.schema.table("monitor", function (table) { + table.string("kafka_producer_topic", 255); + table.text("kafka_producer_brokers"); + + // patch-fix-kafka-producer-booleans.sql + table.boolean("kafka_producer_ssl").defaultTo(0).notNullable(); + table.boolean("kafka_producer_allow_auto_topic_creation").defaultTo(0).notNullable(); + + table.text("kafka_producer_sasl_options"); + table.text("kafka_producer_message"); + }); + + /* + patch-add-certificate-expiry-status-page.sql + ALTER TABLE status_page + ADD show_certificate_expiry BOOLEAN default 0 NOT NULL; + */ + await knex.schema.table("status_page", function (table) { + table.boolean("show_certificate_expiry").defaultTo(0).notNullable(); + }); + + /* + patch-monitor-oauth-cc.sql + ALTER TABLE monitor + ADD oauth_client_id TEXT default null; + +ALTER TABLE monitor + ADD oauth_client_secret TEXT default null; + +ALTER TABLE monitor + ADD oauth_token_url TEXT default null; + +ALTER TABLE monitor + ADD oauth_scopes TEXT default null; + +ALTER TABLE monitor + ADD oauth_auth_method TEXT default null; + */ + await knex.schema.table("monitor", function (table) { + table.text("oauth_client_id").defaultTo(null); + table.text("oauth_client_secret").defaultTo(null); + table.text("oauth_token_url").defaultTo(null); + table.text("oauth_scopes").defaultTo(null); + table.text("oauth_auth_method").defaultTo(null); + }); + + /* + patch-add-timeout-monitor.sql + ALTER TABLE monitor + ADD timeout DOUBLE default 0 not null; + */ + await knex.schema.table("monitor", function (table) { + table.double("timeout").defaultTo(0).notNullable(); + }); + + /* + patch-add-gamedig-given-port.sql + ALTER TABLE monitor + ADD gamedig_given_port_only BOOLEAN default 1 not null; + */ + await knex.schema.table("monitor", function (table) { + table.boolean("gamedig_given_port_only").defaultTo(1).notNullable(); + }); + + log.info("mariadb", "Created basic tables for MariaDB"); +} + +module.exports = { + createTables, +}; diff --git a/db/knex_migrations/2023-08-16-0000-create-uptime.js b/db/knex_migrations/2023-08-16-0000-create-uptime.js new file mode 100644 index 0000000..ab89931 --- /dev/null +++ b/db/knex_migrations/2023-08-16-0000-create-uptime.js @@ -0,0 +1,41 @@ +exports.up = function (knex) { + return knex.schema + .createTable("stat_minutely", function (table) { + table.increments("id"); + table.comment("This table contains the minutely aggregate statistics for each monitor"); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("timestamp") + .notNullable() + .comment("Unix timestamp rounded down to the nearest minute"); + table.float("ping").notNullable().comment("Average ping in milliseconds"); + table.smallint("up").notNullable(); + table.smallint("down").notNullable(); + + table.unique([ "monitor_id", "timestamp" ]); + }) + .createTable("stat_daily", function (table) { + table.increments("id"); + table.comment("This table contains the daily aggregate statistics for each monitor"); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("timestamp") + .notNullable() + .comment("Unix timestamp rounded down to the nearest day"); + table.float("ping").notNullable().comment("Average ping in milliseconds"); + table.smallint("up").notNullable(); + table.smallint("down").notNullable(); + + table.unique([ "monitor_id", "timestamp" ]); + }); +}; + +exports.down = function (knex) { + return knex.schema + .dropTable("stat_minutely") + .dropTable("stat_daily"); +}; diff --git a/db/knex_migrations/2023-08-18-0301-heartbeat.js b/db/knex_migrations/2023-08-18-0301-heartbeat.js new file mode 100644 index 0000000..fe4152b --- /dev/null +++ b/db/knex_migrations/2023-08-18-0301-heartbeat.js @@ -0,0 +1,16 @@ +exports.up = function (knex) { + // Add new column heartbeat.end_time + return knex.schema + .alterTable("heartbeat", function (table) { + table.datetime("end_time").nullable().defaultTo(null); + }); + +}; + +exports.down = function (knex) { + // Rename heartbeat.start_time to heartbeat.time + return knex.schema + .alterTable("heartbeat", function (table) { + table.dropColumn("end_time"); + }); +}; diff --git a/db/knex_migrations/2023-09-29-0000-heartbeat-retires.js b/db/knex_migrations/2023-09-29-0000-heartbeat-retires.js new file mode 100644 index 0000000..a6b9c7b --- /dev/null +++ b/db/knex_migrations/2023-09-29-0000-heartbeat-retires.js @@ -0,0 +1,15 @@ +exports.up = function (knex) { + // Add new column heartbeat.retries + return knex.schema + .alterTable("heartbeat", function (table) { + table.integer("retries").notNullable().defaultTo(0); + }); + +}; + +exports.down = function (knex) { + return knex.schema + .alterTable("heartbeat", function (table) { + table.dropColumn("retries"); + }); +}; diff --git a/db/knex_migrations/2023-10-08-0000-mqtt-query.js b/db/knex_migrations/2023-10-08-0000-mqtt-query.js new file mode 100644 index 0000000..f37bab8 --- /dev/null +++ b/db/knex_migrations/2023-10-08-0000-mqtt-query.js @@ -0,0 +1,16 @@ +exports.up = function (knex) { + // Add new column monitor.mqtt_check_type + return knex.schema + .alterTable("monitor", function (table) { + table.string("mqtt_check_type", 255).notNullable().defaultTo("keyword"); + }); + +}; + +exports.down = function (knex) { + // Drop column monitor.mqtt_check_type + return knex.schema + .alterTable("monitor", function (table) { + table.dropColumn("mqtt_check_type"); + }); +}; diff --git a/db/knex_migrations/2023-10-11-1915-push-token-to-32.js b/db/knex_migrations/2023-10-11-1915-push-token-to-32.js new file mode 100644 index 0000000..47e5ac0 --- /dev/null +++ b/db/knex_migrations/2023-10-11-1915-push-token-to-32.js @@ -0,0 +1,14 @@ +exports.up = function (knex) { + // update monitor.push_token to 32 length + return knex.schema + .alterTable("monitor", function (table) { + table.string("push_token", 32).alter(); + }); +}; + +exports.down = function (knex) { + return knex.schema + .alterTable("monitor", function (table) { + table.string("push_token", 20).alter(); + }); +}; diff --git a/db/knex_migrations/2023-10-16-0000-create-remote-browsers.js b/db/knex_migrations/2023-10-16-0000-create-remote-browsers.js new file mode 100644 index 0000000..c720d3f --- /dev/null +++ b/db/knex_migrations/2023-10-16-0000-create-remote-browsers.js @@ -0,0 +1,21 @@ +exports.up = function (knex) { + return knex.schema + .createTable("remote_browser", function (table) { + table.increments("id"); + table.string("name", 255).notNullable(); + table.string("url", 255).notNullable(); + table.integer("user_id").unsigned(); + }).alterTable("monitor", function (table) { + // Add new column monitor.remote_browser + table.integer("remote_browser").nullable().defaultTo(null).unsigned() + .index() + .references("id") + .inTable("remote_browser"); + }); +}; + +exports.down = function (knex) { + return knex.schema.dropTable("remote_browser").alterTable("monitor", function (table) { + table.dropColumn("remote_browser"); + }); +}; diff --git a/db/knex_migrations/2023-12-20-0000-alter-status-page.js b/db/knex_migrations/2023-12-20-0000-alter-status-page.js new file mode 100644 index 0000000..61ef5d6 --- /dev/null +++ b/db/knex_migrations/2023-12-20-0000-alter-status-page.js @@ -0,0 +1,12 @@ +exports.up = function (knex) { + return knex.schema + .alterTable("status_page", function (table) { + table.integer("auto_refresh_interval").defaultTo(300).unsigned(); + }); +}; + +exports.down = function (knex) { + return knex.schema.alterTable("status_page", function (table) { + table.dropColumn("auto_refresh_interval"); + }); +}; diff --git a/db/knex_migrations/2023-12-21-0000-stat-ping-min-max.js b/db/knex_migrations/2023-12-21-0000-stat-ping-min-max.js new file mode 100644 index 0000000..d936ce5 --- /dev/null +++ b/db/knex_migrations/2023-12-21-0000-stat-ping-min-max.js @@ -0,0 +1,24 @@ +exports.up = function (knex) { + return knex.schema + .alterTable("stat_daily", function (table) { + table.float("ping_min").notNullable().defaultTo(0).comment("Minimum ping during this period in milliseconds"); + table.float("ping_max").notNullable().defaultTo(0).comment("Maximum ping during this period in milliseconds"); + }) + .alterTable("stat_minutely", function (table) { + table.float("ping_min").notNullable().defaultTo(0).comment("Minimum ping during this period in milliseconds"); + table.float("ping_max").notNullable().defaultTo(0).comment("Maximum ping during this period in milliseconds"); + }); + +}; + +exports.down = function (knex) { + return knex.schema + .alterTable("stat_daily", function (table) { + table.dropColumn("ping_min"); + table.dropColumn("ping_max"); + }) + .alterTable("stat_minutely", function (table) { + table.dropColumn("ping_min"); + table.dropColumn("ping_max"); + }); +}; diff --git a/db/knex_migrations/2023-12-22-0000-hourly-uptime.js b/db/knex_migrations/2023-12-22-0000-hourly-uptime.js new file mode 100644 index 0000000..4305900 --- /dev/null +++ b/db/knex_migrations/2023-12-22-0000-hourly-uptime.js @@ -0,0 +1,26 @@ +exports.up = function (knex) { + return knex.schema + .createTable("stat_hourly", function (table) { + table.increments("id"); + table.comment("This table contains the hourly aggregate statistics for each monitor"); + table.integer("monitor_id").unsigned().notNullable() + .references("id").inTable("monitor") + .onDelete("CASCADE") + .onUpdate("CASCADE"); + table.integer("timestamp") + .notNullable() + .comment("Unix timestamp rounded down to the nearest hour"); + table.float("ping").notNullable().comment("Average ping in milliseconds"); + table.float("ping_min").notNullable().defaultTo(0).comment("Minimum ping during this period in milliseconds"); + table.float("ping_max").notNullable().defaultTo(0).comment("Maximum ping during this period in milliseconds"); + table.smallint("up").notNullable(); + table.smallint("down").notNullable(); + + table.unique([ "monitor_id", "timestamp" ]); + }); +}; + +exports.down = function (knex) { + return knex.schema + .dropTable("stat_hourly"); +}; diff --git a/db/knex_migrations/2024-01-22-0000-stats-extras.js b/db/knex_migrations/2024-01-22-0000-stats-extras.js new file mode 100644 index 0000000..b92e889 --- /dev/null +++ b/db/knex_migrations/2024-01-22-0000-stats-extras.js @@ -0,0 +1,26 @@ +exports.up = function (knex) { + return knex.schema + .alterTable("stat_daily", function (table) { + table.text("extras").defaultTo(null).comment("Extra statistics during this time period"); + }) + .alterTable("stat_minutely", function (table) { + table.text("extras").defaultTo(null).comment("Extra statistics during this time period"); + }) + .alterTable("stat_hourly", function (table) { + table.text("extras").defaultTo(null).comment("Extra statistics during this time period"); + }); + +}; + +exports.down = function (knex) { + return knex.schema + .alterTable("stat_daily", function (table) { + table.dropColumn("extras"); + }) + .alterTable("stat_minutely", function (table) { + table.dropColumn("extras"); + }) + .alterTable("stat_hourly", function (table) { + table.dropColumn("extras"); + }); +}; diff --git a/db/knex_migrations/2024-04-26-0000-snmp-monitor.js b/db/knex_migrations/2024-04-26-0000-snmp-monitor.js new file mode 100644 index 0000000..24752f2 --- /dev/null +++ b/db/knex_migrations/2024-04-26-0000-snmp-monitor.js @@ -0,0 +1,16 @@ +exports.up = function (knex) { + return knex.schema + .alterTable("monitor", function (table) { + table.string("snmp_oid").defaultTo(null); + table.enum("snmp_version", [ "1", "2c", "3" ]).defaultTo("2c"); + table.string("json_path_operator").defaultTo(null); + }); +}; + +exports.down = function (knex) { + return knex.schema.alterTable("monitor", function (table) { + table.dropColumn("snmp_oid"); + table.dropColumn("snmp_version"); + table.dropColumn("json_path_operator"); + }); +}; diff --git a/db/knex_migrations/2024-08-24-000-add-cache-bust.js b/db/knex_migrations/2024-08-24-000-add-cache-bust.js new file mode 100644 index 0000000..3644377 --- /dev/null +++ b/db/knex_migrations/2024-08-24-000-add-cache-bust.js @@ -0,0 +1,13 @@ +exports.up = function (knex) { + return knex.schema + .alterTable("monitor", function (table) { + table.boolean("cache_bust").notNullable().defaultTo(false); + }); +}; + +exports.down = function (knex) { + return knex.schema + .alterTable("monitor", function (table) { + table.dropColumn("cache_bust"); + }); +}; diff --git a/db/knex_migrations/2024-08-24-0000-conditions.js b/db/knex_migrations/2024-08-24-0000-conditions.js new file mode 100644 index 0000000..96352fd --- /dev/null +++ b/db/knex_migrations/2024-08-24-0000-conditions.js @@ -0,0 +1,12 @@ +exports.up = function (knex) { + return knex.schema + .alterTable("monitor", function (table) { + table.text("conditions").notNullable().defaultTo("[]"); + }); +}; + +exports.down = function (knex) { + return knex.schema.alterTable("monitor", function (table) { + table.dropColumn("conditions"); + }); +}; diff --git a/db/knex_migrations/2024-10-1315-rabbitmq-monitor.js b/db/knex_migrations/2024-10-1315-rabbitmq-monitor.js new file mode 100644 index 0000000..6a17f33 --- /dev/null +++ b/db/knex_migrations/2024-10-1315-rabbitmq-monitor.js @@ -0,0 +1,17 @@ +exports.up = function (knex) { + return knex.schema.alterTable("monitor", function (table) { + table.text("rabbitmq_nodes"); + table.string("rabbitmq_username"); + table.string("rabbitmq_password"); + }); + +}; + +exports.down = function (knex) { + return knex.schema.alterTable("monitor", function (table) { + table.dropColumn("rabbitmq_nodes"); + table.dropColumn("rabbitmq_username"); + table.dropColumn("rabbitmq_password"); + }); + +}; diff --git a/db/knex_migrations/2024-11-27-1927-fix-info-json-data-type.js b/db/knex_migrations/2024-11-27-1927-fix-info-json-data-type.js new file mode 100644 index 0000000..0f3f75e --- /dev/null +++ b/db/knex_migrations/2024-11-27-1927-fix-info-json-data-type.js @@ -0,0 +1,13 @@ +// Update info_json column to LONGTEXT mainly for MariaDB +exports.up = function (knex) { + return knex.schema + .alterTable("monitor_tls_info", function (table) { + table.text("info_json", "longtext").alter(); + }); +}; + +exports.down = function (knex) { + return knex.schema.alterTable("monitor_tls_info", function (table) { + table.text("info_json", "text").alter(); + }); +}; diff --git a/db/knex_migrations/README.md b/db/knex_migrations/README.md new file mode 100644 index 0000000..d2b8470 --- /dev/null +++ b/db/knex_migrations/README.md @@ -0,0 +1,56 @@ +# Info + +https://knexjs.org/guide/migrations.html#knexfile-in-other-languages + +## Basic rules + +- All tables must have a primary key named `id` +- Filename format: `YYYY-MM-DD-HHMM-patch-name.js` +- Avoid native SQL syntax, use knex methods, because Uptime Kuma supports SQLite and MariaDB. + +## Template + +```js +exports.up = function(knex) { + +}; + +exports.down = function(knex) { + +}; + +// exports.config = { transaction: false }; +``` + +## Example + +Filename: 2023-06-30-1348-create-user-and-product.js + +```js +exports.up = function(knex) { + return knex.schema + .createTable('user', function (table) { + table.increments('id'); + table.string('first_name', 255).notNullable(); + table.string('last_name', 255).notNullable(); + }) + .createTable('product', function (table) { + table.increments('id'); + table.decimal('price').notNullable(); + table.string('name', 1000).notNullable(); + }).then(() => { + knex("products").insert([ + { price: 10, name: "Apple" }, + { price: 20, name: "Orange" }, + ]); + }); +}; + +exports.down = function(knex) { + return knex.schema + .dropTable("product") + .dropTable("user"); +}; +``` + +https://knexjs.org/guide/migrations.html#transactions-in-migrations diff --git a/db/kuma.db b/db/kuma.db new file mode 100644 index 0000000..6e02ccc Binary files /dev/null and b/db/kuma.db differ diff --git a/db/old_migrations/README.md b/db/old_migrations/README.md new file mode 100644 index 0000000..3b2bd96 --- /dev/null +++ b/db/old_migrations/README.md @@ -0,0 +1,3 @@ +# Don't create a new migration file here + +Please go to ./db/knex_migrations/README.md diff --git a/db/old_migrations/patch-2fa-invalidate-used-token.sql b/db/old_migrations/patch-2fa-invalidate-used-token.sql new file mode 100644 index 0000000..2f0b42c --- /dev/null +++ b/db/old_migrations/patch-2fa-invalidate-used-token.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE user + ADD twofa_last_token VARCHAR(6); + +COMMIT; diff --git a/db/old_migrations/patch-2fa.sql b/db/old_migrations/patch-2fa.sql new file mode 100644 index 0000000..35069d8 --- /dev/null +++ b/db/old_migrations/patch-2fa.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE user + ADD twofa_secret VARCHAR(64); + +ALTER TABLE user + ADD twofa_status BOOLEAN default 0 NOT NULL; + +COMMIT; diff --git a/db/old_migrations/patch-add-certificate-expiry-status-page.sql b/db/old_migrations/patch-add-certificate-expiry-status-page.sql new file mode 100644 index 0000000..63a2010 --- /dev/null +++ b/db/old_migrations/patch-add-certificate-expiry-status-page.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE status_page + ADD show_certificate_expiry BOOLEAN default 0 NOT NULL; + +COMMIT; diff --git a/db/old_migrations/patch-add-clickable-status-page-link.sql b/db/old_migrations/patch-add-clickable-status-page-link.sql new file mode 100644 index 0000000..cd11cdd --- /dev/null +++ b/db/old_migrations/patch-add-clickable-status-page-link.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor_group + ADD send_url BOOLEAN DEFAULT 0 NOT NULL; + +COMMIT; diff --git a/db/old_migrations/patch-add-description-monitor.sql b/db/old_migrations/patch-add-description-monitor.sql new file mode 100644 index 0000000..da1aa55 --- /dev/null +++ b/db/old_migrations/patch-add-description-monitor.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD description TEXT default null; + +COMMIT; diff --git a/db/old_migrations/patch-add-docker-columns.sql b/db/old_migrations/patch-add-docker-columns.sql new file mode 100644 index 0000000..4cea448 --- /dev/null +++ b/db/old_migrations/patch-add-docker-columns.sql @@ -0,0 +1,18 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +CREATE TABLE docker_host ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + user_id INT NOT NULL, + docker_daemon VARCHAR(255), + docker_type VARCHAR(255), + name VARCHAR(255) +); + +ALTER TABLE monitor + ADD docker_host INTEGER REFERENCES docker_host(id); + +ALTER TABLE monitor + ADD docker_container VARCHAR(255); + +COMMIT; diff --git a/db/old_migrations/patch-add-gamedig-given-port.sql b/db/old_migrations/patch-add-gamedig-given-port.sql new file mode 100644 index 0000000..897a9c7 --- /dev/null +++ b/db/old_migrations/patch-add-gamedig-given-port.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD gamedig_given_port_only BOOLEAN default 1 not null; + +COMMIT; diff --git a/db/old_migrations/patch-add-gamedig-monitor.sql b/db/old_migrations/patch-add-gamedig-monitor.sql new file mode 100644 index 0000000..e20a0cd --- /dev/null +++ b/db/old_migrations/patch-add-gamedig-monitor.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + + ALTER TABLE monitor + ADD game VARCHAR(255); + +COMMIT; diff --git a/db/old_migrations/patch-add-google-analytics-status-page-tag.sql b/db/old_migrations/patch-add-google-analytics-status-page-tag.sql new file mode 100644 index 0000000..fe6fa34 --- /dev/null +++ b/db/old_migrations/patch-add-google-analytics-status-page-tag.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE status_page + ADD google_analytics_tag_id VARCHAR; + +COMMIT; diff --git a/db/old_migrations/patch-add-invert-keyword.sql b/db/old_migrations/patch-add-invert-keyword.sql new file mode 100644 index 0000000..8ca199e --- /dev/null +++ b/db/old_migrations/patch-add-invert-keyword.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD invert_keyword BOOLEAN default 0 not null; + +COMMIT; diff --git a/db/old_migrations/patch-add-other-auth.sql b/db/old_migrations/patch-add-other-auth.sql new file mode 100644 index 0000000..b83f1ee --- /dev/null +++ b/db/old_migrations/patch-add-other-auth.sql @@ -0,0 +1,18 @@ +BEGIN TRANSACTION; + + ALTER TABLE monitor + ADD auth_method VARCHAR(250); + + ALTER TABLE monitor + ADD auth_domain TEXT; + ALTER TABLE monitor + + ADD auth_workstation TEXT; + +COMMIT; + +BEGIN TRANSACTION; + UPDATE monitor + SET auth_method = 'basic' + WHERE basic_auth_user is not null; +COMMIT; diff --git a/db/old_migrations/patch-add-parent-monitor.sql b/db/old_migrations/patch-add-parent-monitor.sql new file mode 100644 index 0000000..ac2697f --- /dev/null +++ b/db/old_migrations/patch-add-parent-monitor.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD parent INTEGER REFERENCES [monitor] ([id]) ON DELETE SET NULL ON UPDATE CASCADE; + +COMMIT; diff --git a/db/old_migrations/patch-add-radius-monitor.sql b/db/old_migrations/patch-add-radius-monitor.sql new file mode 100644 index 0000000..4625f21 --- /dev/null +++ b/db/old_migrations/patch-add-radius-monitor.sql @@ -0,0 +1,19 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD radius_username VARCHAR(255); + +ALTER TABLE monitor + ADD radius_password VARCHAR(255); + +ALTER TABLE monitor + ADD radius_calling_station_id VARCHAR(50); + +ALTER TABLE monitor + ADD radius_called_station_id VARCHAR(50); + +ALTER TABLE monitor + ADD radius_secret VARCHAR(255); + +COMMIT; diff --git a/db/old_migrations/patch-add-retry-interval-monitor.sql b/db/old_migrations/patch-add-retry-interval-monitor.sql new file mode 100644 index 0000000..adb6462 --- /dev/null +++ b/db/old_migrations/patch-add-retry-interval-monitor.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD retry_interval INTEGER default 0 not null; + +COMMIT; \ No newline at end of file diff --git a/db/old_migrations/patch-add-sqlserver-monitor.sql b/db/old_migrations/patch-add-sqlserver-monitor.sql new file mode 100644 index 0000000..f253dd2 --- /dev/null +++ b/db/old_migrations/patch-add-sqlserver-monitor.sql @@ -0,0 +1,10 @@ +BEGIN TRANSACTION; + + ALTER TABLE monitor + ADD database_connection_string VARCHAR(2000); + + ALTER TABLE monitor + ADD database_query TEXT; + + + COMMIT diff --git a/db/old_migrations/patch-add-timeout-monitor.sql b/db/old_migrations/patch-add-timeout-monitor.sql new file mode 100644 index 0000000..b62eb14 --- /dev/null +++ b/db/old_migrations/patch-add-timeout-monitor.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD timeout DOUBLE default 0 not null; + +COMMIT; diff --git a/db/old_migrations/patch-added-json-query.sql b/db/old_migrations/patch-added-json-query.sql new file mode 100644 index 0000000..d5b7f1a --- /dev/null +++ b/db/old_migrations/patch-added-json-query.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD json_path TEXT; + +ALTER TABLE monitor + ADD expected_value VARCHAR(255); + +COMMIT; diff --git a/db/old_migrations/patch-added-kafka-producer.sql b/db/old_migrations/patch-added-kafka-producer.sql new file mode 100644 index 0000000..933d30b --- /dev/null +++ b/db/old_migrations/patch-added-kafka-producer.sql @@ -0,0 +1,22 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD kafka_producer_topic VARCHAR(255); + +ALTER TABLE monitor + ADD kafka_producer_brokers TEXT; + +ALTER TABLE monitor + ADD kafka_producer_ssl INTEGER; + +ALTER TABLE monitor + ADD kafka_producer_allow_auto_topic_creation VARCHAR(255); + +ALTER TABLE monitor + ADD kafka_producer_sasl_options TEXT; + +ALTER TABLE monitor + ADD kafka_producer_message TEXT; + +COMMIT; diff --git a/db/old_migrations/patch-added-mqtt-monitor.sql b/db/old_migrations/patch-added-mqtt-monitor.sql new file mode 100644 index 0000000..02b0b09 --- /dev/null +++ b/db/old_migrations/patch-added-mqtt-monitor.sql @@ -0,0 +1,16 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD mqtt_topic TEXT; + +ALTER TABLE monitor + ADD mqtt_success_message VARCHAR(255); + +ALTER TABLE monitor + ADD mqtt_username VARCHAR(255); + +ALTER TABLE monitor + ADD mqtt_password VARCHAR(255); + +COMMIT; diff --git a/db/old_migrations/patch-api-key-table.sql b/db/old_migrations/patch-api-key-table.sql new file mode 100644 index 0000000..4116db3 --- /dev/null +++ b/db/old_migrations/patch-api-key-table.sql @@ -0,0 +1,15 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +CREATE TABLE [api_key] ( + [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + [key] VARCHAR(255) NOT NULL, + [name] VARCHAR(255) NOT NULL, + [user_id] INTEGER NOT NULL, + [created_date] DATETIME DEFAULT (DATETIME('now')) NOT NULL, + [active] BOOLEAN DEFAULT 1 NOT NULL, + [expires] DATETIME DEFAULT NULL, + CONSTRAINT FK_user FOREIGN KEY ([user_id]) REFERENCES [user]([id]) ON DELETE CASCADE ON UPDATE CASCADE +); + +COMMIT; diff --git a/db/old_migrations/patch-fix-kafka-producer-booleans.sql b/db/old_migrations/patch-fix-kafka-producer-booleans.sql new file mode 100644 index 0000000..be2e992 --- /dev/null +++ b/db/old_migrations/patch-fix-kafka-producer-booleans.sql @@ -0,0 +1,34 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +-- Rename COLUMNs to another one (suffixed by `_old`) +ALTER TABLE monitor + RENAME COLUMN kafka_producer_ssl TO kafka_producer_ssl_old; + +ALTER TABLE monitor + RENAME COLUMN kafka_producer_allow_auto_topic_creation TO kafka_producer_allow_auto_topic_creation_old; + +-- Add correct COLUMNs +ALTER TABLE monitor + ADD COLUMN kafka_producer_ssl BOOLEAN default 0 NOT NULL; + +ALTER TABLE monitor + ADD COLUMN kafka_producer_allow_auto_topic_creation BOOLEAN default 0 NOT NULL; + +-- These SQL is still not fully safe. See https://github.com/louislam/uptime-kuma/issues/4039. + +-- Set bring old values from `_old` COLUMNs to correct ones +-- UPDATE monitor SET kafka_producer_allow_auto_topic_creation = monitor.kafka_producer_allow_auto_topic_creation_old +-- WHERE monitor.kafka_producer_allow_auto_topic_creation_old IS NOT NULL; + +-- UPDATE monitor SET kafka_producer_ssl = monitor.kafka_producer_ssl_old +-- WHERE monitor.kafka_producer_ssl_old IS NOT NULL; + +-- Remove old COLUMNs +ALTER TABLE monitor + DROP COLUMN kafka_producer_allow_auto_topic_creation_old; + +ALTER TABLE monitor + DROP COLUMN kafka_producer_ssl_old; + +COMMIT; diff --git a/db/old_migrations/patch-group-table.sql b/db/old_migrations/patch-group-table.sql new file mode 100644 index 0000000..1c6f366 --- /dev/null +++ b/db/old_migrations/patch-group-table.sql @@ -0,0 +1,30 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +create table `group` +( + id INTEGER not null + constraint group_pk + primary key autoincrement, + name VARCHAR(255) not null, + created_date DATETIME default (DATETIME('now')) not null, + public BOOLEAN default 0 not null, + active BOOLEAN default 1 not null, + weight BOOLEAN NOT NULL DEFAULT 1000 +); + +CREATE TABLE [monitor_group] +( + [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + [monitor_id] INTEGER NOT NULL REFERENCES [monitor] ([id]) ON DELETE CASCADE ON UPDATE CASCADE, + [group_id] INTEGER NOT NULL REFERENCES [group] ([id]) ON DELETE CASCADE ON UPDATE CASCADE, + weight BOOLEAN NOT NULL DEFAULT 1000 +); + +CREATE INDEX [fk] + ON [monitor_group] ( + [monitor_id], + [group_id]); + + +COMMIT; diff --git a/db/old_migrations/patch-grpc-monitor.sql b/db/old_migrations/patch-grpc-monitor.sql new file mode 100644 index 0000000..bac024e --- /dev/null +++ b/db/old_migrations/patch-grpc-monitor.sql @@ -0,0 +1,25 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD grpc_url VARCHAR(255) default null; + +ALTER TABLE monitor + ADD grpc_protobuf TEXT default null; + +ALTER TABLE monitor + ADD grpc_body TEXT default null; + +ALTER TABLE monitor + ADD grpc_metadata TEXT default null; + +ALTER TABLE monitor + ADD grpc_method VARCHAR(255) default null; + +ALTER TABLE monitor + ADD grpc_service_name VARCHAR(255) default null; + +ALTER TABLE monitor + ADD grpc_enable_tls BOOLEAN default 0 not null; + +COMMIT; diff --git a/db/old_migrations/patch-http-body-encoding.sql b/db/old_migrations/patch-http-body-encoding.sql new file mode 100644 index 0000000..322c8b8 --- /dev/null +++ b/db/old_migrations/patch-http-body-encoding.sql @@ -0,0 +1,12 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor ADD http_body_encoding VARCHAR(25); + +COMMIT; + +BEGIN TRANSACTION; + +UPDATE monitor SET http_body_encoding = 'json' WHERE (type = 'http' or type = 'keyword') AND http_body_encoding IS NULL; + +COMMIT; diff --git a/db/old_migrations/patch-http-monitor-method-body-and-headers.sql b/db/old_migrations/patch-http-monitor-method-body-and-headers.sql new file mode 100644 index 0000000..dc2526b --- /dev/null +++ b/db/old_migrations/patch-http-monitor-method-body-and-headers.sql @@ -0,0 +1,13 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD method TEXT default 'GET' not null; + +ALTER TABLE monitor + ADD body TEXT default null; + +ALTER TABLE monitor + ADD headers TEXT default null; + +COMMIT; diff --git a/db/old_migrations/patch-improve-performance.sql b/db/old_migrations/patch-improve-performance.sql new file mode 100644 index 0000000..c07d629 --- /dev/null +++ b/db/old_migrations/patch-improve-performance.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +-- For sendHeartbeatList +CREATE INDEX monitor_time_index ON heartbeat (monitor_id, time); + +-- For sendImportantHeartbeatList +CREATE INDEX monitor_important_time_index ON heartbeat (monitor_id, important,time); + +COMMIT; diff --git a/db/old_migrations/patch-incident-table.sql b/db/old_migrations/patch-incident-table.sql new file mode 100644 index 0000000..531cfb3 --- /dev/null +++ b/db/old_migrations/patch-incident-table.sql @@ -0,0 +1,18 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +create table incident +( + id INTEGER not null + constraint incident_pk + primary key autoincrement, + title VARCHAR(255) not null, + content TEXT not null, + style VARCHAR(30) default 'warning' not null, + created_date DATETIME default (DATETIME('now')) not null, + last_updated_date DATETIME, + pin BOOLEAN default 1 not null, + active BOOLEAN default 1 not null +); + +COMMIT; diff --git a/db/old_migrations/patch-maintenance-cron.sql b/db/old_migrations/patch-maintenance-cron.sql new file mode 100644 index 0000000..bc51b88 --- /dev/null +++ b/db/old_migrations/patch-maintenance-cron.sql @@ -0,0 +1,11 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +DROP TABLE maintenance_timeslot; + +-- 999 characters. https://stackoverflow.com/questions/46134830/maximum-length-for-cron-job +ALTER TABLE maintenance ADD cron TEXT; +ALTER TABLE maintenance ADD timezone VARCHAR(255); +ALTER TABLE maintenance ADD duration INTEGER; + +COMMIT; diff --git a/db/old_migrations/patch-maintenance-table2.sql b/db/old_migrations/patch-maintenance-table2.sql new file mode 100644 index 0000000..96b2ebd --- /dev/null +++ b/db/old_migrations/patch-maintenance-table2.sql @@ -0,0 +1,83 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +-- Just for someone who tested maintenance before (patch-maintenance-table.sql) +DROP TABLE IF EXISTS maintenance_status_page; +DROP TABLE IF EXISTS monitor_maintenance; +DROP TABLE IF EXISTS maintenance; +DROP TABLE IF EXISTS maintenance_timeslot; + +-- maintenance +CREATE TABLE [maintenance] ( + [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + [title] VARCHAR(150) NOT NULL, + [description] TEXT NOT NULL, + [user_id] INTEGER REFERENCES [user]([id]) ON DELETE SET NULL ON UPDATE CASCADE, + [active] BOOLEAN NOT NULL DEFAULT 1, + [strategy] VARCHAR(50) NOT NULL DEFAULT 'single', + [start_date] DATETIME, + [end_date] DATETIME, + [start_time] TIME, + [end_time] TIME, + [weekdays] VARCHAR2(250) DEFAULT '[]', + [days_of_month] TEXT DEFAULT '[]', + [interval_day] INTEGER +); + +CREATE INDEX [manual_active] ON [maintenance] ( + [strategy], + [active] +); + +CREATE INDEX [active] ON [maintenance] ([active]); + +CREATE INDEX [maintenance_user_id] ON [maintenance] ([user_id]); + +-- maintenance_status_page +CREATE TABLE maintenance_status_page ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + status_page_id INTEGER NOT NULL, + maintenance_id INTEGER NOT NULL, + CONSTRAINT FK_maintenance FOREIGN KEY (maintenance_id) REFERENCES maintenance (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_status_page FOREIGN KEY (status_page_id) REFERENCES status_page (id) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE INDEX [status_page_id_index] + ON [maintenance_status_page]([status_page_id]); + +CREATE INDEX [maintenance_id_index] + ON [maintenance_status_page]([maintenance_id]); + +-- maintenance_timeslot +CREATE TABLE [maintenance_timeslot] ( + [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + [maintenance_id] INTEGER NOT NULL CONSTRAINT [FK_maintenance] REFERENCES [maintenance]([id]) ON DELETE CASCADE ON UPDATE CASCADE, + [start_date] DATETIME NOT NULL, + [end_date] DATETIME, + [generated_next] BOOLEAN DEFAULT 0 +); + +CREATE INDEX [maintenance_id] ON [maintenance_timeslot] ([maintenance_id] DESC); + +CREATE INDEX [active_timeslot_index] ON [maintenance_timeslot] ( + [maintenance_id] DESC, + [start_date] DESC, + [end_date] DESC +); + +CREATE INDEX [generated_next_index] ON [maintenance_timeslot] ([generated_next]); + +-- monitor_maintenance +CREATE TABLE monitor_maintenance ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + monitor_id INTEGER NOT NULL, + maintenance_id INTEGER NOT NULL, + CONSTRAINT FK_maintenance FOREIGN KEY (maintenance_id) REFERENCES maintenance (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_monitor FOREIGN KEY (monitor_id) REFERENCES monitor (id) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE INDEX [maintenance_id_index2] ON [monitor_maintenance]([maintenance_id]); + +CREATE INDEX [monitor_id_index] ON [monitor_maintenance]([monitor_id]); + +COMMIT; diff --git a/db/old_migrations/patch-monitor-add-resend-interval.sql b/db/old_migrations/patch-monitor-add-resend-interval.sql new file mode 100644 index 0000000..8e28bf6 --- /dev/null +++ b/db/old_migrations/patch-monitor-add-resend-interval.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD resend_interval INTEGER default 0 not null; + +ALTER TABLE heartbeat + ADD down_count INTEGER default 0 not null; + +COMMIT; diff --git a/db/old_migrations/patch-monitor-basic-auth.sql b/db/old_migrations/patch-monitor-basic-auth.sql new file mode 100644 index 0000000..de4bdef --- /dev/null +++ b/db/old_migrations/patch-monitor-basic-auth.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD basic_auth_user TEXT default null; + +ALTER TABLE monitor + ADD basic_auth_pass TEXT default null; + +COMMIT; diff --git a/db/old_migrations/patch-monitor-expiry-notification.sql b/db/old_migrations/patch-monitor-expiry-notification.sql new file mode 100644 index 0000000..7a33001 --- /dev/null +++ b/db/old_migrations/patch-monitor-expiry-notification.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD expiry_notification BOOLEAN default 1; + +COMMIT; diff --git a/db/old_migrations/patch-monitor-oauth-cc.sql b/db/old_migrations/patch-monitor-oauth-cc.sql new file mode 100644 index 0000000..f33e952 --- /dev/null +++ b/db/old_migrations/patch-monitor-oauth-cc.sql @@ -0,0 +1,19 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD oauth_client_id TEXT default null; + +ALTER TABLE monitor + ADD oauth_client_secret TEXT default null; + +ALTER TABLE monitor + ADD oauth_token_url TEXT default null; + +ALTER TABLE monitor + ADD oauth_scopes TEXT default null; + +ALTER TABLE monitor + ADD oauth_auth_method TEXT default null; + +COMMIT; diff --git a/db/old_migrations/patch-monitor-push_token.sql b/db/old_migrations/patch-monitor-push_token.sql new file mode 100644 index 0000000..8c2e7a4 --- /dev/null +++ b/db/old_migrations/patch-monitor-push_token.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD push_token VARCHAR(20) DEFAULT NULL; + +COMMIT; diff --git a/db/old_migrations/patch-monitor-tls-info-add-fk.sql b/db/old_migrations/patch-monitor-tls-info-add-fk.sql new file mode 100644 index 0000000..9b9c2d2 --- /dev/null +++ b/db/old_migrations/patch-monitor-tls-info-add-fk.sql @@ -0,0 +1,18 @@ +BEGIN TRANSACTION; + +PRAGMA writable_schema = TRUE; + +UPDATE + SQLITE_MASTER +SET + sql = replace(sql, + 'monitor_id INTEGER NOT NULL', + 'monitor_id INTEGER NOT NULL REFERENCES [monitor] ([id]) ON DELETE CASCADE ON UPDATE CASCADE' +) +WHERE + name = 'monitor_tls_info' + AND type = 'table'; + +PRAGMA writable_schema = RESET; + +COMMIT; diff --git a/db/old_migrations/patch-monitor-tls.sql b/db/old_migrations/patch-monitor-tls.sql new file mode 100644 index 0000000..ac4edb7 --- /dev/null +++ b/db/old_migrations/patch-monitor-tls.sql @@ -0,0 +1,13 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD tls_ca TEXT default null; + +ALTER TABLE monitor + ADD tls_cert TEXT default null; + +ALTER TABLE monitor + ADD tls_key TEXT default null; + +COMMIT; diff --git a/db/old_migrations/patch-notification-config.sql b/db/old_migrations/patch-notification-config.sql new file mode 100644 index 0000000..16944fb --- /dev/null +++ b/db/old_migrations/patch-notification-config.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +-- SQLite: Change the data type of the column "config" from VARCHAR to TEXT +ALTER TABLE notification RENAME COLUMN config TO config_old; +ALTER TABLE notification ADD COLUMN config TEXT; +UPDATE notification SET config = config_old; +ALTER TABLE notification DROP COLUMN config_old; + +COMMIT; diff --git a/db/old_migrations/patch-notification_sent_history.sql b/db/old_migrations/patch-notification_sent_history.sql new file mode 100644 index 0000000..759eb38 --- /dev/null +++ b/db/old_migrations/patch-notification_sent_history.sql @@ -0,0 +1,18 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +CREATE TABLE [notification_sent_history] ( + [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + [type] VARCHAR(50) NOT NULL, + [monitor_id] INTEGER NOT NULL, + [days] INTEGER NOT NULL, + UNIQUE([type], [monitor_id], [days]) +); + +CREATE INDEX [good_index] ON [notification_sent_history] ( + [type], + [monitor_id], + [days] +); + +COMMIT; diff --git a/db/old_migrations/patch-ping-packet-size.sql b/db/old_migrations/patch-ping-packet-size.sql new file mode 100644 index 0000000..f127fc2 --- /dev/null +++ b/db/old_migrations/patch-ping-packet-size.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD packet_size INTEGER DEFAULT 56 NOT NULL; + +COMMIT; diff --git a/db/old_migrations/patch-proxy.sql b/db/old_migrations/patch-proxy.sql new file mode 100644 index 0000000..41897b1 --- /dev/null +++ b/db/old_migrations/patch-proxy.sql @@ -0,0 +1,23 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +CREATE TABLE proxy ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + user_id INT NOT NULL, + protocol VARCHAR(10) NOT NULL, + host VARCHAR(255) NOT NULL, + port SMALLINT NOT NULL, + auth BOOLEAN NOT NULL, + username VARCHAR(255) NULL, + password VARCHAR(255) NULL, + active BOOLEAN NOT NULL DEFAULT 1, + 'default' BOOLEAN NOT NULL DEFAULT 0, + created_date DATETIME DEFAULT (DATETIME('now')) NOT NULL +); + +ALTER TABLE monitor ADD COLUMN proxy_id INTEGER REFERENCES proxy(id); + +CREATE INDEX proxy_id ON monitor (proxy_id); +CREATE INDEX proxy_user_id ON proxy (user_id); + +COMMIT; diff --git a/db/old_migrations/patch-setting-value-type.sql b/db/old_migrations/patch-setting-value-type.sql new file mode 100644 index 0000000..4816bc6 --- /dev/null +++ b/db/old_migrations/patch-setting-value-type.sql @@ -0,0 +1,21 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +-- Generated by Intellij IDEA +create table setting_dg_tmp +( + id INTEGER + primary key autoincrement, + key VARCHAR(200) not null + unique, + value TEXT, + type VARCHAR(20) +); + +insert into setting_dg_tmp(id, key, value, type) select id, key, value, type from setting; + +drop table setting; + +alter table setting_dg_tmp rename to setting; + +COMMIT; diff --git a/db/old_migrations/patch-status-page-footer-css.sql b/db/old_migrations/patch-status-page-footer-css.sql new file mode 100644 index 0000000..beef922 --- /dev/null +++ b/db/old_migrations/patch-status-page-footer-css.sql @@ -0,0 +1,11 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE status_page + ADD footer_text TEXT; +ALTER TABLE status_page + ADD custom_css TEXT; +ALTER TABLE status_page + ADD show_powered_by BOOLEAN NOT NULL DEFAULT 1; + +COMMIT; diff --git a/db/old_migrations/patch-status-page.sql b/db/old_migrations/patch-status-page.sql new file mode 100644 index 0000000..d23b75b --- /dev/null +++ b/db/old_migrations/patch-status-page.sql @@ -0,0 +1,31 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +CREATE TABLE [status_page]( + [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + [slug] VARCHAR(255) NOT NULL UNIQUE, + [title] VARCHAR(255) NOT NULL, + [description] TEXT, + [icon] VARCHAR(255) NOT NULL, + [theme] VARCHAR(30) NOT NULL, + [published] BOOLEAN NOT NULL DEFAULT 1, + [search_engine_index] BOOLEAN NOT NULL DEFAULT 1, + [show_tags] BOOLEAN NOT NULL DEFAULT 0, + [password] VARCHAR, + [created_date] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + [modified_date] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE UNIQUE INDEX [slug] ON [status_page]([slug]); + + +CREATE TABLE [status_page_cname]( + [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + [status_page_id] INTEGER NOT NULL REFERENCES [status_page]([id]) ON DELETE CASCADE ON UPDATE CASCADE, + [domain] VARCHAR NOT NULL UNIQUE +); + +ALTER TABLE incident ADD status_page_id INTEGER; +ALTER TABLE [group] ADD status_page_id INTEGER; + +COMMIT; diff --git a/db/old_migrations/patch-timeout.sql b/db/old_migrations/patch-timeout.sql new file mode 100644 index 0000000..f257112 --- /dev/null +++ b/db/old_migrations/patch-timeout.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +UPDATE monitor SET timeout = (interval * 0.8) +WHERE timeout IS NULL OR timeout <= 0; + +COMMIT; diff --git a/db/old_migrations/patch1.sql b/db/old_migrations/patch1.sql new file mode 100644 index 0000000..6a31fa2 --- /dev/null +++ b/db/old_migrations/patch1.sql @@ -0,0 +1,37 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +-- Change Monitor.created_date from "TIMESTAMP" to "DATETIME" +-- SQL Generated by Intellij Idea +PRAGMA foreign_keys=off; + +BEGIN TRANSACTION; + +create table monitor_dg_tmp +( + id INTEGER not null + primary key autoincrement, + name VARCHAR(150), + active BOOLEAN default 1 not null, + user_id INTEGER + references user + on update cascade on delete set null, + interval INTEGER default 20 not null, + url TEXT, + type VARCHAR(20), + weight INTEGER default 2000, + hostname VARCHAR(255), + port INTEGER, + created_date DATETIME, + keyword VARCHAR(255) +); + +insert into monitor_dg_tmp(id, name, active, user_id, interval, url, type, weight, hostname, port, created_date, keyword) select id, name, active, user_id, interval, url, type, weight, hostname, port, created_date, keyword from monitor; + +drop table monitor; + +alter table monitor_dg_tmp rename to monitor; + +create index user_id on monitor (user_id); + +COMMIT; + +PRAGMA foreign_keys=on; diff --git a/db/old_migrations/patch10.sql b/db/old_migrations/patch10.sql new file mode 100644 index 0000000..488db11 --- /dev/null +++ b/db/old_migrations/patch10.sql @@ -0,0 +1,19 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +CREATE TABLE tag ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + name VARCHAR(255) NOT NULL, + color VARCHAR(255) NOT NULL, + created_date DATETIME DEFAULT (DATETIME('now')) NOT NULL +); + +CREATE TABLE monitor_tag ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + monitor_id INTEGER NOT NULL, + tag_id INTEGER NOT NULL, + value TEXT, + CONSTRAINT FK_tag FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_monitor FOREIGN KEY (monitor_id) REFERENCES monitor(id) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE INDEX monitor_tag_monitor_id_index ON monitor_tag (monitor_id); +CREATE INDEX monitor_tag_tag_id_index ON monitor_tag (tag_id); diff --git a/db/old_migrations/patch2.sql b/db/old_migrations/patch2.sql new file mode 100644 index 0000000..2f34e29 --- /dev/null +++ b/db/old_migrations/patch2.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +CREATE TABLE monitor_tls_info ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + monitor_id INTEGER NOT NULL, + info_json TEXT +); + +COMMIT; diff --git a/db/old_migrations/patch3.sql b/db/old_migrations/patch3.sql new file mode 100644 index 0000000..e615632 --- /dev/null +++ b/db/old_migrations/patch3.sql @@ -0,0 +1,37 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +-- Add maxretries column to monitor +PRAGMA foreign_keys=off; + +BEGIN TRANSACTION; + +create table monitor_dg_tmp +( + id INTEGER not null + primary key autoincrement, + name VARCHAR(150), + active BOOLEAN default 1 not null, + user_id INTEGER + references user + on update cascade on delete set null, + interval INTEGER default 20 not null, + url TEXT, + type VARCHAR(20), + weight INTEGER default 2000, + hostname VARCHAR(255), + port INTEGER, + created_date DATETIME, + keyword VARCHAR(255), + maxretries INTEGER NOT NULL DEFAULT 0 +); + +insert into monitor_dg_tmp(id, name, active, user_id, interval, url, type, weight, hostname, port, created_date, keyword) select id, name, active, user_id, interval, url, type, weight, hostname, port, created_date, keyword from monitor; + +drop table monitor; + +alter table monitor_dg_tmp rename to monitor; + +create index user_id on monitor (user_id); + +COMMIT; + +PRAGMA foreign_keys=on; diff --git a/db/old_migrations/patch4.sql b/db/old_migrations/patch4.sql new file mode 100644 index 0000000..ff40da2 --- /dev/null +++ b/db/old_migrations/patch4.sql @@ -0,0 +1,40 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +-- OK.... serious wrong, missing maxretries column +-- Developers should patch it manually if you have missing the maxretries column +PRAGMA foreign_keys=off; + +BEGIN TRANSACTION; + +create table monitor_dg_tmp +( + id INTEGER not null + primary key autoincrement, + name VARCHAR(150), + active BOOLEAN default 1 not null, + user_id INTEGER + references user + on update cascade on delete set null, + interval INTEGER default 20 not null, + url TEXT, + type VARCHAR(20), + weight INTEGER default 2000, + hostname VARCHAR(255), + port INTEGER, + created_date DATETIME, + keyword VARCHAR(255), + maxretries INTEGER NOT NULL DEFAULT 0, + ignore_tls BOOLEAN default 0 not null, + upside_down BOOLEAN default 0 not null +); + +insert into monitor_dg_tmp(id, name, active, user_id, interval, url, type, weight, hostname, port, created_date, keyword, maxretries) select id, name, active, user_id, interval, url, type, weight, hostname, port, created_date, keyword, maxretries from monitor; + +drop table monitor; + +alter table monitor_dg_tmp rename to monitor; + +create index user_id on monitor (user_id); + +COMMIT; + +PRAGMA foreign_keys=on; diff --git a/db/old_migrations/patch5.sql b/db/old_migrations/patch5.sql new file mode 100644 index 0000000..5730b2d --- /dev/null +++ b/db/old_migrations/patch5.sql @@ -0,0 +1,70 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +PRAGMA foreign_keys = off; + +BEGIN TRANSACTION; + +create table monitor_dg_tmp ( + id INTEGER not null primary key autoincrement, + name VARCHAR(150), + active BOOLEAN default 1 not null, + user_id INTEGER references user on update cascade on delete + set + null, + interval INTEGER default 20 not null, + url TEXT, + type VARCHAR(20), + weight INTEGER default 2000, + hostname VARCHAR(255), + port INTEGER, + created_date DATETIME default (DATETIME('now')) not null, + keyword VARCHAR(255), + maxretries INTEGER NOT NULL DEFAULT 0, + ignore_tls BOOLEAN default 0 not null, + upside_down BOOLEAN default 0 not null +); + +insert into + monitor_dg_tmp( + id, + name, + active, + user_id, + interval, + url, + type, + weight, + hostname, + port, + keyword, + maxretries, + ignore_tls, + upside_down + ) +select + id, + name, + active, + user_id, + interval, + url, + type, + weight, + hostname, + port, + keyword, + maxretries, + ignore_tls, + upside_down +from + monitor; + +drop table monitor; + +alter table + monitor_dg_tmp rename to monitor; + +create index user_id on monitor (user_id); + +COMMIT; + +PRAGMA foreign_keys = on; diff --git a/db/old_migrations/patch6.sql b/db/old_migrations/patch6.sql new file mode 100644 index 0000000..4f539a2 --- /dev/null +++ b/db/old_migrations/patch6.sql @@ -0,0 +1,74 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +PRAGMA foreign_keys = off; + +BEGIN TRANSACTION; + +create table monitor_dg_tmp ( + id INTEGER not null primary key autoincrement, + name VARCHAR(150), + active BOOLEAN default 1 not null, + user_id INTEGER references user on update cascade on delete + set + null, + interval INTEGER default 20 not null, + url TEXT, + type VARCHAR(20), + weight INTEGER default 2000, + hostname VARCHAR(255), + port INTEGER, + created_date DATETIME default (DATETIME('now')) not null, + keyword VARCHAR(255), + maxretries INTEGER NOT NULL DEFAULT 0, + ignore_tls BOOLEAN default 0 not null, + upside_down BOOLEAN default 0 not null, + maxredirects INTEGER default 10 not null, + accepted_statuscodes_json TEXT default '["200-299"]' not null +); + +insert into + monitor_dg_tmp( + id, + name, + active, + user_id, + interval, + url, + type, + weight, + hostname, + port, + created_date, + keyword, + maxretries, + ignore_tls, + upside_down + ) +select + id, + name, + active, + user_id, + interval, + url, + type, + weight, + hostname, + port, + created_date, + keyword, + maxretries, + ignore_tls, + upside_down +from + monitor; + +drop table monitor; + +alter table + monitor_dg_tmp rename to monitor; + +create index user_id on monitor (user_id); + +COMMIT; + +PRAGMA foreign_keys = on; diff --git a/db/old_migrations/patch7.sql b/db/old_migrations/patch7.sql new file mode 100644 index 0000000..2e8eba1 --- /dev/null +++ b/db/old_migrations/patch7.sql @@ -0,0 +1,10 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD dns_resolve_type VARCHAR(5); + +ALTER TABLE monitor + ADD dns_resolve_server VARCHAR(255); + +COMMIT; diff --git a/db/old_migrations/patch8.sql b/db/old_migrations/patch8.sql new file mode 100644 index 0000000..d63a594 --- /dev/null +++ b/db/old_migrations/patch8.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE monitor + ADD dns_last_result VARCHAR(255); + +COMMIT; diff --git a/db/old_migrations/patch9.sql b/db/old_migrations/patch9.sql new file mode 100644 index 0000000..d4d13aa --- /dev/null +++ b/db/old_migrations/patch9.sql @@ -0,0 +1,7 @@ +-- You should not modify if this have pushed to Github, unless it does serious wrong with the db. +BEGIN TRANSACTION; + +ALTER TABLE notification + ADD is_default BOOLEAN default 0 NOT NULL; + +COMMIT; diff --git a/db/patch-monitor-tls-info-add-fk.sql b/db/patch-monitor-tls-info-add-fk.sql new file mode 100644 index 0000000..9b9c2d2 --- /dev/null +++ b/db/patch-monitor-tls-info-add-fk.sql @@ -0,0 +1,18 @@ +BEGIN TRANSACTION; + +PRAGMA writable_schema = TRUE; + +UPDATE + SQLITE_MASTER +SET + sql = replace(sql, + 'monitor_id INTEGER NOT NULL', + 'monitor_id INTEGER NOT NULL REFERENCES [monitor] ([id]) ON DELETE CASCADE ON UPDATE CASCADE' +) +WHERE + name = 'monitor_tls_info' + AND type = 'table'; + +PRAGMA writable_schema = RESET; + +COMMIT; -- cgit v1.2.3