#!/usr/bin/python
#
# $Id: $
#
#	Tables:
#	RunRange: runrangeid, minRun, maxRun, comment
#	(alter all other tables to include runrangeid)
#	Log: function, args, user, date
#
#	Functions:
#	CreateRunRange(minRun, maxRun, comment)
#	GetRunRanges(systems)      returns ids
#	DeleteRunRange(systems)
#	CopyToRunRange(fromid, toid, systems)
#	GetRunRangeInfo(id)
#	DeleteFromRunRange(id, systems)
#	ChangeRunRange(id, minRun, maxRun, <comment>)
#
#	SwapChannels(rocid1, slot1, chan1, rocid2, slot2, chan2, runrangeid=0)
#
#	CreateSystem({columns})
#	DeleteSystem(systems)
#	GetSystems()  returns systems
#	AddChannel(system, {columns}, [runrangeids])
#	RemoveChannel(system, {columns}, [runrangeids])
#	GetChannels(system, <runrangeid>) returns {columns}
#
#	AddToLog(message)
#	GetLog(<limit>, <startdate>, <enddate>)
#	GetXML(<runrangeid>, <run>)
#

import sqlite3 as lite
import sys
import os
import re
import datetime

USER = os.getenv('USER')

# Globals
db_valid = False
db_filename = 'tt.db'
con = None
cur = None

#===========================================================

#----------------------------
# AddToLog
#----------------------------
def AddToLog(function, args):
	argstr = ''
	for arg in args: argstr = argstr + ' ' + arg
	sql = 'INSERT INTO Log VALUES ("%s", "%s", "%s", datetime("now","localtime"))' % (USER, function, argstr)
	cur.execute(sql)
	con.commit()

#----------------------------
# TableExists
#
# Returns True if table of given name exists. False if not.
#----------------------------
def TableExists(tablename, printmess=False):
	cur.execute('SELECT name FROM sqlite_master WHERE type="table" AND name="%s"' % tablename)
	if len(cur.fetchall()) == 0:
		if printmess: print 'Table "%s" does not exist' % tablename
		return False
	else:
		return True

#----------------------------
# ColumnExists
#
# Returns True if given table exists and it has the given
# column defined. False otherwise.
#----------------------------
def ColumnExists(tablename, columnname, printmess=False):
	cur.execute('SELECT sql FROM sqlite_master WHERE type="table" AND name="%s"' % tablename)
	s_rows = cur.fetchall()
	if len(s_rows) < 1 : return False
	sql = s_rows[0][0]
	istart = sql.find('(')
	iend   = sql.rfind(')')
	columns = sql[istart+1:iend-1].split(',')
	for col in columns:
		(name, definition) = col.lstrip().split(' ', 1)
		if name ==columnname : return True
	if printmess: print 'Column "%s" does not exist in table "%s"' % (columnname, tablename)
	return False

