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