package de.ugoe.cs.swe.memos.database;

import java.io.InputStream;
import java.io.OutputStream;
import java.nio.channels.Channels;
import java.nio.channels.WritableByteChannel;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Vector;

import org.eclipse.swt.SWT;
import org.eclipse.swt.widgets.MessageBox;
import org.eclipse.swt.widgets.Shell;

import de.ugoe.cs.swe.memos.SettingsManager;
import de.ugoe.cs.swe.memos.Utils;
import de.ugoe.cs.swe.memos.datamodel.Category;
import de.ugoe.cs.swe.memos.datamodel.File;
import de.ugoe.cs.swe.memos.datamodel.Memo;
import de.ugoe.cs.swe.memos.datamodel.StreamCopy;
import de.ugoe.cs.swe.memos.datamodel.Tag;

public class DBUtility {

	private Connection connection;
	private static DBUtility instance;

	// MS: Das soll kein Singleton sein!!!
	public static DBUtility getInstance() {
		instance = new DBUtility();
		return instance;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	private DBUtility() {
		connection = Connector.getConnection();
	}

	public Vector<Category> queryCategories() throws DatabaseException {
		Vector<Category> tbr = new Vector<Category>();

		try {
			Statement statement = this.connection.createStatement();
			ResultSet results = statement
					.executeQuery("SELECT * from Categories");

			while (results.next()) {
				Category newCat = new Category(results.getString(3));
				newCat.setiD(results.getLong(1));
				newCat.setParentID(results.getLong(2));
				tbr.add(newCat);
			}

		} catch (Exception e) {
			Utils
					.showError("Fehler beim Abrufen der Kategorien aus der Datenbank.");
			throw new DatabaseException(e.getMessage());
		}

		Iterator<Category> catIt = tbr.iterator();

		while (catIt.hasNext()) {
			Category current = catIt.next();

			for (int i = 0; i < tbr.size(); i++) {
				if (tbr.elementAt(i).getiD().equals(current.getParentID()))
					current.setParentCategory(tbr.elementAt(i));
				else if (tbr.elementAt(i).getParentID().equals(current.getiD()))
					current.getChildCategories().add(tbr.elementAt(i));
			}

		}

		return tbr;
	}

	public ArrayList<Memo> getMemosByCategory(Category currentCategory)
			throws DatabaseException {

		ArrayList<Memo> result = new ArrayList<Memo>();
		PreparedStatement statement;

		try {

			statement = connection
					.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
							+ " m.lockedby, m.author, m.draft, m.lockedat from Memos m "
							+ " where m.category=?;");

			statement.setLong(1, currentCategory.getiD());
			ResultSet results = statement.executeQuery();

			while (results.next()) {
				Memo newMemo = new Memo(results.getString(2), results
						.getDate(4), results.getInt(8) == 1);
				newMemo.setAuthor(results.getString(7));
				newMemo.setCategoryID(results.getLong(5));
				newMemo.setContent(results.getString(3));
				newMemo.setLock(results.getString(6));
				newMemo.setiD(results.getLong(1));
				newMemo.setCategory(currentCategory);
				result.add(newMemo);
			}
			statement.close();

		} catch (SQLException e) {
			Utils.showError("Fehler beim Laden von Memos aus der Datenbank.");
			return new ArrayList<Memo>();
		}

		for (Memo m : result)
			m.loadTags();

		return result;
	}

	public ArrayList<Tag> getTagsByMemo(Memo memo) throws DatabaseException {

		ArrayList<Tag> result = new ArrayList<Tag>();
		PreparedStatement statement;

		try {

			statement = connection.prepareStatement("select t.id, t.tag from"
					+ " Tagging t" + " where t.memo=?;");

			statement.setLong(1, memo.getiD());
			ResultSet results = statement.executeQuery();

			while (results.next()) {
				Tag newTag = new Tag(results.getString(2));
				newTag.setiD(results.getLong(1));
				newTag.setMemo(memo);
				result.add(newTag);
			}
			statement.close();

		} catch (SQLException e) {
			Utils.showError("Fehler beim Laden von Tags aus der Datenbank.");
			return new ArrayList<Tag>();
		}

		return result;
	}

