From deef0f8baabe0218c21d7b9f2d95890e8987642a Mon Sep 17 00:00:00 2001 From: "kilian (dks-laptop)" Date: Wed, 9 Dec 2020 11:24:16 +0100 Subject: [PATCH] v20201209 --- dev/db/live/potlu2_db.etude_dbe.pg.full.sql | 10 +- dev/db/live/potlu2_db.etude_dbe.pg.schema.sql | 10 +- dev/db/live/potlu2_db.public.pg.full.sql | 142 ++++++------ dev/db/live/potlu2_db.public.pg.schema.sql | 142 ++++++------ dev/db/potlu2_db.live.pg.full.sql | 202 +++++++++--------- dev/db/potlu2_db.live.pg.schema.sql | 202 +++++++++--------- dev/dump_fulldb.ps1 | 50 ++--- 7 files changed, 379 insertions(+), 379 deletions(-) diff --git a/dev/db/live/potlu2_db.etude_dbe.pg.full.sql b/dev/db/live/potlu2_db.etude_dbe.pg.full.sql index a6e54337..f7ad87cd 100644 --- a/dev/db/live/potlu2_db.etude_dbe.pg.full.sql +++ b/dev/db/live/potlu2_db.etude_dbe.pg.full.sql @@ -29,11 +29,11 @@ CREATE SCHEMA etude_dbe; CREATE FUNCTION etude_dbe.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; diff --git a/dev/db/live/potlu2_db.etude_dbe.pg.schema.sql b/dev/db/live/potlu2_db.etude_dbe.pg.schema.sql index 8334019d..14fd0ff3 100644 --- a/dev/db/live/potlu2_db.etude_dbe.pg.schema.sql +++ b/dev/db/live/potlu2_db.etude_dbe.pg.schema.sql @@ -29,11 +29,11 @@ CREATE SCHEMA etude_dbe; CREATE FUNCTION etude_dbe.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; diff --git a/dev/db/live/potlu2_db.public.pg.full.sql b/dev/db/live/potlu2_db.public.pg.full.sql index bc2aed71..64ee77e5 100644 --- a/dev/db/live/potlu2_db.public.pg.full.sql +++ b/dev/db/live/potlu2_db.public.pg.full.sql @@ -29,13 +29,13 @@ CREATE SCHEMA public; CREATE FUNCTION public.add_schemalink(vdatatsetname text, vschemaname text) RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; -begin - INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; - return newuuid; -end; + AS $$ +declare + newuuid text; +begin + INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; + return newuuid; +end; $$; @@ -71,17 +71,17 @@ $$; CREATE FUNCTION public.day_name(day_index text) RETURNS text LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE - AS $$ - SELECT CASE day_index - WHEN '0' THEN 'di' - WHEN '1' THEN 'lu' - WHEN '2' THEN 'ma' - WHEN '3' THEN 'me' - WHEN '4' THEN 'je' - WHEN '5' THEN 've' - WHEN '6' THEN 'sa' - WHEN '7' THEN 'di' - END; + AS $$ + SELECT CASE day_index + WHEN '0' THEN 'di' + WHEN '1' THEN 'lu' + WHEN '2' THEN 'ma' + WHEN '3' THEN 'me' + WHEN '4' THEN 'je' + WHEN '5' THEN 've' + WHEN '6' THEN 'sa' + WHEN '7' THEN 'di' + END; $$; @@ -91,13 +91,13 @@ $$; CREATE FUNCTION public.del_user(vuserid text) RETURNS boolean LANGUAGE plpgsql - AS $$ -declare - -begin - delete from public.users where id = vuserid; - return true; -end; + AS $$ +declare + +begin + delete from public.users where id = vuserid; + return true; +end; $$; @@ -107,14 +107,14 @@ $$; CREATE FUNCTION public.getsession(vidsession text, vremoteaddr text, vuseragent text) RETURNS TABLE(idsession text, id text, username text, usergroup text, sessiondata json) LANGUAGE plpgsql - AS $$ - BEGIN - return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se -join users us on (us.id=se.id_user) -left join usergroups ugrp on (ugrp.id=us.id_usergroup) -where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and -us.blocked is null group by se.id,us.id,ugrp.id; - END; + AS $$ + BEGIN + return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se +join users us on (us.id=se.id_user) +left join usergroups ugrp on (ugrp.id=us.id_usergroup) +where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and +us.blocked is null group by se.id,us.id,ugrp.id; + END; $$; @@ -124,13 +124,13 @@ $$; CREATE FUNCTION public.getuuid() RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; - begin - SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; - return newuuid; -END; + AS $$ +declare + newuuid text; + begin + SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; + return newuuid; +END; $$; @@ -140,14 +140,14 @@ $$; CREATE FUNCTION public.random_string(vlength integer) RETURNS text LANGUAGE plpgsql - AS $$ -declare - randstr text := null; -BEGIN -SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr -FROM generate_series(1, vlength); -return randstr; -end; + AS $$ +declare + randstr text := null; +BEGIN +SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr +FROM generate_series(1, vlength); +return randstr; +end; $$; @@ -157,16 +157,16 @@ $$; CREATE FUNCTION public.setjsonkeyvalue(vschemata text, vtable text, vcol text, vid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ -declare - sqlxx text; - res json; - begin - sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; - --raise notice 'SQL: %',sqlxx; - execute sqlxx into res; - return res; -END; + AS $$ +declare + sqlxx text; + res json; + begin + sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; + --raise notice 'SQL: %',sqlxx; + execute sqlxx into res; + return res; +END; $$; @@ -176,13 +176,13 @@ $$; CREATE FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ - declare - res json; - begin - select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; - return res; - end; + AS $$ + declare + res json; + begin + select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; + return res; + end; $$; @@ -192,12 +192,12 @@ CREATE FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) RETURNS CREATE FUNCTION public.trg_update_modified() RETURNS trigger LANGUAGE plpgsql - AS $$ - BEGIN - new.modified = Current_timestamp; - - RETURN NEW; - END; + AS $$ + BEGIN + new.modified = Current_timestamp; + + RETURN NEW; + END; $$; diff --git a/dev/db/live/potlu2_db.public.pg.schema.sql b/dev/db/live/potlu2_db.public.pg.schema.sql index 5d4cc932..99e90916 100644 --- a/dev/db/live/potlu2_db.public.pg.schema.sql +++ b/dev/db/live/potlu2_db.public.pg.schema.sql @@ -29,13 +29,13 @@ CREATE SCHEMA public; CREATE FUNCTION public.add_schemalink(vdatatsetname text, vschemaname text) RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; -begin - INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; - return newuuid; -end; + AS $$ +declare + newuuid text; +begin + INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; + return newuuid; +end; $$; @@ -71,17 +71,17 @@ $$; CREATE FUNCTION public.day_name(day_index text) RETURNS text LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE - AS $$ - SELECT CASE day_index - WHEN '0' THEN 'di' - WHEN '1' THEN 'lu' - WHEN '2' THEN 'ma' - WHEN '3' THEN 'me' - WHEN '4' THEN 'je' - WHEN '5' THEN 've' - WHEN '6' THEN 'sa' - WHEN '7' THEN 'di' - END; + AS $$ + SELECT CASE day_index + WHEN '0' THEN 'di' + WHEN '1' THEN 'lu' + WHEN '2' THEN 'ma' + WHEN '3' THEN 'me' + WHEN '4' THEN 'je' + WHEN '5' THEN 've' + WHEN '6' THEN 'sa' + WHEN '7' THEN 'di' + END; $$; @@ -91,13 +91,13 @@ $$; CREATE FUNCTION public.del_user(vuserid text) RETURNS boolean LANGUAGE plpgsql - AS $$ -declare - -begin - delete from public.users where id = vuserid; - return true; -end; + AS $$ +declare + +begin + delete from public.users where id = vuserid; + return true; +end; $$; @@ -107,14 +107,14 @@ $$; CREATE FUNCTION public.getsession(vidsession text, vremoteaddr text, vuseragent text) RETURNS TABLE(idsession text, id text, username text, usergroup text, sessiondata json) LANGUAGE plpgsql - AS $$ - BEGIN - return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se -join users us on (us.id=se.id_user) -left join usergroups ugrp on (ugrp.id=us.id_usergroup) -where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and -us.blocked is null group by se.id,us.id,ugrp.id; - END; + AS $$ + BEGIN + return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se +join users us on (us.id=se.id_user) +left join usergroups ugrp on (ugrp.id=us.id_usergroup) +where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and +us.blocked is null group by se.id,us.id,ugrp.id; + END; $$; @@ -124,13 +124,13 @@ $$; CREATE FUNCTION public.getuuid() RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; - begin - SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; - return newuuid; -END; + AS $$ +declare + newuuid text; + begin + SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; + return newuuid; +END; $$; @@ -140,14 +140,14 @@ $$; CREATE FUNCTION public.random_string(vlength integer) RETURNS text LANGUAGE plpgsql - AS $$ -declare - randstr text := null; -BEGIN -SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr -FROM generate_series(1, vlength); -return randstr; -end; + AS $$ +declare + randstr text := null; +BEGIN +SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr +FROM generate_series(1, vlength); +return randstr; +end; $$; @@ -157,16 +157,16 @@ $$; CREATE FUNCTION public.setjsonkeyvalue(vschemata text, vtable text, vcol text, vid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ -declare - sqlxx text; - res json; - begin - sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; - --raise notice 'SQL: %',sqlxx; - execute sqlxx into res; - return res; -END; + AS $$ +declare + sqlxx text; + res json; + begin + sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; + --raise notice 'SQL: %',sqlxx; + execute sqlxx into res; + return res; +END; $$; @@ -176,13 +176,13 @@ $$; CREATE FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ - declare - res json; - begin - select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; - return res; - end; + AS $$ + declare + res json; + begin + select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; + return res; + end; $$; @@ -192,12 +192,12 @@ CREATE FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) RETURNS CREATE FUNCTION public.trg_update_modified() RETURNS trigger LANGUAGE plpgsql - AS $$ - BEGIN - new.modified = Current_timestamp; - - RETURN NEW; - END; + AS $$ + BEGIN + new.modified = Current_timestamp; + + RETURN NEW; + END; $$; diff --git a/dev/db/potlu2_db.live.pg.full.sql b/dev/db/potlu2_db.live.pg.full.sql index 3832a3c6..82fffffb 100644 --- a/dev/db/potlu2_db.live.pg.full.sql +++ b/dev/db/potlu2_db.live.pg.full.sql @@ -94,11 +94,11 @@ ALTER SCHEMA portanova OWNER TO potlu2_user; CREATE FUNCTION barto.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -110,11 +110,11 @@ ALTER FUNCTION barto.trg_before_upd_schematable() OWNER TO potlu2_user; CREATE FUNCTION brasserie_du_theatre.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -158,11 +158,11 @@ ALTER FUNCTION demo50.trg_before_upd_schematable() OWNER TO potlu2_user; CREATE FUNCTION demoold.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -174,11 +174,11 @@ ALTER FUNCTION demoold.trg_before_upd_schematable() OWNER TO potlu2_user; CREATE FUNCTION elch.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -190,11 +190,11 @@ ALTER FUNCTION elch.trg_before_upd_schematable() OWNER TO potlu2_user; CREATE FUNCTION etude_dbe.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -206,11 +206,11 @@ ALTER FUNCTION etude_dbe.trg_before_upd_schematable() OWNER TO potlu2_user; CREATE FUNCTION portanova.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -222,13 +222,13 @@ ALTER FUNCTION portanova.trg_before_upd_schematable() OWNER TO potlu2_user; CREATE FUNCTION public.add_schemalink(vdatatsetname text, vschemaname text) RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; -begin - INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; - return newuuid; -end; + AS $$ +declare + newuuid text; +begin + INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; + return newuuid; +end; $$; @@ -268,17 +268,17 @@ ALTER FUNCTION public.checklogin(vusername text, vpassword text, vremoteaddr tex CREATE FUNCTION public.day_name(day_index text) RETURNS text LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE - AS $$ - SELECT CASE day_index - WHEN '0' THEN 'di' - WHEN '1' THEN 'lu' - WHEN '2' THEN 'ma' - WHEN '3' THEN 'me' - WHEN '4' THEN 'je' - WHEN '5' THEN 've' - WHEN '6' THEN 'sa' - WHEN '7' THEN 'di' - END; + AS $$ + SELECT CASE day_index + WHEN '0' THEN 'di' + WHEN '1' THEN 'lu' + WHEN '2' THEN 'ma' + WHEN '3' THEN 'me' + WHEN '4' THEN 'je' + WHEN '5' THEN 've' + WHEN '6' THEN 'sa' + WHEN '7' THEN 'di' + END; $$; @@ -290,13 +290,13 @@ ALTER FUNCTION public.day_name(day_index text) OWNER TO potlu2_user; CREATE FUNCTION public.del_user(vuserid text) RETURNS boolean LANGUAGE plpgsql - AS $$ -declare - -begin - delete from public.users where id = vuserid; - return true; -end; + AS $$ +declare + +begin + delete from public.users where id = vuserid; + return true; +end; $$; @@ -308,14 +308,14 @@ ALTER FUNCTION public.del_user(vuserid text) OWNER TO potlu2_user; CREATE FUNCTION public.getsession(vidsession text, vremoteaddr text, vuseragent text) RETURNS TABLE(idsession text, id text, username text, usergroup text, sessiondata json) LANGUAGE plpgsql - AS $$ - BEGIN - return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se -join users us on (us.id=se.id_user) -left join usergroups ugrp on (ugrp.id=us.id_usergroup) -where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and -us.blocked is null group by se.id,us.id,ugrp.id; - END; + AS $$ + BEGIN + return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se +join users us on (us.id=se.id_user) +left join usergroups ugrp on (ugrp.id=us.id_usergroup) +where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and +us.blocked is null group by se.id,us.id,ugrp.id; + END; $$; @@ -327,13 +327,13 @@ ALTER FUNCTION public.getsession(vidsession text, vremoteaddr text, vuseragent t CREATE FUNCTION public.getuuid() RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; - begin - SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; - return newuuid; -END; + AS $$ +declare + newuuid text; + begin + SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; + return newuuid; +END; $$; @@ -345,14 +345,14 @@ ALTER FUNCTION public.getuuid() OWNER TO potlu2_user; CREATE FUNCTION public.random_string(vlength integer) RETURNS text LANGUAGE plpgsql - AS $$ -declare - randstr text := null; -BEGIN -SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr -FROM generate_series(1, vlength); -return randstr; -end; + AS $$ +declare + randstr text := null; +BEGIN +SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr +FROM generate_series(1, vlength); +return randstr; +end; $$; @@ -364,16 +364,16 @@ ALTER FUNCTION public.random_string(vlength integer) OWNER TO potlu2_user; CREATE FUNCTION public.setjsonkeyvalue(vschemata text, vtable text, vcol text, vid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ -declare - sqlxx text; - res json; - begin - sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; - --raise notice 'SQL: %',sqlxx; - execute sqlxx into res; - return res; -END; + AS $$ +declare + sqlxx text; + res json; + begin + sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; + --raise notice 'SQL: %',sqlxx; + execute sqlxx into res; + return res; +END; $$; @@ -385,13 +385,13 @@ ALTER FUNCTION public.setjsonkeyvalue(vschemata text, vtable text, vcol text, vi CREATE FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ - declare - res json; - begin - select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; - return res; - end; + AS $$ + declare + res json; + begin + select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; + return res; + end; $$; @@ -403,12 +403,12 @@ ALTER FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) OWNER TO CREATE FUNCTION public.trg_update_modified() RETURNS trigger LANGUAGE plpgsql - AS $$ - BEGIN - new.modified = Current_timestamp; - - RETURN NEW; - END; + AS $$ + BEGIN + new.modified = Current_timestamp; + + RETURN NEW; + END; $$; diff --git a/dev/db/potlu2_db.live.pg.schema.sql b/dev/db/potlu2_db.live.pg.schema.sql index 6563b498..43237967 100644 --- a/dev/db/potlu2_db.live.pg.schema.sql +++ b/dev/db/potlu2_db.live.pg.schema.sql @@ -78,11 +78,11 @@ CREATE SCHEMA portanova; CREATE FUNCTION barto.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -92,11 +92,11 @@ $$; CREATE FUNCTION brasserie_du_theatre.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -134,11 +134,11 @@ $$; CREATE FUNCTION demoold.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -148,11 +148,11 @@ $$; CREATE FUNCTION elch.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -162,11 +162,11 @@ $$; CREATE FUNCTION etude_dbe.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -176,11 +176,11 @@ $$; CREATE FUNCTION portanova.trg_before_upd_schematable() RETURNS trigger LANGUAGE plpgsql - AS $$ - begin - new.modified = now(); - RETURN NEW; - END; + AS $$ + begin + new.modified = now(); + RETURN NEW; + END; $$; @@ -190,13 +190,13 @@ $$; CREATE FUNCTION public.add_schemalink(vdatatsetname text, vschemaname text) RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; -begin - INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; - return newuuid; -end; + AS $$ +declare + newuuid text; +begin + INSERT INTO public.companies (datasetname,schemata) values (vdatatsetname,vschemaname) returning id into newuuid; + return newuuid; +end; $$; @@ -232,17 +232,17 @@ $$; CREATE FUNCTION public.day_name(day_index text) RETURNS text LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE - AS $$ - SELECT CASE day_index - WHEN '0' THEN 'di' - WHEN '1' THEN 'lu' - WHEN '2' THEN 'ma' - WHEN '3' THEN 'me' - WHEN '4' THEN 'je' - WHEN '5' THEN 've' - WHEN '6' THEN 'sa' - WHEN '7' THEN 'di' - END; + AS $$ + SELECT CASE day_index + WHEN '0' THEN 'di' + WHEN '1' THEN 'lu' + WHEN '2' THEN 'ma' + WHEN '3' THEN 'me' + WHEN '4' THEN 'je' + WHEN '5' THEN 've' + WHEN '6' THEN 'sa' + WHEN '7' THEN 'di' + END; $$; @@ -252,13 +252,13 @@ $$; CREATE FUNCTION public.del_user(vuserid text) RETURNS boolean LANGUAGE plpgsql - AS $$ -declare - -begin - delete from public.users where id = vuserid; - return true; -end; + AS $$ +declare + +begin + delete from public.users where id = vuserid; + return true; +end; $$; @@ -268,14 +268,14 @@ $$; CREATE FUNCTION public.getsession(vidsession text, vremoteaddr text, vuseragent text) RETURNS TABLE(idsession text, id text, username text, usergroup text, sessiondata json) LANGUAGE plpgsql - AS $$ - BEGIN - return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se -join users us on (us.id=se.id_user) -left join usergroups ugrp on (ugrp.id=us.id_usergroup) -where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and -us.blocked is null group by se.id,us.id,ugrp.id; - END; + AS $$ + BEGIN + return QUERY select se.id as idsession,us.id,us.username,ugrp.id as usergroup,se.sessiondata from sessions se +join users us on (us.id=se.id_user) +left join usergroups ugrp on (ugrp.id=us.id_usergroup) +where se.id= vidsession and se.remote_addr= vremoteaddr and se.user_agent= vuseragent and +us.blocked is null group by se.id,us.id,ugrp.id; + END; $$; @@ -285,13 +285,13 @@ $$; CREATE FUNCTION public.getuuid() RETURNS text LANGUAGE plpgsql - AS $$ -declare - newuuid text; - begin - SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; - return newuuid; -END; + AS $$ +declare + newuuid text; + begin + SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring) into newuuid; + return newuuid; +END; $$; @@ -301,14 +301,14 @@ $$; CREATE FUNCTION public.random_string(vlength integer) RETURNS text LANGUAGE plpgsql - AS $$ -declare - randstr text := null; -BEGIN -SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr -FROM generate_series(1, vlength); -return randstr; -end; + AS $$ +declare + randstr text := null; +BEGIN +SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') into randstr +FROM generate_series(1, vlength); +return randstr; +end; $$; @@ -318,16 +318,16 @@ $$; CREATE FUNCTION public.setjsonkeyvalue(vschemata text, vtable text, vcol text, vid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ -declare - sqlxx text; - res json; - begin - sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; - --raise notice 'SQL: %',sqlxx; - execute sqlxx into res; - return res; -END; + AS $$ +declare + sqlxx text; + res json; + begin + sqlxx := 'UPDATE ' || vschemata || '.' || vtable || ' SET ' || vcol || '=' || 'jsonb_set('|| vcol || '::jsonb,''{' || vkey || '}'',''"' || vvalue || '"'',true) WHERE id= ''' || vid || ''' returning ' || vcol || ';'; + --raise notice 'SQL: %',sqlxx; + execute sqlxx into res; + return res; +END; $$; @@ -337,13 +337,13 @@ $$; CREATE FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) RETURNS json LANGUAGE plpgsql - AS $$ - declare - res json; - begin - select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; - return res; - end; + AS $$ + declare + res json; + begin + select public.setjsonkeyvalue('public','sessions','sessiondata',vsid,vkey,vvalue) into res; + return res; + end; $$; @@ -353,12 +353,12 @@ CREATE FUNCTION public.setsessiondata(vsid text, vkey text, vvalue text) RETURNS CREATE FUNCTION public.trg_update_modified() RETURNS trigger LANGUAGE plpgsql - AS $$ - BEGIN - new.modified = Current_timestamp; - - RETURN NEW; - END; + AS $$ + BEGIN + new.modified = Current_timestamp; + + RETURN NEW; + END; $$; diff --git a/dev/dump_fulldb.ps1 b/dev/dump_fulldb.ps1 index 54337108..92c6ca41 100644 --- a/dev/dump_fulldb.ps1 +++ b/dev/dump_fulldb.ps1 @@ -1,25 +1,25 @@ -$ENVTYPE=$args[0]; -$BINDIR="C:/Tools/db/bin" -$conffile=$PSScriptRoot + "/conf.ps1" -. $conffile; -if (($ENVTYPE) -And ($ENVTYPE -eq "live")){ - $DBCURRENTHOST=$DBLIVEHOST; -} else { - $DBCURRENTHOST=$DBDEVHOST; - $ENVTYPE="dev"; -} -$OUTDIR= $PSScriptRoot + "/db/"; -$PGDUMP = $BINDIR+ "/pg_dump.exe"; -$PSQL = $BINDIR+ "/psql.exe"; - -$outfile = $OUTDIR + ${DBNAME} + "." + $ENVTYPE + '.pg.full.sql'; -$dumpargs = @("-h",$DBCURRENTHOST,"-U",$DBUSER,"-f",$outfile,"-w",$DBNAME); -Start-Process -NoNewWindow -FilePath $PGDUMP -PassThru -Wait -ArgumentList $dumpargs - -$outfile = $OUTDIR + ${DBNAME} + "." + $ENVTYPE + '.pg.schema.sql'; -$dumpargs = @("-h",$DBCURRENTHOST,"-U",$DBUSER,"-f",$outfile,"-w","-O","-x","-s",$DBNAME); -Start-Process -NoNewWindow -FilePath $PGDUMP -PassThru -Wait -ArgumentList $dumpargs - -$outfile = $OUTDIR + ${DBNAME} + "." + $ENVTYPE + '.pg.data.sql'; -$dumpargs = @("-h",$DBCURRENTHOST,"-U",$DBUSER,"-f",$outfile,"-w","-O","-x","-a",$DBNAME); -Start-Process -NoNewWindow -FilePath $PGDUMP -PassThru -Wait -ArgumentList $dumpargs +$ENVTYPE=$args[0]; +$BINDIR="C:/Tools/db/bin" +$conffile=$PSScriptRoot + "/conf.ps1" +. $conffile; +if (($ENVTYPE) -And ($ENVTYPE -eq "live")){ + $DBCURRENTHOST=$DBLIVEHOST; +} else { + $DBCURRENTHOST=$DBDEVHOST; + $ENVTYPE="dev"; +} +$OUTDIR= $PSScriptRoot + "/db/"; +$PGDUMP = $BINDIR+ "/pg_dump.exe"; +$PSQL = $BINDIR+ "/psql.exe"; + +$outfile = $OUTDIR + ${DBNAME} + "." + $ENVTYPE + '.pg.full.sql'; +$dumpargs = @("-h",$DBCURRENTHOST,"-U",$DBUSER,"-f",$outfile,"-w",$DBNAME); +Start-Process -NoNewWindow -FilePath $PGDUMP -PassThru -Wait -ArgumentList $dumpargs + +$outfile = $OUTDIR + ${DBNAME} + "." + $ENVTYPE + '.pg.schema.sql'; +$dumpargs = @("-h",$DBCURRENTHOST,"-U",$DBUSER,"-f",$outfile,"-w","-O","-x","-s",$DBNAME); +Start-Process -NoNewWindow -FilePath $PGDUMP -PassThru -Wait -ArgumentList $dumpargs + +$outfile = $OUTDIR + ${DBNAME} + "." + $ENVTYPE + '.pg.data.sql'; +$dumpargs = @("-h",$DBCURRENTHOST,"-U",$DBUSER,"-f",$outfile,"-w","-O","-x","-a",$DBNAME); +Start-Process -NoNewWindow -FilePath $PGDUMP -PassThru -Wait -ArgumentList $dumpargs -- 2.39.5