View Source

Interface ISqlSyntaxProvider

Defines an SqlSyntaxProvider.

Namespace: Umbraco.Cms.Infrastructure.Persistence.SqlSyntax

Assembly: Umbraco.Infrastructure.dll

Syntax

public interface ISqlSyntaxProvider

Properties

AddColumn

Declaration

string AddColumn { get; }

Property Value

Type	Description
string

AliasRegex

Gets a regex matching aliased fields.

Declaration

Regex AliasRegex { get; }

Property Value

Type	Description
Regex

Remarks

Matches "(table.column) AS (alias)" where table, column and alias are properly escaped.

AlterColumn

Declaration

string AlterColumn { get; }

Property Value

Type	Description
string

AlterSchema

Declaration

string AlterSchema { get; }

Property Value

Type	Description
string

ConvertDateToOrderableString

Declaration

string ConvertDateToOrderableString { get; }

Property Value

Type	Description
string

ConvertDecimalToOrderableString

Declaration

string ConvertDecimalToOrderableString { get; }

Property Value

Type	Description
string

ConvertIntegerToOrderableString

Declaration

string ConvertIntegerToOrderableString { get; }

Property Value

Type	Description
string

ConvertUniqueIdentifierToString

Declaration

string ConvertUniqueIdentifierToString { get; }

Property Value

Type	Description
string

CreateConstraint

Declaration

string CreateConstraint { get; }

Property Value

Type	Description
string

CreateIndex

Declaration

string CreateIndex { get; }

Property Value

Type	Description
string

CreateSchema

Declaration

string CreateSchema { get; }

Property Value

Type	Description
string

CreateTable

Declaration

string CreateTable { get; }

Property Value

Type	Description
string

DbProvider

Declaration

string DbProvider { get; }

Property Value

Type	Description
string

DefaultIsolationLevel

Returns the default isolation level for the database

Declaration

IsolationLevel DefaultIsolationLevel { get; }

Property Value

Type	Description
IsolationLevel

DeleteConstraint

Declaration

string DeleteConstraint { get; }

Property Value

Type	Description
string

DeleteData

Declaration

string DeleteData { get; }

Property Value

Type	Description
string

DeleteDefaultConstraint

Declaration

string DeleteDefaultConstraint { get; }

Property Value

Type	Description
string

DropColumn

Declaration

string DropColumn { get; }

Property Value

Type	Description
string

DropIndex

Declaration

string DropIndex { get; }

Property Value

Type	Description
string

DropSchema

Declaration

string DropSchema { get; }

Property Value

Type	Description
string

DropTable

Declaration

string DropTable { get; }

Property Value

Type	Description
string

InsertData

Declaration

string InsertData { get; }

Property Value

Type	Description
string

Length

Declaration

string Length { get; }

Property Value

Type	Description
string

ProviderName

Declaration

string ProviderName { get; }

Property Value

Type	Description
string

RenameColumn

Declaration

string RenameColumn { get; }

Property Value

Type	Description
string

RenameTable

Declaration

string RenameTable { get; }

Property Value

Type	Description
string

ScalarMappers

Declaration

IDictionary<Type, IScalarMapper>? ScalarMappers { get; }

Property Value

Type	Description
IDictionary<Type, IScalarMapper>

Substring

Declaration

string Substring { get; }

Property Value

Type	Description
string

TruncateTable

Declaration

string TruncateTable { get; }

Property Value

Type	Description
string

UpdateData

Declaration

string UpdateData { get; }

Property Value

Type	Description
string

Methods

AlterSequences(IUmbracoDatabase)

Alters the database sequences to match the current schema requirements.

Declaration

void AlterSequences(IUmbracoDatabase database)

Parameters

Type	Name	Description
IUmbracoDatabase	database	The database connection to use for altering sequences.

Remarks

This is an optional extension point for SQL providers that support database sequences. Providers that support sequences should override this method and implement any required changes when schema updates (for example, after a migration) require sequence adjustments. Callers should typically check SupportsSequences() before invoking this method. The default implementation throws System.NotImplementedException.

AlterSequences(IUmbracoDatabase, string)

Alters the database sequences associated with the specified table for providers that support sequences.

Declaration

void AlterSequences(IUmbracoDatabase database, string tableName)

Parameters

Type	Name	Description
IUmbracoDatabase	database	The database connection to use for altering the sequences.
string	tableName	The name of the table whose sequences will be altered.

Remarks

This is an optional extension point for SQL providers that support database sequences. Providers that support sequences should override this method to update sequences associated with the specified table when schema changes require it. Callers should typically check SupportsSequences() before invoking this method. The default implementation throws System.NotImplementedException.

AppendForUpdateHint(Sql<ISqlContext>)

Appends the relevant ForUpdate hint.

Declaration

Sql<ISqlContext> AppendForUpdateHint(Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql

Returns

Type	Description
Sql<ISqlContext>

ColumnWithAlias(string, string, string)

Declaration

string ColumnWithAlias(string tableNameOrAlias, string columnName, string columnAlias = "")

Parameters

Type	Name	Description
string	tableNameOrAlias
string	columnName
string	columnAlias

Returns

Type	Description
string

ConvertIntegerToBoolean(int)

Declaration

string ConvertIntegerToBoolean(int value)

Parameters

Type	Name	Description
int	value

Returns

Type	Description
string

DoesPrimaryKeyExist(IDatabase, string, string)

Declaration

bool DoesPrimaryKeyExist(IDatabase db, string tableName, string primaryKeyName)

Parameters

Type	Name	Description
IDatabase	db
string	tableName
string	primaryKeyName

Returns

Type	Description
bool

DoesTableExist(IDatabase, string)

Declaration

bool DoesTableExist(IDatabase db, string tableName)

Parameters

Type	Name	Description
IDatabase	db
string	tableName

Returns

Type	Description
bool

EscapeString(string)

Declaration

string EscapeString(string val)

Parameters

Type	Name	Description
string	val

Returns

Type	Description
string

Format(IEnumerable<ColumnDefinition>)

Declaration

string Format(IEnumerable<ColumnDefinition> columns)

Parameters

Type	Name	Description
IEnumerable<ColumnDefinition>	columns

Returns

Type	Description
string

Format(IEnumerable<ForeignKeyDefinition>)

Declaration

List<string> Format(IEnumerable<ForeignKeyDefinition> foreignKeys)

Parameters

Type	Name	Description
IEnumerable<ForeignKeyDefinition>	foreignKeys

Returns

Type	Description
List<string>

Format(IEnumerable<IndexDefinition>)

Declaration

List<string> Format(IEnumerable<IndexDefinition> indexes)

Parameters

Type	Name	Description
IEnumerable<IndexDefinition>	indexes

Returns

Type	Description
List<string>

Format(ColumnDefinition)

Declaration

string Format(ColumnDefinition column)

Parameters

Type	Name	Description
ColumnDefinition	column

Returns

Type	Description
string

Format(ColumnDefinition, string, out IEnumerable<string>)

Declaration

string Format(ColumnDefinition column, string tableName, out IEnumerable<string> sqls)

Parameters

Type	Name	Description
ColumnDefinition	column
string	tableName
IEnumerable<string>	sqls

Returns

Type	Description
string

Format(ForeignKeyDefinition)

Declaration

string Format(ForeignKeyDefinition foreignKey)

Parameters

Type	Name	Description
ForeignKeyDefinition	foreignKey

Returns

Type	Description
string

Format(IndexDefinition)

Declaration

string Format(IndexDefinition index)

Parameters

Type	Name	Description
IndexDefinition	index

Returns

Type	Description
string

Format(TableDefinition)

Declaration

string Format(TableDefinition table)

Parameters

Type	Name	Description
TableDefinition	table

Returns

Type	Description
string

FormatColumnRename(string?, string?, string?)

Declaration

string FormatColumnRename(string? tableName, string? oldName, string? newName)

Parameters

Type	Name	Description
string	tableName
string	oldName
string	newName

Returns

Type	Description
string

FormatDateTime(DateTime, bool)

Declaration

string FormatDateTime(DateTime date, bool includeTime = true)

Parameters

Type	Name	Description
DateTime	date
bool	includeTime

Returns

Type	Description
string

FormatPrimaryKey(TableDefinition)

Declaration

string FormatPrimaryKey(TableDefinition table)

Parameters

Type	Name	Description
TableDefinition	table

Returns

Type	Description
string

FormatTableRename(string?, string?)

Declaration

string FormatTableRename(string? oldName, string? newName)

Parameters

Type	Name	Description
string	oldName
string	newName

Returns

Type	Description
string

GetColumn(DatabaseType, string, string, string?, string?, bool)

Declaration

string GetColumn(DatabaseType dbType, string tableName, string columnName, string? columnAlias, string? referenceName = null, bool forInsert = false)

Parameters

Type	Name	Description
DatabaseType	dbType
string	tableName
string	columnName
string	columnAlias
string	referenceName
bool	forInsert

Returns

Type	Description
string

GetColumnsInSchema(IDatabase)

Declaration

IEnumerable<ColumnInfo> GetColumnsInSchema(IDatabase db)

Parameters

Type	Name	Description
IDatabase	db

Returns

Type	Description
IEnumerable<ColumnInfo>

GetConcat(params string[])

Declaration

string GetConcat(params string[] args)

Parameters

Type	Name	Description
string[]	args

Returns

Type	Description
string

GetConstraintsPerColumn(IDatabase)

Returns all constraints defined in the database (Primary keys, foreign keys, unique constraints...) (does not include indexes)

Declaration

IEnumerable<Tuple<string, string, string>> GetConstraintsPerColumn(IDatabase db)

Parameters

Type	Name	Description
IDatabase	db

Returns

Type	Description
IEnumerable<Tuple<string, string, string>>	A Tuple containing: TableName, ColumnName, ConstraintName

GetConstraintsPerTable(IDatabase)

Returns all constraints defined in the database (Primary keys, foreign keys, unique constraints...) (does not include indexes)

Declaration

IEnumerable<Tuple<string, string>> GetConstraintsPerTable(IDatabase db)

Parameters

Type	Name	Description
IDatabase	db

Returns

Type	Description
IEnumerable<Tuple<string, string>>	A Tuple containing: TableName, ConstraintName

GetDefinedIndexes(IDatabase)

Returns all defined Indexes in the database excluding primary keys

Declaration

IEnumerable<Tuple<string, string, string, bool>> GetDefinedIndexes(IDatabase db)

Parameters

Type	Name	Description
IDatabase	db

Returns

Type	Description
IEnumerable<Tuple<string, string, string, bool>>	A Tuple containing: TableName, IndexName, ColumnName, IsUnique

GetFieldNameForUpdate<TDto>(Expression<Func<TDto, object?>>, string?)

Declaration

string GetFieldNameForUpdate<TDto>(Expression<Func<TDto, object?>> fieldSelector, string? tableAlias = null)

Parameters

Type	Name	Description
Expression<Func<TDto, object>>	fieldSelector
string	tableAlias

Returns

Type	Description
string

Type Parameters

Name	Description
TDto

GetIndexType(IndexTypes)

Declaration

string GetIndexType(IndexTypes indexTypes)

Parameters

Type	Name	Description
IndexTypes	indexTypes

Returns

Type	Description
string

GetNullCastSuffix<T>()

Gets the SQL type cast extension (null type annotation) associated with a null value for the specified type parameter.

Declaration

string GetNullCastSuffix<T>()

Returns

Type	Description
string	A string containing the SQL type cast extension (null type annotation) that represents a null value for type T, or an empty string if no extension is defined.

Type Parameters

Name	Description
T	The type for which to retrieve the SQL null type annotation.

Remarks

This method is useful when generating SQL queries that require explicit type casting of NULL values, such as in PostgreSQL. The returned string can be used directly in SQL statements for type-safe comparisons or assignments (for example, ::integer or ::text).

GetQuotedColumnName(string?)

Declaration

string GetQuotedColumnName(string? columnName)

Parameters

Type	Name	Description
string	columnName

Returns

Type	Description
string

GetQuotedName(string?)

Declaration

string GetQuotedName(string? name)

Parameters

Type	Name	Description
string	name

Returns

Type	Description
string

GetQuotedTableName(string?)

Declaration

string GetQuotedTableName(string? tableName)

Parameters

Type	Name	Description
string	tableName

Returns

Type	Description
string

GetQuotedValue(string)

Declaration

string GetQuotedValue(string value)

Parameters

Type	Name	Description
string	value

Returns

Type	Description
string

GetSpecialDbType(SpecialDbType)

Declaration

string GetSpecialDbType(SpecialDbType dbType)

Parameters

Type	Name	Description
SpecialDbType	dbType

Returns

Type	Description
string

GetStringColumnEqualComparison(string, int, TextColumnType)

Declaration

string GetStringColumnEqualComparison(string column, int paramIndex, TextColumnType columnType)

Parameters

Type	Name	Description
string	column
int	paramIndex
TextColumnType	columnType

Returns

Type	Description
string

GetStringColumnWildcardComparison(string, int, TextColumnType)

Declaration

string GetStringColumnWildcardComparison(string column, int paramIndex, TextColumnType columnType)

Parameters

Type	Name	Description
string	column
int	paramIndex
TextColumnType	columnType

Returns

Type	Description
string

GetTablesInSchema(IDatabase)

Declaration

IEnumerable<string> GetTablesInSchema(IDatabase db)

Parameters

Type	Name	Description
IDatabase	db

Returns

Type	Description
IEnumerable<string>

GetUpdatedDatabaseType(DatabaseType, string?)

Declaration

DatabaseType GetUpdatedDatabaseType(DatabaseType current, string? connectionString)

Parameters

Type	Name	Description
DatabaseType	current
string	connectionString

Returns

Type	Description
DatabaseType

GetWildcardConcat(string)

This ensures that GetWildcardPlaceholder() character is surronded by '' when used inside a LIKE statement. E.g. in WhereLike() extension and the defaultConcat is used.

Declaration

string GetWildcardConcat(string concatDefault = "")

Parameters

Type	Name	Description
string	concatDefault	When provided this overides the GetWildcardPlaceholder() default.

Returns

Type	Description
string

GetWildcardPlaceholder()

Declaration

string GetWildcardPlaceholder()

Returns

Type	Description
string

HandleCreateTable(IDatabase, TableDefinition, bool)

Declaration

void HandleCreateTable(IDatabase database, TableDefinition tableDefinition, bool skipKeysAndIndexes = false)

Parameters

Type	Name	Description
IDatabase	database
TableDefinition	tableDefinition
bool	skipKeysAndIndexes

InsertForUpdateHint(Sql<ISqlContext>)

Appends the relevant ForUpdate hint.

Declaration

Sql<ISqlContext> InsertForUpdateHint(Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql

Returns

Type	Description
Sql<ISqlContext>

LeftJoinWithNestedJoin<TDto>(Sql<ISqlContext>, Func<Sql<ISqlContext>, Sql<ISqlContext>>, string?)

Handles left join with nested join

Declaration

Sql<ISqlContext>.SqlJoinClause<ISqlContext> LeftJoinWithNestedJoin<TDto>(Sql<ISqlContext> sql, Func<Sql<ISqlContext>, Sql<ISqlContext>> nestedJoin, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql
Func<Sql<ISqlContext>, Sql<ISqlContext>>	nestedJoin
string	alias

Returns

Type	Description
SqlJoinClause<ISqlContext>

Type Parameters

Name	Description
TDto

OrderByGuid(string, string)

Declaration

string OrderByGuid(string tableName, string columnName)

Parameters

Type	Name	Description
string	tableName
string	columnName

Returns

Type	Description
string

SelectTop(Sql<ISqlContext>, int)

Declaration

Sql<ISqlContext> SelectTop(Sql<ISqlContext> sql, int top)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql
int	top

Returns

Type	Description
Sql<ISqlContext>

SupportsClustered()

Declaration

bool SupportsClustered()

Returns

Type	Description
bool

SupportsIdentityInsert()

Declaration

bool SupportsIdentityInsert()

Returns

Type	Description
bool

SupportsSequences()

Determines whether the current database provider supports sequence objects for generating numeric values like PostgreSQL.

Declaration

bool SupportsSequences()

Returns

Type	Description
bool	true if the provider supports sequences; otherwise, false.

TruncateConstraintName<T>(string)

Some databases have a maximum length for constraint names, this method truncates the name if necessary.

Declaration

string TruncateConstraintName<T>(string constraintName)

Parameters

Type	Name	Description
string	constraintName	unlimited name.

Returns

Type	Description
string	truncated name.

Type Parameters

Name	Description
T	type of the entity.

TryGetDefaultConstraint(IDatabase, string?, string, out string)

Tries to gets the name of the default constraint on a column.

Declaration

bool TryGetDefaultConstraint(IDatabase db, string? tableName, string columnName, out string constraintName)

Parameters

Type	Name	Description
IDatabase	db	The database.
string	tableName	The table name.
string	columnName	The column name.
string	constraintName	The constraint name.

Returns

Type	Description
bool	A value indicating whether a default constraint was found.

Remarks

Some database engines may not have names for default constraints, in which case the function may return true, but constraintName is unspecified.