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	Este modelo's máximo contexto longitud es 8192 fichas. Sin embargo, su mensajes resultó en <Más-que-8192> fichas. Por favor, reducir el longitud de el mensajes.

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 NÚMERO := 0;

contador NÚMERO := 0;

COMENZAR

PARA i EN 1..4 BUCLE

x := x + 1000;

contador := contador + 1;

INSERTAR EN temp VALORES (x, contador, en bucle externo);

--iniciar un interior bloque

DECLARE

x NÚMERO := 0; -- este es a local versión de x

COMENZAR

PARA i EN 1..4 BUCLE

x := x + 1; -- este incrementos el local x

contador := contador + 1;

INSERTAR EN temp VALORES (x, contador, bucle interior);

FIN BUCLE;

FIN;

FIN BUCLE;

COMPROMETERSE;

FIN;

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-a-jsudf -u "capella-firmar-mailid" -p "capella-firmar-contraseña" -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();
}

función bucle anidado(){

var x = 0;

var contador = 0;

var querybegin = COMENZAR TRABAJO;

querybegin.cerrar();

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

x = x + 1000;

contador = contador + 1;

var parámetros = [x, contador];

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

consulta.cerrar();

var x_inner = 0;

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

x_inner = x_inner + 1;

contador = contador + 1;

var params_inner = [x_inner, contador];

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

query_inner.cerrar();

}

var querycommit = COMPROMETERSE TRABAJO;

querycommit.cerrar();

}

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-nombre",

"log": "argumento a consola.registro", // la línea de registro real

"tiempo": "actual ISO tiempo cadena"

}

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	SELECCIONAR * DESDE registro DONDE udf= ""func-name""; SELECCIONAR * DESDE registro DONDE tiempo ENTRE "fecha1" Y ""fecha2"";

Un ejemplo:

El PL/SQL original

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

COMENZAR

SGBD.SALIDA.PUT("¡Hola mundo!");

FIN;

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();
}

función holaMundo() {

// solución para console.log("¡Hola mundo!");

var fechaactual = nuevo Fecha();

var utcISOString = fechaactual.toISOString();

var parámetros = [utcISOString,anónimobloque1,"¡Hola mundo!"];

var consulta de registro = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', parámetros);

consulta de registro.cerrar();

}

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"}}
]

EJECUTAR FUNCIÓN holaMundo();

"resultados": [

null

]

CREAR PRIMARIO ÍNDICE EN registro;

"resultados": [

]

SELECCIONAR * DESDE registro;

