A Tool to Ease Your Transition From Oracle PL/SQL to Couchbase JavaScript UDF

¿Qué es PL/SQL?

PL/SQL es un lenguaje procedimental diseñado específicamente para incluir sentencias SQL en su sintaxis. Incluye elementos de lenguaje procedimental como condiciones y bucles, y puede manejar excepciones (errores en tiempo de ejecución).

PL/SQL es nativo de las bases de datos Oracle, y bases de datos como IBM DB2, PostgreSQL y MySQL admiten construcciones PL/SQL a través de funciones de compatibilidad.

¿Qué es una UDF de JavaScript?

JavaScript UDF es la alternativa de Couchbase a PL/SQL.

JavaScript UDF traslada la flexibilidad de las secuencias de comandos de uso general de JavaScript a las bases de datos, lo que permite realizar operaciones dinámicas y potentes en los sistemas de bases de datos modernos y mejora la flexibilidad en la consulta, el procesamiento y la transformación de datos.

La mayoría de las bases de datos modernas como Couchbase, MongoDB, Snowflake y Google BigQuery soportan JavaScript UDF.

El problema

Un problema común visto por los usuarios que migran de Oracle a Couchbase es portar sus scripts PL/SQL. En lugar de soportar PL/SQL, Couchbase permite a los usuarios construir funciones definidas por el usuario en JavaScript (soportado desde 2021).

Las UDF de JavaScript permiten una manipulación fácil e intuitiva de los datos de variantes y JSON. Los objetos variantes pasados a una UDF se transforman en tipos y valores nativos de JavaScript.

La consecuencia involuntaria de esto es que la mayoría de los RDBMS que han existido en los últimos diez años han animado encarecidamente a los desarrolladores a acceder a la base de datos utilizando sus extensiones procedimentales de SQL (PL/pgSQL, PL/SQL), que soportan construcciones procedimentales, integración con SQL, gestión de errores, funciones y procedimientos, disparadores y cursores, o como mínimo, funciones y procedimientos (como Sakila). Para cualquier intento de alejarse de ellos, habría que reescribir todos sus scripts.

Reescribir código es a menudo una tarea tediosa, especialmente cuando se trata de scripts PL/SQL que han sido escritos en la década de 2000 y mantenidos desde entonces. Estos scripts pueden ser complejos, a menudo extendiéndose a miles de líneas, lo que puede ser abrumador para el usuario medio de la empresa.

Solución

Lo ideal sería desarrollar un evaluador PL/SQL completamente nuevo, pero eso requeriría una cantidad excesiva de horas de ingeniería y, para el mismo caso de uso, ya disponemos de un JSEvaluator moderno, estable y rápido.

Esto convierte el problema en un caso de uso perfecto para aprovechar los avances actuales en IA y LLM. Y eso es lo que hemos hecho aquí. Hemos utilizado modelos de IA generativa para automatizar la conversión de PL/SQL a JSUDF.

A partir de junio de 2024, los modelos tienen una ventana de contexto limitada, which significa PL/SQL más largo se golpean con el error:

This model's maximum context length is 8192 tokens. However, your messages resulted in <More-than-8192> tokens. Please reduce the length of the messages.

1	This model's maximum context length is 8192 tokens. However, your messages resulted in <More-than-8192> tokens. Please reduce the length of the messages.

Tenga en cuenta que esto es para GPT4.

Entonces, ¿esperamos a que la IA sea más potente y permita más fichas (como la Ley de Moore, pero para el contexto-longitud-vs-precisión de la IA)?

No, ahí es donde ANTLR, a herramienta generadora de analizadores sintácticos. ANTLR es bien conocido por ser utilizado para el desarrollo de compiladores e intérpretes. De esta manera podemos dividir el script grande en unidades más pequeñas que se pueden traducir de forma independiente.

Entonces, ¿estamos construyendo ¿transpilador? Bueno, sí y no.

Etapas de un transpilador:

1. Análisis léxico (tokenización)
2. Análisis sintáctico (Parsing)
3. Análisis semántico
4. Generación de representaciones intermedias (RI)
5. Optimización (opcional)
6. Generación de código de destino

Cómo funciona el traductor de IA

Los pasos 1,2 anteriores se realizan utilizando ANTLR. Utilizamos la interfaz Listener de ANTLR para agarrar procedimiento/función/bloque anónimo individualya que son bloques de código independientes. En el caso de que el procedimiento/función/bloque anónimo exceda por sí mismo la ventana de contexto, traducimos a nivel de sentencia (donde el LLM asume la existencia de uso de variables/llamadas a funciones que no están definidas aquí sino en algún lugar anterior).

Posteriormente, los pasos 3, 4, 5 y 6 se dejan en manos del LLM (por ejemplo, GPT), es decir, traducir cada bloque PL/SQL en una función JavaScript lo mejor posible que también preserve la semántica operativa del bloque y sea sintácticamente precisa.

Los resultados son sorprendentemente positivos; la traducción es 80-85% exacta.

Otra ventaja de la solución es que reducimos las alucinaciones al centrarnos en una tarea cada vez, lo que se traduce en traducciones más precisas.

Para visualizar:

Cómo utilizar la herramienta

- Descargue el ejecutable desde Couchbase Labs GitHub y acceder al LÉAME.

El ejecutable espera los siguientes argumentos de línea de comandos:

