HashCode

Depois de uma escravização no fim do ano passado e no inicio deste, estou de volta para “espalhar a maldade”!

Vou falar sobre PL/Ruby ou melhor fazer funcionar.

PL/Ruby é uma linguagem procedural acoplada dentro do Postgresql. Entre as linguagem do Postgres é a mais flexível e fácil.
Demonstrarei inicialmente “o jeitão da coisa” e depois uma implementação distribuída.

Instalação
Instale o banco e o módulo plruby, se o postres já estiver instalado, instale apenas o módulo(claro!)

apt-get install postgresql-8.1 postgresql-8.1-plruby

Agora conecte via psql

psql template1

Criaremos um banco novo para todos os testes deste tutorial, o banco “ruby_test”, então vamos lá

template1=# create database ruby_test;

conecte no banco novo

template1=# \c ruby_test

Crie a função/linguagem via o script plruby.sql que faz parte do pacote postgresql-8.1-plruby, se a sua distro difere de ubuntu, verifique onde o arquivo plruby.sql está e invoque com \i semelhante ao exemplo abaixo.

ruby_test=# \i /usr/share/postgresql-8.1-plruby/plruby.sql

Jeitão
Para familiarizarmos um pouco vamos criar uma função que produz uma seqüencia de números separados por espaço.
Portanto a função abaixo cria um Range do primeiro argumento(args[0]) até o segundo argumento(args[1]), converte o range para Array e depois para String separando os elementos por espaço.

create or replace function string_array(int,int) returns text as $RUBY$


  (args[0]..args[1]).to_a.join " "

$RUBY$ language plruby;

Executando

ruby_test=# select string_array(10,20);

Produzindo

"10 11 12 13 14 15 16 17 18 19 20"

Agora uma função que procura um padrão dentro do um texto, se o padrão for encontrado, o mesmo ficará entre tags <b>.

