view release on metacpan or  search on metacpan

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

# no ensure-worksheet dance.  Caller is responsible for making sure the
# worksheet already exists (it does for any track that's been scanned).
# Returns 1 on success, 0 if the track or scan-folder can't be resolved
# or the drive_id isn't already on the sheet.
sub push_track {
    my ($self, $db, $track_id) = @_;
    my $track = $db->get_track($track_id)             or return 0;
    my $sf    = $db->scan_folder_for_track($track_id) or return 0;
    my $tab   = _ws_name($sf->{name});
    my $ss    = $self->_open();

    my $ws = eval { $ss->open_worksheet(name => $tab) } or return 0;

    # 1 API call: values.get on column A.  Flat arrayref starting with
    # the "drive_id" header at [0]; data drive_ids at [1..].
    my $ids = $ws->col(1) || [];
    my $row_num;
    for my $i (1 .. $#$ids) {
        next unless ($ids->[$i] // '') eq ($track->{drive_id} // '');
        $row_num = $i + 1;   # array index -> 1-based sheet row
        last;
    }
    return 0 unless $row_num;   # not on sheet — fall through to a full sync

    my $cols   = $SHEET_PROPERTIES{tracks}{cols};
    my @values = map { $track->{$_} // '' } @$cols;

    # 1 API call: values.update on the single row range.
    $ws->row($row_num, \@values);
    return 1;
}

# Return drive_ids from both lists in order (local first), with no duplicates.
sub _union_ids {
    my ($local, $sheet) = @_;
    my %seen;
    return grep { length $_ && !$seen{$_}++ }
        (map { $_->{drive_id} } @$local),
        (map { $_->{drive_id} } @$sheet);
}

# Return the local value for $field if it's defined and non-empty, otherwise
# fall back to the sheet value (or '' if neither side has it).
sub _merge_field {
    my ($field, $local, $sheet) = @_;
    my $lv = $local ? $local->{$field} : undef;
    return $lv if defined $lv && $lv ne '';
    my $sv = $sheet ? $sheet->{$field} : undef;
    return defined $sv ? $sv : '';
}

# ------------------------------------------------------------------
# Sync  (bidirectional reconcile)
# ------------------------------------------------------------------

# Reconcile the local DB with the sheet per these rules:
#   - Both sides have a value for a field, different: DB wins.
#   - One side blank/missing: fill from the other (blank = missing).
#   - drive_id only in DB: add to sheet.
#   - drive_id only on sheet: ask drive_exists; if exists, add to DB; else
#     delete from sheet.  If drive_exists throws, keep the row as-is
#     (never destroy data on an API error).
#   - Scan-folder list: union, never auto-delete (config is user-owned).
# Returns a summary hashref.
sub sync_with_db {
    my ($self, $db, %opts) = @_;
    my $drive_exists = $opts{drive_exists} || sub { 1 };
    my $ss = $self->_open();

    my %summary = (
        folders_added_local => 0,
        folders_added_sheet => 0,
        tracks_added_local  => 0,
        tracks_added_sheet  => 0,
        tracks_deleted_sheet => 0,
        tracks_merged       => 0,
    );

    # --- Folders index: union, never auto-delete. ---
    my @local_folders      = $db->all_scan_folders();
    my $sheet_folders      = $self->_read_worksheet($ss, 'folders');
    my %local_folder_by_id = map { $_->{drive_id} => $_ } @local_folders;
    my %sheet_folder_by_id = map { $_->{drive_id} => $_ } @$sheet_folders;

    my @folder_ids = _union_ids(\@local_folders, $sheet_folders);
    my @folder_rows;
    for my $id (@folder_ids) {
        my $local = $local_folder_by_id{$id};
        my $sheet = $sheet_folder_by_id{$id};

        if ($sheet && !$local) {
            $db->upsert_scan_folder($id, $sheet->{name});
            $summary{folders_added_local}++;
        }
        elsif ($local && !$sheet) {
            $summary{folders_added_sheet}++;
        }
        push @folder_rows, [$id, _merge_field('name', $local, $sheet)];
    }
    $self->_write_worksheet($ss, 'folders', 'folders', \@folder_rows);

    # --- Per-folder track tabs: only process tabs for scan folders we
    # have locally, so another device's folder tabs are left untouched.
    my $cols = $SHEET_PROPERTIES{tracks}{cols};
    for my $sf (@local_folders) {
        my $tab_name      = _ws_name($sf->{name});
        my @local_tracks  = $db->tracks_by_scan_folder($sf->{id});
        my $sheet_rows    = $self->_read_worksheet($ss, $tab_name);
        my %local_by_id   = map { $_->{drive_id} => $_ } @local_tracks;
        my %sheet_by_id   = map { $_->{drive_id} => $_ } @$sheet_rows;

        # Ensure a root folder record exists so sheet-only tracks have a
        # valid folder_id when we upsert them from the sheet.
        my $folder_rec = $db->upsert_folder(
            drive_id        => $sf->{drive_id},
            name            => $sf->{name},
            parent_drive_id => undef,
            path            => $sf->{name},
            scan_folder_id  => $sf->{id},
        );

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

Maintains a Google Spreadsheet with one worksheet per scan folder, plus a
C<folders> index tab:

=over 4

=item folders

C<drive_id> and C<name> for every top-level folder in the library.

=item One tab per folder (named after the folder)

Track metadata columns: C<drive_id title artist album track_number year
duration_ms genre comment>.  Structural fields (folder_id, etc.)
are re-derived from Drive scanning and are not stored in the sheet.

=back

The local SQLite database remains the working store for all runtime queries.
The Sheet is a portable sync target accessible from any device with Drive access.

=head1 NEW DEVICE WORKFLOW

On first launch the app detects a fresh local DB and automatically runs
C<pull_from_sheet> to seed scan folders and track metadata from the
sheet.  The user then runs Library -> Sync to discover audio files on
Drive and reconcile two-way.

=head1 METHODS

=head2 new(%args)

C<api> (L<Google::RestApi> instance) is required.
C<spreadsheet_id> is optional (omit before calling C<create()>).

=head2 create()

Creates a new "DrivePlayer Library" spreadsheet with a C<folders> tab.
Returns and stores the new spreadsheet ID.

=head2 sync_with_db($db, drive_exists => \&cb)

Two-way reconciliation keyed on C<drive_id>:

=over 4

=item *

Fields present on both sides: DB wins, and any DB blanks are filled
from the sheet.  A blank (C<undef> or empty string) is treated as
"missing" on either side, so cleared fields cannot be propagated.

=item *

C<drive_id> in the DB but not the sheet: row added to the sheet.

=item *

C<drive_id> on the sheet but not in the DB: C<drive_exists-E<gt>($id)>
is called.  A truthy result adds the track to the DB; a falsy result
deletes the row from the sheet.  If the callback throws (API failure),
the row is preserved on both sides --sync never destroys data on error.

=item *

Scan-folder list: union only; folders are never auto-deleted because
the list is user-owned configuration.

=back

Returns a summary hashref.

=head2 push_to_sheet($db)

Merge-push only: local non-blank values overwrite the sheet, local
blanks preserve whatever is on the sheet, and C<drive_id>s only on the
sheet are kept intact.  Used by auto-push after metadata edits.

=head2 pull_from_sheet($db)

Upserts scan folders into SQLite and applies track metadata to any
tracks already present (keyed by C<drive_id>).  Used once on first
launch when the local DB is brand new.

=cut