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