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