/ 
App-DrivePlayer-0.2.9
/ App::DrivePlayer::SheetDB

NAME

App::DrivePlayer::SheetDB - Sync the DrivePlayer library to/from a Google Sheet

SYNOPSIS

use App::DrivePlayer::SheetDB;

my $sheet = App::DrivePlayer::SheetDB->new(
    api            => $google_rest_api,
    spreadsheet_id => $id,             # omit when calling create()
);

my $id      = $sheet->create();             # create spreadsheet, returns ID
my $summary = $sheet->sync_with_db($db, drive_exists => \&check);
my $counts  = $sheet->push_to_sheet($db);   # merge-push only
my $counts  = $sheet->pull_from_sheet($db); # new-device restore

DESCRIPTION

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

folders

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

One tab per folder (named after the folder)

Track metadata columns: 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.

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.

NEW DEVICE WORKFLOW

On first launch the app detects a fresh local DB and automatically runs 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.

METHODS

new(%args)

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

create()

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

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

Two-way reconciliation keyed on drive_id:

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

  • drive_id in the DB but not the sheet: row added to the sheet.

  • drive_id on the sheet but not in the DB: drive_exists->($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.

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

Returns a summary hashref.

push_to_sheet($db)

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

pull_from_sheet($db)

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