Python: validação de email

Olá,

hoje seguimos para o exercício 2 em Python, desta vez para validação de email.

Exercício 2

#Escreva uma função que retorne True se uma string for um endereço de e-mail e False caso contrário.
Nota: Para efeitos deste exercício, considere que um endereço de e-mail:

  • contém exactamente uma arroba (@);
  • contém apenas letras minúsculas antes da arroba;
  • contém apenas um ponto, e este está a seguir à arroba;
  • não contém caracteres além de letras minúsculas, pontos e arrobas.

Resolução:

P_ex2

No início da função é criada uma lista de letras minúsculas, de “a” a “z”. Esta lista contém todos os elementos permitidos na string do email com excepção do símbolo do arroba (“@”) e do ponto (“.”).

Assim, criando outra lista com os elementos que estejam na string inicial e não estejam na lista de letras minúsculas criada no passo 1, obteremos este valor para um email correcto:

[‘@’,’.’]

Repare-se que no caso de existirem mais que um símbolo arroba, ou mais que um ponto, ou um ponto antes do arroba, ou outros símbolos e números, esta lista resultante será diferente.

Assim, garante-se que para o email seguir as regras implícitas no problema basta que a lista resultante da comparação seja de facto [‘@’,’.’].

O método utilizado em python para a criação da lista com letras minúsculas assim como para a lista resultante da comparação das duas listas é a “list comprehension”.

Na segunda parte da função verifica-se, portanto, que se a lista resultante for a descrita anteriormente a função deverá retornar True (email correcto), caso contrário retornará False.

Repare-se, por exemplo que se se tentar executar a função com o valor da string assim:

"joao.Ratao33 at carochinha.pt"

a função retornará False.

Até ao próximo exercício,

JG

Python: Moda estatística

Olá,

hoje irei iniciar a resolução e divulgação de alguns exercícios em Python. Python é uma linguagem bastante antiga mas cada vez mais utilizada, muito devido à aplicação em Data Science com boas bibliotecas para trabalhar e visualizar dados, implementar modelos e previsões…mas antes de enveredar por esse ramo, quero deixar aqui alguns exercícios simples e sua resolução.

#Exercício 1

Escreva uma função que encontre a moda estatística de uma lista de números inteiros.

Resolução

A moda de qualquer conjunto de elementos é o maior valor dependente do número de vezes que cada elemento existe no conjunto. Como exemplo o conjunto (lista de números inteiros):

l = [1,2,3,2,2,6,5,9,2,22,22,22,22,22,22,22,6,6,6,6,6,6,6,6,6,6,-2,-2,-2,-2,-2,-4,0]

A moda será seis (6), é o número que aparece mais vezes no conjunto (11 vezes).

Posto isto, vamos ao código Python para a resolução do enunciado.

P_ex5

Na primeira parte da função, é gerado um dicionário, inicialmente vazio, que será preenchido por cada elemento da lista (chave) e seu valor será o total de vezes que aparece nessa mesma lista.

Na segunda parte da função, é encontrado o valor máximo existente no dicionário e sua respectiva chave; esta representa o valor da Moda.

Vejamos o resultado da execução da função f(l) para a lista l:

P_ex5_

Até ao próximo exercício.

JG

 

Utilização de Triggers no SQL Server

Olá,

há quase duas décadas que analiso, crio e programo sistemas de informação e controlo em tecnologia web (ASP – vbscript, javascript, html, css, ajax, jquery) e a minha particularidade é que também faço a ligação com as bases de dados (SQL) bem como todo o desenvolvimento necessário em TSQL…bem, com isto quero dizer que por vezes nem me apercebo da vantagem de fazer tudo isto pois existem programadores que quando o assunto são os dados apontam para os DBAs.

Como o meu desenvolvimento abrange toda a linha, dou comigo muitas vezes a criar na própria aplicação sistemas de actualização de dados após alteração de outros. Por vezes, num código TSQL coloco updates numa determinada tabela e logo de seguida faço inserts noutra, tudo no mesmo ciclo de código SQL. Para mim é óbvio e simples esta manipulação dos dados. Mas… e se eu não soubesse TSQL? E se eu não quisesse colocar código deste na aplicação (independentemente se é código na própria página ASP ou em stored procedures no server…)?

Este artigo vem ajudar com a utilização de Triggers no SQL, permitindo ao programador abandonar determinadas acções de manipulação de dados passando-as para o DBA, além de não sobrecarregar tanto a aplicação (este processo passa a ser do servidor SQL e não do IIS).

Trigger

