From c59f1d55b8942e41f96f868e39edff67c47434e2 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Asbj=C3=B8rn=20Sloth=20T=C3=B8nnesen?= <ast@fiberby.net>
Date: Sat, 7 Sep 2019 13:15:52 +0000
Subject: handle deleted objects
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Signed-off-by: Asbjørn Sloth Tønnesen <ast@fiberby.net>
---
 README.md |  9 +++------
 sync.py   | 15 +++++++++++++--
 2 files changed, 16 insertions(+), 8 deletions(-)

diff --git a/README.md b/README.md
index 814d561..4d613c8 100644
--- a/README.md
+++ b/README.md
@@ -43,17 +43,14 @@ SELECT
 	data->>'irr_as_set',
 	(data->>'info_prefixes4')::int pfx4,
 	(data->>'info_prefixes6')::int pfx6
-FROM peeringdb.net ORDER BY asn;
+FROM peeringdb.net
+WHERE deleted IS NULL
+ORDER BY asn;
 ```
 
 Note: if you add an `info_prefixes4` column to the schema,
 then it will automatically be used, just like the `asn` column.
 
-Known issues
-------------
-
-* Objects are not deleted when they are deleted from PeeringDB.
-
 Disclaimer
 ----------
 
diff --git a/sync.py b/sync.py
index 5f1c3b3..c256147 100755
--- a/sync.py
+++ b/sync.py
@@ -51,9 +51,16 @@ def test_table(table_name):
         raise
 
 def gen_sql(table_name, cols):
-    updates = [sql.Identifier(col) + sql.SQL(' = EXCLUDED.') + sql.Identifier(col) for col in cols[1:]]
+    updates = []
+    for col in cols[1:]:
+        if col == 'updated':
+            stmt = sql.SQL('updated = CASE WHEN EXCLUDED.deleted IS NOT NULL THEN t.updated ELSE EXCLUDED.updated END')
+        else:
+            stmt = sql.SQL('{} = EXCLUDED.{}').format(sql.Identifier(col), sql.Identifier(col))
+        updates.append(stmt)
+
     comp = sql.SQL("""
-            INSERT INTO {}.{}
+            INSERT INTO {}.{} AS t
                 ({})
                 VALUES ({})
                 ON CONFLICT ({})
@@ -76,6 +83,8 @@ def update_object(kind, obj):
         else:
             t[key] = None
     t['data'] = json.dumps(obj)
+    if t['status'] == 'deleted':
+        t['deleted'] = t['updated']
     cur = conn.cursor()
     try:
         cur.execute(comp, t)
@@ -96,6 +105,8 @@ def fetch_objects(s, kind, extra_params):
     endpoint = f'https://peeringdb.com/api/{kind:s}'
     params = {
             'depth': 0,
+            'status__in': 'ok,pending,deleted',
+            'since': 1,
             **extra_params }
 
     r = s.get(endpoint, params=params )
-- 
cgit v1.2.1