/// <inheritdoc/>
protected override string GetUpdateQuery(
IDbName table,
IColumnList columns,
IColumn idColumn,
object id
) =>
GetUpdateQuery(table, columns, idColumn, id, null);
/// <inheritdoc/>
protected override string GetUpdateQuery(
IDbName table,
IColumnList columns,
IColumn idColumn,
object id,
IColumn?versionColumn
)
{
// Get set list
var set = GetSetListForUpdateQuery(columns);
// Add version
AddVersionToSetList(set, versionColumn);
// Begin query
var sql =
$"UPDATE {Escape(table)} " +
$"SET {JoinList(set, false)} " +
$"WHERE {Escape(idColumn)} = {id}"
;
// Add WHERE Version
sql = AddVersionToWhere(sql, versionColumn);
// Return query
return($"{sql};");
}
/// <summary>
/// Get columns for <see cref="GetRetrieveQuery(IDbName, IColumnList, IColumn, object)"/>
/// </summary>
/// <param name="columns">ColumnList</param>
protected virtual List <string> GetColumnsForRetrieveQuery(IColumnList columns)
{
var col = new List <string>();
foreach (var column in columns)
{
col.Add(Escape(column, true));
}
return(col);
}
/// <summary>
/// Get the list of columns for a query, escaped with the alias
/// </summary>
/// <param name="client">IDbClient</param>
/// <param name="columns">IColumnList</param>
public static List <string> GetColumnsFromList(IDbClient client, IColumnList columns)
{
// Create empty list
var col = new List <string>();
// Escape and Add each column
foreach (var column in columns)
{
col.Add(client.Escape(column, true));
}
// Return list
return(col);
}
/// <inheritdoc/>
protected override string GetCreateQuery(
IDbName table,
IColumnList columns
)
{
// Get columns
var(col, par) = GetColumnsForCreateQuery(columns);
// Build and return query
return
($"INSERT INTO {Escape(table)} {JoinList(col, true)} " +
$"VALUES {JoinList(par, true)}; " +
"SELECT LASTVAL();"
);
}
/// <summary>
/// Get columns for <see cref="GetUpdateQuery(IDbName, IColumnList, IColumn, object, IColumn?)"/>
/// </summary>
/// <param name="columns">ColumnList</param>
protected virtual List <string> GetSetListForUpdateQuery(IColumnList columns)
{
var col = new List <string>();
foreach (var column in columns)
{
if (column.PropertyInfo.IsReadonly())
{
continue;
}
col.Add($"{Escape(column)} = {GetParamRef(column.ColAlias)}");
}
return(col);
}
/// <inheritdoc/>
protected override string GetRetrieveQuery(
IDbName table,
IColumnList columns,
IColumn idColumn,
object id
)
{
// Get columns
var col = GetColumnsForRetrieveQuery(columns);
// Build and return query
return
($"SELECT {JoinList(col, false)} " +
$"FROM {Escape(table)} " +
$"WHERE {Escape(idColumn)} = {id};"
);
}
/// <summary>
/// Escape and join a list of columns for a select query
/// </summary>
/// <param name="client">IDbClient</param>
/// <param name="columns">IColumnList</param>
public static string GetSelectFromList(IDbClient client, IColumnList columns)
{
// Do nothing if there are no columns
if (columns.Count == 0)
{
return(string.Empty);
}
// Escape and add each column to the select list
var select = new List <string>();
foreach (var column in columns)
{
select.Add(client.EscapeWithTable(column, true));
}
// Join without wrapping
return(client.JoinList(select, false));
}
/// <summary>
/// Convert LINQ expression property selectors to column names
/// </summary>
/// <param name="columns">Mapped entity columns</param>
/// <param name="predicates">Predicates (matched using AND)</param>
public static Maybe <IImmutableList <(IColumn column, Compare cmp, dynamic value)> > ConvertPredicatesToColumns(
IColumnList columns,
(string alias, Compare cmp, dynamic value)[] predicates
internal List <string> GetColumnsForRetrieveQueryTest(IColumnList columns) => GetColumnsForRetrieveQuery(columns);
/// <inheritdoc cref="GetRetrieveQuery{TEntity, TModel}(object)"/>
/// <param name="table">Table name</param>
/// <param name="columns">List of columns to select</param>
/// <param name="idColumn">ID column for predicate</param>
/// <param name="id">Entity ID</param>
protected abstract string GetRetrieveQuery(
IDbName table,
IColumnList columns,
IColumn idColumn,
object id
);
/// <inheritdoc cref="GetCreateQuery{TEntity}"/>
/// <param name="table">Table name</param>
/// <param name="columns">List of mapped columns</param>
protected abstract string GetCreateQuery(
IDbName table,
IColumnList columns
);
/// <summary>
/// Return a query to retrieve a list of entities that match all the specified parameters
/// </summary>
/// <param name="table">Table name</param>
/// <param name="columns">List of columns to select</param>
/// <param name="predicates">Predicates (matched using AND)</param>
protected abstract (string query, IQueryParametersDictionary param) GetQuery(
IDbName table,
IColumnList columns,
IImmutableList <(IColumn column, Compare cmp, object value)> predicates
/// <summary>
/// Get columns and parameter names for <see cref="GetCreateQuery(IDbName, IColumnList)"/>
/// </summary>
/// <param name="columns">IColumnList</param>
protected virtual (List <string> col, List <string> par) GetColumnsForCreateQuery(IColumnList columns)
{
var col = new List <string>();
var par = new List <string>();
foreach (var column in columns)
{
if (column.PropertyInfo.IsReadonly())
{
continue;
}
col.Add(Escape(column));
par.Add(GetParamRef(column.ColAlias));
}
return(col, par);
}
internal List <string> GetSetListForUpdateQueryTest(IColumnList columns) => GetSetListForUpdateQuery(columns);
public static IColumnList Add(this IColumnList columns, string columnName, SqlType columnType, SqlExpression defaultValue = null)
{
columns.Add(new ColumnInfo(columnName, columnType, defaultValue));
return(columns);
}
/// <inheritdoc/>
protected override (string query, IQueryParametersDictionary param) GetQuery(
IDbName table,
IColumnList columns,
IImmutableList <(IColumn column, Compare cmp, dynamic value)> predicates
internal (List <string> col, List <string> par) GetColumnsForCreateQueryTest(IColumnList columns) =>