#!/usr/bin/perl
use strict;
use warnings;
use FindBin;
use DBI;
use Text::Ngram::MySQL::FullText;

my $dbh = connect_db(
	db   => 'my_database',  # この辺りを自分の環境用に書き換える
	user => 'my_id',        #
	pass => 'my_password'   #
);

print "truncate & create tables...\n";
$dbh->do(q{
	truncate table hoge
});
$dbh->do(q{
	alter table hoge drop index ngram
});

my $sth =$dbh->prepare(
	q{ INSERT INTO hoge VALUES (?,?,?) });

my $parser = Text::Ngram::MySQL::FullText->new;

my $dat_file = "$FindBin::RealBin/sample.txt";
open(my $fh, '<', $dat_file) or die $!;

my $i=1;
while (<$fh>){
	print "loading records... $i\r";
	chomp;
	my $ngram = $parser->to_fulltext( $_ );
	$sth->execute(undef, $_, $ngram);
	$i++;
}
print "\n";

# create fulltext index
print "creating fulltext index...\n";
$dbh->do(q{alter table hoge add fulltext(ngram)});

$dbh->disconnect;

sub connect_db {
	my %arg = @_;
	my($d,$db,$h) = ('mysql', $arg{db}, 'localhost');
	my $dsn = "DBI:$d:database=$db;host=$h";
	my $dbh = DBI->connect(
		$dsn,
		$arg{user},
		$arg{pass},
		{
			AutoCommit => 0,
			RaiseError => 1,
		}
	);
	$dbh->do("SET NAMES utf8");
	return $dbh;
}