def test_git_hub_user(app_empty):
app = app_empty
user1 = GitHubUser(login="******", git_hub_id="04:User1")
user2 = GitHubUser(login="******", git_hub_id="04:User2")
type_map = ProductType(name="map")
map1 = Product(name="map1", type_=type_map, posting_git_hub_user=user1)
with app.app_context():
sa.session.add(user1)
sa.session.add(user2)
sa.session.add(map1)
sa.session.commit()
with app.app_context():
map1 = Product.query.filter_by(name="map1").one()
user1 = GitHubUser.query.filter_by(login="******").one()
assert user1 == map1.posting_git_hub_user
assert [map1] == user1.posted_products
with app.app_context():
map1 = Product.query.filter_by(name="map1").one()
user2 = GitHubUser.query.filter_by(login="******").one()
map1.updating_git_hub_user = user2
sa.session.commit()
with app.app_context():
map1 = Product.query.filter_by(name="map1").one()
user2 = GitHubUser.query.filter_by(login="******").one()
assert user2 == map1.updating_git_hub_user
assert [map1] == user2.updated_products
def test_id(app_empty):
# This test doesn't pass because the "set" events to the
# relationships won't be triggered when the foreign keys are set.
# It might be possible to pass this test with before_flush().
# Useful links about before_flush():
# https://docs.sqlalchemy.org/en/14/orm/session_events.html#persistence-events
# https://stackoverflow.com/questions/36731020/sqlalchemy-orm-event-hook-for-attribute-persisted
app = app_empty
#
# +---------+ +--------+
# | | --(child)--> | |
# | parent1 | | | child1 |
# | | <-(parent)-- | |
# +---------+ +--------+
#
type_ = ProductType(name="robot")
parent1 = Product(product_id=1, name="parent1", type_=type_)
child1 = Product(product_id=2, name="child1", type_=type_)
relation_type_parent = ProductRelationType(type_id=1, name="parent")
relation_type_child = ProductRelationType(type_id=2, name="child")
relation_type_parent.reverse = relation_type_child
relation_parent1_to_child1 = ProductRelation(type_id=2,
self_product_id=1,
other_product_id=2)
with app.app_context():
sa.session.add(parent1)
sa.session.add(child1)
sa.session.add(relation_type_parent)
sa.session.add(relation_parent1_to_child1)
sa.session.flush()
sa.session.commit()
with app.app_context():
parent1 = Product.query.filter_by(name="parent1").first()
child1 = Product.query.filter_by(name="child1").first()
assert 1 == len(parent1.relations)
assert 1 == len(child1.relations)
assert "child" == parent1.relations[0].type_.name
assert "parent" == child1.relations[0].type_.name
assert child1 is parent1.relations[0].other
assert parent1 is child1.relations[0].other
assert parent1.relations[0] is child1.relations[0].reverse
assert parent1.relations[0].reverse is child1.relations[0]
def test_self_reverse_type(app_empty, perm):
app = app_empty
#
# +----------+ +----------+
# | | --(sibling)--> | |
# | sibling1 | | | sibling2 |
# | | <-(sibling)-- | |
# +----------+ +----------+
#
type_ = ProductType(name="robot")
sibling1 = Product(name="sibling1", type_=type_)
sibling2 = Product(name="sibling2", type_=type_)
relation_type_sibling = ProductRelationType(name="sibling")
relation_type_sibling.reverse = relation_type_sibling
relation_sibling1_to_sibling2 = ProductRelation()
# set 'type_', 'self_', 'other' in different orders
# each triggers an event in which a reverse is automatically set
for p in perm:
if p == 1:
relation_sibling1_to_sibling2.type_ = relation_type_sibling
elif p == 2:
relation_sibling1_to_sibling2.self_ = sibling1
elif p == 3:
relation_sibling1_to_sibling2.other = sibling2
with app.app_context():
sa.session.add(sibling1)
sa.session.commit()
with app.app_context():
sibling1 = Product.query.filter_by(name="sibling1").first()
sibling2 = Product.query.filter_by(name="sibling2").first()
assert 1 == len(sibling1.relations)
assert 1 == len(sibling2.relations)
assert "sibling" == sibling1.relations[0].type_.name
assert "sibling" == sibling2.relations[0].type_.name
assert sibling1.relations[0].type_ is sibling2.relations[0].type_
assert sibling2 is sibling1.relations[0].other
assert sibling1 is sibling2.relations[0].other
assert sibling1.relations[0] is sibling2.relations[0].reverse
assert sibling1.relations[0].reverse is sibling2.relations[0]
def test_permutations(app_empty, perm):
app = app_empty
#
# +---------+ +--------+
# | | --(child)--> | |
# | parent1 | | | child1 |
# | | <-(parent)-- | |
# +---------+ +--------+
#
type_ = ProductType(name="robot")
parent1 = Product(name="parent1", type_=type_)
child1 = Product(name="child1", type_=type_)
relation_type_parent = ProductRelationType(name="parent")
relation_type_child = ProductRelationType(name="child")
relation_type_parent.reverse = relation_type_child
relation_type_child.reverse = relation_type_parent
relation_parent1_to_child1 = ProductRelation()
# set 'type_', 'self_', 'other' in different orders
# each triggers an event in which a reverse is automatically set
for p in perm:
if p == 1:
relation_parent1_to_child1.type_ = relation_type_child
elif p == 2:
relation_parent1_to_child1.self_ = parent1
elif p == 3:
relation_parent1_to_child1.other = child1
with app.app_context():
sa.session.add(parent1)
sa.session.commit()
with app.app_context():
parent1 = Product.query.filter_by(name="parent1").first()
child1 = Product.query.filter_by(name="child1").first()
assert 1 == len(parent1.relations)
assert 1 == len(child1.relations)
assert "child" == parent1.relations[0].type_.name
assert "parent" == child1.relations[0].type_.name
assert child1 is parent1.relations[0].other
assert parent1 is child1.relations[0].other
assert parent1.relations[0] is child1.relations[0].reverse
assert parent1.relations[0].reverse is child1.relations[0]
def app(app):
y = app
type_map = ProductType(name="map")
map1 = Product(name="map1", type_=type_map) # noqa: F841
map2 = Product(name="map2", type_=type_map) # noqa: F841
map3 = Product(name="map3", type_=type_map) # noqa: F841
with y.app_context():
sa.session.add(type_map)
sa.session.commit()
yield y
def test_attach_to_self(app_empty):
app = app_empty
#
# +---------+ +--------+
# | | --(child)--> | |
# | parent1 | | | child1 |
# | | <-(parent)-- | |
# +---------+ +--------+
#
type_ = ProductType(name="robot")
parent1 = Product(product_id=1, name="parent1", type_=type_)
child1 = Product(product_id=2, name="child1", type_=type_)
relation_type_parent = ProductRelationType(type_id=1, name="parent")
relation_type_child = ProductRelationType(type_id=2, name="child")
relation_type_parent.reverse = relation_type_child
relation_parent1_to_child1 = ProductRelation(type_=relation_type_child,
other=child1)
parent1.relations = [relation_parent1_to_child1]
with app.app_context():
sa.session.add(parent1)
sa.session.add(child1)
sa.session.add(relation_type_parent)
sa.session.add(relation_parent1_to_child1)
sa.session.flush()
sa.session.commit()
with app.app_context():
parent1 = Product.query.filter_by(name="parent1").first()
child1 = Product.query.filter_by(name="child1").first()
assert 1 == len(parent1.relations)
assert 1 == len(child1.relations)
assert "child" == parent1.relations[0].type_.name
assert "parent" == child1.relations[0].type_.name
assert child1 is parent1.relations[0].other
assert parent1 is child1.relations[0].other
assert parent1.relations[0] is child1.relations[0].reverse
assert parent1.relations[0].reverse is child1.relations[0]
def app(app_empty):
y = app_empty
# +--------+
# --(child)--> | |
# | | child1 |
# +---------+ <-(parent)-- | |
# | | +--------+
# | parent1 |
# | | +--------+
# +---------+ --(child)--> | |
# | | child2 |
# <-(parent)-- | |
# +--------+
type_ = ProductType(name="robot")
parent1 = Product(name="parent1", type_=type_)
child1 = Product(name="child1", type_=type_)
child2 = Product(name="child2", type_=type_)
relation_type_parent = ProductRelationType(name="parent")
relation_type_child = ProductRelationType(name="child")
relation_type_parent.reverse = relation_type_child
# parent1 --(child)--> child1
relation_parent1_to_child1 = ProductRelation()
relation_parent1_to_child1.type_ = relation_type_child
relation_parent1_to_child1.self_ = parent1
relation_parent1_to_child1.other = child1
## the reverse relation child1 --(parent)--> parent1 will be
## automatically set
# parent1 --(child)--> child2
relation_parent1_to_child2 = ProductRelation()
relation_parent1_to_child2.type_ = relation_type_child
relation_parent1_to_child2.self_ = parent1
relation_parent1_to_child2.other = child2
## the reverse relation child2 --(parent)--> parent1 will be
## automatically set
# commit
with y.app_context():
sa.session.add(parent1)
sa.session.commit()
yield y
def test_commit(app_empty, field_type, AttributeClass, value):
app = app_empty
with app.app_context():
field = Field(name="field1", type_=field_type)
assoc = TypeFieldAssociation(field=field)
product_type = ProductType(name="map")
product_type.fields = [assoc]
sa.session.add(product_type)
sa.session.commit()
with app.app_context():
product_type = ProductType.query.filter_by(name="map").one()
assoc = product_type.fields[0]
field = assoc.field
product = Product(name="product1", type_=product_type)
attr = AttributeClass(
product=product,
type_field_association=assoc,
field=field,
value=value,
)
sa.session.add(attr)
sa.session.commit()
with app.app_context():
attr = AttributeClass.query.one()
assert attr.__class__ is AttributeClass
assert attr.type_field_association.field.name == "field1"
assert attr.type_field_association.field.type_ is field_type
assert attr.field.name == "field1"
assert attr.field.type_ is field_type
assert attr.value == value
assert getattr(attr.product, AttributeClass.backref_column) == [attr]
assert getattr(attr.field, AttributeClass.backref_column) == [attr]
def test_relation(app):
"""A simple test of adding an object with relation"""
type1 = ProductType(name="type1")
product1 = Product(name="product1", type_=type1)
# The relation has been already established
assert type1 is product1.type_
assert [product1] == type1.products
# The primary and foreign keys are still None
assert type1.type_id is None
assert product1.product_id is None
assert product1.type_id is None
with app.app_context():
sa.session.add(product1)
sa.session.commit()
# The primary keys are assigned
assert type1.type_id is not None
assert product1.product_id is not None
# The foreign key is correctly set
assert product1.type_id == type1.type_id
with app.app_context():
product1 = Product.query.filter_by(name="product1").first()
# The relation is preserved in a different app context
type1 = product1.type_
assert "type1" == type1.name
assert product1 is type1.products[0]
assert product1.type_id == type1.type_id
def app(app_empty):
y = app_empty
# product --- path1
# |
# +- path2
type1 = ProductType(name="type1")
product = Product(name="product", type_=type1)
path1 = ProductFilePath(path="/path1")
path2 = ProductFilePath(path="/path2")
product.paths = [path1, path2]
# commit
with y.app_context():
sa.session.add(product)
sa.session.commit()
yield y
def test_constraint_type_required_add(app_empty):
app = app_empty
map1 = Product(name="map1")
with app.app_context():
sa.session.add(map1)
with pytest.raises(exc.IntegrityError):
sa.session.commit()
with app.app_context():
map1 = Product.query.filter_by(name="map1").one_or_none()
assert map1 is None
def test_product(app_empty):
app = app_empty
type_map = ProductType(name="map")
map1 = Product(name="map1", type_=type_map)
with app.app_context():
sa.session.add(map1)
sa.session.commit()
with app.app_context():
map1 = Product.query.filter_by(name="map1").one_or_none()
assert map1 is not None
type_map = map1.type_
assert "map" == type_map.name
assert [map1] == type_map.products
def test_add_raise(app):
"""A simple test of adding an object with a wrong type"""
type1 = ProductType(name="type1")
# It is not impossible to instnaiate a date field with a wrong
# type, e.g, str
product1 = Product(name="product1",
time_posted="2019-02-13 10:15:21",
type_=type1)
with app.app_context():
# It is also possible to add
sa.session.add(product1)
# However, it is not possible to commit
with pytest.raises(sqlalchemy.exc.StatementError):
sa.session.commit()
def test_constraint_type_required_delete(app_empty):
app = app_empty
type_map = ProductType(name="map")
map1 = Product(name="map1", type_=type_map)
with app.app_context():
sa.session.add(map1)
sa.session.commit()
# fail to delete a type with a product
with app.app_context():
type_map = ProductType.query.filter_by(name="map").one()
sa.session.delete(type_map)
with pytest.raises(exc.IntegrityError):
sa.session.commit()
# assert the type and the product are still there
with app.app_context():
map1 = Product.query.filter_by(name="map1").one()
assert map1 is not None
type_map = map1.type_
assert "map" == type_map.name
assert [map1] == type_map.products
# delete the product
with app.app_context():
map1 = Product.query.filter_by(name="map1").one()
sa.session.delete(map1)
sa.session.commit()
# assert the type still exists
with app.app_context():
type_map = ProductType.query.filter_by(name="map").one()
assert type_map is not None
assert [] == type_map.products
# delete the type
with app.app_context():
type_map = ProductType.query.filter_by(name="map").one()
sa.session.delete(type_map)
sa.session.commit()
def test_add(app):
"""A simple test of adding an object with a date field"""
type1 = ProductType(name="type1")
# time_posted needs to be initialized with a datetime.date
time_posted = datetime.datetime(2019, 2, 23, 9, 10, 25)
product1 = Product(name="product1", time_posted=time_posted, type_=type1)
with app.app_context():
sa.session.add(product1)
sa.session.commit()
with app.app_context():
product1 = Product.query.filter_by(name="product1").first()
# The type of the field "time_posted" of Product is "datetime.date"
assert isinstance(product1.time_posted, datetime.date)
assert (datetime.datetime(2019, 2, 23, 9, 10,
25) == product1.time_posted)
def test_delte_orphan_type_field_association_and_field(app_empty):
app = app_empty
with app.app_context():
field1 = Field(name="field1", type_=FieldType.UnicodeText)
assoc1 = TypeFieldAssociation(field=field1)
type1 = ProductType(type_id=1, name="type1", fields=[assoc1])
product1 = Product(name="product1", type_=type1)
field1_attribute_class = field1.type_.attribute_class
attr1 = field1_attribute_class(
product=product1,
type_field_association=assoc1,
field=field1,
value="value1",
)
sa.session.add(product1)
sa.session.commit()
field1_id = field1.field_id
assoc1_id = assoc1.iid
product1_id = product1.product_id
attr1_id = attr1.iid
with app.app_context():
assoc1 = TypeFieldAssociation.query.filter_by(iid=assoc1_id).one()
field1 = Field.query.filter_by(field_id=field1_id).one()
sa.session.delete(assoc1)
sa.session.delete(field1)
sa.session.commit()
with app.app_context():
product1 = Product.query.filter_by(product_id=product1_id).one()
attr1 = field1_attribute_class.query.filter_by(
iid=attr1_id
).one_or_none()
assert attr1 is None
def test_order_nested(app_empty):
app = app_empty
field_attr1 = Field(name="attr1", type_=FieldType.UnicodeText)
field_attr2 = Field(name="attr2", type_=FieldType.UnicodeText)
Map = ProductType(type_id=1, name="map")
assoc1 = TypeFieldAssociation(field=field_attr1)
assoc2 = TypeFieldAssociation(field=field_attr2)
Map.fields = [assoc1, assoc2]
map1 = Product(product_id=1, name="map1", type_=Map)
map2 = Product(product_id=2, name="map2", type_=Map)
map3 = Product(product_id=3, name="map3", type_=Map)
map4 = Product(product_id=4, name="map4", type_=Map)
map5 = Product(product_id=5, name="map5", type_=Map)
AttributeUnicodeText(
product=map1,
type_field_association=assoc1,
field=field_attr1,
value="1",
)
AttributeUnicodeText(
product=map2,
type_field_association=assoc1,
field=field_attr1,
value="2",
)
AttributeUnicodeText(
product=map3,
type_field_association=assoc1,
field=field_attr1,
value="1",
)
AttributeUnicodeText(
product=map4,
type_field_association=assoc1,
field=field_attr1,
value="2",
)
AttributeUnicodeText(
product=map5,
type_field_association=assoc1,
field=field_attr1,
value="1",
)
AttributeUnicodeText(
product=map1,
type_field_association=assoc2,
field=field_attr2,
value="b",
)
AttributeUnicodeText(
product=map2,
type_field_association=assoc2,
field=field_attr2,
value="a",
)
AttributeUnicodeText(
product=map3,
type_field_association=assoc2,
field=field_attr2,
value="c",
)
AttributeUnicodeText(
product=map4,
type_field_association=assoc2,
field=field_attr2,
value="b",
)
AttributeUnicodeText(
product=map5,
type_field_association=assoc2,
field=field_attr2,
value="a",
)
with app.app_context():
sa.session.add(Map)
sa.session.commit()
with app.app_context():
map1 = Product.query.filter_by(product_id=1).one()
map2 = Product.query.filter_by(product_id=2).one()
map3 = Product.query.filter_by(product_id=3).one()
map4 = Product.query.filter_by(product_id=4).one()
map5 = Product.query.filter_by(product_id=5).one()
expected = [map2, map4, map5, map1, map3]
# ORM Alias: https://docs.sqlalchemy.org/en/14/tutorial/data_select.html#orm-entity-aliases
# refer to the same table multiple times
AliasedAttributeUnicodeText1 = aliased(AttributeUnicodeText)
AliasedAttributeUnicodeText2 = aliased(AttributeUnicodeText)
AliasedTypeFieldAssociation1 = aliased(TypeFieldAssociation)
AliasedTypeFieldAssociation2 = aliased(TypeFieldAssociation)
AliasedField1 = aliased(Field)
AliasedField2 = aliased(Field)
# sort descending order of attr1 then ascending order of attr2
query = (
Product.query.join(ProductType)
.filter_by(name="map")
.join(AliasedAttributeUnicodeText1)
.join(AliasedTypeFieldAssociation1)
.join(AliasedField1)
.filter_by(name="attr1")
.order_by(AliasedAttributeUnicodeText1.value.desc())
.join(
AliasedAttributeUnicodeText2, Product.attributes_unicode_text
)
.join(AliasedTypeFieldAssociation2)
.join(AliasedField2)
.filter_by(name="attr2")
.order_by(AliasedAttributeUnicodeText2.value)
)
# print(sqlparse.format(str(query), reindent=True))
actual = query.all()
assert actual == expected
def app(app_empty):
y = app_empty
# create fields
field1 = Field(name="attr1", type_=FieldType.UnicodeText)
field2 = Field(name="date_produced", type_=FieldType.Date)
field3 = Field(name="time_posted", type_=FieldType.DateTime)
fields = [field1, field2, field3]
# create product types
Map = ProductType(
type_id=1,
name="map",
order=2,
indef_article="a",
singular="map",
plural="maps",
icon="mdi-map",
fields=[TypeFieldAssociation(field=f) for f in fields],
)
Beam = ProductType(
type_id=2,
name="beam",
order=1,
indef_article="a",
singular="beam",
plural="beams",
icon="mdi-spotlight-beam",
fields=[TypeFieldAssociation(field=f) for f in fields],
)
# create products
map1 = Product(product_id=1, name="map1", type_=Map)
values = (
"value1",
datetime.date(2020, 2, 1),
datetime.datetime(2020, 2, 1, 9, 10, 25),
)
for assoc, value in zip(Map.fields, values):
assoc.field.type_.attribute_class(
product=map1,
type_field_association=assoc,
field=assoc.field,
value=value,
)
map2 = Product(product_id=2, name="map2", type_=Map)
values = (
"value2",
datetime.date(2020, 2, 10),
datetime.datetime(2020, 2, 10, 13, 20, 2),
)
for assoc, value in zip(Map.fields, values):
assoc.field.type_.attribute_class(
product=map2,
type_field_association=assoc,
field=assoc.field,
value=value,
)
map3 = Product(product_id=3, name="map3", type_=Map)
values = (
"value3",
datetime.date(2020, 3, 19),
datetime.datetime(2020, 3, 20, 8, 45, 30),
)
for assoc, value in zip(Map.fields, values):
assoc.field.type_.attribute_class(
product=map3,
type_field_association=assoc,
field=assoc.field,
value=value,
)
beam1 = Product(product_id=4, name="beam1", type_=Beam)
values = (
"value4",
datetime.date(2020, 2, 5),
datetime.datetime(2020, 2, 5, 12, 3, 48),
)
for assoc, value in zip(Map.fields, values):
assoc.field.type_.attribute_class(
product=beam1,
type_field_association=assoc,
field=assoc.field,
value=value,
)
beam2 = Product(product_id=5, name="beam2", type_=Beam)
values = (
"value5",
datetime.date(2020, 3, 4),
datetime.datetime(2020, 3, 4, 19, 22, 5),
)
for assoc, value in zip(Map.fields, values):
assoc.field.type_.attribute_class(
product=beam2,
type_field_association=assoc,
field=assoc.field,
value=value,
)
with y.app_context():
sa.session.add(Map)
sa.session.add(Beam)
sa.session.commit()
yield y