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