view release on metacpan or  search on metacpan

lib/App/DrivePlayer/SheetDB.pm  view on Meta::CPAN

        next unless $row->{drive_id} && $row->{name};
        $db->upsert_scan_folder($row->{drive_id}, $row->{name});
        $folder_count++;
    }

    # Pull tracks from each folder worksheet
    my $track_count = 0;
    for my $folder_row (@$folder_rows) {
        next unless $folder_row->{drive_id} && $folder_row->{name};

        # Ensure a root folder record exists for this scan folder so that
        # skeleton tracks have a valid folder_id and tracks_by_scan_folder
        # can find them before a Drive scan has been run.
        my $sf = $db->get_scan_folder_by_drive_id($folder_row->{drive_id});
        next unless $sf;
        my $folder = $db->upsert_folder(
            drive_id        => $folder_row->{drive_id},
            name            => $folder_row->{name},
            parent_drive_id => undef,
            path            => $folder_row->{name},
            scan_folder_id  => $sf->{id},
        );

        my $rows = $self->_read_worksheet($ss, _ws_name($folder_row->{name}));
        for my $row (@$rows) {
            next unless $row->{drive_id};
            my %meta = map  { $_ => $row->{$_} }
                       grep { defined $row->{$_} && $row->{$_} ne '' }
                       $SHEET_PROPERTIES{tracks}->{cols}->@*;
            my $track = $db->get_track_by_drive_id($row->{drive_id});
            if ($track) {
                $db->update_track_metadata($track->{id}, %meta);
            } else {
                $db->upsert_track_from_metadata(%meta, folder_id => $folder->{id});
            }
            $track_count++;
        }
    }

    return { scan_folders => $folder_count, tracks => $track_count };
}

# ------------------------------------------------------------------
# Private helpers
# ------------------------------------------------------------------

sub _sheets_api {
    my ($self) = @_;
    return Google::RestApi::SheetsApi4->new(api => $self->api);
}

sub _open {
    my ($self) = @_;
    die "No spreadsheet_id configured\n" unless $self->spreadsheet_id;

    # Use Drive API to verify the file exists and is not trashed before
    # opening via Sheets API (which happily operates on trashed files).
    my $drive = Google::RestApi::DriveApi3->new(api => $self->api);
    my $meta  = eval { $drive->file(id => $self->spreadsheet_id)->get(fields => 'id,trashed') };
    if ($@) {
        die "SHEET_NOT_FOUND: $@" if $@ =~ /404|not.?found/i;
        die $@;
    }
    die "SHEET_NOT_FOUND: spreadsheet has been trashed\n" if $meta->{trashed};

    return $self->_sheets_api->open_spreadsheet(id => $self->spreadsheet_id);
}

# Write a header row then all data rows to a named worksheet (full replace).
sub _write_worksheet {
    my ($self, $ss, $name, $properties, $rows) = @_;
    my $cols = $SHEET_PROPERTIES{$properties}{cols};

    my $n  = scalar @$rows;
    my $ws = $self->_ensure_worksheet($ss, $name, $n + 1);
    $ws->clear_values()->submit_requests();
    $ws->row(1, $cols);
    $ws->rows([2 .. $n + 1], $rows) if $n;
}

# Read a worksheet and return arrayref of hashrefs keyed by header row.
# Returns [] if the worksheet doesn't exist or is empty.
sub _read_worksheet {
    my ($self, $ss, $name) = @_;
    my $ws = eval { $ss->open_worksheet(name => $name) };
    if ($@) { $log->warn("Could not open worksheet '$name': $@") if $log; return [] }
    return [] unless $ws;

    $ws->enable_header_row();
    my $cols = $ws->tie_cols;
    tied(%$cols)->values();      # prefetch the columns.

    my @result;
    my $i = tied(%$cols)->iterator(from => 0);
    while (my $row = $i->iterate()) {
        tied(%$row)->values();
        last unless $row->{drive_id};
        push(@result, $row);
    }
    return \@result;
}

# Open a worksheet by name, creating it with enough rows if absent, or
# expand it if it already exists but the grid is too small for the data.
sub _ensure_worksheet {
    my ($self, $ss, $name, $needed_rows) = @_;
    # Default Google Sheets grid is 1000 rows; honour that for small data.
    $needed_rows = 1000 if !$needed_rows || $needed_rows < 1000;

    # Try to create; silently ignore the error if it already exists.
    # If the API call fails (e.g. sheet already exists), the failed addSheet
    # request is left in $ss's internal batch queue.  Clear it so that
    # subsequent submit_requests() calls don't re-send the stale request.
    eval { $ss->add_worksheet(
        name            => $name,
        grid_properties => { rows => $needed_rows },
    )->submit_requests() };
    delete $ss->{requests} if $@;

    my $ws = $ss->open_worksheet(name => $name);