V13.1.0.20190503__z_index_to_elements_added.sql 2.59 KB
Newer Older
1
2
3
4
-- add z-index to all elements in the database in similar way as they are drawn right now
alter table element_table add column z integer;
alter table reaction_table add column z integer;

5
6
--add artificial element outside compartment, so the update queries will always return non-null values
insert into element_table (id, element_type_db, visibility_level, width, height) values(-1, 'GENERIC_PROTEIN', 0, 1,1);
7
8
9

with v_element_table as
(
10
 select id, row_number() over (order by width*height desc, id) as rn from element_table where not element_type_db like '%COMPARTMENT%' and not element_type_db like '%PATHWAY%' and compartment_id is null and idcomplexdb is null
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
) 
update element_table set z =  v_element_table.rn
from v_element_table
where element_table.id = v_element_table.id;

with v_reaction_table as
(
 select id, row_number() over (order by id) as rn from reaction_table
) 
update reaction_table set z =  v_reaction_table.rn + (select max(z) from element_table)
from v_reaction_table
where reaction_table.id = v_reaction_table.id;

with v_element_table as
(
 select id, row_number() over (order by width*height desc, id) + (select max(z) from reaction_table)  as rn from element_table where compartment_id is null and idcomplexdb is null and z is null
) 
update element_table set z =  v_element_table.rn
from v_element_table
where element_table.id = v_element_table.id;


DO $$ 
DECLARE 
begin

  for i in 1..10 loop
    with v_element_table as
    (
     select id, row_number() over (order by width*height desc, id) + (select max(z) from element_table)  as rn from element_table where z is null and compartment_id in (select id from element_table where z is not null) and idcomplexdb is null
    ) 
    update element_table set z =  v_element_table.rn
    from v_element_table
    where element_table.id = v_element_table.id;
  end loop;

end;
$$
;

DO $$ 
DECLARE 
begin

  for i in 1..10 loop
    with v_element_table as
    (
     select id, row_number() over (order by width*height desc, id) + (select max(z) from element_table)  as rn from element_table where z is null and idcomplexdb in (select id from element_table where z is not null)
    ) 
    update element_table set z =  v_element_table.rn
    from v_element_table
    where element_table.id = v_element_table.id;
  end loop;

end;
$$
;

-- just in case 10 loops were not enough
update element_table set z = (select max(z) from element_table) where z is null;

ALTER TABLE element_table ALTER COLUMN z SET NOT NULL;
ALTER TABLE reaction_table ALTER COLUMN z SET NOT NULL;
74
75
76

--remove artificial element added at the beginning of the script
delete from element_table where id = -1;