private PreparedStatement getSelectAllLatestStatement(Connection conn, EntityType entityType) throws SQLException { StringBuilder queryBuilder = new StringBuilder(); // TODO: COOPR-684 - Handle latest version querying more robustly and efficiently queryBuilder.append("SELECT t.").append(entityType.getBlobColumn()).append(", t.name, t.version"); queryBuilder.append(" FROM ").append(entityType.getTableName()).append(" t"); queryBuilder.append(" INNER JOIN("); queryBuilder.append(" SELECT name, MAX(version) version"); queryBuilder.append(" FROM ").append(entityType.getTableName()); queryBuilder.append(" WHERE tenant_id=?"); queryBuilder.append(" GROUP BY name"); queryBuilder.append(" ) ss on t.name = ss.name AND t.version = ss.version"); queryBuilder.append(" WHERE t.tenant_id=?"); String queryString = queryBuilder.toString(); // TODO: remove once types are defined through server instead of through provisioner // automator and provider types are constant across tenants and defined only in the superadmin tenant. String tenantId = (entityType == EntityType.AUTOMATOR_TYPE || entityType == EntityType.PROVIDER_TYPE) ? Constants.SUPERADMIN_TENANT : account.getTenantId(); PreparedStatement statement = conn.prepareStatement(queryString); statement.setString(1, tenantId); statement.setString(2, tenantId); return statement; } }
protected PreparedStatement getSelectStatement(Connection conn, EntityType entityType, String entityName, int entityVersion) throws SQLException { String entityTypeId = entityType.getId(); // immune to sql injection since everything is an enum or constant StringBuilder queryStr = new StringBuilder(); queryStr.append("SELECT "); queryStr.append(entityTypeId); queryStr.append(" FROM "); queryStr.append(entityTypeId); queryStr.append("s WHERE name=? AND tenant_id=? AND version="); if (entityVersion == Constants.FIND_MAX_VERSION) { queryStr.append(String.format("(SELECT MAX(version) FROM %ss WHERE name=? AND tenant_id=?)", entityTypeId)); } else { queryStr.append("?"); } String tenantId = (entityType == EntityType.AUTOMATOR_TYPE || entityType == EntityType.PROVIDER_TYPE) ? Constants.SUPERADMIN_TENANT : account.getTenantId(); PreparedStatement statement = conn.prepareStatement(queryStr.toString()); statement.setString(1, entityName); statement.setString(2, tenantId); if (entityVersion == Constants.FIND_MAX_VERSION) { statement.setString(3, entityName); statement.setString(4, tenantId); } else { statement.setInt(3, entityVersion); } return statement; }
@Override protected byte[] getEntity(EntityType entityType, String entityName, int entityVersion) throws IOException { try { byte[] entityBytes = null; Connection conn = dbConnectionPool.getConnection(); try { PreparedStatement statement = getSelectStatement(conn, entityType, entityName, entityVersion); try { ResultSet rs = statement.executeQuery(); try { if (rs.next()) { entityBytes = rs.getBytes(1); } } finally { rs.close(); } } finally { statement.close(); } } finally { conn.close(); } return entityBytes; } catch (SQLException e) { throw new IOException("Exception getting entity of type " + entityType.name().toLowerCase() + " of name " + entityName + " of version " + entityVersion + accountErrorSnippet); } }
@Override protected int getVersion(EntityType entityType, String entityName) throws IOException { try { Integer version = 1; Connection conn = dbConnectionPool.getConnection(); try { PreparedStatement statement = getSelectMaxVersionStatement(conn, entityType, entityName); try { ResultSet rs = statement.executeQuery(); try { if (rs.next()) { version += rs.getInt(1); } } finally { rs.close(); } } finally { statement.close(); } } finally { conn.close(); } return version; } catch (SQLException e) { throw new IOException("Exception getting highest version of entity of type " + entityType.name().toLowerCase() + " of name " + entityName + accountErrorSnippet); } }
@Override protected <T> Collection<T> getAllLatestEntities(EntityType entityType, Function<byte[], T> transform) throws IOException { try { Connection conn = dbConnectionPool.getConnection(); List<T> entities = Lists.newLinkedList(); try { PreparedStatement statement = getSelectAllLatestStatement(conn, entityType); try { ResultSet rs = statement.executeQuery(); try { while (rs.next()) { entities.add(transform.apply(rs.getBytes(1))); } } finally { rs.close(); } } finally { statement.close(); } } finally { conn.close(); } return entities; } catch (SQLException e) { throw new IOException("Exception getting all entities of type " + entityType.name().toLowerCase() + accountErrorSnippet); } }
@Override protected void deleteEntity(EntityType entityType, String entityName, int entityVersion) throws IOException { try { Connection conn = dbConnectionPool.getConnection(); try { PreparedStatement statement = getDeleteStatementWithVersion(conn, entityType, entityName, entityVersion); try { statement.executeUpdate(); } finally { statement.close(); } } finally { conn.close(); } } catch (SQLException e) { throw new IOException("Exception deleting entity of type " + entityType.name().toLowerCase() + " with name " + entityName + " and version " + entityVersion + accountErrorSnippet); } }
@Override protected void deleteEntity(EntityType entityType, String entityName) throws IOException { try { Connection conn = dbConnectionPool.getConnection(); try { PreparedStatement statement = getDeleteStatementWithoutVersion(conn, entityType, entityName); try { statement.executeUpdate(); } finally { statement.close(); } } finally { conn.close(); } } catch (SQLException e) { throw new IOException("Exception deleting all versions of type " + entityType.name().toLowerCase() + " with name " + entityName + accountErrorSnippet); } }
public void clearData() throws SQLException { Connection conn = dbConnectionPool.getConnection(); try { for (BaseEntityStoreView.EntityType type : BaseEntityStoreView.EntityType.values()) { Statement stmt = conn.createStatement(); try { stmt.execute("DELETE FROM " + type.getId() + "s"); } finally { stmt.close(); } } } finally { conn.close(); } }
@Override protected void writeEntity(EntityType entityType, String entityName, int version, byte[] data) throws IOException { try { Connection conn = dbConnectionPool.getConnection(); try { DBPut entityPut = new EntityDBPut(entityType, entityName, version, data); entityPut.executePut(conn); } finally { conn.close(); } } catch (SQLException e) { throw new IOException("Exception writing entity of type " + entityType.name().toLowerCase() + " with name " + entityName + accountErrorSnippet); } }
@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); } } }
protected PreparedStatement getSelectMaxVersionStatement(Connection conn, EntityType entityType, String entityName) throws SQLException { String entityTypeId = entityType.getId(); // immune to sql injection since everything is an enum or constant String query = "SELECT MAX(version) FROM " + entityTypeId + "s WHERE name=? AND tenant_id=?"; PreparedStatement statement = conn.prepareStatement(query); statement.setString(1, entityName); statement.setString(2, account.getTenantId()); return statement; }
private PreparedStatement getDeleteStatementWithoutVersion(Connection conn, EntityType entityType, String entityName) throws SQLException { String entityTypeId = entityType.getId(); // immune to sql injection since it comes from the enum. String queryStr = "DELETE FROM " + entityTypeId + "s WHERE name=? AND tenant_id=?"; PreparedStatement statement = conn.prepareStatement(queryStr); statement.setString(1, entityName); statement.setString(2, account.getTenantId()); return statement; }
@Override public PreparedStatement createInsertStatement(Connection conn) throws SQLException { String entityTypeId = entityType.getId(); // immune to sql injection since it comes from the enum. String queryStr = "INSERT INTO " + entityTypeId + "s (name, version, tenant_id, " + entityTypeId + ") VALUES (?, ?, ?, ?)"; PreparedStatement statement = conn.prepareStatement(queryStr); statement.setString(1, entityName); statement.setInt(2, version); statement.setString(3, account.getTenantId()); statement.setBytes(4, data); return statement; } }
private PreparedStatement getDeleteStatementWithVersion(Connection conn, EntityType entityType, String entityName, int entityVersion) throws SQLException { String entityTypeId = entityType.getId(); // immune to sql injection since it comes from the enum. String queryStr = "DELETE FROM " + entityTypeId + "s WHERE name=? AND version=? AND tenant_id=?"; PreparedStatement statement = conn.prepareStatement(queryStr); statement.setString(1, entityName); statement.setInt(2, entityVersion); statement.setString(3, account.getTenantId()); return statement; }