Synology NAS: from DownloadStation to Filebot to Plex

DownloadStation

METHOD OVERVIEW:
================

This method involves creating a PSQL trigger, and PSQL C function which runs a bash script.
The bash script is responsible for running FileBot and notifying Plex server of new files.
The bash script outputs the recognized files to the PSQL C function, which removes completed
downloads containing those files from the database (and from DownloadStation GUI).

NOTE: you need to give the Synology user 'postgres' access to all the required paths
(download folder, plex media folder, scripts folder, filebot, java).
Easiest is to use synogroup to add postgres to a group that already has permissions.

STEP 1: Install PSQL headers, and compile the following PSQL C function:
========================================================================

-------------------- scrape.c --------------------
#include "postgres.h"
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* ... triggers ... */
#include "utils/rel.h"          /* ... and relations */
#include <stdio.h>
#include <stdlib.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum aftertaskcompleted(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(aftertaskcompleted);

Datum
aftertaskcompleted(PG_FUNCTION_ARGS)
{
    TriggerData *trigdata = (TriggerData *) fcinfo->context;
    TupleDesc   tupdesc;
    HeapTuple   rettuple;
    bool        checknull = false;
    bool        isnull;
    int         ret;

    /* make sure it's called as a trigger at all */
    if (!CALLED_AS_TRIGGER(fcinfo))
        elog(ERROR, "aftertaskcompleted: not called by trigger manager");

    /* tuple to return to executor */
    if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
        rettuple = trigdata->tg_newtuple;
    else
        rettuple = trigdata->tg_trigtuple;

    /* check for null values */
    if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
        && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        checknull = true;

    tupdesc = trigdata->tg_relation->rd_att;

    /* connect to SPI manager */
    if ((ret = SPI_connect()) < 0)
	elog(ERROR, "aftertaskcompleted: SPI_connect returned error %d", ret);

    /* run script */
    FILE *fp = popen("/volume1/video/scripts/scrape.sh", "r");
    if (fp != NULL) {
	/* parse script output */
	char filename[512]; /* one line of output containing one detected filename */
	while (fgets(filename, sizeof(filename)-1, fp) != NULL) {
	    char cmd[1024];
	    if (strlen(filename) > 3) {
		/* remove database entries containing processed filename */
		snprintf(cmd, sizeof(cmd), "DELETE FROM download_queue WHERE status = 5 AND position(filename in '%s') > 0;", filename);
		ret = SPI_exec(cmd, 0);
		if (ret < 0)
		    elog(ERROR, "aftertaskcompleted: SPI_exec returned error %d on query [%s]", ret, cmd);
	    }
	}
    }
    /* end function code */

    /* disconnect from SPI manager */
    SPI_finish();

    if (checknull)
    {
        SPI_getbinval(rettuple, tupdesc, 1, &isnull);
        if (isnull)
            rettuple = NULL;
    }

    return PointerGetDatum(rettuple);
}
--------------------------------------------------

E.g on Ubuntu 18.04 you need to download and install postgresql-server-dev-9.3 + dependencies
(http://security.ubuntu.com/ubuntu/pool/main/p/postgresql-9.3/postgresql-server-dev-9.3_9.3.22-0ubuntu0.14.04_amd64.deb)
Then compile the function in a shared library with:

$ gcc -I /usr/include/postgresql/9.3/server/ -fPIC -c scrape.c
$ gcc -shared -o /volume1/video/scripts/scrape.so scrape.o

STEP 2: Install PSQL trigger:
=============================

Connect to PSQL on Synology, e.g. from terminal with:
$ sudo -u DownloadStation bash -c 'psql -d download'

Paste the following function and trigger code:
--------------------------------------------------
DROP TRIGGER "onComplete" ON download_queue;
DROP FUNCTION aftertaskcompleted();

CREATE OR REPLACE FUNCTION aftertaskcompleted()
  RETURNS trigger
  LANGUAGE c
  SECURITY DEFINER
AS '/volume1/video/scripts/scrape.so';

CREATE TRIGGER "onComplete"
AFTER UPDATE OF status ON download_queue
FOR EACH ROW WHEN (new.status = 5)
EXECUTE PROCEDURE aftertaskcompleted();
--------------------------------------------------

STEP 3: Script to run FileBot and Plex:
=======================================

The PSQL trigger executes the script /volume1/video/scripts/scrape.sh, which runs FileBot and notifies Plex.
You need the Plex server IP and an auth token (see https://support.plex.tv/articles/204059436-finding-an-authentication-token-x-plex-token/)

-------------------- scrape.sh --------------------
#!/bin/sh

PIDFILE='/tmp/scrape.pid'
OUTFILE='/tmp/scrape.out'
# delete PIDFILE and OUTFILE on program exit
trap "rm -f -- '$PIDFILE' '$OUTFILE'" EXIT

# wait for other instance of the program to finish
if [ -f $PIDFILE ]; then
        tail --pid=$(<$PIDFILE) -f /dev/null
fi

# save program pid in PIDFILE
echo $$ > "$PIDFILE"

# run filebot
export PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/syno/sbin:/usr/syno/bin:/usr/local/sbin:/usr/local/bin:/var/packages/Java8/target/j2sdk-image/bin:/var/packages/Java8/target/j2sdk-image/jre/bin:/var/packages/Java8/target/j2sdk-image/bin:/var/packages/Java8/target/j2sdk-image/jre/bin

/usr/local/bin/filebot -script fn:amc --log-file /volume1/video/scripts/amc-log.txt --output "/volume1/video" --action move --conflict override -non-strict --def artwork=n --def clean=y --def music=y -r --def excludeList=/volume1/video/scripts/amc-files.txt --def plex="<IP>:<token>" "/volume1/downloads" > $OUTFILE

# update kodi library
# /usr/bin/curl --data-binary '{ "jsonrpc": "2.0", "method": "VideoLibrary.Scan", "id": "mybash"}' -H 'content-type: application/json;' http://<IP>:<kodi_port>/jsonrpc

# output identified files to console and exit
cat $OUTFILE | grep "\[MOVE\]" | awk  'BEGIN { FS = "From|to" } ; { print $2 }' | tr -d ' \t'

exit 0
--------------------------------------------------