/// <summary>
/// Operations to be performed during the upgrade process.
/// </summary>
public override void Up()
{
AddColumn("dbo.Person", "MetaPersonicxLifestageClusterId", c => c.Int());
AddColumn("dbo.Person", "MetaPersonicxLifestageGroupId", c => c.Int());
AlterColumn("dbo.MetaPersonicxLifestageCluster", "Summary", c => c.String(maxLength: 1000));
CreateIndex("dbo.Person", "MetaPersonicxLifestageClusterId");
CreateIndex("dbo.Person", "MetaPersonicxLifestageGroupId");
AddForeignKey("dbo.Person", "MetaPersonicxLifestageClusterId", "dbo.MetaPersonicxLifestageCluster", "Id");
AddForeignKey("dbo.Person", "MetaPersonicxLifestageGroupId", "dbo.MetaPersonicxLifestageGroup", "Id");
Sql("DELETE [MetaFirstNameGenderLookup]");
ExecuteSqlInsert(
"INSERT INTO [MetaFirstNameGenderLookup] ([FirstName],[MaleCount],[FemaleCount],[Country],[Language],[TotalCount],[FemalePercent],[MalePercent],[Guid])",
MigrationSQL._201707311527250_FirstNameGender);
// use BulkInsert for LastName since there are 150,000+ rowsa
List <Rock.Model.MetaLastNameLookup> metaLastNameLookupsToInsert = new List <Model.MetaLastNameLookup>();
var lastNameLines = MigrationSQL._201707311527250_LastName.Split(new[] { "\n" }, StringSplitOptions.None).ToList();
foreach (var lastNameLine in lastNameLines)
{
var lastNameColValues = lastNameLine.Split(new[] { ',' }, StringSplitOptions.None).ToList();
if (lastNameColValues.Count == 4)
{
var metaLastNameLookup = new Rock.Model.MetaLastNameLookup();
metaLastNameLookup.LastName = lastNameColValues[0].TrimStart(new[] { '\'' }).TrimEnd(new[] { '\'' });
metaLastNameLookup.Count = lastNameColValues[1].AsInteger();
metaLastNameLookup.Rank = lastNameColValues[2].AsInteger();
metaLastNameLookup.CountIn100k = lastNameColValues[3].AsDecimalOrNull();
metaLastNameLookup.Guid = Guid.NewGuid();
metaLastNameLookupsToInsert.Add(metaLastNameLookup);
}
}
using (var rockContext = new Rock.Data.RockContext())
{
rockContext.Database.ExecuteSqlCommand("DELETE [MetaLastNameLookup]");
rockContext.Database.ExecuteSqlCommand("ALTER TABLE MetaLastNameLookup ALTER COLUMN LastName NVARCHAR(100)");
rockContext.BulkInsert(metaLastNameLookupsToInsert);
}
Sql("DELETE [MetaNickNameLookup]");
ExecuteSqlInsert(
"INSERT INTO [MetaNickNameLookup] ([FirstName],[NickName],[Gender],[Count],[Guid])",
MigrationSQL._201707311527250_NickName);
Sql("DELETE [MetaPersonicxLifestageGroup]");
ExecuteSqlInsert(
"INSERT INTO [MetaPersonicxLifestageGroup] ([LifestyleGroupCode],[LifestyleGroupName],[Description],[Summary],[PercentUS],[LifeStage],[MaritalStatus],[HomeOwnership],[Children],[Income],[IncomeRank],[Urbanicity], [UrbanicityRank], [NetWorth], [NetworthRank],[DetailsUrl],[Guid])",
MigrationSQL._201707311527250_PersonicxGroup);
Sql("DELETE [MetaPersonicxLifestageCluster]");
ExecuteSqlInsert(
"INSERT INTO [MetaPersonicxLifestageCluster] ([LifestyleClusterCode],[LifestyleClusterName],[Description],[Summary],[PercentUS],[LifeStage],[MaritalStatus],[HomeOwnership],[Children],[Income],[IncomeRank],[Urbanicity], [UrbanicityRank], [NetWorth], [NetworthRank],[MetaPersonicxLifestyleGroupId],[DetailsUrl],[Guid])",
MigrationSQL._201707311527250_PersonicxCluster);
}
/// <summary>
/// Operations to be performed during the upgrade process.
/// </summary>
public override void Up()
{
Sql("DELETE [MetaFirstNameGenderLookup]");
ExecuteSqlInsert(
"INSERT INTO [MetaFirstNameGenderLookup] ([FirstName],[MaleCount],[FemaleCount],[Country],[Language],[TotalCount],[FemalePercent],[MalePercent],[Guid])",
MigrationSQL._201707311527250_FirstNameGender);
// use BulkInsert for LastName since there are 150,000+ rowsa
List <Rock.Model.MetaLastNameLookup> metaLastNameLookupsToInsert = new List <Model.MetaLastNameLookup>();
List <string> lastNameLines;
using (var lastNameZip = new System.IO.Compression.GZipStream(new MemoryStream(MigrationSQL._201707311527250_LastName), System.IO.Compression.CompressionMode.Decompress, false))
{
var lastNameText = Encoding.ASCII.GetString(lastNameZip.ReadBytesToEnd());
lastNameLines = lastNameText.Split(new[] { "\n" }, StringSplitOptions.None).ToList();
}
foreach (var lastNameLine in lastNameLines)
{
var lastNameColValues = lastNameLine.Split(new[] { ',' }, StringSplitOptions.None).ToList();
if (lastNameColValues.Count == 4)
{
var metaLastNameLookup = new Rock.Model.MetaLastNameLookup();
metaLastNameLookup.LastName = lastNameColValues[0].TrimStart(new[] { '\'' }).TrimEnd(new[] { '\'' });
metaLastNameLookup.Count = lastNameColValues[1].AsInteger();
metaLastNameLookup.Rank = lastNameColValues[2].AsInteger();
metaLastNameLookup.CountIn100k = lastNameColValues[3].AsDecimalOrNull();
metaLastNameLookup.Guid = Guid.NewGuid();
metaLastNameLookupsToInsert.Add(metaLastNameLookup);
}
}
using (var rockContext = new Rock.Data.RockContext())
{
rockContext.Database.ExecuteSqlCommand("DELETE [MetaLastNameLookup]");
rockContext.Database.ExecuteSqlCommand("ALTER TABLE MetaLastNameLookup ALTER COLUMN LastName NVARCHAR(100)");
rockContext.BulkInsert(metaLastNameLookupsToInsert);
}
Sql("DELETE [MetaNickNameLookup]");
ExecuteSqlInsert(
"INSERT INTO [MetaNickNameLookup] ([FirstName],[NickName],[Gender],[Count],[Guid])",
MigrationSQL._201707311527250_NickName);
Sql("DELETE [MetaPersonicxLifestageGroup]");
ExecuteSqlInsert(
"INSERT INTO [MetaPersonicxLifestageGroup] ([LifestyleGroupCode],[LifestyleGroupName],[Description],[Summary],[PercentUS],[LifeStage],[MaritalStatus],[HomeOwnership],[Children],[Income],[IncomeRank],[Urbanicity], [UrbanicityRank], [NetWorth], [NetworthRank],[DetailsUrl],[Guid])",
MigrationSQL._201707311527250_PersonicxGroup);
Sql("DELETE [MetaPersonicxLifestageCluster]");
ExecuteSqlInsert(
"INSERT INTO [MetaPersonicxLifestageCluster] ([LifestyleClusterCode],[LifestyleClusterName],[Description],[Summary],[PercentUS],[LifeStage],[MaritalStatus],[HomeOwnership],[Children],[Income],[IncomeRank],[Urbanicity], [UrbanicityRank], [NetWorth], [NetworthRank],[MetaPersonicxLifestyleGroupId],[DetailsUrl],[Guid])",
MigrationSQL._201707311527250_PersonicxCluster);
}