aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--README.md9
-rwxr-xr-xsync.py15
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 )