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