1#! /usr/bin/env perl
2#
3# This program is free software; you can redistribute it and/or modify
4# it under the terms of the GNU General Public License as published by
5# the Free Software Foundation; either version 2 of the License, or
6# (at your option) any later version.
7#
8# This program is distributed in the hope that it will be useful,
9# but WITHOUT ANY WARRANTY; without even the implied warranty of
10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
11# GNU General Public License for more details.
12#
13# You should have received a copy of the GNU General Public License
14# along with this program; if not, write to the Free Software Foundation.
15#
16# Copyright (C) 2013 Wind River Systems, Inc.
17#
18# - It tests DBI and DBD::SQLite could work correctly which means one could
19#   manipulate sqlite database in perl
20# - The test includes create/insert/update/delete/select, the five important
21#   things one can do with a table
22use DBI;
23
24sub execute_sql {
25    my $dbh = $_[0];
26    my $sql = $_[1];
27    my $sth = $dbh->prepare($sql)
28        or die "Couldn't prepare statement: " . $dbh->errstr;
29    $sth->execute();
30    print "$sql\n";
31    return $sth;
32}
33
34sub select_all {
35    my $dbh = $_[0];
36    my $table = $_[1];
37    my $sth = &execute_sql($dbh, "Select * from $table");
38
39    print "-----------------------------------\n";
40    while (@data = $sth->fetchrow_array()) {
41        my $name = $data[0];
42        my $id = $data[1];
43        print "$name: $id\n";
44    }
45    print "\n";
46
47    $sth->finish;
48    return $sth;
49}
50
51# A private, temporary in-memory database is created for the connection.
52# This in-memory database will vanish when the database connection is
53# closed. It is handy for your library tests.
54my $dbfile = ":memory:";
55my $dbh = DBI->connect("DBI:SQLite:dbname=$dbfile","","")
56        or die "Couldn't connect to database: " . DBI->errstr;
57print "Connect to SQLite's in-memory database\n";
58
59&execute_sql($dbh, "Create table tbl1(name varchar(10), id smallint)");
60&execute_sql($dbh, "Insert into tbl1 values('yocto',10)");
61&execute_sql($dbh, "Insert into tbl1 values('windriver', 20)");
62&select_all($dbh, "tbl1");
63
64&execute_sql($dbh, "Update tbl1 set name = 'oe-core' where id = 10");
65&execute_sql($dbh, "Delete from tbl1 where id = 20");
66&select_all($dbh, "tbl1");
67
68$dbh->disconnect;
69print "Test Success\n"
70