	public Vector<Tag> queryTags() throws DatabaseException {

		Vector<Tag> tags = new Vector<Tag>();

		try {
			Statement statement = this.connection.createStatement();
			ResultSet results = statement.executeQuery("SELECT * from Tagging");

			while (results.next()) {
				Tag newTag = new Tag(results.getString(2));
				tags.add(newTag);
			}

		} catch (Exception e) {
			Utils.showError("Fehler beim Laden von Tags aus der Datenbank.");
			throw new DatabaseException(e.getMessage());
		}

		return tags;
	}

	public Memo getMemoByID(long id) {
		Memo memo = new Memo("", false);
		memo.setiD(id);
		updateMemoFromDB(memo);
		return memo;
	}

	public void updateMemoFromDB(Memo memo) {
		try {
			Statement statement = this.connection.createStatement();
			ResultSet results = statement
					.executeQuery("SELECT * from Memos WHERE ID ="
							+ memo.getiD());

			if(results.next())
			{

				// Memo newMemo = new Memo(results.getString(2), results.getDate(4),
				// results.getInt(8) == 1);
				memo.setTitle(results.getString(2));
				memo.setTimestamp(results.getDate(4));
				memo.setDraft(results.getBoolean(8));
				memo.setAuthor(results.getString(7));
				memo.setCategoryID(results.getLong(5));
				memo.setContent(results.getString(3));
				memo.setLock(results.getString(6));
				memo.setiD(results.getLong(1));
				memo.loadTags();
			}
			else
			{
				memo.setiD(null);
			}
		} catch (Exception e) {
			Utils.showError("Fehler beim Laden eines Memos aus der Datenbank.");
		}
	}

	public Vector<Memo> queryMemos() throws DatabaseException {

		HashMap<Long, Memo> tbr = new HashMap<Long, Memo>();
		Vector<Tag> tags = new Vector<Tag>();

		try {
			Statement statement = this.connection.createStatement();
			ResultSet results = statement.executeQuery("SELECT * from Memos");

			while (results.next()) {
				Memo newMemo = new Memo(results.getString(2), results
						.getDate(4), results.getInt(8) == 1);
				newMemo.setAuthor(results.getString(7));
				newMemo.setCategoryID(results.getLong(5));
				newMemo.setContent(results.getString(3));
				newMemo.setLock(results.getString(6));
				newMemo.setiD(results.getLong(1));
				tbr.put(newMemo.getiD(), newMemo);
			}

			results = statement.executeQuery("SELECT * from Tagging");

			while (results.next()) {
				Tag newTag = new Tag(results.getString(2), tbr.get(results
						.getLong(3)));
				newTag.setMemoID(results.getLong(3));
				newTag.setiD(results.getLong(1));
				tags.add(newTag);
			}

		} catch (Exception e) {
			Utils.showError("Fehler beim Laden von Memos aus der Datenbank.");
			throw new DatabaseException(e.getMessage());
		}

		Vector<Memo> memos = new Vector<Memo>();
		Iterator<Long> i = tbr.keySet().iterator();
		while (i.hasNext())
			memos.add(tbr.get(i.next()));
		return memos;
	}

	public void addChildrenToCategory(Category parent) throws DatabaseException {

		String postfix;

		if (parent.getiD()== null || parent.getiD() == 0)
			postfix = " IS NULL;";
		else
			postfix = "=" + parent.getiD();

		Category newChild;
		ResultSet results;
		Statement statement;

		try {
			statement = this.connection.createStatement();
			results = statement
					.executeQuery("SELECT * FROM Categories WHERE Parent"
							.concat(postfix));

			parent.clearAllChildren();

			while (results.next()) {
				newChild = new Category(results.getString(3));
				newChild.setiD(results.getLong(1));
				newChild.setParentID(results.getLong(2));
				newChild.setParentCategory(parent);
				parent.addChild(newChild);
			}
		} catch (Exception e) {
			Utils.showError("Fehler beim Laden von "
					+ "Kategorien aus der Datenbank.");
			throw new DatabaseException(e.getMessage());
		}
	}

