· 7 years ago · Jan 13, 2019, 03:30 AM
1/* Helper functions */
2
3-- Returns the parent of arg, and the matching slot_name
4drop function if exists get_parent_link;
5create or replace function get_parent_link(arg varchar) returns table (id varchar, slot_name varchar) as $$
6 begin
7 return query
8 select l.target id, l.name slot_name from links l where
9 l.id = arg and l.type = 'parent';
10 end;
11$$ language plpgsql;
12
13drop function if exists get_classes;
14create or replace function get_classes(arg varchar) returns table (id varchar) as $$
15 declare
16 our_table varchar;
17 begin
18 select e.in_table from everything e where e.id = arg into our_table;
19 return query
20 execute format('select unnest(o.classes) from %s o where o.id = ''%s'' ', our_table, arg);
21 end;
22$$ language plpgsql;
23
24drop function if exists get_classes_recursive;
25create or replace function get_classes_recursive(arg varchar) returns table (id varchar) as $$
26 begin
27 return query with recursive my_recursive_table(current_id) as (
28 values (arg) -- valor inicial de current_id
29 union
30 select get_classes(current_id) from my_recursive_table where current_id is not null
31 ) select current_id as id from my_recursive_table rt
32; -- where current_id != arg; -- TODO: chequear que current_id no sea un objeto
33 end;
34$$ language plpgsql;
35
36-- Returns the tags for all classes inherited by the object
37drop function if exists get_tags;
38create or replace function get_tags(arg varchar) returns table (tag varchar) as $$
39 DECLARE
40 r record;
41 our_table varchar;
42 begin
43 for r in (select c.id from get_classes_recursive(arg) c)
44 loop
45 select e.in_table from everything e where e.id = r.id into our_table;
46 return query
47 execute format('select unnest(o.tags) from %s o where o.id = ''%s'' ', our_table, r.id);
48 end loop;
49 end;
50$$ language plpgsql;
51
52drop function if exists get_elements;
53create or replace function get_elements(arg varchar) returns table (id varchar) as $$
54 DECLARE
55 r record;
56 our_table varchar;
57 begin
58 for r in (select c.id from get_classes_recursive(arg) c)
59 loop
60 return query
61 select slot.element from slots slot where slot.id = r.id and slot.element != null;
62 return query
63 select plug.element from plugs plug where plug.id = r.id and plug.element != null;
64 return query
65 select slice.element from slices slice where slice.id = r.id;
66 end loop;
67 end;
68$$ language plpgsql;
69
70drop function if exists get_elements_recursive;
71create or replace function get_elements_recursive(arg varchar) returns table (id varchar) as $$
72 declare
73 r record;
74 begin
75 for r in (select e.id from get_elements(arg) e)
76 loop
77 return query
78 select c.id from get_classes_recursive(r.id) c;
79 end loop;
80 end;
81$$ language plpgsql;
82
83drop function if exists get_element_tags;
84create or replace function get_element_tags(arg varchar) returns table (tag varchar) as $$
85 declare
86 r record;
87 begin
88 for r in (select e.id from get_elements_recursive(arg) e)
89 loop
90 return query
91 select t.tag from get_tags(r.id) t;
92 end loop;
93 end;
94$$ language plpgsql;
95
96drop function if exists get_all_tags;
97create or replace function get_all_tags(arg varchar) returns table (tag varchar) as $$
98 begin
99 return query
100 select t.tag from get_tags(arg) t;
101 return query
102 select e.tag from get_element_tags(arg) e;
103 end;
104$$ language plpgsql;
105
106/* Queries
107 *
108 * All queries accept one argument, id varchar, and return a single-column table,
109 * containing the ids of the objects that match the query.
110 */
111
112drop function if exists get_parent;
113create or replace function get_parent(arg varchar) returns table (id varchar) as $$
114 begin
115 return query
116 select l.target id from links l where
117 l.id = arg and l.type = 'parent';
118 end;
119$$ language plpgsql;
120
121-- Returns all object that has the same parent as arg
122drop function if exists get_all_in_parent;
123create or replace function get_all_in_parent(arg varchar) returns table (id varchar) as $$
124 declare
125 parent_id varchar;
126 begin
127 select p.id from get_parent(arg) p into parent_id;
128 return query
129 select l.id from links l where
130 l.target = parent_id and l.type = 'parent' and l.id != arg;
131 end;
132$$ language plpgsql;
133
134-- Returns all objects that have arg as parent
135drop function if exists get_all_in_self;
136create or replace function get_all_in_self(arg varchar) returns table (id varchar) as $$
137 begin
138 return query
139 select l.id from links l where
140 l.target = arg and l.type = 'parent';
141 end;
142$$ language plpgsql;
143
144-- Returns arg's parent, arg's parent's parent, and so, until the object 'world'
145drop function if exists get_all_parents_recursive;
146create or replace function get_all_parents_recursive(arg varchar) returns table (id varchar) as $$
147 begin
148 return query with recursive my_recursive_table(current_id) as (
149 values (arg) -- valor inicial de current_id
150 union all
151 select get_parent(current_id) from my_recursive_table where current_id is not null
152 ) select current_id as id from my_recursive_table rt where
153 current_id != arg;
154 end;
155$$ language plpgsql;
156
157/* Tests
158 *
159 * All tests read a single-column table (id varchar) and return a table with
160 * the objects passing the test. Arguments vary; all functions accept an optional
161 * (amount numeric) argument, which defaults to 1.
162 */
163
164/* TODO:
165
166Queries:
167
168get_all_leafs_in_self
169get_all_in_self_recursive
170get_all_in_parent_slot
171get_all_in_parents_children
172get_all_in_parents_slot_children
173get_all_in_parent_recursive
174get_all_in_parent_slot_recursive
175get_all_parents_recursive
176get_tile
177get_all_tiles_children
178get_all_tiles_children_recursive
179get_all_tiles_neighbours
180get_all_in_tiles_neighbours
181get_all_in_tiles_neighbours_recursive
182get_all_tiles_far_neighbours
183get_all_in_tiles_far_neighbours
184get_all_in_tiles_far_neighbours_recursive
185
186Checks:
187
188has_tag(tag [, amount])
189has_class(class [, amount])
190has_slot(slot_type [, amount])
191has_plug(slot_type [, amount])
192has_element(element [, amount])
193has_attr(attr [, amount])
194test_attr_boundary(type, attr, value [, amount])
195test_extended_attr_boundary(type, extended_attr, value [, amount])
196
197*/