"resultados": [

{"registro":{"log":"¡Hola mundo!","tiempo":"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;
/

CREAR O SUSTITUIR PAQUETE utilidades_matemáticas AS

-- Público función a añada dos números

FUNCIÓN añadir_números(p_num1 NÚMERO, p_num2 NÚMERO) DEVOLVER NÚMERO;

FIN utilidades_matemáticas;

CREAR O SUSTITUIR PAQUETE CUERPO utilidades_matemáticas AS

FUNCIÓN añadir_números(p_num1 NÚMERO, p_num2 NÚMERO) DEVOLVER NÚMERO IS

COMENZAR

DEVOLVER p_num1 + p_num2;

FIN añadir_números;

FIN utilidades_matemáticas;

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;
/

CREAR O SUSTITUIR PAQUETE mostrar_suma AS

-- Público procedimiento a mostrar el suma de dos números

PROCEDIMIENTO mostrar_suma(p_num1 NÚMERO, p_num2 NÚMERO);

FIN mostrar_suma;

CREAR O SUSTITUIR PAQUETE CUERPO mostrar_suma AS

PROCEDIMIENTO mostrar_suma(p_num1 NÚMERO, p_num2 NÚMERO) IS

v_suma NÚMERO;

COMENZAR

-- Llamando a el añadir_números función de utilidades_matemáticas paquete

v_suma := utilidades_matemáticas.añadir_números(p_num1, p_num2);

-- Visualización de el suma utilizando DBMS_OUTPUT.PUT_LÍNEA

DBMS_OUTPUT.PUT_LINE('La suma de ' || p_num1 || y || p_num2 || ' es ' || v_suma);

FIN mostrar_suma;

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();
}

función mostrar_suma(a, b) {

var suma_resultado;

// Solución para la llamada a la función de biblioteca cruzada math_utils.add_numbers(a, b)

var crossfunc = N1QL("EJECUTAR FUNCIÓN añadir_números($1,$2)",[a, b])

var crossfuncres = []

para(const doc de crossfunc) {

crossfuncres.pulse(doc);

}

// sustitución real de math_utils.add_numbers(a, b)

suma_resultado = crossfuncres[0];

// solución para console.log('La suma de ' + a + ' y ' + b + ' es: ' + suma_resultado);

var fechaactual = nuevo Fecha();

var utcISOString = fechaactual.toISOString();

var parámetros = [utcISOString,SHOW_SUM,'La suma de ' + a + y + b + ' es: ' + suma_resultado];

var consulta de registro = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', parámetros);

consulta de registro.cerrar();

}

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;
   /

CREAR O SUSTITUIR PAQUETE global_vars_pkg AS

-- Global variable declaraciones

g_contador NÚMERO := 0;

g_mensaje VARCHAR2(100) := Mensaje inicial;

-- Público procedimiento declaraciones

PROCEDIMIENTO contador_de_incrementos;

PROCEDIMIENTO set_message(p_mensaje VARCHAR2);

PROCEDIMIENTO mostrar_globales;

FIN global_vars_pkg;

CREAR O SUSTITUIR PAQUETE CUERPO global_vars_pkg AS

-- Procedimiento a incrementar el contador

PROCEDIMIENTO contador_de_incrementos IS

COMENZAR

g_contador := g_contador + 1;

FIN contador_de_incrementos;

-- Procedimiento a configure el global mensaje

PROCEDIMIENTO set_message(p_mensaje VARCHAR2) IS

COMENZAR

g_mensaje := p_mensaje;

FIN set_message;

-- Procedimiento a mostrar el actual valores de global variables

PROCEDIMIENTO mostrar_globales IS

COMENZAR

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

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

FIN mostrar_globales;

FIN 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
}

función contador_de_incrementos(contador){

contador = contador + 1;

devolver contador

}

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();
}

función mostrar_globales(contador, mensaje){

// solución para console.log(counter);

var fechaactual = nuevo Fecha();

var utcISOString = fechaactual.toISOString();

var parámetros = [utcISOString,SHOW_GLOBALS,correo electrónico];

var consulta de registro = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', parámetros);

consulta de registro.cerrar();

// solución para console.log(mensaje);

var fechaactual = nuevo Fecha();

var utcISOString = fechaactual.toISOString();

var parámetros = [utcISOString,SHOW_GLOBALS,mensaje];

var consulta de registro = N1QL('INSERT INTO logging VALUES(UUID(),{"udf":$2, "log":$3, "time":$1}, {"expiration": 5*24*60*60 })', parámetros);

consulta de registro.cerrar();

}

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;
/

CREAR O SUSTITUIR PAQUETE emp_pkg IS

PROCEDIMIENTO insertar_empleado(

p_emp_id EN empleados.emp_id%TIPO,

p_nombre EN empleados.nombre%TIPO,

p_último_nombre EN empleados.apellido%TIPO,

p_salario EN empleados.salario%TIPO

);

PROCEDIMIENTO actualizar_empleado(

p_emp_id EN empleados.emp_id%TIPO,

p_nombre EN empleados.nombre%TIPO,

p_último_nombre EN empleados.apellido%TIPO,

p_salario EN empleados.salario%TIPO

);

PROCEDIMIENTO eliminar_empleado(

p_emp_id EN empleados.emp_id%TIPO

);

