protected override string GetUpdateSql(EntityDbMetadata metadata)
{
string setParamenters = string.Join(", ", metadata.Table.UpdateColumns.Select(p => $"`{p}`=@{p}"));
StringBuilder primaryParamenters = new StringBuilder();
if ("__id" == metadata.Table.Primary)
{
primaryParamenters.Append("`__Id` = @__Id,");
}
else
{
var columns = metadata.Table.Primary.Split(',');
foreach (var column in columns)
{
if (columns.Last() != column)
{
primaryParamenters.Append($" `{column}` = @{column} AND ");
}
else
{
primaryParamenters.Append($" `{column}` = @{column}");
}
}
}
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE `{0}`.`{1}` SET {2} WHERE {3};",
metadata.Table.Database,
metadata.Table.Name,
setParamenters, primaryParamenters);
return(sqlBuilder.ToString());
}
protected override string GetUpdateSql(EntityDbMetadata metadata)
{
string setParamenters = string.Join(", ", metadata.Table.UpdateColumns.Select(p => $"[{p}]=@{p}"));
StringBuilder primaryParamenters = new StringBuilder();
if (string.IsNullOrEmpty(metadata.Table.Primary))
{
primaryParamenters.Append("[__Id] = @__Id");
}
else
{
var columns = metadata.Table.Primary.Split(',');
foreach (var column in columns)
{
if (columns.Last() != column)
{
primaryParamenters.Append($" [{column}] = @{column} AND ");
}
else
{
primaryParamenters.Append($" [{column}] = @{column}");
}
}
}
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("USE {0}; UPDATE [{1}] SET {2} WHERE {3};",
metadata.Table.Database,
metadata.Table.Name,
setParamenters, primaryParamenters);
return(sqlBuilder.ToString());
}
protected override string GetCreateTableSql(EntityDbMetadata metadata)
{
StringBuilder builder = new StringBuilder($"USE {metadata.Table.Database}; IF OBJECT_ID('{metadata.Table.Name}', 'U') IS NULL CREATE table {metadata.Table.Name} (");
StringBuilder columnNames = new StringBuilder();
foreach (var p in metadata.Columns)
{
columnNames.Append($",[{p.Name}] {GetDataTypeSql(p)}");
}
builder.Append(columnNames.ToString().Substring(1, columnNames.Length - 1));
builder.Append(",[CDate] DATETIME DEFAULT(GETDATE())");
if ("__id" == metadata.Table.Primary.ToLower())
{
builder.Append(", [__Id] [bigint] IDENTITY(1,1) NOT NULL");
}
builder.Append(",");
StringBuilder primaryKey = new StringBuilder();
if (string.IsNullOrEmpty(metadata.Table.Primary))
{
primaryKey.Append("[__Id] ASC,");
}
else
{
var columns = metadata.Table.Primary.Split(',');
foreach (var column in columns)
{
primaryKey.Append($"[{column}] ASC,");
}
}
builder.Append(
$" CONSTRAINT [PK_{metadata.Table.Name}] PRIMARY KEY CLUSTERED ({primaryKey.ToString(0, primaryKey.Length - 1)})WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]) ON[PRIMARY];");
if (metadata.Table.Indexs != null)
{
foreach (var index in metadata.Table.Indexs)
{
var columns = index.Split(',');
string name = string.Join("_", columns.Select(c => c));
string indexColumNames = string.Join(", ", columns.Select(c => $"[{c}]"));
builder.Append($"CREATE NONCLUSTERED INDEX [index_{name}] ON {metadata.Table.Name} ({indexColumNames.Substring(0, indexColumNames.Length)});");
}
}
if (metadata.Table.Uniques != null)
{
foreach (var unique in metadata.Table.Uniques)
{
var columns = unique.Split(',');
string name = string.Join("_", columns.Select(c => c));
string uniqueColumNames = string.Join(", ", columns.Select(c => $"[{c}]"));
builder.Append($"CREATE UNIQUE NONCLUSTERED INDEX [unique_{name}] ON {metadata.Table.Name} ({uniqueColumNames.Substring(0, uniqueColumNames.Length)});");
}
}
return(builder.ToString());
}
protected override string GetSelectSql(EntityDbMetadata metadata)
{
string selectParamenters = string.Join(", ", metadata.Table.UpdateColumns.Select(p => $"`{p}`"));
StringBuilder primaryParamenters = new StringBuilder();
//string.Join(" AND ", $"`{Schema.Primary}`=@{Schema.Primary}");
if (Core.Infrastructure.Environment.IdColumn == metadata.Table.Primary)
{
primaryParamenters.Append($"`{Core.Infrastructure.Environment.IdColumn}` = @{Core.Infrastructure.Environment.IdColumn},");
}
else
{
var columns = metadata.Table.Primary.Split(',');
foreach (var column in columns)
{
if (columns.Last() != column)
{
primaryParamenters.Append($" `{column}` = @{column} AND ");
}
else
{
primaryParamenters.Append($" `{column}` = @{column}");
}
}
}
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("SELECT {0} FROM `{1}`.`{2}` WHERE {3};",
selectParamenters,
metadata.Table.Database,
metadata.Table.Name,
primaryParamenters);
return(sqlBuilder.ToString());
}
protected override string GetCreateTableSql(EntityDbMetadata metadata)
{
StringBuilder builder = new StringBuilder($"USE {metadata.Table.Database}; IF OBJECT_ID('{metadata.Table.Name}', 'U') IS NULL CREATE table {metadata.Table.Name} (");
StringBuilder columnNames = new StringBuilder();
foreach (var p in metadata.Columns)
{
columnNames.Append($",[{p.Name}] {(p.Length <= 0 ? "NVARCHAR(MAX)" : $"NVARCHAR({p.Length})")} {(p.NotNull ? "NOT NULL" : "NULL")}");
}
protected override string GetSelectSql(EntityDbMetadata metadata)
{
string selectParamenters = string.Join(", ", metadata.Table.UpdateColumns.Select(p => $"[{p}]"));
string primaryParamenters = $" [{metadata.Table.Primary}]=@{metadata.Table.Primary}";
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("USE {0}; SELECT {1} FROM [{2}] WHERE {3};",
metadata.Table.Database,
selectParamenters,
metadata.Table.Name,
primaryParamenters);
return(sqlBuilder.ToString());
}
protected override string GetInsertSql(EntityDbMetadata metadata)
{
string columNames = string.Join(", ", metadata.Columns.Select(p => $"`{p.Name}`"));
string values = string.Join(", ", metadata.Columns.Select(p => $"@{p.Name}"));
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("INSERT IGNORE INTO `{0}`.`{1}` {2} {3};",
metadata.Table.Database,
metadata.Table.Name,
string.IsNullOrEmpty(columNames) ? string.Empty : $"({columNames})",
string.IsNullOrEmpty(values) ? string.Empty : $" VALUES ({values})");
return(sqlBuilder.ToString());
}
protected override string GetIfSchemaExistsSql(EntityDbMetadata metadata, string serverVersion)
{
string version = serverVersion.Split('.')[0];
switch (version)
{
case "11":
{
return($"SELECT COUNT(*) FROM sysdatabases WHERE name='{metadata.Table.Database}'");
}
default:
{
return($"SELECT COUNT(*) FROM sys.databases WHERE name='{metadata.Table.Database}'");
}
}
}
protected override string GetCreateSchemaSql(EntityDbMetadata metadata, string serverVersion)
{
string version = serverVersion.Split('.')[0];
switch (version)
{
case "11":
{
return($"USE master; IF NOT EXISTS(SELECT * FROM sysdatabases WHERE name='{metadata.Table.Database}') CREATE DATABASE {metadata.Table.Database};");
}
default:
{
return($"USE master; IF NOT EXISTS(SELECT * FROM sys.databases WHERE name='{metadata.Table.Database}') CREATE DATABASE {metadata.Table.Database};");
}
}
}
protected override string GetCreateTableSql(EntityDbMetadata metadata)
{
StringBuilder builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS `{metadata.Table.Database }`.`{metadata.Table.Name}` (");
string columNames = string.Join(", ", metadata.Columns.Select(p => $"`{p.Name}` {GetDataTypeSql(p)} "));
builder.Append(columNames);
builder.Append(",`cdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP");
if (metadata.Table.Primary.ToLower() == Core.Infrastructure.Environment.IdColumn)
{
builder.Append($", `{Core.Infrastructure.Environment.IdColumn}` bigint AUTO_INCREMENT");
}
if (metadata.Table.Indexs != null)
{
foreach (var index in metadata.Table.Indexs)
{
var columns = index.Split(',');
string name = string.Join("_", columns.Select(c => c));
string indexColumNames = string.Join(", ", columns.Select(c => $"`{c}`"));
builder.Append($", KEY `index_{name}` ({indexColumNames.Substring(0, indexColumNames.Length)})");
}
}
if (metadata.Table.Uniques != null)
{
foreach (var unique in metadata.Table.Uniques)
{
var columns = unique.Split(',');
string name = string.Join("_", columns.Select(c => c));
string uniqueColumNames = string.Join(", ", columns.Select(c => $"`{c}`"));
builder.Append($", UNIQUE KEY `unique_{name}` ({uniqueColumNames.Substring(0, uniqueColumNames.Length)})");
}
}
builder.Append($", PRIMARY KEY ({ metadata.Table.Primary})");
using (var conn = new MySqlConnection(ConnectString))
{
var dbEngine = MySqlEngine.IsSupportToku(conn) ? "TokuDB" : "InnoDB";
builder.Append($") ENGINE={dbEngine} AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
string sql = builder.ToString();
return(sql);
}
}
protected override string GetCreateTableSql(EntityDbMetadata metadata)
{
StringBuilder builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS `{metadata.Table.Database }`.`{metadata.Table.Name}` (");
string columNames = string.Join(", ", metadata.Columns.Select(p => $"`{p.Name}` {GetDataTypeSql(p)} "));
builder.Append(columNames);
builder.Append(",`cdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP");
if (metadata.Table.Primary.ToLower() == "__id")
{
builder.Append(", `__id` bigint AUTO_INCREMENT");
}
if (metadata.Table.Indexs != null)
{
foreach (var index in metadata.Table.Indexs)
{
var columns = index.Split(',');
string name = string.Join("_", columns.Select(c => c));
string indexColumNames = string.Join(", ", columns.Select(c => $"`{c}`"));
builder.Append($", KEY `index_{name}` ({indexColumNames.Substring(0, indexColumNames.Length)})");
}
}
if (metadata.Table.Uniques != null)
{
foreach (var unique in metadata.Table.Uniques)
{
var columns = unique.Split(',');
string name = string.Join("_", columns.Select(c => c));
string uniqueColumNames = string.Join(", ", columns.Select(c => $"`{c}`"));
builder.Append($", UNIQUE KEY `unique_{name}` ({uniqueColumNames.Substring(0, uniqueColumNames.Length)})");
}
}
builder.Append($", PRIMARY KEY ({ metadata.Table.Primary})");
builder.Append(") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
string sql = builder.ToString();
return(sql);
}
protected override string GetIfSchemaExistsSql(EntityDbMetadata metadata, string serverVersion)
{
return($"SELECT COUNT(*) FROM information_schema.SCHEMATA where SCHEMA_NAME='{metadata.Table.Database}';");
}
protected override string GetCreateSchemaSql(EntityDbMetadata metadata, string serverVersion)
{
return($"CREATE SCHEMA IF NOT EXISTS `{metadata.Table.Database}` DEFAULT CHARACTER SET utf8mb4 ;");
}
protected override string GetCreateTableSql(EntityDbMetadata metadata)
{
StringBuilder builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS `{metadata.Table.Database }`.`{metadata.Table.Name}` (");
string columNames = string.Join(", ", metadata.Columns.Select(p => $"`{p.Name}` {((p.Length <= 0) ? "TEXT" : $"VARCHAR({ p.Length})")} "));
public override void AddEntity(Entity metadata)
{
if (metadata.Table == null)
{
Spider.Log($"Schema is necessary, Pass {GetType().Name} for {metadata.Name}.", LogLevel.Warn);
return;
}
EntityDbMetadata dbMetadata = new EntityDbMetadata {
Table = metadata.Table
};
foreach (var f in metadata.Fields)
{
var column = f;
if (!column.IgnoreStore)
{
dbMetadata.Columns.Add(column);
}
}
if (dbMetadata.Columns.Count == 0)
{
throw new SpiderException($"Columns is necessary, Pass {GetType().Name} for {metadata.Name}.");
}
if (!string.IsNullOrEmpty(metadata.Table.Primary))
{
var items = new HashSet <string>(metadata.Table.Primary.Split(','));
if (items.Count > 0)
{
foreach (var item in items)
{
var column = dbMetadata.Columns.FirstOrDefault(c => c.Name == item);
if (column == null)
{
throw new SpiderException("Columns set as Primary is not a property of your entity.");
}
if (column.Length > 256)
{
throw new SpiderException("Column length of Primary should not large than 256.");
}
column.NotNull = true;
}
}
else
{
dbMetadata.Table.Primary = "__id";
}
}
else
{
dbMetadata.Table.Primary = "__id";
}
if (dbMetadata.Table.UpdateColumns != null && dbMetadata.Table.UpdateColumns.Length > 0)
{
foreach (var column in dbMetadata.Table.UpdateColumns)
{
if (dbMetadata.Columns.All(c => c.Name != column))
{
throw new SpiderException("Columns set as update is not a property of your entity.");
}
}
var updateColumns = new List <string>(dbMetadata.Table.UpdateColumns);
updateColumns.Remove(dbMetadata.Table.Primary);
dbMetadata.Table.UpdateColumns = updateColumns.ToArray();
if (dbMetadata.Table.UpdateColumns.Length == 0)
{
throw new SpiderException("There is no column need update.");
}
dbMetadata.SelectSql = GetSelectSql(dbMetadata);
dbMetadata.UpdateSql = GetUpdateSql(dbMetadata);
dbMetadata.IsInsertModel = false;
}
if (dbMetadata.Table.Indexs != null && dbMetadata.Table.Indexs.Length > 0)
{
for (int i = 0; i < dbMetadata.Table.Indexs.Length; ++i)
{
var items = new HashSet <string>(dbMetadata.Table.Indexs[i].Split(','));
if (items.Count == 0)
{
throw new SpiderException("Index should contain more than a column.");
}
foreach (var item in items)
{
var column = dbMetadata.Columns.FirstOrDefault(c => c.Name == item);
if (column == null)
{
throw new SpiderException("Columns set as index is not a property of your entity.");
}
if (column.Length <= 0 || column.Length > 256)
{
throw new SpiderException("Column length of index should not large than 256.");
}
}
dbMetadata.Table.Indexs[i] = string.Join(",", items);
}
}
if (dbMetadata.Table.Uniques != null && dbMetadata.Table.Uniques.Length > 0)
{
for (int i = 0; i < dbMetadata.Table.Uniques.Length; ++i)
{
var items = new HashSet <string>(dbMetadata.Table.Uniques[i].Split(','));
if (items.Count == 0)
{
throw new SpiderException("Unique should contain more than a column.");
}
foreach (var item in items)
{
var column = dbMetadata.Columns.FirstOrDefault(c => c.Name == item);
if (column == null)
{
throw new SpiderException("Columns set as unique is not a property of your entity.");
}
if (column.DataType == DataType.Text && (column.Length <= 0 || column.Length > 256))
{
throw new SpiderException("Column length of unique should not large than 256.");
}
}
dbMetadata.Table.Uniques[i] = string.Join(",", items);
}
}
dbMetadata.InsertSql = GetInsertSql(dbMetadata);
DbMetadatas.TryAdd(metadata.Name, dbMetadata);
}
protected abstract string GetIfSchemaExistsSql(EntityDbMetadata metadata, string serverVersion);
protected abstract string GetCreateSchemaSql(EntityDbMetadata metadata, string serverVersion);
protected abstract string GetInsertSql(EntityDbMetadata metadata);
protected abstract string GetUpdateSql(EntityDbMetadata metadata);
protected abstract string GetSelectSql(EntityDbMetadata metadata);
protected abstract string GetCreateTableSql(EntityDbMetadata metadata);
