Fabiano Arndt

Nesse vídeo mostro um exemplo de como criar um banco de dados simples com o Arduino, utilizando Ethernet Shield com SD Card, utilizando a biblioteca EDB (Extended Database Library). os arquivos html são salvos no cartão de memória.

Os dados são retornados para o browser através de javascript no formato JSON.

Baixar a biblioteca EDB

https://code.google.com/p/arduino-edb/downloads/list

Depois de instalada a biblioteca acima, é preciso corrigir um bug que existe no código-fonte da biblioteca. abra o arquivo EDB.cpp e modifique a seguinte função, incluindo a linha sinalizada pelo +:

EDB_Status EDB::open(unsigned long head_ptr)

{

EDB_head_ptr = head_ptr;

+ EDB_table_ptr = sizeof(EDB_Header) + EDB_head_ptr;

readHead();

return EDB_OK;

}

modifique também a parte onde está

#include "WProgram.h"
#include <EDB.h>

para

#include "Arduino.h"
#include <EDB.h>

Salve os arquivos HTML abaixo na raiz do cartão de memória

arquivo aut.htm

HTTP/1.1 401 Authorization Required
WWW-Authenticate: Basic realm="Area Restrita"
Content-Type: text/html
Connnection: close

<!DOCTYPE HTML>\n
<html><head><title>Error</title>
</head><body><h1>401 Acesso nao autorizado</h1></body></html>

arquivo cab.htm

HTTP/1.1 200 OK

Content-Type: text/html

Connection: keep-alive

arquivo logoff.htm:

HTTP/1.1 401 Authorization Required

Content-Type: text/html

Connnection: close

<!DOCTYPE HTML>

<html><head><title>Logoff</title>

</head><body><h1>Você não está mais conectado</h1></body></html>

arquivo plist.htm:

<!DOCTYPE html>

<html>

<head>

tr, td {

border-bottom: 1px solid black;

padding: 5px;

margin: 0px;

}

label{

position:absolute;

left:19px;

margin-right:5px;

}

body{font-family: arial}

