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