Приглашаем посетить
db-lib
# Name: db-lib.pl
# Author: Gunther Birznieks (some commenting by Selena Sol)
# Date Created: 11-15-96
# Date Last Modified: 11-26-96
#
# Copyright Info: This library was written by Gunther Birznieks
# (birzniek@hlsun.redcross.org) having been inspired by countless
# other Perl authors. Feel free to copy, cite, reference, sample,
# borrow, resell or plagiarize the contents. However, if you don't
# mind, please let me know where it goes so that I can at least
# watch and take part in the development of the memes. Information
# wants to be free, support public domain freware. Donations are
# appreciated and will be spent on further upgrades and other public
# domain scripts.
#
# Description: This library contains the routines that the Web store uses to
# interface with a flatfile (plain ASCII text file) database file.
#################################################################
# submit_query Subroutine #
#################################################################
# This subroutine is the primary interface for your main
# program. The following sections explain how youcall the
# subroutine and what you should expect in response.
#
# Usage: ($row_count) = &submit_query(*db_rows);
#
# Parameters:
#
# *db_rows = an empty array is passed by reference
# so that it can be filled with the contents of
# the rows that satisfy the query.
#
# Output:
#
# $row_count = amount of rows that satisfied query
# even if the count exceeded the maximum allowed.
# This row count will never actually exceed
# 1 above the row count in the flat file version
# of this routine because it is inefficient to
# keep reading a text file if we do not
# intend to present the user with the subsequent
# information.
#
# *db_rows = an array where each row is a row that
# satisfied the results of the query. The rows
# stop being added to this array if $max_rows
# setup variable is exceeded.
#
# Each row contains the fields in a PIPE delimited
# form.
sub submit_query
{
# The subroutine begins by defining variables that we will
# be using within this subroutine. We will define them as
# local so that this subroutine will not affect variables
# used in the main routine.
#
# database_rows will be an array which will point back to
# the array given to us by the main routine when it called
# us. We are going to fill that array with the actual
# database rows which were determined to be "hits".
#
# @fields will be an array that we will use to temporarily
# use to hold database fields while we are working.
#
# $row_count is used to keep track of how many rows are
# hits so that we can cut off processing when we have
# reached the limit of rows to be returned defined in the
# setup file.
#
# @not_found_criteria will be an array we will use to keep
# track of whether or not we have completely satisfied the
# search term(s) submitted by the user.
#
# $line will be used as a temporary storage variable for
# the current line in the database that we are reading.
#
# exact_match and case_sensitive are special form
# variables which alter the behavior of keyword searches
# (string data type with the = operator).
#
# You can cause your search to be case sensitive or based
# on whole word matching by uincluding those variables
# within your HTML code. For example, you might have the
# following checkboxes so that the user can specify what
# type of search they want:
#
# Exact Match?
# <INPUT TYPE = "checkbox" NAME = "exact_match">
#
# Case Sensitive Search?
# <INPUT TYPE = "checkbox" NAME = "case_sensitive">
#
# Thus, if the user clicks on the HTML checkbox, the
# form variable will be set to "on". We will use that
# information within this routine when we decide what
# actually counts as a "hit".
#
# Normally, of course, keyword searches are case
# insensitive and are not exact match searches.
local(*database_rows) = @_;
local(@fields);
local($row_count);
local(@not_found_criteria);
local($line);
local($exact_match) = $form_data{'exact_match'};
local($case_sensitive) = $form_data{'case_sensitive'};
# Now that it has set up its working space, the script
# initializes row count to 0.
$row_count = 0;
# Then, the subroutine opens the data file and checks to
# see if there was an error doing this. If there was an
# error, we will use the file_open_error subroutine
# documented at the end of this file to process the error
# in a meaningful way.
open(DATAFILE, "$data_file_path") ||
&file_open_error("$data_file_path",
"Read Database",__FILE__,__LINE__);
# If there was no error opening it, then the subroutine
# reads each line into $line until the file ends.
while(($line = <DATAFILE>))
{
# As we work our way through the datafile we will process
# the rows to see if they match our search parameters.
# First the script will skip over any "comment" line in
# the datafile. Comment lines are denoted as beginning
# (^) with the flag "COMMENT:". The newline charcater
# will also be stripped off every row before it is
# processed.
unless ($line =~ /^COMMENT:/)
{
chop($line); # Chop off extraneous newline
# Then each row is split into its database field based on
# the pipe (|) delimiter. Note that if you must change
# your datafile to a delimiter other than the pipe symbol,
# you will have to modify this line. However, we
# recommend using the pipe delimter if posible.
@fields = split(/\|/, $line);
# Once we have gathered all of the database fields for the
# current database row as separate elements in the @fields
# array, we can begin to process them, checking to
# see if they match the client-submitted criteria.
#
# First, we set not_found to zero which indicates that we
# are assuming the criteria was satisfied for the row.
#
# Then, for each criteria specified in @db_query_criteria,
# we call the flatfile_apply_criteria subroutine
# (documented later in this file) to apply the criteria.
# If the criteria is not satisfied, it keeps returning 1
# which would increment $not_found.
#
# Thus, $not_found will end up being the number of
# criteria that were not found, thus a "no match". a
# zero, on the other hand, means success.
$not_found = 0;
foreach $criteria (@db_query_criteria)
{
$not_found += &flatfile_apply_criteria(
$exact_match,
$case_sensitive,
*fields,
$criteria);
}
# If not found is still 0, the row is pushed into the
# @database_rows array. We will use this array to hold
# all of the rows which met the user-submitted search
# criteria until we are ready to display them all to the
# user.
if ($not_found == 0)
{
push(@database_rows, join("\|", @fields));
}
# Once we have gone all the way through the data file
# we are ready to display them to the user.
#
# However, we also want to increment row_count.
# That way, we will be able to report how many hits were
# scored even if we only are displaying a partial list.
#
# Recall that when not_found = 0, that means that the
# criteria was satisfied for the row.
if ($not_found == 0)
{
$row_count++;
}
} # End of unless ($line =~ /^COMMENT:/)
} # End of while datafile has data
# Finally, we close the datafile.
close (DATAFILE);
# Now we need to reply to the main program which called
# us. Recall that this subroutine was passed
# @database_rows by reference so that we do not need to
# send that information back to the main routine, it has
# already been receiving it! However, we will want to
# pass back some of the information we gained while
# processing the user-submitted criteria.
#
# We will return row_count so that the main program
# will be able to report to the user how many hits were
# registered based on their criteria.
return($row_count);
} # End of submit query
#################################################################
# flatfile_apply_criteri Subroutine #
#################################################################
# This subroutine is used to actually determine if the
# current database row being processed will satisfy the
# search criteria that the user submitted.
# It is called with the following syntax
#
# $status = &flatfile_apply_criteria(
# $exact_match,
# $case_sensitive,
# *fields,
# $criteria);
#
# $exact_match specifies whether or not the user has
# asked to perform exact whole word matches on the
# database strings. Typically, you will have the
# following HTML code to provide this option.
#
# <INPUT TYPE = "checkbox" NAME = "exact_match">
#
# Thus, if the user clicks the checkbox, the value will be
# set to "on".
#
# $case_sensitive works just the same way and defines
# whether the user has asked to perform case sensitive
# matches on the database strings. You can use the
# following HTML code to provide the option:
#
# <INPUT TYPE = "checkbox" NAME = "case_sensitive">
#
# *fields is a reference to the array of fields
# in the current database row that we are
# searching.
#
# $criteria is the current criteria that we are applying
# to the database row. The criteria is gathered from the
# @query_criteria array from the setup file.
#
# After the subroutine processes the request, it
# will return astatus indicating whether the criteria was
# not found or not. If it is not found, a 1
# is returned. If it is, then a 0 is returned.
sub flatfile_apply_criteria
{
# Initially, the variables passed to this subroutine are
# initialized as local variables. $exact_match,
# $case_sensitive, and *fields have been explained
# above.
#
# $criteria, however, could use some explanation. To
# remind, this subroutine was called by submit_query above
# in a foreach loop. Specifically, submit_query calls
# this subroutine for each criteria in the
# @db_query_criteria array which is defined in the setup
# file.
#
# these criteria elements are pipe delimited and consist
# of 1) the form variable name that the criteria will be
# matched against, 2) the fields in the database
# which will be matched against, 3) the operator to use in
# comparison, and finally, 4) the data type that the
# operator should use in the comparison (date, number, or
# string comparison).
#
# Consider the following example
#
# @db_query_criteria = ("fname|0|=|string",
# "age_low|9|<=|number",
# "age_high|9|>=|number");
#
# In this example, the subroutine will take the current
# row and match it against the user-defined values for
# fname, age_low and age_high. It will expect that the
# fname field will be field 0 and the age field will be
# field 9. It will perform a keyword match on the fname
# value and will perform range calculation on the age
# field.
#
# As we are comparing user-defined criteria against the
# actual database info, we will use several working
# variables to define the 4 elements of each $criteria.
# These will be $c_name, $c_fields, $c_op, $c_type
#
# @criteria_fields will collect those values in an array
#
# $not_found will flag us as to whether we found a match
#
# $form_value will be used to temporarily hold the
# user-defined info coming in from the form while
# $db_value will be set equal to the actual value of the
# field in the database that the $form_value should be
# compared against.
#
# $month, $year, $day, $db_date, and $form_date are used
# as date comparison place holders
#
# $db_index is a place marker for current database field
# index we are looking at.
#
# @word_list is the list of words in a string for matching
local($exact_match, $case_sensitive,
*fields, $criteria) = @_;
local($c_name, $c_fields, $c_op, $c_type);
local(@criteria_fields);
local($not_found);
local($form_value);
local($db_value);
local($month, $year, $day);
local($db_date, $form_date);
local($db_index);
local(@word_list);
# Now that we have defined our working environment, we are
# going to take the current criteria element that we are
# matching for and break it up into its 4 component parts.
($c_name, $c_fields, $c_op, $c_type) = split(/\|/, $criteria);
# Next, we will perform a second split on the $c_fields
# value because the criteria can match more than ONE
# field in the database! For example, you may want to
# allow the user to do a keyword match on several database
# fields.
#
# Thus, we get the index values of the fields in each row
# of the database that the form variable will be compared
# against. Note that these index numbers are comma
# delimited.
#
# Remember, fields and lists in perl start counting at 0.
@criteria_fields = split(/,/,$c_fields);
# Next, the subroutine gets the value of the form.
$form_value = $form_data{$c_name};
# Once we have that, we are ready to apply the
# criteria and the user-defined form value to the actual
# database. However, there are three cases of comparison
# that will return a value each with its own slightly
# different processing logic.
#
# Case 1: The form field for the criteria was not filled
# out, so the match is considered a success.
#
# Remember, if the user does not enter a keyword, we want
# the search to be open-ended. That is, we only restrict
# the search if the user chooses to enter a search word
# into the appropriate query field.
if ($form_value eq "")
{
return 0;
}
# Case 2: The data type is a number or a date OR the
# data type is a string and the operator is NOT "=". In
# these cases, we match against the operator directly
# based on the data type. (A string, "=" match is
# considered a separate case below).
#
# Note that we use regular expressions to match the value
# of $c_type to the values of date and number to determine
# if it is the correct type of match. We will use the or
# (||) operator to make sure that if any of the conditions
# are true, we will process them.
if (($c_type =~ /date/i) ||
($c_type =~ /number/i) ||
($c_op ne "="))
{
# First, we set not_found to yes. We assume that the data
# did not match. If any fields match the data submitted by
# the user, then, we will set not_found to no later on
# and we will know that we found a match.
$not_found = "yes";
# Next, the subroutine goes through each database field
# specified in @criteria_fields and compares it to the
# value submitted by the user on the HTML form frontend.
foreach $db_index (@criteria_fields)
{
# The first part of the comparison is to get the value of
# the field in the database that corresponds to the
# index number of the field we need to check.
$db_value = $fields[$db_index];
# Now we can go about comparing the user-submitted data to
# the actual database field value.
#
# However, if the type of data comparison we are doing is
# based on a date compare, then we need to convert the
# date into the format YYYYMMDD instead of MM/DD/YY.
#
# This is because YYYYMMDD is easier to compare directly.
# A date in the form YYYYMMDD can use the normal >,<,etc..
# numerical operators to compare against. Kind've a nifty
# trick, huh?
#
# Besides flipping the date format around, we will also
# format the date from 1 digit months and days (1 --> 01)
# to 2 digit months and days and from 2 digit years to 4
# digit years (87 --> 1987)
if ($c_type =~ /date/i)
{
($month, $day, $year) = split(/\//, $db_value);
$month = "0" . $month
if (length($month) < 2);
$day = "0" . $day
if (length($day) < 2);
$year = ($current_century-1) . $year
if (length($day) < 3);
# Then we will assign the new formatted date to $db_date.
$db_date = $year . $month . $day;
# Next, we will perform the same type of reformatting on
# the user-submitted date that we did for the date in the
# database.
($month, $day, $year) = split(/\//, $form_value);
$month = "0" . $month
if (length($month) < 2);
$day = "0" . $day
if (length($day) < 2);
$year = ($current_century-1) . $year
if (length($day) < 3);
# The user-submitted formatted date is stored in
# $form_date
$form_date = $year . $month . $day;
# Now we can actually compare the date entered against the
# date in the database.
#
# The subroutine supports all of the common comparison
# operators (<, >, <=. >=, =, !=) and the comparisons work
# as usual)
#
# If any of the date comparisons match then a 0 is
# returned to let the submit_query routine know that a
# match was found.
if ($c_op eq ">")
{
return 0 if ($form_date > $db_date);
}
if ($c_op eq "<")
{
return 0 if ($form_date < $db_date);
}
if ($c_op eq ">=")
{
return 0 if ($form_date >= $db_date);
}
if ($c_op eq "<=")
{
return 0 if ($form_date <= $db_date);
}
if ($c_op eq "!=")
{
return 0 if ($form_date != $db_date);
}
if ($c_op eq "=")
{
return 0 if ($form_date == $db_date);
}
} # End of if ($c_type =~ /date/i)
# If the data type is a number then we perform normal
# number comparisons in Perl.
elsif ($c_type =~ /number/i)
{
if ($c_op eq ">")
{
return 0 if ($form_value > $db_value);
}
if ($c_op eq "<")
{
return 0 if ($form_value < $db_value);
}
if ($c_op eq ">=")
{
return 0 if ($form_value >= $db_value);
}
if ($c_op eq "<=")
{
return 0 if ($form_value <= $db_value);
}
if ($c_op eq "!=")
{
return 0 if ($form_value != $db_value);
}
if ($c_op eq "=")
{
return 0 if ($form_value == $db_value);
}
} # End of elsif ($c_type =~ /number/i)
# Finally, if the data type is a string then we take the
# operators and apply the corresponding Perl string
# operation. For example, != is ne, > is gt, etc.
else
{
if ($c_op eq ">")
{
return 0 if ($form_value gt $db_value);
}
if ($c_op eq "<")
{
return 0 if ($form_value lt $db_value);
}
if ($c_op eq ">=")
{
return 0 if ($form_value ge $db_value);
}
if ($c_op eq "<=")
{
return 0 if ($form_value le $db_value);
}
if ($c_op eq "!=")
{
return 0 if ($form_value ne $db_value);
}
} # End of else $ct_type is a string.
} # End of foreach $form_field
} # End of case 2, Begin Case 3
# Case 3: The data type is a string and the operator is =.
# This is more complex because we need to check whether
# our string matching matches whole words or is case
# sensitive.
#
# In otherwords, this is a more "fuzzy" search.
#
# There are two special input form variables which might
# afffect how we perform the search which have been
# explained above: $exact_match, $case_sensitive
#
# In addition, the form_value will be split on whitespace
# so that white-space separated words will be searched
# separately. Thus if the user submitted "hello world",
# the subroutine would only match rows in which "hello"
# and "world" appeared.
else
{
# First, the subroutine takes the words that were entered
# and parses them into an array of words based on word
# boundary (\s+ splits on whitespace)
@word_list = split(/\s+/,$form_value);
# Again, we go through the fields in the database that are
# checked for this particular criteria definition.
foreach $db_index (@criteria_fields)
{
# Also, as before, we obtain the value of the database
# field we are currently matching against and set
# $not_found equal to "yes".
$db_value = $fields[$db_index];
$not_found = "yes";
# This time, however, we will use to new local
# variables. $match_word is a place marker for the words
# we are going to be looking for in the database row.
# $x is a place marker inside the for loops.
local($match_word) = "";
local($x) = "";
# Now we begin searching for matches. Basically, the deal
# is that as the words get found, they get removed
# from the @word_list array.
#
# When the array is empty, we know that all the keywords
# were found.
#
# We will later celebrate this event by returning the fact
# that a match was found for this criteria.
if ($case_sensitive eq "on")
{
if ($exact_match eq "on")
{
for ($x = @word_list; $x > 0; $x--)
{
$match_word = $word_list[$x - 1]; # \b matches on word boundary
if ($db_value =~ /\b$match_word\b/)
{
splice(@word_list,$x - 1, 1);
} # End of If
} # End of For Loop
}
else
{
for ($x = @word_list; $x > 0; $x--)
{
$match_word = $word_list[$x - 1];
if ($db_value =~ /$match_word/)
{
splice(@word_list,$x - 1, 1);
} # End of If
} # End of For Loop
} # End of ELSE
}
else
{
if ($exact_match eq "on")
{
for ($x = @word_list; $x > 0; $x--)
{
$match_word = $word_list[$x - 1];
if ($db_value =~ /\b$match_word\b/i)
{
splice(@word_list,$x - 1, 1);
}
} # End of for ($x = @word_list; $x > 0; $x--)
} # End of if ($exact_match eq "on")
else
{
for ($x = @word_list; $x > 0; $x--)
{
$match_word = $word_list[$x - 1];
if ($db_value =~ /$match_word/i)
{
splice(@word_list,$x - 1, 1);
}
} # End of for ($x = @word_list; $x > 0; $x--)
} # End of else
} # End of else
} # End of foreach $db_index
# If there is nothing left in the word_list we want to say
# that we found the word in the $db_value. Thus,
# $not_found is set to "no" in this case.
if (@word_list < 1)
{
$not_found = "no";
}
} # End of case 3
# If not_found is still equal to yes, we return a 1,
# indicating that the criteria was not satisfied
#
# If not_found is not yes, then we return that a
# successful match was found (0).
if ($not_found eq "yes")
{
return 1;
}
else
{
return 0;
}
} # End of flatfile_apply_criteria
1; # Returns a true value because it is a library file
