001    /**
002     * Copyright (c) 2000-2012 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.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.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 static DB getInstance() {
043                    return _instance;
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() throws SQLException {
058                    List<Index> indexes = new ArrayList<Index>();
059    
060                    Connection con = null;
061                    PreparedStatement ps = null;
062                    ResultSet rs = null;
063    
064                    try {
065                            con = DataAccess.getConnection();
066    
067                            StringBundler sb = new StringBundler(3);
068    
069                            sb.append("select indexname, tablename, indexdef from pg_indexes ");
070                            sb.append("where indexname like 'liferay_%' or indexname like ");
071                            sb.append("'ix_%'");
072    
073                            String sql = sb.toString();
074    
075                            ps = con.prepareStatement(sql);
076    
077                            rs = ps.executeQuery();
078    
079                            while (rs.next()) {
080                                    String indexName = rs.getString("indexname");
081                                    String tableName = rs.getString("tablename");
082                                    String indexSQL = rs.getString("indexdef").toLowerCase().trim();
083    
084                                    boolean unique = true;
085    
086                                    if (indexSQL.startsWith("create index ")) {
087                                            unique = false;
088                                    }
089    
090                                    indexes.add(new Index(indexName, tableName, unique));
091                            }
092                    }
093                    finally {
094                            DataAccess.cleanUp(con, ps, rs);
095                    }
096    
097                    return indexes;
098            }
099    
100            protected PostgreSQLDB() {
101                    super(TYPE_POSTGRESQL);
102            }
103    
104            @Override
105            protected String buildCreateFileContent(
106                            String sqlDir, String databaseName, int population)
107                    throws IOException {
108    
109                    String suffix = getSuffix(population);
110    
111                    StringBundler sb = new StringBundler(14);
112    
113                    sb.append("drop database ");
114                    sb.append(databaseName);
115                    sb.append(";\n");
116                    sb.append("create database ");
117                    sb.append(databaseName);
118                    sb.append(" encoding = 'UNICODE';\n");
119                    sb.append("\\c ");
120                    sb.append(databaseName);
121                    sb.append(";\n\n");
122                    sb.append(
123                            readFile(
124                                    sqlDir + "/portal" + suffix + "/portal" + suffix +
125                                            "-postgresql.sql"));
126                    sb.append("\n\n");
127                    sb.append(readFile(sqlDir + "/indexes/indexes-postgresql.sql"));
128                    sb.append("\n\n");
129                    sb.append(readFile(sqlDir + "/sequences/sequences-postgresql.sql"));
130    
131                    return sb.toString();
132            }
133    
134            @Override
135            protected String getServerName() {
136                    return "postgresql";
137            }
138    
139            @Override
140            protected String[] getTemplate() {
141                    return _POSTGRESQL;
142            }
143    
144            @Override
145            protected String reword(String data) throws IOException {
146                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
147                            new UnsyncStringReader(data));
148    
149                    StringBundler sb = new StringBundler();
150    
151                    String line = null;
152    
153                    while ((line = unsyncBufferedReader.readLine()) != null) {
154                            if (line.startsWith(ALTER_COLUMN_NAME)) {
155                                    String[] template = buildColumnNameTokens(line);
156    
157                                    line = StringUtil.replace(
158                                            "alter table @table@ rename @old-column@ to @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.indexOf(DROP_INDEX) != -1) {
170                                    String[] tokens = StringUtil.split(line, ' ');
171    
172                                    line = StringUtil.replace(
173                                            "drop index @index@;", "@index@", tokens[2]);
174                            }
175                            else if (line.indexOf(DROP_PRIMARY_KEY) != -1) {
176                                    String[] tokens = StringUtil.split(line, ' ');
177    
178                                    line = StringUtil.replace(
179                                            "alter table @table@ drop constraint @table@_pkey;",
180                                            "@table@", tokens[2]);
181                            }
182                            else if (line.indexOf("\\\'") != -1) {
183                                    line = StringUtil.replace(line, "\\\'", "\'\'");
184                            }
185    
186                            sb.append(line);
187                            sb.append("\n");
188                    }
189    
190                    unsyncBufferedReader.close();
191    
192                    return sb.toString();
193            }
194    
195            private static final String[] _POSTGRESQL = {
196                    "--", "true", "false", "'01/01/1970'", "current_timestamp", " oid",
197                    " bytea", " bool", " timestamp", " double precision", " integer",
198                    " bigint", " text", " text", " varchar", "", "commit"
199            };
200    
201            private static PostgreSQLDB _instance = new PostgreSQLDB();
202    
203    }