How to use

DBHelper

in

co.cask.coopr.store

@Override
protected void startUp() throws Exception {
 if (dbConnectionPool.isEmbeddedDerbyDB()) {
  DBHelper.createDerbyTableIfNotExists("CREATE TABLE provisioners (" +
                      "id VARCHAR(255), " +
                      "last_heartbeat TIMESTAMP, " +
                      "capacity_total INTEGER, " +
                      "capacity_free INTEGER, " +
                      "provisioner BLOB, " +
                      "PRIMARY KEY (id) )",
                     dbConnectionPool);
  DBHelper.createDerbyIndex(dbConnectionPool, "provisioners_heartbeat_index", "provisioners", "last_heartbeat");
  DBHelper.createDerbyTableIfNotExists("CREATE TABLE provisionerWorkers (" +
                      "provisioner_id VARCHAR(255), " +
                      "tenant_id VARCHAR(255), " +
                      "num_assigned INTEGER, " +
                      "num_live INTEGER, " +
                      "PRIMARY KEY (tenant_id, provisioner_id) )",
                     dbConnectionPool);
 }
}

@Override
PreparedStatement getSelectAllClusterJobsStatement(Connection conn, Set<Cluster.Status> states) throws SQLException {
 PreparedStatement statement = conn.prepareStatement(
  "SELECT C.cluster, J.job FROM clusters C, jobs J WHERE C.latest_job_num=J.job_num AND C.id=J.cluster_id " +
   "AND C.tenant_id=? AND C.owner_id=? AND C.status IN " + DBHelper.createInString(states.size()) +
   "ORDER BY C.create_time DESC");
 statement.setString(1, tenantId);
 statement.setString(2, userId);
 setInClause(statement, states, 3);
 return statement;
}

@Override
public void setHeartbeat(String provisionerId, long ts) throws IOException {
 try {
  Connection conn = dbConnectionPool.getConnection();
  try {
   PreparedStatement statement = conn.prepareStatement("UPDATE provisioners SET last_heartbeat=? WHERE id=?");
   try {
    statement.setTimestamp(1, DBHelper.getTimestamp(ts));
    statement.setString(2, provisionerId);
    statement.executeUpdate();
   } finally {
    statement.close();
   }
  } finally {
   conn.close();
  }
 } catch (SQLException e) {
  throw new IOException("Exception setting heartbeat time for provisioner " + provisionerId, e);
 }
}

 @AfterClass
 public static void afterClass() {
  DBHelper.dropDerbyDB();
 }
}

@Override
protected void startUp() throws Exception {
 if (dbConnectionPool.isEmbeddedDerbyDB()) {
  DBHelper.createDerbyTableIfNotExists(
   "CREATE TABLE users ( " +
    "user_id VARCHAR(256), " +
    "tenant_id VARCHAR(64), " +
    "profile BLOB )", dbConnectionPool);
 }
}

 @AfterClass
 public static void afterClass() {
  DBHelper.dropDerbyDB();
 }
}

@Override
protected void startUp() throws Exception {
 if (dbConnectionPool.isEmbeddedDerbyDB()) {
  DBHelper.createDerbyTableIfNotExists(
   "CREATE TABLE sensitiveFields ( " +
    "tenant_id VARCHAR(64), " +
    "cluster_id VARCHAR(255), " +
    "fields BLOB )", dbConnectionPool);
 }
}

 @Override
 public PreparedStatement createInsertStatement(Connection conn) throws SQLException {
  PreparedStatement statement = conn.prepareStatement(
   "INSERT INTO jobs (job_num, cluster_id, status, create_time, job) VALUES (?, ?, ?, ?, ?)");
  statement.setLong(1, jobId.getJobNum());
  statement.setLong(2, clusterId);
  statement.setString(3, clusterJob.getJobStatus().name());
  statement.setTimestamp(4, DBHelper.getTimestamp(System.currentTimeMillis()));
  statement.setBytes(5, jobBytes);
  return statement;
 }
}

@Override
protected void startUp() throws Exception {
 if (dbConnectionPool.isEmbeddedDerbyDB()) {
  LOG.warn("Initializing Derby DB... Tables are not optimized for performance.");
  boolean created = DBHelper.createDerbyTableIfNotExists("CREATE TABLE nodes ( " +
                              "cluster_id BIGINT, " +
                              "id VARCHAR(64), " +
                              "node BLOB, " +
                              "PRIMARY KEY (id) )", dbConnectionPool
                             );
  if (created) {
   DBHelper.createDerbyIndex(dbConnectionPool, "nodes_cluster_index", "nodes", "cluster_id", "id");
  }
 }
}

 @AfterClass
 public static void afterClass() {
  DBHelper.dropDerbyDB();
 }
}

@Override
protected void startUp() throws Exception {
 if (dbConnectionPool.isEmbeddedDerbyDB()) {
  for (BaseEntityStoreView.EntityType entityType : BaseEntityStoreView.EntityType.values()) {
   String entityName = entityType.getId();
   // immune to sql injection since it comes from the enum
   String createString = "CREATE TABLE " + entityName +
    "s ( name VARCHAR(255), version BIGINT, tenant_id VARCHAR(255), " + entityName +
    " BLOB, PRIMARY KEY (tenant_id, name, version))";
   DBHelper.createDerbyTableIfNotExists(createString, dbConnectionPool);
  }
 }
}

