dblink jsp postgres

 dblink jsp postgres

Debido a la necesidad de integrar la información de las bases de datos en un solo punto para poder tener el control de distintos centros administrativos, nos dimos a la tarea de encontrar un forma de hacer de una forma sencilla pero eficiente, se encontró la propuesta del dblink , que precisamente hace esa función de una forma muy eficiente, sin embargo en la vida real , no todo es tan sencillo, pues ocurre que e nuestros distintos Centos usamos  casi todo el abanico de versiones del postgres desde la versión 8.1 hasta la 9.2 y que por razones que no se van a discutir a aquí se está a varios meses de que todos estén en una versión siquiera mas cercana la la última que este en ese momento  , sin embargo esto planteo un problema a resolver, que que como dicen los manuales de los que saben sobre este tema , la versión 9.2 tiene introspección de datos, lo que quiere decir que si deseo copiar los datos de la base de datos B en la base de datos A, solo basta ejecutar el comando dblink apropiado y basta, siempre y cuando las dos bases de datos tengan los mismos campos, no ha mucho problema en ello y funciona como un reloj, sin embargo como decía , nuestra sistema organizativo, no es ni de lejos perfecto y las versiones 8.1, 8.4 etc no tiene introspección y hay que detallar,uno a uno los campos que se van a consolidar , incluyendo el tipo y la longitud del campo, algo muy rígido, demorado y porque no decirlo frustrante, y si ademas de están modificando las bases de datos darle soporte a ese sistema  es un verdadero infierno.

Se decidio hacer por así decirlo nuestra propia introspección , si se me permite el uso ligero del termino , para no tener que estar pendiente de las bases, tipos de campos, actualización  versiones, nada del otro mundo y eventualmente un poco rustico pero nos sirvió a las mil maravillas.

Como lo primero es saber que el dblink , no viene por defecto con el postgres hay que instalar el paquete en el cual se encuentra, en nuestro caso Centos 7.0 en las librerías Contrib. Luego debemos debemos habilitar nuestras bases de datos que van a compartir la información por cada un de ellas ha que hacer el mismo proceso y la información va fluir de unas a otras,como lo que se pretende es consolidar la información para analisis en una sola DB , el proceso solo hubo necesidad de hacerla en la DB de consolidación.

 Ubicamos donde quedaron instaladas las librerias contrib, especificamente el un sencillo locate no puede ayudar con el tema 

#locate dblink.sql En nuestro caso lo ubicamos en /usr/share/pgsql/contrib/dblink.sql , nos cambiamos a ese directorio. (Muy importante hay que cambiarse al directorio donde esta el dblink.sql , sino no lo encuentra , es un error muy frecuente.) 

cd /usr/share/pgsql/contrib/dblink.sql 

Ingresamos a postgres para instalar el sql en nuestra DB mibase es la DB donde vamos a llevar los datos que estamos migrando, en mi caso es el centro de consolidación 

# su postgres 

$ psql mibase -U postgres < dblink.sql 

$ exit 

Una muy buena opción es instalar el sistema en templates1 para que cuando se cree una nueva DB quede de una vez instalado el dblink, ojo las que ya están hay que hacerles el proceso si se necesita que reciban datos desde otra DB

# su postgres 

$ psql templates1 -U postgres < dblink.sql 

En postgres 9.2 el proceso se puede instalar de una forma directa mediante 

$ psql postgres psql 

(9.2beta1) Type "help" for help. 

$ postgres=CREATE EXTENSION 

dblink CREATE EXTENSION 

Ya tenemos instalado el dblink, vamos entonces hacer nuestro pequeño programa de introspección que nos permite integrar diferentes versiones de bases de datos,nos en que consultar la estructura de la base de datos en cuestión y pasarla como parámetros al dblink , que como dije necesita la definición de campos (la versión 9.2 no necesita este modelo ya que lo tiene incorporado). ***********************************************************************************

