TestJDBCConnection.java

package tap.db;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import adql.db.DBChecker;
import adql.db.DBColumn;
import adql.db.DBTable;
import adql.db.DBType;
import adql.db.DBType.DBDatatype;
import adql.parser.ADQLParser;
import adql.parser.ParseException;
import adql.query.ADQLQuery;
import adql.query.IdentifierField;
import adql.translator.AstroH2Translator;
import adql.translator.PostgreSQLTranslator;
import tap.data.DataReadException;
import tap.data.TableIterator;
import tap.data.VOTableIterator;
import tap.db_testtools.DBTools;
import tap.metadata.TAPColumn;
import tap.metadata.TAPForeignKey;
import tap.metadata.TAPMetadata;
import tap.metadata.TAPMetadata.STDSchema;
import tap.metadata.TAPMetadata.STDTable;
import tap.metadata.TAPSchema;
import tap.metadata.TAPTable;

public class TestJDBCConnection {

	private static Connection h2Connection;
	private static JDBCConnection h2JDBCConnection;
	private static JDBCConnection sensH2JDBCConnection;

	private static Connection sqliteConnection;
	private static JDBCConnection sqliteJDBCConnection;
	private static JDBCConnection sensSqliteJDBCConnection;

	private static String uploadExamplePath;

	private final static String sqliteDbFile = "./test/tap/db_testtools/db-test/sqlite_testDB.db";

	@BeforeClass
	public static void setUpBeforeClass() throws Exception{

		uploadExamplePath = "./test/tap/db/upload_example.vot";

		DBTools.createTestDB();
		h2Connection = DBTools.createConnection("h2", null, null, DBTools.DB_TEST_PATH, DBTools.DB_TEST_USER, DBTools.DB_TEST_PWD);
		h2JDBCConnection = new JDBCConnection(h2Connection, new AstroH2Translator(false), "H2", new QueryExecutor(), null);
		sensH2JDBCConnection = new JDBCConnection(h2Connection, new AstroH2Translator(true, true, true, true), "SensitiveH2", new QueryExecutor(), null);

		sqliteConnection = DBTools.createConnection("sqlite", null, null, sqliteDbFile, null, null);
		sqliteJDBCConnection = new JDBCConnection(sqliteConnection, new PostgreSQLTranslator(false), "SQLITE", new QueryExecutor(), null);
		sensSqliteJDBCConnection = new JDBCConnection(sqliteConnection, new PostgreSQLTranslator(true), "SensitiveSQLite", new QueryExecutor(), null);
	}

	@AfterClass
	public static void tearDownAfterClass() throws Exception{
		// Drop the H2 database:
		DBTools.dropTestDB();

		// Drop the SQLite database:
		(new File(sqliteDbFile)).delete();
	}

	/* ***** */
	/* TESTS */
	/* ***** */

	@Test
	public void testGetTAPSchemaTablesDef(){
		// There should be no difference between a H2 connection and a SQLITE one!
		JDBCConnection[] connections = new JDBCConnection[]{h2JDBCConnection,sensH2JDBCConnection,sqliteJDBCConnection,sensSqliteJDBCConnection};
		for(JDBCConnection conn : connections){
			TAPMetadata meta = createCustomSchema();
			TAPTable customColumns = meta.getTable(STDSchema.TAPSCHEMA.toString(), STDTable.COLUMNS.toString());
			TAPTable[] tapTables = conn.mergeTAPSchemaDefs(meta);
			TAPSchema stdSchema = TAPMetadata.getStdSchema(conn.supportsSchema);
			assertEquals(5, tapTables.length);
			assertTrue(equals(tapTables[0], stdSchema.getTable(STDTable.SCHEMAS.label)));
			assertEquals(customColumns.getSchema(), tapTables[0].getSchema());
			assertTrue(equals(tapTables[1], stdSchema.getTable(STDTable.TABLES.label)));
			assertEquals(customColumns.getSchema(), tapTables[1].getSchema());
			assertTrue(equals(tapTables[2], customColumns));
			assertTrue(equals(tapTables[3], stdSchema.getTable(STDTable.KEYS.label)));
			assertEquals(customColumns.getSchema(), tapTables[3].getSchema());
			assertTrue(equals(tapTables[4], stdSchema.getTable(STDTable.KEY_COLUMNS.label)));
			assertEquals(customColumns.getSchema(), tapTables[4].getSchema());
		}
	}

