/**
 * Copyright (C) 2005 David Lopes , Fernando Branquinho
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation; either version 2 of the License, or (at your option) any later
 * version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc., 59 Temple
 * Place - Suite 330, Boston, MA 02111-1307, USA.
 * 
 * Contacts perolaproject@gmail.com
 * 
 */

package perola.core.drivers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Set;
import java.util.TreeSet;

import perola.core.PerolaDriver;

/**
 * <b>Projeto: Perola</b><br>
 * 
 * @author Fernando Branquinho
 * 
 * Criado em : 25/10/2004
 * 
 * @version 0.9.0<br>
 *          <br>
 */
public final class DriverPerolaForJDBC extends PerolaDriver {

	public static final String ODBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
	
	public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	
	public static final String POSTGRESQL_DRIVER = "org.postgresql.Driver";
	
	public static final String SQLITE_DRIVER = "org.sqlite.JDBC";
	
	public static final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	
	public static final String FIREBIRD_DRIVER = "org.firebirdsql.jdbc.FBDriver";
	
	public static final String INTERBASE_DRIVER = "interbase.interclient.Driver";
	
	public static final String INFORMIX_DRIVER = "com.informix.jdbc.IfxDriver";
	
	public static final String DB2_DRIVER = "com.ibm.db2.jcc.DB2Driver";
	
	public static final String MS_SQL_SERVER_AND_SYBASE_JTDS_DRIVER = "net.sourceforge.jtds.jdbc.Driver";
	
	public static final String MS_SQL_SERVER_OFICIAL_DRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	
	public static final String SYBASE_OFICIAL_DRIVER = "com.sybase.jdbc.SybDriver";
	
	public static final String HSQLDB_DRIVER = "org.hsqldb.jdbcDriver";
	
	private String DBURL, USER, PASSWORD, DRIVER;

	private Connection conn = null;

	public DriverPerolaForJDBC(String url, String user, String password, String driver) {
		DBURL = url;
		USER = user;
		PASSWORD = password;
		DRIVER = driver;
	}
	
	public DriverPerolaForJDBC(String url, String user, String password) {
		this(url, user, password, ODBC_DRIVER);
	}
	
	public DriverPerolaForJDBC(String url, String driver) {
		this(url, "", "", driver);
	}
	
	public DriverPerolaForJDBC(String url) {
		this(url, "", "", ODBC_DRIVER);
	}
	
