001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oña, 107 1º2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.datacopy;
034:
035: import com.knowgate.debug.DebugFile;
036:
037: import java.sql.Connection;
038: import java.sql.DatabaseMetaData;
039: import java.sql.ResultSet;
040: import java.sql.ResultSetMetaData;
041: import java.sql.Statement;
042: import java.sql.SQLException;
043:
044: /**
045: * <p>Keeps information about each table definition.</p>
046: * @author Sergio Montoro Ten
047: * @version 0.5 alpha
048: */
049:
050: public class DataTblDef {
051:
052: public DataTblDef() {
053: }
054:
055: // ----------------------------------------------------------
056:
057: private void alloc(int cCols) {
058: // Nº total de columnas en la tabla
059: ColCount = cCols;
060: // Array con los nombres de las columnas
061: ColNames = new String[cCols];
062: // Array con los tipos SQL de las columnas
063: ColTypes = new int[cCols];
064: // Array con las longitudes de las columnas
065: ColSizes = new int[cCols];
066: // Para cada columna, tiene true si forma parte de la PK, false en caso contrario
067: PrimaryKeyMarks = new boolean[cCols];
068: // Inicializar todos los campos por defecto a No-PK
069: for (int c = 0; c < cCols; c++)
070: PrimaryKeyMarks[c] = false;
071: }
072:
073: // ----------------------------------------------------------
074:
075: /**
076: * Read table metadata
077: * @param oConn JDBC Connection
078: * @param sTable Table Name (case insensitive)
079: * @param sPK List of primary key columns delimited by commas
080: * @throws SQLException
081: */
082: public void readMetaData(Connection oConn, String sTable, String sPK)
083: throws SQLException {
084: int lenPK;
085: int iCurr;
086: int cCols;
087: Statement oStmt = null;
088: ResultSet oRSet = null;
089: ResultSetMetaData oMDat = null;
090:
091: if (DebugFile.trace) {
092: DebugFile
093: .writeln("Begin DataTblDef.readMetaData([Connection], \""
094: + sTable + "\",\"" + sPK + "\")");
095: DebugFile.incIdent();
096: }
097:
098: BaseTable = sTable;
099:
100: // **********************************
101: // * Leer los valores de metadatos
102:
103: // Lanza una SELECT que no devuelve ningún registro y luego
104: // hace una llamada a getMetaData para guardar en memoria
105: // la definición de los campos leidos.
106: // La clave primaria no se extrae de la SELECT sino que debe
107: // pasarse externamente como parametro.
108:
109: try {
110: oStmt = oConn.createStatement();
111:
112: if (DebugFile.trace)
113: DebugFile
114: .writeln("Statement.executeQuery(SELECT * FROM "
115: + sTable + " WHERE 1=0)");
116:
117: oRSet = oStmt.executeQuery("SELECT * FROM " + sTable
118: + " WHERE 1=0");
119:
120: oMDat = oRSet.getMetaData();
121:
122: cCols = oMDat.getColumnCount();
123:
124: alloc(cCols); // Función interna de soporte
125:
126: for (int c = 0; c < cCols; c++) {
127: ColNames[c] = oMDat.getColumnName(c + 1);
128: ColTypes[c] = oMDat.getColumnType(c + 1);
129: ColSizes[c] = oMDat.getPrecision(c + 1);
130:
131: if (DebugFile.trace)
132: DebugFile.writeln(ColNames[c] + " SQLType "
133: + String.valueOf(ColTypes[c])
134: + " precision " + ColSizes[c]);
135: } // next (c)
136:
137: oMDat = null;
138: } catch (SQLException sqle) {
139: throw new SQLException(sqle.getMessage(), sqle
140: .getSQLState(), sqle.getErrorCode());
141: } finally {
142: if (null != oRSet)
143: oRSet.close();
144: if (null != oStmt)
145: oStmt.close();
146: }
147:
148: // *********************************************************
149: // * Almacenar los nombres de campos de la clave primaria
150:
151: if (null != sPK) {
152: lenPK = sPK.length() - 1;
153:
154: cPKs = 1;
155: // Cuenta el nº de comas que hay en la cadena de entrada
156: for (int i = 1; i <= lenPK; i++)
157: if (sPK.charAt(i) == ',')
158: cPKs++;
159:
160: // El nº de campos es la cantidad de comas mas uno
161: PrimaryKeys = new String[cPKs];
162:
163: // Parsea la cadena de entrada usando las coma como indicador de salto
164: iCurr = 0;
165: PrimaryKeys[0] = "";
166: for (int j = 0; j <= lenPK; j++)
167: if (sPK.charAt(j) != ',') {
168: PrimaryKeys[iCurr] += sPK.charAt(j);
169: } else {
170: if (DebugFile.trace)
171: DebugFile.writeln("PrimaryKeys["
172: + String.valueOf(iCurr) + "]="
173: + PrimaryKeys[iCurr]);
174: PrimaryKeys[++iCurr] = "";
175: }
176:
177: if (DebugFile.trace)
178: DebugFile.writeln("PrimaryKeys["
179: + String.valueOf(iCurr) + "]="
180: + PrimaryKeys[iCurr]);
181:
182: // Almacenar un indicador booleano para cada campo que forme parte de la PK
183:
184: for (int l = 0; l < ColCount; l++)
185: PrimaryKeyMarks[l] = false;
186:
187: for (int k = 0; k < cPKs; k++) {
188: for (int f = 0; f < ColCount; f++) {
189: PrimaryKeyMarks[f] |= PrimaryKeys[k]
190: .equalsIgnoreCase(ColNames[f]);
191: } // next (f)
192: } // next (k)
193: } // end if (null!=sPK)
194: else {
195: cPKs = 0;
196: PrimaryKeys = null;
197: }
198:
199: if (DebugFile.trace) {
200: DebugFile.decIdent();
201: DebugFile.writeln("End DataTblDef.readMetaData()");
202: }
203:
204: } // readMetaData ()
205:
206: // ---------------------------------------------------------------------------
207:
208: /**
209: * Get table primary key
210: * @param oConn JDBC Connection
211: * @param sSchema String Schema name
212: * @param sCatalog String Catalog Name
213: * @param sTable String Table Name (case sensitive)
214: * @return String List of table primary key columns delimited by commas, if table does not have a
215: * primary key then return value is <b>null</b>
216: * @throws SQLException
217: */
218: public String getPrimaryKeys(Connection oConn, String sSchema,
219: String sCatalog, String sTable) throws SQLException {
220:
221: String sPKCols = null;
222: DatabaseMetaData oMDat = oConn.getMetaData();
223: ResultSet oRSet = oMDat.getPrimaryKeys(sCatalog, sSchema,
224: sTable);
225:
226: while (oRSet.next()) {
227: if (null == sPKCols)
228: sPKCols = oRSet.getString(4);
229: else
230: sPKCols += "," + oRSet.getString(4);
231:
232: } // wend
233:
234: oRSet.close();
235:
236: return sPKCols;
237: }
238:
239: // ----------------------------------------------------------
240:
241: public int findColumnPosition(String sColName) {
242: // Busca la posición de una columna por nombre
243: // Devuelve -1 si no la encuentra
244:
245: int iCol = -1;
246:
247: for (int c = 0; (c < ColCount) && (iCol == -1); c++)
248: if (sColName.equalsIgnoreCase(ColNames[c]))
249: iCol = c;
250:
251: return iCol;
252: } // findColumnPosition
253:
254: // ----------------------------------------------------------
255:
256: public int findColumnType(String sColName) {
257: // Busca el tipo de una columna por nombre
258: // Devuelve 0 si no la encuentra
259:
260: int iType = 0;
261:
262: for (int c = 0; c < ColCount; c++)
263: if (sColName.equalsIgnoreCase(ColNames[c])) {
264: iType = ColTypes[c];
265: break;
266: }
267: return iType;
268: } // findColumnType
269:
270: // ----------------------------------------------------------
271:
272: public boolean inheritsPK(DataTblDef oTblDef)
273: throws ArrayIndexOutOfBoundsException {
274:
275: if (DebugFile.trace) {
276: DebugFile.writeln("Begin " + BaseTable
277: + " DataTblDef.inheritsPK(" + oTblDef.BaseTable
278: + ")");
279: DebugFile.incIdent();
280: DebugFile.writeln(BaseTable + " has "
281: + String.valueOf(cPKs) + " pk columns");
282: }
283:
284: // Comprueba si dos DataTblDef tienen la misma estructura de tipos
285: // y longitudes en sus campos de la PK y, por consiguiente, con
286: // gran probabilidad un DataTblDef hereda la PK del otro.
287: boolean bSamePK;
288: int pc, fc;
289:
290: int iMatchCount = 0;
291:
292: if (DebugFile.trace)
293: if (cPKs < oTblDef.cPKs)
294: DebugFile.writeln(BaseTable
295: + " does not inherit PK from "
296: + oTblDef.BaseTable + " because "
297: + oTblDef.BaseTable + " has "
298: + String.valueOf(oTblDef.cPKs)
299: + " PK columns and " + BaseTable + " has only "
300: + String.valueOf(cPKs) + " PK columns");
301:
302: bSamePK = (cPKs >= oTblDef.cPKs);
303:
304: if (bSamePK) {
305:
306: for (int fk = 0; fk < cPKs; fk++) {
307:
308: if (DebugFile.trace)
309: DebugFile.writeln("fk=" + String.valueOf(fk));
310:
311: fc = findColumnPosition(PrimaryKeys[fk]);
312:
313: if (DebugFile.trace && -1 == fc)
314: DebugFile.writeln("cannot find column "
315: + PrimaryKeys[fk] + " on " + BaseTable);
316:
317: if (-1 != fc) {
318:
319: for (int pk = 0; pk < oTblDef.cPKs; pk++) {
320:
321: if (DebugFile.trace)
322: DebugFile.writeln("pk="
323: + String.valueOf(pk));
324:
325: pc = oTblDef
326: .findColumnPosition(oTblDef.PrimaryKeys[pk]);
327:
328: if (DebugFile.trace && -1 == pc)
329: DebugFile.writeln("cannot find column "
330: + oTblDef.PrimaryKeys[pk] + " on "
331: + oTblDef.BaseTable);
332:
333: if (-1 != pc) {
334:
335: if (DebugFile.trace)
336: DebugFile.writeln("trying " + BaseTable
337: + "." + ColNames[fc] + " and "
338: + oTblDef.BaseTable + "."
339: + oTblDef.ColNames[pc]);
340:
341: if ((oTblDef.ColTypes[pc] == ColTypes[fc] && oTblDef.ColSizes[pc] == ColSizes[fc])
342: && ((cPKs == 1 && oTblDef.ColNames[pc]
343: .equalsIgnoreCase(ColNames[fc])) || (cPKs > 1))) {
344:
345: if (DebugFile.trace) {
346: if (cPKs > 1)
347: DebugFile
348: .writeln(BaseTable
349: + "."
350: + PrimaryKeys[fk]
351: + " matches "
352: + oTblDef.BaseTable
353: + "."
354: + oTblDef.PrimaryKeys[pk]);
355: else
356: DebugFile
357: .writeln(BaseTable
358: + "."
359: + PrimaryKeys[fk]
360: + " matches same column on "
361: + oTblDef.BaseTable
362: + "."
363: + oTblDef.PrimaryKeys[pk]);
364: }
365:
366: iMatchCount++;
367: break;
368: } else {
369: if (DebugFile.trace) {
370: if (oTblDef.ColTypes[pc] != ColTypes[fc])
371: DebugFile
372: .writeln(BaseTable
373: + "."
374: + PrimaryKeys[fk]
375: + " has SQLType "
376: + ColTypes[fc]
377: + " and "
378: + oTblDef.BaseTable
379: + "."
380: + oTblDef.PrimaryKeys[pk]
381: + " has SQLType "
382: + oTblDef.ColTypes[pc]);
383: else if (oTblDef.ColSizes[pc] == ColSizes[fc])
384: DebugFile
385: .writeln(BaseTable
386: + "."
387: + PrimaryKeys[fk]
388: + " has size "
389: + ColSizes[fc]
390: + " and "
391: + oTblDef.BaseTable
392: + "."
393: + oTblDef.PrimaryKeys[pk]
394: + " has size "
395: + oTblDef.ColSizes[pc]);
396: else if (cPKs == 1
397: && !oTblDef.ColNames[pc]
398: .equalsIgnoreCase(ColNames[fc]))
399: DebugFile
400: .writeln(BaseTable
401: + "."
402: + PrimaryKeys[fk]
403: + " as same SQLType and size as "
404: + oTblDef.BaseTable
405: + "."
406: + oTblDef.PrimaryKeys[pk]
407: + " but it is not considered match because "
408: + PrimaryKeys[fk]
409: + " is a single primary key column and they don't have the same name");
410: }
411: }
412: } // fi (-1!=pc)
413: } // next (pk)
414: if (iMatchCount == oTblDef.cPKs)
415: break;
416:
417: } // fi (-1!=fc)
418: } // next (fk)
419: }
420:
421: if (DebugFile.trace)
422: DebugFile.writeln("match count = "
423: + String.valueOf(iMatchCount) + " , primary keys ="
424: + String.valueOf(oTblDef.cPKs));
425:
426: if (iMatchCount < oTblDef.cPKs)
427: bSamePK = false;
428:
429: if (DebugFile.trace) {
430: DebugFile.decIdent();
431: DebugFile.writeln("End DataTblDef.inheritsPK() : "
432: + String.valueOf(bSamePK));
433: }
434:
435: return bSamePK;
436: } // inheritsPK
437:
438: // ----------------------------------------------------------
439:
440: public boolean bestMatch(int iThisCol, DataTblDef oTblDef,
441: int iParentPK) {
442: int[] aScores = new int[cPKs];
443: int iPKPos;
444: int iParentCol;
445: int iPKRelativePos = 0;
446: int iBestMatch = -1;
447: int iBestScore = -1;
448:
449: if (DebugFile.trace) {
450: DebugFile.writeln("Begin DataTblDef.bestMatch(" + BaseTable
451: + "." + ColNames[iThisCol] + " , "
452: + oTblDef.BaseTable + "."
453: + oTblDef.PrimaryKeys[iParentPK] + ")");
454: DebugFile.incIdent();
455: }
456:
457: iParentCol = oTblDef
458: .findColumnPosition(oTblDef.PrimaryKeys[iParentPK]);
459:
460: // Find seeked field relative position inside primary key
461: for (int c = 0; c < this .ColCount & iPKRelativePos < cPKs; c++)
462: if (PrimaryKeyMarks[c]
463: && !ColNames[c]
464: .equalsIgnoreCase(ColNames[iThisCol]))
465: iPKRelativePos++;
466: else if (PrimaryKeyMarks[c]
467: && ColNames[c].equalsIgnoreCase(ColNames[iThisCol]))
468: break;
469:
470: // For each key field, assign a score
471: for (int k = 0; k < cPKs; k++) {
472: aScores[k] = 0;
473:
474: if (PrimaryKeys[k]
475: .equalsIgnoreCase(oTblDef.ColNames[iParentCol]))
476: aScores[k] += 5; // Add 5 points if names match
477:
478: iPKPos = findColumnPosition(PrimaryKeys[k]);
479:
480: if (iPKPos > -1)
481: if (ColTypes[iPKPos] == oTblDef.ColTypes[iParentCol]
482: && ColSizes[iPKPos] == oTblDef.ColSizes[iParentCol])
483: aScores[k] += 1; // Add 1 point if types and sizes match
484: } // next
485:
486: // Check if seeked field has the highest score
487: for (int k = 0; k < cPKs; k++) {
488: if (aScores[k] > iBestScore) {
489: iBestScore = aScores[k];
490: iBestMatch = k;
491: } // fi
492: } // next
493:
494: if (DebugFile.trace) {
495: DebugFile.writeln("pk relative position is "
496: + String.valueOf(iPKRelativePos)
497: + ", best match relative position is "
498: + String.valueOf(iBestMatch));
499: DebugFile.decIdent();
500: DebugFile.writeln("End DataTblDef.bestMatch() : "
501: + String.valueOf(iPKRelativePos == iBestMatch));
502: }
503: return (iPKRelativePos == iBestMatch);
504:
505: } // bestMatch
506:
507: // ----------------------------------------------------------
508:
509: public boolean isPrimaryKey(int iCol) {
510:
511: boolean bRetVal = PrimaryKeyMarks[iCol];
512:
513: return bRetVal;
514: } // isPrimaryKey
515:
516: // ----------------------------------------------------------
517:
518: public boolean isPrimaryKey(String sCol) {
519: boolean bRetVal;
520:
521: int iCol = findColumnPosition(sCol);
522:
523: if (-1 == iCol)
524: bRetVal = false;
525: else
526: bRetVal = PrimaryKeyMarks[iCol];
527:
528: return bRetVal;
529: } // isPrimaryKey
530:
531: // *********************************************************
532: // * Member Variables
533:
534: public int cPKs; // Nº total de campos en la PK
535: public boolean bMayInheritPK;
536: private boolean PrimaryKeyMarks[]; // Array con flags booleanos de PK / No-PK
537: public String PrimaryKeys[]; // Nombre de los campos en la PK
538: public String ColNames[]; // Nombres de todas las columnas (por orden de aparición)
539: public int ColTypes[]; // Tipos de todas las columnas
540: public int ColSizes[]; // Longitudes de todas las columnas
541: public int ColCount; // Cuenta del nº total de columnas
542: public String BaseTable;
543:
544: } // DataTblDef
|