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