Newer
Older
}catch(DBException de){
rollback();
throw de;
}catch(DataReadException dre){
rollback();
throw dre;
}finally{
closeStatement();
endTransaction();
}
}
/**
* <p>Fill the table uploaded by the user with the given data.</p>
*
* <p><i>Note:
* Batch updates may be done here if its supported by the DBMS connection.
* In case of any failure while using this feature, it will be flagged as unsupported and one-by-one updates will be processed.
* </i></p>
*
* <p><i>Note:
* This function proceeds to a formatting of TIMESTAMP and GEOMETRY (point, circle, box, polygon) values.
* </i></p>
*
* @param metaTable Description of the updated table.
* @param data Iterator over the rows to insert.
*
* @return Number of inserted rows.
*
* @throws DBException If rows can not be inserted because the SQL update query has failed.
* @throws SQLException If any other SQL exception occurs.
* @throws DataReadException If there is any error while reading the data from the given {@link TableIterator} (and particularly if a limit - in byte or row - has been reached).
*/
protected int fillUploadedTable(final TAPTable metaTable, final TableIterator data) throws SQLException, DBException, DataReadException{
// 1. Build the SQL update query:
StringBuffer sql = new StringBuffer("INSERT INTO ");
StringBuffer varParam = new StringBuffer();
// ...table name:
gmantele
committed
sql.append(translator.getTableName(metaTable, supportsSchema)).append(" (");
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
// ...list of columns:
TAPColumn[] cols = data.getMetadata();
for(int c = 0; c < cols.length; c++){
if (c > 0){
sql.append(", ");
varParam.append(", ");
}
sql.append(translator.getColumnName(cols[c]));
varParam.append('?');
}
// ...values pattern:
sql.append(") VALUES (").append(varParam).append(");");
// 2. Prepare the statement:
PreparedStatement stmt = null;
int nbRows = 0;
try{
stmt = connection.prepareStatement(sql.toString());
// 3. Execute the query for each given row:
while(data.nextRow()){
nbRows++;
int c = 1;
while(data.hasNextCol()){
Object val = data.nextCol();
if (val != null && cols[c - 1] != null){
/* TIMESTAMP FORMATTING */
if (cols[c - 1].getDatatype().type == DBDatatype.TIMESTAMP){
try{
val = new Timestamp(ISO8601Format.parse(val.toString()));
}catch(ParseException pe){
if (logger != null)
gmantele
committed
logger.logDB(LogLevel.ERROR, this, "UPLOAD", "[l. " + nbRows + ", c. " + c + "] Unexpected date format for the value: \"" + val + "\"! A date formatted in ISO8601 was expected.", pe);
throw new DBException("[l. " + nbRows + ", c. " + c + "] Unexpected date format for the value: \"" + val + "\"! A date formatted in ISO8601 was expected.", pe);
}
}
/* GEOMETRY FORMATTING */
else if (cols[c - 1].getDatatype().type == DBDatatype.POINT || cols[c - 1].getDatatype().type == DBDatatype.REGION){
Region region;
gmantele
committed
// parse the region as an STC-S expression:
try{
region = STCS.parseRegion(val.toString());
gmantele
committed
}catch(adql.parser.ParseException e){
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "UPLOAD", "[l. " + nbRows + ", c. " + c + "] Incorrect STC-S syntax for the geometrical value \"" + val + "\"! " + e.getMessage(), e);
throw new DataReadException("[l. " + nbRows + ", c. " + c + "] Incorrect STC-S syntax for the geometrical value \"" + val + "\"! " + e.getMessage(), e);
}
// translate this STC region into the corresponding column value:
try{
val = translator.translateGeometryToDB(region);
}catch(adql.parser.ParseException e){
if (logger != null)
gmantele
committed
logger.logDB(LogLevel.ERROR, this, "UPLOAD", "[l. " + nbRows + ", c. " + c + "] Impossible to import the ADQL geometry \"" + val + "\" into the database! " + e.getMessage(), e);
throw new DataReadException("[l. " + nbRows + ", c. " + c + "] Impossible to import the ADQL geometry \"" + val + "\" into the database! " + e.getMessage(), e);
}
}
/* BOOLEAN CASE (more generally, type incompatibility) */
else if (val != null && cols[c - 1].getDatatype().type == DBDatatype.SMALLINT && val instanceof Boolean)
val = ((Boolean)val) ? (short)1 : (short)0;
/* NULL CHARACTER CASE (JUST FOR POSTGRESQL) */
else if ((dbms == null || dbms.equalsIgnoreCase(DBMS_POSTGRES)) && val instanceof Character && (Character)val == 0x00)
val = null;
}
stmt.setObject(c++, val);
}
executeUpdate(stmt, nbRows);
}
executeBatchUpdates(stmt, nbRows);
return nbRows;
}finally{
close(stmt);
}
}
/**
* <p><i><b>Important note:</b>
* Only tables uploaded by users can be dropped from the database. To ensure that, the schema name of this table MUST be {@link STDSchema#UPLOADSCHEMA} ("TAP_UPLOAD") in ADQL.
* If it has another ADQL name, an exception will be thrown. Of course, the DB name of this schema MAY be different.
* </i></p>
*
* <p><i><b>Important note:</b>
* This function may modify the given {@link TAPTable} object if schemas are not supported by this connection.
* In this case, this function will prefix the table's DB name by the schema's DB name directly inside the given
* {@link TAPTable} object. Then the DB name of the schema will be set to NULL.
* </i></p>
*
* <p><i>Note:
* This implementation is able to drop only one uploaded table. So if this function finds more than one table matching to the given one,
* an exception will be thrown and no table will be dropped.
* </i></p>
*
* @see tap.db.DBConnection#dropUploadedTable(tap.metadata.TAPTable)
* @see #checkUploadedTableDef(TAPTable)
*/
@Override
public synchronized boolean dropUploadedTable(final TAPTable tableDef) throws DBException{
// If no table to upload, consider it has been dropped and return TRUE:
if (tableDef == null)
return true;
// Starting of new query execution => disable the cancel flag:
resetCancel();
// Check the table is well defined (and particularly the schema is well set with an ADQL name = TAP_UPLOAD):
checkUploadedTableDef(tableDef);
try{
// Check the existence of the table to drop:
if (!isTableExisting(tableDef.getDBSchemaName(), tableDef.getDBName(), connection.getMetaData()))
return true;
// Execute the update:
int cnt = getStatement().executeUpdate("DROP TABLE " + translator.getTableName(tableDef, supportsSchema) + ";");
// Log the end:
if (logger != null){
gmantele
committed
if (cnt >= 0)
logger.logDB(LogLevel.INFO, this, "TABLE_DROPPED", "Table \"" + tableDef.getADQLName() + "\" (in DB: " + translator.getTableName(tableDef, supportsSchema) + ") dropped.", null);
else
gmantele
committed
logger.logDB(LogLevel.ERROR, this, "TABLE_DROPPED", "Table \"" + tableDef.getADQLName() + "\" (in DB: " + translator.getTableName(tableDef, supportsSchema) + ") NOT dropped.", null);
}
// Ensure the update is successful:
gmantele
committed
return (cnt >= 0);
}catch(SQLException se){
if (!isCancelled() && logger != null)
gmantele
committed
logger.logDB(LogLevel.WARNING, this, "DROP_UPLOAD_TABLE", "Impossible to drop the uploaded table: " + translator.getTableName(tableDef, supportsSchema) + "!", se);
throw new DBException("Impossible to drop the uploaded table: " + translator.getTableName(tableDef, supportsSchema) + "!", se);
}finally{
closeStatement();
}
}
/**
* <p>Ensures that the given table MUST be inside the upload schema in ADQL.</p>
*
* <p>Thus, the following cases are taken into account:</p>
* <ul>
* <li>
* The schema name of the given table MUST be {@link STDSchema#UPLOADSCHEMA} ("TAP_UPLOAD") in ADQL.
* If it has another ADQL name, an exception will be thrown. Of course, the DB name of this schema MAY be different.
* </li>
* <li>
* If schemas are not supported by this connection, this function will prefix the table DB name by the schema DB name directly
* inside the given {@link TAPTable} object. Then the DB name of the schema will be set to NULL.
* </li>
* </ul>
*
* @param tableDef Definition of the table to create/drop.
*
* @throws DBException If the given table is not in a schema
* or if the ADQL name of this schema is not {@link STDSchema#UPLOADSCHEMA} ("TAP_UPLOAD").
*/
protected void checkUploadedTableDef(final TAPTable tableDef) throws DBException{
// If the table has no defined schema or if the ADQL name of the schema is not TAP_UPLOAD, throw an exception:
if (tableDef.getSchema() == null || !tableDef.getSchema().getADQLName().equals(STDSchema.UPLOADSCHEMA.label))
throw new DBException("Missing upload schema! An uploaded table must be inside a schema whose the ADQL name is strictly equals to \"" + STDSchema.UPLOADSCHEMA.label + "\" (but the DB name may be different).");
gmantele
committed
if (!supportsSchema){
if (tableDef.getADQLSchemaName() != null && tableDef.getADQLSchemaName().trim().length() > 0 && !tableDef.getDBName().startsWith(tableDef.getADQLSchemaName() + "_"))
tableDef.setDBName(tableDef.getADQLSchemaName() + "_" + tableDef.getDBName());
if (tableDef.getSchema() != null)
tableDef.getSchema().setDBName(null);
}
}
/* ************** */
/* TOOL FUNCTIONS */
/* ************** */
/**
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
* <p>Convert the given TAP type into the corresponding DBMS column type.</p>
*
* <p>
* This function tries first the type conversion using the translator ({@link JDBCTranslator#convertTypeToDB(DBType)}).
* If it fails, a default conversion is done considering all the known types of the following DBMS:
* PostgreSQL, SQLite, MySQL, Oracle and JavaDB/Derby.
* </p>
*
* @param type TAP type to convert.
*
* @return The corresponding DBMS type.
*
* @see JDBCTranslator#convertTypeToDB(DBType)
* @see #defaultTypeConversion(DBType)
*/
protected String convertTypeToDB(final DBType type){
String dbmsType = translator.convertTypeToDB(type);
return (dbmsType == null) ? defaultTypeConversion(type) : dbmsType;
}
/**
* <p>Get the DBMS compatible datatype corresponding to the given column {@link DBType}.</p>
*
* <p><i>Note 1:
* This function is able to generate a DB datatype compatible with the currently used DBMS.
* In this current implementation, only Postgresql, Oracle, SQLite, MySQL and Java DB/Derby have been considered.
* Most of the TAP types have been tested only with Postgresql and SQLite without any problem.
* If the DBMS you are using has not been considered, note that this function will return the TAP type expression by default.
* </i></p>
*
* <p><i>Note 2:
* In case the given datatype is NULL or not managed here, the DBMS type corresponding to "VARCHAR" will be returned.
* </i></p>
*
* <p><i>Note 3:
* The special TAP types POINT and REGION are converted into the DBMS type corresponding to "VARCHAR".
* </i></p>
*
* @param datatype Column TAP type.
*
* @return The corresponding DB type, or VARCHAR if the given type is not managed or is NULL.
*/
protected String defaultTypeConversion(DBType datatype){
if (datatype == null)
datatype = new DBType(DBDatatype.VARCHAR);
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
switch(datatype.type){
case SMALLINT:
return dbms.equals("sqlite") ? "INTEGER" : "SMALLINT";
case INTEGER:
case REAL:
return datatype.type.toString();
case BIGINT:
if (dbms.equals("oracle"))
return "NUMBER(19,0)";
else if (dbms.equals("sqlite"))
return "INTEGER";
else
return "BIGINT";
case DOUBLE:
if (dbms.equals("postgresql") || dbms.equals("oracle"))
return "DOUBLE PRECISION";
else if (dbms.equals("sqlite"))
return "REAL";
else
return "DOUBLE";
case BINARY:
if (dbms.equals("postgresql"))
return "bytea";
else if (dbms.equals("sqlite"))
return "BLOB";
else if (dbms.equals("oracle"))
return "RAW" + (datatype.length > 0 ? "(" + datatype.length + ")" : "");
else if (dbms.equals("derby"))
return "CHAR" + (datatype.length > 0 ? "(" + datatype.length + ")" : "") + " FOR BIT DATA";
else
return datatype.type.toString();
case VARBINARY:
if (dbms.equals("postgresql"))
return "bytea";
else if (dbms.equals("sqlite"))
return "BLOB";
else if (dbms.equals("oracle"))
return "LONG RAW" + (datatype.length > 0 ? "(" + datatype.length + ")" : "");
else if (dbms.equals("derby"))
return "VARCHAR" + (datatype.length > 0 ? "(" + datatype.length + ")" : "") + " FOR BIT DATA";
else
return datatype.type.toString();
case CHAR:
if (dbms.equals("sqlite"))
return "TEXT";
else
return "CHAR";
case BLOB:
if (dbms.equals("postgresql"))
return "bytea";
else
return "BLOB";
case CLOB:
if (dbms.equals("postgresql") || dbms.equals("mysql") || dbms.equals("sqlite"))
return "TEXT";
else
return "CLOB";
case TIMESTAMP:
if (dbms.equals("sqlite"))
return "TEXT";
else
return "TIMESTAMP";
case POINT:
case REGION:
case VARCHAR:
default:
if (dbms.equals("sqlite"))
return "TEXT";
else
return "VARCHAR";
}
}
/**
* <p>Start a transaction.</p>
*
* <p>
* Basically, if transactions are supported by this connection, the flag AutoCommit is just turned off.
* It will be turned on again when {@link #endTransaction()} is called.
* </p>
*
* <p>If transactions are not supported by this connection, nothing is done.</p>
*
* <p><b><i>Important note:</b>
* If any error interrupts the START TRANSACTION operation, transactions will be afterwards considered as not supported by this connection.
* So, subsequent call to this function (and any other transaction related function) will never do anything.
* </i></p>
*
* @throws DBException If it is impossible to start a transaction though transactions are supported by this connection.
* If these are not supported, this error can never be thrown.
*/
protected void startTransaction() throws DBException{
try{
if (supportsTransaction){
connection.setAutoCommit(false);
if (logger != null)
logger.logDB(LogLevel.INFO, this, "START_TRANSACTION", "Transaction STARTED.", null);
}
}catch(SQLException se){
supportsTransaction = false;
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "START_TRANSACTION", "Transaction STARTing impossible!", se);
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
throw new DBException("Transaction STARTing impossible!", se);
}
}
/**
* <p>Commit the current transaction.</p>
*
* <p>
* {@link #startTransaction()} must have been called before. If it's not the case the connection
* may throw a {@link SQLException} which will be transformed into a {@link DBException} here.
* </p>
*
* <p>If transactions are not supported by this connection, nothing is done.</p>
*
* <p><b><i>Important note:</b>
* If any error interrupts the COMMIT operation, transactions will be afterwards considered as not supported by this connection.
* So, subsequent call to this function (and any other transaction related function) will never do anything.
* </i></p>
*
* @throws DBException If it is impossible to commit a transaction though transactions are supported by this connection..
* If these are not supported, this error can never be thrown.
*/
protected void commit() throws DBException{
try{
if (supportsTransaction){
connection.commit();
if (logger != null)
logger.logDB(LogLevel.INFO, this, "COMMIT", "Transaction COMMITED.", null);
}
}catch(SQLException se){
supportsTransaction = false;
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "COMMIT", "Transaction COMMIT impossible!", se);
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
throw new DBException("Transaction COMMIT impossible!", se);
}
}
/**
* <p>Rollback the current transaction.</p>
*
* <p>
* {@link #startTransaction()} must have been called before. If it's not the case the connection
* may throw a {@link SQLException} which will be transformed into a {@link DBException} here.
* </p>
*
* <p>If transactions are not supported by this connection, nothing is done.</p>
*
* <p><b><i>Important note:</b>
* If any error interrupts the ROLLBACK operation, transactions will considered afterwards as not supported by this connection.
* So, subsequent call to this function (and any other transaction related function) will never do anything.
* </i></p>
*
* @throws DBException If it is impossible to rollback a transaction though transactions are supported by this connection..
* If these are not supported, this error can never be thrown.
*/
protected void rollback(){
try{
if (supportsTransaction){
connection.rollback();
if (logger != null)
logger.logDB(LogLevel.INFO, this, "ROLLBACK", "Transaction ROLLBACKED.", null);
}
}catch(SQLException se){
supportsTransaction = false;
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "ROLLBACK", "Transaction ROLLBACK impossible!", se);
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
}
}
/**
* <p>End the current transaction.</p>
*
* <p>
* Basically, if transactions are supported by this connection, the flag AutoCommit is just turned on.
* </p>
*
* <p>If transactions are not supported by this connection, nothing is done.</p>
*
* <p><b><i>Important note:</b>
* If any error interrupts the END TRANSACTION operation, transactions will be afterwards considered as not supported by this connection.
* So, subsequent call to this function (and any other transaction related function) will never do anything.
* </i></p>
*
* @throws DBException If it is impossible to end a transaction though transactions are supported by this connection.
* If these are not supported, this error can never be thrown.
*/
protected void endTransaction(){
try{
if (supportsTransaction){
connection.setAutoCommit(true);
if (logger != null)
logger.logDB(LogLevel.INFO, this, "END_TRANSACTION", "Transaction ENDED.", null);
}
}catch(SQLException se){
supportsTransaction = false;
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "END_TRANSACTION", "Transaction ENDing impossible!", se);
}
}
/**
* <p>Close silently the given {@link ResultSet}.</p>
*
* <p>If the given {@link ResultSet} is NULL, nothing (even exception/error) happens.</p>
*
* <p>
* If any {@link SQLException} occurs during this operation, it is caught and just logged
* (see {@link TAPLog#logDB(uws.service.log.UWSLog.LogLevel, DBConnection, String, String, Throwable)}).
* No error is thrown and nothing else is done.
* </p>
*
* @param rs {@link ResultSet} to close.
*/
protected final void close(final ResultSet rs){
try{
if (rs != null)
rs.close();
}catch(SQLException se){
if (logger != null)
logger.logDB(LogLevel.WARNING, this, "CLOSE", "Can not close a ResultSet!", null);
}
}
/**
* <p>Close silently the given {@link Statement}.</p>
*
* <p>If the given {@link Statement} is NULL, nothing (even exception/error) happens.</p>
*
* <p>
* The given statement is explicitly canceled by this function before being closed.
* Thus the corresponding DBMS process is ensured to be stopped. Of course, this
* cancellation is effective only if this operation is supported by the JDBC driver
* and the DBMS.
* </p>
*
* <p><b>Important note:</b>
* In case of cancellation, <b>NO</b> rollback is performed.
* </p>
*
* <p>
* If any {@link SQLException} occurs during this operation, it is caught and just logged
* (see {@link TAPLog#logDB(uws.service.log.UWSLog.LogLevel, DBConnection, String, String, Throwable)}).
* No error is thrown and nothing else is done.
* </p>
*
* @param stmt {@link Statement} to close.
*
* @see #cancel(Statement, boolean)
*/
protected final void close(final Statement stmt){
try{
if (stmt != null){
cancel(stmt, false);
stmt.close();
}catch(SQLException se){
if (logger != null)
logger.logDB(LogLevel.WARNING, this, "CLOSE", "Can not close a Statement!", null);
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
}
}
/**
* <p>Transform the given column value in a boolean value.</p>
*
* <p>The following cases are taken into account in function of the given value's type:</p>
* <ul>
* <li><b>NULL</b>: <i>false</i> is always returned.</li>
*
* <li><b>{@link Boolean}</b>: the boolean value is returned as provided (but casted in boolean).</li>
*
* <li><b>{@link Integer}</b>: <i>true</i> is returned only if the integer value is strictly greater than 0, otherwise <i>false</i> is returned.</li>
*
* <li><b>Other</b>: toString().trim() is first called on this object. Then, an integer value is tried to be extracted from it.
* If it succeeds, the previous rule is applied. If it fails, <i>true</i> will be returned only if the string is "t" or "true" (case insensitively).</li>
* </ul>
*
* @param colValue The column value to transform in boolean.
*
* @return Its corresponding boolean value.
*/
protected final boolean toBoolean(final Object colValue){
// NULL => false:
if (colValue == null)
return false;
// Boolean value => cast in boolean and return this value:
else if (colValue instanceof Boolean)
return ((Boolean)colValue).booleanValue();
// Integer value => cast in integer and return true only if the value is positive and not null:
else if (colValue instanceof Integer){
int intFlag = ((Integer)colValue).intValue();
return (intFlag > 0);
}
// Otherwise => get the string representation and:
// 1/ try to cast it into an integer and apply the same test as before
// 2/ if the cast fails, return true only if the value is "t" or "true" (case insensitively):
else{
String strFlag = colValue.toString().trim();
try{
int intFlag = Integer.parseInt(strFlag);
return (intFlag > 0);
}catch(NumberFormatException nfe){
return strFlag.equalsIgnoreCase("t") || strFlag.equalsIgnoreCase("true");
}
}
}
/**
* Return NULL if the given column value is an empty string (or it just contains space characters) or NULL.
gmantele
committed
* Otherwise the given string is returned as provided.
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
*
* @param dbValue Value to nullify if needed.
*
* @return NULL if the given string is NULL or empty, otherwise the given value.
*/
protected final String nullifyIfNeeded(final String dbValue){
return (dbValue != null && dbValue.trim().length() <= 0) ? null : dbValue;
}
/**
* Search a {@link TAPTable} instance whose the ADQL name matches (case sensitively) to the given one.
*
* @param tableName ADQL name of the table to search.
* @param itTables Iterator over the set of tables in which the research must be done.
*
* @return The found table, or NULL if not found.
*/
private TAPTable searchTable(String tableName, final Iterator<TAPTable> itTables){
// Get the schema name, if any prefix the given table name:
String schemaName = null;
int indSep = tableName.indexOf('.');
if (indSep > 0){
schemaName = tableName.substring(0, indSep);
tableName = tableName.substring(indSep + 1);
}
// Search by schema name (if any) and then by table name:
while(itTables.hasNext()){
// get the table:
TAPTable table = itTables.next();
// test the schema name (if one was prefixing the table name) (case sensitively):
if (schemaName != null){
if (table.getADQLSchemaName() == null || !schemaName.equals(table.getADQLSchemaName()))
continue;
}
// test the table name (case sensitively):
if (tableName.equals(table.getADQLName()))
return table;
}
// NULL if no table matches:
return null;
}
/**
* <p>Tell whether the specified schema exists in the database.
* To do so, it is using the given {@link DatabaseMetaData} object to query the database and list all existing schemas.</p>
*
* <p><i>Note:
* This function is completely useless if the connection is not supporting schemas.
* </i></p>
*
* <p><i>Note:
* Test on the schema name is done considering the case sensitivity indicated by the translator
* (see {@link JDBCTranslator#isCaseSensitive(IdentifierField)}).
* </i></p>
*
* <p><i>Note:
* This functions is used by {@link #addUploadedTable(TAPTable, TableIterator)} and {@link #resetTAPSchema(Statement, TAPTable[])}.
* </i></p>
*
* @param schemaName DB name of the schema whose the existence must be checked.
* @param dbMeta Metadata about the database, and mainly the list of all existing schemas.
*
* @return <i>true</i> if the specified schema exists, <i>false</i> otherwise.
*
* @throws SQLException If any error occurs while interrogating the database about existing schema.
*/
protected boolean isSchemaExisting(String schemaName, final DatabaseMetaData dbMeta) throws SQLException{
gmantele
committed
if (!supportsSchema || schemaName == null || schemaName.length() == 0)
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
return true;
// Determine the case sensitivity to use for the equality test:
boolean caseSensitive = translator.isCaseSensitive(IdentifierField.SCHEMA);
ResultSet rs = null;
try{
// List all schemas available and stop when a schema name matches ignoring the case:
rs = dbMeta.getSchemas();
boolean hasSchema = false;
while(!hasSchema && rs.next())
hasSchema = equals(rs.getString(1), schemaName, caseSensitive);
return hasSchema;
}finally{
close(rs);
}
}
/**
* <p>Tell whether the specified table exists in the database.
* To do so, it is using the given {@link DatabaseMetaData} object to query the database and list all existing tables.</p>
*
* <p><i><b>Important note:</b>
* If schemas are not supported by this connection but a schema name is even though provided in parameter,
* the table name will be prefixed by the schema name.
* The research will then be done with NULL as schema name and this prefixed table name.
* </i></p>
*
* <p><i>Note:
* Test on the schema name is done considering the case sensitivity indicated by the translator
* (see {@link JDBCTranslator#isCaseSensitive(IdentifierField)}).
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
* </i></p>
*
* <p><i>Note:
* This function is used by {@link #addUploadedTable(TAPTable, TableIterator)} and {@link #dropUploadedTable(TAPTable)}.
* </i></p>
*
* @param schemaName DB name of the schema in which the table to search is. <i>If NULL, the table is expected in any schema but ONLY one MUST exist.</i>
* @param tableName DB name of the table to search.
* @param dbMeta Metadata about the database, and mainly the list of all existing tables.
*
* @return <i>true</i> if the specified table exists, <i>false</i> otherwise.
*
* @throws SQLException If any error occurs while interrogating the database about existing tables.
*/
protected boolean isTableExisting(String schemaName, String tableName, final DatabaseMetaData dbMeta) throws DBException, SQLException{
if (tableName == null || tableName.length() == 0)
return true;
// Determine the case sensitivity to use for the equality test:
boolean schemaCaseSensitive = translator.isCaseSensitive(IdentifierField.SCHEMA);
boolean tableCaseSensitive = translator.isCaseSensitive(IdentifierField.TABLE);
ResultSet rs = null;
try{
// List all matching tables:
if (supportsSchema){
String schemaPattern = schemaCaseSensitive ? schemaName : null;
String tablePattern = tableCaseSensitive ? tableName : null;
rs = dbMeta.getTables(null, schemaPattern, tablePattern, null);
}else{
String tablePattern = tableCaseSensitive ? tableName : null;
rs = dbMeta.getTables(null, null, tablePattern, null);
}
// Stop on the first table which match completely (schema name + table name in function of their respective case sensitivity):
int cnt = 0;
while(rs.next()){
String rsSchema = nullifyIfNeeded(rs.getString(2));
String rsTable = rs.getString(3);
if (!supportsSchema || schemaName == null || equals(rsSchema, schemaName, schemaCaseSensitive)){
if (equals(rsTable, tableName, tableCaseSensitive))
cnt++;
}
}
if (cnt > 1){
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "TABLE_EXIST", "More than one table match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + "))!", null);
throw new DBException("More than one table match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + "))!");
}
return cnt == 1;
}finally{
close(rs);
}
}
/**
gmantele
committed
* <p>Tell whether the specified column exists in the specified table of the database.
* To do so, it is using the given {@link DatabaseMetaData} object to query the database and list all existing columns.</p>
*
* <p><i><b>Important note:</b>
* If schemas are not supported by this connection but a schema name is even though provided in parameter,
* the table name will be prefixed by the schema name.
gmantele
committed
* The research will then be done with NULL as schema name and this prefixed table name.
* </i></p>
*
* <p><i>Note:
* Test on the schema name is done considering the case sensitivity indicated by the translator
* (see {@link JDBCTranslator#isCaseSensitive(IdentifierField)}).
gmantele
committed
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
* </i></p>
*
* <p><i>Note:
* This function is used by {@link #loadSchemas(TAPTable, TAPMetadata, Statement)}, {@link #loadTables(TAPTable, TAPMetadata, Statement)}
* and {@link #loadColumns(TAPTable, List, Statement)}.
* </i></p>
*
* @param schemaName DB name of the table schema. <i>MAY BE NULL</i>
* @param tableName DB name of the table containing the column to search. <i>MAY BE NULL</i>
* @param columnName DB name of the column to search.
* @param dbMeta Metadata about the database, and mainly the list of all existing tables.
*
* @return <i>true</i> if the specified column exists, <i>false</i> otherwise.
*
* @throws SQLException If any error occurs while interrogating the database about existing columns.
*/
protected boolean isColumnExisting(String schemaName, String tableName, String columnName, final DatabaseMetaData dbMeta) throws DBException, SQLException{
if (columnName == null || columnName.length() == 0)
return true;
// Determine the case sensitivity to use for the equality test:
boolean schemaCaseSensitive = translator.isCaseSensitive(IdentifierField.SCHEMA);
boolean tableCaseSensitive = translator.isCaseSensitive(IdentifierField.TABLE);
boolean columnCaseSensitive = translator.isCaseSensitive(IdentifierField.COLUMN);
ResultSet rsT = null, rsC = null;
try{
/* Note:
*
* The DatabaseMetaData.getColumns(....) function does not work properly
* with the SQLite driver: when all parameters are set to null, meaning all columns of the database
* must be returned, absolutely no rows are selected.
*
* The solution proposed here, is to first search all (matching) tables, and then for each table get
* all its columns and find the matching one(s).
*/
// List all matching tables:
if (supportsSchema){
String schemaPattern = schemaCaseSensitive ? schemaName : null;
String tablePattern = tableCaseSensitive ? tableName : null;
rsT = dbMeta.getTables(null, schemaPattern, tablePattern, null);
}else{
String tablePattern = tableCaseSensitive ? tableName : null;
rsT = dbMeta.getTables(null, null, tablePattern, null);
}
// For each matching table:
int cnt = 0;
String columnPattern = columnCaseSensitive ? columnName : null;
while(rsT.next()){
String rsSchema = nullifyIfNeeded(rsT.getString(2));
String rsTable = rsT.getString(3);
// test the schema name:
if (!supportsSchema || schemaName == null || equals(rsSchema, schemaName, schemaCaseSensitive)){
// test the table name:
if ((tableName == null || equals(rsTable, tableName, tableCaseSensitive))){
// list its columns:
rsC = dbMeta.getColumns(null, rsSchema, rsTable, columnPattern);
// count all matching columns:
while(rsC.next()){
String rsColumn = rsC.getString(4);
if (equals(rsColumn, columnName, columnCaseSensitive))
cnt++;
}
close(rsC);
}
}
}
if (cnt > 1){
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "COLUMN_EXIST", "More than one column match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + ") && column=" + columnName + " (case sensitive?" + columnCaseSensitive + "))!", null);
throw new DBException("More than one column match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + ") && column=" + columnName + " (case sensitive?" + columnCaseSensitive + "))!");
}
return cnt == 1;
}finally{
close(rsT);
close(rsC);
}
}
/*
* <p>Build a table prefix with the given schema name.</p>
*
* <p>By default, this function returns: schemaName + "_".</p>
*
* <p><b>CAUTION:
* This function is used only when schemas are not supported by the DBMS connection.
* It aims to propose an alternative of the schema notion by prefixing the table name by the schema name.
* </b></p>
*
* <p><i>Note:
* If the given schema is NULL or is an empty string, an empty string will be returned.
* Thus, no prefix will be set....which is very useful when the table name has already been prefixed
* (in such case, the DB name of its schema has theoretically set to NULL).
* </i></p>
*
* @param schemaName (DB) Schema name.
*
* @return The corresponding table prefix, or "" if the given schema name is an empty string or NULL.
gmantele
committed
*
protected String getTablePrefix(final String schemaName){
if (schemaName != null && schemaName.trim().length() > 0)
return schemaName + "_";
else
return "";
gmantele
committed
}*/
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
/**
* Tell whether the specified table (using its DB name only) is a standard one or not.
*
* @param dbTableName DB (unqualified) table name.
* @param stdTables List of all tables to consider as the standard ones.
* @param caseSensitive Indicate whether the equality test must be done case sensitively or not.
*
* @return The corresponding {@link STDTable} if the specified table is a standard one,
* NULL otherwise.
*
* @see TAPMetadata#resolveStdTable(String)
*/
protected final STDTable isStdTable(final String dbTableName, final TAPTable[] stdTables, final boolean caseSensitive){
if (dbTableName != null){
for(TAPTable t : stdTables){
if (equals(dbTableName, t.getDBName(), caseSensitive))
return TAPMetadata.resolveStdTable(t.getADQLName());
}
}
return null;
}
/**
* <p>"Execute" the query update. <i>This update must concern ONLY ONE ROW.</i></p>
*
* <p>
* Note that the "execute" action will be different in function of whether batch update queries are supported or not by this connection:
* </p>
* <ul>
* <li>
* If <b>batch update queries are supported</b>, just {@link PreparedStatement#addBatch()} will be called.
* It means, the query will be appended in a list and will be executed only if
* {@link #executeBatchUpdates(PreparedStatement, int)} is then called.
* </li>
* <li>
* If <b>they are NOT supported</b>, {@link PreparedStatement#executeUpdate()} will merely be called.
* </li>
* </ul>
*
* <p>
* Before returning, and only if batch update queries are not supported, this function is ensuring that exactly one row has been updated.
* If it is not the case, a {@link DBException} is thrown.
* </p>
*
* <p><i><b>Important note:</b>
* If the function {@link PreparedStatement#addBatch()} fails by throwing an {@link SQLException}, batch updates
* will be afterwards considered as not supported by this connection. Besides, if this row is the first one in a batch update (parameter indRow=1),
* then, the error will just be logged and an {@link PreparedStatement#executeUpdate()} will be tried. However, if the row is not the first one,
* the error will be logged but also thrown as a {@link DBException}. In both cases, a subsequent call to
* {@link #executeBatchUpdates(PreparedStatement, int)} will have obviously no effect.
* </i></p>
*
* @param stmt {@link PreparedStatement} in which the update query has been prepared.
* @param indRow Index of the row in the whole update process. It is used only for error management purpose.
*
* @throws SQLException If {@link PreparedStatement#executeUpdate()} fails.</i>
* @throws DBException If {@link PreparedStatement#addBatch()} fails and this update does not concern the first row, or if the number of updated rows is different from 1.
*/
protected final void executeUpdate(final PreparedStatement stmt, int indRow) throws SQLException, DBException{
// BATCH INSERTION: (the query is queued and will be executed later)
if (supportsBatchUpdates){
// Add the prepared query in the batch queue of the statement:
try{
stmt.addBatch();
}catch(SQLException se){
if (!isCancelled())
supportsBatchUpdates = false;
/*
* If the error happens for the first row, it is still possible to insert all rows
* with the non-batch function - executeUpdate().
*
* Otherwise, it is impossible to insert the previous batched rows ; an exception must be thrown
* and must stop the whole TAP_SCHEMA initialization.
*/
if (indRow == 1){
if (!isCancelled() && logger != null)
logger.logDB(LogLevel.WARNING, this, "EXEC_UPDATE", "BATCH query impossible => TRYING AGAIN IN A NORMAL EXECUTION (executeUpdate())!", se);
}else{
if (!isCancelled() && logger != null)
logger.logDB(LogLevel.ERROR, this, "EXEC_UPDATE", "BATCH query impossible!", se);
throw new DBException("BATCH query impossible!", se);
}
}
}
// NORMAL INSERTION: (immediate insertion)
if (!supportsBatchUpdates){
// Insert the row prepared in the given statement:
int nbRowsWritten = stmt.executeUpdate();
// Check the row has been inserted with success:
if (nbRowsWritten != 1){
if (logger != null)
logger.logDB(LogLevel.ERROR, this, "EXEC_UPDATE", "ROW " + indRow + " not inserted!", null);
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
throw new DBException("ROW " + indRow + " not inserted!");
}
}
}
/**
* <p>Execute all batched queries.</p>
*
* <p>To do so, {@link PreparedStatement#executeBatch()} and then, if the first was successful, {@link PreparedStatement#clearBatch()} is called.</p>
*
* <p>
* Before returning, this function is ensuring that exactly the given number of rows has been updated.
* If it is not the case, a {@link DBException} is thrown.
* </p>
*
* <p><i>Note:
* This function has no effect if batch queries are not supported.
* </i></p>
*
* <p><i><b>Important note:</b>
* In case {@link PreparedStatement#executeBatch()} fails by throwing an {@link SQLException},
* batch update queries will be afterwards considered as not supported by this connection.
* </i></p>
*
* @param stmt {@link PreparedStatement} in which the update query has been prepared.
* @param nbRows Number of rows that should be updated.
*
* @throws DBException If {@link PreparedStatement#executeBatch()} fails, or if the number of updated rows is different from the given one.
*/
protected final void executeBatchUpdates(final PreparedStatement stmt, int nbRows) throws DBException{
if (supportsBatchUpdates){
// Execute all the batch queries:
int[] rows;
try{
rows = stmt.executeBatch();
}catch(SQLException se){
if (!isCancelled()){