A simple NLP query system for Car_Database

Introduction

The primary aim for this post is to present a simple NLP query system for Car_Database. Users can type in simple questions such as those shown below to consult the database:

• help
• what do you know?
• show me all dealers.
• list all brands.
• what models do you know?
  
• how many customers are there?
• get all first names, last names and phone numbers.

Database Source: https://github.com/dtaivpp/car_company_database

After getting input from the user, the system will process the sentence in the following ways:

1. Transfer the sentence into tokens of words, some punctuation marks are skipped.
2. Use simplification rules to convert the list of tokens into a simple sentence, which is also in form of a list of words.
3. Apply translation rules to convert the simple sentence into a command consisting of keywords.
4. If the command is a SQL command, further translate it into a formal SQL statement.
5. Execute the command, call Sqlite if necessary to retrieve data from Car database with the SQL statement built before.

In this post the engine can only generate simple SELECT and SELECT COUNT statements. Furthermore, only single table query can be preformed, that is queries involving relations between multiple tables cannot be handled yet.

Output

# swipl nlp01.pl Enter a query: |: help [help] [showHelp] --- Hi, I am a NLP system for Car_Database.db Query examples: |: What do you know? |: What brands do you know? |: How many models are there? |: Show me all dealers. |: list all customers first names and last names. |: quit --- Enter a query: |: what do you know [what] [showTables] --- I know the following information: models brands dealers customers --- Enter a query: |: what models do you know? [what,models] [sql,get,all,models] SELECT * FROM models row(1,The Blonde,23000,1) row(2,The Brunette,25000,1) row(3,The Red Head,29000,1) row(4,Hat,22000,2) row(5,Sweater,25000,2) row(6,T-Shirt,27000,2) row(7,Orange,15000,3) row(8,Blue,12000,3) row(9,Green,17000,3) row(10,LaFerrari,125000,4) row(11,450,75000,4) row(12,F12 Berlinetta,110000,4) row(13,F40,100000,4) row(14,Extra,30000,5) row(15,Too Much,35000,5) row(16,Beats,24000,6) row(17,Bars,35000,6) Enter a query: |: get all brands [get,all,brands] [sql,get,all,brands] SELECT * FROM brands row(1,Cover Squirrel) row(2,Supreme) row(3,Yellow) row(4,Ferrari) row(5,Boujiee) row(6,Freshest) Enter a query: |: list all dealers [list,all,dealers] [sql,get,all,dealers] SELECT * FROM dealers row(1,Joes Autos,123 Abc Lane Virginia) row(2,Priority X,999 Nein-Nein Virginia) row(3,Priority Y,404 Address Not Found) row(4,Priority Z,8675309 Jessies Mom) Enter a query: |: list all customers [list,all,customers] [sql,get,all,customers] SELECT * FROM customers row(1,Jeremy,Jacobs,Male,120000,1990-12-12,587619723,Jeremy@Gmail.com) row(2,Maria,Swabota,Female,60000,1980-06-15,-1012185205,Maria@Gmail.com) row(3,Jacob,Wong,Male,24000,1992-02-20,2129990234,WonTon@hotmail.com) row(4,Pitbull,Perez,Male,1200000,1985-12-01,-697592765,Pitbull@ymail.com) row(5,Minnie,Mouse,Female,200000,1950-03-01,-1047043605,MinnieMe@gmail.com) row(6,Jessica,Parker,Female,120000,1989-06-29,-49288296,JennyFromThePark@mymail.com) Enter a query: |: show all first names, last names and phone numbers [get,all,first_name,last_name,phone_number] [sql,get,all,first_name,last_name,phone_number] SELECT first_name,last_name,phone_number FROM customers row(Jeremy,Jacobs,587619723) row(Maria,Swabota,-1012185205) row(Jacob,Wong,2129990234) row(Pitbull,Perez,-697592765) row(Minnie,Mouse,-1047043605) row(Jessica,Parker,-49288296) Enter a query: |: show all dealer names [get,all,dealer_name] [sql,get,all,dealer_name] SELECT dealer_name FROM dealers row(Joes Autos) row(Priority X) row(Priority Y) row(Priority Z) Enter a query: |: how many models are there? [count,models] [sql,count,models] SELECT COUNT() FROM models row(17) Enter a query: |: bye [bye] [unknownQry] --- Sorry, I don't understand what you mean, please ask in another way. --- Enter a query: |: quit [quit] [quit] --- Bye. --- ?-

