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