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)); }