Java > Open Source Codes > org > webdocwf > util > loader > generator > SqlStatements


1 /*
2      LoaderGenerator - tool for generated xml, sql and doml file needed for Octopus.
3     Copyright (C) 2003 Together
4     This library is free software; you can redistribute it and/or
5     modify it under the terms of the GNU Lesser General Public
6     License as published by the Free Software Foundation; either
7     version 2.1 of the License, or (at your option) any later version.
8     This library is distributed in the hope that it will be useful,
9     but WITHOUT ANY WARRANTY; without even the implied warranty of
10     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
11     Lesser General Public License for more details.
12     You should have received a copy of the GNU Lesser General Public
13     License along with this library; if not, write to the Free Software
14     Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
15  */
16
17 package org.webdocwf.util.loader.generator;
18
19 import java.util.*;
20
21 import org.webdocwf.util.loader.logging.Logger;
22 import org.webdocwf.util.loader.logging.StandardLogger;
23
24 /**
25  *
26  * SqlStatements class creates the sql statements for building target database.
27  * @author Radoslav Dutina
28  * @version 1.0
29  */
30 public class SqlStatements {
31
32     private List list = new ArrayList();
33         private List hsqlPKlist = new ArrayList();
34     // private String[] uncompatibileTypes = {
35 // "int", "integer", "datetime", "smalldatetime",
36 // "money", "smallmoney", "bigint", "smallint", "timestamp", "tinyint",
37 // "ntext", "bit", "int2", "int4", "int8", "bytea",
38 // "text", "real", "date", "time", "tiny", "long", "double", "identity",
39 // "image","longvarchar","long varchar","nvarchar","ntext",
40 // "binary", "blob", "graphic", "longraw", "long raw", "byte"};
41 private String [] targetDBWithoutAlterTable = { "xml", "csv", "excel" };
42
43     private boolean incompatible = false;
44     private boolean incompatibleDBTarget = false;
45     private boolean incompatibleDBSource = false;
46     private Logger logger = null;
47     /**
48      * Construct object SqlStatements with associated parameters.
49      * @param nameSQL is the name of created sql file.
50      * @param tableName is name of the table form which we retrieve data.
51      * @param importDefinitionAttributes is references to ImportDefinitionAttributes object.
52      * @param relationshipsAttributes is references to RelationshipsAttributes object.
53      * @param k is the counter, whish are used for restricting same operations.
54          * @param generatorParameters represents the references to InputParameter object.
55      */
56
57     public SqlStatements(String nameSQL, String tableName, ImportDefinitionAttributes importDefinitionAttributes, RelationshipsAttributes relationshipsAttributes, int k, InputParameters generatorParameters) {
58         setLogger();
59         this.logger.write("full", "SqlStatements is started.");
60         String targetDBType = generatorParameters.getTargetType();
61         String sourceDBType = generatorParameters.getSourceType();
62         checkDBTypeTarget(targetDBType);
63         checkDBTypeSource(sourceDBType);
64
65         if (nameSQL.equalsIgnoreCase("CreateTables")) {
66             list.add("Create table" + " " + tableName);
67             list.add("(");
68             if (generatorParameters.getSourceType().equalsIgnoreCase("csv")) {
69                 for (int i = 0; i < importDefinitionAttributes.getTagSourceColumnName().length; i++) {
70                     if (i == importDefinitionAttributes.getTagSourceColumnName().length - 1)
71                         list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + "VARCHAR" + " (254) " + " ");
72                     else
73                         list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + "VARCHAR" + " (254) " + " ,");
74                 }
75                 list.add(");");
76                 list.add("");
77                 list.add("");
78                 list.add("");
79             } else if (generatorParameters.getSourceType().equalsIgnoreCase("access")) {
80                 String end = " ,";
81                 for (int i = 0; i < importDefinitionAttributes.getTagSourceColumnName().length; i++) {
82                     if (i == importDefinitionAttributes.getTagSourceColumnName().length - 1)
83                         end = " ";
84                     if (importDefinitionAttributes.getTagColumnLenght()[i].equalsIgnoreCase(""))
85                         list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() + importDefinitionAttributes.getTagAllowNulls()[i] + end);
86                     else
87                         list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() + "(" + importDefinitionAttributes.getTagColumnLenght()[i] + ") " + importDefinitionAttributes.getTagAllowNulls()[i] + end);
88                 }
89                 list.add(");");
90                 list.add("");
91                 list.add("");
92                 list.add("");
93             } else {
94                 String end = " ";
95                 for (int i = 0; i < importDefinitionAttributes.getTagSourceColumnName().length; i++) {
96                     boolean pk = true;
97                     for (int j = 0; j < relationshipsAttributes.getPrimaryKeys().length; j = j + 2) {
98                         if (relationshipsAttributes.getPrimaryKeys()[j + 1].equalsIgnoreCase(importDefinitionAttributes.getTagSourceColumnName()[i])) {
99                             pk = false;
100                             break;
101                         }
102                     }
103                     if (i != importDefinitionAttributes.getTagSourceColumnName().length - 1)
104                         end = " ,";
105                     else
106                         end = " ";
107
108                     // checkType(importDefinitionAttributes.getTagColumnType()[i]);
109 //incompatible atibile is parameter, which decide if we should put the
110 //length parameter in to sql expression.
111 // if (!incompatible) {
112 if (generatorParameters.getAlterTablePrimaryKey().equalsIgnoreCase("true")) {
113                         list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() //+ "("
114 +importDefinitionAttributes.getTagColumnLenght()[i] //+
115 //") "
116 +importDefinitionAttributes.getTagAllowNulls()[i] + end);
117                     } else {
118                         //pk parameter is used to decide if the fild in table is primary key.
119 //If it is, then we put the fild in to expression.
120 if (pk) {
121                             //parameter isn't the primary key.
122 list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase()
123                             // + "("
124 +importDefinitionAttributes.getTagColumnLenght()[i] +
125                             // ") "
126 // +
127 importDefinitionAttributes.getTagAllowNulls()[i] + end);
128                         } else {
129                                                     //TODO 23.8 2004 ZK added because of hsql problem with primary keys on 2,3,... columns.
130 //Change this to support other databases, not only hsql.
131 if (generatorParameters.getTargetType().equalsIgnoreCase("HypersonicSQL")){
132 //
133 hsqlPKlist.add(importDefinitionAttributes.getTagSourceColumnName()[i]);
134                                                                 list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase()
135                                                               +importDefinitionAttributes.getTagColumnLenght()[i]
136                                                                 +", ");
137                                 }else{
138                                 //end
139 //parameter is the primary key.
140 list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + " " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() //+ "("
141 +importDefinitionAttributes.getTagColumnLenght()[i] //+
142 //") "
143 +"PRIMARY KEY " + end);
144                                 }
145                         }
146                     }
147                     // }
148 // else {
149 // if (generatorParameters.getAlterTablePrimaryKey().equalsIgnoreCase(
150 // "true")) {
151 // list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
152 // " " +
153 // (importDefinitionAttributes.getTagColumnType()[i]).
154 // toUpperCase() + " "
155 // + importDefinitionAttributes.getTagAllowNulls()[i]
156 // + end);
157 // }
158 // else {
159 // if (pk) {
160 // list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
161 // " " +
162 // (importDefinitionAttributes.getTagColumnType()[i]).
163 // toUpperCase() + " "
164 // + importDefinitionAttributes.getTagAllowNulls()[i]
165 // + end);
166 // }
167 // else {
168 // list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
169 // " " +
170 // (importDefinitionAttributes.getTagColumnType()[i]).
171 // toUpperCase() + " "
172 // + " PRIMARY KEY "
173 // + end);
174 // }
175 // }
176 // }
177 }
178                                 //TODO 23.8 2004 ZK added because of hsql problem with primary keys on 2,3,... columns.
179 //Change this to support other databases, not only hsql.
180 if (generatorParameters.getTargetType().equalsIgnoreCase("HypersonicSQL")){
181
182                                     if (hsqlPKlist.size()>0) {
183
184                                         String columnNames = "";
185                                         columnNames = columnNames+" (";
186
187                                         for (int i = 0; i < hsqlPKlist.size()-1; i++) {
188                                             columnNames = columnNames + hsqlPKlist.get(i) + ",";
189
190                                         }
191
192                                           if (hsqlPKlist.size() >= 2){
193                                                 columnNames = columnNames + hsqlPKlist.get(hsqlPKlist.size()-1) + ")";
194
195                                           }else if (hsqlPKlist.size() == 1){
196                                                 columnNames = columnNames + hsqlPKlist.get(0) + ")";
197
198                                           }
199                                           list.add(", CONSTRAINT" + " " +importDefinitionAttributes.getTableName()+"_PK" +" "
200                                                                 +"PRIMARY KEY " + columnNames);
201                                   }
202                                 }
203                                 //end
204 list.add(");");
205                 list.add("");
206                 list.add("");
207                 list.add("");
208             }
209         } else if (nameSQL.equalsIgnoreCase("CreatePrimary")) {
210             list.add("");
211             if (incompatibleDBSource) {
212                 if (k == 0) {
213                     list.add("");
214                 }
215             } else if (incompatibleDBTarget) {
216                 if (k == 0) {
217                     list.add("");
218                 }
219             } else {
220                 if (relationshipsAttributes.getPrimaryKeys().length != 0) {
221                     int primaryCount = relationshipsAttributes.getPrimaryKeys().length;
222                     for (int i = 0; i < primaryCount; i = i + 2) {
223                         String arrayOfPrimaryNames = relationshipsAttributes.getPrimaryKeys()[i + 1];
224                         for (int m = i + 2; m < primaryCount; m = m + 2) {
225                             if (relationshipsAttributes.getPrimaryKeys()[i].equalsIgnoreCase(relationshipsAttributes.getPrimaryKeys()[m])) {
226
227                                 arrayOfPrimaryNames = arrayOfPrimaryNames + "," + relationshipsAttributes.getPrimaryKeys()[m + 1];
228                                 i = i + 2;
229                             } else
230                                 break;
231                         }
232
233                         list.add("ALTER TABLE " + " " + relationshipsAttributes.getTableName() + " " + "ADD CONSTRAINT " + relationshipsAttributes.getPrimaryKeys()[i] + " PRIMARY KEY" + "(" +
234                         //relationshipsAttributes.getPrimaryKeys()[i+1]+") ;");
235 arrayOfPrimaryNames + ") ;");
236                     }
237                 } else {
238                     list.add("");
239                 }
240             } //end of main else
241 } else if (nameSQL.equalsIgnoreCase("CreateIndex")) {
242             list.add("");
243             if (incompatibleDBSource) {
244                 if (k == 0) {
245                     list.add("");
246                 }
247             } else if (incompatibleDBTarget) {
248                 if (k == 0) {
249                     list.add("");
250                 }
251             } else {
252                 if (relationshipsAttributes.getIndexVariables().length != 0) {
253                     int indexCount = relationshipsAttributes.getIndexVariables().length;
254                     for (int i = 0; i < indexCount; i = i + 3) {
255                         String arrayOfIndexNames = relationshipsAttributes.getIndexVariables()[i + 2];
256                         for (int m = i + 3; m < indexCount; m = m + 3) {
257                             if (relationshipsAttributes.getIndexVariables()[i + 1].equalsIgnoreCase(relationshipsAttributes.getIndexVariables()[m + 1])) {
258                                 arrayOfIndexNames = arrayOfIndexNames + "," + relationshipsAttributes.getIndexVariables()[m + 2];
259                                 i = i + 3;
260                             } else
261                                 break;
262                         }
263                         if (relationshipsAttributes.getIndexVariables()[i].equalsIgnoreCase("0") || relationshipsAttributes.getIndexVariables()[i].equalsIgnoreCase("1")) {
264                             //unique mey have the value 0 or 1
265 int j = Integer.parseInt(relationshipsAttributes.getIndexVariables()[i]);
266                             String unique;
267                             if (j == 1)
268                                 unique = "";
269                             else
270                                 unique = "UNIQUE";
271                             list.add("CREATE " + unique + " INDEX " + relationshipsAttributes.getIndexVariables()[i + 1] +
272                             //" ON "+tableName+"("+relationshipsAttributes.getIndexVariables()[i+2]+") ;");
273 " ON " + tableName + "(" + arrayOfIndexNames + ") ;");
274
275                         } else if (relationshipsAttributes.getIndexVariables()[i].equalsIgnoreCase("true") || relationshipsAttributes.getIndexVariables()[i].equalsIgnoreCase("false")) {
276                             //unique mey have the value true or false
277 String unique;
278                             String j = relationshipsAttributes.getIndexVariables()[i];
279                             if (j.equalsIgnoreCase("true"))
280                                 unique = "";
281                             else
282                                 unique = "UNIQUE";
283                             list.add("CREATE " + unique + " INDEX " + relationshipsAttributes.getIndexVariables()[i + 1] +
284                             //" ON "+tableName+"("+relationshipsAttributes.getIndexVariables()[i+2]+") ;");
285 " ON " + tableName + "(" + arrayOfIndexNames + ") ;");
286                         }
287                     }
288                 } else {
289                     //relationshipsAttributes.getIndexVariables().length==0
290 list.add("");
291                 }
292             }
293         } else if (nameSQL.equalsIgnoreCase("CreateIntegrity")) {
294             list.add("");
295             if (incompatibleDBSource) {
296                 if (k == 0) {
297                     list.add("");
298                 }
299             } else if (incompatibleDBTarget) {
300                 if (k == 0) {
301                     list.add("");
302                 }
303             } else {
304                 //TODO ZK added relationshipsAttributes.getForeignVariables().length >= 5 because problems with c-jdbc
305 if (relationshipsAttributes.getForeignVariables().length != 0 && relationshipsAttributes.getForeignVariables().length >= 5) {
306
307                     for (int i = 0; i < relationshipsAttributes.getForeignVariables().length; i = i + 5) {
308
309                         list.add("ALTER TABLE " + relationshipsAttributes.getForeignVariables()[i] + " ADD CONSTRAINT " + relationshipsAttributes.getForeignVariables()[i + 1] + " FOREIGN KEY (" + relationshipsAttributes.getForeignVariables()[i + 2] + ")" + " REFERENCES " + relationshipsAttributes.getForeignVariables()[i + 3] + " (" + relationshipsAttributes.getForeignVariables()[i + 4] + ") ;");
310                     }
311                 } else {
312                     list.add("");
313                 }
314             }
315         } else if (nameSQL.equalsIgnoreCase("DropTables")) {
316             list.add("");
317             list.add("DROP TABLE " + tableName + " ;");
318             list.add("");
319         } else if (nameSQL.equalsIgnoreCase("DropIntegrity")) {
320             //TODO ZK added relationshipsAttributes.getForeignVariables().length >= 2 because problems with c-jdbc
321 if (relationshipsAttributes.getForeignVariables().length != 0 && relationshipsAttributes.getForeignVariables().length >= 2) {
322
323                 for (int i = 0; i < relationshipsAttributes.getForeignVariables().length; i = i + 5) {
324                     list.add("");
325                     list.add("ALTER TABLE " + relationshipsAttributes.getForeignVariables()[i] + " DROP CONSTRAINT " + relationshipsAttributes.getForeignVariables()[i + 1] + " ;");
326                     list.add("");
327                 }
328
329             } else {
330                 list.add("");
331             }
332
333         }
334         this.logger.write("full", "SqlStatements is finished.");
335     }
336
337     /**
338      * This method sets the value of list patameter.
339      * @param create_Stream is the value of parameter.
340      */
341     public void setCreateStream(String [] create_Stream) {
342         list = Arrays.asList(create_Stream);
343     }
344
345     /**
346      * This method read the value of list patameter.
347      * @return value of parameter.
348      */
349     public String [] getCreateStream() {
350         String [] ret = new String [list.size()];
351         list.toArray(ret);
352         return ret;
353     }
354
355     // /**
356 // * This method check the type of named data type.
357 // * @param type is data type which we wont to check.
358 // * @return true or false.
359 // */
360 // private boolean checkType(String type) {
361 // for (int i = 0; i < uncompatibileTypes.length; i++) {
362 // if (type.toLowerCase().endsWith(uncompatibileTypes[i])) {
363 // this.incompatible = true;
364 // break;
365 // }
366 // else {
367 // this.incompatible = false;
368 // }
369 // }
370 // return incompatible;
371 // }
372
373     private boolean checkDBTypeTarget(String type) {
374         for (int i = 0; i < targetDBWithoutAlterTable.length; i++) {
375             if (type.equalsIgnoreCase(targetDBWithoutAlterTable[i])) {
376                 this.incompatibleDBTarget = true;
377                 break;
378             } else {
379                 this.incompatibleDBTarget = false;
380             }
381         }
382         return incompatibleDBTarget;
383     }
384
385     private boolean checkDBTypeSource(String type) {
386         for (int i = 0; i < targetDBWithoutAlterTable.length; i++) {
387             if (type.equalsIgnoreCase(targetDBWithoutAlterTable[i])) {
388                 this.incompatibleDBSource = true;
389                 break;
390             } else {
391                 this.incompatibleDBSource = false;
392             }
393         }
394         return incompatibleDBSource;
395     }
396
397     /**
398                 * This method will set logger object
399                 * @param logger
400                 */
401     private void setLogger() {
402         this.logger = StandardLogger.getCentralLogger();
403     }
404 }
405

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags