001: /**
002: * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
003: *
004: * Permission is hereby granted, free of charge, to any person obtaining a copy
005: * of this software and associated documentation files (the "Software"), to deal
006: * in the Software without restriction, including without limitation the rights
007: * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
008: * copies of the Software, and to permit persons to whom the Software is
009: * furnished to do so, subject to the following conditions:
010: *
011: * The above copyright notice and this permission notice shall be included in
012: * all copies or substantial portions of the Software.
013: *
014: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
015: * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
016: * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
017: * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
018: * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
019: * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
020: * SOFTWARE.
021: */package com.liferay.portal.tools.sql;
022:
023: import com.liferay.portal.kernel.util.StringMaker;
024: import com.liferay.portal.kernel.util.StringPool;
025: import com.liferay.portal.kernel.util.StringUtil;
026: import com.liferay.portal.spring.hibernate.HibernateUtil;
027: import com.liferay.portal.velocity.VelocityUtil;
028: import com.liferay.util.FileUtil;
029: import com.liferay.util.SimpleCounter;
030: import com.liferay.util.dao.DataAccess;
031:
032: import java.io.BufferedReader;
033: import java.io.File;
034: import java.io.FileReader;
035: import java.io.IOException;
036: import java.io.InputStream;
037: import java.io.StringReader;
038:
039: import java.sql.Connection;
040: import java.sql.SQLException;
041: import java.sql.Statement;
042:
043: import java.util.HashMap;
044: import java.util.Map;
045:
046: import org.apache.commons.logging.Log;
047: import org.apache.commons.logging.LogFactory;
048:
049: import org.hibernate.dialect.DB2Dialect;
050: import org.hibernate.dialect.DerbyDialect;
051: import org.hibernate.dialect.Dialect;
052: import org.hibernate.dialect.FirebirdDialect;
053: import org.hibernate.dialect.HSQLDialect;
054: import org.hibernate.dialect.InformixDialect;
055: import org.hibernate.dialect.InterbaseDialect;
056: import org.hibernate.dialect.JDataStoreDialect;
057: import org.hibernate.dialect.MySQLDialect;
058: import org.hibernate.dialect.Oracle10gDialect;
059: import org.hibernate.dialect.Oracle8iDialect;
060: import org.hibernate.dialect.Oracle9Dialect;
061: import org.hibernate.dialect.Oracle9iDialect;
062: import org.hibernate.dialect.OracleDialect;
063: import org.hibernate.dialect.PostgreSQLDialect;
064: import org.hibernate.dialect.SAPDBDialect;
065: import org.hibernate.dialect.SQLServerDialect;
066: import org.hibernate.dialect.SybaseDialect;
067:
068: /**
069: * <a href="DBUtil.java.html"><b><i>View Source</i></b></a>
070: *
071: * @author Alexander Chow
072: *
073: */
074: public abstract class DBUtil {
075:
076: public static final String DB_TYPE_DB2 = "db2";
077:
078: public static final String DB_TYPE_DERBY = "derby";
079:
080: public static final String DB_TYPE_FIREBIRD = "firebird";
081:
082: public static final String DB_TYPE_HYPERSONIC = "hypersonic";
083:
084: public static final String DB_TYPE_INFORMIX = "informix";
085:
086: public static final String DB_TYPE_INTERBASE = "interbase";
087:
088: public static final String DB_TYPE_JDATASTORE = "jdatastore";
089:
090: public static final String DB_TYPE_MYSQL = "mysql";
091:
092: public static final String DB_TYPE_ORACLE = "oracle";
093:
094: public static final String DB_TYPE_POSTGRESQL = "postgresql";
095:
096: public static final String DB_TYPE_SAP = "sap";
097:
098: public static final String DB_TYPE_SQLSERVER = "sqlserver";
099:
100: public static final String DB_TYPE_SYBASE = "sybase";
101:
102: public static final String[] DB_TYPE_ALL = { DB_TYPE_DB2,
103: DB_TYPE_DERBY, DB_TYPE_FIREBIRD, DB_TYPE_HYPERSONIC,
104: DB_TYPE_INFORMIX, DB_TYPE_INTERBASE, DB_TYPE_JDATASTORE,
105: DB_TYPE_MYSQL, DB_TYPE_ORACLE, DB_TYPE_POSTGRESQL,
106: DB_TYPE_SAP, DB_TYPE_SQLSERVER, DB_TYPE_SYBASE };
107:
108: public static DBUtil getInstance() {
109: if (_dbUtil != null) {
110: return _dbUtil;
111: }
112:
113: Dialect dialect = HibernateUtil.getWrappedDialect();
114:
115: if (dialect instanceof DB2Dialect) {
116: if (dialect instanceof DerbyDialect) {
117: _dbUtil = DerbyUtil.getInstance();
118: } else {
119: _dbUtil = DB2Util.getInstance();
120: }
121: } else if (dialect instanceof HSQLDialect) {
122: _dbUtil = HypersonicUtil.getInstance();
123: } else if (dialect instanceof InformixDialect) {
124: _dbUtil = InformixUtil.getInstance();
125: } else if (dialect instanceof InterbaseDialect) {
126: if (dialect instanceof FirebirdDialect) {
127: _dbUtil = FirebirdUtil.getInstance();
128: } else {
129: _dbUtil = InterBaseUtil.getInstance();
130: }
131: } else if (dialect instanceof JDataStoreDialect) {
132: _dbUtil = JDataStoreUtil.getInstance();
133: } else if (dialect instanceof MySQLDialect) {
134: _dbUtil = MySQLUtil.getInstance();
135: } else if (dialect instanceof OracleDialect
136: || dialect instanceof Oracle8iDialect
137: || dialect instanceof Oracle9Dialect
138: || dialect instanceof Oracle9iDialect
139: || dialect instanceof Oracle10gDialect) {
140:
141: _dbUtil = OracleUtil.getInstance();
142: } else if (dialect instanceof PostgreSQLDialect) {
143: _dbUtil = PostgreSQLUtil.getInstance();
144: } else if (dialect instanceof SAPDBDialect) {
145: _dbUtil = SAPUtil.getInstance();
146: } else if (dialect instanceof SybaseDialect) {
147: if (dialect instanceof SQLServerDialect) {
148: _dbUtil = SQLServerUtil.getInstance();
149: } else {
150: _dbUtil = SybaseUtil.getInstance();
151: }
152: }
153:
154: return _dbUtil;
155: }
156:
157: public static DBUtil getInstance(String dbType) {
158: DBUtil dbUtil = null;
159:
160: if (dbType.equals(DB_TYPE_DB2)) {
161: dbUtil = DB2Util.getInstance();
162: } else if (dbType.equals(DB_TYPE_DERBY)) {
163: dbUtil = DerbyUtil.getInstance();
164: } else if (dbType.equals(DB_TYPE_FIREBIRD)) {
165: dbUtil = FirebirdUtil.getInstance();
166: } else if (dbType.equals(DB_TYPE_HYPERSONIC)) {
167: dbUtil = HypersonicUtil.getInstance();
168: } else if (dbType.equals(DB_TYPE_INFORMIX)) {
169: dbUtil = InformixUtil.getInstance();
170: } else if (dbType.equals(DB_TYPE_INTERBASE)) {
171: dbUtil = InterBaseUtil.getInstance();
172: } else if (dbType.equals(DB_TYPE_JDATASTORE)) {
173: dbUtil = JDataStoreUtil.getInstance();
174: } else if (dbType.equals(DB_TYPE_MYSQL)) {
175: dbUtil = MySQLUtil.getInstance();
176: } else if (dbType.equals(DB_TYPE_ORACLE)) {
177: dbUtil = OracleUtil.getInstance();
178: } else if (dbType.equals(DB_TYPE_POSTGRESQL)) {
179: dbUtil = PostgreSQLUtil.getInstance();
180: } else if (dbType.equals(DB_TYPE_SAP)) {
181: dbUtil = SAPUtil.getInstance();
182: } else if (dbType.equals(DB_TYPE_SQLSERVER)) {
183: dbUtil = SQLServerUtil.getInstance();
184: } else if (dbType.equals(DB_TYPE_SYBASE)) {
185: dbUtil = SybaseUtil.getInstance();
186: }
187:
188: return dbUtil;
189: }
190:
191: public void buildCreateFile(String databaseName) throws IOException {
192: buildCreateFile(databaseName, true);
193: buildCreateFile(databaseName, false);
194: }
195:
196: public abstract String buildSQL(String template) throws IOException;
197:
198: public void buildSQLFile(String fileName) throws IOException {
199: String template = buildTemplate(fileName);
200:
201: template = buildSQL(template);
202:
203: FileUtil.write("../sql/" + fileName + "/" + fileName + "-"
204: + getServerName() + ".sql", template);
205: }
206:
207: public void runSQL(String sql) throws IOException, SQLException {
208: runSQL(new String[] { sql });
209: }
210:
211: public void runSQL(String[] sqls) throws IOException, SQLException {
212:
213: Connection con = null;
214: Statement stmt = null;
215:
216: try {
217: con = HibernateUtil.getConnection();
218:
219: stmt = con.createStatement();
220:
221: for (int i = 0; i < sqls.length; i++) {
222: String sql = buildSQL(sqls[i]);
223:
224: sql = sql.trim();
225:
226: if (sql.endsWith(";")) {
227: sql = sql.substring(0, sql.length() - 1);
228: }
229:
230: if (sql.endsWith("go")) {
231: sql = sql.substring(0, sql.length() - 2);
232: }
233:
234: if (_log.isDebugEnabled()) {
235: _log.debug(sql);
236: }
237:
238: try {
239: stmt.executeUpdate(sql);
240: } catch (SQLException sqle) {
241: throw sqle;
242: }
243: }
244: } finally {
245: DataAccess.cleanUp(con, stmt);
246: }
247: }
248:
249: public void runSQLTemplate(String path) throws IOException,
250: SQLException {
251: runSQLTemplate(path, true);
252: }
253:
254: public void runSQLTemplate(String path, boolean failOnError)
255: throws IOException, SQLException {
256:
257: ClassLoader classLoader = getClass().getClassLoader();
258:
259: InputStream is = classLoader
260: .getResourceAsStream("com/liferay/portal/tools/sql/dependencies/"
261: + path);
262:
263: if (is == null) {
264: is = classLoader.getResourceAsStream(path);
265: }
266:
267: String template = StringUtil.read(is);
268:
269: is.close();
270:
271: boolean evaluate = path.endsWith(".vm");
272:
273: runSQLTemplateString(template, evaluate, failOnError);
274: }
275:
276: public void runSQLTemplateString(String template, boolean evaluate,
277: boolean failOnError) throws IOException, SQLException {
278:
279: if (evaluate) {
280: try {
281: template = evaluateVM(template);
282: } catch (Exception e) {
283: _log.error(e, e);
284: }
285: }
286:
287: StringMaker sm = new StringMaker();
288:
289: BufferedReader br = new BufferedReader(new StringReader(
290: template));
291:
292: String line = null;
293:
294: while ((line = br.readLine()) != null) {
295: if (!line.startsWith("##")) {
296: sm.append(line);
297:
298: if (line.endsWith(";")) {
299: String sql = sm.toString();
300:
301: sm = new StringMaker();
302:
303: try {
304: if (!sql.equals("COMMIT_TRANSACTION;")) {
305: runSQL(sql);
306: } else {
307: if (_log.isDebugEnabled()) {
308: _log.debug("Skip commit sql");
309: }
310: }
311: } catch (IOException ioe) {
312: if (failOnError) {
313: throw ioe;
314: } else if (_log.isWarnEnabled()) {
315: _log.warn(ioe.getMessage());
316: }
317: } catch (SQLException sqle) {
318: if (failOnError) {
319: throw sqle;
320: } else if (_log.isWarnEnabled()) {
321: _log.warn(sqle.getMessage());
322: }
323: }
324: }
325: }
326: }
327:
328: br.close();
329: }
330:
331: protected abstract void buildCreateFile(String databaseName,
332: boolean minimal) throws IOException;
333:
334: protected String[] buildColumnNameTokens(String line) {
335: String[] words = StringUtil.split(line, " ");
336:
337: if (words.length == 7) {
338: words[5] = "not null;";
339: }
340:
341: String[] template = { words[1], words[2], words[3], words[4],
342: words[5] };
343:
344: return template;
345: }
346:
347: protected String[] buildColumnTypeTokens(String line) {
348: String[] words = StringUtil.split(line, " ");
349:
350: String nullable = "";
351:
352: if (words.length == 6) {
353: nullable = "not null;";
354: } else if (words.length == 5) {
355: nullable = words[4];
356: } else if (words.length == 4) {
357: nullable = "not null;";
358:
359: if (words[3].endsWith(";")) {
360: words[3] = words[3].substring(0, words[3].length() - 1);
361: }
362: }
363:
364: String[] template = { words[1], words[2], "", words[3],
365: nullable };
366:
367: return template;
368: }
369:
370: protected String buildTemplate(String fileName) throws IOException {
371: File file = new File("../sql/" + fileName + ".sql");
372:
373: String template = FileUtil.read(file);
374:
375: if (fileName.equals("portal")
376: || fileName.equals("portal-minimal")
377: || fileName.equals("update-3.6.0-4.0.0")) {
378:
379: BufferedReader br = new BufferedReader(new StringReader(
380: template));
381:
382: StringMaker sm = new StringMaker();
383:
384: String line = null;
385:
386: while ((line = br.readLine()) != null) {
387: if (line.startsWith("@include ")) {
388: int pos = line.indexOf(" ");
389:
390: String includeFileName = line.substring(pos + 1,
391: line.length());
392:
393: File includeFile = new File("../sql/"
394: + includeFileName);
395:
396: if (!includeFile.exists()) {
397: continue;
398: }
399:
400: String include = FileUtil.read(includeFile);
401:
402: if (includeFileName.endsWith(".vm")) {
403: try {
404: include = evaluateVM(include);
405: } catch (Exception e) {
406: e.printStackTrace();
407: }
408: }
409:
410: include = convertTimestamp(include);
411: include = StringUtil.replace(include, TEMPLATE,
412: getTemplate());
413:
414: sm.append(include);
415: sm.append("\n\n");
416: } else {
417: sm.append(line);
418: sm.append("\n");
419: }
420: }
421:
422: br.close();
423:
424: template = sm.toString();
425: }
426:
427: if (fileName.equals("indexes") && (this instanceof SybaseUtil)) {
428: template = removeBooleanIndexes(template);
429: }
430:
431: return template;
432: }
433:
434: protected String convertTimestamp(String data) {
435: String s = null;
436:
437: if (this instanceof MySQLUtil) {
438: s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
439: } else {
440: s = data.replaceAll("SPECIFIC_TIMESTAMP_" + "\\d+",
441: "CURRENT_TIMESTAMP");
442: }
443:
444: return s;
445: }
446:
447: protected String evaluateVM(String template) throws Exception {
448: Map variables = new HashMap();
449:
450: variables.put("counter", new SimpleCounter());
451:
452: template = VelocityUtil.evaluate(template, variables);
453:
454: // Trim insert statements because it breaks MySQL Query Browser
455:
456: BufferedReader br = new BufferedReader(new StringReader(
457: template));
458:
459: StringMaker sm = new StringMaker();
460:
461: String line = null;
462:
463: while ((line = br.readLine()) != null) {
464: line = line.trim();
465:
466: sm.append(line);
467: sm.append("\n");
468: }
469:
470: br.close();
471:
472: template = sm.toString();
473: template = StringUtil.replace(template, "\n\n\n", "\n\n");
474:
475: return template;
476: }
477:
478: protected String getMinimalSuffix(boolean minimal) {
479: if (minimal) {
480: return "-minimal";
481: } else {
482: return StringPool.BLANK;
483: }
484: }
485:
486: protected abstract String getServerName();
487:
488: protected abstract String[] getTemplate();
489:
490: protected String readSQL(String fileName, String comments,
491: String eol) throws IOException {
492:
493: BufferedReader br = new BufferedReader(new FileReader(new File(
494: fileName)));
495:
496: StringMaker sm = new StringMaker();
497:
498: String line = null;
499:
500: while ((line = br.readLine()) != null) {
501: if (!line.startsWith(comments)) {
502: line = StringUtil.replace(line, new String[] { "\n",
503: "\t" }, new String[] { "", "" });
504:
505: if (line.endsWith(";")) {
506: sm.append(line.substring(0, line.length() - 1));
507: sm.append(eol);
508: } else {
509: sm.append(line);
510: }
511: }
512: }
513:
514: br.close();
515:
516: return sm.toString();
517: }
518:
519: protected String removeBooleanIndexes(String data)
520: throws IOException {
521: String portalData = FileUtil.read("../sql/portal-tables.sql");
522:
523: BufferedReader br = new BufferedReader(new StringReader(data));
524:
525: StringMaker sm = new StringMaker();
526:
527: String line = null;
528:
529: while ((line = br.readLine()) != null) {
530: boolean append = true;
531:
532: int x = line.indexOf(" on ");
533:
534: if (x != -1) {
535: int y = line.indexOf(" (", x);
536:
537: String table = line.substring(x + 4, y);
538:
539: x = y + 2;
540: y = line.indexOf(")", x);
541:
542: String[] columns = StringUtil.split(line
543: .substring(x, y));
544:
545: x = portalData.indexOf("create table " + table + " (");
546: y = portalData.indexOf(");", x);
547:
548: String portalTableData = portalData.substring(x, y);
549:
550: for (int i = 0; i < columns.length; i++) {
551: if (portalTableData.indexOf(columns[i].trim()
552: + " BOOLEAN") != -1) {
553:
554: append = false;
555:
556: break;
557: }
558: }
559: }
560:
561: if (append) {
562: sm.append(line);
563: sm.append("\n");
564: }
565: }
566:
567: br.close();
568:
569: return sm.toString();
570: }
571:
572: protected String removeInserts(String data) throws IOException {
573: BufferedReader br = new BufferedReader(new StringReader(data));
574:
575: StringMaker sm = new StringMaker();
576:
577: String line = null;
578:
579: while ((line = br.readLine()) != null) {
580: if (!line.startsWith("insert into ")
581: && !line.startsWith("update ")) {
582:
583: sm.append(line);
584: sm.append("\n");
585: }
586: }
587:
588: br.close();
589:
590: return sm.toString();
591: }
592:
593: protected String removeLongInserts(String data) throws IOException {
594: BufferedReader br = new BufferedReader(new StringReader(data));
595:
596: StringMaker sm = new StringMaker();
597:
598: String line = null;
599:
600: while ((line = br.readLine()) != null) {
601: if (!line.startsWith("insert into Image (")
602: && !line.startsWith("insert into JournalArticle (")
603: && !line
604: .startsWith("insert into JournalStructure (")
605: && !line
606: .startsWith("insert into JournalTemplate (")) {
607:
608: sm.append(line);
609: sm.append("\n");
610: }
611: }
612:
613: br.close();
614:
615: return sm.toString();
616: }
617:
618: protected String removeNull(String content) {
619: content = StringUtil.replace(content, " not null", " not_null");
620: content = StringUtil.replace(content, " null", "");
621: content = StringUtil.replace(content, " not_null", " not null");
622:
623: return content;
624: }
625:
626: protected abstract String reword(String data) throws IOException;
627:
628: protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
629:
630: protected static String ALTER_COLUMN_NAME = "alter_column_name ";
631:
632: protected static String DROP_PRIMARY_KEY = "drop primary key";
633:
634: protected static String[] REWORD_TEMPLATE = { "@table@",
635: "@old-column@", "@new-column@", "@type@", "@nullable@" };
636:
637: protected static String[] TEMPLATE = { "##", "TRUE", "FALSE",
638: "'01/01/1970'", "CURRENT_TIMESTAMP", " BOOLEAN", " DATE",
639: " DOUBLE", " INTEGER", " LONG", " STRING", " TEXT",
640: " VARCHAR", " IDENTITY", "COMMIT_TRANSACTION" };
641:
642: private static Log _log = LogFactory.getLog(DBUtil.class);
643:
644: private static DBUtil _dbUtil;
645:
646: }
|