# JMDB (c) Uwe Freese, Juergen Ulbts
# http://www.jmdb.de/
#
# Changelog:
# ==========
# 2010-03-29 - J.U. - added new file setting
# 2003-06-08 - J.U. - added new macros, settings,...
#
# myVideo list sample
#
# lines starting with # and empty lines are ignored

# ============
# File options
# ============
# NOTE: If FileEncoding is not set the default file system of the operating
#       system is used. If FileEncoding is set this operating system value
#       is overwritten. The Java CharSet values are allowed to be used here
#       ("ISO8859-15", "UTF8",...).
#
FileEncoding = UTF8

# ==============
# Window options
# ==============
# WindowScrollH   should there be a horizontal scrollbar and the table column
#                 be shown in their fixed preferred size or should the columns
#                 always resized so that all columns are visible every time.
#
WindowPosX     =  50
WindowPosY     =  50
WindowWidth    = 900
WindowHeight   = 550
WindowMaximize = yes
WindowScrollH  = yes

# ==================
# Column definitions
# ==================
# The order of the column needs to be the same as the order in
# the *.data file.
#
# NOTE: Column 0 is the IMDb-Title. This one does NOT need to be set because
#       it is added automatically. The IMDb-Title is used to identify the
#       other data and has to be provided in the *.data file as first entry
#       (which is the column id '0').
# 
# Each entry consists of:
#
# NAME;MININALWIDTH;PREFERREDWIDTH;MAXIMANWIDTH;TYPE;BACKGROUNDCOLOR;TEXTCOLOR
#
# with
# NAME    a String that's displayer in the table column header
# MIN     the minimal width in pixels for this column
# PREF    the preferred width in pixels
# MAX     the maximal width in pixels for this column
# TYPE    either "String" or "int", important for the sorting working correctly
# BACKGROUNDCOLOR  the color of the column background,
#                  allowed colors are black, white, red, green, blue, yellow, RRRGGGBBB
# TEXTCOLOR        the color of the text,
#                  allowed colors are black, white, red, green, blue, yellow, RRRGGGBBB
#
# NOTE: You can make invisible columns with MIN=PREF=MAX=0
#
COLUMN = Title             ; 200 ; 200 ; 1500 ; String ; 0xCEFFDE ; black
COLUMN = Year              ;  40 ;  55 ;   75 ; String ; 0xCEDEDE ; black
COLUMN = Rating            ; 100 ; 110 ;  120 ; String ; 0xCEDEDE ; black
COLUMN = Container-Type    ;  70 ;  85 ;   90 ; String ; 0xCEEFDE ; black
COLUMN = Codec             ;  50 ;  55 ;   75 ; String ; 0xCEEFDE ; black
COLUMN = Video-Format      ;  70 ;  75 ;   80 ; String ; 0xCEEFDE ; black
COLUMN = Audio-Languages   ; 100 ; 100 ;  100 ; String ; 0xCEEFDE ; black
COLUMN = Filesize (MB)     ;  80 ;  80 ;  80  ;    int ; 0xCEEFDE ; black
COLUMN = AKA-Title         ; 150 ; 175 ;  250 ; String ; 0xCEDEDE ; black
COLUMN = Writer            ; 125 ; 125 ;  175 ; String ; 0xCEDEDE ; black
COLUMN = Actor             ; 275 ; 325 ;  400 ; String ; 0xCEDEDE ; black
COLUMN = Director          ; 125 ; 125 ;  175 ; String ; 0xCEDEDE ; black
COLUMN = Genre             ;  75 ;  75 ;  100 ; String ; 0xCEDEDE ; black
COLUMN = Certificates      ; 200 ; 230 ;  250 ; String ; 0xCEDEDE ; black
COLUMN = MPAA-Rating       ; 100 ; 200 ;  200 ; String ; 0xCEDEDE ; black
COLUMN = Releasedates      ; 300 ; 380 ;  400 ; String ; 0xCEDEDE ; black
COLUMN = Comment           ; 200 ; 200 ; 1500 ; String ; 0xCEEFDE ; black

