def _create_field(*args, **kwargs):
""" controls how fields are created when declared in the field
list with a simple string, list, tuple, or dictionary rather
than a field instance. By default, passes field declaration
to the Field constructor.
"""
return Field(*args, **kwargs)
def describeTable(self, table, schema=None):
cur = self.conn.cursor()
if self.verbose:
def execute(sql):
debug('SQL: %s', (sql, ))
cur.execute(sql)
else:
execute = cur.execute
sql = "SHOW TABLES LIKE '%s'" % table
execute(sql)
res = cur.fetchone()
if not res:
raise ValueError, "table %s not found" % table
sql = "SHOW COLUMNS FROM %s" % table
execute(sql)
res = cur.fetchall()
fields = {}
nullableFields = []
for row in res:
name, tipe, nullable, key, default, extra = row
if nullable:
nullableFields.append(name)
if (not nullable) and extra == 'auto_increment':
fields[name] = Sequence(name)
else:
fields[name] = Field(name)
sql = "SHOW INDEX FROM %s" % table
execute(sql)
res = cur.fetchall()
cur.close()
indices = {}
blacklist = set(nullableFields)
# columns we care about, and their index in the result set:
# Non_unique: 1
# Key_name: 2
# Column_name : 4
for row in res:
keyname = row[2]
colname = row[4]
notunique = row[1]
# if not a unique index, the whole index is tainted, don't use it
if notunique:
blacklist.add(keyname)
continue
if keyname in blacklist:
continue
# build dictionary of lists
indices.setdefault(keyname, [])
indices[keyname].append(colname)
unique = set(frozenset(x) for x in indices.values())
return fields, unique
def describeTable(self, table, schema=None):
if schema is not None:
raise ValueError, "db schemas not supported by sqlite driver"
fields = {}
unique = set()
nullable = []
c = self.conn.cursor()
if self.verbose:
def execute(sql):
debug('SQL: %s', (sql, ))
c.execute(sql)
else:
execute = c.execute
sql = "pragma table_info('%s')" % table
execute(sql)
res = c.fetchall()
if not res:
raise ValueError, "no such table: %s" % table
for row in res:
cid, name, type, notnull, dflt_value, pk = row
# we ignore the nullable bit for sequences, because
# apparently sqlite permits sequences to be defined as nullable
# (thanks Tim Golden)
if type == 'INTEGER' and int(pk): # and int(notnull):
# a sequence
fields[name] = Sequence(name)
else:
fields[name] = Field(name)
if not int(notnull):
nullable.append(name)
# get indexes
sql = "pragma index_list('%s')" % table
execute(sql)
res = c.fetchall()
for row in res:
seq, name, uneek = row
if uneek:
sql = "pragma index_info('%s')" % name
execute(sql)
subres = c.fetchall()
unset = frozenset(x[-1] for x in subres)
if not unset.intersection(nullable):
unique.add(unset)
c.close()
return fields, unique
def describeTable(self, table, schema=None):
# verify that the table exists
sql = """
SELECT t.tablename AS tname
FROM pg_catalog.pg_tables t
WHERE t.tablename=%s
AND t.schemaname=%s
UNION
SELECT v.viewname AS tname
FROM pg_catalog.pg_views v
WHERE v.viewname=%s
and v.schemaname=%s
"""
if schema is None:
schema = 'public'
cur = self.conn.cursor()
bind = (table, schema, table, schema)
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, bind)
for row in cur.fetchall():
# it exists, get on with it
break
else:
raise ValueError, "no such table or view: %s.%s" % (schema, table)
sql = """
SELECT a.attname, a.attnum
FROM pg_catalog.pg_attribute a,
pg_catalog.pg_namespace n,
pg_catalog.pg_class c
WHERE a.attrelid = %s::regclass
AND c.oid=a.attrelid
AND c.relnamespace=n.oid
AND n.nspname=%s
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
"""
fields = {}
cur = self.conn.cursor()
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, (table, schema))
for row in cur.fetchall():
if self.verbose:
debug("Found column %s" % list(row))
fields[row[1]] = Field(row[0])
sql = """
SELECT indkey
FROM pg_catalog.pg_index i,
pg_catalog.pg_class c,
pg_catalog.pg_namespace n,
pg_catalog.pg_attribute a
WHERE i.indrelid = %s::regclass
AND c.oid=i.indrelid
AND c.relnamespace=n.oid
AND n.nspname=%s
AND i.indisunique
AND a.attrelid=c.oid
AND a.attnum=indkey[0]
AND a.attnotnull
"""
unique = set()
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, (table, schema))
for row in cur.fetchall():
L = [int(i) for i in row[0].split(' ')]
if self.verbose:
debug("Found unique index on %s" % L)
if len(L) == 1:
fields[L[0]].unique = True
else:
unique.add(frozenset([fields[i].name for i in L]))
sql = """
SELECT c.relname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE relname like '%s_%%%%_seq'
AND c.relnamespace=n.oid
AND n.nspname=%%s
AND relkind = 'S'
""" % table
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, (schema, ))
for row in cur.fetchall():
maybecolname = row[0][len(table) + 1:-4]
for field in fields.values():
if field.name == maybecolname:
if self.verbose:
debug("Found sequence %s on %s" % (row[0], field.name))
field.sequence = row[0]
break
cur.close()
d = {}
for f in fields.values():
d[f.name] = f
return (d, unique)
def describeTable(self, table, schema=None):
schema = schema or 'dbo'
fields={}
unique=set()
nullable=[]
c=self.conn.cursor()
if self.verbose:
def execute(sql):
debug('SQL: %s', (sql,))
c.execute(sql)
else:
execute=c.execute
sql = """
select
column_name = col.COLUMN_NAME,
is_nullable = CASE LOWER (col.IS_NULLABLE) WHEN 'yes' THEN 1 ELSE 0 END,
is_identity = COLUMNPROPERTY (OBJECT_ID (col.TABLE_SCHEMA + '.' + col.TABLE_NAME), col.COLUMN_NAME, 'IsIdentity'),
is_primary_key = CASE WHEN ccu.COLUMN_NAME IS NULL THEN 0 ELSE 1 END
FROM
INFORMATION_SCHEMA.COLUMNS AS col
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tco ON
tco.TABLE_SCHEMA = col.TABLE_SCHEMA AND
tco.TABLE_NAME = col.TABLE_NAME AND
tco.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON
ccu.CONSTRAINT_CATALOG = tco.CONSTRAINT_CATALOG AND
ccu.CONSTRAINT_SCHEMA = tco.CONSTRAINT_SCHEMA AND
ccu.CONSTRAINT_NAME = tco.CONSTRAINT_NAME AND
ccu.COLUMN_NAME = col.COLUMN_NAME
WHERE
col.TABLE_SCHEMA = '%s' AND
col.TABLE_NAME = '%s'
""" % (schema, table)
execute (sql)
for name, is_nullable, is_identity, is_primary_key in c.fetchall ():
#
# We're only interested in a sequence if it is
# a NOT-NULL PK IDENTITY.
# ROWGUIDCOL columns probably do not count here,
# since their values have to be filled in by hand
# although typically via a DEFAULT of NewID ().
#
if is_identity and not is_nullable and is_primary_key:
fields[name] = Sequence (name)
else:
fields[name] = Field (name)
if is_nullable:
nullable.append (name)
#
# In theory this query should pull out unique constraints
# and unique indexes, but in practice it only pulls out
# the former.
#
constraint_sql = """
SELECT
tco.CONSTRAINT_CATALOG,
tco.CONSTRAINT_SCHEMA,
tco.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tco
WHERE
tco.TABLE_SCHEMA = '%s' AND
tco.TABLE_NAME = '%s'
AND
tco.CONSTRAINT_TYPE IN ('UNIQUE', 'PRIMARY KEY')
""" % (schema, table)
column_sql = """
SELECT
ccu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
WHERE
ccu.CONSTRAINT_CATALOG = '%s' AND
ccu.CONSTRAINT_SCHEMA = '%s' AND
ccu.CONSTRAINT_NAME = '%s'
"""
execute (constraint_sql)
for constraint_catalog, constraint_schema, constraint_name in c.fetchall ():
q2 = self.conn.cursor ()
q2.execute (column_sql % (constraint_catalog, constraint_schema, constraint_name))
unique.add (frozenset ([r[0] for r in q2.fetchall ()]))
#
# The adodbapi driver complains if the sp_helpindex returns with
# no indexes, so make sure that there is at least one before
# trying.
#
sql = """
SELECT * FROM sysindexes WHERE id = OBJECT_ID ('%s.%s') AND indid BETWEEN 1 AND 254
""" % (schema, table)
execute (sql)
rows = c.fetchall ()
print "rows=", rows
if rows: # c.fetchall ():
sql = "sp_helpindex '%s.%s'" % (schema, table)
execute (sql)
for index_name, index_description, index_keys in c.fetchall ():
#
# The description field from sp_helpindex contains an ill-defined
# set of descriptors, somewhere including the word "unique". The
# separators could be commas or spaces, or both.
#
descriptions = [d.strip () for d in index_description.lower ().replace (",", " ").split ()]
if "unique" in descriptions:
unique.add (frozenset ([k.strip () for k in index_keys.split (",")]))
return fields, unique
def describeTable(self, table, schema=None, sequence_mapper=None):
"""for the given table, returns a 2-tuple: a dict of Field objects
keyed by name, and list of multi-column unique constraints (sets of Fields)).
The Field instances should contain information about whether they are unique
or sequenced.
"""
if schema is None:
schema = 'PUBLIC'
cur = self.conn.cursor()
# Build dict of Field objects
sql = """
SELECT tc.column_name, nullable
FROM sys.all_tab_columns tc
WHERE tc.owner = :schema AND tc.table_name = :table_name
ORDER BY column_id
"""
cur.execute(sql, schema=schema, table_name=table)
fields = {}
for row in cur:
(column_name, nullable) = row
if nullable:
fields[column_name] = Field(column_name)
if not fields:
raise Exception("table %s not found" % table)
# Get uniqueness constraints
sql = """
SELECT concol.constraint_name, concol.column_name
FROM sys.all_constraints con
JOIN sys.all_cons_columns concol ON con.constraint_name = concol.constraint_name
WHERE con.owner = :schema AND con.table_name = :table_name
AND con.constraint_type in ('P', 'U')
ORDER BY constraint_name
"""
cur.execute(sql, schema=schema, table_name=table)
unique = set()
for (key, rows) in groupby(cur, itemgetter(0)):
columns = frozenset(r[1] for r in rows)
unique.add(columns)
if len(columns) == 1:
fields[list(columns)[0]].unique = True
# Get Sequence fields. Essentially this is a best guess as Oracle doesn't have an
# autosequence type as such, this functionality typically being implemented via a trigger
# that increments a user-specified sequence. For all numeric fields that are modified by
# a trigger, I check if the sequence exists whose name is given by the sequence_mapper
# function (supplied as a parameter to describeTable).
sql = """
SELECT seq.sequence_name
FROM sys.all_sequences seq
WHERE seq.sequence_owner = :schema
"""
cur.execute(sql, schema=schema)
sequences = frozenset(sequence for (sequence, ) in cur)
if not sequence_mapper:
sequence_mapper = self.sequence_mapper
sql = """
SELECT tabcol.column_name
FROM sys.all_tab_cols tabcol
JOIN sys.all_trigger_cols trigcol ON tabcol.owner = trigcol.table_owner
AND tabcol.table_name = trigcol.table_name
AND tabcol.column_name = trigcol.column_name
JOIN sys.all_triggers trig ON trigcol.trigger_owner = trig.owner
AND trigcol.trigger_name = trig.trigger_name
WHERE tabcol.owner = :schema AND tabcol.table_name = :table_name
AND tabcol.data_type = 'NUMBER' AND trigcol.column_usage = 'NEW OUT'
AND trig.status = 'ENABLED'
"""
cur.execute(sql, schema=schema, table_name=table)
for (column, ) in cur:
sequence = sequence_mapper(table, column)
if sequence in sequences:
fields[column].sequence = sequence
return fields, unique