private void LoadSchema(string uri) { if (this.schemaInfo !.TargetNamespaces.ContainsKey(uri)) { return; } if (this.XmlResolver == null) { return; } SchemaInfo?schemaInfo = null; if (SchemaCollection != null) { schemaInfo = SchemaCollection.GetSchemaInfo(uri); } if (schemaInfo != null) { if (schemaInfo.SchemaType != SchemaType.XDR) { throw new XmlException(SR.Xml_MultipleValidationTypes, string.Empty, this.PositionInfo.LineNumber, this.PositionInfo.LinePosition); } this.schemaInfo.Add(schemaInfo, EventHandler); return; } LoadSchemaFromLocation(uri); }
private DbTypeInfo GetDbTypeInfo <TEntity>() where TEntity : IDbEntity, new() { Type entityType = typeof(TEntity); DbTypeInfo entityDbTypeInfo; if (!DbConnection.dbTypeInfo.TryGetValue(entityType, out entityDbTypeInfo)) { IDbEntity defaultEntity = new TEntity(); SchemaCollection entitySchemaCollection = new SchemaCollection(); foreach (DbOperationInfo operation in defaultEntity.DbOperations.Values) { if (operation.ParameterType != null && !entitySchemaCollection.Contains(operation.ParameterType)) { DataTable schemaTable = this.GetSchema(operation.ParameterType); entitySchemaCollection.Add(schemaTable); } } entityDbTypeInfo = new DbTypeInfo() { DefaultEntity = defaultEntity, SchemaInfo = entitySchemaCollection }; DbConnection.dbTypeInfo[entityType] = entityDbTypeInfo; } return(entityDbTypeInfo); }
private void ProcessInlineSchema() { if (!inlineSchemaParser.ParseReaderNode()) // Done { inlineSchemaParser.FinishParsing(); XmlSchema schema = inlineSchemaParser.XmlSchema; string inlineNS = null; if (schema != null && schema.ErrorCount == 0) { try { SchemaInfo inlineSchemaInfo = new SchemaInfo(); inlineSchemaInfo.SchemaType = SchemaType.XSD; inlineNS = schema.TargetNamespace == null? string.Empty : schema.TargetNamespace; if (!SchemaInfo.TargetNamespaces.ContainsKey(inlineNS)) { if (SchemaCollection.Add(inlineNS, inlineSchemaInfo, schema, true) != null) //If no errors on compile //Add to validator's SchemaInfo { SchemaInfo.Add(inlineSchemaInfo, EventHandler); } } } catch (XmlSchemaException e) { SendValidationEvent(Res.Sch_CannotLoadSchema, new string[] { BaseUri.AbsoluteUri, e.Message }, XmlSeverityType.Error); } } inlineSchemaParser = null; } }
private void LoadSchema(string uri, string url) { if (this.XmlResolver == null) { return; } if (SchemaInfo.TargetNamespaces.ContainsKey(uri) && nsManager.LookupPrefix(uri) != null) { return; } SchemaInfo schemaInfo = null; if (SchemaCollection != null) { schemaInfo = SchemaCollection.GetSchemaInfo(uri); } if (schemaInfo != null) { if (schemaInfo.SchemaType != SchemaType.XSD) { throw new XmlException(Res.Xml_MultipleValidaitonTypes, string.Empty, this.PositionInfo.LineNumber, this.PositionInfo.LinePosition); } SchemaInfo.Add(schemaInfo, EventHandler); return; } if (url != null) { LoadSchemaFromLocation(uri, url); } }
public void Indexer_NullContentType_ShouldThrowException() { var schemaCollection = new SchemaCollection(); var schema = new JSchema(JsonSchemaType.Any, new JsonSchemaExtendedType()); Assert.Throws <ArgumentNullException>(() => schemaCollection[null] = schema); }
public SchemaCollection<StoredProcedureModel> Extract( DatabaseConfiguration configuration, StoredProcedureCollection procedures) { Dictionary<string, StoredProcedure> storedProcedureLookup = CreateLookup(procedures); using (var connection = new SqlConnection(_connectionStringProvider.ConnectionString)) { connection.Open(); var collection = new SchemaCollection<StoredProcedureModel>( _schemaElementCollectionBuilder, configuration.StoredProcedures.Namespace); foreach (var element in configuration.StoredProcedures.Elements) { string escapedFullName = _nameConverter.EscapeDatabaseName(element.SchemaName, element.Name); if (storedProcedureLookup.ContainsKey(escapedFullName)) { StoredProcedure procedure = storedProcedureLookup[escapedFullName]; StoredProcedureModel model = ToModel(connection, configuration, procedure); collection.AddElement(procedure.Schema, model); } else { throw new ArgumentException(string.Format("Unable to locate stored procedure '{0}'", escapedFullName)); } } return collection; } }
private void LoadSchemaFromLocation(string uri, string url) { XmlReader reader = null; SchemaInfo schemaInfo = null; try { Uri ruri = this.XmlResolver.ResolveUri(BaseUri, url); Stream stm = (Stream)this.XmlResolver.GetEntity(ruri, null, null); reader = new XmlTextReader(ruri.ToString(), stm, NameTable); //XmlSchema schema = SchemaCollection.Add(uri, reader, this.XmlResolver); Parser parser = new Parser(SchemaType.XSD, NameTable, SchemaNames, EventHandler); parser.XmlResolver = this.XmlResolver; SchemaType schemaType = parser.Parse(reader, uri); schemaInfo = new SchemaInfo(); schemaInfo.SchemaType = schemaType; if (schemaType == SchemaType.XSD) { if (SchemaCollection.EventHandler == null) { SchemaCollection.EventHandler = this.EventHandler; } SchemaCollection.Add(uri, schemaInfo, parser.XmlSchema, true); } //Add to validator's SchemaInfo SchemaInfo.Add(schemaInfo, EventHandler); while (reader.Read()) { ; // wellformness check } } catch (XmlSchemaException e) { schemaInfo = null; SendValidationEvent(SR.Sch_CannotLoadSchema, new string[] { uri, e.Message }, XmlSeverityType.Error); } catch (Exception e) { schemaInfo = null; SendValidationEvent(SR.Sch_CannotLoadSchema, new string[] { uri, e.Message }, XmlSeverityType.Warning); } finally { if (reader != null) { reader.Close(); } } }
public static SchemaInfo Schema(IBMWIoTP.ApiClient cli) { Console.WriteLine("Get all draft schima"); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(cli.GetAllDraftSchemas())); Console.WriteLine("==============================================================================="); SchemaDraft sd = new SchemaDraft(); sd.name = "test"; sd.schemaFile = "tempSchemaPi.json"; Console.WriteLine("Add schima draft"); SchemaInfo si = cli.AddDraftSchema(sd); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(si)); Console.WriteLine("==============================================================================="); Console.WriteLine("Updated draft schema content"); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(cli.UpdateDraftSchemaContent(si.id, "tempSchema.json"))); Console.WriteLine("==============================================================================="); Console.WriteLine("Delete draft schema"); cli.DeleteDraftSchema(cli.AddDraftSchema(sd).id); Console.WriteLine("==============================================================================="); Console.WriteLine("Updated draft schema metadata"); si.description = "new temp change"; Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(cli.UpdateDraftSchemaMetadata(si))); Console.WriteLine("==============================================================================="); Console.WriteLine("Get draft schima metadata"); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(cli.GetDraftSchemaMetadata(si.id))); Console.WriteLine("==============================================================================="); Console.WriteLine("Get draft schima Content"); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(cli.GetDraftSchemaContent(si.id))); Console.WriteLine("==============================================================================="); Console.WriteLine("Get All active schimas"); SchemaCollection coll = cli.GetAllActiveSchemas(); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(coll)); Console.WriteLine("==============================================================================="); if (coll.results.Length > 0) { Console.WriteLine("Get active schimas"); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(cli.GetActiveSchemaMetadata(coll.results[0].id))); Console.WriteLine("==============================================================================="); Console.WriteLine("Get active schima content "); Console.WriteLine(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(cli.GetActiveSchemaContent(coll.results[0].id))); Console.WriteLine("==============================================================================="); } return(si); }
private string CompareSchemas( SchemaCollection sourceSchemas, SchemaCollection targetSchema ) { var returnScript = ""; foreach (Schema item in sourceSchemas) { if (!item.IsSystemObject && !targetSchema.Contains(item.Name)) { returnScript += "CREATE SCHEMA [" + item.Name + "] AUTHORIZATION [" + item.Owner + "];" + Environment.NewLine + "GO" + Environment.NewLine; } } return(returnScript); }
private void correctionButton_Click(object sender, System.EventArgs e) { if (false == ValidatePublisher()) { return; } try { ConnectionManager.BeginTransaction(); // // Deserialize into a change record object // StringReader reader = new StringReader(correctionTextBox.Text); ChangeRecordCorrection changeRecordCorrection = ( ChangeRecordCorrection )correctionSerializer.Deserialize(reader); // // Validate what we created. // SchemaCollection.Validate(changeRecordCorrection); // // Create a new change record to hold the correction. // ChangeRecord changeRecord = new ChangeRecord(changeRecordCorrection); changeRecord.Process(); ConnectionManager.Commit(); // // If we made it this far, we were able to process the correction // MessageBox.Show("Correction processed!"); // // Refresh our display. // ShowChangeRecord(); } catch (Exception exception) { ConnectionManager.Abort(); MessageBox.Show("An exception occurred when trying to process the correction:\r\n\r\n" + exception.ToString()); } }
private void LoadSchemaFromLocation(string uri) { // is x-schema if (!XdrBuilder.IsXdrSchema(uri)) { return; } string url = uri.Substring(x_schema.Length); XmlReader reader = null; SchemaInfo xdrSchema = null; try { Uri ruri = this.XmlResolver.ResolveUri(BaseUri, url); Stream stm = (Stream)this.XmlResolver.GetEntity(ruri, null, null); reader = new XmlTextReader(ruri.ToString(), stm, NameTable); ((XmlTextReader)reader).XmlResolver = this.XmlResolver; Parser parser = new Parser(SchemaType.XDR, NameTable, SchemaNames, EventHandler); parser.XmlResolver = this.XmlResolver; parser.Parse(reader, uri); while (reader.Read()) { ; // wellformness check } xdrSchema = parser.XdrSchema; } catch (XmlSchemaException e) { SendValidationEvent(SR.Sch_CannotLoadSchema, new string[] { uri, e.Message }, XmlSeverityType.Error); } catch (Exception e) { SendValidationEvent(SR.Sch_CannotLoadSchema, new string[] { uri, e.Message }, XmlSeverityType.Warning); } finally { if (reader != null) { reader.Close(); } } if (xdrSchema != null && xdrSchema.ErrorCount == 0) { schemaInfo.Add(xdrSchema, EventHandler); SchemaCollection.Add(uri, xdrSchema, null, false); } }
private void Validate(SoapMessage message) { Debug.Enter(); StreamReader srdr = new StreamReader(message.Stream, System.Text.Encoding.UTF8); #if DEBUG Debug.Write(SeverityType.Verbose, CategoryType.None, srdr.ReadToEnd()); message.Stream.Seek(0, System.IO.SeekOrigin.Begin); #endif // // Validate incoming XML, ValidateStream will rewind stream when finished // so I don't have to. // SchemaCollection.Validate(message.Stream); Debug.Leave(); }
public static void Execute(string host, int datasourceid, bool useSelection = false) { if (!new Api.GetSchema(host, datasourceid).ExecuteLoggedIn(out string result)) { Useful.Error(Resources.UnableToRetrieveSchema, Resources.Error); return; } SchemaModel model = new SchemaModel(); bool UserValidation = false; Range r = useSelection ? Useful.ActiveRange : Useful.ActiveSheet.UsedRange; if (!r.ValidateAgainstShemata(SchemaCollection.Deserialize(result).GetSchemata(), ref UserValidation, ref model)) { Useful.Error(Resources.SchemaValidationFailure, Resources.Error); return; } if (UserValidation) { if (UserSchemaValidation.Launch(model, false) == DialogResult.Cancel) { return; } if (!new Api.UpdateSchema(host, datasourceid, model).ExecuteLoggedIn(out result)) { Useful.Error(string.Format(Resources.UnableToUpdateRemoteSchema, result), Resources.Error); return; } } if (!new Api.Post(host, datasourceid, r.ToCSV(), false).ExecuteLoggedIn(out result)) { Useful.Error(string.Format(Resources.PostErrorText, result), Resources.Error); return; } MessageBox.Show(Resources.PostSuccess, Resources.Success); }
public void Write(SchemaCollection<UserDefinedTableTypeModel> types) { WriteNamespaceStart(types.ElementNamespace); foreach (var schema in types.SchemaElementCollections.OrderBy(s => s.SchemaName)) { BeginWriteStaticClass(schema.SchemaName); { List<UserDefinedTableTypeModel> elements = schema.Elements.OrderBy(t => t.DatabaseName).ToList(); int lastIndex = elements.Count - 1; for (int i = 0; i < elements.Count; i++) { var element = elements[i]; WriteType(element, i == lastIndex); } } WriteBlockEnd(); } WriteBlockEnd(); }
private void ProcessInlineSchema() { if (!_inlineSchemaParser.ParseReaderNode()) { // Done _inlineSchemaParser.FinishParsing(); SchemaInfo xdrSchema = _inlineSchemaParser.XdrSchema; if (xdrSchema != null && xdrSchema.ErrorCount == 0) { foreach (string inlineNS in xdrSchema.TargetNamespaces.Keys) { if (!this.schemaInfo.HasSchema(inlineNS)) { schemaInfo.Add(xdrSchema, EventHandler); SchemaCollection.Add(inlineNS, xdrSchema, null, false); break; } } } _inlineSchemaParser = null; } }
/// <summary> /// Gets the tables. /// </summary> /// <returns>The tables.</returns> public SchemaCollection GetTables() { SchemaCollection list = new SchemaCollection(); using (SqlConnection connection = this.CreateConnection()) { this.Logger.Write("Opening connection for table list"); SqlCommand command = connection.CreateCommand(); command.CommandText = @" SELECT h.name + '.' + s.name AS name, s.object_id, s.modify_date FROM sys.tables s INNER JOIN sys.schemas h ON h.schema_id = s.schema_id WHERE s.type = 'U' ORDER BY s.name"; command.CommandType = CommandType.Text; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { list.Add(new SchemaData() { Name = (string)reader["name"], ObjectID = (int)reader["object_id"], Type = SchemaDataType.Table, Data = this.GetTableSchema((int)reader["object_id"]), ModifyDate = (DateTime)reader["modify_date"] }); } } } return list; }
//Copie les métata data d'une BDD d'un serveur vers un autre public static void copyMetaData(string connexion1, string connexion2, List <string> include, List <string> exclude) { //Obtenion de la DataBase de la premiere Connection--------------------------------------------------------------- ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings[connexion1]; SqlConnection Connection = new SqlConnection(connectionString.ToString()); //SMO Server object setup with SQLConnection. Server server = new Server(new ServerConnection(Connection)); string[] opt = connectionString.ToString().Split(';'); string dbName = ""; for (int i = 0; i < opt.Length; i++) { if (opt[i].Contains("Initial Catalog")) { dbName += opt[i].Split('=')[1]; } } //Set Database to the database Database db = server.Databases[dbName]; //---------------------------------------------------------------------------------------------------------------- //Obtenion de la DataBase de la seconde Connection--------------------------------------------------------------- ConnectionStringSettings connectionString2 = ConfigurationManager.ConnectionStrings[connexion2]; SqlConnection Connection2 = new SqlConnection(connectionString2.ToString()); //---------------------------------------------------------------------------------------------------------------- /*Option pour la creation des tables * inclus les cles primaires, les contraintes nonclustered et * if not exist pour ne pas creer une table qui existe deja*/ ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriPrimaryKey = true; scriptOptions.IncludeIfNotExists = true; scriptOptions.DriNonClustered = true; /*Option pour les foreign key de chaque table, * préposé de leur schéma*/ /*ScriptingOptions scrOpt = new ScriptingOptions(); * scrOpt.SchemaQualifyForeignKeysReferences = true; * scrOpt.DriForeignKeys = true; */ bool includeMode = exclude.Count == 0 || exclude == null; bool excludeMode = include.Count == 0 || include == null; Console.WriteLine("Obtention metadonnees tables et clefs"); List <string> schemaCollection = new List <string>(); List <string> tableCol = new List <string>(); //List<string> foreignKeyCol = new List<string>(); foreach (Table myTable in db.Tables) { if ((includeMode && include.Contains(myTable.Name)) || (excludeMode && !exclude.Contains(myTable.Name))) { //Si c'est un nouveau schéma on retient son nom if (!schemaCollection.Contains("[" + myTable.Schema + "]")) { schemaCollection.Add("[" + myTable.Schema + "]"); } //On joute le script de la table à tableCol foreach (string s in myTable.Script(scriptOptions)) { tableCol.Add(s); } #region foreign keys, inutile /*//On ajoute le script des foreign keys à foreignKeyCol * ForeignKeyCollection fk = myTable.ForeignKeys; * foreach (ForeignKey myFk in fk) * { * StringCollection stmp = myFk.Script(scrOpt); * string[] tmp2 = new string[stmp.Count]; * stmp.CopyTo(tmp2, 0); * foreignKeyCol.AddRange(tmp2); * }*/ #endregion } } //Eneleve le schéma par défault schemaCollection.Remove("[dbo]"); #region Procedures stockées, inutile /*Console.WriteLine("Obtention des Procédures stockées"); * ScriptingOptions scrOpt2 = new ScriptingOptions() { IncludeIfNotExists = true }; * StringCollection prostoCol = new StringCollection(); * foreach (StoredProcedure sp in db.StoredProcedures) * { * if (!sp.Schema.Equals("sys")) * { * StringCollection scsp = sp.Script(); * string[] tmp2 = new string[scsp.Count]; * scsp.CopyTo(tmp2, 0); * prostoCol.AddRange(tmp2); * } * * }*/ #endregion Console.WriteLine("Obtention Metadonees schemas"); SchemaCollection sc = db.Schemas; List <string> schemaCol = new List <string>(); foreach (Schema schem in sc) { if (schemaCollection.Contains(schem.ToString())) { string[] strtmp = { "AUTHORIZATION" }; schemaCol.Add(schem.Script(new ScriptingOptions() { IncludeIfNotExists = true })[0].Split(strtmp, new StringSplitOptions())[0] + "AUTHORIZATION [dbo]'"); } } Connection.Close(); //Execution sur la nouvelle base des scripts récupérés Connection2.Open(); using (SqlCommand cmd = Connection2.CreateCommand()) { Console.WriteLine("Création Schemas"); foreach (string str in schemaCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } Console.WriteLine("Création Tables"); foreach (string str in tableCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } } Connection2.Close(); }
public void Indexer_NullSchema_ShouldThrowException() { var schemaCollection = new SchemaCollection(); Assert.Throws <ArgumentNullException>(() => schemaCollection["contentType"] = null); }
/// <summary> /// Copie les métata data (schema, tables, PK et FK, ProcStock d'une BDD dans un tableau à 4 entrées: /// <list type="dd">TABLE</list> /// <list type="dd">SCHEMA</list> /// <list type="dd">FK</list> /// <list type="dd">PROCSTOC</list> /// </summary> /// <param name="ListMode">rien ou vide pour ne pas tenir compte de la liste , toutes les tables de la source sont concernées / "exclude" pour donner une liste de tables à exclure / "include" pour donner le nom des tables à inclure</param> /// <param name="ListTableName">la liste des noms des tables à considerer dns un mode exclude ou include </param> public override DataSet GetMetatDataDBScripts(ListeMode mode = ListeMode.Aucune, List <DatabaseTable> ListTableName = null) { // nom de la base de source string dbName = Connection.Database.ToString(); // structure de retour contenant les scripts DataSet ds = new DataSet("SCRIPTS"); DataTable tableDT = new DataTable("TABLE"); tableDT.Columns.Add("sql"); ds.Tables.Add(tableDT); DataTable fkDT = new DataTable("FK"); fkDT.Columns.Add("sql"); ds.Tables.Add(fkDT); DataTable procstocDT = new DataTable("PROCSTOC"); procstocDT.Columns.Add("sql"); ds.Tables.Add(procstocDT); DataTable schemaDT = new DataTable("SCHEMA"); schemaDT.Columns.Add("sql"); ds.Tables.Add(schemaDT); //SMO Server object setup with SQLConnection. Server server = new Server(new ServerConnection((SqlConnection)Connection)); //Set Database to the database Database db = server.Databases[dbName]; //---------------------------------------------------------------------------------------------------------------- /*Option pour la creation des tables * inclus les cles primaires, les contraintes nonclustered et * if not exist pour ne pas creer une table qui existe deja*/ ScriptingOptions tablesScriptOptions = new ScriptingOptions(); tablesScriptOptions.DriPrimaryKey = true; tablesScriptOptions.IncludeIfNotExists = true; tablesScriptOptions.DriNonClustered = true; /*Option pour les foreign key de chaque table, * préposé de leur schéma*/ ScriptingOptions fkScriptOptions = new ScriptingOptions(); fkScriptOptions.SchemaQualifyForeignKeysReferences = true; fkScriptOptions.DriForeignKeys = true; InfoLogger.Debug("Obtention metadonnees tables et clefs"); StringCollection schemaCollection = new StringCollection(); foreach (Table myTable in db.Tables) { // si il y a une liste if (mode.Equals(ListeMode.Include)) { if (!ListTableName.Contains(myTable.Name, myTable.Schema)) { continue; } } else if (mode.Equals(ListeMode.Exclude)) { if (ListTableName.Contains(myTable.Name, myTable.Schema)) { continue; } } //Si c'est un nouveau schéma on retient son nom if (!schemaCollection.Contains("[" + myTable.Schema + "]")) { schemaCollection.Add("[" + myTable.Schema + "]"); } //On ajoute le script de la table à tableCol StringCollection tableScripts = myTable.Script(tablesScriptOptions); // maj de la Dataset DataRow dr = tableDT.NewRow(); foreach (string scriptLine in tableScripts) { dr["sql"] += scriptLine + System.Environment.NewLine; } tableDT.Rows.Add(dr); //On ajoute le script des foreign keys à foreignKeyCol ForeignKeyCollection fk = myTable.ForeignKeys; foreach (ForeignKey myFk in fk) { StringCollection stmp = myFk.Script(fkScriptOptions); // maj de la Dataset DataRow fkDr = fkDT.NewRow(); foreach (string scriptLine in stmp) { fkDr["sql"] += scriptLine + System.Environment.NewLine; } fkDT.Rows.Add(fkDr); } } //Enleve le schéma par défault schemaCollection.Remove("[dbo]"); InfoLogger.Debug("Obtention des Procédures stockées"); ScriptingOptions scrOptProcStoc = new ScriptingOptions() { NoCommandTerminator = false, ScriptBatchTerminator = true, IncludeIfNotExists = true }; foreach (StoredProcedure sp in db.StoredProcedures) { if (!sp.Schema.Equals("sys") && !sp.IsSystemObject) { StringCollection scsp = sp.Script(scrOptProcStoc); // maj de la Dataset DataRow pcDr = procstocDT.NewRow(); foreach (string scriptLine in scsp) { pcDr["sql"] += scriptLine + System.Environment.NewLine; } procstocDT.Rows.Add(pcDr); } } InfoLogger.Debug("Obtention Metadonnees schemas"); SchemaCollection sc = db.Schemas; ScriptingOptions scrOpt_Schema = new ScriptingOptions() { IncludeIfNotExists = true }; foreach (Schema schem in sc) { if (schemaCollection.Contains(schem.ToString())) { StringCollection schemaScripts = schem.Script(scrOpt_Schema); // maj de la Dataset DataRow schemaDr = schemaDT.NewRow(); foreach (string scriptLine in schemaScripts) { schemaDr["sql"] += scriptLine + System.Environment.NewLine; } schemaDT.Rows.Add(schemaDr); } } Connection.Close(); return(ds); }
static int Main(string[] args) { Console.OutputEncoding = System.Text.Encoding.UTF8; var commandLineApplication = new CommandLineApplication(); commandLineApplication.Name = "sqlscripter"; commandLineApplication.Description = "Sqlscripter"; var sqlserver = commandLineApplication.Option("-S | --server", "Sql Server", CommandOptionType.SingleValue); var sqluser = commandLineApplication.Option("-U | --user", "Sql User. Do not use in order to switch to integrated authentication.", CommandOptionType.SingleValue); var sqlpsw = commandLineApplication.Option("-P | --psw", "Sql Password", CommandOptionType.SingleValue); var sqldb = commandLineApplication.Option("-d | --database", "Sql Database", CommandOptionType.SingleValue); var nouseprogress = commandLineApplication.Option("--no-progress", "Disable progress bar", CommandOptionType.NoValue); commandLineApplication.Command("info", command => { command.Options.AddRange(command.Parent.Options); command.Description = $"{command.Name} render server information"; command.OnExecute(() => { ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); System.Console.WriteLine("Databases:"); foreach (var db in server.Databases) { System.Console.WriteLine($"\t{db}"); } return(0); }); }); commandLineApplication.Command("dbindex", command => { command.Options.AddRange(command.Parent.Options); command.Description = $"{command.Name} allow to connect to a database and build an ordered index of all objects"; var indexfile = command.Option("-i | --index", "Generate Index File", CommandOptionType.SingleValue); var querymode = command.Option("--query-mode", "Use object query for objects", CommandOptionType.NoValue); command.OnExecute(() => { DateTime pinned = DateTime.UtcNow; disable_console = nouseprogress.HasValue(); ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); Scripter scripter = new Scripter(server); ScriptingOptions op = new ScriptingOptions { AllowSystemObjects = false , WithDependencies = true }; scripter.Options = op; UrnCollection urns = new UrnCollection(); List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> preobjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); Console.WriteLine("CONNECTED ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; //bool display_progress = (!useprogress.HasValue()) && System.Console.h bool fast = querymode.HasValue(); Database db = server.Databases[sqldb.Value()]; //server.GetSmoObject SchemaCollection sc = db.Schemas; foreach (Schema schema in sc) { if (!schema.IsSystemObject) { preobjects.Add(schema.Urn); } } TableCollection tc = db.Tables; add_urns_from_collection(tc, urns, (!nouseprogress.HasValue())); if (fast) { add_urn_from_query(db, "P", (sp, sch) => db.StoredProcedures[sp, sch].Urn, urns, (!nouseprogress.HasValue())); } else { var sp = server.Databases[sqldb.Value()].StoredProcedures; add_urns_from_collection(sp, urns); } //-------------------------------- if (fast) { add_urn_from_query(db, "V", (sp, sch) => db.Views[sp, sch].Urn, urns, (!nouseprogress.HasValue())); } else { var vs = server.Databases[sqldb.Value()].Views; add_urns_from_collection(vs, urns); } var ss = server.Databases[sqldb.Value()].Synonyms; add_urns_from_collection(ss, urns); if (fast) { add_urn_from_query(db, "IF", (sp, sch) => db.UserDefinedFunctions[sp, sch].Urn, urns, (!nouseprogress.HasValue())); } else { var ff = server.Databases[sqldb.Value()].UserDefinedFunctions; add_urns_from_collection(ff, urns); } var tt = server.Databases[sqldb.Value()].UserDefinedTypes; add_urns_from_collection(tt, urns); Console.WriteLine("DISCOVERING ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; //scripter.DiscoveryProgress += Scripter_DiscoveryProgress; DependencyTree tr = scripter.DiscoverDependencies(urns, true); Console.WriteLine("DEPENDENCY ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; DependencyCollection dc = scripter.WalkDependencies(tr); Console.WriteLine("WALKED ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; dependency_index index = dependency.index(tr); Console.WriteLine("INDEXED ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; string path = indexfile.Value(); if (null != path) { if (System.IO.File.Exists(path)) { System.IO.File.Delete(path); } System.IO.File.AppendAllText(path, "#file auto-generated" + Environment.NewLine); } foreach (Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn in preobjects) { UrnToIndex(db.Name, path, urn, index); } foreach (DependencyCollectionNode j in dc) { Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn = j.Urn; UrnToIndex(db.Name, path, urn, index); } Console.WriteLine("EXPORTED ({0})", DateTime.UtcNow.Subtract(pinned)); return(0); }); }); commandLineApplication.Command("urn", command => { var urn = command.Option("-u | --urn", "Sql Urn", CommandOptionType.SingleValue); command.Description = @"Normalize an Input. From Server[@Name='4f4c6527222b']/Database[@Name='MONITORING']/Table[@Name='Procedures' and @Schema='Gathering'] to Table:[Gathering].[Procedures]"; command.OnExecute(() => { Console.WriteLine(NormalizeUrn(urn.Value())); return(0); }); }); commandLineApplication.Command("script", command => { command.Options.AddRange(command.Parent.Options); command.Description = $"{command.Name} allows to script objects listed in a file or in the command line"; var target = command.Option("-t | --target", "Sql target Object", CommandOptionType.MultipleValue); var output = command.Option("-o | --output", "Script Output", CommandOptionType.SingleValue); var file = command.Option("-f | -i | --file", "Input File", CommandOptionType.SingleValue); command.OnExecute(() => { disable_console = nouseprogress.HasValue(); ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); string[] objs = target.Values.ToArray(); if (null != file.Value()) { objs = System.IO.File.ReadAllLines(file.Value()); } string outputdir = output.Value() ?? "./"; Script(objs, server.Databases[sqldb.Value()], outputdir, (!nouseprogress.HasValue())); return(0); }); //scripter.Script( }); commandLineApplication.Command("build", command => { command.Options.AddRange(command.Parent.Options); var indexfiles = command.Option("-i | --index", "Input Index File", CommandOptionType.MultipleValue); var excludetyes = command.Option("-x | --exclude-types", "Types to exclude from the index", CommandOptionType.MultipleValue); var output = command.Option("-o | --output", "Script Build Output", CommandOptionType.SingleValue); var basepath = command.Option("-b | --basepath", "Root of files referenced by index", CommandOptionType.SingleValue); command.OnExecute(() => { string outputfile = output.Value(); if (null != outputfile) { if (System.IO.File.Exists(outputfile)) { System.IO.File.Delete(outputfile); } } //ProcessDirs(pretypes.Values.ToArray(), outputfile); string basep = basepath.Value(); string main_index = indexfiles.Values[0]; if (null == basep) { basep = System.IO.Path.GetDirectoryName(main_index); } foreach (string indexfile in indexfiles.Values) { string indexfilepath = System.IO.Path.GetFullPath(indexfile); System.Console.WriteLine("Adding " + System.IO.Path.GetFileName(indexfile)); string[] types = System.IO.File.ReadAllLines(indexfilepath); int types_count = 0; foreach (string tt in types) { obj_info oi = ObjectInfo(tt); drawTextProgressBar(++types_count, types.Length, $" ({tt}) "); if (oi.is_type) { if (!excludetyes.Values.Contains(oi.type)) { string source = FilePath(basep, oi, false); string content = System.IO.File.ReadAllText(source); if (null != outputfile) { System.IO.File.AppendAllText(outputfile, content); } else { Console.Write(content); } } } } } //ProcessDirs(posttypes.Values.ToArray(), outputfile); }); }); commandLineApplication.Command("coverage", command => { command.Options.AddRange(command.Parent.Options); command.Description = @"Run sql stetament from files or command line and track coverage"; var indexfiles = command.Option("-i | --input", "Input Coverage File", CommandOptionType.MultipleValue); var statements = command.Option("-s | --statement", "Input Coverage Statement", CommandOptionType.MultipleValue); var free_proccache = command.Option("-f | --free-proccache", @"Run DBCC FREEPROCCACHE before your test in order to count only what you are running and not previous runs. Do Not use in a production system.", CommandOptionType.NoValue); command.OnExecute(() => { disable_console = nouseprogress.HasValue(); ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); Database db = server.Databases[sqldb.Value()]; if (free_proccache.HasValue()) { db.ExecuteNonQuery("DBCC FREEPROCCACHE"); } foreach (string statement in statements.Values) { string sql = statement; handle_coverage(db, sql); } foreach (string indexfile in indexfiles.Values) { string[] lines = System.IO.File.ReadAllLines(indexfile); string sql = string.Join("\r\n", lines); handle_coverage(db, sql); } return(0); }); }); commandLineApplication.HelpOption("-h | --help", inherited: true); try { int r = commandLineApplication.Execute(args); return(r); } catch (CommandParsingException ex) { Console.Error.Write("Invalid Command Line: "); Console.Error.WriteLine(ex.Message); Console.Error.WriteLine(commandLineApplication.GetHelpText()); return(22); } catch (Exception ex) { Console.Error.WriteLine(ex.ToString()); return(99); } }
public void Add_NullSchema_ShouldThrowException() { var schemaCollection = new SchemaCollection(); Assert.Throws <ArgumentNullException>(() => schemaCollection.Add("contentType", null)); }
/// <summary> /// Look at <see cref="IFormatter.Format(SchemaCollection, StringBuilder)"/> documentation. /// </summary> public virtual void Format(SchemaCollection schemas, StringBuilder output) { }
/// <summary> /// Look at <see cref="IFormatter.Format(SchemaCollection, StringBuilder)"/> documentation. /// </summary> public override void Format(SchemaCollection schemas, StringBuilder output) { // Enclose putput in an <schematron> element. output.Insert(0, "<schematron>"); output.Append("</schematron>"); }
public Database(IMigrationContext context) { _context = context; _tables = new TableCollection(_root); _schemata = new SchemaCollection(_root); }
//Copie les métata data d'une BDD sur un serveur vers un autre public static void copyMetaData(String sourceConnection = "Connection1", String destinationConnection = "Connection2") { //Obtenion de la DataBase de la premiere Connection--------------------------------------------------------------- ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings[sourceConnection]; SqlConnection Connection = new SqlConnection(connectionString.ToString()); //SMO Server object setup with SQLConnection. Server server = new Server(new ServerConnection(Connection)); string[] opt = connectionString.ToString().Split(';'); string dbName = ""; for (int i = 0; i < opt.Length; i++) { if (opt[i].Contains("Initial Catalog")) { dbName += opt[i].Split('=')[1]; } } //Set Database to the database Database db = server.Databases[dbName]; //---------------------------------------------------------------------------------------------------------------- //Obtenion de la DataBase de la seconde Connection--------------------------------------------------------------- ConnectionStringSettings connectionString2 = ConfigurationManager.ConnectionStrings[destinationConnection]; SqlConnection Connection2 = new SqlConnection(connectionString2.ToString()); //---------------------------------------------------------------------------------------------------------------- /*Option pour la creation des tables * inclus les cles primaires, les contraintes nonclustered et * if not exist pour ne pas creer une table qui existe deja*/ ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriPrimaryKey = true; scriptOptions.IncludeIfNotExists = true; scriptOptions.DriNonClustered = true; /*Option pour les foreign key de chaque table, * préposé de leur schéma*/ ScriptingOptions scrOpt = new ScriptingOptions(); scrOpt.SchemaQualifyForeignKeysReferences = true; scrOpt.DriForeignKeys = true; Console.WriteLine("Obtention metadonnees tables et clefs"); StringCollection schemaCollection = new StringCollection(); StringCollection tableCol = new StringCollection(); StringCollection foreignKeyCol = new StringCollection(); foreach (Table myTable in db.Tables) { //Si c'est un nouveau schéma on retient son nom if (!schemaCollection.Contains("[" + myTable.Schema + "]")) { schemaCollection.Add("[" + myTable.Schema + "]"); } //On joute le script de la table à tableCol StringCollection tableScripts = myTable.Script(scriptOptions); string[] tmp = new string[tableScripts.Count]; tableScripts.CopyTo(tmp, 0); tableCol.AddRange(tmp); //On ajoute le script des foreign keys à foreignKeyCol ForeignKeyCollection fk = myTable.ForeignKeys; foreach (ForeignKey myFk in fk) { StringCollection stmp = myFk.Script(scrOpt); string[] tmp2 = new string[stmp.Count]; stmp.CopyTo(tmp2, 0); foreignKeyCol.AddRange(tmp2); } } //Eneleve le schéma par défault schemaCollection.Remove("[dbo]"); Console.WriteLine("Obtention des Procédures stockées"); ScriptingOptions scrOpt2 = new ScriptingOptions() { IncludeIfNotExists = true }; StringCollection prostoCol = new StringCollection(); foreach (StoredProcedure sp in db.StoredProcedures) { if (!sp.Schema.Equals("sys") && !sp.IsSystemObject) { StringCollection scsp = sp.Script(); string[] tmp2 = new string[scsp.Count]; scsp.CopyTo(tmp2, 0); prostoCol.AddRange(tmp2); } } Console.WriteLine("Obtention Metadonees schemas"); SchemaCollection sc = db.Schemas; StringCollection schemaCol = new StringCollection(); foreach (Schema schem in sc) { if (schemaCollection.Contains(schem.ToString())) { StringCollection tableScripts = schem.Script(new ScriptingOptions() { IncludeIfNotExists = true }); string[] tmp = new string[tableScripts.Count]; tableScripts.CopyTo(tmp, 0); string[] strtmp = { "AUTHORIZATION" }; schemaCol.Add(tmp[0].Split(strtmp, new StringSplitOptions())[0] + "AUTHORIZATION [dbo]'"); } } Connection.Close(); //Execution sur la nouvelle base des scripts récupérés Connection2.Open(); using (SqlCommand cmd = Connection2.CreateCommand()) { Console.WriteLine("Création Schemas"); foreach (string str in schemaCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } Console.WriteLine("Création Tables"); foreach (string str in tableCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } Console.WriteLine("Création Clefs etrangeres"); foreach (string str in foreignKeyCol) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } Console.WriteLine("Création Procedures Stockées"); foreach (string str in prostoCol) { try { cmd.CommandText = str; cmd.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine("Suite à une erreur la procédure suivante n'a pas pu etre copiée"); Console.WriteLine(str); Console.WriteLine(e.Message); } } } Connection2.Close(); }
public override void ProcessMessage(SoapMessage message) { try { switch (message.Stage) { case SoapMessageStage.BeforeDeserialize: // // Check to see if the server has been manually stopped. // if (0 == Config.GetInt("Run", 1)) { DispositionReport.ThrowFinal(new UDDIException(ErrorType.E_busy, "UDDI_ERROR_BUSY_SERVICENOTAVAILABLE")); // // DispositionReport.ThrowFinal will close the HTTP stream so there is no point going on in this method // return; } try { // // Validate against the UDDI schemas // SchemaCollection.Validate(oldStream); } catch (Exception e) { DispositionReport.ThrowFinal(new UDDIException(ErrorType.E_fatalError, "UDDI_ERROR_FATALERROR_SCHEMAVALIDATIONFAILED", e.Message)); // // DispositionReport.ThrowFinal will close the HTTP stream so there is no point going on in this method // return; } // // Make sure we only have 1 UDDI request in the SOAP body. This method will also set the versionMajor // member. // CheckForSingleRequest(oldStream); // // If this is a v1 message, we'll first map it to the v2 // namespace so that it can be processed by the new // library. // if (1 == Context.ApiVersionMajor || 2 == Context.ApiVersionMajor) { TextReader reader = new StreamReader(oldStream); TextWriter writer = new StreamWriter(newStream, new System.Text.UTF8Encoding(false)); string xml = reader.ReadToEnd(); if (1 == Context.ApiVersionMajor) { xml = xml.Replace("=\"urn:uddi-org:api\"", "=\"urn:uddi-org:api_v2\""); xml = xml.Replace("='urn:uddi-org:api'", "=\"urn:uddi-org:api_v2\""); } writer.Write(xml); writer.Flush(); newStream.Position = 0; } break; case SoapMessageStage.AfterDeserialize: // // After the message is deserialized is the earliest place where we // have access to our SOAP headers. // CheckSOAPHeaders(message); // // Now that the message has been deserialized, make // sure that the generic and xmlns attributes agree. // IMessage obj = message.GetInParameterValue(0) as IMessage; if (null != obj) { // // We only need to do this if the deserialized object supports IMessage // string expected = Context.ApiVersionMajor + ".0"; string actual = obj.Generic.Trim(); if (expected != actual) { throw new UDDIException(ErrorType.E_unrecognizedVersion, "UDDI_ERROR_UNKNOWNVERSION_GENERICNAMESPACEMISMATCH"); } } break; case SoapMessageStage.BeforeSerialize: break; case SoapMessageStage.AfterSerialize: // // There may have been exceptions thrown during serialization. // if (null != message.Exception && (null == message.Exception.Detail || 0 == message.Exception.Detail.ChildNodes.Count)) { DispositionReport.ThrowFinal(new UDDIException(ErrorType.E_fatalError, "UDDI_ERROR_FATALERROR_FAILEDDESERIALIZATION")); // // DispositionReport.ThrowFinal will close the HTTP stream so there is no point going on in this method // return; } // // If the original request was v1, then we'll need to // remap the output to use the v1 namespace. // if (1 == Context.ApiVersionMajor || 2 == Context.ApiVersionMajor) { newStream.Position = 0; TextReader reader = new StreamReader(newStream); TextWriter writer = new StreamWriter(oldStream, new System.Text.UTF8Encoding(false)); string xml = reader.ReadToEnd(); // // We don't have to use the same 'loose' replacement as we did on the incoming request // because our response will be serialized such that the default namespace is our UDDI // namespace. // if (1 == Context.ApiVersionMajor) { xml = xml.Replace("xmlns=\"urn:uddi-org:api_v2\"", "xmlns=\"urn:uddi-org:api\""); xml = xml.Replace("generic=\"2.0\"", "generic=\"1.0\""); } writer.Write(xml); writer.Flush(); } break; default: throw new UDDIException(ErrorType.E_fatalError, "UDDI_ERROR_FATALERROR_UNKNOWNEXTSTAGE"); } } catch (Exception e) { DispositionReport.Throw(e); } }
public static void ImportRCF() { FileStream rcfStream = File.Open(rcfFile, FileMode.Open, FileAccess.Read, FileShare.Read); try { // // Validate the RCF file. // SchemaCollection.Validate(rcfStream); // // // Open our RCF file, it has already been checked to make sure it exists. // XmlTextReader rcfReader = new XmlTextReader(rcfStream); while (true == rcfReader.Read() && false == rcfReader.EOF) { if (rcfReader.Name.Equals("operator") && rcfReader.NamespaceURI.Equals(UDDI.Replication.Constants.Namespace)) { // // For each operator node, we want the following information. These are all // mandatory elements, so if any were missing we should not have passed schema // validation. // // operatorNodeID (this is the operatorKey) // operatorStatus // soapReplicationUrl // certificate // operatorCustodyName (operatorName) // // // Note that contacts are currently being ignored. This is because we are not sending // the businessKey for the operator. Since we store contacts based on businessKey (actually businessID) // we do not have a way of storing contacts. IN 70 says that the operatorNodeID should actually be this // businessKey, so once we decide to implement this, we'll process contacts. // X509Certificate certificate = null; string operatorKey = null; string operatorName = null; string soapReplicationUrl = null; int operatorStatus = 0; string localOperatorKey = Config.GetString("OperatorKey").ToLower(); do { switch (rcfReader.Name) { case "operatorNodeID": { operatorKey = rcfReader.ReadElementString(); break; } case "operatorCustodyName": { operatorName = rcfReader.ReadElementString(); break; } case "operatorStatus": { operatorStatus = OperatorStatus2ID(rcfReader.ReadElementString()); break; } case "soapReplicationURL": { soapReplicationUrl = rcfReader.ReadElementString(); break; } case "certificate": { // // Read our data in 1024 byte chunks. Keep an array list of these // chunks. // int bytesRead = 0; int chunkSize = 1024; ArrayList chunks = new ArrayList(); do { byte[] data = new byte[chunkSize]; bytesRead = rcfReader.ReadBase64(data, 0, chunkSize); if (bytesRead > 0) { chunks.Add(data); } }while(bytesRead != 0); // // Allocate a buffer to hold all of our chunks. // byte[] certificateData = new byte[chunks.Count * chunkSize]; // // Copy each chunk into our buffer. This buffer is our certificate. // int index = 0; foreach (byte[] chunkData in chunks) { Array.Copy(chunkData, 0, certificateData, index, chunkData.Length); index += chunkData.Length; } // // Create a certificate from our byte data. // certificate = new X509Certificate(certificateData); break; } } }while(true == rcfReader.Read() && false == rcfReader.EOF && false == rcfReader.Name.Equals("operator")); // // Make sure we identify the local operator. // if (false == operatorKey.ToLower().Equals(localOperatorKey)) { // // Import this operator // SaveOperatorInfo(operatorKey, operatorName, soapReplicationUrl, operatorStatus, certificate); Console.WriteLine("Successfully imported {0}.", operatorName); } else { SaveOperatorInfo(null, operatorName, soapReplicationUrl, operatorStatus, certificate); Console.WriteLine("Successfully update the local operator."); } } } } catch (XmlException xmlException) { Console.WriteLine("Exception processing the RCF: "); Console.WriteLine("\t"); Console.WriteLine(xmlException.ToString()); } catch (XmlSchemaException schemaException) { Console.WriteLine("The RCF did not pass schema validation: "); Console.WriteLine("\t"); Console.WriteLine(schemaException.ToString()); } finally { rcfStream.Close(); } }
static int Main(string[] args) { Console.OutputEncoding = System.Text.Encoding.UTF8; var commandLineApplication = new CommandLineApplication(); commandLineApplication.Name = "sqlscripter"; commandLineApplication.Description = "Sqlscripter"; var sqlserver = commandLineApplication.Option("-S | --server", "Sql Server", CommandOptionType.SingleValue); var sqluser = commandLineApplication.Option("-U | --user", "Sql User. Do not use in order to switch to integrated authentication.", CommandOptionType.SingleValue); var sqlpsw = commandLineApplication.Option("-P | --psw", "Sql Password", CommandOptionType.SingleValue); var sqldb = commandLineApplication.Option("-d | --database", "Sql Database", CommandOptionType.SingleValue); var nouseprogress = commandLineApplication.Option("--no-progress", "Disable progress bar", CommandOptionType.NoValue); commandLineApplication.Command("info", command => { command.Options.AddRange(command.Parent.Options); command.Description = $"{command.Name} render server information"; command.OnExecute(() => { ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); System.Console.WriteLine("Databases:"); foreach (var db in server.Databases) { System.Console.WriteLine($"\t{db}"); } return(0); }); }); commandLineApplication.Command("dbindex", command => { command.Options.AddRange(command.Parent.Options); command.Description = $"{command.Name} allow to connect to a database and build an ordered index of all objects"; var indexfile = command.Option("-i | --index", "Generate Index File", CommandOptionType.SingleValue); var querymode = command.Option("--query-mode", "Use object query for objects", CommandOptionType.NoValue); var one_stored = command.Option("--one-stored", "Generate one stored dependency", CommandOptionType.SingleValue); var include_schemas = command.Option("-sc | --schema", "Database schemas to include in the output", CommandOptionType.MultipleValue); command.OnExecute(() => { StringCollection schemas = new StringCollection(); if (null != include_schemas) { schemas.AddRange(include_schemas.Values.ToArray()); } DateTime pinned = DateTime.UtcNow; util.disable_console = nouseprogress.HasValue(); ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); Scripter scripter = new Scripter(server); ScriptingOptions op = new ScriptingOptions { AllowSystemObjects = false , WithDependencies = true }; scripter.Options = op; UrnCollection urns = new UrnCollection(); List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> preobjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); Console.WriteLine("CONNECTED ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; //bool display_progress = (!useprogress.HasValue()) && System.Console.h bool fast = querymode.HasValue(); Database db = server.Databases[sqldb.Value()]; //add all or just one sp if (one_stored.HasValue()) { var sp = db.StoredProcedures[one_stored.Value()]; urns.Add(sp.Urn); } else { SchemaCollection sc = db.Schemas; foreach (Schema schema in sc) { if (!schema.IsSystemObject) { //if (null==schemas || schemas.Count==0 || (null != schemas && schemas.Count != 0 && schemas.Contains(schema.Name))) preobjects.Add(schema.Urn); } } TableCollection tc = db.Tables; add_urns_from_collection(tc, urns, (!nouseprogress.HasValue())); if (fast) { add_urn_from_query(db, "P", (sp, sch) => db.StoredProcedures[sp, sch].Urn, urns, (!nouseprogress.HasValue()) , (sp, sch) => !db.StoredProcedures[sp, sch].IsSystemObject); } else { var sp = server.Databases[sqldb.Value()].StoredProcedures; add_urns_from_collection(sp, urns); } //-------------------------------- if (fast) { add_urn_from_query(db, "V", (sp, sch) => db.Views[sp, sch].Urn, urns, (!nouseprogress.HasValue())); } else { var vs = server.Databases[sqldb.Value()].Views; add_urns_from_collection(vs, urns); } var ss = server.Databases[sqldb.Value()].Synonyms; add_urns_from_collection(ss, urns); if (fast) { add_urn_from_query(db, "IF", (sp, sch) => db.UserDefinedFunctions[sp, sch].Urn, urns, (!nouseprogress.HasValue())); } else { var ff = server.Databases[sqldb.Value()].UserDefinedFunctions; add_urns_from_collection(ff, urns); } var ut = server.Databases[sqldb.Value()].UserDefinedDataTypes; add_urns_from_collection(ut, urns); var tt = server.Databases[sqldb.Value()].UserDefinedTypes; add_urns_from_collection(tt, urns); var dt = server.Databases[sqldb.Value()].UserDefinedTableTypes; add_urns_from_collection(dt, urns); } //string s = urns[0].GetAttribute("Schema"); //(?m)(?<=\@Schema=)'(.+?)' Console.WriteLine("DISCOVERING ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; //scripter.DiscoveryProgress += Scripter_DiscoveryProgress; DependencyTree tr = scripter.DiscoverDependencies(urns, true); Console.WriteLine("DEPENDENCY ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; DependencyCollection dc = scripter.WalkDependencies(tr); Console.WriteLine("WALKED ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; dependency_index index = dependency.index(tr); Console.WriteLine("INDEXED ({0})", DateTime.UtcNow.Subtract(pinned)); pinned = DateTime.UtcNow; string path = indexfile.Value(); if (null != path) { if (System.IO.File.Exists(path)) { System.IO.File.Delete(path); } System.IO.File.AppendAllText(path, "#file auto-generated" + Environment.NewLine); } foreach (Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn in preobjects) { if (schemas == null || schemas.Count == 0 || (schemas.Count > 0 && schemas.IndexOf(GetSchemaFromUrn(urn.ToString())) != -1)) { UrnToIndex(db.Name, path, urn, index); } } foreach (DependencyCollectionNode j in dc) { Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn = j.Urn; if (schemas == null || schemas.Count == 0 || (schemas.Count > 0 && schemas.IndexOf(GetSchemaFromUrn(urn.ToString())) != -1)) { UrnToIndex(db.Name, path, urn, index); } } Console.WriteLine("EXPORTED ({0})", DateTime.UtcNow.Subtract(pinned)); return(0); }); }); commandLineApplication.Command("urn", command => { var urn = command.Option("-u | --urn", "Sql Urn", CommandOptionType.SingleValue); command.Description = @"Normalize an Input. From Server[@Name='4f4c6527222b']/Database[@Name='MONITORING']/Table[@Name='Procedures' and @Schema='Gathering'] to Table:[Gathering].[Procedures]"; command.OnExecute(() => { Console.WriteLine(NormalizeUrn(urn.Value())); return(0); }); }); commandLineApplication.Command("script", command => { command.Options.AddRange(command.Parent.Options); command.Description = $"{command.Name} allows to script objects listed in a file or in the command line"; var target = command.Option("-t | --target", "Sql target Object. For instance Table:[dbo].[Table_1]", CommandOptionType.MultipleValue); var output = command.Option("-o | --output", "Scripts Directory Output", CommandOptionType.SingleValue); var file = command.Option("-f | -i | --file", "Input File", CommandOptionType.SingleValue); var version = command.Option("--sql-version", "Sql Version Generation Target", CommandOptionType.SingleValue); var file_version = command.Option("--file-version", "Enable object version support", CommandOptionType.NoValue); var modified = command.Option("--modified", "Export all object modified in the last <input> minutes. Es 1440 last day", CommandOptionType.SingleValue); var plural_exceptions = command.Option("-sp | --singular-plural", "Singular|Plural exceptions i.e. Security|Security", CommandOptionType.MultipleValue); command.OnExecute(() => { util.disable_console = nouseprogress.HasValue(); StringCollection pluralExceptions = new StringCollection(); if (null != plural_exceptions) { pluralExceptions.AddRange(plural_exceptions.Values.ToArray()); } ProcessPlurals(pluralExceptions); ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); Database db = server.Databases[sqldb.Value()]; //TODO: ALLOW MULTIPLE TARGETS AND MULTIPLE FILES List <string> objs = new List <string>(); objs.AddRange(target.Values.ToArray()); if (null != file.Value()) { string [] lines = System.IO.File.ReadAllLines(file.Value()); objs.AddRange(lines); } if (modified.HasValue()) { int minutes = int.Parse(modified.Value()); string [] mods = exporter.get_modified_objects(db, minutes); foreach (string obj in mods) { Console.WriteLine(string.Format("\t\tMODIFIED:\t{0}", obj)); } objs.AddRange(mods); } string outputdir = output.Value() ?? "./"; SqlServerVersion sql_version = SqlServerVersion.Version100; if (version.HasValue()) { sql_version = (SqlServerVersion)Enum.Parse(typeof(SqlServerVersion), version.Value()); } scripter.Script(objs.ToArray(), db , outputdir, (!nouseprogress.HasValue()) , sql_version , file_version.HasValue()); return(0); }); //scripter.Script( }); commandLineApplication.Command("build", command => { command.Options.AddRange(command.Parent.Options); var indexfiles = command.Option("-i | --index", "Input Index File", CommandOptionType.MultipleValue); var excludetyes = command.Option("-x | --exclude-types", "Types to exclude from the index", CommandOptionType.MultipleValue); var output = command.Option("-o | --output", "Script Build Output", CommandOptionType.SingleValue); var basepath = command.Option("-b | --basepath", "Root of files referenced by index", CommandOptionType.SingleValue); var database_version = command.Option("--database-version", "Insert database version in script with object version", CommandOptionType.SingleValue); var use_relative_path = command.Option("-r | --relative-path", "Use indexes relative path to reference files", CommandOptionType.NoValue); var plural_exceptions = command.Option("-sp | --singular-plural", "Singular|Plural exceptions i.e. Security|Security", CommandOptionType.MultipleValue); command.OnExecute(() => { string outputfile = output.Value(); if (null != outputfile) { if (System.IO.File.Exists(outputfile)) { System.IO.File.Delete(outputfile); } } StringCollection pluralExceptions = new StringCollection(); if (null != plural_exceptions) { pluralExceptions.AddRange(plural_exceptions.Values.ToArray()); } ProcessPlurals(pluralExceptions); //ProcessDirs(pretypes.Values.ToArray(), outputfile); bool relative_path = use_relative_path.HasValue(); string basep = basepath.Value(); string main_index = indexfiles.Values[0]; if (null == basep) { basep = System.IO.Path.GetDirectoryName(main_index); } foreach (string indexfile in indexfiles.Values) { string indexfilepath = System.IO.Path.GetFullPath(System.IO.Path.Join(basep, System.IO.Path.GetFileName(indexfile))); if (!System.IO.File.Exists(indexfilepath)) { indexfilepath = System.IO.Path.GetFullPath(indexfile); } string indexfiledir = System.IO.Path.GetDirectoryName(indexfile); System.Console.WriteLine("Adding " + System.IO.Path.GetFileName(indexfile)); string[] types = System.IO.File.ReadAllLines(relative_path ? indexfile : indexfilepath); int types_count = 0; foreach (string tt in types) { obj_info oi = util.ObjectInfo(tt); util.drawTextProgressBar(++types_count, types.Length, $" ({tt}) "); if (oi.is_type) { if (!excludetyes.Values.Contains(oi.type)) { string source = util.FilePath((relative_path ? indexfiledir : basep), oi, false); string content = System.IO.File.ReadAllText(source); if (database_version.HasValue()) { content = scripter.insert_database_version(content, database_version.Value()); } if (null != outputfile) { System.IO.File.AppendAllText(outputfile, content); } else { Console.Write(content); } } } } } //ProcessDirs(posttypes.Values.ToArray(), outputfile); }); }); commandLineApplication.Command("coverage", command => { command.Options.AddRange(command.Parent.Options); command.Description = @"Run sql stetament from files or command line and track coverage"; var indexfiles = command.Option("-i | --input", "Input Coverage File", CommandOptionType.MultipleValue); var statements = command.Option("-s | --statement", "Input Coverage Statement", CommandOptionType.MultipleValue); var free_proccache = command.Option("-f | --free-proccache", @"Run DBCC FREEPROCCACHE before your test in order to count only what you are running and not previous runs. Do Not use in a production system.", CommandOptionType.NoValue); var no_exec = command.Option("-n | --no-exec", @"Do not Run the procedure.", CommandOptionType.NoValue); var tout_exec = command.Option("-t | --timeout-exec", @"Sql executions timeout.", CommandOptionType.SingleValue); var datail = command.Option("--detail", @"Provide the list of not covered query_hash", CommandOptionType.NoValue); var save = command.Option("--save", @"save a test result with performance and coverage", CommandOptionType.SingleValue); command.OnExecute(() => { util.disable_console = nouseprogress.HasValue(); ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); Database db = server.Databases[sqldb.Value()]; if (null == db) { throw new ScripterException("Invalid database"); } string save_path = null; if (save.HasValue()) { save_path = save.Value(); } if (free_proccache.HasValue()) { db.ExecuteNonQuery("DBCC FREEPROCCACHE"); } int timeout = -1; if (tout_exec.HasValue()) { timeout = Convert.ToInt32(tout_exec.Value()); } foreach (string statement in statements.Values) { string sql = statement; handle_coverage(db, sql, !no_exec.HasValue(), datail.HasValue(), save_path, timeout); } foreach (string indexfile in indexfiles.Values) { string[] lines = System.IO.File.ReadAllLines(indexfile); string sql = string.Join("\r\n", lines); handle_coverage(db, sql, !no_exec.HasValue(), datail.HasValue(), save_path, timeout); } return(0); }); }); commandLineApplication.Command("template", command => { command.Options.AddRange(command.Parent.Options); command.Description = @"Run sql statement from files or command line and track coverage"; //var indexfiles = command.Option("-i | --input", "Input Coverage File", CommandOptionType.MultipleValue); //var statements = command.Option("-s | --statement", "Input Coverage Statement", CommandOptionType.MultipleValue); //var free_proccache = command.Option("-f | --free-proccache", @"Run DBCC FREEPROCCACHE before your test in order // to count only what you are running and not previous runs. // Do Not use in a production system.", CommandOptionType.NoValue); //var no_exec = command.Option("-n | --no-exec", @"Do not Run the procedure.", CommandOptionType.NoValue); //var datail = command.Option("--detail", @"Provide the list of not covered query_hash", CommandOptionType.NoValue); //var save = command.Option("--save", @"save a test result with performance and coverage", CommandOptionType.SingleValue); var table = command.Option("-t | --table", "The table name to genarate CRUD", CommandOptionType.MultipleValue); var output = command.Option("-o | --output", "Scripts Directory Output", CommandOptionType.SingleValue); //var file = command.Option("-f | -i | --file", "Input File", CommandOptionType.SingleValue); command.OnExecute(() => { //string outputdir = output.Value() ?? "./StoredProcedures"; util.disable_console = nouseprogress.HasValue(); ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw); if (null == serverConnection) { return(2); } Server server = new Server(serverConnection); Database db = server.Databases[sqldb.Value()]; if (null == db) { throw new ScripterException("Invalid database"); } foreach (string t in table.Values) { Table db_table; if (!t.Contains(".")) { db_table = db.Tables[t]; } else { string a = t.Split('.')[0]; string b = t.Split('.')[1]; db_table = db.Tables[b, a]; } Template temp = new Template(); temp.Table = db_table; //Console.Write(temp.Execute()); StringCollection sc = temp.Execute(); foreach (string s in sc) { Console.Write(s); db.ExecuteNonQuery(s); } } return(0); }); }); commandLineApplication.HelpOption("-h | --help", inherited: true); try { int r = commandLineApplication.Execute(args); return(r); } catch (CommandParsingException ex) { Console.Error.Write("Invalid Command Line: "); Console.Error.WriteLine(ex.Message); Console.Error.WriteLine(commandLineApplication.GetHelpText()); return(22); } catch (Exception ex) { ConsoleColor color = Console.ForegroundColor; try{ Console.ForegroundColor = ConsoleColor.Red; Console.Error.WriteLine(ex.Message); Console.ForegroundColor = ConsoleColor.Gray; Console.Error.WriteLine(ex.ToString()); } finally { Console.ForegroundColor = color; } return(99); } }
/// <summary> /// Look at <see cref="IFormatter.Format(SchemaCollection, StringBuilder)"/> documentation. /// </summary> public override void Format(SchemaCollection schemas, StringBuilder output) { output.Insert(0, "Results from Schematron validation:\r\n"); output.Append("\r\n"); }