find LCOM metric (all pairs of a class's methods that are not related)

Question

I have 4 tables in PostgreSQL: class, method, attribute and attributeaccess.

Each method row and each attribute row has 1 class ID named cid.

Each attributeaccess row has 1 method ID named mid and 1 attribute ID named aid. the data model is shown in below:

and the create queries are:

CREATE TABLE IF NOT EXISTS public.class
(
    id bigint NOT NULL DEFAULT nextval('class_id_seq'::regclass),
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT class_pkey PRIMARY KEY (id)
)
CREATE TABLE IF NOT EXISTS public.method
(
    id bigint NOT NULL DEFAULT nextval('method_id_seq'::regclass),
    name character varying(200) COLLATE pg_catalog."default",
    cid bigint,
    CONSTRAINT method_pkey PRIMARY KEY (id),
    CONSTRAINT method_cid_fkey FOREIGN KEY (cid)
        REFERENCES public.class (id) 
)
CREATE TABLE IF NOT EXISTS public.attribute
(
    id bigint NOT NULL DEFAULT nextval('attribute_id_seq'::regclass),
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    cid bigint,
    CONSTRAINT attribute_pkey PRIMARY KEY (id),
    CONSTRAINT attribute_cid_fkey FOREIGN KEY (cid)
        REFERENCES public.class (id) 
)
CREATE TABLE IF NOT EXISTS public.attributeaccess
(
    id bigint NOT NULL DEFAULT nextval('attributeaccess_id_seq'::regclass),
    mid bigint,
    aid bigint,
    CONSTRAINT attributeaccess_aid_fkey FOREIGN KEY (aid)
        REFERENCES public.attribute (id),
    CONSTRAINT attributeaccess_mid_fkey FOREIGN KEY (mid)
        REFERENCES public.method (id) 
)

I need to find LCOM metric: all pairs of a class's methods (for example class id=1), that are not related through the attributeaccess. (there is not any common attribute for any pairs of methods)

for example for class id=1:

we have 3 methods:

id	name
1	m1
2	m2
3	m3

and 4 attributes:

id	name
1	a1
2	a2
3	a3
4	a4

and in the attributeaccess we have 5 attributeaccess:

id	mid	aid
1	m1	a1
2	m1	a2
3	m2	a2
4	m2	a3
5	m3	a4

so, LCOM is 2 and pairs are: (m1,m3), (m2,m3)

because: m1 access a1 and a2, and m3 access a4, there is not any commons in attributes.

m2 access a2 and a3, and m3 access a4, there is not any commons in attributes.

but:

m1 access a1 and a2, and m2 access a2 and a3, but a2 in common.

how can find all method pairs that have not common attribute in attributeaccess with PL/pgSQL for one class (eg: class id=1)?

Answer 1

You can collect all attributes accessed by each method into an array using array_agg() then check for overlaps between these arrays using && operator, filtering out methods accessing common attributes.

Pair (m1,m3) should be equal to (m3,m1) - the (least,greatest) order in each pair rewrites both as (m1,m3) offering deduplication with a plain distinct: demo

with
 attributes_per_method as (
    select m.cid,
           m.name,
           array_agg(aa.aid) as accessed_attributes
    from method m inner join attributeaccess aa
      on m.id=aa.mid
    where m.cid in (1) --specify targeted classes or remove to target all
    group by m.cid,
             m.name )
,pairs_per_class as (
    select distinct apm1.cid,
           array[least(apm1.name,apm2.name),greatest(apm1.name,apm2.name)] pair
    from attributes_per_method as apm1 inner join
         attributes_per_method as apm2
            on apm1.cid=apm2.cid
            and apm1.name<>apm2.name
            and not apm1.accessed_attributes && apm2.accessed_attributes )
select cid,
       count(*) as "LCOM",
       array_agg(pair) pairs
from pairs_per_class
group by cid;
-- cid | LCOM |       pairs
-------+------+-------------------
--   1 |    2 | {{m1,m3},{m2,m3}}

The plain SQL above can be wrapped in a PL/pgSQL routine, but it's not necessary.