Source Code

There are two prolog files for the system: nlp01.pl holds the simplification and translation rules, while buildsql01.pl responsible for building and executing the SQL statements from the commands.

nlp01.pl

% A very simple NLP example % % lower level functions % -------------------- % convert a list of atoms into % a list of lower case atoms lower([Y|X1], [Z|X2]) :- downcase_atom(Y,Z), lower(X1,X2). lower([], []). % read file and split the content % into list of lines file_lines(File, Lines) :- setup_call_cleanup(open(File, read, In), stream_lines(In, Lines), close(In)). stream_lines(In, Lines) :- read_string(In, _, Str), split_string(Str, "\n", "", Lines). % simplification rules % -------------------- % key phrases sr([what,information|X], [showTables|X]). sr([what,info|X], [showTables|X]). sr([what,data|X], [showTables|X]). sr([how,many|X], [count|X]). sr([which|X], [get|X]). sr([show|X], [get|X]). sr([give|X], [get|X]). sr([find|X], [get|X]). % field names % eg. % [get,all,dealer,name] -> [get,all,dealer_name] sr([A,B|X], [C|X]) :- atom_concat(A,'_',S1), atom_concat(S1,B,C), table_col(_,C). % plural form % [get,all,dealer,names] -> [get,all,dealer_name] sr([A,B|X], [C|X]) :- atom_concat(A,'_',S1), atom_concat(S1,B,S2), atom_chars(S2,S3), reverse(S3,S4), S4 = [_|S5], reverse(S5,S6), atom_chars(C,S6), table_col(_,C). % stop words sr([and|X], X). sr([are|X], X). sr([the|X], X). sr([do|X], X). sr([you|X], X). sr([know|X], X). sr([me|X], X). sr([is|X], X). sr([have|X], X). sr([keep|X], X). sr([for|X], X). sr([of|X], X). sr([there|X], X). % removing punctuations % --------------------- rp(['#'|X], X). rp(['.'|X], X). rp([','|X], X). rp([':'|X], X). rp(['?'|X], X). rp(['!'|X], X). rp([''|X], X). % skip spaces % translation rules % ----------------- tr([what], [showTables]). tr([what,X], [sql,get,all,X]). tr([showTables], [showTables]). tr([count|X],[sql,count|X]). tr([list|X],[sql,get|X]). tr([get|X],[sql,get|X]). tr([greet], [greet]). tr([sysTime], [sysTime]). tr([sysDate], [sysDate]). tr([get,sysName], [sysName]). tr([get,sysTime], [sysTime]). tr([get,sysDate], [sysDate]). tr([get,help], [showHelp]). tr([help],[showHelp]). tr([quit], [quit]). % remove punctuations % -------------------------------- remove_punc(List, Result) :- rp(List, NewList), !, remove_punc(NewList, Result). remove_punc([W|Words], [W|NewWords]) :- remove_punc(Words, NewWords). remove_punc([], []). % simplify query to short sentence % -------------------------------- simplify(List, Result) :- sr(List, NewList), !, simplify(NewList, Result). simplify([W|Words], [W|NewWords]) :- simplify(Words, NewWords). simplify([], []). % translate short sentence into command % ------------------------------------- translate(ShSent, Cmmd) :- tr(ShSent, Cmmd), !. translate(_,[unknownQry]). % actions % ------- action([sql|C]) :- build(C,L), writeln(L), sql_exec(L,RL), forall(member(X,RL),writeln(X)), nl. action([showTables]) :- writeln('---'), writeln('I know the following information:'), findall(T,table_col(T,_),TL), list_to_set(TL,TL1), forall(member(X,TL1), (atom_concat(' ',X,X1), writeln(X1))), writeln('---'), nl. action([unknownQry]) :- writeln('---'), writeln('Sorry, I don\'t understand what you mean, please ask in another way.'), writeln('---'), nl. action([greet]) :- writeln('---'), writeln('I am fine, thank you.'), writeln('---'), nl. action([sysName]) :- writeln('---'), writeln('I am PL-1, a very smart AI, thank you for using me.'), writeln('---'), nl. action([sysTime]) :- get_time(T), format_time(string(Time), "%X", T), writeln('---'), writeln(Time), writeln('---'), nl. action([sysDate]) :- get_time(T), format_time(string(Date), "%x", T), writeln('---'), writeln(Date), writeln('---'), nl. action([showHelp]) :- writeln('---'), writeln('Hi, I am a NLP system for Car_Database.db'), writeln('Query examples:'), writeln(' |: What do you know?'), writeln(' |: What brands do you know?'), writeln(' |: How many models are there?'), writeln(' |: Show me all dealers.'), writeln(' |: list all customers first names and last names.'), writeln(' |: quit'), writeln('---'), nl. action([quit]) :- writeln('---'), writeln('Bye.'), writeln('---'), nl. % accept an atom in form of a sentence and % convert them into a list of atoms by % skipping all punctuations % such as (.), (,), (:), (?), (!), and spaces, % convert all atoms to lower case letters % clean_atoms(Atom, CleanAtoms) :- atom_chars(Atom, Cs), % split atom into atoms remove_punc(Cs, CleanCs), % obtain atoms w/o punctuation atomic_list_concat(CleanCs, A1), % combine into atom atomic_list_concat(A2, ' ', A1), % split atom to atoms, sep.: space remove_punc(A2, CA1), lower(CA1, CleanAtoms). % main programs % ------------- /* inactive mode */ go :- repeat, writeln('Enter a query: '), read_line_to_codes(user_input, Codes), atom_codes(L, Codes), % convert Codes to an atom clean_atoms(L, AL1), %writeln(AL1), simplify(AL1, ShSent), writeln(ShSent), translate(ShSent, Cmmd), writeln(Cmmd), action(Cmmd), Cmmd == [quit], !. /* batch mode */ go2 :- writeln('Enter a file name: '), read_line_to_codes(user_input, Codes), atom_codes(FN, Codes), % convert Codes to an atom file_lines(FN, Lines), % read file to lines member(L, Lines), writeln(L), clean_atoms(L, AL1), % split an atom into cleaned atoms simplify(AL1, ShSent), writeln(ShSent), translate(ShSent, Cmmd), writeln(Cmmd), action(Cmmd), nl, fail. % start go after this script is loaded. :- initialization(([buildsql01],go), program).

