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