protected virtual void ScriptTableForeignKeys(ITable table, IScriptWriter writer)
{
Throw.If(table, "table").IsNull();
Throw.If(writer, "writer").IsNull();
string tableName = DbObjectName.CreateDbObjectName(table);
string msg = string.Format("Scripting {0} foreign keys", tableName);
messageManager.OnScriptMessage(msg);
SqlScript script = new SqlScript();
foreach (IForeignKey fk in table.ForeignKeys)
{
// only script fks on fk table
if (fk.PrimaryTable == table)
{
continue;
}
msg = string.Format("Scripting foreign key {0}", fk.Name);
messageManager.OnScriptMessage(msg);
script += scriptBuilder.Create(fk);
}
writer.WriteForeignKeyScript(tableName, script.ToScript());
}
public MarkdownRecorder(Uri redirectHost, string targetFile, IScriptWriter scriptWriter, IServiceInteroperation service, IDictionary <int, IInteraction>?interactions = null)
{
_redirectHost = redirectHost;
_service = service;
_scriptWriter = scriptWriter;
_allInteractions = interactions ?? new Dictionary <int, IInteraction> {
};
_targetFile = targetFile;
}
/// <summary>
/// Exports the T-SQL scripts to disk.
/// </summary>
public virtual void ExportUpgradeScripts(IExportParams exportParameters, IScriptWriter scriptWriter)
{
Throw.If(exportParameters).IsNull();
Throw.If(scriptWriter).IsNull();
exportParams = exportParameters;
writer = scriptWriter;
CalculateScriptObjectCount();
ScriptTableDataDifferences();
}
/// <summary>
/// Exports the T-SQL scripts to disk.
/// </summary>
public virtual void ExportScripts(IExportParams exportParameters, IScriptWriter scriptWriter)
{
Throw.If(exportParameters).IsNull();
Throw.If(scriptWriter).IsNull();
exportParams = exportParameters;
writer = scriptWriter;
CalculateScriptObjectCount();
ScriptSprocs();
ScriptViews();
ScriptTables();
ScriptTableData();
}
protected void ScriptView(IView view, IScriptWriter writer)
{
Throw.If(view, "view").IsNull();
Throw.If(writer, "writer").IsNull();
string name = DbObjectName.CreateDbObjectName(view);
string msg = string.Format("Scripting view {0}", name);
messageManager.OnScriptMessage(msg);
SqlScript script = scriptBuilder.Create(view);
writer.WriteViewScript(name, script.ToScript());
}
/// <summary>
/// Exports the T-SQL scripts to disk.
/// </summary>
public virtual void ExportScripts(IExportParams exportParameters, IScriptWriter scriptWriter)
{
Throw.If(exportParameters).IsNull();
Throw.If(scriptWriter).IsNull();
exportParams = exportParameters;
writer = scriptWriter;
CalculateScriptObjectCount();
ScriptSprocs();
ScriptViews();
ScriptTables();
ScriptTableData();
}
protected void ScriptSproc(IProcedure sproc, IScriptWriter writer)
{
Throw.If(sproc, "sproc").IsNull();
Throw.If(writer, "writer").IsNull();
string name = DbObjectName.CreateDbObjectName(sproc);
string msg = string.Format("Scripting stored procedure {0}", name);
messageManager.OnScriptMessage(msg);
SqlScript script = scriptBuilder.Create(sproc);
writer.WriteSprocScript(name, script.ToScript());
}
protected void ScriptTableSchema(ITable table, IScriptWriter writer)
{
Throw.If(table, "table").IsNull();
Throw.If(writer, "writer").IsNull();
string tableName = DbObjectName.CreateDbObjectName(table);
string msg = string.Format("Scripting {0} table schema", tableName);
messageManager.OnScriptMessage(msg);
SqlScript script = scriptBuilder.Create(table);
writer.WriteTableScript(tableName, script.ToScript());
}
protected void ScriptTableIndexes(ITable table, IScriptWriter writer)
{
Throw.If(table, "table").IsNull();
Throw.If(writer, "writer").IsNull();
string tableName = DbObjectName.CreateDbObjectName(table);
string msg = string.Format("Scripting {0} indexes", tableName);
messageManager.OnScriptMessage(msg);
SqlScript script = new SqlScript();
foreach (IIndex index in table.Indexes)
{
msg = string.Format("Scripting index {0}", index.Name);
messageManager.OnScriptMessage(msg);
script += scriptBuilder.Create(index);
}
writer.WriteIndexScript(tableName, script.ToScript());
}
/// <summary>
/// Create a Script Writer that writes to a Stream using a particular format provider
/// </summary>
/// <param name="stream"></param>
/// <param name="scriptWriter"></param>
public ScriptWriter(Stream stream, IScriptWriter scriptWriter)
{
if (stream == null)
throw new Exception("ScriptWriter does not like null Streams");
Writer = new StreamWriter(stream);
this.CopySettings(scriptWriter);
}
public void Write(IScriptWriter writer)
{
writer.WriteLine($"{QueryParameterHelperFunctions.TryAppendObjectFuncName}({InitUrlParamsScriptExtensions.UrlParamsVarName}, {_paramName});");
}
/// <summary>
/// Create a Script Writer that writes to a specific TextWriter based on another ScriptWriters settings
/// </summary>
/// <param name="writer"></param>
/// <param name="scriptWriter"></param>
public ScriptWriter(TextWriter writer, IScriptWriter scriptWriter)
{
if (writer == null)
throw new Exception("ScriptWriter does not like null TextWriters");
Writer = writer;
this.CopySettings(scriptWriter);
}
public void Write(IScriptWriter writer)
{
writer.WriteLine($"const {UrlParamsVarName} = new URLSearchParams();");
}
public void Write(IScriptWriter writer)
{
writer.WriteLine($"{QueryParameterHelperFunctions.TryAppendKeyValueFuncName}({InitUrlParamsScriptExtensions.UrlParamsVarName}, \"{_key}\", \"{_value}\");");
}
protected virtual void ScriptTableDataDifferences(ITable sourceTable, ITable targetTable, IScriptWriter writer)
{
Throw.If(sourceTable, "sourceTable").IsNull();
Throw.If(targetTable, "targetTable").IsNull();
Throw.If(writer, "writer").IsNull();
string name = DbObjectName.CreateDbObjectName(sourceTable);
messageManager.OnScriptMessage(
string.Format("Starting table data difference scripting on table {0}.",
name));
script = new SqlScript();
DifferentialDataMigrator migrator = new DifferentialDataMigrator();
script = migrator.ScriptDataDifferences(sourceTable, targetTable, script);
writer.WriteTableDataScript(name, script.ToScript());
messageManager.OnScriptMessage(
string.Format("Finished table data difference scripting on table {0}.",
name));
}
/// <summary>
/// Scripts a database to disk for use in creating a brand new db. This
/// method scripts the entire object, i.e. no differntial or comparisons
/// are done.
/// </summary>
/// <param name="connectionInfo">The db connection parameters.</param>
/// <param name="scriptingOptions">The export scripting settings.</param>
public virtual void Script(IConnectionSettings connectionInfo, IScriptingOptions scriptingOptions)
{
Throw.If(connectionInfo, "connectionInfo").IsNull();
Throw.If(scriptingOptions, "scriptingOptions").IsNull();
messageManager.OnScriptMessage("Starting database scripting.");
IDatabase db = DatabaseConnectionFactory.CreateDbConnection(connectionInfo);
List <ITable> tablesToScript = new List <ITable>();
foreach (DbObjectName tableName in scriptingOptions.TablesToScript)
{
ITable table = db.Tables[tableName.ShortName];
if (table == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the table {0} in database {1} on server.",
tableName, connectionInfo.Database));
}
tablesToScript.Add(table);
}
IScriptWriter writer = CreateScriptWriter(scriptingOptions, connectionInfo);
int totalObjects = CalculateScriptObjectCount(scriptingOptions);
int exportCount = 0;
if (scriptingOptions.ScriptSchema)
{
foreach (ITable table in tablesToScript)
{
ScriptTableSchema(table, writer);
OnProgressEvent(++exportCount, totalObjects);
}
}
if (scriptingOptions.ScriptIndexes)
{
foreach (ITable table in tablesToScript)
{
ScriptTableIndexes(table, writer);
OnProgressEvent(++exportCount, totalObjects);
}
}
if (scriptingOptions.ScriptData || scriptingOptions.ScriptDataAsXml)
{
IScriptWriter dataWriter = writer;
IDataMigrator dataMigrator = new DataMigrator();
if (scriptingOptions.ScriptDataAsXml)
{
dataMigrator = new XmlDataMigrator();
dataWriter = new XmlDataWriter(scriptingOptions.ExportDirectory, messageManager);
}
foreach (ITable table in tablesToScript)
{
ScriptTableData(dataMigrator, table, dataWriter);
OnProgressEvent(++exportCount, totalObjects);
}
}
if (scriptingOptions.ScriptForeignKeys)
{
foreach (ITable table in tablesToScript)
{
ScriptTableForeignKeys(table, writer);
OnProgressEvent(++exportCount, totalObjects);
}
}
foreach (DbObjectName sprocName in scriptingOptions.SprocsToScript)
{
IProcedure sproc = db.Procedures[sprocName.ShortName];
if (sproc == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the procedure {0} in database {1} on server.",
sprocName, connectionInfo.Database));
}
ScriptSproc(sproc, writer);
OnProgressEvent(++exportCount, totalObjects);
}
foreach (DbObjectName viewName in scriptingOptions.ViewsToScript)
{
IView view = db.Views[viewName.ShortName];
if (view == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the view {0} in database {1} on server.",
viewName, connectionInfo.Database));
}
ScriptView(view, writer);
OnProgressEvent(++exportCount, totalObjects);
}
messageManager.OnScriptMessage("Finished database scripting.");
}
public MarkdownRecorder(Uri redirectHost, string targetFile, IScriptWriter scriptWriter) : this(redirectHost, targetFile, scriptWriter, new ServiceInteropViaSystemNetHttp())
{
}
public void Write(IScriptWriter writer)
{
string text = new QueryParameterHelperFunctions(_needsObjHelperFunc).TransformText();
writer.Write(text);
}
/// <summary>
/// Render the content to a script writer
/// </summary>
/// <param name="writer"></param>
public void Render(IScriptWriter writer)
{
Render(writer, 0);
}
/// <summary>
/// Generates table data inserts and updates to sync two tables in
/// different databases.
/// </summary>
/// <param name="migrator">The data migrator instance.</param>
/// <param name="source">The source table to script all data from.</param>
/// <param name="writer">The script writer strategy.</param>
protected virtual void ScriptTableData(IDataMigrator migrator, ITable source, IScriptWriter writer)
{
Throw.If(source, "source").IsNull();
Throw.If(writer, "writer").IsNull();
string name = DbObjectName.CreateDbObjectName(source);
messageManager.OnScriptMessage(
string.Format("Starting table data scripting on table {0}.",
name));
script = new SqlScript();
script = migrator.ScriptAllData(source, script);
writer.WriteTableDataScript(name, script.ToScript());
messageManager.OnScriptMessage(
string.Format("Finished table data scripting on table {0}.",
name));
}
/// <summary>
/// Render the content to a script writer indenting the content as specified
/// </summary>
/// <param name="writer">the script writer to render to</param>
/// <param name="indents">how many indents to add. These indents are added to the item own Indent level</param>
public void Render(IScriptWriter writer, int indents)
{
try
{
int totalIndents = 0;
if (this.Layout == ScriptLayout.Block || this.Layout == ScriptLayout.InlineBlock) // don't indent if not multiline
totalIndents = Indents + indents;
try
{
writer.BeginIndent(totalIndents);
OnRender(new RenderingEventArgs(writer));
}
finally
{
writer.EndIndent(totalIndents);
}
}
finally
{
writer.Flush();
}
}
/// <summary>
/// Copies settings from the provided writer so that this writer will write in the same way
/// </summary>
/// <param name="scriptWriter"></param>
protected void CopySettings(IScriptWriter scriptWriter)
{
this._FormatProvider = scriptWriter.FormatProvider;
if (scriptWriter is ScriptWriter)
{
ScriptWriter sw = (ScriptWriter)scriptWriter;
this.IncludeIndentation = sw.IncludeIndentation;
this.IndentText = sw.IndentText;
this.NewLine = sw.NewLine;
this.Compress = sw.Compress;
}
}
/// <summary>
/// Returns an alter script to upgrade a database from target version
/// to source version.
/// </summary>
/// <remarks>
/// <para>This method is not thread safe.</para>
/// <para>Operations are scripted in the following order:
/// <list type="number">
/// <item>create new tables</item>
/// <item>add new columns to existing tables</item>
/// <item>drop removed foreign keys</item>
/// <item>drop removed columns</item>
/// <item>drop removed tables</item>
/// <item>alter any changed columns (like if you added a new default to a column)</item>
/// <item>add new foreign keys</item>
/// <item>add new indexes</item>
/// <item>functions</item>
/// <item>views</item>
/// <item>sprocs</item>
/// </list>
/// </para>
/// </remarks>
/// <param name="connectionInfoSourceDb">The newer, source db.</param>
/// <param name="connectionInfoTargetDb">The older, target db to upgrade.</param>
/// <param name="scriptingOptions">The export scripting settings.</param>
public virtual void ScriptDifferences(IConnectionSettings connectionInfoSourceDb,
IConnectionSettings connectionInfoTargetDb,
IScriptingOptions scriptingOptions)
{
Throw.If(connectionInfoSourceDb, "connectionInfoSourceDb").IsNull();
Throw.If(connectionInfoTargetDb, "connectionInfoTargetDb").IsNull();
Throw.If(scriptingOptions, "scriptingOptions").IsNull();
messageManager.OnScriptMessage("Starting scripting database differences.");
IDatabase srcDb = DatabaseConnectionFactory.CreateDbConnection(connectionInfoSourceDb);
IDatabase targetDb = DatabaseConnectionFactory.CreateDbConnection(connectionInfoTargetDb);
IScriptWriter writer = CreateScriptWriter(scriptingOptions, connectionInfoTargetDb);
int totalObjects = CalculateScriptObjectCount(scriptingOptions);
int exportCount = 0;
foreach (DbObjectName tableName in scriptingOptions.TablesToScript)
{
// TODO: Need to split this up like the Script() method to order things correctly?
ITable table = srcDb.Tables[tableName.ShortName];
if (table == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the source table {0} in database {1} on server.",
tableName, connectionInfoSourceDb.Database));
}
ITable targetTable = targetDb.Tables[tableName.ShortName];
if (targetTable == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the target table {0} in database {1} on server.",
tableName, connectionInfoTargetDb.Database));
}
if (scriptingOptions.ScriptData)
{
ScriptTableDataDifferences(table, targetTable, writer);
OnProgressEvent(++exportCount, totalObjects);
}
// TODO: constraints, Schema, Indexes?
}
messageManager.OnScriptMessage("Finished scripting database differences.");
// sourceDB = source;
// targetDB = target;
//
// messageManager.OnScriptMessage("Starting database differencing.");
//
// script = new SqlScript();
//
// ScriptNewTablesAndColumns();
// ScriptRemovedForeignKeys();
// ScriptRemovedIndexes();
// ScriptRemovedTablesAndColumns();
// ScriptAlteredColumns();
// ScriptNewForeignKeys();
// ScriptNewIndexes();
//
// ScriptNewAndAlteredSprocs();
// ScriptRemovedSprocs();
//
// messageManager.OnScriptMessage("Finished database differencing.");
}
public async Task <bool> ExecuteCode(string code, CancellationToken token = default, IScriptWriter sender = null)
{
string result = null; object returnedValue = null; bool isError = false; bool isCancelled = false;
ScriptExecuted?.Invoke(this, new ScriptRequest {
Script = code, Writer = sender
});
try
{
if (!await InitScript(token))
{
return(false);
}
using (await _scriptStateLock.LockAsync(token))
{
if (_executionContext != null)
{
await _executionContext(async() => _scriptState = await _scriptState.ContinueWithAsync(code, cancellationToken: token));
}
else
{
_scriptState = await _scriptState.ContinueWithAsync(code, cancellationToken : token);
}
returnedValue = _scriptState.ReturnValue;
result = _scriptState.ReturnValue?.ToString();
if (_scriptState.ReturnValue != null && _scriptState.ReturnValue.GetType() == typeof(string))
{
result = $"\"{result}\"";
}
}
}
catch (CompilationErrorException e)
{
result = e.Message;
isError = true;
}
catch (OperationCanceledException)
{
result = string.Empty;
isCancelled = true;
}
if (result != null)
{
var scriptResult = new ScriptResult {
Result = result, ReturnedValue = returnedValue, IsError = isError, IsCancelled = isCancelled
};
Results.Add(scriptResult);
ScriptResultReceived?.Invoke(this, scriptResult);
}
return(!isError && !isCancelled);
}
public SqlGeneratorSimple(ILogger logger, IScriptWriter writer)
{
Logger = logger;
Writer = writer;
}
/// <summary>
/// An example on creating a component
/// </summary>
/// <param name="writer"></param>
/// <returns></returns>
protected ExtJsComponent ComponentExample(IScriptWriter writer)
{
string componentName = "test";
string baseComponent = "Ext.Window";
return
ExtJs.Component(
componentName, baseComponent, // define the component name and the one it derives from
Js.Object(
// define properties of the component
Js.Property("panel", "{}"),
// initComponent function. is the most commonly overriden one
Js.Property("initComponent",
Js.Function(ScriptLayout.InlineBlock,
Js.Block(
// initialise properties in the initComponent function
Js.Statement("this.panel = ", Js.New("Ext.Panel")),
// apply changes to the components settings
ExtJs.Apply(
Js.Object(
Js.Property("title",Js.Q("Test"))
)
),
// always call the base function
ExtJs.BaseApply(this.ComponentName, "initComponent")
)
)
)
)
);
}
/// <summary>
/// passed the writer used for rendering
/// </summary>
/// <param name="writer"></param>
public RenderingEventArgs(IScriptWriter writer)
{
Writer = writer;
}
