/// <summary>
/// Example of how to create migration scripts from an old legacy databse
/// </summary>
/// <param name="newServerDbName"></param>
/// <param name="doCreate"></param>
/// <returns></returns>
public static List <Sql> GenerateDataMigrationScripts(string newServerDbName, bool doCreate)
{
// generate models off old database with underscorized naming (connstr = guru)
// newServerDbName = thatcher.rinnai
var result = new List <Sql>();
var models = ActiveRecordDatabaseGenerator.GetModelInstances();
foreach (ActiveRecord model in models)
{
var tableName = model.GetTableName().SqlizeName().value;
if (!tableName.Contains("rn_"))
{
continue;
}
if (tableName.Contains("homebanner"))
{
continue;
}
if (tableName.Contains("filetracking"))
{
continue;
}
if (tableName.Contains("rn_tracking"))
{
continue;
}
if (tableName.Contains("rn_emailtemplate"))
{
continue;
}
if (tableName.Contains("rn_categorybanner"))
{
continue;
}
if (tableName.Contains("rn_pagecontent"))
{
continue;
}
if (tableName.Contains("rn_promo"))
{
continue;
}
if (tableName.Contains("rn_pagesection"))
{
continue;
}
if (tableName.Contains("banner"))
{
continue;
}
string sourceTable, destTable;
sourceTable = tableName;
destTable = model.GetType().Name;
destTable = destTable.Replace("tradesmart", "_tradesmart_");
destTable = destTable.Replace("centre", "_centre_");
destTable = destTable.Replace("subcategory", "_subcategory_");
destTable = destTable.Replace("training", "_training_");
destTable = destTable.Replace("file", "_file_");
destTable = destTable.Replace("association", "_association_");
destTable = destTable.Replace("group", "_group_");
destTable = destTable.Replace("manuals", "_manuals_");
destTable = destTable.Replace("archive", "_archive_");
destTable = destTable.Replace("rn_tn", "rn_trade_");
destTable = destTable.Remove("rn_").PascalCase();
bool hasID = false;
var fields = new DelimitedString(",");
var destfields = new DelimitedString(",");
var creates = "" + destTable + "ID [int] IDENTITY(1,1) NOT NULL, ";
foreach (var field in model.GetFields())
{
fields += field.Name.SqlizeName().value;
var newFieldName = field.Name;
foreach (var repl in "meta,centre,areas,covered,subcategory,training,retailer,tradesmart,consumer,accessory,group,price,email,size,section,path,name,manuals,product,banner,studies,performance,compare,text,study,archive,video,type,manuals,content,outlet".Split(','))
{
newFieldName = newFieldName.Replace(repl, "_" + repl + "_");
}
newFieldName = newFieldName.Replace("dateandtime", "date_and_time");
newFieldName = Fmt.PascalCase(newFieldName);
newFieldName = newFieldName.Replace("Id", "ID");
if (newFieldName == "ID" && destTable == "Country")
{
newFieldName = "Code";
}
else if (newFieldName == "ID")
{
newFieldName = destTable + "ID";
hasID = true;
}
if (newFieldName == "Priority")
{
newFieldName = "SortPosition";
}
if (newFieldName == "Image")
{
newFieldName = "Picture";
}
if (newFieldName == "Created")
{
newFieldName = "DateAdded";
}
destfields += newFieldName;
if (field.Name != "id" || !hasID)
{
creates += newFieldName + " " + field.GetSqlDataTypeDeclaration().Replace("tinyint", "bit").Replace("smallint", "int").Replace("char", "varchar").Replace("varchar", "nvarchar").Replace("varnvarchar", "nvarchar").Replace("text", "nvarchar(max)").Replace("NOT NULL", "") + ", ";
}
}
if (doCreate)
{
var sql2 = new Sql("create table " + destTable + " (<b>" + creates + "</b> CONSTRAINT [pk_" + destTable + "] PRIMARY KEY CLUSTERED (" + destTable + "ID ASC));");
result.Add(sql2);
}
var sql = new Sql("delete from " + destTable + ";"); // maybe use trunc table
if (hasID)
{
sql.Add("set identity_insert " + destTable + " on;");
}
sql.Add("insert into " + destTable + " (" + destfields + ") select " + fields + " from RinnaiOld.dbo." + sourceTable + ";");
if (hasID)
{
sql.Add("set identity_insert " + destTable + " off;");
}
result.Add(sql);
}
foreach (var migration in result)
{
Web.WriteLine(migration.ToString());
}
return(result);
}
protected void WriteResults()
{
string isOK;
// Savvy Console JSON
CommandStatus.success = true;
if (mode == "GenModels")
{
Web.Write("<b>Generating Savvy Active Record Classes</b><br /><br />");
ActiveRecordGenerator.Run();
Web.Write("<br>Done.");
CommandStatus.message = "Models updated successfully";
}
else if (mode == "AddDbFields")
{
Web.Write("<b>Creating any missing fields in Database from Savvy Active Record Classes</b><br /><br />");
ActiveRecordDatabaseGenerator.CreateAnyMissingFields();
Web.Write("<br>Done.");
CommandStatus.message = "Database updated successfully";
}
else if (mode == "MinifyJsCss")
{
Web.Write("<b>Minifying JS and CSS files</b><br /><br />");
MinifyJS();
MinifyCSS();
Web.Write("<br>Done. (Note: The script will only minify immediate children files)");
CommandStatus.message = "Minified JS and CSS files created successfully";
}
else if (mode == "GenDatabaseScripts")
{
Web.Write("<b>Generating Database Create Statements from Savvy Active Record Classes</b><br /><br />");
Web.Write(Fmt.Text(ActiveRecordDatabaseGenerator.GetSqlStatements()));
Web.Write("<br>Done.");
}
else if (mode == "ImportFromLive")
{
isOK = OKToImport("LVE");
if (isOK == "OK")
{
Web.Write("<b>Importing all data from Live and replacing local data</b><br /><br />");
//ActiveRecordDatabaseGenerator.ImportDataFrom(Util.GetSetting("LinkedServerDatabaseLVE", "throw"));
ActiveRecordDatabaseGenerator.ImportDataFrom("LVE");
}
else
{
Web.Write(isOK);
}
}
else if (mode == "ImportFromStaging")
{
isOK = OKToImport("STG");
if (isOK == "OK")
{
Web.Write("<b>Importing all data from Staging and replacing local data</b><br /><br />");
ActiveRecordDatabaseGenerator.ImportDataFrom("STG");
Web.Write("<br>Done.");
}
else
{
Web.Write(isOK);
}
}
else if (mode == "LegacyMigration")
{
Web.Write("<b>Migrating all Old Data</b><br /><br />");
//LegacyDataMigration.GenerateDataMigrationScripts("RinnaiWebsite", true);
Web.Write("<br>Done.");
}
else if (mode == "ExportToStaging")
{
Web.Write("<b>Copying all local data up to Staging and replacing existing staging data</b><br /><br />");
ActiveRecordDatabaseGenerator.ExportDataTo("STG");
Web.Write("<br>Done.");
}
else if (mode == "ViewCache")
{
Web.Write("<b>Displaying all objects in ASP.NET Web Cache</b><br /><br />");
Web.Write(Fmt.Text(Logging.DumpCache()));
Web.Write("<br>Done.");
}
else if (mode == "ClearCache")
{
Web.Write("<b>Clearing all objects in ASP.NET Web Cache</b><br /><br />");
Web.CacheClearAll();
Web.Write("<br>Done.");
CommandStatus.message = "Cache cleared successfully";
}
else if (mode == "UpdateDLLs")
{
Web.Write("<b>Updating DLLs</b><br /><br />");
UpdateDLLs();
Web.Write("<br>Done.");
Web.Write("<br>");
Web.Write("<br><br><a href='?mode=AddDbFields'>Add fields to database</a>");
Web.Write("<br><br><a href='?mode=RollbackDLLs'>Rollback</a>");
Web.Write("<br><br><a href='" + Web.Root + "'>Check homepage</a>");
}
else if (mode == "RollbackDLLs")
{
Web.Write("<b>Rolling back DLLs to backup</b><br /><br />");
RollbackDLLs();
Web.Write("<br>Done.");
Web.Write("<br><br><a href='" + Web.Root + "'>Check homepage</a>");
}
else if (mode == "FixError")
{
Web.Write("<b>Applying a Fix</b><br /><br />");
Beweb.Logging.FixError(Request["message"], Request["title"], Request["lineThatDied"]);
Web.Write("<br>Done.");
}
else if (currentClass == "all")
{
Assert.RunAllTests();
Web.Write("<br>Done.");
}
else if (currentClass != null)
{
Assert.RunTests(currentClass);
Web.Write("<br>Done.");
}
else if (mode == "CreateTable")
{
if (Util.ServerIsDev)
{
var tableName = Request["tableName"].UpperCaseFirstLetter();
var ar = new ActiveRecord(tableName, tableName + "ID");
var sql = ar.GetSqlForCreate();
sql.Execute();
CommandStatus.message = "Created table " + tableName;
}
else
{
CommandStatus.success = false;
CommandStatus.message = "Only allowed on dev.";
}
}
else if (mode == "DropTable")
{
if (Util.ServerIsDev)
{
var tableName = Request["tableName"].UpperCaseFirstLetter();
new Sql("DROP TABLE " + tableName).Execute();
CommandStatus.message = "Dropped table " + tableName;
}
else
{
CommandStatus.success = false;
CommandStatus.message = "Only allowed on dev.";
}
}
else if (mode == "ListTables")
{
var sql = new Sql().AddRawSqlString("SELECT t.name as [Table Name] FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] ORDER BY t.name");
CommandStatus.message = "Done";
CommandStatus.data = Logging.DumpTableToHtml(sql.GetDataTable());
}
else if (mode == "Sql")
{
var query = Request["query"];
if (query.IsNotBlank())
{
if (query.ToLower().DoesntContain("select"))
{
try {
int affectedRows = new Sql().AddRawSqlString(query).Execute();
CommandStatus.message = "Affected rows: " + affectedRows;
} catch (Exception ex) {
CommandStatus.success = false;
CommandStatus.message = ex.Message;
}
}
else
{
var sql = new Sql().AddRawSqlString(query);
CommandStatus.message = query;
CommandStatus.data = Logging.DumpTableToHtml(sql.GetDataTable());
}
}
else
{
CommandStatus.success = false;
CommandStatus.message = "Missing parameter";
}
}
else if (mode == "ListTables")
{
var sql = new Sql().AddRawSqlString("SELECT t.name as [Table Name] FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] ORDER BY t.name");
CommandStatus.message = "Done";
CommandStatus.data = Logging.DumpTableToHtml(sql.GetDataTable());
}
else if (mode == "SendEmail")
{
var email = Request["email"];
var subject = Request["subject"];
var message = Request["message"];
if (email.IsNotBlank() && subject.IsNotBlank() && message.IsNotBlank())
{
try {
SendEMail.SimpleSendHTMLEmail(email, subject, message);
CommandStatus.message = "Email sent to " + email;
} catch (Exception ex) {
CommandStatus.success = false;
CommandStatus.message = ex.Message;
}
}
else
{
CommandStatus.success = false;
CommandStatus.message = "Missing parameters";
}
}
else if (mode == "WhoAmI")
{
if (Security.IsLoggedIn)
{
CommandStatus.message = "You are logged in as " + UserSession.Person.FullName;
}
else
{
CommandStatus.message = "You are not logged in";
CommandStatus.success = false;
}
}
else if (mode == "gotoServerStage")
{
var serverStage = Request["serverStage"];
CommandStatus.data = Util.GetSetting("WebsiteBaseUrl" + serverStage);
CommandStatus.message = "Going to " + serverStage + " url " + CommandStatus.data;
}
else if (mode == "ServerIs")
{
CommandStatus.message = "Server is: " + Util.ServerIs();
}
else if (mode == "UploadFile")
{
try {
var file = Request.Files[0];
if (file.ContentLength > 0)
{
var fullPath = Server.MapPath(Path.Combine("~/attachments/", Path.GetFileName(file.FileName)));
int count = 1;
string fileNameOnly = Path.GetFileNameWithoutExtension(fullPath);
string extension = Path.GetExtension(fullPath);
string path = Path.GetDirectoryName(fullPath);
string newFullPath = fullPath;
while (File.Exists(newFullPath))
{
string tempFileName = string.Format("{0}({1})", fileNameOnly, count++);
newFullPath = Path.Combine(path, tempFileName + extension);
}
file.SaveAs(newFullPath);
CommandStatus.message = "The file was uploaded successfully. <a href='" + Web.Attachments + Path.GetFileName(newFullPath) + "' target='_blank'>" + Path.GetFileName(newFullPath) + "</a>";
}
else
{
throw new Exception("No file selected");
}
} catch (Exception ex) {
CommandStatus.success = false;
CommandStatus.message = ex.Message.Replace("\\", "\\\\");
}
}
else if (mode == "UploadURL")
{
var url = Request["url"];
if (url.IsNotBlank())
{
try {
var filename = url.RightFrom("/");
if (!url.Replace("http://", "").Replace("https://", "").Contains("/") || !filename.Contains("."))
{
throw new Exception("The URL doesn't point to any file");
}
var newFilename = FileSystem.GetUniqueAttachmentFilename(filename);
using (var client = new WebClient()) {
client.DownloadFile(url, Server.MapPath(Path.Combine("~/attachments/", newFilename)));
}
CommandStatus.message = "The file was uploaded successfully. <a href='" + Web.Attachments + newFilename + "' target='_blank'>" + newFilename + "</a>";
} catch (Exception ex) {
CommandStatus.success = false;
CommandStatus.message = ex.Message;
}
}
else
{
CommandStatus.success = false;
CommandStatus.message = "Missing parameter";
}
}
else if (mode == "EncodeKeystoneID")
{
CommandStatus.message = "Encoded ID: " + Crypto.EncryptID(Request["id"].ToInt(0));
}
else if (mode == "EncodeKeystoneIDClassic")
{
CommandStatus.message = "Encoded ID: " + Crypto.EncryptIDClassic(Request["id"].ToInt(0));
}
else if (mode == "Encrypt")
{
CommandStatus.message = "Encrypted: " + Crypto.Encrypt(Request["enc"]);
}
else if (mode == "Decrypt")
{
CommandStatus.message = "Decrypted: " + Crypto.Decrypt(Request["decr"]);
}
else if (mode == "DecodeKeystoneID")
{
try {
var id = Crypto.DecryptID(Request["encid"]);
if (id == -1)
{
throw new Exception();
}
CommandStatus.message = "Decoded ID: " + id;
} catch {
CommandStatus.success = false;
CommandStatus.message = "Invalid ID";
}
}
else if (mode == "DecodeKeystoneIDClassic")
{
try {
var id = Crypto.DecryptIDClassic(Request["encid"]);
if (id == -1)
{
throw new Exception();
}
CommandStatus.message = "Decoded ID: " + id;
} catch {
CommandStatus.success = false;
CommandStatus.message = "Invalid ID";
}
}
else if (mode == "LockSite")
{
try {
ConfigurationManager.AppSettings["LockSiteHomepage" + Util.ServerIs()] = "true";
ConfigurationManager.RefreshSection("appSettings");
//edit the actual web config
SetLockSiteHomepageAppSettingsValue(true);
//clear the cookie so if it works the lockscreen will appear on next page load / refresh
var httpCookie = Response.Cookies["pwlock"];
if (httpCookie != null)
{
httpCookie.Expires = DateTime.Now.AddDays(-1);
}
CommandStatus.message = "Site locked. [PWD:" + Util.GetSetting("LockSitePassword") + "] Double check your app settings before your next deploy";
} catch (Exception) {
CommandStatus.success = false;
CommandStatus.message = "Failed to lock site";
}
}
else if (mode == "UnlockSite")
{
try {
//update app settings in memory.
ConfigurationManager.AppSettings["LockSiteHomepage" + Util.ServerIs()] = "false";
ConfigurationManager.RefreshSection("appSettings");
//edit the actual web config
SetLockSiteHomepageAppSettingsValue(false);
//var replaceText = fileContent.ExtractTextBetween(@"<add key=""LockSiteHomepage"+Util.ServerIs()+@""" value=""", @"""/>");
//clear the cookie so if it fails the lockscreen will appear on next page load / refresh
var httpCookie = Response.Cookies["pwlock"];
if (httpCookie != null)
{
httpCookie.Expires = DateTime.Now.AddDays(-1);
}
CommandStatus.message = "Site unlocked. Double check your app settings before your next deploy.";
} catch (Exception) {
CommandStatus.success = false;
CommandStatus.message = "Failed to unlock site";
}
}
}
