2013-08-29

Sacarle más partido a Plex con un poco de SQL

Si no conoces Plex, échale un ojo: http://www.plexapp.com, además de las aplicaciones para Windows y Mac (gratuitas) tienen unos excelentes clientes (baratillos) para iOS y Android que, en sus versiones "premium" (plexpass) tienen una funcionalidad interesante, llamada PlexSync.

Resumidamente, plexsync premite sincronizar automáticamente ciertos contenidos en nuestros dispositivos móviles para, por ejemplo, llevar siempre los episodios que no hemos visto de nuestra serie favorita en el tablet o el móvil.

Como Plex almacena el catálogo de contenidos en una base de datos (SQLite 3) podemos montarnos un plexsync casero con un poco de SQL... le pongo muchos comentarios para que se entienda:

--
--  solo me interesa la ruta del fichero, para llegar a ella
--  tengo que enlazar las tablas 
--  METADATA_ITEMS, MEDIA_ITEMS y MEDIA_PARTS 
--  METADATA_ITEMS es la tabla principal, por asi decirlo
select distinct P.file  
  from METADATA_ITEMS I
           --

           -- tengo varias secciones (musica, peliculas en ingles, etc...)
           -- y no me interesan todas, asi que enlazo la tabla LIBRARY_SECTIONS
           -- para poder filtrar  
           inner join LIBRARY_SECTIONS L       
              on I.library_section_id = L.id
           --

           -- la tabla METADATA_ITEM_SETTINGS me interesa porque es ahí
           -- donde plex almacena si has visto o no el item 
           left outer join METADATA_ITEM_SETTINGS S
              on I.guid = S.guid

           --
           -- ahora vienen las dos tablas que necesito para llegar al
           -- nombre del fichero
           inner join MEDIA_ITEMS M            
              on I.id = M.metadata_item_id
           inner join MEDIA_PARTS P            
              on M.id = P.media_item_id

           --
           -- los episodios de una serie se agrupan en temporadas (el
           -- item "padre") y, a su vez, en series (el "abuelo")        
           left outer join METADATA_ITEMS IPadre
              on I.parent_id = IPadre.id
           left outer join METADATA_ITEMS IAbuelo
              on IPadre.parent_id = IAbuelo.id
 where 

   --
   -- los "no vistos":
   ( S.view_count = 0 or S.view_count is null)
   --

   -- las secciones que me interesan:
   and L.name in
          ("01 Series",
           "02 Peliculas",
           "03 TVShows")

   --
   -- y, ahora viene lo chulo: me interesan los 3 siguientes episodios
   -- de cada serie, así que para cada fila que me devuelve la query 
   -- compruebo si el episodio es uno de los tres primeros que me devuelve
   -- esta subquery. no es muy eficiente, pero tampoco es que tenga prisa...
   and (
          IPadre.[index]*100 + I.[index] in
          (
            select IPadre2.[index] * 100 + I2.[index]
              from METADATA_ITEMS I2
                 inner join LIBRARY_SECTIONS L2       
                    on I2.library_section_id = L2.id
                 left outer join METADATA_ITEMS IPadre2
                    on I2.parent_id = IPadre2.id
                 left outer join METADATA_ITEMS IAbuelo2
                    on IPadre2.parent_id = IAbuelo2.id
              where IAbuelo.id = IAbuelo2.id

                and ( S2.view_count = 0 or S2.view_count is null)
              order by 1
              limit 3            
          )
          or

          --
          -- las peliculas no tienen abuelo
          IAbuelo.id is null
       ) 


Esta query devuelve una lista con las rutas de los ficheros que me interesa tener copiados en, digamos, un pen drive que me llevo por ahí. Sólo falta automatizar la copia:

Metemos la query en un fichero (query.sql) al que le metemos, al principio, estas dos líneas adicionales:

.headers OFF
.output ficheros.txt


y, al final, esta:

.quit

para que cuando ejecutemos la query el sqlite3 no nos devuelva el nombre de la columna y para que el resultado lo guarde en un ficherito llamado "ficheros.txt".

Finalmente, un pequeño script, y listo:

# query para obtener los ficheros ondeck
#   - la query esta en query.sql
#   - deja los resultados en "ficheros.txt"

# ruta de la b.d. de Plex Media Server
RUTABD="/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"
# ejecucion de la query en sqlite
cat query.sql | sqlite3 "$RUTABD"

# copiamos los ficheros al disco usb, asegurandonos de modificar fecha
while read unfichero; do
  sinextension=${unfichero%.*}
  # echo $sinextension
  nombrefichero=$(basename "$unfichero")
  # echo $nombrefichero
  cp -u "$sinextension".* /media/usb0/series
  touch "/media/usb0/series/${nombrefichero%.*}".*
done < "ficheros.txt"


# borramos ficheros viejos
find /media/usb0/series/* -mtime +1 -exec rm {} \;


No hay comentarios: