SQLite FTS5 Virtual Table Default Primary Key

[0xcaff]

Setup

CREATE VIRTUAL TABLE email USING fts5(sender, title, body);

Versions

• Rust: rustc 1.26.0 (a77568041 2018-05-07)
• Diesel: diesel 1.2.0
• Database: sqlite
• Operating System linux

Feature Flags

• diesel: chrono, sqlite

Problem Description

Trying to infer the schema for the table above fails.

What are you trying to accomplish?

I'm trying to use SQLite's FTS5 with diesel.

What is the expected output?

I'd expect diesel to use the rowid field as the default PRIMARY KEY for sql tables without explicit primary keys.

The FTS5 docs:

It is an error to add types, constraints or PRIMARY KEY declarations to a CREATE VIRTUAL TABLE statement used to create an FTS5 table. [...] Like any other table with no PRIMARY KEY declaration, an FTS5 table has an implicit INTEGER PRIMARY KEY field named rowid.

What is the actual output?

[me@falcon core]$ ~/.cargo/bin/diesel migration run
Running migration 2018-05-14-200933_setup
[me@falcon core]$ ~/.cargo/bin/diesel print-schema
Diesel only supports tables with primary keys. Table email has no primary key

Related Issues

• #1112
• #1115

Workaround

Use print-schema's blacklist and exclude all tables related to the virtual table, the declare the table explicitly.

[me@falcon core]$ ~/.cargo/bin/diesel print-schema --blacklist email{,_config,_content,_data,_docsize,_idx} > src/database.rs 

table! {
	email(rowid) {
		rowid -> Integer,
		sender -> Text,
		title -> Text,
		body -> Text,
	}
}

[0xcaff]

Hm, on second thought, fts virtual tables are special enough that the workaround is probably the best way to go. Here's a minimal example of using FTS.

Here's a minimal example of using full text search:

main.rs

#[macro_use]
extern crate diesel;

mod database;

use database::email;
use diesel::prelude::*;

fn main() {
    let conn = SqliteConnection::establish("./test.sqlite").unwrap();

    let values = email::table
        .select((email::rowid, email::rank))
        .filter(email::whole_row.eq("john"))
        .first::<(i32, f32)>(&conn)
        .unwrap();

    println!("{:?}", values);
}

database.rs

table! {
    email(rowid) {
        rowid -> Integer,

        #[sql_name = "email"]
        whole_row -> Text,
        rank -> Float,
    }
}

up.sql

CREATE VIRTUAL TABLE email USING fts5(sender, title, body);

INSERT INTO email (sender, title, body) VALUES ('John Doe', 'Hello World', 'Welcome john to the land of the awesome');

Running

/usr/bin/cargo run --color=always --package diesel-fts --bin diesel-fts
   Compiling diesel-fts v0.1.0 (file:///home/me/projects/diesel-fts)
    Finished dev [unoptimized + debuginfo] target(s) in 1.39 secs
     Running `target/debug/diesel-fts`
(1, -0.000001375)

Process finished with exit code 0