| #!/usr/bin/env python |
| # |
| # odb.py |
| # |
| # Object Database Api |
| # |
| # Written by David Jeske <jeske@neotonic.com>, 2001/07. |
| # Inspired by eGroups' sqldb.py originally written by Scott Hassan circa 1998. |
| # |
| # Copyright (C) 2001, by David Jeske and Neotonic |
| # |
| # Goals: |
| # - a simple object-like interface to database data |
| # - database independent (someday) |
| # - relational-style "rigid schema definition" |
| # - object style easy-access |
| # |
| # Example: |
| # |
| # import odb |
| # |
| # # define table |
| # class AgentsTable(odb.Table): |
| # def _defineRows(self): |
| # self.d_addColumn("agent_id",kInteger,None,primarykey = 1,autoincrement = 1) |
| # self.d_addColumn("login",kVarString,200,notnull=1) |
| # self.d_addColumn("ticket_count",kIncInteger,None) |
| # |
| # if __name__ == "__main__": |
| # # open database |
| # ndb = MySQLdb.connect(host = 'localhost', |
| # user='username', |
| # passwd = 'password', |
| # db='testdb') |
| # db = Database(ndb) |
| # tbl = AgentsTable(db,"agents") |
| # |
| # # create row |
| # agent_row = tbl.newRow() |
| # agent_row.login = "foo" |
| # agent_row.save() |
| # |
| # # fetch row (must use primary key) |
| # try: |
| # get_row = tbl.fetchRow( ('agent_id', agent_row.agent_id) ) |
| # except odb.eNoMatchingRows: |
| # print "this is bad, we should have found the row" |
| # |
| # # fetch rows (can return empty list) |
| # list_rows = tbl.fetchRows( ('login', "foo") ) |
| # |
| |
| import string |
| import sys, zlib |
| from log import * |
| |
| import handle_error |
| |
| eNoSuchColumn = "odb.eNoSuchColumn" |
| eNonUniqueMatchSpec = "odb.eNonUniqueMatchSpec" |
| eNoMatchingRows = "odb.eNoMatchingRows" |
| eInternalError = "odb.eInternalError" |
| eInvalidMatchSpec = "odb.eInvalidMatchSpec" |
| eInvalidData = "odb.eInvalidData" |
| eUnsavedObjectLost = "odb.eUnsavedObjectLost" |
| eDuplicateKey = "odb.eDuplicateKey" |
| |
| ##################################### |
| # COLUMN TYPES |
| ################ ###################### |
| # typename ####################### size data means: |
| # # # |
| kInteger = "kInteger" # - |
| kFixedString = "kFixedString" # size |
| kVarString = "kVarString" # maxsize |
| kBigString = "kBigString" # - |
| kIncInteger = "kIncInteger" # - |
| kDateTime = "kDateTime" |
| kTimeStamp = "kTimeStamp" |
| kReal = "kReal" |
| |
| |
| DEBUG = 0 |
| |
| ############## |
| # Database |
| # |
| # this will ultimately turn into a mostly abstract base class for |
| # the DB adaptors for different database types.... |
| # |
| |
| class Database: |
| def __init__(self, db, debug=0): |
| self._tables = {} |
| self.db = db |
| self._cursor = None |
| self.compression_enabled = 0 |
| self.debug = debug |
| self.SQLError = None |
| |
| self.__defaultRowClass = self.defaultRowClass() |
| self.__defaultRowListClass = self.defaultRowListClass() |
| |
| def defaultCursor(self): |
| if self._cursor is None: |
| self._cursor = self.db.cursor() |
| return self._cursor |
| |
| def escape(self,str): |
| raise "Unimplemented Error" |
| |
| def getDefaultRowClass(self): return self.__defaultRowClass |
| def setDefaultRowClass(self, clss): self.__defaultRowClass = clss |
| def getDefaultRowListClass(self): return self.__defaultRowListClass |
| def setDefaultRowListClass(self, clss): self.__defaultRowListClass = clss |
| |
| def defaultRowClass(self): |
| return Row |
| |
| def defaultRowListClass(self): |
| # base type is list... |
| return list |
| |
| def addTable(self, attrname, tblname, tblclass, |
| rowClass = None, check = 0, create = 0, rowListClass = None): |
| tbl = tblclass(self, tblname, rowClass=rowClass, check=check, |
| create=create, rowListClass=rowListClass) |
| self._tables[attrname] = tbl |
| return tbl |
| |
| def close(self): |
| for name, tbl in self._tables.items(): |
| tbl.db = None |
| self._tables = {} |
| if self.db is not None: |
| self.db.close() |
| self.db = None |
| |
| def __getattr__(self, key): |
| if key == "_tables": |
| raise AttributeError, "odb.Database: not initialized properly, self._tables does not exist" |
| |
| try: |
| table_dict = getattr(self,"_tables") |
| return table_dict[key] |
| except KeyError: |
| raise AttributeError, "odb.Database: unknown attribute %s" % (key) |
| |
| def beginTransaction(self, cursor=None): |
| if cursor is None: |
| cursor = self.defaultCursor() |
| dlog(DEV_UPDATE,"begin") |
| cursor.execute("begin") |
| |
| def commitTransaction(self, cursor=None): |
| if cursor is None: |
| cursor = self.defaultCursor() |
| dlog(DEV_UPDATE,"commit") |
| cursor.execute("commit") |
| |
| def rollbackTransaction(self, cursor=None): |
| if cursor is None: |
| cursor = self.defaultCursor() |
| dlog(DEV_UPDATE,"rollback") |
| cursor.execute("rollback") |
| |
| ## |
| ## schema creation code |
| ## |
| |
| def createTables(self): |
| tables = self.listTables() |
| |
| for attrname, tbl in self._tables.items(): |
| tblname = tbl.getTableName() |
| |
| if tblname not in tables: |
| print "table %s does not exist" % tblname |
| tbl.createTable() |
| else: |
| invalidAppCols, invalidDBCols = tbl.checkTable() |
| |
| ## self.alterTableToMatch(tbl) |
| |
| def createIndices(self): |
| indices = self.listIndices() |
| |
| for attrname, tbl in self._tables.items(): |
| for indexName, (columns, unique) in tbl.getIndices().items(): |
| if indexName in indices: continue |
| |
| tbl.createIndex(columns, indexName=indexName, unique=unique) |
| |
| def synchronizeSchema(self): |
| tables = self.listTables() |
| |
| for attrname, tbl in self._tables.items(): |
| tblname = tbl.getTableName() |
| self.alterTableToMatch(tbl) |
| |
| def listTables(self, cursor=None): |
| raise "Unimplemented Error" |
| |
| def listFieldsDict(self, table_name, cursor=None): |
| raise "Unimplemented Error" |
| |
| def listFields(self, table_name, cursor=None): |
| columns = self.listFieldsDict(table_name, cursor=cursor) |
| return columns.keys() |
| |
| ########################################## |
| # Table |
| # |
| |
| |
| class Table: |
| def subclassinit(self): |
| pass |
| def __init__(self,database,table_name, |
| rowClass = None, check = 0, create = 0, rowListClass = None): |
| self.db = database |
| self.__table_name = table_name |
| if rowClass: |
| self.__defaultRowClass = rowClass |
| else: |
| self.__defaultRowClass = database.getDefaultRowClass() |
| |
| if rowListClass: |
| self.__defaultRowListClass = rowListClass |
| else: |
| self.__defaultRowListClass = database.getDefaultRowListClass() |
| |
| # get this stuff ready! |
| |
| self.__column_list = [] |
| self.__vcolumn_list = [] |
| self.__columns_locked = 0 |
| self.__has_value_column = 0 |
| |
| self.__indices = {} |
| |
| # this will be used during init... |
| self.__col_def_hash = None |
| self.__vcol_def_hash = None |
| self.__primary_key_list = None |
| self.__relations_by_table = {} |
| |
| # ask the subclass to def his rows |
| self._defineRows() |
| |
| # get ready to run! |
| self.__lockColumnsAndInit() |
| |
| self.subclassinit() |
| |
| if create: |
| self.createTable() |
| |
| if check: |
| self.checkTable() |
| |
| def _colTypeToSQLType(self, colname, coltype, options): |
| |
| if coltype == kInteger: |
| coltype = "integer" |
| elif coltype == kFixedString: |
| sz = options.get('size', None) |
| if sz is None: coltype = 'char' |
| else: coltype = "char(%s)" % sz |
| elif coltype == kVarString: |
| sz = options.get('size', None) |
| if sz is None: coltype = 'varchar' |
| else: coltype = "varchar(%s)" % sz |
| elif coltype == kBigString: |
| coltype = "text" |
| elif coltype == kIncInteger: |
| coltype = "integer" |
| elif coltype == kDateTime: |
| coltype = "datetime" |
| elif coltype == kTimeStamp: |
| coltype = "timestamp" |
| elif coltype == kReal: |
| coltype = "real" |
| |
| coldef = "%s %s" % (colname, coltype) |
| |
| if options.get('notnull', 0): coldef = coldef + " NOT NULL" |
| if options.get('autoincrement', 0): coldef = coldef + " AUTO_INCREMENT" |
| if options.get('unique', 0): coldef = coldef + " UNIQUE" |
| # if options.get('primarykey', 0): coldef = coldef + " primary key" |
| if options.get('default', None) is not None: coldef = coldef + " DEFAULT %s" % options.get('default') |
| |
| return coldef |
| |
| def getTableName(self): return self.__table_name |
| def setTableName(self, tablename): self.__table_name = tablename |
| |
| def getIndices(self): return self.__indices |
| |
| def _createTableSQL(self): |
| defs = [] |
| for colname, coltype, options in self.__column_list: |
| defs.append(self._colTypeToSQLType(colname, coltype, options)) |
| |
| defs = string.join(defs, ", ") |
| |
| primarykeys = self.getPrimaryKeyList() |
| primarykey_str = "" |
| if primarykeys: |
| primarykey_str = ", PRIMARY KEY (" + string.join(primarykeys, ",") + ")" |
| |
| sql = "create table %s (%s %s)" % (self.__table_name, defs, primarykey_str) |
| return sql |
| |
| def createTable(self, cursor=None): |
| if cursor is None: cursor = self.db.defaultCursor() |
| sql = self._createTableSQL() |
| print "CREATING TABLE:", sql |
| cursor.execute(sql) |
| |
| def dropTable(self, cursor=None): |
| if cursor is None: cursor = self.db.defaultCursor() |
| try: |
| cursor.execute("drop table %s" % self.__table_name) # clean out the table |
| except self.SQLError, reason: |
| pass |
| |
| def renameTable(self, newTableName, cursor=None): |
| if cursor is None: cursor = self.db.defaultCursor() |
| try: |
| cursor.execute("rename table %s to %s" % (self.__table_name, newTableName)) |
| except sel.SQLError, reason: |
| pass |
| |
| self.setTableName(newTableName) |
| |
| def getTableColumnsFromDB(self): |
| return self.db.listFieldsDict(self.__table_name) |
| |
| def checkTable(self, warnflag=1): |
| invalidDBCols = {} |
| invalidAppCols = {} |
| |
| dbcolumns = self.getTableColumnsFromDB() |
| for coldef in self.__column_list: |
| colname = coldef[0] |
| |
| dbcoldef = dbcolumns.get(colname, None) |
| if dbcoldef is None: |
| invalidAppCols[colname] = 1 |
| |
| for colname, row in dbcolumns.items(): |
| coldef = self.__col_def_hash.get(colname, None) |
| if coldef is None: |
| invalidDBCols[colname] = 1 |
| |
| if warnflag == 1: |
| if invalidDBCols: |
| print "----- WARNING ------------------------------------------" |
| print " There are columns defined in the database schema that do" |
| print " not match the application's schema." |
| print " columns:", invalidDBCols.keys() |
| print "--------------------------------------------------------" |
| |
| if invalidAppCols: |
| print "----- WARNING ------------------------------------------" |
| print " There are new columns defined in the application schema" |
| print " that do not match the database's schema." |
| print " columns:", invalidAppCols.keys() |
| print "--------------------------------------------------------" |
| |
| return invalidAppCols, invalidDBCols |
| |
| |
| def alterTableToMatch(self): |
| raise "Unimplemented Error!" |
| |
| def addIndex(self, columns, indexName=None, unique=0): |
| if indexName is None: |
| indexName = self.getTableName() + "_index_" + string.join(columns, "_") |
| |
| self.__indices[indexName] = (columns, unique) |
| |
| def createIndex(self, columns, indexName=None, unique=0, cursor=None): |
| if cursor is None: cursor = self.db.defaultCursor() |
| cols = string.join(columns, ",") |
| |
| if indexName is None: |
| indexName = self.getTableName() + "_index_" + string.join(columns, "_") |
| |
| uniquesql = "" |
| if unique: |
| uniquesql = " unique" |
| sql = "create %s index %s on %s (%s)" % (uniquesql, indexName, self.getTableName(), cols) |
| warn("creating index", sql) |
| cursor.execute(sql) |
| |
| |
| ## Column Definition |
| |
| def getColumnDef(self,column_name): |
| try: |
| return self.__col_def_hash[column_name] |
| except KeyError: |
| try: |
| return self.__vcol_def_hash[column_name] |
| except KeyError: |
| raise eNoSuchColumn, "no column (%s) on table %s" % (column_name,self.__table_name) |
| |
| def getColumnList(self): |
| return self.__column_list + self.__vcolumn_list |
| def getAppColumnList(self): return self.__column_list |
| |
| def databaseSizeForData_ColumnName_(self,data,col_name): |
| try: |
| col_def = self.__col_def_hash[col_name] |
| except KeyError: |
| try: |
| col_def = self.__vcol_def_hash[col_name] |
| except KeyError: |
| raise eNoSuchColumn, "no column (%s) on table %s" % (col_name,self.__table_name) |
| |
| c_name,c_type,c_options = col_def |
| |
| if c_type == kBigString: |
| if c_options.get("compress_ok",0) and self.db.compression_enabled: |
| z_size = len(zlib.compress(data,9)) |
| r_size = len(data) |
| if z_size < r_size: |
| return z_size |
| else: |
| return r_size |
| else: |
| return len(data) |
| else: |
| # really simplistic database size computation: |
| try: |
| a = data[0] |
| return len(data) |
| except: |
| return 4 |
| |
| |
| def columnType(self, col_name): |
| try: |
| col_def = self.__col_def_hash[col_name] |
| except KeyError: |
| try: |
| col_def = self.__vcol_def_hash[col_name] |
| except KeyError: |
| raise eNoSuchColumn, "no column (%s) on table %s" % (col_name,self.__table_name) |
| |
| c_name,c_type,c_options = col_def |
| return c_type |
| |
| def convertDataForColumn(self,data,col_name): |
| try: |
| col_def = self.__col_def_hash[col_name] |
| except KeyError: |
| try: |
| col_def = self.__vcol_def_hash[col_name] |
| except KeyError: |
| raise eNoSuchColumn, "no column (%s) on table %s" % (col_name,self.__table_name) |
| |
| c_name,c_type,c_options = col_def |
| |
| if c_type == kIncInteger: |
| raise eInvalidData, "invalid operation for column (%s:%s) on table (%s)" % (col_name,c_type,self.__table_name) |
| |
| if c_type == kInteger: |
| try: |
| if data is None: data = 0 |
| else: return long(data) |
| except (ValueError,TypeError): |
| raise eInvalidData, "invalid data (%s) for col (%s:%s) on table (%s)" % (repr(data),col_name,c_type,self.__table_name) |
| elif c_type == kReal: |
| try: |
| if data is None: data = 0.0 |
| else: return float(data) |
| except (ValueError,TypeError): |
| raise eInvalidData, "invalid data (%s) for col (%s:%s) on table (%s)" % (repr(data), col_name,c_type,self.__table_name) |
| |
| else: |
| if type(data) == type(long(0)): |
| return "%d" % data |
| else: |
| return str(data) |
| |
| def getPrimaryKeyList(self): |
| return self.__primary_key_list |
| |
| def hasValueColumn(self): |
| return self.__has_value_column |
| |
| def hasColumn(self,name): |
| return self.__col_def_hash.has_key(name) |
| def hasVColumn(self,name): |
| return self.__vcol_def_hash.has_key(name) |
| |
| |
| def _defineRows(self): |
| raise "can't instantiate base odb.Table type, make a subclass and override _defineRows()" |
| |
| def __lockColumnsAndInit(self): |
| # add a 'odb_value column' before we lockdown the table def |
| if self.__has_value_column: |
| self.d_addColumn("odb_value",kBigText,default='') |
| |
| self.__columns_locked = 1 |
| # walk column list and make lookup hashes, primary_key_list, etc.. |
| |
| primary_key_list = [] |
| col_def_hash = {} |
| for a_col in self.__column_list: |
| name,type,options = a_col |
| col_def_hash[name] = a_col |
| if options.has_key('primarykey'): |
| primary_key_list.append(name) |
| |
| self.__col_def_hash = col_def_hash |
| self.__primary_key_list = primary_key_list |
| |
| # setup the value columns! |
| |
| if (not self.__has_value_column) and (len(self.__vcolumn_list) > 0): |
| raise "can't define vcolumns on table without ValueColumn, call d_addValueColumn() in your _defineRows()" |
| |
| vcol_def_hash = {} |
| for a_col in self.__vcolumn_list: |
| name,type,size_data,options = a_col |
| vcol_def_hash[name] = a_col |
| |
| self.__vcol_def_hash = vcol_def_hash |
| |
| |
| def __checkColumnLock(self): |
| if self.__columns_locked: |
| raise "can't change column definitions outside of subclass' _defineRows() method!" |
| |
| # table definition methods, these are only available while inside the |
| # subclass's _defineRows method |
| # |
| # Ex: |
| # |
| # import odb |
| # class MyTable(odb.Table): |
| # def _defineRows(self): |
| # self.d_addColumn("id",kInteger,primarykey = 1,autoincrement = 1) |
| # self.d_addColumn("name",kVarString,120) |
| # self.d_addColumn("type",kInteger, |
| # enum_values = { 0 : "alive", 1 : "dead" } |
| |
| def d_addColumn(self,col_name,ctype,size=None,primarykey = 0, |
| notnull = 0,indexed=0, |
| default=None,unique=0,autoincrement=0,safeupdate=0, |
| enum_values = None, |
| no_export = 0, |
| relations=None,compress_ok=0,int_date=0): |
| |
| self.__checkColumnLock() |
| |
| options = {} |
| options['default'] = default |
| if primarykey: |
| options['primarykey'] = primarykey |
| if unique: |
| options['unique'] = unique |
| if indexed: |
| options['indexed'] = indexed |
| self.addIndex((col_name,)) |
| if safeupdate: |
| options['safeupdate'] = safeupdate |
| if autoincrement: |
| options['autoincrement'] = autoincrement |
| if notnull: |
| options['notnull'] = notnull |
| if size: |
| options['size'] = size |
| if no_export: |
| options['no_export'] = no_export |
| if int_date: |
| if ctype != kInteger: |
| raise eInvalidData, "can't flag columns int_date unless they are kInteger" |
| else: |
| options['int_date'] = int_date |
| |
| if enum_values: |
| options['enum_values'] = enum_values |
| inv_enum_values = {} |
| for k,v in enum_values.items(): |
| if inv_enum_values.has_key(v): |
| raise eInvalidData, "enum_values paramater must be a 1 to 1 mapping for Table(%s)" % self.__table_name |
| else: |
| inv_enum_values[v] = k |
| options['inv_enum_values'] = inv_enum_values |
| if relations: |
| options['relations'] = relations |
| for a_relation in relations: |
| table, foreign_column_name = a_relation |
| if self.__relations_by_table.has_key(table): |
| raise eInvalidData, "multiple relations for the same foreign table are not yet supported" |
| self.__relations_by_table[table] = (col_name,foreign_column_name) |
| if compress_ok: |
| if ctype == kBigString: |
| options['compress_ok'] = 1 |
| else: |
| raise eInvalidData, "only kBigString fields can be compress_ok=1" |
| |
| self.__column_list.append( (col_name,ctype,options) ) |
| |
| def d_addValueColumn(self): |
| self.__checkColumnLock() |
| self.__has_value_column = 1 |
| |
| def d_addVColumn(self,col_name,type,size=None,default=None): |
| self.__checkColumnLock() |
| |
| if (not self.__has_value_column): |
| raise "can't define VColumns on table without ValueColumn, call d_addValueColumn() first" |
| |
| options = {} |
| if default: |
| options['default'] = default |
| if size: |
| options['size'] = size |
| |
| self.__vcolumn_list.append( (col_name,type,options) ) |
| |
| ##################### |
| # _checkColMatchSpec(col_match_spec,should_match_unique_row = 0) |
| # |
| # raise an error if the col_match_spec contains invalid columns, or |
| # (in the case of should_match_unique_row) if it does not fully specify |
| # a unique row. |
| # |
| # NOTE: we don't currently support where clauses with value column fields! |
| # |
| |
| def _fixColMatchSpec(self,col_match_spec, should_match_unique_row = 0): |
| if type(col_match_spec) == type([]): |
| if type(col_match_spec[0]) != type((0,)): |
| raise eInvalidMatchSpec, "invalid types in match spec, use [(,)..] or (,)" |
| elif type(col_match_spec) == type((0,)): |
| col_match_spec = [ col_match_spec ] |
| elif type(col_match_spec) == type(None): |
| if should_match_unique_row: |
| raise eNonUniqueMatchSpec, "can't use a non-unique match spec (%s) here" % col_match_spec |
| else: |
| return None |
| else: |
| raise eInvalidMatchSpec, "invalid types in match spec, use [(,)..] or (,)" |
| |
| if should_match_unique_row: |
| unique_column_lists = [] |
| |
| # first the primary key list |
| my_primary_key_list = [] |
| for a_key in self.__primary_key_list: |
| my_primary_key_list.append(a_key) |
| |
| # then other unique keys |
| for a_col in self.__column_list: |
| col_name,a_type,options = a_col |
| if options.has_key('unique'): |
| unique_column_lists.append( (col_name, [col_name]) ) |
| |
| unique_column_lists.append( ('primary_key', my_primary_key_list) ) |
| |
| |
| new_col_match_spec = [] |
| for a_col in col_match_spec: |
| name,val = a_col |
| # newname = string.lower(name) |
| # what is this doing?? - jeske |
| newname = name |
| if not self.__col_def_hash.has_key(newname): |
| raise eNoSuchColumn, "no such column in match spec: '%s'" % newname |
| |
| new_col_match_spec.append( (newname,val) ) |
| |
| if should_match_unique_row: |
| for name,a_list in unique_column_lists: |
| try: |
| a_list.remove(newname) |
| except ValueError: |
| # it's okay if they specify too many columns! |
| pass |
| |
| if should_match_unique_row: |
| for name,a_list in unique_column_lists: |
| if len(a_list) == 0: |
| # we matched at least one unique colum spec! |
| # log("using unique column (%s) for query %s" % (name,col_match_spec)) |
| return new_col_match_spec |
| |
| raise eNonUniqueMatchSpec, "can't use a non-unique match spec (%s) here" % col_match_spec |
| |
| return new_col_match_spec |
| |
| def __buildWhereClause (self, col_match_spec,other_clauses = None): |
| sql_where_list = [] |
| |
| if not col_match_spec is None: |
| for m_col in col_match_spec: |
| m_col_name,m_col_val = m_col |
| c_name,c_type,c_options = self.__col_def_hash[m_col_name] |
| if c_type in (kIncInteger, kInteger): |
| try: |
| m_col_val_long = long(m_col_val) |
| except ValueError: |
| raise ValueError, "invalid literal for long(%s) in table %s" % (repr(m_col_val),self.__table_name) |
| |
| sql_where_list.append("%s = %d" % (c_name, m_col_val_long)) |
| elif c_type == kReal: |
| try: |
| m_col_val_float = float(m_col_val) |
| except ValueError: |
| raise ValueError, "invalid literal for float(%s) is table %s" % (repr(m_col_val), self.__table_name) |
| sql_where_list.append("%s = %s" % (c_name, m_col_val_float)) |
| else: |
| sql_where_list.append("%s = '%s'" % (c_name, self.db.escape(m_col_val))) |
| |
| if other_clauses is None: |
| pass |
| elif type(other_clauses) == type(""): |
| sql_where_list = sql_where_list + [other_clauses] |
| elif type(other_clauses) == type([]): |
| sql_where_list = sql_where_list + other_clauses |
| else: |
| raise eInvalidData, "unknown type of extra where clause: %s" % repr(other_clauses) |
| |
| return sql_where_list |
| |
| def __fetchRows(self,col_match_spec,cursor = None, where = None, order_by = None, limit_to = None, |
| skip_to = None, join = None): |
| if cursor is None: |
| cursor = self.db.defaultCursor() |
| |
| # build column list |
| sql_columns = [] |
| for name,t,options in self.__column_list: |
| sql_columns.append(name) |
| |
| # build join information |
| |
| joined_cols = [] |
| joined_cols_hash = {} |
| join_clauses = [] |
| if not join is None: |
| for a_table,retrieve_foreign_cols in join: |
| try: |
| my_col,foreign_col = self.__relations_by_table[a_table] |
| for a_col in retrieve_foreign_cols: |
| full_col_name = "%s.%s" % (my_col,a_col) |
| joined_cols_hash[full_col_name] = 1 |
| joined_cols.append(full_col_name) |
| sql_columns.append( full_col_name ) |
| |
| join_clauses.append(" left join %s as %s on %s=%s " % (a_table,my_col,my_col,foreign_col)) |
| |
| except KeyError: |
| eInvalidJoinSpec, "can't find table %s in defined relations for %s" % (a_table,self.__table_name) |
| |
| # start buildling SQL |
| sql = "select %s from %s" % (string.join(sql_columns,","), |
| self.__table_name) |
| |
| # add join clause |
| if join_clauses: |
| sql = sql + string.join(join_clauses," ") |
| |
| # add where clause elements |
| sql_where_list = self.__buildWhereClause (col_match_spec,where) |
| if sql_where_list: |
| sql = sql + " where %s" % (string.join(sql_where_list," and ")) |
| |
| # add order by clause |
| if order_by: |
| sql = sql + " order by %s " % string.join(order_by,",") |
| |
| # add limit |
| if not limit_to is None: |
| if not skip_to is None: |
| # log("limit,skip = %s,%s" % (limit_to,skip_to)) |
| if self.db.db.__module__ == "sqlite.main": |
| sql = sql + " limit %s offset %s " % (limit_to,skip_to) |
| else: |
| sql = sql + " limit %s, %s" % (skip_to,limit_to) |
| else: |
| sql = sql + " limit %s" % limit_to |
| else: |
| if not skip_to is None: |
| raise eInvalidData, "can't specify skip_to without limit_to in MySQL" |
| |
| dlog(DEV_SELECT,sql) |
| cursor.execute(sql) |
| |
| # create defaultRowListClass instance... |
| return_rows = self.__defaultRowListClass() |
| |
| # should do fetchmany! |
| all_rows = cursor.fetchall() |
| for a_row in all_rows: |
| data_dict = {} |
| |
| col_num = 0 |
| |
| # for a_col in cursor.description: |
| # (name,type_code,display_size,internal_size,precision,scale,null_ok) = a_col |
| for name in sql_columns: |
| if self.__col_def_hash.has_key(name) or joined_cols_hash.has_key(name): |
| # only include declared columns! |
| if self.__col_def_hash.has_key(name): |
| c_name,c_type,c_options = self.__col_def_hash[name] |
| if c_type == kBigString and c_options.get("compress_ok",0) and a_row[col_num]: |
| try: |
| a_col_data = zlib.decompress(a_row[col_num]) |
| except zlib.error: |
| a_col_data = a_row[col_num] |
| |
| data_dict[name] = a_col_data |
| elif c_type == kInteger or c_type == kIncInteger: |
| value = a_row[col_num] |
| if not value is None: |
| data_dict[name] = int(value) |
| else: |
| data_dict[name] = None |
| else: |
| data_dict[name] = a_row[col_num] |
| |
| else: |
| data_dict[name] = a_row[col_num] |
| |
| col_num = col_num + 1 |
| |
| newrowobj = self.__defaultRowClass(self,data_dict,joined_cols = joined_cols) |
| return_rows.append(newrowobj) |
| |
| |
| |
| return return_rows |
| |
| def __deleteRow(self,a_row,cursor = None): |
| if cursor is None: |
| cursor = self.db.defaultCursor() |
| |
| # build the where clause! |
| match_spec = a_row.getPKMatchSpec() |
| sql_where_list = self.__buildWhereClause (match_spec) |
| |
| sql = "delete from %s where %s" % (self.__table_name, |
| string.join(sql_where_list," and ")) |
| dlog(DEV_UPDATE,sql) |
| cursor.execute(sql) |
| |
| |
| def __updateRowList(self,a_row_list,cursor = None): |
| if cursor is None: |
| cursor = self.db.defaultCursor() |
| |
| for a_row in a_row_list: |
| update_list = a_row.changedList() |
| |
| # build the set list! |
| sql_set_list = [] |
| for a_change in update_list: |
| col_name,col_val,col_inc_val = a_change |
| c_name,c_type,c_options = self.__col_def_hash[col_name] |
| |
| if c_type != kIncInteger and col_val is None: |
| sql_set_list.append("%s = NULL" % c_name) |
| elif c_type == kIncInteger and col_inc_val is None: |
| sql_set_list.append("%s = 0" % c_name) |
| else: |
| if c_type == kInteger: |
| sql_set_list.append("%s = %d" % (c_name, long(col_val))) |
| elif c_type == kIncInteger: |
| sql_set_list.append("%s = %s + %d" % (c_name,c_name,long(col_inc_val))) |
| elif c_type == kBigString and c_options.get("compress_ok",0) and self.db.compression_enabled: |
| compressed_data = zlib.compress(col_val,9) |
| if len(compressed_data) < len(col_val): |
| sql_set_list.append("%s = '%s'" % (c_name, self.db.escape(compressed_data))) |
| else: |
| sql_set_list.append("%s = '%s'" % (c_name, self.db.escape(col_val))) |
| elif c_type == kReal: |
| sql_set_list.append("%s = %s" % (c_name,float(col_val))) |
| |
| else: |
| sql_set_list.append("%s = '%s'" % (c_name, self.db.escape(col_val))) |
| |
| # build the where clause! |
| match_spec = a_row.getPKMatchSpec() |
| sql_where_list = self.__buildWhereClause (match_spec) |
| |
| if sql_set_list: |
| sql = "update %s set %s where %s" % (self.__table_name, |
| string.join(sql_set_list,","), |
| string.join(sql_where_list," and ")) |
| |
| dlog(DEV_UPDATE,sql) |
| try: |
| cursor.execute(sql) |
| except Exception, reason: |
| if string.find(str(reason), "Duplicate entry") != -1: |
| raise eDuplicateKey, reason |
| raise Exception, reason |
| a_row.markClean() |
| |
| def __insertRow(self,a_row_obj,cursor = None,replace=0): |
| if cursor is None: |
| cursor = self.db.defaultCursor() |
| |
| sql_col_list = [] |
| sql_data_list = [] |
| auto_increment_column_name = None |
| |
| for a_col in self.__column_list: |
| name,type,options = a_col |
| |
| try: |
| data = a_row_obj[name] |
| |
| sql_col_list.append(name) |
| if data is None: |
| sql_data_list.append("NULL") |
| else: |
| if type == kInteger or type == kIncInteger: |
| sql_data_list.append("%d" % data) |
| elif type == kBigString and options.get("compress_ok",0) and self.db.compression_enabled: |
| compressed_data = zlib.compress(data,9) |
| if len(compressed_data) < len(data): |
| sql_data_list.append("'%s'" % self.db.escape(compressed_data)) |
| else: |
| sql_data_list.append("'%s'" % self.db.escape(data)) |
| elif type == kReal: |
| sql_data_list.append("%s" % data) |
| else: |
| sql_data_list.append("'%s'" % self.db.escape(data)) |
| |
| except KeyError: |
| if options.has_key("autoincrement"): |
| if auto_increment_column_name: |
| raise eInternalError, "two autoincrement columns (%s,%s) in table (%s)" % (auto_increment_column_name, name,self.__table_name) |
| else: |
| auto_increment_column_name = name |
| |
| if replace: |
| sql = "replace into %s (%s) values (%s)" % (self.__table_name, |
| string.join(sql_col_list,","), |
| string.join(sql_data_list,",")) |
| else: |
| sql = "insert into %s (%s) values (%s)" % (self.__table_name, |
| string.join(sql_col_list,","), |
| string.join(sql_data_list,",")) |
| |
| dlog(DEV_UPDATE,sql) |
| try: |
| cursor.execute(sql) |
| except Exception, reason: |
| # sys.stderr.write("errror in statement: " + sql + "\n") |
| log("error in statement: " + sql + "\n") |
| if string.find(str(reason), "Duplicate entry") != -1: |
| raise eDuplicateKey, reason |
| raise Exception, reason |
| |
| if auto_increment_column_name: |
| if cursor.__module__ == "sqlite.main": |
| a_row_obj[auto_increment_column_name] = cursor.lastrowid |
| elif cursor.__module__ == "MySQLdb.cursors": |
| a_row_obj[auto_increment_column_name] = cursor.insert_id() |
| else: |
| # fallback to acting like mysql |
| a_row_obj[auto_increment_column_name] = cursor.insert_id() |
| |
| # ---------------------------------------------------- |
| # Helper methods for Rows... |
| # ---------------------------------------------------- |
| |
| |
| |
| ##################### |
| # r_deleteRow(a_row_obj,cursor = None) |
| # |
| # normally this is called from within the Row "delete()" method |
| # but you can call it yourself if you want |
| # |
| |
| def r_deleteRow(self,a_row_obj, cursor = None): |
| curs = cursor |
| self.__deleteRow(a_row_obj, cursor = curs) |
| |
| |
| ##################### |
| # r_updateRow(a_row_obj,cursor = None) |
| # |
| # normally this is called from within the Row "save()" method |
| # but you can call it yourself if you want |
| # |
| |
| def r_updateRow(self,a_row_obj, cursor = None): |
| curs = cursor |
| self.__updateRowList([a_row_obj], cursor = curs) |
| |
| ##################### |
| # InsertRow(a_row_obj,cursor = None) |
| # |
| # normally this is called from within the Row "save()" method |
| # but you can call it yourself if you want |
| # |
| |
| def r_insertRow(self,a_row_obj, cursor = None,replace=0): |
| curs = cursor |
| self.__insertRow(a_row_obj, cursor = curs,replace=replace) |
| |
| |
| # ---------------------------------------------------- |
| # Public Methods |
| # ---------------------------------------------------- |
| |
| |
| |
| ##################### |
| # deleteRow(col_match_spec) |
| # |
| # The col_match_spec paramaters must include all primary key columns. |
| # |
| # Ex: |
| # a_row = tbl.fetchRow( ("order_id", 1) ) |
| # a_row = tbl.fetchRow( [ ("order_id", 1), ("enterTime", now) ] ) |
| |
| |
| def deleteRow(self,col_match_spec, where=None): |
| n_match_spec = self._fixColMatchSpec(col_match_spec) |
| cursor = self.db.defaultCursor() |
| |
| # build sql where clause elements |
| sql_where_list = self.__buildWhereClause (n_match_spec,where) |
| if not sql_where_list: |
| return |
| |
| sql = "delete from %s where %s" % (self.__table_name, string.join(sql_where_list," and ")) |
| |
| dlog(DEV_UPDATE,sql) |
| cursor.execute(sql) |
| |
| ##################### |
| # fetchRow(col_match_spec) |
| # |
| # The col_match_spec paramaters must include all primary key columns. |
| # |
| # Ex: |
| # a_row = tbl.fetchRow( ("order_id", 1) ) |
| # a_row = tbl.fetchRow( [ ("order_id", 1), ("enterTime", now) ] ) |
| |
| |
| def fetchRow(self, col_match_spec, cursor = None): |
| n_match_spec = self._fixColMatchSpec(col_match_spec, should_match_unique_row = 1) |
| |
| rows = self.__fetchRows(n_match_spec, cursor = cursor) |
| if len(rows) == 0: |
| raise eNoMatchingRows, "no row matches %s" % repr(n_match_spec) |
| |
| if len(rows) > 1: |
| raise eInternalError, "unique where clause shouldn't return > 1 row" |
| |
| return rows[0] |
| |
| |
| ##################### |
| # fetchRows(col_match_spec) |
| # |
| # Ex: |
| # a_row_list = tbl.fetchRows( ("order_id", 1) ) |
| # a_row_list = tbl.fetchRows( [ ("order_id", 1), ("enterTime", now) ] ) |
| |
| |
| def fetchRows(self, col_match_spec = None, cursor = None, |
| where = None, order_by = None, limit_to = None, |
| skip_to = None, join = None): |
| n_match_spec = self._fixColMatchSpec(col_match_spec) |
| |
| return self.__fetchRows(n_match_spec, |
| cursor = cursor, |
| where = where, |
| order_by = order_by, |
| limit_to = limit_to, |
| skip_to = skip_to, |
| join = join) |
| |
| def fetchRowCount (self, col_match_spec = None, |
| cursor = None, where = None): |
| n_match_spec = self._fixColMatchSpec(col_match_spec) |
| sql_where_list = self.__buildWhereClause (n_match_spec,where) |
| sql = "select count(*) from %s" % self.__table_name |
| if sql_where_list: |
| sql = "%s where %s" % (sql,string.join(sql_where_list," and ")) |
| if cursor is None: |
| cursor = self.db.defaultCursor() |
| dlog(DEV_SELECT,sql) |
| cursor.execute(sql) |
| try: |
| count, = cursor.fetchone() |
| except TypeError: |
| count = 0 |
| return count |
| |
| |
| ##################### |
| # fetchAllRows() |
| # |
| # Ex: |
| # a_row_list = tbl.fetchRows( ("order_id", 1) ) |
| # a_row_list = tbl.fetchRows( [ ("order_id", 1), ("enterTime", now) ] ) |
| |
| def fetchAllRows(self): |
| try: |
| return self.__fetchRows([]) |
| except eNoMatchingRows: |
| # else return empty list... |
| return self.__defaultRowListClass() |
| |
| def newRow(self,replace=0): |
| row = self.__defaultRowClass(self,None,create=1,replace=replace) |
| for (cname, ctype, opts) in self.__column_list: |
| if opts['default'] is not None and ctype is not kIncInteger: |
| row[cname] = opts['default'] |
| return row |
| |
| class Row: |
| __instance_data_locked = 0 |
| def subclassinit(self): |
| pass |
| def __init__(self,_table,data_dict,create=0,joined_cols = None,replace=0): |
| |
| self._inside_getattr = 0 # stop recursive __getattr__ |
| self._table = _table |
| self._should_insert = create or replace |
| self._should_replace = replace |
| self._rowInactive = None |
| self._joinedRows = [] |
| |
| self.__pk_match_spec = None |
| self.__vcoldata = {} |
| self.__inc_coldata = {} |
| |
| self.__joined_cols_dict = {} |
| for a_col in joined_cols or []: |
| self.__joined_cols_dict[a_col] = 1 |
| |
| if create: |
| self.__coldata = {} |
| else: |
| if type(data_dict) != type({}): |
| raise eInternalError, "rowdict instantiate with bad data_dict" |
| self.__coldata = data_dict |
| self.__unpackVColumn() |
| |
| self.markClean() |
| |
| self.subclassinit() |
| self.__instance_data_locked = 1 |
| |
| def joinRowData(self,another_row): |
| self._joinedRows.append(another_row) |
| |
| def getPKMatchSpec(self): |
| return self.__pk_match_spec |
| |
| def markClean(self): |
| self.__vcolchanged = 0 |
| self.__colchanged_dict = {} |
| |
| for key in self.__inc_coldata.keys(): |
| self.__coldata[key] = self.__coldata.get(key, 0) + self.__inc_coldata[key] |
| |
| self.__inc_coldata = {} |
| |
| if not self._should_insert: |
| # rebuild primary column match spec |
| new_match_spec = [] |
| for col_name in self._table.getPrimaryKeyList(): |
| try: |
| rdata = self[col_name] |
| except KeyError: |
| raise eInternalError, "must have primary key data filled in to save %s:Row(col:%s)" % (self._table.getTableName(),col_name) |
| |
| new_match_spec.append( (col_name, rdata) ) |
| self.__pk_match_spec = new_match_spec |
| |
| def __unpackVColumn(self): |
| if self._table.hasValueColumn(): |
| pass |
| |
| def __packVColumn(self): |
| if self._table.hasValueColumn(): |
| pass |
| |
| ## ----- utility stuff ---------------------------------- |
| |
| def __del__(self): |
| # check for unsaved changes |
| changed_list = self.changedList() |
| if len(changed_list): |
| info = "unsaved Row for table (%s) lost, call discard() to avoid this error. Lost changes: %s\n" % (self._table.getTableName(), repr(changed_list)[:256]) |
| if 0: |
| raise eUnsavedObjectLost, info |
| else: |
| sys.stderr.write(info) |
| |
| |
| def __repr__(self): |
| return "Row from (%s): %s" % (self._table.getTableName(),repr(self.__coldata) + repr(self.__vcoldata)) |
| |
| ## ---- class emulation -------------------------------- |
| |
| def __getattr__(self,key): |
| if self._inside_getattr: |
| raise AttributeError, "recursively called __getattr__ (%s,%s)" % (key,self._table.getTableName()) |
| try: |
| self._inside_getattr = 1 |
| try: |
| return self[key] |
| except KeyError: |
| if self._table.hasColumn(key) or self._table.hasVColumn(key): |
| return None |
| else: |
| raise AttributeError, "unknown field '%s' in Row(%s)" % (key,self._table.getTableName()) |
| finally: |
| self._inside_getattr = 0 |
| |
| def __setattr__(self,key,val): |
| if not self.__instance_data_locked: |
| self.__dict__[key] = val |
| else: |
| my_dict = self.__dict__ |
| if my_dict.has_key(key): |
| my_dict[key] = val |
| else: |
| # try and put it into the rowdata |
| try: |
| self[key] = val |
| except KeyError, reason: |
| raise AttributeError, reason |
| |
| |
| ## ---- dict emulation --------------------------------- |
| |
| def __getitem__(self,key): |
| self.checkRowActive() |
| |
| try: |
| c_type = self._table.columnType(key) |
| except eNoSuchColumn, reason: |
| # Ugh, this sucks, we can't determine the type for a joined |
| # row, so we just default to kVarString and let the code below |
| # determine if this is a joined column or not |
| c_type = kVarString |
| |
| if c_type == kIncInteger: |
| c_data = self.__coldata.get(key, 0) |
| if c_data is None: c_data = 0 |
| i_data = self.__inc_coldata.get(key, 0) |
| if i_data is None: i_data = 0 |
| return c_data + i_data |
| |
| try: |
| return self.__coldata[key] |
| except KeyError: |
| try: |
| return self.__vcoldata[key] |
| except KeyError: |
| for a_joined_row in self._joinedRows: |
| try: |
| return a_joined_row[key] |
| except KeyError: |
| pass |
| |
| raise KeyError, "unknown column %s in %s" % (key,self) |
| |
| def __setitem__(self,key,data): |
| self.checkRowActive() |
| |
| try: |
| newdata = self._table.convertDataForColumn(data,key) |
| except eNoSuchColumn, reason: |
| raise KeyError, reason |
| |
| if self._table.hasColumn(key): |
| self.__coldata[key] = newdata |
| self.__colchanged_dict[key] = 1 |
| elif self._table.hasVColumn(key): |
| self.__vcoldata[key] = newdata |
| self.__vcolchanged = 1 |
| else: |
| for a_joined_row in self._joinedRows: |
| try: |
| a_joined_row[key] = data |
| return |
| except KeyError: |
| pass |
| raise KeyError, "unknown column name %s" % key |
| |
| def __delitem__(self,key,data): |
| self.checkRowActive() |
| |
| if self.table.hasVColumn(key): |
| del self.__vcoldata[key] |
| else: |
| for a_joined_row in self._joinedRows: |
| try: |
| del a_joined_row[key] |
| return |
| except KeyError: |
| pass |
| raise KeyError, "unknown column name %s" % key |
| |
| |
| def copyFrom(self,source): |
| for name,t,options in self._table.getColumnList(): |
| if not options.has_key("autoincrement"): |
| self[name] = source[name] |
| |
| |
| # make sure that .keys(), and .items() come out in a nice order! |
| |
| def keys(self): |
| self.checkRowActive() |
| |
| key_list = [] |
| for name,t,options in self._table.getColumnList(): |
| key_list.append(name) |
| for name in self.__joined_cols_dict.keys(): |
| key_list.append(name) |
| |
| for a_joined_row in self._joinedRows: |
| key_list = key_list + a_joined_row.keys() |
| |
| return key_list |
| |
| |
| def items(self): |
| self.checkRowActive() |
| |
| item_list = [] |
| for name,t,options in self._table.getColumnList(): |
| item_list.append( (name,self[name]) ) |
| for name in self.__joined_cols_dict.keys(): |
| item_list.append( (name,self[name]) ) |
| |
| for a_joined_row in self._joinedRows: |
| item_list = item_list + a_joined_row.items() |
| |
| |
| return item_list |
| |
| def values(elf): |
| self.checkRowActive() |
| |
| value_list = self.__coldata.values() + self.__vcoldata.values() |
| |
| for a_joined_row in self._joinedRows: |
| value_list = value_list + a_joined_row.values() |
| |
| return value_list |
| |
| |
| def __len__(self): |
| self.checkRowActive() |
| |
| my_len = len(self.__coldata) + len(self.__vcoldata) |
| |
| for a_joined_row in self._joinedRows: |
| my_len = my_len + len(a_joined_row) |
| |
| return my_len |
| |
| def has_key(self,key): |
| self.checkRowActive() |
| |
| if self.__coldata.has_key(key) or self.__vcoldata.has_key(key): |
| return 1 |
| else: |
| |
| for a_joined_row in self._joinedRows: |
| if a_joined_row.has_key(key): |
| return 1 |
| return 0 |
| |
| def get(self,key,default = None): |
| self.checkRowActive() |
| |
| |
| |
| if self.__coldata.has_key(key): |
| return self.__coldata[key] |
| elif self.__vcoldata.has_key(key): |
| return self.__vcoldata[key] |
| else: |
| for a_joined_row in self._joinedRows: |
| try: |
| return a_joined_row.get(key,default) |
| except eNoSuchColumn: |
| pass |
| |
| if self._table.hasColumn(key): |
| return default |
| |
| raise eNoSuchColumn, "no such column %s" % key |
| |
| def inc(self,key,count=1): |
| self.checkRowActive() |
| |
| if self._table.hasColumn(key): |
| try: |
| self.__inc_coldata[key] = self.__inc_coldata[key] + count |
| except KeyError: |
| self.__inc_coldata[key] = count |
| |
| self.__colchanged_dict[key] = 1 |
| else: |
| raise AttributeError, "unknown field '%s' in Row(%s)" % (key,self._table.getTableName()) |
| |
| |
| ## ---------------------------------- |
| ## real interface |
| |
| |
| def fillDefaults(self): |
| for field_def in self._table.fieldList(): |
| name,type,size,options = field_def |
| if options.has_key("default"): |
| self[name] = options["default"] |
| |
| ############### |
| # changedList() |
| # |
| # returns a list of tuples for the columns which have changed |
| # |
| # changedList() -> [ ('name', 'fred'), ('age', 20) ] |
| |
| def changedList(self): |
| if self.__vcolchanged: |
| self.__packVColumn() |
| |
| changed_list = [] |
| for a_col in self.__colchanged_dict.keys(): |
| changed_list.append( (a_col,self.get(a_col,None),self.__inc_coldata.get(a_col,None)) ) |
| |
| return changed_list |
| |
| def discard(self): |
| self.__coldata = None |
| self.__vcoldata = None |
| self.__colchanged_dict = {} |
| self.__vcolchanged = 0 |
| |
| def delete(self,cursor = None): |
| self.checkRowActive() |
| |
| |
| fromTable = self._table |
| curs = cursor |
| fromTable.r_deleteRow(self,cursor=curs) |
| self._rowInactive = "deleted" |
| |
| def save(self,cursor = None): |
| toTable = self._table |
| |
| self.checkRowActive() |
| |
| if self._should_insert: |
| toTable.r_insertRow(self,replace=self._should_replace) |
| self._should_insert = 0 |
| self._should_replace = 0 |
| self.markClean() # rebuild the primary key list |
| else: |
| curs = cursor |
| toTable.r_updateRow(self,cursor = curs) |
| |
| # the table will mark us clean! |
| # self.markClean() |
| |
| def checkRowActive(self): |
| if self._rowInactive: |
| raise eInvalidData, "row is inactive: %s" % self._rowInactive |
| |
| def databaseSizeForColumn(self,key): |
| return self._table.databaseSizeForData_ColumnName_(self[key],key) |
| |
| |
| if __name__ == "__main__": |
| print "run odb_test.py" |