# ==============================
# Table/Grid lines between cells
# ==============================
# Here you can define if vertical and/or horizontal lines should be shown
# in the data table.
#
SHOWVERTICALLINES   = yes
SHOWHORIZONTALLINES = yes

# ============================
# Max. number of lines per row
# ============================
# Define the maximum number of lines to be shown per table row. Normally this
# will be automatically calculated over all columns and rows but different
# systems or special system settings might cause a line to be hidden.
# If that happens you might want to change this setting.
# If the maximum number of lines for all rows is for example '5' you can
# enter this value directly.
# 
# Note: The default value ('0') activates autodetection!!!
#
LINESPERROW = 0

# =======
# Sorting
# =======
# You might want to sort the entries by one of the columns you defined above.
# This is possible if you enter the exact name of that column!
# The sorting algorithm uses the default 'ascending' sort. So far this can't
# be changed to a descending sort order. If you need it descending just click
# the column header, change the sort order.
#
SORTCOLUMN = Title

# =======================
# Temporary tables option
# =======================
# Normally a temporary table for movies is created to store the movie data
# as long as the connection lives. The default is: 'SkipTmpMovies = no'
# This temporary table may not be needed if the database supports joins and
# subselects. See 'SQL command definitions' and 'Macro settings' for
# additional information. The table 'tmpmovies' will be used there.
#
# The temporary table is called 'tmpmovies' and has the columns 'title', 'movieid' and 'year'.e = ?
#
SkipTmpMovies = yes

# =======================
# SQL command definitions
# =======================
# Here you define SQL commands that are executed before the macros
# are processed. The SQL commands use temporary tables that are deleted
# automatically after the data has been processed and the table is filled.
#
# NOTE: Beware! For the following section you need some SQL knowledge!
#       It's not that complicated, but you may need to read an online SQL
#       tutorial to fully understand it.
#
# If JMDB detects that you are using SQL queries to fill your table (that is,
# when you use SQLCOMMAND or macro keywords), JMDB automatically creates a
# table 'tmpmovies' with all movie titles and the matching movieid from your
# table the SQLCOMMANDs are executed after this.
# As you may have noticed you can disable the automatic creation of the table
# 'tmpmovies' if you get the required data from SQL-JOINs or SQL-SUBSELECTs.
# Skipping the creation of table 'tmpmovies' might also speed things up, if
# the data is not used at all.
#
#SQLCOMMAND = CREATE TEMPORARY TABLE tmpRatings (title varchar(200) not null, year varchar(25), rank char(4), votes mediumint(8) unsigned, primary key (title))
#SQLCOMMAND = INSERT into tmpRatings SELECT tmpmovies.title, year, rank, votes FROM tmpmovies NATURAL JOIN movies NATURAL JOIN ratings

# HERE THE SQL COMMANDS TO GET THE ACTORS OF THE MOVIE INTO A TEMPORARY TABLE
# THE "UPDATE...rank=..." ARE NEEDED TO GET THE IMPORTANT ONES MARKED, SO THEY
# CAN BE SORTED BY THE KEY "RANK" (only the first 10)
# ==============================================================================
##SQLCOMMAND = INSERT INTO tmpActors (movieid, title, actorid, as_character, rank) SELECT movies2actors.movieid, title, movies2actors.actorid, movies2actors.as_character, '999' FROM tmpmovies NATURAL JOIN movies2actors 
#SQLCOMMAND = CREATE TEMPORARY TABLE tmpActors (title varchar(200) not null, movieid mediumint unsigned not null, actorid mediumint unsigned not null, as_character varchar(1000), rank varchar(6), key actorid(actorid), key movieid(movieid))
#SQLCOMMAND = INSERT INTO tmpActors (movieid, title, actorid, as_character, rank) SELECT movies2actors.movieid, title, movies2actors.actorid, movies2actors.as_character, '999' FROM tmpmovies INNER JOIN movies2actors ON tmpmovies.movieid = movies2actors.movieid
#SQLCOMMAND = UPDATE tmpACTORS SET rank='001' WHERE as_character like '%<1>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='002' WHERE as_character like '%<2>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='003' WHERE as_character like '%<3>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='004' WHERE as_character like '%<4>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='005' WHERE as_character like '%<5>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='006' WHERE as_character like '%<6>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='007' WHERE as_character like '%<7>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='008' WHERE as_character like '%<8>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='009' WHERE as_character like '%<9>'
#SQLCOMMAND = UPDATE tmpACTORS SET rank='010' WHERE as_character like '%<10>'

