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