Text search

Content:

  1. Introduction
  2. The problem
  3. Relational database
    1. Do It Yourself
    2. MySQL
    3. PostgreSQL
  4. Solr
    1. Background
    2. Client API
    3. HTPP POST
    4. HTPP GET

Introduction

The problem:

First question: why isn't an ordinary VARCHAR or TEXT field with index in a relational database good enough for text search?

Let us look at some big O characteristics. 

N1 = number of rows
N2 = average field length
tree based index
operation big O for number of rows big O for average field length
SELECT ... WHERE f = 'xxx' O(log(N1)) not relevant
SELECT ... WHERE f LIKE 'xxx%' O(log(N1)) O(1)
SELECT ... WHERE f LIKE '%xxx' O(N1) O(1)
SELECT ... WHERE f LIKE '%xxx%' O(N1) O(N2)

The conclusion is obvious: this approach only works in case of many rows and long text fields if one is searching for the start of the text .

Relational database:

Most relational databases has a "full text search" capability to handle this problem.

Do It Yourself:

So to elaborate on previous section.

doctbl:

docid doctxt
1 This is a test.
2 This is another test. 
SELECT docid,doctxt FROM doctbl WHERE doctxt LIKE '%another%'

is problamtic performance wise - even with index on doctbl.doctxt.

doctbl:

docid doctxt
1 This is a test.
2 This is another test.

wtbl:

docid w
1 This
1 is
1 a
1 test
2 This
2 is
2 another
2 test 
SELECT docid,doctxt FROM doctbl WHERE docid IN (SELECT docid FROM wtbl WHERE w = 'another')

works fine with index on wtbl.w.

This can be done explicit in the application. Let us see a demo. We will only demo in Java as the focus of this article is how this can be done implicit without writing a lot of code.

Load:

Definition: 

CREATE TABLE co2 (id INTEGER NOT NULL,
                  names VARCHAR(255),
                  moves TEXT, PRIMARY KEY(id))
CREATE TABLE co2_names_moves (id INTEGER NOT NULL, w VARCHAR(32) NOT NULL,PRIMARY KEY(id,w),INDEX nmix (w)) 
CREATE TABLE co2_names (id INTEGER NOT NULL, w VARCHAR(32) NOT NULL,PRIMARY KEY(id,w),INDEX nix (w)) 
CREATE TABLE co2_moves (id INTEGER NOT NULL, w VARCHAR(32) NOT NULL,PRIMARY KEY(id,w),INDEX mix (w))

Data load: 

INSERT INTO co2 VALUES(?,?,?)
INSERT INTO co2_names_moves VALUES(?,?)
INSERT INTO co2_names VALUES(?,?)
INSERT INTO co2_moves VALUES(?,?)

package textsearch.rdbms.diy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class Load {
    private static void insert(PreparedStatement pstmt, PreparedStatement pstmt_nm, PreparedStatement pstmt_n, PreparedStatement pstmt_m,
                               int id, String names, String moves) throws SQLException {
        pstmt.setInt(1,  id);
        pstmt.setString(2, names);
        pstmt.setString(3, moves);
        pstmt.executeUpdate();
        for(String w : names.split("[^A-Za-z0-9]")) {
            if(w.length() > 1) {
                pstmt_nm.setInt(1, id);
                pstmt_nm.setString(2, w);
                pstmt_nm.executeUpdate();
                pstmt_n.setInt(1, id);
                pstmt_n.setString(2, w);
                pstmt_n.executeUpdate();
            }
        }
        for(String w : moves.split("[^A-Za-z0-9]")) {
            if(w.length() > 1) {
                pstmt_nm.setInt(1, id);
                pstmt_nm.setString(2, w);
                pstmt_nm.executeUpdate();
                pstmt_m.setInt(1, id);
                pstmt_m.setString(2, w);
                pstmt_m.executeUpdate();
            }
        }
    }
    public static void main(String[] args) throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "arne", "hemmeligt")) {
            try(Statement stmt = con.createStatement()) {
                stmt.executeUpdate("DROP TABLE co2");
                stmt.executeUpdate("DROP TABLE co2_names_moves");
                stmt.executeUpdate("DROP TABLE co2_names");
                stmt.executeUpdate("DROP TABLE co2_moves");
                stmt.executeUpdate("CREATE TABLE co2 (id INTEGER NOT NULL, " +
                                                     "names VARCHAR(255), " +
                                                     "moves TEXT, PRIMARY KEY(id))");
                stmt.executeUpdate("CREATE TABLE co2_names_moves (id INTEGER NOT NULL, w VARCHAR(32) NOT NULL,PRIMARY KEY(id,w),INDEX nmix (w))"); 
                stmt.executeUpdate("CREATE TABLE co2_names (id INTEGER NOT NULL, w VARCHAR(32) NOT NULL,PRIMARY KEY(id,w),INDEX nix (w))"); 
                stmt.executeUpdate("CREATE TABLE co2_moves (id INTEGER NOT NULL, w VARCHAR(32) NOT NULL,PRIMARY KEY(id,w),INDEX mix (w))"); 
                try(PreparedStatement pstmt = con.prepareStatement("INSERT INTO co2 VALUES(?,?,?)");
                    PreparedStatement pstmt_nm = con.prepareStatement("INSERT INTO co2_names_moves VALUES(?,?)");
                    PreparedStatement pstmt_n = con.prepareStatement("INSERT INTO co2_names VALUES(?,?)");
                    PreparedStatement pstmt_m = con.prepareStatement("INSERT INTO co2_moves VALUES(?,?)")) {
                    insert(pstmt, pstmt_nm, pstmt_n, pstmt_m, 1, "Spanish defense, Ruy Lopez", "1. e4, e5, 2. Nf3, Nc6, 3. Bb5,");
                    insert(pstmt, pstmt_nm, pstmt_n, pstmt_m, 2, "French defense", "1. e4, e6, 2. d4, d5,");
                    insert(pstmt, pstmt_nm, pstmt_n, pstmt_m, 3, "Sicilian defense", "1. e4, c5,");
                    insert(pstmt, pstmt_nm, pstmt_n, pstmt_m, 4, "Queens gambit", "1. d4, d5, 2. c4,");
                    insert(pstmt, pstmt_nm, pstmt_n, pstmt_m, 5, "Dutch defense", "1. d4, f5,");
                    insert(pstmt, pstmt_nm, pstmt_n, pstmt_m, 6, "Reti opening", "1. Nf3, d5, 2. c4,");
                }
            }
        }
    }
}

Search:

Query examples: 


SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names WHERE w = ?)
SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_moves WHERE w = ?)
SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?)
SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?) OR id IN (SELECT id FROM co2_names_moves WHERE w = ?)
SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?) AND id IN (SELECT id FROM co2_names_moves WHERE w = ?)
SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?) AND id NOT IN (SELECT id FROM co2_names_moves WHERE w = ?)
SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w LIKE ?)

package textsearch.rdbms.diy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Search {
    private static void dump0(PreparedStatement pstmt, String searchtype) throws SQLException {
        System.out.printf("%s\n", searchtype);
        try(ResultSet rs  = pstmt.executeQuery()) {
            while(rs.next()) {
                String names = rs.getString(1);
                String moves = rs.getString(2);
                System.out.printf("  %s - %s\n", names, moves);
            }
        }
        
    }
    private static void dump1(PreparedStatement pstmt, String searchtype, String searchterm) throws SQLException {
        System.out.printf("%s search for : %s\n", searchtype, searchterm);
        pstmt.setString(1, searchterm);
        try(ResultSet rs  = pstmt.executeQuery()) {
            while(rs.next()) {
                String names = rs.getString(1);
                String moves = rs.getString(2);
                System.out.printf("  %s - %s\n", names, moves);
            }
        }
    }
    private static void dump2(PreparedStatement pstmt, String searchtype, String searchterm1, String op, String searchterm2) throws SQLException {
        System.out.printf("%s search for : %s %s %s\n", searchtype, searchterm1, op, searchterm2);
        pstmt.setString(1, searchterm1);
        pstmt.setString(2, searchterm2);
        try(ResultSet rs  = pstmt.executeQuery()) {
            while(rs.next()) {
                String names = rs.getString(1);
                String moves = rs.getString(2);
                System.out.printf("  %s - %s\n", names, moves);
            }
        }
    }
    public static void main(String[] args) throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "arne", "hemmeligt")) {
            // all
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2")) {
                dump0(pstmt, "all");
            }
            // one
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names WHERE w = ?)")) {
                dump1(pstmt, "one", "defense");
            }
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_moves WHERE w = ?)")) {
                dump1(pstmt, "one", "d4");
            }
            // dual
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?)")) {
                dump1(pstmt, "dual", "defense");
                dump1(pstmt, "dual", "d4");
            }
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?) OR id IN (SELECT id FROM co2_names_moves WHERE w = ?)")) {
                dump2(pstmt, "dual boolean", "defense", "OR", "d4"); // or
            }
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?) AND id IN (SELECT id FROM co2_names_moves WHERE w = ?)")) {
                dump2(pstmt, "dual boolean", "defense", "AND", "d4"); // and
            }
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w = ?) AND id NOT IN (SELECT id FROM co2_names_moves WHERE w = ?)")) {
                dump2(pstmt, "dual boolean", "defense", "NOT", "d4"); // not
            }
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM co2 WHERE id IN (SELECT id FROM co2_names_moves WHERE w LIKE ?)")) {
                dump1(pstmt, "dual boolean", "def%"); // wildcard
                dump1(pstmt, "dual boolean", "N%"); // wildcard
            }
        }
    }
}

