public static Result Open(string filename, out Sqlite3DatabaseHandle db, int flags, IntPtr zVfs)
{
#if USE_WP8_NATIVE_SQLITE
return((Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, ""));
#else
return((Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, null));
#endif
}
private async Task <Dictionary <string, int> > InsertItemsAsync(SQLitePCL.sqlite3 db, Stream csvFile)
{
var authors = new Dictionary <string, int>();
using var fileReader = new StreamReader(csvFile);
using var csv = new CsvReader(fileReader, CultureInfo.InvariantCulture, true);
using var stmt = db.prepare("INSERT INTO Items (date, hash, topic, post, author_id, size, category, title, description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
using var fts = db.prepare("INSERT INTO Search (title, description, data_id) VALUES (?, ?, last_insert_rowid())");
csv.Configuration.RegisterClassMap <ItemRowClassMap>();
var records = csv.GetRecordsAsync <ItemRow>();
db.exec("BEGIN TRANSACTION");
await foreach (var item in records)
{
stmt.reset();
fts.reset();
var authorName = item.Author;
var authorId = GetAuthorId(authorName);
stmt.bind_text(1, item.Date);
stmt.bind_text(2, item.Hash);
stmt.bind_int(3, item.Topic);
stmt.bind_int(4, item.Post);
stmt.bind_int(5, authorId);
stmt.bind_int64(6, item.Size);
stmt.bind_int(7, item.Category);
stmt.bind_text(8, item.Title);
stmt.bind_text(9, item.Description);
stmt.step();
fts.bind_text(1, item.Title);
fts.bind_text(2, item.Description);
fts.step();
}
db.exec("COMMIT TRANSACTION");
return(authors);
int GetAuthorId(string name)
{
if (authors.TryGetValue(name, out var id))
{
return(id);
}
var newId = authors.Count + 1;
authors.Add(name, newId);
return(newId);
}
}
public static Sqlite3Statement Prepare2(Sqlite3DatabaseHandle db, string query)
{
var r = Sqlite3.sqlite3_prepare_v2(db, query, out var stmt);
if (r != 0)
{
throw new SQLiteException((Result)r, GetErrorMessageUTF8(db), sql: query);
}
return(stmt);
}
public override void Dispose()
{
lock (this)
{
if (_db != null)
{
SQLitePCL.raw.sqlite3_close(_db);
}
_db = null;
_transactionStack.Clear();
}
}
public static Sqlite3Statement Prepare2(Sqlite3DatabaseHandle db, string query)
{
Sqlite3Statement stmt = default(Sqlite3Statement);
#if USE_WP8_NATIVE_SQLITE || USE_SQLITEPCL_RAW
var r = Sqlite3.sqlite3_prepare_v2(db, query, out stmt);
#else
stmt = new Sqlite3Statement();
var r = Sqlite3.sqlite3_prepare_v2(db, query, -1, ref stmt, 0);
#endif
if (r != 0)
{
throw SQLiteException.New((Result)r, GetErrmsg(db));
}
return(stmt);
}
private static void BuildTables(SQLitePCL.sqlite3 db)
{
db.exec(
@"CREATE TABLE Categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);"
);
db.exec(
@"CREATE TABLE Items (
id INTEGER PRIMARY KEY,
date TEXT NOT NULL,
hash TEXT NOT NULL,
topic INTEGER NOT NULL,
post INTEGER NOT NULL,
author_id INTEGER NOT NULL,
size INTEGER NOT NULL,
category INTEGER NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL
);"
);
db.exec(
@"CREATE VIRTUAL TABLE Search USING fts5(title, description, data_id UNINDEXED);"
);
db.exec(
@"CREATE TABLE Authors (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL
);"
);
db.exec(
@"CREATE TABLE Metadata (
key TEXT PRIMARY KEY,
value TEXT
) WITHOUT ROWID"
);
}
private void InsertAuthors(SQLitePCL.sqlite3 db, Dictionary <string, int> authors)
{
using var stmt = db.prepare("INSERT INTO Authors (id, username) VALUES (?, ?)");
db.exec("BEGIN TRANSACTION");
foreach (var kvp in authors)
{
var user = kvp.Key;
var id = kvp.Value;
stmt.reset();
stmt.bind_int(1, id);
stmt.bind_text(2, user);
stmt.step();
}
db.exec("COMMIT TRANSACTION");
}
private async Task InsertCategories(SQLitePCL.sqlite3 db, Stream readmeFile)
{
using var reader = new StreamReader(readmeFile);
var readmeContent = await reader.ReadToEndAsync();
using var stmt = db.prepare("INSERT INTO Categories (id, name) VALUES (?, ?)");
db.exec("BEGIN TRANSACTION");
var matches = _categoryRegex.Matches(readmeContent);
foreach (Match match in matches)
{
stmt.reset();
var id = match.Groups[1].Value;
var name = match.Groups[2].Value;
stmt.bind_int(1, int.Parse(id));
stmt.bind_text(2, name);
stmt.step();
}
db.exec("COMMIT TRANSACTION");
}
public static int Connect()
{
//All these Init() ensure the linker doesn't strip assemblies we need
SQLitePCL.Batteries_V2.Init();
SQLitePCL.lib.embedded.Init();
//Sets the provider to load libe_sqlite3.so for SQLite
SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_e_sqlite3());
SQLitePCL.sqlite3 db = null;
try
{
//Open an in-memory db, and just return the result code, 0 is OK
return(SQLitePCL.raw.sqlite3_open(":memory:", out db));
}
finally
{
if (db != null)
{
db.Dispose();
}
}
}
public static ExtendedResult ExtendedErrCode(Sqlite3DatabaseHandle db)
{
return((ExtendedResult)Sqlite3.sqlite3_extended_errcode(db));
}
public static string GetErrorMessageUTF8(Sqlite3DatabaseHandle db)
{
return(Sqlite3.sqlite3_errmsg(db).utf8_to_string());
}
public static string GetErrmsg(Sqlite3DatabaseHandle db)
{
return(Sqlite3.sqlite3_errmsg(db));
}
public static Result EnableLoadExtension(Sqlite3DatabaseHandle db, int onoff)
{
return((Result)Sqlite3.sqlite3_enable_load_extension(db, onoff));
}
public static Result Close(Sqlite3DatabaseHandle db)
{
return(LibraryVersion >= MinClose2Version ? (Result)Sqlite3.sqlite3_close_v2(db) : (Result)Sqlite3.sqlite3_close(db));
}
public static long LastInsertRowid(Sqlite3DatabaseHandle db)
{
return(Sqlite3.sqlite3_last_insert_rowid(db));
}
public static int Changes(Sqlite3DatabaseHandle db)
{
return(Sqlite3.sqlite3_changes(db));
}
public static Result BusyTimeout(Sqlite3DatabaseHandle db, int milliseconds)
{
return((Result)Sqlite3.sqlite3_busy_timeout(db, milliseconds));
}
public static Result Close2(Sqlite3DatabaseHandle db)
{
return((Result)Sqlite3.sqlite3_close_v2(db));
}
public static readonly SQLiteVersion MinClose2Version = new SQLiteVersion(3007014); // 3.7.14
public static Result Open(string filename, out Sqlite3DatabaseHandle db, int flags, IntPtr zVfs)
{
return((Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, null));
}
public static Result Open(string filename, out Sqlite3DatabaseHandle db)
{
return((Result)Sqlite3.sqlite3_open(filename, out db));
}
