# Copyright (c) 2012-2021 VMware, Inc. All rights reserved.
# VMware Confidential
'''
VCDB helper database methods
'''

import logging
import base64
import sys
import re
import os
from subprocess import Popen, PIPE, call
from os import path

from vcdb.const import (
   XML_CFG_FILE, SYMKEY_FILE, VPXD_CFG_FILE, PYTHON, OPEN_SSL, PATCH_OUT_LOG,
   PATCH_ERR_LOG, RUI_KEY_FILE, LIBODBC_SO_2, DBC_UPGRADE_BINARY, DBC_UPGRADE_FOLDER,
   LD_PATH, PSQL, B2B_PATCH_FILE_7,B2B_PATCH_FILE_8, PRE_PATCH_OUT_LOG, PRE_PATCH_ERR_LOG,
   FI_SQL_FILE, MIN_SUPPORTED_B2B, DB_VPX_VERSION_67)

import os_utils # pylint: disable=E0401
from l10n import msgMetadata as _T, localizedString as _ # pylint: disable=E0401
from patch_errors import PermanentError # pylint: disable=E0401

PY2 = sys.version_info[0] == 2

LOGGER = logging.getLogger(__name__)

LOGGING_DIR = '/var/log/vmware/applmgmt'
LOG_OUT = path.join(LOGGING_DIR, PATCH_OUT_LOG)
LOG_ERR = path.join(LOGGING_DIR, PATCH_ERR_LOG)
PRE_LOG_OUT = path.join(LOGGING_DIR, PRE_PATCH_OUT_LOG)
PRE_LOG_ERR = path.join(LOGGING_DIR, PRE_PATCH_ERR_LOG)

POSTGRES_PORT = '5432'
TABLESP_PERCENT = 0.05

def get_db_user_linux():
   '''
   Get vc database user name from source vpxd.cfg

   @return: user name
   '''
   pipe1 = Popen([XML_CFG_FILE, "-f", VPXD_CFG_FILE, "get", "/config/registry/DB/key_2"],
                 stdout=PIPE)
   db_user = pipe1.communicate()[0].strip()
   pipe1.stdout.close()
   return db_user

def get_db_pass_linux_ruikey():
   '''
   Get vc database user password from vpxd.cfg using rui.key
   This implementation is used for vCSA 5.5 source.

   @return: password
   '''
   key3_cmd = [PYTHON, XML_CFG_FILE,
               "-f", VPXD_CFG_FILE,
               "get", "/config/registry/DB/key_3"]
   py_cmd = "import sys\n"\
            "import base64\n"\
            "data = sys.stdin.read()\n"\
            "data = data[1:]\n"\
            "decBase64 = base64.b64decode(data)\n"\
            "data_len = len(decBase64)\n"\
            "for i in range(data_len):\n"\
            "\n\tsys.stdout.write(decBase64[data_len-i-1])"
   ssl_cmd = [OPEN_SSL, "rsautl", "-inkey", RUI_KEY_FILE, "-pkcs", "-decrypt"]

   pipe1 = Popen(key3_cmd, stdout=PIPE)
   pipe2 = Popen([PYTHON, "-c", py_cmd], stdin=pipe1.stdout, stdout=PIPE)
   pipe1.stdout.close()
   pipe3 = Popen(ssl_cmd, stdin=pipe2.stdout, stdout=PIPE)
   pipe2.stdout.close()
   db_pass = pipe3.communicate()[0].strip()
   pipe3.stdout.close()
   return db_pass

def get_db_pass_symkey():
   '''
   Get vc database user password from vpxd.cfg using symkey.dat
   This implementation is used for vCSA 5.5 source.

   @return: password
   '''
   db_pass = ""
   key3_cmd = [XML_CFG_FILE, "-f", VPXD_CFG_FILE, "get", "/config/registry/DB/key_3"]

   key3_val = os_utils.getCommandOutput(key3_cmd).strip()[1:]
   db_pass = decrypt_key3(get_symkey(), key3_val)
   return db_pass