# HERE THE SQL-COMMANDS TO GET THE DIRECTORS OF THE MOVIE INTO A TEMPORARY TABLE
# ==============================================================================
#SQLCOMMAND = CREATE TEMPORARY TABLE tmpDirectors (title varchar(200) not null, movieid mediumint unsigned not null, directorid mediumint unsigned not null, key directorid(directorid), key movieid(movieid))
#SQLCOMMAND = INSERT INTO tmpDirectors (movieid, title, directorid) SELECT movies2directors.movieid, title, movies2directors.directorid FROM tmpmovies NATURAL JOIN movies2directors

# HERE THE SQL-COMMANDS TO GET THE WRITERS OF THE MOVIE INTO A TEMPORARY TABLE
# ==============================================================================
#SQLCOMMAND = CREATE TEMPORARY TABLE tmpWriters (title varchar(200) not null, movieid mediumint unsigned not null, writerid mediumint unsigned not null, key writerid(writerid), key movieid(movieid))
#SQLCOMMAND = INSERT INTO tmpWriters (movieid, title, writerid) SELECT movies2writers.movieid, title, movies2writers.writerid FROM tmpmovies NATURAL JOIN movies2writers


# ********************************************
# * macros (do not use the names used above) *
# ********************************************
#AKA-TITLES   = SELECT DISTINCT akatitles.title, addition FROM tmpmovies LEFT JOIN akatitles ON tmpmovies.movieid = akatitles.movieid WHERE tmpmovies.title=? AND akatitles.title IS NOT NULL
AKA-TITLES   = SELECT DISTINCT akatitles.title, addition FROM akatitles INNER JOIN movies ON akatitles.movieid = movies.movieid WHERE movies.title=? ORDER BY akatitles.title ASC
# AND akatitles.title IS NOT NULL
#WRITERS      = SELECT DISTINCT name FROM tmpWriters LEFT JOIN writers ON tmpWriters.writerid = writers.writerid WHERE tmpWriters.title=? ORDER BY NAME ASC LIMIT 5
WRITERS      = SELECT DISTINCT writers.name FROM movies INNER JOIN movies2writers ON movies.movieid = movies2writers.movieid INNER JOIN writers ON movies2writers.writerid = writers.writerid WHERE movies.title=? ORDER BY writers.name ASC LIMIT 5
#DIRECTORS    = SELECT name FROM tmpDirectors LEFT JOIN directors ON tmpDirectors.directorid = directors.directorid WHERE tmpDirectors.title=? ORDER BY name ASC LIMIT 5
DIRECTORS    = SELECT directors.name FROM movies INNER JOIN movies2directors ON movies.movieid = movies2directors.movieid INNER JOIN directors ON movies2directors.directorid = directors.directorid WHERE movies.title=? ORDER BY directors.name ASC LIMIT 5
#ACTORS       = SELECT name, as_character FROM tmpActors LEFT JOIN actors ON tmpActors.actorid = actors.actorid WHERE tmpActors.title=? ORDER BY rank ASC LIMIT 5
ACTORS       = SELECT actors.name, movies2actors.as_character FROM actors INNER JOIN movies2actors ON movies2actors.actorid = actors.actorid INNER JOIN movies ON movies2actors.movieid = movies.movieid WHERE movies.title=? LIMIT 5
#ORDER BY rank ASC LIMIT 5
GENRES       = SELECT genre FROM genres INNER JOIN movies ON genres.movieid = movies.movieid WHERE movies.title=? ORDER BY genre ASC LIMIT 5
MPAA-RATE-REASONS = SELECT reasontext FROM mpaaratings INNER JOIN movies ON mpaaratings.movieid = movies.movieid WHERE movies.title=?
#CERTIFICATES = SELECT CONCAT(country, ': ', certification, IFNULL(CONCAT(' - ', addition),'')) FROM certificates NATURAL JOIN movies where title=? ORDER BY country ASC
#CERTIFICATES = SELECT CONCAT(country, ': ', certification, IFNULL(CONCAT(' - ', addition),'')) FROM certificates NATURAL JOIN movies where title=? AND (country='West Germany' OR country='Germany' OR country='UK' OR country='USA') ORDER BY country ASC
#CERTIFICATES = SELECT CONCAT(country, ': ', certification, IFNULL(CONCAT(' - ', addition),'')) FROM certificates NATURAL JOIN movies where title=? AND country IN ('west germany', 'germany', 'uk', 'usa') ORDER BY country ASC
#CERTIFICATES = SELECT CONCAT(country, ': ', certification, IFNULL(CONCAT(' - ', addition),'')) FROM certificates NATURAL JOIN movies where title=? AND country IN ('west germany', 'germany', 'uk', 'usa') AND (addition NOT LIKE '%(cut)%' OR addition NOT LIKE '(cut%' OR addition IS NULL) ORDER BY country ASC
#CERTIFICATES = SELECT CONCAT(REPLACE(country, 'West Germany', 'Germany'), ': ', certification, IFNULL(CONCAT(' - ', addition),'')) FROM certificates INNER JOIN movies ON certificates.movieid = movies.movieid WHERE movies.title=? AND LOWER(country) IN ('west germany', 'germany', 'uk', 'usa') AND (LOWER(addition) NOT LIKE '%(cut)%' OR LOWER(addition) NOT LIKE '(cut%' OR addition IS NULL) ORDER BY certificates.country ASC
CERTIFICATES = SELECT CONCAT(REPLACE(country, 'West Germany', 'Germany'), ': ', certification, COALESCE(' - '||addition,'') ) FROM certificates INNER JOIN movies ON certificates.movieid = movies.movieid WHERE movies.title=? AND LOWER(country) IN ('west germany', 'germany', 'uk', 'usa') AND (LOWER(addition) NOT LIKE '%(cut)%' OR LOWER(addition) NOT LIKE '(cut%' OR addition IS NULL) ORDER BY certificates.country ASC
#RELEASEDATES = SELECT CONCAT(country, ': ', imdbdate, IFNULL(CONCAT(' - ', addition),'')) FROM releasedates NATURAL JOIN movies WHERE title=? ORDER BY country ASC
#RELEASEDATES = SELECT CONCAT(REPLACE(country, 'West Germany', 'Germany'), ': ', imdbdate, IFNULL(CONCAT(' - ', addition),'')) FROM movies INNER JOIN releasedates ON movies.movieid = releasedates.movieid WHERE movies.title=? ORDER BY releasedates.country ASC
RELEASEDATES = SELECT CONCAT(REPLACE(country, 'West Germany', 'Germany'), ': ', imdbdate, COALESCE(' - '||addition,'') ) FROM movies INNER JOIN releasedates ON movies.movieid = releasedates.movieid WHERE movies.title=? ORDER BY releasedates.country ASC
RATING       = SELECT CONCAT(rank, ' (',votes,' Votes)') FROM ratings INNER JOIN movies ON ratings.movieid = movies.movieid WHERE movies.title=?
YEAR         = SELECT year FROM movies WHERE title=?
