String[] configColumnNames, String[] sqlColumnNames, String sqlSchema, String sqlTable,
boolean ignoreKeyColumnQueries, int[] filterColumnIndices, DataEntityMetadata tableImportInfo, boolean configAppend)
throws RemoteException
{
int table_id = DataConfig.NULL;
DataConfig dataConfig = getDataConfig();
if (sqlColumnNames == null || sqlColumnNames.length == 0)
throw new RemoteException("No columns were found.");
ConnectionInfo connInfo = getConnectionConfig().getConnectionInfo(connectionName);
if (connInfo == null)
throw new RemoteException(String.format("Connection named \"%s\" does not exist.", connectionName));
String failMessage = String.format("Failed to add DataTable \"%s\" to the configuration.\n", configDataTableName);
String query = null;
try
{
Connection conn = connInfo.getStaticReadOnlyConnection();
// If key column is actually the name of a column, put quotes around it. Otherwise, don't.
String q_sqlKeyColumn;
if (ListUtils.findString(sqlKeyColumn, sqlColumnNames) >= 0)
q_sqlKeyColumn = SQLUtils.quoteSymbol(conn, sqlKeyColumn);
else
q_sqlKeyColumn = sqlKeyColumn;
String q_sqlSecondKeyColumn = secondKeyColumnIndex >= 0
? SQLUtils.quoteSymbol(conn, sqlColumnNames[secondKeyColumnIndex])
: null;
// Write SQL statements into sqlconfig.
// generate and test each query before modifying config file
List<ColumnInfo> columnInfoList = new Vector<ColumnInfo>();
boolean foundMatchingColumnIds = false;
SQLResult filteredValues = null;
if (filterColumnIndices != null && filterColumnIndices.length > 0)
{
// get a list of unique combinations of filter values
String columnList = "";
for (int i = 0; i < filterColumnIndices.length; i++)
{
if (i > 0)
columnList += ",";
columnList += SQLUtils.quoteSymbol(conn, sqlColumnNames[filterColumnIndices[i]]);
}
query = String.format(
"select distinct %s from %s order by %s",
columnList,
SQLUtils.quoteSchemaTable(conn, sqlSchema, sqlTable),
columnList
);
filteredValues = SQLUtils.getResultFromQuery(conn, query, null, true);
// System.out.println(query);
// System.out.println(filteredValues);
}
String queryFormat = "SELECT %s,%s FROM %s";
if (SQLUtils.isOracleServer(conn))
{
// workaround for ambiguous column name when filtering by rownum
queryFormat = "SELECT %s thekey, %s thevalue FROM %s";
}
for (int iCol = 0; iCol < sqlColumnNames.length; iCol++)
{
String q_sqlColumn = sqlColumnNames[iCol];
// System.out.println("columnName: " + columnName +
// "\tkeyColumnName: " + keyColumnName + "\toriginalKeyCol: " +
// originalKeyColumName);
if (ignoreKeyColumnQueries && sqlKeyColumn.equals(q_sqlColumn))
continue;
q_sqlColumn = SQLUtils.quoteSymbol(conn, q_sqlColumn);
// hack
if (secondKeyColumnIndex >= 0)
q_sqlColumn += "," + q_sqlSecondKeyColumn;
// generate column query
query = String.format(
queryFormat,
q_sqlKeyColumn,
q_sqlColumn,
SQLUtils.quoteSchemaTable(conn, sqlSchema, sqlTable)
);
DataEntityMetadata metaQuery = new DataEntityMetadata();
// we don't search public metadata because that would be a separate sql query
// and we only know the entityType.
metaQuery.setPrivateValues(
PrivateMetadata.CONNECTION, connectionName,
PrivateMetadata.SQLQUERY, query
);
if (filteredValues != null)
{
String filteredQuery = buildFilteredQuery(conn, query, filteredValues.columnNames);
metaQuery.setPrivateValues(PrivateMetadata.SQLQUERY, filteredQuery);
// generate one query per unique filter value combination
for (int iRow = 0; iRow < filteredValues.rows.length; iRow++)
{
ColumnInfo info = new ColumnInfo();
columnInfoList.add(info);
info.schema = sqlSchema;
info.table = sqlTable;
info.column = sqlColumnNames[iCol];
info.sqlParamsArray = filteredValues.rows[iRow];
info.sqlParamsStr = CSVParser.defaultParser.createCSVRow(info.sqlParamsArray, true);
info.title = buildFilteredColumnTitle(configColumnNames[iCol], info.sqlParamsArray);
info.query = filteredQuery;
testQueryAndGetDataType(conn, info);
if (configAppend)
{
// try to find a matching column using private metadata: connection, sqlQuery, and sqlParams
metaQuery.setPrivateValues(PrivateMetadata.SQLPARAMS, info.sqlParamsStr);
info.existingColumnId = ListUtils.getFirstSortedItem(
dataConfig.searchPrivateMetadata(metaQuery.privateMetadata, null),
DataConfig.NULL
);
if (info.existingColumnId != DataConfig.NULL)
foundMatchingColumnIds = true;
}
}
}
else
{
ColumnInfo info = new ColumnInfo();
columnInfoList.add(info);
info.schema = sqlSchema;
info.table = sqlTable;
info.column = sqlColumnNames[iCol];
info.title = configColumnNames[iCol];
info.query = query;
testQueryAndGetDataType(conn, info);
if (configAppend)
{
// try to find a matching column using private metadata: connection and sqlQuery
info.existingColumnId = ListUtils.getFirstSortedItem(
dataConfig.searchPrivateMetadata(metaQuery.privateMetadata, null),
DataConfig.NULL
);
if (info.existingColumnId != DataConfig.NULL)
foundMatchingColumnIds = true;
}
}
}
// done generating column info
// determine if columns should be appended to an existing table
int existingTableId = DataConfig.NULL;
if (foundMatchingColumnIds)
{
// get the set of all matching column ids
Set<Integer> columnIds = new HashSet<Integer>();
for (ColumnInfo info : columnInfoList)
columnIds.add(info.existingColumnId);
// find first matching parent table id
for (Relationship r : dataConfig.getRelationships(columnIds))
{
String parentType = dataConfig.getEntityTypes(Arrays.asList(r.parentId)).get(r.parentId);
if (Strings.equal(parentType, EntityType.TABLE))
{
existingTableId = r.parentId;
break;
}
}
}
DataEntityMetadata tableInfo = tableImportInfo == null ? new DataEntityMetadata() : tableImportInfo;
tableInfo.setPublicValues(PublicMetadata.ENTITYTYPE, EntityType.TABLE);
tableInfo.setPrivateValues(
PrivateMetadata.CONNECTION, connectionName,
PrivateMetadata.SQLSCHEMA, sqlSchema,
PrivateMetadata.SQLTABLE, sqlTable,
PrivateMetadata.SQLKEYCOLUMN, sqlKeyColumn
);
if (existingTableId == DataConfig.NULL)
{
// only set title if creating a new table
tableInfo.setPublicValues(PublicMetadata.TITLE, configDataTableName);
table_id = dataConfig.newEntity(tableInfo, DataConfig.NULL, DataConfig.NULL);
}
else
{
table_id = existingTableId;
// update private metadata only
dataConfig.updateEntity(table_id, tableInfo);
}
for (int i = 0; i < columnInfoList.size(); i++)
{
ColumnInfo info = columnInfoList.get(i);
DataEntityMetadata newMeta = new DataEntityMetadata();
// only set title on new columns
if (existingTableId == DataConfig.NULL || info.existingColumnId == DataConfig.NULL)
newMeta.setPublicValues(PublicMetadata.TITLE, info.title);
newMeta.setPublicValues(
PublicMetadata.ENTITYTYPE, EntityType.COLUMN,
PublicMetadata.KEYTYPE, keyType,
PublicMetadata.DATATYPE, info.dataType,
PublicMetadata.PROJECTION, info.projection
);
newMeta.setPrivateValues(
PrivateMetadata.CONNECTION, connectionName,
PrivateMetadata.SQLQUERY, info.query,
PrivateMetadata.SQLSCHEMA, info.schema,
PrivateMetadata.SQLTABLE, info.table,
PrivateMetadata.SQLKEYCOLUMN, sqlKeyColumn,
PrivateMetadata.SQLCOLUMN, info.column
);
if (filteredValues != null)
{
newMeta.setPrivateValues(
PrivateMetadata.SQLPARAMS, info.sqlParamsStr,
PrivateMetadata.SQLFILTERCOLUMNS, CSVParser.defaultParser.createCSVRow(ListUtils.getItems(sqlColumnNames, filterColumnIndices), true)
);
}
// if not updating an existing column, create a new one
if (existingTableId == DataConfig.NULL || info.existingColumnId == DataConfig.NULL)
dataConfig.newEntity(newMeta, table_id, DataConfig.NULL);
else
dataConfig.updateEntity(info.existingColumnId, newMeta);
}
}
catch (SQLException e)
{
throw new RemoteException(failMessage, e);