/******************************************************************************
*
*   File:     sql_db.pl
*   Authors:  Bob Wall & Jeff Elser                      CS 536   Assignment 1
*   Date:     02/12/2004
*
*
*   Prolog program that acts as a simple database with a limited SQL-based
*   interface.  
*
*   The following SQL statements are supported:
*   CREATE TABLE a (col1, col2, ... coln);
*   DROP   TABLE a;
*   SHOW   TABLES;
*   INSERT INTO a VALUES (val1, val2, ... valn);
*   SELECT cola, colb ... FROM a;
*   SELECT * FROM a;
*
*   The program reads statements from stdin and processes them until you enter
*   QUIT;
*
*   CAVEATS:
*    -  The ';' is the statement terminator, and you will need to enter it.
*
*    -  SQL is case-insensitive, so
*   SELECT X FROM A;
*   is exactly the same as
*   select x from a;
*
*    -  This is not a strict SQL parser - it ignores the '(', ')', ',', in
*   the queries, so it can't tell the difference between
*   CREATE TABLE x (a, b, c)
*   and
*   create table x a b c
*
*   We also don't enforce other syntax requirements such as names begin-
*   ning with a letter - you can call a table or column 1A if you want.
*
*    -  We are not bothering with the types of columns in the table, so please
*   don't try 
*   CREATE TABLE A (x INT, y VARCHAR(20), z DATE);
%
%   -   There isn't the concept of a NULL, so you can't leave the value for
%   a database column unspecified.
%
%   -   If you try to insert the same values into a table twice, it will
%   only remember one of them.
*   
******************************************************************************/


%------------------------------------------------------------------------------
%
%   This section of the code is adapted from the Prolog tutorial referenced in
%   the assignment, which can be found at
%
%   http://cs.wwc.edu/~cs_dept/KU/PR/Prolog.html
%
%   
%   Anything that is not a single-character word or an alpha-numeric is ignored;
%   for instance, '(', ')', and ','.
%
%------------------------------------------------------------------------------

%  Read a sentence and return a list of words.

read_in([W | Ws]) :- get_byte(C), read_word(C, W, C1), rest_sent(W, C1, Ws).

%  Given a word and the next character, read in the rest of the sentence

rest_sent(W, _, []) :- lastword(W).
rest_sent(_, C, [W1 | Ws]) :- read_word(C, W1, C1), rest_sent(W1, C1, Ws).

read_word(C, W, C1) :- single_character(C), !, name(W, [C]), get_byte(C1). read_word(C, W, C2) :- in_word(C, NewC), get_byte(C1),
       rest_word(C1, Cs, C2), name(W, [NewC | Cs]). read_word(_, W, C2) :- get_byte(C1), read_word(C1, W, C2).

rest_word(C, [NewC | Cs], C2) :- in_word(C, NewC), !, get_byte(C1), 
     rest_word(C1, Cs, C2).
rest_word(C, [], C).


%  These are single character words.

single_character(42).      %  *  -  used for select all columns
single_character(59).      %  ;  -  SQL statement terminator
single_character(61).      %  =  -  used in SQL joins


%  These characters can appear within a word.  Note that all upper-case letters %  are converted to lower-case, which is handy since SQL is case-insensitive.

in_word(C, C) :- C > 96, C < 123.   %  a,b,...,z
in_word(C, L) :- C > 64, C < 91, L is C + 32.   %  A,B,...,Z
in_word(C, C) :- C > 47, C < 58.    %  0,1,...,9

%  This word terminates an SQL query.

lastword(';').



%------------------------------------------------------------------------------
%   Take SQL command (as a word list) and determine which SQL statement is
%   being executed.
%   Options are
%   CREATE ...;
%   INSERT ...;
%   SELECT ...;
%   DROP   ...;
%   SHOW   ...;
%   HELP;
%   QUIT;
%
%   Each predicate takes the user's input as a list of words.
%------------------------------------------------------------------------------
process_sql_cmd([create | Rest]) :-
    process_create(Rest).

process_sql_cmd([insert | Rest]) :-
    process_insert(Rest).

