Using FTS5 in an SQLite database in C# I get:
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.<Main>(String[] args)
On integrity check the meta table comes up clean, but even after rebuilding search is invalid.
Program.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<DatabaseManager> 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();
}
}
}
Is my syntax wrong? Is there something I have to do before matching, or something I have to configure in the virtual table?
Using FTS5 in an SQLite database in C# I get:
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.<Main>(String[] args)
On integrity check the meta table comes up clean, but even after rebuilding search is invalid.
Program.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<DatabaseManager> 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();
}
}
}
Is my syntax wrong? Is there something I have to do before matching, or something I have to configure in the virtual table?
Share Improve this question edited Nov 18, 2024 at 23:45 user4157124 2,99614 gold badges31 silver badges46 bronze badges asked Nov 18, 2024 at 20:03 ThreeRoundedSquaresThreeRoundedSquares 11 silver badge2 bronze badges1 Answer
Reset to default 0As Richard Hipp from the SQLite forum has told me:
You cannot use a WITHOUT ROWID table as an external content table. The external content table must be a rowid table. The "content_rowid=" argument to FTS5 must refer to a column of type INTEGER PRIMARY KEY. If you make the "content_rowid=" refer to a TEXT PRIMARY KEY column, it won't work.
Removing the WITHOUT ROWID and content_rowid=path fixes the issue.