[grassyknoll commit] r968 - branches/unhork/grassyknoll/backend/sql
codesite...@google.com
Date: Mon Dec 29 10:01:04 2008
New Revision: 968
Modified:
branches/unhork/grassyknoll/backend/sql/MysqlCollection.py
branches/unhork/grassyknoll/backend/sql/SqlCollection.py
branches/unhork/grassyknoll/backend/sql/TableMaker.py
Log:
Issue #161: Fixed TableMaker, SqlCollection to quote table/column names to
ensure preservation of case; fixed MysqlCollection to use ANSI SQL mode to
allow such quotation.
Modified: branches/unhork/grassyknoll/backend/sql/MysqlCollection.py
==============================================================================
--- branches/unhork/grassyknoll/backend/sql/MysqlCollection.py (original)
+++ branches/unhork/grassyknoll/backend/sql/MysqlCollection.py Mon Dec 29
10:01:04 2008
@@ -38,8 +38,11 @@
(host, port, user, passwd, db) =
(getattr(location, 'host', 'localhost') or 'localhost',
getattr(location, 'port', 3306)
or 3306,
location.user, location.passwd,
location.db)
- return MySQLdb.connect(host=host, port=port, user=user,
- passwd=passwd, db=db)
+ connection = MySQLdb.connect(host=host, port=port, user=user,
+ passwd=passwd, db=db)
+ cursor = connection.cursor()
+ cursor.execute("SET sql_mode='ANSI'")
+ return connection
@classmethod
def tableExists(cls, location):
Modified: branches/unhork/grassyknoll/backend/sql/SqlCollection.py
==============================================================================
--- branches/unhork/grassyknoll/backend/sql/SqlCollection.py (original)
+++ branches/unhork/grassyknoll/backend/sql/SqlCollection.py Mon Dec 29
10:01:04 2008
@@ -90,7 +90,7 @@
@rollback
def __len__(self):
- sql = 'SELECT count(*) FROM %s' % self.location.table.name
+ sql = 'SELECT count(*) FROM %s' % self.tableName()
row = self.execute(sql).fetchone()
return row[0]
@@ -106,14 +106,15 @@
if fields is not None:
if not all(TableMaker.validSqlColumn(field) for field in
fields):
raise ValueError('Invalid column name in fields: %r' %
fields)
- fields = ', '.join(fields)
+ fields = ', '.join(map(self.quoteSqlName, fields))
else:
fields = '*'
- sql = 'SELECT %s FROM %s WHERE %s' % (fields,
self.location.table.name, where)
+ sql = 'SELECT %s FROM %s WHERE %s' % \
+ (fields, self.tableName(), where)
return build_result_set(self.execute(sql, *args, **kwargs))
def retrieve(self, ids, fields=None):
- where = '__id__ IN (%s)' % self.placeholders(ids)
+ where = '"__id__" IN (%s)' % self.placeholders(ids)
return self._sqlQuery(where, ids, fields=fields)
@rollback
@@ -139,7 +140,7 @@
raise ValueError('Invalid boolean operator: %r' % boolean)
exprs = []
for name in kwargs:
- exprs.append(' '.join([name, comparator, ':' + name]))
+ exprs.append(' '.join([self.quoteSqlName(name),
comparator, ':' + name]))
where = (' %s ' % boolean).join(exprs)
return self._sqlQuery(where, kwargs, fields=fields)
@@ -151,7 +152,8 @@
def _delete(self, ids):
assert ids
placeholders = self.placeholders(ids)
- sql = 'DELETE FROM %s WHERE __id__ IN (%s)' %
(self.location.table.name, placeholders)
+ sql = 'DELETE FROM "%s" WHERE "__id__" IN (%s)' % \
+ (self.location.table.name, placeholders)
self.execute(sql, ids)
@tryCommit
@@ -163,11 +165,18 @@
(names, values) = undictify(doc)
if not all(TableMaker.validSqlColumn(name) for name in names):
raise ValueError('Invalid column name in doc: %r' % names)
- sql = 'INSERT INTO %s (%s) VALUES (%s)' %
(self.location.table.name,
+ names = map(self.quoteSqlName, names)
+ sql = 'INSERT INTO "%s" (%s) VALUES (%s)' %
(self.location.table.name,
', '.join(names),
self.placeholders(values))
self.execute(sql, values)
+ def quoteSqlName(self, name):
+ return '"%s"' % name
+
+ def tableName(self):
+ return self.quoteSqlName(self.location.table.name)
+
def close(self):
self.connection.close()
Modified: branches/unhork/grassyknoll/backend/sql/TableMaker.py
==============================================================================
--- branches/unhork/grassyknoll/backend/sql/TableMaker.py (original)
+++ branches/unhork/grassyknoll/backend/sql/TableMaker.py Mon Dec 29
10:01:04 2008
@@ -63,7 +63,7 @@
return bool(sqlId.match(s)) and s.upper() not in reservedWords
class Table(Model.Schema):
- """Represents a SQL table, including its full schema (columns,
indexes, etc.)
+ '''Represents a SQL table, including its full schema (columns,
indexes, etc.)
@ivar name: The name of the table.
@type name: str
@@ -76,28 +76,28 @@
>>> table.color=Column(unicode, indexed=True)
>>> table.bought_on=Column(datetime.date, optional=True)
>>> table.show()
- CREATE TABLE pants (
- __id__ VARCHAR(255) PRIMARY KEY NOT NULL,
- bought_on date,
- color text NOT NULL,
- size integer NOT NULL)
- CREATE INDEX idx_pants_color ON pants (color)
- CREATE INDEX idx_pants_size ON pants (size)
+ CREATE TABLE "pants" (
+ "__id__" VARCHAR(255) PRIMARY KEY NOT NULL,
+ "bought_on" date,
+ "color" text NOT NULL,
+ "size" integer NOT NULL)
+ CREATE INDEX "idx_pants_color" ON "pants" ("color")
+ CREATE INDEX "idx_pants_size" ON "pants" ("size")
>>> table.addMultiIndex('size', 'bought_on')
>>> table.addMultiIndex('color', 'size')
>>> table.addMultiIndex('size', 'color')
>>> table.show()
- CREATE TABLE pants (
- __id__ VARCHAR(255) PRIMARY KEY NOT NULL,
- bought_on date,
- color text NOT NULL,
- size integer NOT NULL)
- CREATE INDEX idx_pants_color ON pants (color)
- CREATE INDEX idx_pants_size ON pants (size)
- CREATE INDEX idx_pants_size_bought_on ON pants (size, bought_on)
- CREATE INDEX idx_pants_color_size ON pants (color, size)
- CREATE INDEX idx_pants_size_color ON pants (size, color)
- """
+ CREATE TABLE "pants" (
+ "__id__" VARCHAR(255) PRIMARY KEY NOT NULL,
+ "bought_on" date,
+ "color" text NOT NULL,
+ "size" integer NOT NULL)
+ CREATE INDEX "idx_pants_color" ON "pants" ("color")
+ CREATE INDEX "idx_pants_size" ON "pants" ("size")
+ CREATE INDEX "idx_pants_size_bought_on" ON "pants"
("size", "bought_on")
+ CREATE INDEX "idx_pants_color_size" ON "pants" ("color", "size")
+ CREATE INDEX "idx_pants_size_color" ON "pants" ("size", "color")
+ '''
FieldType = Column
@@ -134,7 +134,7 @@
self.multi_indexes.append(colnames)
def tableSQL(self):
- return "CREATE TABLE %s (\n%s)\n" % (self.name, self.columnsSQL())
+ return 'CREATE TABLE "%s" (\n%s)\n' % (self.name,
self.columnsSQL())
def columnsSQL(self):
# sorting isn't strictly necessary here, since we'll always refer
to
@@ -144,7 +144,7 @@
@staticmethod
def columnSQL(name, column):
- col = '%s %s' % (name, python2column(column.type))
+ col = '"%s" %s' % (name, python2column(column.type))
if column.primary_key:
col += ' PRIMARY KEY NOT NULL' # Additional NOT NULL for
sqlite stupidity.
elif not column.optional:
@@ -152,12 +152,14 @@
return col
def indexSQL(self, name):
- return "CREATE INDEX idx_%s_%s ON %s (%s)" % (self.name, name,
self.name, name)
+ return 'CREATE INDEX "idx_%s_%s" ON "%s" ("%s")' % \
+ (self.name, name, self.name, name)
def multiIndexSql(self, colnames):
assert len(colnames) >= 2, 'Cannnot make multiple column index on
< 2 columns'
- return "CREATE INDEX idx_%s_%s ON %s (%s)" %
(self.name, "_".join(colnames),
-
self.name, ", ".join(colnames))
+ quoted_colnames = ['"%s"' % colname for colname in colnames]
+ return 'CREATE INDEX "idx_%s_%s" ON "%s" (%s)' % \
+ (self.name, "_".join(colnames),
self.name, ", ".join(quoted_colnames))
def build(self, func):
func(self.tableSQL())