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