PROCEDIMIENTO get_employee(

p_emp_id EN empleados.emp_id%TIPO,

p_nombre OUT empleados.nombre%TIPO,

p_último_nombre OUT empleados.apellido%TIPO,

p_salario OUT empleados.salario%TIPO

);

FIN emp_pkg;

CREAR O SUSTITUIR PAQUETE CUERPO emp_pkg IS

PROCEDIMIENTO insertar_empleado(

p_emp_id EN empleados.emp_id%TIPO,

p_nombre EN empleados.nombre%TIPO,

p_último_nombre EN empleados.apellido%TIPO,

p_salario EN empleados.salario%TIPO

) IS

COMENZAR

INSERTAR EN empleados (emp_id, nombre, apellido, salario)

VALORES (p_emp_id, p_nombre, p_apellido_nombre, p_salario);

FIN insertar_empleado;

PROCEDIMIENTO actualizar_empleado(

p_emp_id EN empleados.emp_id%TIPO,

p_nombre EN empleados.nombre%TIPO,

p_último_nombre EN empleados.apellido%TIPO,

p_salario EN empleados.salario%TIPO

) IS

COMENZAR

ACTUALIZACIÓN empleados

SET nombre = p_nombre,

último_nombre = p_apellido_nombre,

salario = p_salario

DONDE emp_id = p_emp_id;

FIN actualizar_empleado;

PROCEDIMIENTO eliminar_empleado(

p_emp_id EN empleados.emp_id%TIPO

) IS

COMENZAR

BORRAR DESDE empleados

DONDE emp_id = p_emp_id;

FIN eliminar_empleado;

PROCEDIMIENTO get_employee(

p_emp_id EN empleados.emp_id%TIPO,

p_nombre OUT empleados.nombre%TIPO,

p_último_nombre OUT empleados.apellido%TIPO,

p_salario OUT empleados.salario%TIPO

) IS

COMENZAR

SELECCIONE nombre, apellido, salario

EN p_nombre, p_apellido_nombre, p_salario

DESDE empleados

DONDE emp_id = p_emp_id;

FIN get_employee;

FIN 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-a-jsudf -u "capella-firmar-mailid" -p "capella-firmar-contraseña" -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};
}

función insertar_empleado(p_emp_id, p_nombre, p_apellido_nombre, p_salario){

var parámetros = [p_emp_id, p_nombre, p_apellido_nombre, p_salario];

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

consulta.cerrar();

}

función actualizar_empleado(p_emp_id, p_nombre, p_apellido_nombre, p_salario){

var parámetros = [p_nombre, p_apellido_nombre, p_salario, p_emp_id];

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

consulta.cerrar();

}

función eliminar_empleado(p_emp_id){

var querybegin=COMENZAR TRABAJO;

var parámetros = [p_emp_id];

var consulta= N1QL('DELETE FROM test.testscope.empleados WHERE emp_id = $1',parámetros);

consulta.cerrar();

var querycommit=COMPROMETERSE TRABAJO;

querycommit.cerrar();

}

función get_employee(p_emp_id){

var consulta = N1QL(SELECT nombre, apellido, salario FROM test.testscope.empleados WHERE emp_id = $1', [p_emp_id]);

var rs = [];

para (const fila de consulta) {

rs.pulse(fila);

}

consulta.cerrar();

var p_nombre = rs[0][nombre];

var p_apellido_nombre = rs[0][apellido];

var p_salario = rs[0]["salario];

devolver {nombre: p_nombre, apellido: p_apellido_nombre, salario: p_salario};

}

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	rizo -u Administrador:contraseña https://127.0.0.1:8091/pools/default/buckets/test/scopes/testscope/collections -d name=empleados

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}
}

rizo -u Administrador:contraseña 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",

"firma": null,

