# Migration to Virtuoso (or GraphDB) ## Install virtuoso in servers Download latest version [vos.openlinksw.com](https://vos.openlinksw.com/owiki/wiki/VOS) Extract it in the folder `/srv/ontoportal/data/virtuoso` ```jsx 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)` ```bash cd virtuoso-opensource cp database/virtuoso.ini.sample database/virtuoso.ini ``` :::info You may need to change the config file to replace all references from `../` to `./` ::: You can edit this part depending of your RAM ```bash ;; ;; 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` ```ruby [SPARQL] ... ResultSetMaxRows = 100000000000 ``` ## Run `virtuoso` ```jsx sudo bin/virtuoso-t +configfile ./database/virtuoso.ini +foreground ``` ## Configure `CRON` Edit `config/onfig.rb` to put this instead ```jsx 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 ```bash sudo systemctl restart ncbo_cron # or sudo oprestart to restart everything ``` ## Run Migration scripts the migration require this version of ncbo_cron * **0- Create metadata graphs file** ```jsx nano ./metadata_graphs ``` Put the following inside ```jsx 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 ``` * Add this for the new Projects model [`https://schema.org/ResearchProject`](https://schema.org/ResearchProject) * **1- Create a 4store dump** ``` sudo 4s-dump http://localhost:8081/sparql/ -o ./data -f ``` will generate `./data` folder * **2- Convert the 4store dump to graphs file dump** ``` bin/migrations/4s-to-graph-files . ``` * 3- Grant write access ```bash bin/migrations/virtuoso/virtuso_grant_write_permission.sh /srv/ontoportal/data/virtuoso-opensource ``` * **3- Import the graphs file into graph db** ```jsx 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 ```ruby 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` ```ruby subs = LinkedData::Models::Ontology.all.map{|x| x.latest_submission(status: :any) } ``` copy and paste this function ```ruby 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 ``` ```ruby 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 ```ruby 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 ```jsx 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: ```jsx sudo nano /etc/systemd/system/virtuoso.service ``` Add the following content to define the service: ```jsx [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 ``` ```jsx 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` ```ruby sudo nano /usr/local/bin/opstart sudo nano /usr/local/bin/opstop sudo naon /usr/local/bin/optatus ``` ```ruby 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 ```jsx [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 ```bash sudo systemctl restart unicorn # or do sudo oprestart to restart everything ``` ## (Optional) opening the `SPARQL` endpoint for public :::warning this is not recommended as everyone will have read and write with no security ::: Opening the port 8890 we make available the triple store ```jsx [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` ```ruby # 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` ```ruby #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 ```sql $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 ``` --- **Documents** - [💁‍♂️ Acknowledgments](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/acknowledgments-15GdRXLQdm) - [📚Publications and references](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/publications-and-references-87tEoeoGKy) - [🗽Terms & Conditions](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/terms-conditions-naDsDo2Zxq) - [🧞‍♂️Metadata guidelines and recommendations](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/metadata-guidelines-and-recommendations-pOFbK9sIfp) - [📜 AgroPortal Release Notes](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/agroportal-release-notes-btKjZk5tU2) - [ 👩‍🚀 User Guide](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/user-guide-ZszoBwfjGF) - [📎 API Documentation](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/api-documentation-kMCurGKD97) - [🤖 Developer Documentation](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/developer-documentation-LhsrucqGTa) - [🗃External Documentation](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/external-documentation-vNPQCdArrr) - [AgroPortal infrastructure](https://wiki.agroportal.eu/s/e6158eda-c109-4385-852c-51a42de9a412/doc/agroportal-infrastructure-ietsAU4kPs)