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.sdbcx; 23 24 import java.util.ArrayList; 25 import java.util.List; 26 import java.util.Map; 27 import java.util.Set; 28 import java.util.TreeMap; 29 import java.util.TreeSet; 30 31 import org.apache.openoffice.comp.sdbc.dbtools.comphelper.CompHelper; 32 import org.apache.openoffice.comp.sdbc.dbtools.util.ComposeRule; 33 import org.apache.openoffice.comp.sdbc.dbtools.util.DbTools; 34 import org.apache.openoffice.comp.sdbc.dbtools.util.Osl; 35 import org.apache.openoffice.comp.sdbc.dbtools.util.StandardSQLState; 36 37 import com.sun.star.container.ElementExistException; 38 import com.sun.star.sdbc.SQLException; 39 import com.sun.star.sdbc.XDatabaseMetaData; 40 import com.sun.star.sdbc.XResultSet; 41 import com.sun.star.sdbc.XRow; 42 import com.sun.star.sdbcx.KeyType; 43 import com.sun.star.uno.Any; 44 import com.sun.star.uno.UnoRuntime; 45 46 public class SqlTableHelper { 47 public static class ColumnDescription { 48 public String columnName; 49 public int type; 50 public String typeName; 51 public int columnSize; 52 public int decimalDigits; 53 public int nullable; 54 public String remarks; 55 public String defaultValue; 56 public int ordinalPosition; 57 } 58 59 private static class KeyProperties { 60 ArrayList<String> columnNames = new ArrayList<>(); 61 String referencedTable; 62 int type; 63 int updateRule; 64 int deleteRule; 65 KeyProperties(String referencedTable, int type, int updateRule, int deleteRule)66 KeyProperties(String referencedTable, int type, int updateRule, int deleteRule) { 67 this.referencedTable = referencedTable; 68 this.type = type; 69 this.updateRule = updateRule; 70 this.deleteRule = deleteRule; 71 } 72 } 73 readColumns(XDatabaseMetaData metadata, String catalogName, String schema, String table)74 public List<ColumnDescription> readColumns(XDatabaseMetaData metadata, String catalogName, String schema, String table) throws SQLException { 75 Object catalog = Any.VOID; 76 if (!catalogName.isEmpty()) { 77 catalog = catalogName; 78 } 79 80 XResultSet results = metadata.getColumns(catalog, schema, table, "%"); 81 List<ColumnDescription> columnDescriptions = collectColumnDescriptions(results); 82 sanitizeColumnDescriptions(columnDescriptions); 83 84 List<ColumnDescription> columnsByOrdinal = new ArrayList<>(columnDescriptions); 85 for (ColumnDescription columnDescription : columnDescriptions) { 86 columnsByOrdinal.set(columnDescription.ordinalPosition - 1, columnDescription); 87 } 88 return columnsByOrdinal; 89 } 90 collectColumnDescriptions(XResultSet results)91 private List<ColumnDescription> collectColumnDescriptions(XResultSet results) throws SQLException { 92 List<ColumnDescription> columns = new ArrayList<>(); 93 XRow row = UnoRuntime.queryInterface(XRow.class, results); 94 while (results.next()) { 95 ColumnDescription columnDescription = new ColumnDescription(); 96 columnDescription.columnName = row.getString(4); 97 columnDescription.type = row.getInt(5); 98 columnDescription.typeName = row.getString(6); 99 columnDescription.columnSize = row.getInt(7); 100 columnDescription.decimalDigits = row.getInt(9); 101 columnDescription.nullable = row.getInt(11); 102 columnDescription.remarks = row.getString(12); 103 columnDescription.defaultValue = row.getString(13); 104 columnDescription.ordinalPosition = row.getInt(17); 105 columns.add(columnDescription); 106 } 107 return columns; 108 } 109 sanitizeColumnDescriptions(List<ColumnDescription> columnDescriptions)110 private void sanitizeColumnDescriptions(List<ColumnDescription> columnDescriptions) { 111 if (columnDescriptions.isEmpty()) { 112 return; 113 } 114 Set<Integer> usedOrdinals = new TreeSet<>(); 115 int maxOrdinal = Integer.MIN_VALUE; 116 for (ColumnDescription columnDescription : columnDescriptions) { 117 usedOrdinals.add(columnDescription.ordinalPosition); 118 if (maxOrdinal < columnDescription.ordinalPosition) { 119 maxOrdinal = columnDescription.ordinalPosition; 120 } 121 } 122 // we need to have as many different ordinals as we have different columns 123 boolean hasDuplicates = usedOrdinals.size() != columnDescriptions.size(); 124 // and it needs to be a continuous range 125 boolean hasGaps = (maxOrdinal - usedOrdinals.iterator().next() + 1) != columnDescriptions.size(); 126 // if that's not the case, normalize it 127 Osl.ensure(!hasDuplicates && !hasGaps, "database provided invalid ORDINAL_POSITION values!"); 128 // what's left is that the range might not be from 1 to <column count>, but for instance 129 // 0 to <column count>-1. 130 int offset = usedOrdinals.iterator().next() - 1; 131 for (ColumnDescription columnDescription : columnDescriptions) { 132 columnDescription.ordinalPosition -= offset; 133 } 134 } 135 readKeys(XDatabaseMetaData metadata, String catalogName, String schemaName, String tableName, boolean isCaseSensitive, OTable table)136 public Map<String, OKey> readKeys(XDatabaseMetaData metadata, String catalogName, String schemaName, String tableName, 137 boolean isCaseSensitive, OTable table) throws SQLException { 138 Map<String, OKey> keys = new TreeMap<>(); 139 OKey primaryKey = readPrimaryKey(metadata, catalogName, schemaName, tableName, isCaseSensitive, table); 140 keys.put(primaryKey.getName(), primaryKey); 141 readForeignKeys(metadata, catalogName, schemaName, tableName, isCaseSensitive, keys, table); 142 return keys; 143 } 144 readPrimaryKey(XDatabaseMetaData metadata, String catalogName, String schemaName, String tableName, boolean isCaseSensitive, OTable table)145 private OKey readPrimaryKey(XDatabaseMetaData metadata, 146 String catalogName, String schemaName, String tableName, boolean isCaseSensitive, OTable table) throws SQLException { 147 Object catalog = Any.VOID; 148 if (!catalogName.isEmpty()) { 149 catalog = catalogName; 150 } 151 XResultSet results = null; 152 try { 153 ArrayList<String> columns = new ArrayList<>(); 154 boolean alreadyFetched = false; 155 String pkName = ""; 156 OKey key = null; 157 results = metadata.getPrimaryKeys(catalog, schemaName, tableName); 158 if (results != null) { 159 XRow row = UnoRuntime.queryInterface(XRow.class, results); 160 while (results.next()) { 161 String columnName = row.getString(4); 162 columns.add(columnName); 163 if (!alreadyFetched) { 164 alreadyFetched = true; 165 pkName = row.getString(6); 166 } 167 } 168 key = new OKey(pkName, isCaseSensitive, "", KeyType.PRIMARY, 0, 0, columns, table); 169 } 170 return key; 171 } catch (ElementExistException elementExistException) { 172 throw new SQLException("Error", this, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, elementExistException); 173 } finally { 174 CompHelper.disposeComponent(results); 175 } 176 } 177 readForeignKeys(XDatabaseMetaData metadata, String catalogName, String schemaName, String tableName, boolean isCaseSensitive, Map<String, OKey> keys, OTable table)178 private void readForeignKeys(XDatabaseMetaData metadata, 179 String catalogName, String schemaName, String tableName, boolean isCaseSensitive, Map<String, OKey> keys, OTable table) throws SQLException { 180 Object catalog = Any.VOID; 181 if (!catalogName.isEmpty()) { 182 catalog = catalogName; 183 } 184 XResultSet results = null; 185 try { 186 results = metadata.getImportedKeys(catalog, schemaName, tableName); 187 XRow row = UnoRuntime.queryInterface(XRow.class, results); 188 if (row != null) { 189 String oldFkName = ""; 190 KeyProperties keyProperties = null; 191 while (results.next()) { 192 String catalogReturned = row.getString(1); 193 if (row.wasNull()) { 194 catalogReturned = ""; 195 } 196 String schemaReturned = row.getString(2); 197 String nameReturned = row.getString(3); 198 199 String foreignKeyColumn = row.getString(8); 200 int updateRule = row.getInt(10); 201 int deleteRule = row.getInt(11); 202 String fkName = row.getString(12); 203 204 if (!row.wasNull() && !fkName.isEmpty()) { 205 if (!oldFkName.equals(fkName)) { 206 if (keyProperties != null) { 207 OKey key = new OKey(oldFkName, isCaseSensitive, keyProperties.referencedTable, keyProperties.type, 208 keyProperties.updateRule, keyProperties.deleteRule, keyProperties.columnNames, table); 209 keys.put(oldFkName, key); 210 } 211 String referencedName = DbTools.composeTableName(metadata, catalogReturned, schemaReturned, nameReturned, 212 false, ComposeRule.InDataManipulation); 213 keyProperties = new KeyProperties(referencedName, KeyType.FOREIGN, updateRule, deleteRule); 214 keyProperties.columnNames.add(foreignKeyColumn); 215 oldFkName = fkName; 216 } else { 217 if (keyProperties != null) { 218 keyProperties.columnNames.add(foreignKeyColumn); 219 } 220 } 221 } 222 } 223 if (keyProperties != null) { 224 OKey key = new OKey(oldFkName, isCaseSensitive, keyProperties.referencedTable, keyProperties.type, 225 keyProperties.updateRule, keyProperties.deleteRule, keyProperties.columnNames, table); 226 keys.put(oldFkName, key); 227 } 228 } 229 } catch (ElementExistException elementExistException) { 230 throw new SQLException("Error", this, StandardSQLState.SQL_GENERAL_ERROR.text(), 0, elementExistException); 231 } finally { 232 CompHelper.disposeComponent(results); 233 } 234 } 235 readIndexes(XDatabaseMetaData metadata, String catalogName, String schemaName, String tableName, OTable table)236 public ArrayList<String> readIndexes(XDatabaseMetaData metadata, String catalogName, String schemaName, String tableName, OTable table) throws SQLException { 237 Object catalog = Any.VOID; 238 if (!catalogName.isEmpty()) { 239 catalog = catalogName; 240 } 241 ArrayList<String> names = new ArrayList<>(); 242 XResultSet results = null; 243 try { 244 results = metadata.getIndexInfo(catalog, schemaName, tableName, false, false); 245 if (results != null) { 246 XRow row = UnoRuntime.queryInterface(XRow.class, results); 247 String catalogSep = metadata.getCatalogSeparator(); 248 String previousRoundName = ""; 249 while (results.next()) { 250 String name = row.getString(5); 251 if (!name.isEmpty()) { 252 name += catalogSep; 253 } 254 name += row.getString(6); 255 if (!name.isEmpty()) { 256 // don't insert the name if the last one we inserted was the same 257 if (!previousRoundName.equals(name)) { 258 names.add(name); 259 } 260 } 261 } 262 } 263 } finally { 264 CompHelper.disposeComponent(results); 265 } 266 return names; 267 } 268 } 269