handle deleted objects

Signed-off-by: Asbjørn Sloth Tønnesen <ast@fiberby.net>

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 )