001    /**
002     * Copyright (c) 2000-2012 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.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(Connection con) throws SQLException {
058                    List<Index> indexes = new ArrayList<Index>();
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 = rs.getString("indexdef").toLowerCase().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            protected PostgreSQLDB() {
098                    super(TYPE_POSTGRESQL);
099            }
100    
101            @Override
102            protected String buildCreateFileContent(
103                            String sqlDir, String databaseName, int population)
104                    throws IOException {
105    
106                    String suffix = getSuffix(population);
107    
108                    StringBundler sb = new StringBundler(14);
109    
110                    sb.append("drop database ");
111                    sb.append(databaseName);
112                    sb.append(";\n");
113                    sb.append("create database ");
114                    sb.append(databaseName);
115                    sb.append(" encoding = 'UNICODE';\n");
116                    sb.append("\\c ");
117                    sb.append(databaseName);
118                    sb.append(";\n\n");
119                    sb.append(
120                            readFile(
121                                    sqlDir + "/portal" + suffix + "/portal" + suffix +
122                                            "-postgresql.sql"));
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                    return sb.toString();
129            }
130    
131            @Override
132            protected String getServerName() {
133                    return "postgresql";
134            }
135    
136            @Override
137            protected String[] getTemplate() {
138                    return _POSTGRESQL;
139            }
140    
141            @Override
142            protected String reword(String data) throws IOException {
143                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
144                            new UnsyncStringReader(data));
145    
146                    StringBundler sb = new StringBundler();
147    
148                    String line = null;
149    
150                    while ((line = unsyncBufferedReader.readLine()) != null) {
151                            if (line.startsWith(ALTER_COLUMN_NAME)) {
152                                    String[] template = buildColumnNameTokens(line);
153    
154                                    line = StringUtil.replace(
155                                            "alter table @table@ rename @old-column@ to @new-column@;",
156                                            REWORD_TEMPLATE, template);
157                            }
158                            else if (line.startsWith(ALTER_COLUMN_TYPE)) {
159                                    String[] template = buildColumnTypeTokens(line);
160    
161                                    line = StringUtil.replace(
162                                            "alter table @table@ alter @old-column@ type @type@ " +
163                                                    "using @old-column@::@type@;",
164                                            REWORD_TEMPLATE, template);
165                            }
166                            else if (line.indexOf(DROP_INDEX) != -1) {
167                                    String[] tokens = StringUtil.split(line, ' ');
168    
169                                    line = StringUtil.replace(
170                                            "drop index @index@;", "@index@", tokens[2]);
171                            }
172                            else if (line.indexOf(DROP_PRIMARY_KEY) != -1) {
173                                    String[] tokens = StringUtil.split(line, ' ');
174    
175                                    line = StringUtil.replace(
176                                            "alter table @table@ drop constraint @table@_pkey;",
177                                            "@table@", tokens[2]);
178                            }
179                            else if (line.indexOf("\\\'") != -1) {
180                                    line = StringUtil.replace(line, "\\\'", "\'\'");
181                            }
182    
183                            sb.append(line);
184                            sb.append("\n");
185                    }
186    
187                    unsyncBufferedReader.close();
188    
189                    return sb.toString();
190            }
191    
192            private static final String[] _POSTGRESQL = {
193                    "--", "true", "false", "'01/01/1970'", "current_timestamp", " oid",
194                    " bytea", " bool", " timestamp", " double precision", " integer",
195                    " bigint", " text", " text", " varchar", "", "commit"
196            };
197    
198            private static PostgreSQLDB _instance = new PostgreSQLDB();
199    
200    }