#!/usr/bin/python
|
# Copyright (c) 2012 The Chromium OS Authors. All rights reserved.
|
# Use of this source code is governed by a BSD-style license that can be
|
# found in the LICENSE file.
|
|
import argparse
|
import datetime
|
import logging
|
import os
|
import re
|
import sys
|
import time
|
|
os.environ['DJANGO_SETTINGS_MODULE'] = 'frontend.settings'
|
|
import common
|
from autotest_lib.server import utils
|
from django.db import connections, transaction
|
|
|
# Format Appears as: [Date] [Time] - [Msg Level] - [Message]
|
LOGGING_FORMAT = '%(asctime)s - %(levelname)s - %(message)s'
|
# This regex makes sure the input is in the format of YYYY-MM-DD (2012-02-01)
|
DATE_FORMAT_REGEX = ('^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]'
|
'|3[01])$')
|
SELECT_CMD_FORMAT = """
|
SELECT %(table)s.%(primary_key)s FROM %(table)s
|
WHERE %(table)s.%(time_column)s <= "%(date)s"
|
"""
|
SELECT_JOIN_CMD_FORMAT = """
|
SELECT %(table)s.%(primary_key)s FROM %(table)s
|
INNER JOIN %(related_table)s
|
ON %(table)s.%(foreign_key)s=%(related_table)s.%(related_primary_key)s
|
WHERE %(related_table)s.%(time_column)s <= "%(date)s"
|
"""
|
SELECT_WITH_INDIRECTION_FORMAT = """
|
SELECT %(table)s.%(primary_key)s FROM %(table)s
|
INNER JOIN %(indirection_table)s
|
ON %(table)s.%(foreign_key)s =
|
%(indirection_table)s.%(indirection_primary_key)s
|
INNER JOIN %(related_table)s
|
ON %(indirection_table)s.%(indirection_foreign_key)s =
|
%(related_table)s.%(related_primary_key)s
|
WHERE %(related_table)s.%(time_column)s <= "%(date)s"
|
"""
|
DELETE_ROWS_FORMAT = """
|
DELETE FROM %(table)s
|
WHERE %(table)s.%(primary_key)s IN (%(rows)s)
|
"""
|
|
|
AFE_JOB_ID = 'afe_job_id'
|
JOB_ID = 'job_id'
|
JOB_IDX = 'job_idx'
|
TEST_IDX = 'test_idx'
|
|
# CAUTION: Make sure only the 'default' connection is used. Otherwise
|
# db_cleanup may delete stuff from the global database, which is generally not
|
# intended.
|
cursor = connections['default'].cursor()
|
|
# Globals for command line flag constants, for convenience.
|
DRY_RUN = False
|
STEP_SIZE = None
|
LOAD_RATIO = 1.0
|
|
class ProgressBar(object):
|
TEXT = "{:<40s} [{:<20s}] ({:>9d}/{:>9d})"
|
|
def __init__(self, name, amount):
|
self._name = name
|
self._amount = amount
|
self._cur = 0
|
|
def __enter__(self):
|
return self
|
|
def __exit__(self, a, b, c):
|
sys.stdout.write('\n')
|
sys.stdout.flush()
|
|
def update(self, x):
|
"""
|
Advance the counter by `x`.
|
|
@param x: An integer of how many more elements were processed.
|
"""
|
self._cur += x
|
|
def show(self):
|
"""
|
Display the progress bar on the current line. Repeated invocations
|
"update" the display.
|
"""
|
if self._amount == 0:
|
barlen = 20
|
else:
|
barlen = int(20 * self._cur / float(self._amount))
|
if barlen:
|
bartext = '=' * (barlen-1) + '>'
|
else:
|
bartext = ''
|
text = self.TEXT.format(self._name, bartext, self._cur, self._amount)
|
sys.stdout.write('\r')
|
sys.stdout.write(text)
|
sys.stdout.flush()
|
|
|
def grouper(iterable, n):
|
"""
|
Group the elements of `iterable` into groups of maximum size `n`.
|
|
@param iterable: An iterable.
|
@param n: Max size of returned groups.
|
@returns: Yields iterables of size <= n.
|
|
>>> grouper('ABCDEFG', 3)
|
[['A', 'B', C'], ['D', 'E', 'F'], ['G']]
|
"""
|
args = [iter(iterable)] * n
|
while True:
|
lst = []
|
try:
|
for itr in args:
|
lst.append(next(itr))
|
yield lst
|
except StopIteration:
|
if lst:
|
yield lst
|
break
|
|
|
def _delete_table_data_before_date(table_to_delete_from, primary_key,
|
related_table, related_primary_key,
|
date, foreign_key=None,
|
time_column="started_time",
|
indirection_table=None,
|
indirection_primary_key=None,
|
indirection_foreign_key=None):
|
"""
|
We want a delete statement that will only delete from one table while
|
using a related table to find the rows to delete.
|
|
An example mysql command:
|
DELETE FROM tko_iteration_result USING tko_iteration_result INNER JOIN
|
tko_tests WHERE tko_iteration_result.test_idx=tko_tests.test_idx AND
|
tko_tests.started_time <= '2012-02-01';
|
|
There are also tables that require 2 joins to determine which rows we want
|
to delete and we determine these rows by joining the table we want to
|
delete from with an indirection table to the actual jobs table.
|
|
@param table_to_delete_from: Table whose rows we want to delete.
|
@param related_table: Table with the date information we are selecting by.
|
@param foreign_key: Foreign key used in table_to_delete_from to reference
|
the related table. If None, the primary_key is used.
|
@param primary_key: Primary key in the related table.
|
@param date: End date of the information we are trying to delete.
|
@param time_column: Column that we want to use to compare the date to.
|
@param indirection_table: Table we use to link the data we are trying to
|
delete with the table with the date information.
|
@param indirection_primary_key: Key we use to connect the indirection table
|
to the table we are trying to delete rows
|
from.
|
@param indirection_foreign_key: Key we use to connect the indirection table
|
to the table with the date information.
|
"""
|
if not foreign_key:
|
foreign_key = primary_key
|
|
if not related_table:
|
# Deleting from a table directly.
|
variables = dict(table=table_to_delete_from, primary_key=primary_key,
|
time_column=time_column, date=date)
|
sql = SELECT_CMD_FORMAT % variables
|
elif not indirection_table:
|
# Deleting using a single JOIN to get the date information.
|
variables = dict(primary_key=primary_key, table=table_to_delete_from,
|
foreign_key=foreign_key, related_table=related_table,
|
related_primary_key=related_primary_key,
|
time_column=time_column, date=date)
|
sql = SELECT_JOIN_CMD_FORMAT % variables
|
else:
|
# There are cases where we need to JOIN 3 TABLES to determine the rows
|
# we want to delete.
|
variables = dict(primary_key=primary_key, table=table_to_delete_from,
|
indirection_table=indirection_table,
|
foreign_key=foreign_key,
|
indirection_primary_key=indirection_primary_key,
|
related_table=related_table,
|
related_primary_key=related_primary_key,
|
indirection_foreign_key=indirection_foreign_key,
|
time_column=time_column, date=date)
|
sql = SELECT_WITH_INDIRECTION_FORMAT % variables
|
|
logging.debug('SQL: %s', sql)
|
cursor.execute(sql, [])
|
rows = [x[0] for x in cursor.fetchall()]
|
logging.debug(rows)
|
|
if not rows or rows == [None]:
|
with ProgressBar(table_to_delete_from, 0) as pb:
|
pb.show()
|
logging.debug('Nothing to delete for %s', table_to_delete_from)
|
return
|
|
with ProgressBar(table_to_delete_from, len(rows)) as pb:
|
for row_keys in grouper(rows, STEP_SIZE):
|
variables['rows'] = ','.join([str(x) for x in row_keys])
|
sql = DELETE_ROWS_FORMAT % variables
|
|
start = time.time()
|
logging.debug('SQL: %s', sql)
|
if not DRY_RUN:
|
cursor.execute(sql, [])
|
transaction.commit_unless_managed(using='default')
|
end = time.time()
|
|
pb.update(len(row_keys))
|
pb.show()
|
|
if LOAD_RATIO != 1.0:
|
assert 0 < LOAD_RATIO <= 1, (
|
'Load ratio must be a fraction between 0 and 1.')
|
time.sleep((end - start) / LOAD_RATIO)
|
|
|
def _subtract_days(date, days_to_subtract):
|
"""
|
Return a date (string) that is 'days' before 'date'
|
|
@param date: date (string) we are subtracting from.
|
@param days_to_subtract: days (int) we are subtracting.
|
"""
|
date_obj = datetime.datetime.strptime(date, '%Y-%m-%d')
|
difference = date_obj - datetime.timedelta(days=days_to_subtract)
|
return difference.strftime('%Y-%m-%d')
|
|
|
def _delete_all_data_before_date(date):
|
"""
|
Delete all the database data before a given date.
|
|
This function focuses predominately on the data for jobs in tko_jobs.
|
However not all jobs in afe_jobs are also in tko_jobs.
|
|
Therefore we delete all the afe_job and foreign key relations prior to two
|
days before date. Then we do the queries using tko_jobs and these
|
tables to ensure all the related information is gone. Even though we are
|
repeating deletes on these tables, the second delete will be quick and
|
completely thorough in ensuring we clean up all the foreign key
|
dependencies correctly.
|
|
@param date: End date of the information we are trying to delete.
|
@param step: Rows to delete per SQL query.
|
"""
|
# First cleanup all afe_job related data (prior to 2 days before date).
|
# The reason for this is not all afe_jobs may be in tko_jobs.
|
afe_date = _subtract_days(date, 2)
|
logging.info('Cleaning up all afe_job data prior to %s.', afe_date)
|
_delete_table_data_before_date('afe_aborted_host_queue_entries',
|
'queue_entry_id',
|
'afe_jobs', 'id', afe_date,
|
time_column= 'created_on',
|
foreign_key='queue_entry_id',
|
indirection_table='afe_host_queue_entries',
|
indirection_primary_key='id',
|
indirection_foreign_key='job_id')
|
_delete_table_data_before_date('afe_special_tasks', 'id',
|
'afe_jobs', 'id',
|
afe_date, time_column='created_on',
|
foreign_key='queue_entry_id',
|
indirection_table='afe_host_queue_entries',
|
indirection_primary_key='id',
|
indirection_foreign_key='job_id')
|
_delete_table_data_before_date('afe_host_queue_entries', 'id',
|
'afe_jobs', 'id',
|
afe_date, time_column='created_on',
|
foreign_key=JOB_ID)
|
_delete_table_data_before_date('afe_job_keyvals', 'id',
|
'afe_jobs', 'id',
|
afe_date, time_column='created_on',
|
foreign_key=JOB_ID)
|
_delete_table_data_before_date('afe_jobs_dependency_labels', 'id',
|
'afe_jobs', 'id',
|
afe_date, time_column='created_on',
|
foreign_key=JOB_ID)
|
_delete_table_data_before_date('afe_jobs', 'id',
|
None, None,
|
afe_date, time_column='created_on')
|
# Special tasks that aren't associated with an HQE
|
# Since we don't do the queue_entry_id=NULL check, we might wipe out a bit
|
# more than we should, but I doubt anyone will notice or care.
|
_delete_table_data_before_date('afe_special_tasks', 'id',
|
None, None,
|
afe_date, time_column='time_requested')
|
|
# Now go through and clean up all the rows related to tko_jobs prior to
|
# date.
|
logging.info('Cleaning up all data related to tko_jobs prior to %s.',
|
date)
|
_delete_table_data_before_date('tko_test_attributes', 'id',
|
'tko_tests', TEST_IDX,
|
date, foreign_key=TEST_IDX)
|
_delete_table_data_before_date('tko_test_labels_tests', 'id',
|
'tko_tests', TEST_IDX,
|
date, foreign_key= 'test_id')
|
_delete_table_data_before_date('tko_iteration_result', TEST_IDX,
|
'tko_tests', TEST_IDX,
|
date)
|
_delete_table_data_before_date('tko_iteration_perf_value', TEST_IDX,
|
'tko_tests', TEST_IDX,
|
date)
|
_delete_table_data_before_date('tko_iteration_attributes', TEST_IDX,
|
'tko_tests', TEST_IDX,
|
date)
|
_delete_table_data_before_date('tko_job_keyvals', 'id',
|
'tko_jobs', JOB_IDX,
|
date, foreign_key='job_id')
|
_delete_table_data_before_date('afe_aborted_host_queue_entries',
|
'queue_entry_id',
|
'tko_jobs', AFE_JOB_ID, date,
|
foreign_key='queue_entry_id',
|
indirection_table='afe_host_queue_entries',
|
indirection_primary_key='id',
|
indirection_foreign_key='job_id')
|
_delete_table_data_before_date('afe_special_tasks', 'id',
|
'tko_jobs', AFE_JOB_ID,
|
date, foreign_key='queue_entry_id',
|
indirection_table='afe_host_queue_entries',
|
indirection_primary_key='id',
|
indirection_foreign_key='job_id')
|
_delete_table_data_before_date('afe_host_queue_entries', 'id',
|
'tko_jobs', AFE_JOB_ID,
|
date, foreign_key='job_id')
|
_delete_table_data_before_date('afe_job_keyvals', 'id',
|
'tko_jobs', AFE_JOB_ID,
|
date, foreign_key='job_id')
|
_delete_table_data_before_date('afe_jobs_dependency_labels', 'id',
|
'tko_jobs', AFE_JOB_ID,
|
date, foreign_key='job_id')
|
_delete_table_data_before_date('afe_jobs', 'id',
|
'tko_jobs', AFE_JOB_ID,
|
date, foreign_key='id')
|
_delete_table_data_before_date('tko_tests', TEST_IDX,
|
'tko_jobs', JOB_IDX,
|
date, foreign_key=JOB_IDX)
|
_delete_table_data_before_date('tko_jobs', JOB_IDX,
|
None, None, date)
|
|
|
def parse_args():
|
"""Parse command line arguments"""
|
parser = argparse.ArgumentParser()
|
parser.add_argument('-v', '--verbose', action='store_true',
|
help='Print SQL commands and results')
|
parser.add_argument('--step', type=int, action='store',
|
default=1000,
|
help='Number of rows to delete at once')
|
parser.add_argument('--dry_run', action='store_true',
|
help='Print SQL queries instead of executing them.')
|
parser.add_argument('--load_ratio', type=float, action='store', default=0.2,
|
help=('The fraction of time the script should be '
|
'performing deletes. For example --load_ratio=.2 '
|
'will cause the script to sleep 80% of the time, '
|
'and perform work for the other 20%.'))
|
parser.add_argument('date', help='Keep results newer than')
|
return parser.parse_args()
|
|
|
def main():
|
args = parse_args()
|
|
verbose = args.verbose or args.dry_run
|
level = logging.DEBUG if verbose else logging.INFO
|
logging.basicConfig(level=level, format=LOGGING_FORMAT)
|
logging.info('Calling: %s', sys.argv)
|
|
if not re.match(DATE_FORMAT_REGEX, args.date):
|
print 'DATE must be in yyyy-mm-dd format!'
|
return
|
|
global STEP_SIZE, DRY_RUN, LOAD_RATIO
|
STEP_SIZE = args.step
|
DRY_RUN = args.dry_run
|
LOAD_RATIO = args.load_ratio
|
|
_delete_all_data_before_date(args.date)
|
|
|
if __name__ == '__main__':
|
main()
|
