1   /**
2    * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.tools.sql;
24  
25  import com.liferay.portal.kernel.util.StringMaker;
26  import com.liferay.portal.kernel.util.StringUtil;
27  import com.liferay.portal.spring.hibernate.HibernateUtil;
28  import com.liferay.util.FileUtil;
29  import com.liferay.util.dao.DataAccess;
30  
31  import java.io.BufferedReader;
32  import java.io.File;
33  import java.io.IOException;
34  import java.io.StringReader;
35  
36  import java.sql.CallableStatement;
37  import java.sql.Connection;
38  import java.sql.SQLException;
39  
40  import java.util.HashSet;
41  import java.util.Set;
42  
43  /**
44   * <a href="DB2Util.java.html"><b><i>View Source</i></b></a>
45   *
46   * @author Alexander Chow
47   * @author Bruno Farache
48   *
49   */
50  public class DB2Util extends DBUtil {
51  
52      public static DBUtil getInstance() {
53          return _instance;
54      }
55  
56      public String buildSQL(String template) throws IOException {
57          template = convertTimestamp(template);
58          template = StringUtil.replace(template, TEMPLATE, getTemplate());
59  
60          template = reword(template);
61          template = removeLongInserts(template);
62          template = removeNull(template);
63          template = StringUtil.replace(template, "\\'", "''");
64  
65          return template;
66      }
67  
68      public void runSQL(String template) throws IOException, SQLException {
69          if (template.startsWith(ALTER_COLUMN_NAME)) {
70              String sql = buildSQL(template);
71  
72              String[] renameSqls = sql.split(";");
73  
74              runSQL(renameSqls);
75          }
76          else {
77              super.runSQL(template);
78          }
79      }
80  
81      public void runSQL(String[] templates) throws IOException, SQLException {
82          super.runSQL(templates);
83  
84          _reorgTables(templates);
85      }
86  
87      protected DB2Util() {
88      }
89  
90      protected void buildCreateFile(String databaseName, boolean minimal)
91          throws IOException {
92  
93          String minimalSuffix = getMinimalSuffix(minimal);
94  
95          File file = new File(
96              "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
97                  "-db2.sql");
98  
99          StringMaker sm = new StringMaker();
100 
101         sm.append("drop database " + databaseName + ";\n");
102         sm.append("create database " + databaseName + ";\n");
103         sm.append("connect to " + databaseName + ";\n");
104         sm.append(
105             FileUtil.read("../sql/portal" + minimalSuffix + "/portal" +
106                 minimalSuffix + "-db2.sql"));
107         sm.append("\n\n");
108         sm.append(FileUtil.read("../sql/indexes/indexes-db2.sql"));
109         sm.append("\n\n");
110         sm.append(FileUtil.read("../sql/sequences/sequences-db2.sql"));
111 
112         FileUtil.write(file, sm.toString());
113     }
114 
115     protected String getServerName() {
116         return "db2";
117     }
118 
119     protected String[] getTemplate() {
120         return _DB2;
121     }
122 
123     protected String reword(String data) throws IOException {
124         BufferedReader br = new BufferedReader(new StringReader(data));
125 
126         StringMaker sm = new StringMaker();
127 
128         String line = null;
129 
130         while ((line = br.readLine()) != null) {
131             if (line.startsWith(ALTER_COLUMN_TYPE)) {
132                 line = "-- " + line;
133             }
134             else if (line.startsWith(ALTER_COLUMN_NAME)) {
135                 String[] template = buildColumnNameTokens(line);
136 
137                 line = StringUtil.replace(
138                     "alter table @table@ add column @new-column@ @type@;\n",
139                     REWORD_TEMPLATE, template);
140 
141                 line = line + StringUtil.replace(
142                     "update @table@ set @new-column@ = @old-column@;\n",
143                     REWORD_TEMPLATE, template);
144 
145                 line = line + StringUtil.replace(
146                     "alter table @table@ drop column @old-column@",
147                     REWORD_TEMPLATE, template);
148             }
149 
150             sm.append(line);
151             sm.append("\n");
152         }
153 
154         br.close();
155 
156         return sm.toString();
157     }
158 
159     private void _reorgTables(String[] templates) throws SQLException {
160         Set<String> tableNames = new HashSet<String>();
161 
162         for (String template : templates) {
163             if (template.startsWith("alter table")) {
164                 tableNames.add(template.split(" ")[2]);
165             }
166         }
167 
168         if (tableNames.size() == 0) {
169             return;
170         }
171 
172         Connection con = null;
173         CallableStatement callStmt = null;
174 
175         try {
176             con = HibernateUtil.getConnection();
177 
178             for (String tableName : tableNames) {
179                 String sql = "call sysproc.admin_cmd(?)";
180 
181                 callStmt = con.prepareCall(sql);
182 
183                 String param = "reorg table " + tableName;
184 
185                 callStmt.setString(1, param);
186 
187                 callStmt.execute();
188             }
189         }
190         finally {
191             DataAccess.cleanUp(con, callStmt);
192         }
193     }
194 
195     private static String[] _DB2 = {
196         "--", "1", "0",
197         "'1970-01-01-00.00.00.000000'", "current timestamp",
198         " smallint", " timestamp", " double",
199         " integer", " bigint",
200         " varchar(500)", " clob", " varchar",
201         " generated always as identity", "commit"
202     };
203 
204     private static DB2Util _instance = new DB2Util();
205 
206 }