Overview of SQLite Database

Guidelines to accessing the SQLite database within an OSGi-enabled platform.

The open source SQLite database is integrated in the Database Module. It is an embedded database library that implements a subset of the SQL-92 standard. For further information refer to the SQLite official site.

SQLite Java Wrapper/JDBC Driver

The SQLite database is accessible through the integrated SQLite Java Wrapper/JDBC Driver via the DataSourceFactory service.

To get the DataSourceFactory service, follow these steps:

1. Get its Service Reference and then get the service itself:

   ServiceReference ref = bundleContext.getServiceReference(DataSourceFactory.class.getName());

   DataSourceFactory dataSourceFactory = (DataSourceFactory) bundleContext.getService(ref);

2. Get the DataSource from the given dataSourceFactory by calling the createDataSource method:

   Properties dataProps = new Properties();

   dataProps.put(DataSourceFactory.JDBC_URL, "jdbc:sqlite:/" + dbLocation);

   DataSource ds = dataSourceFactory.createDataSource(dataProps);

   In the code snippet above dataProps is a Properties table, that must include the database URL as a minimum.

3. Get the specific connection:

   Connection conn = ds.getConnection();

The dataSourceFactory service is registered with the following properties:

• DataSourceFactory.OSGI_JDBC_DRIVER_CLASS = "SQLite.JDBCDriver"
• DataSourceFactory.OSGI_JDBC_DRIVER_NAME = "SQLite/JDBC"
• DataSourceFactory.OSGI_JDBC_DRIVER_VERSION = "1.20150419"

The build version of the driver in the last property can be changed.

Backup and Restore

The backup and restore functionalities of the Database module are provided via the DataSourceBackup service. The examples below show how to use the DataSourceBackup service methods.

As can be seen from the examples, both DataSourceFactory and DataSourceBackup services use the same properties for their registration.

• To backup a source database to a destination:

  Properties dataProps = new Properties();

  dataProps.put(DataSourceFactory.JDBC_URL, "jdbc:sqlite:/" + sourceLocation);

  DataSource source = dataSourceFactory.createDataSource(dataProps);

  dataProps.put(DataSourceFactory.JDBC_URL, "jdbc:sqlite:/" + destinationLocation);

  DataSource destination = dataSourceFactory.createDataSource(dataProps);

  dataSourceBackup.backup(source, destination);

  
  

• To restore a source database to a destination:

  Properties dataProps = new Properties();

  dataProps.put(DataSourceFactory.JDBC_URL, "jdbc:sqlite:/" + sourceLocation);

  DataSource source = dataSourceFactory.createDataSource(dataProps);

  dataProps.put(DataSourceFactory.JDBC_URL, "jdbc:sqlite:/" + destinationLocation);

  DataSource destination = dataSourceFactory.createDataSource(dataProps);

  dataSourceBackup.restore(source, destination);

The package that provides the DataSourceBackup interface is com.prosyst.mbs.services.db.sql – for details refer to the API page.

Database URL Specifics

The user executing the OSGi framework must have the proper file permissions over the database file.

If a database write operation exists but the user does not have the needed permissions, an exception will be thrown. The following examples illustrate different approaches towards this condition.

• In the example bellow it will write to the /tmp/test folder in the JDBCDemoDB-sqlite.db file.

  jdbc:sqlite://tmp/test/JDBCDemoDB-sqlite.db

• In the following example the database file – JDBCDemoDB-sqlite.db, is created in the bundle storage directory – /osgi/bin/vms/jdk/storage/data/<bundle id>. This directory preference is set by default but can be changed by altering the sqlite.storage.location property.

  jdbc:sqlite:/JDBCDemoDB-sqlite.db

• Another option is to create the database in memory like in the example bellow:

  jdbc:sqlite:/:memory:

References

• SQLite main page
• SQLite Java Wrapper/JDBC Driver