[Bioc-devel] Trouble modifying read-only ChipDb SQLite database

[Amanda Hiser]

Hello all,

I�m creating a ChipDb package for a protein-based assay. To generate a template package structure and the SQLite database file, I used `AnnotationForge::makeDBPackage()` with a "HUMANCHIP_DB" schema. I�m now trying to add new tables to the database by modifying the `zzz.R` file, following the instructions in the AnnotationForge vignette and the RSQLite vignette. However, I�m unable to write to the SQLite db file, even after modifying the permissions.

See below for a reprex reproducing the �readonly database� error I�m getting:

```
reprex::reprex({
  suppressPackageStartupMessages(library(AnnotationForge))
  suppressPackageStartupMessages(library(DBI))

  # Preparing for package creation
  df <- data.frame(probeID = c("probe1", "probe2", "probe3"),
                   entrezID = c("9570", "7421", "56"))
  write.table(df, "example_ids.txt", sep = "\t",
              col.names = FALSE, row.names = FALSE, quote = FALSE)

  # Generating package
  suppressMessages(AnnotationForge::makeDBPackage("HUMANCHIP_DB",
                                 affy = FALSE,
                                 prefix = "Example",
                                 fileName = "example_ids.txt",
                                 baseMapType = "eg", # Using Entrez Gene IDs
                                 version = "0.1.0",
                                 manufacturer = "Example",
                                 chipName = "ExampleReprex",
                                 author = "Example Author"))

  # Connecting to database (based on code from package zzz.R file)
  dbfile <- "Example.db/inst/extdata/Example.sqlite"
  dbconn <- AnnotationDbi::dbFileConnect(dbfile)

  # Testing connection
  DBI::dbGetQuery(dbconn, 'SELECT * FROM probes')
  file.access(dbfile, mode = 2) # -1 = failure

  # Modifying permissions of SQLite database file and parent dir
  Sys.chmod("Example.db/inst/extdata/Example.sqlite", "755")
  Sys.chmod("Example.db/inst/extdata/", "755")

  # 0 = success (file is writable)
  file.access(dbfile, mode = 2)
  DBI::dbIsReadOnly(dbconn) # Checking with another method

  new_table <- data.frame(probeID = c("probe1", "probe2", "probe3"),
                          diseaseRisk = c("high", "low", "med"))

  # Attempting to write new table to DB, using code example from RSQLite vignette
  # https://cran.r-project.org/web/packages/RSQLite/vignettes/RSQLite.html
  DBI::dbWriteTable(dbconn, "disease_risk", new_table)
})
```

According to the output of `file.access()` and `dbIsReadOnly()`, I should be able to write to the database. I�ve done some Googling to see if I�m missing a step, and it looks like others frequently run into this problem when either A) the parent directory (in this instance, `extdata`) doesn�t have write permissions, or B) the user attempting to perform the writing action is not the user that was granted writing permissions. I�ve attempted to address both, but neither has solved the problem. I�m still unable to write to the database. Is there a step that I�m missing or some other commonly overlooked factor that could be causing the database to be read-only? What specifically needs to be done to make the �.sqlite� file (in `inst/extdata`) writable?

Thank you!
Amanda
The information contained in this message is intended fo...{{dropped:8}}