O Trigger (gatilho) é uma espécie de stored procedure que é despoletado após determinada acção (numa tabela por exemplo). Geralmente essas acções que disparam os Triggers são alterações nas tabelas por meio de operações de inserção, exclusão e actualização de dados (insert, delete e update).

Basicamente existem três tipos de triggers: DML, DDL ou Logon trigger.

DML triggers disparam quando o utilizador tenta modificar dados através de eventos de Data Manipulation Language (DML). Os eventos DML são códigos INSERT, UPDATE, ou DELETE sobre uma tabela ou view.

DDL triggers disparam em resposta a uma variedade de eventos de Data Definition Language (DDL). Este eventos correspondem primariamente ao código TSQL CREATE, ALTER e DROP.

Logon triggers disparam em resposta ao evento de LOGON que aparece quando o utilizador estabelece a sua sessão no SQL.

Mais informação em https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017.

A sintaxe para criação de um trigger é a seguinte:

CREATE TRIGGER [NOME DO TRIGGER]
ON [NOME DA TABELA]
[FOR/AFTER/INSTEAD OF] [INSERT/UPDATE/DELETE]
AS
    --CORPO DO TRIGGER

Os parâmetros são:

  • NOME DO TRIGGER: nome que identificará o gatilho como objecto da base de dados. Deve seguir as regras básicas de nomenclatura de objectos.
  • NOME DA TABELA: tabela à qual o gatilho estará ligado, para ser disparado mediante acções de insert, update ou delete.
  • FOR/AFTER/INSTEAD OF: uma dessas opções deve ser escolhida para definir o momento em que o trigger será disparado.
    FOR é o valor padrão e faz com que o gatilho seja disparado junto da acção.
    AFTER faz com que o disparo se dê somente após a acção que o gerou ser concluída. INSTEAD OF faz com que o trigger seja executado no lugar da acção que o gerou.
  • INSERT/UPDATE/DELETE: uma ou várias dessas opções (separadas por vírgula) devem ser indicadas para informar o SQL qual é a acção que disparará o gatilho. Por exemplo, se o trigger deve ser disparado após toda inserção, deve-se utilizar AFTER INSERT.

Mas vejamos um caso prático para se perceber melhor toda esta sintaxe.

Exemplo prático de utilização de Triggers

Para exemplificar esta temática, utilizemos um cenário de inserções de registos de novos clientes numa tabela e obrigatoriedade de lançar alertas – optei por lançar um alerta sobre o aniversário de cada novo cliente mas poderia bem ser para alertar a direcção de marketing de nova subscrição.

Após a inserção de um novo cliente na tabela dos clientes é necessário iniciar o trigger para inserção de alertas de aniversário (neste ex.) numa outra tabela – para poder ser apresentada em qualquer interface, ou poder ser enviada alguma mensagem por email.

Iniciemos por criar a tabela de clientes: TblClientes

Esta tabela contém diversos campos como o número de cliente, nome, morada, contacto, data de nascimento, etc.

trigger1

Vamos criar agora a tabela de alertas: AVISOANIVERSARIO

Esta tabela contém os campos relacionados com o o cliente bem como a mensagem (em descritivo).

trigger2

O programador pode criar um interface para o utilizador inserir novos clientes, preenchendo os campos especificados anteriormente. E será apenas isto que o programador terá com que se preocupar…o trabalho de registar noutra tabela dados sobre alertas de cada uma das anteriores inserções passa a ser automática após a criação do trigger necessário pelo DBA.

Assim, criemos o trigger para inserção de alertas automáticos na tabela AvisoAniversario após a inserção de um novo cliente na TblClientes:

trigger3

O trigger tem um nome com iniciais de trigger (TGR) e termina com inicial de inserção (I).

Nesse trigger utilizamos uma tabela temporária chamada INSERTED. Essa tabela existe somente dentro do trigger e possui apenas uma linha, contendo os dados do registo que acabou de ser incluído.
Assim, fazemos um select sobre a tabela Tblclientes e passamos o valores de suas colunas para as variáveis internas, @Numcliente, @NomeCompleto, @Telefone e @DataNascimento, que são utilizadas posteriormente para realizar o Insert na tabela de alertas.

Agora, se inserirmos registos na tabela Tblclientes,

trigger4

poderemos verificar que na tabela AvisoAniversario foram inseridas novas linhas com alertas para cada um destes clientes novos:

trigger5

Tudo isto de uma forma automática.

Poderemos, agora, criar um trigger para a função contrária, isto é, eliminar os registos de alertas de aniversário se algum cliente for eliminado da tabela dos clientes.

Será um trigger para o caso de Delete, e utilizará a tabela temporária DELETED.

O trigger tem um nome com iniciais de trigger e termina com inicial de delete (D).

