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.db.Index;
019    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
020    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
021    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
022    import com.liferay.portal.kernel.util.StringBundler;
023    import com.liferay.portal.kernel.util.StringPool;
024    import com.liferay.portal.kernel.util.StringUtil;
025    
026    import java.io.IOException;
027    
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.ArrayList;
034    import java.util.List;
035    
036    /**
037     * @author Alexander Chow
038     * @author Sandeep Soni
039     * @author Ganesh Ram
040     */
041    public class PostgreSQLDB extends BaseDB {
042    
043            public static String getCreateRulesSQL(
044                    String tableName, String columnName) {
045    
046                    StringBundler sb = new StringBundler(45);
047    
048                    sb.append("create or replace rule delete_");
049                    sb.append(tableName);
050                    sb.append(StringPool.UNDERLINE);
051                    sb.append(columnName);
052                    sb.append(" as on delete to ");
053                    sb.append(tableName);
054                    sb.append(" do also select case when exists(select 1 from ");
055                    sb.append("pg_catalog.pg_largeobject where (loid = old.");
056                    sb.append(columnName);
057                    sb.append(")) then lo_unlink(old.");
058                    sb.append(columnName);
059                    sb.append(") end from ");
060                    sb.append(tableName);
061                    sb.append(" where ");
062                    sb.append(tableName);
063                    sb.append(StringPool.PERIOD);
064                    sb.append(columnName);
065                    sb.append(" = old.");
066                    sb.append(columnName);
067    
068                    sb.append(";\ncreate or replace rule update_");
069                    sb.append(tableName);
070                    sb.append(StringPool.UNDERLINE);
071                    sb.append(columnName);
072                    sb.append(" as on update to ");
073                    sb.append(tableName);
074                    sb.append(" where old.");
075                    sb.append(columnName);
076                    sb.append(" is distinct from new.");
077                    sb.append(columnName);
078                    sb.append(" and old.");
079                    sb.append(columnName);
080                    sb.append(" is not null do also select case when exists(select 1 ");
081                    sb.append("from pg_catalog.pg_largeobject where (loid = old.");
082                    sb.append(columnName);
083                    sb.append(")) then lo_unlink(old.");
084                    sb.append(columnName);
085                    sb.append(") end from ");
086                    sb.append(tableName);
087                    sb.append(" where ");
088                    sb.append(tableName);
089                    sb.append(StringPool.PERIOD);
090                    sb.append(columnName);
091                    sb.append(" = old.");
092                    sb.append(columnName);
093                    sb.append(StringPool.SEMICOLON);
094    
095                    return sb.toString();
096            }
097    
098            public PostgreSQLDB(int majorVersion, int minorVersion) {
099                    super(DBType.POSTGRESQL, majorVersion, minorVersion);
100            }
101    
102            @Override
103            public String buildSQL(String template) throws IOException {
104                    template = convertTimestamp(template);
105                    template = replaceTemplate(template, getTemplate());
106    
107                    template = reword(template);
108    
109                    return template;
110            }
111    
112            @Override
113            public List<Index> getIndexes(Connection con) throws SQLException {
114                    List<Index> indexes = new ArrayList<>();
115    
116                    PreparedStatement ps = null;
117                    ResultSet rs = null;
118    
119                    try {
120                            StringBundler sb = new StringBundler(3);
121    
122                            sb.append("select indexname, tablename, indexdef from pg_indexes ");
123                            sb.append("where indexname like 'liferay_%' or indexname like ");
124                            sb.append("'ix_%'");
125    
126                            String sql = sb.toString();
127    
128                            ps = con.prepareStatement(sql);
129    
130                            rs = ps.executeQuery();
131    
132                            while (rs.next()) {
133                                    String indexName = rs.getString("indexname");
134                                    String tableName = rs.getString("tablename");
135                                    String indexSQL = StringUtil.toLowerCase(
136                                            rs.getString("indexdef").trim());
137    
138                                    boolean unique = true;
139    
140                                    if (indexSQL.startsWith("create index ")) {
141                                            unique = false;
142                                    }
143    
144                                    indexes.add(new Index(indexName, tableName, unique));
145                            }
146                    }
147                    finally {
148                            DataAccess.cleanUp(ps, rs);
149                    }
150    
151                    return indexes;
152            }
153    
154            @Override
155            public boolean isSupportsQueryingAfterException() {
156                    return _SUPPORTS_QUERYING_AFTER_EXCEPTION;
157            }
158    
159            @Override
160            protected String buildCreateFileContent(
161                            String sqlDir, String databaseName, int population)
162                    throws IOException {
163    
164                    String suffix = getSuffix(population);
165    
166                    StringBundler sb = new StringBundler(14);
167    
168                    sb.append("drop database ");
169                    sb.append(databaseName);
170                    sb.append(";\n");
171                    sb.append("create database ");
172                    sb.append(databaseName);
173                    sb.append(" encoding = 'UNICODE';\n");
174    
175                    if (population != BARE) {
176                            sb.append("\\c ");
177                            sb.append(databaseName);
178                            sb.append(";\n\n");
179                            sb.append(getCreateTablesContent(sqlDir, suffix));
180                            sb.append("\n\n");
181                            sb.append(readFile(sqlDir + "/indexes/indexes-postgresql.sql"));
182                            sb.append("\n\n");
183                            sb.append(readFile(sqlDir + "/sequences/sequences-postgresql.sql"));
184                    }
185    
186                    return sb.toString();
187            }
188    
189            @Override
190            protected String getServerName() {
191                    return "postgresql";
192            }
193    
194            @Override
195            protected String[] getTemplate() {
196                    return _POSTGRESQL;
197            }
198    
199            @Override
200            protected String reword(String data) throws IOException {
201                    try (UnsyncBufferedReader unsyncBufferedReader =
202                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
203    
204                            StringBundler sb = new StringBundler();
205    
206                            StringBundler createRulesSQLSB = new StringBundler();
207                            String line = null;
208                            String tableName = null;
209    
210                            while ((line = unsyncBufferedReader.readLine()) != null) {
211                                    if (line.startsWith(ALTER_COLUMN_NAME)) {
212                                            String[] template = buildColumnNameTokens(line);
213    
214                                            line = StringUtil.replace(
215                                                    "alter table @table@ rename @old-column@ to " +
216                                                            "@new-column@;",
217                                                    REWORD_TEMPLATE, template);
218                                    }
219                                    else if (line.startsWith(ALTER_COLUMN_TYPE)) {
220                                            String[] template = buildColumnTypeTokens(line);
221    
222                                            line = StringUtil.replace(
223                                                    "alter table @table@ alter @old-column@ type @type@ " +
224                                                            "using @old-column@::@type@;",
225                                                    REWORD_TEMPLATE, template);
226                                    }
227                                    else if (line.startsWith(ALTER_TABLE_NAME)) {
228                                            String[] template = buildTableNameTokens(line);
229    
230                                            line = StringUtil.replace(
231                                                    "alter table @old-table@ rename to @new-table@;",
232                                                    RENAME_TABLE_TEMPLATE, template);
233                                    }
234                                    else if (line.startsWith(CREATE_TABLE)) {
235                                            String[] tokens = StringUtil.split(line, ' ');
236    
237                                            tableName = tokens[2];
238                                    }
239                                    else if (line.contains(DROP_INDEX)) {
240                                            String[] tokens = StringUtil.split(line, ' ');
241    
242                                            line = StringUtil.replace(
243                                                    "drop index @index@;", "@index@", tokens[2]);
244                                    }
245                                    else if (line.contains(DROP_PRIMARY_KEY)) {
246                                            String[] tokens = StringUtil.split(line, ' ');
247    
248                                            line = StringUtil.replace(
249                                                    "alter table @table@ drop constraint @table@_pkey;",
250                                                    "@table@", tokens[2]);
251                                    }
252                                    else if (line.contains(getTemplateBlob())) {
253                                            String[] tokens = StringUtil.split(line, ' ');
254    
255                                            createRulesSQLSB.append(StringPool.NEW_LINE);
256                                            createRulesSQLSB.append(
257                                                    getCreateRulesSQL(tableName, tokens[0]));
258                                    }
259                                    else if (line.contains("\\\'")) {
260                                            line = StringUtil.replace(line, "\\\'", "\'\'");
261                                    }
262    
263                                    sb.append(line);
264                                    sb.append("\n");
265                            }
266    
267                            sb.append(createRulesSQLSB.toString());
268    
269                            return sb.toString();
270                    }
271            }
272    
273            private static final String[] _POSTGRESQL = {
274                    "--", "true", "false", "'01/01/1970'", "current_timestamp", " oid",
275                    " bytea", " bool", " timestamp", " double precision", " integer",
276                    " bigint", " text", " text", " varchar", "", "commit"
277            };
278    
279            private static final boolean _SUPPORTS_QUERYING_AFTER_EXCEPTION = false;
280    
281    }