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 
23 package com.sun.star.wizards.db;
24 
25 import java.util.Vector;
26 
27 import com.sun.star.beans.XPropertySet;
28 import com.sun.star.sdbc.ColumnSearch;
29 import com.sun.star.sdbc.ColumnValue;
30 import com.sun.star.sdbc.DataType;
31 import com.sun.star.sdbc.SQLException;
32 import com.sun.star.sdbc.XResultSet;
33 import com.sun.star.sdbc.XRow;
34 import com.sun.star.uno.AnyConverter;
35 import com.sun.star.uno.UnoRuntime;
36 import com.sun.star.wizards.common.JavaTools;
37 import com.sun.star.wizards.common.PropertyNames;
38 
39 public class TypeInspector
40 {
41 
42     private String[] sDataTypeNames;
43     private int[] nDataTypeInfos;
44     private int[] nPrecisionInfos;
45     private int[] nNullableInfos;
46     private int[] nSearchables;
47     private boolean[] bisAutoIncrementableInfos;
48     private int[] nMinScaleInfos;
49     private int[] nMaxScaleInfos;
50     private int[] nNumericFallBackList = new int[]
51     {
52         DataType.INTEGER, DataType.FLOAT, DataType.REAL, DataType.DOUBLE, DataType.NUMERIC, DataType.DECIMAL
53     };
54     final int INVALID = 999999;
55     XResultSet xResultSet;
56 
57     public class TypeInfo
58     {
59 
60         public int nDataType;
61         public String sDataTypeName;
62         public boolean bisAutoIncrementable;
63 
TypeInfo(int _nDataType, String _sDataTypeName, boolean _bisAutoIncrementable)64         public TypeInfo(int _nDataType, String _sDataTypeName, boolean _bisAutoIncrementable)
65         {
66             nDataType = _nDataType;
67             sDataTypeName = _sDataTypeName;
68             bisAutoIncrementable = _bisAutoIncrementable;
69         }
70     }
71 
TypeInspector(XResultSet _xResultSet)72     public TypeInspector(XResultSet _xResultSet)
73     {
74         try
75         {
76             xResultSet = _xResultSet;
77             Vector<String> aTypeNameVector = new Vector<String>();
78             Vector<Integer> aTypeVector = new Vector<Integer>();
79             Vector<Integer> aNullableVector = new Vector<Integer>();
80             Vector<Boolean> aAutoIncrementVector = new Vector<Boolean>();
81             Vector<Integer> aPrecisionVector = new Vector<Integer>();
82             Vector<Integer> aMinScaleVector = new Vector<Integer>();
83             Vector<Integer> aMaxScaleVector = new Vector<Integer>();
84             Vector<Integer> aSearchableVector = new Vector<Integer>();
85             // Integer[] aIntegerDataTypes = null;
86 //      XResultSet xResultSet = xDBMetaDagetTypeInfo();
87             XRow xRow = UnoRuntime.queryInterface(XRow.class, xResultSet);
88             while (xResultSet.next())
89             {
90                 aTypeNameVector.addElement(xRow.getString(1));
91                 aTypeVector.addElement(new Integer(xRow.getShort(2)));
92                 aPrecisionVector.addElement(new Integer(xRow.getInt(3)));
93                 aNullableVector.addElement(new Integer(xRow.getShort(7)));
94                 aSearchableVector.addElement(new Integer(xRow.getShort(9)));
95                 aAutoIncrementVector.addElement(Boolean.valueOf(xRow.getBoolean(12)));
96                 aMinScaleVector.addElement(new Integer(xRow.getShort(14)));
97                 aMaxScaleVector.addElement(new Integer(xRow.getShort(15)));
98 
99             }
100             sDataTypeNames = new String[aTypeNameVector.size()];
101             aTypeNameVector.toArray(sDataTypeNames);
102             nDataTypeInfos = JavaTools.IntegerTointList(aTypeVector);
103             nNullableInfos = JavaTools.IntegerTointList(aNullableVector);
104             nSearchables = JavaTools.IntegerTointList(aSearchableVector);
105             bisAutoIncrementableInfos = JavaTools.BooleanTobooleanList(aAutoIncrementVector);
106             nPrecisionInfos = JavaTools.IntegerTointList(aPrecisionVector);
107             nMinScaleInfos = JavaTools.IntegerTointList(aMinScaleVector);
108             nMaxScaleInfos = JavaTools.IntegerTointList(aMaxScaleVector);
109         }
110         catch (SQLException e)
111         {
112             e.printStackTrace(System.out);
113         }
114     }
115 
getScale(XPropertySet _xColPropertySet)116     public int getScale(XPropertySet _xColPropertySet)
117     {
118         try
119         {
120             int i = getDataTypeIndex(_xColPropertySet, false);
121             int nScale = AnyConverter.toInt(_xColPropertySet.getPropertyValue("Scale"));
122             if (i == -1)
123             {
124                 return nScale;
125             }
126             if (nScale > nMaxScaleInfos[i])
127             {
128                 return nMaxScaleInfos[i];
129             }
130             else if (nScale < nMinScaleInfos[i])
131             {
132                 return nMinScaleInfos[i];
133             }
134             else
135             {
136                 return nScale;
137             }
138         }
139         catch (Exception e)
140         {
141             e.printStackTrace(System.out);
142             return 0;
143         }
144     }
145 
getNullability(XPropertySet _xColPropertySet, int _nNullable)146     public int getNullability(XPropertySet _xColPropertySet, int _nNullable)
147     {
148         int i = getDataTypeIndex(_xColPropertySet, false);
149         if (i == -1)
150         {
151             return ColumnValue.NO_NULLS;
152         }
153         if (_nNullable == ColumnValue.NULLABLE)
154         {
155             return nNullableInfos[i];           //probably nullability is not allowed
156         }
157         return _nNullable;
158     }
159 
getNullability(XPropertySet _xColPropertySet)160     public int getNullability(XPropertySet _xColPropertySet)
161     {
162         try
163         {
164             int i = getDataTypeIndex(_xColPropertySet, false);
165             if (i == -1)
166             {
167                 return ColumnValue.NO_NULLS;
168             }
169             int nNullable = AnyConverter.toInt(_xColPropertySet.getPropertyValue("IsNullable"));
170             if (nNullable == ColumnValue.NULLABLE)
171             {
172                 return nNullableInfos[i];
173             }
174             return nNullable;
175         }
176         catch (Exception e)
177         {
178             e.printStackTrace(System.out);
179         }
180         return ColumnValue.NO_NULLS;
181     }
182 
isColumnOrderable(XPropertySet _xColPropertySet)183     public boolean isColumnOrderable(XPropertySet _xColPropertySet)
184     {
185         int i = getDataTypeIndex(_xColPropertySet, false);
186         if (i > -1)
187         {
188             return (nSearchables[i] != ColumnSearch.NONE);
189         }
190         else
191         {
192             return false;
193         }
194     }
195 
isNullable(XPropertySet _xColPropertySet)196     public int isNullable(XPropertySet _xColPropertySet)
197     {
198         int i = getDataTypeIndex(_xColPropertySet, false);
199         if (i > -1)
200         {
201             return nNullableInfos[i];
202         }
203         else
204         {
205             return ColumnValue.NO_NULLS;
206         }
207     }
208 
getDataTypeIndex(XPropertySet _xColPropertySet, boolean _bCheckNumericAttributes)209     private int getDataTypeIndex(XPropertySet _xColPropertySet, boolean _bCheckNumericAttributes)
210     {
211         try
212         {
213             int nPrecision = -1;
214             int nScale = -1;
215             int nDataType = AnyConverter.toInt(_xColPropertySet.getPropertyValue("Type"));
216             String sTypeName = AnyConverter.toString(_xColPropertySet.getPropertyValue("TypeName"));
217             if (_bCheckNumericAttributes)
218             {
219                 nPrecision = AnyConverter.toInt(_xColPropertySet.getPropertyValue("Precision"));
220                 nScale = AnyConverter.toInt(_xColPropertySet.getPropertyValue("Scale"));
221             }
222             boolean bleaveloop = false;
223             int startindex = 0;
224             while (!bleaveloop)
225             {
226                 int i = JavaTools.FieldInIntTable(nDataTypeInfos, nDataType, startindex);
227                 startindex = i + 1;
228                 bleaveloop = (i < 0);
229                 if (!bleaveloop && sTypeName.equals(sDataTypeNames[i]))
230                 {
231                     if (_bCheckNumericAttributes)
232                     {
233                         if (nPrecision <= nPrecisionInfos[i] && (nScale >= nMinScaleInfos[i]) && (nScale <= nMinScaleInfos[i]) )
234                         {
235                             return i;
236                         }
237                     }
238                     else
239                     {
240                         return i;
241                     }
242                 }
243             }
244         }
245         catch (Exception e)
246         {
247             e.printStackTrace(System.out);
248         }
249         return -1;
250     }
251 
supportsDataType(int _curDataType)252     public boolean supportsDataType(int _curDataType)
253     {
254         return (JavaTools.FieldInIntTable(nDataTypeInfos, _curDataType) > -1);
255     }
256 
getLastConversionFallbackDataType()257     public int getLastConversionFallbackDataType()
258     {
259         if (supportsDataType(DataType.VARCHAR))
260         {
261             return DataType.VARCHAR;
262         }
263         else
264         {
265             return DataType.LONGVARCHAR;
266         }
267     }
268 
269     /**
270      * an empty string is returned when no appropriate Typename can be found
271      * finds the first TypeName of the passed datatype.
272      * @param _curDataType
273      * @return
274      */
getDefaultTypeName(int _curDataType, Integer precision)275     public String getDefaultTypeName(int _curDataType, Integer precision)
276     {
277         String ret = PropertyNames.EMPTY_STRING;
278         for (int i = 0; i < nDataTypeInfos.length; i++)
279         {
280             if (nDataTypeInfos[i] == _curDataType)
281             {
282 //                 System.out.println( "Desired prec " + precision + ",nPrecisionInfos[i]="+nPrecisionInfos[i] + ",sDataTypeNames[i]="+sDataTypeNames[i] );
283 
284                 if (precision == null || nPrecisionInfos[i] >= precision.intValue())
285                 {
286                     ret = sDataTypeNames[i]; // this fits best !
287                     break;
288                 }
289                 else if (ret.length() == 0)
290                 {
291                     // in case we dont find anything else, we at return a typename
292                     // with the correct class
293                     ret = sDataTypeNames[i];
294                 }
295             }
296         }
297 //         System.out.println( "_curDataType="+_curDataType+",precision="+precision+",ret="+
298         return ret;
299     }
300 
getDataType(String _sTypeName)301     public int getDataType(String _sTypeName)
302     {
303         int i = JavaTools.FieldInList(sDataTypeNames, _sTypeName);
304         if (i > -1)
305         {
306             return nDataTypeInfos[i];
307         }
308         else
309         {
310             return getLastConversionFallbackDataType();
311         }
312     }
313 
convertDataType(int _curDataType)314     public int convertDataType(int _curDataType)
315     {
316         int retDataType = _curDataType;
317         if (!supportsDataType(_curDataType))
318         {
319             switch (_curDataType)
320             {
321                 case DataType.BIT:
322                     retDataType = convertDataType(DataType.BOOLEAN);
323                     break;
324                 case DataType.BOOLEAN:
325                     retDataType = convertDataType(DataType.BIT);
326                     break;
327                 case DataType.TINYINT:
328                     retDataType = convertDataType(DataType.SMALLINT);
329                     break;
330                 case DataType.SMALLINT:
331                     retDataType = convertDataType(DataType.INTEGER);
332                     break;
333                 case DataType.INTEGER:
334                     retDataType = convertDataType(DataType.FLOAT);
335                     break;
336                 case DataType.FLOAT:
337                     retDataType = convertDataType(DataType.REAL);
338                     break;
339                 case DataType.DATE:
340                 case DataType.TIME:
341                     retDataType = convertDataType(DataType.TIMESTAMP);
342                     break;
343                 case DataType.TIMESTAMP:
344                 case DataType.REAL:
345                 case DataType.BIGINT:
346                     retDataType = convertDataType(DataType.DOUBLE);
347                     break;
348                 case DataType.DOUBLE:
349                     retDataType = convertDataType(DataType.NUMERIC);
350                     break;
351                 case DataType.NUMERIC:
352                     retDataType = convertDataType(DataType.DECIMAL);
353                     break;
354                 case DataType.DECIMAL:
355                     if (supportsDataType(DataType.DOUBLE))
356                     {
357                         retDataType = convertDataType(DataType.DOUBLE);
358                     }
359                     else if (supportsDataType(DataType.NUMERIC))
360                     {
361                         retDataType = DataType.NUMERIC;
362                     }
363                     else
364                     {
365                         retDataType = getLastConversionFallbackDataType();
366                     }
367                     break;
368                 case DataType.VARCHAR:
369                     retDataType = getLastConversionFallbackDataType();
370                     break;
371                 default:
372                     retDataType = getLastConversionFallbackDataType();
373             }
374         }
375         return retDataType;
376     }
377 
getAutoIncrementIndex(XPropertySet _xColPropertySet)378     public int getAutoIncrementIndex(XPropertySet _xColPropertySet)
379     {
380         try
381         {
382             boolean bleaveloop = false;
383             int startindex = 0;
384             int curDataType = ((Integer) _xColPropertySet.getPropertyValue("Type")).intValue();
385             while (!bleaveloop)
386             {
387                 int i = JavaTools.FieldInIntTable(nDataTypeInfos, curDataType, startindex);
388                 startindex = i + 1;
389                 bleaveloop = (i == -1);
390                 if (!bleaveloop)
391                 {
392                     if (bisAutoIncrementableInfos[i])
393                     {
394                         return nDataTypeInfos[i];
395                     }
396                 }
397             }
398         }
399         catch (Exception e)
400         {
401             e.printStackTrace(System.out);
402         }
403         return INVALID;
404 
405     }
406 
isAutoIncrementable(XPropertySet _xColPropertySet)407     public boolean isAutoIncrementable(XPropertySet _xColPropertySet)
408     {
409         return (getAutoIncrementIndex(_xColPropertySet) != INVALID);
410     }
411 
412     /** finds the first available DataType that can be used as a primary key in a table.
413      * @return The first datatype that also supports Autoincrmentation is taken according to the following list:
414      *1) INTEGER
415      *2) FLOAT
416      *3) REAL
417      *4) DOUBLE
418      *5) NUMERIC
419      *6) DECIMAL         *
420      * If no appropriate datatype is found ther first available numeric type after DataType.INTEGER
421      * according to the 'convertDataType' method is returned
422      */
423     /**TODO the fallback order is the same as implemented in the method 'convertDataType'.
424      * It's not very elegant to have the same intelligence
425      * on several spots in the class!!
426      *
427      */
findAutomaticPrimaryKeyType()428     public TypeInfo findAutomaticPrimaryKeyType()
429     {
430         int nDataType;
431         for (int n = 0; n < this.nNumericFallBackList.length; n++)
432         {
433             nDataType = nNumericFallBackList[n];
434             boolean bleaveloop = false;
435             int startindex = 0;
436             while (!bleaveloop)
437             {
438                 int i = JavaTools.FieldInIntTable(nDataTypeInfos, nDataType, startindex);
439                 bleaveloop = (i < 0);
440                 if (!bleaveloop)
441                 {
442                     if (this.bisAutoIncrementableInfos[i])
443                     {
444                         return new TypeInfo(nDataType, this.sDataTypeNames[i], true);
445                     }
446                     startindex = i + 1;
447                 }
448                 startindex = i + 1;
449             }
450         }
451         // As Autoincrementation is not supported for any numeric datatype we take the first available numeric Type;
452         nDataType = convertDataType(DataType.INTEGER);
453         return new TypeInfo(nDataType, getDefaultTypeName(nDataType, null), false);
454     }
455 }
456