/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
 
/**
 * This is a helper class to access the database
 * that comes with the book samples.
 */
 
public abstract class DatabaseConnection {
 
    /** our connection to the db. */
    protected Connection connection;
    
    /**
     * Closes the connection to the database.
     */
    public void close() throws SQLException {
        connection.close();
    }
 
    /**
     * Creates a statement.
     * @return    a statement
     * @throws SQLException 
     */
    public Statement createStatement() throws SQLException {
        return connection.createStatement();
    }
 
    /**
     * Creates a prepated statement using a query.
     * @param    query    the query that will be used to create
     * a prepared statement.
     * @return    a statement
     * @throws SQLException 
     */
    public PreparedStatement createPreparedStatement(String query)
        throws SQLException {
        return connection.prepareStatement(query);
    }
    
    /**
     * Performs an update in the database.
     * @param    expression    an SQL expression
     * (CREATE, DROP, INSERT, UPDATE)
     */
    public void update(String expression) throws SQLException {
        Statement st = createStatement();
        int i = st.executeUpdate(expression);
        st.close();
        if (i == -1) {
            throw new SQLException("db error : " + expression);
        }
    }
}

/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
/**
 * This is a helper class to access an hsqldb database.
 * The database scripts are supposed to be present in directory:
 * resources/db
 */
public class HsqldbConnection extends DatabaseConnection {
 
    /**
     * Creates the connection.
     * @param db_file_name_prefix the database name,
     * which is the prefix of the database file
     * @throws SQLException 
     */
    public HsqldbConnection(String db_file_name_prefix)
        throws SQLException {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException e) {
            throw new SQLException("HSQLDB database driver not found");
        }
        connection = DriverManager.getConnection(
            "jdbc:hsqldb:resources/db/" + db_file_name_prefix, "SA", "");
    }
    
    /**
     * Shuts down the database and closes the connection.
     */
    public void close() throws SQLException {
        Statement st = createStatement();
        st.execute("SHUTDOWN");
        super.close();
    }
}

/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
 
 
/**
 * Creates the hsqldatabase using SQL scripts containing
 * CREATE statements with the table definitions, and
 * INSERT statements with the data.
 */
public class CreateHsqldbTables {
 
    /**
     * Imports a number of SQL scripts into an HSQLDB database.
     * @param    args    no arguments needed
     * @throws SQLException 
     * @throws IOException 
     * @throws UnsupportedEncodingException 
     */
    public static void main(String[] args)
        throws SQLException, UnsupportedEncodingException, IOException {
        DatabaseConnection conn = new HsqldbConnection("filmfestival");
        BufferedReader in;
        String line;
        conn.update("SET IGNORECASE TRUE");
        in = new BufferedReader(
                new FileReader("resources/scripts/filmfestival_hsqldb.sql"));
        while( (line = in.readLine()) != null) {
            conn.update(line);
        }
        in = new BufferedReader(
                new FileReader("resources/scripts/filmfestival.sql"));
        while( (line = in.readLine()) != null) {
            conn.update(line);
        }
        conn.close();
    }
}

/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
 
/**
 * This is a helper class to access a mySql database.
 * The username and password of the database are supposed to be present
 * in a properties file mysql.props (in the working directory)
 */
public class MySqlConnection extends DatabaseConnection {
 
    /**
     * Creates the connection.
     * @param db_file_name_prefix the database name,
     * which is the prefix of the database file
     * @throws SQLException 
     */
    public MySqlConnection(String database) throws SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new SQLException("mySql database driver not found");
        }
        Properties credentials = new Properties();
        try {
            credentials.load(new FileInputStream("mysql.props"));
        } catch (IOException e) {
            throw new SQLException("Can't read mysql.props.");
        }
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost/" + database,
                credentials.getProperty("username"),
                credentials.getProperty("password"));
    }
 
}

