update_clair.py

import psycopg2
import psycopg2.extras

HOST = "localhost"
PASSWORD = "password"

conn = psycopg2.connect(host=HOST, user="clair", password=PASSWORD, database="clair", port="5432")
print("Database opened successfully")

cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

cur.execute("SELECT id FROM namespace WHERE name='enterprise:8'")
enterprise_id = cur.fetchone()['id']

cur.execute("SELECT id FROM namespace WHERE name='centos:8'")
centos_id = cur.fetchone()['id']


cur.execute("""SELECT * from feature as f
    INNER JOIN featureversion fv on f.id = fv.feature_id
    WHERE f.namespace_id=%d""" % enterprise_id)
enterprise_fvs = cur.fetchall()

update_featureversion = {}
update_feature = {}
update_namspace = {}

# 3 possibilities
# 1. featureversion exists in both enterprise and centos (exact match)
#    - update `layer_diff_featureversion` table to point to the centos `featureversion`
# 2. feature exists in enterprise and centos but featureversion exists only in enterprise
#    - update featureversion table to point to the centos `feature`
# 3. feature exists in enterprise only
#    - update feature table to point to the centos `namespace`

for e_fv in enterprise_fvs:
    cur.execute("""SELECT * from feature as f
        INNER JOIN featureversion fv on f.id = fv.feature_id
        WHERE f.name='%s' AND f.namespace_id=%d AND fv.version='%s'""" %
            (e_fv['name'], centos_id, e_fv['version']))

    c_fv = cur.fetchall()
    if c_fv:
        assert len(c_fv) == 1, c_fv
        print("featureversion exists in enterprise and centos: %s" % (e_fv))
        update_featureversion[e_fv['id']] = c_fv[0]['id']
        new_fv_id = c_fv[0]['id']
        old_fv_id = e_fv['id']
        cur.execute("UPDATE layer_diff_featureversion SET featureversion_id=%d WHERE featureversion_id=%d" % (new_fv_id, old_fv_id))
        cur.commit()

    else:
        cur.execute("""SELECT * from feature where name='%s'""" % (e_fv['name']))
        c_f = cur.fetchall()
        if c_f:
            print("feature exists in enterprise and centos but featureversion exists only in enterprise", e_fv)
            update_feature[e_fv['feature_id']] = c_f[0]['id']
            new_f_id = c_f[0]['id']
            old_f_id = e_fv['feature_id']
            cur.execute("UPDATE featureversion SET feature_id=%d WHERE feature_id=%d" % (new_f_id, old_f_id))
            cur.commit()
        else:
            print("feature and featureversion exists in enterprise only", e_fv)
            update_namespace[e_fv['feature_id']] = centos_id
            f_id = e_fv['feature_id']
            ns_id = centos_id
            cur.execute("UPDATE feature SET namespace=%d WHERE id=%d" % (f_id, ns_id))
            cur.commit()

# finally update the namespace in the layer table
cur.execute("UPDATE layer SET namespace_id=%d WHERE namepsace_id=%d" % (centos_id, enterprise_id))
cur.commit()