Class SqlStructuredStoreBase

Shared base for the ADO.NET-backed IStructuredStore providers (MySql, Sqlite, Postgres). Holds the write/query/read members whose SQL-building logic is identical across the three dialects, expressed over DbConnection/DbCommand/ DbTransaction/DbDataReader and parameterized through the abstract hooks declared first.

Inheritance
object
SqlStructuredStoreBase
Inherited Members
Namespace: Serenity.StructuredPersistence.Infrastructure.Providers
Assembly: Serenity.StructuredPersistence.Infrastructure.Providers.Sql.dll
Syntax
public abstract class SqlStructuredStoreBase
Remarks

Genuinely dialect-specific behavior is deliberately kept OUT of this base and stays in each provider: the per-key upsert locking strategy, schema DDL construction, the derived-table trim statement shape, connection opening, and identifier/value/ordering conventions — those surface here only through the abstract hooks below, never as shared logic.

Constructors

SqlStructuredStoreBase()

Declaration
protected SqlStructuredStoreBase()

Fields

IDENTITY_COLUMN

Declaration
protected const string IDENTITY_COLUMN = "id"
Field Value
Type Description
string

POSITION_COLUMN

Declaration
protected const string POSITION_COLUMN = "srn_position"
Field Value
Type Description
string

ROW_RANK_COLUMN

Declaration
protected const string ROW_RANK_COLUMN = "srn_row_rank"
Field Value
Type Description
string

Properties

PositionLowerBoundFunction

The SQL function that clamps a window position to at least 1 in QueryWindowAroundAsync(StoreTable, IReadOnlyList<FieldFilter>, IReadOnlyList<string>, IReadOnlyList<OrderField>, FieldFilter, int, CancellationToken) — GREATEST for MySql/Postgres, max for Sqlite.

Declaration
protected abstract string PositionLowerBoundFunction { get; }
Property Value
Type Description
string

TimeoutSeconds

Connect/command timeout, in seconds, from this store's configuration.

Declaration
protected abstract int TimeoutSeconds { get; }
Property Value
Type Description
int

Methods

AddParameterValue(DbCommand, string, object)

Registers value under parameterName, applying BuildParameterKey(string)'s dialect-specific naming convention. Built from CreateParameter() + Add(object) instead of a driver-specific AddWithValue so this helper works identically for every ADO.NET provider.

Declaration
protected void AddParameterValue(DbCommand command, string parameterName, object value)
Parameters
Type Name Description
DbCommand command
string parameterName
object value

AppendOrderByClause(StringBuilder, IReadOnlyList<OrderField>)

Appends an ORDER BY clause, including the dialect's null-placement convention.

Declaration
protected abstract void AppendOrderByClause(StringBuilder sql, IReadOnlyList<OrderField> ordering)
Parameters
Type Name Description
StringBuilder sql
IReadOnlyList<OrderField> ordering

ApplyTrimIfNeededAsync(DbConnection, DbTransaction, StoreTable, StructuredTrim, CancellationToken)

Declaration
protected Task ApplyTrimIfNeededAsync(DbConnection connection, DbTransaction transaction, StoreTable table, StructuredTrim trim, CancellationToken token)
Parameters
Type Name Description
DbConnection connection
DbTransaction transaction
StoreTable table
StructuredTrim trim
CancellationToken token
Returns
Type Description
Task

BuildCollapsedSelect(StoreTable, IReadOnlyList<FieldFilter>, IReadOnlyList<string>, IReadOnlyList<OrderField>, DbCommand)

Builds the SELECT producing filtered records collapsed to the first row per distinct-by group under the ordering (via ROW_NUMBER() OVER (PARTITION BY ...)), or a plain filtered SELECT when no distinct-by fields are given.

Declaration
protected string BuildCollapsedSelect(StoreTable table, IReadOnlyList<FieldFilter> filters, IReadOnlyList<string> distinctBy, IReadOnlyList<OrderField> ordering, DbCommand command)
Parameters
Type Name Description
StoreTable table
IReadOnlyList<FieldFilter> filters
IReadOnlyList<string> distinctBy
IReadOnlyList<OrderField> ordering
DbCommand command
Returns
Type Description
string

BuildEqualityPredicate(FieldFilter, DbCommand, string)

Declaration
protected string BuildEqualityPredicate(FieldFilter targetKey, DbCommand command, string parameterName)
Parameters
Type Name Description
FieldFilter targetKey
DbCommand command
string parameterName
Returns
Type Description
string

BuildFilterPredicate(FieldFilter, DbCommand, string)

Declaration
protected string BuildFilterPredicate(FieldFilter filter, DbCommand command, string parameterName)
Parameters
Type Name Description
FieldFilter filter
DbCommand command
string parameterName
Returns
Type Description
string

BuildKeyWhereClause(StructuredRecord, IReadOnlyList<string>, DbCommand)

