########################################################################
#
# File Name: 	        OracleManagement.py
#
# Documentation:	http://docs.ftsuite.com/4ODS/Drivers/OracleManagement.py.html
#
"""
The Oracle Database Management functions
WWW: http://4suite.org/4ODS         e-mail: support@4suite.org

Copyright (c) 1999 Fourthought, Inc, USA.   All Rights Reserved.
See  http://4suite.org/COPYRIGHT  for license and copyright information
"""

import string,os, sys

from Ft.Ods.StorageManager.Adapters import Util
from Ft.Ods.StorageManager.Adapters import Constants
import DCOracle

from Ft.Ods.StorageManager.Adapters import SQL
from DCOracle import ociUtil

class DbManager(SQL.DbManager):

    def connect(self,connectString):
        dbName,hostName,port,userName,passwd = Util.SplitDbConnectString(connectString)
        if dbName: oracleConnect = "%s/%s@%s" % (userName,passwd,dbName)
        else: oracleConnect = "%s/%s" % (userName,passwd)
        db = DCOracle.Connect(oracleConnect)
        return [db,db.cursor(),connectString]


    def create(self,connString,initRepo = 1):
        pass
        


    def exists(self,connString):
        # validate connection string
        try:
            db = self.connect(connString) # test connection
        except:
            (etype, value, tb) =sys.exc_info()
            if etype in (DCOracle.error, 'OracleError'): return 0
            else: raise etype, value, tb

        # check for ODS system tables
        tables =map(lambda x: string.lower(x[0]),
                    filter(lambda x: x[1] =='TABLE', db[0].objects()))
        db[0].close()
        for table_name in SQL.Manager.g_sortedTableNames:
            if table_name not in tables: return 0
        collection_map = self.collectionTableMapping
        for table_name,table_type in collection_map.values():
            if table_name not in tables: return 0
        # all ODS tables found.
        return 1

    def destroy(self,connString):
        dbTuple = self.connect(connString)
        db,cursor,st = dbTuple

        table_names = []
        try:
            cursor.execute("SELECT repoid from ftods_class")
            rt = cursor.fetchall()
            table_names = table_names + map(lambda x: "interface__%s" % x[0],rt)
        except ociUtil.error, e:
            sys.stderr.write("Unable to get generated tables!\n")

        try:
            cursor.execute("SELECT repoid from ftods_interface")
            rt = cursor.fetchall()
            table_names = table_names + map(lambda x: "interface__%s" % x[0],rt)
        except ociUtil.error, e:
            sys.stderr.write("Unable to get generated tables!\n")

        for table_name in table_names:
            try:
                db.execute('DROP TABLE %s' % table_name)
            except ociUtil.error, e:
                sys.stderr.write("Unable to drop table %s!\n" % table_name)
        

        for seq in self.sequences.keys():
            try:
                db.execute('DROP SEQUENCE %s' % seq)
            except ociUtil.error, e:
                sys.stderr.write("Unable to drop sequence %s\n" % seq)
        

        for table_name in SQL.Manager.g_sortedTableNames:
            try:
                db.execute('DROP TABLE %s' % table_name)
            except ociUtil.error, e:
                sys.stderr.write("Unable to drop table %s!\n" % table_name)

        doneTables = []
        for table_name,table_type in self.collectionTableMapping.values():
            if table_name in doneTables:
                continue
            doneTables.append(table_name)
            try:
                db.execute('DROP TABLE %s' % table_name)
            except ociUtil.error, e:
                sys.stderr.write("Unable to drop table %s!\n" % table_name)


    def close(self,conn):
        pass
        

    def _createSequence(self,db,name):
        if name == 'ftods_repoid':
            start = " START WITH 2"
        else:
            start = ""
        db[0].execute('CREATE SEQUENCE %s' % name  + start)

    def _resetSequence(self,db,name):
        raise "Finish"
        db.query("SELECT SETVAL('%s',1)" % name)
        


    def _createTable(self,db,table_name,initData,inherits):
        st = "CREATE TABLE %s (" % table_name
        st = st + self._tableInitString(table_name,initData,inherits) + ')'
        db[0].execute(st)

    def _dropTable(self,db,tableName):
        db[0].execute("DROP TABLE %s" % tableName)

    def _createIndex(self,db,index_name,columns,table_name):
        iString = 'CREATE INDEX %s  on %s (' % (index_name,table_name)
        for c in columns:
            if c == '_cid':
                newC = 'cid'
            elif c == 'index':
                newC = 'rindex'
            else:
                newC = c
            iString = iString + newC
            if c != columns[-1]:
                iString = iString + ','
        iString = iString + ')'
        db[0].execute(iString)


    def _getAllGeneratedTables(self,db):
        table_names = []

        db[1].execute('SELECT repoid,inherits,extender from ftods_class')
        rt = db[1].fetchall()

        allInterfaces = {}
        for rid,inherits,extender in rt:
            rid = int(rid)
            allInterfaces[rid] = []
            if inherits:
                db[1].execute('SELECT rvalue from ftods_long_collection where cid=%s'%inherits)
                res = db[1].fetchall()
                for r in res:
                    allInterfaces[rid].append(int(r[0]))
            if extender and int(extender):
                allInterfaces[rid].append(int(extender))


        db[1].execute('SELECT repoid,inherits from ftods_interface')
        rt = db[1].fetchall()

        for rid,inherits in rt:
            rid = int(rid)
            allInterfaces[rid] = []
            if inherits:
                db[1].execute('SELECT rvalue from ftods_long_collection where cid=%s'%inherits)
                res = db[1].fetchall()
                for r in res:
                    allInterfaces[rid].append(int(r[0]))

        tables = []
        for rid,derived in allInterfaces.items():
            curIndex = len(tables)
            for d in derived:
                if d in tables:
                    curIndex = tables.index(d)
            tables.insert(curIndex,rid)

        return map(lambda x:'interface__%s'%x,tables)


    def _deleteTable(self,db,tableName):
        db[0].execute("DELETE from %s" % tableName)

    def _getAllExtentNames(self,db):
        db[1].execute('SELECT name,extentid from ftods_extents')
        return db[1].fetchall()

    def _deleteExtent(self,db,id):
        db[0].execute("DELETE from ftods_extentmapping where extentid = '%s'"%id)
        db[0].execute("DELETE from ftods_extents where extentid = '%s'"%id)

    def _dropAllBindings(self,db):
        db[0].execute("DELETE from ftods_binding")


    def _deleteAllBlobs(self,db):
        db[1].execute("SELECT fileName from ftods_blobs")
        rt = db[1].fetchall()
        for fileName in rt:
            os.unlink(fileName[0])
        db[0].execute("DELETE from ftods_blobs")
        



    def _4ods_interfaceTableInitString(self,data):
        tableName,defs,bClasses = data
        st = ""
        for b in bClasses:
            st = st + self._4ods_interfaceTableInitString(b)

        for (rid,type_,other) in defs:
            if rid != 'oid':
                rid = 'def__%i' % rid
            st = st + rid + ' ' + type_ + ' ' + other
            st = st + ','


        return st


    #Repository Object Helpers
    def _createInterfaceStorage(self,db,data):
        tableName,defs,bClasses = data
        qstr = 'CREATE TABLE %s ( ' % tableName
        qstr = qstr + self._4ods_interfaceTableInitString(data)
        qstr = qstr [:-1] + ')'
        try:
            db[0].execute(qstr)
        except:
            print qstr
            raise







    odmgToSqlTypes = {
        # in Postgres adapter, IDs are 64-bit integers representable by 20 digits
        # short is int4 (32 bits, at most 10 digits)
        Constants.Types.BOOLEAN: 'NUMBER(1,0)',
        Constants.Types.BOOLEAN_BAG: 'NUMBER(20,0)',
        Constants.Types.BOOLEAN_SET: 'NUMBER(20,0)',
        Constants.Types.BOOLEAN_LIST: 'NUMBER(20,0)',
        Constants.Types.SIGNED_SHORT: 'NUMBER(15,0)',
        Constants.Types.SIGNED_SHORT_BAG: 'NUMBER(20,0)',
        Constants.Types.SIGNED_SHORT_LIST: 'NUMBER(20,0)',
        Constants.Types.SIGNED_SHORT_SET: 'NUMBER(20,0)',
        Constants.Types.STRING: 'VARCHAR(2048)',
        Constants.Types.STRING_BAG: 'NUMBER(20,0)',
        Constants.Types.STRING_SET: 'NUMBER(20,0)',
        Constants.Types.STRING_LIST: 'NUMBER(20,0)',
        Constants.Types.UNSIGNED_SHORT: 'NUMBER(10,0)',
        Constants.Types.UNSIGNED_SHORT_BAG: 'NUMBER(20,0)',
        Constants.Types.UNSIGNED_SHORT_SET: 'NUMBER(20,0)',
        Constants.Types.UNSIGNED_SHORT_LIST: 'NUMBER(20,0)',
        Constants.Types.SIGNED_LONG: 'NUMBER(20,0)',
        Constants.Types.SIGNED_LONG_BAG: 'NUMBER(20,0)',
        Constants.Types.SIGNED_LONG_SET: 'NUMBER(20,0)',
        Constants.Types.SIGNED_LONG_LIST: 'NUMBER(20,0)',
        Constants.Types.UNSIGNED_LONG: 'NUMBER(20,0)',
        Constants.Types.UNSIGNED_LONG_BAG: 'NUMBER(20,0)',
        Constants.Types.UNSIGNED_LONG_SET: 'NUMBER(20,0)',
        Constants.Types.UNSIGNED_LONG_LIST: 'NUMBER(20,0)',
        # XXX long longs (128 bits) can not be stored in Oracle NUMBER column
        #     because number precision is limited with 38 digits (up to 126 bit ints).
        Constants.Types.SIGNED_LONG_LONG: 'VARCHAR(40)',
        Constants.Types.SIGNED_LONG_LONG_BAG: 'NUMBER(20,0)',
        Constants.Types.SIGNED_LONG_LONG_SET: 'NUMBER(20,0)',
        Constants.Types.SIGNED_LONG_LONG_LIST: 'NUMBER(20,0)',
        # Postgres adapter uses float4 (up to 6 digits) for float
        # XXX Constants.Types.FLOAT: 'NUMBER(12,6)',
        Constants.Types.FLOAT: 'NUMBER(15,7)',
        Constants.Types.FLOAT_BAG: 'NUMBER(20,0)',
        Constants.Types.FLOAT_SET: 'NUMBER(20,0)',
        Constants.Types.FLOAT_LIST: 'NUMBER(20,0)',
        # Postgres adapter uses float8 (up to 18 digits) for double
        # XXX Constants.Types.DOUBLE: 'NUMBER(36,18)',
        Constants.Types.DOUBLE: 'NUMBER(30,15)',
        Constants.Types.DOUBLE_BAG: 'NUMBER(20,0)',
        Constants.Types.DOUBLE_SET: 'NUMBER(20,0)',
        Constants.Types.DOUBLE_LIST: 'NUMBER(20,0)',
        Constants.Types.OBJECT: 'NUMBER(20,0)',
        Constants.Types.OBJECT_BAG: 'NUMBER(20,0)',
        Constants.Types.OBJECT_SET: 'NUMBER(20,0)',
        Constants.Types.OBJECT_LIST: 'NUMBER(20,0)',
        Constants.Types.ENUMERATION: 'NUMBER(15,0)',
        Constants.Types.STRUCTURE: 'NUMBER(20,0)',
        Constants.Types.DICTIONARY:'NUMBER(20,0)',
        Constants.Types.BLOB:'NUMBER(20,0)',
        }


    systemTypesToSql = {SQL.DbManager.SystemTypes.NAME:"VARCHAR(128)",
                        SQL.DbManager.SystemTypes.OBJECT:odmgToSqlTypes[Constants.Types.OBJECT],
                        SQL.DbManager.SystemTypes.ENUMERATION:odmgToSqlTypes[Constants.Types.ENUMERATION],
                        SQL.DbManager.SystemTypes.STRING:odmgToSqlTypes[Constants.Types.STRING],
                        SQL.DbManager.SystemTypes.BOOLEAN:odmgToSqlTypes[Constants.Types.BOOLEAN],
                        SQL.DbManager.SystemTypes.SHORT:odmgToSqlTypes[Constants.Types.UNSIGNED_SHORT],
                        SQL.DbManager.SystemTypes.STRING:odmgToSqlTypes[Constants.Types.STRING],
                        SQL.DbManager.SystemTypes.DOUBLE:odmgToSqlTypes[Constants.Types.DOUBLE],
                        SQL.DbManager.SystemTypes.TIMESTAMP:odmgToSqlTypes[Constants.Types.DOUBLE],
                        SQL.DbManager.SystemTypes.ANY:odmgToSqlTypes[Constants.Types.STRING],
                        }



    tables = SQL.DbManager.tables.copy()

    tables['ftods_actualclass']={SQL.DbManager.TableData.TABLE_INIT:[('oid',SQL.DbManager.SystemTypes.OBJECT,'PRIMARY KEY'),
                                                                     ('pythonclassid',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                     ('lastUpdate',SQL.DbManager.SystemTypes.TIMESTAMP,''),
                                                                     ],
                                 SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.OBJECT_ACCESS,
                                 SQL.DbManager.TableData.TABLE_INHERITS:[]
                                 }
    tables['ftods_collectionclass']={SQL.DbManager.TableData.TABLE_INIT:[('cid',SQL.DbManager.SystemTypes.OBJECT,'PRIMARY KEY'),
                                                                         ('pythonclassid',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                         ('subtype',SQL.DbManager.SystemTypes.ENUMERATION,''),
                                                                         ('lastUpdate',SQL.DbManager.SystemTypes.TIMESTAMP,''),
                                                                         ],
                                     SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.OBJECT_ACCESS,
                                     SQL.DbManager.TableData.TABLE_INHERITS:[]
                                     }
    tables['ftods_object']={SQL.DbManager.TableData.TABLE_INIT:[('oid',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                ],
                            SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.OBJECT_ACCESS,
                            SQL.DbManager.TableData.TABLE_INHERITS:[]
                        }
    tables['ftods_collection']={SQL.DbManager.TableData.TABLE_INIT:[('cid',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                    ],
                                SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.SYSTEM_ACCESS,
                                SQL.DbManager.TableData.TABLE_INHERITS:[]
                                }

    tables['ftods_literal']={SQL.DbManager.TableData.TABLE_INIT:[('lid',SQL.DbManager.SystemTypes.OBJECT,'PRIMARY KEY'),
                                                                 ],
                             SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.OBJECT_ACCESS,
                             SQL.DbManager.TableData.TABLE_INHERITS:[]
                             }
    tables['ftods_repositoryobject']={SQL.DbManager.TableData.TABLE_INIT:[('repoid',SQL.DbManager.SystemTypes.OBJECT,'PRIMARY KEY'),
                                                                          ('meta_kind',SQL.DbManager.SystemTypes.ENUMERATION,''),
                                                                          ],
                                      SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.METADATA_ACCESS,
                                      SQL.DbManager.TableData.TABLE_INHERITS:[]
                                      }
    tables['ftods_metaobject']={SQL.DbManager.TableData.TABLE_INIT:[('rname',SQL.DbManager.SystemTypes.STRING,''),
                                                                    ('rcomment',SQL.DbManager.SystemTypes.STRING,''),
                                                                    ('definedIn',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                    ],
                                SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.METADATA_ACCESS,
                                SQL.DbManager.TableData.TABLE_INHERITS:["ftods_repositoryobject"]
                                }

    tables['ftods_specifier'] = {SQL.DbManager.TableData.TABLE_INIT:[('rname',SQL.DbManager.SystemTypes.STRING,''),
                                                                     ('rtype',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                     ],
                                 SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.METADATA_ACCESS,
                                 SQL.DbManager.TableData.TABLE_INHERITS:["ftods_repositoryobject"]
                                 }


    tables['ftods_constant']={SQL.DbManager.TableData.TABLE_INIT:[('the_value',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                  ('rtype',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                  ('referenced_by',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                  ('enumeration',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                  ],
                              SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.METADATA_ACCESS,
                              SQL.DbManager.TableData.TABLE_INHERITS:["ftods_metaobject"]
                          }

    tables['ftods_property']={SQL.DbManager.TableData.TABLE_INIT:[('rtype',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                  ],
                              SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.METADATA_ACCESS,
                              SQL.DbManager.TableData.TABLE_INHERITS:["ftods_metaobject"]
                         }

    tables['ftods_const_operand']={SQL.DbManager.TableData.TABLE_INIT:[('references',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                       ],
                                   SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.METADATA_ACCESS,
                                   SQL.DbManager.TableData.TABLE_INHERITS:["ftods_operand"]
                                   }


    tables['ftods_blobs'] = {SQL.DbManager.TableData.TABLE_INIT:[('bid',SQL.DbManager.SystemTypes.OBJECT,'PRIMARY KEY'),
                                                                 ('filename',SQL.DbManager.SystemTypes.STRING,''),
                                                                 ],
                              SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.OBJECT_ACCESS,
                              SQL.DbManager.TableData.TABLE_INHERITS:[]
                              }
    SQL.Manager.g_sortedTableNames.append('ftods_blobs')


    collectionTableData = [('rindex',SQL.DbManager.SystemTypes.SHORT,''),
                           ['rvalue',"PLACE HOLDER",''],
                           ]

    sequences = SQL.DbManager.sequences.copy()
    sequences['ftods_blobId'] = SQL.DbManager.AccessTypes.SYSTEM_ACCESS
