====== VideoDB ======

Access, Synchronize & Organize home video collections.

the unix way: many small tools.. working together.


===== Tools Overview =====
The scenario is incremental. The tools can be re-run to add new films or incrementally process additions or changes to the video storage.

//blue//: disk-storage - there can be multiple root folders that contain ''/<Director>/<Title>_(<YEAR>)/'' or  ''/<Director>/<Title>'' subfolders. Each of these movie folders can contain arbitrarily named files. (there are some reserved names (aka. dot-files) for storage of ext information (eg. ''.imdbid'' or ''.preview/'')

//green//: user interface - If you are not happy with the rigid structure and a file-browser. You can access the videodatabase with one of these interfaces.

//yellow//: sql database

<graphviz>
digraph videoScenario {
        graph [overlap=false, splines=true, sep=2];
        node  [fontname=Arial, fontsize=10];
        edge  [fontname=Arial, fontsize=9, fontcolor=blue];

  unsorted -> sorted [label="movemovie (php)\n - lookup at IMDB\l - add .imdbid\l - move files to folder\l"]

  sorted   -> ext_sorted   [label="synchronize\n - copy or merge video\l locations (rsync)\l",
                            arrowtail="normal"]

  sorted -> videoDB [label="vdb update\n updates sqlite database\l - parses folder structure\l - reads dot-files\l"]

  sorted   -> webinterface [label="read\n serve video files\l", arrowhead="dot"]
  sort_prv -> webinterface [label="read\n show .preview/ files\l", arrowhead="dot"]
  webinterface -> sort_prv [label="webadmin:\n - update cover images\l"]

  videoDB -> webinterface [label="query movies", arrowhead="dot"]
  webinterface -> videoDB [label="webadmin:\n - update imdb info\l"]

  videoDB -> vdb [label="query database", arrowhead="dot"]

  sort_pls -> sort_prv [label="genVprev.sh (shell, ffmpeg)\n - create preview flv and thumbnails\l"]
  sorted   -> sort_pls [label="genplaylist.pl (perl)\n - interactively create playlist files\l"]
  sorted   -> sorted   [label="imdb.php (php)\n - interactively aggregate ext\l info from IMDB to dot-files\l"] 


  ext_sorted [label="backup/external Storage\n rigid folder structure\n", 
            fillcolor="#40e0d0", style="filled"]
  webinterface [label="AJAX GUI (php/smarty/sqlite)",
                fillcolor=green, style="filled", shape=box]
  unsorted [label="Folder with unsorted files"]
  sorted   [label="MAIN STATE\n rigid folder structure\n /Director/Movie/\l contains .imdbid", 
            fillcolor=blue, style="filled"]
  sort_pls [label="rigid folder structure\n with .playlist files for each movie\l", 
            fillcolor=blue, style="filled"]
  sort_prv [label="rigid folder structure\n with .preview/ folder\l", 
            fillcolor=blue, style="filled"]
  vdb      [label="vdb - command line interface (c/sqlite)",
                fillcolor=green, style="filled", shape=box]
  videoDB  [label="sqlite3 video database\n provides index", 
            fillcolor=yellow, style="rounded,filled", shape=diamond]
}

</graphviz>

===== Example Folder Structure =====

<code>
Christopher_Nolan/
Christopher_Nolan/.imdbid
Christopher_Nolan/.info
Christopher_Nolan/Insomnia_(2002)
Christopher_Nolan/Insomnia_(2002)/.actors
Christopher_Nolan/Insomnia_(2002)/.imdbid
Christopher_Nolan/Insomnia_(2002)/.plot
Christopher_Nolan/Insomnia_(2002)/.preview
Christopher_Nolan/Insomnia_(2002)/.preview/poster.jpg
Christopher_Nolan/Insomnia_(2002)/.preview/preview_small.flv
Christopher_Nolan/Insomnia_(2002)/.preview/thumb_0002.jpg
Christopher_Nolan/Insomnia_(2002)/.preview/thumb_0003.jpg
Christopher_Nolan/Insomnia_(2002)/.preview/thumb_0004.jpg
Christopher_Nolan/Insomnia_(2002)/.preview/thumb_0005.jpg
Christopher_Nolan/Insomnia_(2002)/.tagline
Christopher_Nolan/Insomnia_(2002)/.titles
Christopher_Nolan/Insomnia_(2002)/.playlist
Christopher_Nolan/Insomnia_(2002)/insomnia1.mpg
Christopher_Nolan/Insomnia_(2002)/insomnia2.mpg
Christopher_Nolan/Memento_(2000)
Christopher_Nolan/Memento_(2000)/.actors
Christopher_Nolan/Memento_(2000)/.imdbid
Christopher_Nolan/Memento_(2000)/.plot
Christopher_Nolan/Memento_(2000)/.preview
Christopher_Nolan/Memento_(2000)/.preview/poster.jpg
Christopher_Nolan/Memento_(2000)/.tagline
Christopher_Nolan/Memento_(2000)/.titles
Christopher_Nolan/Memento_(2000)/.playlist
Christopher_Nolan/Memento_(2000)/memento1.mpg
Christopher_Nolan/Memento_(2000)/memento2.mpg
</code>

===== Info =====
  * there can be multiple roots for the ''SORTED'' rigid folder structure
  * There are no restrictions for filenames in each movie folder (they can contain whitespaces or be arbitray names) - but there are files and folders with special meanings eg ''.preview'' and files ''.imdbid''.   
  * rsync can be used to backup or synchronize (and combine) each root

==== reserved names ====
The sql database is not the authoritative source of information. in fact it can be dynamically rebuilt from meta information on the filesystem. The data is kept in so called dot-files (leading ''.'' in filename) which are usually ASCII or UTF8 text files. In addition to meta data, there is a loose standard to organize derived (''.preview/'') or common supplemental information (eg. ''.pictures/'') in subfolders. 

minimal meta info:
  * ''.imdbid'' - not newline terminated (usually 9byte) IMDB Identifier
  * ''.playlist'' - one filename per line. paths are relative to this folder.

aggregated meta info:
  * ''.actors''
  * ''.titles''
  * ''.tagline''
  * ''.plot''
  * ''.preview/poster.[jpg|png]''

<del>
generated meta data:
  * ''.preview/thumb_0000.jpg'' - up to 1000 thumbnail pictures
  * ''.preview/preview.flv'' - flash video 256kbps
</del> - Those are cached by the web-interface in a dedicated (and distributed) cache.

other:
  * ''.pictures/'' - generic folder for pictures related to this film
  * ''.info''

===== Tools =====

links to come.

  * vdb - c util
  * [[http://rg42.org/gitweb/?p=videoSync.git;a=summary|videoSync]] - C++/QT4 unusual rsync GUI
  * mirvid - php frontend
  * genVprev.sh
  * genPlaylist.pl
  * movemovie.php
  
==== Scripts ====
Handy helper scripts that came to be while bootstaping and developing videodb.

=== merge dot-info ===

merge ''.*'' files from one location (here: ''/home/data/video/ext2/flo-index/video/'' to multiple others (here: ''/home/data/video/*/video/'')

<code>
#!/bin/sh

interactive=0
dryrun=0

cd /home/data/video/ext2/flo-index/video/
cd /home/data/video/ext2/flo-index2/video/


function filmfolders # arg $1: Filmname
{
  find /home/data/video/*/video/ -mindepth 1 -maxdepth 2 -type d -print0 | grep -z "$1" | tr '\000' ' ' || echo "Movie '$1' not fonud." >&2
}

function directorfolders # arg $1: Directorname
{
  find /home/data/video/*/video/ -maxdepth 1 -type d -print0 | grep -z "$1" | tr '\000' ' ' || echo "Director '$1' not fonud." >&2
}

function dofiles # $1: Folder to search
{
  find "$1" -maxdepth 1 -type f -iname ".*" -print0 | tr '\000' ' '  #dotfiles
 #find "$1" -maxdepth 1 -type f -print0 | tr '\000' ' ' # allfiles
}

function debug
{
  ls -l "$1"
  ls -l "$2"
  echo  ">>>>>>>>>>"
  diff "$1" "$2"
  echo  "<<<<<<<<<<"
}

function copydot # $1: src   $2: dst
{
  if [ ! -d "$2" ];then
   echo " BBBBBBBBBBBBBBBUUUUUUUUUUUUUUUUUUUUUUGGGGGGGGGGGGGG?!";
   echo " dir $2 does not exist."
   exit;
  fi
  for file in $(dofiles "$1"); do
    COPY=1

    if [ ! -f "$file" ]; then
      echo -n " Ignored: (spaces in filename?)";
      echo " '$file' is not a file"
      continue;
    fi

    if [ -f "$2/$(basename $file)" ]; then
      diff -q "$1" "$2/$(basename $file)" && COPY=0 || debug "$file" "$2/$(basename $file)"
    fi

    if [ "$interactive" -eq 1 -a $COPY -eq 1 ];then
      echo -n cp -vi "$file" "$2/"
      echo -n " [n]?"
      read -n 1 VAR;
      if [ ! "$VAR" == "y" ]; then COPY=0 ; fi
    fi

    if [ $COPY -eq 1 ]; then
      if [ ! "$dryrun" -eq 1 ];then
        cp -vi "$file" "$2/"
      else
        echo cp -vi "$file" "$2/"
      fi
    fi
  done
}

#######################################################################

# loop over films
if [ 1 -eq 1 ]; then
  for film in $(ls -d */*) ; do
    for dest in $(filmfolders "$film"); do
      copydot "$film" "$dest"
    done
  done
fi

# loop over directors
if [ 1 -eq 1 ]; then
  for director in $(ls -d *) ; do
    for dest in $(directorfolders "$director"); do
      copydot "$director" "$dest"
    done
  done
fi
</code>


==== Command-Lines ====

Find missing playlist files:
  find . -type d -mindepth 2 -maxdepth 2 -exec ls {}/.playlist >/dev/null \;

Check playlist files for validity (if files in playlist exist):
<code>
#!/bin/sh
# check for files in .playlist files.
#
# invoke with:
#  find /home/data/video/1 -iname .playlist -exec /tmp/checkpls.sh "{}" \;
#

PLS=$1

cd `dirname "$PLS"`
cat .playlist | xargs -d "\n" ls &> /dev/null || echo $PLS
</code>

Find/Purge "empty" directories - not counting dot files (ie .imdbid) 
  cd /home/data/video/1
  for file in $(ls); do ls $file | wc -l | grep "^0" >/dev/null && echo $file ; done
  for file in $(ls); do ls $file | wc -l | grep "^0" >/dev/null && rm -rf $file ; done