def decrypt_key3(symkey_hexstring, encrypted_base64):
   '''
   Decrypt password using symkey and encrypted value.

   @symkey_hexstring: symkey.dat contents
   @encrypted_base64: stripped key_3 value from vpxd.cfg
   @return: decrypted password
   '''
   from Crypto.Cipher import AES # pylint: disable=F0401

   # On Python2 you can decode strings as hex, on Py3+ bytes has that option
   if PY2:
      symkey = symkey_hexstring.decode('hex')
   else:
      symkey = bytes.fromhex(symkey_hexstring)
   encrypted_raw = base64.b64decode(encrypted_base64)
   assert (len(encrypted_raw) > 16), "Wrong encrypted string length."
   ciphertext = encrypted_raw[16:]
   ivs = encrypted_raw[0:16]
   cipher = AES.new(symkey, AES.MODE_CBC, ivs)
   try:
      padded_plaintext = cipher.decrypt(ciphertext)
   except Exception as exc:
      LOGGER.error("Failed to decrypt password: %s", exc)
      error = _(_T('vcdb.error.decrypt.password', "Failed to decrypt password: %s"),
                exc)
      raise PermanentError(cause=error)
   #padded_plaintext in Py3+ is bytes thus -1 is a number, in Py2 need to cast it to one
   padding_len = padded_plaintext[-1]
   if PY2:
      padding_len = ord(padding_len)
   plaintext = padded_plaintext[:-padding_len]
   return plaintext

def get_symkey():
   '''
   Read encryption hex string from symkey.dat

   @return: encryption key
   '''
   with open(SYMKEY_FILE) as fhd:
      content = fhd.readlines()
   content_str = content[0].strip()
   symkey_length = len(content_str)
   if symkey_length != 64:
      LOGGER.error("Unexpected symkey length: %d", symkey_length)
      error = _(_T('vcdb.error.symkey.length', "Unexpected symkey length: %d"),
                symkey_length)
      raise PermanentError(cause=error)
   return content_str

def log_std(std, file_path):
   '''
   Write stdout/err output into log file.

   @std: stdout/err output.
   @file_path: log file path
   '''
   with open(file_path, "a+") as fhd:
      for line in std.splitlines():
         fhd.write("%s\n" % line)


def log_is_db_error(text):
   """
   Check for error indicators in the log text.
   """
   error_db_list = set([u"error", u"panic",
                        u"odbc", u"fatal"])

   return any(err in text.lower() for err in error_db_list)


def patch_db(vc_user, vc_pass):
   '''
   Run dbcupgrade tool to patch DB
   '''

   if not path.islink(LIBODBC_SO_2):
      cmd = ["ln", "-s", "/usr/lib64/libodbc.so.1", LIBODBC_SO_2]
      ret = os_utils.getCommandExitCode(cmd)
      if ret != 0:
         LOGGER.error("Error during execution: %s", cmd)
         error = _(_T('vcdb.db.error.link', 'Error during link libodbc.so.1.'))
         sugg_action = _(_T('vcdb.db.resolution.link',
                            'Check that libodbc.so.1 exists.'))
         raise PermanentError(cause=error, resolution=sugg_action)

   with open(LOG_OUT,  "a+") as FILE_OUT:
      with open(LOG_ERR, "w") as FILE_ERR:
         err, vc_ver = get_db_vpx_version (FILE_OUT, FILE_ERR)
         if err != 0:
            return

   cmd_patch = [DBC_UPGRADE_BINARY,
                "-u", base64.b64encode(vc_user),
                "-p", base64.b64encode(vc_pass),
                "-b", str(vc_ver),
                "-s", "0",
                "-y",
                "-t", "postgresql"]
   b2b_patch = 0
   stdout, stderr, ret = os_utils.executeCommand(cmd_patch,
                                                 cwd=DBC_UPGRADE_FOLDER,
                                                 env={"LD_LIBRARY_PATH":LD_PATH})
   log_std(stdout, LOG_OUT)
   log_std(stderr, LOG_ERR)

   with open(LOG_ERR) as f_h:
      for line in f_h:
          if log_is_db_error(line):
             b2b_patch = 1

   if ret != 0 or b2b_patch > 0:
      LOGGER.error("ERROR dbcupgrade")
      error = _(_T('vcdb.db.error.text',
                   'Error during patch VCDB. See vcdb_patch.err.'))
      sugg_action = _(_T('vcdb.db.resolution.text',
                         'Patch DB is failed. Please check vcdb_patch.log'))
      raise PermanentError(cause=error, resolution=sugg_action)

   try:
      cmd = ["unlink", LIBODBC_SO_2]
      os_utils.getCommandExitCode(cmd)
   except Exception: # pylint: disable=W0703
      LOGGER.info("Error during unlink %s", LIBODBC_SO_2)

