[Tile-serving] [openstreetmap/osm2pgsql] Add a script for simple updating of a database (#1411)

[Paul Norman]

@pnorman requested changes on this pull request.

A few minor things - I want to do a bit more of a review still.

> +    with conn.cursor() as cur:
+        cur.execute("SELECT max(id) FROM {}_ways".format(prefix))
+        osmid = cur.fetchone()[0] if cur.rowcount == 1 else None
+
+        if osmid is None:
+            LOG.fatal("No data found in the database.")
+            return None
+
+    LOG.info("Using way id %d for timestamp lookup", osmid)
+    # Get the node from the API to find the timestamp when it was created.
+    node_url = 'https://www.openstreetmap.org/api/0.6/way/{}/1'.format(osmid)
+    headers = {"User-Agent" : "osm2pgsql-update"}
+    with urlrequest.urlopen(urlrequest.Request(node_url, headers=headers)) as response:
+        data = response.read().decode('utf-8')
+
+    match = re.search(r'timestamp="((\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2}))Z"', data)

Let's not parse XML with regular expressions.

We could get this with a HEAD request and the last-modified header

> +                        sequence INTEGER,
+                        importdate TIMESTAMP WITH TIME ZONE)
+                    """.format(table))
+        cur.execute("INSERT INTO {} VALUES(%s, %s, %s)".format(table),
+                    (base_url, seq, date))
+    conn.commit()
+
+
+def update_replication_state(conn, table, seq, date):
+    """ Update sequence and date in the replication state table.
+        The table is assumed to exist.
+    """
+    with conn.cursor() as cur:
+        if date is not None:
+            cur.execute("""UPDATE {} SET sequence=%s, importdate=%s""".format(table),
+                        (seq, date))

Quote {} with `"` for safety

> +    if match is None:
+        LOG.fatal("The way data downloaded from the API does not contain valid data.\n"
+                  "URL used: %s", node_url)
+        return None
+
+    LOG.debug("Found timestamp %s", match[1])
+
+    return dt.datetime.fromisoformat(match[1]).replace(tzinfo=dt.timezone.utc)
+
+
+def setup_replication_state(conn, table, base_url, seq, date):
+    """ (Re)create the table for the replication state and fill it with
+        the given state.
+    """
+    with conn.cursor() as cur:
+        cur.execute('DROP TABLE IF EXISTS {}'.format(table))

Quote {} with `"` for safety

> +        LOG.fatal("The way data downloaded from the API does not contain valid data.\n"
+                  "URL used: %s", node_url)
+        return None
+
+    LOG.debug("Found timestamp %s", match[1])
+
+    return dt.datetime.fromisoformat(match[1]).replace(tzinfo=dt.timezone.utc)
+
+
+def setup_replication_state(conn, table, base_url, seq, date):
+    """ (Re)create the table for the replication state and fill it with
+        the given state.
+    """
+    with conn.cursor() as cur:
+        cur.execute('DROP TABLE IF EXISTS {}'.format(table))
+        cur.execute("""CREATE TABLE {}

Quote {} with `"` for safety

> +
+    return dt.datetime.fromisoformat(match[1]).replace(tzinfo=dt.timezone.utc)
+
+
+def setup_replication_state(conn, table, base_url, seq, date):
+    """ (Re)create the table for the replication state and fill it with
+        the given state.
+    """
+    with conn.cursor() as cur:
+        cur.execute('DROP TABLE IF EXISTS {}'.format(table))
+        cur.execute("""CREATE TABLE {}
+                       (url TEXT,
+                        sequence INTEGER,
+                        importdate TIMESTAMP WITH TIME ZONE)
+                    """.format(table))
+        cur.execute("INSERT INTO {} VALUES(%s, %s, %s)".format(table),

Quote {} with `"` for safety

> +                    """.format(table))
+        cur.execute("INSERT INTO {} VALUES(%s, %s, %s)".format(table),
+                    (base_url, seq, date))
+    conn.commit()
+
+
+def update_replication_state(conn, table, seq, date):
+    """ Update sequence and date in the replication state table.
+        The table is assumed to exist.
+    """
+    with conn.cursor() as cur:
+        if date is not None:
+            cur.execute("""UPDATE {} SET sequence=%s, importdate=%s""".format(table),
+                        (seq, date))
+        else:
+            cur.execute('UPDATE {} SET sequence=%s'.format(table),

Quote {} with `"` for safety

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/pull/1411#pullrequestreview-583833998
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20210204/a8222461/attachment.htm>