/* Paso como parámetro el nombre de la tabla de la cual voy a extraer los  datos y recibo como respuesta un vector con los nombres de los campos, tipo y extensión de los campos a extreaer 

*/

 public final synchronized Vector CamposTabla(String gTabla)
{
StringBuffer sqlSb = new StringBuffer() ;
Vector campos = new Vector() ;
Vector linea = new Vector() ;

try
{

sqlSb.append(" SELECT ") ;
sqlSb.append(" z.relname, " ) ;
sqlSb.append(" a.attname, " ) ;
sqlSb.append(" pg_catalog.format_type(a.atttypid, a.atttypmod)") ;
sqlSb.append(" AS TYPE ") ;
sqlSb.append(" FROM " ) ;
sqlSb.append(" pg_catalog.pg_attribute a ") ;
sqlSb.append(" LEFT JOIN " ) ;
sqlSb.append(" pg_catalog.pg_attrdef adef " ) ;
sqlSb.append(" ON a.attrelid=adef.adrelid " ) ;
sqlSb.append(" AND " ) ;
sqlSb.append(" a.attnum=adef.adnum " ) ;

sqlSb.append(" JOIN " ) ;
sqlSb.append(" pg_catalog.pg_class z ") ;
sqlSb.append(" ON " ) ;
sqlSb.append(" a.attrelid = z.oid " ) ;
sqlSb.append(" AND " ) ;
sqlSb.append(" z.relnamespace = " ) ;
sqlSb.append(" ( " ) ;
sqlSb.append(" SELECT oid " ) ;
sqlSb.append(" FROM pg_catalog.pg_namespace " ) ;
sqlSb.append(" WHERE " ) ;
sqlSb.append(" nspname = 'public' " ) ;
sqlSb.append(" ) " ) ;

sqlSb.append(" WHERE " ) ;
sqlSb.append(" a.attnum > 0 ") ;
sqlSb.append(" AND NOT " ) ;
sqlSb.append(" a.attisdropped " ) ;
sqlSb.append(" AND " ) ;
 

sqlSb.append(" ( " ) ;
sqlSb.append(" z.relname NOT ilike '%_seq' " ) ;
sqlSb.append(" AND " ) ;
sqlSb.append(" z.relname NOT ilike '%_pk') " ) ;
sqlSb.append(" AND " ) ;
sqlSb.append(" (z.relname= " + "'" + Lower(gTabla) + "')");
sqlSb.append(" ORDER BY z.relname, a.attnum ");

rt = st.executeQuery(sqlSb.toString()) ;
while (rt.next())
{
linea = new Vector() ;
linea.add(rt.getString(2)) ;// campo nombre
linea.add(rt.getString(3)) ;// tipo de campo

campos.add(linea) ;
}


return campos ;

}
catch(Exception e)
{
bufferError.append("Error campos de la tabla:" + e) ;
return campos ;
}
}
 

/* Dado la forma como los datos deben ser presentados a dblink, separo el vector de la estructura de la tabla en dos vectores uno con los nombres y otro con los tipo y extensión de los campos, el primero llamado campos y el segundo tipos

 */

public final synchronized String Campos(Vector campos)
  {
    Vector linea  = new Vector()                 ;
    String c      = new String()                 ;// en c esta la descripcion de los campos ej, registro

    for (int i= 0 ; i < campos.size() ; i++)
       {
          linea = new Vector()                   ;
          linea = (Vector) campos.elementAt(i)   ;

          c    += linea.elementAt(0).toString()  ;
          if ( i < campos.size()-1 )
            {
              c += ","                           ;// no coloca coma al final de la cadena
            }
       }

    return c                                     ;
  }

//--------------------------------------------------------------------------------------------------------------------

public final synchronized String Tipos(Vector campos)
  {

    Vector linea  = new Vector()               ;
    String t      = new String()               ;// en t esta descripcion y tipo ej: registro varchar(20)

    for (int i= 0 ; i < campos.size() ; i++)
       {
          linea = new Vector()                   ;
          linea = (Vector) campos.elementAt(i)   ;

          t    += linea.elementAt(0).toString()  ;
          t    += " "                            ;
          t    += linea.elementAt(1).toString()  ;
          if ( i < campos.size()-1 )
            {
              t += ","                           ;// no coloca una coma al final de la cadena
            }
       }


    /*
       los tipos de datos generados por la rutina de exploracion de
       tablas CamposTablas(),son tipo character varing(##), que no son
       reconocidos por dblink el cual los reconoce varchar(#).
       hice un ajuste mediante replaceAll().
    */
    t = t.replaceAll("character", "")             ;
    t = t.replaceAll("varying", "varchar")        ;
    return t                                      ;
  }

// Un pequeño ejemplo real aquí

