/*
 * Created on 8/04/2003
 *
 */
package com.quantum.sql.metadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.quantum.util.StringMatrix;

/**
 *	Groups functions that allow extracting data from a JDBC connection 
 *  in the form of StringMatrix objects
 *
 * @author panic
 *
 */
public class MetaDataJDBCInterface {
	
	/**
	 * @param con : A valid (open) connection to an JDBC database
	 * @param schema : Schema of the table. Must be null if not given
	 * @param table : Name of the table.
	 * @return a StringMatrix with the info of the columns' metadata, null if error
	 */
	public static StringMatrix getColumns(Connection con, String schema, String table) 
			throws SQLException {

		DatabaseMetaData meta = con.getMetaData();
		ResultSet set = null;
		StringMatrix columns = null;
		try {
			set = meta.getColumns(null, schema, table, null);
			columns = fillMatrix(set);
			set.close();
			
		} catch (SQLException e) {
		}
		if (columns == null) {
			columns = new StringMatrix();
			String query = "SELECT * FROM \"" + table + "\" WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
			Statement stmt = con.createStatement();
			stmt.execute(query);
			ResultSet rset = stmt.getResultSet();
			ResultSetMetaData rsMetaData = rset.getMetaData();
			int columnCount = rsMetaData.getColumnCount();
			String headMatrix[] = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME","COLUMN_NAME","DATA_TYPE","TYPE_NAME","COLUMN_SIZE",
									"BUFFER_LENGTH","DECIMAL_DIGITS","NUM_PREC_RADIX","NULLABLE","REMARKS",
									"COLUMN_DEF","SQL_DATA_TYPE","SQL_DATETIME_SUB","CHAR_OCTET_LENGTH",
									"ORDINAL_POSITION","IS_NULLABLE","SCOPE_CATLOG","SCOPE_SCHEMA","SCOPE_TABLE","SOURCE_DATA_TYPE"};
			columns.addMatrixHeader(headMatrix); 
			for (int i = 0; i < columnCount; i++) {
				columns.add(null, i); /* TABLE_CAT */
				columns.add(schema, i); /* TABLE_SCHEM */
				columns.add(table, i); /* TABLE_NAME */
				columns.add(rsMetaData.getColumnName(i+1), i); /* COLUMN_NAME */
				columns.add(String.valueOf(rsMetaData.getColumnType(i+1)), i); /* DATA_TYPE */
				columns.add(rsMetaData.getColumnTypeName(i+1), i); /* TYPE_NAME */
				columns.add(String.valueOf(rsMetaData.getPrecision(i+1)), i); /* COLUMN_SIZE */
				columns.add(null, i); /* BUFFER_LENGTH */
				columns.add(String.valueOf(rsMetaData.getScale(i+1)), i); /* DECIMAL_DIGITS */
				columns.add(null, i); /* NUM_PREC_RADIX */
				int isNullable = rsMetaData.isNullable(i+1);
				columns.add(String.valueOf(isNullable), i); /* NULLABLE */
				columns.add(null, i); /* REMARKS */
				columns.add(null, i); /* COLUMN_DEF */
				columns.add(null, i); /* SQL_DATA_TYPE */
				columns.add(null, i); /* SQL_DATETIME_SUB */
				columns.add(String.valueOf(rsMetaData.getColumnDisplaySize(i+1)), i); /* CHAR_OCTET_LENGTH */
				columns.add(String.valueOf(i+1), i); /* ORDINAL_POSITION */
				columns.add(isNullable == DatabaseMetaData.columnNullable ? "YES" : "NO", i );
				columns.add(null, i); /* SCOPE_CATLOG */
				columns.add(null, i); /* SCOPE_SCHEMA */
				columns.add(null, i); /* SCOPE_TABLE */
				columns.add(null, i); /* SOURCE_DATA_TYPE */
			}					
		}
		return columns;
	}

	/**
	 * @param con : A valid (open) connection to an JDBC database
	 * @param schema : Schema of the table. Must be null if not given
	 * @param table : Name of the table.
	 * @return a StringMatrix with the info of the primary keys
	 */
	public static StringMatrix getPrimaryKeys(Connection con, String schema, String table) {

		StringMatrix keys = new StringMatrix();
		try {
			DatabaseMetaData meta = con.getMetaData();
			ResultSet set = meta.getPrimaryKeys(null, schema, table);
			keys = fillMatrix(set);
			set.close();
		} catch (SQLException e) {
			   e.printStackTrace();
		}
		return keys;
	}

	/**
	 * @param con : A valid (open) connection to an JDBC database
	 * @param schema : Schema of the table. Must be null if not given
	 * @param table : Name of the table.
	 * @param imported : Determines if the foreign keys are the imported or exported ones
	 * @return a StringMatrix with the info of the foreign keys
	 */
	public static StringMatrix getForeignKeys(Connection con, String schema, String table,  boolean imported ) {
				
		ResultSet set = null;
		StringMatrix keys = new StringMatrix();
		try {
				DatabaseMetaData meta = con.getMetaData();
			if (imported){
				set = meta.getImportedKeys(null, schema, table);
			} else {
				set = meta.getExportedKeys(null, schema, table);
			}
			keys = fillMatrix(set);
			set.close();
		} catch (SQLException e) {
			   e.printStackTrace();
		}
		return keys;
	}

	/**
	 * @param con : A valid (open) connection to an JDBC database
	 * @param schema : Schema of the table. Must be null if not given
	 * @param table : Name of the table.
	 * @return a StringMatrix with the info of the indexes on that table
	 */
	public static StringMatrix getIndexInfo(Connection con, String schema, String table) {

		ResultSet set = null;
		DatabaseMetaData meta;
		StringMatrix keys = new StringMatrix();
		try {
			meta = con.getMetaData();
			set = meta.getIndexInfo(null, schema, table, false, false);
			keys = fillMatrix(set);
			set.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return keys;
	}


	/**
	 * 
	 * @param set
	 * @return a filled StringMatrix with the set results
	 * @throws SQLException
	 */
	public static StringMatrix fillMatrix(ResultSet set) throws SQLException {
		int columnCount = set.getMetaData().getColumnCount();
		StringMatrix keys = new StringMatrix();
		for (int i = 1; i <= columnCount; i++) {
			keys.addHeader(set.getMetaData().getColumnName(i));
		}
		int row = 0;
		while (set.next()) {
			for (int i = 1; i <= columnCount; i++) {
				keys.add(set.getString(i), row);
			}
			row++;
		}
		return keys;
	}

	/**
	 * @param con
	 * @param schema
	 * @param tableName
	 */
	public static StringMatrix getBestRowId(Connection con, String schema, String table) 
		throws SQLException {

		ResultSet set = null;
		DatabaseMetaData meta = con.getMetaData();
		set = meta.getBestRowIdentifier(null, schema, table, DatabaseMetaData.bestRowSession, true);
		StringMatrix keys = fillMatrix(set);
		set.close();
		return keys;

	}


}