#!/usr/bin/env python
#-*- coding: ISO-8859-1 -*-
#
# Author:  Valentin Kuznetsov, Cornell University, 2006
#

# system modules
import sys, string
#import sqlalchemy.mods.threadlocal
from   sqlalchemy import *

# DBS modules
import dbsException

# DB engines, we use sqlite_db engine as a helper to verify DB schemas
#DBS_ENGINE     = create_engine('sqlite:///mydb', strategy='threadlocal', echo=True)
#DBS_DB         = BoundMetaData(DBS_ENGINE)
#DBS_CONNECTION = DBS_ENGINE.connect()
#DBS_DB         = create_engine('sqlite://mydb', strategy='threadlocal', echo=True)

useEngine = 'oracle'
verbose = True

if useEngine=='sqlite':
   print "Use SQLite engine"
   engine = create_engine('sqlite:///sqlite.db', strategy='threadlocal', echo=verbose)
elif useEngine=='oracle':
   print "Use ORACLE engine"
   account="XXX"
   passwd ="YYY"
   _db    = "devdb10"
   engine = create_engine('oracle://%s:%s@%s'%(account,passwd,_db),
                           strategy='threadlocal', echo=verbose)
elif useEngine=='mysql':
   print "Use MySQL engine"
   account="XXX"
   passwd ="YYY"
   _db    = "devdb10"
   engine = create_engine('mysql://%s:%s@localhost/%s'%(account,passwd,_db),echo=verbose)
else:
   raise "Unsupported DB engine backend"

DBS_DB = engine
DBS_CONNECTION = DBS_DB.connect()

