def test(self):
for country_name, cities in self.data:
new_country = country(name=country_name)
self.ds.insert(new_country)
for city_name in cities:
new_country.cities.append(city(name=city_name))
new_country.cities.append(city(name="Bremen"))
# But Bremen is not an English town, is it?
result = self.ds.select(
city, sql.where("name=", sql.string_literal("Bremen")))
bremen = result.next()
result = self.ds.select(
country, sql.where("name=", sql.string_literal("Germany")))
germany = result.next()
# this will update the foreign key in the city table
bremen.country_id = germany.id
self.ds.flush_updates()
german_cities = germany.cities.select(sql.order_by("name"))
names = map(lambda c: c.name, list(german_cities))
self.assertEqual(
names, [u"Berlin", u"Bremen", u"Hamburg", u"München", u"Witten"])
bremen_where = sql.where("name='Bremen'")
self.assertEqual(len(germany.cities), 5)
self.assertEqual(germany.cities.len(bremen_where), 1)
bremen = germany.cities.select(bremen_where).next()
self.assertEqual(bremen.name, u"Bremen")
def test(self):
for country_name, cities in self.data:
new_country = country(name=country_name)
self.ds.insert(new_country)
for city_name in cities:
new_country.cities.append(city(name=city_name))
new_country.cities.append(city(name="Bremen"))
# But Bremen is not an English town, is it?
result = self.ds.select(city, sql.where("name=", sql.string_literal("Bremen")))
bremen = result.next()
result = self.ds.select(country, sql.where("name=", sql.string_literal("Germany")))
germany = result.next()
# this will update the foreign key in the city table
bremen.country_id = germany.id
self.ds.commit(bremen, germany)
german_cities = germany.cities.select(sql.order_by("name"))
names = map(lambda c: c.name, list(german_cities))
self.assertEqual(names, [u"Berlin", u"Bremen", u"Hamburg", u"München", u"Witten"])
bremen_where = sql.where("name='Bremen'")
self.assertEqual(len(germany.cities), 5)
self.assertEqual(germany.cities.len(bremen_where), 1)
bremen = germany.cities.select(bremen_where).next()
self.assertEqual(bremen.name, u"Bremen")
def test(self):
root = mail_admin(name="Me", email="*****@*****.**")
one = mail_admin(name="Admin One", email="*****@*****.**")
two = mail_admin(name="Admin Two", email="*****@*****.**")
self.ds.insert(one)
self.ds.insert(two)
self.ds.insert(root)
domains = []
for a in (
"tux4web.de",
"t4w.de",
"vorberg.name",
):
d = domain(fqdn=a)
domains.append(d)
self.ds.insert(d)
# sqllog.reset()
one.domains.append(domains[0])
two.domains.append(domains[1])
two.domains.append(domains[2])
self.assertEqual(one.domains.len(), 1)
self.assertEqual(two.domains.len(), 2)
root.domains = domains
self.assertEqual(len(root.domains), 3)
root.domains.append(domain(fqdn="test.de"))
self.assertEqual(len(root.domains), 4)
# type checking...
self.assertRaises(TypeError, root.domains.append, one)
def invalid_set():
root.domains = 0
self.assertRaises(ValueError, invalid_set)
# some special cases:
count = root.domains.len(sql.where("domain.fqdn = 'test.de'"))
self.assertEqual(count, 1)
result = root.domains.select(sql.where("domain.fqdn > 'u'"))
fqdns = map(lambda domain: domain.fqdn, result)
self.assertEqual(fqdns[0], "vorberg.name")
# unlink
two.domains.unlink(domains[1])
self.assertEqual(len(two.domains), 1)
two.domains.unlink_by_primary_key(sql.integer_literal(3))
self.assertEqual(len(two.domains), 0)
def test(self):
root = mail_admin(name="Me", email="*****@*****.**")
one = mail_admin(name="Admin One", email="*****@*****.**")
two = mail_admin(name="Admin Two", email="*****@*****.**")
self.ds.insert(one)
self.ds.insert(two)
self.ds.insert(root)
domains = []
for a in ("tux4web.de", "t4w.de", "vorberg.name",):
d = domain(fqdn=a)
domains.append(d)
self.ds.insert(d)
# sqllog.reset()
one.domains.append(domains[0])
two.domains.append(domains[1])
two.domains.append(domains[2])
self.assertEqual(one.domains.len(), 1)
self.assertEqual(two.domains.len(), 2)
root.domains = domains
self.assertEqual(len(root.domains), 3)
root.domains.append(domain(fqdn="test.de"))
self.assertEqual(len(root.domains), 4)
# type checking...
self.assertRaises(TypeError, root.domains.append, one)
def invalid_set(): root.domains = 0
self.assertRaises(ValueError, invalid_set)
# some special cases:
count = root.domains.len(sql.where("domain.fqdn = 'test.de'"))
self.assertEqual(count, 1)
result = root.domains.select(sql.where("domain.fqdn > 'u'"))
fqdns = map(lambda domain: domain.fqdn, result)
self.assertEqual(fqdns[0], "vorberg.name")
# unlink
two.domains.unlink(domains[1])
self.assertEqual(len(two.domains), 1)
two.domains.unlink_by_primary_key(sql.integer_literal(3))
self.assertEqual(len(two.domains), 0)
def select_after_insert_where(self, dbobj):
if dbobj.__primary_key__ is None: raise PrimaryKeyNotKnown()
primary_key_attributes = tuple(dbobj.__primary_key__.attributes())
if len(primary_key_attributes) == 1:
primary_key_attribute = primary_key_attributes[0]
else:
primary_key_attribute = None
if isinstance(primary_key_attribute, datatypes.serial):
# Serial columns are treated specially
if primary_key_attribute.sequence_name is None:
if dbobj.__relation__.schema is not None:
# If the relation's name is quoted and contains illegal
# characters for a sequence name, this wil result in an
# illegal identifyer. In that case please specify
# the sequence name by hand.
relation = "%s.%s" % (
dbobj.__relation__.schema,
dbobj.__relation__.name.name,
)
else:
relation = dbobj.__relation__.name
sequence_name = "%s_%s_seq" % (
relation,
primary_key_attribute.column,
)
else:
sequence_name = primary_key_attribute.sequence_name
where = sql.where(
sql.expression(primary_key_attribute.column, " = ",
"currval('%s')" % (sequence_name, )))
elif isinstance(primary_key_attribute, common_serial):
where = sql.where(
sql.expression("id = ",
"currval('%s_id_seq')" % dbobj.__relation__))
elif dbobj.__primary_key__.isset():
# If we know the primary key value, we use it to identify
# the new row.
where = dbobj.__primary_key__.where()
else:
raise PrimaryKeyNotKnown()
return where
def select_and_set(self):
result = self.ds.select(person, sql.where("height > 170"))
for person in result:
person.height += 10
self.ds.flush_updates()
def primary_key_where(self, dbclass, key):
"""
Return a orm2.sql where clause that will yield the object of dbclass
whoes primary key equals key
@param dbclass: The dbclass of the object the where clause is
supposed to be for.
@param key: Python value representing the primary key or a tuple of
such Python values, if the primary key has multiple columns
"""
# this function is very simmilar to keys.key.where() - maybe unify?
if type(key) != TupleType: key = ( key, )
primary_key = keys.primary_key(dbclass)
if len(key) != len(primary_key.key_attributes):
msg = "The primary key for %s must have %i elements." % \
( repr(dbclass), len(primary_key.key_attributes), )
raise IllegalPrimaryKey(msg)
where = []
for property, value in zip(primary_key.attributes(), key):
where.append(property.column)
where.append("=")
where.append(property.sql_literal_class(value))
where.append("AND")
del where[-1] # remove last "AND"
return sql.where(*where)
def test(self):
localhost = self.ds.select_one(host, sql.where("fqhn = 'localhost'"))
self.assertEqual(localhost.ip_numeric, dottedQuadToNum("127.0.0.1"))
self.assertEqual(localhost.ip_dotted_quad, "127.0.0.1")
lisa = self.ds.select_one(host, sql.where("fqhn = 'lisa.tux4web.de'"))
lisa.ip_numeric = 0L
self.ds.flush_updates()
self.assertEqual(lisa.ip_numeric, 0L)
self.assertEqual(lisa.ip_dotted_quad, "0.0.0.0")
bart = self.ds.select_one(host, sql.where("fqhn = 'bart.tux4web.de'"))
bart.ip_dotted_quad = "255.255.255.255"
self.ds.flush_updates()
self.assertEqual(bart.ip_numeric, dottedQuadToNum("255.255.255.255"))
self.assertEqual(bart.ip_dotted_quad, "255.255.255.255")
def select_and_set(self):
result = self.ds.select(person, sql.where("height > 170"))
for person in result:
person.height += 10
self.ds.flush_updates()
def test(self):
localhost = self.ds.select_one(host, sql.where("fqhn = 'localhost'"))
self.assertEqual(localhost.ip_numeric, dottedQuadToNum("127.0.0.1"))
self.assertEqual(localhost.ip_dotted_quad, "127.0.0.1")
lisa = self.ds.select_one(host, sql.where("fqhn = 'lisa.tux4web.de'"))
lisa.ip_numeric = 0L
self.ds.flush_updates()
self.assertEqual(lisa.ip_numeric, 0L)
self.assertEqual(lisa.ip_dotted_quad, "0.0.0.0")
bart = self.ds.select_one(host, sql.where("fqhn = 'bart.tux4web.de'"))
bart.ip_dotted_quad = "255.255.255.255"
self.ds.flush_updates()
self.assertEqual(bart.ip_numeric, dottedQuadToNum("255.255.255.255"))
self.assertEqual(bart.ip_dotted_quad, "255.255.255.255")
def select_with_where(self):
result = self.ds.select(person, sql.where("height > 170"))
firstnames = map(lambda p: p.firstname, list(result))
firstnames.sort()
self.assertEqual( firstnames, [ u"Diedrich", u"Marie-Luise", ] )
def primary_key_where(self, dbclass, key):
"""
Return a orm2.sql where clause that will yield the object of dbclass
whoes primary key equals key
@param dbclass: The dbclass of the object the where clause is
supposed to be for.
@param key: Python value representing the primary key or a tuple of
such Python values, if the primary key has multiple columns
"""
# this function is very simmilar to keys.key.where() - maybe unify?
if type(key) != TupleType: key = (key, )
primary_key = keys.primary_key(dbclass)
if len(key) != len(primary_key.key_attributes):
msg = "The primary key for %s must have %i elements." % \
( repr(dbclass), len(primary_key.key_attributes), )
raise IllegalPrimaryKey(msg)
where = []
for property, value in zip(primary_key.attributes(), key):
where.append(property.column)
where.append("=")
where.append(property.sql_literal_class(value))
where.append("AND")
del where[-1] # remove last "AND"
return sql.where(*where)
def select_after_insert_where(self, dbobj):
if dbobj.__primary_key__ is None: raise PrimaryKeyNotKnown()
primary_key_attributes = tuple(dbobj.__primary_key__.attributes())
if len(primary_key_attributes) == 1:
primary_key_attribute = primary_key_attributes[0]
else:
primary_key_attribute = None
if isinstance(primary_key_attribute, datatypes.serial):
# Serial columns are treated specially
if primary_key_attribute.sequence_name is None:
if dbobj.__relation__.schema is not None:
# If the relation's name is quoted and contains illegal
# characters for a sequence name, this wil result in an
# illegal identifyer. In that case please specify
# the sequence name by hand.
relation = "%s.%s" % ( dbobj.__relation__.schema,
dbobj.__relation__.name.name, )
else:
relation = dbobj.__relation__.name
sequence_name = "%s_%s_seq" % ( relation,
primary_key_attribute.column, )
else:
sequence_name = primary_key_attribute.sequence_name
where = sql.where(sql.expression( primary_key_attribute.column,
" = ",
"currval('%s')" % (sequence_name, ) ))
elif isinstance(primary_key_attribute, common_serial):
where = sql.where(sql.expression( "id = ",
"currval('%s_id_seq')" % dbobj.__relation__ ))
elif dbobj.__primary_key__.isset():
# If we know the primary key value, we use it to identify
# the new row.
where = dbobj.__primary_key__.where()
else:
raise PrimaryKeyNotKnown()
return where
def select_with_where_and_order_by(self):
result = self.ds.select(person,
sql.where("height IS NOT NULL"),
sql.order_by("height"))
firstnames = map(lambda p: p.firstname, list(result))
self.assertEqual(firstnames, [u"Annette", u"Marie-Luise",
u"Diedrich", ])
def test(self):
for fqdn, emails in self.data:
dom, tld = split(fqdn, ".")
new_domain = domain(domain=dom, tld=tld)
self.ds.insert(new_domain)
for addr in emails:
new_domain.emails.append(
email(remote_part_domain=dom,
remote_part_tld=tld,
local_part=addr))
# Let's check...
tux4web = self.ds.select_one(domain, sql.where("domain='tux4web'"))
self.assertEqual(len(tux4web.emails), 4)
apple = self.ds.select_by_primary_key(domain, (
"apple",
"com",
))
self.assertEqual(len(apple.emails), 3)
result = apple.emails.select(sql.where("local_part = 'steve'"))
steve = result.next()
self.assertEqual(steve.local_part, "steve")
self.assertEqual(steve.remote_part_domain, "apple")
# The other way 'round
emails = self.ds.select(email)
# This will yield one select statement for each email.
# This effect can be prevented by using SQL joins.
# This is being worked on.
for a in emails:
self.assertEqual(a.remote_part_domain, a.domain.domain)
new_email = email(local_part="dv")
tux4web.emails.append(new_email)
self.assertEqual(len(tux4web.emails), 5)
result = tux4web.emails.select(sql.order_by("local_part"))
result = list(result)
last = result[-1]
self.assertEqual(last.local_part, "support")
def where(self):
"""
Return the WHERE clause that selects the child objects for the
given parent. The clause will also include the condition to limit
the JOIN to the appropriate rows.
"""
parent_where = sql.where(
sql.column(self.relationship.parent_link_column(self.dbobj),
relation=self.relationship.link_relation), " = ",
self.dbobj.__primary_key__.sql_literal())
join_where = sql.where(
sql.column(self.child_class().__primary_key__,
self.child_class().__relation__), " = ",
sql.column(self.relationship.child_link_column(),
self.relationship.link_relation))
return join_where + parent_where
def select_with_where_and_order_by(self):
result = self.ds.select(person, sql.where("height IS NOT NULL"),
sql.order_by("height"))
firstnames = map(lambda p: p.firstname, list(result))
self.assertEqual(firstnames, [
u"Annette",
u"Marie-Luise",
u"Diedrich",
])
def where(self):
"""
Return the WHERE clause that selects the child objects for the
given parent. The clause will also include the condition to limit
the JOIN to the appropriate rows.
"""
parent_where = sql.where(
sql.column(self.relationship.parent_link_column(self.dbobj), relation=self.relationship.link_relation),
" = ",
self.dbobj.__primary_key__.sql_literal(),
)
join_where = sql.where(
sql.column(self.child_class().__primary_key__, self.child_class().__relation__),
" = ",
sql.column(self.relationship.child_link_column(), self.relationship.link_relation),
)
return join_where + parent_where
def select_with_where(self):
result = self.ds.select(person, sql.where("height > 170"))
firstnames = map(lambda p: p.firstname, list(result))
firstnames.sort()
self.assertEqual(firstnames, [
u"Diedrich",
u"Marie-Luise",
])
def test(self):
for fqdn, emails in self.data:
dom, tld = split(fqdn, ".")
new_domain = domain(domain=dom, tld=tld)
self.ds.insert(new_domain)
for addr in emails:
new_domain.emails.append(
email(remote_part_domain=dom, remote_part_tld=tld,
local_part=addr))
# Let's check...
tux4web = self.ds.select_one(domain, sql.where("domain='tux4web'"))
self.assertEqual(len(tux4web.emails), 4)
apple = self.ds.select_by_primary_key(domain, ("apple", "com",))
self.assertEqual(len(apple.emails), 3)
result = apple.emails.select(sql.where("local_part = 'steve'"))
steve = result.next()
self.assertEqual(steve.local_part, "steve")
self.assertEqual(steve.remote_part_domain, "apple")
# The other way 'round
emails = self.ds.select(email)
# This will yield one select statement for each email.
# This effect can be prevented by using SQL joins.
# This is being worked on.
for a in emails:
self.assertEqual(a.remote_part_domain, a.domain.domain)
new_email = email(local_part="dv")
tux4web.emails.append(new_email)
self.assertEqual(len(tux4web.emails), 5)
result = tux4web.emails.select(sql.order_by("local_part"))
result = list(result)
last = result[-1]
self.assertEqual(last.local_part, "support")
def __set__(self, dbobj, value):
# make sure value is a sequence.
try:
value = list(value)
except TypeError:
raise ValueError("You must assing a sequence of %s to this dbproperty!" % repr(self.child_class))
# delete all links from the link_relation that point to the dbobj
command = sql.delete(
self.link_relation, sql.where(self.parent_link_column(dbobj), " = ", dbobj.__primary_key__.sql_literal())
)
dbobj.__ds__().execute(command)
# use the result class to (re-)insert the links
result = self.result(dbobj, self)
result.append(*value)
def unlink_by_primary_key(self, pkey):
"""
Remove an entry from the link relation that links the parent
dbobj to a child identfyed by pkey
@param pkey: An sql.literal instance
"""
if not isinstance(pkey, sql.literal):
raise TypeError("pkey must be an sql.literal instance!")
cmd = sql.delete(
self.relationship.link_relation,
sql.where(self.relationship.parent_link_column(self.dbobj),
" = ",
self.dbobj.__primary_key__.sql_literal(), " AND ",
self.relationship.child_link_column(), " = ", pkey))
self.ds().execute(cmd)
def __set__(self, dbobj, value):
# make sure value is a sequence.
try:
value = list(value)
except TypeError:
raise ValueError(
"You must assing a sequence of %s to this dbproperty!" %
repr(self.child_class))
# delete all links from the link_relation that point to the dbobj
command = sql.delete(
self.link_relation,
sql.where(self.parent_link_column(dbobj), " = ",
dbobj.__primary_key__.sql_literal()))
dbobj.__ds__().execute(command)
# use the result class to (re-)insert the links
result = self.result(dbobj, self)
result.append(*value)
def select_after_insert_where(self, dbobj):
if dbobj.__primary_key__ is None: raise PrimaryKeyNotKnown()
primary_key_attributes = tuple(dbobj.__primary_key__.attributes())
if len(primary_key_attributes) == 1:
primary_key_attribute = primary_key_attributes[0]
else:
primary_key_attribute = None
if isinstance(primary_key_attribute, datatypes.auto_increment) or \
isinstance(primary_key_attribute, common_serial):
where = sql.where(primary_key_attribute.column,
" = LAST_INSERT_ID()")
elif dbobj.__primary_key__.isset():
# If we know the primary key value, we use it to identify
# the new row.
where = dbobj.__primary_key__.where()
else:
raise PrimaryKeyNotKnown()
return where
def unlink_by_primary_key(self, pkey):
"""
Remove an entry from the link relation that links the parent
dbobj to a child identfyed by pkey
@param pkey: An sql.literal instance
"""
if not isinstance(pkey, sql.literal):
raise TypeError("pkey must be an sql.literal instance!")
cmd = sql.delete(
self.relationship.link_relation,
sql.where(
self.relationship.parent_link_column(self.dbobj),
" = ",
self.dbobj.__primary_key__.sql_literal(),
" AND ",
self.relationship.child_link_column(),
" = ",
pkey,
),
)
self.ds().execute(cmd)
def select_after_insert_where(self, dbobj):
if dbobj.__primary_key__ is None: raise PrimaryKeyNotKnown()
primary_key_attributes = tuple(dbobj.__primary_key__.attributes())
if len(primary_key_attributes) == 1:
primary_key_attribute = primary_key_attributes[0]
else:
primary_key_attribute = None
if isinstance(primary_key_attribute, datatypes.auto_increment) or \
isinstance(primary_key_attribute, common_serial):
where = sql.where(primary_key_attribute.column,
" = LAST_INSERT_ID()")
elif dbobj.__primary_key__.isset():
# If we know the primary key value, we use it to identify
# the new row.
where = dbobj.__primary_key__.where()
else:
raise PrimaryKeyNotKnown()
return where
def select_unicode(self):
result = self.ds.select(person, sql.where("height IS NULL"))
unicode_person = list(result)[0]
self.assertEqual(unicode_person.firstname, u"% üäöÜÄÖß")
def select_unicode(self):
result = self.ds.select(person, sql.where("height IS NULL"))
unicode_person = list(result)[0]
self.assertEqual(unicode_person.firstname, u"% üäöÜÄÖß")
