Bases de Dados 2012/2013 Desenvolvimento de Aplicações com Bases de Dados. Helena Galhardas 2012 IST. Bibliografia

(1)

Bases de Dados 2012/2013

Desenvolvimento de Aplicações com

Bases de Dados

Helena Galhardas

Bibliografia

•  Raghu Ramakrishnan,

Database Management Systems 3rd ed, Cap. 6 e 7

(2)

Sumário

•  Acesso a BD a partir de aplicações •  Embedded SQL / Dynamic SQL •  JDBC / SQLJ

•  Stored procedures

–  Já vimos

•  Aplicações Web: a arquitectura de 3 camadas

–  Acesso a BD Postgres via PHP

2

SQL em Aplicações

•  Os comandos SQL podem ser chamados através de uma aplicação informática

– Escrita numa qualquer linguagem

(3)

Adaptação de Dados

•  A saída de uma interrogação SQL é um conjunto de tuplos

•  Este tipo de dados normalmente não existe nas linguagens de programação imperativas.

•  Integração com SQL usa o cursor como mecanismo de adaptação. Permite iterar sobre os tuplos de um resultado.

Duas abordagem de integração

Estática: SQL embebido na linguagem

•  Embedded SQL

•  SQLJ, uma extensão ao Java

Dinâmica: API para invocar comandos SQL

•  Dynamic SQL

(4)

Embedded SQL (na linguagem C)

•  Ligação à base de dados:

EXEC SQL CONNECT!

•  Declaração de variáveis:

EXEC SQL BEGIN (END) DECLARE SECTION!

•  Comandos

EXEC SQL Statement!

Variáveis

EXEC SQL BEGIN DECLARE SECTION!

!char c_sname[20];! !long c_sid;!

!short c_rating;! !float c_age;!

EXEC SQL END DECLARE SECTION!

•  Variáveis na linguagem hospedeira devem ser prefixados por “:” em instruções SQL

•  Variáveis de erro (pré-definidas):

•  SQLCODE (long, negativo se existir um erro) •  SQLSTATE (char[6], indica o tipo do erro )

(5)

Comandos

•  Comandos EXEC SQL Statement! •  Exemplo: EXEC SQL !

INSERT INTO Sailors VALUES

(:c_sname, :c_sid, :c_rating, :c_age)!

Cursores

1.  Declaração 2.  Abertura

3.  Operação sobre o tuplo para onde aponta 4.  Deslocação do cursor para o tuplo seguinte 5.  Fecho

EXEC SQL DECLARE sinfo CURSOR FOR!

!SELECT S.sname!

!FROM Sailors S, Boats B, Reserves R! !WHERE S.sid=R.sid AND R.bid=B.bid ! !AND B.color=‘red’!

(6)

Exemplo em C

char SQLSTATE[6];!

EXEC SQL BEGIN DECLARE SECTION!

!char c_sname[20]; short c_minrating; float c_age;! EXEC SQL END DECLARE SECTION!

c_minrating = random();!

EXEC SQL DECLARE sinfo CURSOR FOR! !SELECT S.sname, S.age!FROM Sailors S! !WHERE S.rating > :c_minrating!

!ORDER BY S.sname;! EXEC SQL OPEN sinfo;! do {!

!! !EXEC SQL FETCH sinfo INTO :c_sname, :c_age;! !! !printf(“%s is %d years old\n”, c_sname, c_age);! } while (SQLSTATE != ‘02000’);!

EXEC SQL CLOSE sinfo;!

Dynamic SQL

char c_sqlstring[]= 

{“DELETE FROM Sailors WHERE rating>5”};!

EXEC SQL PREPARE readytogo FROM :c_sqlstring;!

EXEC SQL EXECUTE readytogo;!

• Construção de instruções SQL em tempo

(7)

Combinação Java + SQL

Estática •  SQLJ Dinâmica •  JDBC - Java Database Connectivity 12

Sumário

•  Acesso a BD a partir de aplicações •  Embedded SQL / Dynamic SQL

•  JDBC / SQLJ

•  Stored procedures –  Já vimos

•  Aplicações Web: a arquitectura de 3 camadas

(8)

JDBC

•  Percursor: Open Database Connectivity (API Microsoft Office)

•  Dinâmica: acesso a Servidor de BD a partir de uma API (Application Program Interface)

–  Permite alterar BD acedida em tempo de execução

–  Permite acesso simultâneo a várias BD

•  Import java.sql!

14

Componentes de

Arquitectura JDBC

•  Aplicação

