def _find_sellable(self, code=None, barcode=None):
"""Find a sellable given a code or barcode.
When searching using the code attribute of the sellable, the search will
be case insensitive.
:returns: The sellable that matches the given barcode or code or
``None`` if nothing was found.
"""
viewable, default_query = self.get_sellable_view_query()
if barcode:
query = (Lower(viewable.barcode) == barcode.lower())
else:
query = (Lower(viewable.code) == code.lower())
if default_query:
query = And(query, default_query)
# FIXME: doing list() here is wrong. But there is a bug in one of
# the queries, that len() == 1 but results.count() == 2.
results = list(self.store.find(viewable, query))
if len(results) != 1:
return None
return results[0].sellable
def _parse_string_state(self, state, table_field):
if not state.text.strip():
return
def _like(value):
return Like(StoqNormalizeString(table_field),
StoqNormalizeString(u'%%%s%%' % value.lower()),
case_sensitive=False)
if state.mode == StringQueryState.CONTAINS_ALL:
queries = [
_like(word) for word in re.split('[ \n\r]', state.text) if word
]
retval = And(*queries)
elif state.mode == StringQueryState.IDENTICAL_TO:
retval = Lower(table_field) == state.text.lower()
elif state.mode == StringQueryState.CONTAINS_EXACTLY:
retval = (_like(state.text.lower()))
elif state.mode == StringQueryState.NOT_CONTAINS:
queries = [
Not(_like(word)) for word in state.text.split(' ') if word
]
retval = And(*queries)
else: # pragma nocoverage
raise AssertionError
return retval
def apply_patch(store):
cities = store.find(CityLocation, CityLocation.country != _COUNTRY_MARKER)
for city_location in cities:
clause = And(
Lower(CityLocation.state) == city_location.state.lower(),
(StoqNormalizeString(CityLocation.city) == StoqNormalizeString(
city_location.city)))
alikes = list(store.find(CityLocation, clause))
if len(alikes) > 1:
for location in alikes:
if location.country == _COUNTRY_MARKER:
# This is a new city location we just added on
# the last patch. Use it for right_location
right_location = location
break
else:
right_location = alikes[0]
in_str = ', '.join(
[str(cl.id) for cl in alikes if cl != right_location])
# Make all alikes point to right_location and remove them
store.execute("""
UPDATE address
SET city_location_id = %(right_location_id)d
WHERE city_location_id IN (%(in_str)s);
UPDATE individual
SET birth_location_id = %(right_location_id)d
WHERE birth_location_id IN (%(in_str)s);
DELETE FROM city_location WHERE id IN (%(in_str)s);
""" % dict(right_location_id=right_location.id, in_str=in_str))
# Now it's safe to return __BRA__ to Brazil
store.execute("""
UPDATE city_location
SET country = '%s' WHERE country = '%s';
""" % (u'Brazil', _COUNTRY_MARKER))
# Also, do s/Brasil/Brazil/ for city_locations registered that way,
# maybe because of birth location that had country as a textfield.
# It's safe to do this since we did the normalization above
# not taking country in consideration.
store.execute("""
UPDATE city_location
SET country = 'Brazil' WHERE lower(country) = 'brasil';
""")
# Since COUNTRY_SUGGESTED was a free field, try to correct it if
# some user changed it to Brasil (with 's' instead of 'z').
store.execute("""
UPDATE parameter_data
SET field_value = 'Brazil'
WHERE lower(field_value) = 'brasil' AND field_name = 'COUNTRY_SUGGESTED';
""")
def _find_sellable_and_batch(self, text):
"""Find a sellable given a code, barcode or batch_number
When searching using the code attribute of the sellable, the search will
be case insensitive.
:param text: the code, barcode or batch_number
:returns: The sellable that matches the given barcode or code or
``None`` if nothing was found.
"""
viewable, default_query = self.get_sellable_view_query()
# FIXME: Put this logic for getting the sellable based on
# barcode/code/batch_number on domain. Note that something very
# simular is done on POS app
# First try barcode, then code since there might be a product
# with a code equal to another product's barcode
for attr in [viewable.barcode, viewable.code]:
query = Lower(attr) == text.lower()
if default_query:
query = And(query, default_query)
result = self.store.find(viewable, query).one()
if result:
return result.sellable, None
# if none of the above worked, try to find by batch number
query = Lower(StorableBatch.batch_number) == text.lower()
batch = self.store.find(StorableBatch, query).one()
if batch:
sellable = batch.storable.product.sellable
query = viewable.id == sellable.id
if default_query:
query = And(query, default_query)
# Make sure batch's sellable is in the view
if not self.store.find(viewable, query).is_empty():
return sellable, batch
return None, None
def lower(self):
return Lower(self)
def close_account(username, log):
"""Close a person's account.
Return True on success, or log an error message and return False
"""
store = IMasterStore(Person)
janitor = getUtility(ILaunchpadCelebrities).janitor
cur = cursor()
references = list(postgresql.listReferences(cur, 'person', 'id'))
postgresql.check_indirect_references(references)
person = store.using(
Person,
LeftJoin(EmailAddress, Person.id == EmailAddress.personID)).find(
Person,
Or(Person.name == username,
Lower(EmailAddress.email) == Lower(username))).one()
if person is None:
raise LaunchpadScriptFailure("User %s does not exist" % username)
person_name = person.name
# We don't do teams
if person.is_team:
raise LaunchpadScriptFailure("%s is a team" % person_name)
log.info("Closing %s's account" % person_name)
def table_notification(table):
log.debug("Handling the %s table" % table)
# All names starting with 'removed' are blacklisted, so this will always
# succeed.
new_name = 'removed%d' % person.id
# Some references can safely remain in place and link to the cleaned-out
# Person row.
skip = {
# These references express some kind of audit trail. The actions in
# question still happened, and in some cases the rows may still have
# functional significance (e.g. subscriptions or access grants), but
# we no longer identify the actor.
('accessartifactgrant', 'grantor'),
('accesspolicygrant', 'grantor'),
('binarypackagepublishinghistory', 'removed_by'),
('branch', 'registrant'),
('branchmergeproposal', 'merge_reporter'),
('branchmergeproposal', 'merger'),
('branchmergeproposal', 'queuer'),
('branchmergeproposal', 'registrant'),
('branchmergeproposal', 'reviewer'),
('branchsubscription', 'subscribed_by'),
('bug', 'owner'),
('bug', 'who_made_private'),
('bugactivity', 'person'),
('bugnomination', 'decider'),
('bugnomination', 'owner'),
('bugtask', 'owner'),
('bugsubscription', 'subscribed_by'),
('codeimport', 'owner'),
('codeimport', 'registrant'),
('codeimportevent', 'person'),
('faq', 'last_updated_by'),
('featureflagchangelogentry', 'person'),
('gitactivity', 'changee'),
('gitactivity', 'changer'),
('gitrepository', 'registrant'),
('gitrule', 'creator'),
('gitrulegrant', 'grantor'),
('gitsubscription', 'subscribed_by'),
('message', 'owner'),
('messageapproval', 'disposed_by'),
('messageapproval', 'posted_by'),
('packagecopyrequest', 'requester'),
('packagediff', 'requester'),
('packageupload', 'signing_key_owner'),
('personlocation', 'last_modified_by'),
('persontransferjob', 'major_person'),
('persontransferjob', 'minor_person'),
('poexportrequest', 'person'),
('pofile', 'lasttranslator'),
('pofiletranslator', 'person'),
('product', 'registrant'),
('question', 'answerer'),
('questionreopening', 'answerer'),
('questionreopening', 'reopener'),
('snapbuild', 'requester'),
('sourcepackagepublishinghistory', 'creator'),
('sourcepackagepublishinghistory', 'removed_by'),
('sourcepackagepublishinghistory', 'sponsor'),
('sourcepackagerecipebuild', 'requester'),
('sourcepackagerelease', 'creator'),
('sourcepackagerelease', 'maintainer'),
('sourcepackagerelease', 'signing_key_owner'),
('specification', 'approver'),
('specification', 'completer'),
('specification', 'drafter'),
('specification', 'goal_decider'),
('specification', 'goal_proposer'),
('specification', 'last_changed_by'),
('specification', 'starter'),
('structuralsubscription', 'subscribed_by'),
('teammembership', 'acknowledged_by'),
('teammembership', 'proposed_by'),
('teammembership', 'reviewed_by'),
('translationimportqueueentry', 'importer'),
('translationmessage', 'reviewer'),
('translationmessage', 'submitter'),
('translationrelicensingagreement', 'person'),
('usertouseremail', 'recipient'),
('usertouseremail', 'sender'),
('xref', 'creator'),
# This is maintained by trigger functions and a garbo job. It
# doesn't need to be updated immediately.
('bugsummary', 'viewed_by'),
# XXX cjwatson 2019-05-02 bug=1827399: This is suboptimal because it
# does retain some personal information, but it's currently hard to
# deal with due to the size and complexity of references to it. We
# can hopefully provide a garbo job for this eventually.
('revisionauthor', 'person'),
}
reference_names = {(src_tab, src_col)
for src_tab, src_col, _, _, _, _ in references}
for src_tab, src_col in skip:
if (src_tab, src_col) not in reference_names:
raise AssertionError(
"%s.%s is not a Person reference; possible typo?" %
(src_tab, src_col))
# XXX cjwatson 2018-11-29: Registrants could possibly be left as-is, but
# perhaps we should pretend that the registrant was ~registry in that
# case instead?
# Remove the EmailAddress. This is the most important step, as
# people requesting account removal seem to primarily be interested
# in ensuring we no longer store this information.
table_notification('EmailAddress')
store.find(EmailAddress, EmailAddress.personID == person.id).remove()
# Clean out personal details from the Person table
table_notification('Person')
person.display_name = 'Removed by request'
person.name = new_name
person.homepage_content = None
person.icon = None
person.mugshot = None
person.hide_email_addresses = False
person.registrant = None
person.logo = None
person.creation_rationale = PersonCreationRationale.UNKNOWN
person.creation_comment = None
# Keep the corresponding PersonSettings row, but reset everything to the
# defaults.
table_notification('PersonSettings')
store.find(PersonSettings, PersonSettings.personID == person.id).set(
selfgenerated_bugnotifications=DEFAULT,
# XXX cjwatson 2018-11-29: These two columns have NULL defaults, but
# perhaps shouldn't?
expanded_notification_footers=False,
require_strong_email_authentication=False)
skip.add(('personsettings', 'person'))
# Remove almost everything from the Account row and the corresponding
# OpenIdIdentifier rows, preserving only a minimal audit trail.
if person.account is not None:
table_notification('Account')
account = removeSecurityProxy(person.account)
account.displayname = 'Removed by request'
account.creation_rationale = AccountCreationRationale.UNKNOWN
person.setAccountStatus(AccountStatus.CLOSED, janitor,
"Closed using close-account.")
table_notification('OpenIdIdentifier')
store.find(OpenIdIdentifier,
OpenIdIdentifier.account_id == account.id).remove()
# Reassign their bugs
table_notification('BugTask')
store.find(BugTask, BugTask.assigneeID == person.id).set(assigneeID=None)
# Reassign questions assigned to the user, and close all their questions
# in non-final states since nobody else can.
table_notification('Question')
store.find(Question, Question.assigneeID == person.id).set(assigneeID=None)
owned_non_final_questions = store.find(
Question, Question.ownerID == person.id,
Question.status.is_in([
QuestionStatus.OPEN,
QuestionStatus.NEEDSINFO,
QuestionStatus.ANSWERED,
]))
owned_non_final_questions.set(
status=QuestionStatus.SOLVED,
whiteboard=(
'Closed by Launchpad due to owner requesting account removal'))
skip.add(('question', 'owner'))
# Remove rows from tables in simple cases in the given order
removals = [
# Trash their email addresses. People who request complete account
# removal would be unhappy if they reregistered with their old email
# address and this resurrected their deleted account, as the email
# address is probably the piece of data we store that they were most
# concerned with being removed from our systems.
('EmailAddress', 'person'),
# Trash their codes of conduct and GPG keys
('SignedCodeOfConduct', 'owner'),
('GpgKey', 'owner'),
# Subscriptions and notifications
('BranchSubscription', 'person'),
('BugMute', 'person'),
('BugNotificationRecipient', 'person'),
('BugSubscription', 'person'),
('BugSubscriptionFilterMute', 'person'),
('GitSubscription', 'person'),
('MailingListSubscription', 'person'),
('QuestionSubscription', 'person'),
('SpecificationSubscription', 'person'),
('StructuralSubscription', 'subscriber'),
# Personal stuff, freeing up the namespace for others who want to play
# or just to remove any fingerprints identifying the user.
('IrcId', 'person'),
('JabberId', 'person'),
('WikiName', 'person'),
('PersonLanguage', 'person'),
('PersonLocation', 'person'),
('SshKey', 'person'),
# Karma
('Karma', 'person'),
('KarmaCache', 'person'),
('KarmaTotalCache', 'person'),
# Team memberships
('TeamMembership', 'person'),
('TeamParticipation', 'person'),
# Contacts
('AnswerContact', 'person'),
# Pending items in queues
('POExportRequest', 'person'),
# Access grants
('AccessArtifactGrant', 'grantee'),
('AccessPolicyGrant', 'grantee'),
('ArchivePermission', 'person'),
('GitRuleGrant', 'grantee'),
('SharingJob', 'grantee'),
# Soyuz reporting
('LatestPersonSourcePackageReleaseCache', 'creator'),
('LatestPersonSourcePackageReleaseCache', 'maintainer'),
# "Affects me too" information
('BugAffectsPerson', 'person'),
]
for table, person_id_column in removals:
table_notification(table)
store.execute(
"""
DELETE FROM %(table)s WHERE %(person_id_column)s = ?
""" % {
'table': table,
'person_id_column': person_id_column,
}, (person.id, ))
# Trash Sprint Attendance records in the future.
table_notification('SprintAttendance')
store.execute(
"""
DELETE FROM SprintAttendance
USING Sprint
WHERE Sprint.id = SprintAttendance.sprint
AND attendee = ?
AND Sprint.time_starts > CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
""", (person.id, ))
# Any remaining past sprint attendance records can harmlessly refer to
# the placeholder person row.
skip.add(('sprintattendance', 'attendee'))
# generate_ppa_htaccess currently relies on seeing active
# ArchiveAuthToken rows so that it knows which ones to remove from
# .htpasswd files on disk in response to the cancellation of the
# corresponding ArchiveSubscriber rows; but even once PPA authorisation
# is handled dynamically, we probably still want to have the per-person
# audit trail here.
archive_subscriber_ids = set(
store.find(
ArchiveSubscriber.id, ArchiveSubscriber.subscriber_id == person.id,
ArchiveSubscriber.status == ArchiveSubscriberStatus.CURRENT))
if archive_subscriber_ids:
getUtility(IArchiveSubscriberSet).cancel(archive_subscriber_ids,
janitor)
skip.add(('archivesubscriber', 'subscriber'))
skip.add(('archiveauthtoken', 'person'))
# Remove hardware submissions.
table_notification('HWSubmissionDevice')
store.execute(
"""
DELETE FROM HWSubmissionDevice
USING HWSubmission
WHERE HWSubmission.id = HWSubmissionDevice.submission
AND owner = ?
""", (person.id, ))
table_notification('HWSubmission')
store.find(HWSubmission, HWSubmission.ownerID == person.id).remove()
has_references = False
# Check for active related projects, and skip inactive ones.
for col in 'bug_supervisor', 'driver', 'owner':
# Raw SQL because otherwise using Product._owner while displaying it
# as Product.owner is too fiddly.
result = store.execute(
"""
SELECT COUNT(*) FROM product WHERE active AND %(col)s = ?
""" % {'col': col}, (person.id, ))
count = result.get_one()[0]
if count:
log.error("User %s is still referenced by %d product.%s values" %
(person_name, count, col))
has_references = True
skip.add(('product', col))
for col in 'driver', 'owner':
count = store.find(ProductSeries, ProductSeries.product == Product.id,
Product.active,
getattr(ProductSeries, col) == person).count()
if count:
log.error(
"User %s is still referenced by %d productseries.%s values" %
(person_name, count, col))
has_references = True
skip.add(('productseries', col))
# Closing the account will only work if all references have been handled
# by this point. If not, it's safer to bail out. It's OK if this
# doesn't work in all conceivable situations, since some of them may
# require careful thought and decisions by a human administrator.
for src_tab, src_col, ref_tab, ref_col, updact, delact in references:
if (src_tab, src_col) in skip:
continue
result = store.execute(
"""
SELECT COUNT(*) FROM %(src_tab)s WHERE %(src_col)s = ?
""" % {
'src_tab': src_tab,
'src_col': src_col,
}, (person.id, ))
count = result.get_one()[0]
if count:
log.error("User %s is still referenced by %d %s.%s values" %
(person_name, count, src_tab, src_col))
has_references = True
if has_references:
raise LaunchpadScriptFailure("User %s is still referenced" %
person_name)
return True