Compress string memCompress/Decompress

#create string, compress with gzip, convert to SQLite blob string
string <- "gzip this string, store as blob in SQLite database"
string.gz <- memCompress(string, type="gzip")
string.sqlite <- paste("x'",paste(string.gz,collapse=""),"'",sep="")

#create database and table with a BLOB column
library(RSQLite)

con <- dbConnect(dbDriver("SQLite"), "compress.sqlite")
dbGetQuery(con, "CREATE TABLE Compress (id INTEGER, data BLOB);")

#insert the string as a blob
query <- paste("INSERT INTO Compress (id, data) VALUES (1, ", 

dbGetQuery(con, query)

#recover the blob, decompress, and convert back to a string
result <- dbGetQuery(con, "SELECT data FROM Compress;")
string.gz <- result[[1]][[1]]
string <- memDecompress(string.gz, type="gzip")
rawToChar(string)

Hello,

I would like to compress a long string (character vector), store the compressed string in the text field of a SQLite database (using RSQLite), and then load the text back into memory and decompress it back into the the original string.  My character vector can be compressed considerably using standard gzip/bzip2 compression.  In theory it should be much faster for me to compress/decompress a long string than to write the whole string to the hard drive and then read it back (not to mention the saved hard drive space).

I have tried accomplishing this task using memCompress() and memDecompress() without success.  It seems memCompress can only convert a character vector to raw type which cannot be treated as a string.  Does anyone have ideas on how I can go about doing this, especially using the standard base packages?

Thanks!,
Erik

sessionInfo()

R version 2.11.0 (2010-04-22) 
x86_64-apple-darwin9.8.0 

locale:
[1] en_US.UTF-8/en_US.UTF-8/C/C/en_US.UTF-8/en_US.UTF-8

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

loaded via a namespace (and not attached):
[1] tools_2.11.0

Erik, 

Can you store the data as a blob? For example:

#create string, compress with gzip, convert to SQLite blob string
string <- "gzip this string, store as blob in SQLite database"
string.gz <- memCompress(string, type="gzip")
string.sqlite <- paste("x'",paste(string.gz,collapse=""),"'",sep="")

#create database and table with a BLOB column
library(RSQLite)

Loading required package: DBI

con <- dbConnect(dbDriver("SQLite"), "compress.sqlite")
dbGetQuery(con, "CREATE TABLE Compress (id INTEGER, data BLOB);")

NULL

#insert the string as a blob
query <- paste("INSERT INTO Compress (id, data) VALUES (1, ", 

+ string.sqlite, ");", sep="")

dbGetQuery(con, query)

NULL

#recover the blob, decompress, and convert back to a string
result <- dbGetQuery(con, "SELECT data FROM Compress;")
string.gz <- result[[1]][[1]]
string <- memDecompress(string.gz, type="gzip")
rawToChar(string)

[1] "gzip this string, store as blob in SQLite database"


-Matt



On Fri, 2010-07-09 at 12:51 -0400, Erik Wright wrote:

Hello,

I would like to compress a long string (character vector), store the compressed string in the text field of a SQLite database (using RSQLite), and then load the text back into memory and decompress it back into the the original string.  My character vector can be compressed considerably using standard gzip/bzip2 compression.  In theory it should be much faster for me to compress/decompress a long string than to write the whole string to the hard drive and then read it back (not to mention the saved hard drive space).

I have tried accomplishing this task using memCompress() and memDecompress() without success.  It seems memCompress can only convert a character vector to raw type which cannot be treated as a string.  Does anyone have ideas on how I can go about doing this, especially using the standard base packages?

Thanks!,
Erik

sessionInfo()

R version 2.11.0 (2010-04-22) 
x86_64-apple-darwin9.8.0 

locale:
[1] en_US.UTF-8/en_US.UTF-8/C/C/en_US.UTF-8/en_US.UTF-8

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

loaded via a namespace (and not attached):
[1] tools_2.11.0

-- 
Matthew S. Shotwell
Graduate Student
Division of Biostatistics and Epidemiology
Medical University of South Carolina
http://biostatmatt.com

Hi Matt,

This works great, thanks!

At first I got an error message saying BLOB is not implemented in RSQLite.  When I updated to the latest version it worked.

Is there any reason the string needs to be stored as type BLOB?  It seems to work the same when I swap "BLOB" with "TEXT" in the CREATE TABLE command.

Thanks again!,
Erik



On Jul 9, 2010, at 3:21 PM, Matt Shotwell wrote:

Erik, 

Can you store the data as a blob? For example:

#create string, compress with gzip, convert to SQLite blob string
string <- "gzip this string, store as blob in SQLite database"
string.gz <- memCompress(string, type="gzip")
string.sqlite <- paste("x'",paste(string.gz,collapse=""),"'",sep="")

#create database and table with a BLOB column
library(RSQLite)

Loading required package: DBI

con <- dbConnect(dbDriver("SQLite"), "compress.sqlite")
dbGetQuery(con, "CREATE TABLE Compress (id INTEGER, data BLOB);")

NULL

#insert the string as a blob
query <- paste("INSERT INTO Compress (id, data) VALUES (1, ", 

+ string.sqlite, ");", sep="")

dbGetQuery(con, query)

NULL

#recover the blob, decompress, and convert back to a string
result <- dbGetQuery(con, "SELECT data FROM Compress;")
string.gz <- result[[1]][[1]]
string <- memDecompress(string.gz, type="gzip")
rawToChar(string)

[1] "gzip this string, store as blob in SQLite database"


-Matt



On Fri, 2010-07-09 at 12:51 -0400, Erik Wright wrote:

Hello,

I would like to compress a long string (character vector), store the compressed string in the text field of a SQLite database (using RSQLite), and then load the text back into memory and decompress it back into the the original string.  My character vector can be compressed considerably using standard gzip/bzip2 compression.  In theory it should be much faster for me to compress/decompress a long string than to write the whole string to the hard drive and then read it back (not to mention the saved hard drive space).

I have tried accomplishing this task using memCompress() and memDecompress() without success.  It seems memCompress can only convert a character vector to raw type which cannot be treated as a string.  Does anyone have ideas on how I can go about doing this, especially using the standard base packages?

Thanks!,
Erik

sessionInfo()

R version 2.11.0 (2010-04-22) 
x86_64-apple-darwin9.8.0 

locale:
[1] en_US.UTF-8/en_US.UTF-8/C/C/en_US.UTF-8/en_US.UTF-8

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

loaded via a namespace (and not attached):
[1] tools_2.11.0

-- 
Matthew S. Shotwell
Graduate Student
Division of Biostatistics and Epidemiology
Medical University of South Carolina
http://biostatmatt.com

On Fri, 2010-07-09 at 20:02 -0400, Erik Wright wrote:

Hi Matt,

This works great, thanks!

At first I got an error message saying BLOB is not implemented in RSQLite. ?When I updated to the latest version it worked.

SQLite began to support BLOBs from version 3.0.

On Sun, Jul 11, 2010 at 11:31 AM, Matt Shotwell <shotwelm at musc.edu> wrote:

On Fri, 2010-07-09 at 20:02 -0400, Erik Wright wrote:

Hi Matt,

This works great, thanks!

At first I got an error message saying BLOB is not implemented in RSQLite.  When I updated to the latest version it worked.

SQLite began to support BLOBs from version 3.0.

And RSQLite began supporting BLOBs only just recently :-)
See the NEWS file for details.

Below is a minimal example of how you might use BLOBs:

   db <- dbConnect(SQLite(), dbname = ":memory:")
   dbGetQuery(db, "CREATE TABLE t1 (name TEXT, data BLOB)")

   z <- paste("hello", 1:10)
   df <- data.frame(a = letters[1:10],
                    z = I(lapply(z, charToRaw)))
   dbGetPreparedQuery(db, "insert into t1 values (:a, :z)", df)
   a <- dbGetQuery(db, "select name from t1")
   checkEquals(10, nrow(a))
   a <- dbGetQuery(db, "select data from t1")
   checkEquals(10, nrow(a))
   a <- dbGetQuery(db, "select * from t1")
   checkEquals(10, nrow(a))
   checkEquals(2, ncol(a))

   checkEquals(z, sapply(a$data, rawToChar))
   dbDisconnect(db)


-- 
Seth Falcon | @sfalcon | http://userprimary.net/

Hi Seth,

Can you recreate the example below using dbWriteTable?