tableList = []
seqList   = []
#
# TABLE DEFINITIONS go here, define your table and check if its schema in DBS_DB is the same
# if necessary create table
#
seq_primary_dataset = Sequence(name='seq_primary_dataset',start=1,increment=True)
t_primary_dataset = Table('t_primary_dataset', DBS_DB,
  Column('id', Integer, Sequence('seq_primary_dataset'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_primary_dataset)
seqList.append(seq_primary_dataset.name)
#
seq_processing_name = Sequence(name='seq_processing_name',start=1,increment=True)
t_processing_name = Table('t_processing_name', DBS_DB,
  Column('id', Integer, Sequence('seq_processing_name'), nullable = False, primary_key = True),
  Column('name', String(100))
)
tableList.append(t_processing_name)
seqList.append(seq_processing_name.name)
#
seq_data_tier = Sequence(name='seq_data_tier',start=1,increment=True)
t_data_tier = Table('t_data_tier', DBS_DB,
  Column('id', Integer, Sequence('seq_data_tier'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_data_tier)
seqList.append(seq_data_tier.name)
#
seq_processed_dataset = Sequence(name='seq_processed_dataset',start=1,increment=True)
t_processed_dataset = Table('t_processed_dataset', DBS_DB,
  Column('id', Integer, Sequence('seq_processed_dataset'), nullable = False, primary_key = True),
  Column('primary_dataset', Integer, ForeignKey("t_primary_dataset.id"), nullable = False),
  Column('data_tier', Integer, ForeignKey("t_data_tier.id"), nullable = False),
  Column('name', Integer, ForeignKey("t_processing_name.id"), nullable = False),
  Column('input', Integer, ForeignKey("t_processed_dataset.id") )
)
tableList.append(t_processed_dataset)
seqList.append(seq_processed_dataset.name)
#
#seq_processing_path = Sequence(name='seq_processing_path',start=1,increment=True)
#t_processing_path = Table('t_processing_path', DBS_DB,
#  Column('id', Integer, Sequence('seq_processing_path'), nullable = False, primary_key = True),
#  Column('parent', Integer, nullable = False),
#  Column('app_config', Integer, nullable = False),
#  Column('data_tier', Integer, nullable = False)
#)
#tableList.append(t_processing_path)
#
seq_app_family = Sequence(name='seq_app_family',start=1,increment=True)
t_app_family = Table('t_app_family', DBS_DB,
  Column('id', Integer, Sequence('seq_app_family'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_app_family)
seqList.append(seq_app_family.name)
#
seq_application = Sequence(name='seq_application',start=1,increment=True)
t_application = Table('t_application', DBS_DB,
  Column('id', Integer, Sequence('seq_application'), nullable = False, primary_key = True),
  Column('executable', String(100), nullable = False),
  Column('app_version', String(100), nullable = False),
  Column('app_family', Integer, ForeignKey("t_app_family.id"), nullable = False)
)
tableList.append(t_application)
seqList.append(seq_application.name)
#
seq_parameter_set = Sequence(name='seq_parameter_set',start=1,increment=True)
t_parameter_set = Table('t_parameter_set', DBS_DB,
  Column('id', Integer, Sequence('seq_parameter_set'), nullable = False, primary_key = True),
  Column('hash', String(100), nullable = False),
  Column('content', String(100), nullable = False)
)
tableList.append(t_parameter_set)
seqList.append(seq_parameter_set.name)
#
seq_app_config = Sequence(name='seq_app_config',start=1,increment=True)
t_app_config = Table('t_app_config', DBS_DB,
  Column('id', Integer, Sequence('seq_app_config'), nullable = False, primary_key = True),
  Column('application', Integer, ForeignKey("t_application.id"), nullable = False),
  Column('parameter_set', Integer, ForeignKey("t_parameter_set.id"), nullable = False)
)
tableList.append(t_app_config)
seqList.append(seq_app_config.name)
#
seq_evcoll_status = Sequence(name='seq_evcoll_status',start=1,increment=True)
t_evcoll_status = Table('t_evcoll_status', DBS_DB,
  Column('id', Integer, Sequence('seq_evcoll_status'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_evcoll_status)
seqList.append(seq_evcoll_status.name)
#
seq_parentage_type = Sequence(name='seq_parentage_type',start=1,increment=True)
t_parentage_type = Table('t_parentage_type', DBS_DB,
  Column('id', Integer, Sequence('seq_parentage_type'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_parentage_type)
seqList.append(seq_parentage_type.name)
#
seq_processing = Sequence(name='seq_processing',start=1,increment=True)
t_processing = Table('t_processing', DBS_DB,
  Column('id', Integer, Sequence('seq_processing'), nullable = False, primary_key = True),
  Column('primary_dataset', Integer, ForeignKey("t_primary_dataset.id"), nullable = False),
  Column('app_config', Integer, ForeignKey("t_app_config.id"), nullable = False),
  Column('name', Integer, ForeignKey("t_processing_name.id"), nullable = False),
  Column('is_open', String(1), nullable = False),
  Column('input', Integer, ForeignKey("t_processing.id"))
)
tableList.append(t_processing)
seqList.append(seq_processing.name)
#
seq_event_collection = Sequence(name='seq_event_collection',start=1,increment=True)
t_event_collection = Table('t_event_collection', DBS_DB,
  Column('id', Integer, Sequence('seq_event_collection'), nullable = False, primary_key = True),
  Column('processed_dataset', Integer, ForeignKey("t_processed_dataset.id"), nullable = False),
  Column('name', String(100), nullable = False),
  Column('events', Integer, nullable = False),
  Column('status', Integer, ForeignKey("t_evcoll_status.id"), nullable = False)
)
tableList.append(t_event_collection)
seqList.append(seq_event_collection.name)
#
seq_evcoll_parentage = Sequence(name='seq_evcoll_parentage',start=1,increment=True)
t_evcoll_parentage = Table('t_evcoll_parentage', DBS_DB,
  Column('id', Integer, Sequence('seq_evcoll_parentage'), nullable = False, primary_key = True),
  Column('parent', Integer, ForeignKey("t_event_collection.id"), nullable = False),
  Column('child', Integer, ForeignKey("t_event_collection.id"), nullable = False),
  Column('type', Integer, ForeignKey("t_parentage_type.id"), nullable = False)
)
tableList.append(t_evcoll_parentage)
seqList.append(seq_evcoll_parentage.name)
#
seq_block_status = Sequence(name='seq_block_status',start=1,increment=True)
t_block_status = Table('t_block_status', DBS_DB,
  Column('id', Integer, Sequence('seq_block_status'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_block_status)
seqList.append(seq_block_status.name)
#
seq_file_status = Sequence(name='seq_file_status',start=1,increment=True)
t_file_status = Table('t_file_status', DBS_DB,
  Column('id', Integer, Sequence('seq_file_status'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_file_status)
seqList.append(seq_file_status.name)
#
seq_block = Sequence(name='seq_block',start=1,increment=True)
t_block = Table('t_block', DBS_DB,
  Column('id', Integer, Sequence('seq_block'), nullable = False, primary_key = True),
  Column('processing', Integer, ForeignKey("t_processing.id"), nullable = False),
  Column('status', Integer, ForeignKey("t_block_status.id"), nullable = False),
  Column('files', Integer, nullable = False),
  Column('bytes', Integer, nullable = False)
)
tableList.append(t_block)
seqList.append(seq_block.name)
#
seq_file_type = Sequence(name='seq_file_type',start=1,increment=True)
t_file_type = Table('t_file_type', DBS_DB,
  Column('id', Integer, Sequence('seq_file_type'), nullable = False, primary_key = True),
  Column('name', String(100), nullable = False)
)
tableList.append(t_file_type)
seqList.append(seq_file_type.name)
#
seq_file = Sequence(name='seq_file',start=1,increment=True)
t_file = Table('t_file', DBS_DB,
  Column('id', Integer, Sequence('seq_file'), nullable = False, primary_key = True),
  Column('guid', String(100)),
  Column('logical_name', String(100), nullable = False),
  Column('checksum', String(100)),
  Column('filesize', Integer),
  Column('status', Integer, ForeignKey("t_file_status.id")),
  Column('type', Integer, ForeignKey("t_file_type.id"), nullable = False),
  Column('inblock', Integer, ForeignKey("t_block.id"), nullable = False),
)
tableList.append(t_file)
seqList.append(seq_file.name)
#
seq_evcoll_file = Sequence(name='seq_evcoll_file',start=1,increment=True)
t_evcoll_file = Table('t_evcoll_file', DBS_DB,
  Column('id', Integer, Sequence('seq_evcoll_file'), nullable = False, primary_key = True),
  Column('evcoll', Integer, ForeignKey("t_event_collection.id"), nullable = False),
  Column('fileid', Integer, ForeignKey("t_file.id"), nullable = False)
)
tableList.append(t_evcoll_file)
seqList.append(seq_evcoll_file.name)

#
# initialize indecies
#
t_primary_dataset_index = Index('uq_primary_dataset_name', t_primary_dataset.c.name, unique=True)

t_processing_name_index = Index('uq_processing_name_name', t_processing_name.c.name, unique=True)
t_data_tier_index = Index('uq_data_tier_name', t_data_tier.c.name,unique=True)
t_processing_index= Index('uq_processing_key',t_processing.c.input,t_processing.c.app_config,unique=True)
# TODO
#alter table t_processing
#  add constraint ck_processing_open
#  check (is_open in ('y', 'n'));


t_processed_dataset_index = Index('uq_processed_dataset_key', t_processed_dataset.c.primary_dataset, 
                                  t_processed_dataset.c.data_tier,t_processed_dataset.c.name, 
                                  unique=True)

t_app_config_index = Index('uq_app_config', t_app_config.c.application, 
                                  t_app_config.c.parameter_set, unique=True)

t_application_index = Index('uq_application_key', t_application.c.executable, 
                                  t_application.c.app_version, t_application.c.app_family,
                                  unique=True)
t_app_family_index = Index('uq_app_family_name', t_app_family.c.name, unique=True)

t_parameter_set_index = Index('uq_parameter_set_hash', t_parameter_set.c.hash, unique=True)

# File indecies
t_block_status_index = Index('uq_block_status_key', t_block_status.c.name, unique=True)
t_file_status_index = Index('uq_file_status_key', t_file_status.c.name, unique=True)
t_file_type_index = Index('uq_file_type', t_file_type.c.name, unique=True)
t_file_index = Index('uq_file_lfn', t_file.c.logical_name, unique=True)
t_evcoll_file_index = Index('uq_evcoll_file_key', t_evcoll_file.c.evcoll,t_evcoll_file.c.fileid, unique=True)
#
ix_block_status = Index('ix_block_status', t_block.c.status)
ix_file_status = Index('ix_file_status', t_file.c.status)
ix_file_type = Index('ix_file_type', t_file.c.type)
ix_file_inblock = Index('ix_file_inblock', t_file.c.inblock)
#
#
#
t_event_collection_index = Index('uq_event_collection_name', t_event_collection.c.processed_dataset,
                                 t_event_collection.c.name, unique=True)
t_evcoll_parentage_index = Index('uq_evcoll_parentage', t_evcoll_parentage.c.parent,
                                 t_evcoll_parentage.c.child, unique=True)
#
ix_processing_primary = Index('ix_processing_primary', t_processing.c.primary_dataset)
ix_processing_name = Index('ix_processing_name', t_processing.c.name)
ix_processed_dataset_tier = Index('ix_processed_dataset_tier', t_processed_dataset.c.data_tier)
ix_evcoll_parentage_type = Index('ix_evcoll_parentage_type', t_evcoll_parentage.c.type)


# Table mappers
class T_PROCESSED_DATASET(object):
   def __init__(self,iPrimName=None,iTier=None,iName=None,iInput=None):
       self.primary_dataset = iPrimName
       self.data_tier = iTier
       self.name = iName
       self.intput = iInput

class T_PRIMARY_DATASET(object):
   def __init__(self,iName=None):
       self.name = iName

class T_PROCESSING_NAME(object):
   def __init__(self,iName=None):
       self.name = iName

class T_PROCESSING(object):
   def __init__(self,iPrimaryDataset=None,iAppConfig=None,iName=None,isOpen=None,iInput=None):
       self.primary_dataset=iPrimaryDataset
       self.app_config = iAppConfig
       self.name = iName
       self.is_open = isOpen
       self.input = iInput

class T_PROCESSING_PATH(object):
   pass

class T_DATA_TIER(object):
   def __init__(self,iName=None):
       self.name = iName

class T_APP_FAMILY(object):
   def __init__(self,iName=None):
       self.name = iName

class T_APPLICATION(object):
   def __init__(self,iExe=None,iVer=None):
       self.executable = iExe
       self.app_version= iVer

class T_APP_CONFIG(object):
   pass

class T_PARAMETER_SET(object):
   def __init__(self,iHash=None,iContent=None):
       self.hash=iHash
       self.content=iContent

class T_FILE(object):
   def __init__(self,guid=None,lName=None,chkSum=None,fSize=None,iStatus=None,iType=None,iBlock=None):
       self.guid         = guid
       self.logical_name = lName
       self.checksum     = chkSum
       self.filesize     = fSize
       self.status       = iStatus
       self.type         = iType
       self.inblock      = iBlock

class T_FILE_TYPE(object):
   pass

class T_BLOCK(object):
   def __init__(self,iProcessing=None,iStatus=None,iFiles=0,iBytes=0):
       self.processing=iProcessing
       self.status=iStatus
       self.files=iFiles
       self.bytes=iBytes

class T_EVCOLL_STATUS(object):
   def __init__(self,iName=None):
       self.name=iName

class T_EVCOLL_FILE(object):
   def __init__(self,iEvtColl=None,iFileId=None):
       self.evcoll=iEvtColl
       self.fileid=iFileId

class T_EVCOLL_PARENTAGE(object):
   pass

class T_PARENTAGE_TYPE(object):
   def __init__(self,iName=None):
       self.name=iName

class T_EVENT_COLLECTION(object):
   def __init__(self,iProc=None,iName=None,iEvt=None,iStatus=None):
       self.processed_dataset=iProc
       self.name=iName
       self.events=iEvt
       self.status=iStatus

#
############ MAPPERS ###############
#
map_tpr   = mapper(T_PROCESSING,t_processing)
map_tpn   = mapper(T_PROCESSING_NAME,t_processing_name,properties={'t_processing':relation(T_PROCESSING)})
map_tac   = mapper(T_APP_CONFIG,t_app_config,properties={'t_processing':relation(T_PROCESSING)})
map_tap   = mapper(T_APPLICATION,t_application,properties={'t_app_config':relation(T_APP_CONFIG)})
map_taf   = mapper(T_APP_FAMILY,t_app_family,properties={'t_application':relation(T_APPLICATION)})
map_tps   = mapper(T_PARAMETER_SET,t_parameter_set,properties={'t_app_config':relation(T_APP_CONFIG)})
map_tpd   = mapper(T_PRIMARY_DATASET,t_primary_dataset)
map_tb    = mapper(T_BLOCK,t_block)
map_tevcf = mapper(T_EVCOLL_FILE,t_evcoll_file)
map_tevcp = mapper(T_EVCOLL_PARENTAGE,t_evcoll_parentage)
map_tf    = mapper(T_FILE,t_file,properties={'t_evcoll_file':relation(T_EVCOLL_FILE)})
map_tevc  = mapper(T_EVENT_COLLECTION,t_event_collection,properties={'t_evcoll_parentage':relation(T_EVCOLL_PARENTAGE),'t_evcoll_file':relation(T_EVCOLL_FILE)})

map_tevcs = mapper(T_EVCOLL_STATUS,t_evcoll_status,properties={'t_event_collection':relation(T_EVENT_COLLECTION)})
map_tpt   = mapper(T_PARENTAGE_TYPE,t_parentage_type,properties={'t_evcoll_parentage':relation(T_EVCOLL_PARENTAGE)})
map_tprd  = mapper(T_PROCESSED_DATASET,t_processed_dataset,properties={'t_event_collection':relation(T_EVENT_COLLECTION)})
map_tdt   = mapper(T_DATA_TIER,t_data_tier,properties={'t_processed_dataset':relation(T_PROCESSED_DATASET)})
#
# main
#
if __name__ == "__main__":
    tableList.reverse()
    for table in tableList:
        print "Drop table",table
        try:
           table.drop()
        except:
           pass
    tableList.reverse()
    for table in tableList:
        try:
           table.create()
        except:
           sys.excepthook(sys.exc_info()[0],sys.exc_info()[1],sys.exc_info()[2])
           raise "Fail to create %s"%table.name