#!/usr/bin/env python
# Copyright 2008 ETH Zuerich, CISD
#
# 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.

##
# For a given <mapping-file> creates <output-file> containing SQL queries updating location fields 
# in external data table.  
#
# @author: Izabela Adamczyk
##

import os, sys, csv, string
from optparse import OptionParser

def generateUpdateQuery(code, location):
    """
    Returns sql query updating location field in external_data table, identifying old location using code
    """
    return "UPDATE external_data set location='"+location+"' WHERE data_id IN (SELECT id FROM data WHERE code='"+code+"');"
        
def readMappingFile(mappingFile):
    """
    Reads the code-location mapping file and returns a list of sql update queries
    """
    assert os.path.isfile(mappingFile)
    file = open(mappingFile, "r")
    reader = csv.reader(file, delimiter='\t')
    queries = []
    try:
        try:
            for row in reader:
                cols = []
                for col in row:
                    cols.append(string.strip(col))
                code = cols[0]
                location = cols[1]
                queries.append(generateUpdateQuery(code, location))
        except csv.Error, e:
            sys.exit('file %s, line %d: %s' % (mappingFile, reader.line_num, e))
    finally:
        file.close()
    return queries

def generateSqlFile(queries, fileName):
    """
    Creates a file containing SQL queries updating location field
    """
    f = open(fileName, 'w')
    f.write("BEGIN;"+"\n");
    for query in queries:
        f.write(query+"\n")
    f.write("COMMIT;"+"\n");
    f.close
    
def main():
    """
    Main method.
    """
    parser = OptionParser("usage: %prog <mapping-file> <output-file>")
    (options, args) = parser.parse_args()

    if len(args) == 2:
        mappingFile = args[0]
        outputFile = args[1]
        queries = readMappingFile(mappingFile)
        print "Mapping file '%s' loaded: %d code-location mappings have been found." % (os.path.abspath(mappingFile), len(queries))
        generateSqlFile(queries, outputFile)
        print "Sql queries were stored in file '" + outputFile + "'."
        command = "psql -f "+outputFile+" -d DATABASE"
        print "Use '" + command +"' to execute queries."
    else:
        parser.print_help()
        sys.exit(1)
        
if __name__ == '__main__':
    main()