[SQL-CVS] r184 - in trunk/SQLObject/sqlobject: . maxdb
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: <sub...@co...> - 2004-08-18 03:06:01
|
Author: ianb Date: 2004-08-17 18:57:17 -0400 (Tue, 17 Aug 2004) New Revision: 184 Modified: trunk/SQLObject/sqlobject/col.py trunk/SQLObject/sqlobject/maxdb/maxdbconnection.py Log: Got rid of some bad tabs; fixed other minor whitespace issues. Modified: trunk/SQLObject/sqlobject/col.py =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- trunk/SQLObject/sqlobject/col.py 2004-08-17 22:41:14 UTC (rev 183) +++ trunk/SQLObject/sqlobject/col.py 2004-08-17 22:57:17 UTC (rev 184) @@ -206,7 +206,7 @@ return self._sqlType() =20 def _maxdbType(self): - return self._sqlType() + return self._sqlType() =20 def mysqlCreateSQL(self): return ' '.join([self.dbName, self._mysqlType()] + self._extraSQ= L()) @@ -228,7 +228,7 @@ return ' '.join([self.dbName, self._firebirdType()] + self._= extraSQL()) else: return ' '.join([self.dbName] + [self._firebirdType()[0]] + = self._extraSQL() + [self._firebirdType()[1]]) -=09 + def maxdbCreateSQL(self): return ' '.join([self.dbName, self._maxdbType()] + self._extraSQL= ()) =20 @@ -324,10 +324,10 @@ return self._sqlType() =20 def _maxdbType(self): - if not self.length: - return 'LONG ASCII' - else: - return self._sqlType() + if not self.length: + return 'LONG ASCII' + else: + return self._sqlType() =20 class StringCol(Col): baseClass =3D SOStringCol @@ -380,8 +380,8 @@ def _firebirdType(self): return 'INT' =20 - def _maxdbType(self): - return "BOOLEAN" + def _maxdbType(self): + return "BOOLEAN" =20 class BoolCol(Col): baseClass =3D SOBoolCol @@ -469,17 +469,17 @@ sql =3D ' '.join([sql, reference]) return sql =20 - def maxdbCreateSQL(self): - from main import findClass - other =3D findClass(self.foreignKey) - fidName =3D self.dbName - #I assume that foreign key name is identical to the id of the referen= ce table =09 - sql =3D ' '.join([fidName, self._maxdbType()]) - tName =3D other._table - idName =3D other._idName - sql=3Dsql + ',' + '\n'=20 - sql=3Dsql + 'FOREIGN KEY (%s) REFERENCES %s(%s)'%(fidName,tName,idNam= e) - return sql + def maxdbCreateSQL(self): + from main import findClass + other =3D findClass(self.foreignKey) + fidName =3D self.dbName + #I assume that foreign key name is identical to the id of the re= ference table + sql =3D ' '.join([fidName, self._maxdbType()]) + tName =3D other._table + idName =3D other._idName + sql=3Dsql + ',' + '\n'=20 + sql=3Dsql + 'FOREIGN KEY (%s) REFERENCES %s(%s)'%(fidName,tName,= idName) + return sql =20 class ForeignKey(KeyCol): =20 @@ -521,8 +521,8 @@ #NB. Return a tuple, not a string here return "VARCHAR(%i)" % (length), checkConstraint =20 - def _maxdbType(self): - raise "Enum type is not supported" + def _maxdbType(self): + raise "Enum type is not supported" =20 class EnumCol(Col): baseClass =3D SOEnumCol @@ -548,7 +548,7 @@ return 'TIMESTAMP' =20 def _maxdbType(self): - return 'TIMESTAMP' + return 'TIMESTAMP' =20 class DateTimeCol(Col): baseClass =3D SODateTimeCol Modified: trunk/SQLObject/sqlobject/maxdb/maxdbconnection.py =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- trunk/SQLObject/sqlobject/maxdb/maxdbconnection.py 2004-08-17 22:41:1= 4 UTC (rev 183) +++ trunk/SQLObject/sqlobject/maxdb/maxdbconnection.py 2004-08-17 22:57:1= 7 UTC (rev 184) @@ -19,261 +19,278 @@ =20 =20 class maxdbException(Exception): - def __init__(self, value): - self.value =3D value - def __str__(self): - return repr(self.value) -=09 + + def __init__(self, value): + self.value =3D value + + def __str__(self): + return repr(self.value) + =20 class LowerBoundOfSliceIsNotSupported(maxdbException): - def __init__(self, value): - maxdbException.__init__(self,'') -=09 + def __init__(self, value): + maxdbException.__init__(self, '') =20 class IncorrectIDStyleError(maxdbException) : - def __init__(self,value): - maxdbException.__init__(self,'This primary key name is not in the exp= ected style,please rename the column to %s or switch to another style'%(v= alue)) + def __init__(self,value): + maxdbException.__init__( + self, + 'This primary key name is not in the expected style, ' + 'please rename the column to %r or switch to another style' + % value) =20 class StyleMismatchError(maxdbException): - def __init__(self, value): - maxdbException.__init__(self,'The name %s is only permitted for primar= y key,change the column name or switch to another style'%value) + def __init__(self, value): + maxdbException.__init__( + self, + 'The name %r is only permitted for primary key, change the ' + 'column name or switch to another style' % value) =20 - class PrimaryKeyNotFounded(maxdbException): - def __init__(self, value): - maxdbException.__init__(self,"No primary key was defined on table %s"%= (value)) + def __init__(self, value): + maxdbException.__init__( + self, + "No primary key was defined on table %r" % value) =20 - =09 SAPDBMAX_ID_LENGTH=3D32 =20 - =09 + class MaxdbConnection(DBAPI): - =20 - supportTransactions =3D True - dbName =3D 'maxdb' - schemes =3D [dbName] + supportTransactions =3D True + dbName =3D 'maxdb' + schemes =3D [dbName] =20 - def __init__ (self, user, password, database, host =3D '',autoCommit=3D= 1,sqlmode=3D'internal',isolation=3DNone,timeout=3DNone, **kw): - global dbapi - if dbapi is None: - from sapdb import dbapi - self.autoCommit =3D autoCommit - self.user =3D user - self.password =3D password - self.database =3D database - self.host =3D host - self.sqlmode =3D sqlmode - self.isolation =3D isolation - self.timeout =3D timeout - =20 - DBAPI.__init__(self, **kw) - =20 - def connectionFromURI(cls, uri): - auth, password, host, path, args =3D cls._parseURI(uri) - path =3D path.replace('/', os.path.sep) - return cls(host, db=3Dpath, user=3Dauth, passwd=3Dpassword, **args) - connectionFromURI =3D classmethod(connectionFromURI) -=09 - =09 - def _getConfigParams(self,sqlmode,auto): - autocommit=3D'off' - if auto:autocommit=3D'on' - opt=3D{} - opt["autocommit"]=3Dautocommit - opt["sqlmode"]=3Dsqlmode - if self.isolation: - opt["isolation"]=3Dself.isolation - if self.timeout : - opt["timeout"]=3Dself.timeout - =09 - return opt - =09 - def _setAutoCommit(self, conn, auto): - conn.close() - conn.__init__(self.user, self.password, self.database, self.host,**sel= f._getConfigParams(self.sqlmode,auto)) - =09 - =20 -=09 - =20 - def createSequenceName(self,table): - """ sequence name are builded with the concatenation of the table name= with '_SEQ' word - we truncate the name of the sequence_name because - sapdb identifier cannot exceed 32 characters - so that the name of the sequence does not exceed 32 characters - """ - return '%s_SEQ'%(table[:SAPDBMAX_ID_LENGTH -4]) -=09 - def makeConnection(self): - conn =3D dbapi.Connection(self.user, self.password, self.database, se= lf.host,**self._getConfigParams(self.sqlmode,self.autoCommit) ) - return conn -=09 - def _queryInsertID(self, conn, soInstance, id, names, values): - table =3D soInstance._table - idName =3D soInstance._idName - c =3D conn.cursor() - if id is None: - c.execute('SELECT %s.NEXTVAL FROM DUAL' % (self.createSequenceName(ta= ble))) - id =3D c.fetchone()[0] - names =3D [idName] + names - values =3D [id] + values - q =3D self._insertSQL(table, names, values) - if self.debug: - self.printDebug(conn, q, 'QueryIns') - c.execute(q) - if self.debugOutput: - self.printDebug(conn, id, 'QueryIns', 'result') - return id -=09 - def sqlAddLimit(self,query,limit): - sql =3D query - sql =3D sql.replace("SELECT","SELECT ROWNO, ") - if sql.find('WHERE') !=3D -1: - sql =3D sql + ' AND ' + limit - else:=09 - sql =3D sql + 'WHERE ' + limit + def __init__ (self, user, password, database, + host=3D'', autoCommit=3D1, sqlmode=3D'internal', + isolation=3DNone, timeout=3DNone, **kw): + global dbapi + if dbapi is None: + from sapdb import dbapi + self.autoCommit =3D autoCommit + self.user =3D user + self.password =3D password + self.database =3D database + self.host =3D host + self.sqlmode =3D sqlmode + self.isolation =3D isolation + self.timeout =3D timeout =20 - return sql - =09 - def _queryAddLimitOffset(self, query, start, end): - if start: raise LowerBoundOfSliceIsNotSupported - limit =3D ' ROWNO <=3D %d ' % (end) - return self.sqlAddLimit(query,limit) + DBAPI.__init__(self, **kw) =20 - =20 - def createTable(self, soClass): - #we create the table in a transaction because the addition of the - #table and the sequence must be atomic=20 + def connectionFromURI(cls, uri): + auth, password, host, path, args =3D cls._parseURI(uri) + path =3D path.replace('/', os.path.sep) + return cls(host, db=3Dpath, user=3Dauth, passwd=3Dpassword, **ar= gs) + connectionFromURI =3D classmethod(connectionFromURI) =20 - #i tried to use the transaction class but i get a recursion limi= t error =20 - # t=3Dself.transaction() - # t.query('CREATE TABLE %s (\n%s\n)' % \ - # (soClass._table, self.createColumns(soClass))) - # =09 - # t.query("CREATE SEQUENCE %s" % self.createSequenceName(soClass._ta= ble)) - # t.commit() - =09 - #so use transaction when the problem will be solved - self.query('CREATE TABLE %s (\n%s\n)' % \ - (soClass._table, self.createColumns(soClass))) - self.query("CREATE SEQUENCE %s" % self.createSequenceName(soClass._tab= le)) + def _getConfigParams(self,sqlmode,auto): + autocommit=3D'off' + if auto: + autocommit=3D'on' + opt =3D {} + opt["autocommit"] =3D autocommit + opt["sqlmode"] =3D sqlmode + if self.isolation: + opt["isolation"]=3Dself.isolation + if self.timeout : + opt["timeout"]=3Dself.timeout + return opt + + def _setAutoCommit(self, conn, auto): + conn.close() + conn.__init__(self.user, self.password, self.database, + self.host, + **self._getConfigParams(self.sqlmode,auto)) =20 -=09 - def createColumn(self, soClass, col): - return col.maxdbCreateSQL() -=09 - def createIDColumn(self, soClass): - return '%s INT PRIMARY KEY' % soClass._idName -=09 - def dropTable(self, tableName,cascade=3DFalse): - #we drop the table in a transaction because the removal of the - #table and the sequence must be atomic=20 - #i tried to use the transaction class but i get a recursion limit erro= r=09 - # try: - # t=3Dself.transaction() - # t.query("DROP TABLE %s" % tableName) - # t.query("DROP SEQUENCE %s" % self.createSequenceName(tableName)) - # t.commit() - # except:t.rollback() - #so use transaction when the problem will be solved - self.query("DROP TABLE %s" % tableName) - self.query("DROP SEQUENCE %s" % self.createSequenceName(tableName)) -=09 - def joinSQLType(self, join): - return 'INT NOT NULL' -=09 - def tableExists(self, tableName): - for (table,) in self.queryAll("SELECT OBJECT_NAME FROM ALL_OBJECTS WHE= RE OBJECT_TYPE=3D'TABLE'"): - if table.lower() =3D=3D tableName.lower(): - return True - return False - =20 -=09 - def addColumn(self, tableName, column): - self.query('ALTER TABLE %s ADD %s' % - (tableName, - column.maxdbCreateSQL())) -=09 - def delColumn(self, tableName, column): - self.query('ALTER TABLE %s DROP COLUMN %s' % - (tableName, - column.dbName)) -=09 + def createSequenceName(self,table): + """ + sequence name are builded with the concatenation of the table + name with '_SEQ' word we truncate the name of the + sequence_name because sapdb identifier cannot exceed 32 + characters so that the name of the sequence does not exceed 32 + characters + """ + return '%s_SEQ'%(table[:SAPDBMAX_ID_LENGTH -4]) =20 + def makeConnection(self): + conn =3D dbapi.Connection( + self.user, self.password, self.database, self.host, + **self._getConfigParams(self.sqlmode,self.autoCommit)) + return conn =20 + def _queryInsertID(self, conn, soInstance, id, names, values): + table =3D soInstance._table + idName =3D soInstance._idName + c =3D conn.cursor() + if id is None: + c.execute('SELECT %s.NEXTVAL FROM DUAL' % (self.createSequen= ceName(table))) + id =3D c.fetchone()[0] + names =3D [idName] + names + values =3D [id] + values + q =3D self._insertSQL(table, names, values) + if self.debug: + self.printDebug(conn, q, 'QueryIns') + c.execute(q) + if self.debugOutput: + self.printDebug(conn, id, 'QueryIns', 'result') + return id =20 - GET_COLUMNS =3D (" SELECT COLUMN_NAME,NULLABLE,DATA_DEFAULT,DATA_TYPE= , DATA_LENGTH,DATA_SCALE FROM USER_TAB_COLUMNS WHERE TABLE_NAME=3DUPPER('= %s')") - GET_PK_AND_FK =3D (""" SELECT constraint_cols .column_name,constraints.= constraint_type , refname,reftablename FROM - user_cons_columns constraint_cols=20 - INNER JOIN user_constraints constraints ON constraint_cols.cons= traint_name=3Dconstraints.constraint_name=20 - LEFT OUTER JOIN show_foreign_key fk on constraint_cols.column_nam= e=3Dfk.columnname - WHERE constraints.table_name =3DUPPER('%s')""" - ) -=09 - def columnsFromSchema(self, tableName, soClass): - colData =3D self.queryAll(self.GET_COLUMNS - % tableName) - =09 - results =3D [] - keymap =3D {} - pkmap=3D{} - fkData =3D self.queryAll(self.GET_PK_AND_FK% tableName) - for col, cons_type, refcol, reftable in fkData: - col_name=3D col.lower() - pkmap[col_name]=3DFalse - if cons_type =3D=3D 'R': - keymap[col_name]=3Dreftable.lower() - =09 - elif cons_type =3D=3D 'P': - pkmap[col_name]=3DTrue - =09 - if len(pkmap) =3D=3D 0: raise PrimaryKeyNotFounded, tableName - =09 - for field, nullAllowed, default, data_type, data_len, data_scale in co= lData: - #id is defined as primary key --> ok - #We let sqlobject raise error if the 'id' is used for another column - field_name =3D field.lower()=20 - if field_name =3D=3D 'id' and pkmap[field_name]: - continue - =09 - colClass, kw =3D self.guessClass(data_type,data_len,data_scale) - kw['name'] =3D field_name - =09 - if nullAllowed =3D=3D 'Y' :=20 - nullAllowed=3DFalse - else: - nullAllowed=3DTrue - =09 - kw['notNone'] =3D nullAllowed - if default is not None: - kw['default'] =3D default - =09 - if keymap.has_key(field_name): - kw['foreignKey'] =3D keymap[field_name] - =09 - results.append(colClass(**kw)) - =20 - return results - =20 - _numericTypes=3D['INTEGER', 'INT','SMALLINT'] - _dateTypes=3D['DATE','TIME','TIMESTAMP'] -=09 - def guessClass(self, t, flength, fscale=3DNone): - """ - An internal method that tries to figure out what Col subclass - is appropriate given whatever introspective information is - available -- both very database-specific. - """ - if t in self._numericTypes: - return col.IntCol, {} - # The type returned by the sapdb library for LONG is SapDB_LongReader - #To get the data call the read member with desired size (default =3D-1= means get all) - =09 - elif t.find('LONG') !=3D -1: - return col.StringCol, {'length': flength, - 'varchar': False} - elif t in self._dateTypes: - return col.DateTimeCol, {} - elif t =3D=3D 'FIXED': - return CurrencyCol,{'size':flength, - 'precision':fscale} - else: - return col.Col, {} + def sqlAddLimit(self,query,limit): + sql =3D query + sql =3D sql.replace("SELECT","SELECT ROWNO, ") + if sql.find('WHERE') !=3D -1: + sql =3D sql + ' AND ' + limit + else: + sql =3D sql + 'WHERE ' + limit + return sql + + def _queryAddLimitOffset(self, query, start, end): + if start: + raise LowerBoundOfSliceIsNotSupported + limit =3D ' ROWNO <=3D %d ' % (end) + return self.sqlAddLimit(query,limit) + + =20 + def createTable(self, soClass): + #we create the table in a transaction because the addition of th= e + #table and the sequence must be atomic=20 + + #i tried to use the transaction class but i get a recursion limi= t error =20 + #t=3Dself.transaction() + # t.query('CREATE TABLE %s (\n%s\n)' % \ + # (soClass._table, self.createColumns(soClass))) + #=20 + # t.query("CREATE SEQUENCE %s" % self.createSequenceName(soClass= ._table)) + # t.commit() + #so use transaction when the problem will be solved + self.query('CREATE TABLE %s (\n%s\n)' % \ + (soClass._table, self.createColumns(soClass))) + self.query("CREATE SEQUENCE %s" + % self.createSequenceName(soClass._table)) +=20 + def createColumn(self, soClass, col): + return col.maxdbCreateSQL() + + def createIDColumn(self, soClass): + return '%s INT PRIMARY KEY' % soClass._idName + + def dropTable(self, tableName,cascade=3DFalse): + #we drop the table in a transaction because the removal of the + #table and the sequence must be atomic=20 + #i tried to use the transaction class but i get a recursion limi= t error + # try: + # t=3Dself.transaction() + # t.query("DROP TABLE %s" % tableName) + # t.query("DROP SEQUENCE %s" % self.createSequenceName(table= Name)) + # t.commit() + # except: + # t.rollback() + #so use transaction when the problem will be solved + self.query("DROP TABLE %s" % tableName) + self.query("DROP SEQUENCE %s" % self.createSequenceName(tableNam= e)) + + def joinSQLType(self, join): + return 'INT NOT NULL' + + def tableExists(self, tableName): + for (table,) in self.queryAll("SELECT OBJECT_NAME FROM ALL_OBJEC= TS WHERE OBJECT_TYPE=3D'TABLE'"): + if table.lower() =3D=3D tableName.lower(): + return True + return False + + def addColumn(self, tableName, column): + self.query('ALTER TABLE %s ADD %s' % + (tableName, + column.maxdbCreateSQL())) + + def delColumn(self, tableName, column): + self.query('ALTER TABLE %s DROP COLUMN %s' % + (tableName, + column.dbName)) + + GET_COLUMNS =3D """ + SELECT COLUMN_NAME, NULLABLE, DATA_DEFAULT, DATA_TYPE, + DATA_LENGTH, DATA_SCALE + FROM USER_TAB_COLUMNS WHERE TABLE_NAME=3DUPPER('%s')""" + + GET_PK_AND_FK =3D """ + SELECT constraint_cols.column_name, constraints.constraint_type, + refname,reftablename + FROM user_cons_columns constraint_cols=20 + INNER JOIN user_constraints constraints + ON constraint_cols.constraint_name =3D constraints.constraint_name + LEFT OUTER JOIN show_foreign_key fk + ON constraint_cols.column_name =3D fk.columnname + WHERE constraints.table_name =3DUPPER('%s')""" + + def columnsFromSchema(self, tableName, soClass): + colData =3D self.queryAll(self.GET_COLUMNS + % tableName) + + results =3D [] + keymap =3D {} + pkmap=3D{} + fkData =3D self.queryAll(self.GET_PK_AND_FK% tableName) + for col, cons_type, refcol, reftable in fkData: + col_name=3D col.lower() + pkmap[col_name]=3DFalse + if cons_type =3D=3D 'R': + keymap[col_name]=3Dreftable.lower() + =20 + elif cons_type =3D=3D 'P': + pkmap[col_name]=3DTrue + + if len(pkmap) =3D=3D 0: + raise PrimaryKeyNotFounded, tableName + + for (field, nullAllowed, default, data_type, data_len, + data_scale) in colData: + # id is defined as primary key --> ok + # We let sqlobject raise error if the 'id' is used for anoth= er column + field_name =3D field.lower()=20 + if field_name =3D=3D 'id' and pkmap[field_name]: + continue + =20 + colClass, kw =3D self.guessClass(data_type,data_len,data_sca= le) + kw['name'] =3D field_name + + if nullAllowed =3D=3D 'Y' :=20 + nullAllowed=3DFalse + else: + nullAllowed=3DTrue + + kw['notNone'] =3D nullAllowed + if default is not None: + kw['default'] =3D default + + if keymap.has_key(field_name): + kw['foreignKey'] =3D keymap[field_name] + + results.append(colClass(**kw)) + =20 + return results + =20 + _numericTypes=3D['INTEGER', 'INT','SMALLINT'] + _dateTypes=3D['DATE','TIME','TIMESTAMP'] + + def guessClass(self, t, flength, fscale=3DNone): + """ + An internal method that tries to figure out what Col subclass + is appropriate given whatever introspective information is + available -- both very database-specific. + """ + if t in self._numericTypes: + return col.IntCol, {} + # The type returned by the sapdb library for LONG is + # SapDB_LongReader To get the data call the read member with + # desired size (default =3D-1 means get all) + + elif t.find('LONG') !=3D -1: + return col.StringCol, {'length': flength, + 'varchar': False} + elif t in self._dateTypes: + return col.DateTimeCol, {} + elif t =3D=3D 'FIXED': + return CurrencyCol,{'size':flength, + 'precision':fscale} + else: + return col.Col, {} |