MySQL

28

2.2

MYSQL

MODELAÇÃO DA BASE DE DADOS

Conclui-se facilmente que para se desenhar e implementar uma base de dados não basta saber SQL e ter um SGBD, atendendo a que é necessário efetuar trabalho a montante e a jusante, com vista a determinar alguns parâmetros que irão permitir afinar a base de dados e fazer com que corresponda às necessidades para as quais foi construída, sendo o percurso realizado designado por ciclo de vida da base de dados (database lifecycle).

2.2.1

ENTIDADES, ATRIBUTOS E RELAÇÕES

Uma base de dados tem um ciclo de vida que integra várias fases, as quais vão desde a determinação dos requisitos essenciais e avaliação da organização para a qual se vai elaborar a base de dados, até à fase de melhoria da base de dados, não esquecendo também as fases de desenho e implementação. Ora, é ao longo deste ciclo que se definem as necessidades da organização, a finalidade a que se destina a base de dados, o tipo de SGBD a utilizar, entre outras especificidades.

Assim, e com vista à criação e implementação da base de dados final, deve então começar-se por elaborar o diagrama entidade-relação que corresponderá à segunda fase do ciclo de vida, em que temos um esquema que nos irá demonstrar como está estruturada a base de dados. São parte importante deste esquema os seguintes elementos: Entidade – é representada por um retângulo e corresponde ao elemento que se utilizar para identificar uma situação do mundo real (por exemplo, aluno ou docente); Relação – é representada por uma linha que une duas ou mais entidades e que também representa o relacionamento, sendo comum dar-se uma designação que represente a ação que existe entre ambas as entidades (por exemplo, ensina ou frequenta), representada por um losango; Atributo – é representado por um círculo e cada um corresponde às várias propriedades de cada atributo, geralmente coincidindo com os campos criados na tabela (por exemplo, nome, morada, código postal, etc.), sendo comum representar com um duplo círculo ou por um sublinhado o atributo que corresponde à chave primária.

Exemplifica-se, de seguida, um diagrama entidade-relação para o caso de um professor que leciona uma disciplina, onde se evidenciam as entidades “professor” e “disciplina”, os atributos de cada uma destas entidades e ainda a relação entre o professor e a disciplina (Figura 2.1).

© FCA – Editora de Informática

36

MYSQL Catalog, Layers e User Types (4) – permitem visualizar as bases de dados e tipos de dados, de forma idêntica aos apresentados na Figura 2.3; Description, Properties e History (5) – permitem obter as propriedades e as descrições dos objetos, bem como um histórico das operações realizadas.

2.3.2

CRIAÇÃO DE UM DIAGRAMA E-R A PARTIR DE UMA BASE DE DADOS EXISTENTE

Por vezes, o diagrama E-R é realizado em papel e a base de dados é criada diretamente através da linha de comandos ou de um script executado no MySQL Workbench. Contudo, caso o utilizador necessite de obter uma imagem do diagrama E-R pode fazê-lo depois de a base de dados já estar criada, usando o MySQL Model. Contudo, e com base na Figura 2.2, em vez do botão 1 deverá clicar no botão 3, que lhe oferecerá duas opções: Create EER model from database – esta opção permite a criação de um diagrama E-R a partir de uma base de dados existente no servidor; Create EER model from script – esta opção permite a criação de um diagrama E-R a partir de um script em linguagem SQL. Isto significa que, ao clicarmos no botão 3 (ver Figura 2.2), estamos a usar a ferramenta em sentido inverso, ou seja, estamos a criar o diagrama E-R a partir da base de dados já criada ou de um script existente, sendo possível visualizar a imagem da base de dados, seus relacionamentos, respetivas chaves e campos e seu tipo.

2.3.2.1 CREATE EER MODEL FROM DATABASE

A criação de um modelo E-R pode ser realizada a partir de uma base de dados existente. Regra geral, o procedimento é realizado em sentido inverso, ou seja, primeiro constrói-se o modelo E-R e só depois é criada a base de dados. Contudo, podemos ter uma base de dados que não tenha sido criada por nós e em relação à qual necessitamos do modelo E-R. Nesta altura, podemos recorrer à ferramenta disponibilizada pelo MySQL Workbench, fazendo uma espécie de reverse engineering, que nos permitirá obter o modelo E-R que está na origem dessa base de dados.

