Desenmarañando las estructuras en PostgreSQL

Buscar

Tiras Cómicas

Mi Twitter

Follow me

Sábado, 14 de julio del 2007

Publicado por Ernesto Quiñones en PostgreSQL a las 09:01 | Comentarios (-9) | Referencias (0)

Desenmarañando las estructuras en PostgreSQL

Hace unos dias un cliente me pidio que pasaramos estructuras data de PostgreSQL a Oracle, es una jarana hacer esto ya que en el PostgreSQL no engo menos de 100 tablas diferentes y la verdad es bastante tedioso estar haciendolo tabla por tabla.

Se me ideo crear un programita en PHP, que corriendo desde una consola cada cierto tiempo refrescara estructuras y data en el Oracle, así me liberaba del problema de tener que cambiar manualmente el programa cada vez que se creara un nuevo campo o tabla.

Para saber como jalar los datos de estructuras del PostgreSQL tome de referencia phpPgAdmin, magnifico, lo tenia en una clase con los querys necesarios para leer toda la estructura de la db y tome los datos que solo a mi me importaban, aqui les dejo el programitca con algunos comentarios.

<?php
aqui esta todo para conectarme al postgresql, lo convencional 
require_once("adodb/adodb.inc.php");
require_once("db.inc");
en esta linea me conecto al oracle
 
$oracle = oci_connect('user', 'password', '1.1.1.1:1521/dbreceptoradeloracle');
inicio con un loop porque lo voy a ejecutar eternamente pero en un determinado periodo de tiempo, por eso mas abajo veran un sleep
 
