Computational Management of Data Project by ComputerGals 💗 ✨ 🌸 🌟 💞

Silvia Baldassarri, Alice Machieraldo, Elena Mocci 🐈‍⬛ 🍑 🐠

Exam project for the Computational Management of Data course: a system that integrates bibliographic data (publication metadata) and citation data (citation relationships between publications), stored respectively in a relational database (SQLite, queryable via SQL) and a graph database (RDF, queryable via SPARQL on Blazegraph). The project exposes a query engine that combines the results of the two databases, returning Python objects compliant with the project's UML model (BibliographicEntity, Citation, AuthorSelfCitation, JournalSelfCitation, etc.).

File structure 💓

cmd-project/
├── classes.py          # Data model classes (IdentifiableEntity, BibliographicEntity, Citation, AuthorSelfCitation, JournalSelfCitation)
├── handlers.py          # Upload and query handlers, for both the relational DB (SQLite) and the graph DB (SPARQLWrapper + rdflib)
├── basic_full_qe.py     # BasicQueryEngine and FullQueryEngine: combine the results of the various handlers into model objects
├── impl.py              # Single entry point: re-exports all the classes from the files above
├── test.py              # Tests (unittest) that check the code complies with the specification
├── data/                # Source files to load into the databases (citations CSV, bibliographic metadata JSON)
├── relational.db         # SQLite database generated by the upload handlers
├── requirements.txt      # Project's Python dependencies
└── .gitignore

IMPORTANT: prerequisites and setup 💫

Prerequisites

• Python 3.10+
• Java (only needed to run Blazegraph)
• Blazegraph: the graph database on which citations are loaded and queried. Download the blazegraph.jar file from the releases page and place it in the project folder (or any path of your choice).

1. Clone the project and create the virtual environment

git clone https://github.com/elemocc/cmd-project.git
cd cmd-project

python -m venv .venv
source .venv/bin/activate      # on Windows: .venv\Scripts\activate

2. Install the dependencies

pip install -r requirements.txt

This installs pandas, rdflib and SPARQLWrapper (plus their indirect dependencies such as numpy, pyparsing, python-dateutil).

3. Start Blazegraph

In a separate terminal, from the folder where you placed blazegraph.jar:

java -server -Xmx1g -jar blazegraph.jar

Leave this terminal open: Blazegraph needs to keep running for the whole working session. By default it exposes the SPARQL endpoint at:

http://127.0.0.1:9999/blazegraph/sparql

You can check that it's running by opening that address (without /sparql, i.e. http://127.0.0.1:9999/blazegraph/) in a browser: you should see the Blazegraph web interface.

4. Load data into the databases

Before you can query anything, both databases need to be populated. Open Python (or a script) in the project folder:

from impl import BibliographicEntityUploadHandler, CitationUploadHandler

# Populate the relational database (SQLite) with the bibliographic metadata
rel_path = "relational.db"
be = BibliographicEntityUploadHandler()
be.setDbPathOrUrl(rel_path)
be.pushDataToDb("data/dh_metadata.json")

# Populate the graph database with the citations (requires Blazegraph to already be running)
grp_endpoint = "http://127.0.0.1:9999/blazegraph/sparql"
cit = CitationUploadHandler()
cit.setDbPathOrUrl(grp_endpoint)
cit.pushDataToDb("data/dh_citations.csv")

This step needs to be repeated every time you want to load a new file, or the first time you set up the project.

5. Create the query handlers and query the data

from impl import BibliographicEntityQueryHandler, CitationQueryHandler, FullQueryEngine

be_qh = BibliographicEntityQueryHandler()
be_qh.setDbPathOrUrl(rel_path)

cit_qh = CitationQueryHandler()
cit_qh.setDbPathOrUrl(grp_endpoint)

engine = FullQueryEngine()
engine.addBibliographicEntityHandler(be_qh)
engine.addCitationHandler(cit_qh)

# Query examples
all_citations = engine.getAllCitations()
ml_papers = engine.getBibliographicEntitiesWithTitle("Machine learning")
recent_citations = engine.getCitationsWithinTimespan("P1Y", "P5Y")

The result of every method is a list of Python objects (e.g. BibliographicEntity, Citation), not a DataFrame: you can therefore directly use methods like getTitle(), getAuthors(), getCitingEntity(), etc., defined in classes.py.

Using data files other than the ones in data/ 🌺

The project isn't tied to the sample files: you can load any file, as long as it follows the same format as the ones in data/.

• Bibliographic metadata: a .json file containing a list of objects, each with the fields title, author (a list of strings), pub_date, venue, id (a list of identifiers, e.g. doi:..., isbn:..., omid:br/...).
• Citations: a .csv file with the columns oci, citing, cited, creation, timespan, journal_sc, author_sc (the last two with value "yes" or empty).

To use your own file instead of the sample one, just change the path passed to pushDataToDb:

be.pushDataToDb("data/my_metadata.json")
cit.pushDataToDb("data/my_citations.csv")

A few things to keep in mind:

• pushDataToDb overwrites the tables of the relational database (if_exists="replace"): if you want to add data to what's already there instead of replacing it, you'll need to merge the source files before loading them, or modify the logic of BibliographicEntityUploadHandler.
• On the graph database, on the other hand, every call to pushDataToDb adds new triples without deleting the ones already loaded into Blazegraph: so you can call the method multiple times with different files to accumulate citations from multiple sources.
• If you want to start fresh with the graph database, the simplest solution is to restart Blazegraph with a clean namespace/journal (e.g. by deleting the generated .jnl file, if present).

Running the tests 💖

The tests in test.py check that the code is runnable and compliant with the project's UML model. Before running them, make sure Blazegraph is running and that the files data/dh_citations.csv and data/dh_metadata.json exist:

python -m unittest test.py