开源MES qcadoo

-- -- PostgreSQL database dump -- -- Dumped from database version 9.5.5 -- Dumped by pg_dump version 9.5.5 -- -- Name: add_group_role(character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION add_group_role(group_name character varying, role_name character varying) RETURNS void LANGUAGE plpgsql AS $$ DECLARE groupid bigint; roleid bigint; relationcount bigint; BEGIN SELECT id INTO groupid FROM qcadoosecurity_group WHERE identifier = group_name; IF groupid is null THEN RAISE EXCEPTION 'Group(%s) not found', group_name; END IF; SELECT id INTO roleid FROM qcadoosecurity_role WHERE identifier = role_name; IF roleid is null THEN RAISE EXCEPTION 'Role(%) not found', role_name; END IF; SELECT count(*) INTO relationcount FROM jointable_group_role WHERE group_id = groupid and role_id = roleid; IF relationcount = 0 THEN INSERT INTO jointable_group_role (group_id, role_id) VALUES (groupid, roleid); END IF; END; $$; ALTER FUNCTION public.add_group_role(group_name character varying, role_name character varying) OWNER TO postgres; -- -- Name: add_group_role_by_id(bigint, character varying); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION add_group_role_by_id(groupid bigint, role_name character varying) RETURNS void LANGUAGE plpgsql AS $$ DECLARE roleid bigint; relationcount bigint; BEGIN IF groupid is null THEN RAISE EXCEPTION 'Group(%s) not found', groupid; END IF; SELECT id INTO roleid FROM qcadoosecurity_role WHERE identifier = role_name; IF roleid is null THEN RAISE EXCEPTION 'Role(%) not found', role_name; END IF; SELECT count(*) INTO relationcount FROM jointable_group_role WHERE group_id = groupid and role_id = roleid; IF relationcount = 0 THEN INSERT INTO jointable_group_role (group_id, role_id) VALUES (groupid, roleid); END IF; END; $$; ALTER FUNCTION public.add_group_role_by_id(groupid bigint, role_name character varying) OWNER TO postgres; -- -- Name: add_role(character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION add_role(role_name character varying, description character varying) RETURNS void LANGUAGE plpgsql AS $$ DECLARE relationcount bigint; BEGIN SELECT count(*) INTO relationcount FROM qcadoosecurity_role WHERE identifier = role_name; IF relationcount = 0 THEN INSERT INTO qcadoosecurity_role (identifier, description) VALUES (role_name, description); END IF; END; $$; ALTER FUNCTION public.add_role(role_name character varying, description character varying) OWNER TO postgres; -- -- Name: add_sequences(); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION add_sequences() RETURNS void LANGUAGE plpgsql AS $$ DECLARE row record; BEGIN FOR row IN SELECT tablename FROM pg_tables p INNER JOIN information_schema.columns c ON p.tablename = c.table_name WHERE c.table_schema = 'public' AND p.schemaname = 'public' AND c.column_name = 'id' AND data_type = 'bigint' LOOP IF NOT EXISTS (SELECT 0 FROM pg_class WHERE relname = substring('' || quote_ident(row.tablename) || '_id_seq' FROM 0 FOR 64)) THEN EXECUTE 'CREATE SEQUENCE ' || quote_ident(row.tablename) || '_id_seq;'; EXECUTE 'ALTER TABLE ' || quote_ident(row.tablename) || ' ALTER COLUMN id SET DEFAULT nextval(''' || quote_ident(row.tablename) || '_id_seq'');'; EXECUTE 'ALTER SEQUENCE ' || quote_ident(row.tablename) || '_id_seq OWNED BY ' || quote_ident(row.tablename) || '.id'; EXECUTE 'WITH mx AS (SELECT max(id)+1 AS mx FROM ' || quote_ident(row.tablename) || ') SELECT setval( ''' || quote_ident(row.tablename) || '_id_seq'' , mx.mx) FROM mx'; END IF; END LOOP; FOR row IN SELECT viewname FROM pg_views p INNER JOIN information_schema.columns c ON p.viewname = c.table_name WHERE c.table_schema = 'public' AND p.schemaname = 'public' AND c.column_name = 'id' AND data_type = 'bigint' LOOP IF NOT EXISTS (SELECT 0 FROM pg_class WHERE relname = substring('' || quote_ident(row.viewname) || '_id_seq' FROM 0 FOR 64)) THEN EXECUTE 'CREATE SEQUENCE ' || quote_ident(row.viewname) || '_id_seq;'; END IF; END LOOP; END; $$; ALTER FUNCTION public.add_sequences() OWNER TO postgres; -- -- Name: f_add_col(regclass, text, regtype); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION f_add_col(_tbl regclass, _col text, _type regtype) RETURNS boolean LANGUAGE plpgsql AS $$ BEGIN IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = _tbl AND attname = lower(_col) AND NOT attisdropped) THEN RETURN FALSE; ELSE EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s', _tbl, lower(_col), _type); RETURN TRUE; END IF; END $$; ALTER FUNCTION public.f_add_col(_tbl regclass, _col text, _type regtype) OWNER TO postgres; -- -- Name: f_add_col_default(regclass, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION f_add_col_default(_tbl regclass, _col text, _type text, _default text) RETURNS boolean LANGUAGE plpgsql AS $$ BEGIN IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = _tbl AND attname = lower(_col) AND NOT attisdropped) THEN RETURN FALSE; ELSE EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s DEFAULT %s', _tbl, lower(_col), _type, _default); RETURN TRUE; END IF; END $$; ALTER FUNCTION public.f_add_col_default(_tbl regclass, _col text, _type text, _default text) OWNER TO postgres; -- -- Name: generate_and_set_assignmenttoshift_externalnumber_trigger(); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION generate_and_set_assignmenttoshift_externalnumber_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.externalnumber := generate_assignmenttoshift_externalnumber(); return NEW; END; $$; ALTER FUNCTION public.generate_and_set_assignmenttoshift_externalnumber_trigger() OWNER TO postgres; -- -- Name: generate_and_set_confectionprotocol_externalnumber_trigger(); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION generate_and_set_confectionprotocol_externalnumber_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.externalnumber := generate_confectionprotocol_externalnumber(); return NEW; END; $$; ALTER FUNCTION public.generate_and_set_confectionprotocol_externalnumber_trigger() OWNER TO postgres; -- -- Name: generate_and_set_document_number_trigger(); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION generate_and_set_document_number_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.number := generate_document_number(NEW.number); IF NEW.name is null THEN NEW.name := NEW.number; END IF; return NEW; END; $$; ALTER FUNCTION public.generate_and_set_document_number_trigger() OWNER TO postgres; -- -- Name: generate_and_set_extrusionprotocol_externalnumber_trigger(); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION generate_and_set_extrusionprotocol_externalnumber_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.externalnumber := generate_extrusionprotocol_externalnumber(); return NEW; END; $$; ALTER FUNCTION public.generate_and_set_extrusionprotocol_externalnumber_trigger() OWNER TO postgres; -- -- Name: generate_and_set_maintenanceevent_number_trigger(); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION generate_and_set_maintenanceevent_number_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.number := generate_mainten