process_sql_cmd([select | Rest]) :-
    process_select(Rest).

process_sql_cmd([drop | Rest]) :-
    process_drop(Rest).

process_sql_cmd([show | Rest]) :-
    process_show(Rest).

process_sql_cmd([help | _]) :-
    process_help.

process_sql_cmd([First | _]) :-
    First \== quit,
    format('Unrecognized command ~w.~n~n', [First]).


%  This clause needs to be the last one for process_sql_list - since it fails, %  if it is earlier, it will fall through to the next one.

process_sql_cmd([quit | _]) :-
    fail.



%------------------------------------------------------------------------------
%   Handle an SQL CREATE TABLE statement.  First, make sure that the TABLE
%   keyword was present after the CREATE.  If so, check to see if the table
%   has already been defined.  If not, create it (add it to the table_defined
%   facts).
%------------------------------------------------------------------------------
process_create([table | Rest]) :-
    process_create_table(Rest).

process_create(_) :-
    write('Missing TABLE keyword.'), nl, nl.


process_create_table([Table_name | _]) :-
    table_defined(Table_name, _),
    format('~nError - table ~w already exists.~n~n', [Table_name]).

process_create_table([_ | []]) :-
    format('~nError - must specify at least one column name.~n~n', []).

process_create_table([Table_name | Columns]) :-
    format('~nCreated table ~w.~n~n', [Table_name]),
    assert(table_defined(Table_name, Columns)),
    length(Columns, Num_cols),
    call(dynamic(Table_name/Num_cols)).



%------------------------------------------------------------------------------
%   Handle an SQL DROP TABLE statement.  First, make sure that the TABLE
%   keyword was present after the DROP.  If so, check to see if the table
%   has already been defined.  If so, remove any facts for that table, then
%   drop it (remove it from the table_defined facts).
%------------------------------------------------------------------------------
process_drop([table | Rest]) :-
    process_drop_table(Rest).

process_drop(_) :-
    write('Missing TABLE keyword.'), nl, nl.


process_drop_table([Table_name | _]) :-
    table_defined(Table_name, Columns),     %  Make sure table was defined
    length(Columns, Num_cols),
    call(abolish(Table_name/Num_cols)),     %  Remove all clauses for table
    retract(table_defined(Table_name,_)),   %  Remove definition of table
    format('~nTable ~w dropped.~n~n', [Table_name]).

process_drop_table([Table_name | _]) :-
    format('~nError - table ~w does not exist.~n~n', [Table_name]).



%------------------------------------------------------------------------------
%   Handle an SQL SHOW TABLES statement.  First, make sure that the TABLES
%   keyword is present after the SHOW.  If so, generate a list of all the
%   tables that are defined, by querying the tables_defined() predicate.
%------------------------------------------------------------------------------
process_show([tables | _]) :-
    format('~n   Table~t ~t~32| Columns~n   -----~t ~t~32| -------~n'),
    \+ process_show_tables,
    nl.

process_show(_) :-
    write('Missing TABLES keyword.'), nl, nl.


process_show_tables :-
    table_defined(Table_name, Columns),
    format('   ~w~t ~t~32| ~w~n', [Table_name, Columns]),
    fail.



%------------------------------------------------------------------------------
%   Handle an SQL INSERT statement.  Make sure the INTO keyword is present
%   after INSERT, then make sure that the table exists, then make sure that
%   the user provided a value for each column in the table.  If all that is
%   successful, add a fact to the database with the table name as the predi-
%   cate
%------------------------------------------------------------------------------
process_insert([into | Rest]) :-
    process_insert_into(Rest).

process_insert(_) :-
    write('Missing INTO keyword.'), nl, nl.


process_insert_into([Table_name | Rest]) :-
    table_defined(Table_name, _),
    process_insert_into_table(Table_name, Rest).

process_insert_into([Table_name | _]) :-
    format('~nError - table ~w does not exist.~n~n', [Table_name]).

