KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > knowgate > datacopy > 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 JavaDoc;
39 import java.sql.DatabaseMetaData JavaDoc;
40 import java.sql.ResultSet JavaDoc;
41 import java.sql.ResultSetMetaData JavaDoc;
42 import java.sql.Statement JavaDoc;
43 import java.sql.SQLException JavaDoc;
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 JavaDoc[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 JavaDoc oConn, String JavaDoc sTable, String JavaDoc sPK) throws SQLException JavaDoc {
82     int lenPK;
83     int iCurr;
84     int cCols;
85     Statement JavaDoc oStmt = null;
86     ResultSet JavaDoc oRSet = null;
87     ResultSetMetaData JavaDoc 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 JavaDoc sqle) {
129       throw new SQLException JavaDoc (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 JavaDoc[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 JavaDoc getPrimaryKeys (Connection JavaDoc oConn, String JavaDoc sSchema, String JavaDoc sCatalog, String JavaDoc sTable)
199     throws SQLException JavaDoc {
200
201     String JavaDoc sPKCols = null;
202     DatabaseMetaData JavaDoc oMDat = oConn.getMetaData();
203     ResultSet JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc {
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 JavaDoc 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 JavaDoc PrimaryKeys[]; // Nombre de los campos en la PK
429
public String JavaDoc 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 JavaDoc BaseTable;
434
435   } // DataTblDef
436
Popular Tags