public void TestDbTypeToSqLiteStringNameResolverStringFixedLength()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("TEXT", resolver.Resolve(DbType.StringFixedLength));
}
public void TestDbTypeToSqLiteStringNameResolverDate()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("DATE", resolver.Resolve(DbType.Date));
}
public void TestDbTypeToSqLiteStringNameResolverBoolean()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("BOOLEAN", resolver.Resolve(DbType.Boolean));
}
public void TestDbTypeToSqLiteStringNameResolverAnsiString()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("TEXT", resolver.Resolve(DbType.AnsiString));
}
public void TestDbTypeToSqLiteStringNameResolverBinary()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("BLOB", resolver.Resolve(DbType.Binary));
}
public void TestDbTypeToSqLiteStringNameResolverTime()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("TIME", resolver.Resolve(DbType.Time));
}
public void TestDbTypeToSqLiteStringNameResolverInt16()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("INT", resolver.Resolve(DbType.Int16));
}
public void TestDbTypeToSqLiteStringNameResolverSingle()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("REAL", resolver.Resolve(DbType.Single));
}
public void TestDbTypeToSqLiteStringNameResolverDecimal()
{
// Setup
var resolver = new DbTypeToSqLiteStringNameResolver();
// Assert
Assert.AreEqual("DECIMAL", resolver.Resolve(DbType.Decimal));
}
/// <summary>
/// Creates a SQL Statement for insert-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsertAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
int batchSize = 1,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
var commandText = base.CreateInsertAll(builder,
tableName,
fields,
batchSize,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = (string)null;
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToSqLiteStringNameResolver().Resolve(dbType.Value);
}
}
if (identityField != null)
{
// Variables needed
var commandTexts = new List <string>();
var splitted = commandText.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
// Iterate the indexes
for (var index = 0; index < splitted.Count(); index++)
{
var line = splitted[index].Trim();
var returnValue = string.IsNullOrEmpty(databaseType) ?
"SELECT last_insert_rowid()" :
$"SELECT CAST(last_insert_rowid() AS {databaseType})";
commandTexts.Add(string.Concat(line, " ; ", returnValue, " ;"));
}
// Set the command text
commandText = commandTexts.Join(" ");
}
// Return the query
return(commandText);
}
/// <summary>
/// Creates a SQL Statement for insert operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsert(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
base.CreateInsert(builder,
tableName,
fields,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = "BIGINT";
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToSqLiteStringNameResolver().Resolve(dbType.Value);
}
}
// Set the return value
var result = identityField != null?
string.Concat($"CAST(last_insert_rowid() AS {databaseType})") :
primaryField != null?primaryField.Name.AsParameter(DbSetting) : "NULL";
builder
.Select()
.WriteText(result)
.As("Result".AsQuoted(DbSetting))
.End();
// Return the query
return(builder.GetString());
}
/// <summary>
/// Creates a SQL Statement for merge-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be merged.</param>
/// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for merge operation.</returns>
public override string CreateMergeAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers,
int batchSize = 10,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardHints(hints);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of fields cannot be null or empty.");
}
// Check the primay field
if (primaryField == null)
{
throw new PrimaryFieldNotFoundException($"SqLite is using the primary key as qualifier for (INSERT or REPLACE) operation.");
}
// Check the qualifiers
if (qualifiers?.Any() == true)
{
var others = qualifiers.Where(f => !string.Equals(f.Name, primaryField?.Name, StringComparison.OrdinalIgnoreCase));
if (others?.Any() == true)
{
throw new InvalidQualifiersException($"SqLite is using the primary key as qualifier for (INSERT or REPLACE) operation. " +
$"Consider creating 'PrimaryKey' in the {tableName} and set the 'qualifiers' to NULL.");
}
}
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Variables needed
var databaseType = "BIGINT";
// Set the return value
var result = (string)null;
// Set the type
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToSqLiteStringNameResolver().Resolve(dbType.Value);
}
}
// Clear the builder
builder.Clear();
// Iterate the indexes
for (var index = 0; index < batchSize; index++)
{
// Build the query
builder
.Insert()
.Or()
.Replace()
.Into()
.TableNameFrom(tableName, DbSetting)
.OpenParen()
.FieldsFrom(fields, DbSetting)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(fields, index, DbSetting)
.CloseParen()
.End();
// Check both primary and identity
if (identityField != null)
{
result = string.Concat($"CAST(COALESCE(last_insert_rowid(), {primaryField.Name.AsParameter(index, DbSetting)}) AS {databaseType})");
}
else
{
result = string.Concat($"CAST({primaryField.Name.AsParameter(index, DbSetting)} AS {databaseType})");
}
if (!string.IsNullOrEmpty(result))
{
// Set the result
builder
.Select()
.WriteText(result)
.As("Result".AsQuoted(DbSetting))
.End();
}
}
// Return the query
return(builder.GetString());
}
