public GameScreen(
Display display, Schema schema, QuestionRegistry questionRegistry, GameConfig config) {
this.config = config;
this.gameState.remaining_time = config.alotted_time;
this.display = display;
this.schema = schema;
this.questionRegistry = questionRegistry;
try {
insertHighscore = schema.createInsert("Highscores", false, "user", "score");
} catch (SQLException e) {
e.printStackTrace();
}
}
private void import_genres(File directory) throws IOException, SQLException {
reader = new ListFileReader(new File(directory, "genres.list"));
reader.skipUntil("^8: THE GENRES LIST\\s*$");
final Pattern linePattern = Pattern.compile("^(.+?)\\t\\s*(.+)$");
HashMap<String, Integer> genresMap = new HashMap<String, Integer>();
Batch batch =
schema.createBatch(
"INSERT IGNORE MovieGenres (movie, genre) VALUES "
+ "((SELECT movie_id FROM Movies WHERE imdb_name = ? LIMIT 1), ?)");
SimpleInsert insertGenre = schema.createInsert("Genres", true, "name");
SimpleQuery findGenre = schema.createQuery("genre_id", "Genres", "name = ?");
// speedup: drop the FKs
Statement stmt = schema.createStatement();
try {
stmt.executeUpdate("ALTER TABLE MovieGenres DROP FOREIGN KEY `mg_genre`");
} catch (SQLException ex) {
}
try {
stmt.executeUpdate("ALTER TABLE MovieGenres DROP FOREIGN KEY `mg_movie`");
} catch (SQLException ex) {
}
stmt.close();
while (true) {
String line = reader.readLine();
if (line == null) {
break;
}
line = line.trim();
if (line.isEmpty()) {
continue;
}
Matcher m = linePattern.matcher(line);
if (!m.matches()) {
continue;
}
String imdb_name = m.group(1);
String genre = m.group(2);
int genre_id;
genre = genre.toLowerCase();
if (genresMap.containsKey(genre)) {
genre_id = genresMap.get(genre);
} else {
genre_id = insertGenre.insert(genre);
if (genre_id < 0) {
genre_id = findGenre.queryGetKey(genre);
}
genresMap.put(genre, genre_id);
}
batch.add(imdb_name, genre_id);
}
insertGenre.close();
findGenre.close();
batch.close();
// remove anomalies and re-add the FKs
debug("re-normalizing...");
stmt = schema.createStatement();
stmt.executeUpdate("DELETE FROM MovieGenres WHERE movie = 0 OR genre = 0");
stmt.executeUpdate(
"ALTER TABLE MovieGenres ADD CONSTRAINT `mg_movie` "
+ "FOREIGN KEY (`movie`) REFERENCES `Movies` (`movie_id`) "
+ "ON DELETE CASCADE ON UPDATE NO ACTION, "
+ "ADD CONSTRAINT `mg_genre` "
+ "FOREIGN KEY (`genre`) REFERENCES `Genres` (`genre_id`) "
+ "ON DELETE CASCADE ON UPDATE NO ACTION");
stmt.close();
reader.close();
}