Export Synology Video Station metadata (vsmeta) to .nfo files

Synology’s Video Station software is a great tool to organize and serve multimedia content, e.g, to DLNA clients. Sadly, Synology uses a proprietary format to save the metadata in .vsmeta files, with no further documentation available. So, directly re-using that metadata with different software requires additional tools, for example tinyMediaManager. Alternatively, it’s possible to export the metadata to a different format, e.g. .nfo files that can be used by other media server software like Emby, Serviio or Kodi.

Video Station doesn’t provide such an export feature, so access to the database itself is required. With the database accessible, only a few additional steps are required. Since .nfo files are essentially XML, it’s sufficient to export the SQL data as XML and transform it into the .nfo file structure.

Doing this requires some knowledge of SQL and XML/XSLT; the following steps were tested only on a Linux Mint system, they should work on any other current, debian-based Linux system.

Overview

  1. Export VideoStation metadata to a db dump, import it into a local PostgreSQL instance;
  2. Export the SQL metadata to XML (script for psql described below);
  3. Transform the resulting XML into the .nfo structure and write single .nfo files for each db record (XSLT described below).

Details

1. Prepare SQL data – export from DS into a local PostgreSQL db

First, i’s required (!) to export the complete metadata. Reason: PostgreSQL on DSM 6.x was compiled without libxml support, so the query to export the SQL data as XML won’t work when executed directly on the Synology box. To do so, follow the steps to export the Video Station database as described in the Synology forums „Migrate Video Station Database“ , import it into a local PostgreSQL instance (e.g.

pg_restore -U YOUR_PG_USERNAME -c -d video_metadata /PATH/TO/videostation.dump

), and proceed with the following steps using that local data.

2. Export SQL to XML

In short:

Explanation

To convert the metadata to XML, you can use the built-in XML features of PostgreSQL, especially the query_to_xml() function. Writing the result to a XML file should be done using psql and the \copy command. Since this will add ugly \n characters in the output, rendering the resulting XML not well-formed, you’ll have to remove those \n characters first. On a Linux system, this can easily be done using sed.

Putting all this together may result in the following \copy command to execute with psql. This will export the „home_video“ files with separate fields for the id, genre (concatenated), summary and filename, to a single, big xml file. To add more fields, e.g. actor, additional JOIN clauses would be required. The output file name and path is defined in the last line:

(yes, it’s „gnere“… – and please note: There are no double quotes – it’s single quotes, twice to mask the quotes inside the first argument of the query_to_xml() function…)

The SQL query result will also contain the path to the media files. This is useful since it eases putting the resulting .nfo files in the corresponding media directories. Most metadata scrapers expect the .nfo files in the same directory as the media itself.

3. Transform and „tokenize“ .xml to .nfo

In short:

  • grab the XSLT file https://gist.github.com/tohuuuuu/11fb13a5697e377161de11b5a5fe02e6 (see below for details) and save it as /tmp/transform_vsmeta.xslt
  • run the transformation: java -cp /usr/share/java/saxonb.jar net.sf.saxon.Transform -ext:on videodata.xml transform_vsmeta.xslt

Explanation

We need a XSLT engine that’s capable to handle the xsl:result-document element, so it has to be able to process a XSLT 2 stylesheet. The following stylesheet will take the output of our SQL-to-XML Query, writing each single „row“ element to a single .nfo file. It takes the filename of the video file as base of the .nfo file name, creating a file structure according to the structure of the Video Station video library. This is useful since if a directory contains more than one video file, the related .nfo file has to be named the same way as the video file (expect the file type extension) and it has to be placed in the same directory as the video file.

To apply that style sheet to the XML data exported from Synology, the following command should do the trick on a Linux box with Saxon-B installed. It expects both the XML and the XML in the current dir. The variable outpathprefix sets the output root directory.

Finally, just copy the resulting .nfo files into the appropriate media directories. Now, Emby or Serviio should use that metadata when scraping the media libraries.

Remote access to Serviio DB

