Zappiti

Zappiti is done by HD Land for their Dune HD players.
There is an ipad version too, for Android see below for a compatible app.
The only bemol is that it's a Windows-only program (as all the other alternatives I think) so I used it in a Virtualbox with Windows 7.
I had issues with v2.3.1094.0 so I kept using v2.1.2735.100
Note that since v2.3.1097 HD Land is restricting its program for French people who didn't buy their Dune HD via HD Land. For others there is a possibility to get licensing key if they prove they live and bought their product out of France. It was really time to look for alternatives...

Config

Some notes on my config (just for myself to remember)

Dossier de configuration: F:\@zappiti\@db  (F: virtualbox shared /media/MyBook2/@dune)
Dossier de partage: F:\@DuneHDD-mirror ou Z:\   (Z: \\dune\DuneHDD)
Nouveau dossier films:
  F:\@DuneHDD-mirror\films               
  Réécrire en: storage_label://DuneHDD/films

Zappiti works on a local copy of my movies collection and links are rewritten when exported to the Dune HD as the movies are also present locally on its HDD

Exported index

The exported index (_Zappiti/ and Zappiti_Resources/) has then to be copied on the Dune HD and the Android tablet for a faster rendering
Options:

• Install Rsync on Dune HD

Probably the best option. Very easy to install (cf link above) and to use. It's a patched version that creates modules for each mounted resource
To get modules names:

$ rsync dune::
DuneHDD        	Dune storage named DuneHDD

To push Zappiti data:

$ rsync -av --delete ...DuneHDD-mirror/_Zappiti          dune::DuneHDD
$ rsync -av --delete ...DuneHDD-mirror/Zappiti_Resources dune::DuneHDD

• via ftp, to be tested with sitecopy

Note that if you try to copy Zappiti indexes from a locally attached drive via the Dune interface you can easily reach the maximum of 9999 files in _Zappiti. Better to initiate the copy via a telnet session.

Using rsync to push indexes to the Android tablet (rooted, using SSHDroidPro & rsync binary):

$ rsync -av --delete ...DuneHDD-mirror/_Zappiti          root@tablet:/sdcard/Zappiti/
$ rsync -av --delete ...DuneHDD-mirror/Zappiti_Resources root@tablet:/sdcard/Zappiti/

Mangling database

If you want to move or rename files or folders, Zappiti wants to scrap them again and disregard the old entries.
To avoid that, you can edit directly the sqlite database.
Just a little problem: the database is encrypted by using ASP .NET connectionstrings (cf possible implementation [Using http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki here]).
So we'll follow [ http://www.mpcclub.com/forum/archive/index.php/t-27073.html this howto]:
Open

C:\Program Files (x86)\HDLand\Zappiti\Zappiti.exe.config
<connectionStrings>
   <add name="Entities" connectionString="metadata=res://*/Models.Models.csdl|res://*/Models.Models.ssdl|res://*/Models.Models.msl;provider=System.Data.SQLite;provider connection string="data source=D:\Works\NETil\Works\Companies\HDLand\DuneMediaManager_b1\DuneMediaManager\Data\database.db;password=BLABLA"" providerName="System.Data.EntityClient" />
 </connectionStrings>

Here password is BLABLA.
Get Sqlite2009 Pro Enterprise Manager, again a Windows stuff but no much option to use this .NET encryption (sth with RSA but still using a symmetric password, no idea what this means)

- Open database
- select RSA & provide password

Then you can make your SQL queries, e.g.

