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