Perl et les bases de données

name: inverse
class: center, middle, inverse

# Perl et les bases de données
Clément Plancq

22 octobre 2014
---
# DBI
## Database independant interface for Perl

Le module [DBI](https://metacpan.org/pod/DBI) est une interface (API)
d'accès aux bases de données pour Perl.

```

|<- Scope of DBI ->|
                .-.   .--------------.   .-------------.
.-------.       | |---| XYZ Driver   |---| XYZ Engine  |
| Perl  |       | |   `--------------'   `-------------'
| script|  |A|  |D|   .--------------.   .-------------.
| using |--|P|--|B|---|Oracle Driver |---|Oracle Engine|
| DBI   |  |I|  |I|   `--------------'   `-------------'
| API   |       | |...
|methods|       | |... Other drivers
`-------'       | |...
                `-'

```
<sub><sup>from [metacpan](https://metacpan.org/pod/DBI#Architecture-of-a-DBI-Application)</sup></sub>
---
# DBI

```Perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect() or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
$sth->execute();
while (my $ref = $sth->fetchrow_arrayref()){
...
}
#my $ref = $sth->fetchall_arrayref();

$dbh->disconnect();

```
---

# DBI et SQLite
## Voir [doc](http://search.cpan.org/~ishigaki/DBD-SQLite-1.42/lib/DBD/SQLite.pm)

```Perl
use strict;
use warnings;
use DBI;

my $dbfile = 'treetagger.db';    
my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=$dbfile", # DSN: dbi, driver, database file
    "",                          # no user
    "",                          # no password
    { RaiseError => 1,       # complain if something goes wrong
	sqlite_unicode => 1 }
) or die $DBI::errstr;

```
---
# Exemple données TreeTagger
## Création de la base
```perl
my $table = 'treetagger';
$dbh->do("create table $table (form TEXT, pos TEXT, lemma TEXT) ")
  or die $DBI::errstr;

my $sth = $dbh->prepare("INSERT INTO $table VALUES (?, ?, ?)")
or die $DBI::errstr;

while(<>){
  chomp;
  my($form, $pos, $lemma) = split(/\t/);
  $sth->execute($form, $pos, $lemma);
}

```
---
# Exemple données TreeTagger
## Interrogation

```Perl
my $table = 'treetagger';
my $sth = $dbh->prepare("SELECT * FROM $table WHERE pos = \"ADJ\"");
$sth->execute();
my $ref_tab = $sth->fetchall_arrayref;

print Dumper $ref_tab;
```