trigger6

Vamos então eliminar um registo da tabela de clientes, pode ser o número de cliente 13 que é o Paulo Silva Cardoso:

trigger7

Verifiquemos agora a tabela dos alertas para comprovar que o registo com o número de cliente 13 desapareceu:

trigger8

Comprovado! Eliminado automaticamente.

Os triggers criados anteriormente podem ser visualizados graficamente no SQL Management Studio na tabela TblClientes:

trigger9

Espero que vos seja útil,

JG

Proteger os dados em SQL – hashing e encryption

Estou actualmente com um projecto de implementação de WordPress sob a plataforma Microsoft, mantendo a utilização do IIS de forma a responder a pedidos *.ASP e *.PHP.
Na continuidade do projecto, e já numa fase de desenvolvimento em WordPress, surgiu a necessidade de perceber como implementar o interface de autenticação dos utilizadores; não me refiro aos utilizadores do wordpress mas sim aos da aplicação que será criada em wordpress.

Esse sistema já está implementado há anos no nosso sistema em *.ASP, agora será criado em *.PHP. Mas o intuito deste artigo está na protecção em si mesma, não na criação de interfaces web.

A autenticação passa por especificar o utilizador e a password. Será necessário comparar este binómio com as credenciais existentes numa base de dados e verificar se é o mesmo utilizador.
A segurança reside mesmo aqui, na base de dados. A password não pode estar guardada conforme é inserida pelo utilizador, precisa estar “disfarçada” para que ninguém tenha acesso aos dados pessoais do indivíduo.

Quero notar que já vi muitas bases de dados com campos de password em pleno texto, com a verdadeira password. Muito grave! E agora, com o Regulamento Geral de Protecção de Dados (RGPD), além de grave é proibido. Não esquecer que uma grande percentagem de burlas devido ao acesso das passwords dos utilizadores acontece dentro de uma empresa, os ataques não surgem somente fora dela!..

Há duas formas de proteger os dados numa base de dados SQL – Hashing e Encryption.
Ambos utilizam algoritmos para “disfarçar” os dados; Encryption “disfarça” ou “cifra” (codifica) os dados de forma a que possam ser descodificados com uma determinada chave (dois sentidos); Hashing codifica os dados num só sentido, isto é, não existe forma de descodificar os mesmos para voltar ao valor inicial.

Assim de uma forma simplificada, o processo de Encryption lembra a antiga “linguagem dos pp”, aprendida na nossa infância.
Para quem não sabe, esta linguagem adiciona um “p” a cada sílaba repetindo-a (nas vogais adiciona o “p”, nas consoantes substitui). No final ficará uma frase enorme imperceptível para muitos.
Deixem-me exemplificar:

– frase: “Eu estou a escrever este artigo”
– frase encriptada: “Eupeu espestoupou apa espescrepreverper espestepe arpartipigopo”

Conseguem decifrar a frase?
Bem, e se souberem que a “chave” é o “p” juntamente com a regra das vogais e consoantes?
Assim conseguem repor a frase inicial. Encriptaram e decifraram com uma chave – é o método Encryption.

O método Hashing difere pois não existe chave alguma para desencriptar e regressar à frase inicial, uma vez criada a “codificação” não mais é possível regressar.

No nosso sistema informático, a nossa base de dados contempla o utilizador e sua password devidamente codificada (num campo binário). Quando o utilizador se autentica no interface é realizado um processo de comparação entre a password inserida, passando pelo método de Hashing, e a password guardada na base de dados; só poderá aceder se ambas forem iguais.

O SQL Server utiliza os algoritmos MD2, MD4, MD5, SHA, SHA1, SHA2_256 e SHA2_512. O nível de segurança destes algoritmos é da seguinte forma:

  • 128 bits (16 bytes) para MD2, MD4 e MD5
  • 160 bits (20 bytes) para SHA e SHA1
  • 256 bits (32 bytes) para SHA2_256
  • 512 bits (64 bytes) para SHA2_512

A codificação SHA2 foi introduzida a partir do SQL Server 2012.

Hashing é realizado, independentemente do algoritmo a utilizar, com a função de sistema SQL HASHBYTES:

HASHBYTES ( ”, { @input | ‘input’ } )
::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512  

A função HASHBYTES aceita dois parâmetros, o algoritmo e o valor que se pretende codificar.

O código abaixo mostra a codificação da palavra “password” nos diversos algoritmos, sendo apresentado também o seu tamanho em bytes e bits para melhor compreensão:

hash1

O resultado é o seguinte:

hash2

