# HG changeset patch # User Vincent Tondellier # Date 1423436794 -3600 # Node ID 3a0ca02556daea3c101366aee710fba8ce30194c # Parent 7ad91b3d5562766360d600d1bc9fc5322f760712 Add crash_data table to store the json Add searching on crashing backtrace function names Drop never used tables Bump DB schema version Requires PostgreSQL >= 9.4 for now (for jsonb) diff -r 7ad91b3d5562 -r 3a0ca02556da dbicdh/PostgreSQL/deploy/2/002-extract_crashing_functions.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/dbicdh/PostgreSQL/deploy/2/002-extract_crashing_functions.sql Mon Feb 09 00:06:34 2015 +0100 @@ -0,0 +1,22 @@ + +CREATE OR REPLACE FUNCTION extract_crashing_functions (processed_json jsonb) RETURNS text AS +$$ + -- extract threads[crashing_idx]->frames[*]->function + SELECT string_agg(functions, E'\n') + FROM ( + SELECT jsonb_array_elements( + (($1 #> ARRAY['threads', $1->'crash_info'->>'crashing_thread'])->'frames') + )->>'function' AS functions + ) AS frames +$$ +LANGUAGE sql IMMUTABLE; + +-- This extension is the contrib modules +CREATE EXTENSION IF NOT EXISTS pg_trgm; + +-- Used for searching function in the crashing thread backtrace +-- Search will be really slow if this index is not present +CREATE INDEX crash_datas_idx_extract_crashing_functions ON crash_datas USING gin ( + extract_crashing_functions(processed) gin_trgm_ops +); + diff -r 7ad91b3d5562 -r 3a0ca02556da lib/CrashTest/Storage/Sql.pm --- a/lib/CrashTest/Storage/Sql.pm Sun Feb 08 23:59:59 2015 +0100 +++ b/lib/CrashTest/Storage/Sql.pm Mon Feb 09 00:06:34 2015 +0100 @@ -13,7 +13,7 @@ package CrashTest::Storage::Sql; use Mojo::Base "CrashTest::Storage::FileSystem"; -use Mojo::JSON qw/j/; +use Mojo::JSON qw/j decode_json/; use DateTime; use CrashTest::Storage::Sql::Schema; use Search::Query; @@ -33,15 +33,16 @@ if(defined($search_str) && $search_str ne "") { my $search_fields = { - user_id => { alias_for => 'crash_user.user_id' }, - product => { alias_for => 'product.name' }, - version => { alias_for => 'product.version' }, - channel => { alias_for => 'product.release_channel' }, + user_id => { alias_for => 'crash_user.user_id' }, + product => { alias_for => 'product.name' }, + version => { alias_for => 'product.version' }, + channel => { alias_for => 'product.release_channel' }, + function => { alias_for => 'extract_crashing_functions(processed)' }, }; my $query = Search::Query->parser( dialect => 'DBIxClass', fields => $search_fields, - default_field => [qw( product.name product.version )], + default_field => 'extract_crashing_functions(processed)', )->parse($search_str); if(defined($query)) { @@ -53,6 +54,7 @@ $dbic_query, { prefetch => [ 'product', 'crash_user' ], + join => 'crash_data', order_by => { -desc => 'crash_time' }, page => $page, rows => $nperpage, @@ -121,7 +123,10 @@ $crash->crash_user($self->{schema}->resultset('CrashUser')->new($user)); } - # TODO crash_threads => [ ], + my $txt_json = j($pjson); + $crash->create_related('crash_data', { + processed => $txt_json, + }); $crash->insert; }); @@ -130,9 +135,19 @@ sub store_processed_data { my ($self, $uuid, $pjson) = @_; - $self->SUPER::store_processed_data($uuid, $pjson); - $self->_db_insert_processed_data($uuid, $pjson); } +sub get_processed_data { + my ($self, $uuid) = @_; + + + my $crash = $self->{schema}->resultset('CrashReport')->search( + { uuid => $uuid }, + { prefetch => 'crash_data' } + )->first(); + + return decode_json($crash->crash_data->processed); +} + 1; diff -r 7ad91b3d5562 -r 3a0ca02556da lib/CrashTest/Storage/Sql/Schema.pm --- a/lib/CrashTest/Storage/Sql/Schema.pm Sun Feb 08 23:59:59 2015 +0100 +++ b/lib/CrashTest/Storage/Sql/Schema.pm Mon Feb 09 00:06:34 2015 +0100 @@ -15,7 +15,7 @@ package CrashTest::Storage::Sql::Schema; use base qw/DBIx::Class::Schema/; -our $VERSION = '1.00'; +our $VERSION = 2; __PACKAGE__->load_namespaces(); diff -r 7ad91b3d5562 -r 3a0ca02556da lib/CrashTest/Storage/Sql/Schema/Result/CrashData.pm --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/lib/CrashTest/Storage/Sql/Schema/Result/CrashData.pm Mon Feb 09 00:06:34 2015 +0100 @@ -0,0 +1,30 @@ +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program. If not, see . + +package CrashTest::Storage::Sql::Schema::Result::CrashData; + +use CrashTest::Storage::Sql::Schema::Candy; + +primary_column id => { + data_type => 'int', + is_auto_increment => 1, +}; + +column processed => { + data_type => 'jsonb', +}; + +column crash_report_id => { data_type => 'int' }; +belongs_to crash_report => 'CrashTest::Storage::Sql::Schema::Result::CrashReport', 'crash_report_id'; + +1; diff -r 7ad91b3d5562 -r 3a0ca02556da lib/CrashTest/Storage/Sql/Schema/Result/CrashFrame.pm --- a/lib/CrashTest/Storage/Sql/Schema/Result/CrashFrame.pm Sun Feb 08 23:59:59 2015 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,57 +0,0 @@ -# This program is free software: you can redistribute it and/or modify -# it under the terms of the GNU General Public License as published by -# the Free Software Foundation, either version 3 of the License, or -# (at your option) any later version. -# -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program. If not, see . - -package CrashTest::Storage::Sql::Schema::Result::CrashFrame; - -use CrashTest::Storage::Sql::Schema::Candy; - -primary_column id => { - data_type => 'int', - is_auto_increment => 1, -}; - -column number => { - data_type => 'int', -}; - -column function => { - data_type => 'varchar', - size => 128, - is_nullable => 1, -}; - -column source_file => { - data_type => 'varchar', - size => 128, - is_nullable => 1, -}; - -column source_line => { - data_type => 'int', - is_nullable => 1, -}; - -# any of ip, fp, stackscan, cfi -column stack_walk_mode => { - data_type => 'varchar', - size => 10, - is_nullable => 1, -}; - -column crash_thread_id => { data_type => 'int' }; -belongs_to crash_thread => 'CrashTest::Storage::Sql::Schema::Result::CrashThread', 'crash_thread_id'; - -column module_id => { data_type => 'int' }; -belongs_to module => 'CrashTest::Storage::Sql::Schema::Result::Module', 'module_id'; - -1; diff -r 7ad91b3d5562 -r 3a0ca02556da lib/CrashTest/Storage/Sql/Schema/Result/CrashReport.pm --- a/lib/CrashTest/Storage/Sql/Schema/Result/CrashReport.pm Sun Feb 08 23:59:59 2015 +0100 +++ b/lib/CrashTest/Storage/Sql/Schema/Result/CrashReport.pm Mon Feb 09 00:06:34 2015 +0100 @@ -34,8 +34,7 @@ }; column uuid => { - data_type => 'varchar', - size => 36, + data_type => 'uuid', }; column bug_reference => { @@ -44,7 +43,7 @@ is_nullable => 1, }; -has_many crash_threads => 'CrashTest::Storage::Sql::Schema::Result::CrashThread', 'id'; +has_one crash_data => 'CrashTest::Storage::Sql::Schema::Result::CrashData', 'crash_report_id'; column crash_user_id => { data_type => 'int' }; belongs_to crash_user => 'CrashTest::Storage::Sql::Schema::Result::CrashUser', 'crash_user_id'; @@ -52,4 +51,13 @@ column product_id => { data_type => 'int' }; belongs_to product => 'CrashTest::Storage::Sql::Schema::Result::Product', 'product_id'; +sub sqlt_deploy_hook { + my ($self, $sqlt_table) = @_; + $sqlt_table->add_index( + name => 'crash_reports_idx_uuid', + fields => [ 'uuid' ], + type => 'unique', + ); +} + 1; diff -r 7ad91b3d5562 -r 3a0ca02556da lib/CrashTest/Storage/Sql/Schema/Result/CrashThread.pm --- a/lib/CrashTest/Storage/Sql/Schema/Result/CrashThread.pm Sun Feb 08 23:59:59 2015 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,36 +0,0 @@ -# This program is free software: you can redistribute it and/or modify -# it under the terms of the GNU General Public License as published by -# the Free Software Foundation, either version 3 of the License, or -# (at your option) any later version. -# -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program. If not, see . - -package CrashTest::Storage::Sql::Schema::Result::CrashThread; - -use CrashTest::Storage::Sql::Schema::Candy; - -primary_column id => { - data_type => 'int', - is_auto_increment => 1, -}; - -column number => { - data_type => 'int', -}; - -column crashed => { - data_type => 'bool', -}; - -column crash_report_id => { data_type => 'int' }; -belongs_to crash_report => 'CrashTest::Storage::Sql::Schema::Result::CrashReport', 'crash_report_id'; - -has_many crash_frames => 'CrashTest::Storage::Sql::Schema::Result::CrashFrame', 'id'; - -1;