File Coverage

blib/lib/App/Sqitch/Engine/exasol.pm
Criterion Covered Total %
statement 135 182 74.1
branch 22 38 57.8
condition 10 29 34.4
subroutine 49 62 79.0
pod 16 16 100.0
total 232 327 70.9


line stmt bran cond sub pod time code
1             package App::Sqitch::Engine::exasol;
2              
3 2     2   23930 use 5.010;
  2         18  
4 2     2   13 use Moo;
  2         13  
  2         18  
5 2     2   756 use utf8;
  2         13  
  2         13  
6 2     2   61 use Path::Class;
  2         5  
  2         121  
7 2     2   1563 use DBI;
  2         17748  
  2         94  
8 2     2   14 use Try::Tiny;
  2         4  
  2         117  
9 2     2   16 use App::Sqitch::X qw(hurl);
  2         30  
  2         23  
10 2     2   697 use Locale::TextDomain qw(App-Sqitch);
  2         4  
  2         13  
11 2     2   391 use App::Sqitch::Types qw(DBH Dir ArrayRef);
  2         6  
  2         18  
12 2     2   2015 use App::Sqitch::Plan::Change;
  2         5  
  2         99  
13 2     2   13 use List::Util qw(first);
  2         6  
  2         173  
14 2     2   15 use namespace::autoclean;
  2         4  
  2         38  
15              
16             extends 'App::Sqitch::Engine';
17              
18             our $VERSION = 'v1.4.0'; # VERSION
19              
20             sub _dt($) {
21 1     1   1002 require App::Sqitch::DateTime;
22 1         22 return App::Sqitch::DateTime->new(split /:/ => shift);
23             }
24              
25 4     4 1 7404 sub key { 'exasol' }
26 3     3 1 41 sub name { 'Exasol' }
27 1     1 1 8 sub driver { 'DBD::ODBC 1.59' }
28 8     8 1 1189 sub default_client { 'exaplus' }
29              
30             BEGIN {
31             # Disable SQLPATH so that we don't read scripts from unexpected places.
32 2     2   7923 $ENV{SQLPATH} = '';
33             }
34              
35             sub destination {
36 5     5 1 1780 my $self = shift;
37              
38             # Just use the target name if it doesn't look like a URI or if the URI
39             # includes the database name.
40 5 50 33     68 return $self->target->name if $self->target->name !~ /:/
41             || $self->target->uri->dbname;
42              
43             # Use the URI sans password.
44 5         314 my $uri = $self->target->uri->clone;
45 5 50       72 $uri->password(undef) if $uri->password;
46 5         150 return $uri->as_string;
47             }
48              
49             # No username or password defaults.
50       8     sub _def_user { }
51       8     sub _def_pass { }
52              
53             has _exaplus => (
54             is => 'ro',
55             isa => ArrayRef,
56             lazy => 1,
57             default => sub {
58             my $self = shift;
59             my $uri = $self->uri;
60             my @ret = ( $self->client );
61              
62             # Collect the cquery params and convert keys to uppercase.
63             require URI::QueryParam;
64             my $qry = $uri->query_form_hash;
65             for my $key (keys %{ $qry }) {
66             my $ukey = uc $key;
67             next if $key eq $ukey;
68              
69             # Move value to uppercase key.
70             my $val = delete $qry->{$key};
71             if (!exists $qry->{$ukey}) {
72             # Store under uppercase key.
73             $qry->{$ukey} = $val;
74             } else {
75             # Push the value(s) onto upercase key array value.
76             $qry->{$ukey} = [$qry->{$ukey}] if ref $qry->{$ukey} ne 'ARRAY';
77             push @{ $qry->{$ukey} } => ref $val eq 'ARRAY' ? @{ $val } : $val;
78             }
79             }
80              
81             # Use _port instead of port so it's empty if no port is in the URI.
82             # https://github.com/sqitchers/sqitch/issues/675
83             for my $spec (
84             [ u => $self->username ],
85             [ p => $self->password ],
86             [ c => $uri->host && $uri->_port ? $uri->host . ':' . $uri->_port : undef ],
87             [ profile => $uri->host ? undef : $uri->dbname ],
88             [ jdbcparam => ($qry->{SSLCERTIFICATE} || '') eq 'SSL_VERIFY_NONE' ? 'validateservercertificate=0' : undef ],
89             [ jdbcparam => $qry->{AUTHMETHOD} ? "authmethod=$qry->{AUTHMETHOD}" : undef ],
90             ) {
91             push @ret, "-$spec->[0]" => $spec->[1] if $spec->[1];
92             }
93              
94             push @ret => (
95             '-q', # Quiet mode
96             '-L', # Don't prompt if login fails, just exit
97             '-pipe', # Enable piping of scripts to 'exaplus'
98             '-x', # Stop in case of errors
99             '-autoCompletion' => 'OFF',
100             '-encoding' => 'UTF8',
101             '-autocommit' => 'OFF',
102             );
103             return \@ret;
104             },
105             );
106              
107 14     14   3140 sub exaplus { @{ shift->_exaplus } }
  14         299  
