def _get_joins(self):
# pylint: disable=no-member
# SQLObject methods not detected by pylint
return [
LEFTJOINOn(None, PhysicalCard,
PhysicalCard.q.id == self.__oTable.physical_card_id),
LEFTJOINOn(None, AbstractCard,
AbstractCard.q.id == PhysicalCard.q.abstractCardID),
]
def _get_joins(self):
# The join on the AbstractCard table is needed to enable filtering
# physical card sets on abstract card propeties, since the base class
# for physical card sets is the mapping table.
# This is one of the only filters allowed to join like this
# pylint: disable=no-member
# SQLObject methods not detected by pylint
return [
LEFTJOINOn(None, PhysicalCard,
PhysicalCard.q.id == self.__oTable.physical_card_id),
LEFTJOINOn(None, AbstractCard,
AbstractCard.q.id == PhysicalCard.q.abstractCardID),
]
def testAliased():
setupClass(SBButton)
b = Alias(makeSelect(), 'b')
checkCount(b, 1)
checkCount(Select(b.q.id), 1)
# Table1 & Table2 are treated individually in joins
checkCount(JOIN(None, b), 1)
checkCount(JOIN(b, SBButton), 1)
checkCount(JOIN(SBButton, b), 1)
checkCount(LEFTJOINOn(None, b, SBButton.q.id == b.q.id), 1)
checkCount(LEFTJOINOn(b, SBButton, SBButton.q.id == b.q.id), 1)
checkCount(LEFTJOINOn(SBButton, b, SBButton.q.id == b.q.id), 1)
def _get_joins(self):
# pylint: disable=no-member
# SQLObject methods not detected by pylint
return [
LEFTJOINOn(None, self._oMapTable,
AbstractCard.q.id == self._oMapTable.q.abstract_card_id)
]
def __init__(self, name, filter_string):
self.name = name
project_name, keyword_filters = parseutils.extractKeywords(
filter_string)
q_filters = [x.filter() for x in keyword_filters]
project_list = Project.select(LIKE(Project.q.name, project_name))
q_filters.append(IN(Task.q.project, project_list))
# Skip notes
q_filters.append(
parseutils.KeywordFilter("!@" + NOTE_KEYWORD).filter())
# Only list done tasks if they were done after min_date
min_date = compute_min_date()
q_filters.append(
OR(Task.q.status != 'done', Task.q.doneDate >= min_date))
self.tasks = Task.select(AND(*q_filters),
orderBy=Task.q.id,
distinct=True,
join=LEFTJOINOn(
Task, TaskKeyword,
Task.q.id == TaskKeyword.q.taskID))
def _get_joins(self):
# pylint: disable=no-member
# SQLObject methods not detected by pylint
oTable = Table('abstract_card')
return [
LEFTJOINOn(None, PhysicalCard,
PhysicalCard.q.abstractCardID == oTable.id)
]
def test_7join_on():
setup()
setupClass(SOTestJoin3)
setupClass(SOTestJoin4)
setupClass(SOTestJoin5)
select = SOTestJoin1.select(
join=(LEFTJOINOn(SOTestJoin2, SOTestJoin3, SOTestJoin2.q.col2 ==
SOTestJoin3.q.col3),
LEFTJOINOn(SOTestJoin4, SOTestJoin5, SOTestJoin4.q.col4 ==
SOTestJoin5.q.col5)))
assert str(select) == \
"SELECT so_test_join1.id, so_test_join1.col1 " \
"FROM so_test_join1, so_test_join2 " \
"LEFT JOIN so_test_join3 " \
"ON ((so_test_join2.col2) = (so_test_join3.col3)), so_test_join4 " \
"LEFT JOIN so_test_join5 " \
"ON ((so_test_join4.col4) = (so_test_join5.col5)) WHERE 1 = 1"
def test_1syntax():
setup()
join = JOIN("table1", "table2")
assert str(join) == "table1 JOIN table2"
join = LEFTJOIN("table1", "table2")
assert str(join) == "table1 LEFT JOIN table2"
join = LEFTJOINOn("table1", "table2", "tabl1.col1 = table2.col2")
assert getConnection().sqlrepr(join) == \
"table1 LEFT JOIN table2 ON tabl1.col1 = table2.col2"
def _get_joins(self):
# This is one of the filters allowed to
# pass the AbstractCard table as a joining table.
# The join is needed so filtering on abstract card properties can work
# pylint: disable=no-member
# SQLObject methods not detected by pylint
oTable = Table('physical_card')
return [
LEFTJOINOn(None, AbstractCard,
AbstractCard.q.id == oTable.abstract_card_id)
]
def test_3perform_join():
setup()
SOTestJoin1(col1="test1")
SOTestJoin1(col1="test2")
SOTestJoin1(col1="test3")
SOTestJoin2(col2="test1")
SOTestJoin2(col2="test2")
select = SOTestJoin1.select(join=LEFTJOINOn(
SOTestJoin1, SOTestJoin2, SOTestJoin1.q.col1 == SOTestJoin2.q.col2))
assert select.count() == 3
def test_3joins():
setupClass(JoinAlias)
alias = Alias(JoinAlias)
select = JoinAlias.select(
(JoinAlias.q.name == 'a') & (alias.q.name == 'b'),
join=LEFTJOINOn(None, alias, alias.q.name == 'c'))
assert str(select) == \
"SELECT join_alias.id, join_alias.name, join_alias.parent " \
"FROM join_alias " \
"LEFT JOIN join_alias join_alias_alias3 " \
"ON ((join_alias_alias3.name) = ('c')) " \
"WHERE (((join_alias.name) = ('a')) AND " \
"((join_alias_alias3.name) = ('b')))"
def database_gen(authorOrTitle=""):
titles = []
# start out with the join clauses in the where clause list
where_clause_list = []
clause_tables = ["book", "author", "author_title"]
join_list = [
LEFTJOINOn("title", "book", "book.title_id=title.id"),
LEFTJOINOn(None, "author_title", "title.id=author_title.title_id"),
LEFTJOINOn(None, "author", "author.id=author_title.author_id"),
]
# add filter clauses if they are called for
where_clause_list.append(
"(author.author_name RLIKE '%s' OR title.booktitle RLIKE '%s')"
% (authorOrTitle.strip(), authorOrTitle.strip())
)
# AND all where clauses together
where_clause = AND(where_clause_list)
titles = []
# do search.
titles = Title.select(
where_clause, join=join_list, clauseTables=clause_tables, distinct=True
)
for t1 in titles:
yield {
"title": t1.booktitle,
"authors": t1.author,
"authors_as_string": t1.authors_as_string(),
"categories_as_string": t1.categories_as_string(),
"list_price": t1.highest_price_book().ourprice,
"publisher": t1.publisher,
"isbn": t1.isbn,
"format": t1.type,
"kind": t1.kind.kindName,
"known_title": t1,
}
def _get_joins(self):
# pylint: disable=no-member
# avoid SQLObject method not detected problems
return [LEFTJOINOn(None, self._oMapTable,
AbstractCard.q.id == self._oMapTable.q.id)]
def __init__(self, aData):
# aData is a list or tuple of the form (aCounts, sCardSetName)
# Selects cards with a count in the range specified by aCounts from
# the Physical Card Set sCardSetName
# We rely on the joins to limit this to the appropriate card sets
# pylint: disable=no-member
# SQLObject methods not detected by pylint
aIds = []
try:
aCounts, aCardSetName = aData
if not isinstance(aCardSetName, list):
aCardSetName = [aCardSetName]
for sCardSetName in aCardSetName:
try:
oCS = IPhysicalCardSet(sCardSetName)
aIds.append(oCS.id)
except SQLObjectNotFound:
aCounts = []
except ValueError:
aCounts = []
# strip whitespace before comparing stuff
# aCounts may be a single string, so we can't use 'for x in aCounts'
aCounts = {x.strip() for x in list(aCounts)}
self._oFilters = []
self._aCardSetIds = aIds
self._oZeroQuery = None
if '0' in aCounts:
aCounts.remove('0')
self._oZeroQuery = Select(
PhysicalCard.q.abstractCardID,
where=IN(MapPhysicalCardToPhysicalCardSet.q.physicalCardSetID,
aIds),
join=LEFTJOINOn(
PhysicalCard, MapPhysicalCardToPhysicalCardSet,
PhysicalCard.q.id ==
MapPhysicalCardToPhysicalCardSet.q.physicalCardID),
groupBy=PhysicalCard.q.abstractCardID,
having=func.COUNT(PhysicalCard.q.abstractCardID) > 0)
if '>30' in aCounts:
aCounts.remove('>30')
oGreater30Query = Select(
PhysicalCard.q.abstractCardID,
where=IN(MapPhysicalCardToPhysicalCardSet.q.physicalCardSetID,
aIds),
join=LEFTJOINOn(
PhysicalCard, MapPhysicalCardToPhysicalCardSet,
PhysicalCard.q.id ==
MapPhysicalCardToPhysicalCardSet.q.physicalCardID),
groupBy=(PhysicalCard.q.abstractCardID,
MapPhysicalCardToPhysicalCardSet.q.physicalCardSetID),
having=func.COUNT(PhysicalCard.q.abstractCardID) > 30)
self._oFilters.append(oGreater30Query)
if aCounts:
# SQLite doesn't like strings here, so convert to int
oCountFilter = Select(
PhysicalCard.q.abstractCardID,
where=IN(MapPhysicalCardToPhysicalCardSet.q.physicalCardSetID,
aIds),
join=LEFTJOINOn(
PhysicalCard, MapPhysicalCardToPhysicalCardSet,
PhysicalCard.q.id ==
MapPhysicalCardToPhysicalCardSet.q.physicalCardID),
groupBy=(PhysicalCard.q.abstractCardID,
MapPhysicalCardToPhysicalCardSet.q.physicalCardSetID),
having=IN(func.COUNT(PhysicalCard.q.abstractCardID),
[int(x) for x in aCounts]))
self._oFilters.append(oCountFilter)
def searchInventory(sortby="booktitle", out_of_stock=False, **kwargs):
# start building the filter list
where_clause_list = []
print("kwargs are ", kwargs, file=sys.stderr)
for k in kwargs:
if type(k) == bytes:
kwargs[k] = kwargs[k].decode("utf-8")
to_delete = [k for k in kwargs if kwargs[k] == ""]
for td in to_delete:
del kwargs[td]
print(len(kwargs), file=sys.stderr)
# clause_tables=['book', 'author', 'author_title', 'category', 'location']
clause_tables = ["book", "author", "author_title", "location"]
join_list = [
LEFTJOINOn("title", "book", "book.title_id=title.id"),
LEFTJOINOn(None, "author_title", "title.id=author_title.title_id"),
LEFTJOINOn(None, "author", "author.id=author_title.author_id"),
LEFTJOINOn(None, Location, Location.q.id == Book.q.locationID),
]
# join_list=[LEFTJOINOn('title', 'book', 'book.title_id=title.id'), LEFTJOINOn(None, 'author_title', 'title.id=author_title.title_id'), LEFTJOINOn(None, 'author', 'author.id=author_title.author_id'), LEFTJOINOn(None, Category, Category.q.titleID==Title.q.id), LEFTJOINOn(None, Location, Location.q.id==Book.q.locationID)]
if "the_kind" in kwargs:
where_clause_list.append(Title.q.kindID == kwargs["the_kind"])
if "the_location" in kwargs and len(the_location) > 1:
where_clause_list.append(Book.q.locationID == kwargs["the_location"])
if "title" in kwargs:
where_clause_list.append(RLIKE(Title.q.booktitle, kwargs["title"].strip()))
if "publisher" in kwargs:
where_clause_list.append(RLIKE(Title.q.publisher, kwargs["publisher"].strip()))
if "tag" in kwargs:
where_clause_list.append(RLIKE(Title.q.tag, kwargs["tag"].strip()))
if "isbn" in kwargs:
isbn, price = _process_isbn(kwargs["isbn"])
where_clause_list.append(Title.q.isbn == isbn)
if "formatType" in kwargs:
where_clause_list.append(Title.q.type == kwargs["formatType"].strip())
if "owner" in kwargs:
where_clause_list.append(RLIKE(Book.q.owner, kwargs["owner"].strip()))
if "distributor" in kwargs:
where_clause_list.append(
RLIKE(Book.q.distributor, kwargs["distributor"].strip())
)
if "inv_begin_date" in kwargs:
where_clause_list.append(Book.q.inventoried_when >= kwargs["inv_begin_date"])
if "inv_end_date" in kwargs:
where_clause_list.append(Book.q.inventoried_when < kwargs["inv_end_date"])
if "sold_begin_date" in kwargs:
where_clause_list.append(Book.q.sold_when >= kwargs["sold_begin_date"])
if "sold_end_date" in kwargs:
where_clause_list.append(Book.q.sold_when < kwargs["sold_end_date"])
if "author" in kwargs:
where_clause_list.append(RLIKE(Author.q.authorName, kwargs["author"].strip()))
if "category" in kwargs:
where_clause_list.append(
RLIKE(Category.q.categoryName, kwargs["category"].strip())
)
if "status" in kwargs:
where_clause_list.append(Book.q.status == kwargs["status"].strip())
if "id" in kwargs:
where_clause_list.append(Title.q.id == kwargs["id"])
if "authorOrTitle" in kwargs:
where_clause_list.append(
OR(
RLIKE(Author.q.authorName, kwargs["authorOrTitle"].strip()),
RLIKE(Title.q.booktitle, kwargs["authorOrTitle"].strip()),
)
)
where_clause = AND(*where_clause_list)
# do search first. Note it currently doesnt let you search for every book in database, unless you use some sort of
# trick like '1=1' for the where clause string, as the where clause string may not be blank
titles = []
if len(kwargs) > 1 or kwargs.setdefault("out_of_stock", False):
titles = Title.select(
where_clause,
join=join_list,
orderBy=sortby,
clauseTables=clause_tables,
distinct=True,
)
# filter for stock status
# GROUPBY in sqlobject is complicated. We could do it but it's not worth it
if "out_of_stock" in kwargs:
titles = [t for t in titles if t.copies_in_status("STOCK") == 0]
# filter on specific numbers in stock
if "stock_less_than" in kwargs:
titles = [
t
for t in titles
if t.copies_in_status("STOCK") <= int(kwargs["stock_less_than"])
]
if "stock_more_than" in kwargs:
titles = [
t
for t in titles
if t.copies_in_status("STOCK") >= int(kwargs["stock_more_than"])
]
# filter by items sold
if "sold_more_than" in kwargs:
titles = [
t
for t in titles
if t.copies_in_status("SOLD") >= int(kwargs["sold_more_than"])
]
if "sold_less_than" in kwargs:
titles = [
t
for t in titles
if t.copies_in_status("SOLD") >= int(kwargs["sold_less_than"])
]
print(titles, file=sys.stderr)
return titles
def _renderList(self,
renderer,
projectList,
filters,
order,
limit=None,
groupKeyword=None):
"""
Render a list using renderer, according to the restrictions set by the
other parameters
@param renderer: renderer class (for example: TextListRenderer)
@param projectList: list of project name (as unicode string)
@param filters: filters in sqlobject format (example: Task.q.status == 'done')
@param order: ordering in sqlobject format (example: -Task.q.urgency)
@param limit: limit number tasks (int) or None for no limit
@param groupKeyword: keyword used for grouping (as unicode string) or None
"""
if groupKeyword:
if groupKeyword.startswith("@"):
groupKeyword = groupKeyword[1:]
for keyword in Keyword.select(LIKE(Keyword.q.name, groupKeyword)):
if unicode(keyword.name).startswith(
"_") and not groupKeyword.startswith("_"):
# BUG: cannot filter on db side because sqlobject does not understand ESCAPE needed whith _
continue
taskList = Task.select(
AND(TaskKeyword.q.keywordID == keyword.id, *filters),
orderBy=order,
limit=limit,
distinct=True,
join=LEFTJOINOn(Task, TaskKeyword,
Task.q.id == TaskKeyword.q.taskID))
taskList = list(taskList)
if projectList:
taskList = [
x for x in taskList if x.project in projectList
]
if len(taskList) > 0:
self.lastTaskIds.extend([
t.id for t in taskList
]) # Keep selected id for further use
renderer.addTaskList(unicode(keyword), taskList)
renderer.end()
else:
hiddenProjectNames = []
for project in projectList:
if not project.active:
hiddenProjectNames.append(project.name)
continue
taskList = Task.select(AND(Task.q.projectID == project.id,
*filters),
orderBy=order,
limit=limit,
distinct=True,
join=LEFTJOINOn(
Task, TaskKeyword,
Task.q.id == TaskKeyword.q.taskID))
taskList = list(taskList)
if len(taskList) > 0:
self.lastTaskIds.extend([
t.id for t in taskList
]) # Keep selected id for further use
renderer.addTaskList(unicode(project), taskList)
renderer.end()
if len(hiddenProjectNames) > 0:
tui.info("hidden projects: %s" % ", ".join(hiddenProjectNames))
def _get_joins(self):
return [
LEFTJOINOn(None, self.__oTable,
self.__oPT.id == self.__oTable.q.physical_card_id)
]
