Java tutorial
/* * Copyright 2015 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * 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 io.pivotal.spring.xd.jdbcgpfdist.support; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.util.StringUtils; import java.util.List; public abstract class SqlUtils { private static Log log = LogFactory.getLog(SqlUtils.class); public static String createExternalReadableTable(LoadConfiguration config, String prefix, List<String> overrideLocations) { // TODO: this function needs a cleanup StringBuilder buf = new StringBuilder(); // unique table name String name = quotesEscape(config.getTable() + "_ext_" + prefix); buf.append("CREATE READABLE EXTERNAL TABLE "); buf.append(name); buf.append(" ( "); // column types or like ReadableTable externalTable = config.getExternalTable(); if (externalTable.getLike() != null) { buf.append("LIKE "); buf.append(quotesEscape(config.getTable())); } else if (StringUtils.hasText(externalTable.getColumns())) { buf.append(externalTable.getColumns()); } else { buf.append("LIKE "); buf.append(quotesEscape(config.getTable())); } buf.append(" ) "); // locations buf.append("LOCATION ("); if (overrideLocations != null && !overrideLocations.isEmpty()) { buf.append(createLocationString(overrideLocations.toArray(new String[0]))); } else { buf.append(createLocationString(externalTable.getLocations().toArray(new String[0]))); } buf.append(") "); // format type if (externalTable.getFormat() == Format.TEXT) { buf.append("FORMAT 'TEXT'"); } else { buf.append("FORMAT 'CSV'"); } // format parameters buf.append(" ( "); buf.append("DELIMITER '"); if (externalTable.getDelimiter() != null) { buf.append(unicodeEscaped(externalTable.getDelimiter().charValue())); } else { buf.append("|"); } buf.append("'"); if (externalTable.getNullString() != null) { buf.append(" NULL '"); buf.append(externalTable.getNullString()); buf.append("'"); } if (externalTable.getEscape() != null) { buf.append(" ESCAPE '"); buf.append(externalTable.getEscape()); buf.append("'"); } if (externalTable.getQuote() != null) { buf.append(" QUOTE '"); buf.append(externalTable.getQuote()); buf.append("'"); } if (externalTable.getForceQuote() != null) { buf.append(" FORCE QUOTE "); buf.append(StringUtils.arrayToCommaDelimitedString(externalTable.getForceQuote())); } if (externalTable.isFormatHeader()) { buf.append(" HEADER "); } buf.append(" )"); if (externalTable.getEncoding() != null) { buf.append(" ENCODING '"); buf.append(externalTable.getEncoding()); buf.append("'"); } if (externalTable.getSegmentRejectLimit() != null) { if (externalTable.getLogErrorsInto() != null) { buf.append(" LOG ERRORS INTO "); buf.append(externalTable.getLogErrorsInto()); } buf.append(" SEGMENT REJECT LIMIT "); buf.append(externalTable.getSegmentRejectLimit()); buf.append(" "); } return buf.toString(); } /** * * @param config the load configuration * @param prefix the prefix * @return the drop DDL */ public static String dropExternalReadableTable(LoadConfiguration config, String prefix) { StringBuilder b = new StringBuilder(); // unique table name String name = config.getTable() + "_ext_" + prefix; b.append("DROP EXTERNAL TABLE "); b.append(quotesEscape(name)); return b.toString(); } /** * Builds sql clause to load data into a database. * * @param config * Load configuration. * @param prefix * Prefix for temporary resources. * @return * the load DDL */ public static String load(LoadConfiguration config, String prefix) { if (config.getMode() == Mode.INSERT) { return loadInsert(config, prefix); } else if (config.getMode() == Mode.UPDATE) { return loadUpdate(config, prefix); } throw new IllegalArgumentException("Unsupported mode " + config.getMode()); } private static String loadInsert(LoadConfiguration config, String prefix) { StringBuilder b = new StringBuilder(); String name = config.getTable() + "_ext_" + prefix; b.append("INSERT INTO "); b.append(quotesEscape(config.getTable())); b.append(" SELECT "); if (StringUtils.hasText(config.getColumns())) { b.append(config.getColumns()); } else { b.append("*"); } b.append(" FROM "); b.append(quotesEscape(name)); return b.toString(); } private static String loadUpdate(LoadConfiguration config, String prefix) { StringBuilder b = new StringBuilder(); String name = config.getTable() + "_ext_" + prefix; b.append("UPDATE "); b.append(quotesEscape(config.getTable())); b.append(" into_table set "); for (int i = 0; i < config.getUpdateColumns().size(); i++) { b.append(config.getUpdateColumns().get(i) + "=from_table." + config.getUpdateColumns().get(i)); if (i + 1 < config.getUpdateColumns().size()) { b.append(", "); } } b.append(" FROM "); b.append(quotesEscape(name)); b.append(" from_table where "); for (int i = 0; i < config.getMatchColumns().size(); i++) { b.append("into_table." + config.getMatchColumns().get(i) + "=from_table." + config.getMatchColumns().get(i)); if (i + 1 < config.getMatchColumns().size()) { b.append(" and "); } } if (StringUtils.hasText(config.getUpdateCondition())) { b.append(" and " + config.getUpdateCondition()); } return b.toString(); } /** * Converts string array to greenplum friendly string. From new * String[]{"foo","jee"} we get "'foo',jee'". * * @param strings * String array to explode * @return Comma delimited string with values encapsulated with * apostropheres. ' */ public static String createLocationString(String[] strings) { StringBuilder locString = new StringBuilder(); for (int i = 0; i < strings.length; i++) { String string = strings[i]; locString.append("'"); locString.append(string); locString.append("'"); if (i < strings.length - 1) { locString.append(","); } } return locString.toString(); } private static String unicodeEscaped(char ch) { if (ch < 0x10) { return "\\u000" + Integer.toHexString(ch); } else if (ch < 0x100) { return "\\u00" + Integer.toHexString(ch); } else if (ch < 0x1000) { return "\\u0" + Integer.toHexString(ch); } return "\\u" + Integer.toHexString(ch); } private static String quotesEscape(String text) { if (text.contains(".")) { String[] values = text.split("\\."); return values[0] + "." + "\"" + values[1] + "\""; } else { return "\"" + text + "\""; } } }