42 lines
1.6 KiB
Python
42 lines
1.6 KiB
Python
#!/usr/bin/env python3
|
|
|
|
import sqlite3
|
|
import sys
|
|
from contextlib import closing
|
|
|
|
start = int(sys.argv[1]) # starting id to delete (inclusive)
|
|
stop = int(sys.argv[2]) # final id to delete (inclusive)
|
|
|
|
with closing(sqlite3.connect('./woodpecker.sqlite', autocommit=False)) as con:
|
|
del_ids = con.execute('SELECT id FROM pipelines WHERE number BETWEEN ? AND ?;', (start, stop)).fetchall()
|
|
del_ids = tuple(i[0] for i in del_ids)
|
|
n_deleted = len(del_ids)
|
|
max_id = max(del_ids)
|
|
placeholders = ', '.join('?' for i in range(n_deleted))
|
|
|
|
try:
|
|
con.execute(f'DELETE FROM pipelines WHERE id IN ({placeholders});', del_ids)
|
|
con.execute(f'DELETE FROM pipeline_configs WHERE pipeline_id IN ({placeholders});', del_ids)
|
|
con.execute(f'DELETE FROM steps WHERE pipeline_id IN ({placeholders});', del_ids)
|
|
con.execute(f'DELETE FROM workflows WHERE pipeline_id IN ({placeholders});', del_ids)
|
|
con.execute('DELETE FROM configs WHERE id NOT IN (SELECT config_id FROM pipeline_configs);')
|
|
except sqlite3.Error as e:
|
|
e.add_note(f'with {del_ids=}, {n_deleted=}, {placeholders=}')
|
|
raise
|
|
|
|
try:
|
|
res = con.execute(
|
|
'UPDATE pipelines SET number = number - ?, id = id - ? WHERE id > ?;', (n_deleted, n_deleted, max_id)
|
|
)
|
|
res = con.execute(
|
|
'UPDATE pipeline_configs SET pipeline_id = pipeline_id - ? WHERE pipeline_id > ?;', (n_deleted, max_id)
|
|
)
|
|
res = con.execute('UPDATE steps SET pipeline_id = pipeline_id - ? WHERE pipeline_id > ?;', (n_deleted, max_id))
|
|
res = con.execute(
|
|
'UPDATE workflows SET pipeline_id = pipeline_id - ? WHERE pipeline_id > ?;', (n_deleted, max_id)
|
|
)
|
|
except sqlite3.Error as e:
|
|
e.add_note(f'with {n_deleted=}, {max_id=}')
|
|
raise
|
|
|
|
con.commit()
|