Postgres jsonb support;document column types

8 files changed, 93 insertions, 16 deletions

diff --git a/Changes b/Changes
index a02fe04..cc106c2 100644
--- a/Changes
+++ b/Changes
@@ -2,6 +2,8 @@ Revision history for Catmandu-DBI
 
 {{$NEXT}}
   - optimize mysql select with large offsets
+  - fix Catmandu::Importer::DBI utf-8 handling
+  - The Pg handler now supports the jsonb type
 
 0.0702  2018-04-10 11:30:09 CEST
   - document index option
diff --git a/lib/Catmandu/Importer/DBI.pm b/lib/Catmandu/Importer/DBI.pm
index bf30e95..68b7486 100644
--- a/lib/Catmandu/Importer/DBI.pm
+++ b/lib/Catmandu/Importer/DBI.pm
@@ -11,9 +11,9 @@ our $VERSION = '0.0702';
 with 'Catmandu::Importer';
 
 has data_source => (is => 'ro', required => 1, alias => 'dsn');
-has username => (is => 'ro', alias => 'user');
-has password => (is => 'ro', alias => 'pass');
-has query    => (is => 'ro', required => 1);
+has username    => (is => 'ro', alias    => 'user');
+has password    => (is => 'ro', alias    => 'pass');
+has query       => (is => 'ro', required => 1);
 has dbh =>
     (is => 'ro', init_arg => undef, lazy => 1, builder => '_build_dbh',);
 has sth =>
diff --git a/lib/Catmandu/Store/DBI.pm b/lib/Catmandu/Store/DBI.pm
index 6d1f4de..2c2ff04 100644
--- a/lib/Catmandu/Store/DBI.pm
+++ b/lib/Catmandu/Store/DBI.pm
@@ -5,6 +5,7 @@ use Catmandu::Util qw(require_package);
 use DBI;
 use Catmandu::Store::DBI::Bag;
 use Moo;
+use MooX::Aliases;
 use namespace::clean;
 
 our $VERSION = "0.0702";
@@ -303,6 +304,18 @@ See L<Catmandu::Store::DBI::Bag> for more information.
 
 =item binary
 
+=item datetime
+
+Only MySQL, PostgreSQL
+
+=item datetime_milli
+
+Only MySQL, PostgreSQL
+
+=item json
+
+Only PostgreSQL
+
 =back
 
 =head2 Column options
@@ -315,7 +328,7 @@ Name of the table column if it differs from the key in your data.
 
 =item array
 
-Boolean option, default is C<0>. Note that this options is only supported for PostgreSQL.
+Boolean option, default is C<0>. Note that this is only supported for PostgreSQL.
 
 =item unique
 
diff --git a/lib/Catmandu/Store/DBI/Handler/MySQL.pm b/lib/Catmandu/Store/DBI/Handler/MySQL.pm
index f032127..2d45cd3 100644
--- a/lib/Catmandu/Store/DBI/Handler/MySQL.pm
+++ b/lib/Catmandu/Store/DBI/Handler/MySQL.pm
@@ -31,11 +31,14 @@ sub _column_sql {
     }
     elsif ($map->{type} eq 'datetime_milli') {
         if ($dbh->{mysql_clientversion} < 50640) {
-            Catmandu::NotImplemented->throw(
-                "DATETIME(3) only for MySQL > 5.6.4");
+            Catmandu::Error->throw(
+                "DATETIME(3) type only supported in MySQL 5.6.4 and above");
         }
         $sql .= 'DATETIME(3)';
     }
+    else {
+        Catmandu::Error->throw("Unknown type '$map->{type}'");
+    }
     if ($map->{unique}) {
         $sql .= " UNIQUE";
     }
