From cb5e8c8b7ae9dc1916dc75d631717d5ab36fc383 Mon Sep 17 00:00:00 2001
From: Eugene Crosser <crosser@average.org>
Date: Thu, 26 May 2022 00:34:49 +0200
Subject: [PATCH] Opencellid download service

---
 debian/gps303.conf               |   2 +
 debian/gps303.ocid-dload.service |  12 ++++
 debian/gps303.ocid-dload.timer   |  10 +++
 debian/postinst                  |   1 +
 debian/rules                     |   1 +
 gps303/ocid_dload.py             | 116 +++++++++++++++++++++++++++++++
 6 files changed, 142 insertions(+)
 create mode 100644 debian/gps303.ocid-dload.service
 create mode 100644 debian/gps303.ocid-dload.timer
 create mode 100644 gps303/ocid_dload.py

diff --git a/debian/gps303.conf b/debian/gps303.conf
index d77b58a..3b2ca38 100644
--- a/debian/gps303.conf
+++ b/debian/gps303.conf
@@ -15,6 +15,8 @@ publishurl = ipc:///var/lib/gps303/locevt
 
 [opencellid]
 dbfn = /var/lib/opencellid/opencellid.sqlite
+downloadtoken = /var/lib/opencellid/opencellid.token
+downloadmcc = 262
 
 [termconfig]
 statusIntervalMinutes = 25
diff --git a/debian/gps303.ocid-dload.service b/debian/gps303.ocid-dload.service
new file mode 100644
index 0000000..212fb49
--- /dev/null
+++ b/debian/gps303.ocid-dload.service
@@ -0,0 +1,12 @@
+[Unit]
+Description=GPS303 OpenCellID Download Service
+Wants=gps303.ocid-dload.timer
+
+[Service]
+Type=oneshot
+EnvironmentFile=-/etc/default/gps303
+ExecStart=python3 -m gps303.ocid_dload $OPTIONS
+StandardOutput=journal
+StandardError=inherit
+User=gps303
+Group=gps303
diff --git a/debian/gps303.ocid-dload.timer b/debian/gps303.ocid-dload.timer
new file mode 100644
index 0000000..5730f65
--- /dev/null
+++ b/debian/gps303.ocid-dload.timer
@@ -0,0 +1,10 @@
+[Unit]
+Description=Timer For GPS303 OpenCellID Download Service
+Requires=gps303.ocid-dload.service
+
+[Timer]
+Unit=gps303.ocid-dload.service
+OnCalendar=Weekly
+
+[Install]
+WantedBy=timers.target
diff --git a/debian/postinst b/debian/postinst
index 668ac08..84624e5 100644
--- a/debian/postinst
+++ b/debian/postinst
@@ -2,6 +2,7 @@
 
 set -e
 adduser --system --group --home /var/lib/gps303 gps303
+install --owner gps303 --group gps303 --directory /var/lib/opencellid
 
 #DEBHELPER#
 
diff --git a/debian/rules b/debian/rules
index 63d2ee2..e2f6e32 100755
--- a/debian/rules
+++ b/debian/rules
@@ -15,3 +15,4 @@ override_dh_installsystemd:
 	dh_installsystemd --name=gps303.lookaside
 	dh_installsystemd --name=gps303.termconfig
 	dh_installsystemd --name=gps303.wsgateway
+	dh_installsystemd --name=gps303.ocid-dload
diff --git a/gps303/ocid_dload.py b/gps303/ocid_dload.py
new file mode 100644
index 0000000..aa70030
--- /dev/null
+++ b/gps303/ocid_dload.py
@@ -0,0 +1,116 @@
+import csv
+from logging import getLogger
+import requests
+from sqlite3 import connect
+from zlib import decompressobj, MAX_WBITS
+
+from . import common
+
+log = getLogger("gps303/ocid_dload")
+
+RURL = (
+    "https://opencellid.org/ocid/downloads"
+    "?token={token}&type={type}&file={mcc}.csv.gz"
+)
+
+SCHEMA = """create table if not exists cells (
+  "radio" text,
+  "mcc" int,
+  "net" int,
+  "area" int,
+  "cell" int,
+  "unit" int,
+  "lon" int,
+  "lat" int,
+  "range" int,
+  "samples" int,
+  "changeable" int,
+  "created" int,
+  "updated" int,
+  "averageSignal" int
+)"""
+DBINDEX = "create index if not exists cell_idx on cells (area, cell)"
+
+
+class unzipped:
+    """
+    File-like object that unzips http response body.
+    read(size) method returns chunks of binary data as bytes
+    When used as iterator, splits data to lines
+    and yelds them as strings.
+    """
+
+    def __init__(self, zstream):
+        self.zstream = zstream
+        self.decoder = decompressobj(16 + MAX_WBITS)
+        self.outdata = b""
+        self.line = b""
+
+    def read(self, n=None):
+        if self.decoder is None:
+            return b""
+        while len(self.outdata) < n:
+            raw_data = self.zstream.read(n)
+            self.outdata += self.decoder.decompress(raw_data)
+            if not raw_data:
+                self.decoder = None
+                break
+        if self.outdata:
+            data, self.outdata = self.outdata[:n], self.outdata[n:]
+            return data
+        return b""
+
+    def __next__(self):
+        while True:
+            splittry = self.line.split(b"\n", maxsplit=1)
+            if len(splittry) > 1:
+                break
+            moredata = self.read(256)
+            if not moredata:
+                raise StopIteration
+            self.line += moredata
+        line, rest = splittry
+        self.line = rest
+        return line.decode("utf-8")
+
+    def __iter__(self):
+        return self
+
+
+def main(conf):
+    try:
+        with open(
+            conf.get("opencellid", "downloadtoken"), encoding="ascii"
+        ) as fl:
+            token = fl.read().strip()
+    except FileNotFoundError:
+        log.warning("Opencellid access token not configured, cannot download")
+        return
+
+    mcc = conf.get("opencellid", "downloadmcc")
+    url = RURL.format(token=token, type="mcc", mcc=mcc)
+    # url = "http://localhost:8000/262.csv.gz"  # TESTING
+    dbfn = conf.get("opencellid", "dbfn")
+    count = 0
+    with requests.get(url, stream=True) as resp, connect(dbfn) as db:
+        log.debug("Requested %s, result %s", url, resp)
+        if resp.status_code != 200:
+            log.error("Error getting %s: %s", url, resp)
+            return
+        db.execute("pragma journal_mode = wal")
+        db.execute(SCHEMA)
+        db.execute("delete from cells")
+        rows = csv.reader(unzipped(resp.raw))
+        for row in rows:
+            db.execute(
+                """insert into cells
+                   values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
+                row,
+            )
+            count += 1
+        db.execute(DBINDEX)
+    log.info("repopulated %s with %d records for MCC %s", dbfn, count, mcc)
+
+
+if __name__.endswith("__main__"):
+    main(common.init(log))
-- 
2.39.2