#----------------------------
# ReplacePrimaryKey
#
# Modify existing table such that the currently defined primary key
# column is redefined to not be a primary key and that a new 
# primary key column is added that combines multiple columns into
# the primary key. This is a somewhat expensive exercise since one
# cannot simply alter an existing table to change primary key attributes.
# A new table with the correct definitions must be made and the entire
# contents copied over.
#
# This will first look for a column whose type definition is
# "INTEGER PRIMARY KEY". If no such column exists, then nothing
# is done. If one does exist, the new table is made with this
# column redefined to be simply "INTEGER" and the value 
# primarykeydef (e.g. "PRIMARY KEY(chanid, runrangeid)") is
# appended as the last column.
#----------------------------
def ReplacePrimaryKey(tablename, primarykeydef):
	cur.execute('SELECT sql FROM sqlite_master WHERE type="table" AND name="%s"' % tablename)
	s_rows = cur.fetchall()
	if len(s_rows) < 1 :
		print 'Table "' + tablename + '" not found when trying to replace primary key!'
		return
	sql = s_rows[0][0]
	istart = sql.find('(')
	iend   = sql.rfind(')')
	columns = sql[istart+1:iend].split(',')
	found_primary_key_col = False
	coldefs = []
	for col in columns:
		if '(' in col : continue  # Ignore PRIMARY KEY definition which is split into two col
		if ')' in col : continue  # Ignore PRIMARY KEY definition which is split into two col
		(name, definition) = col.lstrip().split(' ', 1)
		if definition == 'INTEGER PRIMARY KEY':
			definition = 'INTEGER'
			found_primary_key_col = True
		coldefs.append((name, definition))
	if not found_primary_key_col :
		print 'Unable to find a primary key in table "' + tablename + '"'
		return
	
	print 'Single column PRIMARY KEY found for table %12s. Modifying primary key ...' % tablename
	AddToLog('ReplacePrimaryKey', [tablename, primarykeydef])
	
	# Rename old table to something else to move it out of the way
	tmp_tablename = '%s_old' % tablename
	sql = 'ALTER TABLE %s RENAME TO %s' % (tablename, tmp_tablename)
	#print sql
	cur.execute(sql)

	# Create new table with correct definition
	sql = 'CREATE TABLE %s(' % tablename
	for (name, definition) in coldefs:
		sql = sql + name + ' ' + definition + ', '
	sql = sql + primarykeydef + ')'
	#print sql
	cur.execute(sql)
	
	# Copy contents from old table to new table
	col_list = ''
	for (name, definition) in coldefs: col_list = col_list + name + ','
	col_list = col_list[:-1]
	
	sql = 'INSERT INTO %s (%s) SELECT * FROM %s' % (tablename, col_list, tmp_tablename)
	#print sql
	cur.execute(sql)
	con.commit()
	
	# Drop existing table
	sql = 'DROP TABLE %s' % tmp_tablename
	#print sql
	cur.execute(sql)
	
	con.commit()
	

#----------------------------
# AddColumnIfNotExists
#----------------------------
def AddColumnIfNotExists(tablename, columndef):
	if not TableExists(tablename) : return

	colname = columndef.split()[0]
	if not ColumnExists(tablename, colname):
		cur.execute('ALTER TABLE %s ADD COLUMN %s' % (tablename, columndef))
		print 'Adding column to %15s : "%s"' % (tablename, columndef)
		AddToLog('AddColumnIfNotExists', [tablename, columndef])
		con.commit()

#----------------------------
# UpdateSchema
#
# This will modify an SQLite schema to include columns to implement
# run ranges for individual channels. This is only needed for older
# tt.db files that existed prior to Jan. 2018. It also adds the Log
# table.
#
# If the changes appear to have already been made to the schema then
# those particular changes are left in place and are not re-applied.
# (i.e. it is safe to run this on DBs that have already been updated)
#----------------------------
def UpdateSchema():
	# Verify that the schema contains the Log and RunRange tables
	# and that all other tables have a runrangeid colummn
	if not TableExists('Log'): 
		cur.execute('CREATE TABLE Log(user TEXT, function TEXT, args TEXT, date DATETIME DEFAULT CURRENT_TIMESTAMP)')
		print 'Created table "Log"'
		AddToLog('UpdateSchema', 'CREATE TABLE Log'.split())
	if not TableExists('RunRange'):
		cur.execute('CREATE TABLE RunRange(runrangeid INTEGER PRIMARY KEY, minRun INT, maxRun INT, name TEXT UNIQUE)')
		AddToLog('UpdateSchema', 'CREATE TABLE RunRange'.split())
		cur.execute('INSERT INTO RunRange(minRun, maxRun, name) VALUES (0, 2147483647, "default")')
		print 'Created table "RunRange" and inserted default range for runs 0-inf'
		AddToLog('UpdateSchema', 'Insert the \'all runs\' range into RunRange'.split())

	columndef = 'runrangeid INTEGER DEFAULT 1'
	
	# If we only had to worry about cable swaps within a system then
	# we would not need to include runrangeid in the Crate Module Channel
	# tables. However, when the RF was introduced to the TT the system
	# for those channels was redfined. Thus, at least the Channel table
	# needs to have the runrangeid added.
