PhilippeTeuwen: /* Exporting pictures & IMDB IDs */

2012-12-26T19:53:43Z

Exporting pictures & IMDB IDs

← Older revision		Revision as of 19:53, 26 December 2012
Line 51:		Line 51:
	To export the DB in an unencrypted SQL dump, press F10.		To export the DB in an unencrypted SQL dump, press F10.
	===Exporting pictures & IMDB IDs===		===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.		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.
	<br>In the previous paragraph you saw how to export the encrypted DB into plain SQL.		<br>In the previous paragraph you saw how to export the encrypted DB into plain SQL.
	<br>Here are very ugly notes (when job is to be done only once I tend to write ugly bash/sed/awk oneliners)		<br>Here are very ugly notes (when job is to be done only once I tend to write ugly bash/sed/awk oneliners)

PhilippeTeuwen: Created page with "[http://www.zappiti.com/ 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 bem…"

2012-12-26T19:53:16Z

Created page with "[http://www.zappiti.com/ 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 bem…"

New page

[http://www.zappiti.com/ 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:
* [http://scottjohnson.org/wiki/Dune/Installing_rsync 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 [http://www.manyfish.co.uk/sitecopy/ 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 [http://www.connectionstrings.com/sqlite 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 [http://osenxpsuite.net/download.htm 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:
<pre> 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</pre>
Get matches between movie internal ID and movie filename (and transcode Windows path to local unix path)
<pre> 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</pre>
Join those two based on movie internal ID
<pre> join database-2.1.2735.100.groupe.small database-2.1.2735.100.media.small > database-2.1.2735.100.small</pre>
Let's see on which entries it could break:
<pre> egrep -v ".(avi|AVI|Avi|mpg|MPG|Mpg|mkv|MKV|Mkv|iso|ISO|Iso|mp4|MP4)\"$" database-2.1.2735.100.small</pre>
Ok we can remove them
<pre> 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</pre>
Recover fanarts
<pre> 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\"")}'</pre>
Recover covers
<pre> 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\"")}'</pre>
Find art not linked to a file (e.g. sets)
<pre> 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")}'</pre>
Some fanart were not flagged as such, find covers with wrong ratio:
<pre> find . -name "*.jpg" -exec jpeginfo -l {} \;|grep -v fanart|awk '{if ($1 > $3) print }'</pre>

Zappiti - Revision history

PhilippeTeuwen: /* Exporting pictures & IMDB IDs */

PhilippeTeuwen: Created page with "[http://www.zappiti.com/ 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 bem…"

PhilippeTeuwen: Created page with "[http://www.zappiti.com/ 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 bem…"