private void import_directors(File directory) throws IOException, SQLException {
// speedup: drop the FKs
Statement stmt = schema.createStatement();
try {
stmt.executeUpdate("ALTER TABLE MovieDirectors DROP FOREIGN KEY `md_movie`");
} catch (SQLException ex) {
}
try {
stmt.executeUpdate("ALTER TABLE MovieDirectors DROP FOREIGN KEY `md_people`");
} catch (SQLException ex) {
}
stmt.close();
reader = new ListFileReader(new File(directory, "directors.list"));
reader.skipUntil("^\\s*Name\\s+Titles\\s*$");
reader.skipUntil("^\\s*-+\\s+-+\\s*$");
DirectorsImporterHelper helper = new DirectorsImporterHelper();
helper.doImport(null);
helper.close();
reader.close();
// remove anomalies and re-add the FKs
debug("re-normalizing...");
stmt = schema.createStatement();
stmt.executeUpdate("DELETE FROM MovieDirectors WHERE movie = 0 OR director = 0");
stmt.executeUpdate(
"ALTER TABLE MovieDirectors ADD CONSTRAINT `md_movie` "
+ "FOREIGN KEY (`movie`) REFERENCES `Movies` (`movie_id`) ON DELETE CASCADE ON UPDATE NO ACTION, "
+ "ADD CONSTRAINT `md_people` FOREIGN KEY (`director`) REFERENCES `People` "
+ "(`person_id`) ON DELETE CASCADE ON UPDATE NO ACTION");
stmt.close();
}
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();
}