Este procedimento contém sete etapas: 1) Connection Options – inserção dos dados referentes à ligação ao servidor onde se encontra a base de dados pretendida. Tais dados referem-se a nome do servidor, utilizador, palavra-passe, porto de ligação, etc.

© FCA – Editora de Informática

48

MYSQL

Caso se tente criar uma base de dados e já exista alguma com o mesmo nome, será devolvido o erro 1007, com a sintaxe ERROR 1007 (HY000): Can´t create database ‘clinica’; database exists. Se tivéssemos dúvidas sobre se a base de dados clinica já existia, poderia utilizar-se o sufixo IF NOT EXISTS, que apenas criaria a base de dados caso ainda não existisse alguma já criada com esse nome.

A Figura 3.2 mostra o comando de criação da base de dados e o respetivo erro, quando se tenta repetir este comando, já com a base de dados criada.

FIGURA 3.2 – Comando de criação da base de dados clinica

Caso se pretendesse verificar quais as bases de dados presentes no servidor, poderia utilizar-se o comando SHOW DATABASES;, que iria mostrar o nome de todas as bases de dados mas não a sua estrutura interna, ou seja, as tabela que as compõem.

Após a criação da base de dados, é preciso criar as tabelas que a constituem mas, para isso, é necessário indicar ao servidor que pretendemos usar a base de dados criada.

Afinal, como já foi visto, no mesmo servidor podem morar várias bases de dados, pelo que é necessário identificar aquela com a qual queremos trabalhar no momento. Para selecionar a base de dados pretendida, insere-se o comando USE seguido do nome da base de dados, ou seja: mysql>USE clinica;

É, então, recebida a mensagem Database changed e estamos, agora, aptos a trabalhar na criação da tabela medicos. O comando USE tem de ser ativado sempre que se inicia uma nova janela da linha de comandos; já no MySQL Workbench bastará clicar e ficará assinalada, a negrito, a base de dados pretendida.

© FCA – Editora de Informática

SQL PARA INICIANTES

3.5.2

61

CONSULTA LIMITADA E ORDENADA

Quando se realizam consultas à base de dados, é possível que seja devolvido um elevado número de entradas, conforme o tamanho desta. Uma forma de limitar esta consulta, sobretudo quando trabalhamos com páginas web e pretendemos que sejam devolvidos, por exemplo, 20 registos por página, é introduzir a instrução LIMIT associada à instrução SELECT. A sintaxe de tal instrução é: SELECT campo1, campo2,... FROM nome_base_dados LIMIT quantidade_registos;

Esta instrução irá permitir que o utilizador receba apenas um determinado número de registos que, no caso de estar programado com um determinado ciclo (por exemplo, do tipo FOR ou WHILE), permitirá que seja devolvido um número finito de dados de cada vez, até serem esgotados todos os registos existentes na base de dados.

No caso da “nossa” base de dados, se quiséssemos consultar apenas os primeiros três registos, bastaria inserir a instrução do tipo: mysql>SELECT * FROM medicos LIMIT 3;.

Para a realização de seleções ordenadas, bastará incluir a instrução ORDER BY, cuja utilização permitirá diversas formas de ordenação, sendo que esta ordenação pode ser realizada de forma ascendente (ASC) ou descendente (DESC).

No caso em apreço, e conjungando o limite anterior com uma seleção dos funcionários ordenados por ordem alfabética ascendente, teríamos uma instrução do seguinte tipo: mysql>SELECT * FROM medicos ORDER BY nome ASC LIMIT 3;

O resultado obtido é apresentado na Figura 3.5.

FIGURA 3.5 – Consulta limitada e ordenada

© FCA – Editora de Informática

OPERAÇÕES COM TABELAS

4.4

81

UTILIZAÇÃO DE VIEWS

O acesso à informação é uma prática cada vez mais generalizada, mas nem sempre daí resulta uma boa solução em termos de trabalho, pois pode haver ingerência a nível de funções ou de acessos, sobretudo quando há informações sensíveis. No caso do exemplo da clínica médica que temos vindo a utilizar, imagine-se que alguém pretende saber quais as consultas realizadas e por que pacientes, com vista ao preenchimento de uma declaração de impostos da clínica.