Uma encriptação de 512 bits será mais difícil de decifrar que uma de 128 (nunca digo que é impossível!, aliás já aconteceu com MD5…).

A técnica de Hashing altera substancialmente a codificação com uma pequena alteração na palavra inicial, daí a “impossibilidade” de regressar ao ponto inicial.

Deixem-me demonstrar a codificação da palavra “password” com duas pequenas alterações – password, password1, Password :

hash3

Repare-se na diferença de resultados apenas com uma pequena alteração!

Algumas notas são que a tabela a criar para guardar esta codificação deve contemplar o campo como “varbinary”; pode acontecer algum problema de quebra de dados quando o campo a codificar é demasiado grande (superior a 8000 bytes)…mas para o efeito deste artigo isso não é importante, importante é codificar os nossos dados pessoais preservando o máximo de segurança informática exterior e interior à empresa.

Espero que vos seja útil,

JG

SQL – Erro na conversão de dados varchar para numérico

Olá,

no decorrer de um projecto que envolve processos ETL de dados e posterior desenvolvimento aplicacional, deparei-me com um problema na utilização dos dados transformados.

O processo ETL lê determinados ficheiros *.csv, com dados exportados previamente na empresa X, e coloca-os em novas tabelas SQL na empresa Y. Existe, portanto, uma transformação inerente ao processo em que os dados deixam muitas vezes de serem datas ou valores para passarem a ser strings. Nem sempre na transformação de dados se tem atenção ao resultado final…

Comigo aconteceu, quando necessitei de trabalhar com os dados nas novas tabelas verifiquei que muitas das colunas que antes eram valores (float por ex.) passaram a estar como “varchar”; a resposta foi que tinha de se implementar rapidamente e “era colocar tudo como varchar”.

A pressa é inimiga da perfeição!

Acontece que temos de trabalhar com o que nos dão e, desta forma, há que resolver o problema.

Sendo o projecto de amostragem de custos de empresas, utiliza-se a tabela “Tbl_Custos”. Uma execução rápida de cinco registos da tabela, verifica-se na listagem o resultado do proprietário (aqui alterado manualmente) e o valor liquido dos custos.

c1

Mas e se agora for necessário criar um resultado total com todas os custos relacionados com o mesmo proprietário?

Bem, faça-se um somatório agrupado por proprietário…

c2

Quando se tentou realizar a operação, surgiu erro dizendo que o campo é “varchar” e, portanto, inválido para se efectuarem somatórios.

Um pouco mais de trabalho mas tudo bem, basta converter previamente o campo para numérico e ecfetuar de seguida o somatório…a conversão passa por utilizar a função “Convert” ou “Cast” do SQL…

c3

Ok, agora já é demais! Não posso converter um “varchar” para “float” por que motivo?!

Os processos de transformação de dados nem sempre são simples, muitas vezes deixamos ficar os valores default “varchar” no resultado e depois serão mais os problemas que as soluções…mas o programador que vem a seguir resolverá com certeza!

O que está a acontecer é que o SQL apenas converte para numérico a string que a ele lhe parecer numérico…ai, que confusão!

Vejamos a seguinte query com a verificação do estado numérico para o campo em questão:

c4

Se o registo for numérico aparece a frase “é numérico”, caso contrário aparecerá “não é numérico”.

Surpreendidos?

É por isso que o erro acontece, pela não possibilidade de converter para numérico uma string que parece numérica (mas não é).

Como resolver?

Com um pouco mais de trabalho, a solução passa por separar o valor pelos seus dígitos à esquerda da vírgula e concatenar com o ponto “.” e com as duas casas decimais à direita da vírgula. De outra forma, e tomando como exemplo o valor “54,090000000…03”, o resultado deverá ficar “54.09”. Só depois é que deverá ser executado o somatório.

Mas atenção, existem números que não têm vírgula, logo a solução anterior apenas pode ser implementada para os outros casos, daí utilizar um “case when” para perceber se o dado tem vírgula ou não…

c5

Reparei agora que estou a converter para “float” num caso e “numeric” noutro. Para o caso em si é irrelevante, o somatório será realizado na mesma.

De salientar que a coluna “proprietario” tem o mesmo valor pois foi alterado manualmente propositadamente, caso contrário mostraria dois proprietários diferentes, com valores líquidos diferentes.

Espero que vos seja útil,

JG

Linguagens de Programação

Certo dia, li um artigo onde mencionava que se deveria aprender uma nova linguagem de programação em cada dois anos. Seu autor era alguém seguramente bem conceituado na matéria e, no meu ponto de vista, com alguma razão. Digo alguma razão pois isto de aprender linguagens novas e alterar a tecnologia em cada dois anos pode não ser implementável…mas aprender tecnologias novas e melhorar o código e forma de o programar, isso sim, é muito importante.

