<commit_before>package model;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

import controller.Controller.QueryType;

/**
 * All db interaction code resides here, could be called directly by view, or by
 * view through intermediary layer of Model. (more about this in QUESTIONS.txt)
 * 
 * This class is a translator between the ER and Object model and vice versa.
 * 
 * 
 */
public final class QueryExecuter implements QueryInterpreter {
	private final static String database = "mediacollection";
	private final static String user = "clientapp";
	private final static String pass = "<PASSWORD>";
	private final static String driver = "com.mysql.jdbc.Driver";
	private final static String config = "?noAccessToProcedureBodies=true";
	private final static String host = "jdbc:mysql://localhost:3306/";

	private Connection connection;
	private Model model;
	private static String lastQuery;
	private static QueryType lastQueryType;

	private synchronized void setLastQuery(String lastQuery, QueryType queryType) {
		QueryExecuter.lastQuery = lastQuery;
		QueryExecuter.lastQueryType = queryType;
	}

	private synchronized String getLastQuery() {
		return QueryExecuter.lastQuery;
	}

	private synchronized QueryType getLastQueryType() {
		return QueryExecuter.lastQueryType;
	}

	public QueryExecuter(Model model) throws SQLException {
		this.model = model;

		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(host + database + config,
					user, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void disconnect() {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
		}
	}

	@Override
	public ArrayList<Album> getAllAlbums(ResultSet rsetAlbum)
			throws SQLException {
		ArrayList<Album> albums = new ArrayList<Album>();

		try {
			// for every album do...
			while (rsetAlbum.next()) {
				ResultSet rsetGenre;
				ResultSet rsetArtist;
				ResultSet rsetRating;
				ResultSet rsetReview;
				ResultSet rsetUser;
				ResultSet rsetId = null;
				Statement stId = connection.createStatement();
				PreparedStatement stUser = connection
						.prepareStatement("select Name from Account where Id = ?;");
				PreparedStatement stGenre = connection
						.prepareStatement("select Name from Genre where Id = ?;");
				PreparedStatement stArtist = connection
						.prepareStatement("select Name from Contributor inner join Creator where Media_Id = ? and Creator.id = Contributor.Creator_Id;");
				PreparedStatement stRating = connection
						.prepareStatement("select avg(Rating) from Rating where Media_Id = ?;");
				PreparedStatement stReview = connection
						.prepareStatement("select Title, Text, Account_Id from Review where Media_Id = ?;");
				Album album = RowConverter.convertRowToAlbum(rsetAlbum);

				stGenre.setInt(1, rsetAlbum.getInt("Genre_Id"));
				rsetGenre = stGenre.executeQuery();

				rsetGenre.first();
				album.setGenre(rsetGenre.getString("Name"));

				stArtist.setInt(1, album.getId());
				rsetArtist = stArtist.executeQuery();

				while (rsetArtist.next())
					album.AddArtist(rsetArtist.getString("Name"));

				// get the rating.
				stRating.setInt(1, album.getId());
				rsetRating = stRating.executeQuery();

				while (rsetRating.next())
					album.setRating(rsetRating.getFloat(1));

				// finally, get reviews..
				stReview.setInt(1, album.getId());
				rsetReview = stReview.executeQuery();

				while (rsetReview.next()) {
					Review review = RowConverter.convertRowToReview(rsetReview);

					stUser.setInt(1, rsetReview.getInt("Account_Id"));
					rsetUser = stUser.executeQuery();
					rsetUser.first();
					review.setUser(rsetUser.getString("Name"));
					album.addReview(review);
				}

				// ops, need user too..
				stUser.setInt(1, rsetAlbum.getInt("Account_Id"));
				rsetUser = stUser.executeQuery();
				rsetUser.first();
				album.setUser(rsetUser.getString("Name"));

				albums.add(album);

				// ha-ha
				listClose(new Statement[] { stId, stUser, stGenre, stArtist,
						stRating, stReview }, new ResultSet[] { rsetGenre,
						rsetArtist, rsetRating, rsetReview, rsetUser, rsetId });
			}
		} finally {
			closeResultSet(rsetAlbum);
		}

		return albums;
	}

	/*** Adds new media to database.
	 * @param name Name/Title of media.
	 * @param year Release Year.
	 * @param genre media genre
	 * @param objects an array of creators (artists/directors/authors)
	 * @param duration length of media. album-total length, movie duration, book pages.
	 * @param mediaType type of media to be added (movie, book, album)
	 */
	@Override
	public void addMedia(String name, String year, String genre,
			Object[] objects, int duration, int mediaType) throws SQLException {
		CallableStatement stMedia = null;
		CallableStatement stCreator = null;
		try {
			connection.setAutoCommit(false);
			String sql = "{call AddMedia(?, ?, ?, ?, ?, ?, ?, ?)}";

			stMedia = connection.prepareCall(sql);
			stMedia.setString(1, model.getUser());
			stMedia.setString(2, model.getPass());
			stMedia.setString(3, name);
			stMedia.setString(4, year);
			stMedia.setString(5, genre);
			stMedia.setInt(6, duration); 
			stMedia.setInt(7, mediaType);
			// returns the PK of the newly added media item, required when adding creators. 
			stMedia.registerOutParameter(8, java.sql.Types.INTEGER);
			stMedia.execute();

			// parameters: username, password, creator name, mediaId
			// the stored procedure will create a creator if not exists.
			sql = "{call AddCreator(?, ?, ?, ?)}";
			stCreator = connection.prepareCall(sql);
			for (int i = 0; i < objects.length; i++) {
				stCreator.setString(1, model.getUser());
				stCreator.setString(2, model.getPass());
				stCreator.setString(3, objects[i].toString());
				stCreator.setInt(4, stMedia.getInt(8));
				stCreator.execute();
			}
			
			connection.commit();
		} finally {
			connection.setAutoCommit(true);
			closeStatement(stMedia);
			closeStatement(stCreator);
		}
		// run last search query again, to display the added media.
		rebootDataSet();
	}

	@Override
	public ArrayList<Album> getAlbumsByAny(String text) throws SQLException {
		Set<Album> album = new HashSet<Album>();

		setLastQuery(text, QueryType.ALBUMSEARCH);
		album.addAll(getAlbumsByUser(text));
		album.addAll(getAlbumsByYear(text));
		album.addAll(getAlbumsByTitle(text));
		album.addAll(getAlbumsByRating(text));
		album.addAll(getAlbumsByArtist(text));
		album.addAll(getAlbumsByGenre(text));<commit_msg>Transaction - AddMedia - RollBack<commit_after>package model;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

import controller.Controller.QueryType;

/**
 * All db interaction code resides here, could be called directly by view, or by
 * view through intermediary layer of Model. (more about this in QUESTIONS.txt)
 * 
 * This class is a translator between the ER and Object model and vice versa.
 * 
 * 
 */
public final class QueryExecuter implements QueryInterpreter {
	private final static String database = "mediacollection";
	private final static String user = "clientapp";
	private final static String pass = "<PASSWORD>";
	private final static String driver = "com.mysql.jdbc.Driver";
	private final static String config = "?noAccessToProcedureBodies=true";
	private final static String host = "jdbc:mysql://localhost:3306/";

	private Connection connection;
	private Model model;
	private static String lastQuery;
	private static QueryType lastQueryType;

	private synchronized void setLastQuery(String lastQuery, QueryType queryType) {
		QueryExecuter.lastQuery = lastQuery;
		QueryExecuter.lastQueryType = queryType;
	}

	private synchronized String getLastQuery() {
		return QueryExecuter.lastQuery;
	}

	private synchronized QueryType getLastQueryType() {
		return QueryExecuter.lastQueryType;
	}

	public QueryExecuter(Model model) throws SQLException {
		this.model = model;

		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(host + database + config,
					user, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void disconnect() {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
		}
	}

	@Override
	public ArrayList<Album> getAllAlbums(ResultSet rsetAlbum)
			throws SQLException {
		ArrayList<Album> albums = new ArrayList<Album>();

		try {
			// for every album do...
			while (rsetAlbum.next()) {
				ResultSet rsetGenre;
				ResultSet rsetArtist;
				ResultSet rsetRating;
				ResultSet rsetReview;
				ResultSet rsetUser;
				ResultSet rsetId = null;
				Statement stId = connection.createStatement();
				PreparedStatement stUser = connection
						.prepareStatement("select Name from Account where Id = ?;");
				PreparedStatement stGenre = connection
						.prepareStatement("select Name from Genre where Id = ?;");
				PreparedStatement stArtist = connection
						.prepareStatement("select Name from Contributor inner join Creator where Media_Id = ? and Creator.id = Contributor.Creator_Id;");
				PreparedStatement stRating = connection
						.prepareStatement("select avg(Rating) from Rating where Media_Id = ?;");
				PreparedStatement stReview = connection
						.prepareStatement("select Title, Text, Account_Id from Review where Media_Id = ?;");
				Album album = RowConverter.convertRowToAlbum(rsetAlbum);

				stGenre.setInt(1, rsetAlbum.getInt("Genre_Id"));
				rsetGenre = stGenre.executeQuery();

				rsetGenre.first();
				album.setGenre(rsetGenre.getString("Name"));

				stArtist.setInt(1, album.getId());
				rsetArtist = stArtist.executeQuery();

				while (rsetArtist.next())
					album.AddArtist(rsetArtist.getString("Name"));

				// get the rating.
				stRating.setInt(1, album.getId());
				rsetRating = stRating.executeQuery();

				while (rsetRating.next())
					album.setRating(rsetRating.getFloat(1));

				// finally, get reviews..
				stReview.setInt(1, album.getId());
				rsetReview = stReview.executeQuery();

				while (rsetReview.next()) {
					Review review = RowConverter.convertRowToReview(rsetReview);

					stUser.setInt(1, rsetReview.getInt("Account_Id"));
					rsetUser = stUser.executeQuery();
					rsetUser.first();
					review.setUser(rsetUser.getString("Name"));
					album.addReview(review);
				}

				// ops, need user too..
				stUser.setInt(1, rsetAlbum.getInt("Account_Id"));
				rsetUser = stUser.executeQuery();
				rsetUser.first();
				album.setUser(rsetUser.getString("Name"));

				albums.add(album);

				// ha-ha
				listClose(new Statement[] { stId, stUser, stGenre, stArtist,
						stRating, stReview }, new ResultSet[] { rsetGenre,
						rsetArtist, rsetRating, rsetReview, rsetUser, rsetId });
			}
		} finally {
			closeResultSet(rsetAlbum);
		}

		return albums;
	}

	/*** Adds new media to database.
	 * @param name Name/Title of media.
	 * @param year Release Year.
	 * @param genre media genre
	 * @param objects an array of creators (artists/directors/authors)
	 * @param duration length of media. album-total length, movie duration, book pages.
	 * @param mediaType type of media to be added (movie, book, album)
	 */
	@Override
	public void addMedia(String name, String year, String genre,
			Object[] objects, int duration, int mediaType) throws SQLException {
		CallableStatement stMedia = null;
		CallableStatement stCreator = null;
		try {
			connection.setAutoCommit(false);
			//parameters: username, password, name, year, genre, duration, type
			String sql = "{call AddMedia(?, ?, ?, ?, ?, ?, ?, ?)}";

			stMedia = connection.prepareCall(sql);
			stMedia.setString(1, model.getUser());
			stMedia.setString(2, model.getPass());
			stMedia.setString(3, name);
			stMedia.setString(4, year);
			stMedia.setString(5, genre);
			stMedia.setInt(6, duration); 
			stMedia.setInt(7, mediaType);
			// returns the PK of the newly added media item, required when adding creators. 
			stMedia.registerOutParameter(8, java.sql.Types.INTEGER);
			stMedia.execute();

			// parameters: username, password, creator name, mediaId
			// the stored procedure will create a creator if not exists.
			sql = "{call AddCreator(?, ?, ?, ?)}";
			stCreator = connection.prepareCall(sql);
			for (int i = 0; i < objects.length; i++) {
				stCreator.setString(1, model.getUser());
				stCreator.setString(2, model.getPass());
				stCreator.setString(3, objects[i].toString());
				stCreator.setInt(4, stMedia.getInt(8));
				stCreator.execute();
			}			
			connection.commit();
		}
		catch (SQLException e)
		{
			connection.rollback();
			throw e;
		}
		 finally {
			connection.setAutoCommit(true);
			closeStatement(stMedia);
			closeStatement(stCreator);
		}
		// run last search query again, to display the added media.
		rebootDataSet();
	}

	@Override
	public ArrayList<Album> getAlbumsByAny(String text) throws SQLException {
		Set<Album> album = new HashSet<Album>();

		setLastQuery(text, QueryType.ALBUMSEARCH);
		album.addAll(getAlbumsByUser(text));
		album.addAll(getAlbumsByYear(text));
		album.addAll(getAlbumsByTitle(text));
		album.addAll(getAlbumsByRating(text));
		album.addAll(getAlbumsByArtist(text));
		album.addAll(getAlbumsByGenre(text));
{'fraction_non_alphanumeric': 0.1175226586102719, 'fraction_numerical': 0.0037764350453172208, 'mean_word_length': 4.013631200302916, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6585727', 'n_tokens_mistral': 4792, 'n_tokens_neox': 4021, 'n_words': 1025}