sql_psql.tmpl

create table metadata (
    "uuid" varchar(255),
    "type" varchar(255),
    "fq_name" varchar(255),
    unique("type", "fq_name"),
    primary key ("uuid"));

create index fq_name_index on metadata ("fq_name");

create table int_pool (
    "key" varchar(255),
    "start" int,
    "end" int
);

alter table int_pool replica identity full;

insert into int_pool ("key", "start", "end") values ('virtual_network_id', 5, 65543);
insert into int_pool ("key", "start", "end") values ('security_group_id', 1, 65543);

create table ipaddress_pool (
    "key" varchar(255),
    "start" inet,
    "end" inet
);

alter table ipaddress_pool replica identity full;

create table kv_store (
	"key" varchar(255),
	"value" varchar(255),
	primary key ("key")
);

{% for schema in schemas %}{% if schema.Type != "abstract" and schema.ID %}
create table "{{ schema.ID | lower }}" (
    {% for column in schema.Columns %}"{{column.Column}}" {{column.JSONSchema.SQL}},
    {% endfor %} primary key("uuid"));

create index {{ schema.ID | lower }}_parent_uuid_index on "{{ schema.ID | lower }}" ("parent_uuid");
{% endif %}
{% endfor %}

{% for schema in schemas %}{% if schema.Type != "abstract" and schema.ID %}
{% for linkTo, reference in schema.References %}
create table {{ reference.Table }} (
    "from" varchar(255),
    "to" varchar(255),
    primary key ("from","to"),
    {% for column in reference.Columns %}"{{column.Column}}" {{column.JSONSchema.SQL}},
    {% endfor %} foreign key ("from") references "{{ schema.ID | lower }}"(uuid) on delete cascade,
    foreign key ("to") references "{{ linkTo | lower }}"(uuid));

create index index_{{ reference.Table }} on {{ reference.Table }} ("from");
{% endfor %}

{% for linkTo, reference in schema.Parents %}
create table {{ reference.Table }} (
    "from" varchar(255),
    "to" varchar(255),
    primary key ("from","to"),
    foreign key ("from") references "{{ schema.ID | lower }}"(uuid) on delete cascade,
    foreign key ("to") references "{{ linkTo | lower }}"(uuid));

create index index_{{ reference.Table }} on {{ reference.Table }} ("from");
{% endfor%}

create table tenant_share_{{ schema.ID | lower }} (
    "uuid" varchar(255),
    "to" varchar(255),
    primary key ("uuid","to"),
    "access" integer,
    foreign key ("uuid") references "{{ schema.ID | lower }}"(uuid) on delete cascade,
    foreign key ("to") references project(uuid) on delete cascade);

create index index_t_{{ schema.ID | lower }}_id on tenant_share_{{ schema.ID | lower }}("uuid");
create index index_t_{{ schema.ID | lower }}_to on tenant_share_{{ schema.ID | lower }}("to");

create table domain_share_{{ schema.ID | lower }} (
    "uuid" varchar(255),
    "to" varchar(255),
    primary key ("uuid","to"),
    "access" integer,
    foreign key ("uuid") references "{{ schema.ID | lower }}"(uuid) on delete cascade,
    foreign key ("to") references domain(uuid) on delete cascade);

create index index_d_{{ schema.ID | lower }}_id on domain_share_{{ schema.ID | lower }}("uuid");
create index index_d_{{ schema.ID | lower }}_to on domain_share_{{ schema.ID | lower }}("to");

{% endif %}
{% endfor %}