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;