ActiveState Code

#!/usr/bin/python
# -*- coding: cp1250 -*-
__version__ = '$Id: schema_inf.py 559 2009-01-20 08:00:01Z mn $'

# export Informix schema to text using ODBC
# usable to compare databases that should be the same
#
# schema info:
# http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlr.doc/sqlrmst41.htm
#
# useful queries:
# http://pentestmonkey.net/blog/informix-sql-injection-cheat-sheet/
#
# tested with
# client: IBM Informix ODBC Driver 3.50 TC3DE
# server: IBM Informix Dynamic Server Version 11.50.TC2DE
#
# previous versions of ODBC client fails sometimes with:
# dbi.operation-error:
# [Informix][Informix ODBC Driver][Informix]
# Character host variable is too short for the data. in FETCH
#
# Table names that start with '_' are ignored as they are temp tables
#
# author: Michal Niklas

USAGE = 'usage:\n\tschema_inf.py connect_string\n\t\tconnect string: odbc_database/user/password'

import sys

import odbc

TABLE_NAMES_SQL = """SELECT tabname
FROM systables
WHERE tabtype='T'
AND tabid >= 100
AND tabname[1] <> '_'
ORDER BY tabname"""

TABLE_INFO_SQL = """SELECT tabname, colname, colno, HEX(coltype),
CASE MOD(coltype, 256)
  WHEN  0 THEN 'char'
  WHEN  1 THEN 'smallint'
  WHEN  2 THEN 'integer'
  WHEN  3 THEN 'float'
  WHEN  4 THEN 'smallfloat'
  WHEN  5 THEN 'decimal'
  WHEN  6 THEN 'serial'
  WHEN  7 THEN 'date'
  WHEN  8 THEN 'money'
  WHEN  9 THEN 'null'
  WHEN 10 THEN 'datetime'
  WHEN 11 THEN 'byte'
  WHEN 12 THEN 'text'
  WHEN 13 THEN 'varchar'
  WHEN 14 THEN 'interval'
  WHEN 15 THEN 'nchar'
  WHEN 16 THEN 'nvarchar'
  WHEN 17 THEN 'int8'
  WHEN 18 THEN 'serial8'
  WHEN 19 THEN 'set'
  WHEN 20 THEN 'multiset'
  WHEN 21 THEN 'list'
  WHEN 22 THEN 'Unnamed ROW'
  WHEN 40 THEN 'Variable-length'
  WHEN 4118 THEN 'Named ROW'
  ELSE '???'
END CASE, collength
FROM syscolumns, systables
WHERE tabtype='T'
AND systables.tabid >= 100
AND tabname[1] <> '_'
AND syscolumns.tabid = systables.tabid
ORDER BY tabname, colname
"""

KEYS_INFO_SQL = """select tabname, a.colname column1, b.colname column2,
c.colname column3, d.colname column4, e.colname column5,
f.colname column6, g.colname column7, h.colname column8,
i.colname column9, j.colname column10, k.colname column11,
l.colname column12, m.colname column13, n.colname column14,
o.colname column15, p.colname column16
from sysindexes si, systables st, sysconstraints sc, syscolumns a,
outer syscolumns b,
outer syscolumns c, outer syscolumns d, outer syscolumns e,
outer syscolumns f, outer syscolumns g, outer syscolumns h,
outer syscolumns i, outer syscolumns j, outer syscolumns k,
outer syscolumns l, outer syscolumns m, outer syscolumns n,
outer syscolumns o, outer syscolumns p
WHERE st.tabid >= 100
AND tabtype='T'
AND tabname[1] <> '_'
AND sc.tabid = st.tabid
AND si.idxname = sc.idxname
AND sc.constrtype='%s'
and st.tabid = si.tabid
and st.tabid = a.tabid
and st.tabid = b.tabid
and st.tabid = c.tabid
and st.tabid = d.tabid
and st.tabid = e.tabid
and st.tabid = f.tabid
and st.tabid = g.tabid
and st.tabid = h.tabid
and st.tabid = i.tabid
and st.tabid = j.tabid
and st.tabid = k.tabid
and st.tabid = l.tabid
and st.tabid = m.tabid
and st.tabid = n.tabid
and st.tabid = o.tabid
and st.tabid = p.tabid
and a.colno = part1
and b.colno = part2
and c.colno = part3
and d.colno = part4
and e.colno = part5
and f.colno = part6
and g.colno = part7
and h.colno = part8
and i.colno = part9
and j.colno = part10
and k.colno = part11
and l.colno = part12
and m.colno = part13
and n.colno = part14
and o.colno = part15
and p.colno = part16
ORDER BY tabname, column1, column2, column3, column4, column5
"""


INDEXES_INFO_SQL = """SELECT idxname, idxtype
FROM systables, sysindices
WHERE tabname='%s'
AND sysindices.tabid = systables.tabid
AND tabtype='T'
AND systables.tabid >= 100
ORDER BY tabname, idxname
"""

