Bases de Dados

Bases de Dados
PostgreSQL, MySQL e Php
Transações
P. Serendero, 2011-13
PostgreSQL e Php: transações
Os comandos SQL utilizados nalgumas aulas
práticas e mostrados aqui foram feitos num
interpretador de comandos: >psql.
O prompt do Mysql: >mysql. Exemplos tirados da Web
Para desenvolver aplicações é preciso inserir os
comandos de SQL na linguagem de programação
escolhida.
Operandos SQL "Embebidos" na linguagem de app.
Neste módulo mostramos como fazer chamados sql
desde a linguagem Php
PostgreSQL e Php: transações
O Php é uma linguagem interpretada. “scripting language”
PHP, é a sigla de Hypertext Preprocessor“, Lerdorf 1995
Uma linguagem open-source para utilização geral, especialmente
adaptada para desenvolvimento Web
Pode ser embebida em HTML.
Uma linguagem que funciona no lado do servidor
Interpretada no web server com um processador
A sintaxe vem de C, Java, e Perl
O principal objetivo desta linguagem é permitir escrever páginas
web dinamicamente geradas
Manual. http://www.php.net/manual/en/index.php
PostgreSQL e Php: transações
scripting language: uma linguagem de
programação que permite o controlo de uma ou
mais aplicações
Os chamados a SQL são interpretados
previamente por um pre-processador
Consultar o Manual do PostreSQL: Programer’s
Guide Client Interfaces
Em php existem extensões do PostgreSQL .
Incluem sql embebido
Ver http://php.net/manual/en/book.pgsql.php /
PostgreSQL e Php: transações
No MySQL consultar:
Ler OBRIGATORIAMENTE
1) introdução:
2) Principais características:
http://dev.mysql.com/doc/refman/5.7/en/features.html
PostgreSQL e Php: transações
ANTES de utilizar a BD é preciso abrir. No fim
devemos fechar
Abrir o servidor de base de dados
<?php
//php embebido em html
include '/dir1/dir11/bibliotecas/biblio-1.php';
$db = dbconnect(); //ligar a bd de dcbm
if (!$db)
{ printf("<p>Erro ao abrir a Base de Dados.
Contactar o Administrador da BD :).</p> \n");
exit();
}
pg_close($db); // fechar a bd no fim
?>
PostgreSQL e Php: transações
<?php
function dbconnect()
//-------------------------{ $hostname= "localhost"; $db_name= “nome-bd";
$db_user = “postgres"; $user_psw= “lasdflakjasdfj";
$connection_string ="host=$hostname dbname = $db_name
user =$db_user password = $user_psw";
$db = pg_connect($connection_string); // abrir a BD PostgreSQL desde php
if (!$db)
{ printf("<p> Não há ligação com a base de
dados. Ensaiar mais tarde. </p>\n");
exit(); }
return $db;
}
?>
MySQL e Php: open DB
Criando uma nova conexão com a Base de Dados:
mysqli_connect(host, username, password, dbname)
//criar a conexão
$conexao=mysqli_connect("localhost","paula","ZyX123","minha_db");
// verificar a conexão
if (mysqli_connect_errno($conexao))
{
echo "Erro na conexão com a BD: " . mysqli_connect_error();
}
?>
BD: SEMPRE VERIFICAR SUCESSO DAS INSTRUÇÕES
MySQL e Php: close DB
Fechando uma conexão existente:
Várias BD
//criar a conexão
$conexao=mysqli_connect("localhost","paula","ZyX123","db1");
// verificar a conexão
Este PC
if (mysqli_connect_errno($conexao))
{
echo "Erro na conexão com a BD: " . mysqli_connect_error();
}
mysqli_close($conexao);
?>
//fechamos a BD
mysqli = instruções melhoradas para o php 5.0
PostgreSQL e Php: interrogar
$OK = pg_query ($db, $instruçao-sql);
Executa o query indicado na string $instruçao-sql
na base de dados indicada na ligação prévia a base de dados
representada aqui pela variável $db
Em caso de erro, devolve NULL (pode-se verificar sempre var $OK)
Pormenores do erro se podem encontrar na função
pg_last_error() se a ligação a bd era válida
O primeiro parâmetro é opcional, mas se recomenda incluir
Atenção: não utilizar acentos ou outros nos nomes das variáveis
PostgreSQL e Php: SELECT
…
if ($nif-tripulante)
{ $query_tripulante = "SELECT * FROM tripulante WHERE
nif = ‘$nif-tripulante‘ ";
$res = pg_query($conexao, $query_tripulante);
if ($res)
{ $c = pg_fetch_array($res);
echo $c[0] . " <- Nome\n";
echo $c[1] . " <- Morada\n";
}
else error …
pg_fetch_array — Carrega um tuplo como um array.
para referenciar os elementos do array,
podem-se utilizar índices ou os nomes dos atributos da tabela
PostgreSQL e Php: SELECT
function valida_chave($db, $tabela, $campo, $chave)
{ //-----------------------------------------------------------------$instr = "SELECT * FROM $tabela WHERE $campo = '$chave'
";
$id = pg_query($db, $instr);
$row = pg_fetch_row($id);
if ($row[0] != $chave)
return 0;
else return 1;
} //---------------------------------------------------------------------pg_fetch_row: semelhante a pg_fetch_array só que apenas refere
os elementos utilizando índices
Isto é, devolve um arranjo numerado com índices
PostgreSQL e Php: SELECT
//---------------------------------------------------------------function get_nome_disciplina($db, $id_disciplina)
{
$query = "SELECT * FROM grupo_curricular
WHERE id = '$id_disciplina' ";
$resultado = pg_query($db, $query);
if ($resultado)
{ $r = pg_fetch_array($resultado);
return $r['nome'];
}
else return NULL:
} //-------------------------------------------------------------------------
MySQL e Php: SELECT
<?php
$nome = 'laura'; $apelido = 'gomes';
$consulta = sprintf("SELECT nome, apelido, morada, idade FROM
aluno WHERE nome='%s' AND apelido='%s'",
mysql_real_escape_string($nome),
mysql_real_escape_string($apelido));
// Executar a pesquisa
$resultado = mysql_query($consulta);
<?php
mysql_real_escape_string() função da biblioteca de MySQL, que antepõe
barras invertidas aos seguintes caracteres: \x00, \n, \r, \, ', " e \x1a.
Se os dados a inserir são binários, sempre deve ser utilizada.
Mais segura para os dados enviados ao servidor para consultas
MySQL e Php: SELECT
// continuando
...
$resultado = mysql_query($consulta);
X
(mysql_query obsoleta desde o Php 5.5.0. Utilizar mysqli_query)
// Comprobar o resultado, utilizado para depurações
if (!$resultado)
{
$mensajem = 'Consulta não válida: ' . mysql_error() . "\n";
die($mensagem);
}
else $mensajem = 'Consulta feita: ' . $consulta;
...
Função die(): imprime uma mensagem e termina o script
MySQL_fetch_array()
// Utilizando os dados extraidos da BD:
//Utilizar alguma das funções de resultados do mysql
// Consultar: mysql_result(), mysql_fetch_array(), mysql_fetch_row(),
etc.
while ($fila = mysql_fetch_array($resultado, MYSQL-ASSOC)) {
echo $fila['nome'];
echo $fila['apelido'];
echo $fila['morada'];
echo $fila['idade']; }
// no fim liberamos os recursos utilizados,
mysql_free_result($resultado);
?>
mysql_fetch_array : um arranjo com os dados do query devolvidos em $
resultado.
Tipo resultado devolvido: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH.
MySQL_fetch_array()
<?php
mysql_connect("localhost", "user1l", "meu-passw") or //ligar ao server
die("Não foi possível conectar: " . mysql_error());
mysqli_select_db("minha-bd"); //ligar a bd que precisamos
$resultado = mysqli_query("SELECT id, nombre FROM mitabla");
while ($fila = mysql_fetch_array($resultado, MYSQL_NUM)) {
printf("ID: %s Nombre: %s", $fila[0], $fila[1]);
}
mysqli_free_result($resultado); //libera toda a memória de $resultado
?>
INSERIR tuplos dinamicamente: HTML
<html>
Iniciar um bloco de html
<head>
<title>DEEI-UALG</title>
<link rel="shortcut icon" href=“/deei/icon.ico" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<form name =“utilizador" action=“validar-user.php" method="POST"
enctype="application/x-www-form-urlencoded">
Nome Utilizador: <INPUT TYPE = "text" value="" name ="usuario” />
Password:
<INPUT TYPE = PASSWORD value ="" name="passwd” />
<p> <input type="submit" value ="Validar" name = "go"></p>
</form>
</html>
Após submit, HTML carrega todos os dados de <input> no array $_POST
Muito importante: POST e action: o nome da função php a executar
INSERIR tuplos dinamicamente
***** este é o programa validar-user.php
<?php …
$usuario = $_POST['usuario']; $passw = $_POST['passw'];
$nivel = $_POST['nivel']; $data = $_POST['data'];
date_default_timezone_set("Europe/Lisbon"); $data = date();
if (verifica_utilizador($db, $usuario)) //verifica se utilizador existe
show_error ("Já existe um usuario com este nome. Favor trocar por
um outro.",1);
$sqlcmd = "INSERT INTO utilizador ( usuario, passw, data) VALUES
('$usuario', '$passw', '$data')";
$OK = mysqli_query($db, $sqlcmd);
pg_close($db);
if (!$OK)
else
?>
//inserir registo na bd.
//após inserção, fechar a BD
show_error(1);
mandar_aviso("Novo Utilizador(a): $usuario - criado(a)", 4);
PostgreSQL e Php: UPDATE
SQL:
UPDATE utilizador
SET passw = ‘D67N9#%BB’, data = ‘11/1172011’,
WHERE usuario = “patricio”;
---------------------------Php ----------------------------------------$usuario = $_POST['usuario']; $passw1 = $_POST['passw1'];
$passw2 = $_POST['passw2'];
if ($passw1 != passw2)
show_error(“password incorrecto. Repetir sff",1);
$cmd = "UPDATE utilizador SET passw ='$passw1', WHERE
usuario = '$usuario' ";
If ( pg_query($db, $cmd)) echo ‘user alterado com sucesso’;
PostgreSQL e Php: APAGAR
SQL
DELETE FROM climatologia WHERE city = ’London’;
--------------------------Php ----------------------------------------------function apagar_registo($db, $bi)
{
$apagar = "DELETE FROM socio WHERE bi = '$bi'";
$OK = pg_query($db, $apagar);
if ($OK)
return("Registo sócio nº BI: $bi apagado. \n");
else
return(“erro: Registo socio nº BI: $bi NÃO
apagado\n");
Gestão de transações nas BD
Gestão de transações nas BD
Uma Base de Dados tem sempre acessos concorrentes: muitos
utilizadores fazendo insert, update, delete simultaneamente
Para cada utilizador, o SGBD abre um novo processo. Uma nova
sessão até este sair da BD. (fechar)
Cada operação que modifica a BD é uma transação
Como assegurar a integridade e coerência da BD?
Como assegurar a recuperação da BD quando há um crash?
Exemplos críticos: reservas bilhetes online, bancos, bolsa
Gestão de transações nas BD
Recuperação (recovery)
Fundamentalmente significa poder recuperar a BD a um estado
correcto após algum falhanço que deixa a BD num estado não
correcto
A chave da recuperação: a redundancia. Recuperamos a partir de
informações armazenadas previamente fazendo redundancia da
BD
A gestão das transações de qualquer BD, relacional ou não, exige
a capacidade de recuperação.
Gestão de transações na BD
Uma transação é uma sequência de operações SQL:
uma unidade lógica de trabalho
Esta sequência é tratada como um todo
Por defeito, uma instrução de SQL é uma transação
As transações obedecem as propriedades “ACID":
•
•
•
•
Atomicity
Consistency
Isolation
Durability
(Ver na introdução)
Gestão de transações na BD
Transações (comandos no MySQL)
START TRANSACTION [WITH CONSISTENT SNAPSHOT]
BEGIN [WORK] Inicia uma transação
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
Termina uma transação. BD actualizada. Mudança é permanente
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
Termina uma transação. BD NÃO actualizada. (“undo”)
SET autocommit = {0 | 1} Coloca o commit em “piloto automático”
Por defeito MySQL utiliza o modo 1: commit automatico
Gestão de transações na BD
O que se passa quando o acesso é concorrente e a um mesmo registo
da BD?
Por exemplo, a sua conta bancária:
Você anda a tirar 30€ da sua conta, mas o seu pai ainda não fez
depósito também na sua conta
Como deveríam acontecer as coisas? Qual transação primeiro?
A conta do seu pai é actualizada depois de lhe depositar os 30€
Que se passa se a sua irmã sabe a sua conta e está intentar retirar 20€
no mesmo momento que você pretende levantar os 30€ que precisa?
Que se passa se a BD do banco “crasha” no meio disto tudo?
Gestão de transações na BD
Uma transação pode envolver uma sequência de operações na BD
Por exemplo, você quer transferir 100€ da conta 120 para a conta 350
O propósito final do transaction manager da BD e transformar o estado
correcto da BD antes da transação, num outro estado correcto da BD após
a operação
Em pseudo código queremos algo assim: –->
Gestão de transações na BD
BEGIN TRANSACTION
1) UPDATE conta 120. saldo-final = saldo-final – 100;
Se operação não funcionar ir para UNDO;
2) UPDATE conta 350. saldo-final = saldo-final + 100;
Se operação não funcionar ir para UNDO;
COMMIT
Ir para TERMINAR;
UNDO:
ROLLBACK;
TERMINAR:
RETURN;
Gestão de transações na BD
BEGIN TRANSACTION
1) UPDATE conta 120. saldo-final = saldo-final – 100;
Se operação não funcionar ir para UNDO;
2) UPDATE conta 350. saldo-final = saldo-final + 100;
Se operação não funcionar ir para UNDO;
COMMIT
Ir para TERMINAR;
UNDO:
ROLLBACK;
TERMINAR:
RETURN;
O objectivo como se pode ver, é claramente não permitir
efectuar só um update. Ou os dois são feitos, ou nada é feito.
Caso contrário a BD ficava num estado incorrecto
Esta é a propriedade de atomicidade das transações
Gestão de transações na BD
A operação COMMIT representa uma transação feita com
sucesso. A BD está agora novamente num estado correcto.
A operação ROLLBACK representa o contrário. Algo correu
mal e a BD poderia estar num estado incerto
Como é possível fazer desfazer um update, ou refazer a
operação iniciada se no meio o sistema falhou?
LOG RECORD: o sistema leva conta sequencialmente de
todas as transações dum periodo de tempo.(before-after images)
Quando o sistema recuperar, pode desfazer ou refazer as
operações que ficaram pendentes
Gestão de transações na BD
Atomicidade:
Sistema deve garantizar execução de atomicidade de instruções
individuais. Especialmente no modelo relacional onde as
operações são feitas a nível de vários tuplos de uma vez
A operação não pode falhar no meio e deixar incorrecta a BD:
uns tuplos actualizadas e outros não
Ou tudo feito ou nada feito
Gestão de transações na BD
Recovery (Recuperação)
Uma transação não só representa uma unidade de trabalho: representa
igualmente uma unidade de recuperação
O SGBD deve garantizar poder recuperar a BD no seu estado
correcto original após um falhanço no meio duma transação
Update primeiro feito nos buffers da RAM. O sistema garantiza
que após COMMIT vai actualizar fisicamente as tabelas envolvidas
Portanto se houver crash, não é necessário fzer UNDO
Caso houver um crash após COMMIT e antes de actualizar
fisicamente a BD, como recuperar
LOG FILE e write-ahead log rule: Primeiro as transações são
escritas no ficheiro log. Logo começa a transação.
Este log file permete refazer operações pendentes após o crash
Gestão de transações na BD
Dois tipos de categorias de falhanço do sistema:
Falhas do sistema afectando todas as transações em progresso
(falta energia por exemplo): soft crash
Falhas dos recursos (falha do disco duro). Hard crash
Destroi parcial ou totalmente a BD
Em ambos os casos os conteúdos da RAM perdem-se: os
buffers utilizados pelo SGBD
Neste contexto, quais transações UNDO?
Quais voltar a fazer?
O SGBD utiliza checkpoint records: a cada intervalo após
algumas transações feitas, o sistema faz uma marca no tempo
para indicar que até alí, tudo bem
Gestão de transações na BD
Tempo
tr
a
n
s
a
ç
o
e
s
checkpoint
falha sistema
1
2
3
4
5
Como resolve o sistema o problema destas transações após restart?
Está claro que transações 3 e 5 tem que ser UNDO
Transações 2 e 4 devem ser refeitas novamente (buffers da RAM)
Tomada do livro do J.C. Date
Gestão de transações na BD
O sistema funciona com 2 listas: UNDO e REDO
Lista UNDO igual a todas as transações do ultimo checkpoint
Lista REDO vazia
Começar verificar o sistema após o checkpoint
Se o registo de log encontra BEGIN TRANSACTION coloque esta
transação na lista de UNDO
Se o registo de log encontra uma transação COMMIT, mover a
transação da lista de UNDO para a lista de REDO
Quando terminar verificar o registo de log, as listas identificam
transações 3 e 5 na lista de UNDO e 2 e 4 na lista de REDO
Gestão de transações na BD
Numa operação de inserção fazer LOCK na tabela:
BEGIN WORK;
LOCK TABLE tripulante IN SHARE MODE;
SELECT id_tripulante FROM tripulante
WHERE nome = ’Alberto Martins Lopes’;
INSERT INTO tripulante VALUES ( _id_ , ’914563312’);
COMMIT WORK;
Isolar a tabela com a chave primária no modo SHARE, quando se
pretende actualizar uma tabela onde ela é chave estrangeira
Atenção: no standard SQl não há lock table  set transaction
Gestão de transações na BD
Numa operação de apagar fazer LOCK na tabela com a
chave primária:
BEGIN WORK;
LOCK TABLE casa IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM descricao_casa
WHERE id IN (SELECT id FROM casa WHERE nivel < 5);
DELETE FROM casa WHERE nivel < 5;
COMMIT WORK;
Gestão de transações na BD
Exemplo:
Na conta do seu pai num depósito de 30€ para sim, o banco terá as
instruções:
BEGIN WORK;
UPDATE conta
SET saldo = saldo - 30
WHERE numero = 3334712;
//a conta do seu pai
UPDATE conta
SET saldo = saldo + 30
WHERE numero = 8885990;
// a sua conta
COMMIT WORK;
Se algo correr mal, o dinheiro não será posto na sua conta, mas
também não será levantado da conta do seu pai
Gestão de transações na BD
Os utilizadores (programas) só vem resultados de transações que
fizeram COMMIT
•E que acontece se no meio da transação do seu pai, a sua mãe que
também tem acesso a conta do seu pai fazer uma transação e o
programa de aplicação no processo dela utiliza igualmente o
COMMIT?
• SQL tem 4 níveis de isolamento das transações:
1. SERIALIZABLE
2. REPEATABLE READ
3. READ COMMITTED
4. READ UNCOMMITTED
Compete ao programador especificar o nível de isolamento pretendido
Gestão de transações na BD
Nível:
1.SERIALIZABLE
2. REPEATABLE READ
3. READ COMMITTED
4. READ UNCOMMITTED
Escrevemos:
SET TRANSACTION ISOLATION LEVEL <nível>
Gestão de transações na BD
SERIALIZABLE
Se o programa da sessão do seu pai está a correr com o nível de
isolamento SERIALIZABLE, só vai ver o seu saldo final
depois a sessão da sua mãe que entrou pelo meio terminar pela
sua vez.
No meio delas não vai ver nada diferente do estado anterior da
sua conta antes do inicio destas duas transações feitas por dois
processos concorrentes
Gestão de transações na BD
READ COMMITTED
Neste caso, é possível ver o estado ‘parcial’ da conta após a
primeira destas transações serem tratadas
REPETEABLE READ
semelhante ao anterior
a diferencia é que se um tuplo é lido uma vez,
então terá de ser forçosamente devolvido se a leitura for repetida
Gestão de transações na BD
READ UNCOMMITTED
Se a transação utilizar esta opção então pode ler os dados
transitórios que possam ter sido escritos por outras transações
Por exemplo, poderá ler um saldo inferior na conta, mesmo se a
transação concorrente que levantava dinheiro “crashar” no meio e
a aplicação fazer um ROLLBACK
Gestão de transações na BD
No caso do PostgreSQL só existem as opções
SERIALIZABLE e READ COMMITTED
SERIALIZABLE Oferece um maior nível de isolamento.
Faz como se as transações tivessem sido feitas sequencialmente e
não concorrentemente.
READ COMMITTED dá menos isolamento, mas é mais eficiente
de implementar pelo SGBD. Pretende manter a consistência dos
dados
Ler sobre concurrency control no manual do PostreSQL
Gestão de transações
fim do módulo