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.StringUtil;
25  
26  import java.io.BufferedReader;
27  import java.io.File;
28  import java.io.IOException;
29  import java.io.StringReader;
30  
31  import java.sql.CallableStatement;
32  import java.sql.Connection;
33  import java.sql.SQLException;
34  
35  import java.util.HashSet;
36  import java.util.Set;
37  
38  import javax.naming.NamingException;
39  
40  /**
41   * <a href="DB2Util.java.html"><b><i>View Source</i></b></a>
42   *
43   * @author Alexander Chow
44   * @author Bruno Farache
45   * @author Sandeep Soni
46   * @author Ganesh Ram
47   *
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 void runSQL(String template)
68          throws IOException, NamingException, SQLException {
69  
70          if (template.startsWith(ALTER_COLUMN_NAME)) {
71              String sql = buildSQL(template);
72  
73              String[] renameSqls = sql.split(";");
74  
75              runSQL(renameSqls);
76          }
77          else {
78              super.runSQL(template);
79          }
80      }
81  
82      public void runSQL(String[] templates)
83          throws IOException, NamingException, SQLException {
84  
85          super.runSQL(templates);
86  
87          _reorgTables(templates);
88      }
89  
90      protected DB2Util() {
91          super(DB_TYPE_DB2);
92      }
93  
94      protected void buildCreateFile(String databaseName, boolean minimal)
95          throws IOException {
96  
97          String minimalSuffix = getMinimalSuffix(minimal);
98  
99          File file = new File(
100             "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
101                 "-db2.sql");
102 
103         StringBuilder sb = new StringBuilder();
104 
105         sb.append("drop database " + databaseName + ";\n");
106         sb.append("create database " + databaseName + ";\n");
107         sb.append("connect to " + databaseName + ";\n");
108         sb.append(
109             FileUtil.read("../sql/portal" + minimalSuffix + "/portal" +
110                 minimalSuffix + "-db2.sql"));
111         sb.append("\n\n");
112         sb.append(FileUtil.read("../sql/indexes/indexes-db2.sql"));
113         sb.append("\n\n");
114         sb.append(FileUtil.read("../sql/sequences/sequences-db2.sql"));
115 
116         FileUtil.write(file, sb.toString());
117     }
118 
119     protected String getServerName() {
120         return "db2";
121     }
122 
123     protected String[] getTemplate() {
124         return _DB2;
125     }
126 
127     protected String reword(String data) throws IOException {
128         BufferedReader br = new BufferedReader(new StringReader(data));
129 
130         StringBuilder sb = new StringBuilder();
131 
132         String line = null;
133 
134         while ((line = br.readLine()) != null) {
135             if (line.startsWith(ALTER_COLUMN_TYPE)) {
136                 line = "-- " + line;
137             }
138             else if (line.startsWith(ALTER_COLUMN_NAME)) {
139                 String[] template = buildColumnNameTokens(line);
140 
141                 line = StringUtil.replace(
142                     "alter table @table@ add column @new-column@ @type@;\n",
143                     REWORD_TEMPLATE, template);
144 
145                 line = line + StringUtil.replace(
146                     "update @table@ set @new-column@ = @old-column@;\n",
147                     REWORD_TEMPLATE, template);
148 
149                 line = line + StringUtil.replace(
150                     "alter table @table@ drop column @old-column@",
151                     REWORD_TEMPLATE, template);
152             }
153 
154             sb.append(line);
155             sb.append("\n");
156         }
157 
158         br.close();
159 
160         return sb.toString();
161     }
162 
163     private void _reorgTables(String[] templates) throws SQLException {
164         Set<String> tableNames = new HashSet<String>();
165 
166         for (String template : templates) {
167             if (template.startsWith("alter table")) {
168                 tableNames.add(template.split(" ")[2]);
169             }
170         }
171 
172         if (tableNames.size() == 0) {
173             return;
174         }
175 
176         Connection con = null;
177         CallableStatement callStmt = null;
178 
179         try {
180             con = DataAccess.getConnection();
181 
182             for (String tableName : tableNames) {
183                 String sql = "call sysproc.admin_cmd(?)";
184 
185                 callStmt = con.prepareCall(sql);
186 
187                 String param = "reorg table " + tableName;
188 
189                 callStmt.setString(1, param);
190 
191                 callStmt.execute();
192             }
193         }
194         finally {
195             DataAccess.cleanUp(con, callStmt);
196         }
197     }
198 
199     private static String[] _DB2 = {
200         "--", "1", "0",
201         "'1970-01-01-00.00.00.000000'", "current timestamp",
202         " blob(2000)", " smallint", " timestamp",
203         " double", " integer", " bigint",
204         " varchar(500)", " clob", " varchar",
205         " generated always as identity", "commit"
206     };
207 
208     private static DB2Util _instance = new DB2Util();
209 
210 }