public void TestWalCheckpoint()
{
var tmpFile = GetTempFile();
using (var db = SQLite3.Open(tmpFile))
{
db.Execute("PRAGMA journal_mode=WAL;");
// CREATE TABLE results in 2 frames check pointed and increaseses the log size by 2
// so manually do a checkpoint to reset the counters thus testing both
// sqlite3_wal_checkpoint and sqlite3_wal_checkpoint_v2.
db.Execute("CREATE TABLE foo (x int);");
db.WalCheckPoint("main");
db.Execute("INSERT INTO foo (x) VALUES (1);");
db.Execute("INSERT INTO foo (x) VALUES (2);");
db.WalCheckPoint("main", WalCheckPointMode.Full, out int logSize, out int framesCheckPointed);
Assert.Equal(2, logSize);
Assert.Equal(2, framesCheckPointed);
}
// Set autocheckpoint to 1 so that regardless of the number of
// commits, explicit checkpoints only checkpoint the last update.
var builder = SQLiteDatabaseConnectionBuilder.Create(
tmpFile,
autoCheckPointCount: 1);
using (var db = builder.Build())
{
db.Execute("INSERT INTO foo (x) VALUES (3);");
db.Execute("INSERT INTO foo (x) VALUES (4);");
db.Execute("INSERT INTO foo (x) VALUES (5);");
int logSize;
int framesCheckPointed;
db.WalCheckPoint("main", WalCheckPointMode.Passive, out logSize, out framesCheckPointed);
Assert.Equal(1, logSize);
Assert.Equal(1, framesCheckPointed);
}
builder = builder.With(autoCheckPointCount: 0);
using (var db = builder.Build())
{
db.Execute("INSERT INTO foo (x) VALUES (3);");
db.Execute("INSERT INTO foo (x) VALUES (4);");
db.Execute("INSERT INTO foo (x) VALUES (5);");
int logSize;
int framesCheckPointed;
db.WalCheckPoint("main", WalCheckPointMode.Passive, out logSize, out framesCheckPointed);
Assert.True(logSize > 1);
Assert.True(framesCheckPointed > 1);
}
raw.sqlite3__vfs__delete(null, tmpFile, 1);
}
public static void ClassInit(TestContext testContext)
{
SQLitePCL.Batteries.Init();
SQLiteDatabaseConnectionBuilder dbbuilder = SQLiteDatabaseConnectionBuilder.InMemory;
db = PrettyFn.Init(dbbuilder);
db.Execute("CREATE TABLE test (vals text, type text);");
db.Execute("INSERT INTO test VALUES ('" + int.MaxValue + "', 'int');");
db.Execute("INSERT INTO test VALUES ('" + uint.MaxValue + "', 'uint');");
db.Execute("INSERT INTO test VALUES ('" + long.MaxValue + "', 'long');");
db.Execute("INSERT INTO test VALUES ('" + ulong.MaxValue + "', 'ulong');");
db.Execute("INSERT INTO test VALUES ('" + byte.MaxValue + "', 'byte');");
db.Execute("INSERT INTO test VALUES ('" + sbyte.MaxValue + "', 'sbyte');");
db.Execute("INSERT INTO test VALUES ('" + short.MaxValue + "', 'short');");
db.Execute("INSERT INTO test VALUES ('" + ushort.MaxValue + "', 'ushort');");
db.Execute("INSERT INTO test VALUES ('" + float.MaxValue + "', 'float');");
db.Execute("INSERT INTO test VALUES ('1.7976931348623157E+308', 'double');");
db.Execute("INSERT INTO test VALUES ('" + decimal.MaxValue + "', 'decimal');");
db.Execute("INSERT INTO test VALUES ('" + char.MaxValue + "', 'char');");
db.Execute("INSERT INTO test VALUES ('P1Y2MT2H', 'isotimespan');");
db.Execute("INSERT INTO test VALUES ('02:14:18', 'dotnettimespan');");
db.Execute("INSERT INTO test VALUES (UUID(), 'guid');");
db.Execute("INSERT INTO test VALUES ('0', 'nonpositiveint');");
db.Execute("INSERT INTO test VALUES ('0', 'nonnegativeint');");
db.Execute("INSERT INTO test VALUES ('1', 'positiveint');");
db.Execute("INSERT INTO test VALUES ('-1', 'negativeint');");
db.Execute("INSERT INTO test VALUES ('true', 'bool');");
db.Execute("INSERT INTO test VALUES ('https', 'uri');");
db.Execute("INSERT INTO test VALUES ('a', 'rownum');");
db.Execute("INSERT INTO test VALUES ('b', 'rownum');");
db.Execute("INSERT INTO test VALUES ('c', 'rownum');");
}
public static SQLiteDatabaseConnection Init(SQLiteDatabaseConnectionBuilder dbbuilder)
{
return(dbbuilder
.WithScalarFunc("REGEXP", RegexIsMatch_Func)
.WithScalarFunc("ISDATETIME", DateIsValid_Func)
.WithScalarFunc("ISBOOL", IsBool_Func)
.WithScalarFunc("ISBYTE", IsByte_Func)
.WithScalarFunc("ISSBYTE", IsSbyte_Func)
.WithScalarFunc("ISSHORT", IsShort_Func)
.WithScalarFunc("ISUSHORT", IsUshort_Func)
.WithScalarFunc("ISINT", IsInt_Func)
.WithScalarFunc("ISUINT", IsUint_Func)
.WithScalarFunc("ISLONG", IsLong_Func)
.WithScalarFunc("ISULONG", IsUlong_Func)
.WithScalarFunc("ISFLOAT", IsFloat_Func)
.WithScalarFunc("ISDOUBLE", IsDouble_Func)
.WithScalarFunc("ISDECIMAL", IsDecimal_Func)
.WithScalarFunc("ISNONPOSINT", IsNonPositiveInt_Func)
.WithScalarFunc("ISNONNEGINT", IsNonNegativeInt_Func)
.WithScalarFunc("ISPOSINT", IsPositiveInt_Func)
.WithScalarFunc("ISNEGINT", IsNegativeInt_Func)
.WithScalarFunc("ISCHAR", IsChar_Func)
.WithScalarFunc("ISGUID", IsGuid_Func)
.WithScalarFunc("ISISOTIMESPAN", IsISO8610Timespan_Func)
.WithScalarFunc("ISDOTNETTIMESPAN", IsDotNetTimespan_Func)
.WithScalarFunc("ISURI", IsUri_Func)
.WithScalarFunc("DATECHK", DateCompare_Func)
.WithScalarFunc("COMPARE", CompareVals_Func)
.WithScalarFunc("UUID", GetUuid_Func)
.WithScalarFunc("ROW_NUMBER", GetRowNumber_Func)
.Build());
}
/// <summary>
/// Builds an IAsyncDatabaseConnection using the specified scheduler.
/// </summary>
/// <returns>An IAsyncDatabaseConnection using the specified scheduler.</returns>
/// <param name="This">A SQLiteDatabaseConnectionBuilder instance.</param>
/// <param name="scheduler">An RX scheduler</param>
public static IAsyncDatabaseConnection BuildAsyncDatabaseConnection(
this SQLiteDatabaseConnectionBuilder This,
IScheduler scheduler)
{
Contract.Requires(This != null);
Contract.Requires(scheduler != null);
var progressHandlerResult = new WriteLockedRef <bool>(false);
var db = This.With(progressHandler: () => progressHandlerResult.Value).Build();
return(new AsyncDatabaseConnectionImpl(db, scheduler, progressHandlerResult));
}
public void TestWithCommitHook()
{
var commits = 0;
var tmpFile = GetTempFile();
var builder =
SQLiteDatabaseConnectionBuilder.Create(tmpFile,
commitHook: () =>
{
commits++;
return(false);
});
using (var db = builder.Build())
{
db.Execute("CREATE TABLE foo (x int);");
db.Execute("INSERT INTO foo (x) VALUES (1);");
Assert.Equal(2, commits);
}
builder = builder.Without(commitHook: true);
using (var db = builder.Build())
{
db.Execute("INSERT INTO foo (x) VALUES (1);");
db.Execute("INSERT INTO foo (x) VALUES (1);");
Assert.Equal(2, commits);
}
builder = builder.With(commitHook: () => true);
using (var db = builder.Build())
{
try
{
db.Execute("INSERT INTO foo (x) VALUES (1);");
Assert.True(false, "Expected exception to be thrown");
}
catch (SQLiteException e)
{
Assert.Equal(ErrorCode.ConstraintCommitHook, e.ExtendedErrorCode);
}
var count =
db.Query("SELECT COUNT(*) from foo")
.Select(row => row.First().ToInt())
.First();
Assert.Equal(3, count);
}
raw.sqlite3__vfs__delete(null, tmpFile, 1);
}
/// <summary> /// Builds an IAsyncDatabaseConnection using the default TaskPool scheduler. /// </summary> /// <returns>An IAsyncDatabaseConnection using the default TaskPool scheduler.</returns> /// <param name="This">A SQLiteDatabaseConnectionBuilder instance.</param> public static IAsyncDatabaseConnection BuildAsyncDatabaseConnection(this SQLiteDatabaseConnectionBuilder This) => This.BuildAsyncDatabaseConnection(TaskPoolScheduler.Default);
/// <summary>
/// Opens a SQLite database.
/// </summary>
/// <param name="filename">The database filename.</param>
/// <param name="flags"><see cref="ConnectionFlags"/> used to defined if the database is readonly,
/// read/write and whether a new database file should be created if it does not already exist.</param>
/// <param name="vfs">
/// The name of the sqlite3_vfs object that defines the operating system interface
/// that the new database connection should use. If <see langword="null"/>, then
/// the default sqlite3_vfs object is used.</param>
/// <returns>A <see cref="SQLiteDatabaseConnection"/> instance.</returns>
/// <seealso href="https://sqlite.org/c3ref/open.html"/>
public static SQLiteDatabaseConnection Open(string filename, ConnectionFlags flags, string vfs) =>
SQLiteDatabaseConnectionBuilder.Create(
filename,
connectionFlags: flags,
vfs: vfs).Build();
/// <summary> /// Opens a SQLite database. /// </summary> /// <param name="filename">The database filename.</param> /// <returns>A <see cref="SQLiteDatabaseConnection"/> instance.</returns> /// <seealso href="https://sqlite.org/c3ref/open.html"/> public static SQLiteDatabaseConnection Open(string filename) => SQLiteDatabaseConnectionBuilder.Create(filename).Build();
public void TestAuthorizer()
{
var tmpFile = GetTempFile();
var builder = SQLiteDatabaseConnectionBuilder.Create(tmpFile,
authorizer: (actionCode, p0, p1, dbName, triggerOrView) =>
{
switch (actionCode)
{
// When creating a table an insert is first done.
case ActionCode.Insert:
Assert.Equal("sqlite_master", p0);
Assert.Null(p1);
Assert.Equal("main", dbName);
Assert.Null(triggerOrView);
break;
case ActionCode.CreateTable:
Assert.Equal("foo", p0);
Assert.Null(p1);
Assert.Equal("main", dbName);
Assert.Null(triggerOrView);
break;
case ActionCode.Read:
Assert.NotNull(p0);
Assert.NotNull(p1);
Assert.Equal("main", dbName);
Assert.Null(triggerOrView);
break;
}
return(AuthorizerReturnCode.Ok);
});
using (var db = builder.Build())
{
db.ExecuteAll(
@"CREATE TABLE foo (x int);
SELECT * FROM foo;
CREATE VIEW TEST_VIEW AS SELECT * FROM foo;");
}
// View authorizer
builder = builder.With(authorizer: (actionCode, p0, p1, dbName, triggerOrView) =>
{
switch (actionCode)
{
case ActionCode.Read:
Assert.NotNull(p0);
Assert.NotNull(p1);
Assert.Equal("main", dbName);
// A Hack. Goal is to prove that inner_most_trigger_or_view is not null when it is returned in the callback
if (p0 == "foo")
{
Assert.NotNull(triggerOrView);
}
break;
}
return(AuthorizerReturnCode.Ok);
});
using (var db = builder.Build())
{
db.Execute("SELECT * FROM TEST_VIEW;");
}
// Denied authorizer
builder = builder.With(authorizer: (actionCode, p0, p1, dbName, triggerOrView) => AuthorizerReturnCode.Deny);
using (var db = builder.Build())
{
try
{
db.Execute("SELECT * FROM TEST_VIEW;");
Assert.True(false);
}
catch (SQLiteException e)
{
Assert.Equal(ErrorCode.NotAuthorized, e.ErrorCode);
}
}
builder = builder.Without(authorizer: true);
using (var db = builder.Build())
{
db.Execute("SELECT * FROM TEST_VIEW;");
}
raw.sqlite3__vfs__delete(null, tmpFile, 1);
}
