001: /*
002: LoaderGenerator - tool for generated xml, sql and doml file needed for Octopus.
003: Copyright (C) 2003 Together
004: This library is free software; you can redistribute it and/or
005: modify it under the terms of the GNU Lesser General Public
006: License as published by the Free Software Foundation; either
007: version 2.1 of the License, or (at your option) any later version.
008: This library is distributed in the hope that it will be useful,
009: but WITHOUT ANY WARRANTY; without even the implied warranty of
010: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
011: Lesser General Public License for more details.
012: You should have received a copy of the GNU Lesser General Public
013: License along with this library; if not, write to the Free Software
014: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
015: */
016:
017: package org.webdocwf.util.loader.generator;
018:
019: import java.util.*;
020:
021: import org.webdocwf.util.loader.logging.Logger;
022: import org.webdocwf.util.loader.logging.StandardLogger;
023:
024: /**
025: *
026: * SqlStatements class creates the sql statements for building target database.
027: * @author Radoslav Dutina
028: * @version 1.0
029: */
030: public class SqlStatements {
031:
032: private List list = new ArrayList();
033: private List hsqlPKlist = new ArrayList();
034: // private String[] uncompatibileTypes = {
035: // "int", "integer", "datetime", "smalldatetime",
036: // "money", "smallmoney", "bigint", "smallint", "timestamp", "tinyint",
037: // "ntext", "bit", "int2", "int4", "int8", "bytea",
038: // "text", "real", "date", "time", "tiny", "long", "double", "identity",
039: // "image","longvarchar","long varchar","nvarchar","ntext",
040: // "binary", "blob", "graphic", "longraw", "long raw", "byte"};
041: private String[] targetDBWithoutAlterTable = { "xml", "csv",
042: "excel" };
043:
044: private boolean incompatible = false;
045: private boolean incompatibleDBTarget = false;
046: private boolean incompatibleDBSource = false;
047: private Logger logger = null;
048:
049: /**
050: * Construct object SqlStatements with associated parameters.
051: * @param nameSQL is the name of created sql file.
052: * @param tableName is name of the table form which we retrieve data.
053: * @param importDefinitionAttributes is references to ImportDefinitionAttributes object.
054: * @param relationshipsAttributes is references to RelationshipsAttributes object.
055: * @param k is the counter, whish are used for restricting same operations.
056: * @param generatorParameters represents the references to InputParameter object.
057: */
058:
059: public SqlStatements(String nameSQL, String tableName,
060: ImportDefinitionAttributes importDefinitionAttributes,
061: RelationshipsAttributes relationshipsAttributes, int k,
062: InputParameters generatorParameters) {
063: setLogger();
064: this .logger.write("full", "SqlStatements is started.");
065: String targetDBType = generatorParameters.getTargetType();
066: String sourceDBType = generatorParameters.getSourceType();
067: checkDBTypeTarget(targetDBType);
068: checkDBTypeSource(sourceDBType);
069:
070: if (nameSQL.equalsIgnoreCase("CreateTables")) {
071: list.add("Create table" + " " + tableName);
072: list.add("(");
073: if (generatorParameters.getSourceType().equalsIgnoreCase(
074: "csv")) {
075: for (int i = 0; i < importDefinitionAttributes
076: .getTagSourceColumnName().length; i++) {
077: if (i == importDefinitionAttributes
078: .getTagSourceColumnName().length - 1)
079: list
080: .add(importDefinitionAttributes
081: .getTagSourceColumnName()[i]
082: + " "
083: + "VARCHAR"
084: + " (254) "
085: + " ");
086: else
087: list.add(importDefinitionAttributes
088: .getTagSourceColumnName()[i]
089: + " "
090: + "VARCHAR"
091: + " (254) "
092: + " ,");
093: }
094: list.add(");");
095: list.add("");
096: list.add("");
097: list.add("");
098: } else if (generatorParameters.getSourceType()
099: .equalsIgnoreCase("access")) {
100: String end = " ,";
101: for (int i = 0; i < importDefinitionAttributes
102: .getTagSourceColumnName().length; i++) {
103: if (i == importDefinitionAttributes
104: .getTagSourceColumnName().length - 1)
105: end = " ";
106: if (importDefinitionAttributes.getTagColumnLenght()[i]
107: .equalsIgnoreCase(""))
108: list.add(importDefinitionAttributes
109: .getTagSourceColumnName()[i]
110: + " "
111: + (importDefinitionAttributes
112: .getTagColumnType()[i])
113: .toUpperCase()
114: + importDefinitionAttributes
115: .getTagAllowNulls()[i] + end);
116: else
117: list.add(importDefinitionAttributes
118: .getTagSourceColumnName()[i]
119: + " "
120: + (importDefinitionAttributes
121: .getTagColumnType()[i])
122: .toUpperCase()
123: + "("
124: + importDefinitionAttributes
125: .getTagColumnLenght()[i]
126: + ") "
127: + importDefinitionAttributes
128: .getTagAllowNulls()[i] + end);
129: }
130: list.add(");");
131: list.add("");
132: list.add("");
133: list.add("");
134: } else {
135: String end = " ";
136: for (int i = 0; i < importDefinitionAttributes
137: .getTagSourceColumnName().length; i++) {
138: boolean pk = true;
139: for (int j = 0; j < relationshipsAttributes
140: .getPrimaryKeys().length; j = j + 2) {
141: if (relationshipsAttributes.getPrimaryKeys()[j + 1]
142: .equalsIgnoreCase(importDefinitionAttributes
143: .getTagSourceColumnName()[i])) {
144: pk = false;
145: break;
146: }
147: }
148: if (i != importDefinitionAttributes
149: .getTagSourceColumnName().length - 1)
150: end = " ,";
151: else
152: end = " ";
153:
154: // checkType(importDefinitionAttributes.getTagColumnType()[i]);
155: //incompatible atibile is parameter, which decide if we should put the
156: //length parameter in to sql expression.
157: // if (!incompatible) {
158: if (generatorParameters.getAlterTablePrimaryKey()
159: .equalsIgnoreCase("true")) {
160: list.add(importDefinitionAttributes
161: .getTagSourceColumnName()[i]
162: + " "
163: + (importDefinitionAttributes
164: .getTagColumnType()[i])
165: .toUpperCase() //+ "("
166: + importDefinitionAttributes
167: .getTagColumnLenght()[i] //+
168: //") "
169: + importDefinitionAttributes
170: .getTagAllowNulls()[i] + end);
171: } else {
172: //pk parameter is used to decide if the fild in table is primary key.
173: //If it is, then we put the fild in to expression.
174: if (pk) {
175: //parameter isn't the primary key.
176: list.add(importDefinitionAttributes
177: .getTagSourceColumnName()[i]
178: + " "
179: + (importDefinitionAttributes
180: .getTagColumnType()[i])
181: .toUpperCase()
182: // + "("
183: + importDefinitionAttributes
184: .getTagColumnLenght()[i]
185: +
186: // ") "
187: // +
188: importDefinitionAttributes
189: .getTagAllowNulls()[i]
190: + end);
191: } else {
192: //TODO 23.8 2004 ZK added because of hsql problem with primary keys on 2,3,... columns.
193: //Change this to support other databases, not only hsql.
194: if (generatorParameters.getTargetType()
195: .equalsIgnoreCase("HypersonicSQL")) {
196: //
197: hsqlPKlist
198: .add(importDefinitionAttributes
199: .getTagSourceColumnName()[i]);
200: list
201: .add(importDefinitionAttributes
202: .getTagSourceColumnName()[i]
203: + " "
204: + (importDefinitionAttributes
205: .getTagColumnType()[i])
206: .toUpperCase()
207: + importDefinitionAttributes
208: .getTagColumnLenght()[i]
209: + ", ");
210: } else {
211: //end
212: //parameter is the primary key.
213: list
214: .add(importDefinitionAttributes
215: .getTagSourceColumnName()[i]
216: + " "
217: + (importDefinitionAttributes
218: .getTagColumnType()[i])
219: .toUpperCase() //+ "("
220: + importDefinitionAttributes
221: .getTagColumnLenght()[i] //+
222: //") "
223: + "PRIMARY KEY " + end);
224: }
225: }
226: }
227: // }
228: // else {
229: // if (generatorParameters.getAlterTablePrimaryKey().equalsIgnoreCase(
230: // "true")) {
231: // list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
232: // " " +
233: // (importDefinitionAttributes.getTagColumnType()[i]).
234: // toUpperCase() + " "
235: // + importDefinitionAttributes.getTagAllowNulls()[i]
236: // + end);
237: // }
238: // else {
239: // if (pk) {
240: // list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
241: // " " +
242: // (importDefinitionAttributes.getTagColumnType()[i]).
243: // toUpperCase() + " "
244: // + importDefinitionAttributes.getTagAllowNulls()[i]
245: // + end);
246: // }
247: // else {
248: // list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
249: // " " +
250: // (importDefinitionAttributes.getTagColumnType()[i]).
251: // toUpperCase() + " "
252: // + " PRIMARY KEY "
253: // + end);
254: // }
255: // }
256: // }
257: }
258: //TODO 23.8 2004 ZK added because of hsql problem with primary keys on 2,3,... columns.
259: //Change this to support other databases, not only hsql.
260: if (generatorParameters.getTargetType()
261: .equalsIgnoreCase("HypersonicSQL")) {
262:
263: if (hsqlPKlist.size() > 0) {
264:
265: String columnNames = "";
266: columnNames = columnNames + " (";
267:
268: for (int i = 0; i < hsqlPKlist.size() - 1; i++) {
269: columnNames = columnNames
270: + hsqlPKlist.get(i) + ",";
271:
272: }
273:
274: if (hsqlPKlist.size() >= 2) {
275: columnNames = columnNames
276: + hsqlPKlist
277: .get(hsqlPKlist.size() - 1)
278: + ")";
279:
280: } else if (hsqlPKlist.size() == 1) {
281: columnNames = columnNames
282: + hsqlPKlist.get(0) + ")";
283:
284: }
285: list.add(", CONSTRAINT"
286: + " "
287: + importDefinitionAttributes
288: .getTableName() + "_PK" + " "
289: + "PRIMARY KEY " + columnNames);
290: }
291: }
292: //end
293: list.add(");");
294: list.add("");
295: list.add("");
296: list.add("");
297: }
298: } else if (nameSQL.equalsIgnoreCase("CreatePrimary")) {
299: list.add("");
300: if (incompatibleDBSource) {
301: if (k == 0) {
302: list.add("");
303: }
304: } else if (incompatibleDBTarget) {
305: if (k == 0) {
306: list.add("");
307: }
308: } else {
309: if (relationshipsAttributes.getPrimaryKeys().length != 0) {
310: int primaryCount = relationshipsAttributes
311: .getPrimaryKeys().length;
312: for (int i = 0; i < primaryCount; i = i + 2) {
313: String arrayOfPrimaryNames = relationshipsAttributes
314: .getPrimaryKeys()[i + 1];
315: for (int m = i + 2; m < primaryCount; m = m + 2) {
316: if (relationshipsAttributes
317: .getPrimaryKeys()[i]
318: .equalsIgnoreCase(relationshipsAttributes
319: .getPrimaryKeys()[m])) {
320:
321: arrayOfPrimaryNames = arrayOfPrimaryNames
322: + ","
323: + relationshipsAttributes
324: .getPrimaryKeys()[m + 1];
325: i = i + 2;
326: } else
327: break;
328: }
329:
330: list.add("ALTER TABLE "
331: + " "
332: + relationshipsAttributes
333: .getTableName()
334: + " "
335: + "ADD CONSTRAINT "
336: + relationshipsAttributes
337: .getPrimaryKeys()[i]
338: + " PRIMARY KEY" + "(" +
339: //relationshipsAttributes.getPrimaryKeys()[i+1]+") ;");
340: arrayOfPrimaryNames + ") ;");
341: }
342: } else {
343: list.add("");
344: }
345: } //end of main else
346: } else if (nameSQL.equalsIgnoreCase("CreateIndex")) {
347: list.add("");
348: if (incompatibleDBSource) {
349: if (k == 0) {
350: list.add("");
351: }
352: } else if (incompatibleDBTarget) {
353: if (k == 0) {
354: list.add("");
355: }
356: } else {
357: if (relationshipsAttributes.getIndexVariables().length != 0) {
358: int indexCount = relationshipsAttributes
359: .getIndexVariables().length;
360: for (int i = 0; i < indexCount; i = i + 3) {
361: String arrayOfIndexNames = relationshipsAttributes
362: .getIndexVariables()[i + 2];
363: for (int m = i + 3; m < indexCount; m = m + 3) {
364: if (relationshipsAttributes
365: .getIndexVariables()[i + 1]
366: .equalsIgnoreCase(relationshipsAttributes
367: .getIndexVariables()[m + 1])) {
368: arrayOfIndexNames = arrayOfIndexNames
369: + ","
370: + relationshipsAttributes
371: .getIndexVariables()[m + 2];
372: i = i + 3;
373: } else
374: break;
375: }
376: if (relationshipsAttributes.getIndexVariables()[i]
377: .equalsIgnoreCase("0")
378: || relationshipsAttributes
379: .getIndexVariables()[i]
380: .equalsIgnoreCase("1")) {
381: //unique mey have the value 0 or 1
382: int j = Integer
383: .parseInt(relationshipsAttributes
384: .getIndexVariables()[i]);
385: String unique;
386: if (j == 1)
387: unique = "";
388: else
389: unique = "UNIQUE";
390: list.add("CREATE "
391: + unique
392: + " INDEX "
393: + relationshipsAttributes
394: .getIndexVariables()[i + 1]
395: +
396: //" ON "+tableName+"("+relationshipsAttributes.getIndexVariables()[i+2]+") ;");
397: " ON " + tableName + "("
398: + arrayOfIndexNames + ") ;");
399:
400: } else if (relationshipsAttributes
401: .getIndexVariables()[i]
402: .equalsIgnoreCase("true")
403: || relationshipsAttributes
404: .getIndexVariables()[i]
405: .equalsIgnoreCase("false")) {
406: //unique mey have the value true or false
407: String unique;
408: String j = relationshipsAttributes
409: .getIndexVariables()[i];
410: if (j.equalsIgnoreCase("true"))
411: unique = "";
412: else
413: unique = "UNIQUE";
414: list.add("CREATE "
415: + unique
416: + " INDEX "
417: + relationshipsAttributes
418: .getIndexVariables()[i + 1]
419: +
420: //" ON "+tableName+"("+relationshipsAttributes.getIndexVariables()[i+2]+") ;");
421: " ON " + tableName + "("
422: + arrayOfIndexNames + ") ;");
423: }
424: }
425: } else {
426: //relationshipsAttributes.getIndexVariables().length==0
427: list.add("");
428: }
429: }
430: } else if (nameSQL.equalsIgnoreCase("CreateIntegrity")) {
431: list.add("");
432: if (incompatibleDBSource) {
433: if (k == 0) {
434: list.add("");
435: }
436: } else if (incompatibleDBTarget) {
437: if (k == 0) {
438: list.add("");
439: }
440: } else {
441: //TODO ZK added relationshipsAttributes.getForeignVariables().length >= 5 because problems with c-jdbc
442: if (relationshipsAttributes.getForeignVariables().length != 0
443: && relationshipsAttributes
444: .getForeignVariables().length >= 5) {
445:
446: for (int i = 0; i < relationshipsAttributes
447: .getForeignVariables().length; i = i + 5) {
448:
449: list.add("ALTER TABLE "
450: + relationshipsAttributes
451: .getForeignVariables()[i]
452: + " ADD CONSTRAINT "
453: + relationshipsAttributes
454: .getForeignVariables()[i + 1]
455: + " FOREIGN KEY ("
456: + relationshipsAttributes
457: .getForeignVariables()[i + 2]
458: + ")"
459: + " REFERENCES "
460: + relationshipsAttributes
461: .getForeignVariables()[i + 3]
462: + " ("
463: + relationshipsAttributes
464: .getForeignVariables()[i + 4]
465: + ") ;");
466: }
467: } else {
468: list.add("");
469: }
470: }
471: } else if (nameSQL.equalsIgnoreCase("DropTables")) {
472: list.add("");
473: list.add("DROP TABLE " + tableName + " ;");
474: list.add("");
475: } else if (nameSQL.equalsIgnoreCase("DropIntegrity")) {
476: //TODO ZK added relationshipsAttributes.getForeignVariables().length >= 2 because problems with c-jdbc
477: if (relationshipsAttributes.getForeignVariables().length != 0
478: && relationshipsAttributes.getForeignVariables().length >= 2) {
479:
480: for (int i = 0; i < relationshipsAttributes
481: .getForeignVariables().length; i = i + 5) {
482: list.add("");
483: list.add("ALTER TABLE "
484: + relationshipsAttributes
485: .getForeignVariables()[i]
486: + " DROP CONSTRAINT "
487: + relationshipsAttributes
488: .getForeignVariables()[i + 1]
489: + " ;");
490: list.add("");
491: }
492:
493: } else {
494: list.add("");
495: }
496:
497: }
498: this .logger.write("full", "SqlStatements is finished.");
499: }
500:
501: /**
502: * This method sets the value of list patameter.
503: * @param create_Stream is the value of parameter.
504: */
505: public void setCreateStream(String[] create_Stream) {
506: list = Arrays.asList(create_Stream);
507: }
508:
509: /**
510: * This method read the value of list patameter.
511: * @return value of parameter.
512: */
513: public String[] getCreateStream() {
514: String[] ret = new String[list.size()];
515: list.toArray(ret);
516: return ret;
517: }
518:
519: // /**
520: // * This method check the type of named data type.
521: // * @param type is data type which we wont to check.
522: // * @return true or false.
523: // */
524: // private boolean checkType(String type) {
525: // for (int i = 0; i < uncompatibileTypes.length; i++) {
526: // if (type.toLowerCase().endsWith(uncompatibileTypes[i])) {
527: // this.incompatible = true;
528: // break;
529: // }
530: // else {
531: // this.incompatible = false;
532: // }
533: // }
534: // return incompatible;
535: // }
536:
537: private boolean checkDBTypeTarget(String type) {
538: for (int i = 0; i < targetDBWithoutAlterTable.length; i++) {
539: if (type.equalsIgnoreCase(targetDBWithoutAlterTable[i])) {
540: this .incompatibleDBTarget = true;
541: break;
542: } else {
543: this .incompatibleDBTarget = false;
544: }
545: }
546: return incompatibleDBTarget;
547: }
548:
549: private boolean checkDBTypeSource(String type) {
550: for (int i = 0; i < targetDBWithoutAlterTable.length; i++) {
551: if (type.equalsIgnoreCase(targetDBWithoutAlterTable[i])) {
552: this .incompatibleDBSource = true;
553: break;
554: } else {
555: this .incompatibleDBSource = false;
556: }
557: }
558: return incompatibleDBSource;
559: }
560:
561: /**
562: * This method will set logger object
563: * @param logger
564: */
565: private void setLogger() {
566: this.logger = StandardLogger.getCentralLogger();
567: }
568: }
|