public void ExecutesSQL()
{
using (var db = new Sqlite3().OpenTest())
{
Assert.IsTrue(db.Execute(@"CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO t1(name) VALUES('flibbety')"));
using (var stmt = db.Prepare("SELECT * FROM t1"))
{
Assert.AreEqual(Status.Row, stmt.Step());
Assert.AreEqual("flibbety", stmt.Columns.GetText(1));
}
}
}
private static IDatabase CreateDatabase(int length, out byte[] blob)
{
var db = new Sqlite3().OpenTest();
db.Execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, data BLOB)");
new Random().NextBytes(blob = new byte[length]);
using (var stmt = db.Prepare($"INSERT INTO t1(data) VALUES(?)"))
{
Assert.IsTrue(stmt.Bindings.SetBlob(1, blob));
Assert.AreEqual(Status.Done, stmt.Step());
}
return(db);
}
/// <summary>
/// 插入一个Album对象
/// </summary>
/// <param name="album"></param>
/// <returns></returns>
public bool Insert(Album album)
{
//int result = db.ExecuteNonQuery("replace into album values(NULL,@XId,@Title,@Subtitle,@ImagePath,@Rating,@XArtistId,@Language,@Publisher,date(@PublishDate),@MediaType,@Genre,@ListenCount,@ShareCount,@CommentCount,@LikeCount,@DiscCount,@Introduction,@LastUpdate)",
// new SQLiteParameter("@XId", album.XId),
// new SQLiteParameter("@Title", album.Title),
// new SQLiteParameter("@Subtitle", album.Subtitle),
// new SQLiteParameter("@ImagePath", album.ImagePath),
// new SQLiteParameter("@Rating", album.Rating),
// new SQLiteParameter("@XArtistId", album.XArtistId),
// new SQLiteParameter("@Language", album.Language),
// new SQLiteParameter("@Publisher", album.Publisher),
// new SQLiteParameter("@PublishDate", album.PublishDate),
// new SQLiteParameter("@MediaType", album.MediaType),
// new SQLiteParameter("@Genre", album.Genre),
// new SQLiteParameter("@ListenCount", album.ListenCount),
// new SQLiteParameter("@ShareCount", album.ShareCount),
// new SQLiteParameter("@CommentCount", album.CommentCount),
// new SQLiteParameter("@LikeCount", album.LikeCount),
// new SQLiteParameter("@DiscCount", album.DiscCount),
// new SQLiteParameter("@Introduction", album.Introduction),
// new SQLiteParameter("@LastUpdate", album.LastUpdate)
// );
int result = db.Execute("replace into album values(NULL,?,?,?,?,?,?,?,?,date(?),?,?,?,?,?,?,?,?,?)",
album.XId,
album.Title,
album.Subtitle,
album.ImagePath,
album.Rating,
album.XArtistId,
album.Language,
album.Publisher,
album.PublishDate,
album.MediaType,
album.Genre,
album.ListenCount,
album.ShareCount,
album.CommentCount,
album.LikeCount,
album.DiscCount,
album.Introduction,
album.LastUpdate
);
return(result == 1);
}
public void CallsRollback()
{
var called = false;
using (var db = new Sqlite3().OpenTest())
{
db.Hooks.Commit = commitHook;
db.Hooks.Rollback = rollbackHook;
db.Execute(string.Join(";", "BEGIN",
"CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT)",
"COMMIT"));
}
Assert.IsTrue(called);
int commitHook() => 7;
void rollbackHook() => called = true;
}
public void CallsCommit()
{
var called = false;
using (var db = new Sqlite3().OpenTest())
{
db.Hooks.Commit = commitHook;
Assert.IsTrue(db.Execute(string.Join(";", "BEGIN",
"CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT)",
"COMMIT")));
}
Assert.IsTrue(called);
int commitHook()
{
called = true; return(0);
};
}
public void CallsUpdateCallback()
{
var called = false;
using (var db = new Sqlite3().OpenTest("CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT)"))
{
db.Hooks.Update = updateHook;
Assert.IsTrue(db.Execute("INSERT INTO t1(name) VALUES('fizzbuzz')"));
}
Assert.IsTrue(called);
void updateHook(int change, string dbName, string tableName, long rowid)
{
called = true;
Assert.AreEqual("main", dbName);
Assert.AreEqual("t1", tableName);
}
}
public void LogsWALCommit()
{
var called = false;
var path = Path.GetTempFileName();
using (var db = new Sqlite3().OpenTest(path, OpenFlags.ReadWrite | OpenFlags.Create, "PRAGMA journal_mode=WAL"))
{
db.Hooks.Wal = walHook;
Assert.IsTrue(db.Execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, name TEXT)"));
}
Assert.IsTrue(called);
File.Delete(path);
int walHook(IDatabase db, string dbName, int pages)
{
called = true;
return(SQLITE_OK);
}
}
public void AddHrefToBook(long bookId, Hyperlink hyperlink)
{
DB.Execute("insert into book_href values(?,?)", bookId, AddHyperlink(hyperlink));
}