|
From: <ian...@us...> - 2003-04-07 01:14:02
|
Update of /cvsroot/sqlobject/SQLObject/SQLObject
In directory sc8-pr-cvs1:/tmp/cvs-serv1207/SQLObject
Modified Files:
DBConnection.py SQLBuilder.py SQLObject.py
Log Message:
* Moved all SQL into DBConnection (_SO_* methods)
* A test case for RelatedJoin
* Created a DBM-based backend. Passes almost all tests!
* Incomplete Pickle-based backend (like DBM, only records are kept
in individual files... I'm not sure I want to use it, though).
Index: DBConnection.py
===================================================================
RCS file: /cvsroot/sqlobject/SQLObject/SQLObject/DBConnection.py,v
retrieving revision 1.11
retrieving revision 1.12
diff -C2 -d -r1.11 -r1.12
*** DBConnection.py 6 Apr 2003 07:56:27 -0000 1.11
--- DBConnection.py 7 Apr 2003 01:13:54 -0000 1.12
***************
*** 5,8 ****
--- 5,15 ----
from Cache import CacheSet
import Col
+ import fcntl
+ try:
+ import cPickle as pickle
+ except ImportError:
+ import pickle
+ import os
+ import anydbm
try:
***************
*** 20,26 ****
except ImportError:
sqlite = None
! __all__ = ['MySQLConnection', 'PostgresConnection', 'SQLiteConnection']
_connections = {}
--- 27,35 ----
except ImportError:
sqlite = None
+ import re
! __all__ = ['MySQLConnection', 'PostgresConnection', 'SQLiteConnection',
! 'PickleConnection', 'DBMConnection']
_connections = {}
***************
*** 290,293 ****
--- 299,310 ----
SQLBuilder.sqlRepr(secondValue)))
+ def _SO_columnClause(self, soClass, kw):
+ return ' '.join(['%s = %s' %
+ (soClass._SO_columnDict[key].dbName,
+ SQLBuilder.sqlRepr(value))
+ for key, value
+ in kw.items()])
+
+
class Transaction(object):
***************
*** 469,470 ****
--- 486,855 ----
# turn it into a boolean:
return not not result
+
+ ########################################
+ ## File-based connections
+ ########################################
+
+ class FileConnection(DBConnection):
+
+ """
+ Files connections should deal with setup, and define the
+ methods:
+
+ * ``_fetchDict(self, table, id)``
+ * ``_saveDict(self, table, id, d)``
+ * ``_newID(table)``
+ * ``tableExists(table)``
+ * ``createTable(soClass)``
+ * ``dropTable(table)``
+ * ``clearTable(table)``
+ * ``_SO_delete(so)``
+ * ``_allIDs()``
+ * ``_SO_createJoinTable(join)``
+ """
+
+ def queryInsertID(self, table, names, values):
+ id = self._newID(table)
+ self._saveDict(table, id, dict(zip(names, values)))
+ return id
+
+ def createColumns(self, soClass):
+ pass
+
+ def _SO_update(self, so, values):
+ d = self._fetchDict(so._table, so.id)
+ for dbName, value in values:
+ d[dbName] = value
+ self._saveDict(so._table, so.id, d)
+
+ def _SO_selectOne(self, so, columnNames):
+ d = self._fetchDict(so._table, so.id)
+ return [d[name] for name in columnNames]
+
+ def _SO_selectOneAlt(self, cls, columnNames, column, value):
+ for id in self._allIDs(cls._table):
+ d = self._fetchDict(cls._table, id)
+ if d[column] == value:
+ d['id'] = id
+ return [d[name] for name in columnNames]
+
+ _createRE = re.compile('CREATE TABLE\s+(IF NOT EXISTS\s+)?([^ ]*)', re.I)
+ _dropRE = re.compile('DROP TABLE\s+(IF EXISTS\s+)?([^ ]*)', re.I)
+
+ def query(self, q):
+ match = self._createRE.search(q)
+ if match:
+ if match.group(1) and self.tableExists(match.group(2)):
+ return
+ class X: pass
+ x = X()
+ x._table = match.group(2)
+ return self.createTable(x)
+ match = self._dropRE.search(q)
+ if match:
+ if match.group(1) and not self.tableExists(match.group(2)):
+ return
+ return self.dropTable(match.group(2), match.group(1))
+
+ def addColumn(self, tableName, column):
+ for id in self._allIDs(tableName):
+ d = self._fetchDict(tableName, id)
+ d[column.dbName] = None
+ self._saveDict(tableName, id, d)
+
+ def delColumn(self, tableName, column):
+ for id in self._allIDs(tableName):
+ d = self._fetchDict(tableName, id)
+ del d[column.dbName]
+ self._saveDict(tableName, id, d)
+
+ def _SO_columnClause(self, soClass, kw):
+ clauses = []
+ for name, value in kw.items():
+ clauses.append(getattr(SQLBuilder.SmartTable(soClass._table), name) == value)
+ return SQLBuilder.AND(*clauses)
+
+ def _SO_selectJoin(self, soClass, column, value):
+ results = []
+ for id in self._allIDs(soClass._table):
+ d = self._fetchDict(soClass._table, id)
+ if d[column] == value:
+ results.append((id,))
+ return results
+
+ class PickleConnection(FileConnection):
+
+ def __init__(self, path, **kw):
+ self.path = path
+ FileConnection.__init__(self, **kw)
+
+ def _filename(self, table, id):
+ return '%s-%i.pickle' % (table, id)
+
+ def _newID(self, table):
+ idFilename = os.path.join(self.path, "%s-ids.txt" % table)
+ try:
+ f = open(idFilename, "r+")
+ except IOError:
+ f = open(idFilename, "w")
+ f.write("0")
+ f.close()
+ f = open(idFilename, "r+")
+ fcntl.lockf(f, fcntl.LOCK_EX)
+ id = int(f.read()) + 1
+ # @@: Can I just close this and open it in write mode, while
+ # retaining this lock?
+ f.seek(0)
+ f.write(str(id))
+ fcntl.lockf(f, fcntl.LOCK_UN)
+ f.close()
+ return id
+
+ def _fetchDict(self, table, id):
+ f = open(self._filename(table, id), 'rb')
+ d = pickle.load(f)
+ f.close()
+ return d
+
+ def _saveDict(self, table, id, d):
+ f = open(self._filename(table, id), 'wb')
+ pickle.dump(d, f)
+ f.close()
+
+ def _fetchTables(self):
+ filename = os.path.join(self.path, "table-list.txt")
+ try:
+ f = open(filename, "r")
+ except IOError:
+ return []
+ lines = [l.strip() for l in f.readlines()]
+ f.close()
+ return lines
+
+ def _saveTables(self, tables):
+ filename = os.path.join(self.path, "table-list.txt")
+ f = open(filename, "w")
+ f.write('\n'.join(tables))
+ f.close()
+
+ def tableExists(self, table):
+ return table in self._fetchTables()
+
+ def createTable(self, soClass):
+ tables = self._fetchTables()
+ tables.append(soClass._table)
+ self._saveTables(tables)
+
+ def dropTable(self, tableName):
+ tables = self._fetchTables()
+ tables.remove(tableName)
+ self._saveTables(tables)
+ self.clearTable(tableName)
+
+ def clearTable(self, tableName):
+ for filename in os.listdir(self.path):
+ if filename.startswith(tableName + "-"):
+ os.unlink(os.path.join(self.path, filename))
+
+ def _SO_delete(self, so):
+ os.unlink(self._filename(so._table, so.id))
+
+
+ class DBMConnection(FileConnection):
+
+ def __init__(self, path, **kw):
+ self.path = path
+ try:
+ self._meta = anydbm.open(os.path.join(path, "meta.db"), "w")
+ except anydbm.error:
+ self._meta = anydbm.open(os.path.join(path, "meta.db"), "c")
+ self._tables = {}
+ FileConnection.__init__(self, **kw)
+
+ def _newID(self, table):
+ id = int(self._meta["%s.id" % table]) + 1
+ self._meta["%s.id" % table] = str(id)
+ return id
+
+ def _saveDict(self, table, id, d):
+ db = self._getDB(table)
+ db[str(id)] = pickle.dumps(d)
+
+ def _fetchDict(self, table, id):
+ return pickle.loads(self._getDB(table)[str(id)])
+
+ def _getDB(self, table):
+ try:
+ return self._tables[table]
+ except KeyError:
+ db = self._openTable(table)
+ self._tables[table] = db
+ return db
+
+ def _openTable(self, table):
+ try:
+ db = anydbm.open(os.path.join(self.path, "%s.db" % table), "w")
+ except anydbm.error:
+ db = anydbm.open(os.path.join(self.path, "%s.db" % table), "c")
+ return db
+
+ def tableExists(self, table):
+ return self._meta.has_key("%s.id" % table) \
+ or os.path.exists(os.path.join(self.path, table + ".db"))
+
+ def createTable(self, soClass):
+ self._meta["%s.id" % soClass._table] = "1"
+
+ def dropTable(self, tableName):
+ try:
+ del self._meta["%s.id" % tableName]
+ except KeyError:
+ pass
+ self.clearTable(tableName)
+
+ def clearTable(self, tableName):
+ if self._tables.has_key(tableName):
+ del self._tables[tableName]
+ filename = os.path.join(self.path, "%s.db" % tableName)
+ if os.path.exists(filename):
+ os.unlink(filename)
+
+ def _SO_delete(self, so):
+ db = self._getDB(so._table)
+ del db[str(so.id)]
+
+ def iterSelect(self, select):
+ return DBMSelectResults(self, select)
+
+ def _allIDs(self, tableName):
+ return self._getDB(tableName).keys()
+
+ def _SO_createJoinTable(self, join):
+ pass
+
+ def _SO_dropJoinTable(self, join):
+ os.unlink(os.path.join(self.path, join.intermediateTable + ".db"))
+
+ def _SO_intermediateJoin(self, table, get, join1, id1):
+ db = self._openTable(table)
+ try:
+ results = db[join1 + str(id1)]
+ except KeyError:
+ return []
+ if not results:
+ return []
+ return [(int(id),) for id in results.split(',')]
+
+ def _SO_intermediateInsert(self, table, join1, id1, join2, id2):
+ db = self._openTable(table)
+ try:
+ results = db[join1 + str(id1)]
+ except KeyError:
+ results = ""
+ if results:
+ db[join1 + str(id1)] = results + "," + str(id2)
+ else:
+ db[join1 + str(id1)] = str(id2)
+
+ try:
+ results = db[join2 + str(id2)]
+ except KeyError:
+ results = ""
+ if results:
+ db[join2 + str(id2)] = results + "," + str(id1)
+ else:
+ db[join2 + str(id2)] = str(id1)
+
+ def _SO_intermediateDelete(self, table, join1, id1, join2, id2):
+ db = self._openTable(table)
+ try:
+ results = db[join1 + str(id1)]
+ except KeyError:
+ results = ""
+ results = map(int, results.split(","))
+ results.remove(int(id2))
+ db[join1 + str(id1)] = ",".join(map(str, results))
+ try:
+ results = db[join2 + str(id2)]
+ except KeyError:
+ results = ""
+ results = map(int, results.split(","))
+ results.remove(int(id1))
+ db[join2 + str(id2)] = ",".join(map(str, results))
+
+
+
+
+ class DBMSelectResults(object):
+
+ def __init__(self, conn, select):
+ self.select = select
+ self.conn = conn
+ self.tables = select.tables
+ self.tableDict = {}
+ self._results = None
+ for i in range(len(self.tables)):
+ self.tableDict[self.tables[i]] = i
+ self.comboIter = _iterAllCombinations(
+ [self.conn._getDB(table).keys() for table in self.tables])
+ if select.ops.get('orderBy'):
+ self._maxNext = -1
+ results = self.allResults()
+ def cmper(a, b, orderBy=select.ops['orderBy']):
+ return cmp(getattr(a, orderBy),
+ getattr(b, orderBy))
+ results.sort(cmper)
+ self._results = results
+ if select.ops.get('start'):
+ if select.ops.get('end'):
+ self._results = self._results[select.ops['start']:select.ops['end']]
+ else:
+ self._results = self._results[select.ops['start']:]
+ elif select.ops.get('end'):
+ self._results = self._results[:select.ops['end']]
+ elif select.ops.get('start'):
+ for i in range(select.ops.get('start')):
+ self.next()
+ if select.ops.get('end'):
+ self._maxNext = select.ops['end'] - select.ops['start']
+ elif select.ops.get('end'):
+ self._maxNext = select.ops['end']
+ else:
+ self._maxNext = -1
+
+ def next(self):
+ if self._results is not None:
+ try:
+ return self._results.pop(0)
+ except IndexError:
+ raise StopIteration
+
+ for idList in self.comboIter:
+ self.idList = idList
+ if SQLBuilder.execute(self.select.clause, self):
+ if not self._maxNext:
+ raise StopIteration
+ self._maxNext -= 1
+ return self.select.sourceClass(int(idList[self.tableDict[self.select.sourceClass._table]]))
+ raise StopIteration
+
+ def field(self, table, field):
+ return self.conn._fetchDict(table, self.idList[self.tableDict[table]])[field]
+
+ def allResults(self):
+ results = []
+ while 1:
+ try:
+ results.append(self.next())
+ except StopIteration:
+ return results
+
+
+ def _iterAllCombinations(l):
+ if len(l) == 1:
+ for id in l[0]:
+ yield [id]
+ else:
+ for id in l[0]:
+ for idList in _iterAllCombinations(l[1:]):
+ yield [id] + idList
Index: SQLBuilder.py
===================================================================
RCS file: /cvsroot/sqlobject/SQLObject/SQLObject/SQLBuilder.py,v
retrieving revision 1.2
retrieving revision 1.3
diff -C2 -d -r1.2 -r1.3
*** SQLBuilder.py 1 Apr 2003 16:51:28 -0000 1.2
--- SQLBuilder.py 7 Apr 2003 01:13:54 -0000 1.3
***************
*** 11,15 ****
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation; either version 2.1 of the
! License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
--- 11,15 ----
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation; either version 2.1 of the
! License, or (at your option any later version.
This program is distributed in the hope that it will be useful,
***************
*** 94,98 ****
origISOStr = None
DateTimeType = None
! import re
def isoStr(val):
--- 94,99 ----
origISOStr = None
DateTimeType = None
! import re, fnmatch
! import operator
def isoStr(val):
***************
*** 131,134 ****
--- 132,141 ----
+ def execute(expr, executor):
+ if hasattr(expr, 'execute'):
+ return expr.execute(executor)
+ else:
+ return expr
+
########################################
## Expression generation
***************
*** 230,236 ****
return {}
class SQLOp(SQLExpression):
def __init__(self, op, expr1, expr2):
! self.op = op
self.expr1 = expr1
self.expr2 = expr2
--- 237,257 ----
return {}
+ operatorMap = {
+ "+": operator.add,
+ "/": operator.div,
+ "-": operator.sub,
+ "*": operator.mul,
+ "<": operator.lt,
+ "<=": operator.le,
+ "=": operator.eq,
+ "!=": operator.ne,
+ ">=": operator.ge,
+ ">": operator.gt,
+ "IN": operator.contains,
+ }
+
class SQLOp(SQLExpression):
def __init__(self, op, expr1, expr2):
! self.op = op.upper()
self.expr1 = expr1
self.expr2 = expr2
***************
*** 239,242 ****
--- 260,284 ----
def components(self):
return [self.expr1, self.expr2]
+ def execute(self, executor):
+ if self.op == "AND":
+ return execute(self.expr1, executor) \
+ and execute(self.expr2, executor)
+ elif self.op == "OR":
+ return execute(self.expr1, executor) \
+ or execute(self.expr2, executor)
+ elif self.op == "LIKE":
+ if not hasattr(self, '_regex'):
+ # @@: Crude, not entirely accurate
+ dest = self.expr2
+ dest = dest.replace("%%", "\001")
+ dest = dest.replace("*", "\002")
+ dest = dest.replace("%", "*")
+ dest = dest.replace("\001", "%")
+ dest = dest.replace("\002", "[*]")
+ self._regex = re.compile(fnmatch.translate(dest), re.I)
+ return self._regex.search(execute(self.expr1, executor))
+ else:
+ return operatorMap[self.op.upper()](execute(self.expr1, executor),
+ execute(self.expr2, executor))
class SQLCall(SQLExpression):
***************
*** 248,251 ****
--- 290,295 ----
def components(self):
return [self.expr] + list(self.args)
+ def execute(self, executor):
+ raise ValueError, "I don't yet know how to locally execute functions"
class SQLPrefix(SQLExpression):
***************
*** 257,260 ****
--- 301,312 ----
def components(self):
return [self.expr]
+ def execute(self, executor):
+ expr = execute(self.expr, executor)
+ if prefix == "+":
+ return expr
+ elif prefix == "-":
+ return -expr
+ elif prefix.upper() == "NOT":
+ return not expr
class SQLConstant(SQLExpression):
***************
*** 263,267 ****
--- 315,328 ----
def sqlRepr(self):
return self.const
+ def execute(self, executor):
+ raise ValueError, "I don't yet know how to execute SQL constants"
+
+ class SQLTrueClauseClass(SQLExpression):
+ def sqlRepr(self):
+ return "1 = 1"
+ def execute(self, executor):
+ return 1
+ SQLTrueClause = SQLTrueClauseClass()
########################################
***************
*** 280,283 ****
--- 341,346 ----
def sqlRepr(self):
return str(self.tableName)
+ def execute(self, executor):
+ raise ValueError, "Tables don't have values"
class SmartTable(Table):
***************
*** 285,289 ****
def __getattr__(self, attr):
if self._capRE.search(attr):
! attr = attr[0] + self._capRE.sub(lambda m: '_%s' % m.group(0), attr[1:])
return Table.__getattr__(self, attr)
--- 348,352 ----
def __getattr__(self, attr):
if self._capRE.search(attr):
! attr = attr[0] + self._capRE.sub(lambda m: '_%s' % m.group(0).lower(), attr[1:])
return Table.__getattr__(self, attr)
***************
*** 296,299 ****
--- 359,364 ----
def tablesUsedImmediate(self):
return [self.tableName]
+ def execute(self, executor):
+ return executor.field(self.tableName, self.fieldName)
class ConstantSpace:
Index: SQLObject.py
===================================================================
RCS file: /cvsroot/sqlobject/SQLObject/SQLObject/SQLObject.py,v
retrieving revision 1.13
retrieving revision 1.14
diff -C2 -d -r1.13 -r1.14
*** SQLObject.py 6 Apr 2003 07:56:27 -0000 1.13
--- SQLObject.py 7 Apr 2003 01:13:54 -0000 1.14
***************
*** 576,580 ****
self._connection._SO_update(self,
[(self._SO_columnDict[name].dbName,
! SQLBuilder.sqlRepr(value))])
# _SO_autoInitDone implies there's a cached value we also
--- 576,580 ----
self._connection._SO_update(self,
[(self._SO_columnDict[name].dbName,
! value)])
# _SO_autoInitDone implies there's a cached value we also
***************
*** 763,771 ****
def selectBy(cls, **kw):
return SelectResults(cls,
! ' '.join(['%s = %s' %
! (cls._SO_columnDict[key].dbName,
! SQLBuilder.sqlRepr(value))
! for key, value
! in kw.items()]))
selectBy = classmethod(selectBy)
--- 763,767 ----
def selectBy(cls, **kw):
return SelectResults(cls,
! cls._connection._SO_columnClause(cls, kw))
selectBy = classmethod(selectBy)
***************
*** 987,991 ****
self.sourceClass = sourceClass
if isinstance(clause, str) and clause == 'all':
! clause = SQLBuilder.SQLConstant('1 = 1')
self.clause = clause
tablesDict = SQLBuilder.tablesUsedDict(self.clause)
--- 983,987 ----
self.sourceClass = sourceClass
if isinstance(clause, str) and clause == 'all':
! clause = SQLBuilder.SQLTrueClause
self.clause = clause
tablesDict = SQLBuilder.tablesUsedDict(self.clause)
|