#	AddColumnIfNotExists('Crate', columndef)
#	AddColumnIfNotExists('Module', columndef)
	AddColumnIfNotExists('Channel', columndef)
	
	AddColumnIfNotExists('BCAL', columndef)
	AddColumnIfNotExists('CDC', columndef)
	AddColumnIfNotExists('FCAL', columndef)
	AddColumnIfNotExists('FDC_Cathodes', columndef)
	AddColumnIfNotExists('FDC_Wires', columndef)
	AddColumnIfNotExists('PS', columndef)
	AddColumnIfNotExists('PSC', columndef)
	AddColumnIfNotExists('ST', columndef)
	AddColumnIfNotExists('TAGH', columndef)
	AddColumnIfNotExists('TAGM', columndef)
	AddColumnIfNotExists('TOF', columndef)
	AddColumnIfNotExists('RF', columndef)
	AddColumnIfNotExists('TPOL', columndef)
	AddColumnIfNotExists('HALO', columndef)
	AddColumnIfNotExists('Active_Target', columndef)
	AddColumnIfNotExists('TAC', columndef)
	AddColumnIfNotExists('CCAL', columndef)

#	ReplacePrimaryKey('Crate', 'PRIMARY KEY(crateid, runrangeid)')
#	ReplacePrimaryKey('Module', 'PRIMARY KEY(moduleid, runrangeid)')
	ReplacePrimaryKey('Channel', 'PRIMARY KEY(chanid, runrangeid)')

#----------------------------
# VerifySchema
#
# This will verify the SQLite schema includes columns added by UpdateSchema
# above.
#----------------------------
def VerifySchema():
	isGood = True
	if not TableExists('Log'      , True): isGood = False
	if not TableExists('RunRange' , True): isGood = False
	tables = ['Channel','BCAL','CDC','FCAL','FDC_Cathodes','FDC_Wires','PS','ST','TAGH','TAGM','TOF','RF','TPOL','HALO','Active_Target','TAC','CCAL']
	for t in tables:
		if not ColumnExists(t, 'runrangeid', True): isGood = False

	return isGood

#----------------------------
# SystemsToArray
#
# This is a utility function that will take a "systems" argument
# and return it in the form of an array. The given systems variable
# can be a None, a single string, or an array. In the first case,
# an empty array will be retuned. In the second case, an array with
# a single element. And in the third case the return value will be
# that as what was passed in.
#----------------------------
def SystemsToArray(systems):
	mysystems = []
	if type(systems) is str : mysystems = [systems]
	if type(systems) is list: mysystems =  systems
	return mysystems

#----------------------------
# GetLog
#
# Returns list of strings, each representing an entry in the Log table.
# If max_messages is not specified or given as zero, then all log messages
# will be returned. Otherwise, only the most recent max_messages in the
# specified date range are returned. Values are returned in descending order 
# by date. 
#----------------------------
def GetLog(max_messages=0, startdate='2000-01-01', enddate='3000-01-01'):
	sql = 'SELECT * FROM Log WHERE date>="%s" AND date<="%s" ORDER BY date DESC' % (startdate, enddate)
	if max_messages>0 : sql = sql + ' LIMIT %d' % int(max_messages)
	cur.execute(sql)
	s_rows = cur.fetchall()
	messages = []
	for log in s_rows:
		user = log[0]
		function = log[1]
		args = log[2]
		date = log[3]
	
		messages.append(date + (' %10s ' % user) + function + ' ' + args)
	return messages