buildsql01.pl

table_col(models,model_name). table_col(models,model_base_price). table_col(brands,brand_name). table_col(dealers,dealer_name). table_col(customers,first_name). table_col(customers,last_name). table_col(customers,phone_number). % remove duplicated items in a list remove_duplicates([], []). remove_duplicates([Head | Tail], Result) :- member(Head, Tail), !, remove_duplicates(Tail, Result). remove_duplicates([Head | Tail], [Head | Result]) :- remove_duplicates(Tail, Result). % get columns and tables from a list of tokens % eg. % ?- get_col_table([get,all,first_name,last_name],L,C,T). % L = [get, all], % C = [first_name, last_name], % T = [customers, customers]. % % % handle column name token C gct(C,LF,CL,TL) :- table_col(T,C), CL = [C], TL = [T], LF = [], !. % handle table name token T gct(T,LF,CL,TL) :- table_col(T,_), CL = [], TL = [T], LF = [], !. % handle other types of token C gct(C,[C],[],[]). % get_col_table(L,LF,CL,TL) :- L = [H|Tail], gct(H,LF1,C,T), get_col_table(Tail,LF2,CL2,TL2), append(LF1,LF2,LF), append(C,CL2,CL3), append(T,TL2,TL3), remove_duplicates(CL3,CL), remove_duplicates(TL3,TL). get_col_table([],[],[],[]). % build SQL from a list of tokens % eg. % ?- build([get,first_name,last_name],L). % L = 'SELECT first_name,last_name FROM customers'. % ?- build([count,all,first_name,last_name],L). % L = 'SELECT COUNT() FROM customers'. % act_sql([count],'SELECT COUNT() FROM '). act_sql([count,all],'SELECT COUNT() FROM '). act_sql([get],'SELECT '). act_sql([get,all],'SELECT '). % build(L,SqlStr) :- % count, count all get_col_table(L,CommL,_,TabL), (CommL=[count]; CommL=[count,all]), act_sql(CommL, Sql1), atomic_list_concat(TabL,',',Sql3), atom_concat(Sql1,Sql3,SqlStr), !. build(L,SqlStr) :- % no columns are provided, % only tables are provided get_col_table(L,CommL,[],TabL), (CommL=[get]; CommL=[get,all]), act_sql(CommL, Sql1), atomic_list_concat(TabL,',',Sql3), atom_concat(Sql1,'* FROM ',S1), atom_concat(S1,Sql3,SqlStr), !. build(L,SqlStr) :- % both columns and tables % are provided get_col_table(L,CommL,ColL,TabL), (CommL=[get]; CommL=[get,all]), act_sql(CommL, Sql1), atomic_list_concat(ColL,',',Sql2), atomic_list_concat(TabL,',',Sql3), atom_concat(Sql1,Sql2,S1), atom_concat(S1,' FROM ',S2), atom_concat(S2,Sql3,SqlStr), !. % Reference: % https://www.swi-prolog.org/pack/file_details/prosqlite/doc/prosqlite.html openDb(DbHandle) :- [library(prosqlite)], Opt = [ext('db')], sqlite_connect('Car_Database',DbHandle,Opt). closeDb(DbHandle) :- sqlite_disconnect(DbHandle). % run sql script sql_exec(Sql, RL) :- openDb(DbHandle), findall(R,sqlite_query(DbHandle, Sql, R),RL), closeDb(DbHandle).

