private Integer executeDocInsert(JCas jCas) throws SQLException, BaleenException {
DocumentAnnotation da = getDocumentAnnotation(jCas);
String documentId = ConsumerUtils.getExternalId(da, contentHashAsId);
insertDocStatement.clearParameters();
insertDocStatement.setString(1, documentId);
insertDocStatement.setString(2, da.getDocType());
insertDocStatement.setString(3, da.getSourceUri());
insertDocStatement.setString(4, jCas.getDocumentText());
insertDocStatement.setString(5, jCas.getDocumentLanguage());
insertDocStatement.setTimestamp(6, new Timestamp(da.getTimestamp()));
insertDocStatement.setString(7, da.getDocumentClassification());
insertDocStatement.setArray(
8, createVarcharArray(postgresResource.getConnection(), da.getDocumentCaveats()));
insertDocStatement.setArray(
9, createVarcharArray(postgresResource.getConnection(), da.getDocumentReleasability()));
insertDocStatement.executeUpdate();
Integer docKey = getKey(insertDocStatement);
if (docKey == null) {
throw new BaleenException("No document key returned");
}
return docKey;
}
public void testArrayA() {
try {
String ddl0 = "DROP TABLE ARRAYTEST IF EXISTS";
String ddl1 = "CREATE TABLE ARRAYTEST(A INTEGER ARRAY)";
String dml1 = "INSERT INTO ARRAYTEST VALUES(ARRAY[0,0])";
String dml2 = "INSERT INTO ARRAYTEST VALUES ?";
statement.execute(ddl0);
statement.execute(ddl1);
statement.execute(dml1);
PreparedStatement ps = connection.prepareStatement(dml2);
Object[] objects = new Object[] {"1", 3, 9};
Array array = connection.createArrayOf("INTEGER", objects);
ps.setArray(1, array);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
fail("array failure");
}
}
@Override
public void setFindDocsSelectStatementParameters(
CollectionSchema colSchema,
Integer[] requestedDocs,
Projection projection,
Connection c,
PreparedStatement ps)
throws SQLException {
ps.setString(1, colSchema.getName());
ps.setArray(2, c.createArrayOf("integer", requestedDocs));
Integer[] requiredTables = requiredTables(colSchema, projection);
ps.setArray(3, c.createArrayOf("integer", requiredTables));
}
public void setArray(int i, Array x) throws SQLException {
checkOpen();
try {
_stmt.setArray(i, x);
} catch (SQLException e) {
handleException(e);
}
}
public void setArray(int idx, Array array) throws SQLException {
try {
addMementoEntry("setArray", new Class[] {Array.class}, idx, array);
wrapped.setArray(idx, array);
} catch (SQLException e) {
throw new UcanaccessSQLException(e);
}
}
@Override
public void set(PreparedStatement target, Array value) throws SQLException {
if (value == null) {
target.setNull(columnIndex, Types.ARRAY);
} else {
target.setArray(columnIndex, value);
}
}
public void setArray(int i, Array x) throws SQLException {
Profiler profiler = _profilerPoint.start();
try {
_preparedStatement.setArray(i, x);
} finally {
profiler.finish();
}
}
public void setArray(int i, Array x) throws SQLException {
String methodCall = "setArray(" + i + ", " + x + ")";
argTraceSet(i, "(Array)", "<Array>");
try {
realPreparedStatement.setArray(i, x);
} catch (SQLException s) {
reportException(methodCall, s);
throw s;
}
reportReturn(methodCall);
}
private Integer executeEntityInsert(
Integer docKey, Collection<String> values, Collection<String> externalIds, String type)
throws SQLException {
insertEntityStatement.clearParameters();
insertEntityStatement.setInt(1, docKey);
insertEntityStatement.setArray(
2,
postgresResource
.getConnection()
.createArrayOf(VARCHAR, externalIds.toArray(new String[0])));
insertEntityStatement.setString(3, type);
insertEntityStatement.setArray(
4, postgresResource.getConnection().createArrayOf(VARCHAR, values.toArray(new String[0])));
insertEntityStatement.executeUpdate();
Integer entityKey = getKey(insertEntityStatement);
if (entityKey == null) {
getMonitor().error("No entity key returned - Geo insertion, if applicable, will be skipped");
}
return entityKey;
}
@Override
public void setToPreparedStatement(
PreparedStatement ps, int index, Serializable value, Column column) throws SQLException {
switch (column.getJdbcType()) {
case Types.VARCHAR:
case Types.CLOB:
setToPreparedStatementString(ps, index, value, column);
return;
case Types.BIT:
ps.setBoolean(index, ((Boolean) value).booleanValue());
return;
case Types.SMALLINT:
ps.setInt(index, ((Long) value).intValue());
return;
case Types.INTEGER:
case Types.BIGINT:
ps.setLong(index, ((Number) value).longValue());
return;
case Types.DOUBLE:
ps.setDouble(index, ((Double) value).doubleValue());
return;
case Types.TIMESTAMP:
ps.setTimestamp(index, getTimestampFromCalendar((Calendar) value));
return;
case Types.ARRAY:
int jdbcBaseType = column.getJdbcBaseType();
String jdbcBaseTypeName = column.getSqlBaseTypeString();
if (jdbcBaseType == Types.TIMESTAMP) {
value = getTimestampFromCalendar((Serializable[]) value);
}
Array array = ps.getConnection().createArrayOf(jdbcBaseTypeName, (Object[]) value);
ps.setArray(index, array);
return;
case Types.OTHER:
ColumnType type = column.getType();
if (type.isId()) {
setId(ps, index, value);
return;
} else if (type == ColumnType.FTSTORED) {
ps.setString(index, (String) value);
return;
}
throw new SQLException("Unhandled type: " + column.getType());
default:
throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
}
}
@Override
public void mark(String[] uris) {
java.sql.Connection connection = getConnection();
try (java.sql.PreparedStatement statement =
connection.prepareStatement("/*NO LOAD BALANCE*/SELECT \"test\".\"mark_Clicked\"(?)")) {
Object[] ids = new Object[uris.length];
for (int i = 0; i < uris.length; i++) {
ids[i] = Long.parseLong(uris[i]);
}
statement.setArray(1, connection.createArrayOf("bigint", ids));
statement.executeUpdate();
} catch (java.sql.SQLException e) {
throw new RuntimeException(e);
} finally {
releaseConnection(connection);
}
}
public ResultSetQueryResult(
QueryMaker queryMaker,
String query,
QueryFilter queryFilter,
PathResolver pathResolver,
JDBCMapper mapper,
Object... params)
throws StorageException, SQLException {
logger = mapper.logger;
q =
queryMaker.buildQuery(
mapper.sqlInfo, mapper.model, pathResolver, query, queryFilter, params);
if (q == null) {
logger.log("Query cannot return anything due to conflicting clauses");
ps = null;
rs = null;
eof = true;
return;
} else {
eof = false;
}
if (logger.isLogEnabled()) {
logger.logSQL(q.selectInfo.sql, q.selectParams);
}
ps =
mapper.connection.prepareStatement(
q.selectInfo.sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
int i = 1;
for (Object object : q.selectParams) {
if (object instanceof Calendar) {
Calendar cal = (Calendar) object;
Timestamp ts = new Timestamp(cal.getTimeInMillis());
ps.setTimestamp(i++, ts, cal); // cal passed for timezone
} else if (object instanceof String[]) {
Array array =
mapper.sqlInfo.dialect.createArrayOf(
Types.VARCHAR, (Object[]) object, mapper.connection);
ps.setArray(i++, array);
} else {
ps.setObject(i++, object);
}
}
rs = ps.executeQuery();
// rs.setFetchDirection(ResultSet.FETCH_UNKNOWN); fails in H2
}
@Override
public java.util.List<gen.model.test.Clicked> find(String[] uris) {
java.sql.Connection connection = getConnection();
try (java.sql.PreparedStatement statement =
connection.prepareStatement(
"SELECT r FROM \"test\".\"Clicked_event\" r WHERE r._event_id = ANY(?)")) {
Object[] ids = new Object[uris.length];
for (int i = 0; i < uris.length; i++) {
ids[i] = Long.parseLong(uris[i]);
}
statement.setArray(1, connection.createArrayOf("bigint", ids));
return readFromDb(statement, new java.util.ArrayList<>(uris.length));
} catch (java.sql.SQLException | java.io.IOException e) {
throw new RuntimeException(e);
} finally {
releaseConnection(connection);
}
}
@Override
public Set<String> findConflicts(@Nullable String namespace, Set<String> paths) {
checkNotNull(paths, "paths is null");
Set<String> matches = new HashSet<>();
namespace = namespace(namespace);
final int partitionSize = 1000;
final String sql =
format(
"SELECT path FROM %s WHERE repository = ? AND namespace = ? AND path = ANY(?)",
conflictsTable);
try (Connection cx = dataSource.getConnection()) {
cx.setAutoCommit(true);
try (PreparedStatement ps = cx.prepareStatement(sql)) {
Iterable<List<String>> partitions = Iterables.partition(paths, partitionSize);
for (List<String> partition : partitions) {
String[] pathsArg = partition.toArray(new String[partition.size()]);
Array array = cx.createArrayOf("varchar", pathsArg);
ps.clearParameters();
ps.setInt(1, repositoryId);
ps.setString(2, namespace);
ps.setArray(3, array);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
matches.add(rs.getString(1));
}
}
}
} catch (SQLException e) {
throw e;
}
} catch (SQLException e) {
throw propagate(e);
}
return matches;
}
public void setDeleteDidsStatementParameters(PreparedStatement ps, Collection<Integer> dids)
throws SQLException {
Connection connection = ps.getConnection();
final int maxInArray = (2 << 15) - 1; // = 2^16 -1 = 65535
Integer[] didsToDelete = dids.toArray(new Integer[dids.size()]);
int i = 0;
while (i < didsToDelete.length) {
int toIndex = Math.min(i + maxInArray, didsToDelete.length);
Integer[] subDids = Arrays.copyOfRange(didsToDelete, i, toIndex);
Array arr = connection.createArrayOf("integer", subDids);
ps.setArray(1, arr);
ps.addBatch();
i = toIndex;
}
}
@Override
public void setToPreparedStatement(
PreparedStatement ps, int index, Serializable value, Column column) throws SQLException {
switch (column.getJdbcType()) {
case Types.VARCHAR:
case Types.CLOB:
setToPreparedStatementString(ps, index, value, column);
return;
case Types.BIT:
ps.setBoolean(index, ((Boolean) value).booleanValue());
return;
case Types.SMALLINT:
ps.setInt(index, ((Long) value).intValue());
return;
case Types.INTEGER:
case Types.BIGINT:
ps.setLong(index, ((Long) value).longValue());
return;
case Types.DOUBLE:
ps.setDouble(index, ((Double) value).doubleValue());
return;
case Types.TIMESTAMP:
setToPreparedStatementTimestamp(ps, index, value, column);
return;
case Types.ARRAY:
Array array = createArrayOf(Types.VARCHAR, (Object[]) value, ps.getConnection());
ps.setArray(index, array);
return;
case Types.OTHER:
if (column.getType() == ColumnType.FTSTORED) {
ps.setString(index, (String) value);
return;
}
throw new SQLException("Unhandled type: " + column.getType());
default:
throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
}
}
@Override
public void removeConflicts(final @Nullable String ns, final Iterable<String> paths) {
checkNotNull(paths, "paths is null");
final String namespace = namespace(ns);
final String sql =
format(
"DELETE FROM %s WHERE repository = ? AND namespace = ? AND path = ANY(?)",
conflictsTable);
try (Connection cx = dataSource.getConnection()) {
cx.setAutoCommit(false);
try (PreparedStatement ps = cx.prepareStatement(sql)) {
final int partitionSize = 1000;
Iterable<List<String>> partitions = Iterables.partition(paths, partitionSize);
for (List<String> partition : partitions) {
String[] pathsArg = partition.toArray(new String[partition.size()]);
Array array = cx.createArrayOf("varchar", pathsArg);
ps.clearParameters();
ps.setInt(1, repositoryId);
ps.setString(2, namespace);
ps.setArray(3, array);
ps.executeUpdate();
}
cx.commit();
} catch (SQLException e) {
cx.rollback();
throw e;
} finally {
cx.setAutoCommit(true);
}
} catch (SQLException e) {
throw propagate(e);
}
}
@Override
public void setArray(int parameterIndex, Array x) throws SQLException {
stmt.setArray(parameterIndex, x);
logValue(parameterIndex, x == null ? null : "<Array>");
}
/**
* Helper method to create a {@link Array} for a specific {@link PDataType}, and set it on the
* provided {@code stmt}.
*/
private static void setArrayInStatement(
PreparedStatement stmt, PDataType<?> type, Object[] obj, int position) throws SQLException {
Array sqlArray =
stmt.getConnection().createArrayOf(PDataType.arrayBaseType(type).getSqlTypeName(), obj);
stmt.setArray(position, sqlArray);
}
public void setArray(int parameterIndex, Array x) throws SQLException {
delegate.setArray(parameterIndex, x);
}
/**
* Function to return list of eventset for given encounter number list
*
* @param detailFlag
* @param blobFlag
* @param statusFlag
* @exception I2B2DAOException
*/
public EventSet getVisitsByEncounterNum(
List<String> encounterNumList, boolean detailFlag, boolean blobFlag, boolean statusFlag)
throws I2B2DAOException {
EventSet visitDimensionSet = new EventSet();
log.debug("visit list size " + encounterNumList.size());
Connection conn = null;
PreparedStatement query = null;
String tempTableName = "";
try {
conn = getDataSource().getConnection();
VisitFactRelated visitRelated =
new VisitFactRelated(buildOutputOptionType(detailFlag, blobFlag, statusFlag));
visitRelated.setMetaDataParamList(this.metaDataParamList);
String selectClause = visitRelated.getSelectClause();
String serverType = dataSourceLookup.getServerType();
if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
oracle.jdbc.driver.OracleConnection conn1 =
(oracle.jdbc.driver.OracleConnection)
((WrappedConnection) conn).getUnderlyingConnection();
String finalSql =
"SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit WHERE visit.encounter_num IN (SELECT * FROM TABLE (cast (? as QT_PDO_QRY_STRING_ARRAY)))";
log.debug("Executing sql[" + finalSql + "]");
query = conn1.prepareStatement(finalSql);
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("QT_PDO_QRY_STRING_ARRAY", conn1);
oracle.sql.ARRAY paramArray =
new oracle.sql.ARRAY(desc, conn1, encounterNumList.toArray(new String[] {}));
query.setArray(1, paramArray);
} else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
log.debug("creating temp table");
tempTableName =
this.getDbSchemaName() + SQLServerFactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE;
java.sql.Statement tempStmt = conn.createStatement();
try {
tempStmt.executeUpdate("drop table " + tempTableName);
} catch (SQLException sqlex) {;
}
uploadTempTable(tempStmt, tempTableName, encounterNumList);
String finalSql =
"SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit WHERE visit.encounter_num IN (select distinct char_param1 FROM "
+ tempTableName
+ ") order by encounter_num";
log.debug("Executing [" + finalSql + "]");
query = conn.prepareStatement(finalSql);
} else if (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
log.debug("creating temp table");
tempTableName = this.getDbSchemaName() + FactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE;
java.sql.Statement tempStmt = conn.createStatement();
try {
tempStmt.executeUpdate("drop table if exists " + tempTableName);
} catch (SQLException sqlex) {;
}
uploadTempTable(tempStmt, tempTableName, encounterNumList);
String finalSql =
"SELECT "
+ selectClause
+ " FROM "
+ getDbSchemaName()
+ "visit_dimension visit WHERE visit.encounter_num IN (select distinct char_param1 FROM "
+ tempTableName
+ ") order by encounter_num";
log.debug("Executing [" + finalSql + "]");
query = conn.prepareStatement(finalSql);
}
ResultSet resultSet = query.executeQuery();
I2B2PdoFactory.EventBuilder eventBuilder =
new I2B2PdoFactory().new EventBuilder(detailFlag, blobFlag, statusFlag);
while (resultSet.next()) {
EventType visitDimensionType =
eventBuilder.buildEventSet(resultSet, this.metaDataParamList);
visitDimensionSet.getEvent().add(visitDimensionType);
}
} catch (SQLException sqlEx) {
log.error("", sqlEx);
throw new I2B2DAOException("sql exception", sqlEx);
} catch (IOException ioex) {
log.error("", ioex);
throw new I2B2DAOException("io exception", ioex);
} finally {
if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
PdoTempTableUtil tempUtil = new PdoTempTableUtil();
tempUtil.deleteTempTableSqlServer(conn, tempTableName);
} else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
PdoTempTableUtil tempUtil = new PdoTempTableUtil();
tempUtil.deleteTempTablePostgres(conn, tempTableName);
}
try {
JDBCUtil.closeJdbcResource(null, query, conn);
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
}
return visitDimensionSet;
}
public void setArray(int i, java.sql.Array x) throws SQLException {
saveParam(i, "" + x, "Array");
statement.setArray(i, x);
}