Examples with SQLStatement com.servoy.j2db.dataprocessing.SQLStatement used on opensource projects

Example 1 with SQLStatement

use of com.servoy.j2db.dataprocessing.SQLStatement in project servoy-client by Servoy.

the class Messages method deleteKey.

public static boolean deleteKey(String key, String i18nDatasource, String clientId, Properties settings, IDataServer dataServer, IRepository repository, IFoundSetManagerInternal fm) {
    String[] names = getServerTableNames(i18nDatasource, settings);
    String serverName = names[0];
    String tableName = names[1];
    if (serverName != null && tableName != null) {
        try {
            IServer server = repository.getServer(serverName);
            if (server == null) {
                return false;
            }
            Table table = (Table) server.getTable(tableName);
            if (table == null) {
                return false;
            }
            QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
            // $NON-NLS-1$
            QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
            QueryDelete delete = new QueryDelete(messagesTable);
            delete.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgKey, key));
            ISQLStatement sqlStatement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, serverName, tableName, null, null, delete, fm != null ? fm.getTableFilterParams(serverName, delete) : null);
            dataServer.performUpdates(clientId, new ISQLStatement[] { sqlStatement });
        } catch (Exception e) {
            return false;
        }
    }
    return true;
}

Example 2 with SQLStatement

use of com.servoy.j2db.dataprocessing.SQLStatement in project servoy-client by Servoy.

the class I18NUtil method writeMessagesToRepository.

