1
22
23 package com.liferay.portal.tools.sql;
24
25 import com.liferay.portal.kernel.util.FileUtil;
26 import com.liferay.portal.kernel.util.StringUtil;
27
28 import java.io.BufferedReader;
29 import java.io.File;
30 import java.io.IOException;
31 import java.io.StringReader;
32
33
41 public class OracleUtil extends DBUtil {
42
43 public static DBUtil getInstance() {
44 return _instance;
45 }
46
47 public String buildSQL(String template) throws IOException {
48 template = _preBuildSQL(template);
49 template = _postBuildSQL(template);
50
51 return template;
52 }
53
54 public void buildSQLFile(String fileName) throws IOException {
55 String oracle = buildTemplate(fileName);
56
57 oracle = _preBuildSQL(oracle);
58
59 BufferedReader br = new BufferedReader(new StringReader(oracle));
60
61 StringBuilder imageSB = new StringBuilder();
62 StringBuilder journalArticleSB = new StringBuilder();
63 StringBuilder journalStructureSB = new StringBuilder();
64 StringBuilder journalTemplateSB = new StringBuilder();
65
66 String line = null;
67
68 while ((line = br.readLine()) != null) {
69 if (line.startsWith("insert into Image")) {
70 _convertToOracleCSV(line, imageSB);
71 }
72 else if (line.startsWith("insert into JournalArticle (")) {
73 _convertToOracleCSV(line, journalArticleSB);
74 }
75 else if (line.startsWith("insert into JournalStructure (")) {
76 _convertToOracleCSV(line, journalStructureSB);
77 }
78 else if (line.startsWith("insert into JournalTemplate (")) {
79 _convertToOracleCSV(line, journalTemplateSB);
80 }
81 }
82
83 br.close();
84
85 if (imageSB.length() > 0) {
86 FileUtil.write(
87 "../sql/" + fileName + "/" + fileName + "-oracle-image.csv",
88 imageSB.toString());
89 }
90
91 if (journalArticleSB.length() > 0) {
92 FileUtil.write(
93 "../sql/" + fileName + "/" + fileName +
94 "-oracle-journalarticle.csv",
95 journalArticleSB.toString());
96 }
97
98 if (journalStructureSB.length() > 0) {
99 FileUtil.write(
100 "../sql/" + fileName + "/" + fileName +
101 "-oracle-journalstructure.csv",
102 journalStructureSB.toString());
103 }
104
105 if (journalTemplateSB.length() > 0) {
106 FileUtil.write(
107 "../sql/" + fileName + "/" + fileName +
108 "-oracle-journaltemplate.csv",
109 journalTemplateSB.toString());
110 }
111
112 oracle = _postBuildSQL(oracle);
113
114 FileUtil.write(
115 "../sql/" + fileName + "/" + fileName + "-oracle.sql", oracle);
116 }
117
118 public boolean isSupportsAlterColumnType() {
119
120
122 return _SUPPORTS_ALTER_COLUMN_TYPE;
123 }
124
125 protected OracleUtil() {
126 super(TYPE_ORACLE);
127 }
128
129 protected void buildCreateFile(String databaseName, boolean minimal)
130 throws IOException {
131
132 String minimalSuffix = getMinimalSuffix(minimal);
133
134 File file = new File(
135 "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
136 "-oracle.sql");
137
138 StringBuilder sb = new StringBuilder();
139
140 sb.append("drop user &1 cascade;\n");
141 sb.append("create user &1 identified by &2;\n");
142 sb.append("grant connect,resource to &1;\n");
143 sb.append("connect &1/&2;\n");
144 sb.append("set define off;\n");
145 sb.append("\n");
146 sb.append(
147 FileUtil.read(
148 "../sql/portal" + minimalSuffix + "/portal" + minimalSuffix +
149 "-oracle.sql"));
150 sb.append("\n\n");
151 sb.append(FileUtil.read("../sql/indexes/indexes-oracle.sql"));
152 sb.append("\n\n");
153 sb.append(FileUtil.read("../sql/sequences/sequences-oracle.sql"));
154 sb.append("\n");
155 sb.append("quit");
156
157 FileUtil.write(file, sb.toString());
158 }
159
160 protected String getServerName() {
161 return "oracle";
162 }
163
164 protected String[] getTemplate() {
165 return _ORACLE;
166 }
167
168 protected String reword(String data) throws IOException {
169 BufferedReader br = new BufferedReader(new StringReader(data));
170
171 StringBuilder sb = new StringBuilder();
172
173 String line = null;
174
175 while ((line = br.readLine()) != null) {
176 if (line.startsWith(ALTER_COLUMN_NAME)) {
177 String[] template = buildColumnNameTokens(line);
178
179 line = StringUtil.replace(
180 "alter table @table@ rename column @old-column@ to " +
181 "@new-column@;",
182 REWORD_TEMPLATE, template);
183 }
184 else if (line.startsWith(ALTER_COLUMN_TYPE)) {
185 String[] template = buildColumnTypeTokens(line);
186
187 line = StringUtil.replace(
188 "alter table @table@ modify @old-column@ @type@;",
189 REWORD_TEMPLATE, template);
190 }
191
192 sb.append(line);
193 sb.append("\n");
194 }
195
196 br.close();
197
198 return sb.toString();
199 }
200
201 private void _convertToOracleCSV(String line, StringBuilder sb) {
202 int x = line.indexOf("values (");
203 int y = line.lastIndexOf(");");
204
205 line = line.substring(x + 8, y);
206
207 line = StringUtil.replace(line, "sysdate, ", "20050101, ");
208
209 sb.append(line);
210 sb.append("\n");
211 }
212
213 private String _preBuildSQL(String template) throws IOException {
214 template = convertTimestamp(template);
215 template = replaceTemplate(template, getTemplate());
216
217 template = reword(template);
218 template = StringUtil.replace(
219 template,
220 new String[] {"\\\\", "\\'", "\\\""},
221 new String[] {"\\", "''", "\""});
222
223 return template;
224 }
225
226 private String _postBuildSQL(String template) throws IOException {
227 template = removeLongInserts(template);
228 template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
229
230 return template;
231 }
232
233 private static String[] _ORACLE = {
234 "--", "1", "0",
235 "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
236 " blob", " number(1, 0)", " timestamp",
237 " number(30,20)", " number(30,0)", " number(30,0)",
238 " varchar2(4000)", " clob", " varchar2",
239 "", "commit"
240 };
241
242 private static boolean _SUPPORTS_ALTER_COLUMN_TYPE;
243
244 private static OracleUtil _instance = new OracleUtil();
245
246 }