# http://groups.google.pl/group/comp.databases.informix/browse_thread/thread/a488c9bfb3a71c5a?ie=UTF-8&oe=utf-8&q=%22outer+syscolumns+f%22
INDEXES_COLUMNS_INFO_SQL = """select idxtype, a.colname column1, b.colname column2,
c.colname column3, d.colname column4, e.colname column5,
f.colname column6, g.colname column7, h.colname column8,
i.colname column9, j.colname column10, k.colname column11,
l.colname column12, m.colname column13, n.colname column14,
o.colname column15, p.colname column16
from sysindexes si, systables st, syscolumns a,
outer syscolumns b,
outer syscolumns c, outer syscolumns d, outer syscolumns e,
outer syscolumns f, outer syscolumns g, outer syscolumns h,
outer syscolumns i, outer syscolumns j, outer syscolumns k,
outer syscolumns l, outer syscolumns m, outer syscolumns n,
outer syscolumns o, outer syscolumns p
WHERE tabname='%s'
AND tabtype='T'
AND tabname[1] <> '_'
AND st.tabid >= 100
and st.tabid = si.tabid
and st.tabid = a.tabid
and st.tabid = b.tabid
and st.tabid = c.tabid
and st.tabid = d.tabid
and st.tabid = e.tabid
and st.tabid = f.tabid
and st.tabid = g.tabid
and st.tabid = h.tabid
and st.tabid = i.tabid
and st.tabid = j.tabid
and st.tabid = k.tabid
and st.tabid = l.tabid
and st.tabid = m.tabid
and st.tabid = n.tabid
and st.tabid = o.tabid
and st.tabid = p.tabid
and a.colno = part1
and b.colno = part2
and c.colno = part3
and d.colno = part4
and e.colno = part5
and f.colno = part6
and g.colno = part7
and h.colno = part8
and i.colno = part9
and j.colno = part10
and k.colno = part11
and l.colno = part12
and m.colno = part13
and n.colno = part14
and o.colno = part15
and p.colno = part16
ORDER BY tabname, column1, column2, column3, column4, column5
"""


DEFAULTS_INFO_SQL = """SELECT tabname, colname, type, default
FROM syscolumns, systables, sysdefaults
WHERE tabtype='T'
AND systables.tabid >= 100
AND tabname[1] <> '_'
AND syscolumns.tabid = systables.tabid
AND sysdefaults.tabid = systables.tabid
AND syscolumns.colno = sysdefaults.colno
ORDER BY tabname, colname"""


VIEWS_INFO_SQL = """SELECT tabname, tabid
FROM systables
WHERE tabtype='V'
AND tabid >= 100
AND tabname[1] <> '_'
"""

VIEWS_TEXT_SQL = """SELECT viewtext
FROM sysviews
WHERE tabid=%s
ORDER BY seqno
"""


TRIGGERS_INFO_SQL = """SELECT tabname, trigname, event
FROM systables, systriggers
WHERE tabtype='T'
AND systables.tabid >= 100
AND tabname[1] <> '_'
AND systables.tabid = systriggers.tabid
ORDER BY tabname, trigname"""


PROCEDURES_INFO_SQL = """SELECT procname, numargs, isproc, paramtypes, variant, handlesnulls, parallelizable
FROM sysprocedures
WHERE internal='f' AND mode IN ('D', 'd', 'O', 'o')
ORDER BY procname, numargs, procid"""



_CONN = None

TABLES = []


def init_db_conn(connect_string):
	"""initializes db connections"""
	global _CONN
	(dbname, dbuser, _) = connect_string.split('/', 3)
	dbinfo = 'db: %s:%s' % (dbname, dbuser)
	try:
		_CONN = odbc.odbc(connect_string)
		print dbinfo
	except KeyboardInterrupt:
		raise
	except:
		ex = sys.exc_info()
		s = 'Exception: %s: %s\n%s' % (ex[0], ex[1], dbinfo)
		print s
		return None
	return _CONN


def db_conn():
	"""access to global db connection"""
	return _CONN


def select_qry(querystr):
	"""return rows from SELECT"""
	if querystr:
		try:
			cur = db_conn().cursor()
			cur.execute(querystr)
			results = cur.fetchall()
			cur.close()
			return results
		except KeyboardInterrupt:
			raise
		except:
			ex = sys.exc_info()
			s = 'Exception: %s: %s\nSomething is terribly wrong with query:\n%s' % (ex[0], ex[1], querystr)
			sys.stderr.write(s)


def field_str(fld_value):
	"""convert fld to printable text"""
	if not fld_value:
		return ''
	s = str(fld_value)
	return s.rstrip()


def show_qry(title, querystr, fld_join = '\t', row_separator = None):
	"""prints rows from SELECT"""
	print '\n\n'
	print '--- %s ---' % title
	rs = select_qry(querystr)
	if rs:
		for row in rs:
			print fld_join.join([field_str(s) for s in row])
			if row_separator:
				print row_separator
	else:
		print ' -- NO DATA --'