public static void writeMessagesToRepository(String i18NServerName, String i18NTableName, IRepository repository, IDataServer dataServer, String clientID, TreeMap<String, MessageEntry> messages, boolean noUpdates, boolean noRemoves, TreeMap<String, MessageEntry> remoteMessages, String filterName, String[] filterValue, IFoundSetManagerInternal fm) throws Exception {
    // get remote messages snapshot
    if (remoteMessages == null)
        remoteMessages = loadSortedMessagesFromRepository(repository, dataServer, clientID, i18NServerName, i18NTableName, filterName, filterValue, fm);
    if (remoteMessages != null) {
        IServer i18NServer = repository.getServer(i18NServerName);
        Table i18NTable = null;
        if (i18NServer != null) {
            i18NTable = (Table) i18NServer.getTable(i18NTableName);
        }
        if (i18NTable != null) {
            // runtime exception when no ident columns
            Column pkColumn = i18NTable.getRowIdentColumns().get(0);
            QueryTable messagesTable = new QueryTable(i18NTable.getSQLName(), i18NTable.getDataSource(), i18NTable.getCatalog(), i18NTable.getSchema());
            QueryColumn pkCol = pkColumn.queryColumn(messagesTable);
            QueryColumn msgLang = new QueryColumn(messagesTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
            QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
            QueryColumn msgVal = new QueryColumn(messagesTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
            ArrayList<SQLStatement> updateStatements = new ArrayList<SQLStatement>();
            // go thorough messages, update exiting, add news to remote
            // in case we need to insert a record, we must know if it is database managed or servoy managed
            boolean logIdIsServoyManaged = false;
            ColumnInfo ci = pkColumn.getColumnInfo();
            if (ci != null) {
                int autoEnterType = ci.getAutoEnterType();
                int autoEnterSubType = ci.getAutoEnterSubType();
                logIdIsServoyManaged = (autoEnterType == ColumnInfo.SEQUENCE_AUTO_ENTER) && (autoEnterSubType != ColumnInfo.NO_SEQUENCE_SELECTED) && (autoEnterSubType != ColumnInfo.DATABASE_IDENTITY);
            }
            List<Column> tenantColumns = i18NTable.getTenantColumns();
            Iterator<Map.Entry<String, MessageEntry>> messagesIte = messages.entrySet().iterator();
            Map.Entry<String, MessageEntry> messageEntry;
            while (messagesIte.hasNext()) {
                messageEntry = messagesIte.next();
                String key = messageEntry.getKey();
                String value = messageEntry.getValue().getValue();
                String lang = messageEntry.getValue().getLanguage();
                if (lang.equals(""))
                    lang = null;
                String messageKey = messageEntry.getValue().getKey();
                if (// insert
                !remoteMessages.containsKey(key)) {
                    QueryInsert insert = new QueryInsert(messagesTable);
                    QueryColumn[] insertColumns = null;
                    Object[] insertColumnValues = null;
                    if (logIdIsServoyManaged) {
                        Object messageId = dataServer.getNextSequence(i18NServerName, i18NTableName, pkColumn.getName(), -1, i18NServerName);
                        if (lang == null) {
                            insertColumns = new QueryColumn[] { pkCol, msgKey, msgVal };
                            insertColumnValues = new Object[] { messageId, messageKey, value };
                        } else {
                            insertColumns = new QueryColumn[] { pkCol, msgKey, msgLang, msgVal };
                            insertColumnValues = new Object[] { messageId, messageKey, lang, value };
                        }
                    } else {
                        if (lang == null) {
                            insertColumns = new QueryColumn[] { msgKey, msgVal };
                            insertColumnValues = new Object[] { messageKey, value };
                        } else {
                            insertColumns = new QueryColumn[] { msgKey, msgLang, msgVal };
                            insertColumnValues = new Object[] { messageKey, lang, value };
                        }
                    }
                    Column filterColumn = i18NTable.getColumn(filterName);
                    if (filterColumn != null && filterValue != null && filterValue.length > 0) {
                        insertColumns = Utils.arrayAdd(insertColumns, filterColumn.queryColumn(messagesTable), true);
                        insertColumnValues = Utils.arrayAdd(insertColumnValues, filterValue[0], true);
                    }
                    insert.setColumnValues(insertColumns, insertColumnValues);
                    updateStatements.add(new SQLStatement(ISQLActionTypes.INSERT_ACTION, i18NServerName, i18NTableName, null, null, insert, null));
                } else if (// update
                !remoteMessages.get(key).getValue().equals(value) && !noUpdates) {
                    QueryUpdate update = new QueryUpdate(messagesTable);
                    update.addValue(msgVal, value);
                    update.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgKey, messageKey));
                    update.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLang, lang));
                    if (filterName != null) {
                        Column filterColumn = i18NTable.getColumn(filterName);
                        if (filterColumn != null && filterValue != null && filterValue.length > 0) {
                            QueryColumn columnFilter = filterColumn.queryColumn(messagesTable);
                            CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(filterValue[0], null));
                            // $NON-NLS-1$
                            update.addCondition("FILTER", cc);
                        }
                    }
                    // Add condition to update only records having the default tenant value (null)
                    for (Column column : tenantColumns) {
                        QueryColumn tenantColumn = column.queryColumn(messagesTable);
                        CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, tenantColumn, null);
                        update.addCondition(cc);
                    }
                    updateStatements.add(new SQLStatement(ISQLActionTypes.UPDATE_ACTION, i18NServerName, i18NTableName, null, null, update, fm != null ? fm.getTableFilterParams(i18NServerName, update) : null));
                }
            }
            if (!noRemoves) {
                // go thorough remote, remove if not existing locally
                Iterator<Map.Entry<String, MessageEntry>> remoteMessagesIte = remoteMessages.entrySet().iterator();
                Map.Entry<String, MessageEntry> remoteMessageEntry;
                while (remoteMessagesIte.hasNext()) {
                    remoteMessageEntry = remoteMessagesIte.next();
                    String key = remoteMessageEntry.getKey();
                    if (// delete
                    !messages.containsKey(key)) {
                        String lang = remoteMessageEntry.getValue().getLanguage();
                        if (lang.equals(""))
                            lang = null;
                        String messageKey = remoteMessageEntry.getValue().getKey();
                        QueryDelete delete = new QueryDelete(messagesTable);
                        delete.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgKey, messageKey));
                        delete.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLang, lang));
                        if (filterName != null) {
                            Column filterColumn = i18NTable.getColumn(filterName);
                            if (filterColumn != null && filterValue != null && filterValue.length > 0) {
                                QueryColumn columnFilter = filterColumn.queryColumn(messagesTable);
                                CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(filterValue[0], null));
                                delete.addCondition(cc);
                            }
                        }
                        // Add condition to remove only records having the default tenant value (null)
                        for (Column column : tenantColumns) {
                            QueryColumn tenantColumn = column.queryColumn(messagesTable);
                            CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, tenantColumn, null);
                            delete.addCondition(cc);
                        }
                        updateStatements.add(new SQLStatement(ISQLActionTypes.DELETE_ACTION, i18NServerName, i18NTableName, null, null, delete, fm != null ? fm.getTableFilterParams(i18NServerName, delete) : null));
                    }
                }
            }
            for (SQLStatement st : updateStatements) {
                st.setDataType(ISQLStatement.I18N_DATA_TYPE);
            }
            dataServer.performUpdates(clientID, updateStatements.toArray(new ISQLStatement[updateStatements.size()]));
        }
    }
}