• Home
• org.jumpmind.symmetric
• symmetric-client
• 3.5.12
• source code
• PostgreSqlSymmetricDialect.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

org.jumpmind.symmetric.db.postgresql.PostgreSqlSymmetricDialect Maven / Gradle / Ivy

/**
 * Licensed to JumpMind Inc under one or more contributor
 * license agreements.  See the NOTICE file distributed
 * with this work for additional information regarding
 * copyright ownership.  JumpMind Inc licenses this file
 * to you under the GNU General Public License, version 3.0 (GPLv3)
 * (the "License"); you may not use this file except in compliance
 * with the License.
 *
 * You should have received a copy of the GNU General Public License,
 * version 3.0 (GPLv3) along with this library; if not, see
 * .
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.jumpmind.symmetric.db.postgresql;

import java.sql.Types;

import org.jumpmind.db.platform.IDatabasePlatform;
import org.jumpmind.db.sql.ISqlTransaction;
import org.jumpmind.db.util.BinaryEncoding;
import org.jumpmind.symmetric.SymmetricException;
import org.jumpmind.symmetric.common.ParameterConstants;
import org.jumpmind.symmetric.db.AbstractSymmetricDialect;
import org.jumpmind.symmetric.db.ISymmetricDialect;
import org.jumpmind.symmetric.model.Trigger;
import org.jumpmind.symmetric.model.TriggerHistory;
import org.jumpmind.symmetric.service.IParameterService;

/*
 * Support for PostgreSQL
 */
public class PostgreSqlSymmetricDialect extends AbstractSymmetricDialect implements ISymmetricDialect {

    static final String TRANSACTION_ID_EXPRESSION = "txid_current()";

    static final String SYNC_TRIGGERS_DISABLED_VARIABLE = "symmetric.triggers_disabled";

    static final String SYNC_NODE_DISABLED_VARIABLE = "symmetric.node_disabled";
    
    static final String SQL_DROP_FUNCTION = "drop function $(functionName)";
    
    static final String SQL_FUNCTION_INSTALLED = 
        " select count(*) from information_schema.routines " + 
        " where routine_name = '$(functionName)' and specific_schema = '$(defaultSchema)'" ;    

    private Boolean supportsTransactionId = null;
        
    public PostgreSqlSymmetricDialect(IParameterService parameterService, IDatabasePlatform platform) {
        super(parameterService, platform);
        this.triggerTemplate = new PostgreSqlTriggerTemplate(this);
    }
    
    @Override
    public void createRequiredDatabaseObjects() {

    	ISqlTransaction transaction = null;
        try {
            transaction = platform.getSqlTemplate().startSqlTransaction();
            enableSyncTriggers(transaction);
        } catch (Exception e) {
            String message = "Please add \"custom_variable_classes = 'symmetric'\" to your postgresql.conf file"; 
            log.error(message);
            throw new SymmetricException(message, e);
        } finally {
            if (transaction != null) {
                transaction.close();
            }
        }
        
        String triggersDisabled = this.parameterService.getTablePrefix() + "_" + "triggers_disabled";
        if (!installed(SQL_FUNCTION_INSTALLED, triggersDisabled)) {
            String sql = "CREATE or REPLACE FUNCTION $(functionName)() RETURNS INTEGER AS $$                                                                                                                     " + 
                    "                                DECLARE                                                                                                                                                                " + 
                    "                                  triggerDisabled INTEGER;                                                                                                                                             " + 
                    "                                BEGIN                                                                                                                                                                  " + 
                    "                                  select current_setting('symmetric.triggers_disabled') into triggerDisabled;                                                                                          " + 
                    "                                  return triggerDisabled;                                                                                                                                              " + 
                    "                                EXCEPTION WHEN OTHERS THEN                                                                                                                                             " + 
                    "                                  return 0;                                                                                                                                                            " + 
                    "                                END;                                                                                                                                                                   " + 
                    "                                $$ LANGUAGE plpgsql;                                                                                                                                                   ";
            install(sql, triggersDisabled);
        }

        String nodeDisabled = this.parameterService.getTablePrefix() + "_" + "node_disabled";
        if (!installed(SQL_FUNCTION_INSTALLED, nodeDisabled)) {
            String sql = "CREATE or REPLACE FUNCTION $(functionName)() RETURNS VARCHAR AS $$                                                                                                                     " + 
                    "                                DECLARE                                                                                                                                                                " + 
                    "                                  nodeId VARCHAR(50);                                                                                                                                                  " + 
                    "                                BEGIN                                                                                                                                                                  " + 
                    "                                  select current_setting('symmetric.node_disabled') into nodeId;                                                                                                       " + 
                    "                                  return nodeId;                                                                                                                                                       " + 
                    "                                EXCEPTION WHEN OTHERS THEN                                                                                                                                             " + 
                    "                                  return '';                                                                                                                                                           " + 
                    "                                END;                                                                                                                                                                   " + 
                    "                                $$ LANGUAGE plpgsql;                                                                                                                                                   ";
            install(sql, nodeDisabled);
        }

        String largeObjects = this.parameterService.getTablePrefix() + "_" + "largeobject";
        if (!installed(SQL_FUNCTION_INSTALLED, largeObjects)) {
            String sql = "CREATE OR REPLACE FUNCTION $(functionName)(objectId oid) RETURNS text AS $$                                                                                                            " + 
                    "                                DECLARE                                                                                                                                                                " + 
                    "                                  encodedBlob text;                                                                                                                                                    " + 
                    "                                  encodedBlobPage text;                                                                                                                                                " + 
                    "                                BEGIN                                                                                                                                                                  " + 
                    "                                  encodedBlob := '';                                                                                                                                                   " + 
                    "                                  FOR encodedBlobPage IN SELECT pg_catalog.encode(data, 'escape')                                                                                                                 " + 
                    "                                  FROM pg_largeobject WHERE loid = objectId ORDER BY pageno LOOP                                                                                                       " + 
                    "                                    encodedBlob := encodedBlob || encodedBlobPage;                                                                                                                     " + 
                    "                                  END LOOP;                                                                                                                                                            " + 
                    "                                  RETURN pg_catalog.encode(pg_catalog.decode(encodedBlob, 'escape'), 'base64');                                                                                                              " + 
                    "                                EXCEPTION WHEN OTHERS THEN                                                                                                                                             " + 
                    "                                  RETURN '';                                                                                                                                                           " + 
                    "                                END                                                                                                                                                                    " + 
                    "                                $$ LANGUAGE plpgsql;                                                                                                                                                   ";
            install(sql, largeObjects);
        }
        
    }
    
