########################################################################
#
# File Name: 	        PostgresManagement.py
#
# Documentation:	http://docs.ftsuite.com/4ODS/Drivers/PostgresManagement.py.html
#
"""
The Postgres 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 _pg
if hasattr(_pg,'disable_notice'):
    _pg.disable_notice()

from Ft.Ods.StorageManager.Adapters import SQL
from Ft.Ods import Exception

class DbManager(SQL.DbManager):

    def connect(self,connectString):
        #Split the connection string
        dbName,hostName,port,userName,passwd = Util.SplitDbConnectString(connectString)
        dbName = 'ft__' + dbName
        try:
            return _pg.connect(dbName, host=hostName, port=port, user=userName, passwd=passwd)
        except _pg.error:
            raise Exception.DatabaseNotFound(connectString)

    def create(self,connString,initRepo = 1):
        dbName,hostName,port,userName,passwd = Util.SplitDbConnectString(connString)
        pg_db_name = "ft__" + dbName
        tmpl_db = _pg.connect('template1',host=hostName, port=port, user=userName, passwd=passwd)
        rt = tmpl_db.query("SELECT datname FROM pg_database where datname='%s'" % pg_db_name).dictresult()
        if len(rt) == 0:
            tmpl_db.query('create database ft__' + dbName)
        


    def exists(self,connString):
        dbName,hostName,port,userName,passwd = Util.SplitDbConnectString(connString)
        pg_db_name = "ft__" + dbName
        tmpl_db = _pg.connect('template1',host=hostName, port=port, user=userName, passwd=passwd)
        rt = tmpl_db.query("SELECT datname FROM pg_database where datname='%s'" % pg_db_name).dictresult()
        if len(rt) != 0:
            db = _pg.connect(pg_db_name,host=hostName, port=port, user=userName, passwd=passwd)
            for tn in db.query("SELECT tablename from pg_tables").getresult():
                if tn[0][:6] == 'ftods_':
                    return 1
            for seq in db.query("SELECT relname from pg_class").getresult():
                if seq[0][:6] == 'ftods_':
                    return 1

        return 0

    def destroy(self,dbName):
        dbName,hostName,port,userName,passwd = Util.SplitDbConnectString(dbName)
        pg_db_name = 'ft__' + dbName
        try:
            db = _pg.connect(pg_db_name,host=hostName, port=port, user=userName, passwd=passwd)
        except _pg.error:
            return
        while 1:
            retry = 0
            for tn in db.query("SELECT tablename from pg_tables").getresult():
                if tn[0][:6] == 'ftods_':
                    try:
                        db.query('DROP TABLE %s' % tn[0])
                    except _pg.error, e:
                        if string.find(str(e),'inherits') != -1:
                            retry = 1
                        else:
                            sys.stderr.write("Unable to drop table %s\n" % tn[0])
                            
            if not retry:
                break
        for seq in db.query("SELECT relname from pg_class").getresult():
            try:
                if seq[0][:6] == 'ftods_':
                    db.query('DROP SEQUENCE %s' % seq[0])
            except _pg.error, e:
                sys.stderr.write("Unable to drop sequence %s\n" % seq[0])
            

    def close(self,conn):
        conn.close()
        

    def reinit(self,connString,adapter):
        SQL.DbManager.reinit(self,connString,adapter)
        dbName,hostName,port,userName,passwd = Util.SplitDbConnectString(connString)
        pg_db_name = 'ft__' + dbName
        db = _pg.connect(pg_db_name,host=hostName, port=port, user=userName, passwd=passwd)
        db.query('VACUUM')
        

    def _createSequence(self,db,name):
        if name == 'ftods_repoid':
            start = " MINVALUE 2 START 2"
        else:
            start = ""
        db.query('CREATE SEQUENCE %s' % name  + start)

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


    def _createIndex(self,db,index_name,columns,table_name):
        iString = 'CREATE INDEX %s  on %s (' % (index_name,table_name)
        for c in columns:
            iString = iString + c
            if c != columns[-1]:
                iString = iString + ','
        iString = iString + ')'
        db.query(iString)


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

        rt = db.query('SELECT _repoid,inherits,extender from ftods_class').getresult()

        allInterfaces = {}
        for rid,inherits,extender in rt:
            rid = int(rid)
            allInterfaces[rid] = []
            if inherits:
                res = db.query('SELECT value from ftods_long_collection where _cid=%s'%inherits).getresult()
                for r in res:
                    allInterfaces[rid].append(int(r[0]))
            if int(extender):
                allInterfaces[rid].append(int(extender))


        rt = db.query('SELECT _repoid,inherits from ftods_interface').getresult()
        for rid,inherits in rt:
            rid = int(rid)
            allInterfaces[rid] = []
            if inherits:
                res = db.query('SELECT value from ftods_long_collection where _cid=%s'%inherits).getresult()
                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:'ftods_interface__%s'%x,tables)


    def _deleteTable(self,db,tableName):
        db.query("DELETE from %s" % tableName)

    def _getAllExtentNames(self,db):
        return db.query('SELECT name,extentid from ftods_extents').getresult()

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

    def _dropAllBindings(self,db):
        db.query("DELETE from ftods_binding")
        
    def _deleteAllBlobs(self, db):
        db.query('DELETE from ftods_blobs')


    odmgToSqlTypes = {
        Constants.Types.BOOLEAN: lambda x:'BOOLEAN',
        Constants.Types.SIGNED_SHORT: lambda x:'INT4',
        Constants.Types.STRING: lambda x:'text',
        Constants.Types.UNSIGNED_SHORT: lambda x:'INT4',
        Constants.Types.SIGNED_LONG: lambda x:'INT8',
        Constants.Types.UNSIGNED_LONG: lambda x:'INT8',
        Constants.Types.SIGNED_LONG_LONG: lambda x:'NUMERIC(40,0)',
        Constants.Types.FLOAT: lambda x:'FLOAT4',
        Constants.Types.DOUBLE: lambda x:'FLOAT8',
        Constants.Types.ROBJECT: lambda x:'INT8',
        Constants.Types.POBJECT: lambda x:'INT8',
        Constants.Types.ENUMERATION: lambda x:'INT4',
        Constants.Types.STRUCTURE: lambda x:'text',
        Constants.Types.UNION: lambda x:'text',
        Constants.Types.BLOB:lambda x:'INT8',

        Constants.Types.CHAR:lambda x:'char',
        Constants.Types.OCTET:lambda x:'INT2',

        Constants.Types.DATE: lambda x:'text',
        Constants.Types.TIME: lambda x:'text',
        Constants.Types.INTERVAL:  lambda x:'text',
        Constants.Types.TIMESTAMP: lambda x:'text',

        Constants.Types.SET_COLLECTION:lambda x:'INT8',
        Constants.Types.LIST_COLLECTION:lambda x:'INT8',
        Constants.Types.BAG_COLLECTION:lambda x:'INT8',
        Constants.Types.DICTIONARY_COLLECTION:lambda x:'INT8',
        Constants.Types.FIXEDSTRING: lambda x:'varchar(%d)'%x.max_size.value(),
    }


    systemTypesToSql = {SQL.DbManager.SystemTypes.NAME:"VARCHAR(128)",
                        SQL.DbManager.SystemTypes.OBJECT:'INT8',
                        SQL.DbManager.SystemTypes.ENUMERATION:'INT4',
                        SQL.DbManager.SystemTypes.STRING:'text',
                        SQL.DbManager.SystemTypes.BOOLEAN:'boolean',
                        SQL.DbManager.SystemTypes.SHORT:'INT4',
                        SQL.DbManager.SystemTypes.STRING:'text',
                        SQL.DbManager.SystemTypes.DOUBLE:'FLOAT8',
                        SQL.DbManager.SystemTypes.TIMESTAMP:'FLOAT8',
                        SQL.DbManager.SystemTypes.ANY:'text',
                        }

    tables = SQL.DbManager.tables.copy()

    tables['ftods_blobs'] = {SQL.DbManager.TableData.TABLE_INIT:[('bid',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                 ('bindex',SQL.DbManager.SystemTypes.OBJECT,''),
                                                                 ('bdata',SQL.DbManager.SystemTypes.STRING,''),
                                                                 ],
                              SQL.DbManager.TableData.TABLE_ACCESS:SQL.DbManager.AccessTypes.OBJECT_ACCESS,
                              SQL.DbManager.TableData.TABLE_INHERITS:[]
                              }
    SQL.SManager.g_sortedTableNames.append('ftods_blobs')

    sequences = SQL.DbManager.sequences.copy()
    sequences['ftods_blobid'] = SQL.DbManager.AccessTypes.OBJECT_ACCESS

