--- a/CrashTest.conf	Wed Jul 30 23:49:59 2014 +0200
+++ b/CrashTest.conf	Sat Aug 02 23:46:30 2014 +0200
@@ -5,14 +5,19 @@
   },
   Storage => {
       Type => "CrashTest::Storage::FileSystem",
-      DataDir => 'data/'
+      DataDir => 'data/',
   },
+#  Storage => {
+#      Type => "CrashTest::Storage::Sql",
+#      DSN => "dbi:SQLite:dbname=data/db.sqlite",
+#      DataDir => 'data/',
+#  },
   DecodeQueue => {
       Type => "CrashTest::Decode::Queue::NoQueue"
   },
 #  DecodeQueue => {
 #      Type => "CrashTest::Decode::Queue::Gearman",
-#      GearmanServers => [ 'localhost:4730' ]
+#      GearmanServers => [ 'localhost:4730' ],
 #  },
   ScmLinks => {
     "svn:svn.example.org/testproject" => 'https://redmine.example.org/projects/testproject/repository/entry/<%= $scmpath =%>?rev=<%= $rev =%>#L<%= $line =%>',

--- a/CrashTest.pl	Wed Jul 30 23:49:59 2014 +0200
+++ b/CrashTest.pl	Sat Aug 02 23:46:30 2014 +0200
@@ -25,7 +25,7 @@
 
 app->attr(storage => sub {
     my $self = shift;
-    eval "require $config->{Storage}->{Type}";
+    eval "require $config->{Storage}->{Type}" or die "Loading module failed $@";
     return $config->{Storage}->{Type}->new($config->{Storage});
 });
 
@@ -113,4 +113,6 @@
 app->secrets([
     'My secret passphrase here'
 ]);
+
+push @{app->commands->namespaces}, 'CrashTest::Commands';
 app->start;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Commands/setup.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,48 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+package CrashTest::Commands::setup;
+use Mojo::Base 'Mojolicious::Command';
+
+use aliased 'DBIx::Class::DeploymentHandler' => 'DH';
+use CrashTest::Storage::Sql::Schema;
+
+# Short description
+has description => 'Setup database.';
+
+# Short usage message
+has usage => <<EOF;
+Usage: APPLICATION setup [OPTIONS]
+
+EOF
+
+sub run {
+    my ($self, @args) = @_;
+
+    my $schema = CrashTest::Storage::Sql::Schema->connect($self->app->config->{Storage}->{DSN});
+
+    my $dh = DH->new(
+        {
+            schema              => $schema,
+            script_directory    => "$FindBin::Bin/dbicdh",
+            sql_translator_args => { add_drop_table => 0 },
+            force_overwrite     => 0,
+        }
+    );
+
+    $dh->prepare_install;
+    $dh->install;
+
+}
+
+1;

--- a/lib/CrashTest/Storage/FileSystem.pm	Wed Jul 30 23:49:59 2014 +0200
+++ b/lib/CrashTest/Storage/FileSystem.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -12,9 +12,8 @@
 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
 package CrashTest::Storage::FileSystem;
-
-use strict;
-use warnings;
+use Mojo::Base -strict;
+use DateTime;
 
 sub new {
     my ($class, $config) = @_;
@@ -27,7 +26,8 @@
 sub index {
     my ($self) = @_;
     my @files;
-    opendir my ($dh), $self->{data_path} or die $!;
+    my $dh;
+    opendir($dh, $self->{data_path}) or die $!;
     my @allfiles = readdir $dh;
     foreach(@allfiles)
     {
@@ -35,11 +35,12 @@
         {
             my $filename = File::Spec->catfile($self->{data_path}, $_);
             push @files, {
-                file => $filename,
-                uuid => $1,
-                signature => $1,
-                product => "",
-                date => (stat $filename)[9],
+                file        => $filename,
+                uuid        => $1,
+                product     => "",
+                version     => "",
+                user        => "",
+                date        => DateTime->from_epoch(epoch => ((stat $filename)[9])),
             };
         }
     }
@@ -73,6 +74,9 @@
     open JSON, '>', File::Spec->catfile($self->{data_path}, "$uuid.json") or die $!;
     print JSON $j;
     close JSON;
+    utime $pjson->{client_info}->{CrashTime}, $pjson->{client_info}->{CrashTime}, File::Spec->catfile($self->{data_path}, "$uuid.json");
+    utime $pjson->{client_info}->{CrashTime}, $pjson->{client_info}->{CrashTime}, File::Spec->catfile($self->{data_path}, "$uuid.dmp");
+
 }
 
 sub store_dump {

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,137 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+package CrashTest::Storage::Sql;
+use Mojo::Base -strict;
+use DateTime;
+use CrashTest::Storage::Sql::Schema;
+
+sub new {
+    my ($class, $config) = @_;
+    my $self  = {};
+
+    $self->{data_path} = $config->{DataDir};
+    $self->{schema} = CrashTest::Storage::Sql::Schema->connect($config->{DSN});
+
+    bless ($self, $class);
+    return $self;
+}
+
+sub index {
+    my ($self) = @_;
+
+    my $dbcrashs = $self->{schema}->resultset('CrashReport')->search(
+        undef,
+        {
+            prefetch => [ 'product', 'crash_user' ],
+            order_by => 'crash_time',
+            rows => 50,
+        },
+    );
+
+    my @results;
+    for my $crash($dbcrashs->all) {
+        my $filename = File::Spec->catfile($self->{data_path}, $crash->uuid);
+        push @results, {
+            file        => $filename,
+            uuid        => $crash->uuid,
+            product     => $crash->product->name,
+            version     => $crash->product->version,
+            user        => $crash->crash_user->user_id,
+            date        => $crash->crash_time,
+        }
+    }
+
+    return \@results;
+}
+
+sub get_processed_data {
+    my ($self, $uuid) = @_;
+
+    open JSON, '<', File::Spec->catfile($self->{data_path}, $uuid . '.json') or die $!;
+    my @json_content_lines = <JSON>;
+    my $json_content = join('', @json_content_lines);
+    close JSON;
+
+    my $json = Mojo::JSON->new;
+    my $processed_data = $json->decode($json_content);
+
+    return $processed_data;
+}
+
+sub store_processed_data {
+    my ($self, $uuid, $pjson) = @_;
+
+    my $json = Mojo::JSON->new;
+    my $j = $json->encode($pjson);
+    open JSON, '>', File::Spec->catfile($self->{data_path}, "$uuid.json") or die $!;
+    print JSON $j;
+    close JSON;
+
+    $self->{schema}->txn_do(sub {
+
+        my $crash = $self->{schema}->resultset('CrashReport')->new({ uuid => $uuid });
+
+        if($pjson->{client_info}->{StartupTime}) {
+            $crash->start_time(DateTime->from_epoch(epoch => $pjson->{client_info}->{StartupTime}));
+        }
+        if($pjson->{client_info}->{CrashTime}) {
+            $crash->crash_time(DateTime->from_epoch(epoch => $pjson->{client_info}->{CrashTime}));
+        }
+
+        my $product = {
+            distributor => $pjson->{client_info}->{Distributor},
+            name        => $pjson->{client_info}->{ProductName},
+            version     => $pjson->{client_info}->{Version},
+        };
+
+        my $dbproduct = $self->{schema}->resultset('Product')->search($product)->next();
+        if($dbproduct) {
+            $crash->product($dbproduct);
+        } else {
+            $product->{release_channel} = $pjson->{client_info}->{ReleaseChannel};
+            $crash->product($self->{schema}->resultset('Product')->new($product));
+        }
+
+        my $user = {
+            user_id => $pjson->{client_info}->{UserID},
+        };
+
+        my $dbuser = $self->{schema}->resultset('CrashUser')->search($user)->next();
+        if($dbuser) {
+            $crash->crash_user($dbuser);
+        } else {
+            $user->{cpu_arch}   = $pjson->{system_info}->{cpu_arch};
+            $user->{cpu_count}  = $pjson->{system_info}->{cpu_count};
+            $user->{os}         = $pjson->{system_info}->{os};
+            $user->{extra_info} = $json->encode($pjson->{client_info});
+            $crash->crash_user($self->{schema}->resultset('CrashUser')->new($user));
+        }
+
+        # TODO crash_threads => [ ],
+
+        $crash->insert;
+    });
+}
+
+sub store_dump {
+    my ($self, $uuid, $file) = @_;
+
+    my $dmp_file = File::Spec->catfile($self->{data_path}, "$uuid.dmp");
+    my $fh = IO::File->new($dmp_file, "w") or die($!);
+    $fh->binmode;
+    print $fh $file;
+    undef $fh;
+}
+
+1;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,23 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+use utf8;
+package CrashTest::Storage::Sql::Schema;
+use base qw/DBIx::Class::Schema/;
+
+our $VERSION = '1';
+$VERSION = eval $VERSION;
+
+__PACKAGE__->load_namespaces();
+
+1;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema/Candy.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,22 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+package CrashTest::Storage::Sql::Schema::Candy;
+
+use Mojo::Base -strict;
+use base 'DBIx::Class::Candy';
+
+sub perl_version { 12 }
+sub autotable { 1 }
+
+1;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema/Result/CrashFrame.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,57 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+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;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema/Result/CrashReport.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,55 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+package CrashTest::Storage::Sql::Schema::Result::CrashReport;
+
+use CrashTest::Storage::Sql::Schema::Candy;
+__PACKAGE__->load_components(qw/InflateColumn::DateTime Core/);
+
+primary_column id => {
+    data_type => 'int',
+    is_auto_increment => 1,
+};
+
+column start_time => {
+    data_type => 'timestamp',
+    inflate_datetime => 1,
+    is_nullable => 1,
+};
+
+column crash_time => {
+    data_type => 'timestamp',
+    inflate_datetime => 1,
+    is_nullable => 1,
+};
+
+column uuid => {
+    data_type => 'varchar',
+    size => 36,
+};
+
+column bug_reference => {
+    data_type => 'varchar',
+    size => 20,
+    is_nullable => 1,
+};
+
+has_many crash_threads => 'CrashTest::Storage::Sql::Schema::Result::CrashThread', 'id';
+
+column crash_user_id => { data_type => 'int' };
+belongs_to crash_user => 'CrashTest::Storage::Sql::Schema::Result::CrashUser', 'crash_user_id';
+
+column product_id => { data_type => 'int' };
+belongs_to product => 'CrashTest::Storage::Sql::Schema::Result::Product', 'product_id';
+
+1;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema/Result/CrashThread.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,36 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+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;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema/Result/CrashUser.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,53 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+package CrashTest::Storage::Sql::Schema::Result::CrashUser;
+
+use CrashTest::Storage::Sql::Schema::Candy;
+
+primary_column id => {
+    data_type => 'int',
+    is_auto_increment => 1,
+};
+
+column user_id => {
+    data_type => 'varchar',
+    size => 40,
+};
+
+column os => {
+    data_type => 'varchar',
+    size => 40,
+    is_nullable => 1,
+};
+
+column cpu_arch => {
+    data_type => 'varchar',
+    size => 10,
+    is_nullable => 1,
+};
+
+column cpu_count => {
+    data_type => 'int',
+    is_nullable => 1,
+};
+
+column extra_info => {
+    data_type => 'text',
+    is_nullable => 1,
+};
+
+#column crash_report_id => { data_type => 'int' };
+#belongs_to crash_report => 'CrashTest::Storage::Sql::Schema::Result::CrashReport', 'crash_report_id';
+
+1;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema/Result/Module.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,41 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+package CrashTest::Storage::Sql::Schema::Result::Module;
+
+use CrashTest::Storage::Sql::Schema::Candy;
+
+primary_column id => {
+    data_type => 'int',
+    is_auto_increment => 1,
+};
+
+column debug_id => {
+    data_type => 'varchar',
+    size => 33,
+};
+
+column filename => {
+    data_type => 'varchar',
+    size => 128,
+};
+
+column version => {
+    data_type => 'varchar',
+    size => 64,
+    is_nullable => 1,
+};
+
+unique_constraint module_id => ['debug_id', 'filename'];
+
+1;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Storage/Sql/Schema/Result/Product.pm	Sat Aug 02 23:46:30 2014 +0200
@@ -0,0 +1,50 @@
+# 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 <http://www.gnu.org/licenses/>.
+
+package CrashTest::Storage::Sql::Schema::Result::Product;
+
+use CrashTest::Storage::Sql::Schema::Candy;
+
+primary_column id => {
+    data_type => 'int',
+    is_auto_increment => 1,
+};
+
+column distributor => {
+    data_type => 'varchar',
+    size => 40,
+    is_nullable => 1,
+};
+
+column name => {
+    data_type => 'varchar',
+    size => 40,
+    is_nullable => 1,
+};
+
+column version => {
+    data_type => 'varchar',
+    size => 16,
+    is_nullable => 1,
+};
+
+column release_channel => {
+    data_type => 'varchar',
+    is_nullable => 16,
+};
+
+#column crash_report_id => { data_type => 'int' };
+#belongs_to crash_report => 'CrashTest::Storage::Sql::Schema::Result::CrashReport', 'crash_report_id';
+
+1;
+

--- a/templates/index.html.ep	Wed Jul 30 23:49:59 2014 +0200
+++ b/templates/index.html.ep	Sat Aug 02 23:46:30 2014 +0200
@@ -3,14 +3,22 @@
 %= t table => (class => 'table table-striped table-bordered table-condensed') => begin
 <thead>
   <tr>
-    <th>Signature</th>
+    <th>Product</th>
+    <th>Version</th>
+    <th>UserID</th>
+    <th>UUID</th>
     <th>Date</th>
   </tr>
 </thead>
-% foreach my $file(@$files) {
+% foreach my $crash(@$files) {
 <tr>
-  <td><%= link_to $file->{uuid} => url_for('report', uuid => $file->{uuid}) =%></td>
-  %= t td => POSIX::strftime("%F %T", localtime($file->{date}))
+  %= t td => $crash->{product}
+  %= t td => $crash->{version}
+  %= t td => $crash->{user}
+  %= t td => (style => "font-family:monospace;") => begin
+    %= link_to $crash->{uuid} => url_for('report', uuid => $crash->{uuid})
+  % end
+  %= t td => $crash->{date}->strftime("%F %T")
 </tr>
 % }
 % end