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