No “meu tempo” de faculdade, há cerca de 25 anos, a linguagem que se aprendia era o Pascal.
Altamente estruturada (procedimentos, funções, filesystem, ciclos if..then, case, …), esta bela linguagem tinha a vantagem de poder ser compilada para um executável.
O executável era o ficheiro “xxx.exe” que poderia ser executado em qualquer ambiente MSDOS/Windows.
O Pascal modernizou-se e apareceu o Delphi, que creio ainda hoje existir.
Além do Pascal, era introduzido o ensino do LISP e do Prolog…quase me esquecia do Assembly, tão importante na criação de programas de alarmes de casa, ou controlo de tráfego!

Com o tempo, já em actividade empresarial, fui induzido a aprender algo relacionado com “bases de dados”, coisa pouco falada nos anos 90 e muito rudimentar.
Entrei no mundo do “Magic”, algo que iria revolucionar o mercado na construção de interfaces e DBs. Entretanto, a minha área de actuação em TI alterou-se e a programação praticamente desapareceu.

Anos mais tarde, com o que veio a ser o princípio de um grande sistema bancário, inteirei-me das tecnologias web (IIS) e da forma de apresentação das páginas (html).
Aprendi o SQL, iniciando com a instalação, passando pela criação de DB e tabelas, e acessos, e…até conseguir programar em TSQL. Aprendi ASP, tecnologia Microsoft para criação e amostragem de páginas web; claro está que tive de melhorar em html, css, um pouco de javascript, ajax e jquery. Durante anos mantive a programação eficiente de Sistemas de Informação baseados neste contexto.

Muitas vezes fui questionado se não deveria enveredar por outra tecnologia, a verdade é que estava satisfeito com esta mas lá tentei o “.NET”.
De facto, a criação de interfaces em .Net é muito mais simplificada, quase não é preciso ser “programador” para fazer muitas das coisas.
Facilitismo? Sim, também é necessário!
O facto é que não continuei com o .Net, pareceu-me a determinada altura mais complexo (…),  além de que o timing para a criação era curto e tinha de apresentar resultados!

Experimentei php, na altura em que pensaram em mudar o sistema para Linux, e nada de novo. Idêntico ao ASP mas adequado a um servidor Linux.

No presente fala-se muito em linguagens novas, coisas que não existiam no passado, como o Ruby, R e Phyton. Fala-se também em BigData e coisas das inteligências artificiais. Fala-se na Cloud e no SQL Azure, na enorme potencialidade do Phyton e pesquisa de dados….bem, fala-se demais!

O mundo dos dados está ao rubro, todos querem o mesmo: perceber, muito antes das pessoas, o que elas precisam – ou não – para lhes ser proporcionada publicidade.
É o mundo revolucionário da procura desenfreada de dados, tanto que foi necessário algum freio nisto por parte do governo. Os novos DPOs terão um papel importante aqui (outra formação a fazer!).

Mas voltando à programação…

Há uns dias iniciei um curso de Phyton, afinal estava a ser um info-excluído, era uma vergonha não saber Phyton e a sua importância abismal no trabalho com dados!
Lá iniciei eu o curso na “CodeAcademy”…e terminei-o talvez numa dúzia de horas, ao longo de alguns dias.
A minha opinião sobre esta linguagem é positiva mas para um info-excluído como eu não vi muito de novo. Dicionários, Listas, arrays, ciclos, filesystem…nada de novo!
Procurei depois algo mais profundo com ligação a SQL…mais uma vez nada de novo!

Quem aprende inicialmente uma linguagem de programação como o Pascal aprende facilmente qualquer outra, é a minha opinião.

JG

SQL Execution Plan para obtenção código mais eficiente

Olá,
para não variar muito, hoje trago aqui um assunto cuja origem esteve num trabalho recentemente desenvolvido.

Foi-me pedido para criar um interface web (ASP) cuja finalidade fosse permitir a qualquer pessoa interagir com uma tabela SQL, seja para alterar registos, inserir novos ou eliminar. Paralelamente, a “aplicação” deveria gravar todo o histórico realizado sob os dados.

Ora bem, a minha opção de implementação foi a de criar um segunda tabela SQL, réplica da original, onde para cada intervenção na primeira se inserisse na segunda a acção correspondente.
Neste cenário, inicialmente ambas as tabelas têm os mesmos registos; se o utilizador alterar algum, este ficará modificado na tabela original e será inserida nova linha de registo na tabela réplica com a acção realizada, incluindo o nome de quem alterou e data do evento.