–  Inicia e termina ligações –  Submete comandos SQL •  Driver Manager

–  Gere o registo dos drivers

–  Escolhe e abre o driver respectivo

–  Passa as chamadas a funções JDBC da aplicação para o driver •  Driver

–  Programa de SW que traduz as chamadas JDBC em chamadas específicas do SGBD

–  Carregados dinamicamente –  Comunica com a Fonte de Dados •  Fonte de dados (Data source)

(9)

Tipos de drivers

•  Bridge

– Independente do SGDB

– Exemplo: JDBC-ODBC driver

•  Direct translation to native API

– Driver específico do SGDB

– Pode ser non-Java ou Java driver

•  Network bridge

– Usa um intermediário na internet

•  Middleware server

Gestão do JDBC Driver

•  Através da classe DriverManager!

– Mantém uma lista de todos os drivers carregados – Código para carregar um driver:

Class.forName

(“oraclejdbc.driver.Oracledriver”);!

– Na chamada da aplicação:

(10)

Ligação ao SGDB

String url=“jdbc:oracle:www.bookstore.com:3083”;! Connection con;! try{! !con = DriverManager.getConnection (url,usedId,password);!

} catch SQLException excpt { …}!

Interface da Classe Connection!

•  Exemplos:

–  boolean getReadOnly()! –  void setReadOnly(boolean b)! –  boolean getAutoCommit()! –  void setAutoCommit(boolean b)! –  boolean isClosed()

(11)

Execução de comandos SQL

Três formas: •  Statement !

–  Permite interrogar a BD com instruções SQL estáticas e dinâmicas

•  PreparedStatement !

–  Gera dinamicamente instruções SQL

pré-compiladas que podem ser usadas várias vezes –  Estrutura fixa, mas podem ter parâmetros

•  CallableStatment !

–  stored procedures

Exemplo

String sql=“INSERT INTO Sailors VALUES (?,?,?,?)”;! PreparedStatment pstmt=con.prepareStatement(sql);! pstmt.clearParameters();! pstmt.setInt(1,sid);! pstmt.setString(2,sname);! pstmt.setInt(3, rating);! pstmt.setFloat(4,age);!

// we know that no rows are returned, ! //thus we use executeUpdate()!

(12)

Processamento do Resultado

Classe ResultSet

•  PreparedStatement.executeUpdate !

– Devolve o número de linhas afectadas

•  PreparedStatement.executeQuery

– Devolve os dados encapsulados num objecto

ResultSet (um cursor)

ResultSet rs = pstmt.executeQuery(sql);! // rs is now a cursor!