create or replace function find(varchar,text) returns text as $RUBY$


  args[1].gsub(/(#{args[0]})/,'<b>\1</b>')

$RUBY$ language plruby;

ruby_test=# select find('[tT]he','The method PL#context and PL#context= give the possibility to store information between the call')

Produto

"<b>The</b> method PL#context and PL#context= give <b>the</b> possibility to store information between <b>the</b> call"

Até agora demonstramos duas funções, faz uma lógica e retorna apenas uma tupla.
Ficou bem elegante essa implementação do PL/Ruby para vários registros(setof) sendo que cada registro é um objeto lançado pelo operador yield do Ruby.
Uma função em PL/Ruby para resolver a função(x ao quadrado vezes a raíz de 2)

f(x) = x^2 * raiz(2)

sendo x inteiro variando de x1 a x2.

create or replace function fx(int,int) returns setof float as $RUBY$


  (args[0]..args[1]).each {|x|  yield x.to_i**2 * Math.sqrt(2) }

$RUBY$ language plruby;

Executando com * from pois estamos pegando vários registros

ruby_test=# select * from fx(1,10);

Resultando em

1.4142135623731
5.65685424949238
12.7279220613579
22.6274169979695
35.3553390593274
50.9116882454314
69.2964645562817
90.5096679918781
114.551298552221
141.42135623731

Manipulando dados
Vamos criar uma tabela denominada “users” para povoarmos de dados para os testes.

ruby_test=# CREATE TABLE users(
  id serial not null primary key,
  name varchar(255) not null ,
  login varchar(80),
  email varchar(80),
  password varchar(80)
) ;

Inserindo alguns registros

ruby_test=# insert into users(name,login,email,password) values ('Fulano de tal','fulano','fulano@server.com','fulanopwd');
ruby_test=# insert into users(name,login,email,password) values ('Maria da Silva','maria','maria.silva@server.com','mariapwd');
ruby_test=# insert into users(name,login,email,password) values ('Sem nome','semnome','semnome@server.com','semnomepwd');

Daqui a pouco faremos a distribuição dos dados via trigger para bancos remotos, portanto criaremos uma função para criação dos inserts.
A função make_insert faz engenharia reversa na ddl e nos dados utilizando o módulo PL disponível no plruby

CREATE OR REPLACE FUNCTION make_insert(users) RETURNS text AS $RUBY$

    row=args[0]
    tn=PL.args_type.to_s
    cn=row.keys.join(',')
    values= row.values.map{|m| (m)?"'#{m}'":'NULL' }.join(',')
    "INSERT INTO #{tn}(#{cn}) values (#{values})"

$RUBY$ language plruby;

Veja o exemplo da execução

ruby_test=# select make_insert(users) from users;

Resultado

INSERT INTO users(name,id,password,login,email) values ('Fulano de tal','1','fulanopwd','fulano','fulano@server.com')
INSERT INTO users(name,id,password,login,email) values ('Maria da Silva','2','mariapwd','maria','maria.silva@server.com')
INSERT INTO users(name,id,password,login,email) values ('Sem nome','3','semnomepwd','semnome','semnome@server.com')

Sem aquela concatenação demasiada do plpgsql usando pipes e aspas para todo lado. Ah! a função make_insert é genérica para qualquer tabela mas no argumento de criação a sua tabela. Tipo

CREATE OR REPLACE FUNCTION make_insert(sua_tabela) RETURNS text AS $RUBY$
...

Semelhante aos parâmentos tabela%rowtype do postgres.

Distribuindo os dados
Já vou dizendo logo, isso não é comparável ao Slony-I, isso é uma simples utilização do dblink do postgresql.
As funções de dblink ficam no pacote contrib do postgres, a instalação com apt-get

apt-get install postgresql-contrib-8.1

Crie as funções com \i onde o argumento é o arquivo dblink.sql

ruby_test=#  \i /usr/share/postgresql/8.1/contrib/dblink.sql

Lembrando a assinatura da função dblink_exec é

select dblink_exec("connection string","query")

Exemplo

select dblink_exec('hostaddr=127.0.0.1 dbname=mydb user=shairon password=senha','insert into...');

Partiremos da seguinte extrategia: criaremos uma tabela chamada nodes onde armazenaremos os dados dos bancos de dados, selecionaremos apenas os nodes ativos e enviaremos os registros para os mesmos através da trigger replica_plruby(T) no evento after insert na tabela users master(A).
Veja o diagrama abaixo.

Então temos a tabela nodes.

create table nodes(id serial not null primary key, host varchar(80), username varchar(80), password varchar(80), active boolean )

Dados para teste

insert into nodes(host,dbname,username,password,active) values ('127.0.0.1','mydb1','foo','bar',true);
insert into nodes(host,dbname,username,password,active) values ('192.168.0.7','mydb2','foo','bar',true);
insert into nodes(host,dbname,username,password,active) values ('10.0.0.38','mydb3','foo','bar',true);

Criando a trigger replica_plruby, acompanhe pelos comentários

CREATE OR REPLACE FUNCTION replica_plruby() RETURNS trigger AS  $RUBY$

  #obtendo a quantidade de nós ativos
  count=PL::exec("select count(*) from nodes where active=true",1,"value").to_s.to_i
  if count > 0
     #obtendo os nós ativos
     PL::Plan.new("select * from nodes where active=true").each do |row|
       #montando a conexão
       #cada row é um hash  tipo {"dbname"=>"delme", "username"=>"shairon" ...}
       conn="hostaddr=#{row['host']} dbname=#{row['dbname']} user=#{row['username']} password=#{row['password']}"
       #Nome das culunas via metadata
       keys  = new.keys.join(",")
       #Os valores com tratamento de nulo
       values = new.values.map{|m| (m)?"''#{m}''":'NULL' }.join(',')
       #Query final
       query = "INSERT INTO #{tg['relname']}(#{keys}) values (#{values})"
       #Disparo
       PL.exec("select dblink_exec('#{conn}','#{query}')")
     end
  end
  new
$RUBY$ LANGUAGE plruby;

Pronto! Insira novos dados na tabela users para ver o resultado.

Referências
Nos arquivos de documentação dos pacotes

ruby1.8
postgresql-8.1
postgresql-8.1-plruby
postgresql-contrib-8.1

Estava desenvolvendo um sistema de distribuição de dados no Postgresql e tive a necessidade de armazenar em um arquivo as inserções caso houvesse erro. Relatarei aqui uma solução desenvolvida um módulo(zinho) em C.

Pacotes necessários para a compilação
Na verdade eu não sei bem se os outros são necessários mais o postgresql-server-dev-8.1 é certeza

postgresql-8.1
postgresql-client-8.1
postgresql-contrib-8.1
postgresql-server-dev-8.1

Função

#include <stdio.h>
#include "postgres.h"
#include <string.h>
#include "fmgr.h"

//Registrando a função log_pg no escopo do postgres
PG_FUNCTION_INFO_V1(log_pg);

//Datum é o tipo esperado, cast automático do int de retorno
Datum log_pg(PG_FUNCTION_ARGS){

//PG_GETARG_TEXT_P(0) pega o primeiro argumento
//VARDATA faz cast para char *
  char *filename = VARDATA(PG_GETARG_TEXT_P(0));

  FILE *fp;

  if ((fp = fopen(filename, "a"))==NULL)  {
    printf("Cannot open file \n");
    return 1;
  }else{
   //Pega o segundo argumento já convertido para char *
    fputs(VARDATA(PG_GETARG_TEXT_P(1)),fp);
    fclose(fp);
    return 0;
   }
}

Salve o arquivo como log_pg.c
Compile

gcc -fpic -I/usr/include/postgresql -I/usr/include/postgresql/8.1/server -c log_pg.c

Gere o Shared Object

gcc -shared -o log_pg.so log_pg.o

No meu caso a dynamic_library_path está apontando para /usr/lib/postgresql/8.1/lib/ portanto, o log_pg.so vai pra lá.

install log_pg.so  /usr/lib/postgresql/8.1/lib/log_pg.so

Agora no Postgresql duas funções, uma mapeia o log_pg.so em log_me

CREATE or replace FUNCTION log_me(varchar,varchar) RETURNS int
 AS 'log_pg.so', 'log_pg' LANGUAGE C;

e a outra uma abstração para gerar a data corrente e concatena .log ao fim do nome do arquivo (melhor que usar strcpy).

create or replace function logger(varchar,varchar) returns int as $BODY$

  begin
    return log_me(($1 || current_date::varchar ||'.log')::text,$2::text);
  end
$BODY$ language 'plpgsql';

Testando

select logger('/tmp/teste', 'meu primeiro teste');

Saiba mais … libpq - C Library

Tenho uma função em PL/pgSQL que identifica a operadora pelo número e DDD denominada ‘operadora’

CREATE OR REPLACE FUNCTION operadora(text) RETURNS text AS '
declare
  ops text[]:= array['VIVO','CLARO', 'TIM','OI','Amazônia','Telemig','BRT','CTBC','Sercomtel','Nextel'];
  regexps text[]=array[
  '((2[1-9]95|1[1-9](95|7[1-4]))|[49][1-9]9[1-4]|([25-7][1-9]9[6-9]|1[1-9]9[6-8]))[0-9]{6}$',
  '([12568][1-9]9[1-4]|4[1-9]88|[37][1-9]8[124]|6195|1[1-9](76|89))[0-9]{6}$',
  '(((([48][1-9]9[6-9])|([37][1-9]9[1-4])|(1[1-9]8[1-5]|[56][1-9]81)|([29][1-9]8[1-3])|(4381[0-9])|(1[1-9]86))[0-9]{6})|5[1-5]99(11|13|39|8[1-9])[0-9]{4})$',
  '[237-9][1-9]8[6-8][0-9]{6}$',
  '9[1-9]9[6-9][0-9]{6}$',
  '3[1-8]9[6-9][0-9]{6}$',
  '[4-6][1-9]84[0-9]{6}$',
  '((3[1-8](96[0-9]{6}|99[67][0-9]{5}|999[1-9][0-9]{4}))|(1[1-9]99[0-9]{6}))$',
  '4[1-6]99(4[1-6]|9[1-8])[0-9]{4}$',
  '7[0-9]{7}$'
];
begin
  for i in 1..10 loop
    if $1 ~ regexps[i] then --se match retorna a posiç
	return ops[i];
    end if;
  end loop;
    return 'outros';
end
' LANGUAGE 'plpgsql' VOLATILE;

Isso funciona da seguinte forma, para cada operadora em ops temos uma expressão regular em regexps e um loop que faz match para todas as regexps.

Exemplo, a fração que analisa a operadora VIVO no centro oeste esta na regexps[0] em negrito(ou morenito, como estão dizendo isso por ai!)

….([25-7][1-9]9[6-9]|1[1-9]9[6-8]))[0-9]{6}$