MySQL:

MySQL supports full text search.

Load:

Definition: 

CREATE TABLE chessopening (id INTEGER NOT NULL,
                           names VARCHAR(255),
                           moves TEXT, PRIMARY KEY(id),
                           FULLTEXT INDEX(names,moves),
                           FULLTEXT INDEX(names),
                           FULLTEXT INDEX(moves))

Data load: 

INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')
INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')
INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')
INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')
INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')
INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')

package textsearch.rdbms.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * Must set:
 *   innodb_ft_min_token_size=2
 *   ft_min_word_len=2
 * as default is 3 and 4
 */
public class Load {
    public static void main(String[] args) throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "arne", "hemmeligt")) {
            try(Statement stmt = con.createStatement()) {
                stmt.executeUpdate("DROP TABLE chessopening");
                stmt.executeUpdate("CREATE TABLE chessopening (id INTEGER NOT NULL, " +
                                                              "names VARCHAR(255), " +
                                                              "moves TEXT, PRIMARY KEY(id), " +
                                                              "FULLTEXT INDEX(names,moves), " +
                                                              "FULLTEXT INDEX(names), " +
                                                              "FULLTEXT INDEX(moves))");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')");
            }
        }
    }
}

using System;
using System.Data;

using MySql.Data.MySqlClient;

namespace TextSearch.Rdbms.MySQL.Load
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (IDbConnection con = new MySqlConnection("Server=localhost;Database=test;User Id=arne;Password=hemmeligt"))
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "DROP TABLE chessopening";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE TABLE chessopening (id INTEGER NOT NULL, " +
                                                                 "names VARCHAR(255), " +
                                                                 "moves TEXT, PRIMARY KEY(id), " +
                                                                 "FULLTEXT INDEX(names,moves), " +
                                                                 "FULLTEXT INDEX(names), " +
                                                                 "FULLTEXT INDEX(moves))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

import pymysql

with pymysql.connect(host='localhost',user='arne',password='hemmeligt',db='Test') as con:
    c = con.cursor()
    c.execute('DROP TABLE chessopening')
    c.execute('CREATE TABLE chessopening (id INTEGER NOT NULL, ' +
                                                  'names VARCHAR(255), ' +
                                                  'moves TEXT, PRIMARY KEY(id), ' +
                                                  'FULLTEXT INDEX(names,moves), ' +
                                                  'FULLTEXT INDEX(names), ' +
                                                  'FULLTEXT INDEX(moves))')
    c.execute("INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')")
    c.execute("INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')")
    c.execute("INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')")
    c.execute("INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')")
    c.execute("INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')")
    c.execute("INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')")
    c.close()
    con.commit()

<?php

$con = new PDO('mysql:host=localhost;dbname=Test', 'arne', 'hemmeligt');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$con->query('DROP TABLE chessopening');
$con->query('CREATE TABLE chessopening (id INTEGER NOT NULL, ' .
                                       'names VARCHAR(255), ' .
                                       'moves TEXT, PRIMARY KEY(id), ' .
                                       'FULLTEXT INDEX(names,moves), ' .
                                       'FULLTEXT INDEX(names), ' .
                                       'FULLTEXT INDEX(moves))');
$con->query("INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')");
$con->query("INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')");
$con->query("INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')");
$con->query("INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')");
$con->query("INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')");
$con->query("INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')");
    
?>

Search:

Query examples: 

SELECT names, moves FROM chessopening WHERE MATCH(names) AGAINST (?)
SELECT names, moves FROM chessopening WHERE MATCH(moves) AGAINST (?)
SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (?)
SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (? IN BOOLEAN MODE)

package textsearch.rdbms.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Search {
    private static void dump0(PreparedStatement pstmt, String searchtype) throws SQLException {
        System.out.printf("%s\n", searchtype);
        try(ResultSet rs  = pstmt.executeQuery()) {
            while(rs.next()) {
                String names = rs.getString(1);
                String moves = rs.getString(2);
                System.out.printf("  %s - %s\n", names, moves);
            }
        }
        
    }
    private static void dump1(PreparedStatement pstmt, String searchtype, String searchterm) throws SQLException {
        System.out.printf("%s search for : %s\n", searchtype, searchterm);
        pstmt.setString(1, searchterm);
        try(ResultSet rs  = pstmt.executeQuery()) {
            while(rs.next()) {
                String names = rs.getString(1);
                String moves = rs.getString(2);
                System.out.printf("  %s - %s\n", names, moves);
            }
        }
    }
    public static void main(String[] args) throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "arne", "hemmeligt")) {
            // all
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening")) {
                dump0(pstmt, "all");
            }
            // one
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening WHERE MATCH(names) AGAINST (?)")) {
                dump1(pstmt, "one natural", "defense");
            }
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening WHERE MATCH(moves) AGAINST (?)")) {
                dump1(pstmt, "one natural", "d4");
            }
            // dual
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (?)")) {
                dump1(pstmt, "dual natural", "defense");
                dump1(pstmt, "dual natural", "d4");
                dump1(pstmt, "dual natural", "defense d4"); // implicit or
            }
            // dual boolean
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (? IN BOOLEAN MODE)")) {
                dump1(pstmt, "dual boolean", "defense");
                dump1(pstmt, "dual boolean", "d4");
                dump1(pstmt, "dual boolean", "defense d4"); // implicit or
                dump1(pstmt, "dual boolean", "+defense+d4"); // and
                dump1(pstmt, "dual boolean", "+defense-d4"); // not
                dump1(pstmt, "dual boolean", "def*"); // wildcard
                dump1(pstmt, "dual boolean", "N*"); // wildcard
            }
        }
    }
}

using System;
using System.Data;

using MySql.Data.MySqlClient;

namespace TextSearch.Rdbms.MySQL.Search
{
    public class Program
    {
        private static void Dump0(IDbCommand cmd, string searchtype)
        {
            Console.WriteLine("{0}", searchtype);
            using(IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    Console.WriteLine("  {0} - {1}", (string)dr["names"], (string)dr["moves"]);
                }
            }
        }
        private static void Dump1(IDbCommand cmd, string searchtype, string searchterm )
        {
            Console.WriteLine("{0} search for : {1}", searchtype, searchterm);
            ((IDbDataParameter)cmd.Parameters["@st"]).Value = searchterm;
            using (IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    Console.WriteLine("  {0} - {1}", (string)dr["names"], (string)dr["moves"]);
                }
            }
        }
        public static void Main(string[] args)
        {
            using(IDbConnection con = new MySqlConnection("Server=localhost;Database=test;User Id=arne;Password=hemmeligt"))
            {
                con.Open();
                // all
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names,moves FROM chessopening";
                    Dump0(cmd, "all");
                }
                // one
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names,moves FROM chessopening WHERE MATCH(names) AGAINST (@st)";
                    IDbDataParameter st = cmd.CreateParameter();
                    st.ParameterName = "@st";
                    st.DbType = DbType.String;
                    cmd.Parameters.Add(st);
                    Dump1(cmd, "one", "defense");
                }
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names, moves FROM chessopening WHERE MATCH(moves) AGAINST (@st)";
                    IDbDataParameter st = cmd.CreateParameter();
                    st.ParameterName = "@st";
                    st.DbType = DbType.String;
                    cmd.Parameters.Add(st);
                    Dump1(cmd, "one natural", "d4");
                }
                // dual
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (@st)";
                    IDbDataParameter st = cmd.CreateParameter();
                    st.ParameterName = "@st";
                    st.DbType = DbType.String;
                    cmd.Parameters.Add(st);
                    Dump1(cmd, "dual natural", "defense");
                    Dump1(cmd, "dual natural", "d4");
                    Dump1(cmd, "dual natural", "defense d4"); // implicit or
                }
                // dual boolean
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (@st IN BOOLEAN MODE)";
                    IDbDataParameter st = cmd.CreateParameter();
                    st.ParameterName = "@st";
                    st.DbType = DbType.String;
                    cmd.Parameters.Add(st);
                    Dump1(cmd, "dual boolean", "defense");
                    Dump1(cmd, "dual boolean", "d4");
                    Dump1(cmd, "dual boolean", "defense d4"); // implicit or
                    Dump1(cmd, "dual boolean", "+defense+d4"); // and
                    Dump1(cmd, "dual boolean", "+defense-d4"); // not
                    Dump1(cmd, "dual boolean", "def*"); // wildcard
                    Dump1(cmd, "dual boolean", "N*"); // wildcard
                }
            }
        }
    }
}

import pymysql

def dump0(con, searchtype, sqlstr):
    print('%s' % (searchtype))
    c = con.cursor()
    c.execute(sqlstr)
    for row in c.fetchall():
        print('  %s - %s' % (row[0], row[1]))
    c.close()

def dump1(con, searchtype, sqlstr, searchterm):
    print('%s search for : %s' % (searchtype, searchterm))
    c = con.cursor()
    c.execute(sqlstr, (searchterm,))
    for row in c.fetchall():
        print('  %s - %s' % (row[0], row[1]))
    c.close()