Declaration
protected string BuildKeyWhereClause(StructuredRecord record, IReadOnlyList<string> keyFields, DbCommand command)
Parameters
Type Name Description
StructuredRecord record
IReadOnlyList<string> keyFields
DbCommand command
Returns
Type Description
string

BuildParameterKey(string)

Builds the key a parameter is registered under, from a bare parameterName (no leading @). The SQL text this base builds always references @{parameterName} regardless of dialect; only the REGISTERED key differs — MySql and Sqlite register it "@"-prefixed, Npgsql registers it bare.

Declaration
protected abstract string BuildParameterKey(string parameterName)
Parameters
Type Name Description
string parameterName
Returns
Type Description
string

BuildWhereClause(IReadOnlyList<FieldFilter>, DbCommand, string)

Builds a parameterized WHERE clause. Null comparison values follow the in-memory engine's "Null orders lowest" semantics: Equal Null → IS NULL, GreaterThan Null → IS NOT NULL, LessThan Null → matches nothing, LessThan value → (column < value OR column IS NULL).

Declaration
protected string BuildWhereClause(IReadOnlyList<FieldFilter> filters, DbCommand command, string parameterPrefix)
Parameters
Type Name Description
IReadOnlyList<FieldFilter> filters
DbCommand command
string parameterPrefix
Returns
Type Description
string

ConvertDatabaseValue(object)

Converts a value read back from the driver into its StructuredValue equivalent.

Declaration
protected abstract StructuredValue ConvertDatabaseValue(object databaseValue)
Parameters
Type Name Description
object databaseValue
Returns
Type Description
StructuredValue

CountAsync(StoreTable, IReadOnlyList<FieldFilter>, IReadOnlyList<string>, CancellationToken)

Counts the records matching filters, collapsed to one per distinctBy group when non-empty. See IStructuredStore.CountAsync.

Declaration
public Task<long> CountAsync(StoreTable table, IReadOnlyList<FieldFilter> filters, IReadOnlyList<string> distinctBy, CancellationToken cancellationToken)
Parameters
Type Name Description
StoreTable table
IReadOnlyList<FieldFilter> filters
IReadOnlyList<string> distinctBy
CancellationToken cancellationToken
Returns
Type Description
Task<long>

CreateLinkedTimeoutTokenSource(CancellationToken)

Declaration
protected CancellationTokenSource CreateLinkedTimeoutTokenSource(CancellationToken cancellationToken)
Parameters
Type Name Description
CancellationToken cancellationToken
Returns
Type Description
CancellationTokenSource

DeleteByQueryAsync(StoreTable, IReadOnlyList<FieldFilter>, CancellationToken)

Deletes every record matching filters. See IStructuredStore.DeleteByQueryAsync.

Declaration
public Task DeleteByQueryAsync(StoreTable table, IReadOnlyList<FieldFilter> filters, CancellationToken cancellationToken)
Parameters
Type Name Description
StoreTable table
IReadOnlyList<FieldFilter> filters
CancellationToken cancellationToken
Returns
Type Description
Task

ExecuteTrimAsync(DbConnection, DbTransaction, StoreTable, IReadOnlyList<FieldFilter>, IReadOnlyList<OrderField>, int, CancellationToken)

