Gossamer Forum
(Advanced Search)
for
Home : Products : Gossamer Links : Development, Plugins and Globals :

Plugin Import to custom table

May 16, 2006, 12:36 AM
rgbworld
User (325 posts)
rgbworld's avatar
May 16, 2006, 12:36 AM
Post #1 of 4
Views: 2604
Shortcut
Quote Reply
Plugin Import to custom table
I need to INSERT 35,000+ records into a custom table created and used by a plugin.
I am currently doing the insert, by manually running an SQL query "from a file" using MySQLMan.
I would like to import the data 'during' the plugin install OR via a setup button 'after' installing the plugin.
I am open to pros/cons of each method.

I have already created the table, and extracted an .sql file to a tmp directory.
Code:
my $creator = $DB->creator ('ZipCodes');
my $error;

$creator->cols ( {
ZipCode => { pos => 1, type => 'varchar', size => '10', not_null => 1, default => ''},
Latitude => { pos => 2, type => 'double', not_null => 0, default => '0'},
Longitude => { pos => 3, type => 'double', not_null => 0, default => '0' },
State => { pos => 4, type => 'char', size => '2', not_null => 0, default => '' }
});

# set the primary key, auto-increment and index
$creator->pk(['ZipCode','Latitude','Longitude']);


if (! $creator->create()) {
$GT::SQL::errcode ||= ''; #silence -w warning.
$GT::SQL::errcode eq 'TBLEXISTS' ? ($error = "Could not create table ZipCodes (table already exists)\n") :
($error = "Could not create table ZipCodes: $GT::SQL::error)");
}

$creator->load_table();

$file = $tar->get_file('ZipCodes.sql');
$file->name("$CFG->{admin_root_path}/tmp/ZipCodes.sql");
$file->write or return Plugins::ZipCodeSearch->error("Unable to extract file '$CFG->{admin_root_path}/tmp/ZipCodes.sql': $GT::Tar::error", 'WARN');

Currently the ZipCodes.sql file contains a crapload of insert statements....
INSERT INTO `glinks_ZipCodes` (ZipCode, Latitude, Longitude, State) VALUES ('00601', 18.180103, -66.749472, 'PR');
INSERT INTO `glinks_ZipCodes` (ZipCode, Latitude, Longitude, State) VALUES ('00602', 18.363285, -67.180247, 'PR');
...

Problems:
The INSERT INTO specifies the table with a prefix of glinks_
I need a more generic insert that will insert into ZipCodes table regardless of the prefix, because I have
a different install of Gossamer Links that uses a prefix of lsql_

So, I should just have a text file of comma separated values without multiple INSERT statements correct? i.e.
INSERT INTO glinks_ZipCodes VALUES('00601','18.180103','-66.749472','PR'),('00602','18.363285','-67.180247','PR') ...
Ok, well I just made a new file reflected in the above line by dumping, using multiple-row output option.

I think all I need to know is how to run the sql file from the plugin's install sub.
the table name is easily created using:

my $prefix_plus_table = $DB->prefix;
$prefix_plus_table .= 'Links';

So close, just not sure how to finish, and it's late.

Thanks for any help, comments or suggestions.

Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com
May 16, 2006, 11:32 PM
rgbworld
User (325 posts)
rgbworld's avatar
May 16, 2006, 11:32 PM
Post #2 of 4
Views: 2516
Shortcut
Quote Reply
Re: [rgbworld] Plugin Import to custom table In reply to
Currently, the following code is running during 'install' sub. And doesnt work yet Unsure
The code in red is the real problem. The table gets created, and no errors are reported,
but the table is empty after installing the plugin.

Code:
my $creator = $DB->creator ('ZipCodes');
my $error;

$creator->cols ( {
ZipCode => { pos => 1, type => 'varchar', size => '10', not_null => 1, default => ''},
City => { pos => 2, type => 'varchar', size => '10', not_null => 1, default => ''},
State => { pos => 3, type => 'char', size => '2', not_null => 0, default => '' },
Latitude => { pos => 4, type => 'double', not_null => 0, default => '0'},
Longitude => { pos => 5, type => 'double', not_null => 0, default => '0' },
TimeZone => { pos => 6, type => 'char', size => '4', not_null => 0, default => '' },
DST => { pos => 7, type => 'char', size => '1', not_null => 0, default => '' }
});

# set the primary key, auto-increment and index
$creator->pk(['ZipCode','Latitude','Longitude']);


if (! $creator->create()) {
$GT::SQL::errcode ||= ''; #silence -w warning.
$GT::SQL::errcode eq 'TBLEXISTS' ? ($error = "Could not create table ZipCodes (table already exists)\n") :
($error = "Could not create table ZipCodes: $GT::SQL::error)");
}

$creator->load_table();


my @vals;
my $db_delim = ',';
my $zipcode_table_name = 'ZipCodes';
my $zipcode_table = $DB->table($zipcode_table_name) || return $GT::SQL::error;
my $sth = $zipcode_table->prepare ("INSERT INTO $zipcode_table_name VALUE (?, ?, ?, ?, ?, ?, ?)");

open (FILE, "$CFG->{admin_root_path}/tmp/zipcode.csv") || return $GT::SQL::error;
while (<FILE>) {
chomp;
@vals = split /$db_delim/;
$sth->execute (@vals);
}
close FILE;
$sth->finish;

