Beispiel #1
0
  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();
  }
Beispiel #2
0
  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();
  }