Migration to Virtuoso (or GraphDB)

Install virtuoso in servers

Download latest version

https://github.com/openlink/virtuoso-opensource/releases/download/v7.2.14/virtuoso-opensource.x86_64-generic_glibc25-linux-gnu.tar.gz

vos.openlinksw.com

Extract it in the folder /srv/ontoportal/data/virtuoso

tar -xvzf file.tar.gz

Configure it, in the folder database , create a file virtuoso.ini , adapt NumberOfBuffers and MaxDirtyBuffers ****depending of the RAM of the server (ask chatgpt to help give the best number)

cd virtuoso-opensource
cp database/virtuoso.ini.sample database/virtuoso.ini

You may need to change the config file to replace all references from ../ to ./

You can edit this part depending of your RAM

;;
;; When running with large data sets, one should configure the Virtuoso
;; process to use between 2/3 to 3/5 of free system memory and to stripe
;; storage on all available disks.
;;
;; Uncomment next two lines if there is 2 GB system memory free
;NumberOfBuffers          = 170000
;MaxDirtyBuffers          = 130000
;; Uncomment next two lines if there is 4 GB system memory free
;NumberOfBuffers          = 340000
; MaxDirtyBuffers          = 250000
;; Uncomment next two lines if there is 8 GB system memory free
;NumberOfBuffers          = 680000
;MaxDirtyBuffers          = 500000
;; Uncomment next two lines if there is 16 GB system memory free
NumberOfBuffers          = 1360000
MaxDirtyBuffers          = 1000000
;; Uncomment next two lines if there is 32 GB system memory free
;NumberOfBuffers          = 2720000
;MaxDirtyBuffers          = 2000000
;; Uncomment next two lines if there is 48 GB system memory free
;NumberOfBuffers          = 4000000
;MaxDirtyBuffers          = 3000000
;; Uncomment next two lines if there is 64 GB system memory free
;NumberOfBuffers          = 5450000
;MaxDirtyBuffers          = 4000000
;;
;; Note the default settings will take very little memory
;; but will not result in very good performance
;;
;;NumberOfBuffers          = 10000
;;MaxDirtyBuffers          = 6000

You neeed also to edit this part to set a bigger ResultSetMaxRows

[SPARQL]
...
ResultSetMaxRows                = 100000000000

Run virtuoso

sudo bin/virtuoso-t +configfile ./database/virtuoso.ini +foreground

Configure CRON

Edit config/onfig.rb to put this instead

GOO_BACKEND_NAME =  "virtuoso"
GOO_HOST =  "localhost"
GOO_PATH_DATA =  "/sparql"
GOO_PATH_QUERY =  "/sparql"
GOO_PATH_UPDATE =  "/sparql"
GOO_PORT =  8890

To restart only the ncbo cron you can do

sudo systemctl restart ncbo_cron
# or sudo oprestart to restart everything

Run Migration scripts

the migration require this version of ncbo_cron https://github.com/ontoportal-lirmm/ncbo_cron/releases/tag/v2.3.0

  • 0- Create metadata graphs file

    nano ./metadata_graphs

    Put the following inside

    http://data.bioontology.org/metadata/Base
http://data.bioontology.org/metadata/Category
http://data.bioontology.org/metadata/Contact
http://data.bioontology.org/metadata/Details
http://data.bioontology.org/metadata/ExternalMappings
http://data.bioontology.org/metadata/Group
http://data.bioontology.org/metadata/InterportalMappings/ncbo
http://data.bioontology.org/metadata/InterportalMappings/sifr
http://data.bioontology.org/metadata/MappingCount
http://data.bioontology.org/metadata/MappingProcess
http://data.bioontology.org/metadata/Metrics
http://data.bioontology.org/metadata/Note
http://data.bioontology.org/metadata/NotificationType
http://data.bioontology.org/metadata/Ontology
http://data.bioontology.org/metadata/OntologyFormat
http://data.bioontology.org/metadata/OntologySubmission
http://data.bioontology.org/metadata/OntologyType
http://data.bioontology.org/metadata/Project
http://data.bioontology.org/metadata/ProvisionalClass
http://data.bioontology.org/metadata/Reply
http://data.bioontology.org/metadata/RestBackupMapping
http://data.bioontology.org/metadata/Review
http://data.bioontology.org/metadata/Role
http://data.bioontology.org/metadata/Slice
http://data.bioontology.org/metadata/SubmissionStatus
http://data.bioontology.org/metadata/Subscription
http://data.bioontology.org/metadata/User
http://www.w3.org/ns/adms#Identifier
http://xmlns.com/foaf/0.1/Agent

  • 1- Create a 4store dump

    sudo 4s-dump http://localhost:8081/sparql/ -o ./data -f <metadata_graphs>

    will generate ./data folder

  • 2- Convert the 4store dump to graphs file dump

    bin/migrations/4s-to-graph-files <path to generated data foldder>  .

  • 3- Grant write access

    bin/migrations/virtuoso/virtuso_grant_write_permission.sh  /srv/ontoportal/data/virtuoso-opensource

  • 3- Import the graphs file into graph db

     bin/migrations/import_metadata_graphs_to_store ./processed_files

  • 4- Check if the triple count corresponds to the count in the dump files

    bundle exec ruby bin/migrations/compare_counts.rb ./processed_files

    will generate a csv called graph_comparaison.csv

