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