1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    *
5    *
6    *
7    * The contents of this file are subject to the terms of the Liferay Enterprise
8    * Subscription License ("License"). You may not use this file except in
9    * compliance with the License. You can obtain a copy of the License by
10   * contacting Liferay, Inc. See the License for the specific language governing
11   * permissions and limitations under the License, including but not limited to
12   * distribution rights of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.dao.db;
24  
25  import com.liferay.portal.kernel.dao.db.DB;
26  import com.liferay.portal.kernel.dao.db.Index;
27  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
28  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
29  import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
30  import com.liferay.portal.kernel.util.FileUtil;
31  import com.liferay.portal.kernel.util.StringUtil;
32  
33  import java.io.IOException;
34  
35  import java.sql.Connection;
36  import java.sql.PreparedStatement;
37  import java.sql.ResultSet;
38  import java.sql.SQLException;
39  
40  import java.util.ArrayList;
41  import java.util.List;
42  
43  /**
44   * <a href="PostgreSQLDB.java.html"><b><i>View Source</i></b></a>
45   *
46   * @author Alexander Chow
47   * @author Sandeep Soni
48   * @author Ganesh Ram
49   */
50  public class PostgreSQLDB extends BaseDB {
51  
52      public static DB getInstance() {
53          return _instance;
54      }
55  
56      public String buildSQL(String template) throws IOException {
57          template = convertTimestamp(template);
58          template = replaceTemplate(template, getTemplate());
59  
60          template = reword(template);
61  
62          return template;
63      }
64  
65      public List<Index> getIndexes() throws SQLException {
66          List<Index> indexes = new ArrayList<Index>();
67  
68          Connection con = null;
69          PreparedStatement ps = null;
70          ResultSet rs = null;
71  
72          try {
73              con = DataAccess.getConnection();
74  
75              StringBuilder sb = new StringBuilder();
76  
77              sb.append("select indexname, tablename, indexdef from pg_indexes ");
78              sb.append("where indexname like 'liferay_%' or indexname like ");
79              sb.append("'ix_%'");
80  
81              String sql = sb.toString();
82  
83              ps = con.prepareStatement(sql);
84  
85              rs = ps.executeQuery();
86  
87              while (rs.next()) {
88                  String indexName = rs.getString("indexname");
89                  String tableName = rs.getString("tablename");
90                  String indexSQL = rs.getString("indexdef").toLowerCase().trim();
91  
92                  boolean unique = true;
93  
94                  if (indexSQL.startsWith("create index ")) {
95                      unique = false;
96                  }
97  
98                  indexes.add(new Index(indexName, tableName, unique));
99              }
100         }
101         finally {
102             DataAccess.cleanUp(con, ps, rs);
103         }
104 
105         return indexes;
106     }
107 
108     protected PostgreSQLDB() {
109         super(TYPE_POSTGRESQL);
110     }
111 
112     protected String buildCreateFileContent(String databaseName, int population)
113         throws IOException {
114 
115         String suffix = getSuffix(population);
116 
117         StringBuilder sb = new StringBuilder();
118 
119         sb.append("drop database " + databaseName + ";\n");
120         sb.append(
121             "create database " + databaseName + " encoding = 'UNICODE';\n");
122         sb.append("\\c " + databaseName + ";\n\n");
123         sb.append(
124             FileUtil.read(
125                 "../sql/portal" + suffix + "/portal" + suffix +
126                     "-postgresql.sql"));
127         sb.append("\n\n");
128         sb.append(FileUtil.read("../sql/indexes/indexes-postgresql.sql"));
129         sb.append("\n\n");
130         sb.append(FileUtil.read("../sql/sequences/sequences-postgresql.sql"));
131 
132         return sb.toString();
133     }
134 
135     protected String getServerName() {
136         return "postgresql";
137     }
138 
139     protected String[] getTemplate() {
140         return _POSTGRESQL;
141     }
142 
143     protected String reword(String data) throws IOException {
144         UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
145             new UnsyncStringReader(data));
146 
147         StringBuilder sb = new StringBuilder();
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 @new-column@;",
157                     REWORD_TEMPLATE, template);
158             }
159             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
160                 String[] template = buildColumnTypeTokens(line);
161 
162                 line = StringUtil.replace(
163                     "alter table @table@ alter @old-column@ type @type@ " +
164                         "using @old-column@::@type@;",
165                     REWORD_TEMPLATE, template);
166             }
167             else if (line.indexOf(DROP_PRIMARY_KEY) != -1) {
168                 String[] tokens = StringUtil.split(line, " ");
169 
170                 line = StringUtil.replace(
171                     "alter table @table@ drop constraint @table@_pkey;",
172                     "@table@", tokens[2]);
173             }
174 
175             sb.append(line);
176             sb.append("\n");
177         }
178 
179         unsyncBufferedReader.close();
180 
181         return sb.toString();
182     }
183 
184     private static String[] _POSTGRESQL = {
185         "--", "true", "false",
186         "'01/01/1970'", "current_timestamp",
187         " bytea", " bool", " timestamp",
188         " double precision", " integer", " bigint",
189         " text", " text", " varchar",
190         "", "commit"
191     };
192 
193     private static PostgreSQLDB _instance = new PostgreSQLDB();
194 
195 }