h1 {color: #FF0000;}

</style>

function init(){

if (document.URL.indexOf("insert_proc") > -1){ window.location = '/'; }

if (document.URL.indexOf("del_proc") > -1) { window.location = '/';}

var obj = JSON.parse(json_proc);

var html = '<table>';

for(var i=0; i<obj.processos.length;i++){

html += '<tr>' +

'<td>' + obj.processos[i].id_proc + '</td>' +

'<td>' + obj.processos[i].proc + '</td>' +

'<td>' + '<a href="del_proc?p='+obj.processos[i].id_proc+'" onclick=\"return confirm(\'Deseja realmente excluir?\');\">Excluir</a></td>' +

'</tr>';

}

html += '</table>';

document.getElementById("lproc").innerHTML = html;

}

</script>

</head>

<div><a href="/">Home</a> | <a href="/logoff" onclick="return confirm('Deseja realmente sair?');">Logoff</a></div>

<br>

Nome Processo:

</form>

<br>

</body>

</html>

código-fonte (sketch)

/**********************************************************************************
************************************BIBLIOTECAS************************************
**********************************************************************************/
#include <SPI.h>
#include <Ethernet.h>
#include <SD.h>
#include <EDB.h>
/**********************************************************************************
********************************FIM BIBLIOTECAS************************************
**********************************************************************************/

/****************************************************************************************
****************************DECLARACAO DAS TABELAS INICIO*******************************
****************************************************************************************/
#define TABLE_SIZE_PROC 512
#define TABLE_NAME_PROC "p.db"

EDB edb_proc(&writer_proc, &reader_proc);

/******************STRUCTS DAS TABELAS***************************************************/
struct Struct_proc {
int id_proc;
char proc[15];
} struct_proc;

/******************INICIALIZA BANCO******************************************************/

void iniciar_banco_dados(){
char * f = TABLE_NAME_PROC;
File file_proc;
if (SD.exists(f)){
file_proc = SD.open(f, FILE_WRITE);
edb_proc.open(0);
} else {
file_proc = SD.open(f, FILE_WRITE);
edb_proc.create(0, TABLE_SIZE_PROC, sizeof(struct_proc));
}
file_proc.close();
}

/******************WRITER/READER PROC******************************************************/
void writer_proc (unsigned long address, byte data) {
File file_proc;
file_proc = SD.open(TABLE_NAME_PROC, FILE_WRITE);
file_proc.seek(address);
file_proc.write(data);
file_proc.flush();
file_proc.close();
}

byte reader_proc (unsigned long address) {
File file_proc;
file_proc = SD.open(TABLE_NAME_PROC, FILE_WRITE);
file_proc.seek(address);
byte b = file_proc.read();
file_proc.close();
return b;
}

/*********operacoes de inclusao/alteracao/leitura/exclusão processos******************/
void edb_insert_proc(int id_proc, char * proc) {
struct_proc.id_proc = id_proc;
strcpy( struct_proc.proc, proc );
edb_proc.appendRec(EDB_REC struct_proc);
}

void edb_delete_proc(int id){
for (int recno=1; recno <= edb_proc.count(); recno++) {
edb_proc.readRec(recno, EDB_REC struct_proc);
if (struct_proc.id_proc == id) {
edb_proc.deleteRec(recno);
break;
}
}
}

void edb_update_proc(int id_proc, char * proc){
struct_proc.id_proc = id_proc;
strcpy( struct_proc.proc, proc );
edb_proc.updateRec(id_proc, EDB_REC struct_proc);
}

void edb_read_proc(unsigned long recno_proc){
edb_proc.readRec(recno_proc, EDB_REC struct_proc);
}

int edb_max_proc_id() {
int max=0;
Struct_proc s;
for (int recno=1; recno <= edb_proc.count(); recno++) {
edb_proc.readRec(recno, EDB_REC s);

if (s.id_proc > max) { max = s.id_proc; }
}
return max;
}

/****************************************************************************************
****************************DECLARACAO DAS TABELAS FIM**********************************
****************************************************************************************/

/****************************************************************************************
****************************SD CARD INICIO***********************************************
****************************************************************************************/

boolean iniciar_sd_card() {
pinMode(10, OUTPUT);
digitalWrite(10, HIGH);

if (!SD.begin(4)) { return false; }

return true;
}

/****************************************************************************************
****************************SD CARD FIM**************************************************
****************************************************************************************/

/**********************************************************************************
*************************ROTINAS USUARIO E SENHA***********************************
***********************************************************************************/

boolean validar_usuario(char * linebuf) {
/*
nesse exemplo o usuario e senha estão definidos dentro do código fonte.
mas o usuário e senha poderiam ser autenticados de diversas maneiras,
lendo os dados de um servidor web, arquivo texto, etc, bastando apenas atribuir
o valor lido para a variável usuario_senha.
*/

char usuario_senha[] = "admin:admin";
int t = strlen(usuario_senha);

int tamanhoEnc = (((t-1) / 3) + 1) * 4; //tamanho da string codificada
char out[tamanhoEnc];
base64_encode(out, usuario_senha, t+1 );

//---desconta é usado pra eliminar os caracteres '='
int desconta = 0;
if ((t%3) == 1) { desconta = 2; }
if ((t%3) == 2) { desconta = 1; }

char out2[tamanhoEnc-desconta];

byte i;
for (i=0; i<(tamanhoEnc-desconta);i++){ out2[i] = out[i]; }
out2[i] = '\0';

return ( strstr(linebuf, out2)>0 );
}

/**********************************************************************************
*************************FIM ROTINA USUARIO E SENHA********************************
***********************************************************************************/

/**********************************************************************************
***********************************PAGINAS HTML************************************
***********************************************************************************/
EthernetServer * server;

void iniciar_ethernet(){
byte ip[4] = {192,168,200,188};
//byte gateway[4] = {192,168,200,254};
//byte subnet[4] = {255,255,255,0};
byte mac[6] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED};
int porta = 80;

server = new EthernetServer(porta);

//Ethernet.begin(mac, ip, gateway, subnet); //caso necessario gateway utilizar essa linha
Ethernet.begin(mac, ip);
server->begin();
}

void write_from_file(EthernetClient &client, char * file_name){
File webFile = SD.open(file_name);
if (webFile) {
while(webFile.available()) {
client.write(webFile.read());
}
webFile.close();
}
}

void html_cab_200_ok(EthernetClient &client){
client.println(F("HTTP/1.1 200 OK\n"
"Content-Type: text/html\n"
"Connection: keep-alive\n\n"));
}

void html_logoff(EthernetClient &client){
write_from_file(client, "logoff.htm");
}

void html_autenticar(EthernetClient &client) {
write_from_file(client, "aut.htm");
}

void html_proc_list(EthernetClient &client) {
html_cab_200_ok(client);
write_from_file(client, "plist.htm");
}

void js_proc_list(EthernetClient &client){
//cabecalho para javascript
client.println(F("HTTP/1.1 200 OK\n"
"Content-Type: text/javascript\n"
"Connection: keep-alive\n\n"));

client.print("var json_proc=\'{\"processos\":[");
char virgula = ' ';
for (int recno = 1; recno <= edb_proc.count(); recno++) {
edb_proc.readRec(recno, EDB_REC struct_proc);

client.print(virgula);
client.print("{\"id_proc\":\"");
client.print(struct_proc.id_proc);
client.print("\", \"proc\":\"");
client.print(struct_proc.proc);
client.print("\"}");

virgula = ',';
}

client.print("]}\';");
}