diff --git a/lib/Catmandu/Store/DBI/Handler/Pg.pm b/lib/Catmandu/Store/DBI/Handler/Pg.pm
index ae03668..be80526 100644
--- a/lib/Catmandu/Store/DBI/Handler/Pg.pm
+++ b/lib/Catmandu/Store/DBI/Handler/Pg.pm
@@ -23,12 +23,22 @@ sub _column_sql {
     elsif ($map->{type} eq 'binary') {
         $sql .= 'BYTEA';
     }
+    elsif ($map->{type} eq 'json') {
+        if ($dbh->{pg_server_version} < 90400) {
+            Catmandu::Error->throw(
+                "JSONB type only supported in PostgreSQL 9.4 and above");
+        }
+        $sql .= 'JSONB';
+    }
     elsif ($map->{type} eq 'datetime') {
         $sql .= 'TIMESTAMP(0)';
     }
     elsif ($map->{type} eq 'datetime_milli') {
         $sql .= 'TIMESTAMP(3)';
     }
+    else {
+        Catmandu::Error->throw("Unknown type '$map->{type}'");
+    }
     if ($map->{array}) {
         $sql .= '[]';
     }
@@ -103,8 +113,15 @@ sub add_row {
     my $id_col   = $mapping->{_id}{column};
     my $q_id_col = $dbh->quote_identifier($id_col);
     my %binary_cols;
+    my %json_cols;
     for my $map (values %$mapping) {
-        $binary_cols{$map->{column}} = 1 if $map->{type} eq 'binary';
+
+        if ($map->{type} eq 'binary') {
+            $binary_cols{$map->{column}} = 1;
+        }
+        elsif ($map->{type} eq 'json') {
+            $json_cols{$map->{column}} = 1;
+        }
     }
     my $id     = $row->{$id_col};
     my @cols   = keys %$row;
@@ -132,6 +149,9 @@ sub add_row {
         if ($binary_cols{$col}) {
             $sth->bind_param($i + 1, $val, {pg_type => DBD::Pg->PG_BYTEA});
         }
+        elsif ($json_cols{$col}) {
+            $sth->bind_param($i + 1, $val, {pg_type => DBD::Pg->PG_JSONB});
+        }
         else {
             $sth->bind_param($i + 1, $val);
         }
@@ -151,6 +171,10 @@ sub add_row {
                 $sth->bind_param($i + 1, $val,
                     {pg_type => DBD::Pg->PG_BYTEA});
             }
+            elsif ($json_cols{$col}) {
+                $sth->bind_param($i + 1, $val,
+                    {pg_type => DBD::Pg->PG_JSONB});
+            }
             else {
                 $sth->bind_param($i + 1, $val);
             }
diff --git a/lib/Catmandu/Store/DBI/Handler/SQLite.pm b/lib/Catmandu/Store/DBI/Handler/SQLite.pm
index 5e5f80c..d1d14bc 100644
--- a/lib/Catmandu/Store/DBI/Handler/SQLite.pm
+++ b/lib/Catmandu/Store/DBI/Handler/SQLite.pm
@@ -22,11 +22,8 @@ sub _column_sql {
     elsif ($map->{type} eq 'binary') {
         $sql .= 'BLOB';
     }
-    elsif ($map->{type} eq 'datetime') {
-        $sql .= 'TEXT';
-    }
-    elsif ($map->{type} eq 'datetime_milli') {
-        $sql .= 'TEXT';
+    else {
+        Catmandu::Error->throw("Unknown type '$map->{type}'");
     }
     if ($map->{unique}) {
         $sql .= " UNIQUE";
diff --git a/t/06-sqlite.t b/t/06-sqlite.t
index 865b2c6..efd1074 100644
--- a/t/06-sqlite.t
+++ b/t/06-sqlite.t
@@ -42,7 +42,7 @@ else {
     };
     my $serializer = Catmandu::Serializer::json->new();
 
-#impliciet mapping (old behaviour) => except for the _id that is not stored anymore in 'data'
+#implicit mapping (old behaviour) => except for the _id that is not stored anymore in 'data'
     {
         my $bag;
         lives_ok(
diff --git a/t/08-postgres.t b/t/08-postgres.t
index bcbe529..730a3ba 100644
--- a/t/08-postgres.t
+++ b/t/08-postgres.t
@@ -57,9 +57,11 @@ else {
         title  => "My little pony",
         author => "孩之寶"
     };
-    my $serializer = Catmandu::Serializer::json->new();
+    my $serializer = Catmandu::Serializer::json->new;
 
-    #impliciet mapping (old behaviour)
+    my $pg_version;
+
+    #implicit mapping (old behaviour)
     {
         my $bag;
         my $bag_name = "data1";
@@ -69,6 +71,9 @@ else {
             },
             "no mapping - bag $bag_name created"
         );
+
+        $pg_version = $bag->store->dbh->{pg_server_version};
+
         lives_ok(sub {$bag->delete_all;},
             "no mapping - bag $bag_name cleared");
 
@@ -198,5 +203,38 @@ else {
 
     }
 
-    done_testing 19;
+    #test jsonb support in postgres 9.4 and above
+    if ($pg_version >= 90400) {
+        my $bag;
+        my $bag_name = "data4";
+        lives_ok(
+            sub {
+                $bag = Catmandu::Store::DBI->new(
+                    %store_args,
+                    bags => {
+                        $bag_name => {
+                            mapping => {
+                                _id => {
+                                    column   => "_id",
+                                    type     => "string",
+                                    index    => 1,
+                                    required => 1,
+                                    unique   => 1
+                                },
+                                _data => {
+                                    column    => "data",
+                                    type      => "json",
+                                    serialize => "all",
+                                    jsonb     => 1
+                                }
+                            }
+                        }
+                    }
+                )->bag($bag_name);
+            },
+            "bag $bag_name with jsonb support created"
+        );
+    }
+
+    done_testing;
 }