    @Override
    public void dropRequiredDatabaseObjects() {
        String triggersDisabled = this.parameterService.getTablePrefix() + "_" + "triggers_disabled";
        if (installed(SQL_FUNCTION_INSTALLED, triggersDisabled)) {
            uninstall(SQL_DROP_FUNCTION+ "() cascade", triggersDisabled);
        }

        String nodeDisabled = this.parameterService.getTablePrefix() + "_" + "node_disabled";
        if (installed(SQL_FUNCTION_INSTALLED, nodeDisabled)) {
            uninstall(SQL_DROP_FUNCTION + "() cascade", nodeDisabled);
        }

        String largeObjects = this.parameterService.getTablePrefix() + "_" + "largeobject";
        if (installed(SQL_FUNCTION_INSTALLED, largeObjects)) {
            uninstall(SQL_DROP_FUNCTION + "(objectId oid) cascade", largeObjects);
        }

    }

    @Override
    public boolean requiresAutoCommitFalseToSetFetchSize() {
        return true;
    }

    @Override
    protected boolean doesTriggerExistOnPlatform(String catalogName, String schema, String tableName, String triggerName) {
        return platform.getSqlTemplate().queryForInt("select count(*) from information_schema.triggers where trigger_name = ? "
                + "and event_object_table = ? and trigger_schema = ?", new Object[] { triggerName.toLowerCase(),
                tableName, schema == null ? platform.getDefaultSchema() : schema }) > 0 ||
                platform.getSqlTemplate().queryForInt("select count(*) from information_schema.routines where routine_name = ? "
                        + "and routine_schema = ?", new Object[] { "f" + triggerName.toLowerCase(),
                        schema == null ? platform.getDefaultSchema() : schema }) > 0;
    }
    
    @Override
    public void removeTrigger(StringBuilder sqlBuffer, String catalogName, String schemaName, String triggerName,
            String tableName, TriggerHistory oldHistory) {
        schemaName = schemaName == null ? "" : (schemaName + ".");
        final String dropSql = "drop trigger " + triggerName + " on " + schemaName + tableName;
        logSql(dropSql, sqlBuffer);
        final String dropFunction = "drop function " + schemaName + "f" + triggerName + "() cascade";
        logSql(dropFunction, sqlBuffer);
        if (parameterService.is(ParameterConstants.AUTO_SYNC_TRIGGERS)) {
            String sql = null;
            try {
                sql = dropSql;
                platform.getSqlTemplate().update(dropSql);
            } catch (Exception e) {
                log.warn("Tried to remove trigger using: {} and failed because: {}", sql, e.getMessage());
            }
            try {                
                sql = dropFunction;
                platform.getSqlTemplate().update(dropFunction);
            } catch (Exception e) {
                log.warn("Tried to remove function using: {} and failed because: {}", sql, e.getMessage());
            }
        }
    }

    public void disableSyncTriggers(ISqlTransaction transaction, String nodeId) {
        transaction.prepareAndExecute("select set_config('" + SYNC_TRIGGERS_DISABLED_VARIABLE + "', '1', false)");
        if (nodeId == null) {
            nodeId = "";
        }
        transaction.prepareAndExecute("select set_config('" + SYNC_NODE_DISABLED_VARIABLE + "', '" + nodeId + "', false)");
    }

    public void enableSyncTriggers(ISqlTransaction transaction) {
        transaction.prepareAndExecute("select set_config('" + SYNC_TRIGGERS_DISABLED_VARIABLE
                + "', '', false)");
        transaction.prepareAndExecute("select set_config('" + SYNC_NODE_DISABLED_VARIABLE
                + "', '', false)");
    }

    public String getSyncTriggersExpression() {
        return "$(defaultSchema)" + parameterService.getTablePrefix() + "_triggers_disabled() = 0";
    }

    @Override
    public String getTransactionTriggerExpression(String defaultCatalog, String defaultSchema, Trigger trigger) {
        if (supportsTransactionId()) {
            return TRANSACTION_ID_EXPRESSION;
        } else {
            return "null";
        }
    }

    @Override
    public boolean supportsTransactionId() {
        if (supportsTransactionId == null) {
            if (platform
                    .getSqlTemplate()
                    .queryForInt(
                            "select count(*) from information_schema.routines where routine_name='txid_current'") > 0) {
                supportsTransactionId = true;
            } else {
                supportsTransactionId = false;
            }
        }
        return supportsTransactionId;
    }

    public void purgeRecycleBin() {
    }

    @Override
    public BinaryEncoding getBinaryEncoding() {
        return BinaryEncoding.BASE64;
    }    

    @Override
    public int getSqlTypeForIds() {
        return Types.BIGINT;
    }

}