/*
* Copyright 2007 - 2017 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 net.sf.jailer.database;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Styles of inline-views for different DBMS'es.
*/
public enum InlineViewStyle {
DB2("(values (1, '2', 3), (4, '5', 6)) %s(A, B, C)") {
@Override
public String head(String[] columnNames) throws SQLException {
return "(values ";
}
@Override
public String item(String[] values, String[] columnNames, int rowNumber) throws SQLException {
StringBuilder sb = new StringBuilder("(");
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(values[i - 1]);
}
sb.append(")");
return sb.toString();
}
@Override
public String separator() throws SQLException {
return ", ";
}
@Override
public String terminator(String name, String[] columnNames) throws SQLException {
StringBuilder sb = new StringBuilder(") " + name + "(");
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(columnNames[i - 1]);
}
sb.append(")");
return sb.toString();
}
},
MySQL("(Select 1 A, '2' B, 3 C Union all " + "Select 4, '5', 6) %s") {
@Override
public String head(String[] columnNames) throws SQLException {
return "(Select ";
}
@Override
public String item(String[] values, String[] columnNames, int rowNumber) throws SQLException {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(values[i - 1]);
if (rowNumber == 0) {
sb.append(" " + columnNames[i - 1]);
}
}
return sb.toString();
}
@Override
public String separator() throws SQLException {
return " Union all Select ";
}
@Override
public String terminator(String name, String[] columnNames) throws SQLException {
return ") " + name;
}
},
Oracle("(Select 1 A, '2' B, 3 C from dual Union all " + "Select 4, '5', 6 from dual) %s") {
@Override
public String head(String[] columnNames) throws SQLException {
return "(Select ";
}
@Override
public String item(String[] values, String[] columnNames, int rowNumber) throws SQLException {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(values[i - 1]);
if (rowNumber == 0) {
sb.append(" " + columnNames[i - 1]);
}
}
return sb.toString();
}
@Override
public String separator() throws SQLException {
return " from dual Union all Select ";
}
@Override
public String terminator(String name, String[] columnNames) throws SQLException {
StringBuilder sb = new StringBuilder(" from dual) " + name);
return sb.toString();
}
},
INFORMIX1("(Select 1 A, '2' B, 3 C from sysmaster:\"informix\".sysdual Union all "
+ "Select 4, '5', 6 from sysmaster:\"informix\".sysdual) %s") {
@Override
public String head(String[] columnNames) throws SQLException {
return "(Select ";
}
@Override
public String item(String[] values, String[] columnNames, int rowNumber) throws SQLException {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(values[i - 1]);
if (rowNumber == 0) {
sb.append(" " + columnNames[i - 1]);
}
}
return sb.toString();
}
@Override
public String separator() throws SQLException {
return " from sysmaster:\"informix\".sysdual Union all Select ";
}
@Override
public String terminator(String name, String[] columnNames) throws SQLException {
StringBuilder sb = new StringBuilder(" from sysmaster:\"informix\".sysdual) " + name);
return sb.toString();
}
},
INFORMIX2("(Select 1 A, '2' B, 3 C from table(set{1}) Union all " +
"Select 4, '5', 6 from table(set{1})) %s") {
@Override
public String head(String[] columnNames) throws SQLException {
return "(Select ";
}
@Override
public String item(String[] values, String[] columnNames, int rowNumber) throws SQLException {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(values[i - 1]);
if (rowNumber == 0) {
sb.append(" " + columnNames[i - 1]);
}
}
return sb.toString();
}
@Override
public String separator() throws SQLException {
return " from table(set{1}) Union all Select ";
}
@Override
public String terminator(String name, String[] columnNames) throws SQLException {
StringBuilder sb = new StringBuilder(" from table(set{1})) " + name);
return sb.toString();
}
},
DB2_ZOS("(Select 1, '2', 3 from sysibm.sysdummy1 Union all " +
"Select 4, '5', 6 from sysibm.sysdummy1) %s(A, B, C)") {
@Override
public String head(String[] columnNames) throws SQLException {
return "(Select ";
}
@Override
public String item(String[] values, String[] columnNames, int rowNumber) throws SQLException {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(values[i - 1]);
}
return sb.toString();
}
@Override
public String separator() throws SQLException {
return " from sysibm.sysdummy1 Union all Select ";
}
@Override
public String terminator(String name, String[] columnNames) throws SQLException {
StringBuilder sb = new StringBuilder(" from sysibm.sysdummy1) " + name + "(");
for (int i = 1; i <= columnNames.length; ++i) {
if (i > 1) {
sb.append(", ");
}
sb.append(columnNames[i - 1]);
}
sb.append(")");
return sb.toString();
}
};
public final String example;
private InlineViewStyle(String example) {
this.example = example;
}
/**
* Gets a style for a session.
*/
public static InlineViewStyle forSession(Session session) {
StringBuilder messages = new StringBuilder();
for (InlineViewStyle style : InlineViewStyle.values()) {
boolean wasSilent = session.getSilent();
try {
session.setSilent(true);
session.executeQuery("Select A, B, C from " + style.example.replace("%s", "Entity"),
new Session.AbstractResultSetReader() {
@Override
public void readCurrentRow(ResultSet resultSet) throws SQLException {
resultSet.getInt("A");
resultSet.getInt("B");
resultSet.getInt("C");
}
});
return style;
} catch (SQLException e) {
String message = e.getMessage();
messages.append(" " + style + ": \"" + message + "\"\n\n");
// try next style
} finally {
session.setSilent(wasSilent);
}
}
throw new RuntimeException("No suitable Inline-View Style known for " + session.dbUrl + "\n\n" + messages);
}
public abstract String head(String[] columnNames) throws SQLException;
public abstract String item(String[] values, String[] columnNames, int rowNumber) throws SQLException;
public abstract String separator() throws SQLException;
public abstract String terminator(String name, String[] columnNames) throws SQLException;
}