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

O que é PL/SQL?

O PL/SQL é uma linguagem procedural projetada especificamente para incluir instruções SQL em sua sintaxe. Ele inclui elementos de linguagem procedural, como condições e loops, e pode tratar exceções (erros em tempo de execução).

O PL/SQL é nativo dos bancos de dados Oracle, e bancos de dados como IBM DB2, PostgreSQL e MySQL suportam construções PL/SQL por meio de recursos de compatibilidade.

O que é um UDF JavaScript?

O JavaScript UDF é a alternativa do Couchbase ao PL/SQL.

O JavaScript UDF traz a flexibilidade de scripts de uso geral do JavaScript para os bancos de dados, permitindo operações dinâmicas e avançadas em sistemas de bancos de dados modernos e aumentando a flexibilidade na consulta, no processamento e na transformação de dados.

A maioria dos bancos de dados modernos, como Couchbase, MongoDB, Snowflake e Google BigQuery, é compatível com JavaScript UDF.

O problema

Um problema comum visto pelos usuários que migram do Oracle para o Couchbase é a portabilidade de seus scripts PL/SQL. Em vez de oferecer suporte a PL/SQL, o Couchbase permite que os usuários construam funções definidas pelo usuário em JavaScript (com suporte desde 2021).

Os UDFs JavaScript permitem a manipulação fácil e intuitiva de dados de variantes e JSON. Os objetos variantes passados para um UDF são transformados em tipos e valores JavaScript nativos.

A consequência não intencional disso é que a maioria dos RDBMS existentes nos últimos dez anos incentivou fortemente os desenvolvedores a acessar o banco de dados usando suas extensões procedimentais para SQL (PL/pgSQL, PL/SQL), que oferecem suporte a construções procedimentais, integração com SQL, tratamento de erros, funções e procedimentos, acionadores e cursores ou, no mínimo, funções e procedimentos (como Sakila). Para qualquer tentativa de se afastar deles, todos os seus scripts precisariam ser reescritos.

A reescrita de código costuma ser uma tarefa tediosa, especialmente quando se trata de scripts PL/SQL que foram escritos nos anos 2000 e mantidos desde então. Esses scripts podem ser complexos, muitas vezes chegando a milhares de linhas, o que pode ser um fardo para o usuário corporativo médio.

Solução

A abordagem ideal seria desenvolver um avaliador PL/SQL totalmente novo, mas isso exigiria uma quantidade excessiva de horas de engenharia e, para o mesmo caso de uso, já temos um avaliador JSE moderno, estável e rápido.

Isso torna o problema um caso de uso perfeito para aproveitar os avanços contínuos em IA e LLMs. E foi isso que fizemos aqui. Usamos modelos de IA generativa para automatizar a conversão de PL/SQL para JSUDF.

A partir de junho de 2024, os modelos têm uma janela de contexto limitada, wo que significa que o PL/SQL mais longo é atingido pelo erro:

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.

Observe que isso é para o GPT4.

Então, esperamos que a IA se torne mais poderosa e permita mais tokens (como a Lei de Moore, mas para o comprimento de contexto versus precisão da IA)?

Não, é aí que ANTLR, a ferramenta geradora de analisadores. O ANTLR é conhecido por ser usado para o desenvolvimento de compiladores e interpretadores. Dessa forma, podemos dividir o script grande em unidades menores que podem ser traduzidas de forma independente.

Então, agora estamos construindo um transpilador? Bem, sim e não.

Estágios em um transpilador:

1. Análise lexical (tokenização)
2. Análise sintática (parsing)
3. Análise semântica
4. Geração de Representação Intermediária (RI)
5. Otimização (opcional)
6. Geração do código de destino

Como o tradutor de IA funciona

As etapas 1 e 2 acima são realizadas com o ANTLR. Usamos a interface Listener do ANTLR para pegar Procedimento/Função/Bloco anônimo individualpois são blocos de código independentes. No caso de o procedimento/função/bloco anônimo exceder a janela de contexto, traduzimos no nível da instrução (em que o LLM assume a existência do uso de variáveis/chamadas de função que não estão definidas aqui, mas em algum lugar anterior).

