SQLiteDatabaseHelper.java :  » UnTagged » swp-dv-ws2010-replication » de » fuberlin » replication » storage » sqlite » Android Open Source

package de.fuberlin.replication.storage.sqlite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Create a connection to the SQLite Database using JDBC and create the needed tables.
 */
public class SQLiteDatabaseHelper {
  private Connection connection = null;
  private static final String DATABASE_NAME = "replication.db";

  /**
   * Constructor.
   */
  public SQLiteDatabaseHelper() {
    try {
      Class.forName("org.sqlite.JDBC");
      this.connection = DriverManager.getConnection("jdbc:sqlite:"
          + DATABASE_NAME);

      // drop();
      create();
      pragma();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  private void create() throws SQLException {
    Statement s = connection.createStatement();

    s.executeUpdate("CREATE TABLE if not exists version (version INTEGER);");

    s.executeUpdate("CREATE TABLE if not exists subset ("
        + "id INTEGER PRIMARY KEY ASC AUTOINCREMENT" + ");");

    s.executeUpdate("CREATE TABLE if not exists knowledge ("
        + "subset_id INTEGER REFERENCES subset (id) ON UPDATE CASCADE ON DELETE CASCADE, "
        + "replica TEXT, " + "version INTEGER, "
        + "PRIMARY KEY (subset_id, replica) ON CONFLICT REPLACE" + ");");

    s.executeUpdate("CREATE TABLE if not exists metadata ("
        + "name TEXT PRIMARY KEY ON CONFLICT REPLACE, "
        + "subset_id INTEGER REFERENCES subset (id) ON UPDATE CASCADE ON DELETE RESTRICT, "
        + "replica TEXT, " + "version INTEGER, " + "timestamp INTEGER"
        + ");");
    s.close();
  }

  private void pragma() throws SQLException {
    Statement s = connection.createStatement();
    s.execute("PRAGMA foreign_keys = ON");
    s.close();
  }

  /**
   * Drop tables from DB.
   * 
   * @throws SQLException If tables could not be dropped
   */
  public void drop() throws SQLException {
    Statement s = connection.createStatement();
    s.executeUpdate("DROP TABLE if exists version;");
    s.executeUpdate("DROP TABLE if exists subset");
    s.executeUpdate("DROP TABLE if exists knowledge");
    s.executeUpdate("DROP TABLE if exists metadata");
    s.close();
  }

  /**
   * Getter for the DB connection.
   * 
   * @return SQLite DB connection
   */
  public Connection getConnection() {
    return this.connection;
  }
}