	public boolean insertSingleCategory(Category c) {
		PreparedStatement statement;

		try {
			if (c.getParentCategory() == null || c.getParentCategory().getiD()==null) {
				statement = connection.prepareStatement(
						"INSERT INTO Categories (Name) VALUES (?)",
						Statement.RETURN_GENERATED_KEYS);
				statement.setString(1, c.getName());
			} else {
				statement = connection.prepareStatement(
						"INSERT INTO Categories (Name, Parent) VALUES (?, ?)",
						Statement.RETURN_GENERATED_KEYS);
				statement.setString(1, c.getName());
				statement.setLong(2, c.getParentCategory().getiD());
			}
			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			c.setiD(rs.getLong(1));
			statement.close();

		} catch (SQLException e) {
			Utils
					.showError("Fehler beim Hinzufügen einer Kategorie in die Datenbank.");
			return false;
		}
		return true;
	}

	public boolean insertSingleTag(Tag t) {
		PreparedStatement statement;

		try {

			statement = connection.prepareStatement(
					"INSERT INTO Tagging (tag,memo) Values(?,?)",
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, t.getName());
			statement.setLong(2, t.getMemo().getiD());

			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			t.setiD(rs.getLong(1));
			statement.close();

		} catch (SQLException e) {
			Utils
					.showError("Fehler beim Hinzufügen eines Tags in die Datenbank.");
			return false;
		}
		return true;
	}

	public boolean insertSingleFile(File f, InputStream inputStream, long size) {
		PreparedStatement statement;
		boolean HSQL = SettingsManager.getInstance().getDBType() == SettingsManager.DatabaseType.HSQL;

		try {

			statement = connection.prepareStatement(
					"INSERT INTO Files (name,memo,content) Values(?,?,?)",
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, f.name);
			statement.setLong(2, f.memo.getiD());
			
			if (HSQL)
				statement.setBinaryStream(3, inputStream, size);
			else
				statement.setBlob(3, inputStream);
			System.out.println(statement.toString());
			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			f.id = rs.getLong(1);
			statement.close();

		} catch (SQLException e) {
			e.printStackTrace();
			Utils.showError("Fehler beim Hinzufügen einer Datei in die Datenbank.");
			return false;
		}
		return true;
	}

	public boolean deleteFilesByMemo(Memo memo) {
		boolean result = true;
		for (File f : memo.getFiles())
			result = result && deleteFileByFileID(f.id);
		return result;
	}

	public boolean deleteFileByFileID(long ID) {
		PreparedStatement statement;
		try {
			statement = connection
					.prepareStatement("DELETE FROM Files WHERE ID=?");
			statement.setLong(1, ID);
			statement.execute();
			return true;
		} catch (SQLException e) {
			Utils
					.showError("Fehler beim Löschen einer Datei aus der Datenbank.");
			return false;
		}
	}

