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_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_unicode(self): result = self.ds.select(person, sql.where("height IS NULL")) unicode_person = list(result)[0] self.assertEqual(unicode_person.firstname, u"% üäöÜÄÖß")