public void ScriptIsParsedCorrectly(string script, ScriptType scriptType, string scriptValue)
{
var result = new DbScript {XmlRoot = script};
Assert.That(result.ScriptType, Is.EqualTo(scriptType));
Assert.That(result.ScriptValue, Is.EqualTo(scriptValue));
}
public DbUpgradeHelper(DbScript script, string dbName,
DbConnection masterDbConnection, DbConnection curDbConnection)
{
_script = script;
_dbName = dbName;
_masterDbCn = masterDbConnection;
_curDbCn = curDbConnection;
}
public void MissingResourceThrows(string script)
{
var result = new DbScript {
XmlRoot = script
};
Assert.That(() => result.Load(), Throws.InstanceOf <MissingManifestResourceException>());
}
public void MissingFileThrows(string script, string fileName)
{
var result = new DbScript {
XmlRoot = script
};
Assert.That(() => result.Load(), Throws.InstanceOf <FileNotFoundException>());
}
public void LoadableScriptIsLoaded(string script, string content)
{
var result = new DbScript {
XmlRoot = script
}.Load();
Assert.That(result, Is.EqualTo(content));
}
public void EmptyBaseDirectoryUsesAppDomainBaseDirectory()
{
var result = new DbScript {
XmlRoot = DbScripts.RelativeFileSchema
};
Assert.That(result.GetBaseDirectory.Invoke(), Is.EqualTo(AppDomain.CurrentDomain.BaseDirectory));
}
public void CanAddScriptProperly()
{
var script = new DbScript();
script.AddScript("HELLO");
Assert.Equal(
TestSqlHelper.Normalize("HELLO"),
TestSqlHelper.Normalize(script.ToString()));
}
public void ScriptIsParsedCorrectly(string script, ScriptType scriptType, string scriptValue)
{
var result = new DbScript {
XmlRoot = script
};
Assert.That(result.ScriptType, Is.EqualTo(scriptType));
Assert.That(result.ScriptValue, Is.EqualTo(scriptValue));
}
public DbScript CreateDbScript(bool UseSquareBrackets)
{
DbScript s = new DbScript(DbCurrent, UseSquareBrackets);
s.DefaultBigText = Utility.Config.TipoTextoLongo;
s.DbScriptTypeList.Clear();
s.DbScriptTypeList.AddRange(Utility.Config.TypeList);
return(s);
}
public void InsertsGoBetweenScripts()
{
var script = new DbScript();
script.AddScript("FIRST;");
script.AddScript("SECOND;");
Assert.Equal(
TestSqlHelper.Normalize("FIRST;GO\nSECOND;"),
TestSqlHelper.Normalize(script.ToString()));
}
public string GetMetadataTable(DataTable tb, bool UseSquareBrackets)
{
if (tb == null)
{
return("");
}
DbScript s = CreateDbScript(UseSquareBrackets);
return(s.GetMetadataTable(DbColumns, tb, UseSquareBrackets) + ";");
}
public string[] GetTablesInOrder(bool UseSquareBrackets)
{
try
{
DbScript dbs = CreateDbScript(UseSquareBrackets);// new DbScript(DbCurrent);
return(dbs.GetTablesInOrder(1000));
}
catch (Exception ex)
{ Msg.Warning(ex.Message); }
return(new string[] { });
}
public async Task Upgrade()
{
var masterCn = new DbConnection(DbDatabaseType.Postgres,
"Host=127.0.0.1;Username=test;Password=test;Database=postgres");
var curCn = new DbConnection(DbDatabaseType.Postgres,
"Host=127.0.0.1;Username=test;Password=test;Database=test");
var script = new DbScript(GetScript());
var dbName = "test";
var upgrade = new DbUpgradeHelper(script, dbName, masterCn, curCn);
await upgrade.CheckAndUpgrade();
}
public void CanAddSqlUpdateProperly()
{
var script = new DbScript();
script.Add(new SqlUpdate(new TestRow
{
TestId = 1,
Description = "Test"
}));
Assert.Equal(TestSqlHelper.Normalize(
"DECLARE @p1 NVARCHAR(4) = 'Test';" +
"DECLARE @p2 BIGINT = 1;" +
"UPDATE Tests SET Description = @p1 WHERE TestId = @p2"),
TestSqlHelper.Normalize(script.ToString()));
}
public void CanAddSqlInsertProperly()
{
var script = new DbScript();
script.Add(new SqlInsert(new TestRow
{
TrackAssignments = true,
TestId = 1,
Description = "Test"
}));
Assert.Equal(TestSqlHelper.Normalize(
"DECLARE @p1 INT = 1;" +
"DECLARE @p2 NVARCHAR(4) = 'Test';" +
"INSERT INTO Tests (TestId, Description) VALUES (@p1, @p2)"),
TestSqlHelper.Normalize(script.ToString()));
}
public void MissingFileThrows(string script, string fileName)
{
var result = new DbScript {XmlRoot = script};
Assert.That(() => result.Load(), Throws.InstanceOf<FileNotFoundException>());
}
public void EmptyBaseDirectoryUsesAppDomainBaseDirectory()
{
var result = new DbScript {XmlRoot = DbScripts.RelativeFileSchema};
Assert.That(result.GetBaseDirectory.Invoke(), Is.EqualTo(AppDomain.CurrentDomain.BaseDirectory));
}
public void Test()
{
{
var content = @"";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.NO_BAGS);
}
{
var content = @"abcxx";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.UNKNOWN_LINE);
}
{
var content = @"--|END|";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.LOST_START_TAG);
}
{
var content = @"--|STA|VERSION|";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.INVALID_START_BAG_TAG);
}
{
var content = @"--|STA|VERSION|d,d,d";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.INVALID_START_BAG_TAG);
}
{
var content = @"--|STA|VERSION|a,b";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.INVALID_FROM_VERSION);
}
{
var content = @"--|STA|VERSION|1,b";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.INVALID_TO_VERSION);
}
{
var content = @"--|STA|VERSION|1,4";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.LOST_END_TAG);
}
{
var content = @"--|STA|VERSION|5,4";
Assert.AreEqual(Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
}).ErrorNo, DbInvalidScriptException.FROM_VERSION_GREATER);
}
{
var content = @"--|STA|VERSION|1,4
--|END|
--dd
";
var ex = Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
});
Assert.AreEqual(ex.ErrorNo, DbInvalidScriptException.UNKNOWN_LINE);
}
{
var content = @"--|STA|VERSION|1,4
--|END|
--//command
";
var dbScript = new DbScript(content);
}
{
var content = @"
--|STA|VERSION|1,4
--|END|
--|STA|VERSION|1,4
--|END|
";
var ex = Assert.Catch <DbInvalidScriptException>(() => {
var dbScript = new DbScript(content);
});
Assert.AreEqual(ex.ErrorNo, DbInvalidScriptException.FROM_VERSION_NOT_CONTINUE);
}
{
var content = @"
--|STA|VERSION|1,4
--|END|
--|STA|VERSION|4,6
--|END|
";
var dbScript = new DbScript(content);
Assert.AreEqual(dbScript.Bags.Count, 2);
}
{
var content = @"
--|STA|VERSION|1,4
aa
bb
cc
--|END|
--|STA|VERSION|4,6
aa
bb
cc
--|END|
";
var dbScript = new DbScript(content);
Assert.AreEqual(dbScript.Bags.Count, 2);
Console.WriteLine(JsonConvert.SerializeObject(dbScript.Bags));
}
{
var content = @"
--|STA|CONFIG|
--|STA|VERSION-TABLE|
--|NAME|db_version
--|CREATE|create table {table}(id integer not null constraint {table}_pk primary key, version integer, create_time timestamp, update_time timestamp)
--|ADD|INSERT INTO {table}(id, version, create_time, update_time) VALUES(1, {version}, NOW(), NOW())
--|CHECK|SELECT version FROM {table} WHERE id = 1
--|UPDATE|UPDATE {table} SET version = {version}, update_time = NOW() WHERE id = 1
--|END|
--|END|
--|STA|VERSION|1,4
aa
bb
cc
--|END|
--|STA|VERSION|4,6
aa
bb
cc
--|END|
";
var dbScript = new DbScript(content);
Assert.AreEqual(dbScript.Bags.Count, 2);
Console.WriteLine(JsonConvert.SerializeObject(dbScript));
}
}
/// <summary>
/// Создать скрипты создания и обновления бд.
/// </summary>
public DbScriptSet(DbScript createScript, DbScript[] updateScripts)
{
CreateScript = createScript;
UpdateScripts = updateScripts;
}
protected void cGenButton_Click(object sender, System.EventArgs e)
{
cMsgLabel.Text = string.Empty;
if (!(cSrcSystemId.Items.Count > 0 && cTarSystemId.Items.Count > 0))
{
PreMsgPopup("Please make sure the table \"Systems\" in both source and target have valid database entries and try again.");
}
else if (cSrcSystemId.SelectedValue != cTarSystemId.SelectedValue)
{
PreMsgPopup("Please make sure the source and target \"Database\" are the same and try again.");
}
else if (cExecScript.Checked && cSrcDataTierId.SelectedValue == cTarDataTierId.SelectedValue)
{
PreMsgPopup("Source and Target data tier can be the same if and only if script is not being executed, please try again.");
}
else
{
string ss;
DataTable dt = (DataTable)Session[KEY_dtDataTier];
string SrcDbProviderCd = dt.Rows[cSrcDataTierId.SelectedIndex]["DbProviderCd"].ToString();
string SrcPortBinPath = dt.Rows[cSrcDataTierId.SelectedIndex]["PortBinPath"].ToString();
string SrcScriptPath = dt.Rows[cSrcDataTierId.SelectedIndex]["ScriptPath"].ToString();
string TarDbProviderCd = dt.Rows[cTarDataTierId.SelectedIndex]["DbProviderCd"].ToString();
string TarPortBinPath = dt.Rows[cTarDataTierId.SelectedIndex]["PortBinPath"].ToString();
string TarPortCmdName;
if (TarDbProviderCd == "S")
{
TarPortCmdName = TarPortBinPath + "isql.exe";
}
else
{
TarPortCmdName = "osql.exe";
}
string TarScriptPath = dt.Rows[cTarDataTierId.SelectedIndex]["ScriptPath"].ToString();
string TarUdFunctionDb = dt.Rows[cTarDataTierId.SelectedIndex]["DesDatabase"].ToString();
DbScript ds = new DbScript(cExemptText.Text, false);
DbPorting pt = new DbPorting();
if (cClearDb.Checked)
{
ss = ds.ScriptClearDb(SrcDbProviderCd, TarDbProviderCd, cTable.Checked, cBcpIn.Checked, cIndex.Checked, cView.Checked, cSp.Checked, base.CSrc, base.CTar);
Robot.WriteToFile("M", SrcScriptPath + "OClearDb.sql", ss);
if (TarDbProviderCd == "S")
{
ss = pt.SqlToSybase(Int16.Parse(cEntityId.SelectedValue), TarUdFunctionDb, ss, LcAppConnString, LcAppPw);
}
Robot.WriteToFile("M", TarScriptPath + "NClearDb.sql", ss);
// ds.ExecScript need to be changed to Utils.WinProc in order for SQL Server on a separate server to work:
if (cExecScript.Checked)
{
ds.ExecScript(TarDbProviderCd, "Clear Database", TarPortCmdName, TarScriptPath + "NClearDb.sql", base.CSrc, base.CTar, LcAppConnString, LcAppPw);
}
sbWarnMsg.Append(pt.sbWarning.ToString()); sbWarnMsg.Append(ds.sbWarning.ToString());
}
if (cTable.Checked)
{
ss = ds.ScriptCreateTables(SrcDbProviderCd, SrcDbProviderCd, true, base.CSrc, base.CTar);
Robot.WriteToFile("M", SrcScriptPath + "OTable.sql", ss);
if (TarDbProviderCd == "S")
{
ss = ds.ScriptCreateTables(SrcDbProviderCd, TarDbProviderCd, true, base.CSrc, base.CTar);
ss = pt.SqlToSybase(Int16.Parse(cEntityId.SelectedValue), TarUdFunctionDb, ss, LcAppConnString, LcAppPw);
}
Robot.WriteToFile("M", TarScriptPath + "NTable.sql", ss);
// ds.ExecScript need to be changed to Utils.WinProc in order for SQL Server on a separate server to work:
if (cExecScript.Checked)
{
ds.ExecScript(TarDbProviderCd, "Script Tables", TarPortCmdName, TarScriptPath + "NTable.sql", base.CSrc, base.CTar, LcAppConnString, LcAppPw);
}
sbWarnMsg.Append(pt.sbWarning.ToString()); sbWarnMsg.Append(ds.sbWarning.ToString());
}
if (cBcpOut.Checked)
{
ss = ds.GenerateBCPFiles("M", TarDbProviderCd, SrcDbProviderCd, SrcPortBinPath, true, TarScriptPath + @"Data\", "~@~", false, base.CSrc, base.CTar);
Robot.WriteToFile("M", TarScriptPath + "BcpOut.bat", ss);
// ds.ExecScript need to be changed to Utils.WinProc in order for SQL Server on a separate server to work:
if (cExecScript.Checked)
{
ds.ExecScript(string.Empty, "Bcp Out", TarScriptPath + "BcpOut.bat", string.Empty, base.CSrc, base.CTar, LcAppConnString, LcAppPw);
}
sbWarnMsg.Append(ds.sbWarning.ToString());
}
if (cBcpIn.Checked)
{
ss = ds.ScriptTruncData(SrcDbProviderCd, true, base.CSrc, base.CTar);
if (TarDbProviderCd == "S")
{
ss = pt.SqlToSybase(Int16.Parse(cEntityId.SelectedValue), TarUdFunctionDb, ss, LcAppConnString, LcAppPw);
}
Robot.WriteToFile("M", TarScriptPath + "BcpIn.sql", ss);
ss = ds.GenerateBCPFiles("M", TarDbProviderCd, TarDbProviderCd, TarPortBinPath, false, TarScriptPath + @"Data\", "~@~", false, base.CSrc, base.CTar);
Robot.WriteToFile("M", TarScriptPath + "BcpIn.bat", ss);
// ds.ExecScript need to be changed to Utils.WinProc in order for SQL Server on a separate server to work:
if (cExecScript.Checked)
{
ds.ExecScript(TarDbProviderCd, "Truncate Data", TarPortCmdName, TarScriptPath + "BcpIn.sql", base.CSrc, base.CTar, LcAppConnString, LcAppPw);
ds.ExecScript(TarDbProviderCd, "Bcp In", TarScriptPath + "BcpIn.bat", string.Empty, base.CSrc, base.CTar, LcAppConnString, LcAppPw);
}
sbWarnMsg.Append(pt.sbWarning.ToString()); sbWarnMsg.Append(ds.sbWarning.ToString());
}
if (cIndex.Checked)
{
ss = ds.ScriptIndexFK(SrcDbProviderCd, SrcDbProviderCd, true, base.CSrc, base.CTar);
Robot.WriteToFile("M", SrcScriptPath + "OIndex.sql", ss);
if (TarDbProviderCd == "S")
{
ss = ds.ScriptIndexFK(SrcDbProviderCd, TarDbProviderCd, true, base.CSrc, base.CTar);
ss = pt.SqlToSybase(Int16.Parse(cEntityId.SelectedValue), TarUdFunctionDb, ss, LcAppConnString, LcAppPw);
}
Robot.WriteToFile("M", TarScriptPath + "NIndex.sql", ss);
// ds.ExecScript need to be changed to Utils.WinProc in order for SQL Server on a separate server to work:
if (cExecScript.Checked)
{
ds.ExecScript(TarDbProviderCd, "Script Indexes", TarPortCmdName, TarScriptPath + "NIndex.sql", base.CSrc, base.CTar, LcAppConnString, LcAppPw);
}
sbWarnMsg.Append(pt.sbWarning.ToString()); sbWarnMsg.Append(ds.sbWarning.ToString());
}
if (cView.Checked)
{
ss = ds.ScriptView(SrcDbProviderCd, SrcDbProviderCd, true, base.CSrc, base.CTar);
Robot.WriteToFile("M", SrcScriptPath + "OView.sql", ss);
if (TarDbProviderCd == "S")
{
ss = ds.ScriptView(SrcDbProviderCd, TarDbProviderCd, true, base.CSrc, base.CTar);
ss = pt.SqlToSybase(Int16.Parse(cEntityId.SelectedValue), TarUdFunctionDb, ss, LcAppConnString, LcAppPw);
}
Robot.WriteToFile("M", TarScriptPath + "NView.sql", ss);
// ds.ExecScript need to be changed to Utils.WinProc in order for SQL Server on a separate server to work:
if (cExecScript.Checked)
{
ds.ExecScript(TarDbProviderCd, "Script View", TarPortCmdName, TarScriptPath + "NView.sql", base.CSrc, base.CTar, LcAppConnString, LcAppPw);
}
sbWarnMsg.Append(pt.sbWarning.ToString()); sbWarnMsg.Append(ds.sbWarning.ToString());
}
if (cSp.Checked)
{
ss = ds.ScriptSProcedures(SrcDbProviderCd, SrcDbProviderCd, true, base.CSrc, base.CTar);
Robot.WriteToFile("M", SrcScriptPath + "OSp.sql", ss);
if (TarDbProviderCd == "S")
{
ss = ds.ScriptSProcedures(SrcDbProviderCd, TarDbProviderCd, true, base.CSrc, base.CTar);
ss = pt.SqlToSybase(Int16.Parse(cEntityId.SelectedValue), TarUdFunctionDb, ss, LcAppConnString, LcAppPw);
}
if (cEncryptSp.Checked)
{
ss = ds.EncryptSProcedures(SrcDbProviderCd, TarDbProviderCd, ss, true, base.CSrc, base.CTar);
}
Robot.WriteToFile("M", TarScriptPath + "NSp.sql", ss);
// ds.ExecScript need to be changed to Utils.WinProc in order for SQL Server on a separate server to work:
if (cExecScript.Checked)
{
ds.ExecScript(TarDbProviderCd, "Script Stored Procedures", TarPortCmdName, TarScriptPath + "NSp.sql", base.CSrc, base.CTar, LcAppConnString, LcAppPw);
}
sbWarnMsg.Append(pt.sbWarning.ToString()); sbWarnMsg.Append(ds.sbWarning.ToString());
}
if (SrcScriptPath != TarScriptPath)
{
cMsgLabel.Text = "Please check directories " + TarScriptPath + " and " + TarScriptPath + " to see if all the scripts have been generated successfully.";
}
else
{
cMsgLabel.Text = "Please check directory " + TarScriptPath + " to see if all the scripts have been generated successfully.";
}
if (sbWarnMsg.ToString() != string.Empty)
{
PreMsgPopup(sbWarnMsg.ToString());
}
}
}
public void SalveScriptInsert(DataTable tb, string FileName, bool UseInsertIdentity, bool UseSquareBrackets)
{
DbScript s = CreateDbScript(UseSquareBrackets);//;new DbScript(DbCurrent);
s.SalveScriptInsert(tb, FileName, UseInsertIdentity, UseSquareBrackets);
}
public ScriptTokenExtracter(DbScript script)
{
this.Script = script;
}
public void ExecBackup(string FileName, List <string> NaoCopiarTabelas, bool UseInsertIdentity, bool UseSquareBrackets)
{
try
{
btnGerarBackup.Enabled = false;
btnFechar.Enabled = false;
grpOpcoes.Enabled = false;
if (System.IO.File.Exists(FileName))
{
System.IO.File.Delete(FileName);
}
DbScript DbScript = new DbScript(Cnn, UseSquareBrackets);
DbScript.OnScriptWriting += new lib.Database.DbScript.ScriptWriting_Handle(DbScript_ScriptWriting);
DbScript.DbScriptTypeList.Clear();
DbScript.DbScriptTypeList.AddRange(DbScriptTypeList.CreateItems());
DbScript.DbScriptTypeList.Add(new DbScriptType(typeof(UInt64), "BOOLEAN", lib.Database.Drivers.enmFieldType.Bool));
string[] TablesInOrder = DbScript.GetTablesInOrder(100);
if (System.IO.File.Exists(ArqScript))
{
System.IO.File.Copy(ArqScript, FileName);
}
TextFile tf = new TextFile();
tf.Open(enmOpenMode.Writing, FileName, Encoding.UTF8);
tf.WriteLine("");
pbBackup.Maximum = TablesInOrder.Length * 2;
pbBackup.Value = 0;
estimatedTime.Start();
// Script de Limpeza
for (int i = (TablesInOrder.Length - 1); i >= 0; i--)
{
if ((i % 100) == 0)
{
this.Refresh();
}
pbBackup.Value += 1;
if (NaoCopiarTabelas.IndexOf(TablesInOrder[i].ToUpper()) == -1)
{
DbScript.SalveScriptDelete(TablesInOrder[i], tf);
}
}
tf.WriteLine(DbScript.COMMIT_COMMAND);
// Desabilita chaves
//for (int i = (TablesInOrder.Length - 1); i >= 0; i--)
//{ tf.WriteLine(Cnn.GetDisableKey(TablesInOrder[i])); }
// Script de Inserção
for (int i = 0; i < TablesInOrder.Length; i++)
{
this.Percent = (i + 1) * 100 / TablesInOrder.Length;
if ((i % 100) == 0)
{
this.Refresh();
}
pbBackup.Value += 1;
if (NaoCopiarTabelas.IndexOf(TablesInOrder[i].ToUpper()) == -1)
{
DbScript.SalveScriptInsert(TablesInOrder[i], tf, UseInsertIdentity, UseSquareBrackets);
}
}
// Habilita chaves
//for (int i = (TablesInOrder.Length - 1); i >= 0; i--)
//{ tf.WriteLine(Cnn.GetEnableKey(TablesInOrder[i])); }
lblTempoEstimado.Text = "";
tf.Close();
}
finally
{
btnGerarBackup.Enabled = true;
btnFechar.Enabled = true;
grpOpcoes.Enabled = true;
}
}
public void MissingResourceThrows(string script)
{
var result = new DbScript {XmlRoot = script};
Assert.That(() => result.Load(), Throws.InstanceOf<MissingManifestResourceException>());
}
public void LoadableScriptIsLoaded(string script, string content)
{
var result = new DbScript {XmlRoot = script}.Load();
Assert.That(result, Is.EqualTo(content));
}
public void SalveScriptUpdate(DataTable tb, string FileName, string[] ColumnsConditions, bool UseSquareBrackets)
{
DbScript s = CreateDbScript(UseSquareBrackets);//new DbScript(DbCurrent);
s.SalveScriptUpdate(tb, FileName, ColumnsConditions);
}
