1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   *
12   *
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.StringBundler;
23  import com.liferay.portal.kernel.util.StringUtil;
24  
25  import java.io.IOException;
26  
27  import java.sql.Connection;
28  import java.sql.PreparedStatement;
29  import java.sql.ResultSet;
30  import java.sql.SQLException;
31  
32  import java.util.ArrayList;
33  import java.util.List;
34  
35  /**
36   * <a href="PostgreSQLDB.java.html"><b><i>View Source</i></b></a>
37   *
38   * @author Alexander Chow
39   * @author Sandeep Soni
40   * @author Ganesh Ram
41   */
42  public class PostgreSQLDB extends BaseDB {
43  
44      public static DB getInstance() {
45          return _instance;
46      }
47  
48      public String buildSQL(String template) throws IOException {
49          template = convertTimestamp(template);
50          template = replaceTemplate(template, getTemplate());
51  
52          template = reword(template);
53  
54          return template;
55      }
56  
57      public List<Index> getIndexes() throws SQLException {
58          List<Index> indexes = new ArrayList<Index>();
59  
60          Connection con = null;
61          PreparedStatement ps = null;
62          ResultSet rs = null;
63  
64          try {
65              con = DataAccess.getConnection();
66  
67              StringBundler sb = new StringBundler(3);
68  
69              sb.append("select indexname, tablename, indexdef from pg_indexes ");
70              sb.append("where indexname like 'liferay_%' or indexname like ");
71              sb.append("'ix_%'");
72  
73              String sql = sb.toString();
74  
75              ps = con.prepareStatement(sql);
76  
77              rs = ps.executeQuery();
78  
79              while (rs.next()) {
80                  String indexName = rs.getString("indexname");
81                  String tableName = rs.getString("tablename");
82                  String indexSQL = rs.getString("indexdef").toLowerCase().trim();
83  
84                  boolean unique = true;
85  
86                  if (indexSQL.startsWith("create index ")) {
87                      unique = false;
88                  }
89  
90                  indexes.add(new Index(indexName, tableName, unique));
91              }
92          }
93          finally {
94              DataAccess.cleanUp(con, ps, rs);
95          }
96  
97          return indexes;
98      }
99  
100     protected PostgreSQLDB() {
101         super(TYPE_POSTGRESQL);
102     }
103 
104     protected String buildCreateFileContent(
105             String sqlDir, String databaseName, int population)
106         throws IOException {
107 
108         String suffix = getSuffix(population);
109 
110         StringBundler sb = new StringBundler(14);
111 
112         sb.append("drop database ");
113         sb.append(databaseName);
114         sb.append(";\n");
115         sb.append("create database ");
116         sb.append(databaseName);
117         sb.append(" encoding = 'UNICODE';\n");
118         sb.append("\\c ");
119         sb.append(databaseName);
120         sb.append(";\n\n");
121         sb.append(
122             readFile(
123                 sqlDir + "/portal" + suffix + "/portal" + suffix +
124                     "-postgresql.sql"));
125         sb.append("\n\n");
126         sb.append(readFile(sqlDir + "/indexes/indexes-postgresql.sql"));
127         sb.append("\n\n");
128         sb.append(readFile(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 }