while ( 1==1) {
este query lo que hace es jalar la relación de tablas en un determinado esquema, en principo solo me interesa el nombre de las tablas por eso solo jalo el campo "RELNAME"
 
$qry = "select  c.relname from pg_class c
        where c.relkind = 'r' and not exists ( select 1 from pg_rewrite r where r.ev_class = c.oid and r.ev_type = '1' )
        order by c.relname";
$rs = $conn->execute($qry);
if ( $rs <> false ) {
    $intTbl = $rs->rowcount();
    echo "tablas encontradas {$intTbl} se procesan solo las del sistema\n";
inicio un loop del proceso tabla por tabla
    for ( $i=0; $i <= $rs->rowcount(); $i++) {
        $obj = $rs->fetchobject(true);
el query me jala todas las tablas, incluso algunas que no me interesan, eso incluye tambien las propias de administracion de postgresql, asi que como las tengo identificadas con ciertos prefijos solo proceso aquellas que me interesan
        if ( substr($obj->RELNAME,0,3) == "mae" or substr($obj->RELNAME,0,3) == "mov" or substr($obj->RELNAME,0,3) == "fij" or
             substr($obj->RELNAME,0,3) == "tab" or substr($obj->RELNAME,0,3) == "his" or substr($obj->RELNAME,0,3) == "cor" or
             substr($obj->RELNAME,0,3) == "arc" or substr($obj->RELNAME,0,3) == "cer" or substr($obj->RELNAME,0,3) == "log") {
            echo "Procesando " . $obj->RELNAME . "...." . date("H:i:s") . "\n";
este query permite jala la estructura de cada tabla, existe un problema para los numeric, no muestra algo como numeric(12,2) sino un numeric(122345) que no pude interpretar, el resto es legible, los varchar tiene 4 bytes mas de extension asi que si uds. declararon un campo varchar(20) aqui lo mostrara como 24 de longitud.
            $qry = "select a.attname, t.typname, a.attlen, a.atttypmod
                from pg_attribute a, pg_class c, pg_type t
                where c.relname = '{$obj->RELNAME}' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid
                order by a.attname";
            $rsT = $conn->execute($qry);
            if ( $rsT <> false ) {
aqui comienzo armar 2 querys, el del create table, que primero dropeare para esegurarme de pasar la ultima estructura, y la de los campos del insert
                $qry = " create table {$obj->RELNAME} (";
                $qryIns = "insert into {$obj->RELNAME} (";
                for ( $a=0; $a < $rsT->rowcount(); $a++) {
                    $objT = $rsT->fetchobject(true);
estos if lo que hacen es hacer unas equivalencias de los tipos de datos de postgresql a oracle
                    if ( $objT->TYPNAME == "int4") {
                        $tipo = "integer";
                    }
                    if ( $objT->TYPNAME == "text") {
                        $tipo = "clob";
                    }
                    if ( $objT->TYPNAME == "bpchar" or $objT->TYPNAME == "varchar") {
                        $tipo = "varchar({$objT->ATTTYPMOD})";
                    }
                    if ( $objT->TYPNAME == "date" or $objT->TYPNAME == "timestamp") {
                        $tipo = "date";
                    }
                    if ( $objT->TYPNAME == "numeric") {
                        $tipo = "numeric(20,10)";
                    }
 
                    $qry .= " {$objT->ATTNAME} {$tipo},";
                    $qryIns .= " {$objT->ATTNAME},";
                    $rsT->movenext();
                }
 
                $qry = substr($qry,0,strlen($qry)-1);
                $qry .= ")";
 
                $qryIns = substr($qryIns,0,strlen($qryIns)-1);
                $qryIns .= ") values (";
 
                echo "Dropeando tabla: {$obj->RELNAME} \n";
entro al oracle a dropear las tablas
                $orcl = oci_parse($oracle, "drop table {$obj->RELNAME}");
                oci_execute($orcl, OCI_DEFAULT);
                //echo "Creando tabla: " . $qry . "\n";
vuelvo a crear la tabla
                echo "Creando tabla: {$obj->RELNAME}\n";
                $orcl = oci_parse($oracle, $qry);
                oci_execute($orcl, OCI_DEFAULT);
                echo "Leyendo Data del PostgreSQL \n";
comienzo a jalar la data del postgresql que tengo que pasar al oracle
                $qry="select  from {$obj->RELNAME}";
                $rsI= $conn->execute($qry);
                if ( $rsI <> false ) {
                    $corte = 0;
                    $proceso ="";
                    $conta = 0;
                    while ($arr = $rsI->FetchRow() ) {
                        $corte++;
                        $conta++;
                        $qry="";
                        $rsT->movefirst();
                        for ( $a=0; $a < $rsT->rowcount(); $a++) {
                            $objT = $rsT->fetchobject(true);
comienzo a armar las columas una a una en el orden correcto del insert, asi si en el transcurso de una subida de datos cambian la estructura en el postgresql no me preocupo, el adodb me ayuda bastante porque cuando paso la data de registor por registor a un arreglo me la pasa con los nombres de los campos, los nombres fisicos y puedo cruzarlo contra lo que me devolvio en los nombres en la jalada de estructura de la tabla
                            if ( is_null($arr[$objT->ATTNAME]) ) {
                                $valor = "null,";
                            } else {
                                if ( $objT->TYPNAME == "int4" or $objT->TYPNAME == "numeric") {
                                    $valor = $arr[$objT->ATTNAME] . ",";
                                }
                                if ( $objT->TYPNAME == "text" or $objT->TYPNAME == "bpchar" or $objT->TYPNAME == "varchar") {
                                    $valor = "'" . str_replace('"','',str_replace("'","",$arr[$objT->ATTNAME])) . "',";
                                }
                                if ( $objT->TYPNAME == "date" or $objT->TYPNAME == "timestamp") {
                                    $valor = "to_date('" . substR($arr[$objT->ATTNAME],0,10) . "','yyyy-mm-dd'),";
                                }
                            }
                            $qry .= $valor;
                            $rsT->movenext();
                            }
 
                        $qryOrcl =  $qryIns . substr($qry,0,strlen(trim($qry)) - 1) . ")";
                        $proceso .= " $qryOrcl ;";
esto de aqui es por facilidad mia, paso los inserts en bloques de 200 registros, es mucho mas rapido que hacerlo 1 por 1, no e probado si puedo pasar muchos mas, de mil en mil por ejemplo, seguramente eso haria que demore menos la pasada, pero de momento con 200 va bien, el futuro del script esta en que se pasen los datos con copy y no con insert, se supone que deberia ser mas rapido.
 
                        if ( $corte == 200) {
                            $corte = 0;
para pasar en mancha varios registros tienes que pasarlo como una transacción en bloque en el oracle, por eso esta el begin y el end, sino no pasa.
                            $proceso = "begin $proceso end;";
                            $orcl = oci_parse($oracle, $proceso);
                            if ( !orcl)   {
                                echo "ERROR $proceso";
                                exit;
                            } else {
                                $tmp = oci_execute($orcl, OCI_DEFAULT);
                                if ( !$tmp ) {
                                    echo "ERROR $proceso";
                                    exit;
                                } else {
                                    echo "$conta.";
                                }
                            }
                            $proceso = "";
                        }
                    }
 
                    if ( $conta > 0 ) {
                        $proceso = "begin $proceso end;";
                        $orcl = oci_parse($oracle, $proceso);
                        if ( !orcl)   {
                            echo "ERROR $proceso";
                            exit;
                        } else {
                            $tmp = oci_execute($orcl, OCI_DEFAULT);
                            if ( !$tmp ) {
                                echo "ERROR $proceso";
                                exit;
                            } else {
                                echo "$conta\n";
                            }
                        }
                    }
                }
            }
        }
esto siempre deben hacerlo, liberen la memoria de los recordset de data que jalaron, adodb se comportmo muy bien, las tablas mas grandes tenian mas de 250k registros y se lo chupo como si nada, pero siempre es bueno ayudar al motor a limpiar la memoria.
        unset ($rsI);
        unset ($rsT);
        $rs->movenext();
    }
} else {
    echo "No se pudo procesar la consulta: $qry\n";
}
ok aqui esta un sleep que espera 4 horas para volver a lanzarce, esto podria ser reemplazado por un cron tambien, la ventaja de tenerlo corriendo en un terminal es que rapidamente se dan cuenta si el proceso fallo por alguna causa, cual podria ser la causa, solo nos paso una vez pero el oracle se trabo cuando alguien bajo el php de golpe, porque? vaya a saber Dios.
 
echo "ESPERANDO 4 HORAS PARA EL SIGUIENTE PASE DATOS, HORA ACTUAL " . date("H:i:s") .  "\n";
sleep(14400);
//sleep(18);
}
?> 

