if verbose >= 9: print """ SELECT DISTINCT uq.id_user\n FROM user_query AS uq LEFT JOIN user AS u\n ON uq.id_user = u.id\n WHERE u.id IS NULL"""
result = run_sql("""SELECT DISTINCT uq.id_user
FROM user_query AS uq LEFT JOIN user AS u
ON uq.id_user = u.id
WHERE u.id IS NULL""")
if verbose >= 9: print result
# delete in user_query one by one
if verbose >= 9: print """ DELETE FROM user_query WHERE id_user = 'TRAVERSE LAST RESULT' \n"""
for (id_user, ) in result:
delcount['user_query'] += run_sql("""DELETE FROM user_query WHERE id_user = %s""" % (id_user, ))
# delete the actual queries
if verbose: print "- deleting queries not attached to any user"
# select queries that must be deleted
if verbose >= 9: print """ SELECT DISTINCT q.id\n FROM query AS q LEFT JOIN user_query AS uq\n ON uq.id_query = q.id\n WHERE uq.id_query IS NULL AND\n q.type <> 'p' """
result = run_sql("""SELECT DISTINCT q.id
FROM query AS q LEFT JOIN user_query AS uq
ON uq.id_query = q.id
WHERE uq.id_query IS NULL AND
q.type <> 'p'""")
if verbose >= 9: print result
# delete queries one by one
if verbose >= 9: print """ DELETE FROM query WHERE id = 'TRAVERSE LAST RESULT \n"""
for (id_user, ) in result:
delcount['query'] += run_sql("""DELETE FROM query WHERE id = %s""" % (id_user, ))
# 3 - DELETE BASKETS NOT OWNED BY ANY USER
if verbose: print "- deleting baskets not owned by any user"
# select basket ids
if verbose >= 9: print """ SELECT ub.id_basket\n FROM user_basket AS ub LEFT JOIN user AS u\n ON u.id = ub.id_user\n WHERE u.id IS NULL"""
result = run_sql("""SELECT ub.id_basket
FROM user_basket AS ub LEFT JOIN user AS u
ON u.id = ub.id_user
WHERE u.id IS NULL""")
if verbose >= 9: print result
# delete from user_basket and basket one by one
if verbose >= 9:
print """ DELETE FROM user_basket WHERE id_basket = 'TRAVERSE LAST RESULT' """
print """ DELETE FROM basket WHERE id = 'TRAVERSE LAST RESULT' \n"""
for (id_basket, ) in result:
delcount['user_basket'] += run_sql("""DELETE FROM user_basket WHERE id_basket = %s""" % (id_basket, ))
delcount['basket'] += run_sql("""DELETE FROM basket WHERE id = %s""" % (id_basket, ))
# 4 - DELETE ALERTS NOT OWNED BY ANY USER
if verbose: print '- deleting alerts not owned by any user'
# select user ids in uqb that reference non-existent users
if verbose >= 9: print """SELECT DISTINCT uqb.id_user FROM user_query_basket AS uqb LEFT JOIN user AS u ON uqb.id_user = u.id WHERE u.id IS NULL"""
result = run_sql("""SELECT DISTINCT uqb.id_user FROM user_query_basket AS uqb LEFT JOIN user AS u ON uqb.id_user = u.id WHERE u.id IS NULL""")
if verbose >= 9: print result
# delete all these entries
for (id_user, ) in result:
if verbose >= 9: print """DELETE FROM user_query_basket WHERE id_user = 'TRAVERSE LAST RESULT """
delcount['user_query_basket'] += run_sql("""DELETE FROM user_query_basket WHERE id_user = %s """ % (id_user, ))