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