This is 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, let's 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, let's 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, two 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 four 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
{
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 specify 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="<" />
<MapItem Content="greater than" Value=">" />
<MapItem Content="less than or equal to" Value="<=" />
<MapItem Content="greater than or equal to" Value=">=" />
</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 two 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, 14th May, 2015 - Initial release