/*
 * ###########################################################################
 * SQLite is fast, low overhead, and so easy to use...
 * ###########################################################################
 * System.Data.SQLite:
 * http://sqlite.phxsoftware.com/
 * 
 * [1] drop the System.Data.SQLite.dll into ~/bin
 * [2] copy iTextInAction2Ed.db3 database file to ~/app_data
 * 
 * database is READ-ONLY (under web context) unless configured otherwise
 * ###########################################################################
 * System.Data.SQLite is the ADO.NET 2.0/3.5 provider for the
 * SQLite database engine:
 * http://www.sqlite.org/
 * ###########################################################################
 * 
 * !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 * if you want to use a different provider see the 
 * README.txt file. the change requires minimumal effort.
 * !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Web.Configuration;
 
namespace kuujinbo.iTextInAction2Ed.Intro_1_2 {
  public class AdoDB {
// ===========================================================================
    public static readonly string CS;
    public static readonly DbProviderFactory Provider;
    static AdoDB() {
      CS = WebConfigurationManager.ConnectionStrings["iTextEx"] != null
// web context        
          ? WebConfigurationManager.ConnectionStrings["iTextEx"]
              .ConnectionString
/*
 * command-line, change to use SQL Server/other ADO.NET data provider
 */
          : string.Format("Data Source={0};", new Uri(
              new Uri(Utility.BaseDirectory), 
              "./app_data/iTextInAction2Ed.db3"
            ).LocalPath
          );
/* 
 * ADO.NET data provider
 * to use SQL Server ADO.NET data provider replace below with:
: "System.Data.SqlClient"
 * 
*/          
      Provider = DbProviderFactories.GetFactory("System.Data.SQLite");   
    } 
// ---------------------------------------------------------------------------
    /** SQL statement to get all the movies of the festival. */
    public const String MOVIES =
@"SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration, 
e.year, c.name, c.keyword, c.color 
FROM film_movietitle m, festival_entry e, festival_category c 
  WHERE m.id = e.film_id AND e.category_id = c.id 
ORDER BY m.title";
// ---------------------------------------------------------------------------
    /** SQL statement to get the directors of a specific movie. */
    public const String DIRECTORS =
@"SELECT d.name, d.given_name 
FROM film_director d, film_movie_director md 
  WHERE md.film_id = @film_id AND md.director_id = d.id";
// ---------------------------------------------------------------------------  
    /** SQL statement to get the movies of a specific director. */
    public const String MOVIEDIRECTORS =
@"SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration 
FROM film_movietitle m, film_movie_director md 
  WHERE md.director_id = @director_id AND md.film_id = m.id
ORDER BY m.title";
// ---------------------------------------------------------------------------
    /** SQL statement to get the countries of a specific movie. */
    public const String COUNTRIES =
@"SELECT c.country 
FROM film_country c, film_movie_country mc 
  WHERE mc.film_id = @film_id AND mc.country_id = c.id";
// ---------------------------------------------------------------------------  
    /** SQL statement to get the movies from a specific country. */
    public const String MOVIECOUNTRIES =
@"SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration
FROM film_movietitle m, film_movie_country mc 
  WHERE mc.country_id = @country_id AND mc.film_id = m.id 
ORDER BY m.title";
// ---------------------------------------------------------------------------
    /** SQL statement to get all the days of the festival. */
    public const String DAYS =
        "SELECT DISTINCT day FROM festival_screening ORDER BY day";
// ---------------------------------------------------------------------------        
    /** SQL statament to get all the locations at the festival */
    public const String LOCATIONS =
        "SELECT DISTINCT location FROM festival_screening ORDER by location";
// ---------------------------------------------------------------------------        
    /** SQL statement to get screenings. */
    public const String SCREENINGS =
@"SELECT m.title, m.original_title, m.imdb, m.year, m.duration,
s.day, s.time, s.location, s.press, 
e.year, c.name, c.keyword, c.color, m.id 
FROM festival_screening s, film_movietitle m, 
festival_entry e, festival_category c 
WHERE day = @day AND s.film_id = m.id 
AND m.id = e.film_id AND e.category_id = c.id";
// ---------------------------------------------------------------------------
    /** SQL statement to get screenings. */
    public const String MOVIESCREENINGS =
@"SELECT s.day, s.time, s.location, s.press 
FROM festival_screening s 
WHERE s.film_id = @film_id";
// ---------------------------------------------------------------------------
    /** SQL statement to get screenings. */
    public const String PRESS =
@"SELECT m.title, m.original_title, m.imdb, m.year, m.duration,
s.day, s.time, s.location, s.press,
e.year, c.name, c.keyword, c.color, m.id 
FROM festival_screening s, film_movietitle m, 
festival_entry e, festival_category c 
WHERE s.press=1 AND s.film_id = m.id 
AND m.id = e.film_id AND e.category_id = c.id 
ORDER BY day, time ASC";
// ===========================================================================  
  }
}