Unable to see reactome.db dbschema
1
0
Entering edit mode
@lluis-revilla-sancho
Last seen 6 days ago
European Union

I can't access the database schema of the reactome.db package:

> library("reactome.db")
> reactome_dbschema()

Warning messages:
1: In file(con, "r") :
  file("") only supports open = "w+" and open = "w+b": using the former
2: In max(ii) : no non-missing arguments to max; returning -Inf
> reactome_dbschema(show.indices=TRUE)

Warning message:
In file(con, "r") :
  file("") only supports open = "w+" and open = "w+b": using the former
#I found the following schema, but I can't read it 
> reactome_dbschema("R/x86_64-pc-linux-gnu-library/3.3/reactome.db/DBschemas/schemas_1.0/REACTOME_DB.sql")

Warning messages:
1: In file(con, "r") :
  file("") only supports open = "w+" and open = "w+b": using the former
2: In max(ii) : no non-missing arguments to max; returning -Inf
# And it doesn't seem to be up to date:
> con <- reactome_dbconn()
> query <- "SELECT COUNT(DISTINCT reactome_id) AS total_react FROM pathway2gene"
> res <- dbGetQuery(con, query)
Error in rsqlite_send_query(conn@ptr, statement) :
  no such column: reactome_id
# I could retrieve the actual schema by using the following:
> a <- dbGetQuery(con, "select * from sqlite_master")
# Which shows that the name of the columns has changed
> a[a$name == "pathway2gene", ]
     type         name     tbl_name rootpage
761 table pathway2gene pathway2gene  2173887
                                                                                                                                                                 sql
761 CREATE TABLE pathway2gene (\n  DB_ID VARCHAR(10) NOT NULL,                  -- Reactome DB_ID\n  gene_id VARCHAR(10) NOT NULL                  -- Entrez Gene\n)
> sessionInfo()

R version 3.3.2 (2016-10-31)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 14.04.5 LTS

locale:
[1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C            
[3] LC_TIME=es_ES.UTF-8        LC_COLLATE=en_US.UTF-8  
[5] LC_MONETARY=es_ES.UTF-8    LC_MESSAGES=en_US.UTF-8 
[7] LC_PAPER=es_ES.UTF-8       LC_NAME=C               
[9] LC_ADDRESS=C               LC_TELEPHONE=C          
[11] LC_MEASUREMENT=es_ES.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] parallel  stats4    stats     graphics  grDevices utils     datasets
[8] methods   base     

other attached packages:
[1] reactome.db_1.58.0   AnnotationDbi_1.36.1 IRanges_2.8.1     
[4] S4Vectors_0.12.1     Biobase_2.34.0       BiocGenerics_0.20.0
[7] BiocInstaller_1.24.0

loaded via a namespace (and not attached):
[1] DBI_0.5-1     tools_3.3.2   memoise_1.0.0 Rcpp_0.12.9   RSQLite_1.1-2
[6] digest_0.6.11

 

reactome.db rsqlite • 1.3k views
ADD COMMENT
1
Entering edit mode
@willemligtenberg-6989
Last seen 6.4 years ago
Netherlands

I think you have actually answered your own question. And actually found a bug. :)

To get the current schema you are probably better off using sqlite (and then the .schema command) directly on the sqlite file.

A while ago the schema was changed, and apparently, I did not update it in the package.
Currently, this package is developed in a two pronged way.
I create the mappings, and Valerie adds the full database dump.

There are too many half-manual steps at the moment, and it was already on my todo list to combine all the steps and to make sure we can automate this as much as possible. I will also add a test to check if the schema definition is current.

ADD COMMENT

Login before adding your answer.

Traffic: 787 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6