As regras para determinar as operadoras são:

Trunking com a Nextel utiliza a série começada com 7. A Vivo em São Paulo está também utilizando a série 7 (71 a 74). A série 95 está sendo utilizada pela Vivo em São Paulo e Rio de Janeiro e pela Claro no Distrito Federal. (Marcado com * na tabela a seguir)

Operadora por área e Banda

Estado (DDD)	Banda A (96 a 99)	Banda B (91 a 94)	Banda D (8)	Banda E (8)
RJ, ES (21 a 28)	Vivo	Claro	Oi 86 a 88	TIM 81 a 83
Amazônia (91 a 99)	Amazônia Celular	Vivo
MG (31 a 38)	Telemig Celular	TIM		Claro 84
BA, SE (71 a 79)	Vivo	TIM		Claro 81
Nordeste (81 a 89)	TIM	Claro		-
PR, SC (41 a 49)	TIM	Vivo	Claro 88	Brasil Telecom 84
RS (51 a 55)	Vivo	Claro	TIM 81
Centro Oeste (61 a 69)
SP (11 a 19)	Vivo*	Claro*	TIM 81-85	-

Casos Especiais de Operadoras (sempre tem

Operadora	Cidades
CTBC Celular	Minas Gerais (96, 9960-9979 e 9991-9999) São Paulo(99)
Sercomtel Cel.	Londrina e Tamarana, PR (9941-9946 e 9991-9998)
TIM	Pelotas e região RGS. (9911, 9913, 9939, 9981-9989) Londrina e Tamarana, PR, área 43 (81) São Paulo (86)
Claro	São Paulo (76 e 89)
Vivo	São Paulo (71, 72, 73 e 74)

Para facilitar o desenvolvimento das expressões, reduzi cada regra de cada localidade de cada operadora em ‘mini-expressões’, assim:

2[1-9]95
1[1-9](95|7[1-4])
[49][1-9]9[1-4]
[25-7][1-9]9[6-9]
1[1-9]9[6-8]
....

Depois juntei tudo colocando um ou entre cada mini-expressão.

Funcionar funciona… mas como sempre teremos novas exceções. Um dos casos seria a utilização de Trunking ou banda de outra operadora ou uma norma da Anatel. Portanto se uma operadora faz parceria/pacto/etc com outra operadora teremos que alterar a expressão relativa a operadora, uma tarefa não muito agradável como essa da expressão da TIM.

(((([48][1-9]9[6-9])|([37][1-9]9[1-4])|(1[1-9]8[1-5]|[56][1-9]81)|([29][1-9]8[1-3])|(4381[0-9])|(1[1-9]86))[0-9]{6})|5[1-5]99(11|13|39|8[1-9])[0-9]{4})$

Dividir novamente e depois juntar tudo … em uma masturbação codificante!? … era isso que eu fazia :|.

Como sofremos por falta de informações ou preguiça de pesquisar!

Pesquisando(com preguiça de quebrar a expressão) encontrei o kregexpeditor, que eu ignorava por começar com K de KDE, que ao longo do tempo-linux esta acabando com o meu preconceito KDE/Qt.

Dei um apt-get e instalei o cara.

Puts! Tenho agora um editor gráfico de expressoes regulares!
Posso trocar isso

(((([48][1-9]9[6-9])|([37][1-9]9[1-4])|(1[1-9]8[1-5]|[56][1-9]81)|([29][1-9]8[1-3])|(4381[0-9])|(1[1-9]86))[0-9]{6})|5[1-5]99(11|13|39|8[1-9])[0-9]{4})$

por algo mais amigável como

Com o kregexpeditor vc pode abrir um arquivo texto pra fazer o match , criar e alterar expressões regulares em texto ou no modo gráfico.

Gostei muito do editor, tenho que rever meus conceitos sobre K*