def testJoin(self):
storage_one = Midgard.QueryStorage(dbclass = "midgard_person")
storage_two = Midgard.QueryStorage(dbclass = "midgard_person")
qs = Midgard.QuerySelect(connection = self.mgd, storage = storage_one)
group = Midgard.QueryConstraintGroup(grouptype = "AND")
constraint_one = Midgard.QueryConstraint(
property = Midgard.QueryProperty(property = "firstname"),
operator = "=",
holder = Midgard.QueryValue.create_with_value("Alice")
)
constraint_two = Midgard.QueryConstraint(
property = Midgard.QueryProperty(property = "firstname", storage = storage_two),
operator = "=",
holder = Midgard.QueryValue.create_with_value("John")
)
group.add_constraint(constraint_one)
group.add_constraint(constraint_two)
qs.set_constraint(group)
qs.add_join(
"LEFT",
Midgard.QueryProperty(property = "lastname"),
Midgard.QueryProperty(property = "lastname", storage = storage_two)
)
qs.execute()
# We expect Alice person only
self.assertEqual(qs.get_results_count(), 1)
objects = qs.list_objects()
self.assertEqual(objects[0].get_property("firstname"), "Alice")
def prepareJoin(self):
self.addStoreColumns()
# Add book and store id required for join
store_storage = self.default_store_storage
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="id", storage=store_storage),
name="store_id",
qualifier="bookstore")
self.select.add_column(column)
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="store", storage=self.default_book_storage),
name=book_store_id,
qualifier=book_qualifier)
self.select.add_column(column)
self.select.add_join(
"INNER",
Midgard.SqlQueryColumn(name=book_store_id,
queryproperty=Midgard.QueryProperty(
property="store",
storage=self.default_book_storage),
qualifier=book_qualifier),
Midgard.SqlQueryColumn(name="store_id",
queryproperty=Midgard.QueryProperty(
property="id",
storage=self.default_store_storage),
qualifier=book_store_qualifier))
def testValidateConstraintGroup(self):
storage = Midgard.QueryStorage(dbclass = "midgard_snippet")
columnA = Midgard.SqlQueryColumn(
queryproperty = Midgard.QueryProperty(property = "name", storage = storage),
name = "a",
qualifier = "tblA"
)
self.select.add_column(columnA)
columnB = Midgard.SqlQueryColumn(
queryproperty = Midgard.QueryProperty(property = "code", storage = storage),
name = "b",
qualifier = "tblA"
)
self.select.add_column(columnB)
cg = Midgard.QueryConstraintGroup(grouptype = "AND")
cg.add_constraint(
Midgard.SqlQueryConstraint(
column = columnA,
operator = "<>",
holder = Midgard.QueryValue.create_with_value("")
)
)
cg.add_constraint(
Midgard.SqlQueryConstraint(
column = columnB,
operator = "<>",
holder = Midgard.QueryValue.create_with_value("")
)
)
self.select.set_constraint(cg)
# Validate constraints during execution
self.select.execute()
def addColumns(self):
storage = self.default_book_storage
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="title", storage=storage),
name="btitle",
qualifier=book_qualifier)
self.select.add_column(column)
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="id", storage=storage),
name="sid",
qualifier=book_qualifier)
self.select.add_column(column)
def addColumns(self):
storage = Midgard.QueryStorage(dbclass="midgard_person")
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="firstname", storage=storage),
name="name",
qualifier="p")
self.select.add_column(column)
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="lastname", storage=storage),
name="lastname",
qualifier="p")
self.select.add_column(column)
def testAddColumn(self):
column = Midgard.SqlQueryColumn(
queryproperty=Midgard.QueryProperty(property="title"),
name="title",
qualifier="t1")
# Expect exception in case of error
self.select.add_column(column)
def testSelectLimit(self):
qs = self.getQSForSmiths()
qs.add_order(Midgard.QueryProperty(property = "firstname"), "DESC")
qs.set_limit(1)
qs.execute()
self.assertEqual(qs.get_results_count(), 1)
l = qs.list_objects()
self.assertEqual(l[0].get_property("firstname"), "Marry")
def testSelectOrderDESC(self):
qs = self.getQSForSmiths()
qs.add_order(Midgard.QueryProperty(property = "firstname"), "DESC")
qs.execute()
l = qs.list_objects()
self.assertEqual(l[0].get_property("firstname"), "Marry")
self.assertEqual(l[1].get_property("firstname"), "John")
self.assertEqual(l[2].get_property("firstname"), "Alice")
def setUp(self):
if self.mgd is None:
self.mgd = TestConnection.openConnection()
if self.column is None:
self.queryproperty = Midgard.QueryProperty(property="title")
self.column = Midgard.SqlQueryColumn(
queryproperty=self.queryproperty,
name="The title",
qualifier="t1")
def addBookTitleConstraint(self):
self.select.set_constraint(
Midgard.SqlQueryConstraint(
column=Midgard.SqlQueryColumn(
queryproperty=Midgard.QueryProperty(
property="title", storage=self.default_book_storage),
name=book_title_column,
qualifier=book_qualifier),
operator="=",
holder=Midgard.QueryValue.create_with_value(book_a_title)))
def getQSForSmiths(self):
st = Midgard.QueryStorage(dbclass = "midgard_person")
qs = Midgard.QuerySelect(connection = self.mgd, storage = st)
qs.set_constraint(
Midgard.QueryConstraint(
property = Midgard.QueryProperty(property = "lastname"),
operator = "=",
holder = Midgard.QueryValue.create_with_value("Smith")
)
)
return qs
def addStoreColumns(self):
storage = self.default_book_storage
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="title", storage=storage),
name=book_title_column,
qualifier=book_qualifier)
self.select.add_column(column)
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="id", storage=storage),
name="book_id",
qualifier=book_qualifier)
self.select.add_column(column)
store_storage = self.default_store_storage
column = Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="name", storage=store_storage),
name="storename",
qualifier="bookstore")
self.select.add_column(column)
def findByName(mgd, name):
storage = Midgard.QueryStorage(dbclass="gir_test_book_store")
qs = Midgard.QuerySelect(connection=mgd, storage=storage)
qs.toggle_read_only(False)
qs.set_constraint(
Midgard.QueryConstraint(
property=Midgard.QueryProperty(property="name"),
operator="=",
holder=Midgard.QueryValue.create_with_value(name)))
qs.execute()
return qs.list_objects()
def getBook(self):
st = Midgard.QueryStorage(dbclass="gir_test_book_crud")
qs = Midgard.QuerySelect(connection=self.mgd, storage=st)
qs.set_constraint(
Midgard.QueryConstraint(
property=Midgard.QueryProperty(property="title"),
operator="=",
holder=Midgard.QueryValue.create_with_value(
"The Hoy Grail and Attachments")))
qs.execute()
res = qs.list_objects()
return res[0]
def testConstraint(self):
person_storage = Midgard.QueryStorage(dbclass = "midgard_person")
prop = Midgard.QueryProperty(property = "firstname", storage = person_storage)
constraint = Midgard.QueryConstraint(
storage = person_storage,
operator = "=",
property = prop
)
# test if we get the same
self.assertEqual(constraint.get_storage(), person_storage)
self.assertEqual(constraint.get_operator(), "=")
self.assertEqual(constraint.get_property(), prop)
# then set new ones and test again
new_storage = Midgard.QueryStorage(dbclass = "midgard_person")
new_prop = Midgard.QueryProperty(property = "firstname", storage = person_storage)
new_operator = "<>"
constraint.set_storage(new_storage)
constraint.set_operator(new_operator)
constraint.set_property(new_prop)
self.assertEqual(constraint.get_storage(), new_storage)
self.assertEqual(constraint.get_operator(), new_operator)
self.assertEqual(constraint.get_property(), new_prop)
def testSelectNothing(self):
st = Midgard.QueryStorage(dbclass = "midgard_person")
qs = Midgard.QuerySelect(connection = self.mgd, storage = st)
qs.set_constraint(
Midgard.QueryConstraint(
property = Midgard.QueryProperty(property = "firstname"),
operator = "=",
holder = Midgard.QueryValue.create_with_value("Sir Lancelot")
)
)
qs.execute()
# Do not expect persons
self.assertEqual(qs.get_results_count(), 0);
def testAddJoinAddOrderASCDESC(self):
self.prepareJoin()
self.select.add_order(
Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="edition", storage=self.default_book_storage),
name="edition",
qualifier=book_qualifier), "DESC")
self.select.add_order(
Midgard.SqlQueryColumn(queryproperty=Midgard.QueryProperty(
property="title", storage=self.default_book_storage),
name=book_title_column,
qualifier=book_qualifier), "ASC")
try:
self.select.execute()
except GObject.GError as e:
print self.select.get_query_string()
raise e
query_result = self.select.get_query_result()
rows = query_result.get_rows()
self.assertEqual(rows[0].get_value(book_title_column), "Book C")
self.assertEqual(rows[1].get_value(book_title_column), "Book B")
self.assertEqual(rows[2].get_value(book_title_column), "Book A")
def testExecuteInvalidQuery(self):
self.addColumns()
self.select.set_constraint(
Midgard.SqlQueryConstraint(
column=Midgard.SqlQueryColumn(
queryproperty=Midgard.QueryProperty(property="title"),
qualifier=book_qualifier),
operator="=",
holder=Midgard.QueryValue.create_with_value(book_a_title)))
try:
self.select.execute()
except GObject.GError as e:
self.assertEqual(e.domain, "midgard-execution-error-quark")
self.assertEqual(e.code, Midgard.ValidationError.TYPE_INVALID)
def testGetColumns(self):
column = Midgard.SqlQueryColumn(
queryproperty=Midgard.QueryProperty(property="a"),
name="a",
qualifier="t1")
self.select.add_column(column)
column = Midgard.SqlQueryColumn(
queryproperty=Midgard.QueryProperty(property="b"),
name="b",
qualifier="t2")
self.select.add_column(column)
column = Midgard.SqlQueryColumn(
queryproperty=Midgard.QueryProperty(property="c"),
name="c",
qualifier="t3")
self.select.add_column(column)
columns = self.select.get_columns()
self.assertEqual(len(columns), 3)
names = {"a", "b", "c"}
qualifiers = {"t1", "t2", "t3"}
for column in columns:
self.assertIn(column.get_name(), names)
self.assertIn(column.get_qualifier(), qualifiers)
def tearDownClass():
mgd = TestConnection.openConnection()
tr = Midgard.Transaction(connection = mgd)
tr.begin()
st = Midgard.QueryStorage(dbclass = "midgard_person")
qs = Midgard.QuerySelect(connection = mgd, storage = st)
qs.set_constraint(
Midgard.QueryConstraint(
property = Midgard.QueryProperty(property = "lastname"),
operator = "=",
holder = Midgard.QueryValue.create_with_value("Smith")
)
)
qs.execute()
for person in qs.list_objects():
person.purge(False)
tr.commit()
def testExecuteInvalidConstraint(self):
self.addColumns()
# SqlQueryConstraint expected
self.select.set_constraint(
Midgard.QueryConstraint(
property=Midgard.QueryProperty(property="name"),
operator="=",
holder=Midgard.QueryValue.create_with_value(
"InvalidConstraint")))
try:
self.select.execute()
except GObject.GError as e:
self.assertEqual(e.domain, "midgard-validation-error-quark")
self.assertEqual(e.code, Midgard.ValidationError.TYPE_INVALID)
self.assertEqual(
e.message,
"Invalid constraint type 'MidgardQueryConstraint'. Expected SqlQueryConstraint or SqlQueryConstraintGroup"
)
def testGetRowsWithABook(self):
self.addColumns()
self.select.set_constraint(
Midgard.SqlQueryConstraint(
column=Midgard.SqlQueryColumn(
queryproperty=Midgard.QueryProperty(
property="title", storage=self.default_book_storage),
qualifier=book_qualifier),
operator="=",
holder=Midgard.QueryValue.create_with_value(book_a_title)))
try:
self.select.execute()
except GObject.GError as e:
print self.select.get_query_string()
raise e
query_result = self.select.get_query_result()
rows = query_result.get_rows()
# There should be one book
self.assertEqual(len(rows), 1)
def testVeryLongStringConstraintWithIndexedColumn(self):
# https://github.com/midgardproject/midgard-core/issues/198
long_firstname = "*****@*****.**"
person = Midgard.Object.factory(self.mgd, "midgard_person", None)
person.set_property("firstname", long_firstname)
self.assertTrue(person.create())
st = Midgard.QueryStorage(dbclass = "midgard_person")
qs = Midgard.QuerySelect(connection = self.mgd, storage = st)
qs.set_constraint(
Midgard.QueryConstraint(
property = Midgard.QueryProperty(property = "firstname"),
operator = "=",
holder = Midgard.QueryValue.create_with_value(long_firstname)
)
)
qs.execute()
objects = qs.list_objects()
self.assertEqual(len(objects), 1)
p = objects[0]
self.assertEqual(p.get_property("firstname"), long_firstname)
person.purge(False)
def testProperty(self):
storage = Midgard.QueryStorage(dbclass = "midgard_person")
prop = Midgard.QueryProperty(property = "firstname", storage = storage)
self.assertEqual(prop.get_property("storage"), storage)
self.assertEqual(prop.get_property("property"), "firstname")
