Something went wrong. Try again.
a tool for shared writing and social publishing
Something went wrong. Try again.
6.3 kB · 200 lines
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201create table "public"."entities" ( "id" uuid not null default gen_random_uuid(), "created_at" timestamp with time zone not null default now());
alter table "public"."entities" enable row level security;
create table "public"."facts" ( "id" uuid not null default gen_random_uuid(), "entity" uuid not null, "attribute" text not null, "data" jsonb not null, "created_at" timestamp without time zone not null default now(), "updated_at" timestamp without time zone, "version" bigint not null default '0'::bigint);
alter table "public"."facts" enable row level security;
create table "public"."replicache_clients" ( "client_id" text not null, "client_group" text not null, "last_mutation" bigint not null);
alter table "public"."replicache_clients" enable row level security;
CREATE UNIQUE INDEX client_pkey ON public.replicache_clients USING btree (client_id);
CREATE UNIQUE INDEX entities_pkey ON public.entities USING btree (id);
CREATE INDEX facts_reference_idx ON public.facts USING btree (((data ->> 'value'::text))) WHERE (((data ->> 'type'::text) = 'reference'::text) OR ((data ->> 'type'::text) = 'ordered-reference'::text));
CREATE UNIQUE INDEX facts_pkey ON public.facts USING btree (id);
alter table "public"."entities" add constraint "entities_pkey" PRIMARY KEY using index "entities_pkey";
alter table "public"."facts" add constraint "facts_pkey" PRIMARY KEY using index "facts_pkey";
alter table "public"."replicache_clients" add constraint "client_pkey" PRIMARY KEY using index "client_pkey";
alter table "public"."facts" add constraint "facts_entity_fkey" FOREIGN KEY (entity) REFERENCES entities(id) ON UPDATE RESTRICT ON DELETE CASCADE not valid;
alter table "public"."facts" validate constraint "facts_entity_fkey";
set check_function_bodies = off;
CREATE OR REPLACE FUNCTION public.get_facts(root uuid) RETURNS SETOF facts LANGUAGE sqlAS $function$WITH RECURSIVE all_facts as ( select * from facts where entity = root union select f.* from facts f inner join all_facts f1 on ( uuid(f1.data ->> 'value') = f.entity ) where f1.data ->> 'type' = 'reference' or f1.data ->> 'type' = 'ordered-reference' )select *from all_facts;$function$;
grant delete on table "public"."entities" to "anon";
grant insert on table "public"."entities" to "anon";
grant references on table "public"."entities" to "anon";
grant select on table "public"."entities" to "anon";
grant trigger on table "public"."entities" to "anon";
grant truncate on table "public"."entities" to "anon";
grant update on table "public"."entities" to "anon";
grant delete on table "public"."entities" to "authenticated";
grant insert on table "public"."entities" to "authenticated";
grant references on table "public"."entities" to "authenticated";
grant select on table "public"."entities" to "authenticated";
grant trigger on table "public"."entities" to "authenticated";
grant truncate on table "public"."entities" to "authenticated";
grant update on table "public"."entities" to "authenticated";
grant delete on table "public"."entities" to "service_role";
grant insert on table "public"."entities" to "service_role";
grant references on table "public"."entities" to "service_role";
grant select on table "public"."entities" to "service_role";
grant trigger on table "public"."entities" to "service_role";
grant truncate on table "public"."entities" to "service_role";
grant update on table "public"."entities" to "service_role";
grant delete on table "public"."facts" to "anon";
grant insert on table "public"."facts" to "anon";
grant references on table "public"."facts" to "anon";
grant select on table "public"."facts" to "anon";
grant trigger on table "public"."facts" to "anon";
grant truncate on table "public"."facts" to "anon";
grant update on table "public"."facts" to "anon";
grant delete on table "public"."facts" to "authenticated";
grant insert on table "public"."facts" to "authenticated";
grant references on table "public"."facts" to "authenticated";
grant select on table "public"."facts" to "authenticated";
grant trigger on table "public"."facts" to "authenticated";
grant truncate on table "public"."facts" to "authenticated";
grant update on table "public"."facts" to "authenticated";
grant delete on table "public"."facts" to "service_role";
grant insert on table "public"."facts" to "service_role";
grant references on table "public"."facts" to "service_role";
grant select on table "public"."facts" to "service_role";
grant trigger on table "public"."facts" to "service_role";
grant truncate on table "public"."facts" to "service_role";
grant update on table "public"."facts" to "service_role";
grant delete on table "public"."replicache_clients" to "anon";
grant insert on table "public"."replicache_clients" to "anon";
grant references on table "public"."replicache_clients" to "anon";
grant select on table "public"."replicache_clients" to "anon";
grant trigger on table "public"."replicache_clients" to "anon";
grant truncate on table "public"."replicache_clients" to "anon";
grant update on table "public"."replicache_clients" to "anon";
grant delete on table "public"."replicache_clients" to "authenticated";
grant insert on table "public"."replicache_clients" to "authenticated";
grant references on table "public"."replicache_clients" to "authenticated";
grant select on table "public"."replicache_clients" to "authenticated";
grant trigger on table "public"."replicache_clients" to "authenticated";
grant truncate on table "public"."replicache_clients" to "authenticated";
grant update on table "public"."replicache_clients" to "authenticated";
grant delete on table "public"."replicache_clients" to "service_role";
grant insert on table "public"."replicache_clients" to "service_role";
grant references on table "public"."replicache_clients" to "service_role";
grant select on table "public"."replicache_clients" to "service_role";
grant trigger on table "public"."replicache_clients" to "service_role";
grant truncate on table "public"."replicache_clients" to "service_role";
grant update on table "public"."replicache_clients" to "service_role";