[Javier Ader]

Buenas. A partir de los temas que surgieron en la instalación de componentes me di cuenta que el tema es un problema más general: JDBC no permite múltiples sentencias en un solo java.sql.PreparedStatement. Me puse a investigar un poco alguna forma de saltear esto y encontré la sentencia PREPARE de Posgres pero de nuevo esta solo permite sentencias simples (hay un PREPARE mas avanzado pero requiere un lenguaje procedural que creo que no está instalado con libertya). La otra forma que encontré que puede sonar poco elegante, pero creo que funciona es lo siguiente:
-se crea una función plpgsql que no te tenga parámetros de entrada y cuyo parámetro de salida se character varying (o tal vez void, pero en ese caso creo que cambia la forma de invocarla) y cuyo cuerpo sea el sql arbitrario a ejecutar. Esto se hace en un primer PreparedStatement
-se invoca a la función mediante un segundo PreparedStatement, lo que debería terminar siendo equivalente a ejecutar el sql arbitrario.
-opcionalmente, se hace un drop de la función creada antes.
Doy una idea del pseudocódigo de como se podría usar en la instalación de componentes:

String sqlArb = "cualquier string de sql con finales de linea, puntos y comas , etc; leido posiblemente desde un archivo";
//la idea del return es que el sql arbitario
// retornar una string no vacia como para informar el error
// en caso de que exista
String sqlArbConReturn =
"BEGIN " + NL + //NL = new line
"set search_path to libertya;" + NL + //setear el search_path quizás no sea necesario... quizás si...
sqlArb + NL +
" RETURN '';" +
"END ";
int ad_component_version_id = xxx
//le agrego el id de la version del componente para
//evitar "choques" de nombres; que aunque hubiese, no
//traería mayor problema siempre que se respeten la
//cantidad de parámetros de entrada y salida de la función
String nameFunction = "fComVersion" + ad_component_version_id;

//se crea la función "temporal"

//aca es importante usar ? para el cuerpo de la función
//si no jdbc (o postgres?) va a preprocesarlo y por ej
//eliminar los LF
String createFunction = "CREATE OR REPLACE FUNCTION "+
nameFunction +
"() RETURNS CHARACTER VARYING AS ? LANGUAGE 'plpgsql' "+

PreparedStatement pstmt = null;
String res = null;
try{
pstmt = DB.prepareStatement (createFunction);
pstmt.setString(1,sqlArbConReturn )
//creo la funcion
pstmt.execute();
// la ejecuto, lo cual es lo mismo que
//ejecutar el sql arbitrario
pstms = DB.prepareStatement( "SELECT * FROM " nameFunction
+ "()");
ResutlSet rs = pstms.execute();
if (rs.next())
res = rs.getString(1)
}
cath{
....
}
if (res != null)
//error diparado por el sql arbitrario
hacer algo, como un rollback


Después hago un test o pruebo modificando el código de instalación de componente en el que se ejecuta el preinstall.sql, pero creo que debería funcionar.

Ahora, más allá de que este tema surgió por los componentes quizá se podría usar en otros lugares, por ej en la generación de tablas temporales para informes (por ej, en vez de hacer muchisimos inserts individuales, se crea una sola función “temporal” que los haga todos dentro de su cuerpo y luego se la ejecuta; esto reduce los accesos a base de datos a solo 2). Obviamente, en caso de ser usado de manera usual habría que tener en cuenta temas como hacer un “drop” de la función despues de ser usada y/o algun tipo de convención para que distintos procesos no intenten usar la misma función.

[Federico Cristina]

Buenas,

La alternativa que comentas fue una de las tantas que evaluamos al momento de implementar el instalador de componentes.

Aun cuando habiamos logrado ejecutar multiples sentencias en un unico envio al DBMS, esto no nos era de utilidad, ya que lo primero que éste hace es parsear el contenido del SQL, y toda referencia inexistente es devuelta como erronea (suponer por ejemplo un CREATE TABLE y luego un INSERT en dicha tabla)

Es por esto que fue necesario enviar sentencia por sentencia inevitablemente. El resultado es el mismo debido al uso de una unica transaccion. La mejora en la performance creo que debe ser notoria y de ahí nuestra primera intencion en resolverlo con dicha aproximación.

Saludos,
Federico

[Javier Ader]

Que tal Federico. Al final no tuve tiempo de probarlo desde libertya, pero no entiendo bien lo que me estas queriendo decir, en particular el tema de que no resuelve las referencias. Yo lo que probe fue, desde pgAdmin, crear un función que creaba una tabla y un vista y luego la ejecute; esta ejecución creó la tabla y la vista lo más bien.
En tiempo de creación de una función postgres debe hacer algún tipo de parsing, pero no hace chequeo de referencias; la siguiente función se crea lo más bien