A nível aplicacional permite para cada registo verificar todas as acções realizadas sobre ele, mesmo os que forem eliminados.

A seguinte imagem retrata o interface que fiz com a amostragem dos dados da tabela. Clicando em qualquer linha de dados permite aceder a outra janela para a modificação dos mesmos.

proj201802_10

Usando o segundo registo como exemplo, clicando  na linha do 129, obtemos a possibilidade de editar ou eliminar o registo, além de perceber tudo o que se passou com ele ao longo do tempo:

proj201802_11

É precisamente para os registos eliminados que surgem dúvidas…”se eliminas um registo da tabela original como consegues uma listagem dos registos eliminados com todas as alterações antes de ser eliminado?”.

Bem, a acção de eliminar um registo retira-o efectivamente da tabela original mas insere-o na tabela réplica com a notação de “delete”. As acções de alteração gravam a notação de “edit”.  Assim, bastará pesquisar a tabela pelos registos “delete”…

Iniciemos por visualizar os registos que foram eliminados. A tabela réplica foi denominada de “TblTipoDespesa_hist”  e os registos eliminados percebem-se pelo campo “accao” que deverá conter a palavra “Delete”:

proj201802_1

Foram eliminados 2 registos, o “IDDespesa 130” e o “IDDespesa 132” (penúltima coluna da figura anterior), por mim (Jorge Gomes) em 2018-02-28.

Para se visualizar o histórico destes registos, pré-eliminação, basta pesquisar na tabela pelos respectivos IDDespesa, ordenando-os pelas respectivas datas de alteração:

proj201802_2

Cada um dos registos sofreu alterações ao longo do tempo antes de ser permanentemente eliminado.

Portanto, poderemos colocar um link na aplicação que permita abrir uma janela com a listagem dos registos eliminados e todo o seu histórico.

O código SQL genérico a implementar no interface será do género:

proj201802_3

mas também poderá ser usado um join entre tabelas:

proj201802_4

Qual código usar?

Vejamos qual o mais eficiente utilizando a ferramenta “include actual execution plan”, conforme figura abaixo:

proj201802_5

Com esta ferramenta activa, vamos executar o código de ambos os processos no mesmo batch (simultaneamente):

proj201802_6

Analisando a figura anterior, verifica-se a existência de um novo separador “Execution Plan” mesmo ao lado dos resultados da query.

Acedendo ao separador “Execution Plan” poderemos ver os seguintes esquemas ilustrativos do “custo” de processamento para cada um dos casos, relativos ao mesmo plano de execução.

Percebe-se que um terá um custo de 47% e outro um custo de 53%:

proj201802_7

Não detalhando o “Execution Plan”, interessa perceber que é uma ferramenta útil na análise de querys e sua performance final, devendo ser utilizado para comparação de código SQL assim como para melhorias do mesmo.

Em conclusão, quando aparentemente duas querys são iguais na produção dos resultados deveremos optar pela que custa menos ao processo, neste caso deveremos optar pela primeira opção em detrimento do join.

PS: o interface para listagem das eliminações e todo o seu histórico está praticamente pronto.

Espero que vos seja útil!

JG

 

 

 

 

Listar Stored Procedures em SQL

Olá,
ao pensar na criação de uma Stored Procedure (SP) no SQL tive a necessidade de perceber se o nome seria adequado e se já existiria. Assim, foi necessário verificar que SPs existiam na base de dados.

O código TSQL que vos mostrarei de seguida lista, para uma determinada DB, todas as Stored Procedures criadas pelos utilizadores.

Para esta demonstração, exemplificarei com a DB “Performance”:

use performance

select * from sys.procedures where is_ms_shipped = 0
order by create_date desc

O filtro “is_ms_shipped=0” permite mostrar as SPs criadas pelo utilizador, caso contrário mostraria todas as SPs, incluindo as do sistema.

Na listagem seguinte, as SPs são mostradas por data de criação mais recente:

spDB

Uma nota para uma boa nomenclatura de SPs permite dizer que a terceira SP (sp_BigTablesSP) não está correta. O prefixo “sp_” não deve ser usado para nomear SPs de utilizador uma vez ser um prefixo utilizado pelo sistema para SPs de sistema.

Espero que vos seja útil,

JG

 

 

SQL Linked Server

Olá,
os dados em SQL podem estar distribuídos por tabelas e views, sejam na mesma DB ou em DBs diferentes.
Por norma, estão armazenados no mesmo servidor, provavelmente em DBs distintas apropriadas a cada uma das direcções ou a cada um dos temas – por exemplo uma DB “Campanhas”, outra “RH”, outra “Performance”, etc; o facto de estarem em DBs diferentes ajuda na organização e distinção dos dados, principalmente na área de desenvolvimento.

