def hasAutoincrement(self, strTableName, strColName): strSql = "SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE UPPER(RDB$GENERATOR_NAME)=UPPER(?);" self.cursor.execute(strSql, [getSeqName(strTableName, strColName)[0:31]]) rows = self.cursor.fetchall() if rows: return True return False
def getTableColumns(self, strTable): """ Returns column in this format (strColumnName, strColType, nColSize, nColPrecision, bNotNull, strDefault, bAutoIncrement) """ strSql = """ SELECT pa.attnum, pa.attname, pt.typname, pa.atttypmod, pa.attnotnull, pa.atthasdef, pc.oid FROM pg_attribute pa, pg_type pt, pg_class pc WHERE pa.atttypid = pt.oid AND pa.attrelid = pc.oid AND pa.attisdropped = 'f' AND pc.relname = %s AND pc.relkind = 'r' ORDER BY attnum""" self.cursor.execute(strSql, [strTable]) rows = self.cursor.fetchall() specialCols = [ 'cmax', 'cmin', 'xmax', 'xmin', 'oid', 'ctid', 'tableoid' ] ret = [] for row in rows: attnum, name, type, attlen, attnotnull, atthasdef, clasoid = row if name not in specialCols: type = self._fixTypeNames(type) attlen, precision = self.decodeLength(type, attlen) default = None bAutoIncrement = False if atthasdef: default = self.getColumnDefault(clasoid, attnum) if default == "nextval('%s')" % (getSeqName( strTable, name)): default = '' bAutoIncrement = True ret.append((name, type, attlen, precision, attnotnull, default, bAutoIncrement)) return ret
def getTableColumnsStandard(self, strTable): """ Returns column in this format (nColIndex, strColumnName, strColType, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, bNotNull, strDefault, auto_increment) """ strSql = """ SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s ORDER BY ORDINAL_POSITION""" self.cursor.execute(strSql, [strTable]) rows = self.cursor.fetchall() ret = [] for row in rows: attnum, name, type, size, numsize, numprecradix, numprec, attnotnull, default = row type = self._fixTypeNames(type) if not size and numprecradix == 10: size = numsize if attnotnull.lower() == "yes": attnotnull = False else: attnotnull = True if default: # remove the '::text stuff default = default.replace('::text', '') bAutoIncrement = False if default == "nextval('%s')" % (getSeqName(strTable, name)): default = '' bAutoIncrement = True ret.append((name, type, size, numprec, attnotnull, default, bAutoIncrement)) return ret
def getTableColumns(self, strTable): """ Returns column in this format (strColumnName, strColType, nColSize, nColPrecision, bNotNull, strDefault, bAutoIncrement) """ strSql = """ SELECT pa.attnum, pa.attname, pt.typname, pa.atttypmod, pa.attnotnull, pa.atthasdef, pc.oid FROM pg_attribute pa, pg_type pt, pg_class pc WHERE pa.atttypid = pt.oid AND pa.attrelid = pc.oid AND pa.attisdropped = 'f' AND pc.relname = %s AND pc.relkind = 'r' ORDER BY attnum""" self.cursor.execute(strSql, [strTable]) rows = self.cursor.fetchall() specialCols = ['cmax', 'cmin', 'xmax', 'xmin', 'oid', 'ctid', 'tableoid'] ret = [] for row in rows: attnum, name, type, attlen, attnotnull, atthasdef, clasoid = row if name not in specialCols: type = self._fixTypeNames(type) attlen, precision = self.decodeLength(type, attlen) default = None bAutoIncrement = False if atthasdef: default = self.getColumnDefault(clasoid, attnum) if default == "nextval('%s')" % (getSeqName(strTable, name)): default = '' bAutoIncrement = True ret.append((name, type, attlen, precision, attnotnull, default, bAutoIncrement)) return ret