def interpret(self): #extra_users_str if len(self.extra_users) > 0: comma_list = helpers.to_commalist(self.extra_users, lambda x: "'" + x.interpret() + "'", ", ") extra_users_str = " i.user_login IN (" + comma_list + ") " else: extra_users_str = " 1 " #extra_paths_str if len(self.extra_paths) > 0: extra_paths_str = helpers.to_commalist( \ self.extra_paths, lambda x: "data_refs.url LIKE '" + x.interpret() + "%'", " OR ") else: extra_paths_str = " 1 " #extra_titles_str if len(self.extra_titles) > 0: extra_titles_str = helpers.to_commalist( \ self.extra_titles, lambda x: "i.title LIKE '%" + x.interpret() + "%'", " OR ") else: extra_titles_str = " 1 " #TODO filter items by item_tags.user_login and item_fields.user_login also s = ''' --SingleExtraClause.interpret() select distinct i.*, ''' + db_schema.DataRef._sql_from() + ''' from items i left join data_refs on data_refs.id = i.data_ref_id where (''' + extra_users_str + ''') and (''' + extra_paths_str + ''') and (''' + extra_titles_str + ''') ''' return s
def __formatErrorSetShort(error_set): if error_set is None: return "" if len(error_set) <= 0: return self.tr("OK") elif len(error_set) > 0: return helpers.to_commalist(error_set, lambda x: __formatErrorShort(x))
def interpret(self): #extra_users_str if len(self.extra_users) > 0: comma_list = helpers.to_commalist( self.extra_users, lambda x: "'" + x.interpret() + "'", ", ") extra_users_str = " i.user_login IN (" + comma_list + ") " else: extra_users_str = " 1 " #extra_paths_str if len(self.extra_paths) > 0: extra_paths_str = helpers.to_commalist( \ self.extra_paths, lambda x: "data_refs.url LIKE '" + x.interpret() + "%'", " OR ") else: extra_paths_str = " 1 " #extra_titles_str if len(self.extra_titles) > 0: extra_titles_str = helpers.to_commalist( \ self.extra_titles, lambda x: "i.title LIKE '%" + x.interpret() + "%'", " OR ") else: extra_titles_str = " 1 " #TODO filter items by item_tags.user_login and item_fields.user_login also s = ''' --SingleExtraClause.interpret() select distinct i.*, ''' + db_schema.DataRef._sql_from() + ''' from items i left join data_refs on data_refs.id = i.data_ref_id where (''' + extra_users_str + ''') and (''' + extra_paths_str + ''') and (''' + extra_titles_str + ''') ''' return s
def __updateCategoryDependentWidgets(self, groupIndex): if groupIndex >= 0: appDescription = self.__extAppMgrState.appDescriptions[groupIndex] self.ui.lineEditAppCmd.setText(appDescription.appCommandPattern) self.ui.lineEditAppCmd.setEnabled(True) self.ui.lineEditFileExtensions.setText( helpers.to_commalist(appDescription.fileExtentions, apply_each=str, sep=" ")) self.ui.lineEditFileExtensions.setEnabled(True) else: self.ui.lineEditAppCmd.setText("") self.ui.lineEditAppCmd.setEnabled(False) self.ui.lineEditFileExtensions.setText("") self.ui.lineEditFileExtensions.setEnabled(False)
def interpret(self): #yes_tags_str, group_by_having group_by_having = "" if len(self.yes_tags) > 0: yes_tags_str = helpers.to_commalist( self.yes_tags, lambda x: "t.name='" + x.interpret() + "'", " or ") if len(self.yes_tags) > 1: group_by_having = " group by i.id having count(*)={} ".format( len(self.yes_tags)) else: yes_tags_str = " 1 " #extra_users_str if len(self.extra_users) > 0: comma_list = helpers.to_commalist( self.extra_users, lambda x: "'" + x.interpret() + "'", ", ") extra_users_str = " it.user_login IN (" + comma_list + ") " else: extra_users_str = " 1 " #extra_paths_str if len(self.extra_paths) > 0: extra_paths_str = helpers.to_commalist( \ self.extra_paths, lambda x: "data_refs.url LIKE '" + x.interpret() + "%'", " OR ") else: extra_paths_str = " 1 " #extra_titles_str if len(self.extra_titles) > 0: extra_titles_str = helpers.to_commalist( \ self.extra_titles, lambda x: "i.title LIKE '%" + x.interpret() + "%'", " OR ") else: extra_titles_str = " 1 " #no_tags_str if len(self.no_tags) > 0: no_tags_str = " i.id NOT IN (select i.id from items i " + \ " left join items_tags it on i.id = it.item_id " + \ " left join tags t on t.id = it.tag_id " + \ " where (" + helpers.to_commalist(self.no_tags, lambda x: "t.name='" + x.interpret() + "'", " or ") + ") " + ") " else: no_tags_str = " 1 " s = ''' --TagsConjunction.interpret() select distinct i.*, ''' + db_schema.DataRef._sql_from() + ''' from items i left join items_tags it on i.id = it.item_id left join tags t on t.id = it.tag_id left join data_refs on data_refs.id = i.data_ref_id where (''' + yes_tags_str + ''') and (''' + extra_users_str + ''') and (''' + no_tags_str + ''') and (''' + extra_paths_str + ''') and (''' + extra_titles_str + ''') ''' + group_by_having return s
def interpret(self): #extra_users_str if len(self.extra_users) > 0: users_comma_list = helpers.to_commalist( self.extra_users, lambda x: "'" + x.interpret() + "'", ", ") else: users_comma_list = None #extra_titles_str if len(self.extra_titles) > 0: extra_titles_str = helpers.to_commalist( \ self.extra_titles, lambda x: "i.title LIKE '%" + x.interpret() + "%'", " OR ") else: extra_titles_str = " 1 " from_parts = [] where_parts = [] i = 1 for field_op_val in self.field_op_vals: extra_users_str = "and if{0}.user_login IN ({1})".format(i, users_comma_list) \ if users_comma_list else "" from_part = ''' inner join items_fields if{0} on if{0}.item_id = i.id inner join fields f{0} on f{0}.id = if{0}.field_id '''.format( str(i)) if i > 1: from_part = from_part + ''' and if{0}.field_id <> if{1}.field_id '''.format( i - 1, i) from_parts.append(from_part) where_part = '''f{0}.name = '{1}' and {2} {3}'''.format( i, field_op_val.name, field_op_val.interpret(i), extra_users_str) where_parts.append(where_part) i = i + 1 #from_str from_str = "" for from_part in from_parts: from_str = from_str + from_part #where_str where_str = helpers.to_commalist(where_parts, lambda x: x, " and \n") #extra_paths_str if len(self.extra_paths) > 0: extra_paths_str = helpers.to_commalist( \ self.extra_paths, lambda x: "data_refs.url LIKE '" + x.interpret() + "%'", " OR ") else: extra_paths_str = " 1 " s = ''' --FieldsConjunction.interpret() select distinct i.*, ''' + db_schema.DataRef._sql_from() + ''' from items i ''' + from_str + ''' left join data_refs on data_refs.id = i.data_ref_id where (''' + where_str + ''') and (''' + extra_paths_str + ''') and (''' + extra_titles_str + ''') ''' return s
def formatItemIds(row, finfo, role): if role == Qt.DisplayRole: return helpers.to_commalist(finfo.itemIds) return None
def formatTags(row, finfo, role): if role == Qt.DisplayRole: return helpers.to_commalist(finfo.tags) return None
def __getRelatedTags(self, tag_names, user_logins, limit): #TODO user_logins is not used yet.. if len(tag_names) == 0: #TODO The more items in the repository, the slower this query is performed. #I think, I should store in database some statistics information, such as number of items #tagged with each tag. With this information, the query can be rewritten and became much faster. if limit > 0: sql = ''' select name, c from (select t.name as name, count(*) as c from items i, tags t join items_tags it on it.tag_id = t.id and it.item_id = i.id and i.alive where 1 group by t.name ORDER BY c DESC LIMIT ''' + str(limit) + ''') as sub ORDER BY name ''' else: sql = ''' --get_related_tags() query select t.name as name, count(*) as c from items i, tags t join items_tags it on it.tag_id = t.id and it.item_id = i.id and i.alive where 1 group by t.name ORDER BY t.name ''' # ResourceClosedError could be raised when there are no related tags # for given list of tags. try: return self._session.query("name", "c").from_statement(sql).all() except ResourceClosedError: return [] else: # First we get a list of item ids sql = '''--get_related_tags(): getting list of id for all selected tags select * from tags t where t.name in (''' + hlp.to_commalist( tag_names, lambda x: "'" + x + "'") + ''') order by t.id''' try: tags = self._session.query(db.Tag).from_statement(sql).all() except ResourceClosedError: tags = [] tag_ids = [] for tag in tags: tag_ids.append(tag.id) if len(tag_ids) == 0: #TODO Maybe raise an exception? return [] sub_from = "" for i in range(len(tag_ids)): if i == 0: sub_from = sub_from + " items_tags it{} ".format(i + 1) else: sub_from = sub_from + \ (" join items_tags it{1} on it{1}.item_id=it{0}.item_id " + \ " AND it{1}.tag_id > it{0}.tag_id ").format(i, i+1) sub_where = "" for i in range(len(tag_ids)): if i == 0: sub_where = sub_where + \ " it{0}.tag_id = {1} ".format(i+1, tag_ids[i]) else: sub_where = sub_where + \ " AND it{0}.tag_id = {1} ".format(i+1, tag_ids[i]) where = "" for i in range(len(tag_ids)): where = where + \ " AND t.id <> {0} ".format(tag_ids[i]) sql = '''--get_related_tags() query select t.name as name, count(*) as c from tags t join items_tags it on it.tag_id = t.id join items i on i.id = it.item_id where it.item_id IN ( select it1.item_id from ''' + sub_from + ''' where ''' + sub_where + ''' ) ''' + where + ''' AND i.alive -- It is important that these ids followed in the order of raising group by t.name ORDER BY t.name''' try: return self._session.query("name", "c").from_statement(sql).all() except ResourceClosedError: return []
def __getRelatedTags(self, tag_names, user_logins, limit): #TODO user_logins is not used yet.. if len(tag_names) == 0: #TODO The more items in the repository, the slower this query is performed. #I think, I should store in database some statistics information, such as number of items #tagged with each tag. With this information, the query can be rewritten and became much faster. if limit > 0: sql = ''' select name, c from (select t.name as name, count(*) as c from items i, tags t join items_tags it on it.tag_id = t.id and it.item_id = i.id and i.alive where 1 group by t.name ORDER BY c DESC LIMIT ''' + str(limit) + ''') as sub ORDER BY name ''' else: sql = ''' --get_related_tags() query select t.name as name, count(*) as c from items i, tags t join items_tags it on it.tag_id = t.id and it.item_id = i.id and i.alive where 1 group by t.name ORDER BY t.name ''' # ResourceClosedError could be raised when there are no related tags # for given list of tags. try: return self._session.query("name", "c").from_statement(sql).all() except ResourceClosedError: return [] else: # First we get a list of item ids sql = '''--get_related_tags(): getting list of id for all selected tags select * from tags t where t.name in (''' + hlp.to_commalist(tag_names, lambda x: "'" + x + "'") + ''') order by t.id''' try: tags = self._session.query(db.Tag).from_statement(sql).all() except ResourceClosedError: tags = [] tag_ids = [] for tag in tags: tag_ids.append(tag.id) if len(tag_ids) == 0: #TODO Maybe raise an exception? return [] sub_from = "" for i in range(len(tag_ids)): if i == 0: sub_from = sub_from + " items_tags it{} ".format(i+1) else: sub_from = sub_from + \ (" join items_tags it{1} on it{1}.item_id=it{0}.item_id " + \ " AND it{1}.tag_id > it{0}.tag_id ").format(i, i+1) sub_where = "" for i in range(len(tag_ids)): if i == 0: sub_where = sub_where + \ " it{0}.tag_id = {1} ".format(i+1, tag_ids[i]) else: sub_where = sub_where + \ " AND it{0}.tag_id = {1} ".format(i+1, tag_ids[i]) where = "" for i in range(len(tag_ids)): where = where + \ " AND t.id <> {0} ".format(tag_ids[i]) sql = '''--get_related_tags() query select t.name as name, count(*) as c from tags t join items_tags it on it.tag_id = t.id join items i on i.id = it.item_id where it.item_id IN ( select it1.item_id from ''' + sub_from + ''' where ''' + sub_where + ''' ) ''' + where + ''' AND i.alive -- It is important that these ids followed in the order of raising group by t.name ORDER BY t.name''' try: return self._session.query("name", "c").from_statement(sql).all() except ResourceClosedError: return []
def interpret(self): #yes_tags_str, group_by_having group_by_having = "" if len(self.yes_tags) > 0: yes_tags_str = helpers.to_commalist(self.yes_tags, lambda x: "t.name='" + x.interpret() + "'", " or ") if len(self.yes_tags) > 1: group_by_having = " group by i.id having count(*)={} ".format(len(self.yes_tags)) else: yes_tags_str = " 1 " #extra_users_str if len(self.extra_users) > 0: comma_list = helpers.to_commalist(self.extra_users, lambda x: "'" + x.interpret() + "'", ", ") extra_users_str = " it.user_login IN (" + comma_list + ") " else: extra_users_str = " 1 " #extra_paths_str if len(self.extra_paths) > 0: extra_paths_str = helpers.to_commalist( \ self.extra_paths, lambda x: "data_refs.url LIKE '" + x.interpret() + "%'", " OR ") else: extra_paths_str = " 1 " #extra_titles_str if len(self.extra_titles) > 0: extra_titles_str = helpers.to_commalist( \ self.extra_titles, lambda x: "i.title LIKE '%" + x.interpret() + "%'", " OR ") else: extra_titles_str = " 1 " #no_tags_str if len(self.no_tags) > 0: no_tags_str = " i.id NOT IN (select i.id from items i " + \ " left join items_tags it on i.id = it.item_id " + \ " left join tags t on t.id = it.tag_id " + \ " where (" + helpers.to_commalist(self.no_tags, lambda x: "t.name='" + x.interpret() + "'", " or ") + ") " + ") " else: no_tags_str = " 1 " s = ''' --TagsConjunction.interpret() select distinct i.*, ''' + db_schema.DataRef._sql_from() + ''' from items i left join items_tags it on i.id = it.item_id left join tags t on t.id = it.tag_id left join data_refs on data_refs.id = i.data_ref_id where (''' + yes_tags_str + ''') and (''' + extra_users_str + ''') and (''' + no_tags_str + ''') and (''' + extra_paths_str + ''') and (''' + extra_titles_str + ''') ''' + group_by_having return s
def interpret(self): #extra_users_str if len(self.extra_users) > 0: users_comma_list = helpers.to_commalist(self.extra_users, lambda x: "'" + x.interpret() + "'", ", ") else: users_comma_list = None #extra_titles_str if len(self.extra_titles) > 0: extra_titles_str = helpers.to_commalist( \ self.extra_titles, lambda x: "i.title LIKE '%" + x.interpret() + "%'", " OR ") else: extra_titles_str = " 1 " from_parts = [] where_parts = [] i = 1 for field_op_val in self.field_op_vals: extra_users_str = "and if{0}.user_login IN ({1})".format(i, users_comma_list) \ if users_comma_list else "" from_part = ''' inner join items_fields if{0} on if{0}.item_id = i.id inner join fields f{0} on f{0}.id = if{0}.field_id '''.format(str(i)) if i > 1: from_part = from_part + ''' and if{0}.field_id <> if{1}.field_id '''.format(i-1, i) from_parts.append(from_part) where_part = '''f{0}.name = '{1}' and {2} {3}'''.format(i, field_op_val.name, field_op_val.interpret(i), extra_users_str) where_parts.append(where_part) i = i + 1 #from_str from_str = "" for from_part in from_parts: from_str = from_str + from_part #where_str where_str = helpers.to_commalist(where_parts, lambda x: x, " and \n") #extra_paths_str if len(self.extra_paths) > 0: extra_paths_str = helpers.to_commalist( \ self.extra_paths, lambda x: "data_refs.url LIKE '" + x.interpret() + "%'", " OR ") else: extra_paths_str = " 1 " s = ''' --FieldsConjunction.interpret() select distinct i.*, ''' + db_schema.DataRef._sql_from() + ''' from items i ''' + from_str + ''' left join data_refs on data_refs.id = i.data_ref_id where (''' + where_str + ''') and (''' + extra_paths_str + ''') and (''' + extra_titles_str + ''') ''' return s