Return-Path: X-Original-To: apmail-incubator-bloodhound-commits-archive@minotaur.apache.org Delivered-To: apmail-incubator-bloodhound-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 144C29BA2 for ; Mon, 5 Mar 2012 11:39:02 +0000 (UTC) Received: (qmail 98484 invoked by uid 500); 5 Mar 2012 11:39:02 -0000 Delivered-To: apmail-incubator-bloodhound-commits-archive@incubator.apache.org Received: (qmail 98457 invoked by uid 500); 5 Mar 2012 11:39:01 -0000 Mailing-List: contact bloodhound-commits-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: bloodhound-dev@incubator.apache.org Delivered-To: mailing list bloodhound-commits@incubator.apache.org Received: (qmail 98448 invoked by uid 99); 5 Mar 2012 11:39:01 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 05 Mar 2012 11:39:01 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 05 Mar 2012 11:38:57 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 8A38123888FD; Mon, 5 Mar 2012 11:38:36 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1297011 - in /incubator/bloodhound/trunk/bloodhound_multiproduct: multiproduct/model.py tests/model.py Date: Mon, 05 Mar 2012 11:38:36 -0000 To: bloodhound-commits@incubator.apache.org From: gjm@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20120305113836.8A38123888FD@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: gjm Date: Mon Mar 5 11:38:36 2012 New Revision: 1297011 URL: http://svn.apache.org/viewvc?rev=1297011&view=rev Log: update parameterised sql queries to make use of automatic escapes; tests fixed Modified: incubator/bloodhound/trunk/bloodhound_multiproduct/multiproduct/model.py incubator/bloodhound/trunk/bloodhound_multiproduct/tests/model.py Modified: incubator/bloodhound/trunk/bloodhound_multiproduct/multiproduct/model.py URL: http://svn.apache.org/viewvc/incubator/bloodhound/trunk/bloodhound_multiproduct/multiproduct/model.py?rev=1297011&r1=1297010&r2=1297011&view=diff ============================================================================== --- incubator/bloodhound/trunk/bloodhound_multiproduct/multiproduct/model.py (original) +++ incubator/bloodhound/trunk/bloodhound_multiproduct/multiproduct/model.py Mon Mar 5 11:38:36 2012 @@ -29,6 +29,23 @@ DB_VERSION = 1 DB_SYSTEM_KEY = 'bloodhound_multi_product_version' PLUGIN_NAME = 'Bloodhound multi product' +def dict_to_kv_str(data=None, sep=' AND '): + """Converts a dictionary into a string and a list suitable for using as part + of an SQL where clause like: + ('key0=%s AND key1=%s', ['value0','value1']) + The sep argument allows ' AND ' to be changed for ',' for UPDATE purposes + """ + if data is None: + return ('', []) + return (sep.join(['%s=%%s' % k for k in data.keys()]), data.values()) + +def fields_to_kv_str(fields, data, sep=' AND '): + """Converts a list of fields and a dictionary containing those fields into a + string and a list suitable for using as part of an SQL where clause like: + ('key0=%s,key1=%s', ['value0','value1']) + """ + return dict_to_kv_str(dict([(f, data[f]) for f in fields]), sep) + class ModelBase(object): """Base class for the models to factor out common features Derived classes should provide a meta dictionary to describe the table like: @@ -94,8 +111,7 @@ class ModelBase(object): def _get_row(self, keys): """queries the database and stores the result in the model""" row = None - key_fields = self._meta['key_fields'] - where = ','.join(['%s="%s"' % (k, keys[k]) for k in key_fields]) + where, values = fields_to_kv_str(self._meta['key_fields'], keys) fields = ','.join(self._meta['key_fields']+self._meta['non_key_fields']) sdata = {'fields':fields, 'where':where} @@ -104,7 +120,7 @@ class ModelBase(object): sql = """SELECT %(fields)s FROM %(table_name)s WHERE %(where)s""" % sdata with self._env.db_query as db: - for row in db(sql): + for row in db(sql, values): self._update_from_row(row) break else: @@ -115,13 +131,13 @@ class ModelBase(object): """Deletes the matching record from the database""" if not self._exists: raise TracError('%(object_name)s does not exist' % self._meta) - sdata = {'where':','.join(['%s="%s"' % (k, self._data[k]) - for k in self._meta['key_fields']])} + where, values = fields_to_kv_str(self._meta['key_fields'], self._data) + sdata = {'where': where} sdata.update(self._meta) sql = """DELETE FROM %(table_name)s WHERE %(where)s""" % sdata with self._env.db_transaction as db: - db(sql) + db(sql, values) self._exists = False self._data = dict([(k, None) for k in self._data.keys()]) self._old_data.update(self._data) @@ -131,7 +147,7 @@ class ModelBase(object): if self._exists or len(self.select(self._env, where = dict([(k,self._data[k]) for k in self._meta['key_fields']]))): - sdata = {'keys':','.join(['%s="%s"' % (k, self._data[k]) + sdata = {'keys':','.join(["%s='%s'" % (k, self._data[k]) for k in self._meta['key_fields']])} sdata.update(self._meta) raise TracError('%(object_name)s %(keys)s already exists' % @@ -145,13 +161,13 @@ class ModelBase(object): sdata) fields = self._meta['key_fields']+self._meta['non_key_fields'] sdata = {'fields':','.join(fields), - 'values':','.join(['"%s"' % self._data[f] for f in fields])} + 'values':','.join(['%s'] * len(fields))} sdata.update(self._meta) sql = """INSERT INTO %(table_name)s (%(fields)s) VALUES (%(values)s)""" % sdata with self._env.db_transaction as db: - db(sql) + db(sql, [self._data[f] for f in fields]) self._exists = True self._old_data.update(self._data) @@ -163,15 +179,17 @@ class ModelBase(object): if self._data[key] != self._old_data[key]: raise TracError('%s cannot be changed' % key) - sdata = {'where':','.join(['%s="%s"' % (k, self._data[k]) - for k in self._meta['key_fields']]), - 'values':','.join(['%s="%s"' % (k, self._data[k]) - for k in self._meta['non_key_fields']])} + setsql, setvalues = fields_to_kv_str(self._meta['non_key_fields'], + self._data, sep=',') + where, values = fields_to_kv_str(self._meta['key_fields'], self._data) + + sdata = {'where': where, + 'values': setsql} sdata.update(self._meta) sql = """UPDATE %(table_name)s SET %(values)s WHERE %(where)s""" % sdata with self._env.db_transaction as db: - db(sql) + db(sql, setvalues + values) self._old_data.update(self._data) @classmethod @@ -182,12 +200,11 @@ class ModelBase(object): sdata = {'fields':','.join(fields),} sdata.update(cls._meta) - sql = 'SELECT %(fields)s FROM %(table_name)s' % sdata - wherestr = '' - if where is not None: - wherestr = ' WHERE ' + ','.join(['%s="%s"' % (k, v) - for k, v in where.iteritems()]) - for row in env.db_query(sql+wherestr): + sql = r'SELECT %(fields)s FROM %(table_name)s' % sdata + wherestr, values = dict_to_kv_str(where) + if wherestr: + wherestr = ' WHERE ' + wherestr + for row in env.db_query(sql + wherestr, values): # we won't know which class we need until called model = cls.__new__(cls) data = dict([(fields[i], row[i]) for i in range(len(fields))]) @@ -269,8 +286,8 @@ class MultiProductEnvironmentProvider(Co def get_version(self): """Finds the current version of the bloodhound database schema""" rows = self.env.db_query(""" - SELECT value FROM system WHERE name = '%s' - """ % DB_SYSTEM_KEY) + SELECT value FROM system WHERE name = %s + """, (DB_SYSTEM_KEY,)) return int(rows[0][0]) if rows else -1 # IEnvironmentSetupParticipant methods Modified: incubator/bloodhound/trunk/bloodhound_multiproduct/tests/model.py URL: http://svn.apache.org/viewvc/incubator/bloodhound/trunk/bloodhound_multiproduct/tests/model.py?rev=1297011&r1=1297010&r2=1297011&view=diff ============================================================================== --- incubator/bloodhound/trunk/bloodhound_multiproduct/tests/model.py (original) +++ incubator/bloodhound/trunk/bloodhound_multiproduct/tests/model.py Mon Mar 5 11:38:36 2012 @@ -18,7 +18,6 @@ """Tests for multiproduct/model.py""" import unittest -import os import tempfile import shutil @@ -72,14 +71,17 @@ class ProductTestCase(unittest.TestCase) product2.insert() product3.insert() - products = list(Product.select(self.env, {'prefix':'tp'})) + products = list(Product.select(self.env, where={'prefix':'tp'})) self.assertEqual(1, len(products)) - products = list(Product.select(self.env, {'name':'test project'})) + products = list(Product.select(self.env, where={'name':'test project'})) self.assertEqual(3, len(products)) + products = list(Product.select(self.env, where={'prefix':'tp3', + 'name':'test project'})) + self.assertEqual(1, len(products)) def test_update(self): """tests that we can use update to push data to the database""" - product = list(Product.select(self.env, {'prefix':'tp'}))[0] + product = list(Product.select(self.env, where={'prefix':'tp'}))[0] self.assertEqual('test project', product._data['name']) new_data = {'prefix':'tp', @@ -88,7 +90,7 @@ class ProductTestCase(unittest.TestCase) product._data.update(new_data) product.update() - comp_product = list(Product.select(self.env, {'prefix':'tp'}))[0] + comp_product = list(Product.select(self.env, where={'prefix':'tp'}))[0] self.assertEqual('updated', comp_product._data['name']) def test_update_key_change(self): @@ -96,7 +98,7 @@ class ProductTestCase(unittest.TestCase) bad_data = {'prefix':'tp0', 'name':'update', 'description':'nothing'} - product = list(Product.select(self.env, {'prefix':'tp'}))[0] + product = list(Product.select(self.env, where={'prefix':'tp'}))[0] product._data.update(bad_data) self.assertRaises(TracError, product.update) @@ -107,7 +109,7 @@ class ProductTestCase(unittest.TestCase) product._data.update(data) product.insert() - check_products = list(Product.select(self.env, {'prefix':'new'})) + check_products = list(Product.select(self.env, where={'prefix':'new'})) self.assertEqual(product._data['prefix'], check_products[0]._data['prefix']) @@ -124,15 +126,15 @@ class ProductTestCase(unittest.TestCase) def test_delete(self): """test that we are able to delete Products""" - product = list(Product.select(self.env, {'prefix':'tp'}))[0] + product = list(Product.select(self.env, where={'prefix':'tp'}))[0] product.delete() - post = list(Product.select(self.env, {'prefix':'tp'})) + post = list(Product.select(self.env, where={'prefix':'tp'})) self.assertEqual(0, len(post)) def test_delete_twice(self): """test that we error when deleting twice on the same key""" - product = list(Product.select(self.env, {'prefix':'tp'}))[0] + product = list(Product.select(self.env, where={'prefix':'tp'}))[0] product.delete() self.assertRaises(TracError, product.delete) @@ -142,7 +144,7 @@ class ProductTestCase(unittest.TestCase) prefix = self.default_data['prefix'] name = self.default_data['name'] description = self.default_data['description'] - product = list(Product.select(self.env, {'prefix':prefix}))[0] + product = list(Product.select(self.env, where={'prefix':prefix}))[0] self.assertEqual(prefix, product.prefix) self.assertEqual(name, product.name) self.assertEqual(description, product.description) @@ -150,7 +152,7 @@ class ProductTestCase(unittest.TestCase) def test_field_set(self): """tests that we can use table.field = something to set field data""" prefix = self.default_data['prefix'] - product = list(Product.select(self.env, {'prefix':prefix}))[0] + product = list(Product.select(self.env, where={'prefix':prefix}))[0] new_description = 'test change of description' product.description = new_description