SPRITE(1) User Contributed Perl Documentation SPRITE(1)
NAME
Sprite - Perl 5.0 module to manipulate text delimited
databases.
SYNOPSIS
use Sprite;
$rdb = new Sprite ();
$rdb->set_delimiter ("Read", "::");
$rdb->set_delimiter ("Write", "::");
$rdb->set_os ("UNIX");
$rdb->sql (<<Query);
.
.
.
Query
$rdb->close ();
$rdb->close ($database);
DESCRIPTION
Here is a simple database where the fields are delimted by
commas:
Player,Years,Points,Rebounds,Assists,Championships
...
Larry Joe Bird,12,28,10,7,3
Michael Jordan,10,33,6,5,3
Earvin Magic Johnson,12,22,7,12,5
...
_N_o_t_e: The first line must contain the field names (case
sensitive).
Supported SQL Commands
Here are a list of the SQL commands that are supported by
Sprite:
_s_e_l_e_c_t - retrieves records that match specified criteria:
select col1 [,col2] from database
where (cond1 OPERATOR value1)
[and|or cond2 OPERATOR value2 ...]
The '*' operator can be used to select all columns.
The _d_a_t_a_b_a_s_e is simply the file that contains the data.
If the file is not in the current directory, the path
20/Jun/96 Last change: perl 1
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
must be specified.
Sprite does _n_o_t support multiple tables (or commonly
knows as "joins").
Valid column names can be used where [cond1..n] and
[value1..n] are expected, such as:
_E_x_a_m_p_l_e _1:
select Player, Points from my_db
where (Rebounds > Assists)
The following SQL operators can be used: =, <, >, <=,
>=, <> as well as Perl's special operators: =~ and !~.
The =~ and !~ operators are used to specify regular
expressions, such as:
_E_x_a_m_p_l_e _2:
select * from my_db
where (Name =~ /Bird$/i)
Selects records where the Name column ends with "Bird"
(case insensitive). For more information, look at a
manual on regexps.
_u_p_d_a_t_e - updates records that match specified criteria.
update database set (cond1 OPERATOR value1)[,(cond2 OPERATOR value2)...]*
where (cond1 OPERATOR value1)
[and|or cond2 OPERATOR value2 ...]
* = This feature was added as of version 3.1.
_E_x_a_m_p_l_e:
update my_db
set Championships = (Championships + 1)
where (Player = 'Larry Joe Bird')
update my_db
set Championships = (Championships + 1),
Years = (12)
where (Player = 'Larry Joe Bird')
_d_e_l_e_t_e - removes records that match specified criteria:
20/Jun/96 Last change: perl 2
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
delete from database
where (cond1 OPERATOR value1)
[and|or cond2 OPERATOR value2 ...]
_E_x_a_m_p_l_e:
delete from my_db
where (Player =~ /Johnson$/i) or
(Years > 12)
_a_l_t_e_r - simplified version of SQL-92 counterpart
Removes the specified column from the database. The
other standard SQL functions for alter table are not
supported:
alter table database
drop column column-name
_E_x_a_m_p_l_e:
alter table my_db
drop column Championships
_i_n_s_e_r_t - inserts a record into the database:
insert into database
(col1, col2, ... coln)
values
(val1, val2, ... valn)
_E_x_a_m_p_l_e:
insert into my_db
(Player, Years, Points, Championships)
values
('Kareem Abdul-Jabbar', 21, 27, 5)
_N_o_t_e: You do not have to specify all of the fields in
the database! Sprite also does not require you to
specify the fields in the same order as that of the
database.
_N_o_t_e: You should make it a habit to quote strings.
METHODS
Here are the four methods that are available:
_s_e_t__d_e_l_i_m_i_t_e_r
The set_delimiter function sets the read and write
delimiter for the the SQL command. The delimiter is not
20/Jun/96 Last change: perl 3
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
limited to one character; you can have a string, and
even a regexp (for reading only).
_R_e_t_u_r_n _V_a_l_u_e
None
_s_e_t__o_s
The set_os function can be used to notify Sprite as to
the operating system that you're using. Valid arguments
are: "UNIX", "VMS", "MSDOS", "NT" and "MacOS". UNIX is
the default.
_R_e_t_u_r_n _V_a_l_u_e
The previous OS value
_s_q_l The sql function is used to pass a SQL command to this
module. All of the SQL commands described above are
supported. The _s_e_l_e_c_t SQL command returns an array
containing the data, where the first element is the
status. All of the other other SQL commands simply
return a status.
_R_e_t_u_r_n _V_a_l_u_e
1 - Success
0 - Error
_c_l_o_s_e
The close function closes the file, and destroys the
database object. You can pass a filename to the
function, in which case Sprite will save the database
to that file.
_R_e_t_u_r_n _V_a_l_u_e
None
EXAMPLES
Here are two simple examples that illustrate some of the
functions of this module:
_E_x_a_m_p_l_e _1
#!/usr/local/bin/perl5
use Sprite;
$rdb = new Sprite ();
20/Jun/96 Last change: perl 4
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
# Sets the read delimiter to a comma (,) character. The delimiter
# is not limited to one character; you can have a string, or even
# a regexp.
$rdb->set_delimiter ("Read", ",");
# Retrieves all records that match the criteria.
@data = $rdb->sql (<<End_of_Query);
select * from /shishir/nba
where (Points > 25)
End_of_Query
# Close the database and destroy the database object (i.e $rdb).
# Since we did not pass a argument to this function, the data
# is not updated in any manner.
$rdb->close ();
# The first element of the array indicates the status.
$status = shift (@data);
$no_records = scalar (@data);
if (!$status) {
die "Sprite database error. Check your query!", "\n";
} elsif (!$no_records) {
print "There are no records that match your criteria!", "\n";
exit (0);
} else {
print "Here are the records that match your criteria: ", "\n";
# The database returns a record where each field is
# separated by the "\0" character.
foreach $record (@data) {
$record =~ s/\0/,/g;
print $record, "\n";
}
}
_E_x_a_m_p_l_e _2
#!/usr/local/bin/perl5
use Sprite;
$rdb = new Sprite ();
$rdb->set_delimiter ("Read", ",");
20/Jun/96 Last change: perl 5
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
# Deletes all records that match the specified criteria. If the
# query contains an error, Sprite returns a status of 1.
$rdb->sql (<<Delete_Query)
|| die "Database Error. Check your query", "\n";
delete from /shishir/nba
where (Rebounds <= 5)
Delete_Query
# Access the database again! This time, select all the records that
# match the specified criteria. The database is updated *internally*
# after the previous delete statement.
# Notice the fact that the full path to the database does not
# need to specified after the first SQL command. This
# works correctly as of version 3.1.
@data = $rdb->sql (<<End_of_Query);
select Player from nba
where (Points > 25)
End_of_Query
# Sets the write delimiter to the (:) character, and outputs the
# updated information to the file: "nba.new". If you do not pass
# an argument to the close function after you update the database,
# the modified information will not be saved.
$rdb->set_delimiter ("Write", ":");
$rdb->close ("nba.new");
# The first element of the array indicates the status.
$status = shift (@data);
$no_records = scalar (@data);
if (!$status) {
die "Sprite database error. Check your query!", "\n";
} elsif (!$no_records) {
print "There are no records that match your criteria!", "\n";
exit (0);
} else {
print "Here are the records that match your criteria: ", "\n";
# The database returns a record where each field is
# separated by the "\0" character.
20/Jun/96 Last change: perl 6
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
foreach $record (@data) {
$record =~ s/\0/,/g;
print $record, "\n";
}
}
ADVANTAGES
Here are the advantages of Sprite over mSQL by David Hughes
available on the Net:
Allows for column names to be specified in the update
command:
Perl's Regular Expressions allows for powerful pattern
matching
The database is stored as text. Very Important! Information
can be added/modified/removed with a text editor.
Can add/delete columns quickly and easily
DISADVANTAGES
Here are the disadvantages of Sprite compared to mSQL:
_S_p_e_e_d. No where close to mSQL! Sprite was designed to be
used to manipulate very small databases (~1000-2000
records).
Does not have the ability to "join" multiple tables
(databases) during a search operation. This will be added
soon!
RESTRICTIONS
1 If a value for a field contains the comma (,) character
or the field delimiter, then you need to quote the
value. Here is an example:
insert into $database
(One, Two)
values
('$some_value', $two)
The information in the variable $some_value _m_i_g_h_t
contain the delimiter, so it is quoted -- you can use
either the single quote (') or the double quote (").
2 All single quotes and double quotes within a value must
be escaped. Looking back at the previous example, if
you think the variable $some_value contains quotes, do
the following:
20/Jun/96 Last change: perl 7
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
$some_value =~ s/(['"])/\\$1/g;
3 If a field's value contains a newline character, you
need to convert the newline to some other character (or
string):
$some_value =~ s/\n/<BR>/g;
4 If you want to search a field by using a regular
expression:
select * from $database
where (Player =~ /Bird/i)
the only delimiter you are allowed is the standard one
(i.e /../). You _c_a_n_n_o_t use any other delimeter:
select * from $database
where (Player =~ m|Bird|i)
5 Field names can only be made up of the following
characters:
"A-Z", "a-z", and "_"
In other words,
[A-Za-z_]
6 If your update value contains parentheses, you need to
escape them:
$rdb->sql (<<End_of_Query);
update my_db
set Phone = ('\\(111\\) 222 3333')
where (Name = /Gundavaram\$/i)
End_of_Query
Notice how the "$" (matches end of line) is escaped as
well!
SEE ALSO
RDB (available at the Metronet Perl archive)
REVISION HISTORY
20/Jun/96 Last change: perl 8
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
v3.1 - June 18, 1996
Added the following features:
o+ As of this version, Sprite allows you to update
multiple fields with a single _u_p_d_a_t_e command. See
the _S_u_p_p_o_r_t_e_d _S_Q_L _C_o_m_m_a_n_d_s section above.
o+ You can execute your scripts with the following:
#!/usr/local/bin/perl5 -wT
use strict;
Sprite no longer generates the "Use of uninitialized
value..." errors.
o+ For records that don't contain quotes or escaped
strings, Perl's split is used to dramatically speed
up database loading.
o+ The _s_e_t__o_s function will allow you to set the
operating system that you're using.
o+ Added a "require 5.002" as Sprite fails on versions
of Perl older than 5.002 with the following error:
"Bad _f_r_e_e() ignored at Sprite.pm..."
Fixed the following bugs:
o+ If you call the _c_l_o_s_e method with a database as an
argument _w_i_t_h_o_u_t opening a database first, Sprite
will warn you as opposed to wiping out the database,
as was the case in earlier versions of Sprite.
o+ Sprite no longer chops off the trailing "0" on
records.
o+ The _d_r_o_p _c_o_l_u_m_n works as it should.
o+ You can safely escape parentheses in the _u_p_d_a_t_e
command.
o+ Extra spaces between field names in the _s_e_l_e_c_t
command and values in the _u_p_d_a_t_e command no longer
cause fatal errors.
o+ In earlier versions of Sprite, if you opened two
databases that were located in different
directories, _b_u_t with the same name, Sprite
incorrectly assumed that it was the same database.
As a result, the second database would never be
20/Jun/96 Last change: perl 9
SPRITE(1) User Contributed Perl Documentation SPRITE(1)
loaded.
o+ Can be used on the Mac OS and Windows NT.
v3.01 - March 5, 1996
Fixed a bug in _p_a_r_s_e__e_x_p_r_e_s_s_i_o_n subroutine so that it
recognizes the "_" character as valid in field names.
v3.0 - Febraury 20, 1996
Totally re-wrote parser; works reasonably well even in
the worst case scenarios.
v2.0 - November 23, 1995
Fixed *numerous* errors in parsing, and added pod style
documentation.
v1.5 - September 10, 1995
Created Perl 5 module instead of a command-line
interface.
v1.0 - September 7, 1995
Initial Release
ACKNOWLEDGEMENTS
I would like to thank the following for finding bugs and
offering suggestions:
Dave Moore (dmoore@videoactv.com)
Shane Hutchins (hutchins@ctron.com)
Josh Hochman (josh@bcdinc.com)
Barry Harrison (barryh@topnet.net)
Lisa Farley (lfarley@segue.com)
Loyd Gore (lgore@ascd.org)
Tanju Cataltepe (tanju@netlabs.net)
COPYRIGHT INFORMATION
Copyright (c) 1995, 1996 by Shishir Gundavaram
All Rights Reserved
Permission to use, copy, and distribute is hereby granted,
providing that the above copyright notice and this permission
appear in all copies and in supporting documentation.
20/Jun/96 Last change: perl 10