segunda-feira, 30 de setembro de 2013

Criando Ranks no Postgres usando Window Functions

Vamos supor que você tenha a seguinte tabela:

E você tenha que ordená-la de acordo com os seguintes critérios: a maior média por departamento, seguido de maior nota a, maior nota b e finalmente maior nota c. Fica bem simples se usarmos a cláusula order by:


SELECT department, name, grade_a, grade_b, grade_c
FROM candidates
ORDER BY department, ((grade_a + grade_b + grade_c) / 3) DESC,
grade_a DESC, grade_b DESC, grade_c DESC


Resultado:


Mas agora queremos criar uma qualificação indicando a posição do candidato de acordo com os critérios acima e esta posição deve ser relativa ao departamento que ele pertence, ou seja queremos saber os trê melhores qualificados no departamento "Development" e os três melhores no departamento "Marketing". Talvez muitos já estejam pensando em usar sua linguagem de programação favorita para resolver este problema mas no Postgres temos as Window Functions:


SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates


Resultado:

Legal, agora temos um número indicando a posição de cada candidato relativo ao seu departamento. Mas digamos agora que apenas os dois melhores candidatos de cada departamento devem ser retornados. Neste caso a seguinte query deve ser satisfatória:


SELECT *
FROM
(SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates) AS sub_query
WHERE rank < 3


Resultado:

Espero que estes exemplos possam te ajudar a entender como as Window Functions funcionam e como elas podem te ajudar.

Referência:http://postgresguide.com/tips/window.html

sábado, 31 de agosto de 2013

GROUP_CONCAT no Postgres

O MySQL tem uma função muito legal chamada GROUP_CONCAT. Ela concatena os valores de uma coluna em uma string separados por um separador que por padrão é uma vírgula(,). Para exemplificar considere a seguinte estrutura no banco de dados(pode ser usada tanto no MySQL quanto no Postgres):

create table countries_languages(
country_code varchar(4) not null,
language varchar(32) not null,
primary key(country_code, language)
);

insert into countries_languages(country_code, language) values('BR', 'Portuguese');
insert into countries_languages(country_code, language) values('THA', 'Chinese');
insert into countries_languages(country_code, language) values('THA', 'Khmer');
insert into countries_languages(country_code, language) values('THA', 'Kuy');
insert into countries_languages(country_code, language) values('THA', 'Lao');


Ao executarmos a seguinte consulta:

select country_code, language as languages
from countries_languages
where country_code = 'THA'


Obteremos a seguinte saída:
THA Chinese
THA Khmer
THA Kuy
THA Lao

Usando o GROUP_CONCAT nesta outra consulta:

select country_code, GROUP_CONCAT(language) as languages
from countries_languages
where country_code = 'THA'
GROUP BY country_code


A saída será:
THA Chinese,Khmer,Kuy,Lao


Certo! Bem legal e útil quando quisermos exportar dados ou apresentá-los, mas como podemos fazer isso no banco de dados Postgres?

No Postgres não existe a função GROUP_CONCAT mas nele temos arrays. Uma forma de ter a mesma saída que o MySQL usando arrays em Postgres é:

select country_code, array_to_string(array(SELECT language FROM countries_languages WHERE country_code = 'THA'), ',')
from countries_languages
where country_code = 'THA'
GROUP BY country_code


O resultado da sub-consulta "SELECT language FROM countries_languages WHERE country_code = 'THA'" é convertido para um array que por sua vez é convertido para uma string pela função array_to_string com cada valor do array separado por uma vírgula. É mais verboso e exige uma sub-query mas o resultado é o mesmo.

Referência:http://stackoverflow.com/a/4469002

quarta-feira, 31 de julho de 2013

Truques SQL

Inserindo a partir de uma consulta SQL

Supondo que temos a seguinte estrutura de tabelas:
CREATE TABLE users(
id int not null,
name text not null,
email text
);

CREATE TABLE user_searches_results(
id int not null,
id_user int not null,
search_date date
);

Agora como nós podemos guardar todos os id's da tabela users na tabela user_searches_results retornados na seguinte consulta: SELECT id FROM users WHERE name LIKE 'Maria%' ?
Este seguinte comando sql é capaz de fazer a tarefa de forma muito eficiente:
INSERT INTO user_searches_results(id, id_user, search_date)
SELECT 1, id, now() FROM users WHERE name LIKE 'Maria%';
Simples não?

Retornando um registro aleatório a partir de uma consulta

MySQL

SELECT column1, column2, ... FROM table ORDER BY rand() LIMIT 1