Mas esta configuração centrada apenas num servidor pode ser insuficiente, cada vez mais a informação está dispersa por máquinas diferentes, em estruturas físicas diferentes, até por motivos de localização.

Ora, para consulta de dados em servidores diferentes existem duas possibilidades:

  1.  Conectar a cada um dos servidores SQL com as respectivas credenciais de acesso
  2.  Criar um “linked Server” entre os servidores SQL

 

1. Conectar a cada um dos servidores SQL com as respectivas credenciais de acesso

Esta forma não é usual, a não ser que seja esporádico. Na área de desenvolvimento, por ex., para acesso a DBs não é conveniente existirem muitas conexões às mesmas, por questões de segurança.

Mesmo utilizando apenas o SQL Management Studio, a pesquisa de dados com conexões diferentes a servidores diferentes só funciona se não se desejar cruzar informação, isto é, apenas se conseguem os dados efectuando a pesquisa em separadores diferentes.
Como fazer um “Join” com duas ou três tabelas existentes em servidores diferentes?…

2. Criar um “linked Server” entre os servidores SQL

A criação de um “linked server” permite utilizar a mesma “janela” do SQL Management Studio (ou código existente num procedure/função) e efectuar pedidos de dados ao servidor local e remoto, simultaneamente, sendo possível o cruzamento de dados entre eles ( e sem ter a necessidade de nova conexão ao servidor).

Isto é particularmente útil na programação de aplicações onde o código de acesso aos dados se rege apenas por uma conexão ao servidor SQL e onde são necessários cruzamento de dados de diversas localizações.

Assim, na janela do nosso servidor é possível efectuar um pedido ao outro servidor da seguinte forma:

select * from [servidorremoto].BD_REMOTA.dbo.Tabela

Será necessário colocar o nome do servidor remoto no pedido de dados.

 

Criar e Configurar Linked Server

Ao criar um linked server no servidor SQL local irei adicionar o servidor remoto ao meu.

Assim, primeiramente faço a autenticação no SQL Management Studio local (meu servidor), depois expando “Server Objects”, seguido de Linked Servers. Aqui prime-se botão direito do rato e acede-se ao menu onde se deverá seleccionar “New Linked Server”:

ls1

De seguida, é mostrado o seguinte interface onde se deve especificar o nome (ou IP) do servidor remoto e dizer que é do tipo SQL Server:

ls2

Uma breve nota aqui para salientar que o servidor remoto pode ser outro que não SQL, pode ser por exemplo de um provider AS400…

Acedendo, depois ao separador “Security”, iremos colocar as credenciais de acesso ao servidor remoto em “Be made using this security context”, colocando nome de utilizador remoto e password:

ls3

Para funcionar dentro do SQL Management Studio este processo está concluído, basta premir o botão “OK”.

A partir deste momento o pedido de dados ao servidor local e remoto pode ser efectuado no mesmo código SQL, na mesma conexão.

Tendo por base duas tabelas fictícias, em servidores distintos, repare-se como os dados são mostrados no mesmo contexto (processo):

ls5

O segundo pedido pertence ao servidor remoto e denota-se pelo “[RemoteServer]”, o mesmo nome especificado na configuração acima.

Com esta configuração qualquer cruzamento de dados é possível, qualquer criação de funções ou stored procedures é viável.

Se na programação de aplicações der erro de utilizador ao aceder aos dados SQL pode ser necessário especificar mapeamentos de utilizadores entre o servidor remoto e o servidor local.

Para isso, é necessário ir à configuração do linked server criado, editar o mesmo e, em “Security”, especificar esse mapeamento em “local server login to remote server login mappings”, colocando o utilizador local e o utilizador remoto:

ls4

O “local login” é o utilizador que a aplicação deverá utilizar como credencial de acesso aos dados.

Espero que vos seja útil,

JG

Stored Procedure e a criação de mapas

Olá,

hoje trago aqui a criação e utilização de Stored Procedure no SQL.

De uma forma simples, o SQL é constituído por tabelas (reservatórios de dados), views (baseadas nas tabelas, são constituídas por filtros de forma a mostrar apenas alguns dados), procedimentos (stored procedures) e funções.

Os procedimentos e as funções são a parte mais criativa do SQL, é aqui que se programa em SQL.  É código trabalhado que fica armazenado no servidor SQL e pode ser executado a qualquer altura, é algo que se faz uma vez e fica para sempre, pode ser reutilizado.

