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