#!/usr/bin/perl
use strict;
use warnings;

use DBI;

my $dbfile = 'TestDB.sqlite';  #whatever SQLite DB name you want

# SQLite doesn't have a user name or password, but needs a
# "connection"

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError => 1})
    or die "Couldn't connect to database: " . DBI->errstr;

# I put in this DROP so that you can run this code again
# and again while you play with it.

$dbh->do("DROP TABLE IF EXISTS Animal");

$dbh->do("CREATE TABLE Animal
             (  id        integer PRIMARY KEY AUTOINCREMENT,
                Type      varchar(10),  
                Name      varchar(10)        
             );
         "); 

my $add_animal = $dbh->prepare ("INSERT INTO Animal (Type,Name)
                                 VALUES (?,?)"); 
                                
## Make a single "transaction" for all inserts
## This is a huge performance increase if making thousands of inserts
## A DB INSERT is "expensive" due to the booking involved
## A million inserts can be part of a single transaction

$dbh->do("BEGIN");  # start a new transaction for all inserts

while (defined (my $line = <DATA>))
{
   chomp $line;
   my ($type,$name) = split /\|/, $line;
  
   $add_animal->execute($type,$name);
}

$dbh->do("COMMIT"); #massive speed up by making one transaction!!

## An Example of Query by Type

my $query_by_type = $dbh->prepare ("SELECT * FROM Animal
                                    WHERE Type IS ?");

$query_by_type->execute('lion'); 
my $table_ref = $query_by_type->fetchall_arrayref;

foreach my $row (@$table_ref)
{
    print "@$row\n";
}

=Prints:
4 lion Simba
5 lion Elsa
=cut


__DATA__
cat|Felix
cat|Sylvester
cat|Garfield
lion|Simba
lion|Elsa
tiger|Sher Khan
dog|Rover