UPDATE al 16/07/2007
Gracias al aporte de Alvaro Herrera, esta es la formula para pasar el número entero que devuelve en las comunas numerci al formato numeric(x,y)

Esta interesante ...

Con respecto a los numeric, la forma de sacar el (12,2) es

scale = attypmod << 16,
weight = atttypmod - scale - 4

o sea

select
attname,
atttypmod >> 16,
atttypmod - ((atttypmod >> 16) << 16) - 4
from pg_attribute
where attrelid = 'num'::regclass;

| Salidas (0)

Vote for articles fresher than 7 days!
Karma actual: -3, 8 voto(s) 6016 hits

Referencias

URI de referencia para esta entrada

No hay referencias

Comentarios

Mostrar comentarios como (Plano | Hilos)

Ernesto , primeramente me presento, soy estudiante de 4to año de Ingeniería Informática en Cuba, y estamos inmersos , mis compañeros y yo en un proyecto personal de hacer una guia de migración bases de datos Oracle a Postgresql, y este post que veo aqui está realmente muy bueno.
Ahora tenemos una preguntica para tí.¿Cómo pudiéramos hacer eso mismo que tu hiciste pero al revés? o sea pasar schemas y data de Oracle a Postgresql, para ayudar a los proyectos de mi facultad que todavía usan Oracle, y demostrarles la buena alternativa que tenemos en Postgresql. De más está decirte que somos fanáticos a este ORDMS .
Saludos desde Cuba
Marcos Ortíz Valmaseda
Linux User # 418229
La Habana Cuba

#1 Marcos Ortiz Valmaseda (URL personal) activado 02.03.2008 10:41 (Responder)

No podría ayudarte en eso, tendría que instalar una copia ilegal de Oracle para comenzar a bucear por ahí como almacena las estructuras de las tablas, pero casí seguro que debe poder hacerse.

Para un cliente hemos echo algo parecido pero a lo bruto, copiamos las estructuras de un lado a otro manualmente, en realidad las rehicimos y luego programamos un php que leía la data del Oracle y la pasaba a Postgresql y viceversa, nada complicado, se pasaba mas o menos 1GB de data en media hora.

Date una vuelta por Gborg o PgFoundry alguna vez vi que alguien tenia un proyecot ya andando para migrar de Oracle a Postgresql.

Como último "apoyo", Oracle permite exportar su data en formato SQL, casí seguro que con pequeños cambios en los tipos de datos no compatibles debería ser casi transparente poder volver a subirlos.

suerte

#1.1 Ernesto Quiñones activado 02.03.2008 16:20 (Responder)

Añadir comentario

Nombre

Correo electrónico

URL personal

En respuesta a

Comentario

Encerrando entre asteriscos convierte el texto en negrita (*palabra*), el subrayado es hecho así: _palabra_.
Smilies normales como :-) y ;-) son convertidos en imágenes.

Para prevenir un ataque spam en los comentarios por parte de bots, por favor ingresa la cadena que ves en la imagen mostrada más abajo en la apropiada caja de texto. Tu comentario será aceptado sólo si ambas cadenas son iguales. Por favor, asegúrate que tu navegador soporta y acepta cookies, o tu comentario no podrá ser verificado correctamente.

Ingresa la cadena de protección contra spam de la imgen mostrada abajo:

¿Recordar la información?
Suscribirse a esta entrada