Este funcionário (digamos, uma secretária ou contabilista) não necessita de obter o contato telefónico dos pacientes para o preenchimento dessa declaração. O mesmo se passa, por exemplo, com a situação em que se elabora a pauta de classificações de alunos de uma escola, em que não é necessário saber o nome dos pais, o seu salário ou profissão para o fazer.

Para restringir o acesso a determinadas informações, é possível implementar views, ou seja, “vistas” ou “janelas” em que a informação é filtrada e apenas se apresenta determinados dados. Além disso, podem ser criadas tantas views quantas as necessárias, em função de cada situação, as quais não são armazenadas no sistema, uma vez que são construídas momentaneamente, como se constituíssem uma fotografia da(s) tabela(s) naquele momento com os dados que lá residem.

Outra vantagem da utilização das views é que estamos a transferir trabalho para o servidor da base de dados, uma vez que aí ficam armazenadas, podendo ser solicitadas a qualquer altura. Tal procedimento é bastante útil quando as instruções das consultas são demasiado grandes ou complexas, permitindo obviar os erros – caso seja necessário alterar algo na instrução, tal apenas é feito uma vez.

4.4.1

CRIAÇÃO DA VIEW COM UMA TABELA

Existem várias formas de criar views mas, de forma genérica, todas são criadas através do comando CREATE VIEW, indicando-se o nome da view e a lista de campos a utilizar. A sintaxe de criação de uma view para uma tabela é a seguinte: CREATE VIEW nome_view (lista de colunas) AS SELECT (opções);

Por exemplo, pretende-se criar a view que mostre o nome dos pacientes e a sua idade (sem mostrar o contato): mysql>CREATE VIEW lista1 AS SELECT nome, idade FROM pacientes;

Neste caso, a interrogação da view para saber os resultados é feita de forma idêntica à de uma interrogação comum usando apenas o SELECT: mysql>SELECT * FROM lista1;

© FCA – Editora de Informática

5

STORED PROCEDURES E FUNÇÕES

5

Neste capítulo serão desenvolvidos alguns conceitos relacionados com funções e stored procedures e analisadas as diferenças entre estas duas figuras e aplicações, bem como as vantagens, desvantagens e aplicações específicas para cada uma. Além disso, será abordada a estrutura de um stored procedure, a sua criação e aplicação em casos concretos.

5.1

STORED PROGRAMS

Genericamente, existem dois tipos de procedimentos que podem ser armazenados numa base de dados, fazendo parte de um grande grupo geral designado por stored programs (SP), ou “programas armazenados” numa tradução muito simplista.

Assim, dentro deste grupo principal temos dois subtipos, sendo que o primeiro se designa por stored procedures enquanto o segundo corresponde às funções. Em alguma bibliografia, aparece a terminologia stored procedure, para designar os stored programs, o que pode gerar alguma confusão, pelo que se adotará a nomenclatura de stored programs para o grupo que contém os stored procedures e as functions (funções).

Ao contrário das funções, que veremos adiante, os stored procedures permitem operações mais complexas e também o input de dados, o que faz com que tenham um maior espetro de aplicação, sobretudo quando queremos interagir com os dados armazenados na base de dados. Por outro lado, os stored procedures são mais utilizados quando se pretende realizar um conjunto de instruções (por exemplo, realizar operações sobre um conjunto de valores), enquanto as funções se aplicam sobretudo a casos em que se pretende a devolução de um valor.

Apesar das diferenças existentes, quer a sintaxe quer a forma como são elaborados e executados os stored procedures e as funções são semelhantes, pelo que se analisará primeiro os stored procedures, que abarcam um maior conjunto de situações.

Compreendidos estes, estaremos em condições de, rapidamente, perceber as funções e o seu funcionamento, uma vez que se aproveitará grande parte dos ensinamentos transmitidos nos stored procedures para a sua criação, utilização e modificação.

© FCA – Editora de Informática

132

MYSQL

Como se verifica, o trigger só é acionado se for respeitado o momento de execução e se ocorrer o evento que lhe dá “ordem” para ser executado.

