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