"""
Fichero         diffdbpg.py
Funcionalidad   Comparar dos bases de datos en postgres para detectar diferencias de estructura de datos
Autor           Alexander Olivares
Email           olivaresa@cantv.net
Fecha           30/05/2007
"""

"""
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
"""

import sys
import string
from pyPgSQL import PgSQL
from pyPgSQL import libpq

#Base de datos 1 que se presume mas actualizada
db1="ad_metro2"            # nombre de base de datos
user1="postgres"             # usuario de base de datos
password1="zidane"         # password de base de datos
host1="localhost"       # host base de datos

#Base de datos 2 que se presume desactualizada
db2="ad_metro"          # nombre de base de datos
user2="postgres"             # usuario de base de datos
password2="zidane"         # password de base de datos
host2="localhost"       # host base de datos

conex1= None
conex2= None

#Consulta para abstraer datos sobre las base de datos
query1 = "select table_schema as esquema,table_name as tabla,column_name as columna,data_type as tipo from information_schema.columns where table_catalog='" + db1 + "' and table_schema<>'pg_catalog' and  table_schema<>'information_schema' order by table_schema,table_name limit 20 OFFSET 0"
query2 = "select table_schema as esquema,table_name as tabla,column_name as columna,data_type as tipo from information_schema.columns where table_catalog='" + db2 + "' and table_schema<>'pg_catalog' and  table_schema<>'information_schema' order by table_schema,table_name limit 20 OFFSET 0"

#funcion para hacer la coneccion a base de datos
def conectar(db,user,password,host):
        return PgSQL.connect(database=db,user=user,password=password,host=host)

try:
    conex1 = conectar(db1,user1,password1,host1)
    conex2 = conectar(db2,user2,password2,host2)
except Exception, error:
    print error
    sys.exit()


#verifica si esta conectado a ambas bases de datos
if conex1 and conex2:
        esquema=""
        tabla=""
        diferencias=0

        cr1 = conex1.cursor()
        cr1.execute(query1)
        cr2 = conex2.cursor()
        cr2.execute(query2)

        matriz1 = cr1.fetchall()
        matriz2 = cr2.fetchall()

        print
        print "Comparando base de datos '%s' en el host '%s' con base de datos '%s' en el host '%s'" %(db1,host1,db2,host2)
        print
        print "Diferencias:"
        print

        for record1 in matriz1:
                dato=None
                for record2 in matriz2:
                        #print "r1=", record1, " - r2=", record2
                        if cmp(record1,record2) == 0:

                                query = "SELECT a.conname, a.conkey FROM pg_constraint a, pg_class b WHERE a.conrelid = b.oid and a.contype = 'p' and b.relname = '"+record1.tabla+"'"
                                cr1.execute(query)
                                result = cr1.fetchall()
                                for fila in result:
                                        tid = fila[0]
                                        idpos = fila[1]

                                query = "select column_name as columna from information_schema.columns where table_catalog='"+db1+"' and table_schema<>'pg_catalog' and  table_schema<>'information_schema' order by table_schema, table_name"
                                cr1.execute(query)
                                result = cr1.fetchall()
                                for fila in result:
                                        campo = fila
##########                      #HERE IS THE PROBLEM
                                qbase1 = "SELECT * FROM " + record1.tabla
                                cr1.execute(qbase1)
                                result = cr1.fetchall()

                                for fila in result:
                                        qbase2 = "SELECT * FROM " + record2.tabla + " a WHERE a." + tid + " = %s" % fila[idpos[0]-1]
                                        cr2.execute(qbase2)
                                        result2 = cr2.fetchall()
                                        for fila2 in result2:
                                                for ii in range(0,len(fila2)):
                                                        if fila[ii] <> fila2[ii]:
                                                                print "Diferencia Columna: %s" % idpos[0]
                                                                print "Valor en la base de datos " + db1 +": %s" % fila[ii]
                                                                print "Valor en la base de datos " + db2 +": %s" % fila2[ii]
                                                break

                                dato=1
                                break
                if(dato==None):
                        #dic = {record1.esquema : {record1.tabla : [record1.columna,record1.tipo] } }
                        if esquema <> record1.esquema:
                                esquema = record1.esquema
                                print "+ %20s" % (string.ljust(esquema,20))
                        if tabla <> record1.tabla:
                                tabla = record1.tabla
                                print "%20s - %20s" % ("",string.ljust(tabla,20))

                        print "%60s * %30s ,%15s" % ("",string.ljust(record1.columna,30),string.ljust(record1.tipo,15))
                        diferencias += 1

        print "%s diferencias encontradas" % diferencias
        print "Leyenda:"
        print "+ = Esquemas"
        print "- = Tablas"
        print "* = Campos"
        print
        print
        print "Corriendo en sistema operativo %s" % sys.platform

        cr1.close()
        cr2.close()
        conex1.close()
        conex2.close()

else:
        print "No se pudo conectar a las bases de datos...!"