view release on metacpan or  search on metacpan

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

        # Can we create a schema?
        my $dbh = $self->dbh;
        my $reg = $dbh->quote_identifier($self->registry);
        $dbh->do("CREATE SCHEMA IF NOT EXISTS $reg");
    } catch {
        die $_ unless $DBI::state && $DBI::state eq '42501'; # ERRCODE_INSUFFICIENT_PRIVILEGE
        # Cannot create schema; strip out schema stuff.
        $file = $self->_strip_file($file);
    };

    # All good.
    return $self->run_file($file);
}

# Creates and returns a copy of $file with C<CREATE SCHEMA> and C<COMMENT ON
# SCHEMA> commands stripped out.
sub _strip_file {
    my ($self, $file) = @_;
    my $in = $file->open('<:raw') or hurl io => __x(
        'Cannot open {file}: {error}',
        file  => $file,
        error => $!
    );

    require File::Temp;
    my $out = File::Temp->new;
    while (<$in>) {
        s/C(?:REATE|OMMENT ON) SCHEMA\b.+//;
        print {$out} $_;
    }

    close $out;
    return $out;
}

sub _no_table_error  {
    return $DBI::state && $DBI::state eq '42S02'; # ERRCODE_UNDEFINED_TABLE
}

sub _no_column_error  {
    return $DBI::state && $DBI::state eq '42703'; # ERRCODE_UNDEFINED_COLUMN
}

sub _unique_error  {
    # https://docs.snowflake.com/en/sql-reference/constraints-overview
    # Snowflake supports defining and maintaining constraints, but does not
    # enforce them, except for NOT NULL constraints, which are always enforced.
    return 0;
}

sub _ts2char_format {
    # The colon has to be inside the quotation marks, because otherwise it
    # generates wayward single quotation marks. Bug report:
    # https://support.snowflake.net/s/case/5000Z000010wTkKQAU/
    qq{to_varchar(CONVERT_TIMEZONE('UTC', %s), '"year:"YYYY":month:"MM":day:"DD":hour:"HH24":minute:"MI":second:"SS":time_zone:UTC"')};
}

sub _char2ts { $_[1]->as_string(format => 'iso') }

sub _dt($) {
    require App::Sqitch::DateTime;
    return App::Sqitch::DateTime->new(split /:/ => shift);
}

sub _regex_op { 'REGEXP' } # XXX But not used; see regex_expr() below.

sub _simple_from { ' FROM dual' }

sub _cid {
    my ( $self, $ord, $offset, $project ) = @_;

    my $offset_expr = $offset ? " OFFSET $offset" : '';
    return try {
        $self->dbh->selectcol_arrayref(qq{
            SELECT change_id
              FROM changes
             WHERE project = ?
             ORDER BY committed_at $ord
             LIMIT 1$offset_expr
        }, undef, $project || $self->plan->project)->[0];
    } catch {
        return if $self->_no_table_error && !$self->initialized;
        die $_;
    };
}

sub changes_requiring_change {
    my ( $self, $change ) = @_;
    # 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->selectall_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 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')