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