/// <summary>
/// Carga la definición de vistas
/// </summary>
private async Task LoadViewsAsync(SqlServerProvider connection, SchemaDbModel schema, TimeSpan timeout, CancellationToken cancellationToken)
{
string sql = @"SELECT Table_Catalog, Table_Schema, Table_Name, View_Definition, Check_Option, Is_Updatable
FROM Information_Schema.Views
ORDER BY Table_Name" ;
// Carga las vistas
using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, null, CommandType.Text, timeout, cancellationToken))
{
// Lee los registros
while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken))
{
ViewDbModel view = new ViewDbModel();
// Asigna los datos al objeto
view.Catalog = (string)reader.IisNull("Table_Catalog");
view.Schema = (string)reader.IisNull("Table_Schema");
view.Name = (string)reader.IisNull("Table_Name");
view.Definition = (string)reader.IisNull("View_Definition");
view.CheckOption = (string)reader.IisNull("Check_Option");
view.IsUpdatable = !(((string)reader.IisNull("Is_Updatable")).Equals("NO", StringComparison.CurrentCultureIgnoreCase));
// Añade el objeto a la colección
schema.Views.Add(view);
}
// Cierra el recordset
reader.Close();
}
// Carga las columnas de la vista
foreach (ViewDbModel view in schema.Views)
{
await LoadColumnsAsync(connection, view, timeout, cancellationToken);
}
}
/// <summary>
/// Carga las rutinas de la base de datos
/// </summary>
private async Task LoadRoutinesAsync(SqlServerProvider connection, SchemaDbModel schema, TimeSpan timeout, CancellationToken cancellationToken)
{
string sql = @"SELECT Routine_Catalog AS Table_Catalog, Routine_Schema AS Table_Schema,
Routine_Name AS Table_Name, Routine_Type, Routine_Definition
FROM Information_Schema.Routines
ORDER BY Routine_Name" ;
// Carga los datos
using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, null, CommandType.Text, timeout, cancellationToken))
{
// Lee los registros
while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken))
{
RoutineDbModel routine = new RoutineDbModel();
// Asigna los datos del recordset al objeto
routine.Catalog = (string)reader.IisNull("Table_Catalog");
routine.Schema = (string)reader.IisNull("Table_Schema");
routine.Name = (string)reader.IisNull("Table_Name");
routine.Type = GetRoutineType((string)reader.IisNull("Routine_Type"));
routine.Content = (string)reader.IisNull("Routine_Definition");
// Añade el objeto a la colección
schema.Routines.Add(routine);
}
// Cierra el recordset
reader.Close();
}
}
/// <summary>
/// Carga el esquema de la conexión
/// </summary>
internal async Task LoadSchemaAsync(ConnectionModel connection, CancellationToken cancellationToken)
{
SchemaDbModel schema = await GetDbProvider(connection).GetSchemaAsync(TimeSpan.FromMinutes(5), cancellationToken);
// Limpia las tablas de la conexión
connection.Tables.Clear();
// Agrega los campos
foreach (TableDbModel tableSchema in schema.Tables)
{
ConnectionTableModel table = new ConnectionTableModel(connection);
// Asigna las propiedades
table.Name = tableSchema.Name;
table.Description = tableSchema.Description;
table.Schema = tableSchema.Schema;
// Asigna los campos
foreach (FieldDbModel fieldSchema in tableSchema.Fields)
{
ConnectionTableFieldModel field = new ConnectionTableFieldModel(table);
// Asigna las propiedades
field.Name = fieldSchema.Name;
field.Description = fieldSchema.Description;
field.Type = fieldSchema.DbType; // fieldSchema.Type.ToString();
field.Length = fieldSchema.Length;
field.IsRequired = fieldSchema.IsRequired;
field.IsKey = fieldSchema.IsKey;
field.IsIdentity = fieldSchema.IsIdentity;
// Añade el campo
table.Fields.Add(field);
}
// Añade la tabla a la colección
connection.Tables.Add(table);
}
}
/// <summary>
/// Obtiene el esquema
/// </summary>
internal async Task <SchemaDbModel> GetSchemaAsync(PostgreSqlProvider provider, TimeSpan timeout, CancellationToken cancellationToken)
{
var schema = new SchemaDbModel();
// Abre la conexión
provider.Open();
// Carga los datos
using (DbDataReader rdoData = await provider.ExecuteReaderAsync(GetSqlReadSchema(), null, CommandType.Text, timeout, cancellationToken))
{
while (!cancellationToken.IsCancellationRequested && await rdoData.ReadAsync(cancellationToken))
{
schema.Add(rdoData.IisNull <string>("TableType").Equals("TABLE", StringComparison.CurrentCultureIgnoreCase),
rdoData.IisNull <string>("SchemaName"),
rdoData.IisNull <string>("TableName"),
rdoData.IisNull <string>("ColumnName"),
GetFieldType(rdoData.IisNull <string>("ColumnType")),
rdoData.IisNull <string>("ColumnType"),
rdoData.IisNull <int>("ColumnLength", 0),
rdoData.IisNull <int>("PrimaryKey") == 1,
rdoData.IisNull <int>("IsRequired") == 1);
}
}
// Cierra la conexión
provider.Close();
// Devuelve el esquema
return(schema);
}
/// <summary>
/// Carga el esquema de la conexión
/// </summary>
internal async Task LoadSchemaAsync(ConnectionModel connection, CancellationToken cancellationToken)
{
SchemaDbModel schema = await GetDbProvider(connection).GetSchemaAsync(TimeSpan.FromMinutes(5), cancellationToken);
// Carga las tablas
LoadSchemaTables(connection, schema);
// Carga las vistas
LoadSchemaViews(connection, schema);
}
/// <summary>
/// Carga las vistas del esquema
/// </summary>
private void LoadSchemaViews(ConnectionModel connection, SchemaDbModel schema)
{
// Limpia las vistas de la conexión
connection.Views.Clear();
// Agrega los campos
foreach (ViewDbModel viewSchema in schema.Views)
{
connection.Views.Add(Convert(connection, viewSchema));
}
}
/// <summary>
/// Carga las tablas del esquema
/// </summary>
private void LoadSchemaTables(ConnectionModel connection, SchemaDbModel schema)
{
// Limpia las tablas de la conexión
connection.Tables.Clear();
// Agrega los campos
foreach (TableDbModel tableSchema in schema.Tables)
{
connection.Tables.Add(Convert(connection, tableSchema));
}
}
/// <summary>
/// Obtiene el esquema
/// </summary>
internal async Task <SchemaDbModel> GetSchemaAsync(SparkProvider provider, TimeSpan timeout, CancellationToken cancellationToken)
{
SchemaDbModel schema = new SchemaDbModel();
List <string> tables = new List <string>();
// Obtiene el esquema
using (OdbcConnection connection = new OdbcConnection(provider.ConnectionString.ConnectionString))
{
// Abre la conexión
await connection.OpenAsync(cancellationToken);
// Obtiene las tablas
using (DataTable table = connection.GetSchema("Tables"))
{
foreach (DataRow row in table.Rows)
{
if (!cancellationToken.IsCancellationRequested &&
row.IisNull <string>("Table_Type").Equals("TABLE", StringComparison.CurrentCultureIgnoreCase))
{
tables.Add(row.IisNull <string>("Table_Name"));
}
}
}
// Carga las columnas
if (!cancellationToken.IsCancellationRequested)
{
using (DataTable table = connection.GetSchema("Columns"))
{
foreach (DataRow row in table.Rows)
{
if (!cancellationToken.IsCancellationRequested &&
tables.FirstOrDefault(item => item.Equals(row.IisNull <string>("Table_Name"), StringComparison.CurrentCultureIgnoreCase)) != null)
{
schema.Add(true,
row.IisNull <string>("Table_Schem"),
row.IisNull <string>("Table_Name"),
row.IisNull <string>("Column_Name"),
GetFieldType(row.IisNull <string>("Type_Name")),
row.IisNull <string>("Type_Name"),
row.IisNull <int>("Column_Size", 0),
false,
row.IisNull <string>("Is_Nullable").Equals("No", StringComparison.CurrentCultureIgnoreCase));
}
}
}
}
// Cierra la conexión
connection.Close();
}
// Devuelve el esquema
return(schema);
}
/// <summary>
/// Convierte un esquema en una estructura de documentación
/// </summary>
public StructDocumentationModel Convert(SchemaDbModel schema, string dataBase, string server)
{
StructDocumentationModel structDoc = new StructDocumentationModel(null, StructDocumentationModel.ScopeType.Global,
dataBase, "DataBase", 0);
// Añade los parámetros a la estructura
structDoc.Parameters.Add("DataBase", dataBase);
structDoc.Parameters.Add("Server", server);
// Convierte el esquema
structDoc.Childs.AddRange(ConvertTables(structDoc, schema));
structDoc.Childs.AddRange(ConvertViews(structDoc, schema));
structDoc.Childs.AddRange(ConvertProcedures(structDoc, schema.Routines));
// Devuelve las estructuras convertidas
return(structDoc);
}
/// <summary>
/// Carga las tablas de un esquema
/// </summary>
private async Task LoadTablesAsync(SqlServerProvider connection, SchemaDbModel schema, TimeSpan timeout, CancellationToken cancellationToken)
{
string sql = @"SELECT Tables.TABLE_CATALOG, Tables.TABLE_SCHEMA, Tables.TABLE_NAME,
Tables.TABLE_TYPE, Objects.Create_Date, Objects.Modify_Date, Properties.Value AS Description
FROM INFORMATION_SCHEMA.TABLES AS Tables INNER JOIN sys.all_objects AS Objects
ON Tables.Table_Name = Objects.name
LEFT JOIN sys.extended_properties AS Properties
ON Objects.object_id = Properties.major_id
AND Properties.minor_id = 0
AND Properties.name = 'MS_Description'
ORDER BY Tables.TABLE_NAME" ;
// Carga las tablas
using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, null, CommandType.Text, timeout, cancellationToken))
{
// Recorre la colección de registros
while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken))
{
TableDbModel table = new TableDbModel();
// Asigna los datos del registro al objeto
table.Catalog = (string)reader.IisNull("TABLE_CATALOG");
table.Schema = (string)reader.IisNull("TABLE_SCHEMA");
table.Name = (string)reader.IisNull("TABLE_NAME");
table.CreatedAt = (DateTime)reader.IisNull("Create_Date");
table.UpdatedAt = (DateTime)reader.IisNull("Modify_Date");
table.Description = (string)reader.IisNull("Description");
// Añade el objeto a la colección
schema.Tables.Add(table);
}
// Cierra el recordset
reader.Close();
}
// Carga los datos de las tablas
foreach (TableDbModel table in schema.Tables)
{
await LoadColumnsAsync(connection, table, timeout, cancellationToken);
await LoadConstraintsAsync(connection, table, timeout, cancellationToken);
}
}
/// <summary>
/// Clase para la carga de un esquema de una base de datos SQL Server
/// </summary>
internal async Task <SchemaDbModel> GetSchemaAsync(SqlServerProvider provider, TimeSpan timeout, CancellationToken cancellationToken)
{
SchemaDbModel schema = new SchemaDbModel();
// Carga los datos del esquema
using (SqlServerProvider connection = new SqlServerProvider(provider.ConnectionString))
{
// Abre la conexión
await connection.OpenAsync(cancellationToken);
// Carga los datos del esquema
await LoadTablesAsync(connection, schema, timeout, cancellationToken);
await LoadTriggersAsync(connection, schema, timeout, cancellationToken);
await LoadViewsAsync(connection, schema, timeout, cancellationToken);
await LoadRoutinesAsync(connection, schema, timeout, cancellationToken);
// Cierra la conexión
connection.Close();
}
// Devuelve el esquema
return(schema);
}
/// <summary>
/// Carga los triggers de un esquema
/// </summary>
private async Task LoadTriggersAsync(SqlServerProvider connection, SchemaDbModel schema, TimeSpan timeout, CancellationToken cancellationToken)
{
string sql = @"SELECT tmpTables.name AS DS_Tabla, tmpTrigger.name AS DS_Trigger_Name,
USER_NAME(tmpTrigger.uid) AS DS_User_Name, tmpTrigger.category AS NU_Category,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'IsExecuted') = 1) THEN 1 ELSE 0 END)) AS IsExecuted,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsAnsiNullsOn') = 1) THEN 1 ELSE 0 END)) AS ExecIsAnsiNullsOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsQuotedIdentOn') = 1) THEN 1 ELSE 0 END)) AS ExecIsQuotedIdentOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'IsAnsiNullsOn') = 1) THEN 1 ELSE 0 END)) AS IsAnsiNullsOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'IsQuotedIdentOn') = 1) THEN 1 ELSE 0 END)) AS IsQuotedIdentOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsAfterTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsAfterTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsDeleteTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsDeleteTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsFirstDeleteTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsFirstDeleteTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsFirstInsertTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsFirstInsertTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsFirstUpdateTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsFirstUpdateTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsInsertTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsInsertTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsInsteadOfTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsInsteadOfTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsLastDeleteTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsLastDeleteTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsLastInsertTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsLastInsertTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsLastUpdateTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsLastUpdateTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsTriggerDisabled') = 1) THEN 1 ELSE 0 END)) AS ExecIsTriggerDisabled,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsUpdateTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsUpdateTrigger,
tmpTrigger.crdate AS FE_Create, tmpTrigger.refdate AS FE_Reference
FROM sys.sysobjects AS tmpTrigger INNER JOIN sys.sysobjects AS tmpTables
ON tmpTrigger.parent_obj = tmpTables.id
WHERE OBJECTPROPERTY(tmpTrigger.id, N'IsTrigger') = 1
AND OBJECTPROPERTY(tmpTrigger.id, N'IsMSShipped') = 0
ORDER BY tmpTables.Name, tmpTrigger.Name" ;
// Carga los desencadenadores
using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, null, CommandType.Text, timeout, cancellationToken))
{
// Recorre la colección de registros
while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken))
{
TriggerDbModel trigger = new TriggerDbModel();
// Asigna los datos del registro al objeto
trigger.Catalog = "TABLE_CATALOG"; // clsBaseDB.iisNull(rdoTables, "TABLE_CATALOG") as string;
trigger.Schema = "TABLE_SCHEMA"; // clsBaseDB.iisNull(rdoTables, "TABLE_SCHEMA") as string;
trigger.Table = (string)reader.IisNull("DS_Tabla");
trigger.Name = (string)reader.IisNull("DS_Trigger_Name");
trigger.UserName = (string)reader.IisNull("DS_User_Name");
trigger.Category = (int)reader.IisNull("NU_Category");
trigger.IsExecuted = (bool)reader.IisNull("IsExecuted");
trigger.IsExecutionAnsiNullsOn = (bool)reader.IisNull("ExecIsAnsiNullsOn");
trigger.IsExecutionQuotedIdentOn = (bool)reader.IisNull("ExecIsQuotedIdentOn");
trigger.IsAnsiNullsOn = (bool)reader.IisNull("IsAnsiNullsOn");
trigger.IsQuotedIdentOn = (bool)reader.IisNull("IsQuotedIdentOn");
trigger.IsExecutionAfterTrigger = (bool)reader.IisNull("ExecIsAfterTrigger");
trigger.IsExecutionDeleteTrigger = (bool)reader.IisNull("ExecIsDeleteTrigger");
trigger.IsExecutionFirstDeleteTrigger = (bool)reader.IisNull("ExecIsFirstDeleteTrigger");
trigger.IsExecutionFirstInsertTrigger = (bool)reader.IisNull("ExecIsFirstInsertTrigger");
trigger.IsExecutionFirstUpdateTrigger = (bool)reader.IisNull("ExecIsFirstUpdateTrigger");
trigger.IsExecutionInsertTrigger = (bool)reader.IisNull("ExecIsInsertTrigger");
trigger.IsExecutionInsteadOfTrigger = (bool)reader.IisNull("ExecIsInsteadOfTrigger");
trigger.IsExecutionLastDeleteTrigger = (bool)reader.IisNull("ExecIsLastDeleteTrigger");
trigger.IsExecutionLastInsertTrigger = (bool)reader.IisNull("ExecIsLastInsertTrigger");
trigger.IsExecutionLastUpdateTrigger = (bool)reader.IisNull("ExecIsLastUpdateTrigger");
trigger.IsExecutionTriggerDisabled = (bool)reader.IisNull("ExecIsTriggerDisabled");
trigger.IsExecutionUpdateTrigger = (bool)reader.IisNull("ExecIsUpdateTrigger");
trigger.CreatedAt = ((DateTime?)reader.IisNull("FE_Create")) ?? DateTime.Now;
trigger.DateReference = (DateTime?)reader.IisNull("FE_Reference");
// Añade el objeto a la colección (si es una tabla)
schema.Triggers.Add(trigger);
}
// Cierra el recordset
reader.Close();
}
// Carga el contenido de los triggers
foreach (TriggerDbModel trigger in schema.Triggers)
{
trigger.Content = await LoadHelpTextAsync(connection, trigger.Name, timeout, cancellationToken);
}
}
/// <summary>
/// Convierte las vistas
/// </summary>
private StructDocumentationModelCollection ConvertViews(StructDocumentationModel parent, SchemaDbModel schema)
{
StructDocumentationModelCollection structsDoc = new StructDocumentationModelCollection();
// Crea la estructura de las vistas
foreach (ViewDbModel view in schema.Views)
{
StructDocumentationModel structDoc = CreateStruct(parent, view, "View");
// Añade los parámetros de la tabla
structDoc.Parameters.Add("Prototype", view.Definition);
structDoc.Parameters.Add("CheckOption", view.CheckOption);
structDoc.Parameters.Add("IsUpdatable", view.IsUpdatable);
structDoc.Parameters.Add("Summary", view.Description);
structDoc.Parameters.Add("DateCreate", Format(view.CreatedAt));
structDoc.Parameters.Add("DateUpdate", Format(view.UpdatedAt));
// Añade las columnas
structDoc.Childs.AddRange(ConvertColumns(structDoc, view.Fields, null));
// Añade la estructura a la colección
structsDoc.Add(structDoc);
}
// Devuelve la colección de estructuras
return(structsDoc);
}
/// <summary>
/// Convierte las tablas
/// </summary>
private StructDocumentationModelCollection ConvertTables(StructDocumentationModel parent, SchemaDbModel schema)
{
StructDocumentationModelCollection structsDoc = new StructDocumentationModelCollection();
// Crea la estructura de las tablas
foreach (TableDbModel table in schema.Tables)
{
StructDocumentationModel structDoc = CreateStruct(parent, table, "Table");
// Añade los parámetros de la tabla
structDoc.Parameters.Add("Summary", table.Description);
structDoc.Parameters.Add("DateCreate", Format(table.CreatedAt));
structDoc.Parameters.Add("DateUpdate", Format(table.UpdatedAt));
// Añade las columnas, restricciones, triggers...
structDoc.Childs.AddRange(ConvertColumns(structDoc, table.Fields, table));
structDoc.Childs.AddRange(ConvertConstrainst(structDoc, table.Constraints));
structDoc.Childs.AddRange(ConvertTriggers(parent, schema.Triggers, table));
// Añade la estructura a la colección
structsDoc.Add(structDoc);
}
// Devuelve la colección de estructuras
return(structsDoc);
}
