Library.db size more than doubled in latest version

Plex Media Server
,
410

I tested the Windows deflate bat file and found that it doesn’t determine the actual location of the plex sqlite exe and the location of the database file. It just assumes the default locations, or rather leaves it to the user to edit the bat file and fill in the locations.
I copied the detection routines from the repair bat file over, so we end up with

@echo off
setlocal EnableDelayedExpansion

REM Windows .BAT script to deflate Plex Media Server database
REM Run this script under the same Windows user account that you use to run Plex Media Server

REM Find PMS installation location.
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PSQL=%%b\Plex SQLite.exe"

if not exist "%PSQL%" (
  REM InstallFolder might be set under HKLM, not HKCU
  for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKLM\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PSQL=%%b\Plex SQLite.exe"
)

REM If InstallFolder wasn't set, or the resulting file doesn't exist, iterate through the
REM PROGRAMFILES variables looking for it. If we still can't find it, ask the user to provide it.
if not exist "%PSQL%" (
  if exist "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe" (
    set "PSQL=%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe"
  ) else (
    if exist "%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe" (
      echo NOTE: 32-bit version of PMS detected on a 64-bit version of Windows. Updating to the 64-bit release of PMS is recommended.
      set "PSQL=%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe"
    ) else (
      echo Could not determine SQLite path. Please provide it below
      echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
      echo.
      REM Last ditch effort, ask the user for the full path to Plex SQLite.exe
      set /p "PSQL=Path to Plex SQLite.exe: "
      if not exist "!PSQL!" (
        echo "!PSQL!" could not be found. Cannot continue.
        goto :EOF
      )
    )
  )
)

REM Find PMS database location
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "LocalAppDataPath" 2^> nul') do set "DBDIR=%%b\Plex Media Server\Plug-in Support\Databases"
if not exist "%DBDIR%" (
  if exist "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases" (
    set "DBDIR=%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases"
  ) else (
    echo Could not determine Plex database path.
    echo Normally %LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases
    echo.
    goto :EOF
  )
)

set "CPPL=com.plexapp.plugins.library.db"

REM Sanity checks
if not exist "%DBDIR%" (
    echo ERROR: No such directory '%DBDIR%'
    exit /b 1
)
if not exist "%PSQL%" (
    echo ERROR: Cannot find 'Plex SQLite' at '%PSQL%'
    exit /b 2
)
if not exist "%DBDIR%\%CPPL%" (
    echo ERROR: Cannot find or no write permission for '%CPPL%'
    exit /b 3
)

echo ==== Current listing
dir "%DBDIR%"

REM Change to the databases directory
cd /d "%DBDIR%"

REM Remove output if exists
if exist "new.com.plexapp.plugins.library.db" del /f "new.com.plexapp.plugins.library.db"

echo ==== Deflating into new DB
echo CREATE TABLE temp_bandwidth as select * from statistics_bandwidth where account_id not null; > temp.sql
echo DROP TABLE statistics_bandwidth; >> temp.sql
echo ALTER TABLE temp_bandwidth RENAME to statistics_bandwidth; >> temp.sql
echo CREATE INDEX 'index_statistics_bandwidth_on_at' ON statistics_bandwidth ('at'); >> temp.sql
echo CREATE INDEX 'index_statistics_bandwidth_on_account_id_and_timespan_and_at' ON 'statistics_bandwidth' ('account_id', 'timespan', 'at'); >> temp.sql
echo VACUUM main into './new.com.plexapp.plugins.library.db'; >> temp.sql

"%PSQL%" "%CPPL%" < temp.sql
set Result=%ERRORLEVEL%

REM Clean up temporary SQL file
del /f temp.sql

REM Check for errors
if %Result% NEQ 0 (
    echo ERROR: Error code %Result% from deflate. Seek assistance
    exit /b 4
)

REM Confirm we have a DB
set OK=1
if not exist "new.com.plexapp.plugins.library.db" set OK=0

REM Confirm not zero size (using dir to get file size)
for %%F in ("new.com.plexapp.plugins.library.db") do set NewSize=%%~zF
if !NewSize! LEQ 300000 set OK=0

REM Abort if not OK
if %OK% NEQ 1 (
    echo ERROR: Something went wrong. Exiting.
    exit /b 31
)

REM Get file ownership and permissions (Windows equivalent)
REM Note: Windows doesn't have direct chown/chmod equivalents; using icacls for permissions info
echo ========== DEBUG ================
echo Owner: (Use icacls to check manually if needed)
icacls "%CPPL%"
echo New Size: %NewSize% bytes

REM Move existing DBs
echo Moving existing DB to '-bloat'
move "%CPPL%" "com.plexapp.plugins.library.db-bloat"
if exist "%CPPL%" move "%CPPL%" "%CPPL%-bloated"

echo Removing WAL and SHM if exist
if exist "%CPPL%-wal" del /f "%CPPL%-wal"
if exist "%CPPL%-shm" del /f "%CPPL%-shm"

REM Move new DB into position
echo Making new DB active
move "new.com.plexapp.plugins.library.db" "%CPPL%"

REM Set permissions (restore original permissions if possible)
REM Note: Windows permissions are complex; may need manual adjustment
echo Restoring permissions (check manually if needed)
icacls "%CPPL%" /reset

echo New databases directory contents
dir "%DBDIR%"

echo Done.
exit /b 0
4 Likes