demo_schema.sql | searchcode

/cmdbuild/src/main/webapp/WEB-INF/sql/sample_schemas/demo_schema.sql

https://bitbucket.org/tecnoteca/cmdbuild · SQL · 12371 lines · 5494 code · 6846 blank · 31 comment · 197 complexity · f1267c04c0fee2540a0aa8141064b3df MD5 · raw file
Large files are truncated click here to view the full file

SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;


CREATE FUNCTION _cm_add_class_cascade_delete_on_relations_trigger(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		CREATE TRIGGER "_CascadeDeleteOnRelations"
			AFTER UPDATE
			ON '|| TableId::regclass ||'
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_cascade_delete_on_relations();
	';
END;
$$;



CREATE FUNCTION _cm_add_class_history_trigger(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		CREATE TRIGGER "_CreateHistoryRow"
			AFTER DELETE OR UPDATE
			ON '|| TableId::regclass ||'
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_create_card_history_row()
	';
END;
$$;



CREATE FUNCTION _cm_add_class_sanity_check_trigger(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		CREATE TRIGGER "_SanityCheck"
			BEFORE INSERT OR UPDATE OR DELETE
			ON '|| TableId::regclass ||'
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_sanity_check();
	';
END;
$$;



CREATE FUNCTION _cm_add_domain_history_trigger(domainid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		CREATE TRIGGER "_CreateHistoryRow"
			AFTER DELETE OR UPDATE
			ON '|| DomainId::regclass ||'
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_create_relation_history_row()
	';
END;
$$;



CREATE FUNCTION _cm_add_domain_sanity_check_trigger(domainid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		CREATE TRIGGER "_SanityCheck"
			BEFORE INSERT OR UPDATE OR DELETE
			ON '|| DomainId::regclass ||'
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_sanity_check();
	';
END
$$;



CREATE FUNCTION _cm_add_fk_constraints(fksourceid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	FKTargetId oid := _cm_get_fk_target_table_id(FKSourceId, AttributeName);
	SubTableId oid;
BEGIN
	IF FKTargetId IS NULL THEN
		RETURN;
	END IF;

	FOR SubTableId IN SELECT _cm_subtables_and_itself(FKSourceId) LOOP
		PERFORM _cm_add_fk_trigger(SubTableId, FKSourceId, AttributeName, FKTargetId);
	END LOOP;

	FOR SubTableId IN SELECT _cm_subtables_and_itself(FKTargetId) LOOP
		PERFORM _cm_add_restrict_trigger(SubTableId, FKSourceId, AttributeName);
	END LOOP;
END;
$$;



CREATE FUNCTION _cm_add_fk_trigger(tableid oid, fksourceid oid, fkattribute text, fktargetid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	TriggerVariant text;
BEGIN
	IF _cm_is_simpleclass(FKSourceId) THEN
		TriggerVariant := 'simple';
	ELSE
		TriggerVariant := '';
	END IF;

	EXECUTE '
		CREATE TRIGGER ' || quote_ident(_cm_classfk_name(FKSourceId, FKAttribute)) || '
			BEFORE INSERT OR UPDATE
			ON ' || TableId::regclass || '
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_fk('||
				quote_literal(FKAttribute) || ',' ||
				quote_literal(FKTargetId::regclass) || ',' ||
				quote_literal(TriggerVariant) ||
			');
	';
END;
$$;



CREATE FUNCTION _cm_add_reference_handling(tableid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	objid integer;
	referencedid integer;
	ctrlint integer;

	AttributeComment text := _cm_comment_for_attribute(TableId, AttributeName);
	ReferenceTargetId oid := _cm_read_reference_target_id_comment(AttributeComment);
	AttributeReferenceType text := _cm_read_reference_type_comment(AttributeComment);
	ReferenceDomainId oid := _cm_read_reference_domain_id_comment(AttributeComment);

	RefSourceIdAttribute text := _cm_get_ref_source_id_domain_attribute(TableId, AttributeName);
	RefSourceClassIdAttribute text := _cm_get_ref_source_class_domain_attribute(TableId, AttributeName);
	RefTargetIdAttribute text := _cm_get_ref_target_id_domain_attribute(TableId, AttributeName);

	ChildId oid;
BEGIN
	IF ReferenceTargetId IS NULL OR AttributeReferenceType IS NULL OR ReferenceDomainId IS NULL THEN
		RETURN;
	END IF;

	-- Updates the reference for every relation
	-- TODO: UNDERSTAND WHAT IT DOES AND MAKE IT READABLE!
	FOR objid IN EXECUTE 'SELECT "Id" from '||TableId::regclass||' WHERE "Status"=''A'''
	LOOP
		FOR referencedid IN EXECUTE '
			SELECT '|| quote_ident(RefTargetIdAttribute) ||
			' FROM '|| ReferenceDomainId::regclass ||
			' WHERE '|| quote_ident(RefSourceClassIdAttribute) ||'='|| TableId ||
				' AND '|| quote_ident(RefSourceIdAttribute) ||'='|| objid ||
				' AND "Status"=''A'''
		LOOP
			EXECUTE 'SELECT count(*) FROM '||ReferenceTargetId::regclass||' where "Id"='||referencedid INTO ctrlint;
			IF(ctrlint<>0) THEN
				EXECUTE 'UPDATE '|| TableId::regclass ||
					' SET '|| quote_ident(AttributeName) ||'='|| referencedid ||
					' WHERE "Id"='|| objid;
			END IF;
		END LOOP;
	END LOOP;

	-- Trigger on reference class (reference -> relation)
	FOR ChildId IN SELECT _cm_subtables_and_itself(TableId) LOOP
		PERFORM _cm_add_update_relation_trigger(ChildId, TableId, AttributeName);
	END LOOP;

	-- Trigger on domain (relation -> reference)
	PERFORM _cm_add_update_reference_trigger(TableId, AttributeName);
END;
$$;



CREATE FUNCTION _cm_add_restrict_trigger(fktargetclassid oid, fkclassid oid, fkattribute text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF FKClassId IS NULL THEN
		RETURN;
	END IF;

	EXECUTE '
		CREATE TRIGGER ' || quote_ident('_Constr_'||_cm_cmtable(FKClassId)||'_'||FKAttribute) || '
			BEFORE UPDATE OR DELETE
			ON ' || FKTargetClassId::regclass || '
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_restrict(' ||
					quote_literal(FKClassId::regclass) || ',' ||
					quote_literal(FKAttribute) ||
				');
	';
END;
$$;



CREATE FUNCTION _cm_add_simpleclass_sanity_check_trigger(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		CREATE TRIGGER "_SanityCheck"
			BEFORE INSERT OR UPDATE OR DELETE
			ON '|| TableId::regclass ||'
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_sanity_check_simple();
	';
END;
$$;



CREATE FUNCTION _cm_add_spherical_mercator() RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	FoundSrid integer;
BEGIN
	SELECT "srid" INTO FoundSrid FROM "spatial_ref_sys" WHERE "srid" = 900913 LIMIT 1;
	IF NOT FOUND THEN
		INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (900913,'spatialreferencing.org',900913,'','+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs');
	END IF;
END;
$$;



CREATE FUNCTION _cm_add_update_reference_trigger(tableid oid, refattribute text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	DomainId oid := _cm_get_reference_domain_id(TableId, RefAttribute);
	DomainSourceIdAttribute text := _cm_get_ref_source_id_domain_attribute(TableId, RefAttribute);
	DomainTargetIdAttribute text := _cm_get_ref_target_id_domain_attribute(TableId, RefAttribute);
BEGIN
	IF DomainId IS NULL OR DomainSourceIdAttribute IS NULL OR DomainTargetIdAttribute IS NULL THEN
		RETURN;
	END IF;

	EXECUTE '
		CREATE TRIGGER ' || quote_ident(_cm_update_reference_trigger_name(TableId, RefAttribute)) || '
			AFTER INSERT OR UPDATE
			ON ' || DomainId::regclass || '
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_update_reference(' ||
					quote_literal(RefAttribute) || ',' ||
					quote_literal(TableId::regclass) || ',' ||
					quote_literal(DomainSourceIdAttribute) || ',' ||
					quote_literal(DomainTargetIdAttribute) ||
				');
	';
END;
$$;



CREATE FUNCTION _cm_add_update_relation_trigger(tableid oid, reftableid oid, refattribute text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	DomainId oid := _cm_get_reference_domain_id(RefTableId, RefAttribute);
	DomainSourceIdAttribute text := _cm_get_ref_source_id_domain_attribute(RefTableId, RefAttribute);
	DomainTargetIdAttribute text := _cm_get_ref_target_id_domain_attribute(RefTableId, RefAttribute);
BEGIN
	IF DomainId IS NULL OR DomainSourceIdAttribute IS NULL OR DomainTargetIdAttribute IS NULL THEN
		RETURN;
	END IF;

	EXECUTE '
		CREATE TRIGGER ' || quote_ident(_cm_update_relation_trigger_name(RefTableId, RefAttribute)) || '
			AFTER INSERT OR UPDATE
			ON ' || TableId::regclass || '
			FOR EACH ROW
			EXECUTE PROCEDURE _cm_trigger_update_relation(' ||
				quote_literal(RefAttribute) || ',' ||
				quote_literal(DomainId::regclass) || ',' ||
				quote_literal(DomainSourceIdAttribute) || ',' ||
				quote_literal(DomainTargetIdAttribute) ||
			');
	';
END;
$$;



CREATE FUNCTION _cm_attribute_default_to_src(tableid oid, attributename text, newdefault text) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
	SQLType text := _cm_get_attribute_sqltype(TableId, AttributeName);
BEGIN
	IF (NewDefault IS NULL OR TRIM(NewDefault) = '') THEN
		RETURN NULL;
	END IF;

    IF SQLType ILIKE 'varchar%' OR SQLType = 'text' OR
    	((SQLType = 'date' OR SQLType = 'timestamp') AND TRIM(NewDefault) <> 'now()')
    THEN
		RETURN quote_literal(NewDefault);
	ELSE
		RETURN NewDefault;
	END IF;
END;
$$;



CREATE FUNCTION _cm_attribute_is_empty(tableid oid, attributename text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	Out boolean;
BEGIN
	EXECUTE 'SELECT (COUNT(*) = 0) FROM '|| TableId::regclass ||
		' WHERE '|| quote_ident(AttributeName) ||' IS NOT NULL' || 
	    ' AND '|| quote_ident(AttributeName) ||'::text <> '''' LIMIT 1' INTO Out;
	RETURN Out;
END;
$$;



CREATE FUNCTION _cm_attribute_is_inherited(tableid oid, attributename text) RETURNS boolean
    LANGUAGE sql
    AS $_$
	SELECT pg_attribute.attinhcount <> 0
	FROM pg_attribute
	WHERE pg_attribute.attrelid = $1 AND pg_attribute.attname = $2;
$_$;



CREATE FUNCTION _cm_attribute_is_local(tableid oid, attributename text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT (attinhcount = 0) FROM pg_attribute WHERE attrelid = $1 AND attname = $2 LIMIT 1;
$_$;



CREATE FUNCTION _cm_attribute_is_notnull(tableid oid, attributename text) RETURNS boolean
    LANGUAGE sql
    AS $_$
SELECT pg_attribute.attnotnull OR c.oid IS NOT NULL
FROM pg_attribute
LEFT JOIN pg_constraint AS c
	ON c.conrelid = pg_attribute.attrelid
	AND c.conname::text = _cm_notnull_constraint_name(pg_attribute.attname::text)
WHERE pg_attribute.attrelid = $1 AND pg_attribute.attname = $2;
$_$;



CREATE FUNCTION _cm_attribute_is_unique(tableid oid, attributename text) RETURNS boolean
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE
	IsUnique boolean;
BEGIN
	SELECT INTO IsUnique (count(*) > 0) FROM pg_class
		JOIN pg_index ON pg_class.oid = pg_index.indexrelid
		WHERE pg_index.indrelid = TableId AND relname = _cm_unique_index_name(TableId, AttributeName);
	RETURN IsUnique;
END;
$$;



CREATE FUNCTION _cm_attribute_list(tableid oid) RETURNS SETOF text
    LANGUAGE sql STABLE
    AS $_$
	SELECT attname::text FROM pg_attribute WHERE attrelid = $1 AND attnum > 0 AND atttypid > 0 ORDER BY attnum;
$_$;



CREATE FUNCTION _cm_attribute_list_cs(classid oid) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT array_to_string(array(
		SELECT quote_ident(name) FROM _cm_attribute_list($1) AS name
	),',');
$_$;



CREATE FUNCTION _cm_attribute_notnull_is_check(tableid oid, attributename text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	AttributeComment text := _cm_comment_for_attribute(TableId, AttributeName);
BEGIN
	RETURN NOT (
		_cm_is_simpleclass(TableId)
		OR _cm_is_system(TableId)
		OR _cm_check_comment(_cm_comment_for_attribute(TableId, AttributeName), 'MODE', 'reserved')
	);
END
$$;



CREATE FUNCTION _cm_attribute_root_table_id(tableid oid, attributename text) RETURNS oid
    LANGUAGE plpgsql
    AS $$
DECLARE
	CurrentTableId oid := TableId;
BEGIN
	LOOP
	    EXIT WHEN CurrentTableId IS NULL OR _cm_attribute_is_local(CurrentTableId, AttributeName);
		CurrentTableId := _cm_parent_id(CurrentTableId);
	END LOOP;
	RETURN CurrentTableId;
END
$$;



CREATE FUNCTION _cm_attribute_set_notnull(tableid oid, attributename text, willbenotnull boolean) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	AttributeComment text := _cm_comment_for_attribute(TableId, AttributeName);
BEGIN
	IF WillBeNotNull = _cm_attribute_is_notnull(TableId, AttributeName) THEN
		RETURN;
	END IF;

    IF WillBeNotNull AND _cm_is_superclass(TableId) AND _cm_check_comment(AttributeComment, 'MODE', 'write')
    THEN
    	RAISE NOTICE 'Non-system superclass attributes cannot be not null';
		RAISE EXCEPTION 'CM_FORBIDDEN_OPERATION';
    END IF;

	PERFORM _cm_attribute_set_notnull_unsafe(TableId, AttributeName, WillBeNotNull);
END;
$$;



CREATE FUNCTION _cm_attribute_set_notnull_unsafe(tableid oid, attributename text, willbenotnull boolean) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    IsCheck boolean := _cm_attribute_notnull_is_check(TableId, AttributeName);
BEGIN
	IF (WillBeNotNull) THEN
		IF (IsCheck) THEN
			EXECUTE 'ALTER TABLE '|| TableId::regclass ||
				' ADD CONSTRAINT ' || quote_ident(_cm_notnull_constraint_name(AttributeName)) ||
				' CHECK ("Status"<>''A'' OR ' || quote_ident(AttributeName) || ' IS NOT NULL)';
		ELSE
			EXECUTE 'ALTER TABLE '|| TableId::regclass ||' ALTER COLUMN '|| quote_ident(AttributeName) ||' SET NOT NULL';
		END IF;
	ELSE
		IF (IsCheck) THEN
			EXECUTE 'ALTER TABLE '|| TableId::regclass ||' DROP CONSTRAINT '||
				quote_ident(_cm_notnull_constraint_name(AttributeName));
		ELSE
			EXECUTE 'ALTER TABLE '|| TableId::regclass ||' ALTER COLUMN '|| quote_ident(AttributeName) ||' DROP NOT NULL';
		END IF;
	END IF;
END;
$$;



CREATE FUNCTION _cm_attribute_set_uniqueness(tableid oid, attributename text, attributeunique boolean) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF _cm_attribute_is_unique(TableId, AttributeName) <> AttributeUnique THEN
		IF AttributeUnique AND (_cm_is_simpleclass(TableId) OR _cm_is_superclass(TableId)) AND NOT _cm_is_system(TableId) THEN
			RAISE NOTICE 'User defined superclass or simple class attributes cannot be unique';
			RAISE EXCEPTION 'CM_FORBIDDEN_OPERATION';
		END IF;

		PERFORM _cm_attribute_set_uniqueness_unsafe(TableId, AttributeName, AttributeUnique);
	END IF;
END;
$$;



CREATE FUNCTION _cm_attribute_set_uniqueness_unsafe(tableid oid, attributename text, attributeunique boolean) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF _cm_is_simpleclass(TableId) THEN
		IF AttributeUnique THEN
			EXECUTE 'ALTER TABLE '|| TableId::regclass ||' ADD UNIQUE ('|| quote_ident(AttributeName) || ')';
		ELSE
			EXECUTE 'ALTER TABLE '|| TableId::regclass ||' DROP UNIQUE ('|| quote_ident(AttributeName) || ')';
		END IF;
	ELSE
		IF AttributeUnique THEN
			EXECUTE 'CREATE UNIQUE INDEX '||
				quote_ident(_cm_unique_index_name(TableId, AttributeName)) ||
				' ON '|| TableId::regclass ||' USING btree (('||
				' CASE WHEN "Status"::text = ''N''::text THEN NULL'||
				' ELSE '|| quote_ident(AttributeName) || ' END))';
		ELSE
			EXECUTE 'DROP INDEX '|| _cm_unique_index_id(TableId, AttributeName)::regclass;
		END IF;
	END IF;
END
$$;



CREATE FUNCTION _cm_cascade(id integer, tableid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE 'DELETE FROM '|| TableId::regclass ||
		' WHERE '||quote_ident(AttributeName)||' = '||Id::text;
END;
$$;



CREATE FUNCTION _cm_check_attribute_comment_and_type(attributecomment text, sqltype text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	SpecialTypeCount integer := 0; 
BEGIN
	IF _cm_read_reference_domain_comment(AttributeComment) IS NOT NULL THEN
		SpecialTypeCount := SpecialTypeCount +1;
	END IF;

	IF _cm_get_fk_target_comment(AttributeComment) IS NOT NULL THEN
		SpecialTypeCount := SpecialTypeCount +1;
	END IF;

	IF _cm_get_lookup_type_comment(AttributeComment) IS NOT NULL THEN
		SpecialTypeCount := SpecialTypeCount +1;
	END IF;

	IF (SpecialTypeCount > 1) THEN
		RAISE NOTICE 'Too many CMDBuild types specified';
		RAISE EXCEPTION 'CM_FORBIDDEN_OPERATION';
	END IF;

	IF SpecialTypeCount = 1 AND SQLType NOT IN ('int4','integer') THEN
		RAISE NOTICE 'The SQL type does not match the CMDBuild type';
		RAISE EXCEPTION 'CM_FORBIDDEN_OPERATION';
	END IF;
END;
$$;



CREATE FUNCTION _cm_check_comment(classcomment text, key text, value text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT (_cm_read_comment($1, $2) ILIKE $3);
$_$;



CREATE FUNCTION _cm_check_id_exists(id integer, tableid oid, deletedalso boolean) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_check_value_exists($1, $2, 'Id', $3);
$_$;



CREATE FUNCTION _cm_check_value_exists(id integer, tableid oid, attributename text, deletedalso boolean) RETURNS boolean
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE
	Out BOOLEAN := TRUE;
	StatusPart TEXT;
BEGIN
	IF _cm_is_simpleclass(TableId) OR DeletedAlso THEN
		StatusPart := '';
	ELSE
		StatusPart := ' AND "Status"=''A''';
	END IF;
	IF Id IS NOT NULL THEN
		EXECUTE 'SELECT (COUNT(*) > 0) FROM '|| TableId::regclass ||' WHERE '||
		quote_ident(AttributeName)||'='||Id||StatusPart||' LIMIT 1' INTO Out;
	END IF;
	RETURN Out;
END
$$;



CREATE FUNCTION _cm_class_has_children(tableid oid) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT (COUNT(*) > 0) FROM pg_inherits WHERE inhparent = $1 AND _cm_is_cmobject(inhrelid) LIMIT 1;
$_$;



CREATE FUNCTION _cm_class_has_domains(tableid oid) RETURNS boolean
    LANGUAGE sql
    AS $_$
	SELECT (COUNT(*) > 0) FROM _cm_domain_list() AS d
	WHERE _cm_table_id(_cm_read_comment(_cm_comment_for_cmobject(d), 'CLASS1')) = $1 OR
		_cm_table_id(_cm_read_comment(_cm_comment_for_cmobject(d), 'CLASS2')) = $1;
$_$;



CREATE FUNCTION _cm_class_list() RETURNS SETOF oid
    LANGUAGE sql STABLE
    AS $$
	SELECT oid FROM pg_class WHERE _cm_is_any_class_comment(_cm_comment_for_cmobject(oid));
$$;



CREATE FUNCTION _cm_classfk_name(cmclassname text, attributename text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_cmtable($1) || '_' || $2 || '_fkey';
$_$;



CREATE FUNCTION _cm_classfk_name(tableid oid, attributename text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_cmtable($1) || '_' || $2 || '_fkey';
$_$;



CREATE FUNCTION _cm_classidx_name(tableid oid, attributename text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT 'idx_' || REPLACE(_cm_cmtable_lc($1), '_', '') || '_' || lower($2);
$_$;



CREATE FUNCTION _cm_classpk_name(cmclassname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_cmtable($1) || '_pkey';
$_$;



CREATE FUNCTION _cm_cmschema(cmname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT (_cm_split_cmname($1))[1];
$_$;



CREATE FUNCTION _cm_cmschema(tableid oid) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT pg_namespace.nspname::text FROM pg_class
	JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
	WHERE pg_class.oid=$1
$_$;



CREATE FUNCTION _cm_cmtable(cmname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT (_cm_split_cmname($1))[2];
$_$;



CREATE FUNCTION _cm_cmtable(tableid oid) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT pg_class.relname::text FROM pg_class	WHERE pg_class.oid=$1
$_$;



CREATE FUNCTION _cm_cmtable_lc(cmname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT lower(_cm_cmtable($1));
$_$;



CREATE FUNCTION _cm_cmtable_lc(tableid oid) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT lower(_cm_cmtable($1));
$_$;



CREATE FUNCTION _cm_comment_for_attribute(tableid oid, attributename text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT description
FROM pg_description
JOIN pg_attribute ON pg_description.objoid = pg_attribute.attrelid AND pg_description.objsubid = pg_attribute.attnum
WHERE attrelid = $1 and attname = $2 LIMIT 1;
$_$;



CREATE FUNCTION _cm_comment_for_class(cmclass text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_comment_for_table_id(_cm_table_id($1));
$_$;



CREATE FUNCTION _cm_comment_for_cmobject(tableid oid) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT description FROM pg_description
	WHERE objoid = $1 AND objsubid = 0 AND _cm_read_comment(description, 'TYPE') IS NOT NULL LIMIT 1;
$_$;



CREATE FUNCTION _cm_comment_for_domain(cmdomain text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_comment_for_table_id(_cm_domain_id($1));
$_$;



CREATE FUNCTION _cm_comment_for_table_id(tableid oid) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT description FROM pg_description WHERE objoid = $1;
$_$;



CREATE FUNCTION _cm_copy_fk_trigger(fromid oid, toid oid) RETURNS void
    LANGUAGE sql
    AS $_$
	SELECT _cm_copy_trigger($1, $2, '%_fkey');
$_$;



CREATE FUNCTION _cm_copy_restrict_trigger(fromid oid, toid oid) RETURNS void
    LANGUAGE sql
    AS $_$
	SELECT _cm_copy_trigger($1, $2, '_Constr_%');
$_$;



CREATE FUNCTION _cm_copy_superclass_attribute_comments(tableid oid, parenttableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	AttributeName text;
BEGIN
	FOR AttributeName IN SELECT * FROM _cm_attribute_list(ParentTableId)
	LOOP
		EXECUTE 'COMMENT ON COLUMN '|| TableId::regclass || '.' || quote_ident(AttributeName) ||
			' IS '|| quote_literal(_cm_comment_for_attribute(ParentTableId, AttributeName));
	END LOOP;
END
$$;



CREATE FUNCTION _cm_copy_trigger(fromid oid, toid oid, triggernamematcher text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	TriggerData record;
BEGIN
	FOR TriggerData IN
		SELECT
			t.tgname AS TriggerName,
			t.tgtype AS TriggerType,
			p.proname AS TriggerFunction,
			array_to_string(array(
				SELECT quote_literal(q.param)
					FROM (SELECT regexp_split_to_table(encode(tgargs, 'escape'), E'\\\\000') AS param) AS q
					WHERE q.param <> ''
			),',') AS TriggerParams
		FROM pg_trigger t, pg_proc p
		WHERE tgrelid = FromId AND tgname LIKE TriggerNameMatcher AND t.tgfoid = p.oid
	LOOP
		EXECUTE '
			CREATE TRIGGER '|| quote_ident(TriggerData.TriggerName) ||'
				'|| _cm_trigger_when(TriggerData.TriggerType) ||'
				ON '|| ToId::regclass ||'
				FOR EACH '|| _cm_trigger_row_or_statement(TriggerData.TriggerType) ||'
				EXECUTE PROCEDURE '|| quote_ident(TriggerData.TriggerFunction) ||'('|| TriggerData.TriggerParams ||')
		';
	END LOOP;
END;
$$;



CREATE FUNCTION _cm_copy_update_relation_trigger(fromid oid, toid oid) RETURNS void
    LANGUAGE sql
    AS $_$
	SELECT _cm_copy_trigger($1, $2, '_UpdRel_%');
$_$;



CREATE FUNCTION _cm_create_class_history(cmclassname text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		CREATE TABLE '|| _cm_history_dbname_unsafe(CMClassName) ||'
		(
			"CurrentId" int4 NOT NULL,
			"EndDate" timestamp NOT NULL DEFAULT now(),
			CONSTRAINT ' || quote_ident(_cm_historypk_name(CMClassName)) ||' PRIMARY KEY ("Id"),
			CONSTRAINT '|| quote_ident(_cm_historyfk_name(CMClassName, 'CurrentId')) ||' FOREIGN KEY ("CurrentId")
				REFERENCES '||_cm_table_dbname(CMClassName)||' ("Id") ON UPDATE RESTRICT ON DELETE SET NULL
		) INHERITS ('||_cm_table_dbname(CMClassName)||');
	';
	PERFORM _cm_create_index(_cm_history_id(CMClassName), 'CurrentId');
END;
$$;



CREATE FUNCTION _cm_create_class_indexes(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	PERFORM _cm_create_index(TableId, 'Code');
	PERFORM _cm_create_index(TableId, 'Description');
	PERFORM _cm_create_index(TableId, 'IdClass');
END;
$$;



CREATE FUNCTION _cm_create_class_triggers(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF _cm_is_superclass(TableId) THEN
		RAISE DEBUG 'Not creating triggers for class %', TableId::regclass;
	ELSIF _cm_is_simpleclass(TableId) THEN
		PERFORM _cm_add_simpleclass_sanity_check_trigger(TableId);
	ELSE
		PERFORM _cm_add_class_sanity_check_trigger(TableId);
		PERFORM _cm_add_class_history_trigger(TableId);
		PERFORM _cm_add_class_cascade_delete_on_relations_trigger(TableId);
	END IF;
END;
$$;



CREATE FUNCTION _cm_create_domain_indexes(domainid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    Cardinality text := _cm_domain_cardinality(DomainId);
BEGIN
	PERFORM _cm_create_index(DomainId, 'IdDomain');
	PERFORM _cm_create_index(DomainId, 'IdObj1');
	PERFORM _cm_create_index(DomainId, 'IdObj2');

	EXECUTE 'CREATE UNIQUE INDEX ' || quote_ident(_cm_domainidx_name(DomainId, 'ActiveRows')) ||
		' ON ' || DomainId::regclass ||
		' USING btree ('||
			'(CASE WHEN "Status" = ''N'' THEN NULL ELSE "IdDomain" END),'||
			'(CASE WHEN "Status" = ''N'' THEN NULL ELSE "IdClass1" END),'||
			'(CASE WHEN "Status" = ''N'' THEN NULL ELSE "IdObj1" END),'||
			'(CASE WHEN "Status" = ''N'' THEN NULL ELSE "IdClass2" END),'||
			'(CASE WHEN "Status" = ''N'' THEN NULL ELSE "IdObj2" END)'||
		')';

	IF substring(Cardinality, 3, 1) = '1' THEN
		EXECUTE
		'CREATE UNIQUE INDEX ' || quote_ident(_cm_domainidx_name(DomainId,'UniqueLeft')) ||
		' ON ' || DomainId::regclass ||
		' USING btree ( '||
			'(CASE WHEN "Status"::text = ''A'' THEN "IdClass1" ELSE NULL END),'||
			'(CASE WHEN "Status"::text = ''A'' THEN "IdObj1" ELSE NULL END)'||
		' )';
	END IF;

	IF substring(Cardinality, 1, 1) = '1' THEN
		EXECUTE
		'CREATE UNIQUE INDEX ' || quote_ident(_cm_domainidx_name(DomainId,'UniqueRight')) ||
		' ON ' || DomainId::regclass ||
		' USING btree ( '||
			'(CASE WHEN "Status"::text = ''A'' THEN "IdClass2" ELSE NULL END),'||
			'(CASE WHEN "Status"::text = ''A'' THEN "IdObj2" ELSE NULL END)'||
		' )';
	END IF;
END
$$;



CREATE FUNCTION _cm_create_domain_triggers(domainid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	PERFORM _cm_add_domain_sanity_check_trigger(DomainId);
	PERFORM _cm_add_domain_history_trigger(DomainId);
END;
$$;



CREATE FUNCTION _cm_create_index(tableid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE 'CREATE INDEX ' || quote_ident(_cm_classidx_name(TableId, AttributeName)) ||
		' ON ' || TableId::regclass ||
		' USING btree (' || quote_ident(AttributeName) || ')';
EXCEPTION
	WHEN undefined_column THEN
		RAISE LOG 'Index for attribute %.% not created because the attribute does not exist',
			TableId::regclass, quote_ident(AttributeName);
END
$$;



CREATE FUNCTION _cm_create_schema_if_needed(cmname text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF _cm_cmschema(CMName) IS NOT NULL THEN
		EXECUTE 'CREATE SCHEMA '||quote_ident(_cm_cmschema(CMName));
	END IF;
EXCEPTION
	WHEN duplicate_schema THEN
		RETURN;
END;
$$;



CREATE FUNCTION _cm_delete_local_attributes(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	AttributeName text;
BEGIN
	FOR AttributeName IN SELECT _cm_attribute_list(TableId) LOOP
		IF NOT _cm_attribute_is_inherited(TableId, AttributeName) THEN
			PERFORM cm_delete_attribute(TableId, AttributeName);
		END IF;
	END LOOP;
END
$$;



CREATE FUNCTION _cm_delete_relation(username text, domainid oid, cardidcolumn text, cardid integer) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN
	EXECUTE 'UPDATE ' || DomainId::regclass ||
		' SET "Status" = ''N'', "User" = ' || coalesce(quote_literal(UserName),'NULL') ||
		' WHERE "Status" = ''A'' AND ' || quote_ident(CardIdColumn) || ' = ' || CardId;
END;
$$;



CREATE FUNCTION _cm_dest_classid_for_domain_attribute(domainid oid, attributename text) RETURNS oid
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_table_id(
		_cm_read_comment(
			_cm_comment_for_table_id($1),
			CASE $2
			WHEN 'IdObj1' THEN
				'CLASS1'
			WHEN 'IdObj2' THEN
				'CLASS2'
			ELSE
				NULL
			END
		)
	);
$_$;



CREATE FUNCTION _cm_dest_reference_classid(domainid oid, refidcolumn text, refid integer) RETURNS oid
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_subclassid(_cm_dest_classid_for_domain_attribute($1, $2), $3)
$_$;



CREATE FUNCTION _cm_disable_triggers_recursively(superclass regclass) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE
	CurrentClass regclass := $1;
BEGIN
	FOR CurrentClass IN SELECT _cm_subtables_and_itself(SuperClass) LOOP
		EXECUTE 'ALTER TABLE '|| CurrentClass::regclass ||' DISABLE TRIGGER USER';
	END LOOP;
END;
$_$;



CREATE FUNCTION _cm_domain_cardinality(domainid oid) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_read_domain_cardinality(_cm_comment_for_table_id($1));
$_$;



CREATE FUNCTION _cm_domain_cmname(cmdomain text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT coalesce(_cm_cmschema($1)||'.','')||coalesce('Map_'||_cm_cmtable($1),'Map');
$_$;



CREATE FUNCTION _cm_domain_cmname_lc(cmdomainname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT lower(_cm_domain_cmname($1));
$_$;



CREATE FUNCTION _cm_domain_dbname(cmdomain text) RETURNS regclass
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_table_dbname(_cm_domain_cmname($1));
$_$;



CREATE FUNCTION _cm_domain_dbname_unsafe(cmdomain text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_table_dbname_unsafe(_cm_domain_cmname($1));
$_$;



CREATE FUNCTION _cm_domain_direction(domainid oid) RETURNS boolean
    LANGUAGE plpgsql STABLE STRICT
    AS $$
DECLARE
	Cardinality text := _cm_domain_cardinality(DomainId);
BEGIN
	IF Cardinality = 'N:1' THEN
		RETURN TRUE;
	ELSIF Cardinality = '1:N' THEN
		RETURN FALSE;
	ELSE
		RETURN NULL;
	END IF;
END
$$;



CREATE FUNCTION _cm_domain_id(cmdomain text) RETURNS oid
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_table_id(_cm_domain_cmname($1));
$_$;



CREATE FUNCTION _cm_domain_list() RETURNS SETOF oid
    LANGUAGE sql STABLE
    AS $$
	SELECT oid FROM pg_class WHERE _cm_is_domain_comment(_cm_comment_for_cmobject(oid));
$$;



CREATE FUNCTION _cm_domainidx_name(domainid oid, type text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT 'idx_' || _cm_cmtable_lc($1) || '_' || lower($2);
$_$;



CREATE FUNCTION _cm_domainpk_name(cmdomainname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_classpk_name(_cm_domain_cmname($1));
$_$;



CREATE FUNCTION _cm_drop_triggers_recursively(tableid oid, triggername text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	SubClassId oid;
BEGIN
	FOR SubClassId IN SELECT _cm_subtables_and_itself(TableId) LOOP
		EXECUTE 'DROP TRIGGER IF EXISTS '|| quote_ident(TriggerName) ||' ON '|| SubClassId::regclass;
	END LOOP;
END;
$$;



CREATE FUNCTION _cm_enable_triggers_recursively(superclass regclass) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE
	CurrentClass regclass := $1;
BEGIN
	FOR CurrentClass IN SELECT _cm_subtables_and_itself(SuperClass) LOOP
		EXECUTE 'ALTER TABLE '|| CurrentClass::text ||' ENABLE TRIGGER USER';
	END LOOP;
END;
$_$;



CREATE FUNCTION _cm_function_list(OUT function_name text, OUT function_id oid, OUT arg_io character[], OUT arg_names text[], OUT arg_types text[], OUT returns_set boolean) RETURNS SETOF record
    LANGUAGE plpgsql STABLE
    AS $_$
DECLARE
	R record;
	i integer;
BEGIN
	FOR R IN
		SELECT *
		FROM pg_proc
		WHERE _cm_comment_for_cmobject(oid) IS NOT NULL
	LOOP
		function_name := R.proname::text;
		function_id := R.oid;
		returns_set := R.proretset;
		IF R.proargmodes IS NULL
		THEN
			arg_io := '{}'::char[];
			arg_types := '{}'::text[];
			arg_names := '{}'::text[];
			-- add input columns
			FOR i IN SELECT generate_series(1, array_upper(R.proargtypes,1)) LOOP
				arg_io := arg_io || 'i'::char;
				arg_types := arg_types || _cm_get_sqltype_string(R.proargtypes[i], NULL);
				arg_names := arg_names || COALESCE(R.proargnames[i], '$'||i);
			END LOOP;
			-- add single output column
			arg_io := arg_io || 'o'::char;
			arg_types := arg_types || _cm_get_sqltype_string(R.prorettype, NULL);
			arg_names := arg_names || function_name;
		ELSE
			-- just normalize existing columns
			arg_io := R.proargmodes;
			arg_types := '{}'::text[];
			arg_names := R.proargnames;
			FOR i IN SELECT generate_series(1, array_upper(arg_io,1)) LOOP
				-- normalize table output
				IF arg_io[i] = 't' THEN
					arg_io[i] := 'o';
				ELSIF arg_io[i] = 'b' THEN
					arg_io[i] := 'io';
				END IF;
				arg_types := arg_types || _cm_get_sqltype_string(R.proallargtypes[i], NULL);
				IF arg_names[i] = '' THEN
					IF arg_io[i] = 'i' THEN
						arg_names[i] = '$'||i;
					ELSE
						arg_names[i] = 'column'||i;
					END IF;
				END IF;
			END LOOP;
		END IF;
		RETURN NEXT;
	END LOOP;

	RETURN;
END
$_$;



CREATE FUNCTION _cm_get_attribute_default(tableid oid, attributename text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT pg_attrdef.adsrc
		FROM pg_attribute JOIN pg_attrdef ON pg_attrdef.adrelid = pg_attribute.attrelid AND pg_attrdef.adnum = pg_attribute.attnum
		WHERE pg_attribute.attrelid = $1 AND pg_attribute.attname = $2;
$_$;



CREATE FUNCTION _cm_get_attribute_sqltype(tableid oid, attributename text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_get_sqltype_string(pg_attribute.atttypid, pg_attribute.atttypmod)
		FROM pg_attribute
		WHERE pg_attribute.attrelid = $1 AND pg_attribute.attname = $2;
$_$;



CREATE FUNCTION _cm_get_domain_reference_target_comment(domaincomment text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT CASE _cm_read_domain_cardinality($1)
		WHEN '1:N' THEN _cm_read_comment($1, 'CLASS1')
		WHEN 'N:1' THEN _cm_read_comment($1, 'CLASS2')
		ELSE NULL
	END
$_$;



CREATE FUNCTION _cm_get_fk_target(tableid oid, attributename text) RETURNS text
    LANGUAGE plpgsql STABLE STRICT
    AS $$
DECLARE
	AttributeComment text := _cm_comment_for_attribute(TableId, AttributeName);
BEGIN
	RETURN COALESCE(
		_cm_get_fk_target_comment(AttributeComment),
		_cm_read_reference_target_comment(AttributeComment)
	);
END
$$;



CREATE FUNCTION _cm_get_fk_target_comment(attributecomment text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_read_comment($1, 'FKTARGETCLASS');
$_$;



CREATE FUNCTION _cm_get_fk_target_table_id(tableid oid, attributename text) RETURNS oid
    LANGUAGE plpgsql STABLE STRICT
    AS $_$ BEGIN
	RETURN _cm_table_id(_cm_get_fk_target($1, $2));
END $_$;



CREATE FUNCTION _cm_get_geometry_type(tableid oid, attribute text) RETURNS text
    LANGUAGE plpgsql STABLE
    AS $_$
DECLARE
	GeoType text;
BEGIN
	SELECT geometry_columns.type INTO GeoType
	FROM pg_attribute
	LEFT JOIN geometry_columns
		ON f_table_schema = _cm_cmschema($1)
		AND f_table_name = _cm_cmtable($1)
		AND f_geometry_column = $2
	WHERE attrelid = $1 AND attname = $2 AND attnum > 0 AND atttypid > 0;
	RETURN GeoType;
EXCEPTION WHEN undefined_table THEN
	RETURN NULL;
END
$_$;



CREATE FUNCTION _cm_get_lookup_type_comment(attributecomment text) RETURNS text
    LANGUAGE sql
    AS $_$
	SELECT _cm_read_comment($1, 'LOOKUP');
$_$;



CREATE FUNCTION _cm_get_ref_source_class_domain_attribute(tableid oid, attributename text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT CASE _cm_domain_direction(_cm_get_reference_domain_id($1, $2))
		WHEN TRUE THEN 'IdClass1'
		WHEN FALSE THEN 'IdClass2'
		ELSE NULL
	END;
$_$;



CREATE FUNCTION _cm_get_ref_source_id_domain_attribute(tableid oid, attributename text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT CASE _cm_domain_direction(_cm_get_reference_domain_id($1, $2))
		WHEN TRUE THEN 'IdObj1'
		WHEN FALSE THEN 'IdObj2'
		ELSE NULL
	END;
$_$;



CREATE FUNCTION _cm_get_ref_target_id_domain_attribute(tableid oid, attributename text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT CASE _cm_domain_direction(_cm_get_reference_domain_id($1, $2))
		WHEN TRUE THEN 'IdObj2'
		WHEN FALSE THEN 'IdObj1'
		ELSE NULL
	END;
$_$;



CREATE FUNCTION _cm_get_reference_domain_id(tableid oid, attributename text) RETURNS oid
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_read_reference_domain_id_comment(_cm_comment_for_attribute($1, $2));
$_$;



CREATE FUNCTION _cm_get_sqltype_string(sqltypeid oid, typemod integer) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT pg_type.typname::text || COALESCE(
			CASE
				WHEN pg_type.typname IN ('varchar','bpchar') THEN '(' || $2 - 4 || ')'
				WHEN pg_type.typname = 'numeric' THEN '(' ||
					$2 / 65536 || ',' ||
					$2 - $2 / 65536 * 65536 - 4|| ')'
			END, '')
		FROM pg_type WHERE pg_type.oid = $1;
$_$;



CREATE FUNCTION _cm_get_type_comment(classcomment text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_read_comment($1, 'TYPE');
$_$;



CREATE FUNCTION _cm_history_cmname(cmclass text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT $1 || '_history';
$_$;



CREATE FUNCTION _cm_history_dbname(cmtable text) RETURNS regclass
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_table_dbname(_cm_history_cmname($1));
$_$;



CREATE FUNCTION _cm_history_dbname_unsafe(cmtable text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_table_dbname_unsafe(_cm_history_cmname($1));
$_$;



CREATE FUNCTION _cm_history_id(cmtable text) RETURNS oid
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_table_id(_cm_history_cmname($1));
$_$;



CREATE FUNCTION _cm_historyfk_name(cmclassname text, attributename text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_classfk_name(_cm_history_cmname($1), $2);
$_$;



CREATE FUNCTION _cm_historypk_name(cmclassname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT _cm_classpk_name(_cm_history_cmname($1));
$_$;



CREATE FUNCTION _cm_insert_relation(username text, domainid oid, cardidcolumn text, cardid integer, refidcolumn text, refid integer, cardclassid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	CardClassIdColumnPart text;
	RefClassIdColumnPart text;
	CardClassIdValuePart text;
	RefClassIdValuePart text;
	StopRecursion boolean;
BEGIN
	IF (CardId IS NULL OR RefId IS NULL) THEN
		RAISE EXCEPTION 'CM_FORBIDDEN_OPERATION';
	END IF;

	-- Needed for backward compatibility
	CardClassIdColumnPart := coalesce(quote_ident('IdClass'||substring(CardIdColumn from '^IdObj(.)+')) || ', ', '');
	RefClassIdColumnPart := coalesce(quote_ident('IdClass'||substring(RefIdColumn from '^IdObj(.)+')) || ', ', '');
	CardClassIdValuePart := CASE WHEN CardClassIdColumnPart IS NOT NULL THEN (coalesce(CardClassId::text, 'NULL') || ', ') ELSE '' END;
	RefClassIdValuePart := coalesce(_cm_dest_reference_classid(DomainId, RefIdColumn, RefId)::text, 'NULL') || ', ';

	-- Stop trigger recursion
	EXECUTE 'SELECT (COUNT(*) > 0) FROM ' || DomainId::regclass ||
		' WHERE' ||
			' "IdDomain" = ' || DomainId::text || -- NOTE: why is this check done?
			' AND ' || quote_ident(CardIdColumn) || ' = ' || CardId::text ||
			' AND ' || quote_ident(RefIdColumn) || ' = ' || RefId::text ||
			' AND "Status" = ''A''' INTO StopRecursion;
	IF NOT StopRecursion THEN
		EXECUTE 'INSERT INTO ' || DomainId::regclass ||
			' (' ||
				'"IdDomain", ' ||
				quote_ident(CardIdColumn) || ', ' ||
				quote_ident(RefIdColumn) || ', ' ||
				CardClassIdColumnPart ||
				RefClassIdColumnPart ||
				'"Status", ' ||
				'"User"' ||
			') VALUES (' ||
				DomainId::text || ', ' ||
				CardId::text || ', ' ||
				RefId::text || ', ' ||
				CardClassIdValuePart ||
				RefClassIdValuePart ||
				'''A'', ' ||
				coalesce(quote_literal(UserName), 'NULL') ||
			')';
	END IF;
END;
$$;



CREATE FUNCTION _cm_is_active_comment(classcomment text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_check_comment($1, 'STATUS', 'active');
$_$;



CREATE FUNCTION _cm_is_any_class(classid oid) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_is_any_class_comment(_cm_comment_for_table_id($1))
$_$;



CREATE FUNCTION _cm_is_any_class_comment(classcomment text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_check_comment($1, 'TYPE', '%class');
$_$;



CREATE FUNCTION _cm_is_cmobject(tableid oid) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_comment_for_cmobject($1) IS NOT NULL;
$_$;



CREATE FUNCTION _cm_is_domain_comment(classcomment text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_check_comment($1, 'TYPE', 'domain');
$_$;



CREATE FUNCTION _cm_is_geometry_type(cmattributetype text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT $1 IN ('POINT','LINESTRING','POLYGON');
$_$;



CREATE FUNCTION _cm_is_process(classid oid) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT $1 IN (SELECT _cm_subtables_and_itself(_cm_table_id('Activity')));
$_$;



CREATE FUNCTION _cm_is_process(cmclass text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_is_process(_cm_table_id($1));
$_$;



CREATE FUNCTION _cm_is_reference_comment(attributecomment text) RETURNS boolean
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT COALESCE(_cm_read_reference_domain_comment($1),'') != '';
$_$;



CREATE FUNCTION _cm_is_simpleclass(cmclass text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_is_simpleclass_comment(_cm_comment_for_class($1));
$_$;



CREATE FUNCTION _cm_is_simpleclass(classid oid) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_is_simpleclass_comment(_cm_comment_for_table_id($1))
$_$;



CREATE FUNCTION _cm_is_simpleclass_comment(classcomment text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_check_comment($1, 'TYPE', 'simpleclass');
$_$;



CREATE FUNCTION _cm_is_superclass(cmclass text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_is_superclass_comment(_cm_comment_for_class($1));
$_$;



CREATE FUNCTION _cm_is_superclass(classid oid) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_is_superclass_comment(_cm_comment_for_table_id($1));
$_$;



CREATE FUNCTION _cm_is_superclass_comment(classcomment text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_check_comment($1, 'SUPERCLASS', 'true');
$_$;



CREATE FUNCTION _cm_is_system(tableid oid) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_check_comment(_cm_comment_for_table_id($1), 'MODE', 'reserved')
$_$;



CREATE FUNCTION _cm_join_cmname(cmschema name, cmtable name) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT $1 || '.' || $2;
$_$;



CREATE FUNCTION _cm_legacy_get_menu_code(boolean, boolean, boolean, boolean) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
    DECLARE 
        issuperclass ALIAS FOR $1;
        isprocess ALIAS FOR $2;
        isreport ALIAS FOR $3;
        isview ALIAS FOR $4;
	menucode varchar;
    BEGIN
	IF (issuperclass) THEN IF (isprocess) THEN menucode='superclassprocess'; ELSE menucode='superclass'; END IF;
	ELSIF(isview) THEN menucode='view';
	ELSIF(isreport) THEN menucode='report';
	ELSIF (isprocess) THEN menucode='processclass'; ELSE menucode='class';
	END IF;

	RETURN menucode;
    END;
$_$;



CREATE FUNCTION _cm_legacy_get_menu_type(boolean, boolean, boolean, boolean) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
    DECLARE 
        issuperclass ALIAS FOR $1;
        isprocess ALIAS FOR $2;
        isreport ALIAS FOR $3;
        isview ALIAS FOR $4;
	menutype varchar;
    BEGIN
	IF (isprocess) THEN menutype='processclass';
	ELSIF(isview) THEN menutype='view';
	ELSIF(isreport) THEN menutype='report';
	ELSE menutype='class';
	END IF;

	RETURN menutype;
    END;
$_$;



CREATE FUNCTION _cm_legacy_read_comment(text, text) RETURNS character varying
    LANGUAGE sql STABLE
    AS $_$
	SELECT COALESCE(_cm_read_comment($1, $2), '');
$_$;



CREATE FUNCTION _cm_new_card_id() RETURNS integer
    LANGUAGE sql
    AS $$
	SELECT nextval(('class_seq'::text)::regclass)::integer;
$$;



CREATE FUNCTION _cm_notnull_constraint_name(attributename text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT '_NotNull_'||$1;
$_$;



CREATE FUNCTION _cm_parent_id(tableid oid) RETURNS SETOF oid
    LANGUAGE sql
    AS $_$
	SELECT COALESCE((SELECT inhparent FROM pg_inherits WHERE inhrelid = $1 AND _cm_is_cmobject(inhparent) LIMIT 1), NULL);
$_$;



CREATE FUNCTION _cm_propagate_superclass_triggers(tableid oid) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	ParentId oid := _cm_parent_id(TableId);
BEGIN
	PERFORM _cm_copy_restrict_trigger(ParentId, TableId);
	PERFORM _cm_copy_update_relation_trigger(ParentId, TableId);
	PERFORM _cm_copy_fk_trigger(ParentId, TableId);
END
$$;



CREATE FUNCTION _cm_read_comment(comment text, key text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT TRIM(SUBSTRING($1 FROM E'(?:^|\\|)'||$2||E':[ ]*([^\\|]+)'));
$_$;



CREATE FUNCTION _cm_read_domain_cardinality(attributecomment text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_read_comment($1, 'CARDIN');
$_$;



CREATE FUNCTION _cm_read_reference_domain_comment(attributecomment text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_read_comment($1, 'REFERENCEDOM');
$_$;



CREATE FUNCTION _cm_read_reference_domain_id_comment(attributecomment text) RETURNS oid
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_domain_id(_cm_read_reference_domain_comment($1));
$_$;



CREATE FUNCTION _cm_read_reference_target_comment(attributecomment text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_get_domain_reference_target_comment(_cm_comment_for_domain(_cm_read_reference_domain_comment($1)));
$_$;



CREATE FUNCTION _cm_read_reference_target_id_comment(attributecomment text) RETURNS oid
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT _cm_table_id(_cm_read_reference_target_comment($1));
$_$;



CREATE FUNCTION _cm_read_reference_type_comment(attributecomment text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
	SELECT COALESCE(NULLIF(_cm_read_comment($1, 'REFERENCETYPE'), ''), 'restrict');
$_$;



CREATE FUNCTION _cm_remove_attribute_triggers(tableid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	PERFORM _cm_remove_fk_constraints(TableId, AttributeName);
	PERFORM _cm_remove_reference_handling(TableId, AttributeName);
END;
$$;



CREATE FUNCTION _cm_remove_constraint_trigger(fktargetclassid oid, fkclassid oid, fkattribute text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE '
		DROP TRIGGER ' || quote_ident('_Constr_'||_cm_cmtable(FKClassId)||'_'||FKAttribute) ||
			' ON ' || FKTargetClassId::regclass || ';
	';
END;
$$;



CREATE FUNCTION _cm_remove_fk_constraints(fksourceid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	TargetId oid := _cm_get_fk_target_table_id(FKSourceId, AttributeName);
	SubTableId oid;
BEGIN
	IF TargetId IS NULL THEN
		RETURN;
	END IF;

	FOR SubTableId IN SELECT _cm_subtables_and_itself(FKSourceId) LOOP
		EXECUTE 'DROP TRIGGER '|| quote_ident(_cm_classfk_name(FKSourceId, AttributeName)) ||
			' ON '|| SubTableId::regclass;
	END LOOP;

	FOR SubTableId IN SELECT _cm_subtables_and_itself(TargetId) LOOP
		PERFORM _cm_remove_constraint_trigger(SubTableId, FKSourceId, AttributeName);
	END LOOP;
END;
$$;



CREATE FUNCTION _cm_remove_reference_handling(tableid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	-- remove UpdRel and UpdRef triggers
	PERFORM _cm_drop_triggers_recursively(
		TableId,
		_cm_update_relation_trigger_name(TableId, AttributeName)
	);
	PERFORM _cm_drop_triggers_recursively(
		_cm_get_reference_domain_id(TableId, AttributeName),
		_cm_update_reference_trigger_name(TableId, AttributeName)
	);
END
$$;



CREATE FUNCTION _cm_restrict(id integer, tableid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $_$
BEGIN
	IF _cm_check_value_exists($1, $2, $3, FALSE) THEN
		RAISE EXCEPTION 'CM_RESTRICT_VIOLATION';
	END IF;
END;
$_$;



CREATE FUNCTION _cm_set_attribute_comment(tableid oid, attributename text, comment text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	SubClassId oid;
BEGIN
	FOR SubClassId IN SELECT _cm_subtables_and_itself(TableId) LOOP
		EXECUTE 'COMMENT ON COLUMN '|| SubClassId::regclass ||'.'|| quote_ident(AttributeName) ||' IS '|| quote_literal(Comment);
	END LOOP;
END;
$$;



CREATE FUNCTION _cm_set_attribute_default(tableid oid, attributename text, newdefault text, updateexisting boolean) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	CurrentDefaultSrc text := _cm_get_attribute_default(TableId, AttributeName);
	NewDefaultSrc text := _cm_attribute_default_to_src(TableId, AttributeName, NewDefault);
BEGIN
    IF (NewDefaultSrc IS DISTINCT FROM CurrentDefaultSrc) THEN
    	IF (CurrentDefaultSrc IS NULL) THEN
	        EXECUTE 'ALTER TABLE ' || TableId::regclass ||
					' ALTER COLUMN ' || quote_ident(AttributeName) ||
					' SET DEFAULT ' || NewDefaultSrc;
			IF UpdateExisting THEN
	        	EXECUTE 'UPDATE '|| TableId::regclass ||' SET '|| quote_ident(AttributeName) ||' = '|| NewDefaultSrc;
	        END IF;
	    ELSE
			EXECUTE 'ALTER TABLE '|| TableId::regclass ||' ALTER COLUMN	'|| quote_ident(AttributeName) ||' DROP DEFAULT';
		END IF;
    END IF;
END;
$$;



CREATE FUNCTION _cm_setnull(id integer, tableid oid, attributename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	EXECUTE 'UPDATE '|| TableId::regclass ||
		' SET '||quote_ident(AttributeName)||' = NULL'||
		' WHERE '||quote_ident(AttributeName)||' = '||Id::text;
END;
$$;



CREATE FUNCTION _cm_split_cmname(cmname text) RETURNS text[]
    LANGUAGE sql IMMUTABLE
    AS $_$
    SELECT regexp_matches($1,E'(?:([^\\.]+)\\.)?([^\\.]+)?');
$_$;



CREATE FUNCTION _cm_subclassid(superclassid oid, cardid integer) RETURNS oid
    LANGUAGE plpgsql STABLE STRICT
    AS $$
DECLARE
	Out integer;
BEGIN
	EXECUTE 'SELECT tableoid FROM '||SuperClassId::regclass||' WHERE "Id"='||CardId||' LIMIT 1' INTO Out;
	RETURN Out;
END;
$$;



CREATE FUNCTION _cm_subtables_and_itself(tableid oid) RETURNS SETOF oid
    LANGUAGE sql
    AS $_$
	SELECT $1 WHERE _cm_is_cmobject($1)
	UNION
	SELECT _cm_subtables_and_itself(inhrelid) FROM pg_inherits WHERE inhparent = $1
$_$;



CREATE FUNCTION _cm_table_dbname(cmname text) RETURNS regclass
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_table_dbname_unsafe($1)::regclass;
$_$;



CREATE FUNCTION _cm_table_dbname_unsafe(cmname text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
	SELECT coalesce(quote_ident(_cm_cmschema($1))||'.','')||quote_ident(_cm_cmtable($1));
$_$;



CREATE FUNCTION _cm_table_id(cmname text) RETURNS oid
    LANGUAGE sql STABLE
    AS $_$
	SELECT _cm_table_dbname_unsafe($1)::regclass::oid;
$_$;



CREATE FUNCTION _cm_table_is_empty(tableid oid) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	NotFound boolean;
BEGIN
	-- Note: FOUND variable is not set on EXECUTE, so we can't use it!
	EXECUTE 'SELECT (COUNT(*) = 0) FROM '|| TableId::regclass ||' LIMIT 1' INTO NotFoun…