Sqlite is useful to create a database (one file, .db extension) used to store configuration data, used Miicrosoft, Skype, Banshee… Smart Phone applications….
The file extension .db stands for a whole database: it’s made by the software C library called Sqlite…
This is a great opportunity to learn about SQLITE and the Perl scripting languages… great, don’t you think?
Let’s kill two birds with a stone!
Install Sqlite3 and follow me:
On UBUNTU, DEBIAN, etc:
$ sudo apt-get install sqlite3
On OPEN-SUSE:
$ sudo zypper install sqlite3
On REDHAT, CentOS, or FEDORA:
$ yum install SQLite3
BASH EXAMPLE
Let’s create an example: a database called “test.db” by the (unix) shell by the following command:
:~$ sqlite3 test.db “create table if not exists user(id INTEGER PRIMARY KEY, name TEXT, surname TEXT);”
Let’s fill it:
:~$ sqlite3 test.db “insert into user (name, surname) values (‘linus’,’torvalds’);”
:~$ sqlite3 test.db “insert into user(name,surname) values(‘richard’, ‘stallman’)”;
To check it out:
:~$sqlite3 test.db “select * from n”;
The result is:
1|linus|torvalds
2|richard|stallman
Alternatively you can create a database entering the sqlite3 enviroment
$ sqlite3 test.db
SQLite version 3.0.8
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>
In the sqlite3 enviroment you can use pure SQL statements to work with your database (in this case the test.db).
You can even change a few default settings to make the ouput of the commands look better. For example the column .mode and the .headers commands.They will last you exit the SQLite shell or change them to something else.
sqlite> .mode col
sqlite> .headers on
To see all the tables and views type:
sqlite> .tables
To see the databases that are currently open use the .databases command. It will show the main and temp databases and where they are on the system:
sqlite> .databases
To exit type .quit or .exit:
sqlite> .quit
Anyway it’s better to work in the shell prompt directly, that allows you to run bash scripts.
In this example the prompt is in your home/user directory. Check it out by the pwd command if you’re not sure…
PERL EXAMPLE
Make a file called test.pl :
$ touch test1.pl
Use an editor(ex. gedit under ubuntu and opensuse) or the cat test1.pl command to fill it with the following script:
#!/usr/bin/perl -w
use DBI;
use strict;
my $db = DBI->connect(“dbi:SQLite:test.db”, “”, “”) or die “couldn’t connect to db”.DBI->errstr;
$db->do(“CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, name TEXT, surname TEXT)”);
$db->do(“INSERT INTO user\(name, surname) VALUES ( ‘linus’, ‘torvalds’)”);
$db->do(“INSERT INTO user\(name, surname) VALUES ( ‘richard’, ‘stallman’)”);
my $all = $db->selectall_arrayref(“SELECT * FROM USER”);
foreach my $row (@$all) {
my ($id, $name, $surname) = @$row;
print “$id|$name|$surname \n”;
}
$db->disconnect;
Alternatively you can make the file directly by the shell:
$ cat > test.pl
.. perl script content…
Type Ctlr+C to close the file and exit.
Now check the file content:
$> cat test.pl
Make the perl script executable:
$ sudo chmod +x test.pl
Then run the perl script simply:
$ ./test.pl
The result is:
1|linus|torvalds
2|richard|stallman
In the same folder where the script lies, you can find a file called “test.db”. That’s the Sqlite database. Just one file.
Yeah! So we’ve learned some Perl and SQLite, right?