#!/usr/bin/python
# 
# Copyright 2020 University of Southern California
# 
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# 
#    http://www.apache.org/licenses/LICENSE-2.0
# 
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
"""
Client for PDB workflow processing.
"""

import os
import subprocess
import json
from urllib.parse import urlparse
import sys
import traceback
import time
import shutil
import hashlib
import smtplib
from email.mime.text import MIMEText
import socket
from datetime import datetime
from dateutil.parser import parse
from socket import gaierror, EAI_AGAIN
from requests import HTTPError
import pickle
import csv
import filecmp
import mimetypes
import tempfile

from deriva.core import PollingErmrestCatalog, HatracStore, urlquote
from deriva.core.utils import hash_utils as hu
from deriva.core.utils.core_utils import DEFAULT_CHUNK_SIZE
from deriva.core.datapath import DataPathException

mail_footer = 'Do not reply to this message.  This is an automated message generated by the system, which does not receive email messages.'
catalog_dev_number = [99]

mmCIF_hold_records="""_pdbx_database_status.status_code                     <status_code>
_pdbx_database_status.entry_id                        <entry_id>
_pdbx_database_status.deposit_site                    ?
_pdbx_database_status.process_site                    ?
_pdbx_database_status.recvd_initial_deposition_date   <deposition_date> 
#
"""
mmCIF_release_records="""_pdbx_database_status.status_code                     <status_code>
_pdbx_database_status.entry_id                        <entry_id>
_pdbx_database_status.deposit_site                    ?
_pdbx_database_status.process_site                    ?
_pdbx_database_status.recvd_initial_deposition_date   <deposition_date> 
# 
loop_
_pdbx_audit_revision_history.ordinal
_pdbx_audit_revision_history.data_content_type
_pdbx_audit_revision_history.major_revision
_pdbx_audit_revision_history.minor_revision
_pdbx_audit_revision_history.revision_date
1 'Structure model' 1 0 <revision_date> 
# 
_pdbx_audit_revision_details.ordinal             1
_pdbx_audit_revision_details.revision_ordinal    1
_pdbx_audit_revision_details.data_content_type   'Structure model'
_pdbx_audit_revision_details.provider            repository
_pdbx_audit_revision_details.type                'Initial release'
_pdbx_audit_revision_details.description         ?
#
"""
Process_Status_Terms = {
    'NEW': 'New (trigger backend process)',
    'REPROCESS': 'Reprocess (trigger backend process after Error)',
    'IN_PROGRESS_UPLOADING_mmCIF_FILE': 'In progress: processing uploaded mmCIF file',
    'IN_PROGRESS_GENERATING_mmCIF_FILE': 'In progress: generating mmCIF file',
    'IN_PROGRESS_GENERATING_ACCESSION_CODE': 'In progress: generating accession code',
    'IN_PROGRESS_RELEASING_ENTRY': 'In progress: releasing entry',
    'SUCCESS': 'Success',
    'RESUME': 'Resume (trigger backend process)',
    'ERROR_PROCESSING_UPLOADED_mmCIF_FILE': 'Error: processing uploaded mmCIF file',
    'ERROR_GENERATING_mmCIF_FILE': 'Error: generating mmCIF file',
    'ERROR_GENERATING_ACCESSION_CODE': 'Error: generating accession code',
    'ERROR_RELEASING_ENTRY': 'Error: releasing entry',
    'IN_PROGRESS_PROCESSING_UPLOADED_RESTRAINT_FILES': 'In progress: processing uploaded restraint files',
    'ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES': 'Error: processing uploaded restraint files'
    }
 