def psql_query(query, user_name="postgres", database="VCDB"):
   '''
   Construct psql query to DB
   '''

   return [PSQL, "-U", user_name, "-p",
           POSTGRES_PORT, "-d", database, "-q", "-t", "-c", query]

def db_query(run_q, FILE_OUT,FILE_ERR):
   '''
   This is common function.
   Returns result from execution of psql query (run_q) as string
   '''
   val=""
   result_file = path.join(LOGGING_DIR, "result_file.out")

   if os.path.isfile(result_file):
      os.remove(result_file)
   #sql_cmd = "\\COPY (" + run_q + ") TO " + result_file
   sql_cmd = '\COPY ({0}) TO {1}'.format(run_q, result_file)
   ret = call(psql_query(sql_cmd),
	       stdout=FILE_OUT, stderr=FILE_ERR)
   if ret != 0:
      LOGGER.info("Failed to execute query: %s", psql_query)

   with open(result_file, "r") as flz:
      val = flz.readline()

   if os.path.isfile(result_file):
      os.remove(result_file)
   return ret, val

def get_db_vpx_version (FILE_OUT,FILE_ERR):
   vc_ver = ""
   ret, vc_ver = db_query ('SELECT VER_ID FROM VPX_VERSION', FILE_OUT, FILE_ERR)
   if ret !=0:
      LOGGER.info("Error during read DB version.See vcdb_pre_patch.err", table_type)

   #In 6.7GA release VPX_VERSION.VER is 670 on fresh install and upgrade and is 663 on B2B
   if vc_ver >= "663":
      vc_ver = DB_VPX_VERSION_67
   LOGGER.info("vCenter version: %s",vc_ver)
   return ret, int(vc_ver)


def parse_b2b_upgrade( FILE_OUT,FILE_ERR):
   '''
   parse lines SELECT 'patch:65056:table:VPX_NON_ORM_VM_CONFIG_INFO:p_size:350:t_size:500';
   to INSERT INTO VPX_PATCH_DISK_REQ
      values (65056,'VPX_NON_ORM_VM_CONFIG_INFO', 350, 500, 200)
   last values is line number of 'patch:65056:table:VPX_NON_ORM_VM_CONFIG_INFO:size:350'
   in b2b_PostgreSQL.sql file
   and execute the statement
   VPX_PATCH_DISK_REQ contains all patch version numbers in b2b_PostgreSQL.sql file
   '''
   ret = 0
   regex = re.compile('SELECT \'patch:[0-9]{5}:table:[A-Z_]+:p_size:[0-9]+:t_size:[0-9]+[\']')
   err, vc_ver = get_db_vpx_version (FILE_OUT, FILE_ERR)
   if err != 0:
      return 1

   b2b_patch_files = []
   if int(vc_ver) > int(MIN_SUPPORTED_B2B) and int(vc_ver) < int(DB_VPX_VERSION_67):
      b2b_patch_files.append(B2B_PATCH_FILE_7)
   if int(vc_ver) >= int(DB_VPX_VERSION_67):
      b2b_patch_files.append(B2B_PATCH_FILE_8)

   for b2b_file in b2b_patch_files:
      LOGGER.info("FILE: %s", b2b_file)
      with open(b2b_file, "r") as flz:
         for line_no, line in enumerate(flz, 1):
            if regex.search( line ):
               ins_patch = line.replace ('SELECT \'patch:','INSERT INTO VPX_PATCH_DISK_REQ (PATCH_VERSION,TABLE_NAME,P_ROW_SIZE, T_ROW_SIZE,  LINE_NUMBER) values (')
               ins_patch = ins_patch[:-3]
               ins_patch = ins_patch.replace (':table:',',\'')
               ins_patch = ins_patch.replace (':p_size:','\',')
               ins_patch = ins_patch.replace (':t_size:',',')
               ins_patch = ins_patch + ',' + str(line_no)+ ');'
               ret = call(psql_query(ins_patch), stdout=FILE_OUT, stderr=FILE_ERR)
   return ret

