From 09c85809a91e4691943154f854dcec49f2e7f4e8 Mon Sep 17 00:00:00 2001 From: lingutln Date: Mon, 20 May 2013 20:21:33 +0000 Subject: [PATCH] Latest commits. All queries are changes to use stored procedures. svn path=/; revision=481 --- .../ia_upload/annotation_data_importer.pl | 130 +++++++++--------- .../ia_upload/stored_procedures.sql | 24 +++- 2 files changed, 85 insertions(+), 69 deletions(-) diff --git a/image_annotation_db/ia_upload/annotation_data_importer.pl b/image_annotation_db/ia_upload/annotation_data_importer.pl index 600b47c..dfc5101 100644 --- a/image_annotation_db/ia_upload/annotation_data_importer.pl +++ b/image_annotation_db/ia_upload/annotation_data_importer.pl @@ -37,6 +37,12 @@ sub establish_db_connection } +sub null_if_empty +{ + return (defined $_[0]) ? trim($_[0]) : 'NULL'; +} + + sub insert_collection_data_into_database { my($dbh, $collection_data) = @_; @@ -44,10 +50,10 @@ sub insert_collection_data_into_database #creating image_source - my($source_name) = trim($collection_data_fields[0]); - my($url) = trim($collection_data_fields[1]); - my($contact_email) = trim($collection_data_fields[3]); - my($contributor_name) = trim($collection_data_fields[2]); + my($source_name) = null_if_empty($collection_data_fields[0]); + my($url) = null_if_empty($collection_data_fields[1]); + my($contact_email) = null_if_empty($collection_data_fields[3]); + my($contributor_name) = null_if_empty($collection_data_fields[2]); my $image_source_id; my $sth = $dbh->prepare("call import_image_source('$source_name', '$url', '$contact_email', '$contributor_name');"); @@ -57,9 +63,9 @@ sub insert_collection_data_into_database #creating image_source_version - my($source_version) = trim($collection_data_fields[6]); - my($contribution_date) = trim($collection_data_fields[4]); - my($publication_id) = trim($collection_data_fields[5]); + my($source_version) = null_if_empty($collection_data_fields[6]); + my($contribution_date) = null_if_empty($collection_data_fields[4]); + my($publication_id) = null_if_empty($collection_data_fields[5]); my $image_source_version_id; my $sth = $dbh->prepare("call import_image_source_version('$image_source_id', '$source_version', '$contribution_date', '$publication_id');"); @@ -67,6 +73,7 @@ sub insert_collection_data_into_database $sth->bind_col(1, \$image_source_version_id); $sth->fetch(); + return $image_source_version_id; } @@ -74,15 +81,14 @@ sub insert_collection_data_into_database sub insert_image_data_into_database { - my($dbh, $image_data) = @_; + my($dbh, $image_data, $image_source_version_id) = @_; my @image_data_fields = split(/\t/, $image_data); - # Creating curator data and storing the respective id my($curator_first_name, $curator_last_name) = split(/ /, trim($image_data_fields[6]), 2); - my $curator_email = trim($image_data_fields[7]); - my $curator_affiliation = trim($image_data_fields[8]); + my $curator_email = null_if_empty($image_data_fields[7]); + my $curator_affiliation = null_if_empty($image_data_fields[8]); my $curator_id; my $sth = $dbh->prepare("call import_curator('$curator_first_name', '$curator_last_name', '$curator_email', '$curator_affiliation');"); @@ -91,11 +97,10 @@ sub insert_image_data_into_database $sth->fetch(); - # Creating taxon data and storing the respective id - my $species = trim($image_data_fields[4]); + my $species = null_if_empty($image_data_fields[4]); my ($genus, $species_name) = split(/\s/, $species, 2); - my $species_id = trim($image_data_fields[5]); + my $species_id = null_if_empty($image_data_fields[5]); my $taxon_id; my $sth = $dbh->prepare("call import_taxon('$species_id', '$species_name', '$genus');"); @@ -104,11 +109,11 @@ sub insert_image_data_into_database $sth->fetch(); # Forming image_path by concatinating import_location and filename - my $image_path = trim($image_data_fields[1]) . trim($image_data_fields[0]); + my $image_path = null_if_empty($image_data_fields[1]) . null_if_empty($image_data_fields[0]); # preprocessing ip_comment data - my $ip_comment = trim($image_data_fields[12]); - my $source_db = trim($image_data_fields[14]); + my $ip_comment = null_if_empty($image_data_fields[12]); + my $source_db = null_if_empty($image_data_fields[14]); my $source_db_name; my $source_db_id; @@ -116,13 +121,16 @@ sub insert_image_data_into_database { ($source_db_name, $source_db_id) = split(/:/, $source_db, 2); } - my $doi = trim($image_data_fields[13]); - my $collection_location = trim($image_data_fields[9]); - my $collection_date = trim($image_data_fields[11]); - my $comments = trim($image_data_fields[15]); + + $source_db_name = null_if_empty($source_db_name); + $source_db_id = null_if_empty($source_db_id); + my $doi = null_if_empty($image_data_fields[13]); + my $collection_location = null_if_empty($image_data_fields[9]); + my $collection_date = null_if_empty($image_data_fields[11]); + my $comments = null_if_empty($image_data_fields[15]); my $annotated_image_id; - my $sth = $dbh->prepare("call import_annotated_image('$curator_id', '$taxon_id', '$image_path', '$collection_location', '$collection_date', '$ip_comment', '$doi', '$source_db_name', '$source_db_id', '$comments');"); + my $sth = $dbh->prepare("call import_annotated_image('$curator_id', '$image_source_version_id', '$taxon_id', '$image_path', '$collection_location', '$collection_date', '$ip_comment', '$doi', '$source_db_name', '$source_db_id', '$comments');"); $sth->execute(); $sth->bind_col(1, \$annotated_image_id); $sth->fetch(); @@ -134,14 +142,18 @@ sub insert_image_data_into_database foreach (0..(scalar(@keywords)-1)) { - #creating annotated_term - $dbh->do('INSERT INTO annotated_term (keyword, ontology_term_id) VALUES(?, ?)', undef, $keywords[$_], $ontology_term_ids[$_]); - my $annotated_term_id = $dbh->{'mysql_insertid'}; + my $annotated_term_id; + my $sth = $dbh->prepare("call import_annotated_term('$keywords[$_]', '$ontology_term_ids[$_]');"); + $sth->execute(); + $sth->bind_col(1, \$annotated_term_id); + $sth->fetch(); - #creating annotated_term_image - $dbh->do('INSERT INTO annotated_term_image (annotated_term_id, annotated_image_id) VALUES(?, ?)', undef, $annotated_term_id, $annotated_image_id); - my $annotated_term_image_id = $dbh->{'mysql_insertid'}; + my $annotated_term_image_id; + my $sth = $dbh->prepare("call import_annotated_term_image('$annotated_term_id', '$annotated_image_id');"); + $sth->execute(); + $sth->bind_col(1, \$annotated_term_image_id); + $sth->fetch(); } @@ -155,54 +167,38 @@ sub import_image_data my $dbh = establish_db_connection; - print "Reading content of each collection specification data(IADB_coll_spec_*.tsv) file and reading data...\n\n"; - - foreach (glob(IMAGE_DATA_DIR."/IADB_coll_spec_*.tsv")) - { + print "Reading content of collection specification data(IADB_coll_spec_*.tsv) file and reading data...\n\n"; - my $content = do { - local $/ = undef; - open (my $file, "<", $_) or die("could not open $_: $!"); - <$file>; - }; + my @collection_files = glob(IMAGE_DATA_DIR."/IADB_coll_spec_*.tsv"); + my $collection_content = do { + local $/ = undef; + open (my $file, "<", $collection_files[0]) or die("could not open $collection_files[0]: $!"); + <$file>; + }; - chomp; + my @collection_spec_records = split(/\n/, $collection_content); - my @collection_spec_records = split(/\n/, $content); - - shift @collection_spec_records; + shift @collection_spec_records; - foreach (@collection_spec_records) - { - insert_collection_data_into_database($dbh, $_); - } + my $image_source_version_id = insert_collection_data_into_database($dbh, $collection_spec_records[0]); + - } + print "Reading content of image data(IADB_img_data_*.tsv) file and reading data...\n\n"; + my @image_data_files = glob(IMAGE_DATA_DIR."/IADB_img_data_*.tsv"); + my $image_content = do { + local $/ = undef; + open (my $file, "<", $image_data_files[0]) or die("could not open $image_data_files[0]: $!"); + <$file>; + }; - print "Reading content of each image data(IADB_img_data_*.tsv) file and reading data...\n\n"; + my @image_data_records = split(/\n/, $image_content); + + shift @image_data_records; - foreach (glob(IMAGE_DATA_DIR."/IADB_img_data_*.tsv")) + foreach (@image_data_records) { - - my $content = do { - local $/ = undef; - print "file is " . $_ . "\n"; - open (my $file, "<", $_) or die("could not open $_: $!"); - <$file>; - }; - - chomp; - - my @image_data_records = split(/\n/, $content); - - shift @image_data_records; - - foreach (@image_data_records) - { - insert_image_data_into_database($dbh, $_); - } - + insert_image_data_into_database($dbh, $_, $image_source_version_id); } } diff --git a/image_annotation_db/ia_upload/stored_procedures.sql b/image_annotation_db/ia_upload/stored_procedures.sql index acacc1a..fabafee 100644 --- a/image_annotation_db/ia_upload/stored_procedures.sql +++ b/image_annotation_db/ia_upload/stored_procedures.sql @@ -43,8 +43,28 @@ end // DROP procedure if exists import_annotated_image; delimiter // -create procedure import_annotated_image (curator_id int(11), taxon_id int(11), image_path varchar(255), collection_location varchar(255), collection_date date, ip_comment varchar(255), doi varchar(45), source_db_name varchar(45), source_db_id varchar(30), comments varchar(255)) +create procedure import_annotated_image (curator_id int(11), image_source_version_id int(11), taxon_id int(11), image_path varchar(255), collection_location varchar(255), collection_date date, ip_comment varchar(255), doi varchar(45), source_db_name varchar(45), source_db_id varchar(30), comments varchar(255)) begin -insert into annotated_image (curator_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments) values (curator_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments); +insert into annotated_image (curator_id, image_source_version_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments) values (curator_id, image_source_version_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments); +select last_insert_id(); +end // + +-- create(drop and create if exists) stored procedure for importing data into annotated_term table + +DROP procedure if exists import_annotated_term; +delimiter // +delimiter +begin +insert into annotated_term (keyword, ontology_term_id) values (keyword, ontology_term_id); +select last_insert_id(); +end // + +-- create(drop and create if exists) stored procedure for importing data into annotated_term_image table + +DROP procedure if exists import_annotated_term_image; +delimiter // +create procedure import_annotated_term_image (annotated_term_id int(11), annotated_image_id int(11)) +begin +insert into annotated_term_image (annotated_term_id, annotated_image_id) values (annotated_term_id, annotated_image_id); select last_insert_id(); end // \ No newline at end of file -- 2.34.1