ODB-package {ODB}

Open Document Databases (.odb) Management

Description

Functions to create, connect, update and query 'HSQL' databases embedded in Open Document Databases files, as 'OpenOffice' and 'LibreOffice' do.

Details

The DESCRIPTION file:

Package:	ODB
Type:	Package
Title:	Open Document Databases (.odb) Management
Version:	1.2.1
Date:	2020-03-10
Author:	Sylvain Mareschal
Maintainer:	Sylvain Mareschal <[e-mail]>
URL:	http://bioinformatics.ovsa.fr/ODB
BugReports:	https://github.com/maressyl/R.ODB/issues
Description:	Functions to create, connect, update and query 'HSQL' databases embedded in Open Document Databases files, as 'OpenOffice' and 'LibreOffice' do.
License:	GPL (>= 3)
Depends:	methods, DBI, RJDBC
Imports:	utils
SystemRequirements:	zip

Index of help topics:

ODB-class                                         Class "ODB"
ODB-package                                       Open Document Databases (.odb) Management
difftimeFmt                                       Formats a time difference in multiple units
isClosed                                          Checks if a DBI connection is closed
odb.close                                         Closes an "ODB" connection and updates the .odb file
odb.comments                                      Gets or sets column comments in an ODB database
odb.create                                        Creates a .odb empty file.
odb.export                                        Exports an ODB database to a SQL file.
odb.insert                                        Wrapper for inserting data in an ODB table.
odb.open                                          Creates a connection to a .odb file
odb.queries                                       Gets or sets stored queries in an ODB database
odb.read                                          Executes a reading SQL query in an ODB database (SELECT ...)
odb.tables                                        Gets description of every table in an ODB database.
odb.write                                         Executes writing SQL queries in an ODB database (INSERT ...)
progress-class                                    Classes "progress", "progress.file" and "progress.console"

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.

Author(s)

Sylvain Mareschal

Maintainer: Sylvain Mareschal <[e-mail]>

Examples

  ## 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))