1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
12   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
13   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
14   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
15   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
16   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
17   * SOFTWARE.
18   */
19  
20  package com.liferay.portal.tools.sql;
21  
22  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
23  import com.liferay.portal.kernel.util.FileUtil;
24  import com.liferay.portal.kernel.util.StringPool;
25  import com.liferay.portal.kernel.util.StringUtil;
26  
27  import java.io.BufferedReader;
28  import java.io.File;
29  import java.io.IOException;
30  import java.io.StringReader;
31  
32  import java.sql.CallableStatement;
33  import java.sql.Connection;
34  import java.sql.SQLException;
35  
36  import java.util.HashSet;
37  import java.util.Set;
38  
39  import javax.naming.NamingException;
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   */
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 = replaceTemplate(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 boolean isSupportsAlterColumnType() {
69          return _SUPPORTS_ALTER_COLUMN_TYPE;
70      }
71  
72      public void runSQL(String template)
73          throws IOException, NamingException, SQLException {
74  
75          if (template.startsWith(ALTER_COLUMN_NAME) ||
76              template.startsWith(ALTER_COLUMN_TYPE)) {
77  
78              String sql = buildSQL(template);
79  
80              String[] alterSqls = StringUtil.split(sql, StringPool.SEMICOLON);
81  
82              for (String alterSql : alterSqls) {
83                  if (!alterSql.startsWith("-- ")){
84                      runSQL(alterSql);
85                  }
86              }
87          }
88          else {
89              super.runSQL(template);
90          }
91      }
92  
93      public void runSQL(String[] templates)
94          throws IOException, NamingException, SQLException {
95  
96          super.runSQL(templates);
97  
98          _reorgTables(templates);
99      }
100 
101     protected DB2Util() {
102         super(TYPE_DB2);
103     }
104 
105     protected void buildCreateFile(String databaseName, boolean minimal)
106         throws IOException {
107 
108         String minimalSuffix = getMinimalSuffix(minimal);
109 
110         File file = new File(
111             "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
112                 "-db2.sql");
113 
114         StringBuilder sb = new StringBuilder();
115 
116         sb.append("drop database " + databaseName + ";\n");
117         sb.append("create database " + databaseName + ";\n");
118         sb.append("connect to " + databaseName + ";\n");
119         sb.append(
120             FileUtil.read("../sql/portal" + minimalSuffix + "/portal" +
121                 minimalSuffix + "-db2.sql"));
122         sb.append("\n\n");
123         sb.append(FileUtil.read("../sql/indexes/indexes-db2.sql"));
124         sb.append("\n\n");
125         sb.append(FileUtil.read("../sql/sequences/sequences-db2.sql"));
126 
127         FileUtil.write(file, sb.toString());
128     }
129 
130     protected String getServerName() {
131         return "db2";
132     }
133 
134     protected String[] getTemplate() {
135         return _DB2;
136     }
137 
138     protected String reword(String data) throws IOException {
139         BufferedReader br = new BufferedReader(new StringReader(data));
140 
141         StringBuilder sb = new StringBuilder();
142 
143         String line = null;
144 
145         while ((line = br.readLine()) != null) {
146             if (line.startsWith(ALTER_COLUMN_NAME)) {
147                 String[] template = buildColumnNameTokens(line);
148 
149                 line = StringUtil.replace(
150                     "alter table @table@ add column @new-column@ @type@;\n",
151                     REWORD_TEMPLATE, template);
152 
153                 line = line + StringUtil.replace(
154                     "update @table@ set @new-column@ = @old-column@;\n",
155                     REWORD_TEMPLATE, template);
156 
157                 line = line + StringUtil.replace(
158                     "alter table @table@ drop column @old-column@",
159                     REWORD_TEMPLATE, template);
160             }
161             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
162                 line = "-- " + line;
163             }
164 
165             sb.append(line);
166             sb.append("\n");
167         }
168 
169         br.close();
170 
171         return sb.toString();
172     }
173 
174     private void _reorgTables(String[] templates) throws SQLException {
175         Set<String> tableNames = new HashSet<String>();
176 
177         for (String template : templates) {
178             if (template.startsWith("alter table")) {
179                 tableNames.add(template.split(" ")[2]);
180             }
181         }
182 
183         if (tableNames.size() == 0) {
184             return;
185         }
186 
187         Connection con = null;
188         CallableStatement callStmt = null;
189 
190         try {
191             con = DataAccess.getConnection();
192 
193             for (String tableName : tableNames) {
194                 String sql = "call sysproc.admin_cmd(?)";
195 
196                 callStmt = con.prepareCall(sql);
197 
198                 String param = "reorg table " + tableName;
199 
200                 callStmt.setString(1, param);
201 
202                 callStmt.execute();
203             }
204         }
205         finally {
206             DataAccess.cleanUp(con, callStmt);
207         }
208     }
209 
210     private static String[] _DB2 = {
211         "--", "1", "0",
212         "'1970-01-01-00.00.00.000000'", "current timestamp",
213         " blob(2000)", " smallint", " timestamp",
214         " double", " integer", " bigint",
215         " varchar(500)", " clob", " varchar",
216         " generated always as identity", "commit"
217     };
218 
219     private static boolean _SUPPORTS_ALTER_COLUMN_TYPE;
220 
221     private static DB2Util _instance = new DB2Util();
222 
223 }