#!/usr/bin/perl

# In a contentless FTS table, the columns are hidden from the schema,

# and therefore SQLite has no information to infer column types, so

# these are typed as SQLITE_NULL ... and this type conflicts with the

# constraint on the 'docid' column. So we have to explicitly type that

# column, using a CAST expression or a call to bind_param().

use strict;

BEGIN {

	$|  = 1;

	$^W = 1;

}

use lib "t/lib";

use SQLiteTest;

use Test::More;

BEGIN { requires_sqlite('3.7.9') }

BEGIN { plan skip_all => 'FTS3 is disabled for this DBD::SQLite' if !grep /ENABLE_FTS3/, DBD::SQLite::compile_options() }

use DBI qw/SQL_INTEGER/;

plan tests => 8;

use Test::NoWarnings;

my $dbh = connect_ok(RaiseError => 1, AutoCommit => 1);

# $dbh->trace(15);

my $sql = q{CREATE VIRTUAL TABLE foo USING fts4 (content="", a, b)};

ok( $dbh->do($sql), 'CREATE TABLE' );

ok($dbh->do("INSERT INTO foo(docid, a, b) VALUES(1, 'a', 'b')"),

   "insert without bind");

# The following yields a constraint error because docid is improperly typed

# $dbh->do("INSERT INTO foo(docid, a, b) VALUES(?, ?, ?)", {}, qw/2 aa bb/);

# This works, thanks to the cast expression

ok($dbh->do("INSERT INTO foo(docid, a, b) VALUES(CAST(? AS INTEGER), ?, ?)",

            {}, qw/2 aa bb/),

   "insert with bind and cast");

# This also works, thanks to the bind_param() call

my $sth = $dbh->prepare("INSERT INTO foo(docid, a, b) VALUES(?, ?, ?)");

$sth->bind_param(1, 3, SQL_INTEGER);

$sth->bind_param(2, "aaa");

$sth->bind_param(3, "bbb");

ok($sth->execute(),

   "insert with bind_param and explicit type ");

# Check that all terms were properly inserted

ok( $dbh->do("CREATE VIRTUAL TABLE foo_aux USING fts4aux(foo)"), 'FTS4AUX');

my $data = $dbh->selectcol_arrayref("select term from foo_aux where col='*'");

is_deeply ([sort @$data], [qw/a aa aaa b bb bbb/], "terms properly indexed");