public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (ConflictAction == "THROW")
{
return(sqlCommand);
}
sqlCommand.AppendCommand($"ON CONFLICT");
if (ConflictAction == "DO NOTHING")
{
sqlCommand.AppendCommandLine(" DO NOTHING");
return(sqlCommand);
}
sqlCommand.AppendCommandLine($" ({String.Join(", ", tableDefinition.PrimaryKeys().Select(c => c.DbName))})");
sqlCommand.AppendCommandLine(" DO UPDATE SET");
if (_valueClauses.Any())
{
foreach (var valuesClause in _valueClauses)
{
if (valuesClause is NamedValues namedValues)
{
foreach (var namedValuesValue in namedValues._values)
{
var expr = new PgSqlParameter(namedValuesValue._key, namedValuesValue._value).SetColum(tableDefinition);
sqlCommand.AppendCommandLine($"{namedValuesValue._key} = {ValuesClauseHelper.GetValuePlaceholder(expr)}", new List <PgSqlParameter>()
{
expr
});
}
}
else if (valuesClause is PositionedValues positionedValues)
{
foreach (var positionedValue in positionedValues._values)
{
var expr = new PgSqlParameter(positionedValue._key, positionedValue._value);
sqlCommand.AppendCommandLine($"{positionedValue._key} = {ValuesClauseHelper.GetValuePlaceholder(expr)}", new List <PgSqlParameter>()
{
expr
});
}
}
}
sqlCommand.Command = sqlCommand.Command.TrimEnd($",{Environment.NewLine}".ToCharArray()) + Environment.NewLine;
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommandLine($"ALTER TABLE {Name}");
foreach (var alterTableAction in _actions)
{
sqlCommand.AppendCommandLine(alterTableAction.GetSqlCommand(tableDefinition).Command);
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
var fromComm = _fromClause.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($" DELETE FROM {fromComm.Command}", fromComm.Parameters);
if (_whereClause != null)
{
var whereComm = _whereClause.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"WHERE {whereComm.Command}", whereComm.Parameters);
}
if (_returning != null)
{
sqlCommand.AppendCommand($"RETURNING {_returning.GetSqlCommand(tableDefinition).Command}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommandLine($"UPDATE {_updateDestination.GetSqlCommand(tableDefinition).Command}");
if (_set != null)
{
sqlCommand.AppendCommandLine("SET");
var setComm = _set.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommand($"{AddIntend(setComm.Command, 4)}", setComm.Parameters);
}
if (_whereClause != null)
{
var whereComm = _whereClause.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"WHERE {whereComm.Command}", whereComm.Parameters);
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
_values.ForEach(v =>
{
if (tableDefinition == null || tableDefinition.GetColumnBuilderByDbName(v._key) != null)
{
var param = new PgSqlParameter(v._key, v._value).SetColum(tableDefinition);
sqlCommand.AppendCommandLine($"\"{param.ColumnName}\" = @{param.UniqueId},", new List <PgSqlParameter>()
{
param
});
}
});
sqlCommand.Command = sqlCommand.Command.TrimEnd($",{Environment.NewLine}".ToCharArray());
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand("SELECT ");
if (_exists != null)
{
var existsCommand = _exists.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommand(existsCommand.Command, existsCommand.Parameters);
return(sqlCommand);
}
if (_distinct != null)
{
if (_distinct is bool)
{
sqlCommand.AppendCommand($"DISTINCT ");
}
else if (_distinct is string)
{
sqlCommand.AppendCommand($"DISTINCT ON ({_distinct}) ");
}
}
var selectComm = _selectList.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"{selectComm.Command}", selectComm.Parameters);
if (_from != null)
{
var fromComm = _from.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"FROM {fromComm.Command}", fromComm.Parameters);
}
if (_where != null)
{
var whereComm = _where.GetSqlCommand(tableDefinition);
if (!String.IsNullOrWhiteSpace(whereComm.Command))
{
sqlCommand.AppendCommandLine($"WHERE {whereComm.Command}", whereComm.Parameters);
}
}
if (_groupBy != null)
{
var groupByComm = _groupBy.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"GROUP BY {groupByComm.Command}");
}
if (_orderBy != null)
{
var orderByComm = _orderBy.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"ORDER BY {orderByComm.Command}");
}
if (_offset != null)
{
var offsetComm = _offset.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"OFFSET {offsetComm.Command}");
}
if (_limit != null)
{
var limitComm = _limit.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"LIMIT {limitComm.Command}");
}
if (_for != null)
{
var forComm = _for.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"FOR {forComm.Command}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
TableDefinition keyDefinition = new TableDefinition();
sqlCommand.AppendCommand($"INSERT INTO {String.Join(", ", _intoDestination.GetSqlCommand(tableDefinition).Command)}");
if (_intoColumns != null)
{
var intoColumnsComm = _intoColumns.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine($"{Environment.NewLine}\t({intoColumnsComm.Command})");
_intoColumns._columns.ForEach(c =>
{
var col = tableDefinition?.GetColumnBuilderByDbName(c._name);
if (col == null)
{
keyDefinition.AddColumn(new ColumnBuilder().SetDbName(c._name));
}
else
{
keyDefinition.AddColumn(col);
}
});
}
if (_valueClauses.Any())
{
sqlCommand.AppendCommandLine($"VALUES");
foreach (var valuesClause in _valueClauses)
{
if (valuesClause is NamedValues namedValues)
{
var valuesComm = namedValues.GetSqlCommand(keyDefinition);
sqlCommand.AppendCommandLine($"\t({valuesComm.Command}),", valuesComm.Parameters);
}
else if (valuesClause is PositionedValues)
{
var valuesComm = valuesClause.GetSqlCommand(keyDefinition);
sqlCommand.AppendCommandLine($"\t({valuesComm.Command}),", valuesComm.Parameters);
}
}
sqlCommand.Command = sqlCommand.Command.TrimEnd($",{Environment.NewLine}".ToCharArray()) + Environment.NewLine;
}
if (_onConflict != null)
{
var onConflict = _onConflict.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommandLine(onConflict.Command, onConflict.Parameters);
}
if (_returningClause != null)
{
sqlCommand.AppendCommand($"RETURNING {_returningClause.GetSqlCommand(tableDefinition).Command}");
}
return(sqlCommand);
}
