From 915859de411997eb79cf2f2cb76ffc811dfb3d33 Mon Sep 17 00:00:00 2001 From: nickmorales Date: Fri, 17 Jun 2016 19:40:24 +0000 Subject: [PATCH] faster list addition --- js/CXGN/List.js | 63 +++++++++++++---------- lib/CXGN/List.pm | 69 ++++++++++++++++++++++++++ lib/SGN/Controller/AJAX/List.pm | 32 +++++------- t/selenium2/breeders/{folder.t => 01_folder.t} | 0 4 files changed, 119 insertions(+), 45 deletions(-) rename t/selenium2/breeders/{folder.t => 01_folder.t} (100%) diff --git a/js/CXGN/List.js b/js/CXGN/List.js index dc468c76c..9129711c9 100644 --- a/js/CXGN/List.js +++ b/js/CXGN/List.js @@ -270,28 +270,30 @@ CXGN.List.prototype = { }, addBulk: function(list_id, items) { - - var elements = items.join("\t"); - - var count; - jQuery.ajax( { - async: false, - method: 'POST', - url: '/list/add/bulk', - data: { 'list_id': list_id, 'elements': elements }, - success: function(response) { - if (response.error) { - alert(response.error); - } - else { - if (response.duplicates) { - alert("The following items are already in the list and were not added: "+response.duplicates.join(", ")); - } - count = response.success; - } - } - }); - return count; + var elements = items.join("\t"); + + var count; + jQuery.ajax( { + async: false, + method: 'POST', + url: '/list/add/bulk', + data: { 'list_id': list_id, 'elements': elements }, + success: function(response) { + if (response.error) { + alert(response.error); + } + else { + if (response.duplicates) { + alert("The following items are already in the list and were not added: "+response.duplicates.join(", ")); + } + count = response.success; + } + }, + error: function(response) { + alert("ERROR: "+response); + } + }); + return count; }, removeItem: function(list_id, item_id) { @@ -418,6 +420,7 @@ CXGN.List.prototype = { }, renderItems: function(div, list_id) { + var list_data = this.getListData(list_id); var items = list_data.elements; var list_type = list_data.type_name; @@ -462,11 +465,9 @@ CXGN.List.prototype = { jQuery('#dialog_add_list_item_button').click( function() { - jQuery('#working_modal').modal("show"); addMultipleItemsToList('dialog_add_list_item', list_id); var lo = new CXGN.List(); lo.renderItems(div, list_id); - jQuery('#working_modal').modal("hide"); } ); @@ -943,7 +944,7 @@ return; //if (duplicates.length >0) { // alert("The following items were not added because they are already in the list: "+ duplicates.join(", ")); // } -lo.renderLists('list_dialog'); +//lo.renderLists('list_dialog'); } /* deprecated */ @@ -1055,10 +1056,20 @@ function deleteItemLink(list_item_id) { lo.renderLists('list_dialog'); } +function working_modal_show() { + jQuery("#working_modal").modal('show'); +} + +function working_modal_hide() { + jQuery("#working_modal").modal('hide'); +} + function showListItems(div, list_id) { + working_modal_show(); var l = new CXGN.List(); - jQuery('#'+div).modal("show"); l.renderItems(div, list_id); + jQuery('#'+div).modal("show"); + working_modal_hide(); } function showPublicListItems(div, list_id) { diff --git a/lib/CXGN/List.pm b/lib/CXGN/List.pm index 86433e7fd..07f0eae94 100644 --- a/lib/CXGN/List.pm +++ b/lib/CXGN/List.pm @@ -32,6 +32,7 @@ Class function (without instantiation): package CXGN::List; use Moose; +use Data::Dumper; has 'dbh' => ( isa => 'DBI::db', is => 'rw', @@ -459,5 +460,73 @@ sub retrieve_elements_with_ids { return \@list; } +sub add_bulk { + my $self = shift; + my $elements = shift; + my %elements_in_list; + my @elements_added; + my @duplicates; + + #print STDERR Dumper $elements; + + my $q = "SELECT content FROM sgn_people.list join sgn_people.list_item using(list_id) where list.list_id =?"; + my $h = $self->dbh()->prepare($q); + $h->execute($self->list_id()); + while (my $list_content = $h->fetchrow_array()) { + $elements_in_list{$list_content} = 1; + } + + $q = "SELECT list_item_id FROM sgn_people.list_item ORDER BY list_item_id DESC LIMIT 1"; + $h = $self->dbh()->prepare($q); + $h->execute(); + my $list_item_id = $h->fetchrow_array() + 1; + + my $iq = "INSERT INTO sgn_people.list_item (list_item_id, list_id, content) VALUES"; + + my $count = 0; + eval { + $self->dbh()->begin_work; + + my @values; + foreach (@$elements) { + if (!exists $elements_in_list{$_}){ + push @values, [$list_item_id, $self->list_id(), $_]; + $elements_in_list{$_} = 1; + push @elements_added, $_; + $list_item_id++; + $count++; + } else { + push @duplicates, $_; + } + } + + my $step = 1; + foreach (@values) { + if ($step < scalar(@values)) { + $iq = $iq." (".$_->[0].",".$_->[1].",'".$_->[2]."'),"; + } else { + $iq = $iq." (".$_->[0].",".$_->[1].",'".$_->[2]."');"; + } + $step++; + } + #print STDERR Dumper $iq; + if (scalar(@elements_added)>0){ + $self->dbh()->do($iq); + } + $self->dbh()->commit; + }; + if ($@) { + $self->dbh()->rollback; + return {error => "An error occurred in bulk addition to list. ($@)"}; + } + + $elements = $self->elements(); + push @$elements, \@elements_added; + $self->elements($elements); + + my %response = (count => $count, duplicates => \@duplicates); + return \%response; +} + 1; diff --git a/lib/SGN/Controller/AJAX/List.pm b/lib/SGN/Controller/AJAX/List.pm index ff2d83658..840bdefba 100644 --- a/lib/SGN/Controller/AJAX/List.pm +++ b/lib/SGN/Controller/AJAX/List.pm @@ -399,16 +399,15 @@ sub add_bulk : Path('/list/add/bulk') Args(0) { my $elements = $c->req->param("elements"); my $user_id = $self->get_user($c); - my $error = $self->check_user($c, $list_id); if ($error) { - $c->stash->{rest} = { error => $error }; - return; + $c->stash->{rest} = { error => $error }; + return; } if (!$elements) { - $c->stash->{rest} = { error => "You must provide one or more elements to add to the list" }; - return; + $c->stash->{rest} = { error => "You must provide one or more elements to add to the list" }; + return; } my @elements = split "\t", $elements; @@ -418,23 +417,18 @@ sub add_bulk : Path('/list/add/bulk') Args(0) { my @duplicates = (); my $count = 0; - foreach my $element (@elements) { - $element =~ s/^\s*(.+?)\s*$/$1/; + my $response = $list->add_bulk(\@elements); + #print STDERR Dumper $response; - if ($list->exists_element($element)) { - push @duplicates, $element; - } - else { - #$ih->execute($list_id, $element); - $list->add_element($element); - $count++; - } + if ($response->{error}) { + $c->stash->{rest} = { error => $response->{error}}; + return; } - if (@duplicates) { - $c->stash->{rest} = { duplicates => \@duplicates }; + if (scalar(@{$response->{duplicates}}) > 0){ + $c->stash->{rest} = { duplicates => $response->{duplicates} }; } - $c->stash->{rest}->{success} = $count; - + + $c->stash->{rest}->{success} = $response->{count}; } sub insert_element : Private { diff --git a/t/selenium2/breeders/folder.t b/t/selenium2/breeders/01_folder.t similarity index 100% rename from t/selenium2/breeders/folder.t rename to t/selenium2/breeders/01_folder.t -- 2.11.4.GIT