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