Process latest submission RDF

The Triplestore is empty at this state so you need to run

bin/ncbo_cron --console

Detect ontologies with no data, using ncbo_cron console

to run it, got to /srv/ontoportal/ncbo_cron/ and run the command bin/ncbo_cron --console


subs = LinkedData::Models::Ontology.all.map{|x| x.latest_submission(status: :any) }

copy and paste this function

 def graph_count_triples(graph)
      query = <<-eos
            SELECT (COUNT(?s) as ?count) WHERE {
            GRAPH <#{graph}> {
              ?s ?p ?v
            }}
      eos
      count = 0
      begin
        rs = Goo.sparql_query_client.query(query)
        rs.each_solution do |sol|
          count = sol[:count].object
        end
      end
      count
 end
subs = LinkedData::Models::Ontology.all.map{|x| x.latest_submission(status: :any)}
subs.map{|x| [x.id, graph_count_triples(x.id)]}.to_h

fix the status issues

subs = LinkedData::Models::Ontology.all.map{|x| x.latest_submission(status: :any).bring_remaining }
ready_status = LinkedData::Models::SubmissionStatus.all.select{|x| !x.id.to_s['ERROR_'] && !x.id['ARCHIVED']}

subs.each{|s| ready_status.each{|st| s.add_submission_status(st)} ; s.valid? ? s.save : puts('error in '+ s.id.to_s) }

Add virtuoso.service

Set Correct Permissions

sudo groupadd virtuoso
sudo useradd -r -g virtuoso -d /home/virtuoso -s /sbin/nologin virtuoso
sudo usermod -s /bin/bash virtuoso
sudo chown -R virtuoso:virtuoso /srv/ontoportal/data/virtuoso-opensource
sudo chmod -R 750 /srv/ontoportal/data/virtuoso-opensource

Open a terminal and use a text editor to create a new service file:

sudo nano /etc/systemd/system/virtuoso.service

Add the following content to define the service:

[Unit]
Description=Virtuoso Database Server
After=network.target

[Service]
Type=simple
StandardOutput=syslog
StandardError=syslog
User=virtuoso
Group=virtuoso
WorkingDirectory=/srv/ontoportal/data/virtuoso-opensource
ExecStart=/srv/ontoportal/data/virtuoso-opensource/bin/virtuoso-t +configfile /srv/ontoportal/data/virtuoso-opensource/database/virtuoso.ini +f
ExecStop=/srv/ontoportal/data/virtuoso-opensource/bin/isql "EXEC=shutdown;"

[Install]
WantedBy=multi-user.target
sudo systemctl daemon-reload
sudo systemctl enable virtuoso
sudo systemctl start virtuoso

Update opstart, opstop and opstatus

change the following files by replace the 4store lines starting with 4s- with virtuoso.service

sudo nano  /usr/local/bin/opstart
sudo nano  /usr/local/bin/opstop
sudo naon /usr/local/bin/optatus
sudo systemctl stop 4s-*
sudo opstart 
sudo opstatus
# you shoudl see all services including virtuoso as active

Configure ontologies_api

Edit config/envrionment/appliance.rb to put instead

[GOO_BACKEND_NAME =  "virtuoso"
GOO_HOST =  "localhost"
GOO_PATH_DATA =  "/sparql"
GOO_PATH_QUERY =  "/sparql"
GOO_PATH_UPDATE =  "/sparql"
GOO_PORT =  8890](https://www.notion.so/BioDivPortal-migration-to-2-8-0-ea6dd373426f48ff9c71c03237bb80f1?pvs=21)

to restart only the API you can do

sudo systemctl restart unicorn
# or do sudo oprestart to restart everything

(Optional) opening the SPARQL endpoint for public

this is not recommended as everyone will have read and write with no security

Opening the port 8890 we make available the triple store

[root@stageportal virtuoso-opensource]# sudo iptables -I INPUT 1 -p tcp --dport 8890 -j ACCEPT
[root@stageportal virtuoso-opensource]# sudo service iptables save


(Optional) if want to use GraphDB or Allegrograph

You need just to adapt the configure CRON and ontologies_api , steps as follow

if you want GraphDB

# Update ncbo_cron config/config.rb and ontologies_api config/envrionment/appliance.rb with 
GOO_BACKEND_NAME="graphdb"
GOO_PORT="7200"
GOO_PATH_QUERY="/repositories/ontoportal"
GOO_PATH_DATA="/repositories/ontoportal/statements"
GOO_PATH_UPDATE="/repositories/ontoportal/statements"

if you want Allegrograph

#update ncbo_cron config/config.rb and ontologies_api config/envrionment/appliance.rb with 
GOO_BACKEND_NAME="allegrograph"
GOO_PORT="10035"
GOO_PATH_QUERY="/repositories/ontoportal_test"
GOO_PATH_DATA="/repositories/ontoportal_test/statements"
GOO_PATH_UPDATE="/repositories/ontoportal_test/statements"




Configure UI sparql proxy

update ui configuration in config/bioportal_config_appliance.rb

by updating or adding this line

$SPARQL_URL = "http://localhost:8890/sparql" # for virtuoso
 # adpat the url for the others triples store spending where port/path they put the open spaql endpoint