wip

1 files changed, 56 insertions, 0 deletions

diff --git a/3.sql b/3.sql
new file mode 100644
index 0000000..e49391e
--- /dev/null
+++ b/3.sql
@@ -0,0 +1,56 @@
+pragma foreign_keys = true;
+
+create table host(
+    id int not null primary key,
+    name text not null unique
+);
+
+insert into host(id, name) values
+(1, 'knot'),
+(2, 'lhn2ix'),
+(3, 'conflatorio'),
+(4, 'hash');
+
+select * from host;
+
+create table router_link(
+  router text references host(name) not null,
+  remote text references host(name) not null,
+  addr text,
+  primary key(router, remote)
+);
+
+create table network(
+    router text references host(name),
+    addr text,
+    range int);
+insert into router_link(router, remote, addr) VALUES
+  ("knot", "lhn2ix", "1:1::1"),
+  ("lhn2ix", "knot", "1:2::1"),
+  ("lhn2ix", "hash", "1:2::2"),
+  ("hash", "lhn2ix", "1:3::1");
+
+insert into network(router, addr, range) VALUES
+  ("knot", "1::f11b::", 64),
+  ("lhn2ix", "1::dbea::", 64),
+  ("hash", "1::e5b0::", 64);
+
+#create table bgp_connection(router, remote, addr);
+#insert into bgp_connection
+create view bgp_connection as
+select 
+  router.router as router,
+  remote.router as remote,
+  remote.addr as remote_addr
+from router_link router
+inner join router_link remote on router.remote = remote.router;
+
+.mode column
+.print
+.print HOST
+select * from host;
+
+.print
+.print BGP CONNECTION
+select * from bgp_connection
+order by router, remote;