view release on metacpan or  search on metacpan

lib/App/Sqitch/Engine/snowflake.pm  view on Meta::CPAN

              FROM tags
        )
        SELECT c.change_id, c.project, c.change, t.tag AS asof_tag
          FROM dependencies d
          JOIN changes  c ON c.change_id = d.change_id
          LEFT JOIN tag t ON t.project   = c.project AND t.committed_at >= c.committed_at
         WHERE d.dependency_id = ?
           AND (t.rnk IS NULL OR t.rnk = 1)
    }, { Slice => {} }, $change->id) };
}

sub name_for_change_id {
    my ( $self, $change_id ) = @_;
    # NOTE: Query from DBIEngine doesn't work in Snowflake:
    #   SQL compilation error: Unsupported subquery type cannot be evaluated (SQL-42601)
    # Looks like it doesn't yet support correlated subqueries.
    # https://docs.snowflake.com/en/sql-reference/operators-subquery.html
    # The CTE-based query borrowed from Exasol seems to be fine, however.
    return $self->dbh->selectcol_arrayref(q{
        WITH tag AS (
            SELECT tag, committed_at, project,
                   ROW_NUMBER() OVER (partition by project ORDER BY committed_at) AS rnk
              FROM tags
        )
        SELECT change || COALESCE(t.tag, '@HEAD')
          FROM changes c
          LEFT JOIN tag t ON c.project = t.project AND t.committed_at >= c.committed_at
         WHERE change_id = ?
           AND (t.rnk IS NULL OR t.rnk = 1)
    }, undef, $change_id)->[0];
}

# https://support.snowflake.net/s/question/0D50Z00008BENO5SAP
sub _limit_default { '4611686018427387903' }

sub _limit_offset {
    # LIMIT/OFFSET don't support parameters, alas. So just put them in the query.
    my ($self, $lim, $off)  = @_;
    # OFFSET cannot be used without LIMIT, sadly.
    # https://support.snowflake.net/s/case/5000Z000010wfnWQAQ
    return ['LIMIT ' . ($lim || $self->_limit_default), "OFFSET $off"], [] if $off;
    return ["LIMIT $lim"], [] if $lim;
    return [], [];
}

sub _regex_expr {
    my ( $self, $col, $regex ) = @_;
    # Snowflake regular expressions are implicitly anchored to match the
    # entire string. To work around this, issue, we use regexp_substr(), which
    # is not so anchored, and just check to see that if it returns a string.
    # https://support.snowflake.net/s/case/5000Z000010wbUSQAY
    # https://support.snowflake.net/s/question/0D50Z00008C90beSAB/
    return "regexp_substr($col, ?) IS NOT NULL", $regex;
}

sub run_file {
    my ($self, $file) = @_;
    $self->_run(_quiet_opts, '--filename' => $file);
}

sub run_verify {
    my ($self, $file) = @_;
    # Suppress STDOUT unless we want extra verbosity.
    return $self->run_file($file) unless $self->sqitch->verbosity > 1;
    $self->_run(_verbose_opts, '--filename' => $file);
}

sub run_handle {
    my ($self, $fh) = @_;
    $self->_spool($fh);
}

sub _run {
    my $self   = shift;
    my $sqitch = $self->sqitch;
    my $pass   = $self->password or
        # Use capture and emit instead of _run to avoid a wayward newline in
        # the output.
        return $sqitch->emit_literal( $sqitch->capture( $self->snowsql, @_ ) );
    # Does not override connection config, alas.
    local $ENV{SNOWSQL_PWD} = $pass;
    return $sqitch->emit_literal( $sqitch->capture( $self->snowsql, @_ ) );
}

sub _capture {
    my $self   = shift;
    my $sqitch = $self->sqitch;
    my $pass   = $self->password or
        return $sqitch->capture( $self->snowsql, _verbose_opts, @_ );
    local $ENV{SNOWSQL_PWD} = $pass;
    return $sqitch->capture( $self->snowsql, _verbose_opts, @_ );
}

sub _probe {
    my $self   = shift;
    my $sqitch = $self->sqitch;
    my $pass   = $self->password or
        return $sqitch->probe( $self->snowsql, _verbose_opts, @_ );
    local $ENV{SNOWSQL_PWD} = $pass;
    return $sqitch->probe( $self->snowsql, _verbose_opts, @_ );
}

sub _spool {
    my $self   = shift;
    my $fh     = shift;
    my $sqitch = $self->sqitch;
    my $pass   = $self->password or
        return $sqitch->spool( $fh, $self->snowsql, _verbose_opts, @_ );
    local $ENV{SNOWSQL_PWD} = $pass;
    return $sqitch->spool( $fh, $self->snowsql, _verbose_opts, @_ );
}

1;

__END__

=head1 Name

App::Sqitch::Engine::snowflake - Sqitch Snowflake Engine

=head1 Synopsis