Posteriormente, as etapas 3, 4, 5 e 6 são deixadas para o LLM (por exemplo, GPT), ou seja, a tradução de cada bloco PL/SQL em uma função JavaScript da melhor forma possível, que também preserva a semântica operacional do bloco e é sintaticamente precisa.

Os resultados são surpreendentemente bastante positivos; a tradução é 80-85% precisa.

Outro benefício da solução é que reduzimos a alucinação ao nos concentrarmos em uma tarefa de cada vez, resultando em traduções mais precisas.

Para visualizar:

Como usar a ferramenta

- Faça o download do executável em GitHub do Couchbase Labs e acessar o LEIAME.

O executável espera os seguintes argumentos de linha de comando:

-u: e-mail de login do capella
-p: senha de login do capella
-cpaddr: capella-url para a API de complementos de bate-papo
-orgid: ID da organização no caminho da API de chat-completions
-cbhost: node-ip: nó do cbcluster
-cbuser: nome do usuário do cluster: usuário do cbcluster, adicionado por meio do acesso ao banco de dados
-cbpassword: cluster-password: senha do cbcluster, adicionada por meio do acesso ao banco de dados
-cbport: porta tls do serviço de consulta: geralmente 18093
filepath , ou seja, o caminho para o script PL/SQL que deve ser traduzido
saída-> No diretório de saída, um arquivo com o mesmo nome do arquivo plsql é gerado com o código da biblioteca JavaScript traduzido.

Um exemplo:

cat exemplo1.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 o script acima:

- Um loop externo é executado por 4 iterações, incrementando x em 1.000 e o contador em 1
- O loop interno é executado por 4 iterações, incrementando x em 1 e o contador em 1

Executar o tradutor no PL/SQL de teste:

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

Saída JSUDF:

cat output/example1.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();

}

O script traduzido tem uma função loop aninhado (nome gerado pelo LLM) que faz exatamente o que o bloco PL/SQL anônimo original especifica.

Observação: para funções/procedimentos nomeados, as funções JS traduzidas terão o mesmo nome. Para blocos anônimos, o LLM usa um nome que ele mesmo cria.

Problemas conhecidos

PL/SQL e JS são duas linguagens diferentes, e a forma como são suportadas no Oracle e no Couchbase não permite um mapeamento direto e limpo entre as duas.

Abaixo estão algumas limitações que descobrimos e as soluções alternativas que implementamos para elas:

1. console.log não é compatível

DBMS_OUTPUT.PUT procedimento interno e outros 2 procedimentos internos semelhantes, DBMS_OUTPUT.PUT_LINE e DBMS_OUTPUT.NEW_LINE são traduzidos para console.log()mas console.log é uma API de navegador e não é compatível com a implementação de avaliação JavaScript do Couchbase. Essa tem sido uma pergunta frequente, considerando que a função de eventos do Couchbase é compatível com print() mas não em UDFs JavaScript.

Solução alternativa:

Espera-se que os usuários criem um registro balde.

Os registros são inseridos como parte de um documento INSERT into padrão.padrão coleta. O documento seria algo parecido com:

{
   "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»

}

O usuário pode consultar seus registros selecionando em 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»";

Um exemplo:

O PL/SQL original

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

BEGIN

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

END;

Traduzido para 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();

}

Isso já está implementado na ferramenta.

Para visualizar o 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. Chamadas de função entre pacotes

Os procedimentos/funções listados na especificação do pacote são globais e podem ser usados em outros pacotes por meio de "package_name". "public_procedure/function". Mas o mesmo não se aplica a uma biblioteca JavaScript no Couchbase, pois as construções de importação-exportação não são compatíveis com a implementação de avaliação JavaScript do Couchbase.

Solução alternativa:

No caso de uma chamada de função entre bibliotecas "nome_da_biblioteca". "função"()Se o usuário tiver a biblioteca referenciada, espera-se que "lib_name" já criado; você pode verificar isso via GET /evaluator/v1/libraries

A função referenciada "function" (função) também deve ser criado como um UDF global; isso pode ser verificado por meio de GET /admin/functions_cache ou select system:functions keyspace. Dessa forma, podemos acessar a função via SQL++/N1QL.

Um exemplo:

Utilitários de matemática Pacote

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;

show_sum Pacote

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 traduzido:

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

}

