public static Connection GetConnection()
{
Connection connection = Org.Mockito.Mockito.Mock <DriverForTest.FakeConnection>();
try
{
Statement statement = Org.Mockito.Mockito.Mock <Statement>();
ResultSet results = Org.Mockito.Mockito.Mock <ResultSet>();
Org.Mockito.Mockito.When(results.GetLong(1)).ThenReturn(15L);
Org.Mockito.Mockito.When(statement.ExecuteQuery(Matchers.Any <string>())).ThenReturn
(results);
Org.Mockito.Mockito.When(connection.CreateStatement()).ThenReturn(statement);
DatabaseMetaData metadata = Org.Mockito.Mockito.Mock <DatabaseMetaData>();
Org.Mockito.Mockito.When(metadata.GetDatabaseProductName()).ThenReturn("Test");
Org.Mockito.Mockito.When(connection.GetMetaData()).ThenReturn(metadata);
PreparedStatement reparedStatement0 = Org.Mockito.Mockito.Mock <PreparedStatement>
();
Org.Mockito.Mockito.When(connection.PrepareStatement(AnyString())).ThenReturn(reparedStatement0
);
PreparedStatement preparedStatement = Org.Mockito.Mockito.Mock <PreparedStatement>
();
ResultSet resultSet = Org.Mockito.Mockito.Mock <ResultSet>();
Org.Mockito.Mockito.When(resultSet.Next()).ThenReturn(false);
Org.Mockito.Mockito.When(preparedStatement.ExecuteQuery()).ThenReturn(resultSet);
Org.Mockito.Mockito.When(connection.PrepareStatement(AnyString(), AnyInt(), AnyInt
())).ThenReturn(preparedStatement);
}
catch (SQLException)
{
}
return(connection);
}
/**
* Returns JdbcResultResource of available databases
*/
protected JdbcResultResource getCatalogs(Env env)
{
clearErrors();
try {
if (_dmd == null)
{
_dmd = _conn.getConnection().getMetaData();
}
ResultSet rs = _dmd.getCatalogs();
if (rs != null)
{
return(createResult(_savedStmt, rs));
}
else
{
return(null);
}
}
catch (SQLException e) {
saveErrors(e);
log.log(Level.FINEST, e.ToString(), e);
return(null);
}
}
public JdbcTableMetaData(Env env,
string catalog,
string schema,
string name,
DatabaseMetaData md)
{
_catalog = catalog;
_schema = schema;
_name = name;
_lastModified = env.getCurrentTime();
ResultSet rs = md.getColumns(_catalog, _schema, _name, null);
try {
while (rs.next())
{
// COLUMN_NAME
string columnName = rs.getString(4);
JdbcColumnMetaData column = new JdbcColumnMetaData(this, rs);
_columnMap.put(columnName, column);
}
rs.close();
try {
rs = md.getPrimaryKeys(_catalog, _schema, _name);
while (rs.next())
{
// COLUMN_NAME
string columnName = rs.getString(4);
JdbcColumnMetaData column = _columnMap.get(columnName);
column.setPrimaryKey(true);
}
} catch (SQLException e) {
log.log(Level.FINE, e.ToString(), e);
} finally {
rs.close();
}
rs = md.getIndexInfo(_catalog, _schema, _name, false, true);
while (rs.next())
{
// COLUMN_NAME
string columnName = rs.getString(9);
JdbcColumnMetaData column = _columnMap.get(columnName);
column.setIndex(true);
}
} catch (Exception e) {
log.log(Level.FINE, e.ToString(), e);
} finally {
rs.close();
}
}
private void AddTableTypes(TreeNode typesNode)
{
var schemas = Database.Dmvs.Schemas.ToDictionary(x => x.schema_id);
var types = Database.Dmvs.TableTypes.Where(x => x.IsTableType && x.IsUserDefined)
.Select(x => new { t = x, Name = schemas[x.SchemaID].name + "." + x.Name })
.OrderBy(t => t.Name).ToArray();
if (types.Length == 0)
{
return;
}
var tableNode = typesNode.Nodes.Add("User-Defined Table Types");
foreach (var type in types)
{
var typeNode = tableNode.Nodes.Add(type.Name);
//typeNode.ContextMenu = tableTypeMenu;
// Add columns
var typeColumnsNode = typeNode.Nodes.Add("Columns");
var columns = Database.Dmvs.Columns
.Where(c => c.ObjectID == type.t.TypeTableObjectId)
.ToArray();
foreach (var c in columns)
{
var mainColumn = Database.Dmvs.Columns.Single(x => x.ColumnID == c.ColumnID && x.ObjectID == c.ObjectID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, mainColumn);
typeColumnsNode.Nodes.Add($"{c.Name} ({typeName})");
}
// Add indexes
var tableIndexesNode = typeNode.Nodes.Add("Indexes");
var indexes = Database.Dmvs.Indexes
.Where(i => i.ObjectID == type.t.TypeTableObjectId && i.IndexID > 0)
.OrderBy(i => i.Name);
foreach (var i in indexes)
{
var indexNode = tableIndexesNode.Nodes.Add(i.Name);
// Add index columns
var indexColumns = Database.Dmvs.IndexColumns
.Where(ic => ic.ObjectID == type.t.TypeTableObjectId && ic.IndexID == i.IndexID);
foreach (var ic in indexColumns)
{
var tableTypeColumn = columns.Single(c => c.ColumnID == ic.ColumnID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, tableTypeColumn);
indexNode.Nodes.Add($"{tableTypeColumn.Name} ({typeName})");
}
}
}
}
/// <summary>
/// function to check if user try to either delete or remove
/// last column which is marked as Maintain Version to false
/// then return false, if there is data in respective Version Table
/// </summary>
/// <param name="delete"></param>
/// <returns>bool (true/false)</returns>
public bool CheckVersions(bool delete)
{
bool check = true;
StringBuilder sb = new StringBuilder("SELECT COUNT(AD_Column_ID) FROM AD_Column WHERE AD_Table_ID = " + GetAD_Table_ID() + " AND IsMaintainVersions = 'Y' AND AD_Column_ID != " + GetAD_Column_ID());
if (!delete)
{
if (!(Is_ValueChanged("IsMaintainVersions") && (Util.GetValueOfBool(Get_ValueOld("IsMaintainVersions")) && !Util.GetValueOfBool(Get_Value("IsMaintainVersions")))))
{
check = false;
}
}
else
{
if (!(Util.GetValueOfBool(Get_ValueOld("IsMaintainVersions"))))
{
check = false;
}
}
if (check)
{
int countVerCols = Util.GetValueOfInt(DB.ExecuteScalar(sb.ToString(), null, Get_Trx()));
if (countVerCols == 0)
{
sb.Clear();
sb.Append("SELECT TableName FROM AD_Table WHERE AD_Table_ID = " + GetAD_Table_ID());
string tableName = Util.GetValueOfString(DB.ExecuteScalar(sb.ToString(), null, Get_Trx()));
sb.Clear();
DatabaseMetaData md = new DatabaseMetaData();
//get columns of a table
DataSet dt = md.GetColumns("", DataBase.DB.GetSchema(), tableName + "_Ver");
md.Dispose();
// check whether version table exists in database
if (dt != null && dt.Tables[0] != null && dt.Tables[0].Rows.Count > 0)
{
MTable tblVer = MTable.Get(GetCtx(), tableName + "_Ver");
if (tblVer.HasVersionData(tableName + "_Ver"))
{
// if Maintain Version is marked on Table Level then allow to remove Maintain Version from Column
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + GetAD_Table_ID(), null, Get_Trx())) == "Y")
{
return(true);
}
else
{
log.SaveError("VersionDataExists", Utility.Msg.GetElement(GetCtx(), "VersionDataExists"));
return(false);
}
}
}
}
}
return(true);
}
private DatabaseMetaData getMetaData()
{
if (_dmd == null)
{
_dmd = _conn.getConnection().getMetaData();
}
return(_dmd);
}
/**
*
* returns the URL string for the given connection
* IE: jdbc:mysql://localhost:3306/test
* XXX: PHP returns Localhost via UNIX socket
*/
public string getHostInfo()
{
if (_dmd == null)
{
_dmd = _conn.getConnection().getMetaData();
}
return(_dmd.getURL());
}
public virtual TableMetaData getTableMetaData(string tableName)
{
TableMetaData result = new TableMetaData();
ResultSet resultSet = null;
try
{
try
{
result.TableName = tableName;
DatabaseMetaData metaData = DbSqlSession.SqlSession.Connection.MetaData;
if (DbSqlSessionFactory.POSTGRES.Equals(DbSqlSession.DbSqlSessionFactory.DatabaseType))
{
tableName = tableName.ToLower();
}
string databaseSchema = DbSqlSession.DbSqlSessionFactory.DatabaseSchema;
tableName = DbSqlSession.prependDatabaseTablePrefix(tableName);
resultSet = metaData.getColumns(null, databaseSchema, tableName, null);
while (resultSet.next())
{
string name = resultSet.getString("COLUMN_NAME").ToUpper();
string type = resultSet.getString("TYPE_NAME").ToUpper();
result.addColumnMetaData(name, type);
}
}
catch (SQLException se)
{
throw se;
}
finally
{
if (resultSet != null)
{
resultSet.close();
}
}
}
catch (Exception e)
{
throw LOG.retrieveMetadataException(e);
}
if (result.ColumnNames.Count == 0)
{
// According to API, when a table doesn't exist, null should be returned
result = null;
}
return(result);
}
/// <summary>
/// Add Table in DB
/// </summary>
/// <param name="md">DB Info</param>
/// <param name="catalog">current catalog</param>
/// <param name="schema">schema</param>
private void AddTable(DatabaseMetaData md, String catalog, String schema)
{
DataSet ds = md.GetTables();
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
String tableName = ds.Tables[0].Rows[i]["TABLE_NAME"].ToString();
String tableType = ds.Tables[0].Rows[i]["TABLE_TYPE"].ToString();
// Try to find
MTable table = MTable.Get(GetCtx(), tableName);
// Create new ?
if (table == null)
{
String tn = tableName.ToUpper();
if (tn.StartsWith("T_SELECTION") || // temp table
tn.EndsWith("_VT") || // print trl views
tn.EndsWith("_V") || // views
tn.EndsWith("_V1") || // views
tn.StartsWith("A_A") || // asset tables not yet
tn.StartsWith("A_D") || // asset tables not yet
(tn.IndexOf("$") != -1 // oracle system tables
) ||
(tn.IndexOf("EXPLAIN") != -1 // explain plan
)
)
{
log.Fine("Ignored: " + tableName + " - " + tableType);
continue;
}
//
log.Info(tableName + " - " + tableType);
// Create New
table = new MTable(GetCtx(), 0, Get_Trx());
table.SetEntityType(p_EntityType);
table.SetName(tableName);
table.SetTableName(tableName);
table.SetIsView("VIEW".Equals(tableType));
if (!table.Save())
{
continue;
}
}
// Check Columns
tableName = tableName.ToUpper();
DataSet rsC = md.GetColumns(catalog, schema, tableName);
//addTableColumn(GetCtx(), rsC, table, p_EntityType);
//SubTableUtil.checkStandardColumns(table, p_EntityType);
}
} // addTable
/**
* Returns the client version
* @deprecated
*/
public string getClientInfo()
{
try {
if (_dmd == null)
{
_dmd = _conn.getConnection().getMetaData();
}
return(_dmd.getDatabaseProductVersion());
} catch (SQLException e) {
log.log(Level.FINE, e.ToString(), e);
return(null);
}
}
internal DatabaseMetaData GetMetaData()
{
if (metaData == null)
{
lock (metaDataLock)
{
if (metaData == null)
{
metaData = new DatabaseMetaData(this);
}
Thread.MemoryBarrier();
}
}
return(metaData);
}
/// <summary>
/// <inheritDoc/>
///
/// </summary>
public virtual void SetConf(Configuration conf)
{
dbConf = new DBConfiguration(conf);
try
{
this.connection = CreateConnection();
DatabaseMetaData dbMeta = connection.GetMetaData();
this.dbProductName = StringUtils.ToUpperCase(dbMeta.GetDatabaseProductName());
}
catch (Exception ex)
{
throw new RuntimeException(ex);
}
tableName = dbConf.GetInputTableName();
fieldNames = dbConf.GetInputFieldNames();
conditions = dbConf.GetInputConditions();
}
public virtual bool isTablePresent(string tableName)
{
tableName = prependDatabaseTablePrefix(tableName);
Connection connection = null;
try
{
connection = sqlSession.Connection;
DatabaseMetaData databaseMetaData = connection.MetaData;
ResultSet tables = null;
string schema = this.connectionMetadataDefaultSchema;
if (!string.ReferenceEquals(dbSqlSessionFactory.DatabaseSchema, null))
{
schema = dbSqlSessionFactory.DatabaseSchema;
}
string databaseType = dbSqlSessionFactory.DatabaseType;
if (DbSqlSessionFactory.POSTGRES.Equals(databaseType))
{
tableName = tableName.ToLower();
}
try
{
tables = databaseMetaData.getTables(this.connectionMetadataDefaultCatalog, schema, tableName, JDBC_METADATA_TABLE_TYPES);
return(tables.next());
}
finally
{
if (tables != null)
{
tables.close();
}
}
}
catch (Exception e)
{
throw LOG.checkDatabaseTableException(e);
}
}
/// <summary>
///
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
public DatabaseMetaData GetMetaDataForDatabase(string connStr)
{
DatabaseMetaData dmd = new DatabaseMetaData();
StoredProcedureMetadataAccess spma = new StoredProcedureMetadataAccess();
TableMetadataAccess tma = new TableMetadataAccess();
ViewMetadataAccess vma = new ViewMetadataAccess();
dmd.Tables = tma.GetMasterTableMetadata(connStr);
dmd.Views = vma.GetMasterViewMetadata(connStr);
ColumnMetadataAccess cma = new ColumnMetadataAccess();
foreach (var item in dmd.Views)
{
item.Columns = cma.GetColumnMetadata(item.Level1Name, item.Schema, connStr, Level1Types.View);
}
dmd.StoredProcedures = spma.GetStoredProcedureMetaData(connStr);
return(dmd);
}
} // prepare
/// <summary>
/// Process
/// </summary>
/// <returns></returns>
protected override String DoIt()
{
if (p_AD_Table_ID == 0)
{
throw new Exception("@NotFound@ @AD_Table_ID@ " + p_AD_Table_ID);
}
log.Info("EntityType=" + p_EntityType
+ ", AllTables=" + p_AllTables
+ ", AD_Table_ID=" + p_AD_Table_ID);
Trx trx = Trx.Get("getDatabaseMetaData");
DatabaseMetaData md = new DatabaseMetaData();
string catalog = "";
string schema = DataBase.DB.GetSchema();
if (p_AllTables)
{
AddTable(md, catalog, schema);
}
else
{
MTable table = new MTable(GetCtx(), p_AD_Table_ID, Get_Trx());
if ((table == null) || (table.Get_ID() == 0))
{
throw new Exception("@NotFound@ @AD_Table_ID@ " + p_AD_Table_ID);
}
log.Info(table.GetTableName() + ", EntityType=" + p_EntityType);
String tableName = table.GetTableName();
//tableName = tableName.ToUpper();
DataSet rs = md.GetColumns(catalog, schema, tableName.ToUpper());
AddTableColumn(GetCtx(), rs, table, p_EntityType);
SubTableUtil.CheckStandardColumns(table, p_EntityType);
}
md.Dispose();
trx.Close();
return("#" + m_count);
}
/// <summary>
/// Sync column or table passed in the parameter
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <param name="noColumns">OUT parameter, returns 0 if table is being synched for the first time</param>
/// <returns>string message</returns>
public static string SyncColumn(MTable table, MColumn column, out int noColumns)
{
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";
String schema = DataBase.DB.GetSchema();
//get table name
string tableName = table.GetTableName();
noColumns = 0;
string sql = null;
//get columns of a table
DataSet dt = md.GetColumns(catalog, schema, tableName);
md.Dispose();
//for each column
for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
{
string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
if (!columnName.Equals(column.GetColumnName().ToLower()))
{
continue;
}
//check if column is null or not
string dtColumnName = "is_nullable";
string value = "YES";
//if database is oracle
if (DatabaseType.IsOracle)
{
dtColumnName = "NULLABLE";
value = "Y";
}
bool notNull = false;
//check if column is null
if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
{
notNull = false;
}
else
{
notNull = true;
}
//............................
//if column is virtual column then alter table and drop this column
if (column.IsVirtualColumn())
{
sql = "ALTER TABLE " + table.GetTableName()
+ " DROP COLUMN " + columnName;
}
else
{
sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
noColumns++;
break;
}
}
dt = null;
//while (rs.next())
//{
// noColumns++;
// String columnName = rs.getString ("COLUMN_NAME");
// if (!columnName.equalsIgnoreCase(column.getColumnName()))
// continue;
// // update existing column
// boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
// if (column.isVirtualColumn())
// sql = "ALTER TABLE " + table.getTableName()
// + " DROP COLUMN " + columnName;
// else
// sql = column.getSQLModify(table, column.isMandatory() != notNull);
// break;
//}
//rs.close();
//rs = null;
// No Table
if (noColumns == 0)
{
sql = table.GetSQLCreate();
}
// No existing column
else if (sql == null)
{
if (column.IsVirtualColumn())
{
return("@IsVirtualColumn@");
}
sql = column.GetSQLAdd(table);
}
return(sql);
}
} // generate
/// <summary>
/// Generate Delta and History Table
/// </summary>
/// <returns></returns>
private bool GenerateTable()
{
// Table
m_derivedTable = MTable.Get(m_ctx, m_dTableName);
if (m_derivedTable == null)
{
m_derivedTable = new MTable(m_ctx, 0, null);
}
PO.CopyValues(m_baseTable, m_derivedTable);
m_derivedTable.SetTableName(m_dTableName);
m_derivedTable.SetName(m_derivedTable.GetName() + " SubTable");
m_derivedTable.SetSubTableType(m_derivedTableType);
m_derivedTable.SetBase_Table_ID(m_baseTable.GetAD_Table_ID());
if (!m_derivedTable.Save())
{
throw new Exception("Cannot save " + m_dTableName);
}
MColumn[] dCols = SyncMColumns(true);
// Sync Columns in Database
List <MColumn> list = new List <MColumn>(dCols.Length);
foreach (MColumn element in dCols)
{
list.Add(element);
}
Trx trx = Trx.Get("getDatabaseMetaData");
//
String catalog = "";
String schema = DataBase.DB.GetSchema();
String tableName = m_dTableName;
tableName = tableName.ToUpper();
int noColumns = 0;
//
DataSet rs = null;
using (DatabaseMetaData md = new DatabaseMetaData())
{
rs = md.GetColumns(catalog, schema, tableName);
}
for (int rscount = 0; rscount <= rs.Tables[0].Rows.Count - 1; rscount++)
{
noColumns++;
String columnName = rs.Tables[0].Rows[rscount]["COLUMN_NAME"].ToString();
bool found = false;
for (int i = 0; i < list.Count; i++)
{
MColumn dCol = list[i];
if (columnName.Equals(dCol.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
String sql = dCol.GetSQLModify(m_derivedTable, false);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
found = true;
list.Remove(list[i]);
break;
}
}
if (!found)
{
String sql = "ALTER TABLE " + m_dTableName + " DROP COLUMN " + columnName;
DataBase.DB.ExecuteQuery(sql, null);
}
}
//rs.close();
trx.Close();
// No Columns
if (noColumns == 0)
{
String sql = m_derivedTable.GetSQLCreate();
return(DataBase.DB.ExecuteUpdateMultiple(sql, false, null) >= 0);
}
// New Columns
for (int i = 0; i < list.Count(); i++)
{
MColumn dCol = list[i];
if (dCol.IsVirtualColumn())
{
continue;
}
String sql = dCol.GetSQLAdd(m_derivedTable);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
}
return(true);
} // generateTable
/// <summary>
/// Process logic to verify database columns and Columns in Application Dictionary
/// </summary>
/// <returns></returns>
protected override string DoIt()
{
// if table not found, in case if run from menu
// or from table, then return with message
if (p_AD_Table_ID.Trim() == "")
{
return(Msg.GetMsg(GetCtx(), "VIS_TableNotSelected"));
}
DataSet dsDT = DB.ExecuteDataset("SELECT AD_Reference_ID, Name FROM AD_Reference WHERE IsActive = 'Y' AND ValidationType = 'D'");
string[] tableIDs = p_AD_Table_ID.Split(',');
// loop through tables selected in parameter or
// if run from window then single table ID
for (int i = 0; i < tableIDs.Length; i++)
{
int AD_Table_ID = Util.GetValueOfInt(tableIDs[i]);
MTable table = MTable.Get(GetCtx(), AD_Table_ID);
// Check on table whether it has single or multiple keys
bool hasSingleKey = true;
if (!table.IsSingleKey())
{
hasSingleKey = false;
}
if (table == null || table.Get_ID() == 0)
{
return(Msg.GetMsg(GetCtx(), "VIS_TableNotFound"));
}
// create HTML for tables
sbHTML.Append("<div class='vis-val-tc-parCtr'> "
+ "<div class='vis-val-tc-hdr'><label>" + Msg.Translate(GetCtx(), "AD_Table_ID") + ": </label>" + table.GetTableName() + "</div>"
+ "<div class='vis-val-tc-colCtr'>"
+ "<div class='vis-val-tc-colHdrs'>" + Msg.GetMsg(GetCtx(), "VIS_ADCols") + "</div>"
+ "<div class='vis-val-tc-colHdrs'>" + Msg.GetMsg(GetCtx(), "VIS_DBCols") + "</div>"
+ "</div>");
// Find Column in Database
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";
String schema = DataBase.DB.GetSchema();
//get table name
string tableName = table.GetTableName();
//get columns of a table
DataSet dt = md.GetColumns(catalog, schema, tableName);
bool hasDBCols = false;
if (dt.Tables[0].Rows.Count > 0)
{
hasDBCols = true;
}
// get all columns from table
MColumn[] columnsAD = table.GetColumns(true);
// variables to create HTML as string for both AD and in DB
StringBuilder adColName = new StringBuilder("");
StringBuilder dbColName = new StringBuilder("");
List <string> ADCols = new List <string>();
// check whether process is running from menu or from table and column Window
// if process is executing from window then apply style
string mainMenuWrap = "";
if (!fromMenu)
{
mainMenuWrap = "style='max-height: 450px;'";
}
sbHTML.Append("<div class='vis-val-tc-mainwrap' " + mainMenuWrap + "> ");
if (columnsAD.Length > 0)
{
// loop through Columns present in Application dictionary
for (int c = 0; c < columnsAD.Length; c++)
{
var col = columnsAD[c];
adColName.Clear();
adColName.Append(col.GetColumnName().ToUpper());
DataRow[] dr = null;
if (hasDBCols && dt.Tables[0].Rows.Count > 0)
{
dr = dt.Tables[0].Select("COLUMN_NAME = '" + adColName.ToString() + "'");
}
DataRow[] drRef = dsDT.Tables[0].Select("AD_Reference_ID = " + col.GetAD_Reference_ID());
// if column is virtual, then add style
var style = " color: red; font-style: italic;";
if (col.IsVirtualColumn() || (dr != null && dr.Length > 0))
{
style = " color: green;";
}
// add different style for key column
string keyCol = "";
// Condition for Multikey columns and Single key to mark as Primary key columns
if ((hasSingleKey && col.GetAD_Reference_ID() == 13) || (!hasSingleKey && col.IsParent()))
{
keyCol = " * ";
style += " font-weight: bold; font-size: initial;";
}
sbHTML.Append("<div class='vis-val-tc-colWid'>"
+ "<div class='vis-val-tc-col-l' style='" + style + "'> " + keyCol + " " + col.GetColumnName() + " (" + drRef[0]["Name"] + " (" + col.GetFieldLength() + ") " + " ) " + "</div>");
if (dr != null && dr.Length > 0)
{
sbHTML.Append("<div class='vis-val-tc-col-r' style='" + style + "'>" + Util.GetValueOfString(dr[0]["Column_Name"]) + " (" + Util.GetValueOfString(dr[0]["DATATYPE"]) + " (" + Util.GetValueOfInt(dr[0]["LENGTH"]) + ") " + " ) " + "</div>");
}
else
{
if (col.IsVirtualColumn())
{
sbHTML.Append("<div class='vis-val-tc-virCol' style='" + style + "'>" + Msg.GetMsg(GetCtx(), "VIS_VirtualCol") + "</div>");
}
else
{
sbHTML.Append("<div class='vis-val-tc-col-r' style='" + style + "'>" + Msg.GetMsg(GetCtx(), "VIS_DBNotFound") + "</div>");
}
}
sbHTML.Append("</div>");
ADCols.Add(adColName.ToString());
}
}
// if columns present in database
if (hasDBCols)
{
// loop through columns in DB
for (int d = 0; d < dt.Tables[0].Rows.Count; d++)
{
dbColName.Clear();
dbColName.Append(dt.Tables[0].Rows[d]["Column_Name"]);
if (ADCols.Contains(dbColName.ToString()))
{
continue;
}
else
{
sbHTML.Append("<div class='vis-val-tc-colWid'>");
DataRow dr = null;
if (hasDBCols && dt.Tables[0].Rows.Count > 0)
{
dr = dt.Tables[0].Rows[d];
}
if (dr != null)
{
sbHTML.Append("<div class='vis-val-tc-col-red-r'>" + Util.GetValueOfString(dr["Column_Name"]) + " (" + Util.GetValueOfString(dr["DATATYPE"]) + " (" + Util.GetValueOfInt(dr["LENGTH"]) + ") " + " ) " + "</div>");
}
else
{
sbHTML.Append("<div class='vis-val-tc-col-red-r'>" + Msg.GetMsg(GetCtx(), "VIS_ADNotFound") + "</div>");
}
sbHTML.Append("<div class='vis-val-tc-col-red-l'>" + Msg.GetMsg(GetCtx(), "VIS_ADNotFound") + "</div>");
sbHTML.Append("</div>");
}
}
}
sbHTML.Append("</div>");
sbHTML.Append("</div>");
md.Dispose();
ProcessInfo pi = GetProcessInfo();
pi.SetCustomHTML(sbHTML.ToString());
}
return("");
}
private void InitKeyInfo(DataRow row, DatabaseMetaData dbMetaData, String catalog, String schema, String table) {
string column = (string)row [(int)SCHEMA_TABLE.BaseColumnName];
row [(int)SCHEMA_TABLE.IsUnique] = false;
row [(int)SCHEMA_TABLE.IsKey] = false;
row [(int)SCHEMA_TABLE.IsIdentity] = false;
row [(int)SCHEMA_TABLE.IsRowVersion] = false;
if ((_command.Behavior & CommandBehavior.KeyInfo) == 0)
return;
if(table == null || column == null || dbMetaData == null)
return;
ResultSet versionCol = dbMetaData.getVersionColumns(catalog, schema, table);
try {
while(versionCol.next()) {
if(versionCol.getString("COLUMN_NAME") == column) {
if (DatabaseMetaData__Finals.versionColumnPseudo == versionCol.getShort("PSEUDO_COLUMN")) {
row [(int)SCHEMA_TABLE.IsIdentity] = true;
row [(int)SCHEMA_TABLE.IsRowVersion] = true;
}
}
}
}
finally {
versionCol.close();
}
ResultSet primaryKeys = dbMetaData.getPrimaryKeys(catalog,schema,table);
bool primaryKeyExists = false;
int columnCount = 0;
try {
while(primaryKeys.next()) {
columnCount++;
if(primaryKeys.getString("COLUMN_NAME") == column) {
row [(int)SCHEMA_TABLE.IsKey] = true;
primaryKeyExists = true;
}
}
// column constitutes a key by itself, so it should be marked as unique
if ((columnCount == 1) && (((bool)row [(int)SCHEMA_TABLE.IsKey]) == true)) {
row [(int)SCHEMA_TABLE.IsUnique] = true;
}
}
finally {
primaryKeys.close();
}
ResultSet indexInfoRes = dbMetaData.getIndexInfo(catalog,schema,table,true,false);
string currentIndexName = null;
columnCount = 0;
bool belongsToCurrentIndex = false;
bool atFirstIndex = true;
bool uniqueKeyExists = false;
try {
while(indexInfoRes.next()) {
if (indexInfoRes.getShort("TYPE") == DatabaseMetaData__Finals.tableIndexStatistic) {
// index of type tableIndexStatistic identifies table statistics - ignore it
continue;
}
uniqueKeyExists = true;
string iname = indexInfoRes.getString("INDEX_NAME");
if (currentIndexName == iname) {
// we're within the rows of the same index
columnCount++;
}
else {
// we jump to row of new index
if (belongsToCurrentIndex && columnCount == 1) {
// there is a constraint of type UNIQUE that applies only to this column
row [(int)SCHEMA_TABLE.IsUnique] = true;
}
if (currentIndexName != null) {
atFirstIndex = false;
}
currentIndexName = iname;
columnCount = 1;
belongsToCurrentIndex = false;
}
if(indexInfoRes.getString("COLUMN_NAME") == column) {
// FIXME : this will cause "spare" columns marked as IsKey. Needs future investigation.
// only the first index we met should be marked as a key
//if (atFirstIndex) {
row [(int)SCHEMA_TABLE.IsKey] = true;
//}
belongsToCurrentIndex = true;
}
}
// the column appears in the last index, which is single-column
if (belongsToCurrentIndex && columnCount == 1) {
// there is a constraint of type UNIQUE that applies only to this column
row [(int)SCHEMA_TABLE.IsUnique] = true;
}
}
finally {
indexInfoRes.close();
}
if(!primaryKeyExists && !uniqueKeyExists) {
ResultSet bestRowId = dbMetaData.getBestRowIdentifier(catalog, schema, table, DatabaseMetaData__Finals.bestRowTemporary, false);
try {
while(bestRowId.next()) {
if(bestRowId.getString("COLUMN_NAME") == column)
row [(int)SCHEMA_TABLE.IsKey] = true;
}
}
finally {
bestRowId.close();
}
}
}
}// prepare
/// <summary>
/// Alert table
/// </summary>
/// <returns>int</returns>
///
override protected string DoIt()
{
string exception = "";
log.Info("C_Column_ID=" + p_AD_Column_ID);
if (p_AD_Column_ID == 0)
{
// return "";
throw new Exception("@No@ @AD_Column_ID@");
}
//IDbTransaction trx = ExecuteQuery.GerServerTransaction();
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());
if (column.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Column_ID@" + p_AD_Column_ID);
}
MTable table = MTable.Get(GetCtx(), column.GetAD_Table_ID());
if (table.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Table_ID@" + column.GetAD_Table_ID());
}
// Find Column in Database
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";
String schema = DataBase.DB.GetSchema();
//get table name
string tableName = table.GetTableName();
int noColumns;
string sql = null;
//get columns of a table
DataSet dt = md.GetColumns(catalog, schema, tableName);
md.Dispose();
//for each column
for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
{
string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
if (!columnName.Equals(column.GetColumnName().ToLower()))
{
continue;
}
//check if column is null or not
string dtColumnName = "is_nullable";
string value = "YES";
//if database is oracle
if (DatabaseType.IsOracle)
{
dtColumnName = "NULLABLE";
value = "Y";
}
bool notNull = false;
//check if column is null
if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
{
notNull = false;
}
else
{
notNull = true;
}
//............................
//if column is virtual column then alter table and drop this column
if (column.IsVirtualColumn())
{
sql = "ALTER TABLE " + table.GetTableName()
+ " DROP COLUMN " + columnName;
}
else
{
sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
noColumns++;
break;
}
}
dt = null;
//while (rs.next())
//{
// noColumns++;
// String columnName = rs.getString ("COLUMN_NAME");
// if (!columnName.equalsIgnoreCase(column.getColumnName()))
// continue;
// // update existing column
// boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
// if (column.isVirtualColumn())
// sql = "ALTER TABLE " + table.getTableName()
// + " DROP COLUMN " + columnName;
// else
// sql = column.getSQLModify(table, column.isMandatory() != notNull);
// break;
//}
//rs.close();
//rs = null;
// No Table
if (noColumns == 0)
{
sql = table.GetSQLCreate();
}
// No existing column
else if (sql == null)
{
if (column.IsVirtualColumn())
{
return("@IsVirtualColumn@");
}
sql = column.GetSQLAdd(table);
}
int no = 0;
if (sql.IndexOf("; ") == -1)
{
//no =
//ExecuteQuery.ExecuteNonQuery(sql, false, Get_Trx());
try
{
no = DataBase.DB.ExecuteQuery(sql, null, Get_Trx());
AddLog(0, DateTime.MinValue, Decimal.Parse(no.ToString()), sql);
}
catch (Exception ex)
{
exception = ex.Message;
}
//addLog (0, null, new BigDecimal(no), sql);
}
else
{
//string ss = "; ";
string[] statements = sql.Split(';');
for (int i = 0; i < statements.Length; i++)
{
int count = DataBase.DB.ExecuteQuery(statements[i].ToString(), null, Get_Trx());
AddLog(0, DateTime.MinValue, Decimal.Parse(count.ToString()), statements[i]);
no += count;
//ExecuteQuery.ExecuteNonQuery(statements[i].ToString());
}
}
if (no == -1)
{
string msg = "@Error@ ";
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
msg += exception + " - ";
}
msg += sql;
throw new Exception(msg);
}
string r = createFK();
return(sql + "; " + r);
} // doIt
} // doIt
private String createFK()
{
String returnMessage = "";
if (p_AD_Column_ID == 0)
{
throw new Exception("@No@ @AD_Column_ID@");
}
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());
if (column.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Column_ID@ " + p_AD_Column_ID);
}
MTable table = MTable.Get(GetCtx(), column.GetAD_Table_ID());
if (table.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Table_ID@ " + column.GetAD_Table_ID());
}
String fk;
if ((column.GetAD_Reference_ID() == DisplayType.Account) &&
!(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (cPK.AD_Column_ID=1014)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N' AND c.AD_Reference_ID=25 AND C.AD_Column_ID= @param" // Acct
+ " AND c.ColumnName<>'C_ValidCombination_ID'"
+ " AND t.IsView='N' "
+ " ORDER BY t.TableName, c.ColumnName";
}
else if ((column.GetAD_Reference_ID() == DisplayType.PAttribute) &&
!(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (cPK.AD_Column_ID=8472)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N' AND c.AD_Reference_ID=35 AND C.AD_Column_ID=@param" // Product Attribute
+ " AND c.ColumnName<>'C_ValidCombination_ID'"
+ " AND t.IsView='N' "
+ " ORDER BY t.TableName, c.ColumnName";
}
else if (((column.GetAD_Reference_ID() == DisplayType.TableDir) || (column.GetAD_Reference_ID() == DisplayType.Search)) &&
(column.GetAD_Reference_Value_ID() == 0))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (c.AD_Element_ID=cPK.AD_Element_ID AND cPK.IsKey='Y')"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID AND tPK.IsView='N') "
+ "WHERE c.IsKey='N' AND c.AD_Reference_Value_ID IS NULL AND C.AD_Column_ID=@param"
+ " AND t.IsView='N' AND c.ColumnSQL IS NULL "
+ " ORDER BY t.TableName, c.ColumnName";
}
else // Table
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID AND c.AD_Reference_Value_ID IS NOT NULL)"
+ " INNER JOIN AD_Ref_Table rt ON (c.AD_Reference_Value_ID=rt.AD_Reference_ID)"
+ " INNER JOIN AD_Column cPK ON (rt.Column_Key_ID=cPK.AD_Column_ID)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N'"
+ " AND t.IsView='N' AND c.ColumnSQL IS NULL AND C.AD_Column_ID=@param"
+ " ORDER BY t.TableName, c.ColumnName";
}
SqlParameter[] pstmt = null;
DataSet ds = null;
try
{
/*Find foreign key relation in Database
* */
//Trx trx = Trx.Get("getDatabaseMetaData");
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";// DB.getCatalog();
String schema = DB.GetSchema();
String tableName = table.GetTableName();
String dropsql = null;
int no = 0;
String constraintNameDB = null;
String PKTableNameDB = null;
String PKColumnNameDB = null;
int constraintTypeDB = 0;
/* Get foreign key information from DatabaseMetadata
* */
ds = md.GetForeignKeys(catalog, schema, tableName);
md.Dispose();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Dictionary <String, String> fkcolumnDetail = md.GetForeignColumnDetail(ds.Tables[0].Rows[i]);
// string sql = "SELECT column_name FROM user_cons_columns WHERE constraint_name='" + ds.Tables[0].Rows[i]["FOREIGN_KEY_CONSTRAINT_NAME"].ToString() + "'";
//string fkcolumnName = Util.GetValueOfString(DB.ExecuteScalar(sql));
if (fkcolumnDetail["FK_Column_Name"].Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
constraintNameDB = fkcolumnDetail["ConstraintNameDB"];
PKTableNameDB = fkcolumnDetail["PK_Table_Name"]; //rs.GetString("PKTABLE_NAME");
PKColumnNameDB = fkcolumnDetail["PK_Column_Name"]; //rs.GetString("PKCOLUMN_NAME");
constraintTypeDB = md.GetConstraintTypeDB(fkcolumnDetail["Delete_Rule"]); //rs.getShort("DELETE_RULE");
break;
}
}
}
pstmt = new SqlParameter[1];
pstmt[0] = new SqlParameter("@param", column.Get_ID());
ds = DB.ExecuteDataset(fk, pstmt, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
String TableName = ds.Tables[0].Rows[0]["TableName"].ToString();
String ColumnName = ds.Tables[0].Rows[0]["ColumnName"].ToString();
// int AD_Column_ID = rs.getInt (3);
// int PK_Column_ID = rs.getInt (4);
String PKTableName = ds.Tables[0].Rows[0]["TableName1"].ToString();
String PKColumnName = ds.Tables[0].Rows[0]["ColumnName1"].ToString();
String ConstraintType = ds.Tables[0].Rows[0]["ConstraintType"].ToString();
String ConstraintName = ds.Tables[0].Rows[0]["ConstraintName"].ToString();
/* verify if the constraint in DB is different than the one to be created */
Boolean modified = true;
if (constraintNameDB != null)
{
if (((constraintNameDB.Equals(ConstraintName, StringComparison.OrdinalIgnoreCase)) &&
(PKTableNameDB != null) && (PKTableNameDB.Equals(PKTableName, StringComparison.OrdinalIgnoreCase)) &&
(PKColumnNameDB != null) && (PKColumnNameDB.Equals(PKColumnName, StringComparison.OrdinalIgnoreCase)) &&
((constraintTypeDB == DatabaseMetaData.importedKeyRestrict) &&
(X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))))
||
((constraintTypeDB == DatabaseMetaData.importedKeyCascade) &&
(X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType)))
||
((constraintTypeDB == DatabaseMetaData.importedKeySetNull) &&
(X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType)))
)
{
modified = false;
}
else
{
dropsql = "ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB;
}
}
if (modified)
{
StringBuilder sql = null;
try
{
if (dropsql != null)
{
/* Drop the existing constraint */
//no = DB.executeUpdate(Get_Trx(), dropsql);
no = DB.ExecuteQuery(dropsql, null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
}
/* Now create the sql foreign key constraint */
sql = new StringBuilder("ALTER TABLE ")
.Append(TableName)
.Append(" ADD CONSTRAINT ").Append(ConstraintName)
.Append(" FOREIGN KEY (").Append(ColumnName)
.Append(") REFERENCES ").Append(PKTableName)
.Append(" (").Append(PKColumnName).Append(")");
Boolean createfk = true;
if (!String.IsNullOrEmpty(ConstraintType))
{
if (X_AD_Column.CONSTRAINTTYPE_DoNOTCreate.Equals(ConstraintType))
{
createfk = false;
}
else if (X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))
{
;
}
else if (X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType))
{
sql.Append(" ON DELETE CASCADE");
}
else if (X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_NullTrigger.Equals(ConstraintType))
{
sql.Append(" ON DELETE SET NULL");
}
}
else
{
createfk = false;
}
/* Create the constraint */
if (createfk)
{
// no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx(), false, true);
AddLog(0, null, Decimal.Parse(no.ToString()), sql.ToString());
if (no != -1)
{
log.Finer(ConstraintName + " - " + TableName + "." + ColumnName);
returnMessage = sql.ToString();
}
else
{
log.Info(ConstraintName + " - " + TableName + "." + ColumnName
+ " - ReturnCode=" + no);
returnMessage = "FAILED:" + sql.ToString();
}
} //if createfk
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString() + " - " + e.ToString());
}
} // modified
} // rs.next
else
{
if (constraintNameDB != null && constraintNameDB.Equals("FK" + column.GetAD_Table_ID() + "_" + p_AD_Column_ID, StringComparison.OrdinalIgnoreCase))
{
dropsql = "ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB;
/* Drop the existing constraint */
no = DB.ExecuteQuery(dropsql, null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
returnMessage = dropsql.ToString();
}
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, fk, e);
}
return(returnMessage);
}
private DatabaseMetaData GetDatabaseMetaData(SqlConnection activeConnection, string dbname)
{
DatabaseMetaData databaseMetaData = new DatabaseMetaData();
SqlCommand cmd = activeConnection.CreateCommand();
cmd.CommandText = "sp_helpdb";
cmd.Parameters.Add("@dbname", SqlDbType.NVarChar).Value = dbname;
cmd.CommandType = CommandType.StoredProcedure;
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
adapter.Dispose();
cmd.Dispose();
if (dataset.Tables != null && dataset.Tables.Count == 2)
{
DataRow primaryinfo = dataset.Tables[0].Rows[0];
string metrics = string.Empty;
databaseMetaData.DBSize = DbSizeStringToLong(dataset.Tables[0].Rows[0], "db_size", out metrics);
databaseMetaData.MetricsDBSize = metrics;
DataTable dbfileinfos = dataset.Tables[1];
foreach (DataRow dbfi in dbfileinfos.Rows)
{
// if database file
if (Convert.ToInt32(dbfi["fileid"]) == 0)
{
string metricsMaxSize;
databaseMetaData.DBMaxSize = DbSizeStringToLong(dbfi, "maxsize", out metricsMaxSize);
databaseMetaData.MetricsDBMaxSize = metricsMaxSize;
databaseMetaData.DbFileSizePercentGrowth = GetInt32FromString(Convert.ToString(dbfi["growth"]));
}
// if log file
if (Convert.ToInt32(dbfi["fileid"]) == 1)
{
//System.Windows.Forms.MessageBox.Show(Convert.ToString(dbfi["size"]), "Debug");
string metricsMaxSize;
databaseMetaData.LogMaxSize = DbSizeStringToLong(dbfi, "maxsize", out metricsMaxSize);
databaseMetaData.MetricsLogMaxSize = metricsMaxSize;
databaseMetaData.LogFileSizePercentGrowth = GetInt32FromString(Convert.ToString(dbfi["growth"]));
}
}
}
cmd = activeConnection.CreateCommand();
cmd.CommandText = "declare @tblSqlPerfLogspace table" +
"(" +
"dbname nvarchar(max)," +
"mb_logsize float," +
"percent_logspace_used float," +
"[status] int" +
");" +
"insert into @tblSqlPerfLogspace exec('dbcc sqlperf (logspace)');" +
"select * from @tblSqlPerfLogspace where[dbname] = @dbname;";
cmd.Parameters.Add("@dbname", SqlDbType.NVarChar).Value = dbname;
DataTable datatable = new DataTable();
adapter = new SqlDataAdapter(cmd);
adapter.Fill(datatable);
adapter.Dispose();
cmd.Dispose();
if (datatable.Rows != null && datatable.Rows.Count > 0)
{
databaseMetaData.LogSize = Convert.ToDouble(datatable.Rows[0]["mb_logsize"]);
databaseMetaData.MetricsLogSize = "MB";
databaseMetaData.LogFileSizePercentUsed = Convert.ToInt32(datatable.Rows[0]["percent_logspace_used"]);
}
return(databaseMetaData);
}
private void AddTablesNode(TreeNode rootNode)
{
var schemas = Database.Dmvs.Schemas.ToDictionary(x => x.schema_id);
var tablesWithRowCount = (from table in Database.Dmvs.Tables.Where(x => !x.IsMSShipped)
join partition in Database.Dmvs.Partitions on table.ObjectID equals partition.ObjectID
where partition.IndexID < 2
group partition by new { Name = $"{schemas[table.SchemaID].name}.{table.Name}", table.ObjectID }
into g
orderby g.Key.Name
select new { g.Key.Name, g.Key.ObjectID, RowCount = g.Sum(x => x.Rows) }).ToArray();
if (tablesWithRowCount.Length == 0)
{
return;
}
var tableRootNode = rootNode.Nodes.Add("Tables");
foreach (var t in tablesWithRowCount)
{
var tableNode = tableRootNode.Nodes.Add(t.Name);
//tableNode.ToolTipText = $"{t.RowCount} rows";
tableNode.Tag = t.RowCount;
tableNode.ContextMenu = tableMenu;
// Add columns
var tableColumnsNode = tableNode.Nodes.Add("Columns");
var columns = Database.Dmvs.Columns
.Where(c => c.ObjectID == t.ObjectID)
.ToArray();
foreach (var c in columns)
{
var mainColumn = Database.Dmvs.Columns.Single(x => x.ColumnID == c.ColumnID && x.ObjectID == c.ObjectID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, mainColumn);
tableColumnsNode.Nodes.Add($"{c.Name} ({typeName})");
}
// Add indexes
var tableIndexesNode = tableNode.Nodes.Add("Indexes");
var indexes = Database.Dmvs.Indexes
.Where(i => i.ObjectID == t.ObjectID && i.IndexID > 0)
.OrderBy(i => i.Name);
foreach (var i in indexes)
{
var indexNode = tableIndexesNode.Nodes.Add(i.Name);
// Add index columns
var indexColumns = Database.Dmvs.IndexColumns
.Where(ic => ic.ObjectID == t.ObjectID && ic.IndexID == i.IndexID);
foreach (var ic in indexColumns)
{
var tableColumn = columns.Single(c => c.ColumnID == ic.ColumnID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, tableColumn);
indexNode.Nodes.Add($"{tableColumn.Name} ({typeName})");
}
}
}
}
private void AddDataTypes(TreeNode typesNode)
{
var schemas = Database.Dmvs.Schemas.ToDictionary(x => x.schema_id);
var types = Database.Dmvs.Types.Where(x => !x.IsTableType && x.IsUserDefined)
.Select(x => new { Name = $"{schemas[x.SchemaID].name}.{x.Name}", Description = DatabaseMetaData.GetTypeName(Database.Dmvs, x, true) })
.OrderBy(t => t.Name).ToArray();
if (types.Length == 0)
{
return;
}
var tableNode = typesNode.Nodes.Add("User-Defined Data Types");
foreach (var type in types)
{
/*var typeNode =*/ tableNode.Nodes.Add($"{type.Name} ({type.Description})");
//typeNode.ContextMenu = dataTypeMenu;
}
}