public final synchronized String ConsolidarFecha(String db,String gFecha)
{
StringBuffer sqlSb = new StringBuffer() ;
String dblinkConnect = new String() ;
dblinkConnect = "'host=127.0.0.1 dbname=" + db + "'" ;
Vector campos = new Vector() ;
String camposTabla = new String() ;
String tiposTabla = new String() ;

try
{

int filasAfectadas = 0 ;
try
{

/*Aquí borro el pkey , porque las diferentes tablas tenían claves similares y no deja consolidar , luego al terminar el proceso genero de nuevo la pkey 

*/

 
sqlSb.append(" ALTER TABLE ") ;
sqlSb.append(" CARTERA ") ;
sqlSb.append(" DROP CONSTRAINT cartera_pkey" ) ;
filasAfectadas = st.executeUpdate(sqlSb.toString()) ;
}
catch(Exception e1)
{
}

sqlSb = new StringBuffer() ;

/*En las 3 lineas siguientes obtengo los campos acondicionados de la tabla  que deseo integrar, para pasarlos como parámetros al dblink CARTERA y BILLETERA son las tablas en cuestion, del ejercicio a consolidar. El ejercicio completo se consolidan 18 tablas , donde por ejemplo CARTERA tiene 2.400.000 registros y BILLETERA 1.700.000 , las demás oscilan entre 300.000 y 400.000 , en diferentes servidores locales y remotos el proceso completo promedio  dura 3 minutos.

*/


campos = CamposTabla("CARTERA") ;
camposTabla = Campos(campos) ;
tiposTabla = Tipos(campos) ;
sqlSb.append(" INSERT INTO ") ;
sqlSb.append(" CARTERA " ) ;

sqlSb.append(" SELECT * FROM " ) ;

sqlSb.append("dblink(") ;
sqlSb.append( dblinkConnect ) ;
sqlSb.append( "," ) ;
sqlSb.append(" 'SELECT ") ;
sqlSb.append( camposTabla ) ;
sqlSb.append(" FROM CARTERA " ) ;

sqlSb.append(" ') as t1( " ) ;
sqlSb.append( tiposTabla ) ;

sqlSb.append(" )") ;

sqlSb.append(" WHERE ") ;
sqlSb.append(" CarteraFecha= " ) ;
sqlSb.append( "'" ) ;
sqlSb.append( DtoCSql(CtoD(gFecha))) ;
sqlSb.append( "'" ) ;

filasAfectadas = st.executeUpdate(sqlSb.toString()) ;

//---------------
try
{
sqlSb = new StringBuffer() ;

sqlSb.append(" ALTER TABLE ") ;
sqlSb.append(" BILLETERA ") ;
sqlSb.append(" DROP CONSTRAINT billetera_pkey" ) ;
filasAfectadas = st.executeUpdate(sqlSb.toString()) ;
}
catch(Exception e1)
{
}

sqlSb = new StringBuffer() ;

campos = CamposTabla("BILLETERA") ;
camposTabla = Campos(campos) ;
tiposTabla = Tipos(campos) ;

sqlSb.append(" INSERT INTO ") ;
sqlSb.append(" BILLETERA " ) ;

sqlSb.append(" SELECT * FROM " ) ;

sqlSb.append("dblink(") ;
sqlSb.append( dblinkConnect ) ;
sqlSb.append( "," ) ;
sqlSb.append(" 'SELECT ") ;
sqlSb.append( camposTabla ) ;
sqlSb.append(" FROM BILLETERA " ) ;

sqlSb.append(" ') as t1( " ) ;
sqlSb.append( tiposTabla ) ;

sqlSb.append(" )") ;

sqlSb.append(" WHERE ") ;
sqlSb.append(" BilleteraFecha= " ) ;
sqlSb.append( "'" ) ;
sqlSb.append( DtoCSql(CtoD(gFecha))) ;
sqlSb.append( "'" ) ;

filasAfectadas = st.executeUpdate(sqlSb.toString()) ;


return "ok" ;
}
catch(Exception e)
{
bufferError.append("Error Consolidando:" + e) ;
return ("Error " + e + sqlSb.toString()) ;
}
}
%>

Como se puede ver el proceso es sencillo, ya que el uso de dblink lo es, la unica pega que tiene las versiones anteriores es tner que pasar como parametros con el nombre y descipcion del campo, que aqui planteo con una potencial solución que nos esta funcionado bien.