#----------------------------
# CreateRunRange
#
# Create a new run range by making a new entry in the RunRange table.
# If a range with the given name already exists, then the id for the
# existing entry is returned and nothng is modified (use ChangeRunRange
# to modify an existing run range). If a new entry is created, then 
# the id for that entry is returned.
#----------------------------
def CreateRunRange(minRun, maxRun, name):
	AddToLog('CreateRunRange', [str(minRun), str(maxRun), name])
	# Check if run range with this name already exists
	cur.execute('SELECT runrangeid FROM RunRange WHERE name="%s"' % name)
	s_rows = cur.fetchall()
	if len(s_rows)>0 :return s_rows[0][0]

	# Check if run range with this min and max already exists
	cur.execute('SELECT runrangeid FROM RunRange WHERE minRun="%d AND maxRun=%d"' % (minRun, maxRun))
	s_rows = cur.fetchall()
	if len(s_rows)>0 :return s_rows[0][0]

	# Create new run range and return id
	cur.execute('INSERT INTO RunRange (minRun, maxRun, name) VALUES (%d,%d,"%s")' % (minRun, maxRun, name ) )
	cur.execute('SELECT runrangeid FROM RunRange WHERE name="%s"' % name)
	s_rows = cur.fetchall()
	con.commit()
	if len(s_rows)>0 :return s_rows[0][0]
	return -1

#----------------------------
# GetRunRanges
#
# Return a list of all run ranges currently defined. Each element of 
# the returned list is a dictionary containing the minRun, maxRun, and 
# name of the run range. If the specified systems list is not empty 
# then the returned list will contain only those run ranges where at
# least one of the specified systems has an entry.
#----------------------------
def GetRunRanges(systems=None):
	runrangeids = []
	mysystems = SystemsToArray(systems)

	# Get ranges if one or more systems were specified
	if len(mysystems)>0 :
		# At least one system specified. Look for run range ids
		for system in mysystems:
			if ColumnExists(system, 'runrangeid'):
				cur.execute('SELECT runrangeid FROM %s GROUP BY runrangeid' % system)
				s_rows = cur.fetchall()
				for row in s_rows : runrangeids.append(row[0])

	# Get ranges if no system is specified
	else:
		# No system specified. Return all run ranges
		cur.execute('SELECT runrangeid FROM RunRange')
		s_rows = cur.fetchall()
		for row in s_rows :
			runrangeids.append(row[0])

	# Get info for each runrangeid
	rrinfos = []
	for runrangeid in set(runrangeids):
		cur.execute('SELECT * FROM RunRange WHERE runrangeid=%d' % runrangeid)
		s_rows = cur.fetchall()
		if len(s_rows)<1 : continue
		colnames = map(lambda x: x[0], cur.description)
		rrinfo = {}
		for colname in colnames: rrinfo[colname] = s_rows[0][colname]
		rrinfos.append(rrinfo)

	return rrinfos

#----------------------------
# GetRunRangeInfo
#----------------------------
def GetRunRangeInfo(runrangeid):
	rrinfo = {}
	cur.execute('SELECT * FROM RunRange WHERE runrangeid=%d' % runrangeid)
	s_rows = cur.fetchall()
	if len(s_rows)>0 :
		colnames = map(lambda x: x[0], cur.description)
		for colname in colnames: rrinfo[colname] = s_rows[0][colname]
	return rrinfo

#----------------------------
# GetRunRangeID
#----------------------------
def GetRunRangeID(name):
	cur.execute('SELECT runrangeid FROM RunRange WHERE name="%s"' % name)
	s_rows = cur.fetchall()
	if len(s_rows)>0 : return s_rows[0][0]
	return -1

#----------------------------
# GetRunRangeIDByRun
#----------------------------
def GetRunRangeIDByRun(run):
	cur.execute('SELECT runrangeid FROM RunRange WHERE minRun<=%d AND maxRun>=%d ORDER BY runrangeid DESC LIMIT 1' % (int(run), int(run)))
	s_rows = cur.fetchall()
	if len(s_rows)>0 : return s_rows[0][0]
	return -1

#----------------------------
# GetRunBoundariesByChannel
#
# This will get a list of run numbers within the specified run range
# where the specified channel had at least one parameter change. If
# all parameters stayed the same, then an empty list is returned.
#----------------------------
def GetRunBoundariesByChannel(rocid, slot, channel, minRun, maxRun):
	
	# First, check if system or col_name changed in the specified range
	cur.execute('SELECT runrangeid FROM Channel,RunRange WHERE minRun<=%d AND maxRun>=%d ORDER BY runrangeid DESC LIMIT 1' % (int(minRun), int(maxRun)))
	s_rows = cur.fetchall()
	if len(s_rows)>0 : return s_rows[0][0]
	return -1