UPDATE 'Media' set Path=REPLACE(Path, 'F:\', 'F:\@DuneHDD-mirror\');
UPDATE 'TVShowFolder' set Folder=REPLACE(Folder, 'F:\', 'F:\@DuneHDD-mirror\')

To export the DB in an unencrypted SQL dump, press F10.

Exporting pictures & IMDB IDs

After having discovered YAMJ, I decided to move away from Zappiti. Not that Zappiti is bad but having to run it under Windows 7 under VirtualBox and to access remotely all movies in and data out is a real pain.
In the previous paragraph you saw how to export the encrypted DB into plain SQL.
Here are very ugly notes (when job is to be done only once I tend to write ugly bash/sed/awk oneliners)
To recover matches between movie names and IMDB IDs:

cat database-2.1.2735.100.sql |recode latin1..utf8|egrep '(<name>|<imdb_id>)' |sed 's/^.*<name>//;s/<\/name>//'|awk '/^[^[:space:]]/{title=$0}/<imdb_id>/{imdb=$0; print title imdb}'|sort|uniq > imdblist

To recover anonymized images from Pictures/ (which are all Jpeg BTW)
Get matches between movie internal ID and fanart ID and cover ID:

 egrep -i "insert into groupe values" database-2.1.2735.100.sql|recode latin1..utf8 > database-2.1.2735.100.groupe.sql
 awk -F, '{sub(/INSERT INTO Groupe VALUES\(/,"",$1); print $1, $4, $5}' database-2.1.2735.100.groupe.sql|sort > database-2.1.2735.100.groupe.small

Get matches between movie internal ID and movie filename (and transcode Windows path to local unix path)

 egrep -i "insert into media values" database-2.1.2735.100.sql|recode latin1..utf8 > database-2.1.2735.100.media.sql
 awk -F, '{sub(/INSERT INTO Media VALUES\(/,"",$1);id=$1;sub(/.*(.)F:\\@DuneHDD-mirror/,"\"");sub(/. (NULL|[^A-Za-z0-9][A-Z][A-Z]).*/,"\"");gsub(/\\/,"/"); print id,$1}' database-2.1.2735.100.media.sql |sed "s/''/'/g"|sort > database-2.1.2735.100.media.small

Join those two based on movie internal ID

 join database-2.1.2735.100.groupe.small database-2.1.2735.100.media.small > database-2.1.2735.100.small

Let's see on which entries it could break:

 egrep -v ".(avi|AVI|Avi|mpg|MPG|Mpg|mkv|MKV|Mkv|iso|ISO|Iso|mp4|MP4)\"$" database-2.1.2735.100.small

Ok we can remove them

 egrep ".(avi|AVI|Avi|mpg|MPG|Mpg|mkv|MKV|Mkv|iso|ISO|Iso|mp4|MP4)\"$" database-2.1.2735.100.small > database-2.1.2735.100.small2

Recover fanarts

 grep -v "^[^[:space:]]\+ NULL" database-2.1.2735.100.small2|awk '{id=$2;gsub(/[^[:alnum:]]/,"",id); $1="";$2="";$3="";sub(/[[:space:]]*"/,"\".");sub(/.(avi|AVI|Avi|mpg|MPG|Mpg|mkv|MKV|Mkv|iso|ISO|Iso|mp4|MP4)\"$/,"");system("mkdir -p \"$(dirname "$0"\")\"; cp Pictures/" id " " $0 ".fanart.jpg\"")}'

Recover covers

 grep -v "^[^[:space:]]\+ [^[:space:]]\+ NULL" database-2.1.2735.100.small2|awk '{id=$3;gsub(/[^[:alnum:]]/,"",id); $1="";$2="";$3="";sub(/[[:space:]]*"/,"\".");sub(/.(avi|AVI|Avi|mpg|MPG|Mpg|mkv|MKV|Mkv|iso|ISO|Iso|mp4|MP4)\"$/,"");system("mkdir -p \"$(dirname "$0"\")\"; cp Pictures/" id " " $0 ".jpg\"")}'

Find art not linked to a file (e.g. sets)

 join -a 1 database-2.1.2735.100.groupe.small database-2.1.2735.100.media.small > database-2.1.2735.100.smallext
 cat database-2.1.2735.100.small database-2.1.2735.100.smallext |sort|uniq -u > database-2.1.2735.100.groupe.small.uniq
 mkdir others
 cat database-2.1.2735.100.groupe.small.uniq |awk '{gsub(/[^[:alnum:]]/,"",$2);gsub(/[^[:alnum:]]/,"",$3);if ($2!="NULL") print "cp Pictures/"$2" others/"$2".jpg";if ($3!="NULL") system("cp Pictures/"$3" others/"$3".jpg")}'

Some fanart were not flagged as such, find covers with wrong ratio:

 find . -name "*.jpg" -exec jpeginfo -l {} \;|grep -v fanart|awk '{if ($1 > $3) print }'