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 __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 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
