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