/**
* @see
* net.sourceforge.squirrel_sql.fw.dialects.DB2DialectExt#getAddUniqueConstraintSQL(java.lang.String,
* java.lang.String, net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo[],
* net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
* net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
*/
@Override
public String[] getAddUniqueConstraintSQL(
String tableName,
String constraintName,
TableColumnInfo[] columns,
DatabaseObjectQualifier qualifier,
SqlGenerationPreferences prefs) {
ArrayList<String> result = new ArrayList<String>();
ArrayList<String> columnNotNullAlters = new ArrayList<String>();
// Derby requires that columns be not-null before applying a unique constraint
final boolean specifyColumnType = false;
final String alterClause = DialectUtils.ALTER_COLUMN_CLAUSE;
for (TableColumnInfo column : columns) {
if (column.isNullable().equalsIgnoreCase("YES")) {
columnNotNullAlters.add(
DialectUtils.getColumnNullableAlterSQL(
column, false, this, alterClause, specifyColumnType, qualifier, prefs));
}
}
result.addAll(columnNotNullAlters);
result.add(
DialectUtils.getAddUniqueConstraintSQL(
tableName, constraintName, columns, qualifier, prefs, this));
return result.toArray(new String[result.size()]);
}
/**
* @see
* net.sourceforge.squirrel_sql.fw.dialects.DB2DialectExt#getColumnTypeAlterSQL(net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo,
* net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo,
* net.sourceforge.squirrel_sql.fw.dialects.DatabaseObjectQualifier,
* net.sourceforge.squirrel_sql.fw.dialects.SqlGenerationPreferences)
*/
@Override
public List<String> getColumnTypeAlterSQL(
final TableColumnInfo from,
final TableColumnInfo to,
final DatabaseObjectQualifier qualifier,
final SqlGenerationPreferences prefs)
throws UnsupportedOperationException {
if (from.getDataType() != to.getDataType()) {
throw new UnsupportedOperationException(i18n.TYPE_MESSAGE);
}
if (from.getDataType() != Types.VARCHAR) {
throw new UnsupportedOperationException(i18n.VARCHAR_MESSAGE);
}
if (from.getColumnSize() > to.getColumnSize()) {
throw new UnsupportedOperationException(i18n.COLUMN_LENGTH_MESSAGE);
}
return super.getColumnTypeAlterSQL(from, to, qualifier, prefs);
}
public ExtendedColumnInfo(TableColumnInfo info, String simpleTableName) {
_columnName = info.getColumnName();
_columnType = info.getTypeName();
_columnTypeID = info.getDataType();
_columnSize = info.getColumnSize();
_decimalDigits = info.getDecimalDigits();
_remarks = info.getRemarks();
if ("YES".equals(info.isNullable())) {
_nullable = true;
} else {
_nullable = false;
}
_cat = info.getCatalogName();
_schem = info.getSchemaName();
_simpleTableName = simpleTableName;
_qualifiedName = _cat + "." + _schem + "." + _simpleTableName + "." + _columnName;
}
/**
* Create table script for selected node.
*
* @see org.eclipse.jface.action.IAction#run()
*/
@Override
public void run() {
TableNode tableNode = (TableNode) _selectedNodes[0];
ITableInfo info = tableNode.getTableInfo();
StringBuffer buf = new StringBuffer(4 * 1024);
String sep = System.getProperty("line.separator");
try {
SQLDatabaseMetaData metaData = tableNode.getSession().getMetaData();
ArrayList<String> pks = new ArrayList<String>();
PrimaryKeyInfo[] pksInfo = metaData.getPrimaryKey(info);
for (PrimaryKeyInfo pkInfo : pksInfo) pks.add(pkInfo.getColumnName());
TableColumnInfo[] columnsInfo = metaData.getColumnInfo(info);
String tableName = _selectedNodes[0].getQualifiedName();
buf.append("CREATE TABLE ");
buf.append(tableName);
buf.append("(");
for (TableColumnInfo col : columnsInfo) {
// String columnName = resultSet.getString(4);
// String typeName = resultSet.getString(6);
// String columnSize = resultSet.getString(7);
// String decimalDigits = resultSet.getString(9);
// String defaultValue = resultSet.getString(13);
boolean notNull = "NO".equalsIgnoreCase(col.isNullable());
String sLower = col.getColumnName().toLowerCase();
buf.append(sep);
buf.append(col.getColumnName() + " ");
buf.append(col.getTypeName());
boolean bNumeric = false;
if (sLower.equals("numeric") || sLower.equals("number") || sLower.equals("decimal"))
bNumeric = true;
if (sLower.indexOf("char") != -1 || sLower.indexOf("int") != -1) {
buf.append("(");
buf.append(col.getColumnSize());
buf.append(")");
} else if (bNumeric) {
buf.append("(");
buf.append(col.getColumnSize());
if (col.getDecimalDigits() > 0) buf.append(col.getDecimalDigits());
buf.append(")");
}
if (pks.size() == 1 && pks.get(0).equals(col.getColumnName())) {
buf.append(" PRIMARY KEY");
}
String defaultValue = col.getDefaultValue();
if (defaultValue != null && !defaultValue.equals("")) {
buf.append(" default ");
boolean isSystemValue = bNumeric;
if (defaultValue.equalsIgnoreCase("CURRENT_TIMESTAMP")) {
isSystemValue = true;
}
if (!isSystemValue) buf.append("'");
buf.append(defaultValue);
if (!isSystemValue) buf.append("'");
}
if (notNull) {
buf.append(" not null");
}
buf.append(",");
}
buf.deleteCharAt(buf.length() - 1);
buf.append(")" + sep);
SQLEditorInput input =
new SQLEditorInput(
"SQL Editor (" + SQLExplorerPlugin.getDefault().getEditorSerialNo() + ").sql");
input.setUser(_selectedNodes[0].getSession().getUser());
IWorkbenchPage page =
SQLExplorerPlugin.getDefault().getWorkbench().getActiveWorkbenchWindow().getActivePage();
SQLEditor editorPart =
(SQLEditor)
page.openEditor(input, "com.safi.workshop.sqlexplorer.plugin.editors.SQLEditor");
editorPart.setText(buf.toString());
} catch (SQLException e) {
SQLExplorerPlugin.error("Error creating export script", e);
} catch (PartInitException e) {
SQLExplorerPlugin.error("Error creating export script", e);
}
}
/** Create the <TT>IDataSet</TT> to be displayed in this tab. */
protected IDataSet createDataSet() throws DataSetException {
final ISession session = getSession();
final ISQLConnection conn = session.getSQLConnection();
ISQLDatabaseMetaData md = session.getMetaData();
try {
final Statement stmt = conn.createStatement();
try {
final SessionProperties props = session.getProperties();
if (props.getContentsLimitRows()) {
try {
stmt.setMaxRows(props.getContentsNbrRowsToShow());
} catch (Exception ex) {
s_log.error("Error on Statement.setMaxRows()", ex);
}
}
final ITableInfo ti = getTableInfo();
/**
* When the SessionProperties are set to read-only (either table or text) but the user has
* selected "Make Editable" on the Popup menu, we want to limit the edit capability to only
* that table, and only for as long as the user is looking at that one table. When the user
* switches away to another table, that new table should not be editable.
*/
final String currentTableName = ti.getQualifiedName();
if (!currentTableName.equals(previousTableName)) {
previousTableName = currentTableName; // needed to prevent an infinite loop
_dataSetUpdateableTableModel.setEditModeForced(false);
/**
* Tell the GUI to rebuild itself. Unfortunately, this has the side effect of calling this
* same function another time. The second call does not seem to be a problem, but we need
* to have reset the previousTableName before makeing this call or we will be in an
* infinite loop.
*/
// props.forceTableContentsOutputClassNameChange();
}
/**
* If the table has a pseudo-column that is the best unique identifier for the rows (like
* Oracle's rowid), then we want to include that field in the query so that it will be
* available if the user wants to edit the data later.
*/
String pseudoColumn = "";
try {
BestRowIdentifier[] rowIDs = md.getBestRowIdentifier(ti);
for (int i = 0; i < rowIDs.length; ++i) {
short pseudo = rowIDs[i].getPseudoColumn();
if (pseudo == DatabaseMetaData.bestRowPseudo) {
pseudoColumn = " ," + rowIDs[i].getColumnName();
break;
}
}
}
// Some DBMS's (EG Think SQL) throw an exception on a call to
// getBestRowIdentifier.
catch (Throwable th) {
if (s_log.isDebugEnabled()) {
s_log.debug("getBestRowIdentifier not supported for table " + currentTableName, th);
}
}
// TODO: - Col - Add method to Databasemetadata that returns array
// of objects for getBestRowIdentifier. For PostgreSQL put this kludge in
// the new function. THis way all the kludges are kept in one place.
//
// KLUDGE!!!!!!
//
// For some DBs (e.g. PostgreSQL) there is actually a pseudo-column
// providing the rowId, but the getBestRowIdentifier function is not
// implemented. This kludge hardcodes the knowledge that specific
// DBs use a specific pseudo-column. Additionally, as of pg 8.1,
// you must create the table using "WITH OID" appended to the create
// statement. Otherwise, OID column is not available by default.
//
if (pseudoColumn.length() == 0) {
if (DialectFactory.isPostgreSQL(md)) {
pseudoColumn = ", oid";
}
if (DialectFactory.isOracle(md)) {
pseudoColumn = ", ROWID";
}
}
ResultSet rs = null;
try {
// Note. Some DBMSs such as Oracle do not allow:
// "select *, rowid from table"
// You cannot have any column name in the columns clause
// if you have * in there. Aliasing the table name seems to
// be the best way to get around the problem.
final StringBuffer buf = new StringBuffer();
buf.append("select tbl.*")
.append(pseudoColumn)
.append(" from ")
.append(ti.getQualifiedName())
.append(" tbl");
String clause =
_sqlFilterClauses.get(WhereClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" where ").append(clause);
}
clause =
_sqlFilterClauses.get(
OrderByClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" order by ").append(clause);
}
if (s_log.isDebugEnabled()) {
s_log.debug("createDataSet running SQL: " + buf.toString());
}
showWaitDialog(stmt);
rs = stmt.executeQuery(buf.toString());
} catch (SQLException ex) {
if (s_log.isDebugEnabled()) {
s_log.debug("createDataSet: exception from pseudocolumn query - " + ex, ex);
}
// We assume here that if the pseudoColumn was used in the query,
// then it was likely to have caused the SQLException. If not,
// (length == 0), then retrying the query won't help - just throw
// the exception.
if (pseudoColumn.length() == 0) {
throw ex;
}
// pseudocolumn query failed, so reset it. Otherwise, we'll
// mistake the last column for a pseudocolumn and make it
// uneditable
pseudoColumn = "";
// Some tables have pseudo column primary keys and others
// do not. JDBC on some DBMSs does not handle pseudo
// columns 'correctly'. Also, getTables returns 'views' as
// well as tables, so the thing we are looking at might not
// be a table. (JDBC does not give a simple way to
// determine what we are looking at since the type of
// object is described in a DBMS-specific encoding.) For
// these reasons, rather than testing for all these
// conditions, we just try using the pseudo column info to
// get the table data, and if that fails, we try to get the
// table data without using the pseudo column.
// TODO: Should we change the mode from editable to
// non-editable?
final StringBuffer buf = new StringBuffer();
buf.append("select *").append(" from ").append(ti.getQualifiedName()).append(" tbl");
String clause =
_sqlFilterClauses.get(WhereClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" where ").append(clause);
}
clause =
_sqlFilterClauses.get(
OrderByClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" order by ").append(clause);
}
rs = stmt.executeQuery(buf.toString());
}
final ResultSetDataSet rsds = new ResultSetDataSet();
// to allow the fw to save and reload user options related to
// specific columns, we construct a unique name for the table
// so the column can be associcated with only that table.
// Some drivers do not provide the catalog or schema info, so
// those parts of the name will end up as null. That's ok since
// this string is never viewed by the user and is just used to
// distinguish this table from other tables in the DB.
// We also include the URL used to connect to the DB so that
// the same table/DB on different machines is treated differently.
rsds.setContentsTabResultSet(
rs, _dataSetUpdateableTableModel.getFullTableName(), DialectFactory.getDialectType(md));
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
}
}
// KLUDGE:
// We want some info about the columns to be available for validating the
// user input during cell editing operations. Ideally we would get that
// info inside the ResultSetDataSet class during the creation of the
// columnDefinition objects by using various functions in ResultSetMetaData
// such as isNullable(idx). Unfortunately, in at least some DBMSs (e.g.
// Postgres, HSDB) the results of those calls are not the same (and are less accurate
// than) the SQLMetaData.getColumns() call used in ColumnsTab to get the column info.
// Even more unfortunate is the fact that the set of attributes reported on by the two
// calls is not the same, with the ResultSetMetadata listing things not provided by
// getColumns. Most of the data provided by the ResultSetMetaData calls is correct.
// However, the nullable/not-nullable property is not set correctly in at least two
// DBMSs, while it is correct for those DBMSs in the getColumns() info. Therefore,
// we collect the collumn nullability information from getColumns() and pass that
// info to the ResultSet to override what it got from the ResultSetMetaData.
TableColumnInfo[] columnInfos = md.getColumnInfo(getTableInfo());
final ColumnDisplayDefinition[] colDefs =
rsds.getDataSetDefinition().getColumnDefinitions();
// get the nullability information and pass it into the ResultSet
// Unfortunately, not all DBMSs provide the column number in object 17 as stated in the
// SQL documentation, so we have to guess that the result set is in column order
for (int i = 0; i < columnInfos.length; i++) {
boolean isNullable = true;
TableColumnInfo info = columnInfos[i];
if (info.isNullAllowed() == DatabaseMetaData.columnNoNulls) {
isNullable = false;
}
if (i < colDefs.length) {
colDefs[i].setIsNullable(isNullable);
}
}
// ?? remember which column is the rowID (if any) so we can
// ?? prevent editing on it
if (pseudoColumn.length() > 0) {
_dataSetUpdateableTableModel.setRowIDCol(rsds.getColumnCount() - 1);
}
return rsds;
} finally {
SQLUtilities.closeStatement(stmt);
}
} catch (SQLException ex) {
throw new DataSetException(ex);
} finally {
disposeWaitDialog();
}
}
