Update network database with new data from Intermine/SGD

[kdahlquist]

It's becoming time to update the network backend database from Intermine/SGD. Note that the previous versions were updated in 3/08/2022 and 3/07/2023, so targeting the beginning of March 2024 would keep us on track for yearly updates.

[kdahlquist]

Also the PPI database which was last updated 3/29/2023

[dondi]

Here is the documentation and script files location: https://github.com/dondi/GRNsight/tree/master/database

Best-case scenario is that the scripts run without issue on the latest downloaded data and the major step will be putting this into production

Other end is that the scripts may need revision due to changes in data format or content

[ntran18]

I found the script for updating the network backend but not for PPI. I will test my local database first and then update the production database. However, this task might not be finished before Tuesday's meeting.

[ntran18]

I don't know how to connect to production database. This is for network database, I'm able to get my local database updated

DB_URL="postgresql://postgres:@https://grnsight.cs.lmu.edu/server/postgres" python3 generate_new_network_version.py --> This error means I didn't provide the port?

DB_URL="postgresql://postgres:@grnsight2.cfimp3lu6uob.us-west-1.rds.amazonaws.com/postgres" python3 generate_new_network_version.py

--> The arrow suggested that I didn't provide password

[dondi]

Yes I’ll have to recall how to do this 😅 …I suspect this will involve ssh tunneling

[dondi]

We’ll need to review the existing documentation for information about:

• Loading the production database
• Updating version information so that the new data set appears in addition to existing versions (2022/2023 for GRN; currently just 2023 for PPI)

Once we have this information ready (or possibly written if we don’t locate existing documentation), @dondi can sit with @ntran18, @ceciliazaragoza, and @akaiap to show them how to access the production database via ssh tunnel

[ntran18]

I updated the network database; however, protein-protein interaction still needs the script to update the network. The script for protein-protein interactions just downloads data from the internet and adds it to the table. If I populate new data to the data, it violates some genes already present in the table.

[dondi]

During a meeting with @ntran18 and @ceciliazaragoza, @dondi went over the process of creating an SSH tunnel in order to communicate with our production database server, which is within a private AWS network. This was how the production database got updated; further @ntran18 wrote up a wiki page documenting this technique: https://github.com/dondi/GRNsight/wiki/Database-Update-Instruction#update-network-database-in-production-database

[dondi]

@ntran18 will review where we left off here (looks like mainly a PPI question and not GRN) to determine next steps. However, we will not wait on this issue for a production release

[ntran18]

1077 Have the instructions to update the database. I will have a session with @dondi to update the database, then the issue should be fixed.

[dondi]

Effectively updated the database as of v7.1.1 release