/// <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();
            }
        }
Пример #3
0
        /// <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);
        }
Пример #5
0
        /// <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);
        }
Пример #6
0
 /// <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));
     }
 }
Пример #7
0
 /// <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));
     }
 }
Пример #8
0
        /// <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);
        }