Uma função é algo que pode ser chamado na execução do código SQL e retorna sempre um valor (seja escalar ou conjunto de registos em formato tabela).
Existem funções internas do sistema, por exemplo a função year() que retorna o valor do ano para um campo do tipo data:

SELECT year(Campodata), descritivo from tblxxx 

Outras há que permitem funcionar com campos numéricos, por exemplo max() que retorna o valor máximo do campo:

SELECT max(numero) from tblxxx

Em relação aos procedimentos (stored procedures), também existem alguns internos (sistema), iniciados por sp_xxx. Temos o caso do sp_send_dbmail que, ao ser executado, permite enviar email através do SQL:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'teste',  
    @recipients = 'alguem@hotmail.com',  
    @body = 'Texto a escrever',  
    @subject = 'Assunto' ;  

Note-se que previamente é preciso configurar o servidor para envio de email…

Repare-se que uma função é chamada dentro do pedido SQL, dentro do “select”; o stored procedure tem de ser executado fora desse pedido.

Por vezes (muitas vezes mesmo!) é necessário construir quer funções quer stored procedures para os mais variados efeitos. Ambos serão criados, guardados no servidor dentro da respectiva DB, e ambos poderão ser chamados/executados a qualquer altura.

Imaginemos, então, o seguinte cenário: uma DB “performance” onde estão armazenadas duas tabelas, “TblVeiculos” e “Abastecimentos“.

Fazendo a selecção de dados de ambas:

select * from performance.dbo.TblVeiculos
select * from performance.dbo.Abastecimentos

produzimos os seguintes dados:

SP1

A primeira tabela é composta por dados relativos a veículos, cada linha é única, cada linha refere-se a um único veículo (distinto dos outros).

Nesta minha pobre tabela tenho apenas quatro veículos, com identificador diferente, matrícula diferente e…bem, pode ter cores iguais!…

Na segunda tabela, existem dados de abastecimento de combustível para todos os veículos: em que kilometragem se efectuou, que tipo de combustível, preço a pagar, etc…

Imaginemos, então, que nos pedem para implementar um processo que nos mostre os custos (apenas de abastecimento de combustível) para os veículos de determinada cor…sabe-se lá porquê, podem estar a trabalhar num tipo de tinta redutora de combustível!

O pedido envolve duas vertentes, uma para juntar num só local os veículos de uma determinada cor, depois fazer o somatório de todas as despesas para cada um desses veículos.

A ideia será fazer a amostragem em dois quadros, talvez num interface ASP/PHP após clicar numa determinada cor; pode ser também para implementar mapas de Excel ou outros.

Este processo pode ser realizado através da construção de um Storage Procedure (SP) no SQL.

Vamos então criar um SP que produzirá duas tabelas, uma com a listagem de veículos da mesma cor (variável conforme input do utilizador/programa):

VeiculosMesmaCor

e outra com a listagem resumo dos custos para cada veículo dessa mesma cor:

ResumoVeiculoCustos

Para se perceber melhor, a imagem seguinte mostra já o resultado pretendido, para o caso da cor cinza:

SP2

Para a cor cinza a amostragem comporta os veículos 1,2 e 4.

Estes quadros podem ser facilmente exportados para Excel ou colocados num interface ASP ou PHP ou…

Vamos, agora, à implementação do Stored Procedure, chamei-lhe SPVeiculosConsumoCor:

SP3

O algoritmo para a criação dos primeiros resultados resume-se mais ou menos nisto:

  • Verificar se existe a tabela VeiculosMesmaCor.
  • Caso positivo, elimina os dados da tabela VeiculosMesmaCor.
  • Insere nesta mesma tabela, para cada iteração,  dados existentes na tabela Abastecimentos.
  • Os dados a inserir são apenas os que possuem cor do carro igual à especificada pelo utilizador.

O algoritmo para a criação dos resultados finais :

  • Elimina dados existentes na tabela ResumoVeiculoCustos.
  • Insere nesta mesma tabela o somatório dos dados da tabela VeiculosMesmaCor , agrupados por identificador de veículo.

Para executar o SP deveremos utilizar o seguinte comando :

exec dbo.SPVeiculosConsumoCor 'cinza';

(isto para o caso do cinza)

Após executar este SP, serão actualizados os dados das duas tabelas anteriores, VeiculosMesmaCor e ResumoVeiculoCustos.

 

Em resumo, o utilizador final apenas terá de efectuar os seguintes passos:

EXEC dbo.SPVeiculosConsumoCor 'cinza';

SELECT * from dbo.VeiculosMesmaCor

SELECT * from dbo.ResumoVeiculoCustos

e os resultados serão imediatos.

Espero que vos seja útil,

JG