-u: capella signin email
-p: contraseña de acceso a capella
-cpaddr: capella-url para chat-completions api
-orgid: id de la organización en la ruta de la api de chat-completions
-cbhost: nodo-ip: nodo cbcluster
-cbuser: cluster-user-name: usuario cbcluster, añadido a través de database-acess
-cbpassword: cluster-password: contraseña del cbcluster, añadida a través del acceso a la base de datos.
-cbport: query-service tls port: normalmente 18093
filepath , es decir, la ruta del script PL/SQL que debe traducirse
salida-> En el directorio de salida, se creará un archivo con el mismo nombre que el archivo plsql con el código traducido de la biblioteca JavaScript.

Un ejemplo:

cat ejemplo1.sql

DECLARE
   x NUMBER := 0;
   counter NUMBER := 0;
BEGIN
   FOR i IN 1..4 LOOP
      x := x + 1000;
      counter := counter + 1;
      INSERT INTO temp VALUES (x, counter, 'in OUTER loop');
       --start an inner block 
      DECLARE
         x NUMBER := 0;  -- this is a local version of x
      BEGIN
         FOR i IN 1..4 LOOP
            x := x + 1;  -- this increments the local x
            counter := counter + 1;
            INSERT INTO temp VALUES (x, counter, 'inner loop');
         END LOOP;
      END;
   END LOOP;
   COMMIT;
END;

DECLARE

x NUMBER := 0;

counter NUMBER := 0;

BEGIN

FOR i IN 1..4 LOOP

x := x + 1000;

counter := counter + 1;

INSERT INTO temp VALUES (x, counter, 'in OUTER loop');

--start an inner block

DECLARE

x NUMBER := 0; -- this is a local version of x

BEGIN

FOR i IN 1..4 LOOP

x := x + 1; -- this increments the local x

counter := counter + 1;

INSERT INTO temp VALUES (x, counter, 'inner loop');

END LOOP;

END;

END LOOP;

COMMIT;

END;

Para explicar brevemente el guión anterior:

- Un bucle externo se ejecuta durante 4 iteraciones, incrementando x en 1000, contador en 1
- El bucle interno se ejecuta durante 4 iteraciones, incrementando x en 1, contador en 1

Ejecutar el traductor en el PL/SQL de prueba:

./plsql-to-jsudf -u «capella-signin-mailid» -p «capella-signin-password» -cpaddr https://api.cloud.couchbase.com -orgid «capella-organisation-id» -cbhost «hostname of data node» -cbuser «cbcluster username» -cbpassword «cbcluster password»  -cbport 18093 ./translator/test/plsql/example1.sql

1	./plsql-to-jsudf -u «capella-signin-mailid» -p «capella-signin-password» -cpaddr https://api.cloud.couchbase.com -orgid «capella-organisation-id» -cbhost «hostname of data node» -cbuser «cbcluster username» -cbpassword «cbcluster password» -cbport 18093 ./translator/test/plsql/example1.sql

Salida JSUDF:

cat salida/ejemplo1.js

function nestedloop(){
    var x = 0;
    var counter = 0;
    var querybegin = BEGIN WORK;
    querybegin.close();
    for (var i = 1; i <= 4; i++){
        x = x + 1000;
        counter = counter + 1;
        var params = [x, counter];
        var query = N1QL('INSERT INTO test.testscope.temp VALUES (uuid(),{"val1":$1,"val2":$2,"val3":"in OUTER loop"})',params);
        query.close();
        var x_inner = 0;
        for (var j = 1; j <= 4; j++){
            x_inner = x_inner + 1;
            counter = counter + 1;
            var params_inner = [x_inner, counter];
            var query_inner = N1QL('INSERT INTO test.testscope.temp VALUES (uuid(),{"val1":$1,"val2":$2,"val3":"inner loop"})',params_inner);
            query_inner.close();
        }
    }
    var querycommit = COMMIT WORK;
    querycommit.close();
}

function nestedloop(){

var x = 0;

var counter = 0;

var querybegin = BEGIN WORK;

querybegin.close();

for (var i = 1; i <= 4; i++){

x = x + 1000;

counter = counter + 1;

var params = [x, counter];

var query = N1QL('INSERT INTO test.testscope.temp VALUES (uuid(),{"val1":$1,"val2":$2,"val3":"in OUTER loop"})',params);

query.close();

var x_inner = 0;

for (var j = 1; j <= 4; j++){

x_inner = x_inner + 1;

counter = counter + 1;

var params_inner = [x_inner, counter];

var query_inner = N1QL('INSERT INTO test.testscope.temp VALUES (uuid(),{"val1":$1,"val2":$2,"val3":"inner loop"})',params_inner);

query_inner.close();

}

var querycommit = COMMIT WORK;

querycommit.close();

}

El script traducido tiene una función bucle anidado (nombre generado por LLM) que hace exactamente lo que especifica el bloque PL/SQL anónimo original.

Nota al margen: para funciones/procedimientos con nombre, las funciones JS traducidas tendrán el mismo nombre. Para bloques anónimos LLM utiliza un nombre que se le ocurre.

Problemas conocidos

PL/SQL y JS son 2 lenguajes diferentes, y la forma en que están soportados en Oracle y Couchbase no permite un mapeo directo entre los 2.

A continuación se indican algunas de las limitaciones que hemos descubierto y las soluciones que hemos aplicado:

1. console.log no es compatible

DBMS_OUTPUT.PUT y otros 2 similares, DBMS_OUTPUT.PUT_LINE y DBMS_OUTPUT.NEW_LINE se traducen a console.log()pero console.log es una API de navegador y no está soportada por la implementación de evaluación JavaScript de Couchbase. Esta ha sido una pregunta frecuente, considerando que la función de eventos de Couchbase sí soporta imprimir() pero no en las UDF de JavaScript.

Solución:

Se espera que los usuarios creen un registro cubo.

Los registros se insertan como parte de un documento INSERT en por defecto.por defecto colección. El documento sería algo así

{
   "udf": «func-name»,
   "log": «argument to console.log», // the actual log line
   "time": «current ISO time string»
}

{

"udf": «func-name»,

"log": «argument to console.log», // the actual log line

"time": «current ISO time string»

}

El usuario puede consultar sus registros seleccionando en registro:

SELECT * FROM logging WHERE udf= "«func-name»";
SELECT * FROM logging WHERE time BETWEEN "«date1»" AND "«date2»";

1 2	SELECT * FROM logging WHERE udf= "«func-name»"; SELECT * FROM logging WHERE time BETWEEN "«date1»" AND "«date2»";

Un ejemplo:

El PL/SQL original

BEGIN
   DBMS.OUTPUT.PUT("Hello world!");
END;

BEGIN

DBMS.OUTPUT.PUT("Hello world!");

END;

Traducido a JavaScript UDF

function helloWorld() {
   // workaround for console.log("Hello world!");
   var currentDate = new Date();
   var utcISOString = currentDate.toISOString();
   var params = [utcISOString,'anonymousblock1',"Hello world!"];
   var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);
   logquery.close();
}