process_insert_into_table(Table_name, [values | Rest]) :-
    table_defined(Table_name, Columns),     %  Get list of table's columns
    length(Columns, Num_cols),
    length(Rest, Num_ins_cols),             %  Make sure user supplied values
    Num_cols =:= Num_ins_cols,              %    for all columns in table

    %  Build the command that will assert the specified fact - the predicate
    %  is the table name, and the predicate arguments are the column values.

    Pred_cmd   =.. [Table_name | Rest],
    Assert_cmd =.. [assert, Pred_cmd],
    call(Assert_cmd),

    format('~nInserted ~w into ~w.~n~n', [Rest, Table_name]).

process_insert_into_table(Table_name, [values | Rest]) :-
    table_defined(Table_name, Columns),     %  Make sure table was defined
    length(Columns, Num_cols),
    length(Rest, Num_ins_cols),     %  Make sure user supplied values
    format('~nError - table ~w has ~d columns - you only provided ~d.~n~n',
       [Table_name, Num_cols, Num_ins_cols]).

process_insert_into_table(_, _) :-
    write('Missing VALUES keyword.'), nl, nl.


%------------------------------------------------------------------------------
%   Utility function to create a list of bind variables corresponding to a list
%   of column names.
%------------------------------------------------------------------------------
create_vars([], []).

create_vars([Col | Rest], List) :- 
    concat_atom(['QV_', Col], A),
    atom_to_term(A, B, _),
    create_vars(Rest, List2),
    List = [ B | List2 ].


%------------------------------------------------------------------------------
%   Handle an SQL SELECT statement.  Special handling for a "SELECT * " -
%   this is equivalent to listing all columns in the table.  Verify that after
%   the column list there is a FROM keyword, then make sure that the table
%   name is valid.  If all this works, create a list of bind variables,
%   formulate a query of the predicate representing the table, execute the
%   query for each match, and output the results.
%------------------------------------------------------------------------------
process_select([ '*' | Rest]) :-        %  Got select * - select all columns
    process_select_all(Rest).

process_select([ from | _]) :-          %  Missing list of column names
    write('Missing list of select columns.'), nl, nl.

process_select([ First | Rest]) :-      %  Got start of list of column names
    process_select_list(Rest, [First]).


%   Consume FROM keyword and continue processing.
process_select_all([from | Rest]) :-
    process_select_all_from(Rest).

process_select_all(_) :-
    write('Missing FROM keyword.'), nl, nl.


%   Get "SELECT * FROM <table>" - make sure table name is valid, create a bind
%   variable for each of the tables columns, build the statement that will run
%   the query, and execute it.
process_select_all_from([Table_name | _]) :-
    table_defined(Table_name, Columns),     %  Make sure table was defined
    create_vars(Columns, Col_vars),
    format('~nSelecting ~w from ~w.~n~n', [Columns, Table_name]),
    Pred_query =.. [Table_name | Col_vars],
    \+ process_select_rows(Pred_query, Col_vars).

process_select_all_from([Table_name | _]) :-
    format('~nError - table ~w does not exist.~n~n', [Table_name]).


%   Consume FROM keyword and continue processing - we now have the complete
%   list of columns specified in the query, but they are in reverse order.
process_select_list([from | Rest], Col_list_rev) :-
    reverse(Col_list_rev, Col_list),
    process_select_list_from(Rest, Col_list).

process_select_list([], _) :-
    write('Missing FROM keyword.'), nl, nl.

process_select_list([First | Rest], Col_list) :-
    process_select_list(Rest, [First | Col_list]).


%   Have "SELECT <col_list> FROM <table>" - make sure table is valid, then
%   decide which of the table's output columns were chosen to be displayed.
process_select_list_from([Table_name | _], Col_list) :-
    table_defined(Table_name, Columns),     %  Make sure table was defined
    process_select_list_cols(Table_name, Columns, Col_list).

process_select_list_from([Table_name | _], _) :-
    format('~nError - table ~w does not exist.~n~n', [Table_name]).


