· 6 years ago · May 22, 2019, 03:24 AM
1-- Returns all slots associated with an object
2create or replace function object_slots(object_id varchar) returns table(id varchar,
3 ord int,
4 name varchar,
5 slot_type varchar,
6 value numeric,
7 element varchar,
8 mass numeric,
9 closure numeric) as $$
10 declare
11 inherited varchar[];
12 begin
13 inherited := array( select all_inherited(object_id) );
14 return query select * from slots s where s.id = any(inherited) order by ord;
15 end;
16$$ language plpgsql;
17
18-- Returns all plugs associated with an object
19create or replace function object_plugs(object_id varchar) returns table(id varchar,
20 ord int,
21 name varchar,
22 slot_type varchar,
23 value numeric,
24 element varchar,
25 mass numeric) as $$
26 declare
27 inherited varchar[];
28 begin
29 inherited := array( select all_inherited(object_id) );
30 return query select * from plugs p where p.id = any(inherited) order by ord;
31 end;
32$$ language plpgsql;
33
34/* Returns a table with the values needed to link a pair of objects as parent and child.
35 Mandatory fields in the input are the parent and child ids.
36 Optional fields are: (slot_type or slot_name) and (plug_type or plug_name).
37 */
38create or replace function prepare_parent_link(input jsonb) returns table (success boolean, slot_name varchar) as $$
39 declare
40 parent_slots record; child_plugs record;
41 winner_slots record; winner_plugs record;
42 available_space numeric; required_space numeric;
43 winner_slot_name varchar;
44 sn varchar; cid varchar; sz numeric; csz numeric; st varchar; sval numeric;
45 begin
46 if not exists input->>'child' or not exists input->>'parent'
47
48 /* Input must have keys 'child' and 'parent' */
49
50 perform log_event('info', concat( 'prepare_parent_link ', jsonb_pretty(input) ), 'No child or parent provchilded');
51 raise exception 'No child or parent provchilded';
52 return query select false as success;
53 else
54
55 /* Gather all candidates to fill the link requirements */
56
57 -- Fill the slots records using whatever is available
58 if exists input->'slot_name'
59 select object_slots(input->>'parent') where name = input->'slot_name' into parent_slots;
60 elseif exists input->'slot_type'
61 select object_slots(input->>'parent') where slot_type = input->'slot_type' into parent_slots;
62 else
63 select object_plugs(input->>'parent') into parent_slots;
64 end if;
65
66 -- Fill the plugs records
67 if exists input->'plug_name'
68 select object_plugs(input->>'child') where name = input->'plug_name' into child_plugs;
69 elseif exists input->'plug_type'
70 select object_plugs(input->>'child') where slot_type = input->'plug_type' into child_plugs;
71 else
72 select object_plugs(input->>'child') into child_plugs;
73 end if;
74
75 /* Test for slot_type matches and free space */
76
77 -- Get the parent slots that match some child plug
78 select * from parent_slots p where p.slot_type = any( array(select distinct slot_type from child_plugs) ) order by ord into winner_slots;
79
80 -- Check for empty space in each slot
81 foreach sn in array(select name from winner slots) loop
82 sval := select value from winner_slots where name = sn;
83 if (sval = -1)
84 name = sn;
85 exit;
86 end if;
87 st := select slot_type from winner_slots where name = sn;
88 -- Sum the size of all childs already located in this slot
89 sz := 0.0;
90 foreach cid in array(select id from links where target = input->'parent' and name = sn) loop
91 csz := select value from object_plugs(cid) where slot_type = st;
92 sz += csz;
93 end loop;
94 -- Substract from the slot capacity
95 available_space := sval - sz;
96 -- and compare with the plug's size
97 required_space := select value from child_plugs where slot_type = st limit 1;
98 if (available_space >= required_space)
99 name = sn;
100 exit;
101 end if;
102 end loop;
103
104 /* Hopefully, if sn exists, we have succeeded */
105
106 if exists sn
107 -- All ok, return the checked slot name
108 return query select (true, sn) as (success, slot_name);
109 else
110 -- No plug could fit in
111 return query select false as success;
112 end if;
113 end if;
114 end;
115$$ language plpgsql;