function helloWorld() {

// workaround for console.log("Hello world!");

var currentDate = new Date();

var utcISOString = currentDate.toISOString();

var params = [utcISOString,'anonymousblock1',"Hello world!"];

var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);

logquery.close();

}

Esto ya está implementado en la herramienta.

Para ver el registro:

EXECUTE FUNCTION helloWorld();
"results": [
   null
]

CREATE PRIMARY INDEX ON logging;
"results": [
]

SELECT * FROM logging;
"results": [
   {"logging":{"log":"Hello world!","time":"2024-06-26T09:20:56.000Z","udf":"anonymousblock1"}}
]

EXECUTE FUNCTION helloWorld();

"results": [

null

]

CREATE PRIMARY INDEX ON logging;

"results": [

]

SELECT * FROM logging;

"results": [

{"logging":{"log":"Hello world!","time":"2024-06-26T09:20:56.000Z","udf":"anonymousblock1"}}

]

2. Llamadas a funciones entre paquetes

Los procedimientos/funciones enumerados en la especificación del paquete son globales y pueden utilizarse desde otros paquetes mediante "nombre_paquete". "procedimiento/funcion_publica". Pero no ocurre lo mismo con una librería JavaScript en Couchbase, ya que las construcciones de importación-exportación no están soportadas por la implementación de evaluación JavaScript de Couchbase.

Solución:

En caso de llamada a una función interbibliotecaria "lib_name". "function"()se espera que el usuario disponga de la biblioteca de referencia "lib_name" ya creado; puede comprobarlo mediante GET /evaluator/v1/bibliotecas

La función de referencia "función" también se espera que sea creada como una UDF global; esto puede ser verificado vía GET /admin/functions_cache o select system:functions keyspace. De esta forma podemos acceder a la función a través de SQL++/N1QL.

Un ejemplo:

utilidades_matemáticas Paquete

CREATE OR REPLACE PACKAGE math_utils AS
   -- Public function to add two numbers
   FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER;
END math_utils;
/

CREATE OR REPLACE PACKAGE BODY math_utils AS
   FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS
      BEGIN
         RETURN p_num1 + p_num2;
      END add_numbers;
END math_utils;
/

CREATE OR REPLACE PACKAGE math_utils AS

-- Public function to add two numbers

FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER;

END math_utils;

CREATE OR REPLACE PACKAGE BODY math_utils AS

FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_num1 + p_num2;

END add_numbers;

END math_utils;

mostrar_suma Paquete

CREATE OR REPLACE PACKAGE show_sum AS
   -- Public procedure to display the sum of two numbers
   PROCEDURE display_sum(p_num1 NUMBER, p_num2 NUMBER);
END show_sum;
/

CREATE OR REPLACE PACKAGE BODY show_sum AS
   PROCEDURE display_sum(p_num1 NUMBER, p_num2 NUMBER) IS
      v_sum NUMBER;
   BEGIN
      -- Calling the add_numbers function from math_utils package
      v_sum := math_utils.add_numbers(p_num1, p_num2);

      -- Displaying the sum using DBMS_OUTPUT.PUT_LINE
      DBMS_OUTPUT.PUT_LINE('The sum of ' || p_num1 || ' and ' || p_num2 || ' is ' || v_sum);
   END display_sum;
END show_sum;
/

CREATE OR REPLACE PACKAGE show_sum AS

-- Public procedure to display the sum of two numbers

PROCEDURE display_sum(p_num1 NUMBER, p_num2 NUMBER);

END show_sum;

CREATE OR REPLACE PACKAGE BODY show_sum AS

PROCEDURE display_sum(p_num1 NUMBER, p_num2 NUMBER) IS

v_sum NUMBER;

BEGIN

-- Calling the add_numbers function from math_utils package

v_sum := math_utils.add_numbers(p_num1, p_num2);

-- Displaying the sum using DBMS_OUTPUT.PUT_LINE

DBMS_OUTPUT.PUT_LINE('The sum of ' || p_num1 || ' and ' || p_num2 || ' is ' || v_sum);

END display_sum;

END show_sum;

Código traducido:

function show_sum(a, b) {
 var sum_result;

// Workaround for cross library function call math_utils.add_numbers(a, b)
var crossfunc = N1QL("EXECUTE FUNCTION add_numbers($1,$2)",[a, b])
var crossfuncres = []
for(const doc of crossfunc) {
   crossfuncres.push(doc);
}

// actual replacement for math_utils.add_numbers(a, b)
sum_result = crossfuncres[0];

// workaround for console.log('The sum of ' + a + ' and ' + b + ' is: ' + sum_result);
var currentDate = new Date();
var utcISOString = currentDate.toISOString();
var params = [utcISOString,'SHOW_SUM','The sum of ' + a + ' and ' + b + ' is: ' + sum_result];
var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);
logquery.close();
}

function show_sum(a, b) {

var sum_result;

// Workaround for cross library function call math_utils.add_numbers(a, b)

var crossfunc = N1QL("EXECUTE FUNCTION add_numbers($1,$2)",[a, b])

var crossfuncres = []

for(const doc of crossfunc) {

crossfuncres.push(doc);

}

// actual replacement for math_utils.add_numbers(a, b)

sum_result = crossfuncres[0];

// workaround for console.log('The sum of ' + a + ' and ' + b + ' is: ' + sum_result);

var currentDate = new Date();

var utcISOString = currentDate.toISOString();

var params = [utcISOString,'SHOW_SUM','The sum of ' + a + ' and ' + b + ' is: ' + sum_result];

var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);

logquery.close();

}

El programa lo gestiona automáticamente, con la advertencia de que debe ser verificado por una persona.

3. Variables globales

PL/SQL soporta variables globales a nivel de paquete y a nivel de sesión, pero las variables globales no están soportadas en JSUDF deliberadamente por diseño ya que esto causa preocupación por fugas de memoria.

La solución sugerida requiere un ajuste manual de la traducción generada. Por ejemplo:

  CREATE OR REPLACE PACKAGE global_vars_pkg AS
     -- Global variable declarations
     g_counter NUMBER := 0;
     g_message VARCHAR2(100) := 'Initial Message';

     -- Public procedure declarations
     PROCEDURE increment_counter;
     PROCEDURE set_message(p_message VARCHAR2);
     PROCEDURE show_globals;
   END global_vars_pkg;
   /

   CREATE OR REPLACE PACKAGE BODY global_vars_pkg AS

     -- Procedure to increment the counter
     PROCEDURE increment_counter IS
     BEGIN
       g_counter := g_counter + 1;
     END increment_counter;

     -- Procedure to set the global message
     PROCEDURE set_message(p_message VARCHAR2) IS
     BEGIN
       g_message := p_message;
     END set_message;

     -- Procedure to display the current values of global variables
     PROCEDURE show_globals IS
     BEGIN
       DBMS_OUTPUT.PUT_LINE('g_counter = ' || g_counter);
       DBMS_OUTPUT.PUT_LINE('g_message = ' || g_message);
     END show_globals;

   END global_vars_pkg;
   /

CREATE OR REPLACE PACKAGE global_vars_pkg AS

-- Global variable declarations

g_counter NUMBER := 0;

g_message VARCHAR2(100) := 'Initial Message';

-- Public procedure declarations

PROCEDURE increment_counter;

PROCEDURE set_message(p_message VARCHAR2);

PROCEDURE show_globals;

END global_vars_pkg;

CREATE OR REPLACE PACKAGE BODY global_vars_pkg AS

-- Procedure to increment the counter

PROCEDURE increment_counter IS

BEGIN

g_counter := g_counter + 1;

END increment_counter;

-- Procedure to set the global message

PROCEDURE set_message(p_message VARCHAR2) IS

BEGIN

g_message := p_message;

END set_message;

-- Procedure to display the current values of global variables

PROCEDURE show_globals IS

BEGIN

DBMS_OUTPUT.PUT_LINE('g_counter = ' || g_counter);

DBMS_OUTPUT.PUT_LINE('g_message = ' || g_message);

END show_globals;

END global_vars_pkg;

Cualquier función que modifique una variable global debe aceptarla como argumento y devolverla a quien la llama.

contador_de_incrementos:

function increment_counter(counter){
   counter = counter + 1;
   return counter
}

function increment_counter(counter){

counter = counter + 1;

return counter

}

Cualquier función que sólo lea un global puede aceptarlo como argumento.

mostrar_globales:

function show_globals(counter, message){
   // workaround for console.log(counter);
   var currentDate = new Date();
   var utcISOString = currentDate.toISOString();
   var params = [utcISOString,'SHOW_GLOBALS',couter];
   var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);
   logquery.close();

   // workaround for console.log(message);
   var currentDate = new Date();
   var utcISOString = currentDate.toISOString();
   var params = [utcISOString,'SHOW_GLOBALS',message];
   var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);
   logquery.close();
}

function show_globals(counter, message){

// workaround for console.log(counter);

var currentDate = new Date();

var utcISOString = currentDate.toISOString();

var params = [utcISOString,'SHOW_GLOBALS',couter];

var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);

logquery.close();

// workaround for console.log(message);

var currentDate = new Date();

var utcISOString = currentDate.toISOString();

var params = [utcISOString,'SHOW_GLOBALS',message];

var logquery = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', params);

logquery.close();

}

Paquete a la biblioteca

Esta sección muestra una conversión de paquete a biblioteca de extremo a extremo utilizando la herramienta.

Paquete PL/SQL de ejemplo:

