Click here to Skip to main content
13,197,527 members (44,144 online)
Click here to Skip to main content
Add your own
alternative version

Stats

15.4K views
602 downloads
18 bookmarked
Posted 14 May 2015

Natural Language Interface to Database using SIML

, 14 May 2015
Rate this:
Please Sign up or sign in to vote.
Using SIML, a language designed for Digital Assistants, to create a Natural Language Interface to an SQL Database.

Introduction

An introductory article on implementing a simple Natural Language Interface to a Database using SIML which is a Markup Language designed for Digital Assistants, Chatbots and NLI for Databases, Games and Websites.

Prerequisites

If SIML is new to you I recommend reading the following articles.

Knowledge of C#, SQL and SIML (pronounced si mal) is a must before proceeding with this article..

Note: If you do not go through the aforementioned articles you may not be able to grasp the content of this article.

Unless stated otherwise Natural Language Interface, NLI, LUI or NLUI maybe used interchangeably in this article.

The Setup

Here's the idea

  • Using Visual Studio, we'll create a simple WPF application (with a TextBox, an Evaluate Button and a DataGrid)
  • On start-up the application will create a simple Database with an Employees table and fill it in with 10 employees and their details like (ID, Name, Age, Salary, Job)
  • We'll then load an SIML project into our SynBot class object and pass in some important values from our Database.
  • later we'll create an SQL adapter that will take an SQL string and evaluate it.
  • Lastly, we'll use the SIML knowledge base to interact with our Database

So firstly create a WPF application and call it NLI-Database. I am asking you to name your project NLI-Database because some of the code snippets ahead may use the namespace NLI_Database.

Before we hop in we'll have to add a reference to the Syn.Bot class library in our Project. To do so, in Visual Studio, click on TOOLS ->NuGet Package Manager ->Package Manager Console and type

Install-Package Syn.Bot

Once that's done we'll have the Bot library in our Project. Note that this ain't just a Bot library it's a specification compliant SIML interpreter. It's a single independent library so your hands won't be getting dirty here.

Now the Database.. Well lets get SQLite imported as well.

Again, in the Package Manager Console type

Install-Package System.Data.SQLite

Fantastic! Hopefully you've got all the references right.

C# Coding

The Database Utility

Because my laziness threshold has long been passed. Lets create a simple utility class DatabaseUtilty that we'll use during application start-up to create a simple Employees table and fill it in with some data. Add a new class file to your Project, name it DatabaseUtility and add the following lines of code.

public class DatabaseUtility
{
   private const string DataSource = "EmployeesTable.db";
   public SQLiteCommand Command { get; set; }
   public SQLiteConnection Connection { get; set; }

