Camelia

SQLite - Database access in Perl 6

In tons of programs, especially web applications, we need to be able to access relational databases. One of the fastest RDBMS is SQLite, an embedded SQL database. In this example we'll see how to use SQLite in Perl 6.

When I was at YAPC::NA 2016 in Orlando, Florida, I also took a day off visiting the Kennedy Space Center with my family. It is a very impressive presentation, especially with the space-shuttle hanging there. They also had a room remembering the Astronauts we lost during the Shuttle flights. To honour them, I am going to use the names of 7 astronauts who perished in the Space Shuttle Columbia disaster in this example.

Note! This site is about Perl 6.
If you are looking for a solution for Perl 5, please check out the Perl 5 tutorial.

examples/columbia.pl

use v6;
use DBIish;


my $dbfile = 'test.db';
my $dbh = DBIish.connect("SQLite", :database($dbfile));

$dbh.do('DROP TABLE IF EXISTS astronaut');

$dbh.do(q:to/STATEMENT/);
    CREATE TABLE astronaut (
        id               INTEGER PRIMARY KEY,
        name             VARCHAR(100),
        country          VARCHAR(100),
        birthday         DATE
    )
    STATEMENT

my $sth_insert = $dbh.prepare(q:to/STATEMENT/);
    INSERT INTO astronaut (name, country, birthday)
    VALUES ( ?, ?, ? )
    STATEMENT

my @people =
		('Rick D. Husband',     'USA',    'July 12, 1957'),
		('William C. McCool',   'USA',    'September 23, 1961'),
		('Michael P. Anderson', 'USA',    'December 25, 1959'),
		('Kalpana Chawla',      'India',  'July 1, 1961'),
		('David M. Brown',      'USA',    'April 16, 1956'),
		('Laurel Clark',        'USA',    'March 10, 1961'),
		('Ilan Ramon',          'Israel', 'June 20, 1954'),
;

for @people -> $p {
	$sth_insert.execute(|$p);
}

my $sth_select = $dbh.prepare('SELECT * FROM astronaut');

$sth_select.execute();

my @rows = $sth_select.allrows();
say @rows.elems;
for @rows -> $r {
	say $r.perl;
}

$sth_select.finish;

$dbh.dispose;

If we run perl6 columbia.pl we get the following output:

7
$[1, "Rick D. Husband", "USA", "July 12, 1957"]
$[2, "William C. McCool", "USA", "September 23, 1961"]
$[3, "Michael P. Anderson", "USA", "December 25, 1959"]
$[4, "Kalpana Chawla", "India", "July 1, 1961"]
$[5, "David M. Brown", "USA", "April 16, 1956"]
$[6, "Laurel Clark", "USA", "March 10, 1961"]
$[7, "Ilan Ramon", "Israel", "June 20, 1954"]

DBIish is a generic, database independent interface to relational databases. Currently, AFAIK, it supports SQLite, MySQL, and PostgreSQL.

The connect method of DBIish establishes the connection to the database. As SQLite is a serverless database we only need to give it the path of the database file as the value of the database parameter. In our case :database($dbfile) means the same as database => $dbfile which is passing a key-value pair to the connect method. The first parameter told connect that we are talking to an SQLite database.

We assign the database connection object to a variable called $dbh. (It is an arbitrary variable name, but it is recommended as dbh stands for database handle.)

Using the Database handle we can call the do method an execute any SQL statement the specific engine is ready to accept.

The first such statement will delete the 'astronaut' table if it existed earlier. e.g from a previous run of the same code.

In the second do statement we use a here-document to include a multi-line sql statement. The expression q:to/STATEMENT/ tells Perl to look for a line that matches the STATEMENT regular expression and that's the end of the string. This second do call will create the table. (In SQLite you don't need special permissions for either dropping a table or creating one.)

INSERT

Then we insert the data. First we call the prepare method with an SQL statement that uses ? placeholders. These placeholder have two functions: They allow us to reuse a prepared statement with several sets of data, thereby improving the speed of our code. They also help us avoid SQL injection attacks.

The returned object is assigned to a variable that is usually called sth-something as it is a "statement handle". As this example has two different statement handles I used two such variables.

Then we have the names of the astronauts in an array of lists and a loop that iterates over the elements of the array. The | in front of $p converts the List item into the individual elements of the list. (We could have written @$p for the same result.)

SELECT

Once we have the data in the database we can try to get it back. For this we prepare a SELECT statement. Then execute it.

Then fetch all the rows.

Finally we close the database connection by calling dispose.


The Perl 6 Tricks and Treats newsletter has been around for a while. If you are interested to get special notification when there is new content on this site, it is the best way to keep track:
Email:
Full name:
This is a newsletter temporarily running on my personal site (szabgab.com) using Mailman, till I implement an alternative system in Perl 6.
Gabor Szabo
Written by Gabor Szabo

Published on 2017-06-05



Comments

In the comments, please wrap your code snippets within <pre> </pre> tags and use spaces for indentation.
comments powered by Disqus
Suggest a change
Elapsed time: 2.3800312

Perl 6 Tricks and Treats newsletter

Register to the free newsletter now, and get updates and news.
Email:
Name: