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.StringUtil;
024    
025    import java.io.IOException;
026    
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.ArrayList;
033    import java.util.List;
034    
035    /**
036     * @author Alexander Chow
037     * @author Sandeep Soni
038     * @author Ganesh Ram
039     */
040    public class PostgreSQLDB extends BaseDB {
041    
042            public PostgreSQLDB(int majorVersion, int minorVersion) {
043                    super(DBType.POSTGRESQL, majorVersion, minorVersion);
044            }
045    
046            @Override
047            public String buildSQL(String template) throws IOException {
048                    template = convertTimestamp(template);
049                    template = replaceTemplate(template, getTemplate());
050    
051                    template = reword(template);
052    
053                    return template;
054            }
055    
056            @Override
057            public List<Index> getIndexes(Connection con) throws SQLException {
058                    List<Index> indexes = new ArrayList<>();
059    
060                    PreparedStatement ps = null;
061                    ResultSet rs = null;
062    
063                    try {
064                            StringBundler sb = new StringBundler(3);
065    
066                            sb.append("select indexname, tablename, indexdef from pg_indexes ");
067                            sb.append("where indexname like 'liferay_%' or indexname like ");
068                            sb.append("'ix_%'");
069    
070                            String sql = sb.toString();
071    
072                            ps = con.prepareStatement(sql);
073    
074                            rs = ps.executeQuery();
075    
076                            while (rs.next()) {
077                                    String indexName = rs.getString("indexname");
078                                    String tableName = rs.getString("tablename");
079                                    String indexSQL = StringUtil.toLowerCase(
080                                            rs.getString("indexdef").trim());
081    
082                                    boolean unique = true;
083    
084                                    if (indexSQL.startsWith("create index ")) {
085                                            unique = false;
086                                    }
087    
088                                    indexes.add(new Index(indexName, tableName, unique));
089                            }
090                    }
091                    finally {
092                            DataAccess.cleanUp(ps, rs);
093                    }
094    
095                    return indexes;
096            }
097    
098            @Override
099            public boolean isSupportsQueryingAfterException() {
100                    return _SUPPORTS_QUERYING_AFTER_EXCEPTION;
101            }
102    
103            @Override
104            protected String buildCreateFileContent(
105                            String sqlDir, String databaseName, int population)
106                    throws IOException {
107    
108                    String suffix = getSuffix(population);
109    
110                    StringBundler sb = new StringBundler(14);
111    
112                    sb.append("drop database ");
113                    sb.append(databaseName);
114                    sb.append(";\n");
115                    sb.append("create database ");
116                    sb.append(databaseName);
117                    sb.append(" encoding = 'UNICODE';\n");
118    
119                    if (population != BARE) {
120                            sb.append("\\c ");
121                            sb.append(databaseName);
122                            sb.append(";\n\n");
123                            sb.append(getCreateTablesContent(sqlDir, suffix));
124                            sb.append("\n\n");
125                            sb.append(readFile(sqlDir + "/indexes/indexes-postgresql.sql"));
126                            sb.append("\n\n");
127                            sb.append(readFile(sqlDir + "/sequences/sequences-postgresql.sql"));
128                    }
129    
130                    return sb.toString();
131            }
132    
133            @Override
134            protected String getServerName() {
135                    return "postgresql";
136            }
137    
138            @Override
139            protected String[] getTemplate() {
140                    return _POSTGRESQL;
141            }
142    
143            @Override
144            protected String reword(String data) throws IOException {
145                    try (UnsyncBufferedReader unsyncBufferedReader =
146                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
147    
148                            StringBundler sb = new StringBundler();
149    
150                            String line = null;
151    
152                            while ((line = unsyncBufferedReader.readLine()) != null) {
153                                    if (line.startsWith(ALTER_COLUMN_NAME)) {
154                                            String[] template = buildColumnNameTokens(line);
155    
156                                            line = StringUtil.replace(
157                                                    "alter table @table@ rename @old-column@ to " +
158                                                            "@new-column@;",
159                                                    REWORD_TEMPLATE, template);
160                                    }
161                                    else if (line.startsWith(ALTER_COLUMN_TYPE)) {
162                                            String[] template = buildColumnTypeTokens(line);
163    
164                                            line = StringUtil.replace(
165                                                    "alter table @table@ alter @old-column@ type @type@ " +
166                                                            "using @old-column@::@type@;",
167                                                    REWORD_TEMPLATE, template);
168                                    }
169                                    else if (line.startsWith(ALTER_TABLE_NAME)) {
170                                            String[] template = buildTableNameTokens(line);
171    
172                                            line = StringUtil.replace(
173                                                    "alter table @old-table@ rename to @new-table@;",
174                                                    RENAME_TABLE_TEMPLATE, template);
175                                    }
176                                    else if (line.contains(DROP_INDEX)) {
177                                            String[] tokens = StringUtil.split(line, ' ');
178    
179                                            line = StringUtil.replace(
180                                                    "drop index @index@;", "@index@", tokens[2]);
181                                    }
182                                    else if (line.contains(DROP_PRIMARY_KEY)) {
183                                            String[] tokens = StringUtil.split(line, ' ');
184    
185                                            line = StringUtil.replace(
186                                                    "alter table @table@ drop constraint @table@_pkey;",
187                                                    "@table@", tokens[2]);
188                                    }
189                                    else if (line.contains("\\\'")) {
190                                            line = StringUtil.replace(line, "\\\'", "\'\'");
191                                    }
192    
193                                    sb.append(line);
194                                    sb.append("\n");
195                            }
196    
197                            return sb.toString();
198                    }
199            }
200    
201            private static final String[] _POSTGRESQL = {
202                    "--", "true", "false", "'01/01/1970'", "current_timestamp", " oid",
203                    " bytea", " bool", " timestamp", " double precision", " integer",
204                    " bigint", " text", " text", " varchar", "", "commit"
205            };
206    
207            private static final boolean _SUPPORTS_QUERYING_AFTER_EXCEPTION = false;
208    
209    }