virtuoso/java/insertion

package fr.inrialpes.exmo.evolution;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.slf4j.Logger;

import com.hp.hpl.jena.util.URIref;

import virtuoso.jdbc4.VirtuosoException;


public class XXXXXXX {
	private static final Logger logger = org.slf4j.LoggerFactory.getLogger(Evolution.class);

	public static void main(String[] args) throws SQLException {
		//getSameAsLinks("<http://umbel.org>");
		//realization("INSTANCE2", "<http://umbel.org>");
		//realization("INSTANCE1","<http://dbpedia.org>");
		getRealisedSameAsLinks("<http://umbel.org>", "<http://dbpedia.org>", "<http://umbel.org>");
	}

	public static void getRealisedSameAsLinks(String graphnameSameAs, String graphnameLeft, String graphnameRight) throws SQLException {

		try {

			logger.info("starting process getRealisedSameAsLinks...");

			//Connection conn = DriverManager.getConnection("jdbc:virtuoso://localhost:1111/exmo.DBA/log_enable=2", "dba", "dba");
			Connection conn = DriverManager.getConnection("jdbc:virtuoso://localhost:1111/", "dba", "dba");

			String query1 = "SELECT instance1, instance2 FROM exmo.DBA.SAMEAS WHERE graphname =  iri_to_id(?)";

			// S = instance
			// O = classname
			// P = rdf:type (very often)
			// G = graphname

			String query2 = "SELECT DISTINCT O AS classname FROM exmo.DBA.INSTANCES WHERE S = iri_id_from_num(?) AND G = iri_to_id(?)";

			String query3 = "SELECT DISTINCT O AS classname FROM exmo.DBA.INSTANCES WHERE S = iri_id_from_num(?) AND G = iri_to_id(?)";

			//String sql = "INSERT INTO exmo.DBA.SAMEAS_REALIZATION (instance1, classname1, instance2, classname2, graphname) VALUES(iri_id_from_num(?),iri_id_from_num(?),iri_id_from_num(?),iri_id_from_num(?),iri_to_id(?))";
			//String sql = "INSERT INTO exmo.DBA.SAMEAS_REALIZATION2 (instance1, classname1, graphname) VALUES(iri_id_from_num(?),iri_id_from_num(?),iri_to_id(?))";
			String sql = "INSERT INTO exmo.DBA.SAMEAS_REALIZATION2 (instance1, classname1, graphname) VALUES(?,?,?)";

			PreparedStatement preparedStatement1 = conn.prepareStatement(query1);
			preparedStatement1.setString(1, graphnameSameAs);
			ResultSet rows1 = preparedStatement1.executeQuery();
			PreparedStatement preparedStatement4 = conn.prepareStatement(sql);

			// disabling log_enable

			String log_enable = "log_enable(2)";
			Statement st = conn.createStatement();
			st.executeQuery(log_enable);

			double total = 729306;
			double i = 0;
			while (rows1.next()) {
				i++;
				int instance1 = rows1.getInt("instance1");
				//System.out.println(instance1);
				int instance2 = rows1.getInt("instance2");
				//System.out.println(instance2);

				ArrayList<Integer> classes1 = new ArrayList<Integer>();
				ArrayList<Integer> classes2 = new ArrayList<Integer>();

				PreparedStatement preparedStatement2 = conn.prepareStatement(query2);
				preparedStatement2.setInt(1, instance1);
				preparedStatement2.setString(2, graphnameLeft);

				ResultSet rows2 = preparedStatement2.executeQuery();

				while (rows2.next()) {
					classes1.add(rows2.getInt("classname"));
				}

			    //System.out.println("classes1 = " + classes1);

				preparedStatement2.close();

				PreparedStatement preparedStatement3 = conn.prepareStatement(query3);
				preparedStatement3.setInt(1, instance2);
				preparedStatement3.setString(2, graphnameRight);

				ResultSet rows3 = preparedStatement3.executeQuery();

				while (rows3.next()) {
					classes2.add(rows3.getInt("classname"));
				}

				//System.out.println("classes2 = " + classes2);

				preparedStatement3.close();

				for (int class1 : classes1) {
					for (int class2 : classes2) {
						//System.out.println(instance1 + "|" + class1 + "|" + instance2 + "|" + class2);
						preparedStatement4.setInt(1, instance1);
						preparedStatement4.setInt(2, class1);
						preparedStatement4.setString(3, graphnameSameAs);
						preparedStatement4.executeUpdate();
					}
				}
				if ((i % 10000) == 0) {
					logger.info("progress " + (i / total) * 100 + "%");
				}
			}
			preparedStatement4.close();
			conn.close();
			logger.info("getRealisedSameAsLinks done...");
		} catch (VirtuosoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void realization(String columname, String graphname) throws SQLException {
		try {
			String query1 = "SELECT DISTINCT id_to_iri(%s) FROM exmo.DBA.SAMEAS";
			String query2 = "SPARQL SELECT ?instance ?class  FROM %s {\n" +
					"?class rdf:type owl:Class.\n" +
					"?instance a ?class.\n" +
					"  FILTER NOT EXISTS {\n" +
					"    ?subtype ^a ?instance ;\n" +
					"             rdfs:subClassOf ?class .\n" +
					"    FILTER ( ?subtype != ?class )\n" +
					"  }\n" +
					"FILTER(?instance = <%s>)\n" +
					"}";

			Connection conn = DriverManager.getConnection("jdbc:virtuoso://localhost:1111/", "dba", "dba");
			Statement st1 = conn.createStatement();
			ResultSet rows1 = st1.executeQuery(String.format(query1,columname));

			String sql = "INSERT INTO exmo.DBA.INSTANCES (P, S, G, O) VALUES(iri_to_id(?),iri_to_id(?),iri_to_id(?),iri_to_id(?))";
			PreparedStatement preparedStatement3 = conn.prepareStatement(sql);
			double total = 729306;
			double i = 0;
			while (rows1.next()) {
				i++;
				Statement st2 = conn.createStatement();
				ResultSet rows2 = st2.executeQuery(String.format(query2, graphname, URIref.encode(rows1.getString(1))));
				while (rows2.next()) {
					preparedStatement3.setString(1, "http://www.w3.org/1999/02/22-rdf-syntax-ns#type");
					preparedStatement3.setString(2, rows2.getString(1));
					preparedStatement3.setString(3, graphname);
					preparedStatement3.setString(4, rows2.getString(2));
					preparedStatement3.executeUpdate();
					if ((i % 10000) == 0) {
						logger.info("progress " + (i / total) * 100 + "%");
					}
				}
				st2.close();
			}
			st1.close();
			conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Change the select parameters to ?s ?o
	 * @param graphname
	 */
   public static void getSameAsLinks(String graphname){
		try {
			Connection conn = DriverManager.getConnection("jdbc:virtuoso://localhost:1111/", "dba", "dba");
			Statement st = conn.createStatement();
			String query = "SPARQL SELECT ?s ?s {GRAPH %s {\n" +
					"?o rdf:type owl:Class.\n" +
					"?s rdf:type ?o.\n" +
					"}}\n" +
					"";
			ResultSet rows = st.executeQuery(String.format(query, graphname));
			String sql = "INSERT INTO exmo.DBA.SAMEAS (instance1, instance2, graphname) VALUES(iri_to_id(?),iri_to_id(?),iri_to_id(?))";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);

			while (rows.next()) {
				preparedStatement.setString(1, rows.getString(1));
				preparedStatement.setString(2, rows.getString(2));
				preparedStatement.setString(3, graphname);
				preparedStatement.executeUpdate();
			}
			conn.close();

		} catch (SQLException ex) {
			ex.printStackTrace();
		}
   }

   public static String realisation_umbel_dbpedia(String instance1, String instance2) {
	   String query = "DEFINE sql:log-enable 2  "
	   		+ "SELECT ?instance1 ?class1 ?instance2 ?class2 {\n" +
	   		"OPTIONAL\n" +
	   		"{GRAPH <http://dbpedia.org> {\n" +
	   		"?class1 rdf:type owl:Class.\n" +
	   		"?instance1 rdf:type ?class1.\n" +
	   		"FILTER NOT EXISTS {?subclass1 rdfs:subClassOf ?class1. ?instance1 rdf:type ?subclass1}\n" +
	   		"FILTER (REGEX(?class1, 'http://dbpedia.org/ontology') && ?class1 != <http://dbpedia.org/ontology/Wikidata:Q532>)\n" +
	   		"}}\n" +
	   		"OPTIONAL\n" +
	   		"{GRAPH <http://umbel.org> {\n" +
	   		"?class2 rdf:type owl:Class.\n" +
	   		"?instance2 rdf:type ?class2.\n" +
	   		"FILTER NOT EXISTS {?subclass2 rdfs:subClassOf ?class2. ?instance2 rdf:type ?subclass2}\n" +
	   		"FILTER (REGEX(?class2, 'http://umbel.org'))\n" +
	   		"\n" +
	   		"}}\n" +
	   		"FILTER(?instance1 = <" + instance1 + "> && ?instance2 = <" + instance2 + ">)\n" +
	   		"}";
	   return query;
   }


}