with pymysql.connect(host='localhost',user='arne',password='hemmeligt',db='Test') as con:
    # all
    dump0(con, 'all', 'SELECT names, moves FROM chessopening')
    # one
    dump1(con, 'one natural', 'SELECT names, moves FROM chessopening WHERE MATCH(names) AGAINST (%s)', 'defense')
    dump1(con, 'one natural', 'SELECT names, moves FROM chessopening WHERE MATCH(moves) AGAINST (%s)', 'd4')
    # dual
    dump1(con, 'dual natural', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s)', 'defense')
    dump1(con, 'dual natural', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s)', 'd4')
    dump1(con, 'dual natural', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s)', 'defense d4') # implicit or
    # dual boolean
    dump1(con, 'dual boolean', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s IN BOOLEAN MODE)', 'defense')
    dump1(con, 'dual boolean', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s IN BOOLEAN MODE)', 'd4')
    dump1(con, 'dual boolean', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s IN BOOLEAN MODE)', 'defense d4') # implicit or
    dump1(con, 'dual boolean', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s IN BOOLEAN MODE)', '+defense+d4') # and
    dump1(con, 'dual boolean', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s IN BOOLEAN MODE)', '+defense-d4') # not
    dump1(con, 'dual boolean', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s IN BOOLEAN MODE)', 'def*') # wildcard
    dump1(con, 'dual boolean', 'SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (%s IN BOOLEAN MODE)', 'N*') # wildcard

<?php

function dump0($stmt, $searchtype) {
    echo "$searchtype\r\n";
    $stmt->execute(array());
    while($row = $stmt->fetch()) {
        $names = $row['names'];
        $moves = $row['moves'];
        echo "  $names - $moves\r\n";
    }
}

function dump1($stmt, $searchtype, $searchterm) {
    echo "$searchtype search for : $searchterm\r\n";
    $stmt->execute(array('st' => $searchterm));
    while($row = $stmt->fetch()) {
        $names = $row['names'];
        $moves = $row['moves'];
        echo "  $names - $moves\r\n";
    }
}

$con = new PDO('mysql:host=localhost;dbname=Test', 'arne', 'hemmeligt');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// all
$stmt = $con->prepare('SELECT names, moves FROM chessopening');
dump0($stmt, "all");
// one
$stmt = $con->prepare('SELECT names, moves FROM chessopening WHERE MATCH(names) AGAINST (:st)');
dump1($stmt, 'one natural', 'defense');
$stmt = $con->prepare('SELECT names, moves FROM chessopening WHERE MATCH(moves) AGAINST (:st)');
dump1($stmt, 'one natural', 'd4');
// dual
$stmt = $con->prepare('SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (:st)');
dump1($stmt, 'dual natural', 'defense');
dump1($stmt, 'dual natural', 'd4');
dump1($stmt, 'dual natural', 'defense d4'); // implicit or
// dual boolean
$stmt = $con->prepare('SELECT names, moves FROM chessopening WHERE MATCH(names,moves) AGAINST (:st IN BOOLEAN MODE)');
dump1($stmt, 'dual boolean', 'defense');
dump1($stmt, 'dual boolean', 'd4');
dump1($stmt, 'dual boolean', 'defense d4'); // implicit or
dump1($stmt, 'dual boolean', '+defense+d4'); // and
dump1($stmt, 'dual boolean', '+defense-d4'); // not
dump1($stmt, 'dual boolean', 'def*'); // wildcard
dump1($stmt, 'dual boolean', 'N*'); // wildcard

?>

PostgreSQL:

PostgreSQL supports full text search. The syntax is a little unusual, but not a problem.

Load:

Definition: 

CREATE TABLE chessopening (id INTEGER NOT NULL,
                           names VARCHAR(255),
                           moves TEXT, PRIMARY KEY(id))
CREATE INDEX names_ix ON chessopening USING GIN(TO_TSVECTOR('english', names))
CREATE INDEX moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',moves))
CREATE INDEX names_moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',names || ' ' || moves))

Data load: 

INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')
INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')
INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')
INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')
INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')
INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')

package textsearch.rdbms.pgsql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Load {
    public static void main(String[] args) throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/Test", "postgres", "hemmeligt")) {
            try(Statement stmt = con.createStatement()) {
                stmt.executeUpdate("DROP TABLE chessopening");
                stmt.executeUpdate("CREATE TABLE chessopening (id INTEGER NOT NULL, " +
                                                              "names VARCHAR(255), " +
                                                              "moves TEXT, PRIMARY KEY(id))");
                stmt.executeUpdate("CREATE INDEX names_ix ON chessopening USING GIN(TO_TSVECTOR('english', names))");
                stmt.executeUpdate("CREATE INDEX moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',moves))");
                stmt.executeUpdate("CREATE INDEX names_moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',names || ' ' || moves))");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')");
                stmt.executeUpdate("INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')");
            }
        }
    }
}

using System;
using System.Data;

using Npgsql;

namespace TextSearch.Rdbms.MySQL.Load
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (IDbConnection con = new NpgsqlConnection("Host=localhost;Database=Test;Username=postgres;Password=hemmeligt"))
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "DROP TABLE chessopening";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE TABLE chessopening (id INTEGER NOT NULL, " +
                                                                 "names VARCHAR(255), " +
                                                                 "moves TEXT, PRIMARY KEY(id))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE INDEX names_ix ON chessopening USING GIN(TO_TSVECTOR('english', names))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE INDEX moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',moves))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE INDEX names_moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',names || ' ' || moves))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

import psycopg2

with psycopg2.connect('host=localhost dbname=Test user=postgres password=hemmeligt') as con:
    c = con.cursor()
    c.execute('DROP TABLE chessopening')
    c.execute('CREATE TABLE chessopening (id INTEGER NOT NULL, ' +
                                         'names VARCHAR(255), ' +
                                         'moves TEXT, PRIMARY KEY(id))')
    c.execute("CREATE INDEX names_ix ON chessopening USING GIN(TO_TSVECTOR('english', names))")
    c.execute("CREATE INDEX moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',moves))")
    c.execute("CREATE INDEX names_moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',names || ' ' || moves))")
    c.execute("INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')")
    c.execute("INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')")
    c.execute("INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')")
    c.execute("INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')")
    c.execute("INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')")
    c.execute("INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')")
    c.close()
    con.commit()

<?php

$con = new PDO('pgsql:host=localhost;dbname=Test', 'postgres', 'hemmeligt');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$con->query('DROP TABLE chessopening');
$con->query('CREATE TABLE chessopening (id INTEGER NOT NULL, ' .
                                       'names VARCHAR(255), ' .
                                       'moves TEXT, PRIMARY KEY(id))');
$con->query("CREATE INDEX names_ix ON chessopening USING GIN(TO_TSVECTOR('english', names))");
$con->query("CREATE INDEX moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',moves))");
$con->query("CREATE INDEX names_moves_ix ON chessopening USING GIN(TO_TSVECTOR('english',names || ' ' || moves))");
$con->query("INSERT INTO chessopening VALUES(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')");
$con->query("INSERT INTO chessopening VALUES(2, 'French defense', '1. e4, e6, 2. d4, d5,')");
$con->query("INSERT INTO chessopening VALUES(3, 'Sicilian defense', '1. e4, c5,')");
$con->query("INSERT INTO chessopening VALUES(4, 'Queens gambit', '1. d4, d5, 2. c4,')");
$con->query("INSERT INTO chessopening VALUES(5, 'Dutch defense', '1. d4, f5,')");
$con->query("INSERT INTO chessopening VALUES(6, 'Reti opening', '1. Nf3, d5, 2. c4,')");
    
?>

Search:

Query examples: 

SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names) @@ TO_TSQUERY (?)
SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(moves) @@ TO_TSQUERY (?)
SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (?)

package textsearch.rdbms.pgsql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Search {
    private static void dump0(PreparedStatement pstmt, String searchtype) throws SQLException {
        System.out.printf("%s\n", searchtype);
        try(ResultSet rs  = pstmt.executeQuery()) {
            while(rs.next()) {
                String names = rs.getString(1);
                String moves = rs.getString(2);
                System.out.printf("  %s - %s\n", names, moves);
            }
        }
        
    }
    private static void dump1(PreparedStatement pstmt, String searchtype, String searchterm) throws SQLException {
        System.out.printf("%s search for : %s\n", searchtype, searchterm);
        pstmt.setString(1, searchterm);
        try(ResultSet rs  = pstmt.executeQuery()) {
            while(rs.next()) {
                String names = rs.getString(1);
                String moves = rs.getString(2);
                System.out.printf("  %s - %s\n", names, moves);
            }
        }
    }
    public static void main(String[] args) throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/Test", "postgres", "hemmeligt")) {
            // all
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening")) {
                dump0(pstmt, "all");
            }
            // one
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names) @@ TO_TSQUERY (?)")) {
                dump1(pstmt, "one", "defense");
            }
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(moves) @@ TO_TSQUERY (?)")) {
                dump1(pstmt, "one", "d4");
            }
            // dual
            try(PreparedStatement pstmt = con.prepareStatement("SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (?)")) {
                dump1(pstmt, "dual", "defense");
                dump1(pstmt, "dual", "d4");
                dump1(pstmt, "dual", "defense | d4"); // or
                dump1(pstmt, "dual", "defense & d4"); // and
                dump1(pstmt, "dual", "defense & !d4"); // not
                dump1(pstmt, "dual", "def:*"); // wildcard
                dump1(pstmt, "dual", "N:*"); // wildcard
            }
        }
    }
}

using System;
using System.Data;

using Npgsql;