#----------------------------
# DeleteRunRange
#
# This will delete entries with the specified runrangeid from all
# tables, effectively removing any trace of the runrange from the
# system. Use caution here! 
#----------------------------
def DeleteRunRange(runrangeid):
	AddToLog('DeleteRunRange', [str(runrangeid)])
	cur.execute('SELECT name FROM sqlite_master WHERE type="table"')
	s_rows = cur.fetchall()
	for row in s_rows:
		tablename = row[0]
		if ColumnExists(tablename, 'runrangeid'):
			cur.execute('SELECT * FROM %s WHERE runrangeid=%d' %(tablename, runrangeid))
			if len(cur.fetchall()) > 0 :
				print 'removing runrangeid=%d from %s' % (runrangeid, tablename)
				cur.execute('DELETE FROM %s WHERE runrangeid=%d' %(tablename, runrangeid))
				con.commit()
		
#----------------------------
# ChangeRunRange
#
# This will modify the minRun, maxRun and name of the run range specified
# by the runrangeid. (To get the runrangeid look at GetRunRangeID() or
# GetRunRanges() ). Any of the 3 parameters minRun, maxRun, or name may
# be omitted and their value will not be changed. For example,
# ChangeRunRange(2, maxRun=987) will change only the maxRun value for
# the run range with runrangeid=2.
#
# Note that this does not check that the maxRun is greater than or equal
# to minRun.
#----------------------------
def ChangeRunRange(runrangeid, minRun=-1, maxRun=-1, name=''):
	if minRun>0: cur.execute('UPDATE RunRange SET minRun=%d WHERE runrangeid=%d' % (minRun, runrangeid))
	if maxRun>0: cur.execute('UPDATE RunRange SET maxRun=%d WHERE runrangeid=%d' % (maxRun, runrangeid))
	if name!='': cur.execute('UPDATE RunRange SET name="%s" WHERE runrangeid=%d' % (name  , runrangeid))
	con.commit()
	

#----------------------------
# CopyToRunRange
#----------------------------
def CopyToRunRange(fromid, toid, systems):
	mysystems = SystemsToArray(systems)	
	AddToLog('CopyToRunRange', [runrangeid, ' '.join(str(x) for x in mysystems)])

#----------------------------
# DeleteFromRunRange
#----------------------------
def DeleteFromRunRange(id, systems):
	pass

#----------------------------
# SwapChannels
#----------------------------
def SwapChannels(rocid1, slot1, chan1, rocid2, slot2, chan2, runrangeid=1):
	pass

#----------------------------
# CreateSystem
#
# Create a new, empty detector system. The "columns" argument
# should be a string containing SQL code to define the columns.
# For example:
#   CreateSystem('TPOL', 'sector INT, adc_chanid INTEGER, tdc_chanid INTEGER')
#
# n.b. The columns value should NOT contain the runrangeid column. This
# is added automatically.
#----------------------------
def CreateSystem(system, columns):
	try:
		sql = 'CREATE table ' + system + '(' + columns + ', runrangeid INTEGER DEFAULT 1)'
		cur.execute(sql)
		print 'Added system %s' % system
		AddToLog('CreateSystem', [system, columns])
	except:
		print 'ERROR adding system using:'
		print sql

#----------------------------
# DeleteSystem
#
# This will completely delete an entire detector system. This drops
# the table associated with the system. It will also check if any entries
# in the channel table are set to this system and print an error message
# if any are found.
#
# WARNING: Deleting a system can be a very destructive operation. Use
# with great caution!
#----------------------------
def DeleteSystem(systems):
	mysystems = SystemsToArray(systems)
	for system in mysystems:
		if TableExists(system): cur.execute('DROP TABLE '+system)
		cur.execute('SELECT count(*) FROM Channel WHERE system="%s"' % system)
		s_rows = cur.fetchall()
		if len(s_rows)>0:
			print 'WARNING: %d Channels still defined for deleted system "%s"' % (s_rows[0][0], system)
	AddToLog('DeleteSystem', [' '.join(str(x) for x in mysystems)])

#----------------------------
# GetSystems
#
# Return list of currently defined systems.
#----------------------------
def GetSystems():
	ignore_tables = ['Channel', 'Module', 'Crate', 'Detector_Hierarchy', 'RunRange', 'Log']
	cur.execute('SELECT name FROM sqlite_master WHERE type="table"')
	s_rows = cur.fetchall()
	systems = []
	for row in s_rows:
		if row[0] not in ignore_tables: systems.append(row[0])

	return systems

#----------------------------
# GetSystemsByROCID
#
# Return list of systems with entries for the given ROCID.
#----------------------------
def GetSystemsByROCID(rocid):
	cur.execute('SELECT system FROM Crate,Module,Channel WHERE rocid=%d AND Crate.crateid=Module.crateid AND Module.moduleid=Channel.moduleid GROUP BY system' % int(rocid))
	s_rows = cur.fetchall()
	systems = []
	for row in s_rows:
		if row[0]!= '': systems.append(row[0])

	return systems

#----------------------------
# GetROCIDs
#
# Return dictionary with rocids as keys and list of systems as values
#----------------------------
def GetROCIDs(runrangeid):
	sql = 'SELECT rocid,system FROM Crate,Module,Channel WHERE Channel.runrangeid=%d AND Crate.crateid=Module.crateid AND Module.moduleid=Channel.moduleid GROUP BY rocid,system ORDER BY rocid DESC, system ASC' % int(runrangeid)
	cur.execute(sql)
	s_rows = cur.fetchall()
	rocids = {}
	for row in s_rows:
		if row[0] not in rocids.keys(): rocids[row[0]] = []
		rocids[row[0]].append(row[1])

	return rocids

#----------------------------
# GetSystemIndices
#
# Return list of system-specific indices for the specified system
#----------------------------
def GetSystemIndices(system):
	ignore_columns = ['adc_chanid', 'tdc_chanid', 'disc_chanid', 'runrangeid']
	cur.execute('SELECT * FROM %s LIMIT 0' % system)
	cur.fetchall()
	colnames = map(lambda x: x[0], cur.description)
	indices = []
	for c in colnames:
		if c not in ignore_columns: indices.append(c)

	return indices

#----------------------------
# GetSystemChanTypes
#
# Return list of system-specific channel types for the specified system.
# The list will contain some combination of "adc_chanid", "tdc_chanid"
# and "disc_chanid"
#----------------------------
def GetSystemChanTypes(system):
	all_types = ['adc_chanid', 'tdc_chanid', 'disc_chanid']
	cur.execute('SELECT * FROM %s LIMIT 0' % system)
	cur.fetchall()
	colnames = map(lambda x: x[0], cur.description)
	types = []
	for t in all_types:
		if t in colnames: types.append(t)

	return types

#----------------------------
# GetSystemIndexLimits
#
# Return dictionary of system-specific indices for the specified system
# The value part of the dictionary will be a dictionary usually with entries
# "min" and "max". For indices that are non-numeric, the dictionary
# keys will correspond to all possible values. (The values of those will be
# set to the same value as the key.)
#----------------------------
def GetSystemIndexLimits(system):

	limits = {}
	for index_name in GetSystemIndices(system):
		sql = 'SELECT min(%s),max(%s) FROM %s' % (index_name, index_name, system)
		cur.execute(sql)
		s_rows = cur.fetchall()
		if str(s_rows[0][0]).isdigit() and str(s_rows[0][1]).isdigit():
			d = { 'min':s_rows[0][0], 'max':s_rows[0][1]}
		else:
			sql = 'SELECT %s FROM %s GROUP BY %s' % (index_name, system, index_name)
			cur.execute(sql)
			s_rows = cur.fetchall()
			d={}
			for row in s_rows:
				d[str(row[0])] = str(row[0])
		
		limits[index_name] = d
		
	return limits

