|
Page 12 of 12
Reading Images (Blob data)
First, a demonstration of how the program eatblob.c works. This program is a C API which inserts binary (blob) data into a table.
The program can be run in two ways. First, script commands can be redirected into it. For example, you can create the following script file "sqlcommands":
$ cat sqlcommands
create table blobtest (des varchar(80),b blob);
insert into blobtest (des,b)
values ('A test file: test.png',?);
select * from blobtest;
Note the "?" on the line "values ('A test file: test.png',?);". This serves as a place holder for blob data in the SQL statement. Using this file, the program is executed as follows:
$ ./eatblob test3.db test.png < sqlcommands
The image file "test.png" will be read into the program and inserted into the field b, since this is where the question mark is placed.
The program also works interactively, as follows:
$ ./eatblob test.db test.png
eatblob:0> create table blobtest2 (des varchar(30), b blob);
eatblob:0> insert into blobtest2 (des,b) values ('A second test: test.png',?);
eatblob:1> insert into blobtest2 (des,b) values ('A third test: test.png',?);
eatblob:2> select * from blobtest2;
A second test: test.png
A third test: test.png
eatblob:2> .q
[chirico@third-fl-71 sqlite_examples]$ ls outdata.*
outdata.0.png outdata.1.png
The blob data is not shown. Instead, it is written to the file outdata.n.png, where n is the record number.
Examining the C code in eatblob.c
The program works by reading all of the binary data from the filename given as the third argument to the command. The complete file is read into memory. One way to do this is to get the total file size first, then allocate that amount of memory with malloc. That approach is not taken here. Instead, a more general approach is used. For instance, if you were to read data from a socket, you may not know beforehand how big the file will be. This general approach will take advantage of the realloc function. The function in the program addmem will give us a number. The number will be the new number of units to reallocate. We want to increase the amount of memory in a non-linear fashion to minimize the number of reallocations for large files.
#define MULT_START 16384
#define MULT_INCREMENT 2
#define INIT_SIZE 1024
long memindx = MULT_START;
long memnext;
...
1 long addmem(char **buf, long size)
2 {
3 memnext = (size > 0) ? size + memindx: INIT_SIZE;
4 memindx = memindx * MULT_INCREMENT;
5 char *tbuf = realloc(*buf, memnext);
6 if (tbuf == NULL) {
7 fprintf(stderr, "Can't allocate memory in addmemn");
8 return size;
9 } else {
10 *buf = tbuf;
11 return memnext;
12 }
13 }
...
One line 3, the first time this function is called, the variable size is the current number of bytes allocated. If no memory has been allocated (size is 0), the new size will be INIT_SIZE. For this program, INIT_SIZE is set to 1024 in the define statement. However, if size is greater than zero, the new size will be the initial size plus memindx, which starts at 1024.
Call memidx Number Returned size
1 1024 1024 = 0 + 1024 0
2 2048 3072 = 1024 + (1024*2) 1024
3 4096 7168 = 3072 + (2048*2) 3072
4 8192 15360 = 7168 + (4096*2) 7168
5 16384 31744 = 15360 + (8192*2) 15360
...
As you can see, the number returned increases exponentially. "memindx" is doubled each time this function is called. This doubled value is added to the size.
1 long addmem(char **buf, long size)
2 {
3 memnext = (size > 0) ? size + memindx: INIT_SIZE;
4 memindx = memindx * MULT_INCREMENT;
5 char *tbuf = realloc(*buf, memnext);
6 if (tbuf == NULL) {
7 fprintf(stderr, "Can't allocate memory in addmemn");
8 return size;
9 } else {
10 *buf = tbuf;
11 return memnext;
12 }
13 }
...
So, the function gives us a number that we can pass to realloc.
realloc works as follows: If realloc is successful, it will copy the contents pointed to by buf to a location of memory with the larger size memnext, then free the old region of memory. This new region of memory will be assigned to tbuf. Since the old location (the location pointed to by *buf) has been released, we need to assign the new value to *buf ( *buf = tbuf).
If realloc cannot get the new size memnext, *buf is left untouched and tbuf will be null.
Note that in the above program, buf is a pointer to a pointer, *buf is a pointer, and **buf is the first byte of data.
Perl and sqlite3
To use Perl with sqlite3, DBI and DBD::SQLite must be installed. To install the packages from CPAN, use the following commands.
# perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::SQLite
The following program will create a database and enter records:
#!/usr/bin/perl
use DBI;
$dbh = DBI->connect( "dbi:SQLite:data.dbl" ) || die "Cannot connect: $DBI::errstr";
$dbh->do( "CREATE TABLE authors ( lastname, firstname )" );
$dbh->do( "INSERT INTO authors VALUES ( 'Conway', 'Damian' ) " );
$dbh->do( "INSERT INTO authors VALUES ( 'Booch', 'Grady' ) " );
$dbh->do( "CREATE TABLE books ( title, author )" );
$dbh->do( "INSERT INTO books VALUES ( 'Object Oriented Perl',
'Conway' ) " );
$dbh->do( "INSERT INTO books VALUES ( 'Object-Oriented Analysis and Design',
'Booch' ) ");
$dbh->do( "INSERT INTO books VALUES ( 'Object Solutions', 'Booch' ) " );
$res = $dbh->selectall_arrayref( q( SELECT a.lastname, a.firstname, b.title
FROM books b, authors a
WHERE b.title like '%Orient%'
AND a.lastname = b.author ) );
foreach( @$res ) {
foreach $i (0..$#$_) {
print "$_->[$i] "
}
print "n";
}
$dbh->disconnect;
For a more elaborate Perl example that defines functions, see perlExample.pl in the download.
Also, consider using the Perl Debugger, for stepping through complex Perl sqlite programs where you are not sure of what is returned. To get into the Perl debugger, execute the following command, and to get out of the Perl Debugger type "q".
$ perl -de 42
A Simple Everyday Application -- Keeping Notes in a Database
This simple bash script allows you to take notes. The notes consist of a line of text followed by an optional category. It doesn't require you to type "sqlite3 ". Instead, you just need a simple one-letter command:
$ n 'Take a look at sqlite3 transactions -
http://www.sqlite.org/lang.html#transaction' 'sqlite3'
This enters the text into a notes table under the category "sqlite3". Whenever a second field appears, it is considered the category. To extract records for the day, enter "n -l" (which is similar to "l -l") to "note list".
With just "n", help is listed for all the commands.
$ n
This command is used to list notes in
a database.
n
-l list all notes
-t list notes for today
-c list categories
-f search for text
-e execute command and add to notes
-d delete last entry
Dirty Tricks
This trick works on sqlite version 3.2.7 and all historical versions. However, it is not guaranteed to work on all future versions. Still, this is an interesting trick.
Suppose you have two similar table structures and you want to update missing rows. This is easier to show with an example.
create table t0 (a int);
insert into t0 (a) values (2);
create table t1 (a int);
insert into t1 (a) values (1);
insert into t1 (a) values (2);
insert into t1 (a) values (3);
Above when table t0 is compared to t1 values 1 and 3 are missing. It is possible to update these these rows as follows:
insert into t0 (a) select t1.a from t1 left join t0 on t0.a=t1.a where t0.a is NULL order by +t0.rowid;
Note above the plus sign in "+t0.rowid" so that you do not get row locking errors.
|
