|
1
23
24 package org.infoglue.cmsinstaller;
25 import java.sql.*;
26 import java.io.*;
27 import java.util.*;
28 import java.util.Date  ;
29
30 public class DB2DatabaseCommander extends DatabaseCommander
31 {
32 private final String driver = "com.ibm.db2.jcc.DB2Driver";
33
34 public DB2DatabaseCommander(String driverClass, String databaseHostName, String databasePortNumber, String databaseInstance, String url, String userName, String password, String infoglueDatabaseUserName, String infoglueDatabasePassword, String databaseName, String databaseSuffix, String hostName, boolean createDatabase, boolean createInitialData, boolean createAntSeekHome, boolean createOfficeStand, boolean createOfficeStand2) throws Exception
35 {
36 super(driverClass, databaseHostName, databasePortNumber, databaseInstance, url, userName, password, infoglueDatabaseUserName, infoglueDatabasePassword, databaseName, databaseSuffix, hostName, createDatabase, createInitialData, createAntSeekHome, createOfficeStand, createOfficeStand2);
37 }
39
40 public String getUrl(String hostName, String databasePortNumber, String database)
41 {
42 return "jdbc:db2://" + databaseHostName + ":" + databasePortNumber + "/" + databaseName + "";
43 }
44
45 public String getUnicodeUrl(String hostName, String databasePortNumber, String database)
46 {
47 return "jdbc:db2://" + databaseHostName + ":" + databasePortNumber + "/" + databaseName + "";
48 }
49
50 public void setupDatabase() throws Exception
51 {
52
55 if(this.createInitialData)
56 {
57 createTables();
58 createInitialData();
59 setupExamples();
60 }
61
62 }
65
66 public void deleteDatabase() throws Exception
67 {
68 }
71
72
73 private void createDatabase() throws Exception
74 {
75 Logger.logInfo("Setting up a new database....");
76 issueCommand(getConnection(), "CREATE DATABASE " + (databaseName.length() > 8 ? databaseName.substring(0, 8) : databaseName) + ";");
77 }
78
79 private void createTables() throws Exception
80 {
81 String url = "jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "";
82 Connection conn = getConnection(url, this.userName, this.password);
83
84 Logger.logInfo("Setting up all tables....");
85
86 try
87 {
88 FileInputStream fis = new FileInputStream("db2_infoglue_core_schema.sql");
89 StringBuffer sb = new StringBuffer ();
90 int c;
91 while((c = fis.read()) != -1)
92 {
93 char character = (char)c;
94 sb.append(character);
95 }
96 String script = sb.toString();
97 Logger.logInfo("script:" + script);
98
99 StringTokenizer st = new StringTokenizer(script, ";");
100 while (st.hasMoreTokens())
101 {
102 String command = st.nextToken();
103 issueCommand(conn, command);
105 }
106
107 }
108 catch(Exception e)
109 {
110 System.out.println("Error: " + e);
111 Logger.logInfo("Error: " + e);
112 e.printStackTrace(System.out);
113 e.printStackTrace(System.err);
114 }
115 }
116
117 private void createInitialData() throws Exception
118 {
119 String url = "jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "";
120 Connection conn = getConnection(url, this.userName, this.password);
121
122 Logger.logInfo("Setting up initial data....");
123
124 try
125 {
126 FileInputStream fis = new FileInputStream("db2_infoglue_initial_data.sql");
127 StringBuffer sb = new StringBuffer ();
128 int c;
129 while((c = fis.read()) != -1)
130 {
131 char character = (char)c;
132 sb.append(character);
133 }
134 String script = sb.toString();
135
137 String [] commands = script.split("--endquery");
138 Logger.logInfo("Parsed " + commands.length + " commands from script");
139
140 for(int i=0; i<commands.length; i++)
141 {
142 String command = commands[i];
143 if(command.indexOf("SPECIAL") > -1)
144 issueSpecialCommand(conn, command.trim());
145 if(command.indexOf("BLOB") > -1)
146 issueSpecialBlobCommand(conn, command.trim());
147 else
148 {
149 command = command.trim();
150 if(!command.equalsIgnoreCase(""))
151 {
152 command = command.substring(0, command.length()-1);
153 issueCommand(conn, command);
154 }
155 }
156 }
157
158 }
159 catch(Exception e)
160 {
161 Logger.logInfo("Error: " + e);
162 e.printStackTrace(System.out);
163 e.printStackTrace(System.err);
164 }
165 }
166
167
168
172
173 protected void issueSpecialBlobCommand(Connection conn, String originalSql)
174 {
175 String sql = originalSql;
176
178 try
179 {
180 String valuesPart = sql.substring(sql.indexOf("VALUES") + 6).trim();
181 sql = sql.substring(0, sql.indexOf("VALUES") + 6);
182
185 String tableName = null;
186 int blobColumn = 0;
187 List columns = null;
188 List columnValues = null;
189
190 StringTokenizer st = new StringTokenizer(sql, " ");
191 int i = 0;
192 while (st.hasMoreTokens())
193 {
194 String part = st.nextToken();
195
197 if(i == 1)
198 blobColumn = new Integer (part).intValue();
199 if(i == 4)
200 tableName = part;
201 if(i == 5)
202 {
203 columns = parseColumns(part);
204 }
205
206 i++;
207 }
208
209 columnValues = parseValues(valuesPart);
210
211 String columnsString = "";
212 String valuesString = "";
213 Iterator columnsIterator = columns.iterator();
214 while(columnsIterator.hasNext())
215 {
216 columnsString += (columnsString.equals("")) ? (String )columnsIterator.next() : "," + columnsIterator.next();
217 valuesString += (valuesString.equals("")) ? "?" : ",?";
218 }
219
220 sql = "INSERT INTO " + tableName + "(" + columnsString + ") VALUES (" + valuesString + ")";
221
222
223 PreparedStatement ps = conn.prepareStatement(sql);
224
225 int index = 1;
226 int loopCount = 0;
227 Iterator columnValuesIterator = columnsIterator = columns.iterator();
228 while(columnsIterator.hasNext())
229 {
230 columnsIterator.next();
231 String value = (String )columnValues.get(loopCount);
232
233 if(index == 1 || value.indexOf("'") == -1)
234 {
235 ps.setInt(index, new Integer (value).intValue());
236 }
237 else if(index == blobColumn)
238 {
239 value = value.substring(1, value.length() - 1);
241
243 if(value.indexOf("assetBlob:") > -1)
244 {
245 String fileName = value.substring(10);
246 File file = new File(fileName);
247 FileInputStream fis = new FileInputStream(file);
248 ps.setBinaryStream(index, fis, (int)(file.length()));
249
250 }
251 else
252 {
253
258 if(value.indexOf("kommen") > -1)
259 {
260 System.out.println(value);
261 System.out.println("Välkommen");
262 }
263
264 ps.setString(index, value);
265 }
266 }
267 else if(value.indexOf("date:") > -1)
268 {
269 value = value.substring(6);
270 Date date = parseDate(value, "yyyy-MM-dd HH:mm:ss");
271
272 ps.setDate(index, new java.sql.Date (date.getTime()));
273 }
274 else
275 {
276 value = value.substring(1, value.length() - 1);
278 ps.setString(index, value);
280 }
281
282 index++;
283 loopCount++;
284 }
285
286 ps.executeUpdate();
287 }
288 catch(Exception ex)
289 {
290 Logger.logInfo("Command failed: " + ex.getMessage());
291 Logger.logInfo("SQL: " + originalSql);
292 ex.printStackTrace();
293 System.err.println("SQLException: " + ex.getMessage());
294 }
295 }
296
297 public void upgradeTo1_3(Connection conn) throws Exception
298 {
299 }
300
301 public void upgradeTo1_3_2(Connection conn) throws Exception
302 {
303 Logger.logInfo("Upgrading to 1.3.2");
304
305 try
306 {
307 FileInputStream fis = new FileInputStream("db2_upgrade1_3_to1_3_2.sql");
308 StringBuffer sb = new StringBuffer ();
309 int c;
310 while((c = fis.read()) != -1)
311 {
312 char character = (char)c;
313 sb.append(character);
314 }
315 String script = sb.toString();
316 Logger.logInfo("script:" + script);
317
318 StringTokenizer st = new StringTokenizer(script, ";");
319 while (st.hasMoreTokens())
320 {
321 String command = st.nextToken();
322 issueCommand(conn, command + ";");
324 }
325 }
326 catch(Exception e)
327 {
328 Logger.logInfo("Error: " + e);
329 }
330 }
331
332 public void upgradeTo2_0(Connection conn) throws Exception
333 {
334 Logger.logInfo("Upgrading to 2_0");
335
336 try
337 {
338 FileInputStream fis = new FileInputStream("db2_upgrade1_3_2_to2_0.sql");
339 StringBuffer sb = new StringBuffer ();
340 int c;
341 while((c = fis.read()) != -1)
342 {
343 char character = (char)c;
344 sb.append(character);
345 }
346 String script = sb.toString();
347 Logger.logInfo("script:" + script);
348
349 StringTokenizer st = new StringTokenizer(script, ";");
350 while (st.hasMoreTokens())
351 {
352 String command = st.nextToken();
353 issueCommand(conn, command + ";");
355 }
356 }
357 catch(Exception e)
358 {
359 Logger.logInfo("Error: " + e);
360 }
361 }
362
363 public void upgradeTo2_1(Connection conn) throws Exception
364 {
365 Logger.logInfo("Upgrading to 2_1");
366
367 try
368 {
369 FileInputStream fis = new FileInputStream("db2_upgrade2_0_to2_1.sql");
370 StringBuffer sb = new StringBuffer ();
371 int c;
372 while((c = fis.read()) != -1)
373 {
374 char character = (char)c;
375 sb.append(character);
376 }
377 String script = sb.toString();
378 Logger.logInfo("script:" + script);
379
380 StringTokenizer st = new StringTokenizer(script, ";");
381 while (st.hasMoreTokens())
382 {
383 String command = st.nextToken();
384 issueCommand(conn, command + ";");
386 }
387 }
388 catch(Exception e)
389 {
390 Logger.logInfo("Error: " + e);
391 }
392 }
393
394 public void upgradeTo2_3(Connection conn) throws Exception
395 {
396 Logger.logInfo("Upgrading to 2_3");
397
398 try
399 {
400 FileInputStream fis = new FileInputStream("db2_upgrade2_1_to2_3.sql");
401 StringBuffer sb = new StringBuffer ();
402 int c;
403 while((c = fis.read()) != -1)
404 {
405 char character = (char)c;
406 sb.append(character);
407 }
408 String script = sb.toString();
409 Logger.logInfo("script:" + script);
410
411 StringTokenizer st = new StringTokenizer(script, ";");
412 while (st.hasMoreTokens())
413 {
414 String command = st.nextToken();
415 issueCommand(conn, command + ";");
417 }
418 }
419 catch(Exception e)
420 {
421 Logger.logInfo("Error: " + e);
422 }
423 }
424
425 private void createUsers() throws Exception
426 {
427 Logger.logInfo("Setting up all users....");
428
429 String url = "jdbc:db2://" + databaseHostName + "";
430 Connection conn = getConnection(url, this.userName, this.password);
431
432 String grantSQL = "GRANT CREATETAB, LOAD ON DATABASE " + this.databaseName + " TO " + this.infoglueDatabaseUserName + " WITH GRANT OPTION;";
433 issueCommand(conn, grantSQL);
434
442 }
443
444
445 private void dropDatabase() throws Exception
446 {
447 Logger.logInfo("Deleting the database....");
448 issueCommand(getConnection(), "DROP DATABASE " + databaseName + ";");
449 }
450
451
452 private void dropUsers() throws Exception
453 {
454 Logger.logInfo("Dropping all users....");
455
456 String url = "jdbc:db2://" + databaseHostName + "";
457 Connection conn = getConnection(url, this.userName, this.password);
458
459 issueCommand(conn, "DELETE FROM mysql.columns_priv WHERE User = '" + this.databaseSuffix + "'");
460 issueCommand(conn, "DELETE FROM mysql.tables_priv WHERE User = '" + this.databaseSuffix + "'");
461 issueCommand(conn, "DELETE FROM mysql.db WHERE User = '" + this.infoglueDatabaseUserName + "'");
462 issueCommand(conn, "DELETE FROM mysql.user WHERE User = '" + this.infoglueDatabaseUserName + "'");
463 }
464
465
468
469 private void issueCommand(Connection conn, String sql)
470 {
471 if(sql == null || sql.trim().length() == 0 || sql.trim().equalsIgnoreCase(";"))
472 return;
473
474 try
475 {
476 System.out.println("issueCommand: " + sql);
478
479 PreparedStatement pstmt = conn.prepareStatement(sql);
480 pstmt.execute();
481 pstmt.close();
482
484 }
488 catch(SQLException ex)
489 {
490 Logger.logInfo("Command failed: " + ex.getMessage());
491 Logger.logInfo("SQL: " + sql);
492 System.err.println("SQLException: " + ex.getMessage());
493 }
494 }
495
496
497 public void createCastorFile() throws Exception
498 {
499 StringBuffer xmlFile = new StringBuffer ();
500
501 xmlFile.append("<!DOCTYPE database PUBLIC \"-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN\" \"http://castor.codehaus.org/jdo-conf.dtd\">\n");
502 xmlFile.append("<jdo-conf>\n");
503 xmlFile.append("<database name=\"INFOGLUE_CMS\" engine=\"db2\">\n");
504 xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
505 xmlFile.append("<param name=\"driver-class-name\" value=\"com.ibm.db2.jcc.DB2Driver\"/>\n");
506 xmlFile.append("<param name=\"username\" value=\"" + this.infoglueDatabaseUserName + "\"/>\n");
507 xmlFile.append("<param name=\"password\" value=\"" + this.infoglueDatabasePassword + "\"/>\n");
508 xmlFile.append("<param name=\"url\" value=\"jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>\n");
509 xmlFile.append("<param name=\"max-active\" value=\"20\"/>\n");
510 xmlFile.append("<param name=\"connection-properties\" value=\"SetBigStringTryClob=true\"/>\n");
511 xmlFile.append("</data-source>\n");
512 xmlFile.append("<mapping HREF=\"classes/oracle_mapping.xml\"/>\n");
513 xmlFile.append("</database>\n");
514 xmlFile.append("<transaction-demarcation mode=\"local\" />\n");
515 xmlFile.append("</jdo-conf>\n");
516
517 PrintWriter pw = new PrintWriter(new FileWriter("localConfigs/database.xml"));
518 pw.println(xmlFile.toString());
519 pw.close();
520 }
521
522 public void createCastorRootFile() throws Exception
523 {
524 StringBuffer xmlFile = new StringBuffer ();
525
526 xmlFile.append("<!DOCTYPE database PUBLIC \"-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN\" \"http://castor.codehaus.org/jdo-conf.dtd\">\n");
527 xmlFile.append("<jdo-conf>\n");
528 xmlFile.append("<database name=\"INFOGLUE_CMS\" engine=\"db2\">\n");
529 xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
530 xmlFile.append("<param name=\"driver-class-name\" value=\"com.ibm.db2.jcc.DB2Driver\"/>\n");
531 xmlFile.append("<param name=\"username\" value=\"" + this.userName + "\"/>\n");
532 xmlFile.append("<param name=\"password\" value=\"" + this.password + "\"/>\n");
533 xmlFile.append("<param name=\"url\" value=\"jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>\n");
534 xmlFile.append("<param name=\"max-active\" value=\"20\"/>\n");
535 xmlFile.append("<param name=\"connection-properties\" value=\"\"/>\n");
536 xmlFile.append("</data-source>\n");
537 xmlFile.append("<mapping HREF=\"classes/oracle_mapping.xml\"/>\n");
538 xmlFile.append("</database>\n");
539 xmlFile.append("<transaction-demarcation mode=\"local\" />\n");
540 xmlFile.append("</jdo-conf>\n");
541
542 PrintWriter pw = new PrintWriter(new FileWriter("localConfigs/database.xml"));
543 pw.println(xmlFile.toString());
544 pw.close();
545 }
546
547
593
594 public void createOSPropertiesFile() throws Exception
595 {
596 StringBuffer xmlFile = new StringBuffer ();
597
598 xmlFile.append("<propertysets>");
599 xmlFile.append("<propertyset name=\"aggregate\" class=\"com.opensymphony.module.propertyset.aggregate.AggregatePropertySet\"/>");
600 xmlFile.append("<propertyset name=\"cached\" class=\"com.opensymphony.module.propertyset.cached.CachingPropertySet\"/>");
601 xmlFile.append("<propertyset name=\"jdbc\" class=\"org.infoglue.cms.util.workflow.InfoGlueJDBCPropertySet\">");
602 xmlFile.append("<arg name=\"username\" value=\"" + this.infoglueDatabaseUserName + "\"/>");
603 xmlFile.append("<arg name=\"password\" value=\"" + this.infoglueDatabasePassword + "\"/>");
604 xmlFile.append("<arg name=\"driverClassName\" value=\"com.ibm.db2.jcc.DB2Driver\"/>");
605 xmlFile.append("<arg name=\"url\" value=\"jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>");
606
607 xmlFile.append("<arg name=\"table.name\" value=\"OS_PROPERTYENTRY\"/>");
608
618 xmlFile.append("<arg name=\"col.globalKey\" value=\"entity_name\"/>");
619 xmlFile.append("<arg name=\"col.entityId\" value=\"entity_id\"/>");
620 xmlFile.append("<arg name=\"col.itemKey\" value=\"entity_key\"/>");
621 xmlFile.append("<arg name=\"col.itemType\" value=\"key_type\"/>");
622 xmlFile.append("<arg name=\"col.booleanVal\" value=\"boolean_val\"/>");
623 xmlFile.append("<arg name=\"col.string\" value=\"string_val\"/>");
624 xmlFile.append("<arg name=\"col.date\" value=\"date_val\"/>");
625 xmlFile.append("<arg name=\"col.data\" value=\"data_val\"/>");
626 xmlFile.append("<arg name=\"col.float\" value=\"double_val\"/>");
627 xmlFile.append("<arg name=\"col.number\" value=\"int_val\"/>");
628
629 xmlFile.append("</propertyset>");
630 xmlFile.append("<propertyset name=\"ejb\" class=\"com.opensymphony.module.propertyset.ejb.EJBPropertySet\"/>");
631 xmlFile.append("<propertyset name=\"javabeans\" class=\"com.opensymphony.module.propertyset.javabeans.BeanIntrospectorPropertySet\"/>");
632 xmlFile.append("<propertyset name=\"map\" class=\"com.opensymphony.module.propertyset.map.MapPropertySet\"/>");
633 xmlFile.append("<propertyset name=\"memory\" class=\"com.opensymphony.module.propertyset.memory.MemoryPropertySet\"/>");
634 xmlFile.append("<propertyset name=\"serializable\" class=\"com.opensymphony.module.propertyset.memory.SerializablePropertySet\"/>");
635 xmlFile.append("<propertyset name=\"ofbiz\" class=\"com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet\"/>");
636 xmlFile.append("<propertyset name=\"hibernate\" class=\"org.infoglue.cms.util.workflow.hibernate.InfoGlueHibernatePropertySet\"/>");
637 xmlFile.append("<propertyset name=\"xml\" class=\"com.opensymphony.module.propertyset.xml.XMLPropertySet\"/>");
638 xmlFile.append("</propertysets>");
639
640 PrintWriter pw = new PrintWriter(new FileWriter("localConfigs/propertyset.xml"));
641 pw.println(xmlFile.toString());
642 pw.close();
643 }
644
645 public void testSetupDummyDatabase() throws Exception
646 {
647
649 PreparedStatement pstmt = getConnection().prepareStatement("CREATE TABLE DUMMYTABLE(ID INTEGER NOT NULL)");
650 pstmt.execute();
651 pstmt.close();
652
653 pstmt = getConnection().prepareStatement("DROP TABLE DUMMYTABLE");
654 pstmt.execute();
655 pstmt.close();
656
657 }
659
660
661 public void testConnectDatabase() throws Exception
662 {
663 System.out.println(this.infoglueDatabaseUserName + ":" + this.infoglueDatabasePassword);
664 getConnection(getUrl(this.databaseHostName, this.databasePortNumber, this.databaseName + this.databaseSuffix), this.infoglueDatabaseUserName, this.infoglueDatabasePassword);
665 }
667
668
669 public String getDriver()
670 {
671 return this.driver;
672 }
673
674 public String getDatabaseVendor() throws Exception
675 {
676 return "DB2";
677 }
678
679 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.