def liverebuild(con):
"""Rebuild the data in all the fti columns against possibly live database.
"""
# Update number of rows per transaction.
batch_size = 50
cur = con.cursor()
for table, ignored in ALL_FTI:
table = quote_identifier(table)
cur.execute("SELECT max(id) FROM %s" % table)
max_id = cur.fetchone()[0]
if max_id is None:
log.info("No data in %s - skipping", table)
continue
log.info("Rebuilding fti column on %s", table)
for id in range(0, max_id, batch_size):
try:
query = """
UPDATE %s SET fti=NULL WHERE id BETWEEN %d AND %d
""" % (table, id + 1, id + batch_size)
log.debug(query)
cur.execute(query)
except psycopg2.Error:
# No commit - we are in autocommit mode
log.exception('psycopg error')
con = connect()
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
def populate_distroseriesdiff(logger, derived_series, parent_series):
"""Compare `derived_distroseries` to parent, and register differences.
The differences are registered by creating `DistroSeriesDifference`
records, insofar as they do not yet exist.
"""
temp_table = "temp_potentialdistroseriesdiff"
store = IStore(derived_series)
drop_table(store, temp_table)
quoted_temp_table = quote_identifier(temp_table)
store.execute("""
CREATE TEMP TABLE %s(
sourcepackagename INTEGER,
source_version debversion,
parent_source_version debversion)
ON COMMIT DROP
""" % (quoted_temp_table))
store.execute(
"INSERT INTO %s %s" %
(quoted_temp_table,
compose_sql_find_differences(derived_series, parent_series)))
logger.info(
"Found %d potential difference(s).",
store.execute("SELECT count(*) FROM %s" % temp_table).get_one()[0])
store.execute(
compose_sql_populate_distroseriesdiff(derived_series, parent_series,
temp_table))
def liverebuild(con):
"""Rebuild the data in all the fti columns against possibly live database.
"""
# Update number of rows per transaction.
batch_size = 50
cur = con.cursor()
for table, ignored in ALL_FTI:
table = quote_identifier(table)
cur.execute("SELECT max(id) FROM %s" % table)
max_id = cur.fetchone()[0]
if max_id is None:
log.info("No data in %s - skipping", table)
continue
log.info("Rebuilding fti column on %s", table)
for id in range(0, max_id, batch_size):
try:
query = """
UPDATE %s SET fti=NULL WHERE id BETWEEN %d AND %d
""" % (table, id + 1, id + batch_size)
log.debug(query)
cur.execute(query)
except psycopg2.Error:
# No commit - we are in autocommit mode
log.exception('psycopg error')
con = connect()
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
def populate_distroseriesdiff(logger, derived_series, parent_series):
"""Compare `derived_distroseries` to parent, and register differences.
The differences are registered by creating `DistroSeriesDifference`
records, insofar as they do not yet exist.
"""
temp_table = "temp_potentialdistroseriesdiff"
store = IStore(derived_series)
drop_table(store, temp_table)
quoted_temp_table = quote_identifier(temp_table)
store.execute("""
CREATE TEMP TABLE %s(
sourcepackagename INTEGER,
source_version debversion,
parent_source_version debversion)
ON COMMIT DROP
""" % (
quoted_temp_table))
store.execute("INSERT INTO %s %s" % (
quoted_temp_table,
compose_sql_find_differences(derived_series, parent_series)))
logger.info(
"Found %d potential difference(s).",
store.execute("SELECT count(*) FROM %s" % temp_table).get_one()[0])
store.execute(
compose_sql_populate_distroseriesdiff(
derived_series, parent_series, temp_table))
def nullify(con):
"""Set all fti index columns to NULL"""
for table, ignored in ALL_FTI:
table = quote_identifier(table)
log.info("Removing full text index data from %s", table)
sexecute(con, "ALTER TABLE %s DISABLE TRIGGER tsvectorupdate" % table)
sexecute(con, "UPDATE %s SET fti=NULL" % table)
sexecute(con, "ALTER TABLE %s ENABLE TRIGGER tsvectorupdate" % table)
sexecute(con, "DELETE FROM FtiCache")
def nullify(con):
"""Set all fti index columns to NULL"""
for table, ignored in ALL_FTI:
table = quote_identifier(table)
log.info("Removing full text index data from %s", table)
sexecute(con, "ALTER TABLE %s DISABLE TRIGGER tsvectorupdate" % table)
sexecute(con, "UPDATE %s SET fti=NULL" % table)
sexecute(con, "ALTER TABLE %s ENABLE TRIGGER tsvectorupdate" % table)
sexecute(con, "DELETE FROM FtiCache")
def compose_sql_populate_distroseriesdiff(derived_series, parent_series,
temp_table):
"""Create `DistroSeriesDifference` rows based on found differences.
Uses field values that describe the difference, as produced by the
SQL from `compose_sql_find_differences`:
* sourcepackagename
* source_version
* parent_source_version
Existing `DistroSeriesDifference` rows are not affected.
:param derived_distroseries: A derived `DistroSeries`.
:param temp_table: The name of a table to select the input fields
from.
:return: SQL query, as a string.
"""
parameters = {
'derived_series': quote(derived_series),
'parent_series': quote(parent_series),
'difference_type_expression': compose_sql_difference_type(),
'needs_attention': quote(
DistroSeriesDifferenceStatus.NEEDS_ATTENTION),
'temp_table': quote_identifier(temp_table),
}
return """
INSERT INTO DistroSeriesDifference (
derived_series,
parent_series,
source_package_name,
status,
difference_type,
source_version,
parent_source_version)
SELECT
%(derived_series)s,
%(parent_series)s,
sourcepackagename,
%(needs_attention)s,
%(difference_type_expression)s,
source_version,
parent_source_version
FROM %(temp_table)s
WHERE sourcepackagename NOT IN (
SELECT source_package_name
FROM DistroSeriesDifference
WHERE derived_series = %(derived_series)s)
""" % parameters
def compose_sql_populate_distroseriesdiff(derived_series, parent_series,
temp_table):
"""Create `DistroSeriesDifference` rows based on found differences.
Uses field values that describe the difference, as produced by the
SQL from `compose_sql_find_differences`:
* sourcepackagename
* source_version
* parent_source_version
Existing `DistroSeriesDifference` rows are not affected.
:param derived_distroseries: A derived `DistroSeries`.
:param temp_table: The name of a table to select the input fields
from.
:return: SQL query, as a string.
"""
parameters = {
'derived_series': quote(derived_series),
'parent_series': quote(parent_series),
'difference_type_expression': compose_sql_difference_type(),
'needs_attention': quote(DistroSeriesDifferenceStatus.NEEDS_ATTENTION),
'temp_table': quote_identifier(temp_table),
}
return """
INSERT INTO DistroSeriesDifference (
derived_series,
parent_series,
source_package_name,
status,
difference_type,
source_version,
parent_source_version)
SELECT
%(derived_series)s,
%(parent_series)s,
sourcepackagename,
%(needs_attention)s,
%(difference_type_expression)s,
source_version,
parent_source_version
FROM %(temp_table)s
WHERE sourcepackagename NOT IN (
SELECT source_package_name
FROM DistroSeriesDifference
WHERE derived_series = %(derived_series)s)
""" % parameters
def drop_table(store, table):
"""Drop `table`, if it exists."""
store.execute("DROP TABLE IF EXISTS %s" % quote_identifier(table))
def drop_table(store, table):
"""Drop `table`, if it exists."""
store.execute("DROP TABLE IF EXISTS %s" % quote_identifier(table))
