ODB (version 1.2.1)

ODB-package: ODB

Description

ODB

Details

The DESCRIPTION file: ODB ODB

First notice this package is not intented to manage all .odb variations. Currently two distinct types of .odb files can be distinguished : files embedding an HSQL database, and files connecting to a remote database engine such as MySQL. This package is made for the formers, as remote databases can be queried directly using the DBI package. Functions involving the XML content of the .odb file (odb.comments and odb.queries) may be operative on such files, but there is no garantee they would be.

You should not consider this package without minimal knowledge of the SQL language, as only a few basic operations have functions not relying on the user to build a valid SQL query (odb.insert, odb.tables, odb.comments, odb.queries).

This package is built around the odb class, which extends DBIConnection. A set of functions is provided to execute reading (SELECT ...) or writing (INSERT, CREATE, DROP ...) SQL queries through the HSQLDB engine, and a set of convenient tools is also provided for the most common tasks (odb.read, odb.write, odb.insert). A few Open Document specific functions are also provided, allowing modifications on features provided by the .odb file format and not the database itself (odb.comments and odb.queries).

Effects of all these functions can be achieved manually via the OpenOffice or LibreOffice Base software, obviously this package offers a way of automating it on a bigger scale. Both manual and automatic modifications can be done on the same .odb file, but users are strongly recommended to not use the two methods simultaneously. This R package works on a copy of the file made at the odb.open call, and this copy will replace the original file at the odb.close call, regardless of what happened between the two calls.

Additional tools are also provided, notably odb.export and isClosed.

See the 'examples' section for more details on the package behavior.

Examples

# NOT RUN {
  ## CONNECTION ##
  
  # Creation of a new .odb database
  odbFile <- tempfile(fileext=".odb")
  odb.create(odbFile, overwrite="do")

  # Connection to the file
  odb <- odb.open(odbFile)
  
  
  ## STRUCTURE ##
  
  # Creation of a new table
  SQL <- "CREATE TABLE fruits (id INTEGER PRIMARY KEY, value VARCHAR(8))"
  odb.write(odb, SQL)
  
  # Addition of comments on columns
  odb.comments(odb, "fruits", c("id", "value")) <- c("Fruit rank", "Fruit name")
  
  # Printing of the database structure
  print(odb.tables(odb))
  
  # Printing of a specific comment
  print(odb.comments(odb, "fruits", "value"))
  
  
  ## DATA ##
  
  # Insertion of data
  data <- data.frame(id=1:3, value=c("apple", "orange", "pear"))
  odb.insert(odb, tableName="fruits", data=data, execute=TRUE)
  
  # Reading
  print(odb.read(odb, "SELECT * FROM fruits WHERE id=2"))
  
  # Saved query
  odb.queries(odb, "2nd record") <- "SELECT * FROM fruits WHERE id=2"
  print(odb.read(odb, odb.queries(odb, "2nd record")))
  
  # SQL export
  sqlFile <- tempfile(fileext=".sql")
  odb.export(odb, sqlFile)
  
  
  ## DISCONNECTION ##
  
  # Is the connection closed ?
  print(isClosed(odb))
  
  # Save changes in the .odb file
  odb.close(odb, write=TRUE)
  
  # And now ?
  print(isClosed(odb))
# }