How to Manage Media Files Efficiently

📄 Procedure Documentation: AddOrUpdateMedia

📌 Purpose

This procedure handles the addition or updating of a media file in the system. It ensures that duplicate or unchanged files are skipped, and updated versions are properly stored both in the file system and the database.

💾 What It Does

1. Copies the selected media file into a predefined shared folder.
2. Checks if a file with the same name already exists:
   • If it exists and is identical (via MD5 hash): skips and shows a message.
   • If it exists but differs: prompts the user whether to overwrite.
3. Copies the file to the destination.
4. Retrieves the file size and attempts to extract the video duration using VideoGrabber.OpenPlayer.
5. Checks if the filename already exists in the database:
   • If yes: updates the existing record.
   • If no: inserts a new record with a generated ID.
6. Refreshes the cdsMedia dataset to reflect changes.

📂 File Handling

• DestFolder: Predefined destination folder from the database config.
• SrcHash/DestHash: MD5 hash values used to detect duplicate content.
• CopyFile: Uses Windows API to copy files.

🎬 Video Duration

• Uses VideoGrabber.PlayerDuration divided by 10,000,000 to convert from 100ns to seconds.
• Falls back to 0 if unable to read duration.

🧮 Database Fields Involved

Field	Description
ID	Unique identifier (auto-generated)
FILENAME	Name of the media file
FILE_HASH	MD5 hash for duplicate detection
FILE_SIZE	Size of the file in bytes
DURATION_SEC	Duration of video in seconds
LAST_UPDATED	Timestamp of last update

✅ Notes for New Developers

• MD5 Hash: Used to determine if a file has changed.
• VideoGrabber: Third-party component used to get video duration.
• Thread Safety: If used in a UI context, wrap this in a thread to prevent freezing.
• Error Handling: If duration cannot be read, it defaults to 0 to ensure database consistency.

📌 Example Use Case

A user selects a new video to upload. The system checks if that file already exists. If it’s a duplicate, it is skipped. If it’s new or updated, the file is copied to the server and its metadata (size, hash, duration) is stored or updated in the database.

💻 Delphi Procedure Code

procedure TfmMedia.AddOrUpdateMedia(const SrcFile: string; FIBQuery: TpFIBQuery);
var
  DestFolder, DestFile: string;
  SrcHash, DestHash: string;
  FileExistsOnDest: Boolean;
  FileSize: Integer;
  NewID: Integer;
  DurationSec: Integer;
begin
  DestFolder := dmDatabase.MediaFilePath+'\';
  DestFile := IncludeTrailingPathDelimiter(DestFolder) + ExtractFileName(SrcFile);
  FileExistsOnDest := FileExists(DestFile);

  SrcHash := GetFileMD5(SrcFile);

  if FileExistsOnDest then
  begin
    DestHash := GetFileMD5(DestFile);
    if DestHash = SrcHash then
    begin
      ShowMessage('File already exists and is identical. Skipping.');
      Exit;
    end
    else
    begin
      if MessageDlg('File exists but has changed. Overwrite?', mtConfirmation, [mbYes, mbNo], 0) = mrNo then
        Exit;
    end;
  end;

  // Copy file (overwrite if exists)
  if not CopyFile(PChar(SrcFile), PChar(DestFile), False) then
  begin
    ShowMessage('Failed to copy file to shared folder.');
    Exit;
  end;

  FileSize := FileSizeByName(DestFile);

  // Get Video Duration in Seconds
  try
    VideoGrabber.PlayerFileName := DestFile;
    VideoGrabber.OpenPlayer;
    DurationSec := Round(VideoGrabber.PlayerDuration / 10000000);  // Convert 100ns units to seconds
  except
    DurationSec := 0; // fallback if unreadable
  end;

  // Check if record exists in DB
  dmDatabase.writeTransaction.StartTransaction;
  FIBQuery.Close;
  FIBQuery.SQL.Text := 'SELECT ID FROM MEDIA WHERE FILENAME = :fn';
  FIBQuery.ParamByName('fn').AsString := ExtractFileName(DestFile);
  FIBQuery.ExecQuery;

  if not FIBQuery.EOF then
  begin
    // Update existing record
    FIBQuery.Close;
    FIBQuery.SQL.Text :=
      'UPDATE MEDIA SET FILE_HASH = :hash, FILE_SIZE = :sz, DURATION_SEC = :dur, LAST_UPDATED = CURRENT_TIMESTAMP ' +
      'WHERE FILENAME = :fn';
    FIBQuery.ParamByName('fn').AsString := ExtractFileName(DestFile);
    FIBQuery.ParamByName('hash').AsString := SrcHash;
    FIBQuery.ParamByName('sz').AsInteger := FileSize;
    FIBQuery.ParamByName('dur').AsInteger := DurationSec;
    FIBQuery.ExecQuery;
    dmDatabase.writeTransaction.Commit;
    ShowMessage('File updated successfully.');
  end
  else
  begin
    // Insert new record
    NewID := dmDatabase.GetNextID;

    FIBQuery.Close;
    FIBQuery.SQL.Text :=
      'INSERT INTO MEDIA (ID, FILENAME, FILE_HASH, FILE_SIZE, DURATION_SEC, LAST_UPDATED) ' +
      'VALUES (:id, :fn, :hash, :sz, :dur, CURRENT_TIMESTAMP)';
    FIBQuery.ParamByName('id').AsInteger := NewID;
    FIBQuery.ParamByName('fn').AsString := ExtractFileName(DestFile);
    FIBQuery.ParamByName('hash').AsString := SrcHash;
    FIBQuery.ParamByName('sz').AsInteger := FileSize;
    FIBQuery.ParamByName('dur').AsInteger := DurationSec;
    FIBQuery.ExecQuery;
    dmDatabase.writeTransaction.Commit;
    ShowMessage('File added successfully.');
  end;

  FIBQuery.Close;

  cdsMedia.Close;
  cdsMedia.Open;
end;