001    /**
002     * Copyright (c) 2000-present 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.db.IndexMetadata;
022    import com.liferay.portal.kernel.dao.db.IndexMetadataFactoryUtil;
023    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
024    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
025    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
026    import com.liferay.portal.kernel.log.Log;
027    import com.liferay.portal.kernel.log.LogFactoryUtil;
028    import com.liferay.portal.kernel.util.FileUtil;
029    import com.liferay.portal.kernel.util.GetterUtil;
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.HashMap;
050    import java.util.HashSet;
051    import java.util.List;
052    import java.util.Map;
053    import java.util.Set;
054    import java.util.regex.Matcher;
055    import java.util.regex.Pattern;
056    
057    import javax.naming.NamingException;
058    
059    /**
060     * @author Alexander Chow
061     * @author Ganesh Ram
062     * @author Brian Wing Shun Chan
063     * @author Daniel Kocsis
064     */
065    public abstract class BaseDB implements DB {
066    
067            @Override
068            public void addIndexes(
069                            Connection con, String indexesSQL, Set<String> validIndexNames)
070                    throws IOException {
071    
072                    if (_log.isInfoEnabled()) {
073                            _log.info("Adding indexes");
074                    }
075    
076                    UnsyncBufferedReader bufferedReader = new UnsyncBufferedReader(
077                            new UnsyncStringReader(indexesSQL));
078    
079                    String sql = null;
080    
081                    while ((sql = bufferedReader.readLine()) != null) {
082                            if (Validator.isNull(sql)) {
083                                    continue;
084                            }
085    
086                            int y = sql.indexOf(" on ");
087                            int x = sql.lastIndexOf(" ", y - 1);
088    
089                            String indexName = sql.substring(x + 1, y);
090    
091                            if (validIndexNames.contains(indexName)) {
092                                    continue;
093                            }
094    
095                            if (_log.isInfoEnabled()) {
096                                    _log.info(sql);
097                            }
098    
099                            try {
100                                    runSQL(con, sql);
101                            }
102                            catch (Exception e) {
103                                    if (_log.isWarnEnabled()) {
104                                            _log.warn(e.getMessage() + ": " + sql);
105                                    }
106                            }
107                    }
108            }
109    
110            @Override
111            public void buildCreateFile(String sqlDir, String databaseName)
112                    throws IOException {
113    
114                    buildCreateFile(sqlDir, databaseName, BARE);
115                    buildCreateFile(sqlDir, databaseName, DEFAULT);
116                    buildCreateFile(sqlDir, databaseName, SHARDED);
117            }
118    
119            @Override
120            public void buildCreateFile(
121                            String sqlDir, String databaseName, int population)
122                    throws IOException {
123    
124                    String suffix = getSuffix(population);
125    
126                    File file = new File(
127                            sqlDir + "/create" + suffix + "/create" + suffix + "-" +
128                                    getServerName() + ".sql");
129    
130                    if (population != SHARDED) {
131                            String content = buildCreateFileContent(
132                                    sqlDir, databaseName, population);
133    
134                            if (content != null) {
135                                    FileUtil.write(file, content);
136                            }
137                    }
138                    else {
139                            String content = buildCreateFileContent(
140                                    sqlDir, databaseName, DEFAULT);
141    
142                            if (content != null) {
143                                    FileUtil.write(file, content);
144                            }
145    
146                            content = buildCreateFileContent(
147                                    sqlDir, databaseName + "1", DEFAULT);
148    
149                            if (content != null) {
150                                    FileUtil.write(file, content, false, true);
151                            }
152    
153                            content = buildCreateFileContent(
154                                    sqlDir, databaseName + "2", DEFAULT);
155    
156                            if (content != null) {
157                                    FileUtil.write(file, content, false, true);
158                            }
159                    }
160            }
161    
162            @Override
163            public abstract String buildSQL(String template) throws IOException;
164    
165            @Override
166            public void buildSQLFile(String sqlDir, String fileName)
167                    throws IOException {
168    
169                    String template = buildTemplate(sqlDir, fileName);
170    
171                    if (Validator.isNull(template)) {
172                            return;
173                    }
174    
175                    template = buildSQL(template);
176    
177                    FileUtil.write(
178                            sqlDir + "/" + fileName + "/" + fileName + "-" + getServerName() +
179                                    ".sql",
180                            template);
181            }
182    
183            @Override
184            @SuppressWarnings("unused")
185            public List<Index> getIndexes(Connection con) throws SQLException {
186                    return Collections.emptyList();
187            }
188    
189            @Override
190            public String getTemplateFalse() {
191                    return getTemplate()[2];
192            }
193    
194            @Override
195            public String getTemplateTrue() {
196                    return getTemplate()[1];
197            }
198    
199            @Override
200            public String getType() {
201                    return _type;
202            }
203    
204            @Override
205            public long increment() {
206                    return CounterLocalServiceUtil.increment();
207            }
208    
209            @Override
210            public long increment(String name) {
211                    return CounterLocalServiceUtil.increment(name);
212            }
213    
214            @Override
215            public boolean isSupportsAlterColumnName() {
216                    return _SUPPORTS_ALTER_COLUMN_NAME;
217            }
218    
219            @Override
220            public boolean isSupportsAlterColumnType() {
221                    return _SUPPORTS_ALTER_COLUMN_TYPE;
222            }
223    
224            @Override
225            public boolean isSupportsDateMilliseconds() {
226                    return _SUPPORTS_DATE_MILLISECONDS;
227            }
228    
229            @Override
230            public boolean isSupportsInlineDistinct() {
231                    return _SUPPORTS_INLINE_DISTINCT;
232            }
233    
234            @Override
235            public boolean isSupportsQueryingAfterException() {
236                    return _SUPPORTS_QUERYING_AFTER_EXCEPTION;
237            }
238    
239            @Override
240            public boolean isSupportsScrollableResults() {
241                    return _SUPPORTS_SCROLLABLE_RESULTS;
242            }
243    
244            @Override
245            public boolean isSupportsStringCaseSensitiveQuery() {
246                    return _supportsStringCaseSensitiveQuery;
247            }
248    
249            @Override
250            public boolean isSupportsUpdateWithInnerJoin() {
251                    return _SUPPORTS_UPDATE_WITH_INNER_JOIN;
252            }
253    
254            @Override
255            public void runSQL(Connection con, String sql)
256                    throws IOException, SQLException {
257    
258                    runSQL(con, new String[] {sql});
259            }
260    
261            @Override
262            public void runSQL(Connection con, String[] sqls)
263                    throws IOException, SQLException {
264    
265                    Statement s = null;
266    
267                    try {
268                            s = con.createStatement();
269    
270                            for (int i = 0; i < sqls.length; i++) {
271                                    String sql = buildSQL(sqls[i]);
272    
273                                    sql = SQLTransformer.transform(sql.trim());
274    
275                                    if (sql.endsWith(";")) {
276                                            sql = sql.substring(0, sql.length() - 1);
277                                    }
278    
279                                    if (sql.endsWith("go")) {
280                                            sql = sql.substring(0, sql.length() - 2);
281                                    }
282    
283                                    if (_log.isDebugEnabled()) {
284                                            _log.debug(sql);
285                                    }
286    
287                                    try {
288                                            s.executeUpdate(sql);
289                                    }
290                                    catch (SQLException sqle) {
291                                            handleSQLException(sql, sqle);
292                                    }
293                            }
294                    }
295                    finally {
296                            DataAccess.cleanUp(s);
297                    }
298            }
299    
300            @Override
301            public void runSQL(String sql) throws IOException, SQLException {
302                    runSQL(new String[] {sql});
303            }
304    
305            @Override
306            public void runSQL(String[] sqls) throws IOException, SQLException {
307                    Connection con = DataAccess.getConnection();
308    
309                    try {
310                            runSQL(con, sqls);
311                    }
312                    finally {
313                            DataAccess.cleanUp(con);
314                    }
315            }
316    
317            @Override
318            public void runSQLTemplate(String path)
319                    throws IOException, NamingException, SQLException {
320    
321                    runSQLTemplate(path, true);
322            }
323    
324            @Override
325            public void runSQLTemplate(String path, boolean failOnError)
326                    throws IOException, NamingException, SQLException {
327    
328                    ClassLoader classLoader = ClassLoaderUtil.getContextClassLoader();
329    
330                    InputStream is = classLoader.getResourceAsStream(
331                            "com/liferay/portal/tools/sql/dependencies/" + path);
332    
333                    if (is == null) {
334                            is = classLoader.getResourceAsStream(path);
335                    }
336    
337                    if (is == null) {
338                            _log.error("Invalid path " + path);
339    
340                            if (failOnError) {
341                                    throw new IOException("Invalid path " + path);
342                            }
343                            else {
344                                    return;
345                            }
346                    }
347    
348                    String template = StringUtil.read(is);
349    
350                    boolean evaluate = path.endsWith(".vm");
351    
352                    runSQLTemplateString(template, evaluate, failOnError);
353            }
354    
355            @Override
356            public void runSQLTemplateString(
357                            String template, boolean evaluate, boolean failOnError)
358                    throws IOException, NamingException, SQLException {
359    
360                    if (evaluate) {
361                            try {
362                                    template = evaluateVM(template);
363                            }
364                            catch (Exception e) {
365                                    _log.error(e, e);
366                            }
367                    }
368    
369                    try (UnsyncBufferedReader unsyncBufferedReader =
370                                    new UnsyncBufferedReader(new UnsyncStringReader(template))) {
371    
372                            StringBundler sb = new StringBundler();
373    
374                            String line = null;
375    
376                            while ((line = unsyncBufferedReader.readLine()) != null) {
377                                    if (line.startsWith("##")) {
378                                            continue;
379                                    }
380    
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    
452                                                            String message = GetterUtil.getString(
453                                                                    sqle.getMessage());
454    
455                                                            if (!message.startsWith("Duplicate key name") &&
456                                                                    _log.isWarnEnabled()) {
457    
458                                                                    _log.warn(message + ": " + buildSQL(sql));
459                                                            }
460    
461                                                            if (message.startsWith("Duplicate entry") ||
462                                                                    message.startsWith(
463                                                                            "Specified key was too long")) {
464    
465                                                                    _log.error(line);
466                                                            }
467                                                    }
468                                            }
469                                    }
470                            }
471                    }
472            }
473    
474            @Override
475            public void setSupportsStringCaseSensitiveQuery(
476                    boolean supportsStringCaseSensitiveQuery) {
477    
478                    if (_log.isInfoEnabled()) {
479                            if (supportsStringCaseSensitiveQuery) {
480                                    _log.info("Database supports case sensitive queries");
481                            }
482                            else {
483                                    _log.info("Database does not support case sensitive queries");
484                            }
485                    }
486    
487                    _supportsStringCaseSensitiveQuery = supportsStringCaseSensitiveQuery;
488            }
489    
490            @Override
491            public void updateIndexes(
492                            Connection con, String tablesSQL, String indexesSQL,
493                            boolean dropIndexes)
494                    throws IOException, SQLException {
495    
496                    List<Index> indexes = getIndexes(con);
497    
498                    Set<String> validIndexNames = null;
499    
500                    if (dropIndexes) {
501                            validIndexNames = dropIndexes(con, tablesSQL, indexesSQL, indexes);
502                    }
503                    else {
504                            validIndexNames = new HashSet<String>();
505    
506                            for (Index index : indexes) {
507                                    String indexName = StringUtil.toUpperCase(index.getIndexName());
508    
509                                    validIndexNames.add(indexName);
510                            }
511                    }
512    
513                    addIndexes(con, indexesSQL, validIndexNames);
514            }
515    
516            protected BaseDB(String type) {
517                    _type = type;
518    
519                    String[] actual = getTemplate();
520    
521                    for (int i = 0; i < TEMPLATE.length; i++) {
522                            _templateMap.put(TEMPLATE[i], actual[i]);
523                    }
524            }
525    
526            protected String[] buildColumnNameTokens(String line) {
527                    String[] words = StringUtil.split(line, ' ');
528    
529                    String nullable = "";
530    
531                    if (words.length == 7) {
532                            nullable = "not null;";
533                    }
534    
535                    String[] template = {
536                            words[1], words[2], words[3], words[4], nullable
537                    };
538    
539                    return template;
540            }
541    
542            protected String[] buildColumnTypeTokens(String line) {
543                    String[] words = StringUtil.split(line, ' ');
544    
545                    String nullable = "";
546    
547                    if (words.length == 6) {
548                            nullable = "not null;";
549                    }
550                    else if (words.length == 5) {
551                            nullable = words[4];
552                    }
553                    else if (words.length == 4) {
554                            nullable = "not null;";
555    
556                            if (words[3].endsWith(";")) {
557                                    words[3] = words[3].substring(0, words[3].length() - 1);
558                            }
559                    }
560    
561                    String[] template = {
562                            words[1], words[2], "", words[3], nullable
563                    };
564    
565                    return template;
566            }
567    
568            protected abstract String buildCreateFileContent(
569                            String sqlDir, String databaseName, int population)
570                    throws IOException;
571    
572            protected String[] buildTableNameTokens(String line) {
573                    String[] words = StringUtil.split(line, StringPool.SPACE);
574    
575                    return new String[] {words[1], words[2]};
576            }
577    
578            protected String buildTemplate(String sqlDir, String fileName)
579                    throws IOException {
580    
581                    String template = readFile(sqlDir + "/" + fileName + ".sql");
582    
583                    if (fileName.equals("portal") ||
584                            fileName.equals("update-5.0.1-5.1.0")) {
585    
586                            StringBundler sb = new StringBundler();
587    
588                            try (UnsyncBufferedReader unsyncBufferedReader =
589                                            new UnsyncBufferedReader(
590                                                    new UnsyncStringReader(template))) {
591    
592                                    String line = null;
593    
594                                    while ((line = unsyncBufferedReader.readLine()) != null) {
595                                            if (line.startsWith("@include ")) {
596                                                    int pos = line.indexOf(" ");
597    
598                                                    String includeFileName = line.substring(pos + 1);
599    
600                                                    File includeFile = new File(
601                                                            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    
631                            template = sb.toString();
632                    }
633    
634                    if (fileName.equals("indexes") && (this instanceof SybaseDB)) {
635                            template = removeBooleanIndexes(sqlDir, template);
636                    }
637    
638                    return template;
639            }
640    
641            protected String convertTimestamp(String data) {
642                    String s = null;
643    
644                    if (this instanceof MySQLDB) {
645                            s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
646                    }
647                    else {
648                            Matcher matcher = _timestampPattern.matcher(data);
649    
650                            s = matcher.replaceAll("CURRENT_TIMESTAMP");
651                    }
652    
653                    return s;
654            }
655    
656            protected Set<String> dropIndexes(
657                            Connection con, String tablesSQL, String indexesSQL,
658                            List<Index> indexes)
659                    throws IOException, SQLException {
660    
661                    if (_log.isInfoEnabled()) {
662                            _log.info("Dropping stale indexes");
663                    }
664    
665                    Set<String> validIndexNames = new HashSet<String>();
666    
667                    if (indexes.isEmpty()) {
668                            return validIndexNames;
669                    }
670    
671                    String tablesSQLLowerCase = StringUtil.toLowerCase(tablesSQL);
672                    String indexesSQLLowerCase = StringUtil.toLowerCase(indexesSQL);
673    
674                    String[] lines = StringUtil.splitLines(indexesSQL);
675    
676                    Set<String> indexNames = new HashSet<String>();
677    
678                    for (String line : lines) {
679                            if (Validator.isNull(line)) {
680                                    continue;
681                            }
682    
683                            IndexMetadata indexMetadata =
684                                    IndexMetadataFactoryUtil.createIndexMetadata(line);
685    
686                            indexNames.add(
687                                    StringUtil.toLowerCase(indexMetadata.getIndexName()));
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 (indexNames.contains(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                    StringBundler sb = new StringBundler();
758    
759                    try (UnsyncBufferedReader unsyncBufferedReader =
760                                    new UnsyncBufferedReader(new UnsyncStringReader(template))) {
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    
772                    template = sb.toString();
773                    template = StringUtil.replace(template, "\n\n\n", "\n\n");
774    
775                    return template;
776            }
777    
778            protected String getCreateTablesContent(String sqlDir, String suffix)
779                    throws IOException {
780    
781                    StringBundler sb = new StringBundler(8);
782    
783                    sb.append(sqlDir);
784    
785                    if (!sqlDir.endsWith("/WEB-INF/sql")) {
786                            sb.append("/portal");
787                            sb.append(suffix);
788                            sb.append("/portal");
789                    }
790                    else {
791                            sb.append("/tables");
792                            sb.append(suffix);
793                            sb.append("/tables");
794                    }
795    
796                    sb.append(suffix);
797                    sb.append(StringPool.DASH);
798                    sb.append(getServerName());
799                    sb.append(".sql");
800    
801                    return readFile(sb.toString());
802            }
803    
804            protected abstract String getServerName();
805    
806            protected String getSuffix(int type) {
807                    if (type == BARE) {
808                            return "-bare";
809                    }
810                    else if (type == SHARDED) {
811                            return "-sharded";
812                    }
813                    else {
814                            return StringPool.BLANK;
815                    }
816            }
817    
818            protected abstract String[] getTemplate();
819    
820            protected void handleSQLException(String sql, SQLException sqle)
821                    throws SQLException {
822    
823                    if (_log.isDebugEnabled()) {
824                            StringBundler sb = new StringBundler(18);
825    
826                            sb.append("SQL: ");
827                            sb.append(sql);
828                            sb.append("\nSQL state: ");
829                            sb.append(sqle.getSQLState());
830                            sb.append("\nVendor: ");
831                            sb.append(getType());
832                            sb.append("\nVendor error code: ");
833                            sb.append(sqle.getErrorCode());
834                            sb.append("\nVendor error message: ");
835                            sb.append(sqle.getMessage());
836    
837                            _log.debug(sb.toString());
838                    }
839    
840                    throw sqle;
841            }
842    
843            protected String readFile(String fileName) throws IOException {
844                    if (FileUtil.exists(fileName)) {
845                            return FileUtil.read(fileName);
846                    }
847                    else {
848                            return StringPool.BLANK;
849                    }
850            }
851    
852            protected String readSQL(String fileName, String comments, String eol)
853                    throws IOException {
854    
855                    if (!FileUtil.exists(fileName)) {
856                            return StringPool.BLANK;
857                    }
858    
859                    try (UnsyncBufferedReader unsyncBufferedReader =
860                                    new UnsyncBufferedReader(new FileReader(new File(fileName)))) {
861    
862                            StringBundler sb = new StringBundler();
863    
864                            String line = null;
865    
866                            while ((line = unsyncBufferedReader.readLine()) != null) {
867                                    if (!line.startsWith(comments)) {
868                                            line = StringUtil.replace(
869                                                    line, new String[] {"\n", "\t"}, new String[] {"", ""});
870    
871                                            if (line.endsWith(";")) {
872                                                    sb.append(line.substring(0, line.length() - 1));
873                                                    sb.append(eol);
874                                            }
875                                            else {
876                                                    sb.append(line);
877                                            }
878                                    }
879                            }
880    
881                            return sb.toString();
882                    }
883            }
884    
885            protected String removeBooleanIndexes(String sqlDir, String data)
886                    throws IOException {
887    
888                    String portalData = readFile(sqlDir + "/portal-tables.sql");
889    
890                    if (Validator.isNull(portalData)) {
891                            return StringPool.BLANK;
892                    }
893    
894                    try (UnsyncBufferedReader unsyncBufferedReader =
895                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
896    
897                            StringBundler sb = new StringBundler();
898    
899                            String line = null;
900    
901                            while ((line = unsyncBufferedReader.readLine()) != null) {
902                                    boolean append = true;
903    
904                                    int x = line.indexOf(" on ");
905    
906                                    if (x != -1) {
907                                            int y = line.indexOf(" (", x);
908    
909                                            String table = line.substring(x + 4, y);
910    
911                                            x = y + 2;
912                                            y = line.indexOf(")", x);
913    
914                                            String[] columns = StringUtil.split(line.substring(x, y));
915    
916                                            x = portalData.indexOf("create table " + table + " (");
917                                            y = portalData.indexOf(");", x);
918    
919                                            String portalTableData = portalData.substring(x, y);
920    
921                                            for (int i = 0; i < columns.length; i++) {
922                                                    if (portalTableData.contains(
923                                                                    columns[i].trim() + " BOOLEAN")) {
924    
925                                                            append = false;
926    
927                                                            break;
928                                                    }
929                                            }
930                                    }
931    
932                                    if (append) {
933                                            sb.append(line);
934                                            sb.append("\n");
935                                    }
936                            }
937    
938                            return sb.toString();
939                    }
940            }
941    
942            protected String removeInserts(String data) throws IOException {
943                    try (UnsyncBufferedReader unsyncBufferedReader =
944                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
945    
946                            StringBundler sb = new StringBundler();
947    
948                            String line = null;
949    
950                            while ((line = unsyncBufferedReader.readLine()) != null) {
951                                    if (!line.startsWith("insert into ") &&
952                                            !line.startsWith("update ")) {
953    
954                                            sb.append(line);
955                                            sb.append("\n");
956                                    }
957                            }
958    
959                            return sb.toString();
960                    }
961            }
962    
963            protected String removeLongInserts(String data) throws IOException {
964                    try (UnsyncBufferedReader unsyncBufferedReader =
965                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
966    
967                            StringBundler sb = new StringBundler();
968    
969                            String line = null;
970    
971                            while ((line = unsyncBufferedReader.readLine()) != null) {
972                                    if (!line.startsWith("insert into Image (") &&
973                                            !line.startsWith("insert into JournalArticle (")) {
974    
975                                            sb.append(line);
976                                            sb.append("\n");
977                                    }
978                            }
979    
980                            return sb.toString();
981                    }
982            }
983    
984            protected String removeNull(String content) {
985                    content = StringUtil.replace(content, " = null", " = NULL");
986                    content = StringUtil.replace(content, " is null", " IS NULL");
987                    content = StringUtil.replace(content, " not null", " not_null");
988                    content = StringUtil.replace(content, " null", "");
989                    content = StringUtil.replace(content, " not_null", " not null");
990    
991                    return content;
992            }
993    
994            protected String replaceTemplate(String template, String[] actual) {
995                    if ((template == null) || (TEMPLATE == null) || (actual == null)) {
996                            return null;
997                    }
998    
999                    if (TEMPLATE.length != actual.length) {
1000                            return template;
1001                    }
1002    
1003                    StringBundler sb = null;
1004    
1005                    int endIndex = 0;
1006    
1007                    Matcher matcher = _templatePattern.matcher(template);
1008    
1009                    while (matcher.find()) {
1010                            int startIndex = matcher.start();
1011    
1012                            if (sb == null) {
1013                                    sb = new StringBundler();
1014                            }
1015    
1016                            sb.append(template.substring(endIndex, startIndex));
1017    
1018                            endIndex = matcher.end();
1019    
1020                            String matched = template.substring(startIndex, endIndex);
1021    
1022                            sb.append(_templateMap.get(matched));
1023                    }
1024    
1025                    if (sb == null) {
1026                            return template;
1027                    }
1028    
1029                    if (template.length() > endIndex) {
1030                            sb.append(template.substring(endIndex));
1031                    }
1032    
1033                    return sb.toString();
1034            }
1035    
1036            protected abstract String reword(String data) throws IOException;
1037    
1038            protected static final String ALTER_COLUMN_NAME = "alter_column_name ";
1039    
1040            protected static final String ALTER_COLUMN_TYPE = "alter_column_type ";
1041    
1042            protected static final String ALTER_TABLE_NAME = "alter_table_name ";
1043    
1044            protected static final String DROP_INDEX = "drop index";
1045    
1046            protected static final String DROP_PRIMARY_KEY = "drop primary key";
1047    
1048            protected static final String[] RENAME_TABLE_TEMPLATE = {
1049                    "@old-table@", "@new-table@"
1050            };
1051    
1052            protected static final String[] REWORD_TEMPLATE = {
1053                    "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
1054            };
1055    
1056            protected static final String[] TEMPLATE = {
1057                    "##", "TRUE", "FALSE", "'01/01/1970'", "CURRENT_TIMESTAMP", " BLOB",
1058                    " SBLOB", " BOOLEAN", " DATE", " DOUBLE", " INTEGER", " LONG",
1059                    " STRING", " TEXT", " VARCHAR", " IDENTITY", "COMMIT_TRANSACTION"
1060            };
1061    
1062            private static final boolean _SUPPORTS_ALTER_COLUMN_NAME = true;
1063    
1064            private static final boolean _SUPPORTS_ALTER_COLUMN_TYPE = true;
1065    
1066            private static final boolean _SUPPORTS_DATE_MILLISECONDS = true;
1067    
1068            private static final boolean _SUPPORTS_INLINE_DISTINCT = true;
1069    
1070            private static final boolean _SUPPORTS_QUERYING_AFTER_EXCEPTION = true;
1071    
1072            private static final boolean _SUPPORTS_SCROLLABLE_RESULTS = true;
1073    
1074            private static final boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN = true;
1075    
1076            private static Log _log = LogFactoryUtil.getLog(BaseDB.class);
1077    
1078            private static Pattern _templatePattern;
1079            private static Pattern _timestampPattern = Pattern.compile(
1080                    "SPECIFIC_TIMESTAMP_\\d+");
1081    
1082            static {
1083                    StringBundler sb = new StringBundler(TEMPLATE.length * 3 - 3);
1084    
1085                    for (int i = 0; i < TEMPLATE.length; i++) {
1086                            String variable = TEMPLATE[i];
1087    
1088                            if (variable.equals("##") || variable.equals("'01/01/1970'")) {
1089                                    sb.append(variable);
1090                            }
1091                            else {
1092                                    sb.append("(?<!\\[\\$)");
1093                                    sb.append(variable);
1094                                    sb.append("(?!\\$\\])");
1095    
1096                                    sb.append("\\b");
1097                            }
1098    
1099                            if (i < (TEMPLATE.length - 1)) {
1100                                    sb.append(StringPool.PIPE);
1101                            }
1102                    }
1103    
1104                    _templatePattern = Pattern.compile(sb.toString());
1105            }
1106    
1107            private boolean _supportsStringCaseSensitiveQuery;
1108            private Map<String, String> _templateMap = new HashMap<String, String>();
1109            private String _type;
1110    
1111    }