Java tutorial
/* * Copyright 2014 Feedzai * * 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 com.feedzai.commons.sql.abstraction.engine.impl; import com.feedzai.commons.sql.abstraction.ddl.*; import com.feedzai.commons.sql.abstraction.dml.*; import com.feedzai.commons.sql.abstraction.engine.AbstractTranslator; import com.feedzai.commons.sql.abstraction.engine.DatabaseEngineRuntimeException; import com.feedzai.commons.sql.abstraction.util.StringUtils; import com.google.common.base.Joiner; import com.google.common.collect.Lists; import java.util.ArrayList; import java.util.List; import static com.feedzai.commons.sql.abstraction.engine.configuration.PdbProperties.VARCHAR_SIZE; import static com.feedzai.commons.sql.abstraction.util.StringUtils.quotize; import static java.lang.String.format; /** * Provides SQL translation for PostgreSQL. * * @author Rui Vilao (rui.vilao@feedzai.com) * @since 2.0.0 */ public class PostgreSqlTranslator extends AbstractTranslator { @Override public String translate(AlterColumn ac) { final DbColumn column = ac.getColumn(); final Expression table = ac.getTable(); final Name name = new Name(column.getName()); inject(table, name); StringBuilder sb = new StringBuilder("ALTER TABLE ").append(table.translate()).append(" ALTER COLUMN ") .append(name.translate()).append(" TYPE ").append(translate(column)).append("; "); if (!column.getColumnConstraints().isEmpty()) { sb.append("ALTER TABLE ").append(table.translate()).append(" ALTER COLUMN ").append(name.translate()) .append(" SET "); List<Object> trans = Lists.transform(column.getColumnConstraints(), new com.google.common.base.Function<DbColumnConstraint, Object>() { @Override public Object apply(DbColumnConstraint input) { return input.translate(); } }); sb.append(Joiner.on(" ").join(trans)); } return sb.toString(); } @Override public String translate(DropPrimaryKey dpk) { final Expression table = dpk.getTable(); inject(table); if (!(table instanceof Name)) { throw new DatabaseEngineRuntimeException("DropPrimaryKey must receive a Name object"); } final String tableName = ((Name) table).getName(); final String pkName = StringUtils.md5(format("PK_%s", tableName), properties.getMaxIdentifierSize()); return String.format("ALTER TABLE %s DROP CONSTRAINT %s", table.translate(), quotize(pkName)); } @Override public String translate(Function f) { final Expression exp = f.getExp(); final String function = f.getFunction(); inject(exp); String expTranslated = ""; if (exp != null) { expTranslated = exp.translate(); } // if it is a user-defined function if (f.isUDF() && properties.isSchemaSet()) { return properties.getSchema() + "." + function + "(" + expTranslated + ")"; } else { return function + "(" + expTranslated + ")"; } } @Override public String translate(Modulo m) { final Expression dividend = m.getDividend(); final Expression divisor = m.getDivisor(); inject(dividend, divisor); return String.format("MOD(%s, %s)", dividend.translate(), divisor.translate()); } @Override public String translate(Rename r) { final Expression oldName = r.getOldName(); final Expression newName = r.getNewName(); inject(oldName, newName); return String.format("ALTER TABLE %s RENAME TO %s", oldName.translate(), newName.translate()); } @Override public String translate(RepeatDelimiter rd) { final String delimiter = rd.getDelimiter(); final List<Expression> exps = rd.getExpressions(); final List<String> all = new ArrayList<>(); final Expression expression = exps.get(0); inject(expression); if (RepeatDelimiter.DIV.equals(delimiter)) { all.add(String.format("CAST(%s AS DOUBLE PRECISION)", expression.translate())); } else { all.add(expression.translate()); } for (int i = 1; i < exps.size(); i++) { final Expression nthExpression = exps.get(i); inject(nthExpression); all.add(nthExpression.translate()); } if (rd.isEnclosed()) { return "(" + join(all, delimiter) + ")"; } else { return join(all, delimiter); } } @Override public String translate(Query q) { final List<Expression> fromColumns = q.getFromColumns(); final List<Expression> groupbyColumns = q.getGroupbyColumns(); final List<Expression> orderbyColumns = q.getOrderbyColumns(); final List<Expression> selectColumns = q.getSelectColumns(); final Expression having = q.getHaving(); final Expression where = q.getWhere(); inject(fromColumns); inject(groupbyColumns); inject(orderbyColumns); inject(selectColumns); inject(having, where); final List<String> query = new ArrayList<>(); // SELECT CLAUSE query.add("SELECT" + (q.isDistinct() ? " DISTINCT" : "")); final List<String> querySelectColumns = new ArrayList<>(); for (Expression dbe : selectColumns) { if (dbe instanceof Query) { querySelectColumns .add("(" + dbe.translate() + (!dbe.isAliased() ? ")" : ") AS " + quotize(dbe.getAlias()))); } else { querySelectColumns .add(dbe.translate() + (!dbe.isAliased() ? "" : " AS " + quotize(dbe.getAlias()))); } } query.add(join(querySelectColumns, ", ")); // FROM CLAUSE if (!fromColumns.isEmpty()) { query.add("FROM"); final List<String> queryFromColumns = new ArrayList<>(); for (Expression dbe : fromColumns) { final List<String> insideFrom = new ArrayList<>(); if (dbe instanceof Query) { insideFrom .add("(" + dbe.translate() + (!dbe.isAliased() ? ")" : ") " + quotize(dbe.getAlias()))); } else { insideFrom.add(dbe.translate() + (!dbe.isAliased() ? "" : " " + quotize(dbe.getAlias()))); } final List<Join> joins = dbe.getJoins(); if (!joins.isEmpty()) { for (Join j : joins) { inject(j); insideFrom.add(j.translate()); } } queryFromColumns.add(join(insideFrom, " ")); } query.add(join(queryFromColumns, ", ")); } // WHERE CLAUSE if (where != null) { query.add("WHERE"); query.add(where.translate()); } // GROUP BY CLAUSE if (!groupbyColumns.isEmpty()) { query.add("GROUP BY"); final List<String> queryGroupByColumns = new ArrayList<>(); for (Expression column : groupbyColumns) { queryGroupByColumns.add(column.translate()); } query.add(join(queryGroupByColumns, ", ")); } // HAVING CLAUSE if (having != null) { query.add("HAVING"); query.add(having.translate()); } // ORDER BY if (!orderbyColumns.isEmpty()) { query.add("ORDER BY"); final List<String> queryOrderByColumns = new ArrayList<>(); for (Expression column : orderbyColumns) { queryOrderByColumns.add(column.translate()); } query.add(join(queryOrderByColumns, ", ")); } // LIMIT AND OFFSET if (q.getLimit() > 0) { if (q.getOffset() > 0) { query.add(String.format("LIMIT %d OFFSET %d", q.getLimit(), q.getOffset())); } else { query.add(String.format("LIMIT %d", q.getLimit())); } } String finalQuery = join(query, " "); return q.isEnclosed() ? ("(" + finalQuery + ")") : finalQuery; } @Override public String translate(View v) { final Expression as = v.getAs(); final String name = v.getName(); inject(as); final List<String> res = new ArrayList<String>(); res.add("CREATE"); if (v.isReplace()) { res.add("OR REPLACE"); } res.add("VIEW"); res.add(quotize(name)); res.add("AS " + as.translate()); return join(res, " "); } @Override public String translate(DbColumn c) { switch (c.getDbColumnType()) { case BOOLEAN: return "BOOLEAN"; case DOUBLE: return "DOUBLE PRECISION"; case INT: if (c.isAutoInc()) { return "SERIAL"; } else { return "INT"; } case LONG: if (c.isAutoInc()) { return "BIGSERIAL"; } else { return "BIGINT"; } case STRING: return format("VARCHAR(%s)", c.isSizeSet() ? c.getSize().toString() : properties.getProperty(VARCHAR_SIZE)); case CLOB: return "TEXT"; case BLOB: return format("BYTEA"); default: throw new DatabaseEngineRuntimeException( format("Mapping not found for '%s'. Please report this error.", c.getDbColumnType())); } } @Override public String translateEscape() { return "\""; } @Override public String translateTrue() { return "TRUE"; } @Override public String translateFalse() { return "FALSE"; } }