"resultados": [

"errores": [{"código":10109,"msg":"Error al ejecutar la función 'insertar_empleado' (blog_test:insertar_empleado)","razón":{"detalles":{"Código":"var query = N1QL('INSERT INTO test.testscope.employees VALUES ($1, {\"emp_id\":$1, \"first_name\":$2, \"last_name\":$3, \"salary\":$4})', params);","Excepción":{"_nivel":"excepción","llamante":"insertar_enviar:207","código":5070,"llave":"execution.insert_key_type_error","mensaje":"No se puede INSERTAR clave 1 de tipo valor.intValor".},"Localización":"functions/blog_test.js:5","Pila":" at insert_employee (functions/blog_test.js:5:17)"},"tipo":"Excepciones del código JS"}}],

"status": "fatal",

"métricas": {"tiempo transcurrido": "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();
}

función insertar_empleado(p_emp_id, p_nombre, p_apellido_nombre, p_salario){

var parámetros = [p_emp_id.toString(), p_emp_id, p_nombre, p_apellido_nombre, p_salario];

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

consulta.cerrar();

}

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}
}

rizo -u Administrador:contraseña 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",

"firma": null,

"resultados": [

"status": "éxito",

"métricas": {"tiempo transcurrido": "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}
}

rizo -u Administrador:contraseña 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",

"firma": null,

"resultados": [

null

"status": "éxito",

"métricas": {"tiempo transcurrido": "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}
}

rizo -u Administrador:contraseña 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",

"firma": null,

"resultados": [

null

"status": "éxito",

"métricas": {"tiempo transcurrido": "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}
}

rizo -u Administrador:contraseña https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION get_employee(1)' -k

{

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

"firma": null,

"resultados": [

{"nombre":"Emily","apellido":"Alvarez","salario":10000}

"status": "éxito",

"métricas": {"tiempo transcurrido": "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}
}

rizo -u Administrador:contraseña https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION delete_employee(1)' -k

{

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

"firma": null,

"resultados": [

"errores": [{"código":10109,"msg":"Error al ejecutar la función 'delete_employee' (blog_test:delete_employee)","razón":{"detalles":{"Código":"var querycommit=N1QL('COMMIT WORK;', {}, false); ","Excepción":{"_nivel":"excepción","llamante":"txcouchbase:240","causa":{"causa":{"cubo":"test","colección":"_por defecto","clave_documento":"_txn:atr-988-#1b0","error_description":"Los requisitos de durabilidad son imposibles de cumplir","nombre_error":"DurabilidadImposible","última_conexión_id":"eda95f8c35df6746/d275e8398a49e515","último_despacho_desde":"127.0.0.1:50069","last_dispatched_to":"127.0.0.1:11210","msg":"durabilidad imposible","opaco":7,"ámbito":"_por defecto","código_estado":161},"subir":"fallido","reintentar":falso,"rollback":falso},"código":17007,"llave":"transacción.statement.commit","mensaje":"Commit Transaction statement error"},"Localización":"functions/blog_test.js:29","Pila":"at delete_employee (functions/blog_test.js:29:21)"},"tipo":"Excepciones del código JS"}}],

"status": "fatal",

"métricas": {"tiempo transcurrido": "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(); 
}

función eliminar_empleado(p_emp_id){

var parámetros = [p_emp_id];

var consulta= N1QL('DELETE FROM test.testscope.empleados WHERE emp_id = $1',parámetros);

consulta.cerrar();

}

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}
}

rizo -u Administrador:contraseña 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",

"firma": null,

"resultados": [

"status": "éxito",

"métricas": {"tiempo transcurrido": "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}
}

rizo -u Administrador:contraseña https://127.0.0.1:18093/query/service -d 'statement=EXECUTE FUNCTION delete_employee(1)' -k

{

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

"firma": null,

"resultados": [

null

"status": "éxito",

"métricas": {"tiempo transcurrido": "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

Why Couchbase?

Migrate to Capella

By Use Case

By Industry

By Application Need

Popular Docs

By Developer Role

Quickstart

Resource Center

About

Partnerships

Our Services

Partners: Register a Deal

Ready to register a deal with Couchbase?

Marriott

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

Author

Posted by 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