def DeleteImplications(self,
file_service_id,
tag_service_id,
implication_tag_ids,
tag_id,
status_hook=None):
if len(implication_tag_ids) == 0:
return
statuses_to_count_delta = collections.Counter()
(cache_current_mappings_table_name, cache_deleted_mappings_table_name,
cache_pending_mappings_table_name
) = ClientDBMappingsStorage.GenerateSpecificMappingsCacheTableNames(
file_service_id, tag_service_id)
(
cache_display_current_mappings_table_name,
cache_display_pending_mappings_table_name
) = ClientDBMappingsStorage.GenerateSpecificDisplayMappingsCacheTableNames(
file_service_id, tag_service_id)
remaining_implication_tag_ids = set(
self.modules_tag_display.GetImpliedBy(
ClientTags.TAG_DISPLAY_ACTUAL, tag_service_id,
tag_id)).difference(implication_tag_ids)
(
current_implication_tag_ids, current_implication_tag_ids_weight,
pending_implication_tag_ids, pending_implication_tag_ids_weight
) = self.modules_mappings_counts.GetCurrentPendingPositiveCountsAndWeights(
ClientTags.TAG_DISPLAY_STORAGE, file_service_id, tag_service_id,
implication_tag_ids)
(
current_remaining_implication_tag_ids,
current_remaining_implication_tag_ids_weight,
pending_remaining_implication_tag_ids,
pending_remaining_implication_tag_ids_weight
) = self.modules_mappings_counts.GetCurrentPendingPositiveCountsAndWeights(
ClientTags.TAG_DISPLAY_STORAGE, file_service_id, tag_service_id,
remaining_implication_tag_ids)
jobs = []
jobs.append(
(HC.CONTENT_STATUS_CURRENT,
cache_display_current_mappings_table_name,
cache_current_mappings_table_name, current_implication_tag_ids,
current_implication_tag_ids_weight,
current_remaining_implication_tag_ids,
current_remaining_implication_tag_ids_weight))
jobs.append(
(HC.CONTENT_STATUS_PENDING,
cache_display_pending_mappings_table_name,
cache_pending_mappings_table_name, pending_implication_tag_ids,
pending_implication_tag_ids_weight,
pending_remaining_implication_tag_ids,
pending_remaining_implication_tag_ids_weight))
for (status, cache_display_mappings_table_name,
cache_mappings_table_name, removee_tag_ids,
removee_tag_ids_weight, keep_tag_ids,
keep_tag_ids_weight) in jobs:
if removee_tag_ids_weight == 0:
# nothing to remove, so nothing to do!
continue
# ultimately here, we are doing "delete all display mappings with hash_ids that have a storage mapping for a removee tag and no storage mappings for a keep tag
# in order to reduce overhead, we go full meme and do a bunch of different situations
with self._MakeTemporaryIntegerTable(
[], 'tag_id') as temp_removee_tag_ids_table_name:
with self._MakeTemporaryIntegerTable(
[], 'tag_id') as temp_keep_tag_ids_table_name:
if len(removee_tag_ids) == 1:
(removee_tag_id, ) = removee_tag_ids
hash_id_in_storage_remove = 'hash_id IN ( SELECT hash_id FROM {} WHERE tag_id = {} )'.format(
cache_mappings_table_name, removee_tag_id)
else:
self._ExecuteMany(
'INSERT INTO {} ( tag_id ) VALUES ( ? );'.format(
temp_removee_tag_ids_table_name),
((removee_tag_id, )
for removee_tag_id in removee_tag_ids))
hash_id_in_storage_remove = 'hash_id IN ( SELECT DISTINCT hash_id FROM {} CROSS JOIN {} USING ( tag_id ) )'.format(
temp_removee_tag_ids_table_name,
cache_mappings_table_name)
if keep_tag_ids_weight == 0:
predicates_phrase = hash_id_in_storage_remove
else:
# WARNING, WARNING: Big Brain Query, potentially great/awful
# note that in the 'clever/file join' situation, the number of total mappings is many, but we are deleting a few
# we want to precisely scan the status of the potential hashes to delete, not scan through them all to see what not to do
# therefore, we do NOT EXISTS, which just scans the parts, rather than NOT IN, which does the whole query and then checks against all results
if len(keep_tag_ids) == 1:
(keep_tag_id, ) = keep_tag_ids
if ClientDBMappingsStorage.DoingAFileJoinTagSearchIsFaster(
removee_tag_ids_weight,
keep_tag_ids_weight):
hash_id_not_in_storage_keep = 'NOT EXISTS ( SELECT 1 FROM {} WHERE {}.hash_id = {}.hash_id and tag_id = {} )'.format(
cache_mappings_table_name,
cache_display_mappings_table_name,
cache_mappings_table_name, keep_tag_id)
else:
hash_id_not_in_storage_keep = 'hash_id NOT IN ( SELECT hash_id FROM {} WHERE tag_id = {} )'.format(
cache_mappings_table_name, keep_tag_id)
else:
self._ExecuteMany(
'INSERT INTO {} ( tag_id ) VALUES ( ? );'.
format(temp_keep_tag_ids_table_name),
((keep_tag_id, )
for keep_tag_id in keep_tag_ids))
if ClientDBMappingsStorage.DoingAFileJoinTagSearchIsFaster(
removee_tag_ids_weight,
keep_tag_ids_weight):
# (files to) mappings to temp tags
hash_id_not_in_storage_keep = 'NOT EXISTS ( SELECT 1 FROM {} CROSS JOIN {} USING ( tag_id ) WHERE {}.hash_id = {}.hash_id )'.format(
cache_mappings_table_name,
temp_keep_tag_ids_table_name,
cache_display_mappings_table_name,
cache_mappings_table_name)
else:
# temp tags to mappings
hash_id_not_in_storage_keep = ' hash_id NOT IN ( SELECT DISTINCT hash_id FROM {} CROSS JOIN {} USING ( tag_id ) )'.format(
temp_keep_tag_ids_table_name,
cache_mappings_table_name)
predicates_phrase = '{} AND {}'.format(
hash_id_in_storage_remove,
hash_id_not_in_storage_keep)
query = 'DELETE FROM {} WHERE tag_id = {} AND {};'.format(
cache_display_mappings_table_name, tag_id,
predicates_phrase)
self._Execute(query)
statuses_to_count_delta[status] = self._GetRowCount()
current_delta = statuses_to_count_delta[HC.CONTENT_STATUS_CURRENT]
pending_delta = statuses_to_count_delta[HC.CONTENT_STATUS_PENDING]
if current_delta > 0 or pending_delta > 0:
counts_cache_changes = ((tag_id, current_delta, pending_delta), )
self.modules_mappings_counts_update.ReduceCounts(
ClientTags.TAG_DISPLAY_ACTUAL, file_service_id, tag_service_id,
counts_cache_changes)
def GetHashIdsFromTagIds( self, tag_display_type: int, file_service_key: bytes, tag_search_context: ClientSearch.TagSearchContext, tag_ids: typing.Collection[ int ], hash_ids = None, hash_ids_table_name = None, job_key = None ):
do_hash_table_join = False
if hash_ids_table_name is not None and hash_ids is not None:
tag_service_id = self.modules_services.GetServiceId( tag_search_context.service_key )
file_service_id = self.modules_services.GetServiceId( file_service_key )
estimated_count = self.modules_mappings_counts.GetAutocompleteCountEstimate( tag_display_type, tag_service_id, file_service_id, tag_ids, tag_search_context.include_current_tags, tag_search_context.include_pending_tags )
if ClientDBMappingsStorage.DoingAFileJoinTagSearchIsFaster( len( hash_ids ), estimated_count ):
do_hash_table_join = True
result_hash_ids = set()
table_names = self.modules_tag_search.GetMappingTables( tag_display_type, file_service_key, tag_search_context )
cancelled_hook = None
if job_key is not None:
cancelled_hook = job_key.IsCancelled
if len( tag_ids ) == 1:
( tag_id, ) = tag_ids
if do_hash_table_join:
# temp hashes to mappings
queries = [ 'SELECT hash_id FROM {} CROSS JOIN {} USING ( hash_id ) WHERE tag_id = ?'.format( hash_ids_table_name, table_name ) for table_name in table_names ]
else:
queries = [ 'SELECT hash_id FROM {} WHERE tag_id = ?;'.format( table_name ) for table_name in table_names ]
for query in queries:
cursor = self._Execute( query, ( tag_id, ) )
result_hash_ids.update( self._STI( HydrusDB.ReadFromCancellableCursor( cursor, 1024, cancelled_hook ) ) )
else:
with self._MakeTemporaryIntegerTable( tag_ids, 'tag_id' ) as temp_tag_ids_table_name:
if do_hash_table_join:
# temp hashes to mappings to temp tags
# old method, does not do EXISTS efficiently, it makes a list instead and checks that
# queries = [ 'SELECT hash_id FROM {} WHERE EXISTS ( SELECT 1 FROM {} CROSS JOIN {} USING ( tag_id ) WHERE {}.hash_id = {}.hash_id );'.format( hash_ids_table_name, table_name, temp_tag_ids_table_name, table_name, hash_ids_table_name ) for table_name in table_names ]
# new method, this seems to actually do the correlated scalar subquery, although it does seem to be sqlite voodoo
queries = [ 'SELECT hash_id FROM {} WHERE EXISTS ( SELECT 1 FROM {} WHERE {}.hash_id = {}.hash_id AND EXISTS ( SELECT 1 FROM {} WHERE {}.tag_id = {}.tag_id ) );'.format( hash_ids_table_name, table_name, table_name, hash_ids_table_name, temp_tag_ids_table_name, table_name, temp_tag_ids_table_name ) for table_name in table_names ]
else:
# temp tags to mappings
queries = [ 'SELECT hash_id FROM {} CROSS JOIN {} USING ( tag_id );'.format( temp_tag_ids_table_name, table_name ) for table_name in table_names ]
for query in queries:
cursor = self._Execute( query )
result_hash_ids.update( self._STI( HydrusDB.ReadFromCancellableCursor( cursor, 1024, cancelled_hook ) ) )
return result_hash_ids