def calc_disk_req(table_type, FILE_OUT, FILE_ERR):
   '''
   Calculate deruired disk space using:
     sum(permanent_space_per_statement_per_row * row_count)
	 + max(transitory_space_per_statement_per_row * row_count)

   '''
   ret = 0
   incl = ""
   if table_type == "core":
      incl = "not"


   sql_cmd = "SELECT COALESCE(SUM(n_live_tup * dr.p_row_size),0)/(1024*1024)" \
             " + COALESCE(MAX(n_live_tup * dr.t_row_size),0)/(1024*1024)" \
             " FROM pg_stat_user_tables md " \
             " join VPX_PATCH_DISK_REQ dr on upper(md.relname) = upper(dr.table_name)" \
             " WHERE upper(dr.table_name) {0} like '%HIST_STAT%' " \
             " OR upper(dr.table_name) {1} like '%EVENT%' ".format(incl, incl)

   ret, disk_req = db_query (sql_cmd, FILE_OUT, FILE_ERR)
   if ret !=0:
      LOGGER.info("Error during calculation of %s disk requirement.See vcdb_pre_patch.err", table_type)
      return ret, 0

   disk_req = float(disk_req)
   return ret, disk_req

def calc_tablespace_size(table_type, FILE_OUT, FILE_ERR):
   '''
   Get percent (TABLESP_PERCENT) of tablespace disk size and
   add it to result from calc_disk_req()
   '''
   ret = 0
   incl = ""
   if table_type == "core":
      sql_cmd = "SELECT {0}*(pg_tablespace_size('pg_default'))/(1024*1024)".format(TABLESP_PERCENT)
   else:
      sql_cmd = " SELECT {0}*(pg_tablespace_size('event')" \
                " + pg_tablespace_size('task')" \
                " + pg_tablespace_size('hs1')" \
                " + pg_tablespace_size('hs2')" \
                " + pg_tablespace_size('hs3')" \
                " + pg_tablespace_size('hs4'))/(1024*1024)".format(TABLESP_PERCENT)

   ret, table_space_size = db_query (sql_cmd, FILE_OUT, FILE_ERR)
   if ret !=0:
      LOGGER.info("Error during calculation of %s tablespace.See vcdb_pre_patch.err", table_type)
      return ret, 0

   table_space_size = float(table_space_size)
   return ret, table_space_size

def get_line_by_ver(current_ver, vc_ver):
   '''
   Returns line numer for provided patch version
   '''
   line_no = 0
   B2B_PATCH_FILE = B2B_PATCH_FILE_8 if int(vc_ver) >= int(DB_VPX_VERSION_67) else B2B_PATCH_FILE_7

   with open(B2B_PATCH_FILE, "r") as flz:
      for line_no, line in enumerate(flz, 1):
         p = '<' + current_ver.strip('\n') + '/>'
         if p in line:
            return line_no

def remove_pg_addons_extension(FILE_OUT,FILE_ERR):
   '''
   PR2875177
   '''
   ret = 0
   databases = ['postgres', 'VCDB', 'template1']

   drop_sql = """DROP EXTENSION IF EXISTS pg_addons"""
   for dbname in databases:
      ret = call(psql_query(drop_sql, "postgres", dbname), stdout=FILE_OUT, stderr=FILE_ERR)
      if ret !=0:
        LOGGER.error("ERROR DROP EXTENSION IF EXISTS pg_addons")
        error = _(_T('Problem dropping vpg extension',
                     'Error during pre-patch VCDB. See vcdb_patch.err.'))
        sugg_action = _(_T('Check for pg_addons leftovers',
                           'pre-patch DB has failed. Please check vcdb_patch.log'))
        raise PermanentError(cause=error, resolution=sugg_action)

