Project

General

Profile

Snippets » History » Version 56

Alexander Watzinger, 2020-01-10 15:30

1 1 Alexander Watzinger
{{toc}}
2
3
Below are some unsorted database related code snippets used in development.
4
5
h1. Exports
6
7
h2. Database Structure
8
9 49 Alexander Watzinger
Export the database structure from the test database (used to avoid specialties in production databases) into install/1_structure.sql
10
11 1 Alexander Watzinger
<pre>
12 49 Alexander Watzinger
pg_dump -sc --if-exists -n model -n gis -n log -n web -n import openatlas_test > install/1_structure.sql
13 1 Alexander Watzinger
</pre>
14
15
h2. Model Data
16
17
<pre>
18 53 Alexander Watzinger
pg_dump openatlas --inserts -a -t model.class -t model.class_i18n -t model.class_inheritance -t model.property -t model.property_i18n -t model.property_inheritance > install/3_data_model.sql
19 1 Alexander Watzinger
</pre> 
20
21
h2. Single Schema
22
23
<pre>
24
pg_dump -n web openatlas > /tmp/openatlas_web.sql
25
pg_dump -n model openatlas > /tmp/openatlas_web.sql
26
</pre>
27
28 56 Alexander Watzinger
h1. Immport database to Windows
29 1 Alexander Watzinger
30 56 Alexander Watzinger
Make a dump with inserts
31
<pre>
32
pg_dump --attribute-inserts openatlas > openatlas.sql
33
</pre>
34
35 55 Christoph Hoffmann
In the pgadmin tool, select the corresponding database, run the _query tool_ and select the _open file_ option.
36 1 Alexander Watzinger
Load the provided dump file and execute (F5) it
37 56 Alexander Watzinger
38
Login and adapt user as needed.
39 55 Christoph Hoffmann
40 1 Alexander Watzinger
h1. Recursive Events
41
42 50 Alexander Watzinger
To get all child events of a given event the SQL below (replace ROOT_EVENT_ID at bottom). It works but is slow and could be improved.
43 47 Bernhard Koschiček-Krombholz
44
<pre><code class="sql">
45 1 Alexander Watzinger
WITH RECURSIVE tree AS (
46
  SELECT e.id, ARRAY[]::INTEGER[] AS ancestors
47
  FROM model.entity e
48
  WHERE (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117')) IS NULL 
49
  UNION ALL 
50
  SELECT e.id, tree.ancestors || 
51
    (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117'))
52
  FROM model.entity e, tree
53
  WHERE (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117')) = tree.id
54
)
55
SELECT * FROM tree WHERE ROOT_EVENT_ID = ANY(tree.ancestors);
56
57 50 Alexander Watzinger
</code></pre>
58
59
h1. CONNEC 3.20.0 update scripts
60
61
Transform all events with type letter exchange to move event:
62
63
<pre><code class="sql">
64
UPDATE model.entity SET class_code = 'E9' WHERE id IN (
65
    SELECT e.id FROM model.entity e
66
    JOIN model.link l ON e.id = l.domain_id AND l.range_id = 639 AND e.class_code = 'E7');
67 47 Bernhard Koschiček-Krombholz
</code></pre>
68 51 Alexander Watzinger
69
Update all move locations to start locations:
70
71
<pre><code class="sql">
72
UPDATE model.link SET property_code = 'P27' WHERE id IN (
73
    SELECT l.id FROM model.link l
74
    JOIN model.entity e ON l.domain_id = e.id AND l.property_code = 'P7' AND e.class_code = 'E9');
75
</code></pre>
76 52 Alexander Watzinger
77
h1. CONNEC 3.20.1 update scripts
78
79
Remove actors from move events and add them to source
80
81
<pre><code class="sql">
82
BEGIN;
83
84
UPDATE model.entity SET class_code = 'E9' WHERE id IN (
85
    SELECT e.id FROM model.entity e
86
    JOIN model.link l ON e.id = l.domain_id AND l.range_id = 939 AND e.class_code = 'E7');
87
    
88
    
89
UPDATE model.link SET property_code = 'P27' WHERE id IN (
90
    SELECT l.id FROM model.link l
91
    JOIN model.entity e ON l.domain_id = e.id AND l.property_code = 'P7' AND e.class_code = 'E9');
92
93
94
INSERT INTO model.link (domain_id, range_id, property_code) 
95
    SELECT el.domain_id, l.range_id, 'P67' FROM model.link l
96
    JOIN model.entity e ON l.domain_id = e.id AND l.type_id IN (862, 1091, 943, 1046, 1045)
97
    JOIN model.link el ON e.id = el.range_id AND el.property_code = 'P67'
98
    JOIN model.entity s ON el.domain_id = s.id AND s.class_code = 'E33';
99
  
100
DELETE FROM model.link WHERE id in (
101
    SELECT l.id FROM model.link l
102
      JOIN model.entity e ON l.domain_id = e.id AND l.type_id IN (862, 1091, 943, 1046, 1045)
103
      JOIN model.link el ON e.id = el.range_id AND el.property_code = 'P67'
104
      JOIN model.entity s ON el.domain_id = s.id AND s.class_code = 'E33'
105
);
106
 
107
COMMIT;
108
</code></pre>
109
110
Remove description form move events and add it to source description.
111
112
<pre><code class="sql">
113
BEGIN;
114
115
UPDATE model.entity s SET description = description || E'\r\n----\r\n' || (
116
	SELECT e.description
117
	FROM model.entity e
118
	JOIN model.link l ON e.id = l.range_id AND l.property_code = 'P67' and e.class_code = 'E9' AND l.domain_id = s.id AND e.id NOT IN (1672, 1617, 1612, 1673, 1674, 1433, 1421, 1663, 1619, 1435, 1444, 1443, 1596, 1603, 826, 1496, 1493))
119
WHERE id IN (
120
	SELECT l.domain_id
121
	FROM model.entity e
122
	JOIN model.link l ON e.id = l.range_id AND l.property_code = 'P67' and e.class_code = 'E9' AND e.id NOT IN (1672, 1617, 1612, 1673, 1674, 1433, 1421, 1663, 1619, 1435, 1444, 1443, 1596, 1603, 826, 1496, 1493));
123
	
124
UPDATE model.entity SET description = ''
125
WHERE id IN (
126
	SELECT l.range_id
127
	FROM model.entity e
128
	JOIN model.link l ON e.id = l.range_id AND l.property_code = 'P67' and e.class_code = 'E9' AND e.id NOT IN (1672, 1617, 1612, 1673, 1674, 1433, 1421, 1663, 1619, 1435, 1444, 1443, 1596, 1603, 826, 1496, 1493));	
129
130
COMMIT;
131
</code></pre>
132 54 Alexander Watzinger
133
Join references
134
135
<pre><code class="sql">
136
UPDATE model.link SET domain_id = 3204
137
WHERE property_code = 'P67' AND domain_id IN 
138
(3216, 3218, 3222, 3230, 3234, 3238, 3285, 3287, 3705, 3713, 3715, 3719, 3745, 3815, 3845, 3849, 3853, 4272, 4276, 4281, 4286, 4293, 4297, 4301, 4367, 4369, 4374, 4494, 4731, 4735, 4742, 4926, 5130, 5132, 5137, 5141, 5148, 5149, 5154, 5158, 5232, 5236, 5240, 5245, 5264, 5269, 5273, 5277, 5281, 5286, 5290, 5294, 5295, 5299, 5303, 5307, 5311, 5315, 5319);
139
140
DELETE FROM model.entity WHERE id IN
141
(3216, 3218, 3222, 3230, 3234, 3238, 3285, 3287, 3705, 3713, 3715, 3719, 3745, 3815, 3845, 3849, 3853, 4272, 4276, 4281, 4286, 4293, 4297, 4301, 4367, 4369, 4374, 4494, 4731, 4735, 4742, 4926, 5130, 5132, 5137, 5141, 5148, 5149, 5154, 5158, 5232, 5236, 5240, 5245, 5264, 5269, 5273, 5277, 5281, 5286, 5290, 5294, 5295, 5299, 5303, 5307, 5311, 5315, 5319);
142
</code></pre>