class PDBClient (object):
    """Network client for PDB workflow processing.
    """
    ## Derived from the ermrest iobox service client

    def __init__(self, **kwargs):
        self.baseuri = kwargs.get("baseuri")
        o = urlparse(self.baseuri)
        self.scheme = o[0]
        host_port = o[1].split(":")
        self.host = host_port[0]
        self.path = o.path
        self.port = None
        if len(host_port) > 1:
            self.port = host_port[1]
        self.catalog_number = int(self.path.split('/')[-1])
        self.is_catalog_dev = (int(self.path.split('/')[-1]) in catalog_dev_number)
        self.mmCIF_defaults = kwargs.get("mmCIF_defaults")
        self.vocab_ucode = kwargs.get("vocab_ucode")
        self.make_mmCIF = kwargs.get("make_mmCIF")
        self.mmCIF_Schema_Version = kwargs.get("mmCIF_Schema_Version")
        self.py_rcsb_db = kwargs.get("py_rcsb_db")
        self.python_bin = kwargs.get("python_bin")
        self.pickle_file = kwargs.get("pickle_file")
        self.tables_groups = kwargs.get("tables_groups")
        self.export_tables = kwargs.get("export_tables")
        self.optional_fk_file = kwargs.get("optional_fk_file")
        self.scratch = kwargs.get("scratch")
        self.cif_tables = kwargs.get("cif_tables")
        self.export_order_by = kwargs.get("export_order_by")
        self.combo1_columns = kwargs.get("combo1_columns")
        self.CifCheck = kwargs.get("CifCheck")
        self.dictSdb = kwargs.get("dictSdb")
        self.entry = kwargs.get("entry")
        self.hatrac_namespace = kwargs.get("hatrac_namespace")
        self.credentials = kwargs.get("credentials")
        self.store = HatracStore(
            self.scheme, 
            self.host,
            self.credentials
        )
        self.catalog = PollingErmrestCatalog(
            self.scheme, 
            self.host,
            self.path.split('/')[-1],
            self.credentials
        )
        self.catalog.dcctx['cid'] = 'pipeline/pdb'
        self.mail_server = kwargs.get("mail_server")
        self.mail_sender = kwargs.get("mail_sender")
        self.mail_receiver = kwargs.get("mail_receiver")
        self.mail_curators = kwargs.get("mail_curators")
        self.logger = kwargs.get("logger")
        self.logger.debug('Client initialized.')

    """
    Get the user email or full name
    """
    def getUser(self, schema, table, rid):
        try:
            """
            Query for detecting the user email
            """
            url = '/attribute/A:={}:{}/RID={}/B:=(A:RCB)=(public:ERMrest_Client:ID)/B:Email,B:Full_Name'.format(urlquote(schema), urlquote(table), urlquote(rid))
            self.logger.debug('Query Email URL: "{}"'.format(url)) 
            
            resp = self.catalog.get(url)
            resp.raise_for_status()
            rows = resp.json()
            if len(rows) == 1:
                row = resp.json()[0]
                if row['Email'] != None:
                    return row['Email']
                else:
                    return row['Full_Name']
            else:
                return None
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            return None

    """
    Get the user_id 
    """
    def getUserId(self, schema, table, rid):
        try:
            """
            Query for detecting the user email
            """
            url = '/attribute/{}:{}/RID={}/RCB'.format(urlquote(schema), urlquote(table), urlquote(rid))
            self.logger.debug('Query user_id URL: "{}"'.format(url)) 
            
            resp = self.catalog.get(url)
            resp.raise_for_status()
            rows = resp.json()
            if len(rows) == 1:
                row = resp.json()[0]
                return row['RCB'].split('/')[-1]
            else:
                return None
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            return None

    """
    Send Linux email notification
    """
    def sendLinuxMail(self, subject, text, receivers):
        if receivers == None:
            receivers = self.mail_receiver
        temp_name = '/tmp/{}.txt'.format(next(tempfile._get_candidate_names()))
        fw = open(temp_name, 'w')
        fw.write('{}\n\n{}'.format(text, mail_footer))
        fw.close()
        fr = open(temp_name, 'r')
        args = ['/usr/bin/mail', '-r', self.mail_sender, '-s', 'DEV {}'.format(subject), receivers]
        p = subprocess.Popen(args, stdout=subprocess.PIPE, stderr=subprocess.PIPE, stdin=fr)
        stdoutdata, stderrdata = p.communicate()
        returncode = p.returncode
        
        if returncode != 0:
            self.logger.debug('Can not send Linux email for file {}.\nstdoutdata: {}\nstderrdata: {}\n'.format(temp_name, stdoutdata, stderrdata)) 
        else:
            self.logger.debug('Sent Linux email for file {}.\n'.format(temp_name)) 
        
        fr.close()
        os.remove(temp_name)

    """
    Send email notification
    """
    def sendMail(self, subject, text, receivers=None):
        if self.mail_server and self.mail_sender and (self.mail_receiver or self.mail_curators):
            if self.host == 'dev.pdb-dev.org':
                subject = 'DEV {}'.format(subject)
                #self.sendLinuxMail(subject, text, receivers)
                #return
            retry = 0
            ready = False
            if receivers == None:
                receivers = self.mail_receiver
            while not ready:
                try:
                    msg = MIMEText('%s\n\n%s' % (text, mail_footer), 'plain')
                    msg['Subject'] = subject
                    msg['From'] = self.mail_sender
                    msg['To'] = receivers
                    s = smtplib.SMTP(self.mail_server)
                    s.sendmail(self.mail_sender, receivers.split(','), msg.as_string())
                    s.quit()
                    self.logger.debug('Sent email notification.')
                    ready = True
                except socket.gaierror as e:
                    if e.errno == socket.EAI_AGAIN:
                        time.sleep(100)
                        retry = retry + 1
                        ready = retry > 10
                    else:
                        ready = True
                    if ready:
                        et, ev, tb = sys.exc_info()
                        self.logger.error('got exception "%s"' % str(ev))
                        self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                except:
                    et, ev, tb = sys.exc_info()
                    self.logger.error('got exception "%s"' % str(ev))
                    self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                    ready = True

    """
    Start the process for generating pyramidal tiles
    """
    def start(self):
        try:
            rid = os.getenv('RID', None)
            if rid == None:
                self.logger.error('RID was not specified in the environment')
                return
            
            action = os.getenv('action', None)
            if action == None:
                self.logger.error('"action" was not specified in the environment')
                return
                
            if action == 'entry':
                self.process_mmCIF('PDB','entry', rid)
            elif action == 'Entry_Related_File':
                self.process_Entry_Related_File('PDB', 'Entry_Related_File', rid)
            elif action == 'export':
                self.export_mmCIF('PDB', 'entry', rid)
            elif action == 'accession_code':
                self.set_accession_code(rid)
            elif action == 'release_mmCIF':
                self.addReleaseRecords(rid)
            else:
                self.logger.error('Unknown action: "%s".' % action)
        except:
            rid = os.getenv('RID', None)
            if action == 'Entry_Related_File':
                table = 'Entry_Related_File'
            else:
                table = 'entry'
            user = self.getUser('PDB', table, rid)
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'unexpected exception', 'Error', user)
            self.sendMail(subject, '%s\nThe process might have been stopped\n' % ''.join(traceback.format_exception(et, ev, tb)))
            raise
        
    """
    Process the csv/tsv file of the Entry_Related_File table
    """
    def process_Entry_Related_File(self, schema, table, rid):
        """
        Get the RCB user
        """
        user = self.getUser(schema, table, rid)
        
        """
        Query for detecting the record to be processed
        """
        url = '/entity/%s:%s/RID=%s' % (urlquote(schema), urlquote(table), urlquote(rid))
        self.logger.debug('Query URL: "%s"' % url) 
        
        resp = self.catalog.get(url)
        resp.raise_for_status()
        row = resp.json()[0]
        filename = row['File_Name']
        file_url = row['File_URL']
        md5 = row['File_MD5']
        structure_id = row['structure_id']
        creation_time = row['RCT']
        
        if self.is_catalog_dev == True:
            subject = 'PDB-Dev {}: {} ({})'.format(rid, row['Restraint_Process_Status'], user)
            self.sendMail(subject, 'The Restraint Process Status of the Entry Related File with RID={} was changed to "{}".'.format(row['RID'], row['Restraint_Process_Status']), receivers=self.mail_curators)

        """
        Extract the file from hatrac
        """
        f,error_message = self.getHatracFile(filename, file_url, self.make_mmCIF, rid, user)
        
        if f == None:
            self.updateAttributes(schema,
                                  table,
                                  rid,
                                  ["Restraint_Process_Status", "Record_Status_Detail", "Restraint_Workflow_Status"],
                                  {'RID': rid,
                                  'Restraint_Process_Status': Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'],
                                  'Record_Status_Detail': error_message,
                                  'Restraint_Workflow_Status': 'ERROR'
                                  },
                                  user)
            return
        
        if md5 == None:
            md5 = self.md5hex(f)
            self.logger.debug("The MD5 was computed and it is: %s" % md5)
                                        
        """
        Load data from the csv/tsv files
        """
        url = '/attribute/%s:%s/RID=%s/Vocab:File_Type/Table_Name' % (urlquote(schema), urlquote(table), urlquote(rid))
        resp = self.catalog.get(url)
        resp.raise_for_status()
        tname = resp.json()[0]['Table_Name']

        url = '/attribute/%s:%s/RID=%s/Vocab:File_Format/Name' % (urlquote(schema), urlquote(table), urlquote(rid))
        resp = self.catalog.get(url)
        resp.raise_for_status()
        file_type = resp.json()[0]['Name']
        delimiter = '\t' if file_type=='TSV' else ','
        
        # see where the csv/tsv file is
        # suppose it is fpath
        returncode,error_message = self.loadTableFromCVS(f, delimiter, tname, structure_id, rid, user)

        if returncode != 0:
            """
            Update the slide table with the failure result.
            """
            self.updateAttributes(schema,
                                  table,
                                  rid,
                                  ["Restraint_Process_Status", "Record_Status_Detail", "Restraint_Workflow_Status"],
                                  {'RID': rid,
                                  'Restraint_Process_Status': Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'],
                                  'Record_Status_Detail': error_message,
                                  'Restraint_Workflow_Status': 'ERROR'
                                  },
                                  user)
            return
                        
        obj = {}
        obj['RID'] = rid
        obj['Restraint_Workflow_Status'] = 'RECORD READY'
        obj['Restraint_Process_Status'] = Process_Status_Terms['SUCCESS']
        obj['File_MD5'] = md5
        obj['Record_Status_Detail'] = None
        columns = ['Restraint_Workflow_Status', 'Restraint_Process_Status', 'File_MD5', 'Record_Status_Detail']
        self.updateAttributes(schema,
                         table,
                         rid,
                         columns,
                         obj,
                         user)
   
        self.logger.debug('Ended PDB Processing for the %s:%s table.' % (schema, table)) 
        
    """
    Export the mmCIF file of the entry table
    """
    def export_mmCIF(self, schema_pdb, table_entry, rid, release=False, user_id=None):
        """
        Get the RCB user
        """
        user = self.getUser(schema_pdb, table_entry, rid)
        if user_id == None:
            user_id = self.getUserId(schema_pdb, table_entry, rid)
        
        if release == True:
            process_status_error = Process_Status_Terms['ERROR_RELEASING_ENTRY']
        else:
            process_status_error = Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE']
        
        deriva_tables = ['entry']
        mmCIF_tables = []
        mmCIF_ignored = []
        self.export_error_message = None

        """
        Query for detecting the record to be exported
        """
        pb = self.catalog.getPathBuilder()
        schema = pb.PDB
        entry = schema.entry
        RID = entry.RID
        path = entry.path
        path.filter(RID == rid)
        self.logger.debug('Query Export URL: {}'.format(path.uri))

        results = path.entities()
        results.fetch()
        file_url = results[0]['mmCIF_File_URL']
        filename = results[0]['mmCIF_File_Name']
        entry_id = results[0]['id']
        creation_time = results[0]['RCT']
        year = parse(creation_time).strftime("%Y")
        
        if self.is_catalog_dev == True:
            subject = 'PDB-Dev {}: {} ({})'.format(rid, results[0]['Process_Status'], user)
            self.sendMail(subject, 'The Process Status of the Entry with RID={} was changed to "{}".'.format(rid, results[0]['Process_Status']), receivers=self.mail_curators)

        hatracFile = self.getOutputCIF(rid, file_url, filename, user)
        if hatracFile == None:
            self.updateAttributes(schema_pdb,
                                  table_entry,
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': process_status_error,
                                  'Record_Status_Detail': 'Update error in exportData():\n{}'.format(self.export_error_message),
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            return 1
            
        tables = self.export_tables
        output = '{}/{}.cif'.format(self.scratch, entry_id)
        fw = open(output, 'w')
        
        mmCIF_export = self.cif_tables
        pks_map = self.export_order_by
        matrix_tables = ['ihm_2dem_class_average_fitting', 'ihm_geometric_object_transformation']

        def writeLine(line, loopLine=None):
            table_name = line[1:].split('.')[0]
            if table_name not in mmCIF_tables:
                mmCIF_tables.append(table_name)
            if table_name not in deriva_tables and table_name not in mmCIF_export and table_name not in mmCIF_ignored and not table_name.startswith('flr_'):
                mmCIF_ignored.append(table_name)
            if table_name not in deriva_tables and (table_name in mmCIF_export or table_name.startswith('flr_')):
                if table_name.startswith('flr_'):
                    return False
                if loopLine != None:
                    fw.write('{}\n'.format(loopLine))
                if table_name in matrix_tables:
                    try:
                        columns = line.split()
                        new_columns = []
                        for column in columns:
                            try:
                                r = re.search('(.*)[_]matrix[_]([0-9]+)[_]([0-9]+)$', column)
                                new_columns.append('{}_matrix[{}][{}]'.format(r.group(1),r.group(2),r.group(3)))
                            except:
                                new_columns.append(column)
                        fw.write('{}\n'.format(' '.join(new_columns)))
                    except:
                        fw.write('{}'.format(line))
                else:
                    fw.write('{}'.format(line))
                return True
            else:
                return False
        
        def getColumnValue(table_name, column_name, column_type, column_value):
            if column_value == None:
                return '.'
            if column_type in ['int4', 'float4']:
                return '{}'.format(column_value)
            if column_type == 'text':
                if '\t' in column_value:
                    self.logger.debug('tab character in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value))
                    self.export_error_message = 'ERROR getColumnValue: tab character in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value)
                    return None
                if '\n' in column_value:
                    return '\n;{}\n;\n'.format(column_value)
                if '"' not in column_value and "“" not in column_value and "”" not in column_value and "'" not in column_value and ' ' not in column_value and not column_value.startswith('_'):
                    return column_value
                if '"' not in column_value and "“" not in column_value and "”" not in column_value:
                    return '"{}"'.format(column_value)
                if "'" not in column_value:
                    return "'{}'".format(column_value)
                else:
                    self.logger.debug('Both " and \' are in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value))
                    return '\n;{}\n;\n'.format(column_value)
                self.export_error_message = 'ERROR getColumnValue: Unhandled value in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value)
                return None
            else:
                self.logger.debug('unknown type: {}, table: {}, column: {}'.format(column_type, table_name, column_name))
                self.export_error_message = 'ERROR getColumnValue: unknown type: {}, table: {}, column: {}'.format(column_type, table_name, column_name)
                return None

        def exportData(rid, user):
            try:
                for table_name, table_body in tables.items():
                    pk = table_body['pkey_columns']
                    try:
                        pk.remove('structure_id')
                    except:
                        pass
                    
                    """
                    if len(pk) == 0:
                        self.logger.debug('No PK Table {}, PK: {}'.format(table_name,pk))
                    elif len(pk) == 2:
                        self.logger.debug('2 PK Table {}, PK: {}'.format(table_name,pk))
                    elif len(pk) == 3:
                        self.logger.debug('3 PK Table {}, PK: {}'.format(table_name,pk))
                    elif len(pk) != 1:
                        self.logger.debug('More than 3 PK Table {}, PK: {}'.format(table_name,pk))
                    """
                            
                    if table_name in ['entry', 'chem_comp_atom']:
                        continue
                    table = schema.tables[table_name]
                    self.logger.debug('Exporting table: {}'.format(table_name))
                    path = table.path
                    if table_name in ['struct', 'pdbx_entry_details']:
                        entry_id_column = table.column_definitions['entry_id']
                        path.filter(entry_id_column == entry_id)
                    else:
                        structure_id = table.column_definitions['structure_id']
                        path.filter(structure_id == entry_id)
                    #self.logger.debug('Query Export Data URL: {}'.format(path.uri))
                    if table_name != 'audit_conform':
                        results = path.entities()
                        if len(pk) == 1:
                            #self.logger.debug('Sorting results based on: {}'.format(table.column_definitions[pk[0]]))
                            results.sort(table.column_definitions[pk[0]])
                        else:
                            pk = pks_map[table_name]
                            #self.logger.debug('Sorting results based on: ({}, {})'.format(table.column_definitions[pk[0]], table.column_definitions[pk[1]]))
                            results.sort(table.column_definitions[pk[0]], table.column_definitions[pk[1]])
                        results.fetch()
                        if len(results) == 0:
                            continue
                    else:
                        url = '/attribute/PDB:Supported_Dictionary/A:=PDB:Data_Dictionary/B:=Vocab:Data_Dictionary_Name/dict_location:=B:Location,dict_name:=B:Name,dict_version:=A:Version@sort(dict_name,dict_version)'
                        self.logger.debug('Query URL: "%s"' % url) 
                        resp = self.catalog.get(url)
                        resp.raise_for_status()
                        results = resp.json()
                        if len(results) == 0:
                            continue
                    deriva_tables.append(table_name)
                    if len(results) > 1:
                        fw.write('loop_\n')
                        for column in table_body['columns']:
                            if column['name'] == 'structure_id':
                                continue
                            if column['name'] not in table.column_definitions.keys():
                                continue
                            fw.write('_{}.{}\n'.format(table_name,column['name']))
                        for row in results:
                            line = []
                            for column in table_body['columns']:
                                column_name = column['name']
                                column_type = column['type']
                                if column_name == 'structure_id':
                                    continue
                                if column_name not in table.column_definitions.keys():
                                    continue
                                column_value = row[column_name]
                                value = getColumnValue(table_name, column_name, column_type, column_value)
                                if value == None:
                                    self.logger.debug('Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                                    subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                                    self.sendMail(subject, 'Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                                    self.export_error_message = 'ERROR exportData: Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value)
                                    return 1
                                line.append(value)
                            fw.write('{}\n'.format('\t'.join(line)))
                    elif len(results) == 1:
                        row = results[0]
                        for column in table_body['columns']:
                            column_name = column['name']
                            column_type = column['type']
                            if column_name == 'structure_id':
                                continue
                            if column_name not in table.column_definitions.keys():
                                continue
                            column_value = row[column_name]
                            value = getColumnValue(table_name, column_name, column_type, column_value)
                            if value == None:
                                self.logger.debug('Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                                self.sendMail(subject, 'Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                                self.export_error_message = 'ERROR exportData: Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value)
                                return 1
                            fw.write('_{}.{}\t{}\n'.format(table_name, column['name'], value))
                        
                    fw.write('#\n')    
                
                return 0
            except:
                et, ev, tb = sys.exc_info()
                self.logger.debug('exportData got exception "%s"' % str(ev))
                self.logger.debug('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                self.sendMail(subject, '%s\nThe process might have been stopped\n' % ''.join(traceback.format_exception(et, ev, tb)))
                self.export_error_message = 'ERROR exportData: "%s"' % str(ev)
                return 1

        def exportCIF(rid, user):
            fr = open(hatracFile, 'r')
            lines = fr.readlines()
            status = 'skip'

            for line in lines:
                if status == 'skip':
                    if line.strip() == 'loop_':
                        status = 'loop'
                    elif line.startswith('_'):
                        if writeLine(line):
                            status = 'columns'
                        
                elif status == 'loop':
                    if line.startswith('_'):
                        if writeLine(line, loopLine='loop_'):
                            status = 'columns'
                        else:
                            status = 'skip'
                    else:
                        self.logger.debug('Unexpected line after loop_:\n{}'.format(line))
                        fr.close()
                        subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                        self.sendMail(subject, 'status = {}\nThe process might have been stopped\n'.format(status))
                        self.export_error_message = 'ERROR exportCIF: Unknown status', 'status = {}'.format(status)
                        return 1
            
                elif status == 'columns':
                    if line.startswith('_'):
                        if not writeLine(line):
                            status = 'skip'
                    elif line.strip() == 'loop_':
                        status = 'loop'
                    else:
                        fw.write('{}'.format(line))
                        status = 'rows'
            
                elif status == 'rows':
                    if line.strip() == 'loop_':
                        status = 'loop'
                    elif line.startswith('_'):
                        if writeLine(line):
                            status = 'columns'
                        else:
                            status = 'skip'
                    else:
                        fw.write('{}'.format(line))
                else:
                    self.logger.debug('Unknown status: {}'.format(status))
                    subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                    self.sendMail(subject, 'status = {}\nThe process might have been stopped\n'.format(status))
                    self.export_error_message = 'ERROR exportCIF: Unknown status', 'status = {}'.format(status)
                    return 1
        
            fr.close()
            return 0
            
        fw.write('data_{}\n\n'.format(entry_id))
        value = getColumnValue('entry', 'id', 'text', '{}'.format(entry_id))
        fw.write('#\n_entry.id  {}\n#\n'.format(value))

        if exportData(rid, user) != 0:
            self.logger.debug('Update error in exportData()')
            fw.close()
            os.remove(hatracFile)
            self.updateAttributes(schema_pdb,
                                  table_entry,
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': process_status_error,
                                  'Record_Status_Detail': 'Update error in exportData():\n{}'.format(self.export_error_message),
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            return 1
        if exportCIF(rid, user) != 0:
            self.logger.debug('Update error in exportCIF()')
            fw.close()
            os.remove(hatracFile)
            self.updateAttributes(schema_pdb,
                                  table_entry,
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': process_status_error,
                                  'Record_Status_Detail': 'Update error in exportCIF():\n{}'.format(self.export_error_message),
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            return 1
        fw.close()
        os.remove(hatracFile)
        
        if len(mmCIF_ignored) > 0:
            self.logger.debug('Tables from the mmCIF file that were not included in the export:')
            for table_name in sorted(mmCIF_ignored):
                self.logger.debug('\t{}'.format(table_name))
                
        file_name = '{}.cif'.format(entry_id)
        returncode,error_message = self.validateExportmmCIF(self.scratch, file_name, year, entry_id, rid, user, process_status_error, user_id)

        if returncode == 0:
            """
            Add the Conform_Dictionary entries
            """
            
            """
            Get the RID of Entry_mmCIF_File
            """
            url = '/attribute/PDB:Entry_mmCIF_File/Structure_Id={}/RID'.format(urlquote(entry_id))
            self.logger.debug('Query URL: "%s"' % url) 
            resp = self.catalog.get(url)
            resp.raise_for_status()
            mmCIF_rows = resp.json()
            if len(mmCIF_rows) != 1:
                self.logger.debug('Entry_mmCIF_File is not unique')
                self.updateAttributes(schema_pdb,
                                      table_entry,
                                      rid,
                                      ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                      {'RID': rid,
                                      'Process_Status': process_status_error,
                                      'Record_Status_Detail': 'Entry_mmCIF_File is not unique',
                                      'Workflow_Status': 'ERROR'
                                      },
                                      user)
                return 1
    
            mmCIF_row = mmCIF_rows[0]
            
            """
            Delete entries from Conform_Dictionary if any
            """
            url = '/entity/PDB:Conform_Dictionary/Exported_mmCIF_RID={}'.format(urlquote(mmCIF_row['RID']))
            self.logger.debug('Query URL: "%s"' % url) 
            resp = self.catalog.get(url)
            resp.raise_for_status()
            conform_rows = resp.json()
            if len(conform_rows) > 0:
                url = '/entity/PDB:Conform_Dictionary/Exported_mmCIF_RID={}'.format(urlquote(mmCIF_row['RID']))
                resp = self.catalog.delete(
                    url
                )
                resp.raise_for_status()
                self.logger.debug('SUCCEEDED deleted the rows for the URL "%s".' % (url)) 
    
            """
            Get the supported entries
            """
            url = '/attribute/PDB:Supported_Dictionary/Data_Dictionary_RID'
            self.logger.debug('Query URL: "%s"' % url) 
            resp = self.catalog.get(url)
            resp.raise_for_status()
            supported_rows = resp.json()
            
            """
            Insert rows into Conform_Dictionary
            """
            for supported_row in supported_rows:
                row = {'Data_Dictionary_RID': supported_row['Data_Dictionary_RID'], 'Exported_mmCIF_RID': mmCIF_row['RID']}
            
                if self.createEntity('PDB:Conform_Dictionary', row, rid, user) == None:
                    self.updateAttributes(schema_pdb,
                                          table_entry,
                                          rid,
                                          ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                          {'RID': rid,
                                          'Process_Status': process_status_error,
                                          'Record_Status_Detail': 'Update error in createEntity():\n{}'.format(self.export_error_message),
                                          'Workflow_Status': 'ERROR'
                                          },
                                          user)
                    return 1
    
            if returncode == 0:
                self.logger.debug('Update success in export_mmCIF()')
                self.updateAttributes(schema_pdb,
                                      table_entry,
                                      rid,
                                      ["Process_Status", "Workflow_Status"],
                                      {'RID': rid,
                                      'Process_Status': Process_Status_Terms['SUCCESS'],
                                      'Workflow_Status': 'mmCIF CREATED'
                                      },
                                      user)
                if release == False:
                    subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'mmCIF CREATED', Process_Status_Terms['SUCCESS'], user)
                    self.sendMail(subject, 'The workflow status of the entry with RID={} was changed to mmCIF CREATED.'.format(rid), receivers=self.mail_curators)
            else:
                self.updateAttributes(schema_pdb,
                                      table_entry,
                                      rid,
                                      ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                      {'RID': rid,
                                      'Process_Status': process_status_error,
                                      'Record_Status_Detail': 'ERROR in validateExportmmCIF:\n{}'.format(error_message),
                                      'Workflow_Status': 'ERROR'
                                      },
                                      user)
                return 1
            return 0
        else:
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
            self.sendMail(subject, error_message)
            return 1
            
    """
    Insert a row in a table
    """
    def createEntity (self, path, row, rid, user):
        """
        Insert the row in the table.
        """
        try:
            url = '/entity/%s' % (path)
            resp = self.catalog.post(
                url,
                json=[row]
            )
            resp.raise_for_status()
            
            self.logger.debug('SUCCEEDED created in the table "%s" the entry "%s".' % (url, json.dumps(row, indent=4))) 
            return url
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            self.export_error_message = 'ERROR createEntity: "%s"' % str(ev)
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'], user)
            self.sendMail(subject, 'RID: %s\n%s\n' % (rid, ''.join(traceback.format_exception(et, ev, tb))))
            return None

    """
    Process the mmCIF file of the entry table
    """
    def process_mmCIF(self, schema, table, rid):
        """
        Get the RCB user
        """
        user = self.getUser(schema, table, rid)
        
        """
        Query for detecting the record to be processed
        """
        url = '/entity/%s:%s/RID=%s' % (urlquote(schema), urlquote(table), urlquote(rid))
        self.logger.debug('Query URL: "%s"' % url) 
        
        resp = self.catalog.get(url)
        resp.raise_for_status()
        row = resp.json()[0]
        filename = row['mmCIF_File_Name']
        file_url = row['mmCIF_File_URL']
        md5 = row['mmCIF_File_MD5']
        last_md5 = row['Last_mmCIF_File_MD5']
        id = row['id']
        creation_time = row['RCT']
        
        if self.is_catalog_dev == True:
            subject = 'PDB-Dev {}: {} ({})'.format(rid, row['Process_Status'], user)
            self.sendMail(subject, 'The Process Status of the Entry with RID={} was changed to "{}".'.format(rid, row['Process_Status']), receivers=self.mail_curators)

        """
        Check if we have a new mmCIF file
        """
        if md5 == last_md5:
            self.logger.debug('RID="{}", Skipping loading the table as the mmCIF file is unchanged'.format(rid))
            obj = {}
            obj['RID'] = rid
            obj['Workflow_Status'] = 'RECORD READY'
            obj['Process_Status'] = Process_Status_Terms['SUCCESS']
            columns = ['Workflow_Status', 'Process_Status']
            self.updateAttributes(schema,
                             table,
                             rid,
                             columns,
                             obj,
                             user)
       
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'RECORD READY', Process_Status_Terms['SUCCESS'], user)
            self.sendMail(subject, 'The workflow status of the entry with RID={} was changed to RECORD READY.'.format(rid), receivers=self.mail_curators)
            self.logger.debug('RID="{}", Ended PDB Processing for the {}:{} table.'.format(rid, schema, table)) 
            return
        
        """
        Extract the file from hatrac
        """
        f,error_message = self.getHatracFile(filename, file_url, self.make_mmCIF, rid, user)
        
        if f == None:
            self.updateAttributes(schema,
                                  table,
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'],
                                  'Record_Status_Detail': error_message,
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            return
        
        """
        Get the md5 if necessary
        """
        if md5 == None:
            md5 = self.md5hex(f)
            self.logger.debug("The MD5 was computed and it is: %s" % md5)
            
        """
        Convert the file to JSON and load the data into the tables
        """
        returncode,error_message = self.convert2json(filename, id, rid, user)
        
        if returncode != 0:
            """
            Update the slide table with the failure result.
            """
            self.updateAttributes(schema,
                                  table,
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'],
                                  'Record_Status_Detail': error_message,
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            return
                        
                            
        obj = {}
        obj['RID'] = rid
        obj['Workflow_Status'] = 'RECORD READY'
        obj['Process_Status'] = Process_Status_Terms['SUCCESS']
        obj['mmCIF_File_MD5'] = md5
        obj['Record_Status_Detail'] = None
        obj['Last_mmCIF_File_MD5'] = md5
        columns = ['Workflow_Status', 'Process_Status', 'mmCIF_File_MD5', 'Record_Status_Detail', 'Last_mmCIF_File_MD5']
        self.updateAttributes(schema,
                         table,
                         rid,
                         columns,
                         obj,
                         user)
   
        subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'RECORD READY', Process_Status_Terms['SUCCESS'], user)
        self.sendMail(subject, 'The workflow status of the entry with RID={} was changed to RECORD READY.'.format(rid), receivers=self.mail_curators)
        self.logger.debug('Ended PDB Processing for the %s:%s table.' % (schema, table)) 
        
    """
    Extract the file from hatrac
    """
    def getHatracFile(self, filename, file_url, input_dir, rid, user):
        error_message = None
        try:
            hatracFile = '{}/{}'.format(input_dir, filename)
            self.store.get_obj(file_url, destfilename=hatracFile)
            self.logger.debug('File "%s", %d bytes.' % (hatracFile, os.stat(hatracFile).st_size)) 
            return (hatracFile,error_message)
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            error_message = 'ERROR getHatracFile: "%s"' % str(ev)
            return (None,error_message)
            
    """
    Convert the input file to JSON
    """
    def convert2json(self, filename, entry_id, rid, user):
        try:
            """
            Prepend the RID to the input file
            """
            shutil.move('{}/{}'.format(self.make_mmCIF, filename), '{}/{}_{}'.format(self.make_mmCIF, rid, filename))
            filename = '{}_{}'.format(rid, filename)

            """
            Apply make-mmcif.py
            """
            error_message = None
            currentDirectory=os.getcwd()
            os.chdir('{}'.format(self.make_mmCIF))
            args = [self.python_bin, 'make-mmcif.py', filename]
            self.logger.debug('Running "{}" from the {} directory'.format(' '.join(args), self.make_mmCIF)) 
            p = subprocess.Popen(args, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            stdoutdata, stderrdata = p.communicate()
            returncode = p.returncode
            os.chdir(currentDirectory)
            
            if returncode != 0:
                self.logger.error('Can not make mmCIF for entry id = "%s" and file "%s".\nstdoutdata: %s\nstderrdata: %s\n' % (entry_id, filename, stdoutdata, stderrdata)) 
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
                self.sendMail(subject, 'Can not make mmCIF for entry id = "%s" and file "%s".\nstdoutdata: %s\nstderrdata: %s\n' % (entry_id, filename, stdoutdata, stderrdata))
                os.remove('{}/{}'.format(self.make_mmCIF, filename))
                error_message = 'ERROR convert2json: {}'.format(stderrdata)
                return (returncode,error_message)
            
            os.remove('{}/{}'.format(self.make_mmCIF, filename))
            
            """
            Move the output.cif file to the rcsb/db/tests-validate/test-output/ihm-files directory and apply testSchemaDataPrepValidate-ihm.py
            """
            shutil.move('{}/output.cif'.format(self.make_mmCIF), '{}/rcsb/db/tests-validate/test-output/ihm-files/'.format(self.py_rcsb_db))
            self.logger.debug('File {} was moved to the {} directory'.format('{}/output.cif'.format(self.make_mmCIF), '{}/rcsb/db/tests-validate/test-output/ihm-files/'.format(self.py_rcsb_db))) 
            currentDirectory=os.getcwd()
            os.chdir('{}'.format(self.py_rcsb_db))
            args = ['env', 'PYTHONPATH={}'.format(self.py_rcsb_db), self.python_bin, 'rcsb/db/tests-validate/testSchemaDataPrepValidate-ihm.py']
            self.logger.debug('Running "{}" from the {} directory'.format(' '.join(args), self.py_rcsb_db)) 
            p = subprocess.Popen(args, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            stdoutdata, stderrdata = p.communicate()
            returncode = p.returncode
            os.chdir(currentDirectory)
            
            if returncode != 0:
                self.logger.error('Can not validate testSchemaDataPrepValidate-ihm for file "%s".\nstdoutdata: %s\nstderrdata: %s\n' % ('output.cif', stdoutdata, stderrdata)) 
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
                self.sendMail(subject, 'Can not make testSchemaDataPrepValidate-ihm for file "%s".\nstdoutdata: %s\nstderrdata: %s\n' % ('output.cif', stdoutdata, stderrdata))
                os.remove('{}/rcsb/db/tests-validate/test-output/ihm-files/output.cif'.format(self.py_rcsb_db))
                error_message = 'ERROR convert2json: {}'.format(stderrdata)
                return (returncode,error_message)
            
            shutil.copy2('{}/rcsb/db/tests-validate/test-output/ihm-files/output.cif'.format(self.py_rcsb_db), '/home/pdbihm/temp')
            os.remove('{}/rcsb/db/tests-validate/test-output/ihm-files/output.cif'.format(self.py_rcsb_db))
            self.logger.debug('File {}/{} was removed'.format(self.py_rcsb_db, 'rcsb/db/tests-validate/test-output/ihm-files/output.cif')) 
            
            """
            Load now the data from JSON files which are in the rcsb/db/tests-validate/test-output directory into the tables 
            """
            fpath = '{}/rcsb/db/tests-validate/test-output'.format(self.py_rcsb_db)

            json_files = []
            for entry in os.scandir(fpath):
                    if entry.is_file() and entry.path.endswith('.json'):
                        json_files.append(entry.name)
            self.logger.debug('The following JSON files were generated in the {}/rcsb/db/tests-validate/test-output directory:\n\t{}'.format(self.py_rcsb_db, '\n\t'.join(json_files))) 

            for entry in os.scandir(fpath):
                    if entry.is_file() and entry.path.endswith('.json'):
                        returncode,error_message = self.loadTablesFromJSON(entry.path, entry_id, rid, user)
                        if returncode != 0:
                            break
            
            """
            Remove the JSON files that were created
            """
            for entry in os.scandir(fpath):
                    if entry.is_file() and entry.path.endswith('.json'):
                        os.remove(entry.path)
                        self.logger.debug('Removed file {}'.format(entry.path))
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            os.chdir(currentDirectory)
            returncode = 1
            error_message = 'ERROR convert2json: "%s"' % str(ev) 
            
        return (returncode,error_message)
            
        
    """
    Update the ermrest attributes
    """
    def updateAttributes (self, schema, table, rid, columns, row, user):
        """
        Update the ermrest attributes with the row values.
        """
        try:
            columns = ','.join([urlquote(col) for col in columns])
            url = '/attributegroup/%s:%s/RID;%s' % (urlquote(schema), urlquote(table), columns)
            resp = self.catalog.put(
                url,
                json=[row]
            )
            resp.raise_for_status()
            self.logger.debug('SUCCEEDED updated the table "%s" for the RID "%s"  with "%s".' % (url, rid, json.dumps(row, indent=4))) 
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'ERROR', row['Process_Status'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            

    """
    Get the hexa md5 checksum of the file.
    """
    def md5hex(self, fpath):
        h = hashlib.md5()
        try:
            f = open(fpath, 'rb')
            try:
                b = f.read(4096)
                while b:
                    h.update(b)
                    b = f.read(4096)
                return h.hexdigest()
            finally:
                f.close()
        except:
            return None

    """
    Sort the tables to be loaded based on the FK dependencies.
    """
    def sortTable(self, fpath):
        excluded_mmCIF_tables = [
            "entry"
        ]

        """
        Get the tables groups
        """
        with open(self.tables_groups, 'r') as f:
            table_groups = json.load(f)
        
        """
        Sort the tables from the JSON file based on the groups
        """
        tables = []
        with open(fpath, 'r') as f:
            pdb = json.load(f)
            pdb = pdb[0]
            group_no = 0
            while group_no < len(table_groups):
                group_str = str(group_no)
                for k,v in pdb.items():
                    if k in table_groups[group_str] and k not in excluded_mmCIF_tables:
                        tables.append(k)
                group_no +=1
        """
        Check that all the tables are in the database
        """
        with open(fpath, 'r') as f:
            pdb = json.load(f)
            pdb = pdb[0]
            for k,v in pdb.items():
                if k not in (tables + excluded_mmCIF_tables):
                    raise RuntimeError('Table "{}" from mmCIF is not present in the DERIVA database. Possible mismatch versions.'.format(k))
        
        return tables
        
    """
    Rollback JSON inserted rows
    """
    def rollbackInsertedRows(self, records, entry_id, user):
        records.reverse()
        for record in records:
            tname = record['name']
            rows = record['rows']
            for row in rows:
                rid = row['RID']
                try:
                    if 'structure_id' in row.keys():
                        path = '%s:%s/%s=%s' % (urlquote('PDB'), urlquote(tname), urlquote('structure_id'), urlquote(entry_id))
                    else:
                        path = '%s:%s/%s=%s' % (urlquote('PDB'), urlquote(tname), urlquote('RID'), urlquote(rid))
                    url = '/entity/%s' % (path)
                    resp = self.catalog.delete(
                        url
                    )
                    resp.raise_for_status()
                    self.logger.debug('SUCCEEDED deleted the rows for the URL "%s".' % (url)) 
                    if 'structure_id' in row.keys():
                        break
                except HTTPError as e:
                    if e.response.status_code == HTTPStatus.NOT_FOUND:
                        self.logger.debug('No rows found to delete from the URL "%s".' % (url))
                    else:
                        et, ev, tb = sys.exc_info()
                        self.logger.error('got exception "%s"' % str(ev))
                        self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                        subject = 'PDB-Dev {} {}: {} ({})'.format(entry_id, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'], user)
                        self.sendMail(subject, 'URL: %s\n%s\n' % (url, ''.join(traceback.format_exception(et, ev, tb))))
                except:
                    et, ev, tb = sys.exc_info()
                    self.logger.error('got exception "%s"' % str(ev))
                    self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                    subject = 'PDB-Dev {} {}: {} ({})'.format(entry_id, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'], user)
                    self.sendMail(subject, 'URL: %s\n%s\n' % (url, ''.join(traceback.format_exception(et, ev, tb))))
                
    """
    Load data into the tables from the JSON file.
    """
    def loadTablesFromJSON(self, fpath, entry_id, rid, user):
        shutil.copy2(fpath, '/home/pdbihm/temp')
        
        """
        Tables that have a NOT NULL *_RID column
        """
        fk_tables = []
        for fk_table in self.combo1_columns.keys():
            if fk_table not in fk_tables:
                fk_tables.append(fk_table)
        
        schema_name = 'PDB'
        pb = self.catalog.getPathBuilder()
        returncode = 0
        error_message = None
        
        """
        Read the JSON file data
        """
        with open(fpath, 'r') as f:
            pdb = json.load(f)
            pdb = pdb[0]

        """
        Read the JSON FK optional file
        """
        with open(self.optional_fk_file, 'r') as f:
            optional_fks = json.load(f)

        """
        Sort the tables based on the FK dependencies
        """
        try:
            tables = self.sortTable(fpath)
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            returncode = 1
            error_message = str(ev)
            return (returncode,error_message)
        
        """
        Keep track of the inserted rows in case of rollback
        """
        inserted_records = []
        
        model_root = self.catalog.getCatalogModel()
        for tname in tables:
            records = pdb[tname]
            if type(records) is dict:
                records = [records]
    
            table = pb.schemas[schema_name].tables[tname]
            entities = []
            for r in records:
                """
                Replace the FK references to the entry table
                """
                r = self.getUpdatedRecord(tname, r, entry_id, model_root.schemas['PDB'].tables[tname], inserted_records, fk_tables)
                if self.is_catalog_dev == True:
                    if tname == 'ihm_entity_poly_segment':
                        r = self.getUpdatedEntityPolySegment(r)
                self.getUpdatedOptional(optional_fks, tname, r, entry_id)
                entities.append(r)
            
            self.logger.debug('Table {}, inserting {} rows'.format(tname, len(entities)))
            """
            Insert the data
            """
            try:
                res = table.insert(entities).fetch()
                inserted_records.append({'name': tname, 'rows': res})
                inserted_rows = len(entities)
                self.logger.debug('File {}: inserted {} rows into table {}'.format(fpath, inserted_rows, tname))
                #self.logger.debug('Inserted into table {} the {} rows:\n'.format(tname, entities))
            except DataPathException as e:
                et, ev, tb = sys.exc_info()
                self.logger.error(e)
                self.logger.error(e.message)
                self.logger.error(e.reason)
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
                self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
                returncode = 1
                error_message = 'Error in inserting values into the table {}\n{}'.format(tname, e.message)
                self.rollbackInsertedRows(inserted_records, entry_id, user)
                break
            except HTTPError as e:
                et, ev, tb = sys.exc_info()
                self.logger.error(e)
                self.logger.error(e.response.text)
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
                self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
                returncode = 1
                error_message = 'Error in inserting values into the table {}\n{}'.format(tname, e.response.text)
                self.rollbackInsertedRows(inserted_records, entry_id, user)
                break
            except:
                et, ev, tb = sys.exc_info()
                self.logger.error('got exception "%s"' % str(ev))
                self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
                self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
                returncode = 1
                error_message = 'Error in inserting values into the table {}\n{}'.format(tname, str(ev))
                self.rollbackInsertedRows(inserted_records, entry_id, user)
                break
        
        return (returncode,error_message)

    """
    Load data into the tables from a csv/tsv file.
    """
    def loadTableFromCVS(self, fpath, delimiter, tname, entry_id, rid, user):
        
        """
        Empty first the tname table
        """
        self.logger.debug('Checking if PDB:{}/structure_id={} is empty'.format(tname, entry_id))
        url = '/entity/PDB:{}/structure_id={}'.format(tname, entry_id)
        resp = self.catalog.get(
            url
        )
        resp.raise_for_status()
        deleted_rows = resp.json()
        if len(deleted_rows) > 0:
            """
            The table is not empty
            """
            url = '/entity/PDB:{}/structure_id={}'.format(tname, entry_id)
            resp = self.catalog.delete(
                url
            )
            resp.raise_for_status()
            self.logger.debug('Deleted rows from PDB:{}/structure_id={}'.format(tname, entry_id))

        """
        Read in chunks of 1000 rows
        Make a temporization of 10 seconds between chunks readings
        """
        returncode = 0
        error_message = None
        chunk_size = 1000
        sleep_time = 10
        schema_name = 'PDB'
        pb = self.catalog.getPathBuilder()
        table = pb.schemas[schema_name].tables[tname]
        column_definitions = table.column_definitions
        counter = 0
        
        try:
            """
            Read the JSON FK optional file
            """
            with open(self.optional_fk_file, 'r') as f:
                optional_fks = json.load(f)
    
            """
            Read the rows of the csv/tsv file as dictionaries
            """
            csvfile = open(fpath, 'r')
            reader = csv.DictReader(csvfile, delimiter=delimiter)
            j=0
            done = False
            missing_columns = []
            while not done:
                done = True
                i = 0
                entities = []
                for row in reader:
                    j=j+1
                    entity = dict(row)
                    for column in list(entity.keys()):
                        try:
                            column_definitions[column]
                        except:
                            if column not in missing_columns:
                                missing_columns.append(column)
                                self.logger.debug('Table "%s" has not the column "%s".' % (tname, column))
                            entity[column] = ''
                            
                        """
                        Columns types:
                            ermrest_rid
                            ermrest_rct
                            ermrest_rmt
                            ermrest_rcb
                            ermrest_rmb
                            ermrest_curie
                            ermrest_uri
                            text
                            markdown
                            text[]
                            int4
                            float4
                            int8
                        """
                        if entity[column] == '':
                            """
                            Any empty value will be treated as NULL
                            """
                            del entity[column]
                        elif column_definitions[column]._wrapped_column.type.typename == 'jsonb':
                            entity[column] = json.loads(entity[column])
                        elif column_definitions[column]._wrapped_column.type.typename.endswith('[]'):
                            entity[column] = entity[column][1:-1].split(',')
                    
                    """
                    Replace the FK references to the entry table
                    """
                    entity = self.getRecordUpdatedWithFK(tname, entity, entry_id)
                    entity = self.getUpdatedOptional(optional_fks, tname, entity, entry_id)
                    entity['Entry_Related_File'] = rid
                    
                    entities.append(entity)
                    i = i+1
                    if i >= chunk_size:
                        """
                        Insert the chunk
                        """
                        done = False
                        break
                if len(entities) > 0:
                    try:
                        table.insert(entities).fetch()
                        counter = counter + len(entities)
                        time.sleep(sleep_time)
                    except DataPathException as e:
                        et, ev, tb = sys.exc_info()
                        self.logger.error(e)
                        self.logger.error(e.message)
                        self.logger.error(e.reason)
                        subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'], user)
                        self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
                        returncode = 1
                        error_message = 'Error in inserting CSV values into the table {}\n{}'.format(tname, e.message)
                        break
                    except HTTPError as e:
                        et, ev, tb = sys.exc_info()
                        self.logger.error(e)
                        self.logger.error(e.response.text)
                        subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'], user)
                        self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
                        returncode = 1
                        error_message = 'Error in inserting CSV values into the table {}\n{}'.format(tname, e.response.text)
                        break
                    except:
                        et, ev, tb = sys.exc_info()
                        self.logger.error('got exception "%s"' % str(ev))
                        self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                        subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'], user)
                        self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
                        returncode = 1
                        error_message = 'Error in inserting CSV values into the table {}\n{}'.format(tname, str(ev))
                        break
            self.logger.debug('File {}: inserted {} rows into table {}'.format(fpath, counter, tname))
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_RESTRAINT_FILES'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            error_message = 'ERROR loadTableFromCVS: "%s"' % str(ev)
            returncode = 1
        return (returncode, error_message)
             
    """
    Get the record with the foreign key updated to the entry id.
    If the FK is missing, add it.
    """
    def getRecordUpdatedWithFK(self, tname, row, entry_id):
        with open('{}'.format(self.entry), 'r') as f:
            pdb = json.load(f)
        referenced_by = pdb['Catalog {}'.format(self.catalog_number)]['schemas']['PDB']['tables']['entry']['referenced_by']
        columns = []
        for k,v in referenced_by.items():
            if v['table'] == tname:
                col = v['columns'][1:-1]
                columns.append(col)
        for col in columns:
            if tname == 'struct' and col == 'structure_id':
                continue
            row[col] = entry_id
                
        return row

    """
    Get the record with the foreign key updated to the entry id.
    """
    def getUpdatedRecord(self, tname, row, entry_id, table, inserted_records, fk_tables):
        with open('{}'.format(self.entry), 'r') as f:
            pdb = json.load(f)
        referenced_by = pdb['Catalog {}'.format(self.catalog_number)]['schemas']['PDB']['tables']['entry']['referenced_by']
        columns = []
        for k,v in referenced_by.items():
            if v['table'] == tname:
                col = v['columns'][1:-1]
                columns.append(col)
        for col in columns:
            if tname == 'struct' and col == 'structure_id':
                continue
            if col in row.keys():
                row[col] = entry_id
        
        """
        Set the missing defaults
        """
        if tname in self.mmCIF_defaults.keys():
            for col in self.mmCIF_defaults[tname]:
                if col not in row.keys():
                    row[col] = '.'
        """
        Set the ucode values
        """
        if tname in self.vocab_ucode.keys():
            for col in self.vocab_ucode[tname]:
                if col in row.keys():
                    row[col] = row[col].upper()
        
        """
        Set the values for the *_RID columns
        """
        if tname in fk_tables:
            entry = self.combo1_columns[tname]
            for col,value in entry.items():
                for pk_table,mappings in value.items():
                    rid_found = False
                    for inserted_record in inserted_records:
                        if inserted_record['name'] == pk_table:
                            for pk_row in inserted_record['rows']:
                                found = True
                                for fk_col, pk_col in mappings.items():
                                    if row[fk_col] != pk_row[pk_col]:
                                        found = False
                                        break
                                if found == True:
                                    row[col] = pk_row['RID']
                                    rid_found = True
                                    break
                            if rid_found == False:
                                self.logger.debug('Could not find a RID value for {} column in the {} table'.format(col, tname))
                                break
                    if rid_found == False:
                        break
        return row

    """
    Get the record for the ihm_entity_poly_segment table updated with values for the Entity_Poly_Seq_RID_Begin and Entity_Poly_Seq_RID_End columns.
    """
    def getUpdatedEntityPolySegment(self, row):
        structure_id = row['structure_id']
        entity_id = row['entity_id']
        comp_id_begin = row ['comp_id_begin']
        comp_id_end = row ['comp_id_end']
        seq_id_begin = row['seq_id_begin']
        seq_id_end = row['seq_id_end']
        
        url = '/attribute/PDB:entity_poly_seq/structure_id={}&entity_id={}&mon_id={}&num={}/RID'.format(urlquote(structure_id), urlquote(entity_id), urlquote(comp_id_begin), seq_id_begin)
        self.logger.debug('Query URL: "%s"' % url) 
        resp = self.catalog.get(url)
        resp.raise_for_status()
        row['Entity_Poly_Seq_RID_Begin'] = resp.json()[0]['RID']

        url = '/attribute/PDB:entity_poly_seq/structure_id={}&entity_id={}&mon_id={}&num={}/RID'.format(urlquote(structure_id), urlquote(entity_id), urlquote(comp_id_end), seq_id_end)
        self.logger.debug('Query URL: "%s"' % url) 
        resp = self.catalog.get(url)
        resp.raise_for_status()
        row['Entity_Poly_Seq_RID_End'] = resp.json()[0]['RID']

        return row

    """
    Update the record for the optional composite FK
    """
    def getUpdatedOptional(self, optional_fks, tname, row, entry_id):
        if tname in optional_fks:
            for fk in optional_fks[tname]:
                url_structure_pattern = fk['url_structure_pattern']
                url_pattern = fk['url_pattern']
                fk_RID_column_name = fk['fk_RID_column_name']
                fk_other_column_name = fk['fk_other_column_name']
                ref_table = fk['ref_table']
                ref_other_column_name = fk['ref_other_column_name']
                if ref_other_column_name not in row.keys():
                    continue
                if fk_other_column_name not in row.keys():
                    continue
                fk_other_value = row[fk_other_column_name]
                if type(fk_other_value).__name__ == 'str':
                    fk_other_value = urlquote(fk_other_value)
                url = url_structure_pattern.format(urlquote(ref_table), urlquote(ref_other_column_name), fk_other_value, entry_id)
                self.logger.debug('Query URL with structure_id for OPTIONAL FK: "%s"' % url) 
                resp = self.catalog.get(url)
                resp.raise_for_status()
                if len(resp.json()) > 0:
                    row[fk_RID_column_name] = resp.json()[0]['RID']
                    continue
                url = url_pattern.format(urlquote(ref_table), urlquote(ref_other_column_name), fk_other_value)
                self.logger.debug('Query URL for OPTIONAL FK: "%s"' % url) 
                resp = self.catalog.get(url)
                resp.raise_for_status()
                if len(resp.json()) > 0:
                    row[fk_RID_column_name] = resp.json()[0]['RID']

        return row

    """
    Get the output.cif file
    """
    def getOutputCIF(self, rid, file_url, filename, user):
        try:
            """
            Get the file from hatrac
            """
            hatracFile = '{}/{}'.format(self.make_mmCIF, filename)
            self.store.get_obj(file_url, destfilename=hatracFile)
            currentDirectory=os.getcwd()
            os.chdir('{}'.format(self.make_mmCIF))

            """
            Prepend the RID to the input file
            """
            shutil.move('{}/{}'.format(self.make_mmCIF, filename), '{}/{}_{}'.format(self.make_mmCIF, rid, filename))
            filename = '{}_{}'.format(rid, filename)

            """
            Apply make-mmcif.py
            """
            args = [self.python_bin, 'make-mmcif.py', filename]
            self.logger.debug('Running "{}" from the {} directory'.format(' '.join(args), self.make_mmCIF)) 
            p = subprocess.Popen(args, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            stdoutdata, stderrdata = p.communicate()
            returncode = p.returncode
            os.chdir(currentDirectory)
            
            if returncode != 0:
                self.logger.error('Can not make mmCIF for entry RID = "%s" and file "%s".\nstdoutdata: %s\nstderrdata: %s\n' % (rid, filename, stdoutdata, stderrdata)) 
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'], user)
                self.sendMail(subject, 'Can not make mmCIF for entry RID = "%s" and file "%s".\nstdoutdata: %s\nstderrdata: %s\n' % (rid, filename, stdoutdata, stderrdata))
                os.remove('{}/{}'.format(self.make_mmCIF, filename))
                self.export_error_message = 'ERROR getOutputCIF: Can not make mmCIF.\nstdoutdata: %s\nstderrdata: %s\n' % (stdoutdata, stderrdata)
                return None
            
            os.remove('{}/{}'.format(self.make_mmCIF, filename))
            
            """
            Move the output.cif file to the scratch directory
            """
            shutil.move('{}/output.cif'.format(self.make_mmCIF), '{}/'.format(self.scratch))
            self.logger.debug('File {} was moved to the {} directory'.format('{}/output.cif'.format(self.make_mmCIF), '{}/rcsb/db/tests-validate/test-output/ihm-files/'.format(self.py_rcsb_db))) 
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            os.chdir(currentDirectory)
            self.export_error_message = 'ERROR getOutputCIF: "%s"' % str(ev)
            return None
            
        return '{}/output.cif'.format(self.scratch)
            
    """
    Get the accession serial value
    """
    def getNextAccessionSerial(self, rid, user):
        try:
            accession_serial_value = None
            row = {'Entry': rid}
            url = '/entity/PDB:Accession_Code?defaults=Accession_Serial,Accession_Code'
            resp = self.catalog.post(
                url,
                json=[row]
            )
            resp.raise_for_status()
            
            self.logger.debug('SUCCEEDED created in the table Accession_Code the row "%s".' % (json.dumps(row, indent=4))) 
            if len(resp.json()) == 1:
                row = resp.json()[0]
                accession_serial_value = row['Accession_Code']
            else:
                self.logger.error('Error created in the table Accession_Code the row "%s".' % (json.dumps(row, indent=4)))
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMISSION COMPLETE', Process_Status_Terms['ERROR_GENERATING_ACCESSION_CODE'], user)
                self.sendMail(subject, 'RID: %s\n%s\n' % (rid, Process_Status_Terms['ERROR_GENERATING_ACCESSION_CODE']))
                return (None, 'Error in getting a new serial value')
            return (accession_serial_value, None)
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            self.export_error_message = 'ERROR getNextAccessionSerial: "%s"' % str(ev)
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_ACCESSION_CODE'], user)
            self.sendMail(subject, 'RID: %s\n%s\n' % (rid, ''.join(traceback.format_exception(et, ev, tb))))
            return (None, str(ev))
        
    """
    Get the accession code value
    """
    def getAccessionCode(self, row, user):
        try:
            #value = 'PDBDEV_' + ('00000000' + str(row['Accession_Serial']))[-8:]
            """
            Check if we have already an Accession Code
            """
            url = '/entity/PDB:Accession_Code/Entry={}'.format(row['RID'])
            resp = self.catalog.get(
                url
            )
            resp.raise_for_status()
            rows = resp.json()
            if len(rows) == 1:
                row = rows[0]
                return (row['Accession_Code'], None)

            value, error_message = self.getNextAccessionSerial(row['RID'], user)
            self.logger.debug('Accession Code = {}'.format(value))
            return (value, error_message)
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(row['RID'], 'SUBMISSION COMPLETE', Process_Status_Terms['ERROR_GENERATING_ACCESSION_CODE'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            return (None, str(ev))
        
    """
    Store the validation error file into hatrac
    """
    def storeFileInHatrac(self, hatrac_namespace, file_name, file_path, rid, user):
        try:
            newFile = '{}/{}'.format(file_path, file_name)
            file_size = os.path.getsize(newFile)
            hashes = hu.compute_file_hashes(newFile, hashes=['md5', 'sha256'])
            new_md5 = hashes['md5'][1]
            new_sha256 = hashes['sha256'][1]
            hexa_md5 = hashes['md5'][0]
            new_uri = '{}/{}'.format(hatrac_namespace, urlquote(file_name))
            chunked = True if file_size > DEFAULT_CHUNK_SIZE else False
            
            """
            Store the log file in hatrac if they are not already
            """
            hatrac_URI = None
            try:
                outfile = '{}.hatrac'.format(newFile)
                r = self.store.get_obj(new_uri, destfilename=outfile)
                hatrac_URI = r.headers['Content-Location']
                hashes = hu.compute_file_hashes(outfile, hashes=['md5', 'sha256'])
                old_hexa_md5 = hashes['md5'][0]
                os.remove(outfile)
            except:
                old_hexa_md5 = None
            
            if hatrac_URI != None and hexa_md5 == old_hexa_md5:
                self.logger.info('Skipping the upload of the file "%s" as it already exists hatrac.' % file_name)
            else:
                if mimetypes.inited == False:
                    mimetypes.init()
                content_type,encoding = mimetypes.guess_type(newFile)
                if content_type == None:
                    content_type = 'application/octet-stream'
                try:
                    hatrac_URI = self.store.put_loc(new_uri,
                                                         newFile,
                                                         headers={'Content-Type': content_type},
                                                         content_disposition = "filename*=UTF-8''%s" % urlquote(file_name),
                                                         md5 = new_md5,
                                                         sha256 = new_sha256,
                                                         content_type = content_type,
                                                         chunked = chunked
                                                       )
                except:
                    et, ev, tb = sys.exc_info()
                    self.logger.error('Can not upload file "%s" in hatrac "%s". Error: "%s"' % (file_name, new_uri, str(ev)))
                    self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
                    subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'], user)
                    self.sendMail(subject, 'RID={}, Can not upload file "{}" in hatrac at location "{}":\n{}\n'.format(rid, file_name, new_uri, ''.join(traceback.format_exception(et, ev, tb))))
                    return (None, None, None, None)
            return (hatrac_URI, file_name, file_size, hexa_md5)

        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'], user)
            self.sendMail(subject, 'RID={}, Can not upload file "{}" in hatrac at location "{}":\n{}\n'.format(rid, file_name, new_uri, ''.join(traceback.format_exception(et, ev, tb))))
            return (None, None, None, None)

    """
    Cleanup the entry file tables
    """
    def cleanupEntryFileTables(self, entry_id, rid, user):
        try:
            url = '/entity/PDB:Entry_mmCIF_File/Structure_Id={}/mmCIF_Schema_Version={}'.format(urlquote(entry_id), urlquote(self.mmCIF_Schema_Version))
            self.logger.debug('Query URL: "%s"' % url) 
            
            resp = self.catalog.get(url)
            resp.raise_for_status()
            if len(resp.json()) > 0:
                resp = self.catalog.delete(
                    url
                )
                resp.raise_for_status()
                self.logger.debug('SUCCEEDED deleted the rows for the URL "%s".' % (url)) 
                
            url = '/entity/PDB:Entry_Error_File/Entry_RID={}'.format(urlquote(rid))
            self.logger.debug('Query URL: "%s"' % url) 
            
            resp = self.catalog.get(url)
            resp.raise_for_status()
            if len(resp.json()) > 0:
                resp = self.catalog.delete(
                    url
                )
                resp.raise_for_status()
                self.logger.debug('SUCCEEDED deleted the rows for the URL "%s".' % (url)) 
            return 0
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'], user)
            self.sendMail(subject, 'RID={}, Can not cleanup the entry file tables:\n{}'.format(rid, ''.join(traceback.format_exception(et, ev, tb))))
            return 1
            
    """
    Validate the exported mmCIF file
    """
    def validateExportmmCIF(self, input_dir, filename, year, entry_id, rid, user, process_status_error, user_id):
        try:
            if self.cleanupEntryFileTables(entry_id, rid, user) != 0:
                self.cleanupDataScratch()
                return (1, 'Can not cleanup the entry file tables')

            currentDirectory=os.getcwd()
            os.chdir('{}'.format(input_dir))
            args = [self.CifCheck, '-f', '{}/{}'.format(input_dir, filename), '-dictSdb', self.dictSdb]
            self.logger.debug('Running "{}" from the {} directory'.format(' '.join(args), input_dir)) 
            p = subprocess.Popen(args, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            stdoutdata, stderrdata = p.communicate()
            returncode = p.returncode
            os.chdir(currentDirectory)
            
            if returncode != 0:
                self.logger.debug('Can not CifCheck for file "{}".\nstdoutdata: {}\nstderrdata: {}\n'.format(filename, stdoutdata.decode('utf-8'), stderrdata.decode('utf-8')))
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                self.sendMail(subject, 'RID={}, Can not execute CifCheck for file "{}".\nstdoutdata: {}\nstderrdata: {}\n'.format(rid, filename, stdoutdata.decode('utf-8'), stderrdata.decode('utf-8')))
                self.cleanupDataScratch()
                return (1, stderrdata.decode('utf-8'))
            has_errors = False
            try:
                hatrac_namespace = '/{}/generated/uid/{}/entry/id/{}/validation_error'.format(self.hatrac_namespace, user_id, entry_id)
                log_file_name = '{}-diag.log'.format(filename)
                log_file_path = '{}/{}-diag.log'.format(input_dir, filename)
                fr = open(log_file_path, 'r')
                has_errors = True
                fr.close()
                hatrac_URI, file_name, file_size, hexa_md5 = self.storeFileInHatrac(hatrac_namespace, log_file_name, input_dir, rid, user)
                if hatrac_URI == None:
                    self.cleanupDataScratch()
                    return (1, 'Can not store file {} in hatrac'.format(file_name))
                self.logger.debug('Insert a row in the Entry_Error_File table')
                row = {'File_URL' : hatrac_URI,
                       'File_Name': file_name,
                       'File_Bytes': file_size,
                       'File_MD5': hexa_md5,
                       'File_Type': 'validation_diag_log',
                       'Entry_RID': rid,
                       }
                if self.createEntity('PDB:Entry_Error_File', row, rid, user) == None:
                    self.updateAttributes('PDB',
                                          'entry',
                                          rid,
                                          ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                          {'RID': rid,
                                          'Process_Status': process_status_error,
                                          'Record_Status_Detail': 'Error in createEntity(Entry_Error_File)',
                                          'Workflow_Status': 'ERROR'
                                          },
                                          user)
                    self.cleanupDataScratch()
                    return (1, 'Error in createEntity(Entry_Error_File)')
            except:
                pass
            try:
                log_file_name = '{}-parser.log'.format(filename)
                log_file_path = '{}/{}-parser.log'.format(input_dir, filename)
                fr = open(log_file_path, 'r')
                has_errors = True
                fr.close()
                hatrac_URI, file_name, file_size, hexa_md5 = self.storeFileInHatrac(hatrac_namespace, log_file_name, input_dir, rid, user)
                self.logger.debug('Insert a row in the Entry_Error_File table')
                row = {'File_URL' : hatrac_URI,
                       'File_Name': file_name,
                       'File_Bytes': file_size,
                       'File_MD5': hexa_md5,
                       'File_Type': 'validation_parser_log',
                       'Entry_RID': rid,
                       }
                if self.createEntity('PDB:Entry_Error_File', row, rid, user) == None:
                    self.updateAttributes('PDB',
                                          'entry',
                                          rid,
                                          ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                          {'RID': rid,
                                          'Process_Status': process_status_error,
                                          'Record_Status_Detail': 'Error in createEntity(Entry_Error_File)',
                                          'Workflow_Status': 'ERROR'
                                          },
                                          user)
                    self.cleanupDataScratch()
                    return (1, 'Error in createEntity(Entry_Error_File)')
            except:
                pass
            
            
            if has_errors == False:
                hatrac_namespace = '/{}/generated/uid/{}/entry/id/{}/final_mmCIF'.format(self.hatrac_namespace, user_id, entry_id)
            else:
                shutil.move('{}/{}'.format(self.scratch, filename), '{}/{}_error.cif'.format(self.scratch, entry_id))
                filename = '{}_error.cif'.format(entry_id)

            hatrac_URI, file_name, file_size, hexa_md5 = self.storeFileInHatrac(hatrac_namespace, filename, input_dir, rid, user)
            if has_errors == False:
                self.logger.debug('Insert a row in the Entry_mmCIF_File table')
                row = {'File_URL' : hatrac_URI,
                       'File_Name': file_name,
                       'File_Bytes': file_size,
                       'File_MD5': hexa_md5,
                       'Structure_Id': entry_id,
                       'mmCIF_Schema_Version': urlquote(self.mmCIF_Schema_Version)
                       }
                if self.createEntity('PDB:Entry_mmCIF_File', row, rid, user) == None:
                    self.updateAttributes('PDB',
                                          'entry',
                                          rid,
                                          ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                          {'RID': rid,
                                          'Process_Status': process_status_error,
                                          'Record_Status_Detail': 'Error in createEntity(Entry_mmCIF_File)',
                                          'Workflow_Status': 'ERROR'
                                          },
                                          user)
                    self.cleanupDataScratch()
                    return (1, 'Error in createEntity(Entry_mmCIF_File)')
                self.cleanupDataScratch()
                return (0, None)
            else:
                self.logger.debug('Insert a row in the Entry_Error_File table')
                row = {'File_URL' : hatrac_URI,
                       'File_Name': file_name,
                       'File_Bytes': file_size,
                       'File_MD5': hexa_md5,
                       'File_Type': 'mmCIF',
                       'Entry_RID': rid,
                       }
                if self.createEntity('PDB:Entry_Error_File', row, rid, user) == None:
                    self.updateAttributes('PDB',
                                          'entry',
                                          rid,
                                          ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                          {'RID': rid,
                                          'Process_Status': process_status_error,
                                          'Record_Status_Detail': 'Error in createEntity(Entry_Error_File)',
                                          'Workflow_Status': 'ERROR',
                                          },
                                          user)
                    self.cleanupDataScratch()
                    return (1, 'Error in createEntity(Entry_Error_File)')
                self.logger.debug('Update error in export_mmCIF()')
                self.updateAttributes('PDB',
                                      'entry',
                                      rid,
                                      ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                      {'RID': rid,
                                      'Process_Status': process_status_error,
                                      'Workflow_Status': 'ERROR',
                                      'Record_Status_Detail': 'mmCIF Validation Failure. For details, see the files:'
                                      },
                                      user)
                self.cleanupDataScratch()
                return (1, 'mmCIF Validation Failure. For details, see the files at: https://data.pdb-dev.org/chaise/recordset/#1/PDB:Entry_Error_File/Entry_RID={}'.format(rid))
                
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            self.cleanupDataScratch()
            return (1, 'ERROR mmCIF Validation: "%s"' % str(ev))
        
    """
    Cleanup the scratch directory.
    """
    def cleanupDataScratch(self):
        for file_name in os.listdir(self.scratch):
            file_path = '{}/{}'.format(self.scratch, file_name)
            if os.path.isfile(file_path):
                self.logger.debug('Removing file "{}"\n'.format(file_path))
                os.remove(file_path)
            elif os.path.isdir(file_path):
                self.logger.debug('Removing directory "{}"\n'.format(file_path))
                shutil.rmtree(file_path)
        
    """
    Add a table that is not specified in the initial json schema (json-full-db-ihm_dev_full-col-ihm_dev_fulljson file).
    """
    def addTable(self, rid, results, table_name, columns, fw):
        def getColumnValue(table_name, column_name, column_type, column_value):
            if column_value == None:
                return '.'
            if column_type in ['int4', 'float4']:
                return '{}'.format(column_value)
            if column_type == 'text':
                if '\t' in column_value:
                    self.logger.debug('tab character in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value))
                    self.export_error_message = 'ERROR getColumnValue: tab character in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value)
                    return None
                if '\n' in column_value:
                    return '\n;{}\n;\n'.format(column_value)
                if '"' not in column_value and "“" not in column_value and "”" not in column_value and "'" not in column_value and ' ' not in column_value and not column_value.startswith('_'):
                    return column_value
                if '"' not in column_value and "“" not in column_value and "”" not in column_value:
                    return '"{}"'.format(column_value)
                if "'" not in column_value:
                    return "'{}'".format(column_value)
                else:
                    self.logger.debug('Both " and \' are in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value))
                    return '\n;{}\n;\n'.format(column_value)
                self.export_error_message = 'ERROR getColumnValue: Unhandled value in table: {}, column: {}, value: {}'.format(table_name, column_name, column_value)
                return None
            else:
                self.logger.debug('unknown type: {}, table: {}, column: {}'.format(column_type, table_name, column_name))
                self.export_error_message = 'ERROR getColumnValue: unknown type: {}, table: {}, column: {}'.format(column_type, table_name, column_name)
                return None

        """
        Get the RCB user
        """
        user = self.getUser('PDB', 'entry', rid)

        if len(results) > 1:
            fw.write('loop_\n')
            for column in columns:
                fw.write('_{}.{}\n'.format(table_name,column['name']))
            for row in results:
                line = []
                for column in columns:
                    column_name = column['name']
                    column_type = column['type']
                    column_value = row[column_name]
                    value = getColumnValue(table_name, column_name, column_type, column_value)
                    if value == None:
                        self.logger.debug('Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                        subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                        self.sendMail(subject, 'Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                        self.export_error_message = 'ERROR exportData: Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value)
                        return 1
                    line.append(value)
                fw.write('{}\n'.format('\t'.join(line)))
        elif len(results) == 1:
            row = results[0]
            for column in columns:
                column_name = column['name']
                column_type = column['type']
                column_value = row[column_name]
                value = getColumnValue(table_name, column_name, column_type, column_value)
                if value == None:
                    self.logger.debug('Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                    subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'SUBMIT', process_status_error, user)
                    self.sendMail(subject, 'Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value))
                    self.export_error_message = 'ERROR exportData: Could not find column value for ({}, {}, {}, {})'.format(table_name, column_name, column_type, column_value)
                    return 1
                fw.write('_{}.{}\t{}\n'.format(table_name, column['name'], value))
            
        fw.write('#\n')    
        return 0

    def addCollectionRecords(self, rid, entry_id, fw):
        """
        Get the RCB user
        """
        user = self.getUser('PDB', 'entry', rid)

        try:
            url = '/attribute/A:=PDB:entry/id={}/B:=PDB:ihm_entry_collection_mapping/C:=PDB:ihm_entry_collection/C:id,C:name,C:details'.format(urlquote(entry_id))
            self.logger.debug('ihm_entry_collection Query URL: "%s"' % url) 
            resp = self.catalog.get(url)
            resp.raise_for_status()
            results = resp.json()
            if len(results) > 0:
                table_name = 'ihm_entry_collection'
                columns = [
                    {'name': 'id', 'type': 'text'},
                    {'name': 'name', 'type': 'text'},
                    {'name': 'details', 'type': 'text'}
                    ]
                if self.addTable(rid, results, table_name, columns, fw) != 0:
                    error_message = 'ERROR addCollectionRecords: "%s"' % self.export_error_message
                    self.updateAttributes('PDB',
                                          'entry',
                                          rid,
                                          ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                          {'RID': rid,
                                          'Process_Status': Process_Status_Terms['ERROR_RELEASING_ENTRY'],
                                          'Record_Status_Detail': error_message,
                                          'Workflow_Status': 'ERROR'
                                          },
                                          user)
                    return False

            url = '/attribute/A:=PDB:entry/id={}/B:=PDB:ihm_entry_collection_mapping/C:=PDB:ihm_entry_collection/B:collection_id,entry_id:=A:Accession_Code'.format(urlquote(entry_id))
            self.logger.debug('ihm_entry_collection_mapping Query URL: "%s"' % url) 
            resp = self.catalog.get(url)
            resp.raise_for_status()
            results = resp.json()
            if len(results) > 0:
                table_name = 'ihm_entry_collection_mapping'
                columns = [
                    {'name': 'collection_id', 'type': 'text'},
                    {'name': 'entry_id', 'type': 'text'}
                    ]
                if self.addTable(rid, results, table_name, columns, fw) != 0:
                    error_message = 'ERROR addCollectionRecords: "%s"' % self.export_error_message
                    self.updateAttributes('PDB',
                                          'entry',
                                          rid,
                                          ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                          {'RID': rid,
                                          'Process_Status': Process_Status_Terms['ERROR_RELEASING_ENTRY'],
                                          'Record_Status_Detail': error_message,
                                          'Workflow_Status': 'ERROR'
                                          },
                                          user)
                    return False
            
            return True
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'RELEASE READY', Process_Status_Terms['ERROR_RELEASING_ENTRY'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            error_message = 'ERROR addCollectionRecords: "%s"' % str(ev)
            self.updateAttributes('PDB',
                                  'entry',
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': Process_Status_Terms['ERROR_RELEASING_ENTRY'],
                                  'Record_Status_Detail': error_message,
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            return False

    def addReleaseRecords(self, rid, hold=False, user_id=None):
        """
        Get the RCB user
        """
        user = self.getUser('PDB', 'entry', rid)
        
        user_id = self.getUserId('PDB', 'entry', rid)

        try:
            if self.export_mmCIF('PDB', 'entry', rid, release=True, user_id=user_id) != 0:
                """
                We can not recreate the mmCIF exported file
                """
                return

            """
            Query for detecting the mmCIF file
            """
            url = '/entity/PDB:entry/RID={}/PDB:Entry_mmCIF_File'.format(urlquote(rid))
            self.logger.debug('Query URL: "%s"' % url) 
            
            resp = self.catalog.get(url)
            resp.raise_for_status()
            rows = resp.json()
            if len(rows) != 1:
                self.updateAttributes('PDB',
                                      'entry',
                                      rid,
                                      ["Process_Status", "Workflow_Status"],
                                      {'RID': rid,
                                      'Record_Status_Detail': 'ERROR addReleaseRecords: Invalid number of mmCIF files: {}'.format(len(rows)),
                                      'Process_Status': Process_Status_Terms['ERROR_RELEASING_ENTRY'],
                                      'Workflow_Status': 'ERROR'
                                      },
                                      user)
                self.logger.debug('Invalid number of mmCIF files: {}'.format(len(rows))) 
                subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'RELEASE READY', Process_Status_Terms['ERROR_RELEASING_ENTRY'], user)
                self.sendMail(subject, 'Invalid number of mmCIF files: {}'.format(len(rows)))
                return
            row = rows[0]
            file_url = row['File_URL']
            filename = row['File_Name']
            mmCIF_File_rid = row['RID']
            f,error_message = self.getHatracFile(filename, file_url, self.scratch, rid, user)
            if f == None:
                self.updateAttributes(schema,
                                      table,
                                      rid,
                                      ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                      {'RID': rid,
                                      'Process_Status': Process_Status_Terms['ERROR_RELEASING_ENTRY'],
                                      'Record_Status_Detail': error_message,
                                      'Workflow_Status': 'ERROR'
                                      },
                                      user)
                return
            """
            Query for detecting the mmCIF file
            """
            url = '/entity/PDB:entry/RID={}'.format(urlquote(rid))
            self.logger.debug('Query URL: "%s"' % url) 
            
            resp = self.catalog.get(url)
            resp.raise_for_status()
            row = resp.json()[0]
            creation_time = row['RCT']
            year = parse(creation_time).strftime("%Y")
            input_dir = self.scratch
            deposition_date = row['Deposit_Date']
            #deposition_date = parse(row['RCT']).strftime("%Y-%m-%d")
            revision_date = row['Release_Date']
            if revision_date == None:
                revision_date = parse(str(datetime.now())).strftime("%Y-%m-%d")
                self.updateAttributes('PDB',
                                      'entry',
                                      rid,
                                      ["Release_Date"],
                                      {'RID': rid,
                                      'Release_Date': revision_date
                                      },
                                      user)
                
            entry_id = row['id']
            if hold==True:
                record_status = 'HOLD'
                records_release = mmCIF_hold_records.replace('<status_code>', record_status).replace('<entry_id>', row['Accession_Code']).replace('<deposition_date>', deposition_date)
            else:
                record_status = 'REL'
                records_release = mmCIF_release_records.replace('<status_code>', record_status).replace('<entry_id>', row['Accession_Code']).replace('<deposition_date>', deposition_date).replace('<revision_date>', revision_date)
            file_name = '{}.cif'.format(row['Accession_Code'])
            fr = open('{}/{}'.format(input_dir, filename), 'r')
            fw = open('{}/{}'.format(input_dir, file_name), 'w')
            audit_conform = False
            
            while True:
                line = fr.readline()
                if not line:
                    break
                if line.startswith('data_'):
                    fw.write('data_{}\n'.format(row['Accession_Code']))
                elif line.startswith('_entry.id'):
                    fw.write('_entry.id  {}\n'.format(row['Accession_Code']))
                elif line.startswith('_struct.'):
                    if line.startswith('_struct.entry_id'):
                        line = '_struct.entry_id\t{}\n'.format(row['Accession_Code'])
                    fw.write(line)
                elif line.startswith('_audit_conform'):
                    audit_conform = True
                    fw.write(line)
                elif audit_conform == True:
                    fw.write(line)
                    if line == '#\n':
                        audit_conform = False
                        fw.write(records_release)
                        if self.addCollectionRecords(rid, entry_id, fw) == False:
                            fr.close()
                            fw.close()
                            return
                else:
                    fw.write(line)
            fr.close()
            fw.close()
            hatrac_namespace = '/{}/generated/uid/{}/entry/id/{}/final_mmCIF'.format(self.hatrac_namespace, user_id, entry_id)
            hatrac_URI, file_name, file_size, hexa_md5 = self.storeFileInHatrac(hatrac_namespace, file_name, input_dir, rid, user)
            self.updateAttributes('PDB',
                                  'Entry_mmCIF_File',
                                  mmCIF_File_rid,
                                  ["File_URL", "File_Name", "File_MD5", "File_Bytes"],
                                  {'RID': mmCIF_File_rid,
                                  'File_URL': hatrac_URI,
                                  'File_Name': file_name,
                                  'File_MD5': hexa_md5,
                                  'File_Bytes': file_size
                                  },
                                  user)
            self.updateAttributes('PDB',
                                  'entry',
                                  rid,
                                  ["Process_Status", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': Process_Status_Terms['SUCCESS'],
                                  'Workflow_Status': 'REL' if hold==False else 'HOLD'
                                  },
                                  user)
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'RELEASE READY', Process_Status_Terms['ERROR_RELEASING_ENTRY'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            error_message = 'ERROR addReleaseRecords: "%s"' % str(ev)
            self.updateAttributes('PDB',
                                  'entry',
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': Process_Status_Terms['ERROR_RELEASING_ENTRY'],
                                  'Record_Status_Detail': error_message,
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            
    def set_accession_code(self, rid):
        """
        Get the RCB user
        """
        user = self.getUser('PDB', 'entry', rid)
        
        try:
            """
            Query for detecting the record to be processed
            """
            url = '/entity/PDB:entry/RID={}'.format(urlquote(rid))
            self.logger.debug('Query URL: "%s"' % url) 
            
            resp = self.catalog.get(url)
            resp.raise_for_status()
            row = resp.json()[0]
            accession_code, error_message = self.getAccessionCode(row, user)
            if accession_code == None:
                self.updateAttributes('PDB',
                                      'entry',
                                      rid,
                                      ["Process_Status", "Accession_Code", "Workflow_Status", "Record_Status_Detail"],
                                      {'RID': rid,
                                      'Process_Status': Process_Status_Terms['ERROR_GENERATING_ACCESSION_CODE'],
                                      'Accession_Code': None,
                                      'Record_Status_Detail': error_message,
                                      'Workflow_Status': 'ERROR'
                                      },
                                      user)
                return

            if self.is_catalog_dev == True:
                subject = 'PDB-Dev {}: {} ({})'.format(rid, row['Process_Status'], user)
                self.sendMail(subject, 'The Process Status of the entry with RID={} was changed to "{}".'.format(rid, row['Process_Status']), receivers=self.mail_curators)

            self.updateAttributes('PDB',
                                  'entry',
                                  rid,
                                  ["Accession_Code"],
                                  {'RID': rid,
                                  'Accession_Code': accession_code
                                  },
                                  user)
            self.addReleaseRecords(rid, hold=True, user_id=None)
            self.logger.debug('Ended PDB Processing to set the accession code for the PDB:entry table with RID="{}".'.format(rid)) 
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'RELEASE READY', Process_Status_Terms['ERROR_RELEASING_ENTRY'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            error_message = 'ERROR set_accession_code: "%s"' % str(ev)
            self.updateAttributes('PDB',
                                  entry,
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': Process_Status_Terms['ERROR_GENERATING_ACCESSION_CODE'],
                                  'Record_Status_Detail': error_message,
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
        
    def clear_entry(self, rid, id, user):
        try:
            """
            Get the references of the "entry" table 
            """
            references = []
            delete_tables = []
            cols = []
            model_root = self.catalog.getCatalogModel()
            schema = model_root.schemas['PDB']
            table = schema.tables['entry']
            for referenced_by in table.referenced_by:
                pk_table_name = referenced_by.table.name
                for foreign_column in referenced_by.foreign_key_columns:
                    col = foreign_column.name
                    references.append({pk_table_name: col})
                    if col not in cols:
                        cols.append(col)
            
            self.logger.debug('References columns of the PDB:entry table:\n{}"'.format(json.dumps(cols, indent=4))) 
            
            """
            Get the referenced that need to be deleted
            """
            for reference in references:
                for k,v in reference.items():
                    if v == 'Entry_RID':
                        val = rid
                    else:
                        val = id
                    url = '/entity/PDB:{}/{}={}'.format(k, v, val)
                    resp = catalog_ermrest.get(url)
                    resp.raise_for_status()
                    if len(resp.json()) > 0:
                        delete_tables.append(url)
            
            """
            Delete the records referenced by the entry table
            """
            for url in delete_tables:
                resp = catalog.get(url)
                resp.raise_for_status()
                if len(resp.json()) > 0:
                    resp = self.catalog.delete(
                        url
                    )
                    resp.raise_for_status()
                    self.logger.debug('SUCCEEDED deleted the rows for the URL "%s".' % (url)) 
            return 0
        except:
            et, ev, tb = sys.exc_info()
            self.logger.error('got unexpected exception "%s"' % str(ev))
            self.logger.error('%s' % ''.join(traceback.format_exception(et, ev, tb)))
            subject = 'PDB-Dev {} {}: {} ({})'.format(rid, 'DEPO', Process_Status_Terms['ERROR_PROCESSING_UPLOADED_mmCIF_FILE'], user)
            self.sendMail(subject, '%s\n' % ''.join(traceback.format_exception(et, ev, tb)))
            error_message = 'ERROR clear_entry: "%s"' % str(ev)
            self.updateAttributes('PDB',
                                  entry,
                                  rid,
                                  ["Process_Status", "Record_Status_Detail", "Workflow_Status"],
                                  {'RID': rid,
                                  'Process_Status': Process_Status_Terms['ERROR_GENERATING_mmCIF_FILE'],
                                  'Record_Status_Detail': error_message,
                                  'Workflow_Status': 'ERROR'
                                  },
                                  user)
            return 1
        