	private PreparedStatement createConnectionAndPrepareCommand(String comando) throws SQLException{
		System.setProperty("jdbc.drivers", DRIVER);
		conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
		return conn.prepareStatement(comando);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see perola.core.PerolaDriver#getRoot()
	 */
	@Override
	public String getRoot() throws SQLException {
		return readObject("root");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see perola.core.PerolaDriver#existRoot()
	 */
	@Override
	public boolean existRoot() {
		boolean retorno = false;
		try {
			PreparedStatement stmt = createConnectionAndPrepareCommand("SELECT * FROM Objects WHERE uid = 'root'");
			ResultSet rs = stmt.executeQuery();
			retorno = rs.next();
			stmt.close();
			conn.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return retorno;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see perola.core.PerolaDriver#saveRoot(java.lang.String)
	 */
	@Override
	public void saveRoot(String nomeArquivo) throws SQLException {
		PreparedStatement stmt = createConnectionAndPrepareCommand("INSERT INTO Objects (uid,contents) VALUES ('root','" + nomeArquivo + "')");
		stmt.executeUpdate();
		stmt.close();
		conn.close();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see perola.core.PerolaDriver#saveObject(java.lang.String,
	 *      java.lang.String)
	 */
	
	@Override
	public void saveObject(String nomeArquivo, String conteudo) throws SQLException {
		saveObject(nomeArquivo, conteudo, true);
	}
	
	private void saveObject(String nomeArquivo, String conteudo, boolean primeiraTentativa) throws SQLException {
		try {
			PreparedStatement stmt = createConnectionAndPrepareCommand("SELECT * FROM Objects WHERE uid = '" + nomeArquivo + "'");
			ResultSet rs = stmt.executeQuery();
			String comando;
			if (rs.next()) comando = "UPDATE Objects SET contents='" + conteudo + "' WHERE uid = '" + nomeArquivo + "'";
			else comando = "INSERT INTO Objects (uid,contents) VALUES ('" + nomeArquivo + "','" + conteudo + "')";
			stmt.executeUpdate(comando);
			stmt.close();
			conn.close();
		}
		catch (SQLException e) {
			if (primeiraTentativa) {
				if (!conn.isClosed()) conn.close();
				String varchar, text;
				if (System.getProperty("jdbc.drivers").equals(DriverPerolaForJDBC.ORACLE_DRIVER)){
					varchar = "VARCHAR2(25)";
					text = "LONG";
				} else if (System.getProperty("jdbc.drivers").equals(DriverPerolaForJDBC.DB2_DRIVER)){
					varchar = "VARCHAR(25)";
					text = "LONG VARCHAR";
				} else if (System.getProperty("jdbc.drivers").equals(DriverPerolaForJDBC.HSQLDB_DRIVER)){
					varchar = "VARCHAR(25)";
					text = "LONGVARCHAR";
				} else if (System.getProperty("jdbc.drivers").equals(DriverPerolaForJDBC.INFORMIX_DRIVER)){
					varchar = "CHARACTER VARYING(25,4)";
					text = "LVARCHAR";
				} else if (System.getProperty("jdbc.drivers").equals(DriverPerolaForJDBC.FIREBIRD_DRIVER) ||
						System.getProperty("jdbc.drivers").equals(DriverPerolaForJDBC.INTERBASE_DRIVER)){
					varchar = "VARCHAR(25)";
					text = "BLOB SUB_TYPE 1";
				} else if (System.getProperty("jdbc.drivers").equals(DriverPerolaForJDBC.ODBC_DRIVER)){
					varchar = "TEXT(25)";
					text = "MEMO"; //Supondo que quem usa o JdbcOdbcDriver usa MS Access, caso não use Access, deve usar um driver JDBC!
				} else {
					varchar = "VARCHAR(25)";
					text = "TEXT";
				}
				PreparedStatement stmt = createConnectionAndPrepareCommand("CREATE TABLE Objects (uid " + varchar + ", contents " + text + ")");
				stmt.executeUpdate();
				stmt.close();
				conn.close();
				saveObject(nomeArquivo, conteudo, false);
			} else throw e;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see perola.core.PerolaDriver#readObject(java.lang.String)
	 */
	@Override
	public String readObject(String nomeArquivo) throws SQLException {
		String contents = null;
		PreparedStatement stmt = createConnectionAndPrepareCommand("SELECT * FROM Objects WHERE uid = '" + nomeArquivo + "'");
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) contents = rs.getString("contents");
		stmt.close();
		conn.close();
		return contents;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see perola.core.PerolaDriver#removeObject(java.lang.String)
	 */
	@Override
	public void removeObject(String objectUID) throws SQLException {
		PreparedStatement stmt = createConnectionAndPrepareCommand("DELETE FROM Objects WHERE uid = '" + objectUID + "'");
		stmt.executeUpdate();
		stmt.close();
		conn.close();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see perola.core.PerolaDriver#listPersistedUIDs()
	 */
	@Override
	public Set<String> listPersistedUIDs() throws SQLException {
		Set<String> persistedUIDs = new TreeSet<String>();
		PreparedStatement stmt = createConnectionAndPrepareCommand("SELECT * FROM Objects WHERE uid != 'root'");
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			persistedUIDs.add(rs.getString("uid"));
		}
		stmt.close();
		conn.close();
		return persistedUIDs;
	}
}