public void GenerateSqlFile(string path)
{
var tablename = "crags";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `slug`, `category`, `name`, `town`, `country_id`, `latitude`, `longitude`,`date_created`,`date_modified`,`published`,`access`, `area_id`, `legacy_id`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var crags = SeedStore.GetCrags();
var rowCount = crags.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var crag in crags)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
crag.Id,
crag.Slug.ToSqlInsertString(),
crag.Category.ToSqlInsertString(),
crag.Name.ToSqlInsertStringEscape(false),
crag.Town.ToSqlInsertStringEscape(true),
crag.CountryId,
crag.Latitude.HasValue ? crag.Latitude.ToString() : "NULL",
crag.Longitude.HasValue ? crag.Longitude.ToString() : "NULL",
crag.DateCreated.ToSqlInsertString(),
crag.DateModified.ToSqlInsertString(),
crag.Published ? 1 : 0,
crag.Access.ToSqlInsertStringEscape(false),
crag.AreaId.HasValue ? crag.AreaId.ToString() : "NULL",
crag.LegacyId.HasValue ? crag.LegacyId.ToString() : "NULL"
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "boulders";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `date_created`, `date_modified`, `difficulty`, `grade`, `grading_system`, `name`, `notes`, `path`, `reference_width`, `sector_id`, `sit_down_start`, `topo_num`, `traverse`, `slug`, `legacy_id`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var boulders = SeedStore.GetBoulders();
var rowCount = boulders.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var thingie in boulders)
{
var item = thingie as _8anu.Api.Common.DataEntities.Boulder;
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
item.Id,
item.DateCreated.ToSqlInsertString(),
item.DateModified.ToSqlInsertString(),
item.Difficulty.ToSqlInsertString(),
item.Grade.ToSqlInsertString(),
item.GradingSystem.ToSqlInsertString(),
item.Name.ToSqlInsertStringEscape(true),
item.Notes.ToSqlInsertStringEscape(true),
item.Path.ToSqlInsertStringEscape(true),
item.ReferenceWidth.HasValue ? item.ReferenceWidth.Value.ToString() : "NULL",
item.SectorId,
item.SitDownStart ? 1 : 0,
item.TopoNum.ToSqlInsertStringEscape(true),
item.Traverse.HasValue ? (item.Traverse.Value ? "1" : "0") : "NULL",
SeedStore.GetNewSlug().ToSqlInsertString(),
item.LegacyId.HasValue ? item.LegacyId.ToString() : "NULL"
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "forum_categories";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `date_created`, `date_modified`, `description`, `name`, `parent_id`, `slug`, `user_id`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var rowCount = SeedStore.ForumCategories.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var cat in SeedStore.ForumCategories.Values)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
cat.Id,
cat.DateCreated.ToSqlInsertString(),
cat.DateModified.ToSqlInsertString(),
cat.Description.ToSqlInsertStringEscape(true),
cat.Name.ToSqlInsertStringEscape(false),
cat.ParentId.HasValue ? cat.ParentId.Value.ToString() : "NULL",
cat.Slug.ToSqlInsertString(),
cat.UserId
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "ascents";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `user_id`, `date`, `difficulty`, `zlaggable_id`, `zlaggable_type`, `comment`, `score`, `type`, `rating`, `repeat`, `project`, `chipped`, `exclude_from_ranking`, `note`, `date_created`, `date_modified`, `recommended`, `legacy_id`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var ascents = SeedStore.Ascents;
var rowCount = ascents.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var item in ascents)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
item.Id,
item.UserId,
item.Date.UtcDateTime.ToSqlInsertString(),
item.Difficulty.ToSqlInsertString(true),
item.ZlaggableId.Value,
item.ZlaggableType.ToSqlInsertString(true),
item.Comment.ToSqlInsertStringEscape(true),
item.Score.HasValue ? item.Score.Value.ToString() : "NULL",
item.Type.ToSqlInsertString(true),
item.Rating.HasValue ? item.Rating.Value.ToString() : "NULL",
item.Repeat.HasValue ? (item.Repeat.Value ? 1 : 0).ToString() : "NULL",
item.Project.HasValue ? (item.Project.Value ? 1 : 0).ToString() : "NULL",
item.Chipped.HasValue ? (item.Chipped.Value ? 1 : 0).ToString() : "NULL",
item.ExcludeFromRanking.HasValue ? (item.ExcludeFromRanking.Value ? 1 : 0).ToString() : "NULL",
item.Note.HasValue ? item.Note.Value.ToString() : "NULL",
item.DateCreated.ToSqlInsertString(),
item.DateModified.ToSqlInsertString(),
(item.Recommended ? 1 : 0).ToString(),
item.LegacyId.HasValue ? item.LegacyId.ToString() : "NULL"
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "countries";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `iso2`, `iso3`, `name`, `slug`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var rowCount = SeedStore.Countries.Values.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var country in SeedStore.Countries.Values)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
country.Id,
country.ISO2.ToSqlInsertString(),
country.ISO3.ToSqlInsertString(),
country.Name.ToSqlInsertStringEscape(),
country.Slug.ToSqlInsertString()
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "sectors";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `category`, `crag_id`, `date_created`, `date_modified`, `latitude`, `longitude`, `name`, `notes`, `ordering`, `slug`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var sectors = SeedStore.GetSectors();
var rowCount = sectors.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var sector in sectors)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
sector.Id,
sector.Category.ToSqlInsertString(),
sector.CragId,
sector.DateCreated.ToSqlInsertString(),
sector.DateModified.ToSqlInsertString(),
sector.Latitude.HasValue ? sector.Latitude.ToString() : "NULL",
sector.Longitude.HasValue ? sector.Longitude.ToString() : "NULL",
sector.Name.ToSqlInsertStringEscape(false),
sector.Notes.ToSqlInsertStringEscape(true),
sector.Ordering.HasValue ? sector.Ordering.ToString() : "NULL",
SeedStore.GetNewSlug().ToSqlInsertString()
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "areas";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `country_id`, `date_created`, `date_modified`, `description`, `name`, `published`, `slug`, `legacy_id`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var rowCount = newAreas.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var area in newAreas)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
area.Id,
area.CountryId,
area.DateCreated.ToSqlInsertString(),
area.DateModified.ToSqlInsertString(),
area.Description.ToSqlInsertStringEscape(),
area.Name.ToSqlInsertStringEscape(),
area.Published ? 1 : 0,
SeedStore.GetNewSlug().ToSqlInsertString(),
area.LegacyId.HasValue ? area.LegacyId.ToString() : "NULL"
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "users";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `country_id`, `date_created`, `date_modified`, `first_name`, `gender`, `last_name`, `slug`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var rowCount = SeedStore.Users.Values.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var user in SeedStore.Users.Values)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
user.Id,
user.CountryId,
user.DateCreated.ToSqlInsertString(),
user.DateModified.ToSqlInsertString(),
user.FirstName.ToSqlInsertStringEscape(false),
user.Gender == Api.Common.Enums.GenderEnum.Male ? 0 : 1,
user.LastName.ToSqlInsertStringEscape(false),
user.Slug.ToSqlInsertString()
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "grading_systems";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `category`, `grade`, `type`, `vl_grade`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var rowCount = SeedStore.GradingSystems.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var grade in SeedStore.GradingSystems)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
grade.Id,
grade.Category.ToSqlInsertString(),
grade.Grade.ToSqlInsertString(),
grade.Type.ToSqlInsertString(),
grade.VLGrade.ToSqlInsertString()
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
public void GenerateSqlFile(string path)
{
var tablename = "test_models";
SeedStore.AddAllSqlString(tablename);
var head = $@"TRUNCATE TABLE `{tablename}`;
ALTER TABLE `{tablename}` DISABLE KEYS;";
var tail = $"ALTER TABLE `{tablename}` ENABLE KEYS;";
var insertHead = @"INSERT INTO `" + tablename + @"` (`id`, `date_created`, `date_modified`, `message`)
VALUES";
var currentPageRow = 1;
var rowNumber = 0;
var maxRowCountPerInsert = SeedStore.MAX_ROW_COUNT_PER_INSERT;
var rowCount = items.Count;
using (var filestream = new FileStream(path, FileMode.Create))
using (var sq = new StreamWriter(filestream))
{
sq.WriteLine(SeedStore.SQL_HEAD);
sq.WriteLine(head);
sq.WriteLine();
sq.WriteLine(insertHead);
foreach (var test in items)
{
rowNumber++;
if (currentPageRow > maxRowCountPerInsert)
{
sq.WriteLine();
sq.WriteLine(insertHead);
currentPageRow = 1;
}
var line = new StringBuilder("(");
line.Append(string.Join(",", new object[] {
test.Id,
test.DateCreated.ToSqlInsertString(),
test.DateModified.ToSqlInsertString(),
test.Message.ToSqlInsertStringEscape()
}));
line.Append(")");
currentPageRow++;
if (currentPageRow <= maxRowCountPerInsert && rowNumber < rowCount)
{
line.Append(",");
}
else
{
line.Append(";");
}
sq.WriteLine(line);
}
sq.WriteLine();
sq.WriteLine(tail);
sq.WriteLine();
sq.WriteLine(SeedStore.SQL_TAIL);
}
}