%   Make sure that all columns in the select list are valid for the table.  If
%   so, create a bind variable for each of the table's columns, then go into
%   a recursive loop to get the bind variable associated with each output
%   column.
process_select_list_cols(Table_name, All_columns, Col_list) :-
    subset(Col_list, All_columns),
    create_vars(All_columns, Col_vars),
    format('~nSelecting ~w (of ~w) from ~w.~n~n',
           [Col_list, All_columns, Table_name]),
    choose_output_cols(Col_list, All_columns, Col_vars, Table_name, []).

process_select_list_cols(Table_name, Columns, Col_list) :-
    format('~nError - one or more column names in ~w are not in the~n',
           [Col_list]),
    format('column list ~w of table ~w.~n~n', [Columns, Table_name]).

%------------------------------------------------------------------------------
%   For each column in list defined for table, decide if it is one of the
%   selected columns.  If so, add its corresponding bind variable to the list
%   of values to display.  (A bind variable is added to the query for every
%   column in the table, but we only want to display the selected ones).
%   When entire list is built, fire off the query.
%------------------------------------------------------------------------------
%   Whole list of output columns consumed - generate & run query.
choose_output_cols([], _, Col_vars, Table_name, Query_vars) :-
    reverse(Query_vars, Query_vars2),
    Pred_query =.. [Table_name | Col_vars],
    \+ process_select_rows(Pred_query, Query_vars2).

%   Find position of next output column in list of columns from table -
%   add corresponding bind variable to query output list.
choose_output_cols([Next | Rest], All_col_list, Col_vars, Table_name,
                   Query_vars) :-
    nth1(N, All_col_list, Next),
    nth1(N, Col_vars, Next_col_var),
    choose_output_cols(Rest, All_col_list, Col_vars, Table_name,
                       [Next_col_var | Query_vars]).



%------------------------------------------------------------------------------
%   Run the query and output the results, whether it was for a subset of the
%   table's columns or all of them.
%------------------------------------------------------------------------------
process_select_rows(Pred_query, Col_list) :-
    call(Pred_query),
    write_results(Col_list),
    fail.

write_results([]) :- nl.

write_results([First | Rest]) :-
    sformat(Str, '   ~w~t ~t~15|', [First]),
    write(Str),
    write_results(Rest).


%------------------------------------------------------------------------------
%   Handle a HELP statement.
%------------------------------------------------------------------------------
process_help :-
    nl,
    write('Supported commands:'), nl,
    write('   CREATE TABLE <tbl_name> ( <col_name> [, <col_name>]* );'), nl,
    write('   DROP   TABLE <tbl_name>;'), nl,
    write('   SHOW   TABLES;'), nl,
    write('   INSERT INTO  <tbl_name> VALUES ( <val> [, <val>]* );'), nl,
    write('   SELECT <col> [, <col>]* FROM <tbl_name>;'), nl, nl,
    write('Note that commands and table/column names are case-insensitive.'),
    nl, nl.
    

%------------------------------------------------------------------------------
%   Driver goal and main processing loop - print a greeting, then just loop
%   reading user commands (which must be terminated by a ';') until user enters
%   QUIT;
%------------------------------------------------------------------------------
pro_sql_main_loop :-
    nl,
    read_in(Word_list), !,
    delete(Word_list, ';', Cmd),
    process_sql_cmd(Cmd), !,
    pro_sql_main_loop.


pro_sql_db :-
    %--------------------------------------------------------------------------
    %   The table_defined predicate will be changed throughout execution of the
    %   program - let Prolog know that it exists (even if no clauses for it are
    %   defined yet).
    %--------------------------------------------------------------------------
    dynamic(table_defined/2),
    nl,
    write('Welcome to Bob and Jeff''s Prolog database'),
    nl,
    nl,
    prompt(_, 'PrologSQL >  '),
    pro_sql_main_loop.


%------------------------------------------------------------------------------
%   Previous version of predicate will eventually fail, when they type in a
%   "QUIT;", so print out a farewell message, and save the Prolog state to a
%   file so it can be recovered for future sessions.
%------------------------------------------------------------------------------
pro_sql_db :-
    nl, write('Goodbye.'), nl,
    qsave_program('saved_db', [autoload = true]).