   public void Initialize()
   {
       if(File.Exists(DataSource))File.Delete(DataSource);
       Connection = new SQLiteConnection { ConnectionString = "Data Source=" + DataSource };
       Connection.Open();
       ExecuteCommand("CREATE TABLE IF NOT EXISTS EMPLOYEES (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) NOT NULL, Job VARCHAR(10), Age INTEGER NOT NULL, Salary INTEGER NOT NULL);");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(1, 'Lincoln', 'Manager', 43, 54000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(2, 'George', 'CEO', 46, 75000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(3, 'Rick', 'Admin', 32, 18000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(4, 'Jorge', 'Engineer', 28, 35000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(5, 'Ivan', 'Tech', 23, 34000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(6, 'Mark', 'Tech', 25, 34000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(7, 'Vincent', 'Support', 21, 20000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(8, 'Carl', 'Support', 20, 20000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(9, 'Marco', 'Tech', 24, 34000)");
       ExecuteCommand("INSERT INTO EMPLOYEES VALUES(10, 'Craig', 'Admin', 25, 18000)");
   }
   public void ExecuteCommand(string commandText)
   {
       Command = new SQLiteCommand(Connection) {CommandText = commandText};
       Command.ExecuteNonQuery();
   }
   public void Close()
   {
       Command.Dispose();
       Connection.Dispose();
   }
}

The Initialize method in the code above checks if the file EmployeesTable.db exists or not. If it exists then it just deletes the file and creates a new one. After that's done the code then adds some dummy employee(s) details into the Employees table .

SQL Adapter

Time to create our first SIML Adapter for SQL. Create a new folder in your Solution and name it Adapter. In this folder add a new class file and call it SqlAdapter. The SqlAdapter class must implement the IAdapter interface (found in the Syn.Bot library ) and it's this interface that glues your application to SIML.

public class SqlAdapter : IAdapter
{
    private readonly MainWindow _window;
    public SqlAdapter(MainWindow window)
    {
        _window = window;
    }
    public bool IsRecursive { get { return true; } }
    public XName TagName { get { return Specification.Namespace.X + "Sql"; } }
    public string Evaluate(Context parameter)
    {
        _window.UpdateDataGrid(parameter.Element.Value);
        return string.Empty;
    }
}

Yes, that's all there is to an SqlAdapter.  So what does it do ?

Well, if you've read the SIML article I wrote previously you'll know that SIML uses an XML compliant construct. What this adapter allows us to do is to use <x:Sql>Some SQL here</x:Sql> within our SIML code. Which upon evaluation, by the interpreter, will call the Evaluate function in the Adapter code above.  You may notice that the Evaluate function in the above code is making a weird call to some UpdateDataGrid function which is supposedly going to be within the MainWindow class. I'll come to that later in this article.

Column Sets

A Set in SIML is a collection of words or sentences. Once a set is created, with a unique name, we can use the name of the set to specify the collection of words we wish to capture in an SIML pattern.

A set "EMP-NAME" will allow us to capture an employee's name. Likewise a set "EMP-JOB" will allow us to capture all the unique roles played by the employees in the Employees table.

Go ahead and create a folder in your Solution and name it Sets. Add a new class file to it and call it NameSet. The NameSet class must implement the ISet interface (found in Syn.Bot library).

public class NameSet : ISet
{
    private readonly HashSet<string> _nameSet;
    public NameSet(DatabaseUtility databaseUtility)
    {
        _nameSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        databaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
        var reader = databaseUtility.Command.ExecuteReader();
        while (reader.Read())
        {
            _nameSet.Add(reader["name"].ToString());
        }
        reader.Close();
    }
    public bool Contains(string item)
    {
        return _nameSet.Contains(item);
    }
    public string Name { get { return "Emp-Name"; }}
    public IEnumerable<string> Values { get { return _nameSet; } }
}

Every SIML Set has a unique name and returns enumerable string values. As SIML sets aren't allowed to hold duplicate values we'll use a HashSet to store the names of all the employees in our Employees table. Yes, 2 or more employees in a company can have similar names but it doesn't matter when the sole purpose of an SIML Set is to facilitate pattern matching.

Just like the NameSet we'll create 4 more Sets for ID, Job, Age and Salary.

AgeSet

public class AgeSet : ISet
{
    private readonly HashSet<string> _ageSet;
    public AgeSet(DatabaseUtility databaseUtility)
    {
        _ageSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        databaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
        var reader = databaseUtility.Command.ExecuteReader();
        while (reader.Read())
        {
            _ageSet.Add(reader["Age"].ToString());
        }
        reader.Close();
    }
    public bool Contains(string item)
    {
        return _ageSet.Contains(item);
    }
    public string Name { get { return "Emp-Age"; }}
    public IEnumerable<string> Values { get { return _ageSet; } }
}

IdSet

public class IdSet : ISet
{
    private readonly HashSet<string> _idSet;
    public IdSet(DatabaseUtility databaseUtility)
    {
        _idSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        databaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
        var reader = databaseUtility.Command.ExecuteReader();
        while (reader.Read())
        {
            _idSet.Add(reader["ID"].ToString());
        }
        reader.Close();
    }
    public bool Contains(string item)
    {
        return _idSet.Contains(item);
    }
    public string Name { get { return "Emp-ID"; }}
    public IEnumerable<string> Values { get { return _idSet; } }
}

JobSet

public class JobSet : ISet
{
    private readonly HashSet<string> _jobSet;
    public JobSet(DatabaseUtility databaseUtility)
    {
        _jobSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        databaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
        var reader = databaseUtility.Command.ExecuteReader();
        while (reader.Read())
        {
            _jobSet.Add(reader["Job"].ToString());
        }
        reader.Close();
    }
    public bool Contains(string item)
    {
        return _jobSet.Contains(item);
    }
    public string Name { get { return "Emp-Job"; }}
    public IEnumerable<string> Values { get { return _jobSet; } }
}

SalarySet

public class SalarySet : ISet
{
    private readonly HashSet<string> _salarySet;
    public SalarySet(DatabaseUtility databaseUtility)
    {
        _salarySet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        databaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
        var reader = databaseUtility.Command.ExecuteReader();
        while (reader.Read())
        {
            _salarySet.Add(reader["Salary"].ToString());
        }
        reader.Close();
    }
    public bool Contains(string item)
    {
        return _salarySet.Contains(item);
    }
    public string Name { get { return "Emp-Salary"; }}
    public IEnumerable<string> Values { get { return _salarySet; } }
}

GUI

 The GUI should look something like the following.

There's an Input box, an Evaluate button and a DataGrid. Ignore the Examples tab for now,

Within the main Grid of MainWindow.xaml add the following. Yes! there are going to be some undefined symbols in there but we'll fix them right after you've finished typing.

<TabControl>
    <TabItem Header="Interaction">
        <Grid>
            <Grid.RowDefinitions>
                <RowDefinition Height="35"/>
                <RowDefinition Height="53*"/>
                <RowDefinition Height="232*"/>
            </Grid.RowDefinitions>
            <Grid>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="414*"/>
                    <ColumnDefinition Width="100"/>
                </Grid.ColumnDefinitions>
                <TextBox Name="InputBox" TextAlignment="Center" CharacterCasing="Upper" KeyDown="InputBox_OnKeyDown"/>
                <Button Name="ExecuteButton" Content="Evaluate" Grid.Column="1" Click="ExecuteButton_OnClick"/>
            </Grid>
            <Label Grid.Row="1"  Name="ResponseLabel" Content="No Response Yet" VerticalContentAlignment="Center"/>
            <DataGrid  Name="EmployeeGrid" Grid.Row="2"  FontSize="14" />
        </Grid>
    </TabItem>
</TabControl>

Great! You've added the XAML code that makes up the GUI. Now replace the code-behind with the following code.

using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Input;
using System.Xml.Linq;
using NLI_Database.Adapter;
using NLI_Database.Sets;
using Syn.Bot;

namespace NLI_Database
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow
    {
        public DatabaseUtility DatabaseUtility { get; private set; }
        public SynBot Bot { get; private set; }
        public MainWindow()
        {
            InitializeComponent();
            Bot = new SynBot();
            DatabaseUtility = new DatabaseUtility();
            DatabaseUtility.Initialize();
            UpdateDataGrid("SELECT * From Employees");
            Bot.Sets.Add(new NameSet(DatabaseUtility));
            Bot.Sets.Add(new JobSet(DatabaseUtility));
            Bot.Sets.Add(new SalarySet(DatabaseUtility));
            Bot.Sets.Add(new AgeSet(DatabaseUtility));
            Bot.Sets.Add(new IdSet(DatabaseUtility));
            Bot.Adapters.Add(new SqlAdapter(this));
            var simlFiles = Directory.GetFiles(Path.Combine(Directory.GetCurrentDirectory(), "SIML"), "*.siml", SearchOption.AllDirectories);
            foreach (var simlDocument in simlFiles.Select(XDocument.Load))
            {
                Bot.AddSiml(simlDocument);
            }
        }

        public void UpdateDataGrid(string sql)
        {
            var dataSet = new DataSet();
            var dataAdapter = new SQLiteDataAdapter(sql, DatabaseUtility.Connection);
            dataAdapter.Fill(dataSet);
            EmployeeGrid.ItemsSource = dataSet.Tables[0].DefaultView;
        }

        private void ExecuteButton_OnClick(object sender, RoutedEventArgs e)
        {
            var result = Bot.Chat(string.IsNullOrEmpty(InputBox.Text) ? "clear" : InputBox.Text);
            ResponseLabel.Content = result.BotMessage;
            InputBox.Clear();
        }

        private void InputBox_OnKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Return)
            {
                ExecuteButton_OnClick(null, null);
            }
        }
}

The Constructor in the code above instantiates the Bot and DatabaseUtility variables, calls the Initialize method of the DatabaseUtility object, adds the SqlAdapter and the previously created SIML Sets to the Bot object and finally loads all the SIML files found in the SIML folder which is in the root directory of our application.

The UpdateDataGrid method takes an SQL string and evaluates it. Once the evaluation is done it refreshes the ItemSource property of the EmployeeGrid.

SIML Coding

Before proceeding here I am going to assume you've already read at-least the first SIML article I wrote.

Because pattern recognition is way beyond the scope of this article I am going to show some simple SQL command usage within my SIML.  But there's nothing to be bothered about, as the Project attached to this article has a lot of patterns predeclared which can be referred by opening the SIML Project in Chatbot Studio. That's one of the reasons why I've put my SIML project right before your eyes in the SIML directory and haven't used an SIML package.

Create a new folder named SIML within the root directory of your application. It maybe the Bin/Debug or Bin/Release directory depending on your project's Output path settings.

If you haven't got Chatbot Studio yet download it from here. Create a new project by pressing Ctrl+Shift+N. Fill in the required details and select English Minimum as the default template. Save the project within the SIML folder you've just created.

Click on the file Hello Bot and you'll see a simple SIML document. This document has just one SIML Model. Which matches the pattern Hello Bot and generates the response Hello User!. Remove this Model by selecting it and pressing Delete.

Add a new namespace x to the root element of the SIML document by right clicking just before the > of the <Siml> tag and selecting Insert -> Attributes -> xmlns:x.

A namespace xmlns:x="http://syn.co.in/2014/siml#external" will be inserted. This namespace is of great significance as we are going to make rigorous use of the x namespace in our SIML code.

Simple Employee related Queries

Now press Alt+M to insert a new SIML Model and within the Pattern tag add What is the age of [EMP-NAME].

The above pattern matches:

  • What is the age of Rick?
  • What is the age of Lincoln?
  • What is the age of Jorge? and so on...

If you recall we previously created a NameSet class that derived from the ISet interface. The name of the Set we opted to return was Emp-Name. In SIML you can specifiy a set (within an SIML pattern) by enclosing the name of the set within square brackets. And that's exactly what we are doing here.

Within the Response element type Age of Employee <Match />. followed by:

<x:Sql>SELECT DISTINCT Age FROM Employees WHERE UPPER(Name) LIKE UPPER('%<Match />%')</x:Sql>

Your new SIML Model should now look similar to:

<Model>
  <Pattern>WHAT IS THE AGE OF [EMP-Name]</Pattern>
  <Response>
    Age of the employee <Match />.
    <x:Sql>SELECT DISTINCT Age FROM Employees WHERE UPPER(Name) LIKE UPPER('%<Match />%')</x:Sql>
   </Response>
</Model>

Press Ctrl+S to save the document.

Now go ahead and start your application and type "What is the age of Rick" the output should look something like the following.

That's cute how about some more patterns for the same type of query ? Change your first SIML Model to...

<Model>
  <Pattern>
    <Item>WHAT IS THE AGE OF [EMP-NAME]</Item>
    <Item>HOW OLD IS [EMP-NAME]</Item>
    <Item>$ AGE OF [EMP-NAME]</Item>
  </Pattern>
  <Response>
    Age of the employee <Match />.
    <x:Sql>SELECT DISTINCT Age FROM Employees WHERE UPPER(Name) LIKE UPPER('%<Match />%')</x:Sql></Response>
</Model>

Now the following queries would produce the same result.

  • How old is rick?
  • What is the age of rick ?
  • HAKUNA MATATA age of rick?

You can now go ahead and create a number of patterns for Salary, Job and ID related queries. (Download attached to this article has a lot of predefined patterns)

Here's another example SIML code that responds to questions like Who is Rick?, Who is Jorge?

<Model>
  <Pattern>WHO IS [EMP-NAME]</Pattern>
  <Response>
    Employee(s) with the name <Match />.
    <x:Sql>SELECT * FROM Employees WHERE UPPER(Name) LIKE UPPER('%<Match />%')</x:Sql>
  </Response>
</Model>

Listing information based on some Predicate

Let us now create an Operator Set.

Like I mentioned earlier an SIML set is a unique collection of words or sentences which facilitates pattern matching. Under the File Explorer (in Chatbot Studio) select the Sets file and add the following

<Set Name="operator">
  <Item>equal to</Item>
  <Item>less than</Item>
  <Item>greater than</Item>
  <Item>less than or equal to</Item>
  <Item>greater than or equal to</Item>
</Set>

Now click on Maps and add the following.

<Map Name="operator">
  <MapItem Content="equal to" Value="=" />
  <MapItem Content="less than" Value="&lt;" />
  <MapItem Content="greater than" Value="&gt;" />
  <MapItem Content="less than or equal to" Value="&lt;=" />
  <MapItem Content="greater than or equal to" Value="&gt;=" />
</Map>

An SIML Map on the other hand enables us to map a given value to some other value at run-time. In the code above equal to gets mapped to the symbol = , less than gets mapped to < , greater than gets mapped to the symbol > and so on...

Let us now add a new SIML Model that will allow us to fetch the details of employee(s) whose age or salary is greater than, less than or equal to some specified value.

  • List all employees whose age is less than 40
  • List all employees whose salary is greater than 18000
<Model>
  <Pattern>LIST ALL EMPLOYEES * (AGE|SALARY) IS [OPERATOR] *</Pattern>
  <Response>
    <x:Sql>select * from Employees where <Match Index="2" /><Map Get="operator"><Match Index="3" /></Map><Match Index="4" /></x:Sql>
  </Response>
</Model>

 

Save the document by pressing Ctrl+S, run the WPF Application and type List all employees whose age is greater than 30

Now try List all Employees whose salary is less than 30000

Changing information in the Database

Now we'll try to change the age of an employee given the ID (using the name would be absurd as 2 or more employees in a company can have the same name) of the employee and the new age value.

Add the following SIML Model to your SIML document.

<Model>
  <Pattern>(CHANGE|SET|UPDATE) THE AGE OF ID [EMP-ID] TO *</Pattern>
  <Response>
    Age of ID <Match Index="2" /> has now changed to <Match Index="3" />.
    <x:Sql>UPDATE EMPLOYEES SET AGE=<Match Index="3" /> WHERE ID=<Match Index="2" />;</x:Sql><x:Sql>SELECT * FROM EMPLOYEES WHERE ID=<Match Index="2" />;</x:Sql>
  </Response>
</Model>

Save the document, restart your WPF application and try typing Change the age of ID 3 to 34

Alright, so that wraps it up for this initial release. You'll probably be best trying and experimenting with SIML on your own. You may use the attached project for your own purpose anyway you see fit.

Points of Interest

Pros of using SIML as an interface is that to update a pattern you don't have to make any changes to your application code. The abstraction layer between your SIML Natural Language Interface and the Database is pretty solid and holds well on many scenarios.

Your SIML code stays re-usable even if you for some reason changed the structure of your Employees table. On top of that the SIML interpreter is platform independent so don't be shy to experiment the same in Mono under Linux or Mac.

A word of advice. Do not just blindly connect my setup to some database of yours. Chances are you may whack things up. Always backup your Database and then go around playing with sensitive stuff like Natural Language Interface. I myself messed up a lot of before coming up with a working prototype.

Apart from being a NLI to Database this setup may simulate some search-engine features. In the example Project attached to this article if you just type in the Name, ID, Age or Salary of an employee you'll get the rows matching the given value.

 

History

Thursday, May 14, 2015 - Initial Release

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DaveMathews
United States United States
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionAbout siml NLI database Pin
iqballll9-Jun-17 4:06
memberiqballll9-Jun-17 4:06 
AnswerRe: About siml NLI database Pin
DaveMathews9-Jun-17 6:06
memberDaveMathews9-Jun-17 6:06 
Questionhelp! Pin
Member 129950925-Jun-17 7:53
memberMember 129950925-Jun-17 7:53 
AnswerRe: help! Pin
DaveMathews9-Jun-17 6:03
memberDaveMathews9-Jun-17 6:03 
GeneralRe: help! Pin
Member 1299509214-Jun-17 22:48
memberMember 1299509214-Jun-17 22:48 
GeneralRe: help! Pin
DaveMathews16-Jun-17 16:37
memberDaveMathews16-Jun-17 16:37 
QuestionNeed Help please!!!! Pin
Sandia Chanchal28-May-17 20:56
memberSandia Chanchal28-May-17 20:56 
AnswerRe: Need Help please!!!! Pin
DaveMathews9-Jun-17 6:01
memberDaveMathews9-Jun-17 6:01 
QuestionASP.NET C# Pin
davidcandia10-May-17 23:49
memberdavidcandia10-May-17 23:49 
AnswerRe: ASP.NET C# Pin
DaveMathews9-Jun-17 6:00
memberDaveMathews9-Jun-17 6:00 
QuestionI'm working on a project like it Pin
Sandia Chanchal7-May-17 1:38
memberSandia Chanchal7-May-17 1:38 
AnswerRe: I'm working on a project like it Pin
DaveMathews9-Jun-17 5:58
memberDaveMathews9-Jun-17 5:58 
GeneralMy vote of 5 Pin
Arkitec29-Jun-15 14:50
memberArkitec29-Jun-15 14:50 
GeneralMy vote of 5 Pin
DrABELL13-Jun-15 16:13
professionalDrABELL13-Jun-15 16:13 
GeneralMy vote of 5 Pin
Camilo Reyes14-May-15 6:39
professionalCamilo Reyes14-May-15 6:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171020.1 | Last Updated 14 May 2015
Article Copyright 2015 by DaveMathews
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid