From c2e8e2ac84366e511919f70fa8525b685497b120 Mon Sep 17 00:00:00 2001
From: Jan-Pascal van Best <janpascal@vanbest.org>
Date: Wed, 4 Apr 2012 22:29:54 +0200
Subject: [PATCH] Make cache database upgrade path prettier (no need to
 manually remove database files) by storing schema version in the database;
 more generic way of dealing with EPGSource classes now works, put class names
 in tv_grab_nl_java.properties in the jar file

---
 .../org/vanbest/xmltv/EPGSourceFactory.java   |  6 +-
 .../org/vanbest/xmltv/ProgrammeCache.java     | 99 +++++++++++++++++--
 2 files changed, 94 insertions(+), 11 deletions(-)

diff --git a/src/main/java/org/vanbest/xmltv/EPGSourceFactory.java b/src/main/java/org/vanbest/xmltv/EPGSourceFactory.java
index 1263e9c..31e8acd 100644
--- a/src/main/java/org/vanbest/xmltv/EPGSourceFactory.java
+++ b/src/main/java/org/vanbest/xmltv/EPGSourceFactory.java
@@ -36,9 +36,9 @@ public class EPGSourceFactory {
 			try {
 				Class<EPGSource> clazz = (Class<EPGSource>) loader.loadClass(name);
 				classes.put(source,  clazz);
-				System.out.println("clazz: " + clazz.toString());
+				// System.out.println("clazz: " + clazz.toString());
 				Field NAME=clazz.getField("NAME");
-				System.out.println("NAME: " + NAME.toString());
+				// System.out.println("NAME: " + NAME.toString());
 				String sourceName=(String)NAME.get(null);
 				names.put(source,sourceName);
 				ids.put(sourceName,source);
@@ -68,7 +68,7 @@ public class EPGSourceFactory {
 	public EPGSource createEPGSource(int source, Config config) {
 		Constructor<EPGSource> constructor;
 		try {
-			constructor = classes.get(source).getConstructor(Integer.class,Config.class);
+			constructor = classes.get(source).getConstructor(Integer.TYPE,Config.class);
 			return constructor.newInstance(source, config);
 		} catch (NoSuchMethodException e) {
 			// TODO Auto-generated catch block
diff --git a/src/main/java/org/vanbest/xmltv/ProgrammeCache.java b/src/main/java/org/vanbest/xmltv/ProgrammeCache.java
index 8a8ae80..0a32bb0 100644
--- a/src/main/java/org/vanbest/xmltv/ProgrammeCache.java
+++ b/src/main/java/org/vanbest/xmltv/ProgrammeCache.java
@@ -46,25 +46,104 @@ public class ProgrammeCache {
 	private PreparedStatement removeStatement;
 	private PreparedStatement clearStatement;
 	
+	private final static Integer SCHEMA_VERSION=1;
+	private final static String SCHEMA_KEY="TV_GRAB_NL_JAVA_SCHEMA_VERSION";
+	
 	public ProgrammeCache(Config config) {
 		this.config = config;
         try {
 			db = DriverManager.getConnection(config.cacheDbHandle, config.cacheDbUser, config.cacheDbPassword);
+			/* Test for upgrade path from legacy database
 			Statement stat = db.createStatement();
-			stat.execute("CREATE TABLE IF NOT EXISTS cache (source INTEGER, id VARCHAR(64), date DATE, programme OTHER, PRIMARY KEY (source,id))");
-			stat.close();
-			
-			getStatement = db.prepareStatement("SELECT programme FROM cache WHERE source=? AND id=?");
-			putStatement = db.prepareStatement("INSERT INTO cache VALUES (?,?,?,?)");
-			removeStatement = db.prepareStatement("DELETE FROM cache WHERE source=? AND id=?");
-			clearStatement = db.prepareStatement("DELETE FROM cache");
+			System.out.println("Dropping old table");
+			stat.execute("DROP TABLE IF EXISTS cache");
+			System.out.println("Creating new table");
+			stat.execute("CREATE CACHED TABLE IF NOT EXISTS cache (id VARCHAR(64) PRIMARY KEY, date DATE, programme OTHER)");
+			*/
 		} catch (SQLException e) {
 			db = null;
 			if (!config.quiet) {
 				System.out.println("Unable to open cache database, proceeding without cache");
 				e.printStackTrace();
 			}
-		}
+        }
+        boolean recreateTable = false;
+        if (db != null) {
+	        try {
+				PreparedStatement stat = db.prepareStatement("SELECT programme FROM cache WHERE source=? AND id=?");
+				stat.setInt(1, 1);
+				stat.setString(2, SCHEMA_KEY);
+				ResultSet result = stat.executeQuery();
+				if (!result.next()) {
+					if (!config.quiet) System.out.println("No schema version found in database");
+					recreateTable=true;
+				} else {
+					Integer currentSchema = (Integer) result.getObject("programme");
+					if (currentSchema<SCHEMA_VERSION) {
+						if (!config.quiet) System.out.println("Current cache database schema version " + currentSchema + " is lower than my version " + SCHEMA_VERSION);
+						recreateTable = true;
+					} else if (currentSchema>SCHEMA_VERSION) {
+						if (!config.quiet) System.out.println("Got a database schema from the future, since my version is " + SCHEMA_VERSION+ " and yours is " + currentSchema);
+						recreateTable = true;
+					}
+					
+				}
+				stat.close();
+			} catch (SQLException e) {
+				if (!config.quiet) {
+					System.out.println("Got SQL exception when trying to find current database schema");
+					System.out.flush();
+					e.printStackTrace();
+					System.out.flush();
+				}
+				recreateTable = true;
+			}
+	        if (recreateTable) {
+	        	if (!config.quiet) System.out.println("Unknown cache schema, removing and recreating cache");
+		        try {
+					Statement stat = db.createStatement();
+					// System.out.println("Dropping old table");
+					stat.execute("DROP TABLE IF EXISTS cache");
+					// System.out.println("Creating new table");
+					stat.execute("CREATE CACHED TABLE IF NOT EXISTS cache (source INTEGER, id VARCHAR(64), date DATE, programme OTHER, PRIMARY KEY (source,id))");
+					stat.close();
+
+					// System.out.println("Writing new schema version to database");
+					PreparedStatement stat2 = db.prepareStatement("INSERT INTO cache VALUES (?,?,?,?)");
+
+					stat2.setInt(1, 1);
+					stat2.setString(2, SCHEMA_KEY);
+					stat2.setDate(3, new java.sql.Date(new java.util.Date(2100,11,31).getTime()));
+					stat2.setObject(4, SCHEMA_VERSION);
+					// System.out.println(stat2.toString());
+					stat2.executeUpdate();
+				} catch (SQLException e) {
+					if (!config.quiet) {
+						System.out.println("Unable to create cache database, proceeding without cache");
+						System.out.flush();
+						e.printStackTrace();
+						System.out.flush();
+					}
+					db = null;
+				}
+			}
+	        try {
+				//System.out.println("Preparing statements");
+				getStatement = db.prepareStatement("SELECT programme FROM cache WHERE source=? AND id=?");
+				putStatement = db.prepareStatement("INSERT INTO cache VALUES (?,?,?,?)");
+				removeStatement = db.prepareStatement("DELETE FROM cache WHERE source=? AND id=?");
+				clearStatement = db.prepareStatement("DELETE FROM cache");
+			} catch (SQLException e) {
+				if (!config.quiet) {
+					System.out.println("Unable to prepare statements, proceeding without cache");
+					System.out.flush();
+					e.printStackTrace();
+					System.out.flush();
+				}
+				db = null;
+	        }
+
+        }
 	}
 	
 	public Programme get(int source, String id) {
@@ -119,6 +198,7 @@ public class ProgrammeCache {
 	}
 
 	public void cleanup() {
+		if (db==null) return;
 		Statement stat;
 		try {
 			stat = db.createStatement();
@@ -134,6 +214,7 @@ public class ProgrammeCache {
 	}
 
 	public void clear() {
+		if (db==null) return;
 		try {
 			int count = clearStatement.executeUpdate();
 			if (!config.quiet && count>0) {
@@ -151,6 +232,8 @@ public class ProgrammeCache {
 			try {
 				getStatement.close();
 				putStatement.close();
+				removeStatement.close();
+				clearStatement.close();
 				db.close();
 			} catch (SQLException e) {
 				// TODO Auto-generated catch block
-- 
2.39.5