public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (!String.IsNullOrWhiteSpace(Table))
{
sqlCommand.AppendCommand($"\"{Table}\".");
}
if (Column == "*")
{
sqlCommand.AppendCommand($"*");
}
else
{
sqlCommand.AppendCommand($"\"{Column}\"");
if (DotnetType == typeof(PgSqlLTree))
{
sqlCommand.AppendCommand("::text");
}
//if (!String.IsNullOrWhiteSpace(Alias))
// sqlCommand.AppendCommand($" AS \"{Alias}\"");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand($"\"{_column}\"");
if (!String.IsNullOrWhiteSpace(_alias))
{
sqlCommand.AppendCommand($" AS \"{_alias}\"");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand($"\"{_name}\"");
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand($"ADD COLUMN ");
if (!ThrowIfExists)
{
sqlCommand.AppendCommand("IF NOT EXISTS ");
}
sqlCommand.AppendCommand($"{Name.ClearString()} {DataType}");
sqlCommand.AppendCommand(";");
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand($"\"{Name}\"");
if (SortDescending)
{
sqlCommand.AppendCommand($" DESC");
}
if (InvertNullOrder)
{
sqlCommand.AppendCommand($" {(SortDescending ? "NULLS LAST" : "NULLS FIRST")}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
string num = Number.ToString();
sqlCommand.AppendCommand(num);
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
var selectCommand = _select.GetSqlCommand(tableDefinition);
sqlCommand.AppendCommand($"EXISTS ({selectCommand.Command})", selectCommand.Parameters);
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (_groupByItemList.Any())
{
sqlCommand.AppendCommand($"{String.Join(", ", _groupByItemList)}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (_fromSources.Any())
{
sqlCommand.AppendCommand($"{String.Join(", ", _fromSources.Select(fs => fs.GetSqlCommand(tableDefinition).Command))}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (_offsetClauseItem != null)
{
sqlCommand.AppendCommand($"{_offsetClauseItem}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (_columns.Any())
{
sqlCommand.AppendCommand($"{String.Join(", ", _columns.Select(c => c.GetSqlCommand(tableDefinition).Command))}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (_returningValue?.Any() == true)
{
sqlCommand.AppendCommand($"{String.Join(", ", _returningValue.Select(r => r.GetSqlCommand(tableDefinition).Command))}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (_orderByClauseItems.Any())
{
sqlCommand.AppendCommand($"{String.Join(",", _orderByClauseItems.Select(i => i.GetSqlCommand(tableDefinition).Command))}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
var values = _values.Select(val =>
{
var col = tableDefinition.GetColumnByClrName(val._key) ??
tableDefinition.GetColumnByDbName(val._key);
var expr = new PgSqlParameter(col.GetNameForDb(), val._value);
return(expr);
}).ToList();
var valuesIds = values.Select(v => v.UniqueId).ToList();
var sortedValues = new List <PgSqlParameter>();
if (tableDefinition != null)
{
foreach (var column in tableDefinition.Columns())
{
var exists = values.FirstOrDefault(v => v.ColumnName.Equals(column.GetNameForDb(), StringComparison.OrdinalIgnoreCase));
if (exists != null)
{
exists.Column = column;
sortedValues.Add(exists);
}
else
{
var defaultExpr = new PgSqlParameter(column.GetNameForDb(), "DEFAULT");
defaultExpr.Column = column;
sortedValues.Add(defaultExpr);
}
}
}
else
{
sortedValues = values;
}
var sortedValuesIds = sortedValues.Select(v => v.UniqueId).ToList();
if (_values.Any())
{
var parms = sortedValues.Where(ValuesClauseHelper.IsNotDefault);
var parmsIds = parms.Select(p => p.UniqueId);
var placeHolders = sortedValues.Select(ValuesClauseHelper.GetValuePlaceholder);
sqlCommand.AppendCommand($"{String.Join(", ", placeHolders)}", parms);
}
return(sqlCommand);
}
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();
if (_limitClauseItem != null)
{
var limit = _limitClauseItem.GetSqlCommand(tableDefinition).Command;
sqlCommand.AppendCommand($"{limit}");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (!_includeDescendants)
{
sqlCommand.AppendCommand($"ONLY ");
}
if (!String.IsNullOrWhiteSpace(_table))
{
sqlCommand.AppendCommand($"\"{_table}\".");
}
sqlCommand.AppendCommand($"\"{_column}\"");
if (!String.IsNullOrWhiteSpace(_alias))
{
sqlCommand.AppendCommand($" AS \"{_alias}\"");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (_selectListItems.Any())
{
var commands = new List <PgSqlCommand>();
foreach (var selectListItem in _selectListItems)
{
var comm = selectListItem.GetSqlCommand(tableDefinition);
commands.Add(comm);
}
sqlCommand.AppendCommand($"{String.Join(",", commands.Select(c => c.Command))}", commands.SelectMany(c => c.Parameters));
}
else
{
sqlCommand.AppendCommand("*");
}
return(sqlCommand);
}
//public override string ToString()
//{
// var strBuilder = new StringBuilder();
// if (!_includeChildTables)
// {
// strBuilder.Append("ONLY ");
// }
// if (!String.IsNullOrWhiteSpace(_schema))
// strBuilder.Append($"\"{_schema}\".");
// strBuilder.Append($"\"{_name}\"");
// if (!String.IsNullOrWhiteSpace(_alias))
// strBuilder.Append($" AS \"{_alias}\"");
// return strBuilder.ToString();
//}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (!_includeChildTables)
{
sqlCommand.AppendCommand("ONLY ");
}
if (!String.IsNullOrWhiteSpace(_schema))
{
sqlCommand.AppendCommand($"\"{_schema}\".");
}
sqlCommand.AppendCommand($"\"{_name}\"");
if (!String.IsNullOrWhiteSpace(_alias))
{
sqlCommand.AppendCommand($" AS \"{_alias}\"");
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
switch (_forType)
{
case ForType.Update:
sqlCommand.AppendCommand("UPDATE");
break;
case ForType.NoKeyUpdate:
sqlCommand.AppendCommand("NO KEY UPDATE");
break;
case ForType.Share:
sqlCommand.AppendCommand("SHARE");
break;
case ForType.KeyShare:
sqlCommand.AppendCommand("KEY SHARE");
break;
}
if (_ofTables.Any())
{
sqlCommand.AppendCommand($" OF {String.Join(",", _ofTables)}");
}
switch (_forOptions)
{
case ForOptions.None:
break;
case ForOptions.NoWait:
sqlCommand.AppendCommand(" NOWAIT");
break;
case ForOptions.SkipLocked:
sqlCommand.AppendCommand(" SKIP LOCKED");
break;
}
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();
var sortedValues = new List <PgSqlParameter>();
var columns = tableDefinition.Columns().ToList();
for (int i = 0; i < columns.Count; i++)
{
var column = columns[i];
IValueItem valueItem = null;
if (_values.Count > i)
{
valueItem = _values[i];
}
if (valueItem == null)
{
var defaultExpr = new PgSqlParameter(i.ToString(), "DEFAULT");
sortedValues.Add(defaultExpr);
}
else
{
var expr = new PgSqlParameter(i.ToString(), valueItem._value);
sortedValues.Add(expr);
}
}
if (_values.Any())
{
sqlCommand.AppendCommand($"{String.Join(", ", sortedValues.Select(ValuesClauseHelper.GetValuePlaceholder))}", sortedValues.Where(ValuesClauseHelper.IsNotDefault));
}
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);
}
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);
}