CREATE OR REPLACE PACKAGE emp_pkg IS
  PROCEDURE insert_employee(
    p_emp_id     IN employees.emp_id%TYPE,
    p_first_name IN employees.first_name%TYPE,
    p_last_name  IN employees.last_name%TYPE,
    p_salary     IN employees.salary%TYPE
  );

  PROCEDURE update_employee(
    p_emp_id     IN employees.emp_id%TYPE,
    p_first_name IN employees.first_name%TYPE,
    p_last_name  IN employees.last_name%TYPE,
    p_salary     IN employees.salary%TYPE
  );

  PROCEDURE delete_employee(
    p_emp_id IN employees.emp_id%TYPE
  );

  PROCEDURE get_employee(
    p_emp_id     IN employees.emp_id%TYPE,
    p_first_name OUT employees.first_name%TYPE,
    p_last_name  OUT employees.last_name%TYPE,
    p_salary     OUT employees.salary%TYPE
  );

END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  PROCEDURE insert_employee(
    p_emp_id     IN employees.emp_id%TYPE,
    p_first_name IN employees.first_name%TYPE,
    p_last_name  IN employees.last_name%TYPE,
    p_salary     IN employees.salary%TYPE
  ) IS

  BEGIN
    INSERT INTO employees (emp_id, first_name, last_name, salary)
    VALUES (p_emp_id, p_first_name, p_last_name, p_salary);
  END insert_employee;

  PROCEDURE update_employee(
    p_emp_id     IN employees.emp_id%TYPE,
    p_first_name IN employees.first_name%TYPE,
    p_last_name  IN employees.last_name%TYPE,
    p_salary     IN employees.salary%TYPE
  ) IS

  BEGIN
    UPDATE employees
    SET first_name = p_first_name,
        last_name  = p_last_name,
        salary     = p_salary
    WHERE emp_id = p_emp_id;
  END update_employee;

  PROCEDURE delete_employee(
    p_emp_id IN employees.emp_id%TYPE
  ) IS

  BEGIN
    DELETE FROM employees
    WHERE emp_id = p_emp_id;
  END delete_employee;

  PROCEDURE get_employee(
    p_emp_id     IN employees.emp_id%TYPE,
    p_first_name OUT employees.first_name%TYPE,
    p_last_name  OUT employees.last_name%TYPE,
    p_salary     OUT employees.salary%TYPE
  ) IS

  BEGIN
    SELECT first_name, last_name, salary
    INTO p_first_name, p_last_name, p_salary
    FROM employees
    WHERE emp_id = p_emp_id;
  END get_employee;
END emp_pkg;
/

CREATE OR REPLACE PACKAGE emp_pkg IS

PROCEDURE insert_employee(

p_emp_id IN employees.emp_id%TYPE,

p_first_name IN employees.first_name%TYPE,

p_last_name IN employees.last_name%TYPE,

p_salary IN employees.salary%TYPE

);

PROCEDURE update_employee(

p_emp_id IN employees.emp_id%TYPE,

p_first_name IN employees.first_name%TYPE,

p_last_name IN employees.last_name%TYPE,

p_salary IN employees.salary%TYPE

);

PROCEDURE delete_employee(

p_emp_id IN employees.emp_id%TYPE

);

PROCEDURE get_employee(

p_emp_id IN employees.emp_id%TYPE,

p_first_name OUT employees.first_name%TYPE,

p_last_name OUT employees.last_name%TYPE,

p_salary OUT employees.salary%TYPE

);

END emp_pkg;

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

PROCEDURE insert_employee(

p_emp_id IN employees.emp_id%TYPE,

p_first_name IN employees.first_name%TYPE,

p_last_name IN employees.last_name%TYPE,

p_salary IN employees.salary%TYPE

) IS

BEGIN

INSERT INTO employees (emp_id, first_name, last_name, salary)

VALUES (p_emp_id, p_first_name, p_last_name, p_salary);

END insert_employee;

PROCEDURE update_employee(

p_emp_id IN employees.emp_id%TYPE,

p_first_name IN employees.first_name%TYPE,

p_last_name IN employees.last_name%TYPE,

p_salary IN employees.salary%TYPE

) IS

BEGIN

UPDATE employees

SET first_name = p_first_name,

last_name = p_last_name,

salary = p_salary

WHERE emp_id = p_emp_id;

END update_employee;

PROCEDURE delete_employee(

p_emp_id IN employees.emp_id%TYPE

) IS

BEGIN

DELETE FROM employees

WHERE emp_id = p_emp_id;

END delete_employee;

PROCEDURE get_employee(

p_emp_id IN employees.emp_id%TYPE,

p_first_name OUT employees.first_name%TYPE,

p_last_name OUT employees.last_name%TYPE,

p_salary OUT employees.salary%TYPE

) IS

BEGIN

SELECT first_name, last_name, salary

INTO p_first_name, p_last_name, p_salary

FROM employees

WHERE emp_id = p_emp_id;

END get_employee;

END emp_pkg;

Traducción:

./plsql-to-jsudf -u «capella-signin-mailid» -p «capella-signin-password» -cpaddr https://api.cloud.couchbase.com -orgid «capella-organisation-id» -cbhost «hostname of data node» -cbuser «cbcluster username» -cbpassword «cbcluster password»  -cbport 18093 translator/test/plsql/blog_test.sql

1	./plsql-to-jsudf -u «capella-signin-mailid» -p «capella-signin-password» -cpaddr https://api.cloud.couchbase.com -orgid «capella-organisation-id» -cbhost «hostname of data node» -cbuser «cbcluster username» -cbpassword «cbcluster password» -cbport 18093 translator/test/plsql/blog_test.sql

Código:

function insert_employee(p_emp_id, p_first_name, p_last_name, p_salary){
    var params = [p_emp_id, p_first_name, p_last_name, p_salary];
    var query = N1QL('INSERT INTO test.testscope.employees VALUES ($1, {"emp_id":$1, "first_name":$2, "last_name":$3, "salary":$4})', params);
    query.close();
}