	public boolean getFileContent(File f, OutputStream outputStream) {
		PreparedStatement statement;

		try {

			statement = connection.prepareStatement(
					"select f.content from Files f where f.id=?",
					Statement.RETURN_GENERATED_KEYS);
			statement.setLong(1, f.id);

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				Blob b = rs.getBlob(1);

				try {
					// get an channel from the stream
					final java.nio.channels.ReadableByteChannel inputChannel = Channels
							.newChannel(b.getBinaryStream());
					final WritableByteChannel outputChannel = Channels
							.newChannel(outputStream);
					// copy the channels
					StreamCopy.fastChannelCopy(inputChannel, outputChannel);
					// closing the channels
					inputChannel.close();
					outputChannel.close();
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			statement.close();

		} catch (SQLException e) {
			Utils.showError("Fehler beim Laden einer Datei aus der Datenbank.");
			return false;
		}
		return true;
	}

	public boolean lockMemo(Memo toBeLocked) throws DatabaseException {

		// lock timeout in minutes. get from setting manager after implement
		long timeout = SettingsManager.getInstance().getLockTimeout();
		boolean tbr = false;
		boolean NonHSQL = SettingsManager.getInstance().getDBType() != SettingsManager.DatabaseType.HSQL;

		Long memoID = toBeLocked.getiD();
		String user = SettingsManager.getInstance().getUsername();

		ResultSet results;
		Statement statement;
		PreparedStatement prep;

		try {
			if (NonHSQL)
				connection.setAutoCommit(false);

			statement = this.connection.createStatement();
			results = statement
					.executeQuery("SELECT ID, LockedBy, LockedAt FROM Memos WHERE ID="
							+ memoID + (NonHSQL ? " FOR UPDATE;" : ";"));

			while (results.next()) {
				if (results.getLong(1) == memoID) {
					long dateDiff = 0;

					String lockedBy = results.getString(2);
					java.util.Date now = new java.util.Date();
					Timestamp lockedAt = results.getTimestamp(3);

					if (lockedAt != null) {
						dateDiff = now.getTime() - lockedAt.getTime();
						dateDiff = dateDiff / (60 * 1000);
					}

					// Utils.getMainView().getActiveComposite().getShell()
					MessageBox lockAnyway = new MessageBox(new Shell(),
							SWT.ICON_QUESTION | SWT.YES | SWT.NO);
					if (lockedAt != null) {
						lockAnyway
								.setMessage("Die gewählte Notiz wird zur Zeit bereits berarbeitet.\n\nBenutzer:\t"
										+ lockedBy
										+ "\nUhrzeit:\t\t"
										+ lockedAt.toString()
										+ "\n\nWollen Sie die Notiz für sich selbst sperren?");
						lockAnyway.setText("Gesperrte Notiz");
					}

					if (dateDiff > timeout || lockedBy == null
							|| lockedBy.equals(user)
							|| lockAnyway.open() == SWT.YES) {
						prep = connection.prepareStatement("UPDATE Memos"
								+ " SET LockedBy=?, LockedAt=? WHERE ID=?");
						prep.setString(1, user);
						prep.setTimestamp(2, new Timestamp(now.getTime()));
						prep.setLong(3, memoID);

						toBeLocked.setLock(user);
						toBeLocked.setLockedAt(now);
						toBeLocked.setAuthor(user);

						prep.executeUpdate();
						tbr = true;
					} else {
						toBeLocked.setLock(lockedBy);
						toBeLocked.setLockedAt(lockedAt);
					}
				}
			}
			if (NonHSQL)
				connection.commit();

		} catch (Exception e) {
			Utils
					.showError("Fehler beim Sperren eines Memos in der Datenbank.");
			throw new DatabaseException(e.getMessage());
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return tbr;
	}

	public void updateLockDataInMemo(Memo memo) throws DatabaseException,
			SQLException {
		ResultSet results;
		PreparedStatement statement;

		statement = connection
				.prepareStatement("SELECT LockedBy, LockedAt FROM Memos WHERE ID=?");
		statement.setLong(1, memo.getiD());
		results = statement.executeQuery();
		if (!results.next()) {
			throw new DatabaseException("Memo with ID " + memo.getiD()
					+ " does not exist any longer. Please dispose.");
		} else {
			memo.setLock(results.getString(1));
			memo.setLockedAt(results.getTimestamp(2));
		}

	}

	public boolean unlockMemo(Memo toBeUnlocked) throws DatabaseException {

		boolean tbr = false;

		boolean NonHSQL = SettingsManager.getInstance().getDBType() != SettingsManager.DatabaseType.HSQL;

		Long memoID = toBeUnlocked.getiD();
		String user = SettingsManager.getInstance().getUsername();

		ResultSet results;
		Statement statement;
		PreparedStatement prep;

		try {

			if (NonHSQL)
				connection.setAutoCommit(false);

			statement = this.connection.createStatement();
			results = statement
					.executeQuery("SELECT ID, LockedBy FROM Memos WHERE ID="
							+ memoID + (NonHSQL ? " FOR UPDATE;" : ";"));

			while (results.next()) {
				if (results.getLong(1) == memoID
						&& results.getString(2).equals(user)) {

					prep = connection.prepareStatement("UPDATE Memos"
							+ " SET LockedBy=NULL, LockedAt=NULL WHERE ID=?");
					prep.setLong(1, memoID);
					prep.executeUpdate();
					toBeUnlocked.setLock(null);
					toBeUnlocked.setLockedAt(null);
					tbr = true;
				} else
					throw new DatabaseException("Can not happen");
			}
			if (NonHSQL)
				connection.commit();
		} catch (Exception e) {
			Utils
					.showError("Fehler beim Entsperren eines Memos in der Datenbank.");
			throw new DatabaseException(e.getMessage());
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return tbr;
	}

	public boolean insertMemo(Memo m) {
		PreparedStatement statement;

		try {
			if (m.getCategoryID() == null) {
				statement = connection
						.prepareStatement(
								"INSERT INTO Memos (Title,content, lockedBy, Author, Draft) VALUES (?,?,?,?,?);",
								Statement.RETURN_GENERATED_KEYS);

				statement.setString(1, m.getTitle());
				statement.setString(2, m.getContent());
				statement.setString(3, m.getLock());
				statement.setString(4, SettingsManager.getInstance()
						.getUsername());
				statement.setBoolean(5, m.isDraft());
				// System.out.println(statement);
			} else {
				statement = connection
						.prepareStatement(
								"INSERT INTO Memos (Title,content,lockedBy,Author,Draft,Category) Values(?,?,?,?,?,?);",
								Statement.RETURN_GENERATED_KEYS);

				statement.setString(1, m.getTitle());
				statement.setString(2, m.getContent());
				statement.setString(3, m.getLock());
				statement.setString(4, SettingsManager.getInstance()
						.getUsername());
				statement.setBoolean(5, m.isDraft());
				if (m.getCategoryID() == 0)
					statement.setNull(6, java.sql.Types.NULL);
				else
					statement.setLong(6, m.getCategoryID());
				// System.out.println(statement);
			}
			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			m.setiD(rs.getLong(1));
			statement.close();

			for (Tag t : m.getTags())
				if (!this.insertSingleTag(t))
					return false;

		} catch (SQLException e) {
			Utils
					.showError("Beim Einfügen eines Memos ist ein Fehler aufgetreten.");
			// System.err.println("Error occurred while trying to insert a memo!");
			// e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean updateMemoInDatabase(Memo m) {
		PreparedStatement statement;

		try {
			if (m.getCategoryID() == null) {
				statement = connection.prepareStatement(
						"UPDATE Memos SET Title=?, Content=?, LockedBy=?, "
								+ " Author=?, Draft=? WHERE id=?;",
						Statement.RETURN_GENERATED_KEYS);

				statement.setString(1, m.getTitle());
				statement.setString(2, m.getContent());
				statement.setString(3, m.getLock());
				statement.setString(4, SettingsManager.getInstance()
						.getUsername());
				statement.setBoolean(5, m.isDraft());
				statement.setLong(6, m.getiD());
			} else {
				statement = connection.prepareStatement(
						"UPDATE Memos SET Title=?, Content=? , LockedBy=?,"
								+ " Author=?, Draft=?, Category=? WHERE id=?;",
						Statement.RETURN_GENERATED_KEYS);

				statement.setString(1, m.getTitle());
				statement.setString(2, m.getContent());
				statement.setString(3, m.getLock());
				statement.setString(4, SettingsManager.getInstance()
						.getUsername());
				statement.setBoolean(5, m.isDraft());
				if (m.getCategoryID() == 0)
					statement.setNull(6, java.sql.Types.NULL);
				else
					statement.setLong(6, m.getCategoryID());
				statement.setLong(7, m.getiD());
			}
			statement.executeUpdate();

			for (Tag t : m.getTags())
				if (t.getiD() == null)
					if (!this.insertSingleTag(t))
						return false;

		} catch (SQLException e) {
			Utils
					.showError("Beim Aktualisieren eines Memos ist ein Fehler aufgetreten.");
			// System.err.println("Error occurred while trying to insert a memo!");
			// e.printStackTrace();
			return false;
		}
		return true;
	}

	private boolean performdDeleteCategoryAndSubCategories(Category c)
			throws SQLException {
		c.loadChildrenFromDB();

		for (Category c2 : c.getChildCategories()) {
			performdDeleteCategoryAndSubCategories(c2);
		}

		PreparedStatement statement2 = connection
				.prepareStatement("Delete From Categories where id=?;");

		statement2.setLong(1, c.getiD());
		int rowCount = statement2.executeUpdate();
		statement2.close();

		if (rowCount != 1) {
			throw new SQLException();
		}

		return true;
	}

	public boolean deleteCategoryAndSubCategories(Category c)
			throws DatabaseException {
		try {
			connection.setAutoCommit(false);
			performdDeleteCategoryAndSubCategories(c);
			connection.commit();

		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				try {
					connection.setAutoCommit(true);

				} catch (SQLException e2) {

				}

			}
			Utils.showError("Die Kategorie "
					+ (c != null ? "\"" + c.getName() + "\"" : "")
					+ " konnte nicht entfernt werden.");
			// e.printStackTrace();
			throw new DatabaseException("Beim Delete Fehler");
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {

			}

		}

		return true;
	}

	public boolean deleteCategory(Category c) throws DatabaseException {
		PreparedStatement statement;

		try {
			connection.setAutoCommit(false);
			statement = connection
					.prepareStatement("Update Categories set Parent=? where id=?;");
			for (Category c2 : c.getChildCategories()) {
				if (c.getParentID() == null || c.getParentID() == 0) {
					statement.setNull(1, java.sql.Types.NULL);
				} else {
					statement.setLong(1, c.getParentID());
				}

				statement.setLong(2, c2.getiD());

				statement.executeUpdate();
			}
			statement.close();
			PreparedStatement statement2 = connection
					.prepareStatement("Delete From Categories where id=?;");

			statement2.setLong(1, c.getiD());

			int rowCount = statement2.executeUpdate();
			statement2.close();

			if (rowCount != 1) {
				connection.rollback();
				throw new DatabaseException("Delete Failed");
			}
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				try {
					connection.setAutoCommit(true);

				} catch (SQLException e2) {

				}

			}
			Utils
					.showError("Beim Löschen einer Kategorie ist ein Fehler aufgetreten.");
			throw new DatabaseException("Error while deleting category");
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {

			}

		}

		return true;
	}

	public boolean deleteMemo(Memo m) throws DatabaseException {
		PreparedStatement statement;

		try {

			if (this.lockMemo(m)) {
				statement = connection
						.prepareStatement("Delete From Memos where id=?;");

				statement.setLong(1, m.getiD());

				int rowCount = statement.executeUpdate();
				statement.close();

				if (rowCount != 1) {
					Utils
					.showError("Beim Löschen eines Memos ist ein Fehler aufgetreten.");
					throw new DatabaseException("Delete Failed");
				}
				m.setiD(null);
			} else
				return false;
		} catch (SQLException e) {
			Utils
			.showError("Beim Löschen eines Memos ist ein Fehler aufgetreten.");
			return false;
		}
		return true;
	}

	public boolean updateCategory(Category c) throws DatabaseException {
		PreparedStatement statement;

		try {

			statement = connection
					.prepareStatement("update Categories set name=? where id=?;");

			statement.setString(1, c.getName());
			statement.setLong(2, c.getiD());

			int rowCount = statement.executeUpdate();
			statement.close();

			if (rowCount != 1)
				throw new DatabaseException("Delete Failed");

		} catch (SQLException e) {
			Utils
			.showError("Beim Aktualisieren einer Kategorie in der Datenbank ist ein Fehler aufgetreten.");
			return false;
		}
		return true;
	}

	public boolean checkSSLConnection() {
		PreparedStatement statement;

		try {

			statement = connection
					.prepareStatement("show status like 'Ssl_cipher';");

			ResultSet rs = statement.executeQuery();
			rs.next();

			boolean flag = rs.getString("Value").equals("DHE-RSA-AES128-SHA");
			statement.close();
			return flag;

		} catch (Exception e) {
			Utils
			.showError("Beim Prüfen der SSL Verbindung ist ein Fehler aufgetreten.");
			e.printStackTrace();
			return false;
		}
	}

	public ArrayList<File> getFilesByMemo(Memo memo) throws DatabaseException {

		ArrayList<File> result = new ArrayList<File>();
		PreparedStatement statement;

		try {

			statement = connection.prepareStatement("select f.name, f.id from"
					+ " Files f" + " where f.memo=?;");

			statement.setLong(1, memo.getiD());
			ResultSet results = statement.executeQuery();

			while (results.next()) {
				File newFile = new File();
				newFile.name = results.getString(1);
				newFile.id = results.getLong(2);
				newFile.memo = memo;
				result.add(newFile);
			}
			statement.close();

		} catch (SQLException e) {
			Utils
			.showError("Beim Lesen der Liste der Dateianhänge ist ein Fehler aufgetreten.");
			return new ArrayList<File>();
		}

		return result;
	}

}