@Override
PreparedStatement getSelectAllClusterJobsStatement(Connection conn, Set<Cluster.Status> states) throws SQLException {
 PreparedStatement statement = conn.prepareStatement(
  "SELECT C.cluster, J.job FROM clusters C, jobs J WHERE C.latest_job_num=J.job_num AND C.id=J.cluster_id " +
   "AND C.tenant_id=? AND C.status IN " + DBHelper.createInString(states.size()) + " ORDER BY C.create_time DESC");
 statement.setString(1, tenantId);
 setInClause(statement, states, 2);
 return statement;
}

 @Override
 public PreparedStatement createInsertStatement(Connection conn) throws SQLException {
  PreparedStatement statement = conn.prepareStatement(
   "INSERT INTO provisioners (id, last_heartbeat, capacity_total, capacity_free, provisioner) " +
    "VALUES (?, ?, ?, ?, ?)");
  statement.setString(1, provisioner.getId());
  statement.setTimestamp(2, DBHelper.getTimestamp(System.currentTimeMillis()));
  statement.setInt(3, provisioner.getCapacityTotal());
  statement.setInt(4, provisioner.getCapacityFree());
  statement.setBytes(5, provisionerBytes);
  return statement;
 }
}

@Override
protected void startUp() throws Exception {
 if (dbConnectionPool.isEmbeddedDerbyDB()) {
  boolean created = DBHelper.createDerbyTableIfNotExists(
   "CREATE TABLE pluginMeta (" +
    "tenant_id VARCHAR(255), " +
    "plugin_type VARCHAR(16), " +
    "plugin_name VARCHAR(255), " +
    "resource_type VARCHAR(255), " +
    "name VARCHAR(255), " +
    "version INTEGER, " +
    "live BOOLEAN, " +
    "slated BOOLEAN, " +
    "deleted BOOLEAN, " +
    "create_time TIMESTAMP," +
    "delete_time TIMESTAMP," +
    "PRIMARY KEY(tenant_id, plugin_type, plugin_name, resource_type, name, version) )",
   dbConnectionPool);
  if (created) {
   DBHelper.createDerbyIndex(dbConnectionPool, "plugin_meta_index", "pluginMeta",
                "tenant_id", "plugin_type", "plugin_name", "resource_type", "name", "version");
  }
 }
}

 @AfterClass
 public static void afterClass() {
  DBHelper.dropDerbyDB();
 }
}

@Override
protected void startUp() throws Exception {
 if (dbConnectionPool.isEmbeddedDerbyDB()) {
  DBHelper.createDerbyTableIfNotExists(
   "CREATE TABLE tenants ( " +
    "id VARCHAR(255), " +
    "name VARCHAR(255), " +
    "workers INT, " +
    "deleted BOOLEAN, " +
    "create_time TIMESTAMP, " +
    "delete_time TIMESTAMP, " +
    "tenant BLOB )", dbConnectionPool);
 }
 // add superadmin if it doesn't exist
 Tenant superadminTenant = getTenantByName(Constants.SUPERADMIN_TENANT);
 if (superadminTenant == null) {
  writeTenant(Tenant.DEFAULT_SUPERADMIN);
 }
}

@Override
PreparedStatement getSelectAllClusterJobsStatement(Connection conn, Set<Cluster.Status> states) throws SQLException {
 PreparedStatement statement = conn.prepareStatement(
  "SELECT C.cluster, J.job FROM clusters C, jobs J WHERE C.latest_job_num=J.job_num AND C.id=J.cluster_id " +
   "AND C.status IN " + DBHelper.createInString(states.size()) + " ORDER BY C.create_time DESC");
 setInClause(statement, states, 1);
 return statement;
}

@Override
public void deleteTenantByName(String name) throws IOException {
 try {
  Connection conn = dbConnectionPool.getConnection();
  try {
   PreparedStatement statement = conn.prepareStatement(
    "UPDATE tenants SET deleted=true, delete_time=? WHERE name=? ");
   statement.setTimestamp(1, DBHelper.getTimestamp(System.currentTimeMillis()));
   statement.setString(2, name);
   try {
    statement.executeUpdate();
   } finally {
    statement.close();
   }
  } finally {
   conn.close();
  }
 } catch (SQLException e) {
  LOG.error("Exception deleting tenant {}", name);
  throw new IOException(e);
 }
}

boolean created = DBHelper.createDerbyTableIfNotExists("CREATE TABLE clusters ( " +
                             "id BIGINT, " +
                             "owner_id VARCHAR(255), " +
                            dbConnectionPool);
if (created) {
 DBHelper.createDerbyIndex(dbConnectionPool,
              "clusters_account_index", "clusters", "tenant_id", "owner_id", "id");
 DBHelper.createDerbyIndex(dbConnectionPool, "clusters_ctime_index", "clusters", "create_time");
 DBHelper.createDerbyIndex(dbConnectionPool, "clusters_status_index", "clusters", "status");
created = DBHelper.createDerbyTableIfNotExists("CREATE TABLE jobs ( " +
                         "cluster_id BIGINT, " +
                         "job_num BIGINT, " +
                        dbConnectionPool);
if (created) {
 DBHelper.createDerbyIndex(dbConnectionPool, "jobs_ctime_index", "jobs", "create_time");
 DBHelper.createDerbyIndex(dbConnectionPool, "jobs_status_index", "jobs", "status");
created = DBHelper.createDerbyTableIfNotExists("CREATE TABLE tasks ( " +
                         "cluster_id BIGINT, " +
                         "job_num BIGINT, " +
                        dbConnectionPool);
if (created) {
 DBHelper.createDerbyIndex(dbConnectionPool, "tasks_status_time_index", "tasks", "status_time");
 DBHelper.createDerbyIndex(dbConnectionPool, "tasks_submit_time_index", "tasks", "submit_time");
 DBHelper.createDerbyIndex(dbConnectionPool, "tasks_status_index", "tasks", "status");

@AfterClass
public static void afterClass() {
 DBHelper.dropDerbyDB();
}