# $Id$
from django.db.backends.db2.base import quote_name

def get_table_list(cursor):
	""" Here's the tricky part. The tables are accessed by their 'creator', (schema?)
	for example, all the systems tables are prefixed by SYSIBM as their creator.
	Since we can get really *lots* of tables without filtering by creator this can get
	really messy, So I've used DATABASE_USER to filter it.
	RTRIMs are needed because the fields return in full column size filled with spaces.
	"""
	from django.conf import settings
	if settings.DATABASE_USER:
		cursor.execute("""SELECT name as TABLES FROM SYSIBM.systables 
		WHERE creator = ? AND TYPE = 'T' ORDER BY name""", settings.DATABASE_USER.upper())
	else: # Fallback to everything but SYS*
		cursor.execute("""SELECT RTRIM(name) as TABLES FROM SYSIBM.systables 
		WHERE creator NOT LIKE 'SYS%' AND TYPE = 'T' ORDER BY name""")
	return [row[0] for row in cursor.fetchall()]

def get_table_description(cursor, table_name):
	"Returns a description of the table with the DB-API cursor.description interface."
	cursor.execute("SELECT * FROM %s FETCH FIRST 1 ROWS ONLY" % table_name)
	return cursor.description

def get_relations(cursor, table_name):
	"""
	Returns a dictionary of {field_index: (field_index_other_table, other_table)}
	representing all relationships to the given table. Indexes are 0-based.
	Beware, PyDB2 returns the full length of the field, filled with spaces, 
	I have to .strip() every single string field >.<
	"""
	cursor.execute("""SELECT fk.colno, fk.colseq, pk.tbname
		FROM sysibm.sysrels r, sysibm.sysforeignkeys fk, sysibm.syscolumns pk
		WHERE r.creator = ?	AND r.tbname = ?
		AND r.relname = fk.relname AND r.creator = fk.creator
		AND r.tbname = fk.tbname AND r.reftbcreator = pk.tbcreator
		AND r.reftbname = pk.tbname
		AND fk.colseq = pk.keyseq""", settings.DATABASE_USER.upper(), table_name)
	relations = {}
	for row in cursor.fetchall():
		relations[int(row[0]) - 1] = (int(row[1]) -1, row[2].strip())
	return relations
	
def get_indexes(cursor, table_name):
	"""
	Returns a dictionary of fieldname -> infodict for the given table,
	where each infodict is in the format:
		{'primary_key': boolean representing whether it's the primary key,
		'unique': boolean representing whether it's a unique index}
	DB2 is weird here, like this seems to be ok but I don't get 100% of the indexes
		syscolumns.keyseq means the column part of a "parent key".
		sysindexes.uniquerule == P means it's primary, U == unique,
			and D == duplicates allowed.
	"""	
	cursor.execute("""SELECT c.name, i.uniquerule 
		FROM sysibm.syscolumns c, sysibm.sysindexes i, sysibm.syskeys k
		WHERE c.tbcreator= ?
		AND c.tbname = ?'
		AND i.name = k.ixname AND k.colname = c.name AND i.tbname = c.tbname
		AND k.ixcreator = c.tbcreator AND i.tbcreator = c.tbcreator
	""", settings.DATABASE_USER.upper(), table_name)
	indexes = {}
	for row in cursor.fetchall():
		urule = row[1].strip()
		name = row[0].strip()
		if urule == "P":
			# Override everything, as this is a primary key.
			indexes[name] = {'primary_key':True, 'unique':False}
		elif urule == "U":
			try:
				if indexes[name]['primary_key'] == True:
					# Can appear twice, but primary is primary anyway.
					continue
				else:
					indexes[name] = {'primary_key':False, 'unique':True}
			except: # TODO: Only a keyerror can happen here, right?
				indexes[name] = {'primary_key':False, 'unique':True}
		else: # urule = "D" not sure if there are others.
			try:
				# Should never happen, but...
				if indexes[name]['primary_key'] == True or indexes[name]['unique'] == True:
					continue
				else:
					indexes[name] = {'primary_key':False, 'unique':False}
			except: # TODO: same as above ^_^
				indexes[name] = {'primary_key':False, 'unique':False}
	return indexes

DATA_TYPES_REVERSE = {
	-99:'TextField', # CLOB
	-98:'TextField', # BLOB
	-97:'TextField', # Long VarGraphic
	-96:'TextField', # VarGraphic
	-95:'TextField', # Graphic
	-5: 'IntegerField', # Big Int
	-4: 'TextField', # Binary Long VarChar
	-3: 'TextField', # Binary VarChar
	-2: 'TextField', # Binary
	-1: 'TextField', # Long VarChar
	1: 'IPAddressField',  # Char
	2: 'FloatField', # Numeric
	3: 'FloatField', # Decimal
	4: 'IntegerField', # Integer
	5: 'BooleanField', # SmallInt
	6: 'FloatField', # Float
	7: 'FloatField', # Real
	8: 'FloatField', # Double
	12: 'CharField', # VarChar
	91: 'DateField', # Date
	92: 'TimeField', # Time
	93: 'DateTimeField', # TimeStamp
}