public override CommandData CreateSelectInsertCommand(InsertSelector insertSelector, DataEntityMapping mapping, AggregateSelector selector, AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order, CreateSqlState state) { var selectCommandData = CreateAggregateTableCommand(mapping, selector, groupBy, query, having, null, null, state); var insertFields = insertSelector.GetInsertFields(); var insertFieldNames = new string[insertFields.Length]; for (var i = 0; i < insertFields.Length; i++) { insertFieldNames[i] = CreateDataFieldSql(insertFields[i].FieldName); } var insertString = string.Join(",", insertFieldNames); var selectString = insertSelector.CreateSelectString(this, false, state); var sql = $"insert into {CreateDataTableMappingSql(insertSelector.InsertMapping, state)}({insertString})select {selectString} from ({selectCommandData.CommandText}) as A"; if (order != null) { state.UseFieldAlias = true; sql += GetOrderString(order, false, state); state.UseFieldAlias = false; } selectCommandData.CommandText = sql; return(selectCommandData); }
private static string GetIdentitySeq(DataTableEntityMapping mapping, CreateSqlState state) { if (mapping.IdentityField == null) { throw new LightDataException(SR.NoIdentityField); } string seq; var postgreIdentity = mapping.ExtentParams.GetParamValue("PostgreIdentitySeq"); if (!string.IsNullOrEmpty(postgreIdentity)) { seq = postgreIdentity; } else { if (!state.TryGetAliasTableName(mapping, out var name)) { name = mapping.TableName; } seq = $"{name}_{mapping.IdentityField.Name}_seq"; } return(seq); }
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state) { if (mapping.IdentityField != null) { return("select @@Identity;"); } return(string.Empty); }
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state) { if (mapping.IdentityField != null) { return($"select currval('\"{GetIdentitySeq(mapping, state)}\"');"); } return(string.Empty); }
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state) { if (mapping.IdentityField != null) { return(string.Format("select currval('\"{0}\"');", GetIndentitySeq(mapping, state))); } else { return(string.Empty); } }
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state) { if (mapping.IdentityField != null) { return("select last_insert_id();"); } else { return(string.Empty); } }
public override CommandData CreateBaseInsertCommand(DataTableEntityMapping mapping, object entity, bool refresh, bool updateIdentity, CreateSqlState state) { var command = base.CreateBaseInsertCommand(mapping, entity, refresh, false, state); if (updateIdentity && mapping.IdentityField != null) { var idenSql = $"returning {CreateDataFieldSql(mapping.IdentityField.Name)}"; command.CommandText += idenSql; command.IdentitySql = true; } return(command); }
internal override string CreateSqlString (CommandFactory factory, bool isFullName, CreateSqlState state) { var sql = state.GetDataSql (this, isFullName); if (sql != null) { return sql; } var field = _baseFieldInfo.CreateSqlString (factory, isFullName, state); switch (_part) { case DatePart.Year: sql = factory.CreateYearSql (field); break; case DatePart.Month: sql = factory.CreateMonthSql (field); break; case DatePart.Day: sql = factory.CreateDaySql (field); break; case DatePart.Hour: sql = factory.CreateHourSql (field); break; case DatePart.Minute: sql = factory.CreateMinuteSql (field); break; case DatePart.Second: sql = factory.CreateSecondSql (field); break; case DatePart.Week: sql = factory.CreateWeekSql (field); break; case DatePart.DayOfWeek: sql = factory.CreateWeekDaySql (field); break; case DatePart.DayOfYear: sql = factory.CreateYearDaySql (field); break; } state.SetDataSql (this, isFullName, sql); return sql; }
public override CommandData CreateAggregateTableCommand(DataEntityMapping mapping, AggregateSelector selector, AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order, Region region, CreateSqlState state) { if (region != null && region.Start > 0) { if (order == null) { order = CreateGroupByOrderExpression(groupBy); } if (order != null) { var command = base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, null, state); command.CommandText = $"{command.CommandText} offset {region.Start} row fetch next {region.Size} rows only"; command.InnerPage = true; return(command); } } return(base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, region, state)); }
public override CommandData CreateBaseInsertCommand(DataTableEntityMapping mapping, object entity, bool refresh, bool updateIdentity, CreateSqlState state) { CommandData command = base.CreateBaseInsertCommand(mapping, entity, refresh, false, state); if (updateIdentity && mapping.IdentityField != null) { string idensql = string.Format("returning {0}", CreateDataFieldSql(mapping.IdentityField.Name)); command.CommandText = command.CommandText + idensql; command.IdentitySql = true; } return(command); }
public override CommandData CreateBatchInsertCommand(DataTableEntityMapping mapping, IList entitys, bool refresh, CreateSqlState state) { if (entitys == null || entitys.Count == 0) { throw new ArgumentNullException(nameof(entitys)); } IList <DataFieldMapping> fields = mapping.CreateFieldList; var insertLen = fields.Count; if (insertLen == 0) { throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType)); } string insertSql = null; string cacheKey = null; if (state.Seed == 0) { cacheKey = CommandCache.CreateKey(mapping, state); if (_batchInsertCache.TryGetCommand(cacheKey, out var cache)) { insertSql = cache; } } if (insertSql == null) { var insertList = new string[insertLen]; for (var i = 0; i < insertLen; i++) { var field = fields[i]; insertList[i] = CreateDataFieldSql(field.Name); } var insert = string.Join(",", insertList); insertSql = $"insert into {CreateDataTableMappingSql(mapping, state)}({insert})"; if (cacheKey != null) { _batchInsertCache.SetCommand(cacheKey, insertSql); } } var totalSql = new StringBuilder(); totalSql.AppendFormat("{0}values", insertSql); var cur = 0; var end = entitys.Count; foreach (var entity in entitys) { var valuesList = new string[insertLen]; for (var i = 0; i < insertLen; i++) { var field = fields[i]; var value = field.ToInsert(entity, refresh); valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType); } var values = string.Join(",", valuesList); totalSql.Append($"({values})"); cur++; totalSql.Append(cur < end ? ',' : ';'); } var command = new CommandData(totalSql.ToString()); return(command); }
public override CommandData CreateBatchInsertWithIdentityCommand(DataTableEntityMapping mapping, IList entitys, bool refresh, CreateSqlState state) { if (entitys == null || entitys.Count == 0) { throw new ArgumentNullException(nameof(entitys)); } IList <DataFieldMapping> fields = mapping.CreateFieldList; var insertLen = fields.Count; if (insertLen == 0) { throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType)); } if (!mapping.HasIdentity) { throw new LightDataException(string.Format(SR.NoIdentityField, mapping.ObjectType)); } string insertSql = null; string cacheKey = null; if (state.Seed == 0) { cacheKey = CommandCache.CreateKey(mapping, state); if (_batchInsertCache.TryGetCommand(cacheKey, out var cache)) { insertSql = cache; } } if (insertSql == null) { var insertList = new string[insertLen]; for (var i = 0; i < insertLen; i++) { var field = fields[i]; insertList[i] = CreateDataFieldSql(field.Name); } var insert = string.Join(",", insertList); insertSql = $"insert into {CreateDataTableMappingSql(mapping, state)}({insert})"; if (cacheKey != null) { _batchInsertCache.SetCommand(cacheKey, insertSql); } } var totalSql = new StringBuilder(); totalSql.Append("drop temporary table if exists `temptb`;create temporary table `temptb`(`id` int(11));"); foreach (var entity in entitys) { var valuesList = new string[insertLen]; for (var i = 0; i < insertLen; i++) { var field = fields[i]; var value = field.ToInsert(entity, refresh); valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType); } var values = string.Join(",", valuesList); totalSql.Append($"{insertSql}values({values});insert into `temptb`(`id`) select last_insert_id();"); } totalSql.Append("select `id` from `temptb`;"); var command = new CommandData(totalSql.ToString()); return(command); }
internal override string CreateSqlString(CommandFactory factory, bool isFullName, CreateSqlState state) { var sql = state.GetDataSql(this, isFullName); if (sql != null) { return(sql); } var field = _baseFieldInfo.CreateSqlString(factory, isFullName, state); switch (_type) { case AggregateType.COUNT: sql = factory.CreateCountSql(field, _distinct); break; case AggregateType.SUM: sql = factory.CreateSumSql(field, _distinct); break; case AggregateType.AVG: sql = factory.CreateAvgSql(field, _distinct); break; case AggregateType.MAX: sql = factory.CreateMaxSql(field); break; case AggregateType.MIN: sql = factory.CreateMinSql(field); break; } state.SetDataSql(this, isFullName, sql); return(sql); }
public override CommandData CreateSelectJoinTableBaseCommand(string customSelect, List <IJoinModel> modelList, QueryExpression query, OrderExpression order, Region region, CreateSqlState state) { CommandData command = base.CreateSelectJoinTableBaseCommand(customSelect, modelList, query, order, region, state); if (region != null) { if (region.Start == 0) { command.CommandText = string.Format("{0} limit {1}", command.CommandText, region.Size); } else { command.CommandText = string.Format("{0} limit {2} offset {1}", command.CommandText, region.Start, region.Size); } command.InnerPage = true; } return(command); }
public override CommandData CreateSelectBaseCommand(DataEntityMapping mapping, string customSelect, QueryExpression query, OrderExpression order, Region region, CreateSqlState state)//, bool distinct) { CommandData command = base.CreateSelectBaseCommand(mapping, customSelect, query, order, region, state); if (region != null) { if (region.Start == 0) { command.CommandText = string.Format("{0} limit {1}", command.CommandText, region.Size); } else { command.CommandText = string.Format("{0} limit {2} offset {1}", command.CommandText, region.Start, region.Size); } command.InnerPage = true; } return(command); }
public override CommandData CreateTruncateTableCommand(DataTableEntityMapping mapping, CreateSqlState state) { CommandData data = base.CreateTruncateTableCommand(mapping, state); if (mapping.IdentityField != null) { string restartSeq = string.Format("alter sequence \"{0}\" restart;", GetIndentitySeq(mapping, state)); data.CommandText += restartSeq; } return(data); }
/// <summary> /// Creates the sql string. /// </summary> /// <returns>The sql string.</returns> /// <param name="factory">Factory.</param> /// <param name="isFullName">If set to <c>true</c> is full name.</param> /// <param name="state">State.</param> internal virtual string CreateSqlString(CommandFactory factory, bool isFullName, CreateSqlState state) { var expressionString1 = _expression1.CreateSqlString(factory, isFullName, state); var expressionString2 = _expression2.CreateSqlString(factory, isFullName, state); return(factory.CreateConcatQueryExpressionSql(expressionString1, expressionString2, _operatorType)); }
public override CommandData CreateSelectBaseCommand(DataEntityMapping mapping, string customSelect, QueryExpression query, OrderExpression order, Region region, CreateSqlState state) //, bool distinct) { var command = base.CreateSelectBaseCommand(mapping, customSelect, query, order, region, state); if (region != null) { command.CommandText = region.Start == 0 ? $"{command.CommandText} limit {region.Size}" : $"{command.CommandText} limit {region.Start},{region.Size}"; command.InnerPage = true; } return(command); }
public override CommandData CreateAggregateTableCommand(DataEntityMapping mapping, AggregateSelector selector, AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order, Region region, CreateSqlState state) { CommandData command = base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, region, state); if (region != null) { if (region.Start == 0) { command.CommandText = string.Format("{0} limit {1}", command.CommandText, region.Size); } else { command.CommandText = string.Format("{0} limit {2} offset {1}", command.CommandText, region.Start, region.Size); } command.InnerPage = true; } return(command); }
public override CommandData CreateSelectJoinTableBaseCommand(string customSelect, List <IJoinModel> modelList, QueryExpression query, OrderExpression order, Region region, CreateSqlState state) { var command = base.CreateSelectJoinTableBaseCommand(customSelect, modelList, query, order, region, state); if (region != null) { command.CommandText = region.Start == 0 ? $"{command.CommandText} limit {region.Size}" : $"{command.CommandText} limit {region.Start},{region.Size}"; command.InnerPage = true; } return(command); }
public override CommandData CreateBatchInsertWithIdentityCommand(DataTableEntityMapping mapping, IList entitys, bool refresh, CreateSqlState state) { if (entitys == null || entitys.Count == 0) { throw new ArgumentNullException(nameof(entitys)); } int totalCount = entitys.Count; IList <DataFieldMapping> fields = mapping.CreateFieldList; int insertLen = fields.Count; if (insertLen == 0) { throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType)); } if (!mapping.HasIdentity) { throw new LightDataException(string.Format(SR.NoIdentityField, mapping.ObjectType)); } string insertSql = null; string cachekey = null; if (state.Seed == 0) { cachekey = CommandCache.CreateKey(mapping, state); if (_batchInsertCache.TryGetCommand(cachekey, out string cache)) { insertSql = cache; } } if (insertSql == null) { string[] insertList = new string[insertLen]; for (int i = 0; i < insertLen; i++) { DataFieldMapping field = fields[i]; insertList[i] = CreateDataFieldSql(field.Name); } string insert = string.Join(",", insertList); insertSql = string.Format("insert into {0}({1})", CreateDataTableMappingSql(mapping, state), insert); if (cachekey != null) { _batchInsertCache.SetCommand(cachekey, insertSql); } } StringBuilder totalSql = new StringBuilder(); totalSql.AppendFormat("{0}values", insertSql); int cur = 0; int end = entitys.Count; foreach (object entity in entitys) { string[] valuesList = new string[insertLen]; for (int i = 0; i < insertLen; i++) { DataFieldMapping field = fields[i]; object value = field.GetInsertData(entity, refresh); valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType); } string values = string.Join(",", valuesList); totalSql.AppendFormat("({0})", values); cur++; if (cur < end) { totalSql.Append(','); } else { totalSql.AppendFormat("returning {0} as id;", CreateDataFieldSql(mapping.IdentityField.Name)); } } CommandData command = new CommandData(totalSql.ToString()); return(command); }