/ 
WebTools-1.27
River stage zero No dependents
/ WTJSprite

NAME

WTJSprite - Modified version of Sprite to manipulate text delimited flat-files as databases using SQL emulating Oracle. The remaining documentation is based on Sprite.

SYNOPSIS

  use WTJSprite;

  $rdb = new WTJSprite;

  $rdb->set_delimiter (-read  => '::')  ## OR: ('read',  '::');
  $rdb->set_delimiter (-write => '::')  ## OR: ('write', '::');
  $rdb->set_delimiter (-record => '\n')  ## OR: ('record', '::');

  $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_lock_file ('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->xclose;
  $rdb->close ($database);       ## To save updated database

DESCRIPTION

Here is a simple database where the fields are delimited by double-colons:

PLAYER=VARCHAR2(16)::YEARS=NUMBER::POINTS=NUMBER::REBOUNDS=NUMBER::ASSISTS=NUMBER::Championships=NUMBER
...
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 insensitive), and the Oracle datatype and length. Currently, the only meaningful datatypes are NUMBER and VARCHAR. All other types are treated the same as VARCHAR (Perl Strings, for comparisens).

Supported SQL Commands

Here are a list of the SQL commands that are supported by WTJSprite:

select - retrieves records that match specified criteria:
select col1 [,col2] from database 
       where (cond1 OPERATOR value1) 
       [and|or (cond2 OPERATOR value2) ...] 
       order by col1 [,col2]

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. By default, the actual file-name will end with the extension ".sdb".

Sprite does not support multiple tables (commonly known 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)

Note: Column names must not be Perl string or boolean operators, ie. (lt, gt, eq, and, or, etc. and are case-insensitive.

The following SQL operators can be used: =, <, >, <=, >=, <>, is, 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 - updates records that match specified criteria.
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 - removes records that match specified criteria:
delete from database 
       where (cond1 OPERATOR value1) 
       [and|or (cond2 OPERATOR value2) ...]

Example:

delete from my_db
       where (Player =~ /Johnson$/i) or
             (Years > 12)
alter - simplified version of SQL-92 counterpart

Removes the specified column from the database. The other standard SQL functions for alter table are also supported:

alter table database drop (column-name [, column-name2...])

alter table database add ([position] column-name datatype
		[, [position2] column-name2 datatype2...] 
		[primary key (column-name [, column-name2...]) ])

Examples:

alter table my_db drop (Years)

alter table my_db add (Legend VARCHAR(40) default "value", Mapname CHAR(5))

alter table my_db add (1 Maptype VARCHAR(40))

This example adds a new column as the 2nd column (0 for 1st column) of the table. By default, new fields are added as the right-most (last) column of the table. This is a WTJSprite Extension and is not supported by standard SQL.

alter table my_db modify (Legend VARCHAR(40))

alter table my_db modify (0 Legend default 1)

The last example moves the "Legend" column to the 1st column in the table and shifts the others over, and causes all subsequent records added to use a default value of "1" for the "Legend" field, if no value is inserted for it. This "Position" field (zero in the example) is a WTJSprite extension and is not part of standard SQL.

insert - inserts a record into the database:
insert into database 
       [(col1, col2, ... coln) ]
values 
       (val1, val2, ... valn)

Example:

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.

METHODS

Here are the available methods:

set_delimiter

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). In WTJSprite, you can also set the record seperator (default is newline).

Return Value

None

set_os

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!

Return Value

None

set_lock_file

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.

Return Value

None

set_db_dir

A path to a database can contain only the following characters:

\w, \x80-\xFF, -, /, \, ., :

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!

Return Value

0 - Failure
1 - Success
set_db_ext

WTJSprite permits the user to specify an extension that is part of the actual file name, but not part of the corresponding table name. The default is '.sdb'.

$rdb->set_db_ext ('.sdb');

Return Value

None

sql

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

commit

The sql function is used to commit changes to the database. Arguments: file-name (usually the table-name) - the file name to write the table to. NOTE: The path and file extension will be appended to it, ie:

&rdb->commit('filename');

Return Value 1 - Success 0 - Error

close

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.

Return Value

None

NOTES

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 the ability to develop and test prototype applications on personal machines (or other machines which do not have an Oracle licence or some other "mainstream" database) before releasing them on "production" machines which do have a "real" database. This can all be done with minimal or no changes to your Perl code.

Another advantage of Sprite is that you can use Perl's regular expressions to search through your data. Yippee!

WTJSprite provides the ability to emulate basic database tables and SQL calls via flat-files. The primary use envisioned for this is to permit website developers who can not afford to purchase an Oracle licence to prototype and develop Perl applications on their own equipment for later hosting at larger customer sites where Oracle is used. :-)

WTJSprite attempts to do things in as database-independent manner as possible, but where differences occurr, WTJSprite most closely emmulates Oracle, for example "sequences/autonumbering". WTJSprite uses tiny one-line text files called "sequence files" (.seq). and Oracle's "seq_file_name.NEXTVAL" function to insert into autonumbered fields.

ADDITIONAL WTJSprite-SPECIFIC FEATURES

WTJSprite supports Oracle sequences and functions. The currently-supported Oracle functions are "SYSTIME", NEXTVAL, and "NULL". Users can also "register" their own functions via the "fn_register" method.

fn_register

Method takes 2 arguments: Function name and optionally, a package name (default is "main").

$rdb->fn_register ('myfn','mypackage');

-or-

WTJSprite::fn_register ('myfn',__PACKAGE__);

Then, you could say:

insert into mytable values (myfn(?))

and bind some value to "?", which is passed to "myfn", and the return-value is inserted into the database. You could also say (without binding):

insert into mytable values (myfn('mystring'))

-or (if the function takes a number)-

select field1, field2 from mytable where field3 = myfn(123)

Return Value

None

WTJSprite has added the SQL "create" function to create new tables and sequences.

Examples:

create table table1 (
	field1 number, 
	field2 varchar(20), 
	field3 number(5,3)  default 3.143)

create sequence sequence-name [increment by 1] start with 0

SEE ALSO

DBD::Sprite, Sprite, Text::CSV, RDB

ACKNOWLEDGEMENTS

I would like to thank the following, especially Rod Whitby and Jim Esten, for finding bugs and offering suggestions:

Shishir Gundavaram  (shishir@ora.com)     (Original Sprite Author)
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 INFORMATION

			WTJSprite Copyright (c) 1998-2001, Jim Turner
          Sprite 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.

2 POD Errors

The following errors were encountered while parsing the POD:

Around line 372:

'=item' outside of any '=over'

Around line 412:

You forgot a '=back' before '=head1'