Code:

set search_path to libertya;

CREATE OR REPLACE FUNCTION fEjFunction2 () RETURNS CHARACTER VARYING AS $$
BEGIN

set search_path to libertya;
create table ejTabla2(
id integer not null,
name character varying(40)

);
— insert en la tabla creada justo antes
insert into ejTabla2(id,name) values (10,’nombre’);
create view ejView2 as select * from ad_client;

RETURN ”;
END;
$$ LANGUAGE ‘plpgsql’;

Fijate que el insert hace referencia a una tabla inexistente en tiempo de creación de la función (tiempo de parsing). La ejecución de esa función (select * from fEjFunction2 (); ) crea la tabla y agrega la fila normalmente.
Incluso creando un función que haga referencia a una tabla no va a existir tiempo de ejecución no tira problemas de parsing.Ej

Code:

set search_path to libertya;

CREATE OR REPLACE FUNCTION fEjFunction3 () RETURNS CHARACTER VARYING AS $$
BEGIN

— insert en la tabla creada justo antes
insert into ejTablaINEXISTENTE(id,name) values (10,’nombre’);

RETURN ”;
END;
$$ LANGUAGE ‘plpgsql’;

La creación de la función no tira errror; obviamente si lo hará la ejecución (ERROR: relation “ejtablainexistente” does not exist)
En cualquier caso yo proponía esta forma solo para ejecutar el preinstall.sql o como un método general para aquellos casos en el que sería bueno ejecutar múltiples sentencias; todo lo demás lo veo correcto que sea haga via los xmls en el caso de los componentes (no creo que tenga tanta importancia la performance en este caso; nadie instala componentes todo el tiempo).

[Federico Cristina]

Javier,

Buenas! El problema que te comentaba previamente sobre las referencias, se nos presentaba bajo un escenario en el que intentábamos impactar modificaciones estructurales y referencias posteriores sin el uso de un stored procedure dinámico como comentás.

Fijate el comentario de la clase PluginXMLBuilder método executeUpdate():

Code:

Procesa el contenido completo del SQL que recibe (simple o multiples sentencias)

Para el preinstall.sql, este método impacta todo el contenido del archivo (por ahora con la limitación de los comentarios, pero eso debería arreglarse mejorando el método readFromJar()). Con lo cual el PreparedStatement sí permite múltiples sentencias. El problema es en el caso en que dichas sentencias hagan referencia a tablas todavía no existentes (escenario en el que te comentaba previamente).

Es por esto que, en lugar de impactar todo en un único script, se realiza un impacto independiente para el preinstall. Tu aproximación es interesante justamente en casos donde sea necesario sortear dicha limitación.

Saludos y gracias por comentar!
Federico

[Javier Ader]

Ah, yo creía que directamente el driver JDBC para postgres no soportaba múltiples sentencias (se ve que existe esta limitación pero solo para ciertas tipos de ejecuciones; en particular para obtener datos basados en “cursores” http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor , pero no es el caso… creo).
Ahora me puse a mirar un poco el código de la clase CPreparedStatement que usa PluginXMLUpdater para llevar a cabo el preinstall, y veo, si no me equivoco, que en al fin y al cabo cuando uno ejecuta el executeUpdate sobre esta clase, esto termina llevando a cabo un executeUpdate sobre un objeto de la subclase especifica del driver de Postgres para la clase JDBC PreparedStatement. El tema es que los PreparedStatement son objetos en el “servidor” y son preprocesados (la idea si no entendí mal es que se suponen que van a ser llamados mas de una vez, y este preprocesamiento evita el “replaneamiento” y “recompilación” de la sentencia Sql a nivel de postgres y otras mejoras a nivel de performance; en este caso NO lo veo para nada necesario, ya que el preinstall no es una “sentencia” que se va ejecutar mas de una vez). Me pregunto si todo estos problemas no surgen porque se esta usando un PreparedStatement en vez de usar simplemente un Statement (este no es preprocesado). Tal vez usando esta clase en estos casos particulares se solucione no solo el tema de los comentarios si no también el tema de las referencias. Voy a intentar testearlo (si ya lo probaron y no anduvo avísenme así me ahorro el trabajo).[la clase CStatement debe hacer esto]

PD : creo que Libertya usa PreparedStatement siempre… me pregunto si este uso es correcto, ya que la idea del usar estos es repetir la misma sentencia solo variando los parámetros (supongo que a nivel de conexión de red JDBC envia primero la sentencias, y después por cada ejecución solo envia los parámetros; de ahi una mejora en performance, PERO SOLO cuando la sentencia ser repite muchas veces). Digamos, al cargar la ventana Productos, el select que los trae, no tiene sentido que sea un PreparedStatement (no tiene ninguna mejora sobre el Statement simple y ademas debe generar un overhead innecesario).

