Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

IMPORT DATA via CGI script

Quote Reply
IMPORT DATA via CGI script
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:

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
Quote Reply
Re: IMPORT DATA via CGI script In reply to
If you've got a properly (from the standpoint of Links) delimited file, I would think the easiest thing would be to simply import it through MySQLMan or phpMyAdmin's upload/import utilities. That would update the Links table, and I believe any other related tables would be properly updated by re-indexing.

Dan

Quote Reply
Re: IMPORT DATA via CGI script In reply to
Thanks, Dan...HOWEVER, I want to automatically UPLOAD/IMPORT the data via a CGI script that can be executed by Cron...Sorry, I probably was NOT clear about this in my original post.

I do know how to use MySQLMan quite well, thank you.

Regards,

Eliot Lee
Quote Reply
Re: IMPORT DATA via CGI script In reply to
I figured there had to be a reason you weren't going that route, but I couldn't find it above...

Dan

Quote Reply
Re: IMPORT DATA via CGI script In reply to
Hi Eliot,

You have:

$values{$_} ||= $search_def->{$_};

Which is setting values from the hash %values to the defaults. However, your data to be imported is in the array @values which is never used. Try:

# Better to pull this automatically, but this is easier.
my @cols = ('SearchID', 'URL', 'Title', 'Description', 'Size', 'DateModified');
# Pull one line of data into values hash.
my $i = 0;
%values = map { $cols[$i++] => $_ } split /\Q$db_delim\E/;

instead. Let me know if this doesn't make sense.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: IMPORT DATA via CGI script In reply to
Thanks, Alex...

Your comments make perfect sense...just have to play around with the script a bit more...almost got it. Smile

Regards,

Eliot Lee