def pre_check_db():
   '''
   Run DB pre-checks. Calculates disk requirements according to b2b_PostgreSQL.sql
   '''

   with open(PRE_LOG_OUT,  "a+") as FILE_OUT:
      with open(PRE_LOG_ERR, "w") as FILE_ERR:
         #FILE_OUT = open(PRE_LOG_OUT, "a+")
         #FILE_ERR = open(PRE_LOG_ERR, "w")
         remove_pg_addons_extension(FILE_OUT, FILE_ERR)
         err, vc_ver = get_db_vpx_version (FILE_OUT, FILE_ERR)
         if err != 0:
            return err, 0, 0

         determinate_db_patch_ver (vc_ver, FILE_OUT, FILE_ERR)

         ret = 0
         disk_req_set = 0.0
         disk_req_core = 0.0
         #Remove vc user CREATEDB privilege
         sec_sql = """ALTER ROLE vc NOCREATEDB"""
         ret = call(psql_query(sec_sql), stdout=FILE_OUT, stderr=FILE_ERR)
         if ret != 0:
            LOGGER.info("DB error in drop VPX_PATCH_DISK_REQ table.See vcdb_pre_patch.err")
            return ret, 0, 0
         #Drop table if exists for disk space consumption
         sql_cmd = "DROP TABLE IF EXISTS VPX_PATCH_DISK_REQ "
         ret = call(psql_query(sql_cmd), stdout=FILE_OUT, stderr=FILE_ERR)
         if ret != 0:
            LOGGER.info("DB error in drop VPX_PATCH_DISK_REQ table.See vcdb_pre_patch.err")
            return ret, 0, 0

         #Create table for disk space consumption
         sql_cmd = "CREATE TABLE VPX_PATCH_DISK_REQ " \
                   " (PATCH_VERSION VARCHAR(10), TABLE_NAME VARCHAR(30), P_ROW_SIZE BIGINT, T_ROW_SIZE BIGINT, LINE_NUMBER INT)"
         LOGGER.info(" sql_cmd %s ", sql_cmd)
         ret = call(psql_query(sql_cmd), stdout=FILE_OUT, stderr=FILE_ERR)
         if ret != 0:
            LOGGER.info("DB error in create VPX_PATCH_DISK_REQ table.See vcdb_pre_patch.err")
            return ret, 0, 0

         #Parse b2b_PostgreSQL file for all disk required statements and populate result in VPX_PATCH_DISK_REQ
         ret = parse_b2b_upgrade( FILE_OUT,FILE_ERR)
         if ret != 0:
            LOGGER.info("DB error while populating VPX_PATCH_DISK_REQ table.See vcdb_pre_patch.err")
            return ret, 0, 0

         # Get current database patch version
         sql_cmd = "SELECT COALESCE(CURRENT_VERSION,INITIAL_VERSION) FROM VPX_PATCH_VERSION"
         ret, current_ver = db_query (sql_cmd, FILE_OUT, FILE_ERR )
         if ret != 0 or not current_ver:
            LOGGER.info("DB error while reading current database patch version.See vcdb_pre_patch.err")
            return ret, 0, 0

         # Check existens of VPX_PATCH_CONTENT table
         sql_cmd = "SELECT count(1) FROM information_schema.tables WHERE " \
                   " table_schema = \'vc\' AND upper(table_name) = \'VPX_PATCH_CONTENT\'"
         ret, patch_content = db_query (sql_cmd, FILE_OUT, FILE_ERR )
         if ret != 0 :
            LOGGER.info("DB error while check VPX_PATCH_CONTENT.See vcdb_pre_patch.err")
            return ret, 0, 0

         #Delete all patch version which have been executed
         if int(patch_content) > 0:
            # VPX_PATCH_CONTENT exists. The table contains all already executed patch versions
            # hence delete them from VPX_PATCH_DISK_REQ
            sql_cmd = "DELETE FROM VPX_PATCH_DISK_REQ WHERE PATCH_VERSION" \
   		           " IN (SELECT PATCH_VERSION FROM VPX_PATCH_CONTENT)"
            ret = call(psql_query(sql_cmd), stdout=FILE_OUT, stderr=FILE_ERR)
            if ret != 0:
               LOGGER.info("Failed to skip applied patches.See vcdb_pre_patch.err")
         else:
            # find line of currrent patch version in b2b_PostgreSQL.sql
            # and calculate only disk requirements after line of current version
            line_no = get_line_by_ver(current_ver, vc_ver)
            LOGGER.info("Current db patch version: %s at line %s", current_ver.strip('\n'),line_no)
            sql_cmd = "DELETE FROM VPX_PATCH_DISK_REQ WHERE LINE_NUMBER < %s" % str(line_no)
            ret = call(psql_query(sql_cmd), stdout=FILE_OUT, stderr=FILE_ERR)
            if ret != 0:
               LOGGER.info("Failed to skip lines before current version.See vcdb_pre_patch.err")


         #Calculate Disk requirements

         #SET tables
         ret, disk_req_set = calc_disk_req ("set", FILE_OUT, FILE_ERR)
         if ret !=0:
            return ret, 0, 0
         LOGGER.info("SET disk requirements: %s", disk_req_set)

         ret, percent_ts_set = calc_tablespace_size ("set", FILE_OUT, FILE_ERR)
         if ret !=0:
            return ret, 0, 0
         LOGGER.info("%s from SET tablestapce: %s", TABLESP_PERCENT, percent_ts_set)

         disk_req_set = disk_req_set + percent_ts_set


         #Core tables
         ret, disk_req_core = calc_disk_req ("core", FILE_OUT, FILE_ERR)
         if ret !=0:
            return ret, 0, 0
         LOGGER.info("Core disk requirements: %s", disk_req_core)

         ret, percent_ts_core = calc_tablespace_size ("core", FILE_OUT, FILE_ERR)
         if ret !=0:
            return ret, 0, 0
         LOGGER.info("%s from core tablestapce: %s", TABLESP_PERCENT, percent_ts_core)

         disk_req_core = disk_req_core + percent_ts_core

         #Drop table after provide disk requirements
         sql_cmd = "DROP TABLE VPX_PATCH_DISK_REQ "
         ret = call(psql_query(sql_cmd), stdout=FILE_OUT, stderr=FILE_ERR)
         if ret !=0:
           LOGGER.info("DB error while dropping VPX_PATCH_DISK_REQ table.See vcdb_pre_patch.err")
   return ret, disk_req_set, disk_req_core