PD 2: bueno, PreparedStatement nivel de API tiene la capacidad de setear parámetros via setXXXs, lo cual lo hace muy util a nivel de programación y esto es usado asiduamente en Libertya; Statement no tiene esta funcionalidad (no entiendo bien por qué pero bueno…). E.d Statement en vez de PreparedStatement puede ser lo más correcto desde el punto de vista de base de datos, pero a nivel de programación complica mucho las cosas.

[Javier Ader]

Bueno, intente probar usando CStatement en lugar de CPreparedStatement en PluginXMLUpdater.executeUpdate y con un par de modificaciones previas anduvo la ejecución en blogue de cualquier sentencia sql (incluso con referencias “internas”).
Lo que hice fue lo siguiente:
PluginXMLUpdater.java:

Code:

public static void executeUpdate(String sql, String trxName) throws Exception
{
if (sql != null && sql.length() > 0)
{
//anterior
//CPreparedStatement cs = new CPreparedStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, sql, trxName, true);
//cs.executeUpdate();
//cs.close();

//MIO: agregado para probar CStatement en vez de CPreparedStatement
CStatement cs = new CStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE,trxName);
cs.executeUpdate(sql,true);
cs.close();

}

Agregue el método executeUpate(string,boolean) en CStatement para que no llame a DB.getDatabaseConvert (no se bien que significa este convert, pero cuando se usa CPreparedStatement se saltea pasando true a noConvert)
CStatement.java:

Code:

//Copy and past de executeUpdtae(sql)
public int executeUpdate( String sql0, boolean noConvert ) throws SQLException {

//MIO: Convert?
p_vo.setSql(noConvert?sql0: DB.getDatabase().convertStatement( sql0 ));
if( p_stmt != null ) { // local
return p_stmt.executeUpdate( p_vo.getSql());
}

//TODO LO demas identico a CStatement.excuteUpdate(string)

y finalmente hice el fix para los finales de linea en la instalación de componentes (poniendo n como separador de linea
en readFromJar)
VPluginInstallerUtils.java:

Code:

public static void doPreInstall(Properties ctx, String trxName, String jarURL, String fileURL) throws Exception
{
/* Toma el archivo SQL correspondiente e impacta en la base de datos */
//String sql = readFromJar(jarURL, fileURL, “”);
//MIO:para que agregue este LF entre sentencias SQL
String sql = readFromJar(jarURL, fileURL, “n”);

if (sql != null && sql.length() > 0)
PluginXMLUpdater.executeUpdate(sql, trxName);
}

Despues instale este componente: http://www.eltita.com.ar/libertya/testCStatement/TestCStatement.zip
Que tiene un preinstall.sql basado en el componente PSP1.0 que postee en otro thread pero que ademas crea una tabla , inserta una fila en la misma, y despues crea un vista basada en esa tabla (e.d referencias internas dentro del mismo conjunto de sentencias a una tabla creada por el mismo archivo).
Pongo el código del preinstall.sql:

Code:

———————————————————————-
———- Nuevas Tablas y/o Vistas
———————————————————————-

CREATE VIEW v_product_not_in_plv AS
SELECT plv.ad_client_id, plv.ad_org_id, plv.m_pricelist_version_id, p.m_product_id, NULL::timestamp without time zone AS created, 0 AS createdby, NULL::timestamp without time zone AS updated, 0 AS updatedby FROM (SELECT m_pricelist_version.m_pricelist_version_id, m_pricelist_version.ad_client_id, m_pricelist_version.ad_org_id FROM m_pricelist_version) plv, (SELECT m_product.m_product_id, m_product.ad_client_id FROM m_product WHERE ((m_product.producttype = ‘I’::bpchar) OR (m_product.producttype = ‘R’::bpchar))) p WHERE ((plv.ad_client_id = p.ad_client_id) AND (NOT (EXISTS (SELECT 1 FROM m_productprice pp WHERE ((pp.m_product_id = p.m_product_id) AND (pp.m_pricelist_version_id = plv.m_pricelist_version_id))))));

–agregado para probar el chequeo de referencias dentro del preinstall

create table ejTabla2(
id integer not null,
name character varying(40)

);
— insert en la tabla creada justo antes
insert into ejTabla2(id,name) values (10,’nombre’);
create view ejView2 as select * from ejTabla2;

———————————————————————-
———- Nuevas columnas en tablas y/o vistas
———————————————————————-

———————————————————————-
———- Modificación de tablas y/o vistas
———————————————————————-

(este preinstall.sql tiene algunos finales de linea con LF y otros con CRLF; no importa al parecer ya que después del readFromJar con n como separador todas las lineas pasan a terminar con este delimitador)
Lo instalo lo mas bien, y me creo la tabla y las dos vistas (la tabla ejTabla2 tenia ademas la fila insertada).
Me dije: Éxito!
Pero… ahora dije: bueno, vamos a recrear el tema de las referencias a tablas inexistentes usando el anterior CPreparedStatement, así que volví al anterior PluginXMLUpdater.executeUpdate (e.d, volvi a usar CPreparedStatement en vez de CStatement). Cree otra base de datos y repetí la instalación suponiendo que me iba a tirar un error de “tabla inexistente” al ejecutar el preinstall.sql…. pero no; lo ejecuto lo mas bien!!!! E.d, a CPreparedStatement tampoco le importan los comentarios, los finales de linea, las referencias internas!!!! Anda lo más bien! Esto “El problema es en el caso en que dichas sentencias hagan referencia a tablas todavía no existentes (escenario en el que te comentaba previamente).” no me ocurrió (todo el problema se solucionaba para el caso de los componentes llamado a readFromJar con n y CPreparedStatement no tenia nada que ver).
Por qué te puede haber ocurrido a vos tal vez provenga de la nota en este link http://jdbc.postgresql.org/documentation/83/server-prepare.html ; ahí dice que la versiones anteriores del driver JDBC para Postgress lleva a cabo la implementación de PreparedStatemet usaban las sentencias PREPARE a nivel SQL; pero estas sentencias NO soportan múltiples sentencias separadas por “;” http://www.postgresql.org/docs/8.1/static/sql-prepare.html ya que solo la primer sentencia va a quedar dentro del PREPARE (el primer “;” que encuentra finaliza la sentencia PREPARE en si misma) y las demas van a ser sentencias normales despues del prepare; bajo estas condiciones es claro que van a ocurrir problemas de referencias si el prepare define algo que es referenciado por las demás sentencias (el PREPARE no ejecuta la sentencia, solo la planifica…). Supongo que para esto haya ocurrido se tiene que estar ejecutando contra un versión del server menor o igual a 7.3 o explícitamente usando el protocolo ‘V2’ en vez de ‘V3’. Vendrá por este lado tu comentario acerca de las referencias a tablas inexistentes?

PD : Bueno, en cualquier caso saco 2 conclusiones
1) mi solución usando funciones temporales no parece para nada necesaria jajaja…
2) usar CStatement en vez de CPreparedStatement no soluciona nada ya que no hay ningún problema! (ok, salvo en situaciones en que se tenga que usar versiones anteriores del server o el protocolo V2; ahí CPreparedStatement debería fallar). De cualquier manera en este caso en particular creo que de todas formas se debería usar CStatement (con la modificación que saltea la conversión del sql por DB), pero esta vez por una cuestión de performance, no de correctitud: el instalador de componentes crea un PreparedStatement sin parámetros por cada uno de los inserts,deletes y modificaciones cuando procesa el Changelog via el install.xml y el postinstall.sml y los descarta después de usarlos UNA sola vez! (esto debe estar creando una cantidad enorme de objetos del lado del servidor de manera innecesaria y ademas generando el doble de “round-trips” a la base de datos ya que ejecución de un PreparedStatment requiere dos pasos). Me dio la sensación de que al ejecutar la instalación usando CStatement tardo menos (pero bueno, muchos tests no hice).
3) usar CStatement en general en lugar de CPreparedStatement debe traer mejoras de performance en otras lugares, pero el tema es que se pierde los setsXXXX y se tiene que modificar muchísimas referencias a esta clase. La otra opción que se me ocurre es que se podría modificar CPreparedStatement para bajo ciertas circunstancias sea un wrapper de un Statement e implementar a ‘mano’ cada una de los setXXX manipulando las string sql y usando las APIs de “escapes” dadas por el driver de postgress para no reinventar la rueda. CPreparedStatement pasaría a ser un wrapper de un PreparedStatement o de un Statement; en el primer lugar delegaría los setXXX al PreparedStatement interno tal como lo hace ahora; en el segundo debería manipular la string sql dada en su constructor de manera manual. Después investigo un poco mas acerca de esto (se me ocurre sniffear un poco la red para ver cuantos accesos genera un PreparedStatement vs un Statement… tal vez no haya mejoras de peformance después de todo…)

[Federico Cristina]

Javier,

Muy interesante tu aporte!

Sobre el convert que comentás, esto se debía a que el framework original soportaba el uso de sentencias escritas en Oracle y Postgres, y dicho convert realizaba la conversión correspondiente según el DBMS utilizado. En fin, el tema es que sin ese bypass (noConvert) en la conversión ocasionalmente se rompía la instalación. Como actualmente estamos seguros que las sentencias escritas están en Postgre, obviamos toda conversión.

Cuando me haga un tiempo veré más en detalle este tema a fin de pulirlo para el próximo release.

Saludos!
Federico

[Autor]

Entradas