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