def determinate_db_patch_ver(vc_ver, FILE_OUT, FILE_ERR):
   '''
   Execute before DB disk requirements. Determine database patch version if VPX_PATCH_VERSION.CURRENT_VERSION is empty like after upgrade.
   '''

   if int(vc_ver) < int(DB_VPX_VERSION_67):
      LOGGER.info("vCenter version is %s . B2B is supported after upgrade to version 6.7", vc_ver)
      return

   sql_query = "SELECT count(*) from VPX_PATCH_VERSION WHERE current_version is not null "
   ret, vc_patch_db = db_query (sql_query, FILE_OUT, FILE_ERR)
   if ret !=0:
      LOGGER.info("Error during read vCenter patch version")
      return

   if int(vc_patch_db) > 0:
      LOGGER.info("vCenter has been patched %s", vc_patch_db)
      return

   patch_n = find_b2b_version_from_fi()
   if patch_n == '':
      LOGGER.info("Patch version could not be found.")
      return

   insert_patch_content = find_insert_command_from_fi(FILE_OUT, FILE_ERR)
   if insert_patch_content:
      ret = call(psql_query(insert_patch_content), stdout=FILE_OUT, stderr=FILE_ERR)
      if ret !=0:
         LOGGER.info("DB error while inserting patch version.See vcdb_pre_patch.err")

   save_db = save_db_patch_version (patch_n, FILE_OUT, FILE_ERR)
   if save_db != 0:
         LOGGER.info("Error during save patch vesrion in DB. See vcdb_pre_patch.err")

   return


