DataTblDef


1   /*
2     Copyright (C) 2003  Know Gate S.L. All rights reserved.
3                         C/O�a, 107 1�2 28050 Madrid (Spain)
4   
5     Redistribution and use in source and binary forms, with or without
6     modification, are permitted provided that the following conditions
7     are met:
8   
9     1. Redistributions of source code must retain the above copyright
10       notice, this list of conditions and the following disclaimer.
11  
12    2. The end-user documentation included with the redistribution,
13       if any, must include the following acknowledgment:
14       "This product includes software parts from hipergate
15       (http://www.hipergate.org/)."
16       Alternately, this acknowledgment may appear in the software itself,
17       if and wherever such third-party acknowledgments normally appear.
18  
19    3. The name hipergate must not be used to endorse or promote products
20       derived from this software without prior written permission.
21       Products derived from this software may not be called hipergate,
22       nor may hipergate appear in their name, without prior written
23       permission.
24  
25    This library is distributed in the hope that it will be useful,
26    but WITHOUT ANY WARRANTY; without even the implied warranty of
27    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
28  
29    You should have received a copy of hipergate License with this code;
30    if not, visit http://www.hipergate.org or mail to info@hipergate.org
31  */
32  
33  
34  package com.knowgate.datacopy;
35  
36  import com.knowgate.debug.DebugFile;
37  
38  import java.sql.Connection  ;
39  import java.sql.DatabaseMetaData  ;
40  import java.sql.ResultSet  ;
41  import java.sql.ResultSetMetaData  ;
42  import java.sql.Statement  ;
43  import java.sql.SQLException  ;
44  
45  /**
46   * <p>Keeps information about each table definition.</p>
47   * @author Sergio Montoro Ten
48   * @version 0.5 alpha
49   */
50  
51  public class DataTblDef {
52  
53    public DataTblDef() { }
54  
55    // ----------------------------------------------------------
56  
57    private void alloc(int cCols) {
58      // N� total de columnas en la tabla
59      ColCount = cCols;
60      // Array con los nombres de las columnas
61      ColNames = new String  [cCols];
62      // Array con los tipos SQL de las columnas
63      ColTypes = new int[cCols];
64      // Array con las longitudes de las columnas
65      ColSizes = new int[cCols];
66      // Para cada columna, tiene true si forma parte de la PK, false en caso contrario
67      PrimaryKeyMarks = new boolean[cCols];
68      // Inicializar todos los campos por defecto a No-PK
69      for (int c=0;c<cCols;c++) PrimaryKeyMarks[c] = false;
70    }
71  
72    // ----------------------------------------------------------
73  
74    /**
75     * Read table metadata
76     * @param oConn JDBC Connection
77     * @param sTable Table Name (case insensitive)
78     * @param sPK List of primary key columns delimited by commas
79     * @throws SQLException
80     */
81    public void readMetaData(Connection   oConn, String   sTable, String   sPK) throws SQLException   {
82      int lenPK;
83      int iCurr;
84      int cCols;
85      Statement   oStmt = null;
86      ResultSet   oRSet = null;
87      ResultSetMetaData   oMDat = null;
88  
89      if (DebugFile.trace) {
90        DebugFile.writeln("Begin DataTblDef.readMetaData([Connection], \"" + sTable + "\",\"" + sPK + "\")");
91        DebugFile.incIdent();
92      }
93  
94      BaseTable = sTable;
95  
96      // **********************************
97      // * Leer los valores de metadatos
98  
99      // Lanza una SELECT que no devuelve ning�n registro y luego
100     // hace una llamada a getMetaData para guardar en memoria
101     // la definici�n de los campos leidos.
102     // La clave primaria no se extrae de la SELECT sino que debe
103     // pasarse externamente como parametro.
104 
105     try {
106       oStmt = oConn.createStatement();
107 
108       if (DebugFile.trace) DebugFile.writeln ("Statement.executeQuery(SELECT * FROM " + sTable + " WHERE 1=0)");
109 
110       oRSet = oStmt.executeQuery("SELECT * FROM " + sTable + " WHERE 1=0");
111 
112       oMDat = oRSet.getMetaData();
113 
114       cCols = oMDat.getColumnCount();
115 
116       alloc(cCols); // Funci�n interna de soporte
117 
118       for (int c=0; c<cCols; c++) {
119         ColNames[c] = oMDat.getColumnName(c+1);
120         ColTypes[c] = oMDat.getColumnType(c+1);
121         ColSizes[c] = oMDat.getPrecision(c+1);
122 
123         if (DebugFile.trace) DebugFile.writeln(ColNames[c] + " SQLType " + String.valueOf(ColTypes[c]) + " precision "  + ColSizes[c]);
124       }  // next (c)
125 
126       oMDat = null;
127     }
128     catch (SQLException   sqle) {
129       throw new SQLException   (sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode());
130     }
131     finally {
132       if (null!=oRSet) oRSet.close();
133       if (null!=oStmt) oStmt.close();
134     }
135 
136     // *********************************************************
137     // * Almacenar los nombres de campos de la clave primaria
138 
139     if (null!=sPK) {
140       lenPK = sPK.length()-1;
141 
142       cPKs = 1;
143       // Cuenta el n� de comas que hay en la cadena de entrada
144       for (int i=1; i<=lenPK; i++)
145         if(sPK.charAt(i)==',') cPKs++;
146 
147       // El n� de campos es la cantidad de comas mas uno
148       PrimaryKeys = new String  [cPKs];
149 
150       // Parsea la cadena de entrada usando las coma como indicador de salto
151       iCurr = 0;
152       PrimaryKeys[0] = "";
153       for (int j=0; j<=lenPK; j++)
154         if (sPK.charAt(j)!=',') {
155           PrimaryKeys[iCurr] += sPK.charAt(j);
156         }
157         else {
158           if (DebugFile.trace) DebugFile.writeln("PrimaryKeys[" + String.valueOf(iCurr) + "]=" + PrimaryKeys[iCurr]);
159           PrimaryKeys[++iCurr] = "";
160         }
161 
162       if (DebugFile.trace) DebugFile.writeln("PrimaryKeys[" + String.valueOf(iCurr) + "]=" + PrimaryKeys[iCurr]);
163 
164       // Almacenar un indicador booleano para cada campo que forme parte de la PK
165 
166       for (int l=0; l<ColCount; l++) PrimaryKeyMarks[l] = false;
167 
168       for (int k=0; k<cPKs; k++) {
169         for (int f=0;f<ColCount;f++) {
170           PrimaryKeyMarks[f] |= PrimaryKeys[k].equalsIgnoreCase(ColNames[f]);
171         } // next (f)
172       }  // next (k)
173     } // end if (null!=sPK)
174     else {
175       cPKs = 0;
176       PrimaryKeys = null;
177       }
178 
179       if (DebugFile.trace) {
180         DebugFile.decIdent();
181         DebugFile.writeln("End DataTblDef.readMetaData()");
182       }
183 
184   } // readMetaData ()
185 
186   // ---------------------------------------------------------------------------
187 
188   /**
189    * Get table primary key
190    * @param oConn JDBC Connection
191    * @param sSchema String Schema name
192    * @param sCatalog String Catalog Name
193    * @param sTable String Table Name (case sensitive)
194    * @return String List of table primary key columns delimited by commas, if table does not have a
195    * primary key then return value is <b>null</b>
196    * @throws SQLException
197    */
198   public String   getPrimaryKeys (Connection   oConn, String   sSchema, String   sCatalog, String   sTable)
199     throws SQLException   {
200 
201     String   sPKCols = null;
202     DatabaseMetaData   oMDat = oConn.getMetaData();
203     ResultSet   oRSet = oMDat.getPrimaryKeys(sCatalog, sSchema, sTable);
204 
205     while (oRSet.next()) {
206       if (null==sPKCols)
207         sPKCols = oRSet.getString(4);
208       else
209         sPKCols += "," + oRSet.getString(4);
210 
211     } // wend
212 
213     oRSet.close();
214 
215     return sPKCols;
216   }
217 
218   // ----------------------------------------------------------
219 
220   public int findColumnPosition(String   sColName) {
221     // Busca la posici�n de una columna por nombre
222     // Devuelve -1 si no la encuentra
223 
224     int iCol = -1;
225 
226     for (int c=0; (c<ColCount) && (iCol==-1); c++)
227       if(sColName.equalsIgnoreCase(ColNames[c]))
228         iCol = c;
229 
230     return iCol;
231   } // findColumnPosition
232 
233   // ----------------------------------------------------------
234 
235   public int findColumnType(String   sColName)  {
236     // Busca el tipo de una columna por nombre
237     // Devuelve 0 si no la encuentra
238 
239     int iType = 0;
240 
241     for (int c=0; c<ColCount; c++)
242       if(sColName.equalsIgnoreCase(ColNames[c])) {
243         iType = ColTypes[c];
244         break;
245       }
246     return iType;
247   } // findColumnType
248 
249   // ----------------------------------------------------------
250 
251   public boolean inheritsPK(DataTblDef oTblDef)
252     throws ArrayIndexOutOfBoundsException   {
253 
254     if (DebugFile.trace) {
255       DebugFile.writeln("Begin " + BaseTable + " DataTblDef.inheritsPK(" + oTblDef.BaseTable + ")");
256       DebugFile.incIdent();
257       DebugFile.writeln(BaseTable + " has " + String.valueOf(cPKs) +" pk columns");
258     }
259 
260     // Comprueba si dos DataTblDef tienen la misma estructura de tipos
261     // y longitudes en sus campos de la PK y, por consiguiente, con
262     // gran probabilidad un DataTblDef hereda la PK del otro.
263     boolean bSamePK;
264     int pc, fc;
265 
266     int iMatchCount = 0;
267 
268     if (DebugFile.trace)
269       if (cPKs<oTblDef.cPKs)
270         DebugFile.writeln(BaseTable + " does not inherit PK from " + oTblDef.BaseTable + " because " + oTblDef.BaseTable + " has " + String.valueOf(oTblDef.cPKs) + " PK columns and " + BaseTable + " has only " + String.valueOf(cPKs) + " PK columns");
271 
272     bSamePK = (cPKs>=oTblDef.cPKs);
273 
274     if (bSamePK) {
275 
276       for (int fk=0; fk<cPKs; fk++) {
277 
278         if (DebugFile.trace) DebugFile.writeln("fk=" + String.valueOf(fk));
279 
280         fc = findColumnPosition(PrimaryKeys[fk]);
281 
282         if (DebugFile.trace && -1==fc) DebugFile.writeln("cannot find column " + PrimaryKeys[fk] + " on " + BaseTable);
283 
284         if (-1!=fc) {
285 
286           for (int pk=0; pk<oTblDef.cPKs; pk++) {
287 
288             if (DebugFile.trace) DebugFile.writeln("pk=" + String.valueOf(pk));
289 
290             pc = oTblDef.findColumnPosition(oTblDef.PrimaryKeys[pk]);
291 
292             if (DebugFile.trace && -1==pc) DebugFile.writeln("cannot find column " + oTblDef.PrimaryKeys[pk] + " on " + oTblDef.BaseTable);
293 
294             if (-1!=pc) {
295 
296               if (DebugFile.trace)
297                 DebugFile.writeln("trying " + BaseTable + "." + ColNames[fc] + " and " + oTblDef.BaseTable + "." + oTblDef.ColNames[pc]);
298 
299               if ((oTblDef.ColTypes[pc]==ColTypes[fc] && oTblDef.ColSizes[pc]==ColSizes[fc]) &&
300                  ((cPKs==1 && oTblDef.ColNames[pc].equalsIgnoreCase(ColNames[fc])) || (cPKs>1))) {
301 
302                 if (DebugFile.trace) {
303                   if (cPKs>1)
304                     DebugFile.writeln(BaseTable + "." + PrimaryKeys[fk] + " matches " + oTblDef.BaseTable + "." + oTblDef.PrimaryKeys[pk]);
305                   else
306                     DebugFile.writeln(BaseTable + "." + PrimaryKeys[fk] + " matches same column on " + oTblDef.BaseTable + "." + oTblDef.PrimaryKeys[pk]);
307                 }
308 
309                 iMatchCount++;
310                 break;
311               }
312               else {
313                 if (DebugFile.trace) {
314                   if (oTblDef.ColTypes[pc]!=ColTypes[fc])
315                     DebugFile.writeln(BaseTable + "." + PrimaryKeys[fk] + " has SQLType " + ColTypes[fc] + " and " + oTblDef.BaseTable + "." + oTblDef.PrimaryKeys[pk] + " has SQLType " + oTblDef.ColTypes[pc]);
316                   else if (oTblDef.ColSizes[pc]==ColSizes[fc])
317                     DebugFile.writeln(BaseTable + "." + PrimaryKeys[fk] + " has size " + ColSizes[fc] + " and " + oTblDef.BaseTable + "." + oTblDef.PrimaryKeys[pk] + " has size " + oTblDef.ColSizes[pc]);
318                   else if (cPKs==1 && !oTblDef.ColNames[pc].equalsIgnoreCase(ColNames[fc]))
319                     DebugFile.writeln(BaseTable + "." + PrimaryKeys[fk] + " as same SQLType and size as " + oTblDef.BaseTable + "." + oTblDef.PrimaryKeys[pk] + " but it is not considered match because " + PrimaryKeys[fk] + " is a single primary key column and they don't have the same name");
320                 }
321               }
322             } // fi (-1!=pc)
323           } // next (pk)
324           if (iMatchCount==oTblDef.cPKs) break;
325 
326         } // fi (-1!=fc)
327       } // next (fk)
328     }
329 
330     if (DebugFile.trace) DebugFile.writeln("match count = " + String.valueOf(iMatchCount) + " , primary keys =" + String.valueOf(oTblDef.cPKs));
331 
332     if (iMatchCount<oTblDef.cPKs) bSamePK = false;
333 
334     if (DebugFile.trace) {
335       DebugFile.decIdent();
336       DebugFile.writeln("End DataTblDef.inheritsPK() : " + String.valueOf(bSamePK));
337     }
338 
339     return bSamePK;
340   } // inheritsPK
341 
342   // ----------------------------------------------------------
343 
344   public boolean bestMatch(int iThisCol, DataTblDef oTblDef, int iParentPK) {
345     int[] aScores = new int[cPKs];
346     int iPKPos;
347     int iParentCol;
348     int iPKRelativePos = 0;
349     int iBestMatch = -1;
350     int iBestScore = -1;
351 
352     if (DebugFile.trace) {
353       DebugFile.writeln("Begin DataTblDef.bestMatch(" + BaseTable + "." + ColNames[iThisCol] + " , " + oTblDef.BaseTable + "." + oTblDef.PrimaryKeys[iParentPK] + ")");
354       DebugFile.incIdent();
355     }
356 
357     iParentCol = oTblDef.findColumnPosition(oTblDef.PrimaryKeys[iParentPK]);
358 
359     // Find seeked field relative position inside primary key
360     for (int c=0; c<this.ColCount & iPKRelativePos<cPKs; c++)
361       if (PrimaryKeyMarks[c] && !ColNames[c].equalsIgnoreCase(ColNames[iThisCol]))
362         iPKRelativePos++;
363       else if (PrimaryKeyMarks[c] && ColNames[c].equalsIgnoreCase(ColNames[iThisCol]))
364         break;
365 
366     // For each key field, assign a score
367     for (int k=0; k<cPKs; k++) {
368       aScores[k] = 0;
369 
370       if (PrimaryKeys[k].equalsIgnoreCase(oTblDef.ColNames[iParentCol]))
371         aScores[k] += 5; // Add 5 points if names match
372 
373       iPKPos = findColumnPosition(PrimaryKeys[k]);
374 
375       if (iPKPos>-1)
376         if (ColTypes[iPKPos]==oTblDef.ColTypes[iParentCol] &&
377             ColSizes[iPKPos]==oTblDef.ColSizes[iParentCol])
378           aScores[k] += 1; // Add 1 point if types and sizes match
379     } // next
380 
381     // Check if seeked field has the highest score
382     for (int k=0; k<cPKs; k++) {
383       if (aScores[k]>iBestScore) {
384         iBestScore = aScores[k];
385         iBestMatch = k;
386       } // fi
387     } // next
388 
389     if (DebugFile.trace) {
390       DebugFile.writeln("pk relative position is " + String.valueOf(iPKRelativePos) + ", best match relative position is " + String.valueOf(iBestMatch));
391       DebugFile.decIdent();
392       DebugFile.writeln("End DataTblDef.bestMatch() : " + String.valueOf(iPKRelativePos==iBestMatch));
393     }
394     return (iPKRelativePos==iBestMatch);
395 
396   } // bestMatch
397 
398   // ----------------------------------------------------------
399 
400   public boolean isPrimaryKey(int iCol) {
401 
402     boolean bRetVal = PrimaryKeyMarks[iCol];
403 
404     return bRetVal;
405   } // isPrimaryKey
406 
407   // ----------------------------------------------------------
408 
409   public boolean isPrimaryKey(String   sCol) {
410     boolean bRetVal;
411 
412     int iCol = findColumnPosition (sCol);
413 
414     if (-1==iCol)
415       bRetVal = false;
416     else
417       bRetVal = PrimaryKeyMarks[iCol];
418 
419     return bRetVal;
420   } // isPrimaryKey
421 
422   // *********************************************************
423   // * Member Variables
424 
425   public int cPKs; // N� total de campos en la PK
426   public boolean bMayInheritPK;
427   private boolean PrimaryKeyMarks[]; // Array con flags booleanos de PK / No-PK
428   public String   PrimaryKeys[]; // Nombre de los campos en la PK
429   public String   ColNames[]; // Nombres de todas las columnas (por orden de aparici�n)
430   public int ColTypes[]; // Tipos de todas las columnas
431   public int ColSizes[]; // Longitudes de todas las columnas
432   public int ColCount; // Cuenta del n� total de columnas
433   public String   BaseTable;
434 
435   } // DataTblDef
436
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Free Books Free Magazines
Popular Tags