Quanto ao nome da tabela, este corresponde à tabela sobre a qual será executado o trigger, colocando-se depois o código entre os delimitadores BEGIN/END caso haja mais do que um comando, tal como ocorria anteriormente para os stored programs. Atendendo a que é sobre esta tabela que o trigger vai atuar, é também aí que o vamos encontrar caso seja necessário alterá-lo ou apagá-lo.

Os triggers permitem ainda que seja utilizada a expressão OLD.nome_coluna para obter os valores da coluna antes da execução das instruções de UPDATE ou DELETE, ou a expressão NEW.nome_coluna para obter os novos valores da coluna após a execução das instruções de INSERT ou UPDATE. Apesar de oferecer algumas possibilidades, a criação de triggers está um pouco limitada pelo tipo de operações que pode registar, pois não é permitido, por exemplo, duas sub-rotinas diferentes no mesmo trigger. Isto é, se o objetivo é registar as operações de inserção e eliminação, é necessário efetuar a construção de dois triggers que trabalhem simultaneamente sobre a mesma tabela.

6.2.3

CRIAÇÃO DE TRIGGERS

Para melhor se compreender o funcionamento e aplicação de um trigger, vamos supor que pretendíamos ter um script que nos indicasse todas as alterações sofridas por uma tabela da base de dados. Por exemplo, pegando na base de dados clinica, queríamos visualizar todas as alterações feitas à tabela consultas, seja por inserção, alteração ou eliminação de consultas nessa tabela.

Para realizar esta tarefa, podemos criar um trigger que é executado sempre que um dos comandos de inserção, alteração ou eliminação é realizado sobre a tabela das consultas. Note-se que este tipo de trigger é silencioso, ou seja, trabalha no servidor e não há qualquer diferença para quem insere, altera ou elimina as consultas na tabela.

Uma vez que o trigger vai registar as alterações que ocorram, é necessário criar uma nova tabela, que designamos por auditar_consultas, que vai ser a tabela onde vão ser inscritas as alterações que vão ocorrendo na tabela consultas.

