mysqlsqlpostgresqlsesameopenrdf

Issue accessing backend DB through openRDF Sesame


I have the following code in java to query SPARQL query over the Backend DB (postgreSQL).

import rdfProcessing.RDFRepository;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

import org.openrdf.query.QueryLanguage;
import org.openrdf.query.TupleQueryResult;
import org.openrdf.repository.Repository;
import org.openrdf.repository.RepositoryConnection;
import org.openrdf.repository.manager.LocalRepositoryManager;
import org.openrdf.repository.manager.RepositoryManager;
import org.openrdf.sail.config.SailImplConfig;
import org.openrdf.sail.memory.config.MemoryStoreConfig;
import org.openrdf.repository.config.RepositoryImplConfig;
import org.openrdf.repository.sail.config.SailRepositoryConfig;
import org.openrdf.repository.config.RepositoryConfig;

public class Qeryrdf {
    Connection connection;

        private static final String REPO_ID = "C:\\RDF_triples\\univData10m\\repositories\\SYSTEM\\memorystore.data";
        private static final String q1 = ""
                        + "PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#>" +
                        "PREFIX ub:<http://univ.org#>" + 
                        "PREFIX owl:<http://www.w3.org/2002/07/owl#>" +
                        "PREFIX rdf:<http://www.w3.org/1999/02/22-rdf-syntax-ns#>" +
                        " select distinct  ?o ?p  where"+
                        "{ ?s rdf:type ?o." +
                        "}";

        public static void main(String[] args)
                        throws Exception {
            LocalRepositoryManager manager = new LocalRepositoryManager(new File("C:\\RDF triples\\univData1"));
                manager.initialize();
                try {
                    Qeryrdf queryrdf = new Qeryrdf();
                    queryrdf.executeQueries(manager);
                } finally {
                        manager.shutDown();
                }
        }

        private void executeQueries(RepositoryManager manager)
                        throws Exception {


            SailImplConfig backendConfig = new MemoryStoreConfig();
            RepositoryImplConfig repositoryTypeSpec = new SailRepositoryConfig(backendConfig);

            String repositoryId = REPO_ID;
            RepositoryConfig repConfig = new RepositoryConfig(repositoryId, repositoryTypeSpec);
            manager.addRepositoryConfig(repConfig);

            Repository repo = manager.getRepository(repositoryId);
                repo.initialize();
                RepositoryConnection con = repo.getConnection();

                RDFRepository repository = new RDFRepository();
                String repoDir = "C:\\RDF triples\\univData1" ;
                repository.initializeRepository(repoDir );

                System.out.println("Executing the query");
                executeQuery(q1, con);
                con.close();
                repo.shutDown();
        }

        private void executeQuery(String query, RepositoryConnection con) {
            getConnection();
                try {
                        TupleQueryResult result = con.prepareTupleQuery(QueryLanguage.SPARQL, query).evaluate();
                        int resultCount = 0;
                        long time = System.currentTimeMillis();
                        while (result.hasNext()) {
                                result.next();
                                resultCount++;
                        }
                        time = System.currentTimeMillis() - time;
                        System.out.printf("Result count: %d in %fs.\n", resultCount, time / 1000.0);
                } catch (Exception e) {
                        e.printStackTrace();
                }
        }

        public void getConnection() {
            try {
                Class.forName("org.postgresql.Driver");
                connection = DriverManager.getConnection(
                        "jdbc:postgresql://localhost:5432/myDB01", "postgres",
                        "aabbcc");
            } catch (Exception e) {
                e.printStackTrace();
                System.err.println(e.getClass().getName() + ": " + e.getMessage());
                System.exit(0);
            }
            System.out.println("The database opened successfully");
        }
}



And I got the following result:

16:46:44.546 [main] DEBUG org.openrdf.sail.memory.MemoryStore - Initializing MemoryStore...
16:46:44.578 [main] DEBUG org.openrdf.sail.memory.MemoryStore - Reading data from C:\RDF triples\univData1\repositories\SYSTEM\memorystore.data...
16:46:44.671 [main] DEBUG org.openrdf.sail.memory.MemoryStore - Data file read successfully
16:46:44.671 [main] DEBUG org.openrdf.sail.memory.MemoryStore - MemoryStore initialized
16:46:44.765 [main] DEBUG org.openrdf.sail.memory.MemoryStore - syncing data to file...
16:46:44.796 [main] DEBUG org.openrdf.sail.memory.MemoryStore - Data synced to file
16:46:44.796 [main] DEBUG o.o.r.manager.LocalRepositoryManager - React to commit on SystemRepository for contexts [_:node18j9mufr0x1]
16:46:44.796 [main] DEBUG o.o.r.manager.LocalRepositoryManager - Processing modified context _:node18j9mufr0x1.
16:46:44.796 [main] DEBUG o.o.r.manager.LocalRepositoryManager - Is _:node18j9mufr0x1 a repository config context?
16:46:44.796 [main] DEBUG o.o.r.manager.LocalRepositoryManager - Reacting to modified repository config for C:\RDF triples\univData1\repositories\SYSTEM\memorystore.data
16:46:44.796 [main] DEBUG o.o.r.manager.LocalRepositoryManager - Modified repository C:\RDF triples\univData1\repositories\SYSTEM\memorystore.data has not been initialized, skipping...
16:46:44.812 [main] DEBUG o.o.r.config.RepositoryRegistry - Registered service class org.openrdf.repository.contextaware.config.ContextAwareFactory
16:46:44.812 [main] DEBUG o.o.r.config.RepositoryRegistry - Registered service class org.openrdf.repository.dataset.config.DatasetRepositoryFactory
16:46:44.843 [main] DEBUG o.o.r.config.RepositoryRegistry - Registered service class org.openrdf.repository.http.config.HTTPRepositoryFactory
16:46:44.843 [main] DEBUG o.o.r.config.RepositoryRegistry - Registered service class org.openrdf.repository.sail.config.SailRepositoryFactory
16:46:44.843 [main] DEBUG o.o.r.config.RepositoryRegistry - Registered service class org.openrdf.repository.sail.config.ProxyRepositoryFactory
16:46:44.843 [main] DEBUG o.o.r.config.RepositoryRegistry - Registered service class org.openrdf.repository.sparql.config.SPARQLRepositoryFactory
16:46:44.859 [main] DEBUG org.openrdf.sail.config.SailRegistry - Registered service class org.openrdf.sail.federation.config.FederationFactory
16:46:44.859 [main] DEBUG org.openrdf.sail.config.SailRegistry - Registered service class org.openrdf.sail.inferencer.fc.config.ForwardChainingRDFSInferencerFactory
16:46:44.859 [main] DEBUG org.openrdf.sail.config.SailRegistry - Registered service class org.openrdf.sail.inferencer.fc.config.DirectTypeHierarchyInferencerFactory
16:46:44.859 [main] DEBUG org.openrdf.sail.config.SailRegistry - Registered service class org.openrdf.sail.inferencer.fc.config.CustomGraphQueryInferencerFactory
16:46:44.859 [main] DEBUG org.openrdf.sail.config.SailRegistry - Registered service class org.openrdf.sail.memory.config.MemoryStoreFactory
16:46:44.859 [main] DEBUG org.openrdf.sail.config.SailRegistry - Registered service class org.openrdf.sail.nativerdf.config.NativeStoreFactory
16:46:44.859 [main] DEBUG org.openrdf.sail.config.SailRegistry - Registered service class org.openrdf.sail.rdbms.config.RdbmsStoreFactory
16:46:44.875 [main] DEBUG org.openrdf.sail.memory.MemoryStore - Initializing MemoryStore...
16:46:44.875 [main] DEBUG org.openrdf.sail.memory.MemoryStore - MemoryStore initialized
16:46:44.876 [main] DEBUG o.openrdf.sail.nativerdf.NativeStore - Initializing NativeStore...
16:46:44.876 [main] DEBUG o.openrdf.sail.nativerdf.NativeStore - Data dir is C:\RDF triples\univData1
16:46:44.970 [main] DEBUG o.openrdf.sail.nativerdf.NativeStore - NativeStore initialized
Executing the query
The database opened successfully
16:46:45.735 [main] DEBUG o.o.query.parser.QueryParserRegistry - Registered service class org.openrdf.query.parser.serql.SeRQLParserFactory
16:46:45.751 [main] DEBUG o.o.query.parser.QueryParserRegistry - Registered service class org.openrdf.query.parser.sparql.SPARQLParserFactory
Result count: 0 in 0.000000s.



My problem is:
1. I changed the SPARQL query many times but still retrieving 0 rows.
2. So, Does OpenRDF Sesame connect to backend DB like PostgreSQL, MySQL, etc?
3. If so, Does OpenRDF Sesame translate SPARQL query to SQL then bring results from the backend DB?

Thanks in Advance.


Solution

  • First, answers to your specific questions, in order:

    1. if the query gives no results, that means that either the repository over which you're executing it is empty, or the query you're trying to execute matches no data in that repository. Since it looks like the way in which you set up and initialize your repository is completely wrong (see remarks below), it is probably empty.

    2. in general, yes, Sesame can connect to a PostgreSQL or MySQL database for storage and query. However, in your code this is not done, because you are not using a Sesame RDBMSStore as your SAIL storage backend, but are using a MemoryStore (which, as the name implies, is an in-memory database).

    3. If you were using a Sesame PostgreSQL/MySQL store, then yes, it would translate SPARQL queries to SQL queries. But you're not using it. Also, the Sesame PostgreSQL/MySQL support is now deprecated - it's recommended not to use it, but instead a NativeStore or MemoryStore or any one of the many available third-party Sesame store implementations .

    More generally, looking at your code, it is unclear what you're trying to accomplish, and I cannot believe your code actually compiles, let alone runs.

    You're using a class RDFRepository in there somewhere, which doesn't exist in Sesame 2, and a method initializeRepository which you give a directory, which also does not exist. It looks vaguely like how things worked in Sesame 1, but that version of Sesame has been out commission for at least 6 years now.

    Then you have a method getConnection which sets up a connection to a PostgreSQL database, but that method doesn't accomplish anything - it just creates a Connection object but then nothing is ever done with that Connection.

    I recommend that you go back to basics and have a good look through the Sesame documentation, especially the tutorial, and the chapter on Programming with Sesame, which explains how to create and manage repositories and how to work with them.