Deletes every in-scope row beyond the best keepCount. Each dialect needs its own statement shape (MySql, for one, cannot reference a DELETE's own target table in a plain subquery).

Declaration
protected abstract Task ExecuteTrimAsync(DbConnection connection, DbTransaction transaction, StoreTable table, IReadOnlyList<FieldFilter> scopeFilters, IReadOnlyList<OrderField> ordering, int keepCount, CancellationToken token)
Parameters
Type Name Description
DbConnection connection
DbTransaction transaction
StoreTable table
IReadOnlyList<FieldFilter> scopeFilters
IReadOnlyList<OrderField> ordering
int keepCount
CancellationToken token
Returns
Type Description
Task

ExecuteWithFailureHandlingAsync<TResult>(Func<CancellationToken, Task<TResult>>, string, StoreTable, CancellationToken)

Runs operation under this store's failure-handling contract (timeout, logging, exception wrapping). Each provider implements this independently because SQLite's synchronous driver needs a thread-pool offload that MySql/Postgres do not.

Declaration
protected abstract Task<TResult> ExecuteWithFailureHandlingAsync<TResult>(Func<CancellationToken, Task<TResult>> operation, string operationName, StoreTable table, CancellationToken callerCancellationToken)
Parameters
Type Name Description
Func<CancellationToken, Task<TResult>> operation
string operationName
StoreTable table
CancellationToken callerCancellationToken
Returns
Type Description
Task<TResult>
Type Parameters
Name Description
TResult

InsertAsync(StoreTable, StructuredRecord, StructuredTrim, CancellationToken)

Inserts record as a new row, optionally trimming the table down to trim's keep-count afterward. See IStructuredStore.InsertAsync.

Declaration
public Task InsertAsync(StoreTable table, StructuredRecord record, StructuredTrim trim, CancellationToken cancellationToken)
Parameters
Type Name Description
StoreTable table
StructuredRecord record
StructuredTrim trim
CancellationToken cancellationToken
Returns
Type Description
Task

InsertRecordAsync(DbConnection, DbTransaction, StoreTable, StructuredRecord, CancellationToken)

Declaration
protected Task InsertRecordAsync(DbConnection connection, DbTransaction transaction, StoreTable table, StructuredRecord record, CancellationToken token)
Parameters
Type Name Description
DbConnection connection
DbTransaction transaction
StoreTable table
StructuredRecord record
CancellationToken token
Returns
Type Description
Task

JoinQuoted(IReadOnlyList<string>)

Declaration
protected string JoinQuoted(IReadOnlyList<string> identifiers)
Parameters
Type Name Description
IReadOnlyList<string> identifiers
Returns
Type Description
string

OpenConnectionAsync(CancellationToken)

Opens and returns a ready-to-use connection, applying any dialect-specific setup (pragmas, directory creation, etc.).

Declaration
protected abstract Task<DbConnection> OpenConnectionAsync(CancellationToken token)
Parameters
Type Name Description
CancellationToken token
Returns
Type Description
Task<DbConnection>

QueryWindowAroundAsync(StoreTable, IReadOnlyList<FieldFilter>, IReadOnlyList<string>, IReadOnlyList<OrderField>, FieldFilter, int, CancellationToken)

Returns the records within radius positions of the record matching targetKey. See IPositionalStructuredStore.QueryWindowAroundAsync.

Declaration
public Task<IReadOnlyList<StructuredRecord>> QueryWindowAroundAsync(StoreTable table, IReadOnlyList<FieldFilter> scope, IReadOnlyList<string> distinctBy, IReadOnlyList<OrderField> ordering, FieldFilter targetKey, int radius, CancellationToken cancellationToken)
Parameters
Type Name Description
StoreTable table
IReadOnlyList<FieldFilter> scope
IReadOnlyList<string> distinctBy
IReadOnlyList<OrderField> ordering
FieldFilter targetKey
int radius
CancellationToken cancellationToken
Returns
Type Description
Task<IReadOnlyList<StructuredRecord>>

Quote(string)

Wraps identifier in the dialect's identifier-quoting syntax.

Declaration
protected abstract string Quote(string identifier)
Parameters
Type Name Description
string identifier
Returns
Type Description
string

ReadRecordsAsync(DbCommand, CancellationToken)

Declaration
protected Task<IReadOnlyList<StructuredRecord>> ReadRecordsAsync(DbCommand command, CancellationToken token)
Parameters
Type Name Description
DbCommand command
CancellationToken token
Returns
Type Description
Task<IReadOnlyList<StructuredRecord>>

RejectNonFiniteComparisonValue(StructuredRecord, string)

Declaration
protected void RejectNonFiniteComparisonValue(StructuredRecord record, string comparisonField)
Parameters
Type Name Description
StructuredRecord record
string comparisonField

SignedPosition(string)

Wraps a window position so it can take part in signed arithmetic in QueryWindowAroundAsync(StoreTable, IReadOnlyList<FieldFilter>, IReadOnlyList<string>, IReadOnlyList<OrderField>, FieldFilter, int, CancellationToken).

Declaration
protected virtual string SignedPosition(string positionExpression)
Parameters
Type Name Description
string positionExpression
Returns
Type Description
string
Remarks

MySQL's ROW_NUMBER() yields BIGINT UNSIGNED, where subtracting past zero raises "value is out of range" instead of producing the negative that PositionLowerBoundFunction exists to clamp — the clamp never gets to run. Postgres returns a signed bigint and Sqlite is dynamically typed, so both leave the expression untouched.

TrimBeyondAsync(StoreTable, IReadOnlyList<FieldFilter>, IReadOnlyList<OrderField>, int, CancellationToken)

Within the records matching scopeFilters, keeps only the best keepCount — in ordering order — deleting the rest. See IStructuredStore.TrimBeyondAsync.

Declaration
public Task TrimBeyondAsync(StoreTable table, IReadOnlyList<FieldFilter> scopeFilters, IReadOnlyList<OrderField> ordering, int keepCount, CancellationToken cancellationToken)
Parameters
Type Name Description
StoreTable table
IReadOnlyList<FieldFilter> scopeFilters
IReadOnlyList<OrderField> ordering
int keepCount
CancellationToken cancellationToken
Returns
Type Description
Task

UpdateRecordFieldsAsync(DbConnection, DbTransaction, StoreTable, StructuredRecord, long, CancellationToken)

Declaration
protected Task UpdateRecordFieldsAsync(DbConnection connection, DbTransaction transaction, StoreTable table, StructuredRecord record, long existingId, CancellationToken token)
Parameters
Type Name Description
DbConnection connection
DbTransaction transaction
StoreTable table
StructuredRecord record
long existingId
CancellationToken token
Returns
Type Description
Task