function update_employee(p_emp_id, p_first_name, p_last_name, p_salary){
    var params = [p_first_name, p_last_name, p_salary, p_emp_id];
    var query = N1QL('UPDATE test.testscope.employees SET first_name = $1, last_name = $2, salary = $3 WHERE emp_id = $4', params);
    query.close();
}

function delete_employee(p_emp_id){
    var querybegin=BEGIN WORK; 
    var params = [p_emp_id];
    var query= N1QL('DELETE FROM test.testscope.employees WHERE emp_id = $1',params); 
    query.close(); 
    var querycommit=COMMIT WORK; 
    querycommit.close(); 
}

function get_employee(p_emp_id){
    var query = N1QL('SELECT first_name, last_name, salary FROM test.testscope.employees WHERE emp_id = $1', [p_emp_id]);
    var rs = [];
    for (const row of query) { 
        rs.push(row); 
    }
    query.close();
    var p_first_name = rs[0]['first_name'];
    var p_last_name = rs[0]['last_name'];
    var p_salary = rs[0]['salary'];
    return {first_name: p_first_name, last_name: p_last_name, salary: p_salary};
}

function insert_employee(p_emp_id, p_first_name, p_last_name, p_salary){

var params = [p_emp_id, p_first_name, p_last_name, p_salary];

var query = N1QL('INSERT INTO test.testscope.employees VALUES ($1, {"emp_id":$1, "first_name":$2, "last_name":$3, "salary":$4})', params);

query.close();

}

function update_employee(p_emp_id, p_first_name, p_last_name, p_salary){

var params = [p_first_name, p_last_name, p_salary, p_emp_id];

var query = N1QL('UPDATE test.testscope.employees SET first_name = $1, last_name = $2, salary = $3 WHERE emp_id = $4', params);

query.close();

}

function delete_employee(p_emp_id){

var querybegin=BEGIN WORK;

var params = [p_emp_id];

var query= N1QL('DELETE FROM test.testscope.employees WHERE emp_id = $1',params);

query.close();

var querycommit=COMMIT WORK;

querycommit.close();

}

function get_employee(p_emp_id){

var query = N1QL('SELECT first_name, last_name, salary FROM test.testscope.employees WHERE emp_id = $1', [p_emp_id]);

var rs = [];

for (const row of query) {

rs.push(row);

}

query.close();

var p_first_name = rs[0]['first_name'];

var p_last_name = rs[0]['last_name'];

var p_salary = rs[0]['salary'];

return {first_name: p_first_name, last_name: p_last_name, salary: p_salary};

}

Insertemos un nuevo documento de empleado

Crear colección de empleados:

curl -u  Administrator:password https://127.0.0.1:8091/pools/default/buckets/test/scopes/testscope/collections -d name=employees

1	curl -u Administrator:password https://127.0.0.1:8091/pools/default/buckets/test/scopes/testscope/collections -d name=employees