Ele é tratado automaticamente pelo programa, com um aviso de que deve ser verificado por um par de olhos humanos!

3. Variáveis globais

O PL/SQL suporta variáveis globais em nível de pacote e de sessão, mas as variáveis globais não são suportadas no JSUDF deliberadamente por design, pois isso causa preocupação com vazamentos de memória.

A solução alternativa sugerida requer ajustes manuais na tradução gerada. Por exemplo:

  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;

Qualquer função que modifique uma variável global deve aceitá-la como um argumento e retorná-la ao chamador.

increment_counter:

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

function increment_counter(counter){

counter = counter + 1;

return counter

}

Qualquer função que apenas leia um global pode aceitá-lo como argumento.

show_globals:

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

}

Pacote para a biblioteca

Esta seção mostra uma conversão de pacote para biblioteca de ponta a ponta usando a ferramenta.

Exemplo de pacote PL/SQL:

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;

Tradução:

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

}

Vamos inserir um novo documento de funcionário

Criar coleção de funcionários:

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

Inserir um funcionário:

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}

}

Isso gera erros e Não tem problema podemos corrigi-lo manualmente.

Ler o motivo e exceção: Não é possível INSERIR a chave 1, que não é uma string, do tipo value.intValue, Ah! sempre se espera que a chave seja uma string, passando insert_employee("1", "joe", "briggs", 10000) seria suficiente, mas não é intuitivo esperar que o ID do funcionário para ser uma string.

Vamos alterar o código gerado:

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

}

E recriar o 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}

}

Tentando inserir novamente:

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}

}

Atualizar um funcionário:

Droga! Há um erro, o funcionário 1 não é o Joe, é a Emily.

Vamos atualizar o funcionário 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 o funcionário:

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}

}

Excluir o funcionário:

Emily saiu.

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}

}

Novamente um erro com o código gerado. Observando o motivo e a exceção, podemos confirmar que o código traduzido inclui a exclusão em uma transação, o que não era o caso no original.

Para transações, os buckets precisam ter durabilidade mas isso requer mais de um servidor de dados, por isso o erro.

A correção aqui é alterar o código para remover a tradução anexa:

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}

}

Agora, todas as funções do PL/SQL original funcionam no Couchbase por meio de UDFs JavaScript. Sim, o exemplo é bastante trivial, mas você entendeu a essência de como usar a ferramenta para migrar seus scripts PL/SQL com pouca supervisão manual.

Lembre-se de que a ferramenta deve lhe fornecer 80%, os outros 20% ainda precisam ser feitos por você, mas é muito melhor do que escrever todo esse código por conta própria!

O futuro

Este projeto é de código aberto, portanto, sinta-se à vontade para contribuir. Algumas ideias que foram sugeridas:

1. IA crítica que pode criticar o código gerado para garantir que a intervenção manual não seja necessária
2. Atualmente, o código-fonte é um código que simplesmente funciona; nenhuma ideia de paralelismo ou reutilização de código foi colocada em prática.

E também inclui as limitações discutidas anteriormente.

Recursos

- GitHub do Couchbase Labs - PL/SQL para JSUDF
- ANTLR gerador de analisador

Por fim, gostaria de agradecer a Kamini Jagtiani por me orientar e Pierre Regazzoni por me ajudar a testar a ferramenta de conversão.

Gaurav Jayaraj - Engenheiro de software

Compartilhe este artigo

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

Uma ferramenta para facilitar sua transição do Oracle PL/SQL para o Couchbase JavaScript UDF

O que é PL/SQL?

O que é um UDF JavaScript?

O problema

Solução

Como o tradutor de IA funciona

Como usar a ferramenta

Problemas conhecidos

1. console.log não é compatível

2. Chamadas de função entre pacotes

3. Variáveis globais

Pacote para a biblioteca

Vamos inserir um novo documento de funcionário

Atualizar um funcionário:

O futuro

Recursos

Receba atualizações do blog do Couchbase em sua caixa de entrada

Author

Posted by Gaurav Jayaraj - Engenheiro de software

Deixe um comentário Cancelar resposta

Pronto para começar a usar o Couchbase Capella?

Iniciar a construção

Use o Capella gratuitamente

Entre em contato