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