[R-pkgs] ODB : connecting OpenOffice Base with R

[Sylvain Mareschal]

The recently released "ODB" package was developped to manage HSQL 
databases embedded in .odb files (the default when creating a database 
with OpenOffice Base) via R.



BRIEFLY

The goal of this package is to access OpenOffice databases via R, to 
process data stored in it or to automatize their building from scratch 
or updating.

The package provides 5 main functions :
- odb.create, to create a new .odb file from a template.
- odb.open, to produce an "odb" connection to a temporary copy of the 
.odb file.
- odb.close, to close the connection and update the .odb file.
- odb.read, to import data from the database to R via "SELECT" SQL 
queries built by the useR.
- odb.write, to update the database via "INSERT" or "CREATE" SQL queries 
built by the useR.

A few other functions are also provided to manage .odb specificties such 
as comments on table fields and stored queries. Some wrappers are also 
provided to insert directly a data.frame in a database table without 
writing the SQL query, list the table names ands fields or export the 
database in a .sql file.

Other wrappers may be added in future versions to help users not 
familiar with the SQL language.



TYPICAL USE

connection <- odb.open("file.odb")
data <- odb.read(connection, "SELECT * FROM table WHERE id < 15")
odb.write(connection, "UPDATE table SET field='peach' WHERE id = 5")
odb.close(connection)



TECHNICAL CONSIDERATIONS

.odb files, as any other OpenDocument files, are ZIP archives containing 
the HSQL files. To establish the connection, the .odb file is unzipped 
via the "zip" bash command if available, and the connection is made via 
the RJDBC interface. The "odb" object produced inherits from the 
"DBIConnection" class, thus all functions provided in the DBI packages 
may be used directly on it to manage the database. The odb.read and 
odb.write functions are only wrappers to such DBI functions, handling 
frequent issues such as charset or factors considerations.

Notice the database files are copied in a temporary directory, thus any 
update made to the database is not written in the .odb file untill the 
odb.close call, so simultaneous access to a same database (via R and 
OpenOffice) should not be considered.



Any suggestion or comment may be sent back to this email adress.

Sylvain Mareschal