001    /**
002     * Copyright (c) 2000-2013 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.dao.db;
016    
017    import com.liferay.counter.service.CounterLocalServiceUtil;
018    import com.liferay.portal.dao.orm.common.SQLTransformer;
019    import com.liferay.portal.kernel.dao.db.DB;
020    import com.liferay.portal.kernel.dao.db.Index;
021    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
022    import com.liferay.portal.kernel.exception.SystemException;
023    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
024    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
025    import com.liferay.portal.kernel.log.Log;
026    import com.liferay.portal.kernel.log.LogFactoryUtil;
027    import com.liferay.portal.kernel.util.FileUtil;
028    import com.liferay.portal.kernel.util.GetterUtil;
029    import com.liferay.portal.kernel.util.PropertiesUtil;
030    import com.liferay.portal.kernel.util.StringBundler;
031    import com.liferay.portal.kernel.util.StringPool;
032    import com.liferay.portal.kernel.util.StringUtil;
033    import com.liferay.portal.kernel.util.Validator;
034    import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
035    import com.liferay.portal.util.ClassLoaderUtil;
036    import com.liferay.portal.velocity.VelocityUtil;
037    import com.liferay.util.SimpleCounter;
038    
039    import java.io.File;
040    import java.io.FileReader;
041    import java.io.IOException;
042    import java.io.InputStream;
043    
044    import java.sql.Connection;
045    import java.sql.SQLException;
046    import java.sql.Statement;
047    
048    import java.util.Collections;
049    import java.util.Enumeration;
050    import java.util.HashMap;
051    import java.util.HashSet;
052    import java.util.List;
053    import java.util.Map;
054    import java.util.Properties;
055    import java.util.Set;
056    import java.util.regex.Matcher;
057    import java.util.regex.Pattern;
058    
059    import javax.naming.NamingException;
060    
061    /**
062     * @author Alexander Chow
063     * @author Ganesh Ram
064     * @author Brian Wing Shun Chan
065     * @author Daniel Kocsis
066     */
067    public abstract class BaseDB implements DB {
068    
069            @Override
070            public void addIndexes(
071                            Connection con, String indexesSQL, Set<String> validIndexNames)
072                    throws IOException {
073    
074                    if (_log.isInfoEnabled()) {
075                            _log.info("Adding indexes");
076                    }
077    
078                    UnsyncBufferedReader bufferedReader = new UnsyncBufferedReader(
079                            new UnsyncStringReader(indexesSQL));
080    
081                    String sql = null;
082    
083                    while ((sql = bufferedReader.readLine()) != null) {
084                            if (Validator.isNull(sql)) {
085                                    continue;
086                            }
087    
088                            int y = sql.indexOf(" on ");
089                            int x = sql.lastIndexOf(" ", y - 1);
090    
091                            String indexName = sql.substring(x + 1, y);
092    
093                            if (validIndexNames.contains(indexName)) {
094                                    continue;
095                            }
096    
097                            if (_log.isInfoEnabled()) {
098                                    _log.info(sql);
099                            }
100    
101                            try {
102                                    runSQL(con, sql);
103                            }
104                            catch (Exception e) {
105                                    if (_log.isWarnEnabled()) {
106                                            _log.warn(e.getMessage() + ": " + sql);
107                                    }
108                            }
109                    }
110            }
111    
112            @Override
113            public void buildCreateFile(String sqlDir, String databaseName)
114                    throws IOException {
115    
116                    buildCreateFile(sqlDir, databaseName, BARE);
117                    buildCreateFile(sqlDir, databaseName, MINIMAL);
118                    buildCreateFile(sqlDir, databaseName, POPULATED);
119                    buildCreateFile(sqlDir, databaseName, SHARDED);
120            }
121    
122            @Override
123            public void buildCreateFile(
124                            String sqlDir, String databaseName, int population)
125                    throws IOException {
126    
127                    String suffix = getSuffix(population);
128    
129                    File file = new File(
130                            sqlDir + "/create" + suffix + "/create" + suffix + "-" +
131                                    getServerName() + ".sql");
132    
133                    if (population != SHARDED) {
134                            String content = buildCreateFileContent(
135                                    sqlDir, databaseName, population);
136    
137                            if (content != null) {
138                                    FileUtil.write(file, content);
139                            }
140                    }
141                    else {
142                            String content = buildCreateFileContent(
143                                    sqlDir, databaseName, MINIMAL);
144    
145                            if (content != null) {
146                                    FileUtil.write(file, content);
147                            }
148    
149                            content = buildCreateFileContent(
150                                    sqlDir, databaseName + "1", MINIMAL);
151    
152                            if (content != null) {
153                                    FileUtil.write(file, content, false, true);
154                            }
155    
156                            content = buildCreateFileContent(
157                                    sqlDir, databaseName + "2", MINIMAL);
158    
159                            if (content != null) {
160                                    FileUtil.write(file, content, false, true);
161                            }
162                    }
163            }
164    
165            @Override
166            public abstract String buildSQL(String template) throws IOException;
167    
168            @Override
169            public void buildSQLFile(String sqlDir, String fileName)
170                    throws IOException {
171    
172                    String template = buildTemplate(sqlDir, fileName);
173    
174                    if (Validator.isNull(template)) {
175                            return;
176                    }
177    
178                    template = buildSQL(template);
179    
180                    FileUtil.write(
181                            sqlDir + "/" + fileName + "/" + fileName + "-" + getServerName() +
182                                    ".sql",
183                            template);
184            }
185    
186            @Override
187            @SuppressWarnings("unused")
188            public List<Index> getIndexes(Connection con) throws SQLException {
189                    return Collections.emptyList();
190            }
191    
192            @Override
193            public String getTemplateFalse() {
194                    return getTemplate()[2];
195            }
196    
197            @Override
198            public String getTemplateTrue() {
199                    return getTemplate()[1];
200            }
201    
202            @Override
203            public String getType() {
204                    return _type;
205            }
206    
207            @Override
208            public long increment() throws SystemException {
209                    return CounterLocalServiceUtil.increment();
210            }
211    
212            @Override
213            public long increment(String name) throws SystemException {
214                    return CounterLocalServiceUtil.increment(name);
215            }
216    
217            @Override
218            public boolean isSupportsAlterColumnName() {
219                    return _SUPPORTS_ALTER_COLUMN_NAME;
220            }
221    
222            @Override
223            public boolean isSupportsAlterColumnType() {
224                    return _SUPPORTS_ALTER_COLUMN_TYPE;
225            }
226    
227            @Override
228            public boolean isSupportsDateMilliseconds() {
229                    return _SUPPORTS_DATE_MILLISECONDS;
230            }
231    
232            @Override
233            public boolean isSupportsInlineDistinct() {
234                    return _SUPPORTS_INLINE_DISTINCT;
235            }
236    
237            @Override
238            public boolean isSupportsQueryingAfterException() {
239                    return _SUPPORTS_QUERYING_AFTER_EXCEPTION;
240            }
241    
242            @Override
243            public boolean isSupportsScrollableResults() {
244                    return _SUPPORTS_SCROLLABLE_RESULTS;
245            }
246    
247            @Override
248            public boolean isSupportsStringCaseSensitiveQuery() {
249                    return _supportsStringCaseSensitiveQuery;
250            }
251    
252            @Override
253            public boolean isSupportsUpdateWithInnerJoin() {
254                    return _SUPPORTS_UPDATE_WITH_INNER_JOIN;
255            }
256    
257            @Override
258            public void runSQL(Connection con, String sql)
259                    throws IOException, SQLException {
260    
261                    runSQL(con, new String[] {sql});
262            }
263    
264            @Override
265            public void runSQL(Connection con, String[] sqls)
266                    throws IOException, SQLException {
267    
268                    Statement s = null;
269    
270                    try {
271                            s = con.createStatement();
272    
273                            for (int i = 0; i < sqls.length; i++) {
274                                    String sql = buildSQL(sqls[i]);
275    
276                                    sql = SQLTransformer.transform(sql.trim());
277    
278                                    if (sql.endsWith(";")) {
279                                            sql = sql.substring(0, sql.length() - 1);
280                                    }
281    
282                                    if (sql.endsWith("go")) {
283                                            sql = sql.substring(0, sql.length() - 2);
284                                    }
285    
286                                    if (_log.isDebugEnabled()) {
287                                            _log.debug(sql);
288                                    }
289    
290                                    try {
291                                            s.executeUpdate(sql);
292                                    }
293                                    catch (SQLException sqle) {
294                                            handleSQLException(sql, sqle);
295                                    }
296                            }
297                    }
298                    finally {
299                            DataAccess.cleanUp(s);
300                    }
301            }
302    
303            @Override
304            public void runSQL(String sql) throws IOException, SQLException {
305                    runSQL(new String[] {sql});
306            }
307    
308            @Override
309            public void runSQL(String[] sqls) throws IOException, SQLException {
310                    Connection con = DataAccess.getConnection();
311    
312                    try {
313                            runSQL(con, sqls);
314                    }
315                    finally {
316                            DataAccess.cleanUp(con);
317                    }
318            }
319    
320            @Override
321            public void runSQLTemplate(String path)
322                    throws IOException, NamingException, SQLException {
323    
324                    runSQLTemplate(path, true);
325            }
326    
327            @Override
328            public void runSQLTemplate(String path, boolean failOnError)
329                    throws IOException, NamingException, SQLException {
330    
331                    ClassLoader classLoader = ClassLoaderUtil.getContextClassLoader();
332    
333                    InputStream is = classLoader.getResourceAsStream(
334                            "com/liferay/portal/tools/sql/dependencies/" + path);
335    
336                    if (is == null) {
337                            is = classLoader.getResourceAsStream(path);
338                    }
339    
340                    if (is == null) {
341                            _log.error("Invalid path " + path);
342    
343                            if (failOnError) {
344                                    throw new IOException("Invalid path " + path);
345                            }
346                            else {
347                                    return;
348                            }
349                    }
350    
351                    String template = StringUtil.read(is);
352    
353                    boolean evaluate = path.endsWith(".vm");
354    
355                    runSQLTemplateString(template, evaluate, failOnError);
356            }
357    
358            @Override
359            public void runSQLTemplateString(
360                            String template, boolean evaluate, boolean failOnError)
361                    throws IOException, NamingException, SQLException {
362    
363                    if (evaluate) {
364                            try {
365                                    template = evaluateVM(template);
366                            }
367                            catch (Exception e) {
368                                    _log.error(e, e);
369                            }
370                    }
371    
372                    StringBundler sb = new StringBundler();
373    
374                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
375                            new UnsyncStringReader(template));
376    
377                    String line = null;
378    
379                    while ((line = unsyncBufferedReader.readLine()) != null) {
380                            if (!line.startsWith("##")) {
381                                    if (line.startsWith("@include ")) {
382                                            int pos = line.indexOf(" ");
383    
384                                            String includeFileName = line.substring(pos + 1);
385    
386                                            ClassLoader classLoader =
387                                                    ClassLoaderUtil.getContextClassLoader();
388    
389                                            InputStream is = classLoader.getResourceAsStream(
390                                                    "com/liferay/portal/tools/sql/dependencies/" +
391                                                            includeFileName);
392    
393                                            if (is == null) {
394                                                    is = classLoader.getResourceAsStream(includeFileName);
395                                            }
396    
397                                            String include = StringUtil.read(is);
398    
399                                            if (includeFileName.endsWith(".vm")) {
400                                                    try {
401                                                            include = evaluateVM(include);
402                                                    }
403                                                    catch (Exception e) {
404                                                            _log.error(e, e);
405                                                    }
406                                            }
407    
408                                            include = convertTimestamp(include);
409                                            include = replaceTemplate(include, getTemplate());
410    
411                                            runSQLTemplateString(include, false, true);
412                                    }
413                                    else {
414                                            sb.append(line);
415    
416                                            if (line.endsWith(";")) {
417                                                    String sql = sb.toString();
418    
419                                                    sb.setIndex(0);
420    
421                                                    try {
422                                                            if (!sql.equals("COMMIT_TRANSACTION;")) {
423                                                                    runSQL(sql);
424                                                            }
425                                                            else {
426                                                                    if (_log.isDebugEnabled()) {
427                                                                            _log.debug("Skip commit sql");
428                                                                    }
429                                                            }
430                                                    }
431                                                    catch (IOException ioe) {
432                                                            if (failOnError) {
433                                                                    throw ioe;
434                                                            }
435                                                            else if (_log.isWarnEnabled()) {
436                                                                    _log.warn(ioe.getMessage());
437                                                            }
438                                                    }
439                                                    catch (SecurityException se) {
440                                                            if (failOnError) {
441                                                                    throw se;
442                                                            }
443                                                            else if (_log.isWarnEnabled()) {
444                                                                    _log.warn(se.getMessage());
445                                                            }
446                                                    }
447                                                    catch (SQLException sqle) {
448                                                            if (failOnError) {
449                                                                    throw sqle;
450                                                            }
451                                                            else if (_log.isWarnEnabled()) {
452                                                                    String message = GetterUtil.getString(
453                                                                            sqle.getMessage());
454    
455                                                                    if (!message.startsWith("Duplicate key name")) {
456                                                                            _log.warn(message + ": " + buildSQL(sql));
457                                                                    }
458    
459                                                                    if (message.startsWith("Duplicate entry") ||
460                                                                            message.startsWith(
461                                                                                    "Specified key was too long")) {
462    
463                                                                            _log.error(line);
464                                                                    }
465                                                            }
466                                                    }
467                                            }
468                                    }
469                            }
470                    }
471    
472                    unsyncBufferedReader.close();
473            }
474    
475            @Override
476            public void setSupportsStringCaseSensitiveQuery(
477                    boolean supportsStringCaseSensitiveQuery) {
478    
479                    if (_log.isInfoEnabled()) {
480                            if (supportsStringCaseSensitiveQuery) {
481                                    _log.info("Database supports case sensitive queries");
482                            }
483                            else {
484                                    _log.info("Database does not support case sensitive queries");
485                            }
486                    }
487    
488                    _supportsStringCaseSensitiveQuery = supportsStringCaseSensitiveQuery;
489            }
490    
491            @Override
492            public void updateIndexes(
493                            Connection con, String tablesSQL, String indexesSQL,
494                            String indexesProperties, boolean dropIndexes)
495                    throws IOException, SQLException {
496    
497                    List<Index> indexes = getIndexes(con);
498    
499                    Set<String> validIndexNames = null;
500    
501                    if (dropIndexes) {
502                            validIndexNames = dropIndexes(
503                                    con, tablesSQL, indexesSQL, indexesProperties, indexes);
504                    }
505                    else {
506                            validIndexNames = new HashSet<String>();
507    
508                            for (Index index : indexes) {
509                                    String indexName = StringUtil.toUpperCase(index.getIndexName());
510    
511                                    validIndexNames.add(indexName);
512                            }
513                    }
514    
515                    addIndexes(con, indexesSQL, validIndexNames);
516            }
517    
518            protected BaseDB(String type) {
519                    _type = type;
520    
521                    String[] actual = getTemplate();
522    
523                    for (int i = 0; i < TEMPLATE.length; i++) {
524                            _templateMap.put(TEMPLATE[i], actual[i]);
525                    }
526            }
527    
528            protected String[] buildColumnNameTokens(String line) {
529                    String[] words = StringUtil.split(line, ' ');
530    
531                    String nullable = "";
532    
533                    if (words.length == 7) {
534                            nullable = "not null;";
535                    }
536    
537                    String[] template = {
538                            words[1], words[2], words[3], words[4], nullable
539                    };
540    
541                    return template;
542            }
543    
544            protected String[] buildColumnTypeTokens(String line) {
545                    String[] words = StringUtil.split(line, ' ');
546    
547                    String nullable = "";
548    
549                    if (words.length == 6) {
550                            nullable = "not null;";
551                    }
552                    else if (words.length == 5) {
553                            nullable = words[4];
554                    }
555                    else if (words.length == 4) {
556                            nullable = "not null;";
557    
558                            if (words[3].endsWith(";")) {
559                                    words[3] = words[3].substring(0, words[3].length() - 1);
560                            }
561                    }
562    
563                    String[] template = {
564                            words[1], words[2], "", words[3], nullable
565                    };
566    
567                    return template;
568            }
569    
570            protected abstract String buildCreateFileContent(
571                            String sqlDir, String databaseName, int population)
572                    throws IOException;
573    
574            protected String[] buildTableNameTokens(String line) {
575                    String[] words = StringUtil.split(line, StringPool.SPACE);
576    
577                    return new String[] {words[1], words[2]};
578            }
579    
580            protected String buildTemplate(String sqlDir, String fileName)
581                    throws IOException {
582    
583                    String template = readFile(sqlDir + "/" + fileName + ".sql");
584    
585                    if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
586                            fileName.equals("update-5.0.1-5.1.0")) {
587    
588                            UnsyncBufferedReader unsyncBufferedReader =
589                                    new UnsyncBufferedReader(new UnsyncStringReader(template));
590    
591                            StringBundler sb = new StringBundler();
592    
593                            String line = null;
594    
595                            while ((line = unsyncBufferedReader.readLine()) != null) {
596                                    if (line.startsWith("@include ")) {
597                                            int pos = line.indexOf(" ");
598    
599                                            String includeFileName = line.substring(pos + 1);
600    
601                                            File includeFile = new File(sqlDir + "/" + includeFileName);
602    
603                                            if (!includeFile.exists()) {
604                                                    continue;
605                                            }
606    
607                                            String include = FileUtil.read(includeFile);
608    
609                                            if (includeFileName.endsWith(".vm")) {
610                                                    try {
611                                                            include = evaluateVM(include);
612                                                    }
613                                                    catch (Exception e) {
614                                                            _log.error(e, e);
615                                                    }
616                                            }
617    
618                                            include = convertTimestamp(include);
619                                            include = replaceTemplate(include, getTemplate());
620    
621                                            sb.append(include);
622                                            sb.append("\n\n");
623                                    }
624                                    else {
625                                            sb.append(line);
626                                            sb.append("\n");
627                                    }
628                            }
629    
630                            unsyncBufferedReader.close();
631    
632                            template = sb.toString();
633                    }
634    
635                    if (fileName.equals("indexes") && (this instanceof SybaseDB)) {
636                            template = removeBooleanIndexes(sqlDir, template);
637                    }
638    
639                    return template;
640            }
641    
642            protected String convertTimestamp(String data) {
643                    String s = null;
644    
645                    if (this instanceof MySQLDB) {
646                            s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
647                    }
648                    else {
649                            Matcher matcher = _timestampPattern.matcher(data);
650    
651                            s = matcher.replaceAll("CURRENT_TIMESTAMP");
652                    }
653    
654                    return s;
655            }
656    
657            protected Set<String> dropIndexes(
658                            Connection con, String tablesSQL, String indexesSQL,
659                            String indexesProperties, List<Index> indexes)
660                    throws IOException, SQLException {
661    
662                    if (_log.isInfoEnabled()) {
663                            _log.info("Dropping stale indexes");
664                    }
665    
666                    Set<String> validIndexNames = new HashSet<String>();
667    
668                    if (indexes.isEmpty()) {
669                            return validIndexNames;
670                    }
671    
672                    String tablesSQLLowerCase = StringUtil.toLowerCase(tablesSQL);
673                    String indexesSQLLowerCase = StringUtil.toLowerCase(indexesSQL);
674    
675                    Properties indexesPropertiesObj = PropertiesUtil.load(
676                            indexesProperties);
677    
678                    Enumeration<String> enu =
679                            (Enumeration<String>)indexesPropertiesObj.propertyNames();
680    
681                    while (enu.hasMoreElements()) {
682                            String key = enu.nextElement();
683    
684                            String value = indexesPropertiesObj.getProperty(key);
685    
686                            indexesPropertiesObj.setProperty(
687                                    StringUtil.toLowerCase(key), value);
688                    }
689    
690                    for (Index index : indexes) {
691                            String indexNameUpperCase = StringUtil.toUpperCase(
692                                    index.getIndexName());
693                            String indexNameLowerCase = StringUtil.toLowerCase(
694                                    indexNameUpperCase);
695                            String tableName = index.getTableName();
696                            String tableNameLowerCase = StringUtil.toLowerCase(tableName);
697                            boolean unique = index.isUnique();
698    
699                            validIndexNames.add(indexNameUpperCase);
700    
701                            if (indexesPropertiesObj.containsKey(indexNameLowerCase)) {
702                                    if (unique &&
703                                            indexesSQLLowerCase.contains(
704                                                    "create unique index " + indexNameLowerCase + " ")) {
705    
706                                            continue;
707                                    }
708    
709                                    if (!unique &&
710                                            indexesSQLLowerCase.contains(
711                                                    "create index " + indexNameLowerCase + " ")) {
712    
713                                            continue;
714                                    }
715                            }
716                            else if (!tablesSQLLowerCase.contains(
717                                                    "create table " + tableNameLowerCase + " (")) {
718    
719                                    continue;
720                            }
721    
722                            validIndexNames.remove(indexNameUpperCase);
723    
724                            String sql =
725                                    "drop index " + indexNameUpperCase + " on " + tableName;
726    
727                            if (_log.isInfoEnabled()) {
728                                    _log.info(sql);
729                            }
730    
731                            runSQL(con, sql);
732                    }
733    
734                    return validIndexNames;
735            }
736    
737            protected String evaluateVM(String template) throws Exception {
738                    Map<String, Object> variables = new HashMap<String, Object>();
739    
740                    variables.put("counter", new SimpleCounter());
741                    variables.put("portalUUIDUtil", PortalUUIDUtil.class);
742    
743                    ClassLoader classLoader = ClassLoaderUtil.getContextClassLoader();
744    
745                    try {
746                            ClassLoaderUtil.setContextClassLoader(
747                                    ClassLoaderUtil.getPortalClassLoader());
748    
749                            template = VelocityUtil.evaluate(template, variables);
750                    }
751                    finally {
752                            ClassLoaderUtil.setContextClassLoader(classLoader);
753                    }
754    
755                    // Trim insert statements because it breaks MySQL Query Browser
756    
757                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
758                            new UnsyncStringReader(template));
759    
760                    StringBundler sb = new StringBundler();
761    
762                    String line = null;
763    
764                    while ((line = unsyncBufferedReader.readLine()) != null) {
765                            line = line.trim();
766    
767                            sb.append(line);
768                            sb.append("\n");
769                    }
770    
771                    unsyncBufferedReader.close();
772    
773                    template = sb.toString();
774                    template = StringUtil.replace(template, "\n\n\n", "\n\n");
775    
776                    return template;
777            }
778    
779            protected String getCreateTablesContent(String sqlDir, String suffix)
780                    throws IOException {
781    
782                    StringBundler sb = new StringBundler(8);
783    
784                    sb.append(sqlDir);
785    
786                    if (!sqlDir.endsWith("/WEB-INF/sql")) {
787                            sb.append("/portal");
788                            sb.append(suffix);
789                            sb.append("/portal");
790                    }
791                    else {
792                            sb.append("/tables");
793                            sb.append(suffix);
794                            sb.append("/tables");
795                    }
796    
797                    sb.append(suffix);
798                    sb.append(StringPool.DASH);
799                    sb.append(getServerName());
800                    sb.append(".sql");
801    
802                    return readFile(sb.toString());
803            }
804    
805            protected abstract String getServerName();
806    
807            protected String getSuffix(int type) {
808                    if (type == BARE) {
809                            return "-bare";
810                    }
811                    else if (type == MINIMAL) {
812                            return "-minimal";
813                    }
814                    else if (type == SHARDED) {
815                            return "-sharded";
816                    }
817                    else {
818                            return StringPool.BLANK;
819                    }
820            }
821    
822            protected abstract String[] getTemplate();
823    
824            protected void handleSQLException(String sql, SQLException sqle)
825                    throws SQLException {
826    
827                    if (_log.isDebugEnabled()) {
828                            StringBundler sb = new StringBundler(18);
829    
830                            sb.append("SQL: ");
831                            sb.append(sql);
832                            sb.append("\nSQL state: ");
833                            sb.append(sqle.getSQLState());
834                            sb.append("\nVendor: ");
835                            sb.append(getType());
836                            sb.append("\nVendor error code: ");
837                            sb.append(sqle.getErrorCode());
838                            sb.append("\nVendor error message: ");
839                            sb.append(sqle.getMessage());
840    
841                            _log.debug(sb.toString());
842                    }
843    
844                    throw sqle;
845            }
846    
847            protected String readFile(String fileName) throws IOException {
848                    if (FileUtil.exists(fileName)) {
849                            return FileUtil.read(fileName);
850                    }
851                    else {
852                            return StringPool.BLANK;
853                    }
854            }
855    
856            protected String readSQL(String fileName, String comments, String eol)
857                    throws IOException {
858    
859                    if (!FileUtil.exists(fileName)) {
860                            return StringPool.BLANK;
861                    }
862    
863                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
864                            new FileReader(new File(fileName)));
865    
866                    StringBundler sb = new StringBundler();
867    
868                    String line = null;
869    
870                    while ((line = unsyncBufferedReader.readLine()) != null) {
871                            if (!line.startsWith(comments)) {
872                                    line = StringUtil.replace(
873                                            line, new String[] {"\n", "\t"}, new String[] {"", ""});
874    
875                                    if (line.endsWith(";")) {
876                                            sb.append(line.substring(0, line.length() - 1));
877                                            sb.append(eol);
878                                    }
879                                    else {
880                                            sb.append(line);
881                                    }
882                            }
883                    }
884    
885                    unsyncBufferedReader.close();
886    
887                    return sb.toString();
888            }
889    
890            protected String removeBooleanIndexes(String sqlDir, String data)
891                    throws IOException {
892    
893                    String portalData = readFile(sqlDir + "/portal-tables.sql");
894    
895                    if (Validator.isNull(portalData)) {
896                            return StringPool.BLANK;
897                    }
898    
899                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
900                            new UnsyncStringReader(data));
901    
902                    StringBundler sb = new StringBundler();
903    
904                    String line = null;
905    
906                    while ((line = unsyncBufferedReader.readLine()) != null) {
907                            boolean append = true;
908    
909                            int x = line.indexOf(" on ");
910    
911                            if (x != -1) {
912                                    int y = line.indexOf(" (", x);
913    
914                                    String table = line.substring(x + 4, y);
915    
916                                    x = y + 2;
917                                    y = line.indexOf(")", x);
918    
919                                    String[] columns = StringUtil.split(line.substring(x, y));
920    
921                                    x = portalData.indexOf("create table " + table + " (");
922                                    y = portalData.indexOf(");", x);
923    
924                                    String portalTableData = portalData.substring(x, y);
925    
926                                    for (int i = 0; i < columns.length; i++) {
927                                            if (portalTableData.contains(
928                                                            columns[i].trim() + " BOOLEAN")) {
929    
930                                                    append = false;
931    
932                                                    break;
933                                            }
934                                    }
935                            }
936    
937                            if (append) {
938                                    sb.append(line);
939                                    sb.append("\n");
940                            }
941                    }
942    
943                    unsyncBufferedReader.close();
944    
945                    return sb.toString();
946            }
947    
948            protected String removeInserts(String data) throws IOException {
949                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
950                            new UnsyncStringReader(data));
951    
952                    StringBundler sb = new StringBundler();
953    
954                    String line = null;
955    
956                    while ((line = unsyncBufferedReader.readLine()) != null) {
957                            if (!line.startsWith("insert into ") &&
958                                    !line.startsWith("update ")) {
959    
960                                    sb.append(line);
961                                    sb.append("\n");
962                            }
963                    }
964    
965                    unsyncBufferedReader.close();
966    
967                    return sb.toString();
968            }
969    
970            protected String removeLongInserts(String data) throws IOException {
971                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
972                            new UnsyncStringReader(data));
973    
974                    StringBundler sb = new StringBundler();
975    
976                    String line = null;
977    
978                    while ((line = unsyncBufferedReader.readLine()) != null) {
979                            if (!line.startsWith("insert into Image (") &&
980                                    !line.startsWith("insert into JournalArticle (") &&
981                                    !line.startsWith("insert into JournalStructure (") &&
982                                    !line.startsWith("insert into JournalTemplate (")) {
983    
984                                    sb.append(line);
985                                    sb.append("\n");
986                            }
987                    }
988    
989                    unsyncBufferedReader.close();
990    
991                    return sb.toString();
992            }
993    
994            protected String removeNull(String content) {
995                    content = StringUtil.replace(content, " = null", " = NULL");
996                    content = StringUtil.replace(content, " is null", " IS NULL");
997                    content = StringUtil.replace(content, " not null", " not_null");
998                    content = StringUtil.replace(content, " null", "");
999                    content = StringUtil.replace(content, " not_null", " not null");
1000    
1001                    return content;
1002            }
1003    
1004            protected String replaceTemplate(String template, String[] actual) {
1005                    if ((template == null) || (TEMPLATE == null) || (actual == null)) {
1006                            return null;
1007                    }
1008    
1009                    if (TEMPLATE.length != actual.length) {
1010                            return template;
1011                    }
1012    
1013                    StringBundler sb = null;
1014    
1015                    int endIndex = 0;
1016    
1017                    Matcher matcher = _templatePattern.matcher(template);
1018    
1019                    while (matcher.find()) {
1020                            int startIndex = matcher.start();
1021    
1022                            if (sb == null) {
1023                                    sb = new StringBundler();
1024                            }
1025    
1026                            sb.append(template.substring(endIndex, startIndex));
1027    
1028                            endIndex = matcher.end();
1029    
1030                            String matched = template.substring(startIndex, endIndex);
1031    
1032                            sb.append(_templateMap.get(matched));
1033                    }
1034    
1035                    if (sb == null) {
1036                            return template;
1037                    }
1038    
1039                    if (template.length() > endIndex) {
1040                            sb.append(template.substring(endIndex));
1041                    }
1042    
1043                    return sb.toString();
1044            }
1045    
1046            protected abstract String reword(String data) throws IOException;
1047    
1048            protected static final String ALTER_COLUMN_NAME = "alter_column_name ";
1049    
1050            protected static final String ALTER_COLUMN_TYPE = "alter_column_type ";
1051    
1052            protected static final String ALTER_TABLE_NAME = "alter_table_name ";
1053    
1054            protected static final String DROP_INDEX = "drop index";
1055    
1056            protected static final String DROP_PRIMARY_KEY = "drop primary key";
1057    
1058            protected static final String[] RENAME_TABLE_TEMPLATE = {
1059                    "@old-table@", "@new-table@"
1060            };
1061    
1062            protected static final String[] REWORD_TEMPLATE = {
1063                    "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
1064            };
1065    
1066            protected static final String[] TEMPLATE = {
1067                    "##", "TRUE", "FALSE", "'01/01/1970'", "CURRENT_TIMESTAMP", " BLOB",
1068                    " SBLOB", " BOOLEAN", " DATE", " DOUBLE", " INTEGER", " LONG",
1069                    " STRING", " TEXT", " VARCHAR", " IDENTITY", "COMMIT_TRANSACTION"
1070            };
1071    
1072            private static final boolean _SUPPORTS_ALTER_COLUMN_NAME = true;
1073    
1074            private static final boolean _SUPPORTS_ALTER_COLUMN_TYPE = true;
1075    
1076            private static final boolean _SUPPORTS_DATE_MILLISECONDS = true;
1077    
1078            private static final boolean _SUPPORTS_INLINE_DISTINCT = true;
1079    
1080            private static final boolean _SUPPORTS_QUERYING_AFTER_EXCEPTION = true;
1081    
1082            private static final boolean _SUPPORTS_SCROLLABLE_RESULTS = true;
1083    
1084            private static final boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN = true;
1085    
1086            private static Log _log = LogFactoryUtil.getLog(BaseDB.class);
1087    
1088            private static Pattern _templatePattern;
1089            private static Pattern _timestampPattern = Pattern.compile(
1090                    "SPECIFIC_TIMESTAMP_\\d+");
1091    
1092            private boolean _supportsStringCaseSensitiveQuery;
1093            private Map<String, String> _templateMap = new HashMap<String, String>();
1094            private String _type;
1095    
1096            static {
1097                    StringBundler sb = new StringBundler(TEMPLATE.length * 3 - 3);
1098    
1099                    for (int i = 0; i < TEMPLATE.length; i++) {
1100                            String variable = TEMPLATE[i];
1101    
1102                            if (variable.equals("##") || variable.equals("'01/01/1970'")) {
1103                                    sb.append(variable);
1104                            }
1105                            else {
1106                                    sb.append(variable);
1107                                    sb.append("\\b");
1108                            }
1109    
1110                            if (i < (TEMPLATE.length - 1)) {
1111                                    sb.append(StringPool.PIPE);
1112                            }
1113                    }
1114    
1115                    _templatePattern = Pattern.compile(sb.toString());
1116            }
1117    
1118    }