1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 package org.apache.openoffice.comp.sdbc.dbtools.util; 23 24 import java.util.Map; 25 import java.util.TreeMap; 26 27 import org.apache.openoffice.comp.sdbc.dbtools.comphelper.CompHelper; 28 import org.apache.openoffice.comp.sdbc.dbtools.sdbcx.OColumnContainer.ExtraColumnInfo; 29 30 import com.sun.star.beans.PropertyValue; 31 import com.sun.star.beans.PropertyVetoException; 32 import com.sun.star.beans.UnknownPropertyException; 33 import com.sun.star.beans.XPropertySet; 34 import com.sun.star.beans.XPropertySetInfo; 35 import com.sun.star.container.ElementExistException; 36 import com.sun.star.container.XChild; 37 import com.sun.star.container.XIndexAccess; 38 import com.sun.star.container.XNameAccess; 39 import com.sun.star.io.IOException; 40 import com.sun.star.io.XInputStream; 41 import com.sun.star.lang.IllegalArgumentException; 42 import com.sun.star.lang.IndexOutOfBoundsException; 43 import com.sun.star.lang.WrappedTargetException; 44 import com.sun.star.sdb.XOfficeDatabaseDocument; 45 import com.sun.star.sdbc.ColumnValue; 46 import com.sun.star.sdbc.DataType; 47 import com.sun.star.sdbc.KeyRule; 48 import com.sun.star.sdbc.SQLException; 49 import com.sun.star.sdbc.XConnection; 50 import com.sun.star.sdbc.XDataSource; 51 import com.sun.star.sdbc.XDatabaseMetaData; 52 import com.sun.star.sdbc.XParameters; 53 import com.sun.star.sdbc.XResultSet; 54 import com.sun.star.sdbc.XResultSetMetaData; 55 import com.sun.star.sdbc.XResultSetMetaDataSupplier; 56 import com.sun.star.sdbc.XRow; 57 import com.sun.star.sdbc.XRowUpdate; 58 import com.sun.star.sdbc.XStatement; 59 import com.sun.star.sdbcx.KeyType; 60 import com.sun.star.sdbcx.XAppend; 61 import com.sun.star.sdbcx.XColumnsSupplier; 62 import com.sun.star.sdbcx.XKeysSupplier; 63 import com.sun.star.uno.Any; 64 import com.sun.star.uno.AnyConverter; 65 import com.sun.star.uno.Type; 66 import com.sun.star.uno.TypeClass; 67 import com.sun.star.uno.UnoRuntime; 68 import com.sun.star.util.Date; 69 import com.sun.star.util.DateTime; 70 import com.sun.star.util.Time; 71 72 public class DbTools { 73 private static class NameComponentSupport { 74 boolean useCatalogs; 75 boolean useSchemas; 76 NameComponentSupport(boolean useCatalogs, boolean useSchemas)77 NameComponentSupport(boolean useCatalogs, boolean useSchemas) { 78 this.useCatalogs = useCatalogs; 79 this.useSchemas = useSchemas; 80 } 81 } 82 83 public static class NameComponents { 84 private String catalog = ""; 85 private String schema = ""; 86 private String table = ""; 87 NameComponents(String catalog, String schema, String table)88 public NameComponents(String catalog, String schema, String table) { 89 this.catalog = catalog; 90 this.schema = schema; 91 this.table = table; 92 } 93 NameComponents()94 public NameComponents() { 95 } 96 getCatalog()97 public String getCatalog() { 98 return catalog; 99 } 100 setCatalog(String catalog)101 public void setCatalog(String catalog) { 102 this.catalog = catalog; 103 } 104 getSchema()105 public String getSchema() { 106 return schema; 107 } 108 setSchema(String schema)109 public void setSchema(String schema) { 110 this.schema = schema; 111 } 112 getTable()113 public String getTable() { 114 return table; 115 } 116 setTable(String table)117 public void setTable(String table) { 118 this.table = table; 119 } 120 } 121 getNameComponentSupport(XDatabaseMetaData metadata, ComposeRule composeRule)122 private static NameComponentSupport getNameComponentSupport(XDatabaseMetaData metadata, ComposeRule composeRule) throws SQLException { 123 switch (composeRule) { 124 case InTableDefinitions: 125 return new NameComponentSupport( 126 metadata.supportsCatalogsInTableDefinitions(), metadata.supportsSchemasInTableDefinitions()); 127 case InIndexDefinitions: 128 return new NameComponentSupport( 129 metadata.supportsCatalogsInIndexDefinitions(), metadata.supportsSchemasInIndexDefinitions()); 130 case InDataManipulation: 131 return new NameComponentSupport( 132 metadata.supportsCatalogsInDataManipulation(), metadata.supportsSchemasInDataManipulation()); 133 case InProcedureCalls: 134 return new NameComponentSupport( 135 metadata.supportsCatalogsInProcedureCalls(), metadata.supportsSchemasInProcedureCalls()); 136 case InPrivilegeDefinitions: 137 return new NameComponentSupport( 138 metadata.supportsCatalogsInPrivilegeDefinitions(), metadata.supportsSchemasInPrivilegeDefinitions()); 139 case Complete: 140 return new NameComponentSupport( 141 true, true); 142 default: 143 throw new UnsupportedOperationException("Invalid/unknown enum value"); 144 } 145 } 146 147 /** compose a complete table name from it's up to three parts, regarding to the database meta data composing rules 148 */ composeTableName( XDatabaseMetaData metadata, String catalog, String schema, String table, boolean quote, ComposeRule composeRule)149 public static String composeTableName( 150 XDatabaseMetaData metadata, String catalog, String schema, String table, boolean quote, ComposeRule composeRule) throws SQLException { 151 if (metadata == null) { 152 return ""; 153 } 154 String quoteString = metadata.getIdentifierQuoteString(); 155 NameComponentSupport nameComponentSupport = getNameComponentSupport(metadata, composeRule); 156 157 StringBuilder composedName = new StringBuilder(); 158 159 String catalogSeparator = ""; 160 boolean catalogAtStart = true; 161 if (!catalog.isEmpty() && nameComponentSupport.useCatalogs) { 162 catalogSeparator = metadata.getCatalogSeparator(); 163 catalogAtStart = metadata.isCatalogAtStart(); 164 if (catalogAtStart && !catalogSeparator.isEmpty()) { 165 composedName.append(quote ? quoteName(quoteString, catalog) : catalog); 166 composedName.append(catalogSeparator); 167 } 168 } 169 if (!schema.isEmpty() && nameComponentSupport.useSchemas) { 170 composedName.append(quote ? quoteName(quoteString, schema) : schema); 171 composedName.append('.'); 172 } 173 composedName.append(quote ? quoteName(quoteString, table) : table); 174 if (!catalog.isEmpty() && !catalogAtStart && !catalogSeparator.isEmpty() && nameComponentSupport.useCatalogs) { 175 composedName.append(catalogSeparator); 176 composedName.append(quote ? quoteName(quoteString, catalog) : catalog); 177 } 178 return composedName.toString(); 179 } 180 composeTableName( XDatabaseMetaData metadata, XPropertySet table, ComposeRule composeRule, boolean suppressCatalog, boolean suppressSchema, boolean shouldQuote)181 public static String composeTableName( 182 XDatabaseMetaData metadata, XPropertySet table, ComposeRule composeRule, 183 boolean suppressCatalog, boolean suppressSchema, boolean shouldQuote) throws SQLException { 184 NameComponents nameComponents = getTableNameComponents(table); 185 return doComposeTableName(metadata, 186 suppressCatalog ? "" : nameComponents.getCatalog(), 187 suppressSchema ? "" : nameComponents.getSchema(), 188 nameComponents.getTable(), 189 shouldQuote, composeRule); 190 } 191 192 /** check if a specific property is enabled in the info sequence 193 * @deprecated 194 * Use getBooleanDataSourceSetting instead, which cares for the default of the property itself, 195 * instead of spreading this knowledge through all callers. 196 */ isDataSourcePropertyEnabled(Object object, String property, boolean defaultValue)197 public static boolean isDataSourcePropertyEnabled(Object object, String property, boolean defaultValue) throws SQLException { 198 try { 199 boolean enabled = defaultValue; 200 XPropertySet properties = UnoRuntime.queryInterface(XPropertySet.class, findDataSource(object)); 201 if (properties != null) { 202 PropertyValue[] info = (PropertyValue[]) AnyConverter.toArray(properties.getPropertyValue("Info")); 203 for (PropertyValue propertyValue : info) { 204 if (propertyValue.Name.equals(property)) { 205 enabled = AnyConverter.toBoolean(propertyValue.Value); 206 break; 207 } 208 } 209 } 210 return enabled; 211 } catch (IllegalArgumentException | WrappedTargetException | UnknownPropertyException exception) { 212 throw new SQLException("Error", object, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 213 } 214 } 215 216 /** search the parent hierarchy for a data source. 217 */ findDataSource(Object parent)218 public static XDataSource findDataSource(Object parent) { 219 XOfficeDatabaseDocument databaseDocument = UnoRuntime.queryInterface(XOfficeDatabaseDocument.class, parent); 220 XDataSource dataSource = null; 221 if (databaseDocument != null) { 222 dataSource = databaseDocument.getDataSource(); 223 } 224 if (dataSource == null) { 225 dataSource = UnoRuntime.queryInterface(XDataSource.class, parent); 226 } 227 if (dataSource == null) { 228 XChild child = UnoRuntime.queryInterface(XChild.class, parent); 229 if (child != null) { 230 dataSource = findDataSource(child.getParent()); 231 } 232 } 233 return dataSource; 234 } 235 doComposeTableName(XDatabaseMetaData metadata, String catalog, String schema, String table, boolean shouldQuote, ComposeRule composeRule)236 public static String doComposeTableName(XDatabaseMetaData metadata, String catalog, String schema, String table, 237 boolean shouldQuote, ComposeRule composeRule) throws SQLException { 238 Osl.ensure(!table.isEmpty(), "At least the table name should be non-empty"); 239 String quoteString = metadata.getIdentifierQuoteString(); 240 NameComponentSupport nameComponentSupport = getNameComponentSupport(metadata, composeRule); 241 242 StringBuilder composedName = new StringBuilder(); 243 String catalogSeparator = ""; 244 boolean catalogAtStart = true; 245 if (!catalog.isEmpty() && nameComponentSupport.useCatalogs) { 246 catalogSeparator = metadata.getCatalogSeparator(); 247 catalogAtStart = metadata.isCatalogAtStart(); 248 249 if (catalogAtStart && !catalogSeparator.isEmpty()) { 250 composedName.append(shouldQuote ? quoteName(quoteString, catalog) : catalog); 251 composedName.append(catalogSeparator); 252 } 253 } 254 255 if (!schema.isEmpty() && nameComponentSupport.useSchemas) { 256 composedName.append(shouldQuote ? quoteName(quoteString, schema) : schema); 257 composedName.append("."); 258 } 259 260 composedName.append(shouldQuote ? quoteName(quoteString, table) : table); 261 262 if (!catalog.isEmpty() && !catalogAtStart && !catalogSeparator.isEmpty() && nameComponentSupport.useCatalogs) { 263 composedName.append(catalogSeparator); 264 composedName.append(shouldQuote ? quoteName(quoteString, catalog) : catalog); 265 } 266 return composedName.toString(); 267 } 268 269 /** composes a table name for usage in a SELECT statement 270 * 271 * This includes quoting of the table as indicated by the connection's meta data, plus respecting 272 * the settings "UseCatalogInSelect" and "UseSchemaInSelect", which might be present 273 * in the data source which the connection belongs to. 274 */ composeTableNameForSelect(XConnection connection, String catalog, String schema, String table)275 public static String composeTableNameForSelect(XConnection connection, String catalog, 276 String schema, String table) throws SQLException { 277 boolean useCatalogInSelect = isDataSourcePropertyEnabled(connection, "UseCatalogInSelect", true); 278 boolean useSchemaInSelect = isDataSourcePropertyEnabled(connection, "UseSchemaInSelect", true); 279 return doComposeTableName(connection.getMetaData(), useCatalogInSelect ? catalog : "", 280 useSchemaInSelect ? schema : "", table, true, ComposeRule.InDataManipulation); 281 } 282 283 /** composes a table name for usage in a SELECT statement 284 * 285 * This includes quoting of the table as indicated by the connection's meta data, plus respecting 286 * the settings "UseCatalogInSelect" and "UseSchemaInSelect", which might be present 287 * in the data source which the connection belongs to. 288 */ composeTableNameForSelect(XConnection connection, XPropertySet table)289 public static String composeTableNameForSelect(XConnection connection, XPropertySet table) throws SQLException { 290 NameComponents nameComponents = getTableNameComponents(table); 291 return composeTableNameForSelect(connection, nameComponents.getCatalog(), nameComponents.getSchema(), nameComponents.getTable()); 292 } 293 getTableNameComponents(XPropertySet table)294 private static NameComponents getTableNameComponents(XPropertySet table) throws SQLException { 295 try { 296 NameComponents nameComponents = new NameComponents(); 297 XPropertySetInfo propertySetInfo = table.getPropertySetInfo(); 298 if (propertySetInfo != null && propertySetInfo.hasPropertyByName(PropertyIds.NAME.name)) { 299 if (propertySetInfo.hasPropertyByName(PropertyIds.CATALOGNAME.name) 300 && propertySetInfo.hasPropertyByName(PropertyIds.SCHEMANAME.name)) { 301 nameComponents.setCatalog(AnyConverter.toString(table.getPropertyValue(PropertyIds.CATALOGNAME.name))); 302 nameComponents.setSchema(AnyConverter.toString(table.getPropertyValue(PropertyIds.SCHEMANAME.name))); 303 } 304 nameComponents.setTable(AnyConverter.toString(table.getPropertyValue(PropertyIds.NAME.name))); 305 } else { 306 Osl.ensure(false, "this is not a table object"); 307 } 308 return nameComponents; 309 } catch (IllegalArgumentException | WrappedTargetException | UnknownPropertyException exception) { 310 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 311 } 312 } 313 314 /** quote the given name with the given quote string. 315 */ quoteName(String quote, String name)316 public static String quoteName(String quote, String name) { 317 if (!quote.isEmpty() && quote.codePointAt(0) != ' ') { 318 return quote + name + quote; 319 } 320 return name; 321 } 322 323 /** quote the given table name (which may contain a catalog and a schema) according to the rules provided by the meta data 324 */ quoteTableName(XDatabaseMetaData metadata, String name, ComposeRule composeRule)325 public static String quoteTableName(XDatabaseMetaData metadata, String name, ComposeRule composeRule) throws SQLException { 326 NameComponents nameComponents = qualifiedNameComponents(metadata, name, composeRule); 327 return doComposeTableName(metadata, nameComponents.getCatalog(), nameComponents.getSchema(), nameComponents.getTable(), true, composeRule); 328 } 329 330 /** split a fully qualified table name (including catalog and schema, if applicable) into its component parts. 331 * @param _rxConnMetaData meta data describing the connection where you got the table name from 332 * @param _rQualifiedName fully qualified table name 333 * @param _eComposeRule where do you need the name for 334 * @return the NameComponents object with the catalog, schema and table 335 */ qualifiedNameComponents(XDatabaseMetaData _rxConnMetaData, String _rQualifiedName, ComposeRule _eComposeRule)336 public static NameComponents qualifiedNameComponents(XDatabaseMetaData _rxConnMetaData, String _rQualifiedName, 337 ComposeRule _eComposeRule) throws SQLException { 338 Osl.ensure(_rxConnMetaData, "QualifiedNameComponents : invalid meta data!"); 339 340 NameComponentSupport aNameComps = getNameComponentSupport( _rxConnMetaData, _eComposeRule ); 341 342 String sSeparator = _rxConnMetaData.getCatalogSeparator(); 343 NameComponents ret = new NameComponents(); 344 345 String sName = _rQualifiedName; 346 // do we have catalogs ? 347 if ( aNameComps.useCatalogs ) { 348 if (_rxConnMetaData.isCatalogAtStart()) { 349 // search for the catalog name at the beginning 350 int nIndex = sName.indexOf(sSeparator); 351 if (-1 != nIndex) { 352 ret.setCatalog(sName.substring(0, nIndex)); 353 sName = sName.substring(nIndex + 1); 354 } 355 } else { 356 // Katalogname am Ende 357 int nIndex = sName.lastIndexOf(sSeparator); 358 if (-1 != nIndex) { 359 ret.setCatalog(sName.substring(nIndex + 1)); 360 sName = sName.substring(0, nIndex); 361 } 362 } 363 } 364 365 if ( aNameComps.useSchemas ) { 366 int nIndex = sName.indexOf('.'); 367 //Osl.ensure(-1 != nIndex, "QualifiedNameComponents : no schema separator!"); 368 if ( nIndex != -1 ) { 369 ret.setSchema(sName.substring(0, nIndex)); 370 } 371 sName = sName.substring(nIndex + 1); 372 } 373 374 ret.setTable(sName); 375 return ret; 376 } 377 378 /** creates a SQL CREATE TABLE statement 379 * 380 * @param descriptor 381 * The descriptor of the new table. 382 * @param connection 383 * The connection. 384 * @param helper 385 * Allow to add special SQL constructs. 386 * @param createPattern 387 * 388 * @return 389 * The CREATE TABLE statement. 390 */ createSqlCreateTableStatement(XPropertySet descriptor, XConnection connection, ISQLStatementHelper helper, String createPattern)391 public static String createSqlCreateTableStatement(XPropertySet descriptor, XConnection connection, 392 ISQLStatementHelper helper, String createPattern) throws SQLException { 393 394 String sql = createStandardCreateStatement(descriptor, connection, helper, createPattern); 395 final String keyStatement = createStandardKeyStatement(descriptor, connection); 396 if (!keyStatement.isEmpty()) { 397 sql += keyStatement; 398 } else { 399 sql += ')'; 400 } 401 return sql; 402 } 403 404 /** creates the standard sql create table statement without the key part. 405 * @param descriptor 406 * The descriptor of the new table. 407 * @param connection 408 * The connection. 409 * @param helper 410 * Allow to add special SQL constructs. 411 * @param createPattern 412 * 413 */ createStandardCreateStatement(XPropertySet descriptor, XConnection connection, ISQLStatementHelper helper, String createPattern)414 public static String createStandardCreateStatement(XPropertySet descriptor, XConnection connection, 415 ISQLStatementHelper helper, String createPattern) throws SQLException { 416 try { 417 XDatabaseMetaData metadata = connection.getMetaData(); 418 String catalog = AnyConverter.toString(descriptor.getPropertyValue("CatalogName")); 419 String schema = AnyConverter.toString(descriptor.getPropertyValue("SchemaName")); 420 String table = AnyConverter.toString(descriptor.getPropertyValue("Name")); 421 String composedName = composeTableName(metadata, catalog, schema, table, true, ComposeRule.InTableDefinitions); 422 if (composedName.isEmpty()) { 423 throw new SQLException(SharedResources.getInstance().getResourceString(Resources.STR_ERRORMSG_SEQUENCE), connection, 424 StandardSQLState.SQL_FUNCTION_SEQUENCE_ERROR.text(), 0, null); 425 } 426 427 XIndexAccess columns = null; 428 XColumnsSupplier columnsSupplier = UnoRuntime.queryInterface(XColumnsSupplier.class, descriptor); 429 if (columnsSupplier != null) { 430 columns = UnoRuntime.queryInterface(XIndexAccess.class, columnsSupplier.getColumns()); 431 } 432 if (columns == null || columns.getCount() <= 0) { 433 throw new SQLException(SharedResources.getInstance().getResourceString(Resources.STR_ERRORMSG_SEQUENCE), connection, 434 StandardSQLState.SQL_FUNCTION_SEQUENCE_ERROR.text(), 0, null); 435 } 436 437 int columnCount = columns.getCount(); 438 StringBuilder columnText = new StringBuilder(); 439 String separator = ""; 440 for (int i = 0; i < columnCount; i++) { 441 XPropertySet columnProperties; 442 columnProperties = AnyConverter.toObject(XPropertySet.class, columns.getByIndex(i)); 443 if (columnProperties != null) { 444 columnText.append(separator); 445 separator = ","; 446 columnText.append(createStandardColumnPart(columnProperties, connection, helper, createPattern)); 447 } 448 } 449 450 return String.format("CREATE TABLE %s (%s", composedName, columnText.toString()); 451 } catch (IllegalArgumentException | WrappedTargetException | UnknownPropertyException | IndexOutOfBoundsException exception) { 452 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 453 } 454 } 455 456 /** creates the standard sql statement for the column part of a create table statement. 457 * @param columnProperties 458 * The descriptor of the column. 459 * @param connection 460 * The connection. 461 * @param helper 462 * Allow to add special SQL constructs. 463 * @param createPattern 464 * 465 */ createStandardColumnPart(XPropertySet columnProperties, XConnection connection, ISQLStatementHelper helper, String createPattern)466 public static String createStandardColumnPart(XPropertySet columnProperties, XConnection connection, 467 ISQLStatementHelper helper, String createPattern) throws SQLException { 468 try { 469 XDatabaseMetaData metadata = connection.getMetaData(); 470 471 final String quoteString = metadata.getIdentifierQuoteString(); 472 final StringBuilder sql = new StringBuilder(); 473 sql.append(quoteName(quoteString, AnyConverter.toString(columnProperties.getPropertyValue("Name")))); 474 sql.append(' '); 475 476 String typename = AnyConverter.toString(columnProperties.getPropertyValue("TypeName")); 477 int datatype = AnyConverter.toInt(columnProperties.getPropertyValue("Type")); 478 int precision = AnyConverter.toInt(columnProperties.getPropertyValue("Precision")); 479 int scale = AnyConverter.toInt(columnProperties.getPropertyValue("Scale")); 480 boolean isAutoIncrement = AnyConverter.toBoolean(columnProperties.getPropertyValue("IsAutoIncrement")); 481 482 // check if the user enter a specific string to create autoincrement values 483 String autoIncrementValue = ""; 484 XPropertySetInfo columnPropertiesInfo = columnProperties.getPropertySetInfo(); 485 if (columnPropertiesInfo != null && columnPropertiesInfo.hasPropertyByName("AutoIncrementCreation")) { 486 autoIncrementValue = AnyConverter.toString(columnProperties.getPropertyValue("AutoIncrementCreation")); 487 } 488 489 // look if we have to use precisions 490 boolean useLiteral = false; 491 String prefix = ""; 492 String postfix =""; 493 String createParams = ""; 494 XResultSet results = null; 495 try { 496 results = metadata.getTypeInfo(); 497 if (results != null) { 498 XRow row = UnoRuntime.queryInterface(XRow.class, results); 499 while (results.next()) { 500 String typeName2Cmp = row.getString(1); 501 int nType = row.getShort(2); 502 prefix = row.getString(4); 503 postfix = row.getString(5); 504 createParams = row.getString(6); 505 // first identical type will be used if typename is empty 506 if (typename.isEmpty() && nType == datatype) { 507 typename = typeName2Cmp; 508 } 509 if (typename.equalsIgnoreCase(typeName2Cmp) && nType == datatype && !row.wasNull() && !createParams.isEmpty()) { 510 useLiteral = true; 511 break; 512 } 513 } 514 } 515 } finally { 516 CompHelper.disposeComponent(results); 517 } 518 519 int index = 0; 520 if (!autoIncrementValue.isEmpty() && (index = typename.indexOf(autoIncrementValue)) != -1) { 521 typename = typename.substring(0, index); 522 } 523 524 if ((precision > 0 || scale > 0) && useLiteral) { 525 int parenPos = typename.indexOf('('); 526 if (parenPos == -1) { 527 sql.append(typename); 528 sql.append('('); 529 } else { 530 sql.append(typename.substring(0, ++parenPos)); 531 } 532 533 if (precision > 0 && datatype != DataType.TIMESTAMP) { 534 sql.append(precision); 535 if (scale > 0 || (!createPattern.isEmpty() && createParams.indexOf(createPattern) != -1)) { 536 sql.append(','); 537 } 538 } 539 if (scale > 0 || (!createPattern.isEmpty() && createParams.indexOf(createPattern) != -1) || datatype == DataType.TIMESTAMP) { 540 sql.append(scale); 541 } 542 if (parenPos == -1) { 543 sql.append(')'); 544 } else { 545 parenPos = typename.indexOf(')', parenPos); 546 sql.append(typename.substring(parenPos)); 547 } 548 } else { 549 sql.append(typename); // simply add the type name 550 } 551 552 String defaultValue = AnyConverter.toString(columnProperties.getPropertyValue("DefaultValue")); 553 if (!defaultValue.isEmpty()) { 554 sql.append(" DEFAULT "); 555 sql.append(prefix); 556 sql.append(defaultValue); 557 sql.append(postfix); 558 } 559 560 if (AnyConverter.toInt(columnProperties.getPropertyValue("IsNullable")) == ColumnValue.NO_NULLS) { 561 sql.append(" NOT NULL"); 562 } 563 564 if (isAutoIncrement && !autoIncrementValue.isEmpty()) { 565 sql.append(' '); 566 sql.append(autoIncrementValue); 567 } 568 569 if (helper != null) { 570 helper.addComment(columnProperties, sql); 571 } 572 573 return sql.toString(); 574 } catch (IllegalArgumentException | WrappedTargetException | UnknownPropertyException exception) { 575 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 576 } 577 } 578 579 /** creates the standard sql statement for the key part of a create table statement. 580 * @param descriptor 581 * The descriptor of the new table. 582 * @param connection 583 * The connection. 584 */ createStandardKeyStatement(XPropertySet descriptor, XConnection connection)585 public static String createStandardKeyStatement(XPropertySet descriptor, XConnection connection) throws SQLException { 586 try { 587 XDatabaseMetaData metadata = connection.getMetaData(); 588 StringBuilder sql = new StringBuilder(); 589 590 XKeysSupplier keysSupplier = UnoRuntime.queryInterface(XKeysSupplier.class, descriptor); 591 XIndexAccess keys = keysSupplier.getKeys(); 592 if (keys != null) { 593 boolean hasPrimaryKey = false; 594 for (int i = 0; i < keys.getCount(); i++) { 595 XPropertySet columnProperties = AnyConverter.toObject(XPropertySet.class, keys.getByIndex(i)); 596 if (columnProperties != null) { 597 int keyType = AnyConverter.toInt(columnProperties.getPropertyValue("Type")); 598 XColumnsSupplier columnsSupplier = UnoRuntime.queryInterface(XColumnsSupplier.class, columnProperties); 599 XIndexAccess columns = UnoRuntime.queryInterface(XIndexAccess.class, columnsSupplier.getColumns()); 600 if (columns == null || columns.getCount() == 0) { 601 throw new SQLException(SharedResources.getInstance().getResourceString(Resources.STR_ERRORMSG_SEQUENCE), connection, 602 StandardSQLState.SQL_FUNCTION_SEQUENCE_ERROR.text(), 0, null); 603 } 604 if (keyType == KeyType.PRIMARY) { 605 if (hasPrimaryKey) { 606 throw new SQLException(SharedResources.getInstance().getResourceString(Resources.STR_ERRORMSG_SEQUENCE), connection, 607 StandardSQLState.SQL_FUNCTION_SEQUENCE_ERROR.text(), 0, null); 608 } 609 hasPrimaryKey = true; 610 sql.append(" PRIMARY KEY "); 611 sql.append(generateColumnNames(columns, metadata)); 612 } else if (keyType == KeyType.UNIQUE) { 613 sql.append(" UNIQUE "); 614 sql.append(generateColumnNames(columns, metadata)); 615 } else if (keyType == KeyType.FOREIGN) { 616 int deleteRule = AnyConverter.toInt(columnProperties.getPropertyValue("DeleteRule")); 617 sql.append(" FOREIGN KEY "); 618 619 String referencedTable = AnyConverter.toString(columnProperties.getPropertyValue("ReferencedTable")); 620 NameComponents nameComponents = qualifiedNameComponents(metadata, referencedTable, ComposeRule.InDataManipulation); 621 String composedName = composeTableName(metadata, nameComponents.getCatalog(), nameComponents.getSchema(), nameComponents.getTable(), 622 true, ComposeRule.InTableDefinitions); 623 if (composedName.isEmpty()) { 624 throw new SQLException(SharedResources.getInstance().getResourceString(Resources.STR_ERRORMSG_SEQUENCE), connection, 625 StandardSQLState.SQL_FUNCTION_SEQUENCE_ERROR.text(), 0, null); 626 } 627 628 sql.append(generateColumnNames(columns, metadata)); 629 630 switch (deleteRule) { 631 case KeyRule.CASCADE: 632 sql.append(" ON DELETE CASCADE "); 633 break; 634 case KeyRule.RESTRICT: 635 sql.append(" ON DELETE RESTRICT "); 636 break; 637 case KeyRule.SET_NULL: 638 sql.append(" ON DELETE SET NULL "); 639 break; 640 case KeyRule.SET_DEFAULT: 641 sql.append(" ON DELETE SET DEFAULT "); 642 break; 643 } 644 } 645 } 646 } 647 } 648 649 if (sql.length() > 0) { 650 sql.append(')'); 651 } 652 return sql.toString(); 653 } catch (IllegalArgumentException | WrappedTargetException | UnknownPropertyException | IndexOutOfBoundsException exception) { 654 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 655 } 656 } 657 generateColumnNames(XIndexAccess columns, XDatabaseMetaData metadata)658 private static String generateColumnNames(XIndexAccess columns, XDatabaseMetaData metadata) throws 659 SQLException, WrappedTargetException, UnknownPropertyException, IllegalArgumentException, IndexOutOfBoundsException { 660 String quote = metadata.getIdentifierQuoteString(); 661 StringBuilder sql = new StringBuilder(" ("); 662 int columnCount = columns.getCount(); 663 String separator = ""; 664 for (int i = 0; i < columnCount; i++) { 665 XPropertySet columnProperties = AnyConverter.toObject(XPropertySet.class, columns.getByIndex(i)); 666 if (columnProperties != null) { 667 sql.append(separator); 668 separator = ","; 669 String columnName = AnyConverter.toString(columnProperties.getPropertyValue("Name")); 670 sql.append(quoteName(quote, columnName)); 671 } 672 } 673 if (columnCount > 0) { 674 sql.append(')'); 675 } 676 return sql.toString(); 677 } 678 679 /** collects the information about auto increment, currency and data type for the given column name. 680 * The column must be quoted, * is also valid. 681 * @param connection 682 * The connection. 683 * @param composedName 684 * The quoted table name. ccc.sss.ttt 685 * @param columnName 686 * The name of the column, or * 687 * @return 688 * The information about the column(s). 689 */ collectColumnInformation(XConnection connection, String composedName, String columnName)690 public static Map<String,ExtraColumnInfo> collectColumnInformation(XConnection connection, String composedName, String columnName) throws SQLException { 691 String sql = String.format("SELECT %s FROM %s WHERE 0 = 1", columnName, composedName); 692 XStatement statement = null; 693 try { 694 statement = connection.createStatement(); 695 XPropertySet statementProperties = UnoRuntime.queryInterface(XPropertySet.class, statement); 696 statementProperties.setPropertyValue(PropertyIds.ESCAPEPROCESSING.name, false); 697 XResultSet results = statement.executeQuery(sql); 698 XResultSetMetaDataSupplier metadataSupplier = UnoRuntime.queryInterface(XResultSetMetaDataSupplier.class, results); 699 XResultSetMetaData metadata = metadataSupplier.getMetaData(); 700 701 Map<String,ExtraColumnInfo> columns = new TreeMap<>(); 702 int count = metadata.getColumnCount(); 703 Osl.ensure(count > 0, "resultset has empty metadata"); 704 for (int i = 1; i <= count; i++) { 705 String newColumnName = metadata.getColumnName(i); 706 ExtraColumnInfo columnInfo = new ExtraColumnInfo(); 707 columnInfo.isAutoIncrement = metadata.isAutoIncrement(i); 708 columnInfo.isCurrency = metadata.isCurrency(i); 709 columnInfo.dataType = metadata.getColumnType(i); 710 columns.put(newColumnName, columnInfo); 711 } 712 return columns; 713 } catch (IllegalArgumentException | WrappedTargetException | UnknownPropertyException | PropertyVetoException exception) { 714 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 715 } finally { 716 CompHelper.disposeComponent(statement); 717 } 718 } 719 720 /** returns the primary key columns of the table 721 */ getPrimaryKeyColumns(XPropertySet table)722 public static XNameAccess getPrimaryKeyColumns(XPropertySet table) throws SQLException { 723 try { 724 XNameAccess keyColumns = null; 725 XKeysSupplier keysSupplier = UnoRuntime.queryInterface(XKeysSupplier.class, table); 726 if (keysSupplier != null) { 727 XIndexAccess keys = keysSupplier.getKeys(); 728 if (keys != null) { 729 int count = keys.getCount(); 730 for (int i = 0; i < count; i++) { 731 XPropertySet propertySet = UnoRuntime.queryInterface(XPropertySet.class, keys.getByIndex(i)); 732 if (propertySet != null) { 733 int keyType = 0; 734 keyType = AnyConverter.toInt(propertySet.getPropertyValue(PropertyIds.TYPE.name)); 735 if (keyType == KeyType.PRIMARY) { 736 XColumnsSupplier columnsSupplier = UnoRuntime.queryInterface(XColumnsSupplier.class, propertySet); 737 keyColumns = columnsSupplier.getColumns(); 738 break; 739 } 740 } 741 } 742 } 743 } 744 return keyColumns; 745 } catch (IndexOutOfBoundsException | IllegalArgumentException | WrappedTargetException | UnknownPropertyException exception) { 746 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 747 } 748 } 749 cloneDescriptorColumns(XPropertySet source, XPropertySet destination)750 public static void cloneDescriptorColumns(XPropertySet source, XPropertySet destination) throws SQLException { 751 XColumnsSupplier sourceColumnsSupplier = UnoRuntime.queryInterface(XColumnsSupplier.class, source); 752 XIndexAccess sourceColumns = UnoRuntime.queryInterface(XIndexAccess.class, sourceColumnsSupplier.getColumns()); 753 754 XColumnsSupplier destinationColumnsSupplier = UnoRuntime.queryInterface(XColumnsSupplier.class, destination); 755 XAppend destinationAppend = UnoRuntime.queryInterface(XAppend.class, destinationColumnsSupplier.getColumns()); 756 757 int count = sourceColumns.getCount(); 758 for (int i = 0; i < count; i++) { 759 try { 760 XPropertySet columnProperties = AnyConverter.toObject(XPropertySet.class, sourceColumns.getByIndex(i)); 761 destinationAppend.appendByDescriptor(columnProperties); 762 } catch (WrappedTargetException | IndexOutOfBoundsException | IllegalArgumentException | ElementExistException exception) { 763 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 764 } 765 } 766 } 767 updateObject(XRowUpdate updatedObject, int columnIndex, Object value)768 public static boolean updateObject(XRowUpdate updatedObject, int columnIndex, Object value) throws SQLException { 769 try { 770 boolean successfullyReRouted = true; 771 Type type = AnyConverter.getType(value); 772 switch (type.getTypeClass().getValue()) { 773 case TypeClass.VOID_value: 774 updatedObject.updateNull(columnIndex); 775 break; 776 case TypeClass.STRING_value: 777 updatedObject.updateString(columnIndex, AnyConverter.toString(value)); 778 break; 779 case TypeClass.BOOLEAN_value: 780 updatedObject.updateBoolean(columnIndex, AnyConverter.toBoolean(value)); 781 break; 782 case TypeClass.BYTE_value: 783 updatedObject.updateByte(columnIndex, AnyConverter.toByte(value)); 784 break; 785 case TypeClass.UNSIGNED_SHORT_value: 786 updatedObject.updateShort(columnIndex, AnyConverter.toUnsignedShort(value)); 787 break; 788 case TypeClass.SHORT_value: 789 updatedObject.updateShort(columnIndex, AnyConverter.toShort(value)); 790 break; 791 case TypeClass.CHAR_value: 792 updatedObject.updateString(columnIndex, Character.toString(AnyConverter.toChar(value))); 793 break; 794 case TypeClass.UNSIGNED_LONG_value: 795 updatedObject.updateInt(columnIndex, AnyConverter.toUnsignedInt(value)); 796 break; 797 case TypeClass.LONG_value: 798 updatedObject.updateInt(columnIndex, AnyConverter.toInt(value)); 799 break; 800 case TypeClass.UNSIGNED_HYPER_value: 801 updatedObject.updateLong(columnIndex, AnyConverter.toUnsignedLong(value)); 802 break; 803 case TypeClass.HYPER_value: 804 updatedObject.updateLong(columnIndex, AnyConverter.toLong(value)); 805 break; 806 case TypeClass.FLOAT_value: 807 updatedObject.updateFloat(columnIndex, AnyConverter.toFloat(value)); 808 break; 809 case TypeClass.DOUBLE_value: 810 updatedObject.updateDouble(columnIndex, AnyConverter.toDouble(value)); 811 break; 812 case TypeClass.SEQUENCE_value: 813 if (AnyConverter.isArray(value)) { 814 Object array = AnyConverter.toArray(value); 815 if (array instanceof byte[]) { 816 updatedObject.updateBytes(columnIndex, (byte[]) array); 817 } else { 818 successfullyReRouted = false; 819 } 820 } else { 821 successfullyReRouted = false; 822 } 823 break; 824 case TypeClass.STRUCT_value: 825 Object object = AnyConverter.toObject(Object.class, value); 826 if (object instanceof Date) { 827 updatedObject.updateDate(columnIndex, (Date)object); 828 } else if (object instanceof Time) { 829 updatedObject.updateTime(columnIndex, (Time)object); 830 } else if (object instanceof DateTime) { 831 updatedObject.updateTimestamp(columnIndex, (DateTime)object); 832 } else { 833 successfullyReRouted = false; 834 } 835 break; 836 case TypeClass.INTERFACE_value: 837 XInputStream inputStream = UnoRuntime.queryInterface(XInputStream.class, AnyConverter.toObject(Object.class, value)); 838 if (inputStream != null) { 839 updatedObject.updateBinaryStream(columnIndex, inputStream, inputStream.available()); 840 } else { 841 successfullyReRouted = false; 842 } 843 break; 844 default: 845 successfullyReRouted = false; 846 } 847 return successfullyReRouted; 848 } catch (IllegalArgumentException | IOException exception) { 849 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 850 } 851 } 852 setObject(XParameters parameters, int index, Object any)853 public static boolean setObject(XParameters parameters, int index, Object any) throws SQLException { 854 Type type = AnyConverter.getType(any); 855 try { 856 boolean successfullyReRouted = true; 857 switch (type.getTypeClass().getValue()) { 858 case TypeClass.HYPER_value: 859 parameters.setLong(index, AnyConverter.toLong(any)); 860 break; 861 case TypeClass.UNSIGNED_HYPER_value: 862 parameters.setLong(index, AnyConverter.toUnsignedLong(any)); 863 break; 864 case TypeClass.VOID_value: 865 parameters.setNull(index, DataType.VARCHAR); 866 break; 867 case TypeClass.STRING_value: 868 parameters.setString(index, AnyConverter.toString(any)); 869 break; 870 case TypeClass.BOOLEAN_value: 871 parameters.setBoolean(index, AnyConverter.toBoolean(any)); 872 break; 873 case TypeClass.BYTE_value: 874 parameters.setByte(index, AnyConverter.toByte(any)); 875 break; 876 case TypeClass.SHORT_value: 877 parameters.setShort(index, AnyConverter.toShort(any)); 878 break; 879 case TypeClass.UNSIGNED_SHORT_value: 880 parameters.setShort(index, AnyConverter.toUnsignedShort(any)); 881 break; 882 case TypeClass.CHAR_value: 883 parameters.setString(index, Character.toString(AnyConverter.toChar(any))); 884 break; 885 case TypeClass.LONG_value: 886 parameters.setInt(index, AnyConverter.toInt(any)); 887 break; 888 case TypeClass.UNSIGNED_LONG_value: 889 parameters.setInt(index, AnyConverter.toUnsignedInt(any)); 890 break; 891 case TypeClass.FLOAT_value: 892 parameters.setFloat(index, AnyConverter.toFloat(any)); 893 break; 894 case TypeClass.DOUBLE_value: 895 parameters.setDouble(index, AnyConverter.toDouble(any)); 896 break; 897 case TypeClass.SEQUENCE_value: 898 if (AnyConverter.isArray(any)) { 899 Object array = AnyConverter.toArray(any); 900 if (array instanceof byte[]) { 901 parameters.setBytes(index, (byte[])array); 902 } else { 903 successfullyReRouted = false; 904 } 905 } else { 906 successfullyReRouted = false; 907 } 908 break; 909 case TypeClass.STRUCT_value: 910 Object object = AnyConverter.toObject(Object.class, any); 911 if (object instanceof Date) { 912 parameters.setDate(index, (Date)object); 913 } else if (object instanceof Time) { 914 parameters.setTime(index, (Time)object); 915 } else if (object instanceof DateTime) { 916 parameters.setTimestamp(index, (DateTime)object); 917 } else { 918 successfullyReRouted = false; 919 } 920 break; 921 case TypeClass.INTERFACE_value: 922 XInputStream inputStream = UnoRuntime.queryInterface(XInputStream.class, AnyConverter.toObject(Object.class, any)); 923 if (inputStream != null) { 924 parameters.setBinaryStream(index, inputStream, inputStream.available()); 925 } else { 926 successfullyReRouted = false; 927 } 928 break; 929 default: 930 successfullyReRouted = false; 931 } 932 return successfullyReRouted; 933 } catch (IllegalArgumentException | IOException exception) { 934 throw new SQLException("Error", Any.VOID, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, exception); 935 } 936 } 937 } 938