UserGuidelines/Admin/Upgrade: db_migration_4_gis.sh

File db_migration_4_gis.sh, 8.8 KB (added by Fran Boon, 7 years ago)

Script to update the database after the GIS schema changes: https://github.com/flavour/eden/commit/393ee22bd91a9125181c09da25a9463692326f7c

Line 
1#!/bin/bash
2
3# Script to update the database after the GIS schema changes:
4# https://github.com/flavour/eden/commit/393ee22bd91a9125181c09da25a9463692326f7c
5
6# This is designed for use with uWSGI (i.e. Cherokee)
7# - it will need minor tweaking to work with Apache/mod_WSGI (i.e. the part for stop/starting the service)
8# It has been tested with PostgreSQL and a little with MySQL
9
10# Usage:
11#wget http://eden.sahanafoundation.org/raw-attachment/wiki/UserGuidelines/Admin/Upgrade/db_migration_4_gis.sh
12#chmod +x db_migration_4_gis.sh
13#clean test # Update the Test instance with current Prod data
14#./db_migration_4_gis.sh test
15
16# Test it out & confirm that all is working for you, then repeat for Prod:
17#./db_migration_4_gis.sh prod
18
19set -e
20if [[ -z "$1" ]]; then
21 echo >&2 "Instance needs to be specified: web2py, prod or test"
22 exit 1
23elif [[ ! -d "/home/$1" ]]; then
24 echo >&2 "$1 is not a valid instance!"
25 exit 1
26fi
27INSTANCE=$1
28cd /home/$INSTANCE
29
30# Shutdown instance
31/etc/init.d/uwsgi-$INSTANCE stop
32
33# Update s3migration
34cd applications/eden/modules
35rm s3migration.py
36wget https://raw.githubusercontent.com/flavour/eden/624a0cd370624d1f44b83b3e7cca72c03096cc78/modules/s3migration.py
37
38cd /home/$INSTANCE
39rm -rf applications/eden/compiled
40#python web2py.py -S eden -M -R applications/eden/static/scripts/tools/compile.py
41
42# Back up relevant Data
43cat << EOF > "/tmp/prep.py"
44m = local_import("s3migration")
45migrate = m.S3Migration()
46migrate.prep(foreigns = [("gis_config", "symbology_id"),
47 ("org_organisation", "organisation_type_id"), # Not GIS-related, but a recent change
48 ],
49 moves={"gis_layer_arcrest":["dir", "gis_layer_config", "layer_id"],
50 "gis_layer_coordinate":["dir", "gis_layer_config", "layer_id"],
51 "gis_layer_empty":["dir", "gis_layer_config", "layer_id"],
52 "gis_layer_feature":["dir", "gis_layer_config", "layer_id"],
53 "gis_layer_geojson":["dir", "gis_layer_config", "layer_id"],
54 "gis_layer_georss":["dir", "gis_layer_config", "layer_id"],
55 "gis_layer_gpx":["dir", "gis_layer_config", "layer_id"],
56 "gis_layer_kml":["dir", "gis_layer_config", "layer_id"],
57 "gis_layer_openstreetmap":["dir", "gis_layer_config", "layer_id"],
58 "gis_layer_openweathermap":["dir", "gis_layer_config", "layer_id"],
59 "gis_layer_shapefile":["dir", "gis_layer_config", "layer_id"],
60 "gis_layer_theme":["dir", "gis_layer_config", "layer_id"],
61 "gis_layer_tms":["dir", "gis_layer_config", "layer_id"],
62 "gis_layer_wfs":["dir", "gis_layer_config", "layer_id"],
63 "gis_layer_wms":["dir", "gis_layer_config", "layer_id"],
64 },
65 news={"gis_style":{"lookup_field": "layer_id",
66 "tables": {"gis_layer_symbology": ["marker_id", "gps_marker"],
67 # NB Test JSON type change too
68 "gis_layer_config": ["style"],
69 },
70 "supers": {"gis_layer_entity": ["opacity",
71 "cluster_distance",
72 "cluster_threshold",
73 "popup_format",
74 ]
75 }
76 },
77 }
78 )
79EOF
80cd /home/$INSTANCE
81python web2py.py -S eden -R /tmp/prep.py
82
83# Update system
84cd /home/$INSTANCE/applications/eden
85git reset --hard HEAD
86git checkout master
87# Better if switching between branches
88#git pull
89git fetch --all
90git reset --hard origin/master
91
92# Migrate DB schema
93cd /home/$INSTANCE/applications/eden
94rm -rf compiled
95sed -i 's/settings.base.migrate = False/settings.base.migrate = True/g' models/000_config.py
96cd /home/$INSTANCE
97sudo -H -u web2py python web2py.py -S eden -M -R applications/eden/static/scripts/tools/noop.py
98cd /home/$INSTANCE/applications/eden
99sed -i 's/settings.base.migrate = True/settings.base.migrate = False/g' models/000_config.py
100
101# Compile
102#cd /home/$INSTANCE
103#python web2py.py -S eden -M -R applications/eden/static/scripts/tools/compile.py
104
105# Restore Data
106cat << EOF > "/tmp/post.py"
107m = local_import("s3migration")
108migrate = m.S3Migration()
109migrate.post(moves={"gis_layer_arcrest":["dir", "gis_layer_config", "layer_id"],
110 "gis_layer_coordinate":["dir", "gis_layer_config", "layer_id"],
111 "gis_layer_empty":["dir", "gis_layer_config", "layer_id"],
112 "gis_layer_feature":["dir", "gis_layer_config", "layer_id"],
113 "gis_layer_geojson":["dir", "gis_layer_config", "layer_id"],
114 "gis_layer_georss":["dir", "gis_layer_config", "layer_id"],
115 "gis_layer_gpx":["dir", "gis_layer_config", "layer_id"],
116 "gis_layer_kml":["dir", "gis_layer_config", "layer_id"],
117 "gis_layer_openstreetmap":["dir", "gis_layer_config", "layer_id"],
118 "gis_layer_openweathermap":["dir", "gis_layer_config", "layer_id"],
119 "gis_layer_shapefile":["dir", "gis_layer_config", "layer_id"],
120 "gis_layer_theme":["dir", "gis_layer_config", "layer_id"],
121 "gis_layer_tms":["dir", "gis_layer_config", "layer_id"],
122 "gis_layer_wfs":["dir", "gis_layer_config", "layer_id"],
123 "gis_layer_wms":["dir", "gis_layer_config", "layer_id"],
124 },
125 news={"gis_style":{"lookup_field": "layer_id",
126 "tables": {"gis_layer_symbology": ["marker_id", "gps_marker"],
127 # NB Test JSON type change too
128 "gis_layer_config": ["style"],
129 },
130 "supers": {"gis_layer_entity": ["opacity",
131 "cluster_distance",
132 "cluster_threshold",
133 "popup_format",
134 ]
135 }
136 },
137 }
138 )
139EOF
140cd /home/$INSTANCE
141python web2py.py -S eden -R /tmp/post.py
142
143# Extra tasks, which want -M
144cat << EOF > "/tmp/post.py"
145# Add a default marker
146ctable = s3db.gis_config
147default_config = db(ctable.uuid == "SITE_DEFAULT").select(ctable.id, limitby=(0, 1)).first().id
148mtable = s3db.gis_marker
149marker_red = db(mtable.name == "marker_red").select(mtable.id, limitby=(0, 1)).first().id
150stable = s3db.gis_style
151stable.insert(config_id=default_config, marker_id = marker_red)
152db.commit()
153db(stable.aggregate == None).update(aggregate=False)
154db.commit()
155# Migrate Popup Label/Popup Fields to Popup Format
156ftable = s3db.gis_layer_feature
157rows = db(ftable.deleted == False).select(ftable.id, ftable.layer_id, ftable.attr_fields, ftable.popup_fields, ftable.popup_label)
158for row in rows:
159 popup_fields = row.popup_fields
160 popup_label = row.popup_label
161 if popup_fields:
162 attr_fields = row.attr_fields
163 style_row = db(stable.layer_id == row.layer_id).select(stable.id, stable.popup_format, limitby=(0, 1)).first()
164 if not style_row.popup_format:
165 if popup_label:
166 popup_format = "{%s} (T('%s'))" % (popup_fields[0], popup_label)
167 else:
168 popup_format = "{%s}" % popup_fields[0]
169 for f in popup_fields[1:]:
170 popup_format = "%s<br />{%s}" % (popup_format, f)
171 # Ensure all the required attributes are present
172 if attr_fields:
173 attr_fields = list(set(attr_fields + popup_fields))
174 else:
175 attr_fields = popup_fields
176 style_row.update_record(popup_format=popup_format)
177 row.update_record(popup_fields=None, popup_label=None, attr_fields=attr_fields)
178# Remove gis_style records for WMS layers (they have an opacity so one gets created, but it shouldn't)
179wtable = s3db.gis_layer_wms
180rows = db(wtable.id > 0).select(wtable.layer_id)
181wms_layers = [r.layer_id for r in rows]
182db(stable.layer_id.belongs(wms_layers)).delete()
183# Update gis_config for an older change
184db(ctable.temp==None).update(temp=False)
185db.commit()
186EOF
187cd /home/$INSTANCE
188python web2py.py -S eden -M -R /tmp/post.py
189
190# Compile
191cd /home/$INSTANCE
192python web2py.py -S eden -M -R applications/eden/static/scripts/tools/compile.py
193
194# Restart instance
195/etc/init.d/uwsgi-$INSTANCE start