2

I am writing an app using shiny where users can add, edit or copy data. The idea is that after they have entered there data in the temporary table it is uploaded to the main database and the table is reset.

I have made a table using RSQLite with the following code: 

    library(RSQLite)
    library(pool)
    library(DBI)

    pool <- dbPool(RSQLite::SQLite(), dbname = "db.sqlite")

    df <- data.frame( name=character(),
                      group=character(),
                      stringsAsFactors = FALSE)

    dbWriteTable(pool, "#df_temp", df, temporary = TRUE, overwrite = TRUE)

The problem is that if multiple users are using the app at the same time all data is added to the same temporary table. Is there a way to make a temporary table that is specific for each session?

Nivel
  • 629
  • 4
  • 12

1 Answers1

2

Your table design should be changed to include a column which keeps track of the particular session to which the data belongs. Here is one option:

library(uuid)

df <- data.frame(sessionId=character(),
                 name=character(),
                 group=character(),
                 stringsAsFactors=FALSE)

# populate the data frame

# assign a UUID (unique identifier) for this particular R user session
sessionId = uuid()
df$sessionId = sessionId
dbWriteTable(pool, "user_data", df, overwrite=FALSE, append=TRUE)

At the end of the session, you may delete all records which were created:

conn <- dbConnect(RSQLite::SQLite(), "your.db")
sql <- paste0("DELETE FROM user_data WHERE uuid = ", sessionId)
dbExecute(conn, sql)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Great thanks a lot! I will try it out. How do you delete the data in the table on session end? – Nivel Jul 04 '19 at 10:41
  • @Nivel You would need to do a `DELETE`. Just to be clear, I am advocating _against_ using a temp table, but rather just using a single table which contains a column to keep track of whose session it is. – Tim Biegeleisen Jul 04 '19 at 10:47
  • Ok I will try it! I was wondering how to trigger the DELETE on session end but I found the answer. It is described here: https://stackoverflow.com/questions/49178159/how-to-delete-a-file-created-by-a-shiny-app-when-the-ssession-closes – Nivel Jul 04 '19 at 10:52
  • Glad that you were able to resolve your problem :-) – Tim Biegeleisen Jul 04 '19 at 10:53
  • I get the following error when I use the SQL quary: Error in result_create: unrecognized token: "6c2d0c74". It seems to have trouble recognizing the "-" in the uuids. Would you happen to know how to solve this? – Nivel Jul 04 '19 at 17:20
  • This error looks like you are trying to create a _table_ using a UUID as a name. This is not what my answer says to do nor is it what I recommend. – Tim Biegeleisen Jul 05 '19 at 01:12
  • @TimBiegeleisen Shiny by default provides an identifier for each session: `session$token` which can be used instead of `uuid()`. See [this](https://stackoverflow.com/a/55221062/9841389) for an example (click bookmark...). – ismirsehregal Jul 05 '19 at 11:40