001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.dao.db;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
019    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
020    import com.liferay.portal.kernel.util.CharPool;
021    import com.liferay.portal.kernel.util.StringBundler;
022    import com.liferay.portal.kernel.util.StringUtil;
023    
024    import java.io.IOException;
025    
026    import java.sql.CallableStatement;
027    import java.sql.Connection;
028    import java.sql.PreparedStatement;
029    import java.sql.ResultSet;
030    import java.sql.SQLException;
031    
032    import java.util.HashSet;
033    import java.util.Set;
034    
035    /**
036     * @author Alexander Chow
037     * @author Bruno Farache
038     * @author Sandeep Soni
039     * @author Ganesh Ram
040     */
041    public class DB2DB extends BaseDB {
042    
043            public DB2DB(int majorVersion, int minorVersion) {
044                    super(TYPE_DB2, majorVersion, minorVersion);
045            }
046    
047            @Override
048            public String buildSQL(String template) throws IOException {
049                    template = convertTimestamp(template);
050                    template = replaceTemplate(template, getTemplate());
051    
052                    template = reword(template);
053                    template = removeLongInserts(template);
054                    template = removeNull(template);
055                    template = StringUtil.replace(template, "\\'", "''");
056                    template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
057    
058                    return template;
059            }
060    
061            @Override
062            public boolean isSupportsAlterColumnType() {
063                    return _SUPPORTS_ALTER_COLUMN_TYPE;
064            }
065    
066            @Override
067            public boolean isSupportsInlineDistinct() {
068                    return _SUPPORTS_INLINE_DISTINCT;
069            }
070    
071            @Override
072            public boolean isSupportsScrollableResults() {
073                    return _SUPPORTS_SCROLLABLE_RESULTS;
074            }
075    
076            @Override
077            public void runSQL(String template) throws IOException, SQLException {
078                    if (template.startsWith(ALTER_COLUMN_NAME)) {
079                            String sql = buildSQL(template);
080    
081                            String[] alterSqls = StringUtil.split(sql, CharPool.SEMICOLON);
082    
083                            for (String alterSql : alterSqls) {
084                                    alterSql = StringUtil.trim(alterSql);
085    
086                                    runSQL(alterSql);
087                            }
088                    }
089                    else {
090                            super.runSQL(template);
091                    }
092            }
093    
094            @Override
095            public void runSQL(String[] templates) throws IOException, SQLException {
096                    super.runSQL(templates);
097    
098                    reorgTables(templates);
099            }
100    
101            @Override
102            protected String buildCreateFileContent(
103                            String sqlDir, String databaseName, int population)
104                    throws IOException {
105    
106                    String suffix = getSuffix(population);
107    
108                    StringBundler sb = new StringBundler(15);
109    
110                    sb.append("drop database ");
111                    sb.append(databaseName);
112                    sb.append(";\n");
113                    sb.append("create database ");
114                    sb.append(databaseName);
115                    sb.append(" pagesize 32768 temporary tablespace managed by automatic ");
116                    sb.append("storage;\n");
117    
118                    if (population != BARE) {
119                            sb.append("connect to ");
120                            sb.append(databaseName);
121                            sb.append(";\n");
122                            sb.append(getCreateTablesContent(sqlDir, suffix));
123                            sb.append("\n\n");
124                            sb.append(readFile(sqlDir + "/indexes/indexes-db2.sql"));
125                            sb.append("\n\n");
126                            sb.append(readFile(sqlDir + "/sequences/sequences-db2.sql"));
127                    }
128    
129                    return sb.toString();
130            }
131    
132            @Override
133            protected String getServerName() {
134                    return "db2";
135            }
136    
137            @Override
138            protected String[] getTemplate() {
139                    return _DB2;
140            }
141    
142            protected boolean isRequiresReorgTable(Connection con, String tableName)
143                    throws SQLException {
144    
145                    boolean reorgTableRequired = false;
146    
147                    PreparedStatement ps = null;
148                    ResultSet rs = null;
149    
150                    try {
151                            StringBundler sb = new StringBundler(4);
152    
153                            sb.append("select num_reorg_rec_alters from table(");
154                            sb.append("sysproc.admin_get_tab_info(current_schema, '");
155                            sb.append(StringUtil.toUpperCase(tableName));
156                            sb.append("')) where reorg_pending = 'Y'");
157    
158                            ps = con.prepareStatement(sb.toString());
159    
160                            rs = ps.executeQuery();
161    
162                            if (rs.next()) {
163                                    int numReorgRecAlters = rs.getInt(1);
164    
165                                    if (numReorgRecAlters >= 1) {
166                                            reorgTableRequired = true;
167                                    }
168                            }
169                    }
170                    finally {
171                            DataAccess.cleanUp(null, ps, rs);
172                    }
173    
174                    return reorgTableRequired;
175            }
176    
177            protected void reorgTable(Connection con, String tableName)
178                    throws SQLException {
179    
180                    if (!isRequiresReorgTable(con, tableName)) {
181                            return;
182                    }
183    
184                    CallableStatement callableStatement = null;
185    
186                    try {
187                            callableStatement = con.prepareCall("call sysproc.admin_cmd(?)");
188    
189                            callableStatement.setString(1, "reorg table " + tableName);
190    
191                            callableStatement.execute();
192                    }
193                    finally {
194                            DataAccess.cleanUp(callableStatement);
195                    }
196            }
197    
198            protected void reorgTables(String[] templates) throws SQLException {
199                    Set<String> tableNames = new HashSet<>();
200    
201                    for (String template : templates) {
202                            if (template.startsWith("alter table")) {
203                                    tableNames.add(template.split(" ")[2]);
204                            }
205                    }
206    
207                    if (tableNames.isEmpty()) {
208                            return;
209                    }
210    
211                    Connection con = null;
212    
213                    try {
214                            con = DataAccess.getConnection();
215    
216                            for (String tableName : tableNames) {
217                                    reorgTable(con, tableName);
218                            }
219                    }
220                    finally {
221                            DataAccess.cleanUp(con);
222                    }
223            }
224    
225            @Override
226            protected String reword(String data) throws IOException {
227                    try (UnsyncBufferedReader unsyncBufferedReader =
228                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
229    
230                            StringBundler sb = new StringBundler();
231    
232                            String line = null;
233    
234                            while ((line = unsyncBufferedReader.readLine()) != null) {
235                                    if (line.startsWith(ALTER_COLUMN_NAME)) {
236                                            String[] template = buildColumnNameTokens(line);
237    
238                                            line = StringUtil.replace(
239                                                    "alter table @table@ add column @new-column@ @type@;\n",
240                                                    REWORD_TEMPLATE, template);
241                                            line += StringUtil.replace(
242                                                    "update @table@ set @new-column@ = @old-column@;\n",
243                                                    REWORD_TEMPLATE, template);
244                                            line += StringUtil.replace(
245                                                    "alter table @table@ drop column @old-column@",
246                                                    REWORD_TEMPLATE, template);
247                                    }
248                                    else if (line.startsWith(ALTER_TABLE_NAME)) {
249                                            String[] template = buildTableNameTokens(line);
250    
251                                            line = StringUtil.replace(
252                                                    "alter table @old-table@ to @new-table@;",
253                                                    RENAME_TABLE_TEMPLATE, template);
254                                    }
255                                    else if (line.contains(DROP_INDEX)) {
256                                            String[] tokens = StringUtil.split(line, ' ');
257    
258                                            line = StringUtil.replace(
259                                                    "drop index @index@;", "@index@", tokens[2]);
260                                    }
261    
262                                    sb.append(line);
263                                    sb.append("\n");
264                            }
265    
266                            return sb.toString();
267                    }
268            }
269    
270            private static final String[] _DB2 = {
271                    "--", "1", "0", "'1970-01-01-00.00.00.000000'", "current timestamp",
272                    " blob", " blob", " smallint", " timestamp", " double", " integer",
273                    " bigint", " varchar(4000)", " clob", " varchar",
274                    " generated always as identity", "commit"
275            };
276    
277            private static final boolean _SUPPORTS_ALTER_COLUMN_TYPE = false;
278    
279            private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
280    
281            private static final boolean _SUPPORTS_SCROLLABLE_RESULTS = false;
282    
283    }