bgpwtf/cccampix: draw the rest of the fucking owl
Change-Id: I49fd5906e69512e8f2d414f406edc0179522f225
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1564664855_initial.down.sql b/bgpwtf/cccampix/verifier/model/migrations/1564664855_initial.down.sql
new file mode 100644
index 0000000..d0ba745
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1564664855_initial.down.sql
@@ -0,0 +1,2 @@
+DROP TABLE peers;
+DROP TABLE peer_routers;
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1564664855_initial.up.sql b/bgpwtf/cccampix/verifier/model/migrations/1564664855_initial.up.sql
new file mode 100644
index 0000000..bab0c35
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1564664855_initial.up.sql
@@ -0,0 +1,27 @@
+CREATE TABLE peers (
+ id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+ asn STRING NOT NULL,
+ name STRING NOT NULL,
+ source STRING check (
+ source = 'from-peeringdb' or
+ source = 'manual'
+ ) NOT NULL,
+ UNIQUE (asn)
+);
+
+CREATE TABLE peer_routers (
+ peer_id UUID NOT NULL,
+ id UUID DEFAULT gen_random_uuid(),
+
+ v6 STRING,
+ v4 STRING,
+ source STRING check (
+ source = 'from-peeringdb' or
+ source = 'manual'
+ ) NOT NULL,
+
+ UNIQUE (v4),
+ UNIQUE (v6),
+ PRIMARY KEY (peer_id, id),
+ CONSTRAINT fk_peer FOREIGN KEY (peer_id) REFERENCES peers (id) ON DELETE CASCADE
+) INTERLEAVE IN PARENT peers (peer_id);
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1564751360_membership.down.sql b/bgpwtf/cccampix/verifier/model/migrations/1564751360_membership.down.sql
new file mode 100644
index 0000000..c47fec6
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1564751360_membership.down.sql
@@ -0,0 +1 @@
+DROP TABLE peer_checks;
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1564751360_membership.up.sql b/bgpwtf/cccampix/verifier/model/migrations/1564751360_membership.up.sql
new file mode 100644
index 0000000..247c9eb
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1564751360_membership.up.sql
@@ -0,0 +1,18 @@
+CREATE TABLE peer_checks (
+ peer_id UUID NOT NULL,
+ id UUID DEFAULT gen_random_uuid(),
+
+ check_name STRING NOT NULL,
+ check_time INT NOT NULL,
+ check_status STRING check (
+ check_status = 'unknown' or
+ check_status = 'okay' or
+ check_status = 'failed'
+ ) NOT NULL,
+ check_message STRING NOT NULL,
+ delete BOOL NOT NULL,
+
+ UNIQUE(peer_id, check_name),
+ PRIMARY KEY (peer_id, id),
+ CONSTRAINT fk_peer FOREIGN KEY (peer_id) REFERENCES peers (id) ON DELETE CASCADE
+) INTERLEAVE IN PARENT peers (peer_id);
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1564867563_pgp.down.sql b/bgpwtf/cccampix/verifier/model/migrations/1564867563_pgp.down.sql
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1564867563_pgp.down.sql
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1564867563_pgp.up.sql b/bgpwtf/cccampix/verifier/model/migrations/1564867563_pgp.up.sql
new file mode 100644
index 0000000..6c6f752
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1564867563_pgp.up.sql
@@ -0,0 +1,11 @@
+CREATE TABLE peer_pgp_keys (
+ peer_id UUID NOT NULL,
+ id UUID DEFAULT gen_random_uuid(),
+
+ fingerprint STRING NOT NULL,
+ time_created INT NOT NULL,
+
+ UNIQUE (peer_id),
+ PRIMARY KEY (peer_id, id),
+ CONSTRAINT fk_peer FOREIGN KEY (peer_id) REFERENCES peers (id) ON DELETE CASCADE
+) INTERLEAVE IN PARENT peers (peer_id);
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1565471293_session_configs.down.sql b/bgpwtf/cccampix/verifier/model/migrations/1565471293_session_configs.down.sql
new file mode 100644
index 0000000..f286d79
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1565471293_session_configs.down.sql
@@ -0,0 +1 @@
+DROP TABLE session_configs;
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1565471293_session_configs.up.sql b/bgpwtf/cccampix/verifier/model/migrations/1565471293_session_configs.up.sql
new file mode 100644
index 0000000..7a1d07b
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1565471293_session_configs.up.sql
@@ -0,0 +1,11 @@
+CREATE TABLE session_configs (
+ peer_id UUID NOT NULL,
+ peer_router_id UUID NOT NULL,
+ id UUID DEFAULT gen_random_uuid(),
+
+ bgp_secret STRING NOT NULL,
+
+ UNIQUE (peer_router_id),
+ PRIMARY KEY (peer_id, peer_router_id, id),
+ CONSTRAINT fk_peer_router FOREIGN KEY (peer_id, peer_router_id) REFERENCES peer_routers ON DELETE CASCADE
+) INTERLEAVE IN PARENT peer_routers (peer_id, peer_router_id);
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1565545845_allowed_prefixes.down.sql b/bgpwtf/cccampix/verifier/model/migrations/1565545845_allowed_prefixes.down.sql
new file mode 100644
index 0000000..d0a98f3
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1565545845_allowed_prefixes.down.sql
@@ -0,0 +1 @@
+DROP TABLE allowed_prefix;
diff --git a/bgpwtf/cccampix/verifier/model/migrations/1565545845_allowed_prefixes.up.sql b/bgpwtf/cccampix/verifier/model/migrations/1565545845_allowed_prefixes.up.sql
new file mode 100644
index 0000000..c033e3d
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/1565545845_allowed_prefixes.up.sql
@@ -0,0 +1,13 @@
+CREATE TABLE allowed_prefixes (
+ peer_id UUID NOT NULL,
+ id UUID DEFAULT gen_random_uuid(),
+ timestamp INT NOT NULL,
+
+ prefix STRING NOT NULL,
+ max_length INT NOT NULL,
+ ta STRING NOT NULL,
+
+ PRIMARY KEY (peer_id, id),
+ UNIQUE (peer_id, prefix),
+ CONSTRAINT fk_peer FOREIGN KEY (peer_id) REFERENCES peers (id) ON DELETE CASCADE
+) INTERLEAVE IN PARENT peers (peer_id);
diff --git a/bgpwtf/cccampix/verifier/model/migrations/BUILD.bazel b/bgpwtf/cccampix/verifier/model/migrations/BUILD.bazel
new file mode 100644
index 0000000..b20d35f
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/BUILD.bazel
@@ -0,0 +1,23 @@
+load("@io_bazel_rules_go//go:def.bzl", "go_library")
+load("@io_bazel_rules_go//extras:embed_data.bzl", "go_embed_data")
+
+go_embed_data(
+ name = "migrations_data",
+ srcs = glob(["*.sql"]),
+ package = "migrations",
+ flatten = True,
+)
+
+go_library(
+ name = "go_default_library",
+ srcs = [
+ "migrations.go",
+ ":migrations_data", # keep
+ ],
+ importpath = "code.hackerspace.pl/hscloud/bgpwtf/cccampix/verifier/model/migrations",
+ visibility = ["//bgpwtf/cccampix/verifier/model:__subpackages__"],
+ deps = [
+ "//go/mirko:go_default_library",
+ "@com_github_golang_migrate_migrate_v4//:go_default_library",
+ ],
+)
diff --git a/bgpwtf/cccampix/verifier/model/migrations/migrations.go b/bgpwtf/cccampix/verifier/model/migrations/migrations.go
new file mode 100644
index 0000000..1782c2e
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/migrations/migrations.go
@@ -0,0 +1,17 @@
+package migrations
+
+import (
+ "fmt"
+
+ "code.hackerspace.pl/hscloud/go/mirko"
+
+ "github.com/golang-migrate/migrate/v4"
+)
+
+func New(dburl string) (*migrate.Migrate, error) {
+ source, err := mirko.NewMigrationsFromBazel(Data)
+ if err != nil {
+ return nil, fmt.Errorf("could not create migrations: %v", err)
+ }
+ return migrate.NewWithSourceInstance("bazel", source, dburl)
+}