While (rs.next()) {! // process the data! }!

ResultSet

•  previous()!

– desloca-se uma linha para trás

•  absolute(int num)!

– desloca-se para uma determinada linha

•  relative (int num)!

– desloca-se para a frente ou para trás relativamente à posição corrente

(13)

Tipos de Dados: Java e SQL

JDBC: Exceptions and Warnings

•  A maior parte do java.sql pode gerar uma

SQLException!

•  SQLWarning é uma subclasse do SQLException!

(14)

Exemplo

try {! stmt=con.createStatement();! warning=con.getWarnings();! while(warning != null) {! // handle SQLWarnings;! warning = warning.getNextWarning():! }! con.clearWarnings();! stmt.executeUpdate(queryString);! warning = con.getWarnings();! …! } //end try!

catch( SQLException SQLe) {! // handle the exception! }!

Meta-dados da Base de Dados

DatabaseMetaData md =

con.getMetaData();!

// print information about the driver:!

System.out.println( 

“Name:” + md.getDriverName() +  “version: ” +

(15)

classe GetMetaData

DatabaseMetaData md=con.getMetaData();! ResultSet trs=md.getTables(null,null,null,null);! String tableName;! While(trs.next()) {! tableName = trs.getString(“TABLE_NAME”);! System.out.println(“Table: “ + tableName);! //print all attributes!

ResultSet crs = md.getColumns(null,null,tableName, ! ! !null);! while (crs.next()) {! System.out.println(crs.getString(“COLUMN_NAME” + ! ! ! !“, “);! }! }!

Um Exemplo

Connection con = // connect!

DriverManager.getConnection(url, ”login", ”pass"); ! Statement stmt = con.createStatement(); // set up stmt!

String query = "SELECT name, rating FROM Sailors";!

ResultSet rs = stmt.executeQuery(query);!

try { // handle exceptions!

// loop through result tuples! while (rs.next()) {! String s = rs.getString(“name");! Int n = rs.getFloat(“rating");! System.out.println(s + " " + n);! }! } catch(SQLException ex) {! System.out.println(ex.getMessage ()! + ex.getSQLState () + ex.getErrorCode ());! }!

(16)

SQLJ

•  “Embedded SQL para Java”

•  Código de aplicações é pré-processado: instruções SQLJ convertidas em código fonte java com invocações a API de biblioteca

–  #sql …

•  “binding” estático de variáveis do Java a variáveis SQL

•  Código mais curto e mais legível. •  Menos flexibilidade que JDBC.

30

Código SQLJ

String title; Float price; String author;!

#sql iterator Books(String title, Float price);!

// app define author, executar query e abrir cursor!

#sql books= { SELECT title, price INTO :title, :price !FROM Books WHERE author = :author!

};! // obter resultados! while (books.next())! {! !System,out.println(books.title() + “,” + ! ! ! !books.price());! }!

(17)

Stored Procedure

•  Programa executado através de um único comando SQL

•  Executado no SGDB •  Vantagens:

– Lógica da aplicação encapsulada e mais perto dos dados

– Reutilização por várias aplicações

– Evita a iteração sobre cursores através da rede

Execução de Stored Procedures

JDBC: CallableStatement cstmt=  con.prepareCall(“{call ShowSailors}”);! ResultSet rs = cstmt.executeQuery();! while (rs.next()) {! …! }!

(18)

APLICAÇÕES NA INTERNET

Protocolo HTTP

•  É um protocolo cliente-servidor:

– Cliente (e.g. Web browser) envia pedido para o servidor HTTP

•  URI (Uniform Resource Identifiers)

–  http://www.di.fc.ul.pt/index.php

(19)

Exemplo de Pedido

GET index.html HTTP/1.1 User-agent: Mozilla/4.0

Accept: text/html, image/gif, image/jpeg

Exemplo de Resposta

HTTP/l.l 200 OK!

Date: Mon, 04 Mar 2002 12:00:00 GMT! Content-Length: 1024!

Content-Type: text/html!

Last-Modified: Mall, 22 JUIl 1998 09:23:24 GMT! <HTML>!

<HEAD>! </HEAD>! <BODY>!

!<H1>Barns and Nobble Internet Bookstore</H1>! !Our inventory:!

!<H3>Science</H3>!

!<B>The Character of Physical Law</B>! </BODY>!

(20)

HTML

•  É uma markup language porque anota o texto com etiquetas (tags):

– <HTML> … </HTML> – <TITLE> … </TITLE> – <H3> … </H3>

•  Web Browsers apresentam o conteúdo do de uma forma "gráfica" ao utilizador

(21)

Componentes

•  Camada de Apresentação –  Web Browser •  JavaScript, HTML, etc •  Camada Intermédia –  Servidor Web (HTTP) •  Apache

•  Executa aplicações (PHP, Java) e mantém estado

•  Camada de Gestão de Dados

–  SGBD •  Oracle, MySQL

Distribuição Física

PHP, ASP ODBC SGBD SQL HTML, XML Web Browsers JavaScript

(22)

Vantagens de usar 3 camadas

•  Independência entre camadas

–  Pode-se trocar o software de uma camada sem afectar outra camada

•  Clientes não consomem muitos recursos (thin clients)

–  Só interpretam HTML; tipicamente Web browsers

•  Escalabilidade para muitos Clientes

–  Podem-se usar várias máquinas como servidor

•  Desenvolvimento mais fácil

–  Mais fácil a reutilização de componentes já implementadas

APLICAÇÕES INTERNET COM

PHP

(23)

Bibliografia

•  Tutorial PHP – http://www.w3schools.com/ •  Manual de PHP – http://www.php.net/manual/pt_BR/index.php

•  Manual do conetor Postgres para PHP

– http://www.php.net/manual/en/book.pgsql.php – http://www.php.net/manual/pt_BR/

book.pgsql.php

44

PHP: PHP Hypertext Preprocessor

•  Linguagem de server-side scripting •  Concebida para geração dinâmica de

páginas Web

•  É executada em muitas plataformas •  Integrada com vários servidores Web •  Suporte para muitos SGBDs

•  Possui um vasto conjunto de extensões •  Software livre

(24)

Web, PHP

46 Cliente 1 (PHP) Cliente 2 (PHP) Clientes Web Ligações HTTP Ligações HTTP

Arquitectura

Browser Servidor Web Sistema de Ficheiros Pedido HTTP X.HTML Resposta HTTP (conteúdo de X) Ler ficheiro X.html Conteúdo de X (HTML) 1 2 4 3

(25)

Arquitectura

Browser Servidor Web PHP Sistema de Ficheiros pedido HTTP resposta HTTP (conteúdo HTML)

extensão *.php ficheiro *.php código PHP HTML 1 2 3 4 5 6 48 Servidor WWW (Apache)

Web, PHP e Postgres

SGBD (Servidor Postgres) BD1 BD2 Cliente 1 (PHP) Cliente 2 (PHP) Ligação 1 Ligação 2 sql sql Clientes Web Ligações HTTP Ligações HTTP

(26)

IST ▪ DEI ▪ Bases de Dados

Arquitectura

Browser Servidor Web PHP SGBD Sistema de Ficheiros pedido HTTP resposta HTTP (HTML) extensão *.php ficheiro *.php código PHP queries SQL HTML dados 1 2 3 4 5 6 7 8 50

Geração Página PHP

HTML PHP HTML SGBD variáveis Servidor Web HTML HTML HTML Cliente URL h_p://host.domain/path?var1=val1 …

(27)

<html> <head> <title>PHP Test</title> </head> <body> <?php echo('<p>Hello World!</p>'); ?> </body> </html>

"Hello World" em PHP

52

Acesso ao SGDB

Segundo Modelo Cliente-Servidor

SGBD (Servidor Postgres) BD1 BD2 Cliente 1 (PHP) Cliente 2 (PHP) Ligação 1 Ligação 2 sql sql

(28)

Servidor WWW (Apache)

3 Camadas: Web, PHP e Postgres

54 SGBD (Servidor Postgres) BD1 BD2 Cliente 1 (PHP) Cliente 2 (PHP) Ligação 1 Ligação 2 sql sql Clientes Web Ligações HTTP Ligações HTTP

PHP e Postgres

1. Abrir uma ligação à BD

$user = "istxxxxxx"; /* username sigma */

$host = "db.ist.utl.pt"; $port = 5432;

$password = "xxxxxxxx"; /* password psql_reset */ $dbname = $user; /* nome da BD = nome user */ $connection = pg_connect("host=$host port=$port user=

$user password=$password dbname=$dbname")

(29)

PHP e Postgres

2. Fazer uma consulta (i.e., abrir um cursor)

$sql = "select * from customer";

$result = pg_query($sql) or die(pg_last_error());

56

PHP e Postgres

3. Determinar o número de colunas e de registos no cursor (opcional)

echo("No. columns: " . pg_num_fields($result) . "<br/>"); echo("No. records: " . pg_num_rows($result) . "<br/>");

(30)

PHP e Postgres

4. Iterar pelos resultados com fetch

echo("<table>"); while($row_array = pg_fetch_assoc($result)) { echo("<tr>"); echo("<td>{$row_array['customer_name']}</td>"); echo("<td>{$row_array['customer_street']}</td>"); echo("<td>{$row_array['customer_city']}</td>"); echo("</tr>"); } echo("</table>"); 58

PHP e Postgres

5. Libertar os resultados e Fechar a ligação

$result = pg_free_result($result) or die('ERROR: ' . pg_last_error()); pg_close($connection);

(31)

Consultas e modificações à BD

$result = pg_query($query)

or die("Could not execute query.");

•  Para perguntas que devolvem um resultado

–  $result é um apontador para os dados

•  Para consultas que não devolvem um resultado

–  TRUE ou FALSE, conforme sucesso da operação

–  pg_last_error() permite determinar último erro

60

Acesso aos resultados – exemplo

<table border=1>

<tr><td><b>Name</b></td><td><b>Street</b></td> <td><b>City</b></td></tr>

<?php

$sql = "select * from customer";

$result = pg_query($sql) or die(pg_last_error());

echo("N. of results: " . pg_num_rows($result) . "<br/>"); echo("N. of columns: " . pg_num_fields($result) . "<br/>"); while($row_array = pg_fetch_assoc($result)) { echo("<tr>"); echo("<td>{$row_array['customer_name']}</td>"); echo("<td>{$row_array['customer_street']}</td>"); echo("<td>{$row_array['customer_city']}</td>"); echo("</tr>"; } ?> </table>

(32)

Sumário

•  Desenvolvimento de Aplicações de BD •  Próxima aula: Dependências funcionais e

Normalização

Bases de Dados 2012/2013 Desenvolvimento de Aplicações com Bases de Dados. Helena Galhardas 2012 IST. Bibliografia