1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    *
5    *
6    *
7    * The contents of this file are subject to the terms of the Liferay Enterprise
8    * Subscription License ("License"). You may not use this file except in
9    * compliance with the License. You can obtain a copy of the License by
10   * contacting Liferay, Inc. See the License for the specific language governing
11   * permissions and limitations under the License, including but not limited to
12   * distribution rights 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.dao.jdbc.DataAccess;
26  import com.liferay.portal.kernel.util.FileUtil;
27  import com.liferay.portal.kernel.util.StringPool;
28  import com.liferay.portal.kernel.util.StringUtil;
29  
30  import java.io.BufferedReader;
31  import java.io.IOException;
32  import java.io.StringReader;
33  
34  import java.sql.CallableStatement;
35  import java.sql.Connection;
36  import java.sql.SQLException;
37  
38  import java.util.HashSet;
39  import java.util.Set;
40  
41  /**
42   * <a href="DB2Util.java.html"><b><i>View Source</i></b></a>
43   *
44   * @author Alexander Chow
45   * @author Bruno Farache
46   * @author Sandeep Soni
47   * @author Ganesh Ram
48   */
49  public class DB2Util extends DBUtil {
50  
51      public static DBUtil getInstance() {
52          return _instance;
53      }
54  
55      public String buildSQL(String template) throws IOException {
56          template = convertTimestamp(template);
57          template = replaceTemplate(template, getTemplate());
58  
59          template = reword(template);
60          template = removeLongInserts(template);
61          template = removeNull(template);
62          template = StringUtil.replace(template, "\\'", "''");
63  
64          return template;
65      }
66  
67      public boolean isSupportsAlterColumnType() {
68          return _SUPPORTS_ALTER_COLUMN_TYPE;
69      }
70  
71      public void runSQL(String template) throws IOException, SQLException {
72          if (template.startsWith(ALTER_COLUMN_NAME) ||
73              template.startsWith(ALTER_COLUMN_TYPE)) {
74  
75              String sql = buildSQL(template);
76  
77              String[] alterSqls = StringUtil.split(sql, StringPool.SEMICOLON);
78  
79              for (String alterSql : alterSqls) {
80                  if (!alterSql.startsWith("-- ")) {
81                      runSQL(alterSql);
82                  }
83              }
84          }
85          else {
86              super.runSQL(template);
87          }
88      }
89  
90      public void runSQL(String[] templates) throws IOException, SQLException {
91          super.runSQL(templates);
92  
93          _reorgTables(templates);
94      }
95  
96      protected DB2Util() {
97          super(TYPE_DB2);
98      }
99  
100     protected String buildCreateFileContent(String databaseName, int population)
101         throws IOException {
102 
103         String suffix = getSuffix(population);
104 
105         StringBuilder sb = new StringBuilder();
106 
107         sb.append("drop database " + databaseName + ";\n");
108         sb.append("create database " + databaseName + ";\n");
109         sb.append("connect to " + databaseName + ";\n");
110         sb.append(
111             FileUtil.read("../sql/portal" + suffix + "/portal" + suffix +
112                 "-db2.sql"));
113         sb.append("\n\n");
114         sb.append(FileUtil.read("../sql/indexes/indexes-db2.sql"));
115         sb.append("\n\n");
116         sb.append(FileUtil.read("../sql/sequences/sequences-db2.sql"));
117 
118         return sb.toString();
119     }
120 
121     protected String getServerName() {
122         return "db2";
123     }
124 
125     protected String[] getTemplate() {
126         return _DB2;
127     }
128 
129     protected String reword(String data) throws IOException {
130         BufferedReader br = new BufferedReader(new StringReader(data));
131 
132         StringBuilder sb = new StringBuilder();
133 
134         String line = null;
135 
136         while ((line = br.readLine()) != null) {
137             if (line.startsWith(ALTER_COLUMN_NAME)) {
138                 String[] template = buildColumnNameTokens(line);
139 
140                 line = StringUtil.replace(
141                     "alter table @table@ add column @new-column@ @type@;\n",
142                     REWORD_TEMPLATE, template);
143 
144                 line = line + StringUtil.replace(
145                     "update @table@ set @new-column@ = @old-column@;\n",
146                     REWORD_TEMPLATE, template);
147 
148                 line = line + StringUtil.replace(
149                     "alter table @table@ drop column @old-column@",
150                     REWORD_TEMPLATE, template);
151             }
152             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
153                 line = "-- " + line;
154             }
155 
156             sb.append(line);
157             sb.append("\n");
158         }
159 
160         br.close();
161 
162         return sb.toString();
163     }
164 
165     private void _reorgTables(String[] templates) throws SQLException {
166         Set<String> tableNames = new HashSet<String>();
167 
168         for (String template : templates) {
169             if (template.startsWith("alter table")) {
170                 tableNames.add(template.split(" ")[2]);
171             }
172         }
173 
174         if (tableNames.size() == 0) {
175             return;
176         }
177 
178         Connection con = null;
179         CallableStatement callStmt = null;
180 
181         try {
182             con = DataAccess.getConnection();
183 
184             for (String tableName : tableNames) {
185                 String sql = "call sysproc.admin_cmd(?)";
186 
187                 callStmt = con.prepareCall(sql);
188 
189                 String param = "reorg table " + tableName;
190 
191                 callStmt.setString(1, param);
192 
193                 callStmt.execute();
194             }
195         }
196         finally {
197             DataAccess.cleanUp(con, callStmt);
198         }
199     }
200 
201     private static String[] _DB2 = {
202         "--", "1", "0",
203         "'1970-01-01-00.00.00.000000'", "current timestamp",
204         " blob(2000)", " smallint", " timestamp",
205         " double", " integer", " bigint",
206         " varchar(500)", " clob", " varchar",
207         " generated always as identity", "commit"
208     };
209 
210     private static boolean _SUPPORTS_ALTER_COLUMN_TYPE;
211 
212     private static DB2Util _instance = new DB2Util();
213 
214 }