1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.portal.dao.db;
16  
17  import com.liferay.portal.kernel.dao.db.DB;
18  import com.liferay.portal.kernel.dao.db.Index;
19  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
20  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
21  import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
22  import com.liferay.portal.kernel.util.FileUtil;
23  import com.liferay.portal.kernel.util.StringBundler;
24  import com.liferay.portal.kernel.util.StringUtil;
25  
26  import java.io.IOException;
27  
28  import java.sql.Connection;
29  import java.sql.PreparedStatement;
30  import java.sql.ResultSet;
31  import java.sql.SQLException;
32  
33  import java.util.ArrayList;
34  import java.util.List;
35  
36  /**
37   * <a href="PostgreSQLDB.java.html"><b><i>View Source</i></b></a>
38   *
39   * @author Alexander Chow
40   * @author Sandeep Soni
41   * @author Ganesh Ram
42   */
43  public class PostgreSQLDB extends BaseDB {
44  
45      public static DB getInstance() {
46          return _instance;
47      }
48  
49      public String buildSQL(String template) throws IOException {
50          template = convertTimestamp(template);
51          template = replaceTemplate(template, getTemplate());
52  
53          template = reword(template);
54  
55          return template;
56      }
57  
58      public List<Index> getIndexes() throws SQLException {
59          List<Index> indexes = new ArrayList<Index>();
60  
61          Connection con = null;
62          PreparedStatement ps = null;
63          ResultSet rs = null;
64  
65          try {
66              con = DataAccess.getConnection();
67  
68              StringBundler sb = new StringBundler(3);
69  
70              sb.append("select indexname, tablename, indexdef from pg_indexes ");
71              sb.append("where indexname like 'liferay_%' or indexname like ");
72              sb.append("'ix_%'");
73  
74              String sql = sb.toString();
75  
76              ps = con.prepareStatement(sql);
77  
78              rs = ps.executeQuery();
79  
80              while (rs.next()) {
81                  String indexName = rs.getString("indexname");
82                  String tableName = rs.getString("tablename");
83                  String indexSQL = rs.getString("indexdef").toLowerCase().trim();
84  
85                  boolean unique = true;
86  
87                  if (indexSQL.startsWith("create index ")) {
88                      unique = false;
89                  }
90  
91                  indexes.add(new Index(indexName, tableName, unique));
92              }
93          }
94          finally {
95              DataAccess.cleanUp(con, ps, rs);
96          }
97  
98          return indexes;
99      }
100 
101     protected PostgreSQLDB() {
102         super(TYPE_POSTGRESQL);
103     }
104 
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             FileUtil.read(
124                 sqlDir + "/portal" + suffix + "/portal" + suffix +
125                     "-postgresql.sql"));
126         sb.append("\n\n");
127         sb.append(FileUtil.read(sqlDir + "/indexes/indexes-postgresql.sql"));
128         sb.append("\n\n");
129         sb.append(
130             FileUtil.read(sqlDir + "/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         StringBundler sb = new StringBundler();
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_INDEX) != -1) {
168                 String[] tokens = StringUtil.split(line, " ");
169 
170                 line = StringUtil.replace(
171                     "drop index @index@;", "@index@", tokens[2]);
172             }
173             else if (line.indexOf(DROP_PRIMARY_KEY) != -1) {
174                 String[] tokens = StringUtil.split(line, " ");
175 
176                 line = StringUtil.replace(
177                     "alter table @table@ drop constraint @table@_pkey;",
178                     "@table@", tokens[2]);
179             }
180 
181             sb.append(line);
182             sb.append("\n");
183         }
184 
185         unsyncBufferedReader.close();
186 
187         return sb.toString();
188     }
189 
190     private static String[] _POSTGRESQL = {
191         "--", "true", "false",
192         "'01/01/1970'", "current_timestamp",
193         " bytea", " bool", " timestamp",
194         " double precision", " integer", " bigint",
195         " text", " text", " varchar",
196         "", "commit"
197     };
198 
199     private static PostgreSQLDB _instance = new PostgreSQLDB();
200 
201 }