void delete_proc(char * linebuf) {
char * p = strstr(linebuf, "p="); // /GET ?p=123
char r[5] = {'\0'};

if (p) {
byte i=0;
while (p[i+2] >= '0' && p[i+2] <= '9') {
r[i] = p[i+2];
i++;
if (i==4) {break;}
}
r[i]='\0';
}

if (r[0] >= 48 && r[0] <= 57) { //48->0 57->9
edb_delete_proc(atoi(r)); //apaga o registro
}
}

void insert_proc(char * linebuf) {
char * p = strstr(linebuf, "p="); // /GET ?p=123
char r[16] = {'\0'};

if (p) {
byte i=0;
while (p[i+2] != ' ') {
r[i] = (p[i+2] == '+') ? r[i] = ' ' : r[i] = p[i+2];
i++;
if (i==15) {break;}
}
r[i]='\0';
}

edb_insert_proc(edb_max_proc_id()+1, r); //inclui registro
}

void exec_ethernet(){
EthernetClient client = server->available();
if (client) {
char linebuf[80];
memset(linebuf, 0, sizeof(linebuf));

int charCount = 0;
boolean autenticado = false;
boolean currentLineIsBlank = true;
boolean logoff = false;
boolean jsProcList = false;
boolean listProc = false;

while (client.connected()) {
if (client.available()) {
char c = client.read();

linebuf[charCount] = c;

if (charCount<sizeof(linebuf)-1) { charCount++; }
Serial.write(c);

if (c == '\n' && currentLineIsBlank) {
if (autenticado && !logoff ) {
if(jsProcList) { js_proc_list(client); }
if(listProc) { html_proc_list(client); }
} else {
logoff ? html_logoff(client) : html_autenticar(client);
}
break;
}
if (c == '\n') {
currentLineIsBlank = true;

if (strstr(linebuf, "Authorization: Basic")>0 ) { if ( validar_usuario(linebuf) ) { autenticado = true; } }
if (strstr(linebuf, "GET /logoff" )>0 ) { logoff = true; }
if (strstr(linebuf, "GET / " )>0 ) { listProc = true; }
if (strstr(linebuf, "GET /js_proc_list" )>0 ) { jsProcList = true; }
if (strstr(linebuf, "GET /del_proc" )>0 ) { listProc = true; delete_proc(linebuf); }
if (strstr(linebuf, "GET /insert_proc" )>0 ) { listProc = true; insert_proc(linebuf); }

memset(linebuf, 0, sizeof(linebuf));
charCount = 0;
} else if (c != '\r') {
currentLineIsBlank = false; // you've gotten a character on the current line
}
}
}

delay(1); // give the web browser time to receive the data
client.stop(); // close the connection:
}
}
/**********************************************************************************
**************************************** FIM PAGINAS HTML**************************
***********************************************************************************/

/**********************************************************************************
*************************BASE 64 CODE/DECODE DO USUARIO E SENHA********************
***********************************************************************************/

void a3_to_a4(unsigned char * a4, unsigned char * a3);
void a4_to_a3(unsigned char * a3, unsigned char * a4);
unsigned char b64_lookup(char c);

int base64_encode(char *output, char *input, int inputLen) {
const char b64_alphabet[] = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
int i=0, j=0, encLen=0;
unsigned char a3[3], a4[4];

while(inputLen--) {
a3[i++] = *(input++);
if(i == 3) {
a3_to_a4(a4, a3);
for(i=0; i<4; i++) { output[encLen++] = b64_alphabet[a4[i]]; }
i = 0;
}
}

if (i) {
for(j = i; j < 3; j++) { a3[j] = '\0'; }
a3_to_a4(a4, a3);
for(j = 0; j < i + 1; j++) { output[encLen++] = b64_alphabet[a4[j]]; }
while((i++ < 3)) { output[encLen++] = '='; }
}
output[encLen] = '\0';
return encLen;
}

void a3_to_a4(unsigned char * a4, unsigned char * a3) {
a4[0] = (a3[0] & 0xfc) >> 2;
a4[1] = ((a3[0] & 0x03) << 4) + ((a3[1] & 0xf0) >> 4);
a4[2] = ((a3[1] & 0x0f) << 2) + ((a3[2] & 0xc0) >> 6);
a4[3] = (a3[2] & 0x3f);
}

unsigned char b64_lookup(char c) {
if(c >='A' && c <='Z') return c - 'A';
if(c >='a' && c <='z') return c - 71;
if(c >='0' && c <='9') return c + 4;
if(c == '+') return 62;
if(c == '/') return 63;
return -1;
}

/**********************************************************************************
*************************FIM BASE 64 CODE/DECODE DO USUARIO E SENHA****************
***********************************************************************************/

/**********************************************************************************
**************************************** VOID / LOOP ******************************
***********************************************************************************/

void setup() {
Serial.begin(9600);
iniciar_sd_card();
iniciar_banco_dados();
iniciar_ethernet();
}

void loop() {
exec_ethernet();
}

/**********************************************************************************
*************************************FIM VOID / LOOP*******************************
***********************************************************************************/