Почему мой результат поиска FTS5 — «образ диска базы данных искажен»? ⇐ C#

[Anonymous]

Используя FTS5 в базе данных SQLite на C#, я получаю:

Код: Выделить всё

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 11: 'database disk image is malformed'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync()
at DatabaseManager.Search(String query) in E:\Documents\projects\sqlite-in-csharp\SqliteExample\DatabaseManager.cs:line 106
at Program.Program.Main(String[] args) in E:\Documents\projects\sqlite-in-csharp\SqliteExample\Program.cs:line 42
at Program.Program.(String[] args)

При проверке целостности мета-таблица оказывается чистой, но даже после перестроения поиска недействительна.
Программа. cs:

Код: Выделить всё

using Microsoft.Data.Sqlite;
using static DatabaseManager;

namespace Program
{
public struct Track
{
public string Path;
public string Artist;
public string Title;

public Track(string path, string artist, string title)
{
Path = path;
Artist = artist;
Title = title;
}
}

class Program
{
static async Task Main(string[] args)
{
// Get database connection
DatabaseManager manager = await DatabaseManager.Build();

// Insert some data
Track[] tracks = [
new Track("E:/Music/King Gizzard & the Lizard Wizard/Flying Microtonal Banana (2017-02-24)/1.1 - Rattlesnake.flac", "King Gizzard & the Lizard Wizard", "Rattlesnake"),
new Track("E:/Music/Tame Impala/Lonerism (2012-10-08)/1.8 - Keep On Lying.flac", "Tame Impala", "Keep On Lying"),
new Track("E:/Music/Tame Impala/Lonerism (2012-10-08)/1.4 - Mind Mischief.flac", "Tame Impala", "Mind Mischief"),
new Track("E:/Music/Tame Impala/Lonerism (2012-10-08)/1.3 - Apocalypse Dreams.flac", "Tame Impala", "Apocalypse Dreams"),
new Track("E:/Music/LCD Soundsystem/This Is Happening (2010-12-03)/1.1 - Dance Yrself Clean.flac", "LCD Soundsystem", "Dance Yrself Clean"),
new Track("E:/Music/LCD Soundsystem/LCD Soundsystem (2005-01-24)/1.1 - Daft Punk Is Playing at My House.flac", "LCD Soundsystem", "Daft Punk Is Playing at My House"),
new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.2 - Ophelia.flac", "The Silents", "Ophelia"),
new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.4 - Tune for a Nymph.flac", "The Silents", "Tune for a Nymph"),
new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.6 - Nightcrawl.flac", "The Silents", "Nightcrawl"),
new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.9 - See the Future.flac", "The Silents", "See the Future"),
];
await manager.InsertData(tracks);

await manager.Search("\"Tame\"");
}
}
}

DatabaseManager.cs:

Код: Выделить всё

using Microsoft.Data.Sqlite;
using Program;

public class DatabaseManager
{
private SqliteConnection Connection { get; set; }

public static async Task  Build()
{
SqliteConnection connection = new SqliteConnection("Data Source=meta.db");
await connection.OpenAsync();

DatabaseManager manager = new DatabaseManager(connection);

await manager.InitTables();

return manager;
}

private DatabaseManager(SqliteConnection connection)
{
Connection = connection;
}

private async Task InitTables()
{
using (SqliteCommand command = this.Connection.CreateCommand())
{
command.CommandText = """
CREATE TABLE IF NOT EXISTS meta (
path TEXT PRIMARY KEY NOT NULL,
artist TEXT,
title TEXT
) WITHOUT ROWID;
""";
await command.ExecuteNonQueryAsync();

command.CommandText = """
CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
path UNINDEXED,
artist,
title,
content=meta,
content_rowid=path
);
""";
await command.ExecuteNonQueryAsync();
command.CommandText = """
CREATE TRIGGER IF NOT EXISTS meta_ai AFTER INSERT ON meta BEGIN
INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
END;
CREATE TRIGGER IF NOT EXISTS meta_ad AFTER DELETE ON meta BEGIN
INSERT INTO search(search, path, artist, title) VALUES ('delete', old.path, old.artist, old.title);
END;
CREATE TRIGGER IF NOT EXISTS meta_au AFTER UPDATE ON meta BEGIN
INSERT INTO search(search, path, artist, title) VALUES ('delete', old.path, old.artist, old.title);
INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
END;
""";
await command.ExecuteNonQueryAsync();
}
}

public async Task InsertData(Track data)
{
using (SqliteCommand command = this.Connection.CreateCommand())
{
command.CommandText = "INSERT INTO meta VALUES (?1, ?2, ?3);";

command.Parameters.AddWithValue("?1", data.Path);
command.Parameters.AddWithValue("?2", data.Artist);
command.Parameters.AddWithValue("?3", data.Title);

await command.ExecuteNonQueryAsync();
}
}

public async Task InsertData(Track[] data)
{
using (SqliteCommand command = this.Connection.CreateCommand())
{
command.CommandText = "INSERT INTO meta VALUES (?1, ?2, ?3);";

var pathParameter = command.Parameters.Add("?1", SqliteType.Text);
var artistParameter = command.Parameters.Add("?2", SqliteType.Text);
var titleParameter = command.Parameters.Add("?3", SqliteType.Text);

foreach (Track track in data)
{
pathParameter.Value = track.Path;
artistParameter.Value = track.Artist;
titleParameter.Value = track.Title;

await command.ExecuteNonQueryAsync();
}
}
}

public async Task Search(string query)
{
using (SqliteCommand command = this.Connection.CreateCommand())
{
command.CommandText = "SELECT * FROM search WHERE search MATCH ?1;";
command.Parameters.AddWithValue("?1", query);

var data = await command.ExecuteReaderAsync();
while (await data.ReadAsync())
{
Console.WriteLine("Test 1");
Console.WriteLine($"{data.GetValue(0)}, {data.GetValue(1)},  {data.GetValue(2)}");
}

Console.WriteLine("Test 2");
}
}

public async Task ResetSanity()
{
using (SqliteCommand command = this.Connection.CreateCommand())
{
command.CommandText = """
INSERT INTO search(search) VALUES ('integrity-check');
INSERT INTO search(search) VALUES ('rebuild');
INSERT INTO search(search) VALUES ('integrity-check');
""";

await command.ExecuteNonQueryAsync();
}
}
}

У меня неправильный синтаксис? Что мне нужно сделать перед сопоставлением или что-то настроить в виртуальной таблице?


Подробнее здесь: https://stackoverflow.com/questions/792 ... -malformed