package com.hp.hpl.squirrelrdf.rdb.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import squirrelrdf.ExtractConfig;
import com.hp.hpl.jena.query.DatasetFactory;
import com.hp.hpl.jena.query.Query;
import com.hp.hpl.jena.query.QueryFactory;
import com.hp.hpl.jena.query.QuerySolution;
import com.hp.hpl.jena.query.ResultSet;
import com.hp.hpl.jena.query.ResultSetFormatter;
import com.hp.hpl.jena.query.engine1.QueryEngine;
import com.hp.hpl.jena.rdf.model.Model;
import com.hp.hpl.jena.rdf.model.RDFNode;
import com.hp.hpl.jena.rdf.model.Resource;
import com.hp.hpl.jena.vocabulary.RDF;
import com.hp.hpl.jena.vocabulary.RDFS;
import com.hp.hpl.squirrelrdf.querymap.exceptions.ConfigException;
import com.hp.hpl.squirrelrdf.rdb.DbMap;
import com.hp.hpl.squirrelrdf.rdb.SQLQueryEngine;
import junit.framework.TestCase;
public class RDBTests extends TestCase {
Connection conn;
protected void setUp() throws Exception {
super.setUp();
Class.forName("org.hsqldb.jdbcDriver");
conn = DriverManager.getConnection("jdbc:hsqldb:mem:test", "sa", "");
java.sql.ResultSet tables = conn.getMetaData().getTables(null, null, "TABLE1", null);
if (!tables.next()) // db not initialised
{
conn.createStatement().execute("CREATE TABLE TABLE1 ( FIELD1 int, FIELD2 varchar(20), FIELD3 int, PRIMARY KEY (FIELD1, FIELD3))");
conn.createStatement().execute("CREATE TABLE TABLE2 ( FIELD1 int, FIELD2 varchar(20));");
conn.createStatement().execute("INSERT INTO TABLE1 VALUES (1, 'A', 2);");
conn.createStatement().execute("INSERT INTO TABLE1 VALUES (2, 'C', 3);");
conn.createStatement().execute("INSERT INTO TABLE2 VALUES (1, 'B');");
}
tables.close();
}
protected void tearDown() throws Exception {
super.tearDown();
conn.close();
}
public QueryEngine prepareQuery(String queryS) throws SQLException, ConfigException
{
Model config = ExtractConfig.process("jdbc:hsqldb:mem:test", "org.hsqldb.jdbcDriver", "urn:ex:", "sa", "", new String[]{"TABLE1", "TABLE2"}, conn.getMetaData());
Query query = QueryFactory.create(queryS);
QueryEngine qe = new SQLQueryEngine(query, config);
qe.setDataset(DatasetFactory.create());
return qe;
}
public void testExtractConfig() throws SQLException, ClassNotFoundException
{
Model config = ExtractConfig.process("jdbc:hsqldb:mem:test", "org.hsqldb.jdbcDriver", "urn:ex:", "sa", "", new String[]{"TABLE1", "TABLE2"}, conn.getMetaData());
Resource table1 = config.createResource("urn:ex:TABLE1");
Resource table2 = config.createResource("urn:ex:TABLE2");
Resource map = config.createResource("urn:ex:map");
assertTrue("Found table1", config.contains(table1, RDF.type, RDFS.Class));
assertTrue("Found table2", config.contains(table2, RDF.type, RDFS.Class));
assertTrue("Map maps table1", config.contains(map, DbMap.mapsClass, table1));
config.write(System.out, "RDF/XML-ABBREV");
}
public void testBasicAskQuery() throws SQLException, ConfigException
{
QueryEngine qe = prepareQuery("ASK { _:s <urn:ex:TABLE1_FIELD1> 1 }");
boolean res = qe.execAsk();
assertTrue("DB contains triple", res);
}
public void testBasicSelectQuery() throws SQLException, ConfigException
{
QueryEngine qe = prepareQuery("SELECT ?s ?val1 WHERE { ?s <urn:ex:TABLE1_FIELD1> ?val1 }");
ResultSet res = qe.execSelect();
assertTrue("Query has results", res.hasNext());
ResultSetFormatter.out(System.out, res);
}
public void testJoinQuery() throws SQLException, ConfigException
{
QueryEngine qe = prepareQuery("SELECT ?val1 ?val2 ?val3 WHERE { ?s1 <urn:ex:TABLE1_FIELD1> ?val1 ; <urn:ex:TABLE1_FIELD2> ?val2 . ?s2 <urn:ex:TABLE2_FIELD1> ?val1 ; <urn:ex:TABLE2_FIELD2> ?val3 .}");
ResultSet res = qe.execSelect();
assertTrue("Query has results", res.hasNext());
ResultSetFormatter.out(System.out, res);
}
public void testOptionalAskQuery() throws SQLException, ConfigException
{
QueryEngine qe = prepareQuery("ASK { _:s <urn:ex:TABLE1_FIELD1> 1 . OPTIONAL { _:s <urn:ex:TABLE1_FIELD2> 'not there'} }");
boolean res = qe.execAsk();
assertTrue("Matched optional", res);
}
public void testOptionalSelectQuerySameSubject() throws SQLException, ConfigException
{
QueryEngine qe = prepareQuery("SELECT * WHERE { ?s <urn:ex:TABLE1_FIELD1> ?val1 . OPTIONAL { ?s <urn:ex:TABLE1_FIELD2> ?val2} }");
ResultSet res = qe.execSelect();
assertTrue("Optional subject join has result", res.hasNext());
// No more than two results!
res.next(); res.next();
assertFalse("I only have two results", res.hasNext());
//ResultSetFormatter.out(System.out, res);
}
public void testTypeSelectQuery() throws SQLException, ConfigException
{
QueryEngine qe = prepareQuery("ASK WHERE { ?s a <urn:ex:TABLE1> ; <urn:ex:TABLE1_FIELD1> ?val1 . }");
boolean result = qe.execAsk();
assertTrue("No problem with type query", result);
qe = prepareQuery("ASK WHERE { ?s a <urn:ex:TABLE2> ; <urn:ex:TABLE1_FIELD1> ?val1 . }");
result = qe.execAsk();
assertFalse("Type mismatch caused no match", result);
}
public void testRoundTrip() throws SQLException, ConfigException
{
QueryEngine qe;
qe = prepareQuery("SELECT ?subj WHERE { ?subj <urn:ex:TABLE1_FIELD2> 'C' }");
ResultSet res = qe.execSelect();
assertTrue("I have results", res.hasNext());
QuerySolution soln = res.nextSolution();
RDFNode subj = soln.get("subj");
assertEquals("Result is correct", "urn:ex:TABLE1;FIELD1=2;FIELD3=3", ((Resource) subj).getURI());
qe = prepareQuery("ASK WHERE { <urn:ex:TABLE1;FIELD1=2;FIELD3=3> <urn:ex:TABLE1_FIELD2> 'C' } ");
boolean rb = qe.execAsk();
assertTrue("Correct result for round trip", rb);
}
public void testConcreteSubject() throws SQLException, ConfigException
{
QueryEngine qe;
boolean res;
qe = prepareQuery("ASK WHERE { <urn:ex:TABLE1;FIELD1=2> <urn:ex:TABLE1_FIELD2> 'C' }");
res = qe.execAsk();
assertFalse("I have no result", res);
qe = prepareQuery("ASK WHERE { <urn:ex:TABLE1;FIELD1=2> <urn:ex:TABLE2_FIELD2> 'C' }");
res = qe.execAsk();
assertFalse("I have no result", res);
}
// Simple 'list things of type...'
public void testType() throws SQLException, ConfigException
{
QueryEngine qe;
qe = prepareQuery("SELECT ?s WHERE { ?s a <urn:ex:TABLE1> }");
ResultSet res = qe.execSelect();
assertTrue("I have a result", res.hasNext());
}
}