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.dao.jdbc.DataAccess;
26  import com.liferay.portal.kernel.util.FileUtil;
27  import com.liferay.portal.kernel.util.StringUtil;
28  
29  import java.io.BufferedReader;
30  import java.io.File;
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  import javax.naming.NamingException;
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   * @author Sandeep Soni
49   * @author Ganesh Ram
50   *
51   */
52  public class DB2Util extends DBUtil {
53  
54      public static DBUtil getInstance() {
55          return _instance;
56      }
57  
58      public String buildSQL(String template) throws IOException {
59          template = convertTimestamp(template);
60          template = replaceTemplate(template, getTemplate());
61  
62          template = reword(template);
63          template = removeLongInserts(template);
64          template = removeNull(template);
65          template = StringUtil.replace(template, "\\'", "''");
66  
67          return template;
68      }
69  
70      public void runSQL(String template)
71          throws IOException, NamingException, SQLException {
72  
73          if (template.startsWith(ALTER_COLUMN_NAME)) {
74              String sql = buildSQL(template);
75  
76              String[] renameSqls = sql.split(";");
77  
78              runSQL(renameSqls);
79          }
80          else {
81              super.runSQL(template);
82          }
83      }
84  
85      public void runSQL(String[] templates)
86          throws IOException, NamingException, SQLException {
87  
88          super.runSQL(templates);
89  
90          _reorgTables(templates);
91      }
92  
93      protected DB2Util() {
94      }
95  
96      protected void buildCreateFile(String databaseName, boolean minimal)
97          throws IOException {
98  
99          String minimalSuffix = getMinimalSuffix(minimal);
100 
101         File file = new File(
102             "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
103                 "-db2.sql");
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" + minimalSuffix + "/portal" +
112                 minimalSuffix + "-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         FileUtil.write(file, 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_TYPE)) {
138                 line = "-- " + line;
139             }
140             else if (line.startsWith(ALTER_COLUMN_NAME)) {
141                 String[] template = buildColumnNameTokens(line);
142 
143                 line = StringUtil.replace(
144                     "alter table @table@ add column @new-column@ @type@;\n",
145                     REWORD_TEMPLATE, template);
146 
147                 line = line + StringUtil.replace(
148                     "update @table@ set @new-column@ = @old-column@;\n",
149                     REWORD_TEMPLATE, template);
150 
151                 line = line + StringUtil.replace(
152                     "alter table @table@ drop column @old-column@",
153                     REWORD_TEMPLATE, template);
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)
166         throws NamingException, SQLException {
167 
168         Set<String> tableNames = new HashSet<String>();
169 
170         for (String template : templates) {
171             if (template.startsWith("alter table")) {
172                 tableNames.add(template.split(" ")[2]);
173             }
174         }
175 
176         if (tableNames.size() == 0) {
177             return;
178         }
179 
180         Connection con = null;
181         CallableStatement callStmt = null;
182 
183         try {
184             con = DataAccess.getConnection();
185 
186             for (String tableName : tableNames) {
187                 String sql = "call sysproc.admin_cmd(?)";
188 
189                 callStmt = con.prepareCall(sql);
190 
191                 String param = "reorg table " + tableName;
192 
193                 callStmt.setString(1, param);
194 
195                 callStmt.execute();
196             }
197         }
198         finally {
199             DataAccess.cleanUp(con, callStmt);
200         }
201     }
202 
203     private static String[] _DB2 = {
204         "--", "1", "0",
205         "'1970-01-01-00.00.00.000000'", "current timestamp",
206         " blob(2000)", " smallint", " timestamp",
207         " double", " integer", " bigint",
208         " varchar(500)", " clob", " varchar",
209         " generated always as identity", "commit"
210     };
211 
212     private static DB2Util _instance = new DB2Util();
213 
214 }