[Maposmatic-dev] [PATCH 3/3] ocitysmap/support: add support for storing the age of the OSM data

[Thomas Petazzoni]

This commit creates a new maposmatic_admin table, which contains a
single column and a single entry, used to store the age of the OSM
data that is currently inside the GIS database. This table is located
on the GIS database side (and *NOT* on the Django database side).

This table must be created once on the GIS database by running the
setup-maposmatic-admin.sql.

This table is then updated by the planet-update.sh script every time
it completes an update of the OSM data. System administrators of the
production and development servers are therefore now requested to use
the planet-update.sh script for the OcitySMap repository on the
servers, and to replicate on the Git repository any change done on
those scripts.

The contents of this table will be used by later commits to show the
replication lag on the MapOSMatic.org website and the date of the OSM
data on the generated maps.

Signed-off-by: Thomas Petazzoni <address@hidden>
---
 support/planet-update.sh           |    5 +++++
 support/setup-maposmatic-admin.sql |   12 ++++++++++++
 2 files changed, 17 insertions(+), 0 deletions(-)
 create mode 100644 support/setup-maposmatic-admin.sql

diff --git a/support/planet-update.sh b/support/planet-update.sh
index 768a15e..4eb20b5 100755
--- a/support/planet-update.sh
+++ b/support/planet-update.sh
@@ -94,6 +94,11 @@ rep=`cat ${OSMOSIS_STATE} |\
        tr 'T' ' ' |\
        xargs -I{} date --utc --date "{}" +"%Y-%m-%d %H:%M:%S"`
 log "Update complete, now at ${rep} UTC (replication lag is 
`${HOME}/replag.sh`)."
+
+# Update the maposmatic_admin table with the last update timestamp of
+# the OSM data
+echo "UPDATE maposmatic_admin SET last_update='${rep}';" | psql -h localhost 
-U maposmatic maposmatic2
+
 rm -f ${PID_FILE} ${CURRENT_OSC}
 
 exit 0
diff --git a/support/setup-maposmatic-admin.sql 
b/support/setup-maposmatic-admin.sql
new file mode 100644
index 0000000..0e1019c
--- /dev/null
+++ b/support/setup-maposmatic-admin.sql
@@ -0,0 +1,12 @@
+-- This file is used to create the maposmatic_admin table, which
+-- contains a single table, with a single column and a single value,
+-- used only to store the timestamp of the last update of the OSM
+-- database. This is used by the website to show the current
+-- replication lag with the official database, and to display on the
+-- generated maps the age of the data used for the rendering.
+
+CREATE TABLE maposmatic_admin (last_update timestamp);
+
+-- Insert dummy value so that the planet-update script only needs to
+-- do an UPDATE query.
+INSERT INTO maposmatic_admin VALUES ('1970-01-01 00:00:00');
-- 
1.7.4.1