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 protected OracleUtil() {
119 super(TYPE_ORACLE);
120 }
121
122 protected void buildCreateFile(String databaseName, boolean minimal)
123 throws IOException {
124
125 String minimalSuffix = getMinimalSuffix(minimal);
126
127 File file = new File(
128 "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
129 "-oracle.sql");
130
131 StringBuilder sb = new StringBuilder();
132
133 sb.append("drop user &1 cascade;\n");
134 sb.append("create user &1 identified by &2;\n");
135 sb.append("grant connect,resource to &1;\n");
136 sb.append("connect &1/&2;\n");
137 sb.append("set define off;\n");
138 sb.append("\n");
139 sb.append(
140 FileUtil.read(
141 "../sql/portal" + minimalSuffix + "/portal" + minimalSuffix +
142 "-oracle.sql"));
143 sb.append("\n\n");
144 sb.append(FileUtil.read("../sql/indexes/indexes-oracle.sql"));
145 sb.append("\n\n");
146 sb.append(FileUtil.read("../sql/sequences/sequences-oracle.sql"));
147 sb.append("\n");
148 sb.append("quit");
149
150 FileUtil.write(file, sb.toString());
151 }
152
153 protected String getServerName() {
154 return "oracle";
155 }
156
157 protected String[] getTemplate() {
158 return _ORACLE;
159 }
160
161 protected String reword(String data) throws IOException {
162 BufferedReader br = new BufferedReader(new StringReader(data));
163
164 StringBuilder sb = new StringBuilder();
165
166 String line = null;
167
168 while ((line = br.readLine()) != null) {
169 if (line.startsWith(ALTER_COLUMN_TYPE)) {
170 String[] template = buildColumnTypeTokens(line);
171
172 line = StringUtil.replace(
173 "alter table @table@ modify @old-column@ @type@;",
174 REWORD_TEMPLATE, template);
175 }
176 else 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
185 sb.append(line);
186 sb.append("\n");
187 }
188
189 br.close();
190
191 return sb.toString();
192 }
193
194 private void _convertToOracleCSV(String line, StringBuilder sb) {
195 int x = line.indexOf("values (");
196 int y = line.lastIndexOf(");");
197
198 line = line.substring(x + 8, y);
199
200 line = StringUtil.replace(line, "sysdate, ", "20050101, ");
201
202 sb.append(line);
203 sb.append("\n");
204 }
205
206 private String _preBuildSQL(String template) throws IOException {
207 template = convertTimestamp(template);
208 template = replaceTemplate(template, getTemplate());
209
210 template = reword(template);
211 template = StringUtil.replace(
212 template,
213 new String[] {"\\\\", "\\'", "\\\""},
214 new String[] {"\\", "''", "\""});
215
216 return template;
217 }
218
219 private String _postBuildSQL(String template) throws IOException {
220 template = removeLongInserts(template);
221 template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
222
223 return template;
224 }
225
226 private static String[] _ORACLE = {
227 "--", "1", "0",
228 "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
229 " blob", " number(1, 0)", " timestamp",
230 " number(30,20)", " number(30,0)", " number(30,0)",
231 " varchar2(4000)", " clob", " varchar2",
232 "", "commit"
233 };
234
235 private static OracleUtil _instance = new OracleUtil();
236
237 }