001    /**
002     * Copyright (c) 2000-2013 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
013     */
014    
015    package com.liferay.portal.dao.db;
016    
017    import com.liferay.portal.kernel.dao.db.DB;
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 DB getInstance() {
044                    return _instance;
045            }
046    
047            public static String getCreateRulesSQL(
048                    String tableName, String columnName) {
049    
050                    StringBundler sb = new StringBundler(45);
051    
052                    sb.append("create or replace rule delete_");
053                    sb.append(tableName);
054                    sb.append(StringPool.UNDERLINE);
055                    sb.append(columnName);
056                    sb.append(" as on delete to ");
057                    sb.append(tableName);
058                    sb.append(" do also select case when exists(select 1 from ");
059                    sb.append("pg_catalog.pg_largeobject where (loid = old.");
060                    sb.append(columnName);
061                    sb.append(")) then lo_unlink(old.");
062                    sb.append(columnName);
063                    sb.append(") end from ");
064                    sb.append(tableName);
065                    sb.append(" where ");
066                    sb.append(tableName);
067                    sb.append(StringPool.PERIOD);
068                    sb.append(columnName);
069                    sb.append(" = old.");
070                    sb.append(columnName);
071    
072                    sb.append(";\ncreate or replace rule update_");
073                    sb.append(tableName);
074                    sb.append(StringPool.UNDERLINE);
075                    sb.append(columnName);
076                    sb.append(" as on update to ");
077                    sb.append(tableName);
078                    sb.append(" where old.");
079                    sb.append(columnName);
080                    sb.append(" is distinct from new.");
081                    sb.append(columnName);
082                    sb.append(" and old.");
083                    sb.append(columnName);
084                    sb.append(" is not null do also select case when exists(select 1 ");
085                    sb.append("from pg_catalog.pg_largeobject where (loid = old.");
086                    sb.append(columnName);
087                    sb.append(")) then lo_unlink(old.");
088                    sb.append(columnName);
089                    sb.append(") end from ");
090                    sb.append(tableName);
091                    sb.append(" where ");
092                    sb.append(tableName);
093                    sb.append(StringPool.PERIOD);
094                    sb.append(columnName);
095                    sb.append(" = old.");
096                    sb.append(columnName);
097                    sb.append(StringPool.SEMICOLON);
098    
099                    return sb.toString();
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<Index>();
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(null, ps, rs);
149                    }
150    
151                    return indexes;
152            }
153    
154            @Override
155            public boolean isSupportsQueryingAfterException() {
156                    return _SUPPORTS_QUERYING_AFTER_EXCEPTION;
157            }
158    
159            protected PostgreSQLDB() {
160                    super(TYPE_POSTGRESQL);
161            }
162    
163            @Override
164            protected String buildCreateFileContent(
165                            String sqlDir, String databaseName, int population)
166                    throws IOException {
167    
168                    String suffix = getSuffix(population);
169    
170                    StringBundler sb = new StringBundler(14);
171    
172                    sb.append("drop database ");
173                    sb.append(databaseName);
174                    sb.append(";\n");
175                    sb.append("create database ");
176                    sb.append(databaseName);
177                    sb.append(" encoding = 'UNICODE';\n");
178    
179                    if (population != BARE) {
180                            sb.append("\\c ");
181                            sb.append(databaseName);
182                            sb.append(";\n\n");
183                            sb.append(getCreateTablesContent(sqlDir, suffix));
184                            sb.append("\n\n");
185                            sb.append(readFile(sqlDir + "/indexes/indexes-postgresql.sql"));
186                            sb.append("\n\n");
187                            sb.append(readFile(sqlDir + "/sequences/sequences-postgresql.sql"));
188                    }
189    
190                    return sb.toString();
191            }
192    
193            @Override
194            protected String getServerName() {
195                    return "postgresql";
196            }
197    
198            @Override
199            protected String[] getTemplate() {
200                    return _POSTGRESQL;
201            }
202    
203            @Override
204            protected String reword(String data) throws IOException {
205                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
206                            new UnsyncStringReader(data));
207    
208                    StringBundler sb = new StringBundler();
209    
210                    StringBundler createRulesSQLSB = new StringBundler();
211                    String line = null;
212                    String tableName = null;
213    
214                    while ((line = unsyncBufferedReader.readLine()) != null) {
215                            if (line.startsWith(ALTER_COLUMN_NAME)) {
216                                    String[] template = buildColumnNameTokens(line);
217    
218                                    line = StringUtil.replace(
219                                            "alter table @table@ rename @old-column@ to @new-column@;",
220                                            REWORD_TEMPLATE, template);
221                            }
222                            else if (line.startsWith(ALTER_COLUMN_TYPE)) {
223                                    String[] template = buildColumnTypeTokens(line);
224    
225                                    line = StringUtil.replace(
226                                            "alter table @table@ alter @old-column@ type @type@ " +
227                                                    "using @old-column@::@type@;",
228                                            REWORD_TEMPLATE, template);
229                            }
230                            else if (line.startsWith(ALTER_TABLE_NAME)) {
231                                    String[] template = buildTableNameTokens(line);
232    
233                                    line = StringUtil.replace(
234                                            "alter table @old-table@ rename to @new-table@;",
235                                            RENAME_TABLE_TEMPLATE, template);
236                            }
237                            else if (line.startsWith(CREATE_TABLE)) {
238                                    String[] tokens = StringUtil.split(line, ' ');
239    
240                                    tableName = tokens[2];
241                            }
242                            else if (line.contains(DROP_INDEX)) {
243                                    String[] tokens = StringUtil.split(line, ' ');
244    
245                                    line = StringUtil.replace(
246                                            "drop index @index@;", "@index@", tokens[2]);
247                            }
248                            else if (line.contains(DROP_PRIMARY_KEY)) {
249                                    String[] tokens = StringUtil.split(line, ' ');
250    
251                                    line = StringUtil.replace(
252                                            "alter table @table@ drop constraint @table@_pkey;",
253                                            "@table@", tokens[2]);
254                            }
255                            else if (line.contains(getTemplateBlob())) {
256                                    String[] tokens = StringUtil.split(line, ' ');
257    
258                                    createRulesSQLSB.append(StringPool.NEW_LINE);
259                                    createRulesSQLSB.append(
260                                            getCreateRulesSQL(tableName, tokens[0]));
261                            }
262    
263                            else if (line.contains("\\\'")) {
264                                    line = StringUtil.replace(line, "\\\'", "\'\'");
265                            }
266    
267                            sb.append(line);
268                            sb.append("\n");
269                    }
270    
271                    unsyncBufferedReader.close();
272    
273                    sb.append(createRulesSQLSB.toString());
274    
275                    return sb.toString();
276            }
277    
278            private static final String[] _POSTGRESQL = {
279                    "--", "true", "false", "'01/01/1970'", "current_timestamp", " oid",
280                    " bytea", " bool", " timestamp", " double precision", " integer",
281                    " bigint", " text", " text", " varchar", "", "commit"
282            };
283    
284            private static final boolean _SUPPORTS_QUERYING_AFTER_EXCEPTION = false;
285    
286            private static PostgreSQLDB _instance = new PostgreSQLDB();
287    
288    }