Serviio is a simply great DLNA server, supporting a lot of DLNA clients out-of-the-box. Since Serviio saves the metadata in an embedded (!) Apache Derby DB, it’s easy to access it using a JDBC-enabled DB client, for example SquirrelSQL (of course, on its own risk – damaging the DB will result in unexpected behavior and potential loss of data… SQL knowledge is mandatory…).

Please notice: Don’t try to modify the metadata of the media library by editing the Derby DB. Your modifications won’t be persistent, because Serviio will update the DB on the next metadata scraping based on nfo files (or other metadata sources defined using the control panel). So, the correct way to feed your own metadata to Serviio’s db is to provide, e.g, nfo files. I’ve described in a different blog entry how to create nfo files from vsmeta (Synology Video Station) metadata.

To access the Serviio DB remotely, the following steps should work (i assume serviio runs on a Debian Stretch box):

    1. Stop the serviio service, otherwise you won’t be able to access the DB remotely.
    2. Install derbytools (providing the required jars to run a derby server).
    3. Check the version of derbytools. This is important because a version mismatch between server and client may lead to strange errors. To do so:
      • Set the classpath:
        export CLASSPATH=/usr/share/java/derbytools.jar:/usr/share/java/derbyclient.jar
      • Run the derby cli:
        java org.apache.derby.tools.ij
      • First line of ij output will be something like "IJ-Version 10.13" – notice the 10.13.
      • Exist ij with
        QUIT;
    4. Start a Derby Server in Serviio’s library root. It’s important that the server process has write access to the db files, so it should be run as user serviio. Depending on the server os, the path to derbynet.jar may be different.
      • cd /opt/serviio/library
      • sudo -u serviio java -jar /usr/share/java/derbynet.jar start -h 0.0.0.0 &
    5. Try to connect locally:
        • Run the derby cli:
          java org.apache.derby.tools.ij
        • Connect to db:
          connect 'jdbc:derby://localhost:1527/db';
        • Display some data:
          SHOW SCHEMAS;
        • Exit ij:
          QUIT;
    6. If this works, before trying to connect remotely, prepare the client so it’s equipped with the same Derby version as the server. Double-check by repeating step 3 (above) on the client.
    7. Now, try to connect from the client. Repeat step 5 on the client, replacing localhost with the server name or ip.
    8. If you’re done working with the client, as last steps:
      • stop the derby server:
        sudo -u serviio java -jar /usr/share/java/derbynet.jar stop -h 0.0.0.0 &
      • and restart the serviio process again.

Apache Derby: connecting remote server fails – java.lang.NoSuchMethodError: java.nio.ByteBuffer.clear()Ljava/nio/ByteBuffer

Trying to connect to a remote Apache Derby server. Derby server version is 10.13.1.1 (Debian Stretch), java version on remote system is openjdk 1.8.0_181.

Thanks to Lars Vogel, i’ve learned i have to run the derby server with -h 0.0.0.0 so to accept both local and remote connections. Connecting locally works fine.

Remote access (both with ij or tools like SquirrelSQL or LibreOffice Base) fails with a strange Java error:

NoSuchMethodError: java.nio.ByteBuffer.clear()Ljava/nio/ByteBuffer

Finally i found that it’s a matter of different derby versions. Client version of derby was 10.14.1.0. With version 10.13.1.1 on client, too, the error disappeared…

MyFritz! App 2 und FritzBox 6360 Cable

Frust nach Handywechsel: Die auf dem neuen Gerät (Android-Version 8.0.0) installierte MyFritz! App 2 (Version: 2.11.3 (14227) BETA) will sich partout nicht mit meiner FritzBox  6360 Cable verbinden. Nach der Eingabe des Passwortes folgt die Aufforderung, die Fritzbox durch Ziehen des Netzsteckers neu zu starten. Nach einem Neustart erscheint dieselbe Meldung erneut. Bestätigt man sie, erscheint der Hinweis:

„Fehler bei der Einrichtung“.

Im Protokoll des Anmeldeversuchs (verfügbar als Attachment der Mail, wenn man auf „Feedback geben“ oder so ähnlich klickt) findet sich mehrfach die Fehlermeldung:

„de.avm.efa.api.exceptions.SoapException: AppID is too long“.

Grummel!

Leider kann (oder will?) auch der freundliche AVM-Support nicht weiterhelfen: Es handele sich um einen seltenen Fehler bei Boxen mit FritzOs kleiner 6.69 (meine hat 6.52, kein Update verfügbar…). Für die 6360 gibt es keinen persönlichen Support mehr…

Dann klappt es doch:

  1. In die FritzBox-Einstellungen -> Heimnetz -> Netzwerkübersicht -> Heimnetzeinstellungen.
  2. Dort unter „Heimnetzfreigaben“ das Häkchen bei „Zugriff für Anwendungen zulassen“ entfernen (!!!),
  3. auf Übernehmen klicken,
  4. Neustart per Steckerziehen;
  5. dann nochmals in dieselben Einstellungen, Häkchen erneut setzen,
  6. Neustart wie beschrieben.

Wenn die FritzBox wieder da ist: WLAN-Verbindung vom Handy, FritzApp! 2 starten, Passwort -> Verbindung wird hergestellt (oder so ähnlich, war zu sehr damit beschäftigt, mir ein Loch in den Strumpf zu freuen) – alles läuft wie gewünscht.

Moneyplex auf Linux Mint 64bit – Port 443

Moneyplex ist eine der wenigen Homebanking-Anwendungen, die nativ unter Linux laufen. Um Moneyplex auf einem aktuellen Linux Mint 64bit-System zu starten, müssen u.a. folgende Pakete installiert werden:

  • libatk-adaptor:i386
  • libgail-common:i386
  • libssl1.0.0:i386

Letzte Abhängigkeit ist besonders wichtig, da Moneyplex anderenfalls den Port 443 nicht nutzen kann, der aber für das PIN/TAN-Verfahren notwendig ist. Wenn also beim Test der Internetverbindung die Ports 80 und 3000 ohne Probleme genutzt werden können, nicht aber Port 443, sollte man prüfen, ob libssl1.0.0:i386 installiert ist.

Java / jarsigner: Un-sign / re-sign jar files

Re-signing jar files is a pain if there are old signatures to remove. jarsigner happily would add the new signature, rendering the jar invalid if the old signatures are still present. There’s no command line switch to tell jarsigner to remove the old signatures first. So, one has to unpack each jar file first, remove the META-INF/*.SF and *.RSA files, and zip the jar again… NO! The linux version of zip comes with a nice command line option „-d“ to remove files from a zip. So, just execute the following command to remove any existing signatures from a jar file (thanks martinhans!):

zip -d file.jar 'META-INF/*.SF' 'META-INF/*.RSA'

To iterate over a whole bunch of jar files, just embed that command into a bash for loop:

for i in *.jar; do zip -d "$i" 'META-INF/*.SF' 'META-INF/*.RSA';done

In fact, you’re done 🙂

For re-signing mutiple jars: jarsigner is able to read the keystore / key password from a file or from an environment variable, using the -keypass / -storepass command line option together with the :file or the :env modifier.

So, it’s possible to put each of the passwords in a file and use a for loop like this to sign all jars in the current directory using the key key_alias:

for i in ./*.jar; do jarsigner -storepass:file ~/.storepass -keypass:file ~/.keypass "$i" key_alias;done

To make jarsigner read the passwords from an env variable, you’ll have to create those variables first:

Windows 10: Startmenü öffnet sich nicht, Info-Center ebenfalls nicht…

Microsoft lässt seine Nutzerschaft mal wieder im Regen stehen, wenn das im Titel beschriebene Problem auftaucht. Den PowerShell-Befehl zur Reparatur findet man nur in diversen Foren, etwa hier und hier:

Get-AppxPackage | % { Add-AppxPackage -DisableDevelopmentMode -Register "$($_.InstallLocation)\AppxManifest.xml" -verbose }

Dann sollten Startmenü und Infocenter wieder erscheinen und funktionieren.