namespace TextSearch.Rdbms.PgSQL.Search
{
    public class Program
    {
        private static void Dump0(IDbCommand cmd, string searchtype)
        {
            Console.WriteLine("{0}", searchtype);
            using (IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    Console.WriteLine("  {0} - {1}", (string)dr["names"], (string)dr["moves"]);
                }
            }
        }
        private static void Dump1(IDbCommand cmd, string searchtype, string searchterm)
        {
            Console.WriteLine("{0} search for : {1}", searchtype, searchterm);
            ((IDbDataParameter)cmd.Parameters["@st"]).Value = searchterm;
            using (IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    Console.WriteLine("  {0} - {1}", (string)dr["names"], (string)dr["moves"]);
                }
            }
        }
        public static void Main(string[] args)
        {
            using (IDbConnection con = new NpgsqlConnection("Host=localhost;Database=Test;Username=postgres;Password=hemmeligt"))
            {
                con.Open();
                // all
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names,moves FROM chessopening";
                    Dump0(cmd, "all");
                }
                // one
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names) @@ TO_TSQUERY (@st)";
                    IDbDataParameter st = cmd.CreateParameter();
                    st.ParameterName = "@st";
                    st.DbType = DbType.String;
                    cmd.Parameters.Add(st);
                    Dump1(cmd, "one", "defense");
                }
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(moves) @@ TO_TSQUERY (@st)";
                    IDbDataParameter st = cmd.CreateParameter();
                    st.ParameterName = "@st";
                    st.DbType = DbType.String;
                    cmd.Parameters.Add(st);
                    Dump1(cmd, "one", "d4");
                }
                // dual
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (@st)";
                    IDbDataParameter st = cmd.CreateParameter();
                    st.ParameterName = "@st";
                    st.DbType = DbType.String;
                    cmd.Parameters.Add(st);
                    Dump1(cmd, "dual", "defense");
                    Dump1(cmd, "dual", "d4");
                    Dump1(cmd, "dual", "defense | d4"); // or
                    Dump1(cmd, "dual", "defense & d4"); // and
                    Dump1(cmd, "dual", "defense & !d4"); // not
                    Dump1(cmd, "dual", "def:*"); // wildcard
                    Dump1(cmd, "dual", "N:*"); // wildcard
                }
            }
        }
    }
}

import psycopg2

def dump0(con, searchtype, sqlstr):
    print('%s' % (searchtype))
    c = con.cursor()
    c.execute(sqlstr)
    for row in c.fetchall():
        print('  %s - %s' % (row[0], row[1]))
    c.close()

def dump1(con, searchtype, sqlstr, searchterm):
    print('%s search for : %s' % (searchtype, searchterm))
    c = con.cursor()
    c.execute(sqlstr, (searchterm,))
    for row in c.fetchall():
        print('  %s - %s' % (row[0], row[1]))
    c.close()

with psycopg2.connect('host=localhost dbname=Test user=postgres password=hemmeligt') as con:
    # all
    dump0(con, 'all', 'SELECT names, moves FROM chessopening')
    # one
    dump1(con, 'one', 'SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names) @@ TO_TSQUERY (%s)', 'defense')
    dump1(con, 'one', 'SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(moves) @@ TO_TSQUERY (%s)', 'd4')
    # dual
    dump1(con, 'dual', "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (%s)", 'defense')
    dump1(con, 'dual', "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (%s)", 'd4')
    dump1(con, 'dual', "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (%s)", 'defense | d4') # or
    dump1(con, 'dual', "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (%s)", 'defense & d4') # and
    dump1(con, 'dual', "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (%s)", 'defense & !d4') # not
    dump1(con, 'dual', "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (%s)", 'def:*') # wildcard
    dump1(con, 'dual', "SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (%s)", 'N:*') # wildcard

<?php

function dump0($stmt, $searchtype) {
    echo "$searchtype\r\n";
    $stmt->execute(array());
    while($row = $stmt->fetch()) {
        $names = $row['names'];
        $moves = $row['moves'];
        echo "  $names - $moves\r\n";
    }
}

function dump1($stmt, $searchtype, $searchterm) {
    echo "$searchtype search for : $searchterm\r\n";
    $stmt->execute(array('st' => $searchterm));
    while($row = $stmt->fetch()) {
        $names = $row['names'];
        $moves = $row['moves'];
        echo "  $names - $moves\r\n";
    }
}

$con = new PDO('pgsql:host=localhost;dbname=Test', 'postgres', 'hemmeligt');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// all
$stmt = $con->prepare('SELECT names, moves FROM chessopening');
dump0($stmt, "all");
// one
$stmt = $con->prepare('SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names) @@ TO_TSQUERY (:st)');
dump1($stmt, 'one', 'defense');
$stmt = $con->prepare('SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(moves) @@ TO_TSQUERY (:st)');
dump1($stmt, 'on', 'd4');
// dual
$stmt = $con->prepare("SELECT names, moves FROM chessopening WHERE TO_TSVECTOR(names || ' ' || moves) @@ TO_TSQUERY (:st)");
dump1($stmt, 'dual', 'defense');
dump1($stmt, 'dual', 'd4');
dump1($stmt, 'dual', 'defense | d4'); // or
dump1($stmt, 'dual', 'defense & d4'); // and
dump1($stmt, 'dual', 'defense & !d4'); // not
dump1($stmt, 'dual', 'def:*'); // wildcard
dump1($stmt, 'dual', 'N:*'); // wildcard

?>

Solr:

Background:

Apache Solr is a web service frontend to Apache Lucene text search engine.

Lucene is the best open source text searh engine available. Solr is the most used frontend for general data. ElasticSearch is the most used frontend for log data.

Some use Solr as authoritative data source - but many use Solr to add full text search capability to annother data source that are authoritative.

Solr expose a simple web service API with support for:

On top of that client libraries exist for many of the most common languages.

All examples shown will use JSON as format. It is most common and makes most sense today.

For client API these clients will be used:

Java
SolrJ builtin
C#
SolrNet.Core via nuget
Python
solrpy via pip
PHP
solr extension

For HTTP API's the simplest/oldest possible API will be used.

To create a collection in Solr: 

solr create -c chessopening

It is not required to define a schema for a Solr collection. Solr creates fields automatically when data comes in.

But it can be beneficial to define a schema to make sure it works optimal.

For chessopening 4 x HTTP request: 

POST http://localhost:8888/solr/chessopening/schema

With payloads: 

{ 'add-field': { 'name': 'names', 'type': 'text_general', 'indexed': true, 'multiValued': false } }

{ 'add-field': { 'name': 'moves', 'type': 'text_general', 'indexed': true, 'multiValued': false} }

{ 'add-field': { 'name': 'alltext', 'type': 'text_general', 'indexed': true, 'multiValued': true } }

{ 'add-copy-field': { 'source': '*', 'dest': 'alltext' } }

add-field does what the name indicates, type is type, multiValues is allow multiple values or not, indexed specify whether the field is searchable, stored specify whether a field gets returned in results.

add-copy-field can be used to create a virtual field consting of multiple fields and thereby allow search in multiple fields.

Client API:

Load:

package textsearch.solr.javafree;

import java.io.IOException;

import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.HttpSolrClient;
import org.apache.solr.common.SolrInputDocument;

public class Load {
    private static SolrInputDocument create(int id, String names, String moves) {
        SolrInputDocument res = new SolrInputDocument();
        res.addField("id", id);
        res.addField("names", names);
        res.addField("moves", moves);
        return res;
    }
    public static void main(String[] args) throws IOException, SolrServerException {
        HttpSolrClient client = new HttpSolrClient.Builder("http://localhost:8888/solr").build();
        client.deleteByQuery("chessopening", "*:*");
        client.add("chessopening", create(1, "Spanish defense, Ruy Lopez", "1. e4, e5, 2. Nf3, Nc6, 3. Bb5,"));
        client.add("chessopening", create(2, "French defense", "1. e4, e6, 2. d4, d5,"));
        client.add("chessopening", create(3, "Sicilian defense", "1. e4, c5,"));
        client.add("chessopening", create(4, "Queens gambit", "1. d4, d5, 2. c4,"));
        client.add("chessopening", create(5, "Dutch defense", "1. d4, f5,"));
        client.add("chessopening", create(6, "Reti opening", "1. Nf3, d5, 2. c4,"));
        client.commit("chessopening");
        client.close();
    }
}

package textsearch.solr.javabean;

import org.apache.solr.client.solrj.beans.Field;

public class ChessOpening {
    private int id;
    private String names;
    private String moves;
    public ChessOpening() {
        this(0, "", "");
    }
    public ChessOpening(int id, String names, String moves) {
        this.id = id;
        this.names = names;
        this.moves = moves;
    }
    public int getId() {
        return id;
    }
    @Field
    public void setId(int id) {
        this.id = id;
    }
    public String getNames() {
        return names;
    }
    @Field
    public void setNames(String names) {
        this.names = names;
    }
    public String getMoves() {
        return moves;
    }
    @Field
    public void setMoves(String moves) {
        this.moves = moves;
    }
}

package textsearch.solr.javabean;

import java.io.IOException;

import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.HttpSolrClient;

public class Load {
    public static void main(String[] args) throws IOException, SolrServerException {
        HttpSolrClient client = new HttpSolrClient.Builder("http://localhost:8888/solr").build();
        client.deleteByQuery("chessopening", "*:*");
        client.addBean("chessopening", new ChessOpening(1, "Spanish defense, Ruy Lopez", "1. e4, e5, 2. Nf3, Nc6, 3. Bb5,"));
        client.addBean("chessopening", new ChessOpening(2, "French defense", "1. e4, e6, 2. d4, d5,"));
        client.addBean("chessopening", new ChessOpening(3, "Sicilian defense", "1. e4, c5,"));
        client.addBean("chessopening", new ChessOpening(4, "Queens gambit", "1. d4, d5, 2. c4,"));
        client.addBean("chessopening", new ChessOpening(5, "Dutch defense", "1. d4, f5,"));
        client.addBean("chessopening", new ChessOpening(6, "Reti opening", "1. Nf3, d5, 2. c4,"));
        client.commit("chessopening");
        client.commit();
        client.close();
    }
}

using SolrNet.Attributes;

namespace TextSearch.Solr.Mapped
{
    public class ChessOpening
    {
        [SolrUniqueKey("id")]
        public int Id { get; set; }
        [SolrField("names")]
        public string Names { get; set; }
        [SolrField("moves")]
        public string Moves { get; set; }
    }
}

using System;
using System.Collections.Generic;

using SolrNet;

namespace TextSearch.Solr.Mapped
{
    public class Program
    {
        public static void Main(string[] args)
        {
            ISolrOperations<ChessOpening> client = SolrNet.SolrNet.GetServer<ChessOpening>("http://localhost:8888/solr/chessopening", true);
            client.Delete(new SolrQuery("*:*"));
            client.Add(new ChessOpening { Id = 1, Names = "Spanish defense, Ruy Lopez", Moves = "1. e4, e5, 2. Nf3, Nc6, 3. Bb5," });
            client.Add(new ChessOpening { Id = 2, Names = "French defense", Moves = "1. e4, e6, 2. d4, d5," });
            client.Add(new ChessOpening { Id = 3, Names = "Sicilian defense", Moves = "1. e4, c5," });
            client.Add(new ChessOpening { Id = 4, Names = "Queens gambit", Moves = "1. d4, d5, 2. c4," });
            client.Add(new ChessOpening { Id = 5, Names = "Dutch defense", Moves = "1. d4, f5," });
            client.Add(new ChessOpening { Id = 6, Names = "Reti opening", Moves = "1. Nf3, d5, 2. c4," });
            client.Commit();
        }
    }
}

import solr


client = solr.SolrConnection('http://localhost:8888/solr/chessopening')
client.delete_query('*:*')
client.add(id=1, names='Spanish defense, Ruy Lopez', moves='1. e4, e5, 2. Nf3, Nc6, 3. Bb5,')
client.add(id=2, names='French defense', moves='1. e4, e6, 2. d4, d5,')
client.add(id=3, names='Sicilian defense', moves='1. e4, c5,')
client.add(id=4, names='Queens gambit', moves='1. d4, d5, 2. c4,')
client.add(id=5, names='Dutch defense', moves='1. d4, f5,')
client.add(id=6, names='Reti opening', moves='1. Nf3, d5, 2. c4,')
client.commit()

<?php

function create($id, $names, $moves) {
    $res = new SolrInputDocument();
    $res->addField('id', $id);
    $res->addField('names', $names);
    $res->addField('moves', $moves);
    return $res;
}

$client = new SolrClient(array('hostname' => 'localhost', 'port' => 8888, 'path' => '/solr/chessopening'));
$client->deleteByQuery('*:*');
$doc = create(1, 'Spanish defense, Ruy Lopez', '1. e4, e5, 2. Nf3, Nc6, 3. Bb5,');
$client->addDocument($doc);
$doc = create(2, 'French defense', '1. e4, e6, 2. d4, d5,');
$client->addDocument($doc);
$doc = create(3, 'Sicilian defense', '1. e4, c5,');
$client->addDocument($doc);
$doc = create(4, 'Queens gambit', '1. d4, d5, 2. c4,');
$client->addDocument($doc);
$doc = create(5, 'Dutch defense', '1. d4, f5,');
$client->addDocument($doc);
$doc = create(6, 'Reti opening', '1. Nf3, d5, 2. c4,');
$client->addDocument($doc);
$client->commit();

?>

Search:

package textsearch.solr.javafree;

import java.io.IOException;

import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.HttpSolrClient;
import org.apache.solr.common.SolrDocument;
import org.apache.solr.common.SolrDocumentList;

public class Search {
    private static void dump(HttpSolrClient client, String searchterm) throws SolrServerException, IOException {
        System.out.printf("search for : %s\n",  searchterm);
        SolrQuery query = new SolrQuery();
        query.setParam("df", "alltext");
        query.setQuery(searchterm);
        query.addField("names");
        query.addField("moves");
        SolrDocumentList res = client.query("chessopening", query).getResults();
        for(SolrDocument doc : res) {
            String names = (String)doc.getFirstValue("names");
            String moves = (String)doc.getFirstValue("moves");
            System.out.printf("  %s - %s\n",  names, moves);
        }
    }
    public static void main(String[] args) throws IOException, SolrServerException {
        HttpSolrClient client = new HttpSolrClient.Builder("http://localhost:8888/solr").build();
        // all
        dump(client, "*:*");
        // one
        dump(client, "names:defense");
        dump(client, "moves:d4");
        // dual
        dump(client, "alltext:defense");
        dump(client, "alltext:d4");
        dump(client, "defense"); // default field
        dump(client, "d4"); // default field
        // boolean
        dump(client, "names:defense moves:d4"); // implicit or
        dump(client, "names:defense AND moves:d4"); // and
        dump(client, "names:defense NOT moves:d4"); // not
        dump(client, "defense d4"); // default field + implicit or
        dump(client, "defense AND d4"); // default field + and
        dump(client, "defense NOT d4"); // default field + not
        // wildcard
        dump(client, "names:def*");
        dump(client, "moves:N*");
        client.close();
    }
}

package textsearch.solr.javabean;

import org.apache.solr.client.solrj.beans.Field;

public class ChessOpening {
    private int id;
    private String names;
    private String moves;
    public ChessOpening() {
        this(0, "", "");
    }
    public ChessOpening(int id, String names, String moves) {
        this.id = id;
        this.names = names;
        this.moves = moves;
    }
    public int getId() {
        return id;
    }
    @Field
    public void setId(int id) {
        this.id = id;
    }
    public String getNames() {
        return names;
    }
    @Field
    public void setNames(String names) {
        this.names = names;
    }
    public String getMoves() {
        return moves;
    }
    @Field
    public void setMoves(String moves) {
        this.moves = moves;
    }
}

Search.java: 

package textsearch.solr.javabean;

import java.io.IOException;
import java.util.List;

import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.HttpSolrClient;

public class Search {
    private static void dump(HttpSolrClient client, String searchterm) throws SolrServerException, IOException {
        System.out.printf("search for : %s\n",  searchterm);
        SolrQuery query = new SolrQuery();
        query.setParam("df", "alltext");
        query.setQuery(searchterm);
        query.addField("names");
        query.addField("moves");
        List<ChessOpening> res = client.query("chessopening", query).getBeans(ChessOpening.class);
        for(ChessOpening doc : res) {
            String names = doc.getNames();
            String moves = doc.getMoves();
            System.out.printf("  %s - %s\n",  names, moves);
        }
    }
    public static void main(String[] args) throws IOException, SolrServerException {
        HttpSolrClient client = new HttpSolrClient.Builder("http://localhost:8888/solr").build();
        // all
        dump(client, "*:*");
        // one
        dump(client, "names:defense");
        dump(client, "moves:d4");
        // dual
        dump(client, "alltext:defense");
        dump(client, "alltext:d4");
        dump(client, "defense"); // default field
        dump(client, "d4"); // default field
        // boolean
        dump(client, "names:defense moves:d4"); // implicit or
        dump(client, "names:defense AND moves:d4"); // and
        dump(client, "names:defense NOT moves:d4"); // not
        dump(client, "defense d4"); // default field + implicit or
        dump(client, "defense AND d4"); // default field + and
        dump(client, "defense NOT d4"); // default field + not
        // wildcard
        dump(client, "names:def*");
        dump(client, "moves:N*");
        client.close();
    }
}

using SolrNet.Attributes;

namespace TextSearch.Solr.Mapped
{
    public class ChessOpening
    {
        [SolrUniqueKey("id")]
        public int Id { get; set; }
        [SolrField("names")]
        public string Names { get; set; }
        [SolrField("moves")]
        public string Moves { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;

using SolrNet;
using SolrNet.Commands.Parameters;

namespace TextSearch.Solr.Mapped
{
    public class Search
    {
        private static void Dump(ISolrOperations<ChessOpening> client, string query)
        {
            Console.WriteLine("search for : {0}", query);
            List<KeyValuePair<string, string>> optparams = new List<KeyValuePair<string, string>>();
            optparams.Add(new KeyValuePair<string,string>("df", "alltext"));   
            QueryOptions options = new QueryOptions();
            options.ExtraParams = optparams;
            List<ChessOpening> res = client.Query(query, options).ToList();
            foreach (ChessOpening doc in res)
            {
                Console.WriteLine("  {0} - {1}", doc.Names, doc.Moves);                    
            }
        }
        public static void Main(string[] args)
        {
            ISolrOperations<ChessOpening> client = SolrNet.SolrNet.GetServer<ChessOpening>("http://localhost:8888/solr/chessopening", true);
            // all
            Dump(client, "*:*");
            // one
            Dump(client, "names:defense");
            Dump(client, "moves:d4");
            // dual
            Dump(client, "alltext:defense");
            Dump(client, "alltext:d4");
            Dump(client, "defense"); // default field
            Dump(client, "d4"); // default field
                                // boolean
            Dump(client, "names:defense moves:d4"); // implicit or
            Dump(client, "names:defense AND moves:d4"); // and
            Dump(client, "names:defense NOT moves:d4"); // not
            Dump(client, "defense d4"); // default field + implicit or
            Dump(client, "defense AND d4"); // default field + and
            Dump(client, "defense NOT d4"); // default field + not
            // wildcard
            Dump(client, "names:def*");
            Dump(client, "moves:N*");
        }
    }
}

import solr

def dump(client, searchterm):
    print('search for : %s' % (searchterm))
    res = client.query(searchterm, df='alltext').results
    for doc in res:
        print('  %s - %s' % (doc['names'], doc['moves']))

client = solr.SolrConnection('http://localhost:8888/solr/chessopening')
# all
dump(client, '*:*')
# one
dump(client, 'names:defense')
dump(client, 'moves:d4')
# dual
dump(client, 'alltext:defense')
dump(client, 'alltext:d4')
dump(client, 'defense') # default field
dump(client, 'd4') # default field
# boolean
dump(client, 'names:defense moves:d4') # implicit or
dump(client, 'names:defense AND moves:d4') # and
dump(client, 'names:defense NOT moves:d4') # not
dump(client, 'defense d4') # default field + implicit or
dump(client, 'defense AND d4') # default field + and
dump(client, 'defense NOT d4') # default field + not
# wildcard
dump(client, 'names:def*') 
dump(client, 'moves:N*')

<?php

function dump($client, $searchterm) {
    echo "search for : $searchterm\r\n";
    $query = new SolrQuery();
    $query->set('df', 'alltext');
    $query->setQuery($searchterm);
    $query->addField('names');
    $query->addField('moves');
    $res = $client->query($query)->getResponse();
    if($res->response->docs) {
        foreach($res->response->docs as $doc) {
            $names = $doc->names;
            $moves = $doc->moves;
            echo "  $names - $moves\r\n";
        }
    }
}


$client = new SolrClient(array('hostname' => 'localhost', 'port' => 8888, 'path' => '/solr/chessopening'));
// all
dump($client, '*:*');
// one
dump($client, 'names:defense');
dump($client, 'moves:d4');
// dual
dump($client, 'alltext:defense');
dump($client, 'alltext:d4');
dump($client, 'defense'); // default field
dump($client, 'd4'); // default field
// boolean
dump($client, 'names:defense moves:d4'); // implicit or
dump($client, 'names:defense AND moves:d4'); // and
dump($client, 'names:defense NOT moves:d4'); // not
dump($client, 'defense d4'); // default field + implicit or
dump($client, 'defense AND d4'); // default field + and
dump($client, 'defense NOT d4'); // default field + not
// wildcard
dump($client, 'names:def*'); 
dump($client, 'moves:N*');

?>

HTPP POST:

Load:

Data load HTTP request: 

POST http://localhost:8888/solr/chessopening/update/json/docs?commit=true

Data load payload, examples: 

{ "id": 1, "names": "Spanish defense, Ruy Lopez", "moves": "1. e4, e5, 2. Nf3, Nc6, 3. Bb5," }
{ "id": 2, "names": "French defense", "moves": "1. e4, e6, 2. d4, d5," }
{ "id": 3, "names": "Sicilian defense", "moves": "1. e4, c5," }
{ "id": 4, "names": "Queens gambit", "moves": "1. d4, d5, 2. c4," }
{ "id": 5, "names": "Dutch defense", "moves": "d4, f5," }
{ "id": 6, "names": "Reti opening", "moves": "Nf3, d5, 2. c4," }

package textsearch.solr.httppost;

import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;

public class Load {
    private static String http(String method, String urlstr, String typ, String body) {
        try {
            HttpURLConnection con = (HttpURLConnection)(new URL(urlstr)).openConnection();
            con.setRequestMethod(method);
            con.addRequestProperty("accept",  typ);
            if(body != null) {
                con.addRequestProperty("content-type", typ);
                con.setDoOutput(true);
                con.getOutputStream().write(body.getBytes());
            }
            StringBuilder sb = new StringBuilder();
            con.connect();
            if(con.getResponseCode() / 100 == 2) {
                InputStream is = con.getInputStream();
                byte[] b = new byte[1000];
                int n;
                while((n = is.read(b)) >= 0) {
                    sb.append(new String(b,0,n));
                }
                is.close();
            } else {
                System.out.printf("Error: %d %s\n", con.getResponseCode(), con.getResponseMessage());
            }
            con.disconnect();
            return sb.toString();
        } catch (MalformedURLException e) {
            e.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
    private static String create(int id, String names, String moves) {
        return String.format("{ 'id': %d, 'names': '%s', 'moves': '%s' }",  id, names, moves);
    }
    public static void main(String[] args) throws IOException {
        http("POST", "http://localhost:8888/solr/chessopening/update?commit=true", "application/json", "{ 'delete': { 'query': '*:*' } }");
        http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", create(1, "Spanish defense, Ruy Lopez", "1. e4, e5, 2. Nf3, Nc6, 3. Bb5,"));
        http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", create(2, "French defense", "1. e4, e6, 2. d4, d5,"));
        http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", create(3, "Sicilian defense", "1. e4, c5,"));
        http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", create(4, "Queens gambit", "1. d4, d5, 2. c4,"));
        http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", create(5, "Dutch defense", "d4, f5,"));
        http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", create(6, "Reti opening", "Nf3, d5, 2. c4,"));
    }
}

using System;
using System.IO;
using System.Net;

namespace TextSearch.Solr.HttpPost
{
    public class Program
    {
        private static string Http(string method, string url, string typ, string body)
        {
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
            req.Method = method;
            req.Accept = typ;
            if (body != null)
            {
                req.ContentType = typ;
                StreamWriter sw = new StreamWriter(req.GetRequestStream());
                sw.WriteLine(body);
                sw.Flush();
            }
            HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
            if (((int)resp.StatusCode) / 100 == 2)
            {
                StreamReader sr = new StreamReader(resp.GetResponseStream());
                string content = sr.ReadToEnd();
                sr.Close();
                return content;
            }
            else
            {
                Console.WriteLine("Error: {0} {1}", resp.StatusCode, resp.StatusDescription);
                return null;
            }
        }
        private static string Create(int id, string names, string moves)
        {
            return string.Format("{{ 'id': {0}, 'names': '{1}', 'moves': '{2}' }}", id, names, moves);
        }
        public static void Main(string[] args)
        {
            Http("POST", "http://localhost:8888/solr/chessopening/update?commit=true", "application/json", "{ 'delete': { 'query': '*:*' } }");
            Http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", Create(1, "Spanish defense, Ruy Lopez", "1. e4, e5, 2. Nf3, Nc6, 3. Bb5,"));
            Http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", Create(2, "French defense", "1. e4, e6, 2. d4, d5,"));
            Http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", Create(3, "Sicilian defense", "1. e4, c5,"));
            Http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", Create(4, "Queens gambit", "1. d4, d5, 2. c4,"));
            Http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", Create(5, "Dutch defense", "d4, f5,"));
            Http("POST", "http://localhost:8888/solr/chessopening/update/json/docs?commit=true", "application/json", Create(6, "Reti opening", "Nf3, d5, 2. c4,"));
        }
    }
}

import urllib.request

def http(url, body):
    req = urllib.request.Request(url, headers={ 'Accept': 'application/json', 'Content-Type': 'application/json'}, data= bytes(body, 'UTF-8'))
    rawresp = urllib.request.urlopen(req)

def create(id, names, moves):
    return '{ "id": %d, "names": "%s", "moves": "%s" }' % (id, names, moves)
    
http('http://localhost:8888/solr/chessopening/update?commit=true', '{ "delete": { "query": "*:*" } }')
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(1, "Spanish defense, Ruy Lopez", "1. e4, e5, 2. Nf3, Nc6, 3. Bb5,"))
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(2, "French defense", "1. e4, e6, 2. d4, d5,"))
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(3, "Sicilian defense", "1. e4, c5,"))
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(4, "Queens gambit", "1. d4, d5, 2. c4,"))
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(5, "Dutch defense", "d4, f5,"))
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(6, "Reti opening", "Nf3, d5, 2. c4,"))

<?php

function http($url, $body) {
    $curl = curl_init($url);
    curl_setopt($curl, CURLOPT_HTTPHEADER, array('Accept: ' . 'application/json', 'Content-Type: ' . 'application/json'));
    curl_setopt($curl, CURLOPT_POST, true);
    curl_setopt($curl, CURLOPT_POSTFIELDS, $body);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    $rawresp = curl_exec($curl);
}

function create($id, $names, $moves) {
    return sprintf('{ "id": %d, "names": "%s", "moves": "%s" }', $id, $names, $moves);
}

http('http://localhost:8888/solr/chessopening/update?commit=true', '{ "delete": { "query": "*:*" } }');
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(1, "Spanish defense, Ruy Lopez", "1. e4, e5, 2. Nf3, Nc6, 3. Bb5,"));
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(2, "French defense", "1. e4, e6, 2. d4, d5,"));
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(3, "Sicilian defense", "1. e4, c5,"));
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(4, "Queens gambit", "1. d4, d5, 2. c4,"));
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(5, "Dutch defense", "d4, f5,"));
http('http://localhost:8888/solr/chessopening/update/json/docs?commit=true', create(6, "Reti opening", "Nf3, d5, 2. c4,"));

?>

Search:

Query HTTP request: 

POST http://localhost:8888/solr/chessopening/select?df=alltext

Query payload, examples: 

{ "query": "names:defense" }
{ "query": "moves:d4" }
{ "query": "alltext:defense" }
{ "query": "alltext:d4" }
{ "query": "defense" }
{ "query": "d4" }
{ "query": "names:defense moves:d4" }
{ "query": "names:defense AND moves:d4" }
{ "query": "names:defense NOT moves:d4" }
{ "query": "defense d4" }
{ "query": "defense AND d4" }
{ "query": "defense NOT d4" }
{ "query": "names:def*" }
{ "query": "moves:N*" }

package textsearch.solr.httppost;

import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;

import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

public class Search {
    private static String http(String method, String urlstr, String typ, String body) {
        try {
            HttpURLConnection con = (HttpURLConnection)(new URL(urlstr)).openConnection();
            con.setRequestMethod(method);
            con.addRequestProperty("accept",  typ);
            if(body != null) {
                con.addRequestProperty("content-type", typ);
                con.setDoOutput(true);
                con.getOutputStream().write(body.getBytes());
            }
            StringBuilder sb = new StringBuilder();
            con.connect();
            if(con.getResponseCode() / 100 == 2) {
                InputStream is = con.getInputStream();
                byte[] b = new byte[1000];
                int n;
                while((n = is.read(b)) >= 0) {
                    sb.append(new String(b,0,n));
                }
                is.close();
            } else {
                System.out.printf("Error: %d %s\n", con.getResponseCode(), con.getResponseMessage());
            }
            con.disconnect();
            return sb.toString();
        } catch (MalformedURLException e) {
            e.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
    private static void dump(String baseurl, String searchterm) throws IOException {
        System.out.printf("search for : %s\n",  searchterm);
        String rawresp = http("POST", baseurl + "/chessopening/select?df=alltext", "application/json", "{ 'query': '" + searchterm + "' }");
        JsonObject resp = JsonParser.parseString(rawresp).getAsJsonObject().get("response").getAsJsonObject();
        for(JsonElement doc : resp.get("docs").getAsJsonArray()) {
            String names = doc.getAsJsonObject().get("names").getAsString();
            String moves = doc.getAsJsonObject().get("moves").getAsString();
            System.out.printf("  %s - %s\n",  names, moves);
        }
    }
    public static void main(String[] args) throws IOException {
        // all
        dump("http://localhost:8888/solr", "*:*");
        // one
        dump("http://localhost:8888/solr", "names:defense");
        dump("http://localhost:8888/solr", "moves:d4");
        // dual
        dump("http://localhost:8888/solr", "alltext:defense");
        dump("http://localhost:8888/solr", "alltext:d4");
        dump("http://localhost:8888/solr", "defense"); // default field
        dump("http://localhost:8888/solr", "d4"); // default field
        // boolean
        dump("http://localhost:8888/solr", "names:defense moves:d4"); // implicit or
        dump("http://localhost:8888/solr", "names:defense AND moves:d4"); // and
        dump("http://localhost:8888/solr", "names:defense NOT moves:d4"); // not
        dump("http://localhost:8888/solr", "defense d4"); // default field + implicit or
        dump("http://localhost:8888/solr", "defense AND d4"); // default field + and
        dump("http://localhost:8888/solr", "defense NOT d4"); // default field + not
        // wildcard
        dump("http://localhost:8888/solr", "names:def*");
        dump("http://localhost:8888/solr", "moves:N*");
    }
}

using System;
using System.IO;
using System.Net;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace TextSearch.Solr.HttpPost
{
    public class Program
    {
        private static string Http(string method, string url, string typ, string body)
        {
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
            req.Method = method;
            req.Accept = typ;
            if (body != null)
            {
                req.ContentType = typ;
                StreamWriter sw = new StreamWriter(req.GetRequestStream());
                sw.WriteLine(body);
                sw.Flush();
            }
            HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
            if (((int)resp.StatusCode) / 100 == 2)
            {
                StreamReader sr = new StreamReader(resp.GetResponseStream());
                string content = sr.ReadToEnd();
                sr.Close();
                return content;
            }
            else
            {
                Console.WriteLine("Error: {0} {1}", resp.StatusCode, resp.StatusDescription);
                return null;
            }
        }
        private static void Dump(String baseurl, String searchterm)
        {
            Console.WriteLine("search for : {0}", searchterm);
            string rawresp = Http("POST", baseurl + "/chessopening/select?df=alltext", "application/json", "{ 'query': '" + searchterm + "' }");
            JObject resp = (JObject)JsonConvert.DeserializeObject(rawresp);
            foreach (JObject doc in resp["response"]["docs"])
            {
                Console.WriteLine("  {0} - {1}", doc["names"], doc["moves"]);
            }
        }
        public static void Main(string[] args)
        {
            // all
            Dump("http://localhost:8888/solr", "*:*");
            // one
            Dump("http://localhost:8888/solr", "names:defense");
            Dump("http://localhost:8888/solr", "moves:d4");
            // dual
            Dump("http://localhost:8888/solr", "alltext:defense");
            Dump("http://localhost:8888/solr", "alltext:d4");
            Dump("http://localhost:8888/solr", "defense"); // default field
            Dump("http://localhost:8888/solr", "d4"); // default field
            // boolean
            Dump("http://localhost:8888/solr", "names:defense moves:d4"); // implicit or
            Dump("http://localhost:8888/solr", "names:defense AND moves:d4"); // and
            Dump("http://localhost:8888/solr", "names:defense NOT moves:d4"); // not
            Dump("http://localhost:8888/solr", "defense d4"); // default field + implicit or
            Dump("http://localhost:8888/solr", "defense AND d4"); // default field + and
            Dump("http://localhost:8888/solr", "defense NOT d4"); // default field + not
            // wildcard
            Dump("http://localhost:8888/solr", "names:def*");
            Dump("http://localhost:8888/solr", "moves:N*");
        }
    }
}

import urllib.request
import json

def dump(baseurl, searchterm):
    print('search for : %s' % (searchterm))
    req = urllib.request.Request(baseurl + '/select?df=alltext', headers={ 'Accept': 'application/json', 'Content-Type': 'application/json'}, data= bytes('{ "query": "' + searchterm + '" }', 'UTF-8'))
    rawresp = urllib.request.urlopen(req)
    resp = json.load(rawresp)
    for doc in resp['response']['docs']:
        print('  %s - %s' % (doc['names'], doc['moves']))

# all
dump('http://localhost:8888/solr/chessopening', '*:*')
# one
dump('http://localhost:8888/solr/chessopening', 'names:defense')
dump('http://localhost:8888/solr/chessopening', 'moves:d4')
# dual
dump('http://localhost:8888/solr/chessopening', 'alltext:defense')
dump('http://localhost:8888/solr/chessopening', 'alltext:d4')
dump('http://localhost:8888/solr/chessopening', 'defense') # default field
dump('http://localhost:8888/solr/chessopening', 'd4') # default field
# boolean
dump('http://localhost:8888/solr/chessopening', 'names:defense moves:d4') # implicit or
dump('http://localhost:8888/solr/chessopening', 'names:defense AND moves:d4') # and
dump('http://localhost:8888/solr/chessopening', 'names:defense NOT moves:d4') # not
dump('http://localhost:8888/solr/chessopening', 'defense d4') # default field + implicit or
dump('http://localhost:8888/solr/chessopening', 'defense AND d4') # default field + and
dump('http://localhost:8888/solr/chessopening', 'defense NOT d4') # default field + not
# wildcard
dump('http://localhost:8888/solr/chessopening', 'names:def*')
dump('http://localhost:8888/solr/chessopening', 'moves:N*')

<?php

function dump($baseurl, $searchterm) {
    echo "search for : $searchterm\r\n";
    $curl = curl_init($baseurl . '/select?df=alltext');
    curl_setopt($curl, CURLOPT_HTTPHEADER, array('Accept: ' . 'application/json', 'Content-Type: ' . 'application/json'));
    curl_setopt($curl, CURLOPT_POST, true);
    curl_setopt($curl, CURLOPT_POSTFIELDS, "{ 'query': '$searchterm' }");
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    $rawresp = curl_exec($curl);
    $resp = json_decode($rawresp);
    if($resp->response->docs) {
        foreach($resp->response->docs as $doc) {
            $names = $doc->names;
            $moves = $doc->moves;
            echo "  $names - $moves\r\n";
        }
    }
}


// all
dump('http://localhost:8888/solr/chessopening', "*:*");
// one
dump('http://localhost:8888/solr/chessopening', "names:defense");
dump('http://localhost:8888/solr/chessopening', "moves:d4");
// dual
dump('http://localhost:8888/solr/chessopening', "alltext:defense");
dump('http://localhost:8888/solr/chessopening', "alltext:d4");
dump('http://localhost:8888/solr/chessopening', "defense"); // default field
dump('http://localhost:8888/solr/chessopening', "d4"); // default field
// boolean
dump('http://localhost:8888/solr/chessopening', "names:defense moves:d4"); // implicit or
dump('http://localhost:8888/solr/chessopening', "names:defense AND moves:d4"); // and
dump('http://localhost:8888/solr/chessopening', "names:defense NOT moves:d4"); // not
dump('http://localhost:8888/solr/chessopening', "defense d4"); // default field + implicit or
dump('http://localhost:8888/solr/chessopening', "defense AND d4"); // default field + and
dump('http://localhost:8888/solr/chessopening', "defense NOT d4"); // default field + not
// wildcard
dump('http://localhost:8888/solr/chessopening', "names:def*");
dump('http://localhost:8888/solr/chessopening', "moves:N*");

?>

HTPP GET:

Search:

Query HTTP request, examples: 

GET http://localhost:8888/solr/chessopening/select?df=alltext&q=names:defense
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=moves:d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=alltext:defense
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=alltext:d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=defense
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=names:defense+moves:d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=names:defense+AND+moves:d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=names:defense+NOT+moves:d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=defense+d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=defense+AND+d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=defense+NOT+d4
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=names:def*
GET http://localhost:8888/solr/chessopening/select?df=alltext&q=moves:N*

package textsearch.solr.httpget;

import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;

import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

public class Search {
    private static String http(String method, String urlstr, String typ, String body) {
        try {
            HttpURLConnection con = (HttpURLConnection)(new URL(urlstr)).openConnection();
            con.setRequestMethod(method);
            con.addRequestProperty("accept",  typ);
            if(body != null) {
                con.addRequestProperty("content-type", typ);
                con.setDoOutput(true);
                con.getOutputStream().write(body.getBytes());
            }
            StringBuilder sb = new StringBuilder();
            con.connect();
            if(con.getResponseCode() / 100 == 2) {
                InputStream is = con.getInputStream();
                byte[] b = new byte[1000];
                int n;
                while((n = is.read(b)) >= 0) {
                    sb.append(new String(b,0,n));
                }
                is.close();
            } else {
                System.out.printf("Error: %d %s\n", con.getResponseCode(), con.getResponseMessage());
            }
            con.disconnect();
            return sb.toString();
        } catch (MalformedURLException e) {
            e.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
    private static void dump(String baseurl, String searchterm) throws IOException {
        System.out.printf("search for : %s\n",  searchterm);
        String rawresp = http("GET", baseurl + "/chessopening/select?q=" + searchterm.replace(" ", "+") + "&df=alltext", "application/json", null);
        JsonObject resp = JsonParser.parseString(rawresp).getAsJsonObject().get("response").getAsJsonObject();
        for(JsonElement doc : resp.get("docs").getAsJsonArray()) {
            String names = doc.getAsJsonObject().get("names").getAsString();
            String moves = doc.getAsJsonObject().get("moves").getAsString();
            System.out.printf("  %s - %s\n",  names, moves);
        }
    }
    public static void main(String[] args) throws IOException {
        // all
        dump("http://localhost:8888/solr", "*:*");
        // one
        dump("http://localhost:8888/solr", "names:defense");
        dump("http://localhost:8888/solr", "moves:d4");
        // dual
        dump("http://localhost:8888/solr", "alltext:defense");
        dump("http://localhost:8888/solr", "alltext:d4");
        dump("http://localhost:8888/solr", "defense"); // default field
        dump("http://localhost:8888/solr", "d4"); // default field
        // boolean
        dump("http://localhost:8888/solr", "names:defense moves:d4"); // implicit or
        dump("http://localhost:8888/solr", "names:defense AND moves:d4"); // and
        dump("http://localhost:8888/solr", "names:defense NOT moves:d4"); // not
        dump("http://localhost:8888/solr", "defense d4"); // default field + implicit or
        dump("http://localhost:8888/solr", "defense AND d4"); // default field + and
        dump("http://localhost:8888/solr", "defense NOT d4"); // default field + not
        // wildcard
        dump("http://localhost:8888/solr", "names:def*");
        dump("http://localhost:8888/solr", "moves:N*");
    }
}

using System;
using System.IO;
using System.Net;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace TextSearch.Solr.HttpGet
{
    public class Program
    {
        private static string Http(string method, string url, string typ, string body)
        {
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
            req.Method = method;
            req.Accept = typ;
            if(body != null)
            {
                req.ContentType = typ;
                StreamWriter sw = new StreamWriter(req.GetRequestStream());
                sw.Write(body);
            }
            HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
            if(((int)resp.StatusCode) / 100 == 2)
            {
                StreamReader sr = new StreamReader(resp.GetResponseStream());
                string content = sr.ReadToEnd();
                sr.Close();
                return content;
            }
            else
            {
                Console.WriteLine("Error: {0} {1}", resp.StatusCode, resp.StatusDescription);
                return null;
            }
        }
        private static void Dump(String baseurl, String searchterm)
        {
            Console.WriteLine("search for : {0}", searchterm);
            string rawresp = Http("GET", baseurl + "/chessopening/select?q=" + searchterm.Replace(" ", "+") + "&df=alltext", "application/json", null);
            JObject resp = (JObject)JsonConvert.DeserializeObject(rawresp);
            foreach(JObject doc in resp["response"]["docs"])
            {
                Console.WriteLine("  {0} - {1}", doc["names"], doc["moves"]);
            }
        }
        public static void Main(string[] args)
        {
            // all
            Dump("http://localhost:8888/solr", "*:*");
            // one
            Dump("http://localhost:8888/solr", "names:defense");
            Dump("http://localhost:8888/solr", "moves:d4");
            // dual
            Dump("http://localhost:8888/solr", "alltext:defense");
            Dump("http://localhost:8888/solr", "alltext:d4");
            Dump("http://localhost:8888/solr", "defense"); // default field
            Dump("http://localhost:8888/solr", "d4"); // default field
            // boolean
            Dump("http://localhost:8888/solr", "names:defense moves:d4"); // implicit or
            Dump("http://localhost:8888/solr", "names:defense AND moves:d4"); // and
            Dump("http://localhost:8888/solr", "names:defense NOT moves:d4"); // not
            Dump("http://localhost:8888/solr", "defense d4"); // default field + implicit or
            Dump("http://localhost:8888/solr", "defense AND d4"); // default field + and
            Dump("http://localhost:8888/solr", "defense NOT d4"); // default field + not
            // wildcard
            Dump("http://localhost:8888/solr", "names:def*");
            Dump("http://localhost:8888/solr", "moves:N*");
        }
    }
}

import urllib.request
import json

def dump(baseurl, searchterm):
    print('search for : %s' % (searchterm))
    req = urllib.request.Request(baseurl + '/select?q=' + searchterm.replace(' ', '+') + '&df=alltext', headers={ 'Accept': 'application/json'})
    rawresp = urllib.request.urlopen(req)
    resp = json.load(rawresp)
    for doc in resp['response']['docs']:
        print('  %s - %s' % (doc['names'], doc['moves']))

# all
dump('http://localhost:8888/solr/chessopening', '*:*')
# one
dump('http://localhost:8888/solr/chessopening', 'names:defense')
dump('http://localhost:8888/solr/chessopening', 'moves:d4')
# dual
dump('http://localhost:8888/solr/chessopening', 'alltext:defense')
dump('http://localhost:8888/solr/chessopening', 'alltext:d4')
dump('http://localhost:8888/solr/chessopening', 'defense') # default field
dump('http://localhost:8888/solr/chessopening', 'd4') # default field
# boolean
dump('http://localhost:8888/solr/chessopening', 'names:defense moves:d4') # implicit or
dump('http://localhost:8888/solr/chessopening', 'names:defense AND moves:d4') # and
dump('http://localhost:8888/solr/chessopening', 'names:defense NOT moves:d4') # not
dump('http://localhost:8888/solr/chessopening', 'defense d4') # default field + implicit or
dump('http://localhost:8888/solr/chessopening', 'defense AND d4') # default field + and
dump('http://localhost:8888/solr/chessopening', 'defense NOT d4') # default field + not    
# wildcard
dump('http://localhost:8888/solr/chessopening', 'names:def*')
dump('http://localhost:8888/solr/chessopening', 'moves:N*')

<?php

function dump($baseurl, $searchterm) {
    echo "search for : $searchterm\r\n";
    $curl = curl_init($baseurl . '/select?q=' . str_replace(' ', '+', $searchterm) . '&df=alltext');
    curl_setopt($curl, CURLOPT_HTTPHEADER, array('Accept: ' . 'application/json'));
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    $rawresp = curl_exec($curl);
    $resp = json_decode($rawresp);
    if($resp->response->docs) {
        foreach($resp->response->docs as $doc) {
            $names = $doc->names;
            $moves = $doc->moves;
            echo "  $names - $moves\r\n";
        }
    }
}


// all
dump('http://localhost:8888/solr/chessopening', "*:*");
// one
dump('http://localhost:8888/solr/chessopening', "names:defense");
dump('http://localhost:8888/solr/chessopening', "moves:d4");
// dual
dump('http://localhost:8888/solr/chessopening', "alltext:defense");
dump('http://localhost:8888/solr/chessopening', "alltext:d4");
dump('http://localhost:8888/solr/chessopening', "defense"); // default field
dump('http://localhost:8888/solr/chessopening', "d4"); // default field
// boolean
dump('http://localhost:8888/solr/chessopening', "names:defense moves:d4"); // implicit or
dump('http://localhost:8888/solr/chessopening', "names:defense AND moves:d4"); // and
dump('http://localhost:8888/solr/chessopening', "names:defense NOT moves:d4"); // not
dump('http://localhost:8888/solr/chessopening', "defense d4"); // default field + implicit or
dump('http://localhost:8888/solr/chessopening', "defense AND d4"); // default field + and
dump('http://localhost:8888/solr/chessopening', "defense NOT d4"); // default field + not
// wildcard
dump('http://localhost:8888/solr/chessopening', "names:def*"); 
dump('http://localhost:8888/solr/chessopening', "moves:N*");

?>

Article history:

Version Date Description
1.0 December 14th 2024 Initial version

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj