def omit_redundant_pofiles(from_table, to_table, batch_size, begin_id, end_id):
"""Batch-pouring callback: skip POFiles that have become redundant.
This is needed to deal with a concurrency problem where POFiles may
get created (through message sharing) while translations are still
being copied.
"""
assert to_table.lower() == "pofile", (
"This callback is meant for pouring the POFile table only.")
params = {
'from_table': from_table,
'begin_id': begin_id,
'end_id': end_id,
}
cursor().execute("""
DELETE FROM %(from_table)s
WHERE
id >= %(begin_id)s AND
id < %(end_id)s AND
EXISTS (
SELECT *
FROM POFile
WHERE
POFile.potemplate = %(from_table)s.potemplate AND
POFile.language = %(from_table)s.language
)
""" % params)
def _updateMaloneStatistics(self, ztm):
self.update('bug_count', Bug.select().count())
ztm.commit()
self.update('bugtask_count', BugTask.select().count())
ztm.commit()
self.update(
'products_using_malone',
Product.selectBy(official_malone=True).count())
ztm.commit()
cur = cursor()
cur.execute(
"SELECT COUNT(DISTINCT product) + COUNT(DISTINCT distribution) "
"FROM BugTask")
self.update("projects_with_bugs", cur.fetchone()[0] or 0)
ztm.commit()
cur = cursor()
cur.execute(
"SELECT COUNT(*) FROM (SELECT COUNT(distinct product) + "
" COUNT(distinct distribution) "
" AS places "
" FROM BugTask GROUP BY bug) "
" AS temp WHERE places > 1")
self.update("shared_bug_count", cur.fetchone()[0] or 0)
ztm.commit()
def omit_redundant_pofiles(from_table, to_table, batch_size, begin_id,
end_id):
"""Batch-pouring callback: skip POFiles that have become redundant.
This is needed to deal with a concurrency problem where POFiles may
get created (through message sharing) while translations are still
being copied.
"""
assert to_table.lower() == "pofile", (
"This callback is meant for pouring the POFile table only.")
params = {
'from_table': from_table,
'begin_id': begin_id,
'end_id': end_id,
}
cursor().execute("""
DELETE FROM %(from_table)s
WHERE
id >= %(begin_id)s AND
id < %(end_id)s AND
EXISTS (
SELECT *
FROM POFile
WHERE
POFile.potemplate = %(from_table)s.potemplate AND
POFile.language = %(from_table)s.language
)
""" % params)
def _updateMaloneStatistics(self, ztm):
self.update('bug_count', Bug.select().count())
ztm.commit()
self.update('bugtask_count', BugTask.select().count())
ztm.commit()
self.update(
'products_using_malone',
Product.selectBy(official_malone=True).count())
ztm.commit()
cur = cursor()
cur.execute(
"SELECT COUNT(DISTINCT product) + COUNT(DISTINCT distribution) "
"FROM BugTask")
self.update("projects_with_bugs", cur.fetchone()[0] or 0)
ztm.commit()
cur = cursor()
cur.execute(
"SELECT COUNT(*) FROM (SELECT COUNT(distinct product) + "
" COUNT(distinct distribution) "
" AS places "
" FROM BugTask GROUP BY bug) "
" AS temp WHERE places > 1")
self.update("shared_bug_count", cur.fetchone()[0] or 0)
ztm.commit()
def _breakTransaction(self):
# make sure the current transaction can not be committed by
# sending a broken SQL statement to the database
try:
cursor().execute('break this transaction')
except psycopg2.DatabaseError:
pass
def _breakTransaction(self):
# make sure the current transaction can not be committed by
# sending a broken SQL statement to the database
try:
cursor().execute('break this transaction')
except psycopg2.DatabaseError:
pass
def test_deleteUnwantedFiles(self):
self.ztm.begin()
cur = cursor()
# We may find files in the LibraryFileContent repository
# that do not have an corresponding LibraryFileContent row.
# Find a content_id we can easily delete and do so. This row is
# removed from the database, leaving an orphaned file on the
# filesystem that should be removed.
cur.execute("""
SELECT LibraryFileContent.id
FROM LibraryFileContent
LEFT OUTER JOIN LibraryFileAlias
ON LibraryFileContent.id = content
WHERE LibraryFileAlias.id IS NULL
LIMIT 1
""")
content_id = cur.fetchone()[0]
cur.execute("""
DELETE FROM LibraryFileContent WHERE id=%s
""", (content_id,))
self.ztm.commit()
path = librariangc.get_file_path(content_id)
self.failUnless(os.path.exists(path))
# Ensure delete_unreferenced_files does not remove the file, because
# it will have just been created (has a recent date_created). There
# is a window between file creation and the garbage collector
# bothering to remove the file to avoid the race condition where the
# garbage collector is run whilst a file is being uploaded.
librariangc.delete_unwanted_files(self.con)
self.failUnless(os.path.exists(path))
# To test removal does occur when we want it to, we need to trick
# the garbage collector into thinking it is tomorrow.
org_time = librariangc.time
def tomorrow_time():
return org_time() + 24 * 60 * 60 + 1
try:
librariangc.time = tomorrow_time
librariangc.delete_unwanted_files(self.con)
finally:
librariangc.time = org_time
self.failIf(os.path.exists(path))
# Make sure nothing else has been removed from disk
self.ztm.begin()
cur = cursor()
cur.execute("""
SELECT id FROM LibraryFileContent
""")
for content_id in (row[0] for row in cur.fetchall()):
path = librariangc.get_file_path(content_id)
self.failUnless(os.path.exists(path))
def check():
cur = cursor()
cur.execute("UPDATE Person SET homepage_content='foo' WHERE name='mark'")
cur.execute("SHOW transaction_isolation")
print cur.fetchone()[0]
transaction.abort()
transaction.begin()
cur = cursor()
cur.execute("UPDATE Person SET homepage_content='bar' WHERE name='mark'")
cur.execute("SHOW transaction_isolation")
print cur.fetchone()[0]
def check():
cur = cursor()
cur.execute("UPDATE Person SET homepage_content='foo' WHERE name='mark'")
cur.execute("SHOW transaction_isolation")
print cur.fetchone()[0]
transaction.abort()
transaction.begin()
cur = cursor()
cur.execute("UPDATE Person SET homepage_content='bar' WHERE name='mark'")
cur.execute("SHOW transaction_isolation")
print cur.fetchone()[0]
def test_DeleteUnreferencedContent(self):
# Merge the duplicates. This creates an
# unreferenced LibraryFileContent
librariangc.merge_duplicates(self.con)
self.ztm.begin()
# Locate the unreferenced LibraryFileContent
cur = cursor()
cur.execute("""
SELECT LibraryFileContent.id
FROM LibraryFileContent
LEFT OUTER JOIN LibraryFileAlias
ON LibraryFileContent.id = LibraryFileAlias.content
WHERE LibraryFileAlias.id IS NULL
AND LibraryFileContent.id IN (%d, %d)
""" % (self.f1_id, self.f2_id))
results = cur.fetchall()
self.failUnlessEqual(len(results), 1)
unreferenced_id = results[0][0]
self.ztm.abort()
# Make sure the file exists on disk
path = librariangc.get_file_path(unreferenced_id)
self.failUnless(os.path.exists(path))
# Delete unreferenced content
librariangc.delete_unreferenced_content(self.con)
# Make sure the file is gone
self.failIf(os.path.exists(path))
# delete_unreferenced_content should have committed
self.ztm.begin()
# Make sure the unreferenced entries have all gone
cur = cursor()
cur.execute("""
SELECT LibraryFileContent.id
FROM LibraryFileContent
LEFT OUTER JOIN LibraryFileAlias
ON LibraryFileContent.id = LibraryFileAlias.content
WHERE LibraryFileAlias.id IS NULL
""")
results = list(cur.fetchall())
self.failUnlessEqual(
len(results), 0, 'Too many results %r' % (results,)
)
def main(self):
"""Update the KarmaCache table for all valid Launchpad users.
For each Launchpad user with a preferred email address, calculate his
karmavalue for each category of actions we have and update his entry
in the KarmaCache table. If a user doesn't have an entry for that
category in KarmaCache a new one will be created.
Entries in the KarmaTotalCache table will also be created/updated for
each user which has entries in the KarmaCache table. Any user which
doesn't have any entries in the KarmaCache table has its entries
removed from the KarmaTotalCache table as well.
"""
self.logger.info("Updating Launchpad karma caches")
self.cur = cursor()
self.karmacachemanager = getUtility(IKarmaCacheManager)
# This method ordering needs to be preserved. In particular,
# C_add_summed_totals method is called last because we don't want to
# include the values added in our calculation in A_update_karmacache.
self.A_update_karmacache()
self.B_update_karmatotalcache()
self.C_add_karmacache_sums()
self.logger.info("Finished updating Launchpad karma caches")
def _pourTable(self, holding_table, table, has_new_id,
transaction_manager):
"""Pour contents of a holding table back into its source table.
This will commit transaction_manager, typically multiple times.
"""
if has_new_id:
# Update ids in holding table from originals to copies. To
# broaden the caller's opportunity to manipulate rows in the
# holding tables, we skip rows that have new_id set to null.
cur = cursor()
cur.execute("UPDATE %s SET id=new_id" % holding_table)
# Restore table to original schema
cur.execute("ALTER TABLE %s DROP COLUMN new_id" % holding_table)
self._commit(transaction_manager)
self.logger.debug("...rearranged ids...")
callback = self.pre_pouring_callbacks.get(table)
if callback is not None:
callback(holding_table, table)
# Now pour holding table's data into its source table. This is where
# we start writing to tables that other clients will be reading, and
# our transaction will usually be serializable, so row locks are a
# concern. Break the writes up in batches of at least a thousand
# rows. The goal is to have these transactions running no longer than
# five seconds or so each; we aim for four just to be sure.
pourer = PouringLoop(holding_table, table, transaction_manager,
self.logger,
self.batch_pouring_callbacks.get(table))
DBLoopTuner(pourer, self.seconds_per_batch,
self.minimum_batch_size).run()
def needsRecovery(self):
"""Do we have holding tables with recoverable data from previous run?
Returns Boolean answer.
"""
cur = cursor()
# If there are any holding tables to be poured into their source
# tables, there must at least be one for the last table that pour()
# processes.
last_holding_table = self.getRawHoldingTableName(self.tables[-1])
if not postgresql.have_table(cur, last_holding_table):
return False
# If the first table in our list also still exists, and it still has
# its new_id column, then the pouring process had not begun yet.
# Assume the data was not ready for pouring.
first_holding_table = self.getRawHoldingTableName(self.tables[0])
if postgresql.table_has_column(cur, first_holding_table, 'new_id'):
self.logger.info(
"Previous run aborted too early for recovery; redo all")
return False
self.logger.info("Recoverable data found")
return True
def getPackageSize(self):
"""See ISourcePackageRelease."""
size_query = """
SELECT
SUM(LibraryFileContent.filesize)/1024.0
FROM
SourcePackagereLease
JOIN SourcePackageReleaseFile ON
SourcePackageReleaseFile.sourcepackagerelease =
SourcePackageRelease.id
JOIN LibraryFileAlias ON
SourcePackageReleaseFile.libraryfile =
LibraryFileAlias.id
JOIN LibraryFileContent ON
LibraryFileAlias.content = LibraryFileContent.id
WHERE
SourcePackageRelease.id = %s
""" % sqlvalues(self)
cur = cursor()
cur.execute(size_query)
results = cur.fetchone()
if len(results) == 1 and results[0] is not None:
return float(results[0])
else:
return 0.0
def test_cronscript(self):
script_path = os.path.join(
config.root, 'cronscripts', 'librarian-gc.py'
)
cmd = [sys.executable, script_path, '-q']
process = Popen(cmd, stdout=PIPE, stderr=STDOUT, stdin=PIPE)
(script_output, _empty) = process.communicate()
self.failUnlessEqual(
process.returncode, 0, 'Error: %s' % script_output)
self.failUnlessEqual(script_output, '')
# Make sure that our example files have been garbage collected
self.ztm.begin()
self.assertRaises(SQLObjectNotFound, LibraryFileAlias.get, self.f1_id)
self.assertRaises(SQLObjectNotFound, LibraryFileAlias.get, self.f2_id)
# And make sure stuff that *is* referenced remains
LibraryFileAlias.get(2)
cur = cursor()
cur.execute("SELECT count(*) FROM LibraryFileAlias")
count = cur.fetchone()[0]
self.failIfEqual(count, 0)
cur.execute("SELECT count(*) FROM LibraryFileContent")
count = cur.fetchone()[0]
self.failIfEqual(count, 0)
def deactivateActiveMemberships(self, team, comment, reviewer):
"""See `ITeamMembershipSet`."""
now = datetime.now(pytz.timezone('UTC'))
cur = cursor()
all_members = list(team.activemembers)
cur.execute("""
UPDATE TeamMembership
SET status=%(status)s,
last_changed_by=%(last_changed_by)s,
last_change_comment=%(comment)s,
date_last_changed=%(date_last_changed)s
WHERE
TeamMembership.team = %(team)s
AND TeamMembership.status IN %(original_statuses)s
""",
dict(
status=TeamMembershipStatus.DEACTIVATED,
last_changed_by=reviewer.id,
comment=comment,
date_last_changed=now,
team=team.id,
original_statuses=ACTIVE_STATES))
for member in all_members:
# store.invalidate() is called for each iteration.
_cleanTeamParticipation(member, team)
def test(self):
"""Test that read-only users cannot make changes to the database."""
# Only one uncancelled, possibly approved unshipped order
# per user.
cur = cursor()
# SELECTs should pass
cur.execute("SELECT * FROM Person")
# Except on sequences
cur.execute("SAVEPOINT attempt")
self.failUnlessRaises(
psycopg2.Error, cur.execute, "SELECT nextval('person_id_seq')"
)
cur.execute("ROLLBACK TO SAVEPOINT attempt")
# UPDATES should fail
cur.execute("SAVEPOINT attempt")
self.failUnlessRaises(
psycopg2.Error, cur.execute, "UPDATE Person SET password=NULL"
)
cur.execute("ROLLBACK TO SAVEPOINT attempt")
# DELETES should fail.
# We need to use a table with no FK references to it
cur.execute("SAVEPOINT attempt")
self.failUnlessRaises(
psycopg2.Error, cur.execute, "DELETE FROM WikiName"
)
cur.execute("ROLLBACK TO SAVEPOINT attempt")
def needsRecovery(self):
"""Do we have holding tables with recoverable data from previous run?
Returns Boolean answer.
"""
cur = cursor()
# If there are any holding tables to be poured into their source
# tables, there must at least be one for the last table that pour()
# processes.
last_holding_table = self.getRawHoldingTableName(self.tables[-1])
if not postgresql.have_table(cur, last_holding_table):
return False
# If the first table in our list also still exists, and it still has
# its new_id column, then the pouring process had not begun yet.
# Assume the data was not ready for pouring.
first_holding_table = self.getRawHoldingTableName(self.tables[0])
if postgresql.table_has_column(cur, first_holding_table, 'new_id'):
self.logger.info(
"Previous run aborted too early for recovery; redo all")
return False
self.logger.info("Recoverable data found")
return True
def _pourTable(
self, holding_table, table, has_new_id, transaction_manager):
"""Pour contents of a holding table back into its source table.
This will commit transaction_manager, typically multiple times.
"""
if has_new_id:
# Update ids in holding table from originals to copies. To
# broaden the caller's opportunity to manipulate rows in the
# holding tables, we skip rows that have new_id set to null.
cur = cursor()
cur.execute("UPDATE %s SET id=new_id" % holding_table)
# Restore table to original schema
cur.execute("ALTER TABLE %s DROP COLUMN new_id" % holding_table)
self._commit(transaction_manager)
self.logger.debug("...rearranged ids...")
callback = self.pre_pouring_callbacks.get(table)
if callback is not None:
callback(holding_table, table)
# Now pour holding table's data into its source table. This is where
# we start writing to tables that other clients will be reading, and
# our transaction will usually be serializable, so row locks are a
# concern. Break the writes up in batches of at least a thousand
# rows. The goal is to have these transactions running no longer than
# five seconds or so each; we aim for four just to be sure.
pourer = PouringLoop(
holding_table, table, transaction_manager, self.logger,
self.batch_pouring_callbacks.get(table))
DBLoopTuner(
pourer, self.seconds_per_batch, self.minimum_batch_size).run()
def getPackageSize(self):
"""See ISourcePackageRelease."""
size_query = """
SELECT
SUM(LibraryFileContent.filesize)/1024.0
FROM
SourcePackagereLease
JOIN SourcePackageReleaseFile ON
SourcePackageReleaseFile.sourcepackagerelease =
SourcePackageRelease.id
JOIN LibraryFileAlias ON
SourcePackageReleaseFile.libraryfile =
LibraryFileAlias.id
JOIN LibraryFileContent ON
LibraryFileAlias.content = LibraryFileContent.id
WHERE
SourcePackageRelease.id = %s
""" % sqlvalues(self)
cur = cursor()
cur.execute(size_query)
results = cur.fetchone()
if len(results) == 1 and results[0] is not None:
return float(results[0])
else:
return 0.0
def test(self):
"""Test that read-only users cannot make changes to the database."""
# Only one uncancelled, possibly approved unshipped order
# per user.
cur = cursor()
# SELECTs should pass
cur.execute("SELECT * FROM Person")
# Except on sequences
cur.execute("SAVEPOINT attempt")
self.assertRaises(psycopg2.Error, cur.execute,
"SELECT nextval('person_id_seq')")
cur.execute("ROLLBACK TO SAVEPOINT attempt")
# UPDATES should fail
cur.execute("SAVEPOINT attempt")
self.assertRaises(psycopg2.Error, cur.execute,
"UPDATE Person SET password=NULL")
cur.execute("ROLLBACK TO SAVEPOINT attempt")
# DELETES should fail.
# We need to use a table with no FK references to it
cur.execute("SAVEPOINT attempt")
self.assertRaises(psycopg2.Error, cur.execute, "DELETE FROM WikiName")
cur.execute("ROLLBACK TO SAVEPOINT attempt")
def main(self):
"""Update the KarmaCache table for all valid Launchpad users.
For each Launchpad user with a preferred email address, calculate his
karmavalue for each category of actions we have and update his entry
in the KarmaCache table. If a user doesn't have an entry for that
category in KarmaCache a new one will be created.
Entries in the KarmaTotalCache table will also be created/updated for
each user which has entries in the KarmaCache table. Any user which
doesn't have any entries in the KarmaCache table has its entries
removed from the KarmaTotalCache table as well.
"""
self.logger.info("Updating Launchpad karma caches")
self.cur = cursor()
self.karmacachemanager = getUtility(IKarmaCacheManager)
# This method ordering needs to be preserved. In particular,
# C_add_summed_totals method is called last because we don't want to
# include the values added in our calculation in A_update_karmacache.
self.A_update_karmacache()
self.B_update_karmatotalcache()
self.C_add_karmacache_sums()
self.logger.info("Finished updating Launchpad karma caches")
def getCurrentIsolation(self, con=None):
if con is None:
cur = cursor()
else:
cur = con.cursor()
cur.execute("SELECT * FROM Person")
cur.execute("SHOW transaction_isolation")
return cur.fetchone()[0]
def getCurrentIsolation(self, con=None):
if con is None:
cur = cursor()
else:
cur = con.cursor()
cur.execute("SELECT * FROM Person")
cur.execute("SHOW transaction_isolation")
return cur.fetchone()[0]
def testProjectTable(self):
cur = cursor()
# Ensure our sample data is valid and that each Project.name
# has a corresponding entry in PillarName.name
cur.execute("""
SELECT COUNT(*)
FROM Project FULL OUTER JOIN PillarName
ON Project.id = PillarName.project
WHERE Project.name != PillarName.name
""")
self.assertEqual(cur.fetchone()[0], 0)
def is_in_sync(name):
cur.execute(
"""
SELECT COUNT(*)
FROM Project, PillarName
WHERE Project.id = PillarName.project
AND Project.name = PillarName.name
AND PillarName.product IS NULL
AND PillarName.distribution IS NULL
AND Project.name = %(name)s
""", dict(name=name))
return cur.fetchone()[0] == 1
# Inserting a new ProjectGroup will populate PillarName
cur.execute("""
INSERT INTO Project (
name, owner, registrant, displayname, title, summary,
description
)
VALUES (
'whatever', 1, 1, 'whatever', 'whatever',
'whatever', 'whatever'
)
""")
self.assertTrue(is_in_sync('whatever'))
# Updating the ProjectGroup.name will propogate changes to PillarName
cur.execute("""
UPDATE Project SET name='whatever2' where name='whatever'
""")
self.assertTrue(is_in_sync('whatever2'))
# Updating other fields won't do any harm.
cur.execute("""
UPDATE Project SET description='whatever2'
WHERE name='whatever2'
""")
self.assertTrue(is_in_sync('whatever2'))
# Deleting a ProjectGroup removes the corresponding entry in
# PillarName.
cur.execute("DELETE FROM Project WHERE name='whatever2'")
cur.execute("SELECT COUNT(*) FROM PillarName WHERE name='whatever2'")
self.assertEqual(cur.fetchone()[0], 0)
def testDistributionTable(self):
cur = cursor()
# Ensure our sample data is valid and that each Distribution.name
# has a corresponding entry in PillarName.name
cur.execute("""
SELECT COUNT(*)
FROM Distribution FULL OUTER JOIN PillarName
ON Distribution.id = PillarName.distribution
WHERE Distribution.name != PillarName.name
""")
self.assertEqual(cur.fetchone()[0], 0)
def is_in_sync(name):
cur.execute(
"""
SELECT COUNT(*)
FROM Distribution, PillarName
WHERE Distribution.id = PillarName.distribution
AND Distribution.name = PillarName.name
AND PillarName.product IS NULL
AND PillarName.project IS NULL
AND Distribution.name = %(name)s
""", dict(name=name))
return cur.fetchone()[0] == 1
# Inserting a new Distribution will populate PillarName
cur.execute("""
INSERT INTO Distribution (
name, description, domainname, owner, registrant,
displayname, summary, title, members, mirror_admin
)
VALUES (
'whatever', 'whatever', 'whatever', 1, 1,
'whatever', 'whatever', 'whatever', 1, 1
)
""")
self.assertTrue(is_in_sync('whatever'))
# Updating the Distribution.name will propogate changes to PillarName
cur.execute("""
UPDATE Distribution SET name='whatever2' where name='whatever'
""")
self.assertTrue(is_in_sync('whatever2'))
# Updating other fields won't do any harm.
cur.execute("""
UPDATE Distribution SET description='whatever2'
WHERE name='whatever2'
""")
self.assertTrue(is_in_sync('whatever2'))
# Deleting a Distribution removes the corresponding entry in
# PillarName
cur.execute("DELETE FROM Distribution WHERE name='whatever2'")
cur.execute("SELECT COUNT(*) FROM PillarName WHERE name='whatever2'")
self.assertEqual(cur.fetchone()[0], 0)
def testDistributionTable(self):
cur = cursor()
# Ensure our sample data is valid and that each Distribution.name
# has a corresponding entry in PillarName.name
cur.execute("""
SELECT COUNT(*)
FROM Distribution FULL OUTER JOIN PillarName
ON Distribution.id = PillarName.distribution
WHERE Distribution.name != PillarName.name
""")
self.failUnlessEqual(cur.fetchone()[0], 0)
def is_in_sync(name):
cur.execute("""
SELECT COUNT(*)
FROM Distribution, PillarName
WHERE Distribution.id = PillarName.distribution
AND Distribution.name = PillarName.name
AND PillarName.product IS NULL
AND PillarName.project IS NULL
AND Distribution.name = %(name)s
""", dict(name=name))
return cur.fetchone()[0] == 1
# Inserting a new Distribution will populate PillarName
cur.execute("""
INSERT INTO Distribution (
name, description, domainname, owner, registrant,
displayname, summary, title, members, mirror_admin
)
VALUES (
'whatever', 'whatever', 'whatever', 1, 1,
'whatever', 'whatever', 'whatever', 1, 1
)
""")
self.failUnless(is_in_sync('whatever'))
# Updating the Distribution.name will propogate changes to PillarName
cur.execute("""
UPDATE Distribution SET name='whatever2' where name='whatever'
""")
self.failUnless(is_in_sync('whatever2'))
# Updating other fields won't do any harm.
cur.execute("""
UPDATE Distribution SET description='whatever2'
WHERE name='whatever2'
""")
self.failUnless(is_in_sync('whatever2'))
# Deleting a Distribution removes the corresponding entry in
# PillarName
cur.execute("DELETE FROM Distribution WHERE name='whatever2'")
cur.execute("SELECT COUNT(*) FROM PillarName WHERE name='whatever2'")
self.failUnlessEqual(cur.fetchone()[0], 0)
def testProjectTable(self):
cur = cursor()
# Ensure our sample data is valid and that each Project.name
# has a corresponding entry in PillarName.name
cur.execute("""
SELECT COUNT(*)
FROM Project FULL OUTER JOIN PillarName
ON Project.id = PillarName.project
WHERE Project.name != PillarName.name
""")
self.failUnlessEqual(cur.fetchone()[0], 0)
def is_in_sync(name):
cur.execute("""
SELECT COUNT(*)
FROM Project, PillarName
WHERE Project.id = PillarName.project
AND Project.name = PillarName.name
AND PillarName.product IS NULL
AND PillarName.distribution IS NULL
AND Project.name = %(name)s
""", dict(name=name))
return cur.fetchone()[0] == 1
# Inserting a new ProjectGroup will populate PillarName
cur.execute("""
INSERT INTO Project (
name, owner, registrant, displayname, title, summary,
description
)
VALUES (
'whatever', 1, 1, 'whatever', 'whatever',
'whatever', 'whatever'
)
""")
self.failUnless(is_in_sync('whatever'))
# Updating the ProjectGroup.name will propogate changes to PillarName
cur.execute("""
UPDATE Project SET name='whatever2' where name='whatever'
""")
self.failUnless(is_in_sync('whatever2'))
# Updating other fields won't do any harm.
cur.execute("""
UPDATE Project SET description='whatever2'
WHERE name='whatever2'
""")
self.failUnless(is_in_sync('whatever2'))
# Deleting a ProjectGroup removes the corresponding entry in
# PillarName.
cur.execute("DELETE FROM Project WHERE name='whatever2'")
cur.execute("SELECT COUNT(*) FROM PillarName WHERE name='whatever2'")
self.failUnlessEqual(cur.fetchone()[0], 0)
def test_autocommit(self):
set_isolation_level('autocommit')
# There is no actual 'autocommit' mode in PostgreSQL. psycopg
# implements this feature by using read committed isolation and
# issuing commit() statements after every query.
self.assertEqual(self.getCurrentIsolation(), 'read committed')
# So we need to confirm we are actually in autocommit mode
# by seeing if we an roll back
cur = cursor()
cur.execute(
"SELECT COUNT(*) FROM Person WHERE homepage_content IS NULL")
self.assertNotEqual(cur.fetchone()[0], 0)
cur.execute("UPDATE Person SET homepage_content=NULL")
transaction.abort()
cur = cursor()
cur.execute(
"SELECT COUNT(*) FROM Person WHERE homepage_content IS NOT NULL")
self.assertEqual(cur.fetchone()[0], 0)
def test_rollback(self):
# Change the isolation level
self.failUnlessEqual(self.getCurrentIsolation(), 'read committed')
set_isolation_level('serializable')
self.failUnlessEqual(self.getCurrentIsolation(), 'serializable')
cur = cursor()
cur.execute("UPDATE Person SET homepage_content=NULL")
transaction.abort()
self.failUnlessEqual(self.getCurrentIsolation(), 'serializable')
def test_rollback(self):
# Change the isolation level
self.assertEqual(self.getCurrentIsolation(), 'read committed')
set_isolation_level('serializable')
self.assertEqual(self.getCurrentIsolation(), 'serializable')
cur = cursor()
cur.execute("UPDATE Person SET homepage_content=NULL")
transaction.abort()
self.assertEqual(self.getCurrentIsolation(), 'serializable')
def remove_all_sample_data_branches():
c = cursor()
c.execute("delete from bugbranch")
c.execute("delete from specificationbranch")
c.execute("update productseries set branch=NULL")
c.execute("delete from branchrevision")
c.execute("delete from branchsubscription")
c.execute("delete from codeimportjob")
c.execute("delete from codeimport")
c.execute("delete from branch")
def test_autocommit(self):
set_isolation_level('autocommit')
# There is no actual 'autocommit' mode in PostgreSQL. psycopg
# implements this feature by using read committed isolation and
# issuing commit() statements after every query.
self.failUnlessEqual(self.getCurrentIsolation(), 'read committed')
# So we need to confirm we are actually in autocommit mode
# by seeing if we an roll back
cur = cursor()
cur.execute(
"SELECT COUNT(*) FROM Person WHERE homepage_content IS NULL")
self.failIfEqual(cur.fetchone()[0], 0)
cur.execute("UPDATE Person SET homepage_content=NULL")
transaction.abort()
cur = cursor()
cur.execute(
"SELECT COUNT(*) FROM Person WHERE homepage_content IS NOT NULL")
self.failUnlessEqual(cur.fetchone()[0], 0)
def remove_all_sample_data_branches():
c = cursor()
c.execute('delete from bugbranch')
c.execute('delete from specificationbranch')
c.execute('update productseries set branch=NULL')
c.execute('delete from branchrevision')
c.execute('delete from branchsubscription')
c.execute('delete from codeimportjob')
c.execute('delete from codeimport')
c.execute('delete from branch')
def getSourcePackageDescriptions(
results, use_names=False, max_title_length=50):
"""Return a dictionary with descriptions keyed on source package names.
Takes an ISelectResults of a *PackageName query. The use_names
flag is a hack that allows this method to work for the
BinaryAndSourcePackageName view, which lacks IDs.
WARNING: this function assumes that there is little overlap and much
coherence in how package names are used, in particular across
distributions if derivation is implemented. IOW, it does not make a
promise to provide The Correct Description, but a pretty good guess
at what the description should be.
"""
# XXX: kiko, 2007-01-17:
# Use_names could be removed if we instead added IDs to the
# BinaryAndSourcePackageName view, but we'd still need to find
# out how to specify the attribute, since it would be
# sourcepackagename_id and binarypackagename_id depending on
# whether the row represented one or both of those cases.
if use_names:
clause = ("SourcePackageName.name in %s" %
sqlvalues([pn.name for pn in results]))
else:
clause = ("SourcePackageName.id in %s" %
sqlvalues([spn.id for spn in results]))
cur = cursor()
cur.execute("""SELECT DISTINCT BinaryPackageName.name,
SourcePackageName.name
FROM BinaryPackageRelease, SourcePackageName,
BinaryPackageBuild, BinaryPackageName
WHERE
BinaryPackageName.id =
BinaryPackageRelease.binarypackagename AND
BinaryPackageRelease.build = BinaryPackageBuild.id AND
BinaryPackageBuild.source_package_name =
SourcePackageName.id AND
%s
ORDER BY BinaryPackageName.name,
SourcePackageName.name"""
% clause)
descriptions = {}
for binarypackagename, sourcepackagename in cur.fetchall():
if not sourcepackagename in descriptions:
descriptions[sourcepackagename] = (
"Source of: %s" % binarypackagename)
else:
if len(descriptions[sourcepackagename]) > max_title_length:
description = "..."
else:
description = ", %s" % binarypackagename
descriptions[sourcepackagename] += description
return descriptions
def getSourcePackageDescriptions(results,
use_names=False,
max_title_length=50):
"""Return a dictionary with descriptions keyed on source package names.
Takes an ISelectResults of a *PackageName query. The use_names
flag is a hack that allows this method to work for the
BinaryAndSourcePackageName view, which lacks IDs.
WARNING: this function assumes that there is little overlap and much
coherence in how package names are used, in particular across
distributions if derivation is implemented. IOW, it does not make a
promise to provide The Correct Description, but a pretty good guess
at what the description should be.
"""
# XXX: kiko, 2007-01-17:
# Use_names could be removed if we instead added IDs to the
# BinaryAndSourcePackageName view, but we'd still need to find
# out how to specify the attribute, since it would be
# sourcepackagename_id and binarypackagename_id depending on
# whether the row represented one or both of those cases.
if use_names:
clause = ("SourcePackageName.name in %s" %
sqlvalues([pn.name for pn in results]))
else:
clause = ("SourcePackageName.id in %s" %
sqlvalues([spn.id for spn in results]))
cur = cursor()
cur.execute("""SELECT DISTINCT BinaryPackageName.name,
SourcePackageName.name
FROM BinaryPackageRelease, SourcePackageName,
BinaryPackageBuild, BinaryPackageName
WHERE
BinaryPackageName.id =
BinaryPackageRelease.binarypackagename AND
BinaryPackageRelease.build = BinaryPackageBuild.id AND
BinaryPackageBuild.source_package_name =
SourcePackageName.id AND
%s
ORDER BY BinaryPackageName.name,
SourcePackageName.name""" % clause)
descriptions = {}
for binarypackagename, sourcepackagename in cur.fetchall():
if not sourcepackagename in descriptions:
descriptions[sourcepackagename] = ("Source of: %s" %
binarypackagename)
else:
if len(descriptions[sourcepackagename]) > max_title_length:
description = "..."
else:
description = ", %s" % binarypackagename
descriptions[sourcepackagename] += description
return descriptions
def test_commit(self):
# Change the isolation level
self.failUnlessEqual(self.getCurrentIsolation(), 'read committed')
set_isolation_level('serializable')
self.failUnlessEqual(self.getCurrentIsolation(), 'serializable')
cur = cursor()
cur.execute("UPDATE Person SET homepage_content=NULL")
transaction.commit()
cur.execute("UPDATE Person SET homepage_content='foo'")
self.failUnlessEqual(self.getCurrentIsolation(), 'serializable')
def fix_plurals_in_all_pofiles(ztm, logger):
"""Go through all PO files and fix plural forms if needed."""
cur = cursor()
cur.execute("""SELECT MAX(id) FROM POFile""")
(max_pofile_id,) = cur.fetchall()[0]
for pofile_id in range(1, max_pofile_id):
try:
pofile = POFile.get(pofile_id)
fix_pofile_plurals(pofile, logger, ztm)
except SQLObjectNotFound:
pass
def _commit(self, transaction_manager):
"""Commit our transaction and create replacement cursor.
Use this as "cur = self._commit(transaction_manager)" to commit a
transaction, restart it, and create a cursor that lives within the new
transaction.
"""
start = time.time()
transaction_manager.commit()
self.logger.debug("Committed in %.3f seconds" % (time.time() - start))
transaction_manager.begin()
return cursor()
def _commit(self, transaction_manager):
"""Commit our transaction and create replacement cursor.
Use this as "cur = self._commit(transaction_manager)" to commit a
transaction, restart it, and create a cursor that lives within the new
transaction.
"""
start = time.time()
transaction_manager.commit()
self.logger.debug("Committed in %.3f seconds" % (time.time() - start))
transaction_manager.begin()
return cursor()
def fix_plurals_in_all_pofiles(ztm, logger):
"""Go through all PO files and fix plural forms if needed."""
cur = cursor()
cur.execute("""SELECT MAX(id) FROM POFile""")
(max_pofile_id, ) = cur.fetchall()[0]
for pofile_id in range(1, max_pofile_id):
try:
pofile = POFile.get(pofile_id)
fix_pofile_plurals(pofile, logger, ztm)
except SQLObjectNotFound:
pass
def _updateRosettaStatistics(self, ztm):
self.update(
'products_using_rosetta',
Product.selectBy(
translations_usage=ServiceUsage.LAUNCHPAD).count())
self.update('potemplate_count', POTemplate.select().count())
ztm.commit()
self.update('pofile_count', POFile.select().count())
ztm.commit()
self.update('pomsgid_count', POMsgID.select().count())
ztm.commit()
self.update('language_count', Language.select(
"POFile.language=Language.id",
clauseTables=['POFile'],
distinct=True).count())
ztm.commit()
cur = cursor()
cur.execute(
"SELECT COUNT(DISTINCT submitter) FROM TranslationMessage")
self.update('translator_count', cur.fetchone()[0] or 0)
ztm.commit()
cur = cursor()
cur.execute("""
SELECT COUNT(DISTINCT submitter)
FROM TranslationMessage
WHERE origin=2
""")
self.update('rosetta_translator_count', cur.fetchone()[0] or 0)
ztm.commit()
cur = cursor()
cur.execute("""
SELECT COUNT(DISTINCT product) FROM ProductSeries,POTemplate
WHERE ProductSeries.id = POTemplate.productseries
""")
self.update('products_with_potemplates', cur.fetchone()[0] or 0)
ztm.commit()
def _updateRosettaStatistics(self, ztm):
self.update(
'products_using_rosetta',
Product.selectBy(
translations_usage=ServiceUsage.LAUNCHPAD).count())
self.update('potemplate_count', POTemplate.select().count())
ztm.commit()
self.update('pofile_count', POFile.select().count())
ztm.commit()
self.update('pomsgid_count', POMsgID.select().count())
ztm.commit()
self.update('language_count', Language.select(
"POFile.language=Language.id",
clauseTables=['POFile'],
distinct=True).count())
ztm.commit()
cur = cursor()
cur.execute(
"SELECT COUNT(DISTINCT submitter) FROM TranslationMessage")
self.update('translator_count', cur.fetchone()[0] or 0)
ztm.commit()
cur = cursor()
cur.execute("""
SELECT COUNT(DISTINCT submitter)
FROM TranslationMessage
WHERE origin=2
""")
self.update('rosetta_translator_count', cur.fetchone()[0] or 0)
ztm.commit()
cur = cursor()
cur.execute("""
SELECT COUNT(DISTINCT product) FROM ProductSeries,POTemplate
WHERE ProductSeries.id = POTemplate.productseries
""")
self.update('products_with_potemplates', cur.fetchone()[0] or 0)
ztm.commit()
def _commit(self):
"""Commit ongoing transaction, start a new one."""
self.transaction_manager.commit()
self.transaction_manager.begin()
self.cur = cursor()
# Disable slow sequential scans. The database server is reluctant to
# use indexes on tables that undergo large changes, such as the
# deletion of large numbers of rows in this case. Usually it's
# right but in this case it seems to slow things down dramatically and
# unnecessarily. We disable sequential scans for every commit since
# initZopeless by default resets our database connection with every
# new transaction.
# MultiTableCopy disables sequential scans for the first batch; this
# just renews our setting after the connection is reset.
postgresql.allow_sequential_scans(self.cur, False)
def _commit(self):
"""Commit ongoing transaction, start a new one."""
self.transaction_manager.commit()
self.transaction_manager.begin()
self.cur = cursor()
# Disable slow sequential scans. The database server is reluctant to
# use indexes on tables that undergo large changes, such as the
# deletion of large numbers of rows in this case. Usually it's
# right but in this case it seems to slow things down dramatically and
# unnecessarily. We disable sequential scans for every commit since
# initZopeless by default resets our database connection with every
# new transaction.
# MultiTableCopy disables sequential scans for the first batch; this
# just renews our setting after the connection is reset.
postgresql.allow_sequential_scans(self.cur, False)
def updateStatus(self, new_remote_status, new_malone_status):
"""See `IBugWatch`."""
for bugtask in self.bug.bugtasks:
if bugtask.conjoined_master is not None:
continue
bugtask = removeSecurityProxy(bugtask)
bugtask._status = new_malone_status
if self.failing:
cur = cursor()
cur.execute("""
UPDATE BugTask
SET assignee = -1
WHERE id = %s
""" % self.bug.bugtasks[0].id)
cur.close()
def updateStatus(self, new_remote_status, new_malone_status):
"""See `IBugWatch`."""
for bugtask in self.bug.bugtasks:
if bugtask.conjoined_master is not None:
continue
bugtask = removeSecurityProxy(bugtask)
bugtask._status = new_malone_status
if self.failing:
cur = cursor()
cur.execute("""
UPDATE BugTask
SET assignee = -1
WHERE id = %s
""" % self.bug.bugtasks[0].id)
cur.close()
def nl_term_candidates(phrase):
"""Returns in an array the candidate search terms from phrase.
Stop words are removed from the phrase and every term is normalized
according to the full text rules (lowercased and stemmed).
:phrase: a search phrase
"""
cur = cursor()
cur.execute("SELECT ftq(%(phrase)s)::text" % sqlvalues(phrase=phrase))
rs = cur.fetchall()
assert len(rs) == 1, "ftq() returned more than one row"
terms = rs[0][0]
if not terms:
# Only stop words
return []
return TS_QUERY_TERM_RE.findall(terms)
def nl_term_candidates(phrase):
"""Returns in an array the candidate search terms from phrase.
Stop words are removed from the phrase and every term is normalized
according to the full text rules (lowercased and stemmed).
:phrase: a search phrase
"""
cur = cursor()
cur.execute("SELECT ftq(%(phrase)s)::text" % sqlvalues(phrase=phrase))
rs = cur.fetchall()
assert len(rs) == 1, "ftq() returned more than one row"
terms = rs[0][0]
if not terms:
# Only stop words
return []
return TS_QUERY_TERM_RE.findall(terms)
def getStatusCountsForProductSeries(self, product_series):
"""See `ISpecificationSet`."""
cur = cursor()
condition = """
(Specification.productseries = %s
OR Milestone.productseries = %s)
""" % sqlvalues(product_series, product_series)
query = """
SELECT Specification.implementation_status, count(*)
FROM Specification
LEFT JOIN Milestone ON Specification.milestone = Milestone.id
WHERE
%s
GROUP BY Specification.implementation_status
""" % condition
cur.execute(query)
return cur.fetchall()
def setUp(self):
super(TestLibrarianGarbageCollection, self).setUp()
self.client = LibrarianClient()
self.patch(librariangc, 'log', BufferLogger())
# A value we use in a number of tests. This represents the
# stay of execution hard coded into the garbage collector.
# We don't destroy any data unless it has been waiting to be
# destroyed for longer than this period. We pick a value
# that is close enough to the stay of execution so that
# forgetting timezone information will break things, but
# far enough so that how long it takes the test to run
# is not an issue. 'stay_of_excution - 1 hour' fits these
# criteria.
self.recent_past = utc_now() - timedelta(days=6, hours=23)
# A time beyond the stay of execution.
self.ancient_past = utc_now() - timedelta(days=30)
self.f1_id, self.f2_id = self._makeDupes()
switch_dbuser(config.librarian_gc.dbuser)
self.ztm = self.layer.txn
# Make sure the files exist. We do this in setup, because we
# need to use the get_file_path method later in the setup and we
# want to be sure it is working correctly.
path = librariangc.get_file_path(self.f1_id)
self.failUnless(os.path.exists(path), "Librarian uploads failed")
# Make sure that every file the database knows about exists on disk.
# We manually remove them for tests that need to cope with missing
# library items.
self.ztm.begin()
cur = cursor()
cur.execute("SELECT id FROM LibraryFileContent")
for content_id in (row[0] for row in cur.fetchall()):
path = librariangc.get_file_path(content_id)
if not os.path.exists(path):
if not os.path.exists(os.path.dirname(path)):
os.makedirs(os.path.dirname(path))
open(path, 'w').write('whatever')
self.ztm.abort()
self.con = connect(
user=config.librarian_gc.dbuser,
isolation=ISOLATION_LEVEL_AUTOCOMMIT)
def getStatusCountsForProductSeries(self, product_series):
"""See `ISpecificationSet`."""
cur = cursor()
condition = """
(Specification.productseries = %s
OR Milestone.productseries = %s)
""" % sqlvalues(product_series, product_series)
query = """
SELECT Specification.implementation_status, count(*)
FROM Specification
LEFT JOIN Milestone ON Specification.milestone = Milestone.id
WHERE
%s
GROUP BY Specification.implementation_status
""" % condition
cur.execute(query)
return cur.fetchall()
def __init__(self, from_table, to_table, transaction_manager, logger,
batch_pouring_callback=None):
self.from_table = str(from_table)
self.to_table = str(to_table)
self.transaction_manager = transaction_manager
self.logger = logger
self.batch_pouring_callback = batch_pouring_callback
self.cur = cursor()
self.cur.execute("SELECT min(id), max(id) FROM %s" % from_table)
self.lowest_id, self.highest_id = self.cur.fetchone()
if self.lowest_id is None:
# Table is empty.
self.lowest_id = 1
self.highest_id = 0
self.logger.debug("Up to %d rows in holding table"
% (self.highest_id + 1 - self.lowest_id))
def iter_sourcepackage_translationdomain_mapping(series):
"""Return an iterator of tuples with sourcepackagename - translationdomain
mapping.
With the output of this method we can know the translationdomains that
a sourcepackage has.
"""
cur = cursor()
cur.execute("""
SELECT SourcePackageName.name, POTemplate.translation_domain
FROM
SourcePackageName
JOIN POTemplate ON
POTemplate.sourcepackagename = SourcePackageName.id AND
POTemplate.distroseries = %s AND
POTemplate.languagepack = TRUE
ORDER BY SourcePackageName.name, POTemplate.translation_domain
""" % sqlvalues(series))
for (sourcepackagename, translationdomain,) in cur.fetchall():
yield (sourcepackagename, translationdomain)
def _updateQuestionStatistics(self, ztm):
self.update('question_count', Question.select().count())
ztm.commit()
self.update(
'answered_question_count',
Question.select(
'status = %s' % sqlvalues(QuestionStatus.ANSWERED)).count())
ztm.commit()
self.update(
'solved_question_count',
Question.select(
'status = %s' % sqlvalues(QuestionStatus.SOLVED)).count())
ztm.commit()
cur = cursor()
cur.execute(
"SELECT COUNT(DISTINCT product) + COUNT(DISTINCT distribution) "
"FROM Question")
self.update("projects_with_questions_count", cur.fetchone()[0] or 0)
ztm.commit()