Inserta un empleado:

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION insert_employee(1, "joe", "briggs", 10000)' -k     
{
"requestID": "2c0854c1-d221-42e9-af47-b6aa0801a46c",
"signature": null,
"results": [
],
"errors": [{"code":10109,"msg":"Error executing function 'insert_employee' (blog_test:insert_employee)","reason":{"details":{"Code":"    var query = N1QL('INSERT INTO test.testscope.employees VALUES ($1, {\"emp_id\":$1, \"first_name\":$2, \"last_name\":$3, \"salary\":$4})', params);","Exception":{"_level":"exception","caller":"insert_send:207","code":5070,"key":"execution.insert_key_type_error","message":"Cannot INSERT non-string key 1 of type value.intValue."},"Location":"functions/blog_test.js:5","Stack":"   at insert_employee (functions/blog_test.js:5:17)"},"type":"Exceptions from JS code"}}],
"status": "fatal",
"metrics": {"elapsedTime": "104.172666ms","executionTime": "104.040291ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2,"errorCount": 1}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION insert_employee(1, "joe", "briggs", 10000)' -k

{

"requestID": "2c0854c1-d221-42e9-af47-b6aa0801a46c",

"signature": null,

"results": [

"errors": [{"code":10109,"msg":"Error executing function 'insert_employee' (blog_test:insert_employee)","reason":{"details":{"Code":" var query = N1QL('INSERT INTO test.testscope.employees VALUES ($1, {\"emp_id\":$1, \"first_name\":$2, \"last_name\":$3, \"salary\":$4})', params);","Exception":{"_level":"exception","caller":"insert_send:207","code":5070,"key":"execution.insert_key_type_error","message":"Cannot INSERT non-string key 1 of type value.intValue."},"Location":"functions/blog_test.js:5","Stack":" at insert_employee (functions/blog_test.js:5:17)"},"type":"Exceptions from JS code"}}],

"status": "fatal",

"metrics": {"elapsedTime": "104.172666ms","executionTime": "104.040291ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2,"errorCount": 1}

}

Esto da error, y Está bien. podemos arreglarlo manualmente.

Lectura del motivo y excepción: No se puede INSERTAR la clave 1 del tipo valor.intValor, ¡Ah! siempre se espera que la clave sea una cadena, pasando insert_employee("1", "joe", "briggs", 10000) sería suficiente, pero no es intuitivo esperar que empleado_id sea una cadena.

Modifiquemos el código generado:

function insert_employee(p_emp_id, p_first_name, p_last_name, p_salary){
    var params = [p_emp_id.toString(), p_emp_id, p_first_name, p_last_name, p_salary];
    var query = N1QL('INSERT INTO test.testscope.employees VALUES ($1, {"emp_id":$2, "first_name":$3, "last_name":$4, "salary":$5})', params);
    query.close();
}

function insert_employee(p_emp_id, p_first_name, p_last_name, p_salary){

var params = [p_emp_id.toString(), p_emp_id, p_first_name, p_last_name, p_salary];

var query = N1QL('INSERT INTO test.testscope.employees VALUES ($1, {"emp_id":$2, "first_name":$3, "last_name":$4, "salary":$5})', params);

query.close();

}

Y vuelva a crear la UDF:

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=CREATE OR REPLACE FUNCTION insert_employee(p_emp_id, p_first_name, p_last_name, p_salary) LANGUAGE JAVASCRIPT AS "insert_employee" AT "blog_test"' -k 
{
"requestID": "89df65ac-2026-4f42-8839-b1ce7f0ea2be",
"signature": null,
"results": [
],
"status": "success",
"metrics": {"elapsedTime": "27.730875ms","executionTime": "27.620083ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=CREATE OR REPLACE FUNCTION insert_employee(p_emp_id, p_first_name, p_last_name, p_salary) LANGUAGE JAVASCRIPT AS "insert_employee" AT "blog_test"' -k

{

"requestID": "89df65ac-2026-4f42-8839-b1ce7f0ea2be",

"signature": null,

"results": [

"status": "success",

"metrics": {"elapsedTime": "27.730875ms","executionTime": "27.620083ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2}

}

Intentando insertar de nuevo:

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION insert_employee(1, "joe", "briggs", 10000)' -k
{
"requestID": "41fb76bf-a87f-4472-b8ba-1949789ae74b",
"signature": null,
"results": [
null
],
"status": "success",
"metrics": {"elapsedTime": "62.431667ms","executionTime": "62.311583ms","resultCount": 1,"resultSize": 4,"serviceLoad": 2}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION insert_employee(1, "joe", "briggs", 10000)' -k

{

"requestID": "41fb76bf-a87f-4472-b8ba-1949789ae74b",

"signature": null,

"results": [

null

"status": "success",

"metrics": {"elapsedTime": "62.431667ms","executionTime": "62.311583ms","resultCount": 1,"resultSize": 4,"serviceLoad": 2}

}

Actualizar a un empleado:

¡Dispara! Hay un error, el empleado 1 no es Joe, es Emily.

Actualicemos al empleado 1

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION update_employee(1, "Emily", "Alvarez", 10000)' -k 
{
"requestID": "92a0ca70-6d0d-4eb1-bf8d-0b4294ae987d",
"signature": null,
"results": [
null
],
"status": "success",
"metrics": {"elapsedTime": "100.967708ms","executionTime": "100.225333ms","resultCount": 1,"resultSize": 4,"serviceLoad": 2}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION update_employee(1, "Emily", "Alvarez", 10000)' -k

{

"requestID": "92a0ca70-6d0d-4eb1-bf8d-0b4294ae987d",

"signature": null,

"results": [

null

"status": "success",

"metrics": {"elapsedTime": "100.967708ms","executionTime": "100.225333ms","resultCount": 1,"resultSize": 4,"serviceLoad": 2}

}

Ver al empleado:

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION get_employee(1)' -k 

{
"requestID": "8f180e27-0028-4653-92e0-606c80d5dabb",
"signature": null,
"results": [
{"first_name":"Emily","last_name":"Alvarez","salary":10000}
],
"status": "success",
"metrics": {"elapsedTime": "101.995584ms","executionTime": "101.879ms","resultCount": 1,"resultSize": 59,"serviceLoad": 2}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION get_employee(1)' -k

{

"requestID": "8f180e27-0028-4653-92e0-606c80d5dabb",

"signature": null,

"results": [

{"first_name":"Emily","last_name":"Alvarez","salary":10000}

"status": "success",

"metrics": {"elapsedTime": "101.995584ms","executionTime": "101.879ms","resultCount": 1,"resultSize": 59,"serviceLoad": 2}

}

Elimina al empleado:

Emily se fue.

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION delete_employee(1)' -k                      

{
"requestID": "18539991-3d97-40e2-bde3-6959200791b1",
"signature": null,
"results": [
],
"errors": [{"code":10109,"msg":"Error executing function 'delete_employee' (blog_test:delete_employee)","reason":{"details":{"Code":"    var querycommit=N1QL('COMMIT WORK;', {}, false); ","Exception":{"_level":"exception","caller":"txcouchbase:240","cause":{"cause":{"bucket":"test","collection":"_default","document_key":"_txn:atr-988-#1b0","error_description":"Durability requirements are impossible to achieve","error_name":"DurabilityImpossible","last_connection_id":"eda95f8c35df6746/d275e8398a49e515","last_dispatched_from":"127.0.0.1:50069","last_dispatched_to":"127.0.0.1:11210","msg":"durability impossible","opaque":7,"scope":"_default","status_code":161},"raise":"failed","retry":false,"rollback":false},"code":17007,"key":"transaction.statement.commit","message":"Commit Transaction statement error"},"Location":"functions/blog_test.js:29","Stack":"   at delete_employee (functions/blog_test.js:29:21)"},"type":"Exceptions from JS code"}}],
"status": "fatal",
"metrics": {"elapsedTime": "129.02975ms","executionTime": "128.724ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2,"errorCount": 1}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION delete_employee(1)' -k

{

"requestID": "18539991-3d97-40e2-bde3-6959200791b1",

"signature": null,

"results": [

"errors": [{"code":10109,"msg":"Error executing function 'delete_employee' (blog_test:delete_employee)","reason":{"details":{"Code":" var querycommit=N1QL('COMMIT WORK;', {}, false); ","Exception":{"_level":"exception","caller":"txcouchbase:240","cause":{"cause":{"bucket":"test","collection":"_default","document_key":"_txn:atr-988-#1b0","error_description":"Durability requirements are impossible to achieve","error_name":"DurabilityImpossible","last_connection_id":"eda95f8c35df6746/d275e8398a49e515","last_dispatched_from":"127.0.0.1:50069","last_dispatched_to":"127.0.0.1:11210","msg":"durability impossible","opaque":7,"scope":"_default","status_code":161},"raise":"failed","retry":false,"rollback":false},"code":17007,"key":"transaction.statement.commit","message":"Commit Transaction statement error"},"Location":"functions/blog_test.js:29","Stack":" at delete_employee (functions/blog_test.js:29:21)"},"type":"Exceptions from JS code"}}],

"status": "fatal",

"metrics": {"elapsedTime": "129.02975ms","executionTime": "128.724ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2,"errorCount": 1}

}

De nuevo un error con el código generado, mirando la razón y la excepción podemos confirmar que el código traducido encierra delete en una transacción, lo que no era el caso en el original.

Para las transacciones, los buckets deben tener durabilidad pero esto requiere más de un servidor de datos, de ahí el error.

La solución consiste en modificar el código para eliminar la traducción adjunta:

function delete_employee(p_emp_id){    
    var params = [p_emp_id];
    var query= N1QL('DELETE FROM test.testscope.employees WHERE emp_id = $1',params); 
    query.close(); 
}

function delete_employee(p_emp_id){

var params = [p_emp_id];

var query= N1QL('DELETE FROM test.testscope.employees WHERE emp_id = $1',params);

query.close();

}

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=CREATE OR REPLACE FUNCTION delete_employee(p_emp_id) LANGUAGE JAVASCRIPT AS "delete_employee" AT "blog_test"' -k 

{
"requestID": "e7432b82-1af8-4dc4-ad94-c34acea59334",
"signature": null,
"results": [
],
"status": "success",
"metrics": {"elapsedTime": "31.129459ms","executionTime": "31.022ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=CREATE OR REPLACE FUNCTION delete_employee(p_emp_id) LANGUAGE JAVASCRIPT AS "delete_employee" AT "blog_test"' -k

{

"requestID": "e7432b82-1af8-4dc4-ad94-c34acea59334",

"signature": null,

"results": [

"status": "success",

"metrics": {"elapsedTime": "31.129459ms","executionTime": "31.022ms","resultCount": 0,"resultSize": 0,"serviceLoad": 2}

}

curl -u  Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION delete_employee(1)' -k

{
"requestID": "d440913f-58ff-4815-b671-1a72b75bb7eb",
"signature": null,
"results": [
null
],
"status": "success",
"metrics": {"elapsedTime": "33.8885ms","executionTime": "33.819042ms","resultCount": 1,"resultSize": 4,"serviceLoad": 2}
}

curl -u Administrator:password https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION delete_employee(1)' -k

{

"requestID": "d440913f-58ff-4815-b671-1a72b75bb7eb",

"signature": null,

"results": [

null

"status": "success",

"metrics": {"elapsedTime": "33.8885ms","executionTime": "33.819042ms","resultCount": 1,"resultSize": 4,"serviceLoad": 2}

}

Ahora, todas las funciones en el PL/SQL original funcionan en Couchbase a través de JavaScript UDFs. Sí, el ejemplo es bastante trivial, pero puedes entender cómo usar la herramienta para migrar tus scripts PL/SQL con poca supervisión manual.

Recuerda que se supone que la herramienta te llevará 80%, los otros 20% aún los tienes que hacer tú, ¡pero mucho mejor que escribir todo ese código tú mismo!

El futuro

Este proyecto es de código abierto, así que siéntete libre de contribuir. Algunas ideas que se han sugerido:

1. IA crítica que puede criticar el código generado para garantizar que la intervención manual no sea necesaria en absoluto.
2. Actualmente, el código fuente es un código que simplemente funciona; no se ha pensado en el paralelismo ni en la reutilización del código.

Y también incluye las limitaciones comentadas anteriormente.

Recursos

- Couchbase Labs GitHub - PL/SQL a JSUDF
- ANTLR generador de analizadores sintácticos

Por último, me gustaría dar las gracias a Kamini Jagtiani por guiarme y Pierre Regazzoni por ayudarme a probar la herramienta de conversión.

Gaurav Jayaraj - Ingeniero de software

Comparte este artículo

Platform

Self-Managed

Services

Capabilities

By Use Case

By Industry

Popular Docs

Quickstart

Resource Center

About

Partnerships

Una herramienta para facilitar la transición de Oracle PL/SQL a Couchbase JavaScript UDF

¿Qué es PL/SQL?

¿Qué es una UDF de JavaScript?

El problema

Solución

Cómo funciona el traductor de IA

Cómo utilizar la herramienta

Problemas conocidos

1. console.log no es compatible

2. Llamadas a funciones entre paquetes

3. Variables globales

Paquete a la biblioteca

Insertemos un nuevo documento de empleado

Actualizar a un empleado:

El futuro

Recursos

Recibe actualizaciones del blog de Couchbase en tu bandeja de entrada

Autor

Publicado por Gaurav Jayaraj - Ingeniero de software

Deja un comentario Cancelar respuesta

¿Listo para empezar con Couchbase Capella?

Empezar a construir

Utilizar Capella gratis

Póngase en contacto