Looking to Fake an SQL statement against a flatfile...

[screamingpackets]

I use flatfiles all the time for small bits of data, but I'm a little new to perl. I'm thinking there has to be an easier way to genericly interface various flatfiles with a SQL style statement. Anyone ever written any small snippets of code like this? I figure it might be a fun project to explore.

[Xuefer]

it will take u long time to develop a slow~~ SQL parser

[screamingpackets]

Your probably right, but as a required library, it could be reused... I've been cheating as of now with something like this

sub select(){
$delimiter = "\t";
$data0 = $_[0];
$data1 = $_[1];
$data2 = $_[2];
$data3 = $_[3];

open(FILE, "$data1") ¦¦ show_error("$data1: Can't open because ($!).");
@db_contents = <FILE>;
close(FILE);
foreach(@db_contents) {
$fields=6;
@test = split($delimiter,$_,$fields);
if (($test[$data2]) eq ($data3)){
for($x=0;$x<=($fields - 1);$x++) {
$results = $results . "$test[$x]";
}
}

}
if ($results eq ""){
$results = "not found!";
}
return $results;
}
1; #return true

[screamingpackets]

forgot this ...

# select( data1, from data2, where data3, equals data4)

[dive into perl]

Hi,

You could look at the DBI driver CSV.pm [search.cpan.org] which allows for SQL statements over a flat CSV file.

-dip

[screamingpackets]

Hello,
I've used the CSV.pm before and like it. Unfortunatly, not all the servers are in my control to add modules to so I needed an all mobile script solution. A little rewrite on what I have seems to be doing me good so I'll probably just keep using it. Maybe someone else can use it too.

#begin: database.pl#######################################
$fields=20;

sub select(){ # select( data0, from data1, where data2, equals data3)
my $data0 = $_[0];
my $data1 = $_[1];
my $data2 = $_[2];
my $data3 = $_[3];
my $select_located, @select_results, @select_db_contents;
open(FILE, "$data1") ¦¦ show_error("$data1: Can't open because ($!).");
@select_db_contents = <FILE>;
close(FILE);
foreach(@select_db_contents) {
my @select_line = split($delimiter,$_,$fields);
if (($select_line[$data2]) eq ($data3)){
for($x=0;$x<=($fields - 1);$x++) {
$select_located = $select_located . "$select_line[$x]" . $delimiter ;
}
push(@select_results,$select_located);
}
$select_located = "";
}
return @select_results;
}

sub insert(){ # insert( data0, into data1)
my $data0 = $_[0];
my $data1 = $_[1];
my @insert_db_contents, $insert_indexof, $insert_thisdata;
open(FILE, "$data1") ¦¦ show_error("$data1: Can't open because ($!).");
@insert_db_contents = <FILE>;
close(FILE);
$insert_indexof = (@insert_db_contents + "1"); # Count the files entries and advance it by 1
$insert_thisdata = "\n" . $insert_indexof . $delimiter . $data0;
push(@insert_db_contents,$insert_thisdata);
open(FILE, ">$data1") ¦¦ show_error("$data1: Can't open because ($!).");
print FILE (@insert_db_contents);
close(FILE);
}

sub update(){ # update( data0, in data1, where data2, equals data3)
my $data0 = $_[0];
my $data1 = $_[1];
my $data2 = $_[2];
my $data3 = $_[3];
my @update_db_contents, @update_results;
open(FILE, "$data1") ¦¦ show_error("$data1: Can't open because ($!).");
@update_db_contents = <FILE>;
close(FILE);
foreach(@update_db_contents) {
my $update_line = $_;
my @update_working_line = split($delimiter,$_,$fields);
if (($update_working_line[$data2]) eq ($data3)){
my $update_data = $data0 . "\n";
push(@update_results,$update_data);
}else{
push(@update_results,$update_line);
}
}
open(FILE, ">$data1") ¦¦ show_error("$data1: Can't open because ($!).");
print FILE (@update_results);
close(FILE);
}

sub delete(){ # delete( data0, from data1, where data2, equals data3)
my $data0 = $_[0];
my $data1 = $_[1];
my $data2 = $_[2];
my $data3 = $_[3];
my $delete_data = "0";
my @delete_db_contents, @delete_results;
open(FILE, "$data1") ¦¦ show_error("$data1: Can't open because ($!).");
@delete_db_contents = <FILE>;
close(FILE);
foreach(@delete_db_contents) {
$delete_line = $_;
@delete_working_line = split($delimiter,$_,$fields);
if (($delete_working_line[$data2]) eq ($data3)){
$delete_data = "1";
}else{
if ($delete_data eq "0"){
push(@delete_results,$delete_line);
}else{
$delete_working_line[0] = (int($delete_working_line[0]) - 1);
$deleted_key_augment = $delete_working_line[0] . $delimiter;
for($x=1;$x<=($fields - 1);$x++) {
$deleted_key_augment = $deleted_key_augment . "$delete_working_line[$x]" . $delimiter ;
}
$deleted_key_augment = $deleted_key_augment . "\n";
push(@delete_results,$deleted_key_augment);
}
}
}
open(FILE, ">$data1") ¦¦ show_error("$data1: Can't open because ($!).");
print FILE (@delete_results);
close(FILE);
}

1; #return true
#end: database.pl######################################

#begin: body.cgi########################################
#!/usr/bin/perl
$delimiter = "\t";
require "database.pl";

# Insert (line of data, specific flat file)
my $newdata = ("2" . $delimiter . "4" . $delimiter . "3" . $delimiter . "2" . $delimiter . "6");
&insert($newdata,"file1.db");

# Update (line of data, specific flat file, where field, matches string)
my $newdata = ("3" . $delimiter . "2" . $delimiter . "1" . $delimiter . "1" . $delimiter . "1" . $delimiter . "1");
&update($newdata,"file1.db","0","3");

# Delete (assumes *, specified flat file, where field, matches string)
&delete(0,"file1.db","2","6");

# Select (assumes *, specified flat file, where field, matches string)
my @request = &select("*","file1.db","1","2");
my $workingdata;
foreach(@request) {
$fields=20;
split($delimiter,$_,$fields);
for($x=0;$x<=($fields - 1);$x++) {
$workingdata = $workingdata . "$_[$x]" . $delimiter ;
}
$workingdata = $workingdata . "<br>";
}

print "Content-type: text/html\n\n";
print "$workingdata";

#end: body.cgi###########################################

file1.db is nothing more than a tab delimited flatfile of at least 6 entries as defined above.

[andreasfriedrich]

>>Unfortunatly, not all the servers are in my control to
>>add modules to so I needed an all mobile script solution

If you can add your mobile script then you can add a module as well. You will not be able to add it to the system-wide extension library but you can just add it to any directoryt that you can write to. Then all you need to do is

[url=http://www.perldoc.com/perl5.8.0/pod/func/use.html]use[/url] [url=http://www.perldoc.com/perl5.8.0/lib/lib.html]lib[/url] '/path/to/module';

.

Andreas