def find_b2b_version_from_fi():
   '''
   Searching DB patch version from fresh install script.
   Before CL#5804512 insert DB patch version looks:
      INSERT INTO VPX_PATCH_VERSION (INITIAL_VERSION) VALUES ('65066');
   After tracking executed DB patch versions looks:
      INSERT INTO VPX_PATCH_CONTENT (PATCH_VERSION,PATCH_TYPE) VALUES
      ('65001','FI'),
      ........
      ('65068','FI')
   '''
   patch_ver = ''

   #Before CL#5804512 table VPX_PATCH_VERSION track DB patch versions. Searching for
   #line to insert value
   with open(FI_SQL_FILE, "r") as flz:
      for line in flz:
         if 'INSERT INTO VPX_PATCH_VERSION (INITIAL_VERSION) VALUES' in line:
            patch_ver = find_between(line,'\'','\'').strip()

   #After creation of VPX_PATCH CONTENT table DB patch version
   # is last value from insert command
   if not patch_ver:
      regex = re.compile('(\'[0-9]{5}\',\'FI\')[^,]')
      with open(FI_SQL_FILE, "r") as flz:
         for line in flz:
            if regex.search( line ):
               patch_ver = find_between(line,'\'','\'').strip()

   return patch_ver

def find_insert_command_from_fi(FILE_OUT, FILE_ERR):
   '''
   Find insert into VPX_PATCH_CONTENT command from fresh install script
   '''
   insert_command = ''
   ret = 0
   ret, patch_content = table_index_seq_exist('VPX_PATCH_CONTENT','r', FILE_OUT, FILE_ERR)
   if ret !=0:
      LOGGER.info("DB error while reading VPX_PATCH_CONTENT.See vcdb_pre_patch.err")
   if int(patch_content) == 1:

      #fi_file = "/usr/lib/vmware-vpx/sql/VCDB_PostgreSQL.sql"
      with open(FI_SQL_FILE, "r") as flz:
         for line in flz:
            if 'INSERT INTO VPX_PATCH_CONTENT (PATCH_VERSION,PATCH_TYPE) VALUES' in line:
               insert_command = line
               continue
            if insert_command and ';' not in line:
               insert_command = insert_command + line
            if insert_command and ';' in line:
               break;
      insert_command = insert_command.replace ('FI', 'CH')

   return insert_command


def find_between( s, first, last ):
   '''
   Returns a string between first and last
   '''

   try:
     start = s.index( first ) + len( first )
     end = s.index( last, start )

     return s[start:end]
   except ValueError:
     return ""

def table_index_seq_exist(obj_name, obj_type, FILE_OUT, FILE_ERR):
   '''
   Check table/index/sequence existence in VCDB
   Returns  0 if not exist
            1 if exist
   '''
   ret = 0
   sql_cmd = "SELECT count(*) FROM pg_class c, pg_namespace n " \
             "WHERE c.relnamespace = n.oid AND nspname = \'vc\' AND relkind = \'{0}\' " \
             "AND relname = lower(\'{1}\')".format( obj_type ,obj_name)
   #print (sql_cmd)
   ret, tbl = db_query (sql_cmd, FILE_OUT, FILE_ERR)
   if ret !=0:
      LOGGER.info("Error during check object existence.")
   return ret, int(tbl)

def save_db_patch_version (patch_ver, FILE_OUT, FILE_ERR):
   '''
   Save patch number from fresh install file into DB.
   '''
   ret = 0
   if patch_ver:
      sql_stsmt = "UPDATE VPX_PATCH_VERSION SET CURRENT_VERSION= \'{0}\'".format (patch_ver.strip())
      ret = call(psql_query(sql_stsmt), stdout=FILE_OUT, stderr=FILE_ERR)
      if ret !=0:
         LOGGER.info("DB error while updating patch version.See vcdb_pre_patch.err")
   return ret

