/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you 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 org.apache.empire.db.codegen;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.empire.data.DataType;
import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBCommandExpr;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBRelation;
import org.apache.empire.db.DBTable;
import org.apache.empire.db.DBTableColumn;
import org.apache.empire.db.DBView;
import org.apache.empire.db.DBView.DBViewColumn;
import org.apache.empire.db.codegen.util.DBUtil;
import org.apache.empire.exceptions.ItemNotFoundException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* This class is used to create a in memory DBDatabase of a given SQLConnection
* and Configuration
*
* @author Benjamin Venditti
*/
public class CodeGenParser {
public static class InMemoryDatabase extends DBDatabase {
private static final long serialVersionUID = 1L;
}
public static class InMemoryView extends DBView {
private final static long serialVersionUID = 1L;
public InMemoryView(String name, DBDatabase db) {
super(name, db);
}
public DBViewColumn addCol(String columnName,DataType dataType)
{
return addColumn(columnName, dataType);
}
@Override
public DBCommandExpr createCommand() {
return null;
}
}
private static final Logger log = LoggerFactory.getLogger(CodeGenParser.class);
private DatabaseMetaData dbMeta;
private Connection con;
private CodeGenConfig config;
/**
* create a empty in memory Database and populates it
*/
public CodeGenParser(CodeGenConfig config) {
this.config = config;
}
/**
* returns the populated DBDatabase
*/
public DBDatabase loadDbModel() {
DBDatabase db = new InMemoryDatabase();
try {
con = openJDBCConnection(config);
populateDatabase(db);
}
catch (SQLException e)
{
throw new RuntimeException("Unable to read database metadata: " + e.getMessage(), e);
}
finally
{
DBUtil.close(con, log);
}
return db;
}
/**
* Opens and returns a JDBC-Connection.
* JDBC url, user and password for the connection are obained from the SampleConfig bean
* Please use the config.xml file to change connection params.
*/
private Connection openJDBCConnection(CodeGenConfig config) throws SQLException{
log.info("Connecting to Database'" + config.getJdbcURL() + "' / User=" + config.getJdbcUser());
Connection conn = null;
try {
Class.forName(config.getJdbcClass()).newInstance();
}catch(Exception ex){
throw new SQLException("Could not load database driver: " + config.getJdbcClass());
}
conn = DriverManager.getConnection(config.getJdbcURL(), config.getJdbcUser(), config.getJdbcPwd());
log.info("Connected successfully");
return conn;
}
/**
* Queries the metadata of the database for tables and vies and populates the
* database with those
* @throws SQLException
*/
private void populateDatabase(DBDatabase db) throws SQLException {
ResultSet tables = null;
ArrayList<String> populatedTables=new ArrayList<String>();
try{
this.dbMeta = con.getMetaData();
String[] tablePatterns = {null}; // Could be null, so start that way.
if(config.getDbTablePattern() != null)
tablePatterns = config.getDbTablePattern().split(","); // Support a comma separated list of table patterns (i.e. specify a list of table names in the config file).
int tableCount = 0; // Moved to be outside table pattern loop.
int viewCount = 0;
for(String pattern : tablePatterns){
// Get table metadata
tables = dbMeta.getTables(
config.getDbCatalog(),
config.getDbSchema(),
pattern == null ? pattern: pattern.trim(),
new String[] { "TABLE", "VIEW" });
// Add all tables and views
while (tables.next()) {
String tableName = tables.getString("TABLE_NAME");
String tableType = tables.getString("TABLE_TYPE");
// Ignore system tables containing a '$' symbol (required for Oracle!)
if (tableName.indexOf('$') >= 0) {
log.info("Ignoring system table " + tableName);
continue;
}
log.info(tableType + ": " + tableName);
if(tableType.equalsIgnoreCase("VIEW")){
InMemoryView view = new InMemoryView(tableName, db);
populateView(view);
viewCount++;
} else {
DBTable table = new DBTable(tableName, db);
populateTable(table);
populatedTables.add(tableName);
tableCount++;
}
}
}
// Add all relations
gatherRelations(db, dbMeta, populatedTables);
if (tableCount==0 && viewCount==0) {
// getTables returned no result
String info = "catalog="+config.getDbCatalog();
info += "/ schema="+config.getDbSchema();
info += "/ pattern="+config.getDbTablePattern();
log.warn("DatabaseMetaData.getTables() returned no tables or views! Please check parameters: "+info);
log.info("Available catalogs: " + getCatalogs(dbMeta));
log.info("Available schemata: " + getSchemata(dbMeta));
}
} finally {
DBUtil.close(tables, log);
}
}
private void gatherRelations(DBDatabase db, DatabaseMetaData dbMeta, ArrayList<String> tables) throws SQLException{
ResultSet relations = null;
String fkTableName, pkTableName, fkColName, pkColName, relName;
DBTableColumn fkCol, pkCol;
DBTable fkTable, pkTable;
DBColumn col;
// Add all Relations
for (String tableName :tables) {
// check for foreign-keys
relations = dbMeta.getImportedKeys(config.getDbCatalog(), config .getDbSchema(), tableName);
while (relations.next()) {
pkCol=fkCol=null;
fkTableName=relations.getString("FKTABLE_NAME");
pkTableName=relations.getString("PKTABLE_NAME");
fkColName=relations.getString("FKCOLUMN_NAME");
pkColName=relations.getString("PKCOLUMN_NAME");
// Detect relation name
relName=relations.getString("FK_NAME");
if (StringUtils.isEmpty(relName))
relName=fkTableName+"."+fkColName+"-"+pkTableName+"."+pkColName;
pkTable = db.getTable(pkTableName);
fkTable = db.getTable(fkTableName);
// check if both tables really exist in the model
if(pkTable==null || fkTable==null){
log.error("Unable to add the relation \""+relName+"\"! One of the tables could not be found.");
continue;
}
col=pkTable.getColumn(pkColName);
if(col instanceof DBTableColumn)
pkCol = (DBTableColumn) col;
col=fkTable.getColumn(fkColName);
if(col instanceof DBTableColumn)
fkCol = (DBTableColumn) col;
// check if both columns really exist in the model
if(fkCol==null || pkCol==null){
log.error("Unable to add the relation \""+relName+"\"! One of the columns could not be found.");
continue;
}
// add the relation
DBRelation.DBReference reference = fkCol.referenceOn(pkCol);
DBRelation.DBReference[] refs = null;
DBRelation r = db.getRelation(relName);
if (r!=null) {
DBRelation.DBReference[] refsOld = r.getReferences();
refs = new DBRelation.DBReference[refsOld.length+1];
int i=0;
for (; i<refsOld.length; i++)
refs[i]=refsOld[i];
refs[i]=reference;
// remove old relation
db.getRelations().remove(r);
} else {
refs = new DBRelation.DBReference[] { reference };
}
// Add a new relation
db.addRelation(relName, refs);
log.info("Added relation (FK-PK): "+relName);
}
}
}
private String getCatalogs(DatabaseMetaData dbMeta) throws SQLException {
String retVal = "";
ResultSet rs = dbMeta.getCatalogs();
while (rs.next()) {
retVal += rs.getString("TABLE_CAT") + ", ";
}
if(retVal.length()>2)
retVal=retVal.substring(0,retVal.length()-2);
return retVal;
}
private String getSchemata(DatabaseMetaData dbMeta) throws SQLException {
String retVal = "";
ResultSet rs = dbMeta.getSchemas();
while (rs.next()) {
retVal += rs.getString("TABLE_SCHEM") + ", ";
}
if(retVal.length()>2)
retVal=retVal.substring(0,retVal.length()-2);
return retVal;
}
/**
* queries the metadata for columns of a specific table and populates the
* table with that information
* @throws SQLException
*/
private void populateTable(DBTable t) throws SQLException {
List<String> pkCols = this.findPkColumns(t.getName());
String lockColName = config.getTimestampColumn();
DBColumn[] keys = new DBColumn[pkCols.size()];
ResultSet rs = null;
try {
rs = dbMeta.getColumns(config.getDbCatalog(), config.getDbSchema(),
t.getName(), null);
int i=0;
while (rs.next()) {
DBTableColumn c = addColumn(t, rs);
// check if it is a KeyColumn
if (pkCols.contains(c.getName()))
keys[i++] = c;
// check if it is the Timestamp/Locking Column
if (lockColName!=null && c.getName().equalsIgnoreCase(lockColName))
t.setTimestampColumn(c);
}
// Check whether all key columns have been set
for (i=0; i<keys.length; i++)
if (keys[i]==null){
throw new ItemNotFoundException(pkCols.get(i));
}
if(keys.length > 0){
t.setPrimaryKey(keys);
}
} finally {
DBUtil.close(rs, log);
}
}
/**
* queries the metadata for columns of a specific table and populates the
* table with that information
* @throws SQLException
*/
private void populateView(InMemoryView v) throws SQLException {
ResultSet rs = null;
try {
rs = dbMeta.getColumns(config.getDbCatalog(), config.getDbSchema(),
v.getName(), null);
while (rs.next()) {
addColumn(v, rs);
}
} finally {
DBUtil.close(rs, log);
}
}
/**
* Returns a list of column names that define the primarykey of the given
* table.
* @throws SQLException
*/
private List<String> findPkColumns(String tableName) throws SQLException {
List<String> cols = new ArrayList<String>();
ResultSet rs = null;
try {
rs = dbMeta.getPrimaryKeys(config.getDbCatalog(), config
.getDbSchema(), tableName);
while (rs.next()) {
cols.add(rs.getString("COLUMN_NAME"));
}
} finally {
DBUtil.close(rs, log);
}
return cols;
}
/**
* Adds DBColumn object to the given DBTable. The DBColumn is created from
* the given ResultSet
*/
private DBTableColumn addColumn(DBTable t, ResultSet rs)
throws SQLException {
String name = rs.getString("COLUMN_NAME");
DataType empireType = getEmpireDataType(rs.getInt("DATA_TYPE"));
double colSize = Double.parseDouble(""+rs.getInt("COLUMN_SIZE") + '.' +rs.getInt("DECIMAL_DIGITS"));
boolean required = false;
String defaultValue = rs.getString("COLUMN_DEF");
if (rs.getString("IS_NULLABLE").equalsIgnoreCase("NO"))
required = true;
// The following is a hack for MySQL which currently gets sent a string "CURRENT_TIMESTAMP" from the Empire-db driver for MySQL.
// This will avoid the driver problem because CURRENT_TIMESTAMP in the db will just do the current datetime.
// Essentially, Empire-db needs the concept of default values of one type that get mapped to another.
// In this case, MySQL "CURRENT_TIMESTAMP" for Types.TIMESTAMP needs to emit from the Empire-db driver the null value and not "CURRENT_TIMESTAMP".
if(rs.getInt("DATA_TYPE") == Types.TIMESTAMP && defaultValue != null && defaultValue.equals("CURRENT_TIMESTAMP")){
required = false; // It is in fact not required even though MySQL schema is required because it has a default value. Generally, should Empire-db emit (required && defaultValue != null) to truly determine if a column is required?
defaultValue = null; // If null (and required per schema?) MySQL will apply internal default value.
}
// AUTOINC indicator is not in java.sql.Types but rather meta data from DatabaseMetaData.getColumns()
// getEmpireDataType() above is not enough to support AUTOINC as it will only return DataType.INTEGER
DataType originalType = empireType;
ResultSetMetaData metaData = rs.getMetaData();
int colCount = metaData.getColumnCount();
String colName;
for (int i = 1; i <= colCount; i++) {
colName = metaData.getColumnName(i);
// MySQL matches on IS_AUTOINCREMENT column.
// SQL Server matches on TYPE_NAME column with identity somewhere in the string value.
if ((colName.equalsIgnoreCase("IS_AUTOINCREMENT") && rs.getString(i).equalsIgnoreCase("YES")) ||
(colName.equals("TYPE_NAME") && rs.getString(i).matches(".*(?i:identity).*"))){
empireType = DataType.AUTOINC;
}
}
// Move from the return statement below so we can add
// some AUTOINC meta data to the column to be used by
// the ParserUtil and ultimately the template.
log.info("\tCOLUMN:\t" + name + " ("+empireType+")");
DBTableColumn col = t.addColumn(name, empireType, colSize, required, defaultValue);
// We still need to know the base data type for this AUTOINC
// because the Record g/setters need to know this, right?
// So, let's add it as meta data every time the column is AUTOINC
// and reference it in the template.
if(empireType.equals(DataType.AUTOINC))
col.setAttribute("AutoIncDataType", originalType);
return col;
}
/**
* Adds DBColumn object to the given DBTable. The DBColumn is created from
* the given ResultSet
*/
private DBViewColumn addColumn(InMemoryView v, ResultSet rs)
throws SQLException {
String name = rs.getString("COLUMN_NAME");
DataType empireType = getEmpireDataType(rs.getInt("DATA_TYPE"));
log.info("\tCOLUMN:\t" + name + " ("+empireType+")");
return v.addCol(name, empireType);
}
/**
* converts a SQL DataType to a EmpireDataType
*/
private DataType getEmpireDataType(int sqlType) {
DataType empireType = DataType.UNKNOWN;
switch (sqlType) {
case Types.INTEGER:
case Types.SMALLINT:
case Types.TINYINT:
case Types.BIGINT:
empireType = DataType.INTEGER;
break;
case Types.VARCHAR:
empireType = DataType.TEXT;
break;
case Types.DATE:
empireType = DataType.DATE;
break;
case Types.TIMESTAMP:
case Types.TIME:
empireType = DataType.DATETIME;
break;
case Types.CHAR:
empireType = DataType.CHAR;
break;
case Types.DOUBLE:
case Types.FLOAT:
case Types.REAL:
empireType = DataType.FLOAT;
break;
case Types.DECIMAL:
case Types.NUMERIC:
empireType = DataType.DECIMAL;
break;
case Types.BIT:
case Types.BOOLEAN:
empireType = DataType.BOOL;
break;
case Types.CLOB:
case Types.LONGVARCHAR:
empireType = DataType.CLOB;
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.BLOB:
empireType = DataType.BLOB;
break;
default:
empireType = DataType.UNKNOWN;
log.warn("SQL column type " + sqlType + " not supported.");
}
log.debug("Mapping date type " + String.valueOf(sqlType) + " to "
+ empireType);
return empireType;
}
}