| 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 |
|
|---|
| 19 | set -e
|
|---|
| 20 | if [[ -z "$1" ]]; then
|
|---|
| 21 | echo >&2 "Instance needs to be specified: web2py, prod or test"
|
|---|
| 22 | exit 1
|
|---|
| 23 | elif [[ ! -d "/home/$1" ]]; then
|
|---|
| 24 | echo >&2 "$1 is not a valid instance!"
|
|---|
| 25 | exit 1
|
|---|
| 26 | fi
|
|---|
| 27 | INSTANCE=$1
|
|---|
| 28 | cd /home/$INSTANCE
|
|---|
| 29 |
|
|---|
| 30 | # Shutdown instance
|
|---|
| 31 | /etc/init.d/uwsgi-$INSTANCE stop
|
|---|
| 32 |
|
|---|
| 33 | # Update s3migration
|
|---|
| 34 | cd applications/eden/modules
|
|---|
| 35 | rm s3migration.py
|
|---|
| 36 | wget https://raw.githubusercontent.com/flavour/eden/624a0cd370624d1f44b83b3e7cca72c03096cc78/modules/s3migration.py
|
|---|
| 37 |
|
|---|
| 38 | cd /home/$INSTANCE
|
|---|
| 39 | rm -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
|
|---|
| 43 | cat << EOF > "/tmp/prep.py"
|
|---|
| 44 | m = local_import("s3migration")
|
|---|
| 45 | migrate = m.S3Migration()
|
|---|
| 46 | migrate.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 | )
|
|---|
| 79 | EOF
|
|---|
| 80 | cd /home/$INSTANCE
|
|---|
| 81 | python web2py.py -S eden -R /tmp/prep.py
|
|---|
| 82 |
|
|---|
| 83 | # Update system
|
|---|
| 84 | cd /home/$INSTANCE/applications/eden
|
|---|
| 85 | git reset --hard HEAD
|
|---|
| 86 | git checkout master
|
|---|
| 87 | # Better if switching between branches
|
|---|
| 88 | #git pull
|
|---|
| 89 | git fetch --all
|
|---|
| 90 | git reset --hard origin/master
|
|---|
| 91 |
|
|---|
| 92 | # Migrate DB schema
|
|---|
| 93 | cd /home/$INSTANCE/applications/eden
|
|---|
| 94 | rm -rf compiled
|
|---|
| 95 | sed -i 's/settings.base.migrate = False/settings.base.migrate = True/g' models/000_config.py
|
|---|
| 96 | cd /home/$INSTANCE
|
|---|
| 97 | sudo -H -u web2py python web2py.py -S eden -M -R applications/eden/static/scripts/tools/noop.py
|
|---|
| 98 | cd /home/$INSTANCE/applications/eden
|
|---|
| 99 | sed -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
|
|---|
| 106 | cat << EOF > "/tmp/post.py"
|
|---|
| 107 | m = local_import("s3migration")
|
|---|
| 108 | migrate = m.S3Migration()
|
|---|
| 109 | migrate.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 | )
|
|---|
| 139 | EOF
|
|---|
| 140 | cd /home/$INSTANCE
|
|---|
| 141 | python web2py.py -S eden -R /tmp/post.py
|
|---|
| 142 |
|
|---|
| 143 | # Extra tasks, which want -M
|
|---|
| 144 | cat << EOF > "/tmp/post.py"
|
|---|
| 145 | # Add a default marker
|
|---|
| 146 | ctable = s3db.gis_config
|
|---|
| 147 | default_config = db(ctable.uuid == "SITE_DEFAULT").select(ctable.id, limitby=(0, 1)).first().id
|
|---|
| 148 | mtable = s3db.gis_marker
|
|---|
| 149 | marker_red = db(mtable.name == "marker_red").select(mtable.id, limitby=(0, 1)).first().id
|
|---|
| 150 | stable = s3db.gis_style
|
|---|
| 151 | stable.insert(config_id=default_config, marker_id = marker_red)
|
|---|
| 152 | db.commit()
|
|---|
| 153 | db(stable.aggregate == None).update(aggregate=False)
|
|---|
| 154 | db.commit()
|
|---|
| 155 | # Migrate Popup Label/Popup Fields to Popup Format
|
|---|
| 156 | ftable = s3db.gis_layer_feature
|
|---|
| 157 | rows = db(ftable.deleted == False).select(ftable.id, ftable.layer_id, ftable.attr_fields, ftable.popup_fields, ftable.popup_label)
|
|---|
| 158 | for 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)
|
|---|
| 179 | wtable = s3db.gis_layer_wms
|
|---|
| 180 | rows = db(wtable.id > 0).select(wtable.layer_id)
|
|---|
| 181 | wms_layers = [r.layer_id for r in rows]
|
|---|
| 182 | db(stable.layer_id.belongs(wms_layers)).delete()
|
|---|
| 183 | # Update gis_config for an older change
|
|---|
| 184 | db(ctable.temp==None).update(temp=False)
|
|---|
| 185 | db.commit()
|
|---|
| 186 | EOF
|
|---|
| 187 | cd /home/$INSTANCE
|
|---|
| 188 | python web2py.py -S eden -M -R /tmp/post.py
|
|---|
| 189 |
|
|---|
| 190 | # Compile
|
|---|
| 191 | cd /home/$INSTANCE
|
|---|
| 192 | python web2py.py -S eden -M -R applications/eden/static/scripts/tools/compile.py
|
|---|
| 193 |
|
|---|
| 194 | # Restart instance
|
|---|
| 195 | /etc/init.d/uwsgi-$INSTANCE start
|
|---|