The NLP system has a batch mode which accepts a list of questions from an external file and answer them one by one. You may activate the batch mode as shown below:

# swipl nlp01.pl
Enter a query: 
|: quit
[quit]
[quit]
---
Bye.
---

?- go2.
Enter a file name: 
|: carQuery.txt

The file named carQuery.txt is the external file which contains a list of queries to be sent to the system, its content can be found below.

Sample Query # what do you know? # What information do you know? # What information do you have? # what data do you know? # what info do you know? # what data do you keep? # what are the dealers? # show me all model names. # show me all model names you know. # show me all models you know. # give me brand names # give 3 dealer names # get all model names # get all customers # list all customer names # list all customers names # list all first names and last names # How many models do you know? # How many brands do you have? # Which customers do you know?

The result of running the batch mode with the above file is given below:

Sample Query [sample,query] [unknownQry] --- Sorry, I don't understand what you mean, please ask in another way. --- [] [unknownQry] --- Sorry, I don't understand what you mean, please ask in another way. --- # what do you know? [what] [showTables] --- I know the following information: models brands dealers customers --- # What information do you know? [showTables] [showTables] --- I know the following information: models brands dealers customers --- # What information do you have? [showTables] [showTables] --- I know the following information: models brands dealers customers --- # what data do you know? [showTables] [showTables] --- I know the following information: models brands dealers customers --- # what info do you know? [showTables] [showTables] --- I know the following information: models brands dealers customers --- # what data do you keep? [showTables] [showTables] --- I know the following information: models brands dealers customers --- [] [unknownQry] --- Sorry, I don't understand what you mean, please ask in another way. --- # what are the dealers? [what,dealers] [sql,get,all,dealers] SELECT * FROM dealers row(1,Joes Autos,123 Abc Lane Virginia) row(2,Priority X,999 Nein-Nein Virginia) row(3,Priority Y,404 Address Not Found) row(4,Priority Z,8675309 Jessies Mom) # show me all model names. [get,all,model_name] [sql,get,all,model_name] SELECT model_name FROM models row(The Blonde) row(The Brunette) row(The Red Head) row(Hat) row(Sweater) row(T-Shirt) row(Orange) row(Blue) row(Green) row(LaFerrari) row(450) row(F12 Berlinetta) row(F40) row(Extra) row(Too Much) row(Beats) row(Bars) # show me all model names you know. [get,all,model_name] [sql,get,all,model_name] SELECT model_name FROM models row(The Blonde) row(The Brunette) row(The Red Head) row(Hat) row(Sweater) row(T-Shirt) row(Orange) row(Blue) row(Green) row(LaFerrari) row(450) row(F12 Berlinetta) row(F40) row(Extra) row(Too Much) row(Beats) row(Bars) # show me all models you know. [get,all,models] [sql,get,all,models] SELECT * FROM models row(1,The Blonde,23000,1) row(2,The Brunette,25000,1) row(3,The Red Head,29000,1) row(4,Hat,22000,2) row(5,Sweater,25000,2) row(6,T-Shirt,27000,2) row(7,Orange,15000,3) row(8,Blue,12000,3) row(9,Green,17000,3) row(10,LaFerrari,125000,4) row(11,450,75000,4) row(12,F12 Berlinetta,110000,4) row(13,F40,100000,4) row(14,Extra,30000,5) row(15,Too Much,35000,5) row(16,Beats,24000,6) row(17,Bars,35000,6) # give me brand names [get,brand_name] [sql,get,brand_name] SELECT brand_name FROM brands row(Cover Squirrel) row(Supreme) row(Yellow) row(Ferrari) row(Boujiee) row(Freshest) # give 3 dealer names [get,3,dealer_name] [sql,get,3,dealer_name] # get all model names [get,all,model_name] [sql,get,all,model_name] SELECT model_name FROM models row(The Blonde) row(The Brunette) row(The Red Head) row(Hat) row(Sweater) row(T-Shirt) row(Orange) row(Blue) row(Green) row(LaFerrari) row(450) row(F12 Berlinetta) row(F40) row(Extra) row(Too Much) row(Beats) row(Bars) # get all customers [get,all,customers] [sql,get,all,customers] SELECT * FROM customers row(1,Jeremy,Jacobs,Male,120000,1990-12-12,587619723,Jeremy@Gmail.com) row(2,Maria,Swabota,Female,60000,1980-06-15,-1012185205,Maria@Gmail.com) row(3,Jacob,Wong,Male,24000,1992-02-20,2129990234,WonTon@hotmail.com) row(4,Pitbull,Perez,Male,1200000,1985-12-01,-697592765,Pitbull@ymail.com) row(5,Minnie,Mouse,Female,200000,1950-03-01,-1047043605,MinnieMe@gmail.com) row(6,Jessica,Parker,Female,120000,1989-06-29,-49288296,JennyFromThePark@mymail.com) # list all customer names [list,all,customer,names] [sql,get,all,customer,names] # list all customers names [list,all,customers,names] [sql,get,all,customers,names] # list all first names and last names [list,all,first_name,last_name] [sql,get,all,first_name,last_name] SELECT first_name,last_name FROM customers row(Jeremy,Jacobs) row(Maria,Swabota) row(Jacob,Wong) row(Pitbull,Perez) row(Minnie,Mouse) row(Jessica,Parker) [] [unknownQry] --- Sorry, I don't understand what you mean, please ask in another way. --- # How many models do you know? [count,models] [sql,count,models] SELECT COUNT() FROM models row(17) # How many brands do you have? [count,brands] [sql,count,brands] SELECT COUNT() FROM brands row(6) # Which customers do you know? [get,customers] [sql,get,customers] SELECT * FROM customers row(1,Jeremy,Jacobs,Male,120000,1990-12-12,587619723,Jeremy@Gmail.com) row(2,Maria,Swabota,Female,60000,1980-06-15,-1012185205,Maria@Gmail.com) row(3,Jacob,Wong,Male,24000,1992-02-20,2129990234,WonTon@hotmail.com) row(4,Pitbull,Perez,Male,1200000,1985-12-01,-697592765,Pitbull@ymail.com) row(5,Minnie,Mouse,Female,200000,1950-03-01,-1047043605,MinnieMe@gmail.com) row(6,Jessica,Parker,Female,120000,1989-06-29,-49288296,JennyFromThePark@mymail.com) [] [unknownQry] --- Sorry, I don't understand what you mean, please ask in another way. --- false. ?-