Sprite - Module to manipulate text delimited databases using SQL.
use Sprite; $rdb = new Sprite; $rdb->set_delimiter (-Read => '::') ## OR: ('Read', '::'); $rdb->set_delimiter (-Write => '::') ## OR: ('Write', '::'); $rdb->set_os ('Win95'); ## Valid arguments (case insensitive) include: ## ## Unix, Win95, Windows95, MSDOS, NT, WinNT, OS2, VMS, ## MacOS or Macintosh. Default determined by $^O. $rdb->set_lock_file ('c:\win95\tmp\Sprite.lck', 10); $rdb->set_db_dir ('Mac OS:Perl 5:Data') || die "Can't access dir!\n"; $data = $rdb->sql (<<Query); ## OR: @data = $rdb->sql (<<Query); . . (SQL) . Query foreach $row (@$data) { ## OR: foreach $row (@data) { @columns = @$row; ## NO null delimited string -- v3.2 } $rdb->close; $rdb->close ($database); ## To save updated database
Here is a simple database where the fields are delimited by commas:
Player,Years,Points,Rebounds,Assists,Championships ... Larry Bird,13,25,11,7,3 Michael Jordan,14,29,6,5,5 Magic Johnson,13,22,7,11,5 ...
Note: The first line must contain the field names (case sensitive).
Here are a list of the SQL commands that are supported by Sprite:
select col1 [,col2] from database where (cond1 OPERATOR value1) [and|or (cond2 OPERATOR value2) ...]
The '*' operator can be used to select all columns.
The database is simply the file that contains the data. If the file is not in the current directory, the path must be specified.
Sprite does not support multiple tables (or commonly knows as "joins").
Valid column names can be used where [cond1..n] and [value1..n] are expected, such as:
Example 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:
Example 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.
Note: A path to a database can contain only the following characters:
\w, \x80-\xFF, -, /, \, ., :
If you have directories with spaces or other 'invalid' characters, you need to use the set_db_dir method.
update database set cond1 = (value1)[,cond2 = (value2) ...] where (cond1 OPERATOR value1) [and|or (cond2 OPERATOR value2) ...]
Example:
update my_db set Championships = (Championships + 1) where (Player = 'Larry Bird') update my_db set Championships = (Championships + 1), Years = (12) where (Player = 'Larry Bird')
delete from database where (cond1 OPERATOR value1) [and|or (cond2 OPERATOR value2) ...]
delete from my_db where (Player =~ /Johnson$/i) or (Years > 12)
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 alter table database add column column-name
alter table my_db drop column Years alter table my_db add column Legend
insert into database (col1, col2, ... coln) values (val1, val2, ... valn)
insert into my_db (Player, Years, Points, Championships) values ('Kareem Abdul-Jabbar', 21, 26, 6)
You don't 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.
Note: You should make it a habit to quote strings.
Here are the available methods:
The set_delimiter function sets the read and write delimiter for the database. The delimiter is not limited to one character; you can have a string, and even a regexp (for reading only).
Return Value
None
The set_os function can be used to notify Sprite as to the operating system that you're using. Default is determined by $^O.
Note: If you're using Sprite on Windows 95/NT or on OS2, make sure to use backslashes -- and NOT forward slashes -- when specifying a path for a database or to the set_db_dir or set_lock_file methods!
For any O/S that doesn't support flock (i.e Mac, Windows 95 and VMS), this method allows you to set a lock file to use and the number of tries that Sprite should try to obtain a 'fake' lock. However, this method is NOT fully reliable, but is better than no lock at all.
'Sprite.lck' (either in the directory specified by set_db_dir or in the current directory) is used as the default lock file if one is not specified.
A path to a database can contain only the following characters:
If your path contains other characters besides the ones listed above, you can use this method to set a default directory. Here's an example:
$rdb->set_db_dir ("Mac OS:Perl 5:Data"); $data = $rdb->sql ("select * from phone.db");
Sprite will look for the file "Mac OS:Perl 5:Data:phone.db". Just to note, the database filename cannot have any characters besides the one listed above!
0 - Failure 1 - Success
The sql function is used to pass a SQL command to this module. All of the SQL commands described above are supported. The select 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.
Return Value 1 - Success 0 - Error
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; the directory set by set_db_dir is used as the default.
Sprite is not the solution to all your data manipulation needs. It's fine for small databases (less than 1000 records), but anything over that, and you'll have to sit there and twiddle your fingers while Sprite goes chugging away ... and returns a few *seconds* or so later.
The main advantage of Sprite is that you can use Perl's regular expressions to search through your data. Yippee!
Text::CSV, RDB
I would like to thank the following, especially Rod Whitby and Jim Esten, for finding bugs and offering suggestions:
Rod Whitby (rwhitby@geocities.com) Jim Esten (jesten@wdynamic.com) 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) Haakon Norheim (hanorhei@online.no)
Copyright (c) 1995-1998, 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.
To install Sprite, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Sprite
CPAN shell
perl -MCPAN -e shell install Sprite
For more information on module installation, please visit the detailed CPAN module installation guide.