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);