The example I am borrowing from:
Code:
my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare ("INSERT INTO table VALUE (?, ?, ..., ?)");
open (FILE, "/path/to/file.txt") or die $!;
while (<FILE>) {
chomp;
@vals = split /DELIMITER/;
$sth->execute (@vals);
}
close FILE;
$sth->finish;
$dbh->disconnect;

The differences I can see is that I am using a variable in place of 'table'. Is that ok?
Also, VALUE (?, ?, ..., ?). The example is not correct is it?
I have 7 fields, so added 7 '?' marks

I will need to be able to re-arrange the columns as they are inserted, because
I will be importing from csv text files that may be in a different order than my table.

Src data Ex #1:
"zip","city","state","latitude","longitude","timezone","dst"
"00210","Portsmouth","NH","43.005895","-71.013202","-5","1"


Src data Ex #2:
"ZIP","State","Town","Latitude1","Longitude1"
"99501","AK","ANCHORAGE",61.223145,-149.852843

So, besides fixing the insert to work, will I be able to import from diff sources
that may even have a diff number of columns?


I did find one other example here:
http://www.gossamer-threads.com/perl/gforum/gforum.cgi?post=133013#133013
Is this more relevent?

Thanks again for any assistance.
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
May 17, 2006, 12:13 AM
rgbworld
User (325 posts)
rgbworld's avatar
May 17, 2006, 12:13 AM
Post #3 of 4
Views: 2511
Shortcut
Quote Reply
Re: [rgbworld] Plugin Import to custom table In reply to
This is from my error log. I'll work on it more tomorrow.
Code:

[Tue May 16 23:27:51 2006] [error] [client 24.211.29.110] GT::SQL::Table (23385): Failed to execute query: 'INSERT INTO ZipCodes VALUE (?, ?, ?, ?, ?, ?, ?)': Table 'supportmusicians_com_-_links_dev.ZipCodes' doesn't exist at /home/virtual/site129/fst/var/www/cgi-bin/links_dev/admin/GT/SQL/Driver/sth.pm line 101., referer: http://www.supportmusicians.com/cgi-bin/links_dev/admin/admin.cgi?do=page&page=plugin_manager.html&plugin_man_do=pre_install&plugin_name=ZipCodeSearch
[Tue May 16 23:27:51 2006] [error] [client 24.211.29.110] GT::SQL::Driver::MYSQL::sth (23385): Executing query: INSERT INTO ZipCodes VALUE ('"99928"', '"Ward Cove"', '"AK"', '"55.395359"', '"-131.67537"', '"-9"', '"1"') at GT::SQL::Driver::sth::execute line 78., referer: http://www.supportmusicians.com/cgi-bin/links_dev/admin/admin.cgi?do=page&page=plugin_manager.html&plugin_man_do=pre_install&plugin_name=ZipCodeSearch
[Tue May 16 23:27:51 2006] [error] [client 24.211.29.110] GT::SQL::Table (23385): Failed to execute query: 'INSERT INTO ZipCodes VALUE (?, ?, ?, ?, ?, ?, ?)': Table 'supportmusicians_com_-_links_dev.ZipCodes' doesn't exist at /home/virtual/site129/fst/var/www/cgi-bin/links_dev/admin/GT/SQL/Driver/sth.pm line 101., referer: http://www.supportmusicians.com/cgi-bin/links_dev/admin/admin.cgi?do=page&page=plugin_manager.html&plugin_man_do=pre_install&plugin_name=ZipCodeSearch
[Tue May 16 23:27:51 2006] [error] [client 24.211.29.110] GT::SQL::Driver::MYSQL::sth (23385): Executing query: INSERT INTO ZipCodes VALUE ('"99929"', '"Wrangell"', '"AK"', '"56.409507"', '"-132.33822"', '"-9"', '"1"') at GT::SQL::Driver::sth::execute line 78., referer: http://www.supportmusicians.com/cgi-bin/links_dev/admin/admin.cgi?do=page&page=plugin_manager.html&plugin_man_do=pre_install&plugin_name=ZipCodeSearch
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
May 17, 2006, 12:41 AM
rgbworld
User (325 posts)
rgbworld's avatar
May 17, 2006, 12:41 AM
Post #4 of 4
Views: 2515
Shortcut
Quote Reply
Re: [rgbworld] Plugin Import to custom table In reply to
Sorry to keep posting, but I almost got it!
I did get INSERTed data 42,000 rows, but I have to eliminate quote marks
because the record data looks like:
"00210" "Portsmout "N 0 0 "-5" "
I know I also have to fix my field lengths, etc, but I just hacked it together
for a different csv data file. The fix was adding the prefix to the table in the INSERT statement:
Code:
my @vals;
my $db_delim = ',';
my $zipcode_table_name = 'ZipCodes';
my $prefix_plus_table = $DB->prefix;
$prefix_plus_table .= $zipcode_table_name;
my $zipcode_table = $DB->table($zipcode_table_name) || return $GT::SQL::error;
my $sth = $zipcode_table->prepare ("INSERT INTO $prefix_plus_table VALUE (?, ?, ?, ?, ?, ?, ?)");

open (FILE, "$CFG->{admin_root_path}/tmp/zipcode.csv") || return $GT::SQL::error;
while (<FILE>) {
chomp;
@vals = split /$db_delim/;
$sth->execute (@vals);
}
close FILE;
$sth->finish;



So if someone could just help me drop the quotes, I can clean it up from there.
Other than still wondering about how to do diff data files with diff qty and order of columns Crazy
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com