#----------------------------
# AddCrate
#
# Add a new crate with the given rocid, area, rack, location.
# Example:
#
#   AddCrate(rocid=12, area='U1', rack=8, location='TOP')
#
# The crate name will be formed automatically from the area, rack and location.
# Serial number (SN), host, and IP are all optional. One or more run 
# ranges may be specified as well. If no run range is specified, then the default 
# run range covering all runs will be used.
#
# Note that the "Function" field of a Crate table entry will be automatically
#  updated by AddModule() based on the module type.
#----------------------------
def AddCrate(rocid, area, rack, location, SN='undefined', host='undefined', IP='undefined'):

	# Check if an entry already exists for this rocid and runrangeid
	cur.execute('SELECT * FROM Crate WHERE rocid=%d' % rocid)
	if len(cur.fetchall()) > 0:
		print 'Crate entry for rocid=%d lready exists!'
	else:
		try:
			name = '%s-%s-%s' % (area, str(rack), location)
			sql = 'INSERT INTO Crate (name,area,rack,location,SN,Function,rocid,host,IP)'
			sql = sql + 'VALUES("%s","%s","%s","%s","%s","undefined",%d,"%s","%s")' % (name,area,rack,location,SN,rocid,host,IP)
			cur.execute(sql)
			print 'Added crate for rocid=%d  %s' % (rocid, name)
		except:
			print 'ERROR: Adding Crate using:'
			print sql
			sys.exit(-1)
	con.commit()

#----------------------------
# AddModule
#
# Add one or more modules to the crate with the specified rocid.
# An entry in Crate with the rocid must exist first (see AddCrate()).
# The value of "slot" may be a scaler for a single slot or a list
# to add multiple slots.
# Note that if multiple slots are specified then all will have the
# same SN!
#----------------------------
def AddModule(rocid, slot, type, SN='undefined'):
	
	# Get the crateid for this rocid
	cur.execute('SELECT * FROM Crate WHERE rocid=%d' % rocid)
	res = cur.fetchone()
	if res == None:
		print 'No entry in Crate for rocid=%d! Cannot add module for slot %d. Call AddCrate() first!' % (rocid, slot)
	else:
		crateid = res['crateid']
		if hasattr(slot, "__len__"):
			# slot is list of slots
			for sl in slot:
				cur.execute('INSERT INTO module (crateid,slot,type,SN) VALUES(%d,%d,"%s","%s")' % (crateid,sl,type,SN))
				print 'Added module for rocid=%d  slot=%d type=%s' % (rocid, slot, type)
		else:
			# slot is a single slot
			cur.execute('INSERT INTO module (crateid,slot,type,SN) VALUES(%d,%d,"%s","%s")' % (crateid,slot,type,SN))
			print 'Added module for rocid=%d  slot=%d type=%s' % (rocid, slot, type)
		con.commit()

#----------------------------
# AddDAQChannel
#----------------------------
def AddDAQChannel(rocid, slot, channel, name, system='undefined', module_type='undefined', module_SN='unknown', runrangeids=[1]):
	pass

#----------------------------
# AddDetectorChannel
#----------------------------
def AddDetectorChannel(system, columns, runrangeids):
	pass

#----------------------------
# LinkDetectorToDAQChannel
#----------------------------
def LinkDetectorToDAQChannel(system, columns, runrangeids):
	pass

#----------------------------
# AddChannel
#----------------------------
def AddChannel(system, columns, runrangeids):
	pass

#----------------------------
# RemoveChannel
#----------------------------
def RemoveChannel(system, columns, runrangeids):
	pass

