SQLite - Perl: A Beginner's Guide

Hello there, future database wizards! I'm thrilled to be your guide on this exciting journey into the world of SQLite and Perl. As someone who's been teaching computer science for years, I can tell you that this combination is like peanut butter and jelly - simply delicious! So, let's roll up our sleeves and dive in!

SQLite - Perl

Installation

Before we can start creating database magic, we need to set up our tools. Think of this as preparing your kitchen before cooking a gourmet meal.

First, make sure you have Perl installed on your system. You can check this by opening a terminal and typing:
```
perl -v
```
If you see version information, you're good to go!
Next, we need to install the DBI (Database Interface) module and the SQLite driver. Open your terminal and type:
```
cpan install DBI
cpan install DBD::SQLite
```
This might take a few minutes, so feel free to grab a coffee while you wait.

DBI Interface APIs

Now that we have our ingredients ready, let's look at the recipe book - the DBI Interface APIs. These are the functions we'll use to interact with our SQLite database.

Here's a table of the most common DBI methods we'll be using:

Method	Description
connect()	Establishes a database connection
prepare()	Prepares an SQL statement for execution
execute()	Executes a prepared statement
fetch()	Retrieves a row of data from a select statement
disconnect()	Closes a database connection

Don't worry if these seem confusing now. We'll explore each of these in detail as we go along.

Connect To Database

Let's start by establishing a connection to our SQLite database. This is like knocking on the door and asking politely to come in.

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

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";

my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

Let's break this down:

We start by importing the necessary modules.
We specify the driver (SQLite) and the database name.
We create a Data Source Name (DSN) string.
We use DBI->connect() to establish a connection and store it in $dbh.
If the connection is successful, we print a success message.

Create a Table

Now that we're in, let's create a table to store some data. Imagine this as creating a new spreadsheet in Excel.

my $stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL););

my $rv = $dbh->do($stmt);
if($rv < 0) {
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}

Here's what's happening:

We define our SQL statement to create a table named COMPANY.
We use $dbh->do() to execute the statement.
We check if the operation was successful and print an appropriate message.

INSERT Operation

Time to add some data to our table. This is like filling in the rows of our spreadsheet.

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 ));

my $rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";

In this code:

We create an SQL INSERT statement.
We execute it using $dbh->do().
If successful, we print a confirmation message.

SELECT Operation

Now, let's retrieve our data. This is like reading the information from our spreadsheet.

my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;

if($rv < 0) {
   print $DBI::errstr;
}

while(my @row = $sth->fetchrow_array()) {
   print "ID = ". $row[0] . "\n";
   print "NAME = ". $row[1] ."\n";
   print "ADDRESS = ". $row[2] ."\n";
   print "SALARY = ". $row[3] ."\n\n";
}

Here's the breakdown:

We prepare our SELECT statement using $dbh->prepare().
We execute the statement with $sth->execute().
We use a while loop with $sth->fetchrow_array() to retrieve and print each row.

UPDATE Operation

Sometimes we need to change our data. This is like editing a cell in our spreadsheet.

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;

if( $rv < 0 ) {
   print $DBI::errstr;
} else {
   print "Total number of rows updated : $rv\n";
}

In this code:

We create an UPDATE SQL statement.
We execute it with $dbh->do().
We check how many rows were affected and print the result.

DELETE Operation

Finally, let's learn how to remove data. This is like deleting a row from our spreadsheet.

my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;

if( $rv < 0 ) {
   print $DBI::errstr;
} else {
   print "Total number of rows deleted : $rv\n";
}

This code follows the same pattern as our UPDATE operation:

We create a DELETE SQL statement.
We execute it with $dbh->do().
We check how many rows were affected and print the result.

And there you have it! You've just learned the basics of working with SQLite databases using Perl. Remember, practice makes perfect, so don't be afraid to experiment with these examples. Before you know it, you'll be creating complex database applications with ease!

Credits: Image by storyset