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");
}