def show_qry_ex(querystr, table, fld_join = '\t', row_separator = None):
	"""like show_qry() but with table name as first column"""
	rs = select_qry(querystr % table)
	if rs:
		for row in rs:
			print "%s%s%s" % (table, fld_join, fld_join.join([field_str(s) for s in row]))
			if row_separator:
				print row_separator


def init_session():
	"""place to change db session settings like locale"""
	pass


def show_tables():
	"""prints table names"""
	cur = db_conn().cursor()
	cur.execute(TABLE_NAMES_SQL)
	for row in cur.fetchall():
		if not row[0].startswith('_'):
			TABLES.append(row[0])
	cur.close()
	show_qry('tables', TABLE_NAMES_SQL)
	show_qry('columns', TABLE_INFO_SQL)


def show_primary_keys():
	"""print primary keys"""
	show_qry('primary keys', KEYS_INFO_SQL % ('P'))


def show_indexes():
	"""print indexes"""
	print '\n\n'
	print '--- %s ---' % 'indexes'
	for tbl in TABLES:
		show_qry_ex(INDEXES_INFO_SQL, tbl)
	#show_qry('indexes columns', INDEXES_COLUMNS_INFO_SQL)
	print '\n\n'
	print '--- %s ---' % 'indexes columns'
	for tbl in TABLES:
		show_qry_ex(INDEXES_COLUMNS_INFO_SQL, tbl)


def show_foreign_keys():
	"""print forign keys"""
	show_qry('foreign keys', KEYS_INFO_SQL % ('R'))


def show_defaults():
	"""print defaults"""
	show_qry('defaults', DEFAULTS_INFO_SQL)


def show_views():
	print '\n\n'
	print '--- %s ---' % 'views'
	cur = db_conn().cursor()
	cur.execute(VIEWS_INFO_SQL)
	for row in cur.fetchall():
		tabname = row[0]
		tabid = row[1]
		cur2 = db_conn().cursor()
		cur2.execute(VIEWS_TEXT_SQL % tabid)
		print tabname
		vt = []
		for row2 in cur2.fetchall():
			vt.append(row2[0])
		vtt = ''.join(vt)
		print vtt.rstrip()
		print



def get_body(qry):
	"""joins body of stored procedure or trigger"""
	body_lines = ['', ]
	if qry:
		rs = select_qry(qry)
		if rs:
			for row in rs:
				body_lines.append(field_str(row[0]))
	return ''.join(body_lines)


def show_procedures():
	"""show procedures and functions"""
	show_qry('procedures/functions', PROCEDURES_INFO_SQL)
	print '\n\n'
	print '--- %s ---' % 'procedures/functions bodies'
	querystr1 = """SELECT procid, procname
FROM sysprocedures
WHERE internal='f' AND mode IN ('D', 'd', 'O', 'o')
ORDER BY procname, numargs, procid"""
	querystr2 = """SELECT data
FROM sysprocbody
WHERE procid=%s
AND datakey='T'
ORDER BY seqno
	"""
	rs = select_qry(querystr1)
	if rs:
		for row in rs:
			funname = row[1]
			body = get_body(querystr2 % row[0])
			print '\n\n -- >>> %s >>> --' % funname
			print body
			print '\n\n -- <<< %s <<< --' % funname


def show_triggers():
	"""show triggers"""
	show_qry('triggers', TRIGGERS_INFO_SQL)
	print '\n\n'
	print '--- %s ---' % 'triggers bodies'
	querystr1 = """SELECT trigid, tabname, trigname
FROM systables, systriggers
WHERE tabtype='T'
AND systables.tabid >= 100
AND systables.tabid = systriggers.tabid
ORDER BY tabname, trigname"""
	querystr2 = """SELECT data FROM systrigbody
WHERE trigid=%s
AND (datakey = 'D')
ORDER BY seqno
	"""
	querystr3 = """SELECT data FROM systrigbody
WHERE trigid=%s
AND (datakey = 'A')
ORDER BY seqno
"""
	rs = select_qry(querystr1)
	if rs:
		for row in rs:
			trigname = 'trigger %s' % (row[2])
			trigid = row[0]
			for row in rs:
				body_def = get_body(querystr2 % trigid)
				body_txt = get_body(querystr3 % trigid)
				print '\n\n -- >>> %s >>> --' % trigname
				print body_def
				print body_txt
				print '\n\n -- <<< %s <<< --' % trigname



def test():
	main('odbc_db/user/password')


def main(connect_string):
	if not init_db_conn(connect_string):
		print 'Something is terribly wrong with db connection'
	else:
		init_session()
		show_tables()
		show_primary_keys()
		show_indexes()
		show_foreign_keys()
		show_defaults()
		show_views()
		show_triggers()
		show_procedures()


if '--version' in sys.argv:
	print __version__
elif '--test' in sys.argv:
	test()
elif __name__ == '__main__':
	if len(sys.argv) != 2:
		print USAGE
	else:
		main(sys.argv[1])