1
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
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 }