PostgreSQL

SELECT column1, column2, ... FROM table ORDER BY random() LIMIT 1

SQL Server

SELECT TOP 1 column1, column2, ... FROM table
ORDER BY NEWID()

DB2

SELECT column1, column2, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Oracle

SELECT column1 FROM
( SELECT column1 FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Selecionar o segundo maior(ou menor valor) de uma coluna

SELECT MAX(column)
  FROM table
 WHERE column < (SELECT MAX(column) FROM table )

Use MIN se você quiser o segundo menor valor e não se esqueça do operador >.

Copiar a estrutura de uma tabela sem copiar os dados(MySQL, PostgresSQL)

CREATE TABLE users_2 AS (SELECT * FROM users WHERE 1=0);
Observe que índices e constraints não são duplicados.

Gerar SQL para atualizar os dados de uma tabela baseado no conteúdo de outra tabela(PostgresSQL)

Considerem a seguinte estrutura e dados:
CREATE TABLE departments(
id int not null,
name text not null,
employee_count int
);

INSERT INTO departments values(1, 'Development', 0);
INSERT INTO departments values(2, 'Sales', 0);
INSERT INTO departments values(3, 'Marketing', 0);

CREATE TABLE employees(
id int not null,
id_department int not null,
name text not null
);

INSERT INTO employees VALUES(1, 2, 'Ryu');
INSERT INTO employees VALUES(2, 2, 'Ken');
INSERT INTO employees VALUES(3, 3, 'Chun-Li');
INSERT INTO employees VALUES(4, 1, 'Guile');

Certo. Agora precisamos atualizar a tabela departments com o número de empregados que cada uma contém. Com o seguinte sql os comandos de update serão gerados automaticamente:
SELECT 'UPDATE departments SET employee_count = ' || COUNT(e.id) 
  || ' WHERE id = ' || e.id_department || ';'
FROM employees e
GROUP BY e.id_department;

A linguagem SQL pode ser mais poderosa e útil do você imagina.

domingo, 30 de junho de 2013

Locale e como isso pode afetar o retorno da função iconv

Execute o trecho de código abaixo na linha de comando o observe o resultado:

ini_set('display_errors', 1);
error_reporting(E_ALL);

header('Content-Type: text/plain; charset=utf-8');
$utf8_word = 'GOIÂNIA';
$ascii_word = iconv('UTF-8', 'US-ASCII//TRANSLIT', $utf8_word);
echo $ascii_word, "\n";
echo md5($ascii_word);
echo "\n";


No meu sistema(PHP 5.3.6-13ubuntu3.10 with Suhosin-Patch (cli)) a saída foi:

GOIANIA
1d92cfdaed61eba2c8dea6e670df9f38

Certo, agora executando o mesmo script através do browser e do servidor web a saída é a seguinte:

GOI?NIA
848aa917b7d132a581c0d9f91bc1b03f

Humm... Por que o mesmo script tem saídas diferentes quando executado na linha de comando e quando executado no servidor web?

Mas o que este script faz mesmo? Ele simplesmente usa a função iconv para converter uma string codificada com UTF-8(GOIÂNIA) para codificação US-ASCII, o nosso velho conhecido ASCII, usando transliteração o que faz com que quando um caractere não possa ser representado na codificação alvo a função tenta usar um caractere da codificação alvo que seja parecido. Mas ainda não sabemos poque o script tem saídas diferentes em ambientes diferentes.

Acontece que a função iconv é afetada pelo locale do ambiente, usando a função setlocale podemos verificar qual locale está sendo usado:

$current_locale = setlocale(LC_ALL, "0");
echo "Current locale:$current_locale", "\n";


No servidor web a resposta é "C" na linha de comando é "LC_CTYPE=pt_BR.UTF-8;LC_NUMERIC=C;LC_TIME=C;LC_COLLATE=C;LC_MONETARY=C;LC_MESSAGES=C;LC_PAPER=C;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=C;LC_IDENTIFICATION=C", que é o locale configurado no meu sistema operacional. Ou seja enquando no servidor web o locale é C na linha de comando é utf-8. Então é só ajustarmos o locale antes de chamar a função iconv para que ela tenha o mesmo comportamento em qualquer ambiente.

ini_set('display_errors', 1);
error_reporting(E_ALL);

setlocale(LC_ALL, 'pt_BR.UTF-8');
header('Content-Type: text/plain; charset=utf-8');
$utf8_word = 'GOIÂNIA';
$ascii_word = iconv('UTF-8', 'US-ASCII//TRANSLIT', $utf8_word);
echo $ascii_word, "\n";
echo md5($ascii_word);
echo "\n";


Mas existem algumas considerações a serem feitas, o locale configurado através da função setlocale deve estar instalado no servidor onde o código será executado, caso contrário a função setlocale simplesmente retorna FALSE e o resultado pode ser diferente para cada ambiente quando demonstrado no início do texto. Outra consideração é que o efeito da função setlocale é "por processo". Na documentação da função existe um "warning"  explicando que quando existirem vários scripts rodando em diferentes threads no servidor(algo muito comum se seu servidor tem várias requisições ao mesmo tempo) uma chamada a setlocale para mudar o locale vai afetar todos os scripts em execução no momento mesmo que o script em si não tenha chamado setlocale.

Espero que este texto possa ajudar as pessoas pegas de surpresa pelos resultados da função iconv. Até a próxima.

Referências:
http://stackoverflow.com/questions/9771886/what-factors-influence-a-successful-iconv-translit-conversion

quinta-feira, 30 de maio de 2013

phpsh um REPL para PHP

Vocês sabem o que a sigla REPL significa? Ela significa read-eval-print-loop. Ela é usada para denominar um abiente shell de programação interativa. É como um programa de linha de comando onde você pudesse executar os comandos de sua linguagem de programação favorita e observar o que cada comando faz imediatamente depois de apertar Enter. Diversas linguagens de programação possuem seus REPL's como Python, Ruby, Perl, Scala, Groovy, Forth, Lisp, Javascript etc. Aliás é bem provável que você que está lendo este texto agora já tenha um ambiente REPL para Javascript instalado e funcional, afinal, os browsers mais usados como Firefox, Chrome e Iternet Explorer possuem interpretadores Javascript. Aperte F12 no Chrome e Internet Explorer ou Ctrl + Shift + K no firefox e procure pela aba console.

O PHP também já vem com um REPL instalado. Se você executar o seguinte comando:
php -a
Verá que ele irá abrir o "Interative Shell". Mas infelizmente ele é muito limitado, no meu caso por exemplo aparece um til(~) cada vez que aperto a tecla Delete e o autoloading não está disponível. E no windows eu diria que ele simplesmente não funciona.

O pessoal do Facebook criou um REPL bem legal para PHP. Chamdo phpsh ele é implementado na maior parte em Python(pecado mortal!). Mas enfim, ele tem recursos legais como autoloading, opção para carregar código já existente e a tecla Delete funciona!

O endereço com instruções para instalá-lo está aqui. Para baixá-lo eu simplesmente clonei o repositório usando o git, mas você também pode obtê-lo através de um arquivo zip na mesma página.

Existem alguns módulos PHP que precisam estar instalados, são eles: pcre, posix e tokenizer. No caso do Python também existem algumas dependências. No meu caso tive que instalar o easy_install para instalar o pacote readline. Para instalar o easy_install tive que instalar o pacote python-setuptools. Depois de instalar o setuptools basta usar o comando para instalar o readline:
sudo easy_install readline
E depois segui as instruções para instalar globalmente:
cd phpsh (precisamos entrar no diretório raiz do phpsh depois de baixá-lo)

python setup.py build
sudo python setup.py install
phpsh

E pronto! Você já pode escrever código PHP e apertar enter para ver o resultado. Eu particularmente acho muito úteis estes ambientes REPL. Quando eu quero ver o que uma função faz ou simplesmente testar algumas poucas linhas de código é muito fácil abrir o terminal e já começar a escrever o código e poucos momentos depois já ver o resultado. Espero que isto ajude vocês tanto quanto me ajuda.

terça-feira, 30 de abril de 2013

Estimando o valor de pi usando um quadrado, um círculo e probabilidade


Existe uma técnica bem legal de se estimar o valor da constante π usando o quadrado e círculo abaixo:


O valor do raio do círculo é 1. Assim a área do círculo é π * r ^ 2 = π * (1 * 1) = π.
E a área do quadrado é :2 ^ 2 = 2 * 2 = 4. Só lembrando que estou usando o símbolo ^ para representar a exponenciação.

E agora nós selecionamos milhares de pontos dentro do quadrado. Para uma boa aproximação devemos fazer isso milhões de vezes. Uma vez isso feito você pode usar a seguinte fórmula para encontrar o valor de π:
π/4 = número de pontos dentro do círculo / número total de pontos

modificando a fórmula para encontrar o valor de π:
π = (número de pontos dentro do círculo * 4) / número total de pontos

Considerando-se que para obtermos um bom resultado devemos escolher milhões de pontos, seria muito demorado e entediante fazer este processo manualmente, portanto, nada melhor do que usarmos um computador para isso. E o programa em PHP abaixo faz exatamente isso:



Acredito que não seja difícil entender o código. Neste exemplo selecionamos 5 milhões de pontos dentro do quadrado. A inspiração veio deste link, lá você também pode encontrar uma boa explicação de como a técnica funciona. Ou você pode usar a constante M_PI da linguagem PHP.

quarta-feira, 6 de março de 2013

Eficiência Matemática

Vou mostrar-lhes dois trechos de código que fazem exatamente a mesma coisa:






Analisem os trechos de código e tentem descobrir o que eles fazem. Uma dica: a essência de tudo está na função calc.


Já analisou? Entendeu o que está acontecendo? Bom, estes trechos de código calculam 1 milhão de vezes a soma do intervalo de números entre 1 e um número aleatório entre 1 e 500. Ao mesmo tempo ele toma nota de quanto tempo este processo leva usando a função microtime.


Você conseguiu descobrir o que estava acontecendo antes de eu ter explicado? Se você não conseguiu, não tem problema, eu vou explicar. Comparando os dois trechos de código, qual você diria que apresenta com mais clareza o comportamento que expliquei anteriormente?


O primeiro trecho usa funções cujos nomes dão uma certa ideia sobre o que está acontecendo. "array_sum" retorna a soma dos valores dentro de um array. "range" retorna um array preenchido com números em um intervalo que vai do primeiro parâmetro passado até o segundo parâmetro passado. Então array_sum soma o conteúdo de um array, range retorna um array de números, bingo! Tudo o que o loop faz é somar os números naturais de 1 até um intervalo superior que varia de 1 até 500. Um milhão de vezes!


E o segundo trecho? Não há chamadas de funções nesta versão da função calc. Ela mais parece uma fórmula matemática. E realmente é, é a seguinte fórmula:
E ela simplesmente retorna a soma entre os números naturais de 1 até n.

Certo! O que eu quero demonstrar com tudo isso? Primeiro eu convido vocês a executar os trechos de código em seus computadores e comparar o tempo gasto nos cálculos tanto pelo trecho usando array_sum quanto pelo outro usando a fórmula matemática, no meu computador um core i7 2600 3,40 GHz os tempos em segundos, cada trecho foi executado três vezes, foram os seguintes:

array_sum:
  • 42,979010105133
  • 42,527684926987
  • 43,008407831192
fórmula matemática:
  • 9,8508331775665
  • 9,889456987381
  • 9,9301979541779
Não é um benchmark rigoroso ou bem feito, mas como vocês podem ver a diferença de tempo entre as duas abordagens rodando no meu computador é enorme. E acredito que aqueles que testarem o código também observarão grandes diferenças de tempo de execução dos dois trechos.

Eu acredito que a grande diferença de eficiência de execução se deve principalmente a dois pontos principais. A função range cria um array dinamicamente e isso envolve alocação de memória por parte do interpretador PHP, o que toma tempo. Mas é o segundo ponto que deve realmente impactar na performance, a função array_sum deve percorrer cada elemento do array gerado pela função range e laços de repetição afetam a performance significativamente.

Eu não tenho dúvidas de que o código usando array_sum é mais fácil de se ler, mais elegante e usa todo o poder do dinamismo da linguagem PHP. Mas a eficiência da abordagem matemática é inegável, e alguns até poderiam dizer que ela é tão elegante quanto a outra, sempre podemos adicionar um comentário informando o que a fórmula faz, não é? Acho que preciso voltar a estudar matemática.

terça-feira, 19 de fevereiro de 2013

Como testar o desempenho de suas aplicações web de forma fácil e rápida

Você acabou de finalizar os últimos retoques no seu site ou aplicação. O cliente já fez exaustivos testes além dos testes realizados por você mesmo. Tudo pronto para colocar em produção aquele código no qual você passou meses trabalhando. E de repente, quando mais de 30 usuários estão fazendo requisições ao mesmo tempo, as requisições demoram a serem respondidas. Parece que a página inicial do site não vai aparecer nunca. Clicar em "salvar" naque formulário de cadastro se torna uma bela desculpa para uma pausa para café.

"Mas como isso está acontecendo?" Você se pergunta. Nos testes realizados as resquisições demoravam poucas dezenas de milisegundos. Como isso foi acontecer? Bom, provavelmente os testes foram realizados com apenas um usuário acessando o servidor, talvez dois. Com o site ou aplicação se tornando popular vários usuários vão fazer seu código ser executado em várias requisições ao mesmo tempo. E é neste momento que estas supresas aparecem.

Para te ajudar a tentar prever como seu código vai se comportar com várias requisições simultâneas existe uma ferramenta muito útil chamada ab (Apache Benchmark), sobre a qual já falei aqui. Em uma máquina com gerenciador de pacotes como o apt-get instalá-la é muito fácil. Basta usar o comando:
#apt-get install apache2-utils

Sua execução básica é assim:
$ab -c 10 -t 30 http://localhost/sitecobaia/

Este comando vai abrir 10 conexões simultâneas e enviar requisições durante 30 segundos para o endereço indicado. Uma variação deste comando é adicionar um parâmetro para que as conexões sejam do tipo keep-alive(comum nas as requisições enviadas por navegadores).
$ab -kc 10 -t 30 http://localhost/sitecobaia/

Quanto aos números você é livre para alterá-los como quiser. Se você for realmente sádico coloque algo como 100 requisições simultâneas durante 60 segundos e veja seu servidor sofrer.

Um exemplo de saída ao comando ab pode ser visto abaixo:

This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking localhost (be patient)
Finished 484 requests


Server Software:        Apache/2.2.20
Server Hostname:        localhost
Server Port:            80

Document Path:          /sitecobaia/
Document Length:        43666 bytes

Concurrency Level:      10
Time taken for tests:   30.022 seconds
Complete requests:      484
Failed requests:        0
Write errors:           0
Keep-Alive requests:    0
Total transferred:      21502202 bytes
HTML transferred:       21134344 bytes
Requests per second:    16.12 [#/sec] (mean)
Time per request:       620.291 [ms] (mean)
Time per request:       62.029 [ms] (mean, across all concurrent requests)
Transfer rate:          699.43 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.2      0       2
Processing:   349  614 108.0    608     950
Waiting:      344  598 105.6    591     932
Total:        349  614 108.0    608     950

Percentage of the requests served within a certain time (ms)
  50%    608
  66%    658
  75%    688
  80%    707
  90%    750
  95%    798
  98%    845
  99%    875
 100%    950 (longest request)


Alguns números mostrados são interessantes. Como por exemplo a média de requisições por segundo(Requests per second) que neste caso foi de 16,12. Quanto mais alto este número mais requisições seu servidor servirá em menos tempo. A média de tempo(Time per request) por requisição também é interessante de ser observada. Neste caso temos dois valores, o primeiro, indica o tempo médio em milisegundos que o servidor demorou para responder uma única requisição, neste caso 620,291 ms. Ou seja com 10 conexões concorrentes a resposta demourou em média pouco mais de meio segundo para chegar(1 segundo tem 1000 milisegundos). O segundo valor da média de tempo por requisição é o resultado da divisão do tempo total para todas as requisições(30,22 segundos ou 30022 milisegundos) pelo número de requisições completas (484) o que dá o valor de 62,029 milisegundos por requisição.

No final temos um resumo da porcentagem de requsições respondidas dentro de um certo tempo em milisegundos. Observem que 50% das requisições demoraram no máximo 608 ms para serem respondidas. E a requisição mais demorada demorou 950 ms.

Um resultado que pode deixar-nos confusos quando usamos o ab com sites dinâmicos é o seguinte:

Failed requests:        32951
   (Connect: 0, Receive: 0, Length: 32951, Exceptions: 0)


Você pensa: "Porque ele reportou que 32951 requisições falharam?". Mas observe que o tipo de erro foi de tamanho(Length), isso acontece porque o ab considera que quando os tamanhos das respostas diferem da primeira resposta obtida do servidor acontece um erro de Length. Como em páginas dinâmicas coisas como identificadores de sessão, cookies ou até mesmo o conteúdo da resposta podem mudar de uma requisição para outra este tipo de "erro" pode aparecer frequentemente.

segunda-feira, 28 de janeiro de 2013

Código fonte de jogo flash opensource

Olá a todos.
Acabei de criar um repositório no github com o código fonte de um pequeno jogo que criei usando Action Script 3 e a biblioteca flixel. O nome do jogo é DarkMatters.

O jogo é bem simples e criei com a intenção de aprender mais sobre o processo de desenvolvimento de um jogo. Infelizmente o código não é de grande qualidade mas espero que possa ajudar alguém interessado no assunto.

terça-feira, 22 de janeiro de 2013