108              
109             has tmpdir => (
110             is => 'ro',
111             isa => Dir,
112             lazy => 1,
113             default => sub {
114             require File::Temp;
115             dir File::Temp::tempdir( CLEANUP => 1 );
116             },
117             );
118              
119             has dbh => (
120             is => 'rw',
121             isa => DBH,
122             lazy => 1,
123             default => sub {
124             my $self = shift;
125             $self->use_driver;
126              
127             my $uri = $self->uri;
128             DBI->connect($uri->dbi_dsn, $self->username, $self->password, {
129             PrintError => 0,
130             RaiseError => 0,
131             AutoCommit => 1,
132             odbc_utf8_on => 1,
133             FetchHashKeyName => 'NAME_lc',
134             HandleError => sub {
135             my ($err, $dbh) = @_;
136             $@ = $err;
137             @_ = ($dbh->state || 'DEV' => $dbh->errstr);
138             goto &hurl;
139             },
140             Callbacks => {
141             connected => sub {
142             my $dbh = shift;
143             $dbh->do("ALTER SESSION SET $_='YYYY-MM-DD HH24:MI:SS'") for qw(
144             nls_date_format
145             nls_timestamp_format
146             );
147             $dbh->do("ALTER SESSION SET TIME_ZONE='UTC'");
148             if (my $schema = $self->registry) {
149             $dbh->do("OPEN SCHEMA $schema")
150             or $self->_handle_no_registry($dbh);
151             }
152             return;
153             },
154             },
155             });
156             }
157             );
158              
159             # Need to wait until dbh is defined.
160             with 'App::Sqitch::Role::DBIEngine';
161              
162             # Timestamp formats
163              
164             sub _char2ts {
165 1     1   23173 my $dt = $_[1];
166 1         9 $dt->set_time_zone('UTC');
167 1         327 $dt->ymd('-') . ' ' . $dt->hms(':');
168             }
169              
170             sub _ts2char_format {
171 1     1   1879 return qq{'year:' || CAST(EXTRACT(YEAR FROM %s) AS SMALLINT)
172             || ':month:' || CAST(EXTRACT(MONTH FROM %1\$s) AS SMALLINT)
173             || ':day:' || CAST(EXTRACT(DAY FROM %1\$s) AS SMALLINT)
174             || ':hour:' || CAST(EXTRACT(HOUR FROM %1\$s) AS SMALLINT)
175             || ':minute:' || CAST(EXTRACT(MINUTE FROM %1\$s) AS SMALLINT)
176             || ':second:' || FLOOR(CAST(EXTRACT(SECOND FROM %1\$s) AS NUMERIC(9,4)))
177             || ':time_zone:UTC'};
178             }
179              
180 1     1   5 sub _ts_default { 'current_timestamp' }
181              
182             sub _listagg_format {
183 1     1   1073 return q{GROUP_CONCAT(%1$s ORDER BY %1$s SEPARATOR ' ')};
184             }
185              
186 5     5   14 sub _regex_op { 'REGEXP_LIKE' }
187              
188             # LIMIT in Exasol doesn't behave properly with values > 18446744073709551611
189 2     2   27 sub _limit_default { '18446744073709551611' }
190              
191 1     1   6 sub _simple_from { ' FROM dual' }
192              
193             sub _multi_values {
194 0     0   0 my ($self, $count, $expr) = @_;
195 0         0 return join "\nUNION ALL ", ("SELECT $expr FROM dual") x $count;
196             }
197              
198             sub _initialized {
199 1     1   8 my $self = shift;
200 1         15 return $self->dbh->selectcol_arrayref(q{
201             SELECT EXISTS(
202             SELECT TRUE FROM exa_all_tables
203             WHERE table_schema = ? AND table_name = ?
204             )
205             }, undef, uc $self->registry, 'CHANGES')->[0];
206             }
207              
208             # LIMIT / OFFSET in Exasol doesn't seem to play nice in the original query with
209             # JOIN and GROUP BY; wrap it in a subquery instead..
210             sub change_offset_from_id {
211 0     0 1 0 my ( $self, $change_id, $offset ) = @_;
212              
213             # Just return the object if there is no offset.
214 0 0       0 return $self->load_change($change_id) unless $offset;
215              
216             # Are we offset forwards or backwards?
217 0         0 my ($dir, $op, $offset_expr) = $self->_offset_op($offset);
218 0         0 my $tscol = sprintf $self->_ts2char_format, 'c.planned_at';
219 0         0 my $tagcol = sprintf $self->_listagg_format, 't.tag';
220              
221 0   0     0 my $change = $self->dbh->selectrow_hashref(qq{
222             SELECT id, name, project, note, "timestamp", planner_name, planner_email,
223             tags, script_hash
224             FROM (
225             SELECT c.change_id AS id, c.change AS name, c.project, c.note,
226             $tscol AS "timestamp", c.planner_name, c.planner_email,
227             $tagcol AS tags, c.committed_at, c.script_hash
228             FROM changes c
229             LEFT JOIN tags t ON c.change_id = t.change_id
230             WHERE c.project = ?
231             AND c.committed_at $op (
232             SELECT committed_at FROM changes WHERE change_id = ?
233             )
234             GROUP BY c.change_id, c.change, c.project, c.note, c.planned_at,
235             c.planner_name, c.planner_email, c.committed_at, c.script_hash
236             ) changes
237             ORDER BY changes.committed_at $dir
238             LIMIT 1 $offset_expr
239             }, undef, $self->plan->project, $change_id) || return undef;
240 0         0 $change->{timestamp} = _dt $change->{timestamp};
241 0 0       0 unless (ref $change->{tags}) {
242 0 0       0 $change->{tags} = $change->{tags} ? [ split / / => $change->{tags} ] : [];
243             }
244 0         0 return $change;
245             }
246              
247             sub changes_requiring_change {
248 0     0 1 0 my ( $self, $change ) = @_;
249             # Why CTE: https://forums.oracle.com/forums/thread.jspa?threadID=1005221
250             # NOTE: Query from DBIEngine doesn't work in Exasol:
251             # Error: [00444] more than one column in select list of correlated subselect
252             # The CTE-based query below seems to be fine, however.
253 0         0 return @{ $self->dbh->selectall_arrayref(q{
  0         0  
254             WITH tag AS (
255             SELECT tag, committed_at, project,
256             ROW_NUMBER() OVER (partition by project ORDER BY committed_at) AS rnk
257             FROM tags
258             )
259             SELECT c.change_id, c.project, c.change, t.tag AS asof_tag
260             FROM dependencies d
261             JOIN changes c ON c.change_id = d.change_id
262             LEFT JOIN tag t ON t.project = c.project AND t.committed_at >= c.committed_at
263             WHERE d.dependency_id = ?
264             AND (t.rnk IS NULL OR t.rnk = 1)
265             }, { Slice => {} }, $change->id) };
266             }
267              
268             sub name_for_change_id {
269 0     0 1 0 my ( $self, $change_id ) = @_;
270             # Why CTE: https://forums.oracle.com/forums/thread.jspa?threadID=1005221
271             # NOTE: Query from DBIEngine doesn't work in Exasol:
272             # Error: [0A000] Feature not supported: non-equality correlations in correlated subselect
273             # The CTE-based query below seems to be fine, however.
274 0         0 return $self->dbh->selectcol_arrayref(q{
275             WITH tag AS (
276             SELECT tag, committed_at, project,
277             ROW_NUMBER() OVER (partition by project ORDER BY committed_at) AS rnk
278             FROM tags
279             )
280             SELECT change || COALESCE(t.tag, '@HEAD')
281             FROM changes c
282             LEFT JOIN tag t ON c.project = t.project AND t.committed_at >= c.committed_at
283             WHERE change_id = ?
284             AND (t.rnk IS NULL OR t.rnk = 1)
285             }, undef, $change_id)->[0];
286             }
287              
288             sub _cid {
289 1     1   535 my ( $self, $ord, $offset, $project ) = @_;
290              
291 1 50       20 my $offset_expr = $offset ? " OFFSET $offset" : '';
292             return try {
293 1   0 1   230 $self->dbh->selectcol_arrayref(qq{
294             SELECT change_id
295             FROM changes
296             WHERE project = ?
297             ORDER BY committed_at $ord
298             LIMIT 1$offset_expr
299             }, undef, $project || $self->plan->project)->[0];
300             } catch {
301 1 50 33 1   45 return if $self->_no_table_error && !$self->initialized;
302 1         13 die $_;
303 1         46 };
304             }
305              
306             sub is_deployed_tag {
307 0     0 1 0 my ( $self, $tag ) = @_;
308 0         0 return $self->dbh->selectcol_arrayref(
309             'SELECT 1 FROM tags WHERE tag_id = ?',
310             undef, $tag->id
311             )->[0];
312             }
313              
314             sub _registry_variable {
315 13     13   30 my $self = shift;
316 13         242 my $schema = $self->registry;
317 13         729 return "DEFINE registry=$schema;";
318             }
319              
320             sub _initialize {
321 0     0   0 my $self = shift;
322 0         0 my $schema = $self->registry;
323 0 0       0 hurl engine => __ 'Sqitch already initialized' if $self->initialized;
324              
325             # Load up our database.
326 0         0 (my $file = file(__FILE__)->dir->file('exasol.sql')) =~ s/"/""/g;
327 0         0 $self->_run_with_verbosity($file);
328 0 0       0 $self->dbh->do("OPEN SCHEMA $schema") if $schema;
329 0         0 $self->_register_release;
330             }
331              
332             sub _limit_offset {
333             # LIMIT/OFFSET don't support parameters, alas. So just put them in the query.
334 6     6   502 my ($self, $lim, $off) = @_;
335             # OFFSET cannot be used without LIMIT, sadly.
336 6 100 66     94 return ['LIMIT ' . ($lim || $self->_limit_default), "OFFSET $off"], [] if $off;
337 4 100       32 return ["LIMIT $lim"], [] if $lim;
338 2         20 return [], [];
339             }
340              
341             sub _regex_expr {
342 4     4   19 my ( $self, $col, $regex ) = @_;
343 4 100       30 $regex = '.*' . $regex if $regex !~ m{^\^};
344 4 100       20 $regex .= '.*' if $regex !~ m{\$$};
345 4         12 my $op = $self->_regex_op;
346 4         31 return "$col $op ?", $regex;
347             }
348              
349             # Override to lock the changes table. This ensures that only one instance of
350             # Sqitch runs at one time.
351             sub begin_work {
352 0     0 1 0 my $self = shift;
353 0         0 my $dbh = $self->dbh;
354              
355             # Start transaction and lock changes to allow only one change at a time.
356             # https://www.exasol.com/portal/pages/viewpage.action?pageId=22518143
357 0         0 $dbh->begin_work;
358 0         0 $dbh->do('DELETE FROM changes WHERE FALSE');
359 0         0 return $self;
360             }
361              
362             # Release lock by comitting or rolling back.
363             sub finish_work {
364 0     0 1 0 my $self = shift;
365 0         0 $self->dbh->commit;
366 0         0 return $self;
367             }
368              
369             sub rollback_work {
370 0     0 1 0 my $self = shift;
371 0         0 $self->dbh->rollback;
372 0         0 return $self;
373             }
374              
375             sub _file_for_script {
376 9     9   9127 my ($self, $file) = @_;
377              
378             # Just use the file if no special character.
379 9 100       86 if ($file !~ /[@?%\$]/) {
380 6         41 $file =~ s/"/""/g;
381 6         62 return $file;
382             }
383              
384             # Alias or copy the file to a temporary directory that's removed on exit.
385 3         128 (my $alias = $file->basename) =~ s/[@?%\$]/_/g;
386 3         90 $alias = $self->tmpdir->file($alias);
387              
388             # Remove existing file.
389 3 100       248 if (-e $alias) {
390 1 50       63 $alias->remove or hurl exasol => __x(
391             'Cannot remove {file}: {error}',
392             file => $alias,
393             error => $!
394             );
395             }
396              
397 2         120 if (App::Sqitch::ISWIN) {
398             # Copy it.
399             $file->copy_to($alias) or hurl exasol => __x(
400             'Cannot copy {file} to {alias}: {error}',
401             file => $file,
402             alias => $alias,
403             error => $!
404             );
405             } else {
406             # Symlink it.
407 2         15 $alias->remove;
408 2 50       198 symlink $file->absolute, $alias or hurl exasol => __x(
409             'Cannot symlink {file} to {alias}: {error}',
410             file => $file,
411             alias => $alias,
412             error => $!
413             );
414             }
415              
416             # Return the alias.
417 2         319 $alias =~ s/"/""/g;
418 2         106 return $alias;
419             }
420              
421             sub run_file {
422 2     2 1 2456 my $self = shift;
423 2         13 my $file = $self->_file_for_script(shift);
424 2         11 $self->_capture(qq{\@"$file"});
425             }
426              
427             sub _run_with_verbosity {
428 2     2   4 my $self = shift;
429 2         6 my $file = $self->_file_for_script(shift);
430             # Suppress STDOUT unless we want extra verbosity.
431             #my $meth = $self->can($self->sqitch->verbosity > 1 ? '_run' : '_capture');
432 2         4 my $meth = '_capture';
433 2         13 $self->$meth(qq{\@"$file"});
434             }
435              
436 0     0 1 0 sub run_upgrade { shift->_run_with_verbosity(@_) }
437 2     2 1 1360 sub run_verify { shift->_run_with_verbosity(@_) }
438              
439             sub run_handle {
440 1     1 1 1125 my ($self, $fh) = @_;
441 1         18 my $conn = $self->_script;
442 1         31 open my $tfh, '<:utf8_strict', \$conn;
443 1         443 $self->sqitch->spool( [$tfh, $fh], $self->exaplus );
444             }
445              
446             # Exasol treats empty string as NULL; adjust accordingly..
447              
448             sub _log_tags_param {
449 0     0   0 my $res = join ' ' => map { $_->format_name } $_[1]->tags;
  0         0  
450 0   0     0 return $res || ' ';
451             }
452              
453             sub _log_requires_param {
454 0     0   0 my $res = join ',' => map { $_->as_string } $_[1]->requires;
  0         0  
455 0   0     0 return $res || ' ';
456             }
457              
458             sub _log_conflicts_param {
459 0     0   0 my $res = join ',' => map { $_->as_string } $_[1]->conflicts;
  0         0  
460 0   0     0 return $res || ' ';
461             }
462              
463             sub _no_table_error {
464 3   66 3   40 return $DBI::errstr && $DBI::errstr =~ /object \w+ not found/m;
465             }
466              
467             sub _no_column_error {
468 2   66 2   21 return $DBI::errstr && $DBI::errstr =~ /object \w+ not found/m;
469             }
470              
471             sub _unique_error {
472             # Unique constraints not supported by Exasol
473 1     1   6 return 0;
474             }
475              
476             sub _script {
477 11     11   5382 my $self = shift;
478 11         326 my $uri = $self->uri;
479 11         478 my %vars = $self->variables;
480              
481             return join "\n" => (
482             'SET FEEDBACK OFF;',
483             'SET HEADING OFF;',
484             'WHENEVER OSERROR EXIT 9;',
485             'WHENEVER SQLERROR EXIT 4;',
486 3         14 (map {; (my $v = $vars{$_}) =~ s/'/''/g; qq{DEFINE $_='$v';} } sort keys %vars),
  3         14  
487             $self->_registry_variable,
488             # Just 'map { s/;?$/;/r } ...' doesn't work in earlier Perl versions;
489             # see: https://www.perlmonks.org/index.pl?node_id=1048579
490 11         156 map { (my $foo=$_) =~ s/;?$/;/; $foo } @_
  16         137  
  16         89  
491             );
492             }
493              
494             sub _run {
495 1     1   4300 my $self = shift;
496 1         5 my $script = $self->_script(@_);
497 1     1   8 open my $fh, '<:utf8_strict', \$script;
  1     1   4  
  1         7  
  1         845  
  1         3  
  1         5  
  1         34  
498 1         826 return $self->sqitch->spool( $fh, $self->exaplus );
499             }
500              
501             sub _capture {
502 3     3   3179 my $self = shift;
503 3         17 my $conn = $self->_script(@_);
504 3         9 my @out;
505             my @errout;
506              
507 3         17 $self->sqitch->debug('CMD: ' . join(' ', $self->exaplus));
508 3         191 $self->sqitch->debug("SQL:\n---\n", $conn, "\n---");
509              
510 3         45 require IPC::Run3;
511 3         17 IPC::Run3::run3(
512             [$self->exaplus], \$conn, \@out, \@out,
513             { return_if_system_error => 1 },
514             );
515              
516             # EXAplus doesn't always seem to give a useful exit value; we need to match
517             # on output as well..
518 3 100 66     18211 if (my $err = $? || grep { /^Error:/m } @out) {
519             # Ugh, send everything to STDERR.
520 1         57 $self->sqitch->vent(@out);
521 1         107 hurl io => __x(
522             '{command} unexpectedly failed; exit value = {exitval}',
523             command => $self->client,
524             exitval => ($err >> 8),
525             );
526             }
527 2 100       73 return wantarray ? @out : \@out;
528             }
529              
530             1;
531              
532             __END__
533              
534             =head1 Name
535              
536             App::Sqitch::Engine::exasol - Sqitch Exasol Engine
537              
538             =head1 Synopsis
539              
540             my $exasol = App::Sqitch::Engine->load( engine => 'exasol' );
541              
542             =head1 Description
543              
544             App::Sqitch::Engine::exasol provides the Exasol storage engine for Sqitch. It
545             is tested with Exasol 6.0 and higher.
546              
547             =head1 Interface
548              
549             =head2 Instance Methods
550              
551             =head3 C<initialized>
552              
553             $exasol->initialize unless $exasol->initialized;
554              
555             Returns true if the database has been initialized for Sqitch, and false if it
556             has not.
557              
558             =head3 C<initialize>
559              
560             $exasol->initialize;
561              
562             Initializes a database for Sqitch by installing the Sqitch registry schema.
563              
564             =head3 C<exaplus>
565              
566             Returns a list containing the C<exaplus> client and options to be passed to it.
567             Used internally when executing scripts.
568              
569             =head1 Author
570              
571             David E. Wheeler <david@justatheory.com>
572              
573             =head1 License
574              
575             Copyright (c) 2012-2023 iovation Inc., David E. Wheeler
576              
577             Permission is hereby granted, free of charge, to any person obtaining a copy
578             of this software and associated documentation files (the "Software"), to deal
579             in the Software without restriction, including without limitation the rights
580             to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
581             copies of the Software, and to permit persons to whom the Software is
582             furnished to do so, subject to the following conditions:
583              
584             The above copyright notice and this permission notice shall be included in all
585             copies or substantial portions of the Software.
586              
587             THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
588             IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
589             FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
590             AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
591             LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
592             OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
593             SOFTWARE.
594              
595             =cut