def GET(self, name=None, version=None):
jobs = []
if version:
title = "%s %s" % (name, version)
package = "%s/%s" % (name, version)
query = Job.select(
IN(
Job.q.package,
Select(
Package.q.id,
AND(Package.q.name == name,
Package.q.version == version))))
else:
title = package = name
query = Job.select(
IN(Job.q.package, Select(Package.q.id,
Package.q.name == name)))
result, page, nb_pages = _paginate_query(
query.orderBy(DESC(Job.q.package)))
jobs.extend(result)
return render.base(page=render.tab(jobs=jobs, page=page, nb_pages=nb_pages), \
hostname=socket.gethostname(), \
title=title, \
package=package, \
archs=RebuilddConfig().arch, \
dists=RebuilddConfig().get('build', 'dists').split(' '))
def filter(self):
"""Return a filter in SQlObject format"""
taskValueFilter = (1 == 1)
projectValueFilter = (1 == 1)
if self.name:
if self.value:
if self.valueOperator == "=":
taskValueFilter = (TaskKeyword.q.value == self.value)
projectValueFilter = (ProjectKeyword.q.value == self.value)
elif self.valueOperator == "!=":
taskValueFilter = (TaskKeyword.q.value != self.value)
projectValueFilter = (ProjectKeyword.q.value != self.value)
# TODO: handle also <, >, =< and >=
taskKeywordTaskIDs = Select(
Task.q.id,
where=(AND(LIKE(Keyword.q.name, self.name),
TaskKeyword.q.keywordID == Keyword.q.id,
TaskKeyword.q.taskID == Task.q.id,
taskValueFilter)))
projectKeywordTaskIDs = Select(
Task.q.id,
where=(AND(LIKE(Keyword.q.name, self.name),
ProjectKeyword.q.keywordID == Keyword.q.id,
ProjectKeyword.q.projectID == Project.q.id,
Project.q.id == Task.q.project,
projectValueFilter)))
if self.negative:
return AND(NOTIN(Task.q.id, taskKeywordTaskIDs),
NOTIN(Task.q.id, projectKeywordTaskIDs))
else:
return OR(IN(Task.q.id, taskKeywordTaskIDs),
IN(Task.q.id, projectKeywordTaskIDs))
def test_4perform_exists():
insert()
select = SOTestIn1.select(
EXISTS(
Select(SOTestIn2.q.col2,
where=(Outer(SOTestIn1).q.col1 == SOTestIn2.q.col2))))
assert len(list(select)) == 2
setupClass(SOTestOuter)
select = SOTestOuter.select(
NOTEXISTS(
Select(SOTestIn1.q.col1,
where=(Outer(SOTestOuter).q.fkID == SOTestIn1.q.id))))
assert len(list(select)) == 0
def relation_values(self, object_name, rows):
joins = {}
ids = rows.keys()
if not ids:
return joins
obj = self.load_object(object_name)
conn = obj._connection
for column in obj.sqlmeta.joins:
query = None
coltype = self.get_column_type(column)
if coltype in ('SOMultipleJoin', 'SOSQLMultipleJoin'):
query = conn.sqlrepr(
Select([
column.soClass.q.id,
func.Count(column.otherClass.q.id)
],
where=AND(
column.soClass.q.id == self.join_foreign_key(
column), IN(column.soClass.q.id, ids)),
groupBy=column.soClass.q.id))
elif coltype in ('SORelatedJoin', 'SOSQLRelatedJoin'):
d = (column.intermediateTable, column.joinColumn,
column.intermediateTable, column.otherColumn,
column.intermediateTable, column.intermediateTable,
column.joinColumn, ','.join(['%s' % x for x in ids]),
column.intermediateTable, column.joinColumn)
query = ("SELECT %s.%s, Count(%s.%s) FROM %s"
" WHERE %s.%s IN(%s) GROUP BY %s.%s" % d)
elif coltype == 'SOSingleJoin':
alias = self.load_label_column_for_object(
column.otherClassName)
query = conn.sqlrepr(
Select([
column.soClass.q.id,
getattr(column.otherClass.q, alias)
],
where=AND(
column.soClass.q.id == self.join_foreign_key(
column), IN(column.soClass.q.id, ids))))
if not query:
continue
joins[column.joinMethodName] = conn.queryAll(query)
return joins
def test_SOME():
setupClass(SOTestSOME)
SOTestSOME(value=10)
SOTestSOME(value=20)
SOTestSOME(value=30)
assert len(list(SOTestSOME.select(
SOTestSOME.q.value > SOME(Select([SOTestSOME.q.value]))))) == 2
def test_ANY():
setupClass(SOTestANY)
SOTestANY(value=10)
SOTestANY(value=20)
SOTestANY(value=30)
assert len(list(SOTestANY.select(
SOTestANY.q.value > ANY(Select([SOTestANY.q.value]))))) == 2
def test_6perform_join():
insert()
j = LEFTOUTERJOINOn(SOTestIn2, SOTestIn1,
SOTestIn1.q.col1 == SOTestIn2.q.col2)
select = SOTestIn1.select(SOTestIn1.q.col1 == Select(
SOTestIn2.q.col2, where=(SOTestIn2.q.col2 == "test"), join=j))
assert select.count() == 1
def _addProjectFilter(self):
if self.projectName is None:
return
self._filters.append(
IN(
Task.q.project,
Select(Project.q.id, LIKE(Project.q.name, "%" + self.projectName + "%"))
))
def test_4syntax_direct():
setup()
select = SOTestIn1.select(SOTestIn1.q.col1 == Select(
SOTestIn2.q.col2, where=(SOTestIn2.q.col2 == "test")))
assert str(select) == \
"SELECT so_test_in1.id, so_test_in1.col1 " \
"FROM so_test_in1 WHERE ((so_test_in1.col1) = " \
"(SELECT so_test_in2.col2 FROM so_test_in2 " \
"WHERE ((so_test_in2.col2) = ('test'))))"
def _query_all_filedata(self):
#return self.fd.select()
return Filedata.select(
# only query things that actually have dependencies
EXISTS(
Select(DtNeededList.q.filedata,
where=(Outer(Filedata).q.id == DtNeededList.q.filedata))
),
# sort by filename
orderBy=Filedata.q.basename)
def import_inp(archive, inp):
archives = set()
files = set()
connection = sqlhub.processConnection
for file, in connection.queryAll(connection.sqlrepr(
Select(Book.q.file, Book.q.archive == archive))):
files.add((archive, tounicode(file)))
for line in inp:
line = line.decode('utf-8')
_archive, parts = split_line(line)
if _archive and (_archive not in archives):
archives.add(_archive)
for file, in connection.queryAll(connection.sqlrepr(
Select(Book.q.file, Book.q.archive == _archive))):
files.add((_archive, tounicode(file)))
file = parts[5]
if (_archive or archive, file) not in files:
files.add((_archive or archive, file))
import_inp_line(_archive or archive, parts)
def test_ANY():
setupClass(SOTestSQLBuilder)
select = Select(
[SOTestSQLBuilder.q.name],
'value' == ANY(SOTestSQLBuilder.q.so_value),
)
assert sqlrepr(select, 'mysql') == \
"SELECT so_test_sql_builder.name FROM so_test_sql_builder " \
"WHERE (('value') = ANY (so_test_sql_builder.so_value))"
def test_3syntax_exists():
setup()
select = SOTestIn1.select(
NOTEXISTS(
Select(SOTestIn2.q.col2,
where=(Outer(SOTestIn1).q.col1 == SOTestIn2.q.col2))))
assert str(select) == \
"SELECT so_test_in1.id, so_test_in1.col1 " \
"FROM so_test_in1 WHERE NOT EXISTS " \
"(SELECT so_test_in2.col2 FROM so_test_in2 " \
"WHERE ((so_test_in1.col1) = (so_test_in2.col2)))"
setupClass(SOTestOuter)
select = SOTestOuter.select(
NOTEXISTS(
Select(SOTestIn1.q.col1,
where=(Outer(SOTestOuter).q.fk == SOTestIn1.q.id))))
assert str(select) == \
"SELECT so_test_outer.id, so_test_outer.fk_id " \
"FROM so_test_outer WHERE NOT EXISTS " \
"(SELECT so_test_in1.col1 FROM so_test_in1 " \
"WHERE ((so_test_outer.fk_id) = (so_test_in1.id)))"
def test_1syntax_in():
setup()
select = SOTestIn1.select(IN(SOTestIn1.q.col1, Select(SOTestIn2.q.col2)))
assert str(select) == \
"SELECT so_test_in1.id, so_test_in1.col1 " \
"FROM so_test_in1 WHERE so_test_in1.col1 IN " \
"(SELECT so_test_in2.col2 FROM so_test_in2)"
select = SOTestIn1.select(IN(SOTestIn1.q.col1, SOTestIn2.select()))
assert str(select) == \
"SELECT so_test_in1.id, so_test_in1.col1 " \
"FROM so_test_in1 WHERE so_test_in1.col1 IN " \
"(SELECT so_test_in2.id FROM so_test_in2 WHERE 1 = 1)"
def test_6syntax_join():
insert()
j = LEFTOUTERJOINOn(SOTestIn2, SOTestIn1,
SOTestIn1.q.col1 == SOTestIn2.q.col2)
select = SOTestIn1.select(SOTestIn1.q.col1 == Select(
SOTestIn2.q.col2, where=(SOTestIn2.q.col2 == "test"), join=j))
assert str(select) == \
"SELECT so_test_in1.id, so_test_in1.col1 " \
"FROM so_test_in1 WHERE ((so_test_in1.col1) = " \
"(SELECT so_test_in2.col2 FROM so_test_in2 " \
"LEFT OUTER JOIN so_test_in1 ON " \
"((so_test_in1.col1) = (so_test_in2.col2)) " \
"WHERE ((so_test_in2.col2) = ('test'))))"
def foreign_key_query(self, column, alias, ids):
if not ids:
return {}
sql_object = self.load_object(column.foreignKey)
conn = sql_object._connection
query = conn.sqlrepr(
Select([sql_object.q.id,
getattr(sql_object.q, alias)],
where=IN(sql_object.q.id, ids)))
fk_values = {}
for id, alias in conn.queryAll(query):
fk_values[str(id)] = self.encode_label(alias)
return fk_values
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 test_groupBy_list():
setupClass(GroupbyTest)
GroupbyTest(name='a', value=1)
GroupbyTest(name='a', value=2)
GroupbyTest(name='b', value=1)
connection = getConnection()
select = Select([GroupbyTest.q.name, GroupbyTest.q.value],
groupBy=[GroupbyTest.q.name, GroupbyTest.q.value],
orderBy=[GroupbyTest.q.name, GroupbyTest.q.value])
sql = connection.sqlrepr(select)
rows = connection.queryAll(sql)
assert rows == [('a', 1), ('a', 2), ('b', 1)]
def _get_expression(self):
"""The expression for the NOT filter.
We generate a suitable subselect from self._oSubFilter, and
negate the results of that.
"""
# pylint: disable=no-member
# SQLObject methods not detected by pylint
oExpression = self.__oSubFilter._get_expression()
aJoins = self.__oSubFilter._get_joins()
if 'AbstractCard' in self.__oSubFilter.types:
return NOT(
IN(AbstractCard.q.id,
Select(AbstractCard.q.id, oExpression, join=aJoins)))
if 'PhysicalCard' in self.__oSubFilter.types:
return NOT(
IN(PhysicalCard.q.id,
Select(PhysicalCard.q.id, oExpression, join=aJoins)))
if 'PhysicalCardSet' in self.__oSubFilter.types:
return NOT(
IN(PhysicalCardSet.q.id,
Select(PhysicalCardSet.q.id, oExpression, join=aJoins)))
raise RuntimeError("FilterNot unable to handle sub-filter type.")
def test_groupBy():
setupClass(GroupbyTest)
GroupbyTest(name='a', so_value=1)
GroupbyTest(name='a', so_value=2)
GroupbyTest(name='b', so_value=1)
connection = getConnection()
select = Select(
[GroupbyTest.q.name, func.COUNT(GroupbyTest.q.so_value)],
groupBy=GroupbyTest.q.name,
orderBy=GroupbyTest.q.name)
sql = connection.sqlrepr(select)
rows = list(connection.queryAll(sql))
assert [tuple(t) for t in rows] == [('a', 2), ('b', 1)]
def test_Select():
setupClass(SOTestSQLBuilder)
select1 = Select([const.id, func.MAX(const.salary)],
staticTables=['employees'])
assert sqlrepr(select1) == 'SELECT id, MAX(salary) FROM employees'
select2 = Select([SOTestSQLBuilder.q.name, SOTestSQLBuilder.q.so_value])
assert sqlrepr(select2) == \
'SELECT so_test_sql_builder.name, so_test_sql_builder.so_value ' \
'FROM so_test_sql_builder'
union = Union(select1, select2)
assert sqlrepr(union) == \
'SELECT id, MAX(salary) FROM employees ' \
'UNION SELECT so_test_sql_builder.name, ' \
'so_test_sql_builder.so_value ' \
'FROM so_test_sql_builder'
union = Union(SOTestSQLBuilder.select().queryForSelect())
assert sqlrepr(union) == \
'SELECT so_test_sql_builder.id, so_test_sql_builder.name, ' \
'so_test_sql_builder.so_value FROM so_test_sql_builder WHERE 1 = 1'
def test_str_or_sqlrepr():
select = Select(['id', 'name'],
staticTables=['employees'],
where='value>0',
orderBy='id')
assert sqlrepr(select, 'sqlite') == \
'SELECT id, name FROM employees WHERE value>0 ORDER BY id'
select = Select(['id', 'name'],
staticTables=['employees'],
where='value>0',
orderBy='id',
lazyColumns=True)
assert sqlrepr(select, 'sqlite') == \
'SELECT id FROM employees WHERE value>0 ORDER BY id'
insert = Insert('employees', values={'id': 1, 'name': 'test'})
assert sqlrepr(insert, 'sqlite') == \
"INSERT INTO employees (id, name) VALUES (1, 'test')"
update = Update('employees', {'name': 'test'}, where='id=1')
assert sqlrepr(update, 'sqlite') == \
"UPDATE employees SET name='test' WHERE id=1"
update = Update('employees', {'name': 'test', 'age': 42}, where='id=1')
assert sqlrepr(update, 'sqlite') == \
"UPDATE employees SET age=42, name='test' WHERE id=1"
delete = Delete('employees', where='id=1')
assert sqlrepr(delete, 'sqlite') == \
"DELETE FROM employees WHERE id=1"
raises(TypeError, Delete, 'employees')
delete = Delete('employees', where=None)
assert sqlrepr(delete, 'sqlite') == \
"DELETE FROM employees"
def render_page(self, channel_id):
channel = PluginChannel.get(channel_id)
con = sqlhub.threadConnection
select_query = Select(['mime_type', 'SUM(file_size)'], where=Asset.q.plugin_channel == channel_id, staticTables=['asset'], groupBy='mime_type')
labels = []
data = []
for type, size in con.queryAll(con.sqlrepr(select_query)):
labels.append(type)
data.append(int(size))
palette = [(0.86, 0.37119999999999997, 0.33999999999999997), (0.86, 0.7612000000000001, 0.33999999999999997), (0.56880000000000008, 0.86, 0.33999999999999997), (0.33999999999999997, 0.86, 0.50120000000000009), (0.33999999999999997, 0.82879999999999987, 0.86), (0.33999999999999997, 0.43879999999999986, 0.86), (0.63119999999999976, 0.33999999999999997, 0.86), (0.86, 0.33999999999999997, 0.69879999999999964)]
background_color = []
border_color = []
for R,G,B in palette:
background_color.append('rgba(%d, %d, %d, 0.95)' % (int(R*255), int(G*255), int(B*255)))
border_color.append('rgba(%d, %d, %d, 1)' % (int(R*255), int(G*255), int(B*255)))
chart_data = {'labels': labels, 'datasets': [{'data': data, 'backgroundColor': background_color}]}
return self.renderer.storage_channel(channel=channel, chart_data=json.dumps(chart_data))
def __init__(self, *args, **kargs):
gtk.GenericTreeModel.__init__(self)
from sqlobject.sqlbuilder import EXISTS, Select, Outer, LEFTOUTERJOIN
self.fd = license_db.Filedata
self.query_only_with_deps = Filedata.select
self.query_only_with_deps_clause = (EXISTS(
Select(DtNeededList.q.Filedata,
where=(Outer(Filedata).q.id == DtNeededList.q.Filedata))), )
self.query_all = Filedata.select
self.query_all_clause = ()
#self.default_query = self.query_all
#self.default_query_clause = self.query_all_clause
self.count = self.query_all(*self.query_all_clause).count()
self.default_query = self.query_only_with_deps
self.default_query_clause = self.query_only_with_deps_clause
def test_CONCAT():
setupClass(SOTestSQLBuilder)
SOTestSQLBuilder(name='test', so_value=42)
assert sqlrepr(CONCAT('a', 'b'), 'mysql') == "CONCAT('a', 'b')"
assert sqlrepr(CONCAT('a', 'b'), 'mssql') == "'a' + 'b'"
assert sqlrepr(CONCAT('a', 'b'), 'sqlite') == "'a' || 'b'"
assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'mysql') == \
"CONCAT('prefix', so_test_sql_builder.name)"
assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'sqlite') == \
"'prefix' || so_test_sql_builder.name"
select = Select([CONCAT(SOTestSQLBuilder.q.name, '-suffix')],
staticTables=['so_test_sql_builder'])
connection = getConnection()
rows = connection.queryAll(connection.sqlrepr(select))
result = rows[0][0]
if not PY2 and not isinstance(result, str):
result = result.decode('ascii')
assert result == "test-suffix"
def test_truediv():
setupClass(SQLiteTruedivTest)
if SQLiteTruedivTest._connection.dbName == "sqlite":
if not SQLiteTruedivTest._connection.using_sqlite2:
pytest.skip("These tests require SQLite v2+")
def SQLiteConnectionFactory(sqlite):
class MyConnection(sqlite.Connection):
def __init__(self, *args, **kwargs):
super(MyConnection, self).__init__(*args, **kwargs)
self.create_function("floor", 1, math.floor)
return MyConnection
setSQLiteConnectionFactory(SQLiteTruedivTest, SQLiteConnectionFactory)
SQLiteTruedivTest(value=-5.0)
assert SQLiteTruedivTest._connection.queryAll(
SQLiteTruedivTest._connection.sqlrepr(
Select(SQLiteTruedivTest.q.value // 4)))[0][0] == -2
def init_cache(self):
"""Initialise the cache with the data from the database."""
self._find_other_join()
oIntermediateTable = Table(self.intermediateTable)
oJoinColumn = getattr(oIntermediateTable, self.joinColumn)
oOtherColumn = getattr(oIntermediateTable, self.otherColumn)
# pylint: disable=protected-access
# We need to access _connection here
oConn = self.soClass._connection
for (oId, oOtherId) in oConn.queryAll(
repr(Select((oJoinColumn, oOtherColumn)))):
oInst = self.soClass.get(oId, oConn)
oOther = self.otherClass.get(oOtherId, oConn)
self._dJoinCache.setdefault(oInst, [])
self._dJoinCache[oInst].append(oOther)
# Apply ordering (we assume it won't change later)
for oInst in self._dJoinCache:
self._dJoinCache[oInst] = self._applyOrderBy(
self._dJoinCache[oInst], self.otherClass)
def test_select():
instance = Select('test')
assert sqlrepr(instance, 'mysql') == "SELECT test"
def test_2perform_in():
insert()
select = SOTestIn1.select(IN(SOTestIn1.q.col1, Select(SOTestIn2.q.col2)))
assert select.count() == 2
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)