	@Test
	public void testSetTAPSchema(){
		// There should be no difference between a H2 connection and a SQLITE one!
		JDBCConnection[] connections = new JDBCConnection[]{h2JDBCConnection,sensH2JDBCConnection,sqliteJDBCConnection,sensSqliteJDBCConnection};
		for(JDBCConnection conn : connections){
			short cnt = -1;
			while(cnt < 1){
				/* NO CUSTOM DEFINITION */
				// Prepare the test:
				if (cnt == -1)
					dropSchema(STDSchema.TAPSCHEMA.label, conn);
				else
					createTAPSchema(conn);
				// Do the test:
				try{
					TAPMetadata meta = new TAPMetadata();
					int[] expectedCounts = getStats(meta);
					conn.setTAPSchema(meta);
					int[] effectiveCounts = getStats(conn, meta);
					for(int i = 0; i < expectedCounts.length; i++)
						assertEquals(expectedCounts[i], effectiveCounts[i]);
				}catch(DBException dbe){
					dbe.printStackTrace(System.err);
					fail("[" + conn.getID() + ";no def] No error should happen here ; when an empty list of metadata is given, at least the TAP_SCHEMA should be created and filled with a description of itself.");
				}

				/* CUSTOM DEFINITION */
				// Prepare the test:
				if (cnt == -1)
					dropSchema(STDSchema.TAPSCHEMA.label, conn);
				// Do the test:
				try{
					TAPMetadata meta = createCustomSchema();
					int[] expectedCounts = getStats(meta);
					conn.setTAPSchema(meta);
					int[] effectiveCounts = getStats(conn, meta);
					for(int i = 0; i < expectedCounts.length; i++)
						assertEquals(expectedCounts[i], effectiveCounts[i]);
				}catch(DBException dbe){
					dbe.printStackTrace(System.err);
					fail("[" + conn.getID() + ";custom def] No error should happen here!");
				}

				cnt++;
			}
		}
	}

	@Test
	public void testGetCreationOrder(){
		// There should be no difference between a H2 connection and a SQLITE one!
		JDBCConnection[] connections = new JDBCConnection[]{h2JDBCConnection,sensH2JDBCConnection,sqliteJDBCConnection,sensSqliteJDBCConnection};
		for(JDBCConnection conn : connections){
			assertEquals(-1, conn.getCreationOrder(null));
			assertEquals(0, conn.getCreationOrder(STDTable.SCHEMAS));
			assertEquals(1, conn.getCreationOrder(STDTable.TABLES));
			assertEquals(2, conn.getCreationOrder(STDTable.COLUMNS));
			assertEquals(3, conn.getCreationOrder(STDTable.KEYS));
			assertEquals(4, conn.getCreationOrder(STDTable.KEY_COLUMNS));
		}
	}

	@Test
	public void testGetDBMSDatatype(){
		assertEquals("VARCHAR", h2JDBCConnection.defaultTypeConversion(null));
		assertEquals("TEXT", sqliteJDBCConnection.defaultTypeConversion(null));

		assertEquals("VARBINARY", h2JDBCConnection.defaultTypeConversion(new DBType(DBDatatype.VARBINARY)));
		assertEquals("BLOB", sqliteJDBCConnection.defaultTypeConversion(new DBType(DBDatatype.VARBINARY)));
	}

	@Test
	public void testMergeTAPSchemaDefs(){
		// There should be no difference between a H2 connection and a SQLITE one!
		JDBCConnection[] connections = new JDBCConnection[]{h2JDBCConnection,sensH2JDBCConnection,sqliteJDBCConnection,sensSqliteJDBCConnection};
		for(JDBCConnection conn : connections){

			// TEST WITH NO METADATA OBJECT:
			// -> expected: throws a NULL exception.
			try{
				conn.mergeTAPSchemaDefs(null);
			}catch(Exception e){
				assertEquals(NullPointerException.class, e.getClass());
			}

			// TEST WITH EMPTY METADATA OBJECT:
			// -> expected: returns at least the 5 tables of the TAP_SCHEMA.
			TAPTable[] stdTables = conn.mergeTAPSchemaDefs(new TAPMetadata());