#----------------------------
# GetDAQChannels
#
# Get channel information for specified rocid, slot, channel, and runrangeid.
# If any of the indices are omitted then all channels matching the remaining
# indicies will be returned.
# Values are retuned in the form of a list of dictionaries with each dictionary
# corresponding to a single channel. The channels will be ordered by runrangeid
# first, then rocid, then slot, then channel.
#----------------------------
def GetDAQChannels(rocid='*', slot='*', channel='*', runrangeid='*'):
	sql = 'SELECT * FROM Crate,Module,Channel WHERE Crate.crateid=Module.crateid AND Module.moduleid=Channel.moduleid'
	if rocid!='*':      sql += ' AND rocid=%d' % int(rocid)
	if slot!='*':       sql += ' AND slot=%d' % int(slot)
	if channel!='*':    sql += ' AND channel=%d' % int(channel)
	if runrangeid!='*': sql += ' AND Channel.runrangeid=%d' % int(runrangeid)
	sql = sql + ' ORDER BY Channel.runrangeid DESC, rocid ASC, slot ASC, channel ASC'
	cur.execute(sql)
	s_rows = cur.fetchall()
	colnames = map(lambda x: x[0], cur.description)
	chinfos = []
	for row in s_rows:
	
		# Skip "empty" channels
		if row['system']=='': continue
		
		# Add DAQ-side table info to dictionary
		chinfo = {}
		for colname in colnames: chinfo[colname] = row[colname]

		# Get system-specific indices and add those to dictionary
		indices = GetSystemIndices(row['system'])
		sql = 'SELECT * FROM %s WHERE %s=%s AND runrangeid=%d' % (row['system'], row['col_name'], row['chanid'], row['runrangeid'])	
		cur.execute(sql)
		sys_rows = cur.fetchall()
		sysinfo = {}
		if len(sys_rows) > 0:
			for idx in indices: sysinfo[ idx ] = sys_rows[0][idx]
		
		chinfo['system_indices'] = sysinfo

		chinfos.append(chinfo)
	
	return chinfos

#----------------------------
# GetChannels
#
# Get channels corresponding to the specified system and given indices.
# The variable "indices" should be a dictionary whose length matches the number
# of indices needed for the given system. Wildcards (*) are allowed for
# indices, but they must be a single "*".
# Values are retuned in the form of a list of dictionaries with each dictionary
# corresponding to a single channel. The channels will be ordered by runrangeid
# first, then by each of the system-specific indices.
#----------------------------
def GetChannels(system, indices, runrangeid=-1):
	
	system_indices = GetSystemIndices(system)
	if len(indices) != len(system_indices):
		raise ValueError('System %s requires %d indices but only %d passed' % (system, len(system_indices), len(indices)))

	sql = 'SELECT * FROM Crate,Module,Channel,%s WHERE Crate.crateid=Module.crateid AND Module.moduleid=Channel.moduleid' % system
	if runrangeid>-1: sql += ' AND Channel.runrangeid=%d AND %s.runrangeid=Channel.runrangeid' % (int(runrangeid), system)
	for idx in system_indices:
		if str(indices[idx]) != "*":
			sql += ' AND %s.%s="%s"' % (system, idx, str(indices[idx]) )

	chan_types = GetSystemChanTypes(system)
	if len(chan_types)>0 :
		ctequals = ['Channel.chanid=' + t for t in chan_types]
		sql += ' AND (' + ' OR '.join(ctequals) + ')'
	sql += ' ORDER BY runrangeid DESC'
	for idx in indices: sql += ','+idx+' ASC'

	cur.execute(sql)
	s_rows = cur.fetchall()
	colnames = map(lambda x: x[0], cur.description)
	chinfos = []
	for row in s_rows:
	
		# Skip "empty" channels
		if row['system']=='': continue

		# Add all table info to dictionary
		chinfo = {}
		for colname in colnames: chinfo[colname] = row[colname]

		# Add channel to list
		chinfos.append(chinfo)
	
	return chinfos

#----------------------------
# GetXML
#----------------------------
def GetXML(runrangeid, run):
	pass



#<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>

#===========================================================
#                    initialize
def Initialize():

	global db_filename, con, cur, db_valid

	# Connect to SQLite DB file
	db_filename = 'tt.db'
	print ""
	print "---------------------------------------------"
	print "opening SQLite DB file: %s" % db_filename
	print "---------------------------------------------"
	if not os.path.isfile(db_filename):
		print 'ERROR: SQLite file "%s" does not exist!\n' % db_filename
		sys.exit(0)
		
	con = lite.connect(db_filename)  # Open DB
	con.row_factory = lite.Row       # Specify that next cursor should be "dictionary"
	cur = con.cursor()               # Create Cursor

	db_valid = VerifySchema()

#===========================================================

#<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>