Okay...I have read through all the IMPORT FAQs in pugdog's site and none of them address issues of IMPORTING data after the installation process.
Here is my problem...I would like to IMPORT data from a flat file that is created via another script. This flat file contains compressed data from a web site I am developing...basically a search index file.
The file is created with the following fields and separated by pipe delimiter:
ID|URL|Title|Description|SIZE|DATEMODIFIED
I have created a .def file that looks like the following:
package AG::DBSQL::tbl_AG_Search_Index;
$db_name = 'anthrotech4';
$db_host = 'localhost';
$db_driver = 'mysql';
$db_user = 'anthrotech';
$db_pass = 'ant>db100';
$db_key = 'SearchID';
$db_primkey = '';
$db_table = 'tbl_AG_Search_Index';
$db_track = '1';
$attach_dir = '';
%db_def = (
SearchID => ['1', 'INT', '10', '20', '1', '0', '^\d*\.?\d*$'],
URL => ['2', 'VARCHAR', '40', '200', '1', '0', '^\d*\.?\d*$'],
Title => ['3', 'TEXT', '40', '255', '1', '0', '^\d*\.?\d*$'],
Description => ['4', 'LONGTEXT', '40', '10000', '0', '', ''],
Size => ['5', 'TEXT', '11', '11', '0', '', ''],
DateModified => ['6', 'DATE', '20', '20', '1', 'NOW', '^\d{4}\-\d{2}\-\d{2}$']
);
%db_select_fields = ();
%db_checkbox_fields = ();
%db_radio_fields = ();
1;
The tbl_Search_Index table looks like the following:
SearchID int(11) PRI 0 auto_increment
URL varchar(200)
Title text
Description longtext
Size text
DateModified date 0000-00-00
I have attempted to write an IMPORT script based on the IMPORT.PL with a few modifications:
#!/usr/local/bin/perl -w
# IMPORT data INTO SEARCH INDEX TABLE
# File Name: nph-import-search.cgi
# Description: IMPORTS data into SEARCH INDEX table.
#=======================================================================
# Load required modules.
# ---------------------------------------------------
use lib '/mnt/web/guide/anthrotech/ag/bin/admin';
use CGI ();
use CGI::Carp qw/fatalsToBrowser/;
use AG::AG;
use AG::DBSQL;
use AG::DB_Utils;
use vars qw($USE_HTML $SEARCHDB);
$|++;
# Determine whether we should print HTML or text.
$USE_HTML = 0;
$ENV{'REQUEST_METHOD'} and ($USE_HTML = 1);
# Create the DBSQL objects we will use.
$SEARCHDB = new AG::DBSQL $AG{def_files} . "tbl_AG_Search_Index.def";
# Determine what type of build we are doing and do it.
if ($USE_HTML) {
CGI->nph(1);
print CGI->header(), CGI->start_html ( -title => 'Backing up Database' ), CGI->h1('Backing up Database ... '), "<pre>";
}
my $s = time();
&main ();
$USE_HTML and print "</pre>";
sub main {
# --------------------------------------------------------
# Backs up important database files.
#
my $today = $SEARCHDB->get_date;
my $time = $SEARCHDB->get_time;
my $db_delim = '|';
print "\t=========================================\n";
print "\tIMPORT DATA INTO SEARCH INDEX....\n";
print "\t=========================================\n";
print "\tDELETING RECORDS FROM SEARCH INDEX....\n";
print "\t=========================================\n";
$SEARCHDB->do("DELETE FROM tbl_AG_Search_Index");
print "\t=========================================\n";
print "\tSEARCH RECORDS DELETED...\n";
print "\t=========================================\n";
# INSERT SEARCH INDEX
#-------------------------------------------------------------------------
eval {
open (DB, "$AG{db_data_path}/search_index.db") or die "import: can't open '$AG{db_data_path}/search_index.db' ($!)";
my $search_def = $SEARCHDB->get_defaults();
while (<DB>) {
chomp;
s/\Q$db_delim\ENULL\Q$db_delim\E/$db_delim$db_delim/g;
@values = split /\Q$db_delim\E/;
@values;
foreach (keys %$search_def) {
$values{$_} ||= $search_def->{$_};
}
eval {
$res = $SEARCHDB->add_record (\%values);
};
$@ and print "Fatal: $@\n$.: @values\n" and next;
$res or print "Error: $AG::DBSQL::error\n$.: @values\n" and next;
}
print "\t=========================================\n";
print "\tScript Completed on $today at $time\n";
print "\t=========================================\n";
close DB;
}
}
And when I execute the script, the only field that is successfully imported is the DateModified. The other columns are blank. And also, twice as many records (32) that are actual in the flat file (16) are imported (I know this is probably a bug with the index file creation script.)
So, I guess in a long-winded fashion, I am asking how to write a simple IMPORT script to accomplish my above stated goal. (BTW: I did try simply using the load_data sub call in the DBSQL.pm file and it didn't work...same result...only the DateModified was imported.)
Any suggestions or directions would be greatly appreciated.
Thanks in advance.
Regards,
Eliot Lee
Here is my problem...I would like to IMPORT data from a flat file that is created via another script. This flat file contains compressed data from a web site I am developing...basically a search index file.
The file is created with the following fields and separated by pipe delimiter:
ID|URL|Title|Description|SIZE|DATEMODIFIED
I have created a .def file that looks like the following:
Code:
package AG::DBSQL::tbl_AG_Search_Index;
$db_name = 'anthrotech4';
$db_host = 'localhost';
$db_driver = 'mysql';
$db_user = 'anthrotech';
$db_pass = 'ant>db100';
$db_key = 'SearchID';
$db_primkey = '';
$db_table = 'tbl_AG_Search_Index';
$db_track = '1';
$attach_dir = '';
%db_def = (
SearchID => ['1', 'INT', '10', '20', '1', '0', '^\d*\.?\d*$'],
URL => ['2', 'VARCHAR', '40', '200', '1', '0', '^\d*\.?\d*$'],
Title => ['3', 'TEXT', '40', '255', '1', '0', '^\d*\.?\d*$'],
Description => ['4', 'LONGTEXT', '40', '10000', '0', '', ''],
Size => ['5', 'TEXT', '11', '11', '0', '', ''],
DateModified => ['6', 'DATE', '20', '20', '1', 'NOW', '^\d{4}\-\d{2}\-\d{2}$']
);
%db_select_fields = ();
%db_checkbox_fields = ();
%db_radio_fields = ();
1;
The tbl_Search_Index table looks like the following:
Code:
SearchID int(11) PRI 0 auto_increment
URL varchar(200)
Title text
Description longtext
Size text
DateModified date 0000-00-00
I have attempted to write an IMPORT script based on the IMPORT.PL with a few modifications:
Code:
#!/usr/local/bin/perl -w
# IMPORT data INTO SEARCH INDEX TABLE
# File Name: nph-import-search.cgi
# Description: IMPORTS data into SEARCH INDEX table.
#=======================================================================
# Load required modules.
# ---------------------------------------------------
use lib '/mnt/web/guide/anthrotech/ag/bin/admin';
use CGI ();
use CGI::Carp qw/fatalsToBrowser/;
use AG::AG;
use AG::DBSQL;
use AG::DB_Utils;
use vars qw($USE_HTML $SEARCHDB);
$|++;
# Determine whether we should print HTML or text.
$USE_HTML = 0;
$ENV{'REQUEST_METHOD'} and ($USE_HTML = 1);
# Create the DBSQL objects we will use.
$SEARCHDB = new AG::DBSQL $AG{def_files} . "tbl_AG_Search_Index.def";
# Determine what type of build we are doing and do it.
if ($USE_HTML) {
CGI->nph(1);
print CGI->header(), CGI->start_html ( -title => 'Backing up Database' ), CGI->h1('Backing up Database ... '), "<pre>";
}
my $s = time();
&main ();
$USE_HTML and print "</pre>";
sub main {
# --------------------------------------------------------
# Backs up important database files.
#
my $today = $SEARCHDB->get_date;
my $time = $SEARCHDB->get_time;
my $db_delim = '|';
print "\t=========================================\n";
print "\tIMPORT DATA INTO SEARCH INDEX....\n";
print "\t=========================================\n";
print "\tDELETING RECORDS FROM SEARCH INDEX....\n";
print "\t=========================================\n";
$SEARCHDB->do("DELETE FROM tbl_AG_Search_Index");
print "\t=========================================\n";
print "\tSEARCH RECORDS DELETED...\n";
print "\t=========================================\n";
# INSERT SEARCH INDEX
#-------------------------------------------------------------------------
eval {
open (DB, "$AG{db_data_path}/search_index.db") or die "import: can't open '$AG{db_data_path}/search_index.db' ($!)";
my $search_def = $SEARCHDB->get_defaults();
while (<DB>) {
chomp;
s/\Q$db_delim\ENULL\Q$db_delim\E/$db_delim$db_delim/g;
@values = split /\Q$db_delim\E/;
@values;
foreach (keys %$search_def) {
$values{$_} ||= $search_def->{$_};
}
eval {
$res = $SEARCHDB->add_record (\%values);
};
$@ and print "Fatal: $@\n$.: @values\n" and next;
$res or print "Error: $AG::DBSQL::error\n$.: @values\n" and next;
}
print "\t=========================================\n";
print "\tScript Completed on $today at $time\n";
print "\t=========================================\n";
close DB;
}
}
And when I execute the script, the only field that is successfully imported is the DateModified. The other columns are blank. And also, twice as many records (32) that are actual in the flat file (16) are imported (I know this is probably a bug with the index file creation script.)
So, I guess in a long-winded fashion, I am asking how to write a simple IMPORT script to accomplish my above stated goal. (BTW: I did try simply using the load_data sub call in the DBSQL.pm file and it didn't work...same result...only the DateModified was imported.)
Any suggestions or directions would be greatly appreciated.
Thanks in advance.
Regards,
Eliot Lee
IMPORT DATA via CGI script