Esta tabela vai registar vários parâmetros, como o nome do utilizador que realizou a alteração, o tipo de operação realizada sobre a tabela (INSERT, DELETE ou UPDATE), a data e hora, e também o código da consulta que sofreu a alteração (id_consulta). Para tal, inicia-se a criação da tabela auditar_consultas, cujo código se apresenta de seguida: mysql>CREATE TABLE `auditar_consultas` ( © FCA – Editora de Informática

170

7.3.4

MYSQL

REVOGAÇÃO DE PERMISSÕES

A revogação de permissões não implica a eliminação de um utilizador, mas antes a alteração ou eliminação das permissões que lhe foram atribuídas. Para tal, utiliza-se o comando REVOKE, que permite revogar todas as permissões ou apenas uma dada base de dados. Assim, se se pretender revogar todas as permissões deverá inserir-se a seguinte instrução: REVOKE tipo_de_permissões ON *.* FROM nome_utilizador@nome_servidor;

Caso se pretenda apenas revogar as permissões sobre todas as tabelas de uma determinada base de dados, a instrução toma a seguinte forma: mysql>REVOKE tipo_de_permissões nome_utilizador@nome_servidor;

ON

nome_base_dados.*

FROM

Pode ainda operar-se a revogação apenas sobre determinada tabela da base de dados, bastando para tal inserir o nome da tabela após o nome da base de dados: mysql>REVOKE tipo_de_permissões nome_utilizador@nome_servidor;

ON

nome_base_dados.nome_tabela

FROM

Se o objetivo for revogar as permissões de todos os utilizadores, então poderá substituir-se o nome do utilizador por ''. O comando REVOKE pode ter vários tipos de permissão associados, não sendo necessário repetir toda a instrução várias vezes, dado que podem ser revogadas várias permissões de uma só vez. Por exemplo, imaginemos que pretendemos limitar o utilizador “Felisberto” para deixar de poder inserir ou alterar dados em todas as tabelas da base de dados clinica. Para tal, utilizar-se-ia a instrução seguinte: mysql>REVOKE INSERT, UPDATE ON clinica.* FROM Felisberto@localhost;

Como se verifica, a sintaxe e os parâmetros aplicados a este comando são muito semelhantes aos utilizados no comando GRANT.

7.3.5

COMANDO FLUSH

Para efetivar alguns procedimentos, não basta inserir as instruções como acontecia com a maioria dos comandos demonstrados no Capítulo 4, uma vez que algumas alterações só se efetivam quando for realizado um comando FLUSH. A este conceito já aludimos superficialmente no Capítulo 4 quando falámos do comando FLUSH TABLE, mas aqui trata-se de um FLUSH PRIVILEGES, ou seja, de efetivar permissões, uma vez que em algumas operações é necessário ter permissões específicas para as realizar.

Isto porque se forem alteradas permissões usando comandos como INSERT, DELETE ou UPDATE, esses apenas produzirão efeitos quando o servidor reiniciar ou for realizado um comando do tipo FLUSH PRIVILEGES para forçar as alterações efetuadas.

© FCA – Editora de Informática

176

MYSQL

Para o comando UPDATE, podemos realizar também a alteração do nome de utilizador, além da palavra-passe. Assim, se pretendermos alterar o utilizador “root” para “administrador” e inserir a palavra-passe “abc123xpto”, as instruções a utilizar são as seguintes: mysql>UPDATE user SET user = 'administrador' WHERE user = 'root'; mysql>UPDATE user 'administrador';

SET

password

=

PASSWORD('abc123xpto')

WHERE

user

=

mysql>FLUSH PRIVILEGES;

Se optarmos pelo comando SET PASSWORD, alteramos apenas a palavra-passe, e o procedimento é idêntico ao descrito na secção 7.2.3 para a mudança da palavra-passe de um utilizador comum.

Depois deste procedimento, devem ainda ser eliminados os utilizadores anónimos que possam, eventualmente, permanecer no sistema, através da instrução DELETE user WHERE user = '';. A partir daqui, sempre que for registado um novo utilizador, o administrador deve atribuir-lhe uma palavra-passe e assegurar a segurança do servidor. Após esta alteração, pode correr-se novamente o comando mysql>SHOW GRANTS FOR 'administrador'@'localhost'; para verificar se não houve nenhuma alteração indesejada nas permissões, uma vez que tal pode comprometer a posteriori as operações que o administrador realizar. Este processo de alteração da palavra-passe e do nome de utilizador também pode ser realizado utilizando o MySQL Workbench, através do separador Login e da opção Users and Privileges.

7.5.2

RESET DA PALAVRA-PASSE

Pode acontecer que, numa situação extrema, a palavra-passe da conta root seja esquecida, o que levará a que o acesso ao servidor fique totalmente impedido caso não haja outra conta de administrador. Para prevenir uma falha deste género, pode ser criada uma segunda conta de administrador com um acesso diferente e com as mesmas permissões que a conta root criada originalmente pelo MySQL. Contudo, se ocorrer a situação em que só haja uma conta root e a palavra-passe seja esquecida, é possível fazer um reset à mesma e aceder ao servidor. Chama-se a atenção para o facto de que para realizar este procedimento é necessário ter acesso físico ao servidor, além de que tal operação vai forçar a paragem do servidor, o que pode ter consequências para os restantes utilizadores. Uma vez que o procedimento é diferente consoante o sistema operativo, será explicado em separado.

© FCA – Editora de Informática

230

9.5

MYSQL

REPLICAÇÃO

Vimos até agora alguns dos processos possíveis para a realização de cópias de segurança. Contudo, em todos eles é necessária a intervenção de um utilizador para que sejam executadas, além de que é um processo que pode tornar-se lento e implicar o bloqueio da base de dados. O processo de replicação é uma das formas que permite a cópia de segurança em tempo real, evitando-se a intervenção de um utilizador, dado que é um processo automático.

9.5.1

CONCEITO

A replicação consiste numa operação que realiza, num local diferente do servidor, uma cópia automática de cada transação realizada numa base de dados. Por exemplo, a cada novo registo inserido numa tabela de uma base de dados do servidor é automaticamente feita a mesma transação noutra máquina diferente, designando-se o servidor por Master e a(s) outra(s) máquina(s) por Slave(s).

Para melhor se compreenderem estes conceitos, atente-se na Figura 9.2.

Cliente B Servidor (Master)

Lê e escreve

Apenas escreve Load Balancer Apenas lê

Slave 1 Cliente A

Slave 2

Slave 3

FIGURA 9.2 – Replicação em MySQL

© FCA – Editora de Informática