/*********************************************************/ /* */ /* DES: Datalog Educational System v.3.0 */ /* */ /* SQL Debugger */ /* */ /* */ /* Yolanda Garcia-Ruiz (*) */ /* Rafael Caballero-Roldan (*) */ /* Fernando Saenz-Perez (**) */ /* (c) 2004-2012 */ /* GPD DSIC, DISIA UCM */ /* Please send comments, questions, etc. to: */ /* fernan@sip.ucm.es */ /* Visit the Web site at: */ /* http://des.sourceforge.net/ */ /* */ /* This file is part of DES. */ /* */ /* DES is free software: you can redistribute it and/or */ /* modify it under the terms of the GNU Lesser General */ /* Public License as published by the Free Software */ /* Foundation, either version 3 of the License, or (at */ /* your option) any later version. */ /* */ /* DES is distributed in the hope that it will be useful,*/ /* but WITHOUT ANY WARRANTY; without even the implied */ /* warranty of MERCHANTABILITY or FITNESS FOR A */ /* PARTICULAR PURPOSE. See the GNU Lesser General Public */ /* License for more details. */ /* */ /* You should have received a copy of the GNU Lesser */ /* General Public License and GNU General Public License */ /* along with this program. If not, see: */ /* */ /* http://www.gnu.org/licenses/ */ /*********************************************************/ %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Debugging SQL Views %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% :- dynamic(buggy/1). % buggy(Question) :- dynamic(state/2). % state(Question,Answer) % Question: % - all(RelationName) % - in(Tuple,RelationName) % - subset(RelationName1,RelationName2) % Answer: % - valid % - nonvalid % - missing % - missing(Tuple) % - wrong % - wrong(Tuple) %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%% Command-line option handling %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% debug_sql(ViewName,Options) :- get_debug_sql_options(Options,TrustTables,TrustFile,Order), !, process_trust_file(TrustFile), debug_sql_code0(ViewName,[TrustTables,TrustFile,Order]), drop_trusted_views. debug_sql(_ViewName,_Options). % Process trust file. If trust file is given, process it process_trust_file(trust_file(FileName)) :- set_flag(trusted_views,[]), (FileName==no(file) -> true ; set_flag(trusting,on), (development(off) -> (output(Output),set_flag(output,off)) ; true), processC(process,[FileName],_NVs,_Continue), (development(off) -> set_flag(output,Output) ; true), set_flag(trusting,off) ). % Drop trusted views after trusted debugging. The extension table is cleared if there are such views drop_trusted_views :- trusted_views(VNs), drop_viewname_k_list(VNs), (VNs\==[] -> processC(clear_et,[],_NVs,_Yes) ; true ). % Get debug options(+,-,-,-) get_debug_sql_options(Options,trust_tables(TT),trust_file(TF),order(O)) :- get_cmd_options(Options,[trust_tables(TT),trust_file(TF),order(O)], [debug_sql_trust_tables_option_test,debug_sql_trust_file_option_test,debug_sql_order_option_test], [trust_tables(yes),trust_file(no(file)),order(preorder)]). % Get command options get_cmd_options([],Defaults,_Tests,Defaults). get_cmd_options([CmdOption|CmdOptions],AllowedOptions,Tests,Defaults) :- remove_option(CmdOption,Test,AllowedOptions,NAllowedOptions,Tests,NTests,Defaults,NDefaults), !, my_apply(Test,[CmdOption]), get_cmd_options(CmdOptions,NAllowedOptions,NTests,NDefaults). get_cmd_options([CmdOption|_CmdOptions],_AllowedOptions,_Tests,_Defaults) :- write_error_log(['Incorrect argument: ',CmdOption]), !, fail. % Removing an option remove_option(X,T,[X|Xs],Xs,[T|Ts],Ts,[_U|Us],Us). remove_option(X,T,[Y|Xs],[Y|Ys],[TY|Ts],[TY|Ss],[U|Us],[U|Vs]) :- remove_option(X,T,Xs,Ys,Ts,Ss,Us,Vs). % Tests for debug_sql command options debug_sql_trust_tables_option_test(trust_tables(O)) :- my_member(O,[yes,no]), !. debug_sql_trust_tables_option_test(trust_tables(O)) :- write_error_log(['Incorrect trust table option ''',O,'''.']), fail. debug_sql_trust_file_option_test(trust_file(F)) :- my_file_exists_with_default_extensions(F,['.sql'],_FP), !. debug_sql_trust_file_option_test(trust_file(_F)) :- fail. debug_sql_order_option_test(order(O)) :- my_member(O,[preorder,'dq']), !. debug_sql_order_option_test(order(O)) :- write_error_log(['Incorrect order option ''',O,'''.']), fail. get_debug_sql_option(Option,Options) :- member(Option,Options), !. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%% Debugging algorithm %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% debug_sql_code0(ViewName,Options) :- ask_oracle(all(ViewName),[],'n',Answer,_NewQuestion), % First enquiry: do not trust file (Answer == abort -> write_info_log(['Debugging aborted by user.']) ; debug_sql_code1(ViewName,Answer,BuggyViewNames,Options,Abort,Error), (Abort==true -> write_info_log(['Debugging aborted by user.']) ; (Error==true -> write_error_log(['Unable to locate a buggy node.']) ; display_sql_buggy_nodes(BuggyViewNames) ) ) ), clean_up_temporary_views. % ask_oracle(+Question,+Options,+DefaultAnswer,-Answer,-NewQuestion) % Only to display the question ask_oracle(Question,Options,DefaultAnswer,Answer,NewQuestion) :- exec_if_development_on(write_info_log(['Question: ',Question])), ask_oracle1(Question,Options,DefaultAnswer,Answer,NewQuestion). % ask_oracle1(+Question,+Options,+DefaultAnswer,-Answer,-NewQuestion) ask_oracle1(Question,Options,DefaultAnswer,Answer,Question) :- Question = all(RelationName), !, sql_node_type(RelationName/Arity,NodeType), input_ask_oracle(RelationName,Arity,NodeType,Options,DefaultAnswer,Answer). ask_oracle1(Question,Options,_DefaultAnswer,Answer,Question) :- Question = in(Tuple,RelationName), !, sql_node_type(RelationName/_Arity,NodeType), display_debugging_info(NodeType,RelationName,Options), Tuple=..[_|UTupleList], my_list_to_tuple(UTupleList,UTuple), write_log_list(['Input: Should ''',RelationName,''' include a tuple of the form ''',UTuple,'''? (y/n/a) [y]: ']), user_input_string(IStr), to_uppercase_char_list(IStr,Str), ((Str=[] ; Str=="Y") -> Answer=valid ; (Str=="N" -> Answer=nonvalid ; (Str=="A" -> Answer=abort ; write_error_log(['Invalid input']), ask_oracle1(Question,Options,'y',Answer,Question) ) ) ). ask_oracle1(Question,Options,DefaultAnswer,Answer,NewQuestion) :- Question = subset(RelationName1,RelationName2), !, sql_node_type(RelationName1/Arity1,NodeType1), length(Args1,Arity1), Query1=..[RelationName1|Args1], compute_datalog(Query1), get_solutions(Query1,Solutions1), (Solutions1=[SolTuple] -> SolTuple=..[_|Args], Tuple=..[RelationName2|Args], NewQuestion1 = in(Tuple,RelationName2), ask_oracle1(NewQuestion1,Options,DefaultAnswer,InAnswer,NewQuestion), (InAnswer == nonvalid -> Answer = wrong(Tuple) ; Answer = InAnswer ) ; NewQuestion = Question, exec_if_development_on( write_log_list(['Info: Debugging ',NodeType1,' ''',RelationName1,'''.',nl]), display_definition(RelationName1,NodeType1)), number_solutions(Solutions1,NumberedSolutions1), display_bag(NumberedSolutions1), exec_if_verbose_on( (sql_node_type(RelationName2/Arity2,NodeType2), write_log_list(['Info: Debugging ',NodeType2,' ''',RelationName2,'''.',nl]), display_definition(RelationName2,NodeType2))), (development(on) -> length(Args2,Arity2), Query2=..[RelationName2|Args2], compute_datalog(Query2), get_solutions(Query2,Solutions2), display_bag(Solutions2), write_log_list(['Input: Is ''',RelationName1,''' included in the expected answer of ''',RelationName2,'''? (y/n/wN/a) [y]: ']) ; write_log_list(['Input: Is the above set included in the expected answer of ''',RelationName2,'''? (y/n/wN/a) [y]: ']) ), user_input_string(IStr), to_uppercase_char_list(IStr,Str), ((Str=[] ; Str=="Y") -> Answer=valid ; (Str=="N" -> Answer=nonvalid ; (wrong_tuple_answer(Solutions1,Str,WAnswer) -> (WAnswer==error -> ask_oracle(Question,Options,DefaultAnswer,Answer,NewQuestion) ; Answer=WAnswer ) ; (Str=="A" -> Answer=abort ; write_error_log(['Invalid input']), ask_oracle(Question,Options,'y',Answer,NewQuestion) ) ) ) ) ). display_debugging_info(table,_RelationName,Options) :- get_debug_sql_option(trust_tables(yes),Options), !. display_debugging_info(NodeType,RelationName,_Options) :- write_log_list(['Info: Debugging ',NodeType,' ''',RelationName,'''.',nl]), exec_if_verbose_on( display_definition(RelationName,NodeType)). % input_ask_oracle(+Rel,+Arity,+NodeType,+Opts,+DefaultAnswer,-Answer) % Ask oracle for input (ask % Trusted tables: input_ask_oracle(_RelationName,_Arity,table,Options,_DefaultAnswer,valid) :- get_debug_sql_option(trust_tables(yes),Options), !. % Trust file: input_ask_oracle(RelationName,Arity,NodeType,Options,_DefaultAnswer,Answer) :- get_debug_sql_option(trust_file(FileName),Options), FileName \== no(file), name_trusted(RelationName,TrustObjectName), pdg((Nodes,_Arcs)), my_member_chk(TrustObjectName/Arity,Nodes), !, % Process the same node as RelationName/Arity from trust file: functor(TrustQuery,TrustObjectName,Arity), % my_term_to_string(TrustQuery,TrustQueryStr), % process_datalog(TrustQueryStr), compute_datalog(TrustQuery), (same_meaning(RelationName,TrustObjectName,Arity) -> Answer=valid, write_log_list(['Info: ',NodeType,' ''',RelationName,''' is valid w.r.t. the trusted file.',nl]) ; Answer=nonvalid, write_log_list(['Info: ',NodeType,' ''',RelationName,''' is nonvalid w.r.t. the trusted file.',nl]) ). % Answer left to oracle: input_ask_oracle(RelationName,Arity,NodeType,Options,DefaultAnswer,Answer) :- %sql_node_type(RelationName/Arity,NodeType), display_debugging_info(NodeType,RelationName,Options), length(Args,Arity), Query=..[RelationName|Args], compute_datalog(Query), get_solutions(Query,Solutions), number_solutions(Solutions,NumberedSolutions), display_bag(NumberedSolutions), write_log_list(['Input: Is this the expected answer for ',NodeType,' ''',RelationName,'''? (y/n/m/mT/w/wN/a/h) [',DefaultAnswer,']: ']), user_input_string(IStr), (IStr==[] -> atom_codes(DefaultAnswer,StrDefaultAnswer), to_uppercase_char_list(StrDefaultAnswer,Str) ; Str=[UC|T], IStr=[C|T], to_uppercase_char(C,UC) ), ((Str=[] ; Str=="Y") -> Answer=valid ; (Str=="N" -> Answer=nonvalid ; (Str=="A" -> Answer=abort ; (missing_tuple_answer(RelationName,Arity,Str,MAnswer) -> (MAnswer==error -> input_ask_oracle(RelationName,Arity,NodeType,Options,DefaultAnswer,Answer) ; Answer=MAnswer ) ; (wrong_tuple_answer(Solutions,Str,WAnswer) -> (WAnswer==error -> input_ask_oracle(RelationName,Arity,NodeType,Options,DefaultAnswer,Answer) ; Answer=WAnswer ) ; (Str=="H" -> help_input_ask_oracle, input_ask_oracle(RelationName,Arity,NodeType,Options,DefaultAnswer,Answer) ; write_error_log(['Invalid input']), help_input_ask_oracle, input_ask_oracle(RelationName,Arity,NodeType,Options,DefaultAnswer,Answer) ) ) ) ) ) ). help_input_ask_oracle :- write_info_log(['Possible answers are:\n y (yes)\n n (no)\n m (some missing tuple(s) in answer)\n mT (missing tuple T as a comma-separated list of SQL constants or placeholders ''_'') \n w (wrong answer)\n wN (wrong tuple at position N)\n a (abort)\n h (this help) ']). number_solutions(Solutions,NumberedSolutions) :- length(Solutions,L), from(1,L,Ns), my_zipWith('-',Ns,Solutions,NumberedSolutions). missing_tuple_answer(RelationName,Arity,Str,Answer) :- missing_tuple_answer(RelationName,Arity,Answer,Str,[]). missing_tuple_answer(RelationName,Arity,missing(Tuple)) --> "M", {functor(Tuple,RelationName,Arity)}. missing_tuple_answer(RelationName,Arity,Answer) --> "M", my_blanks_star, my_noncompound_terms(Cs,[],_Vo), {length(Cs,Arity) -> Tuple=..[RelationName|Cs], Answer=missing(Tuple) ; write_error_log(['Incorrect number of arguments. It must be ',Arity]), Answer=error}. wrong_tuple_answer([],_Str,error) :- !, % No solution tuple. So, no way to be incorrect. write_error_log(['Empty relation. Cannot be incorrect. Maybe missing?']). wrong_tuple_answer(Solutions,Str,Answer) :- wrong_tuple_answer(Solutions,Answer,Str,[]). wrong_tuple_answer([Solution],wrong(Solution)) --> "W". wrong_tuple_answer([Solution|_Solutions],wrong(Tuple)) --> "W", {functor(Solution,RelationName,Arity), functor(Tuple,RelationName,Arity)}. wrong_tuple_answer(Solutions,Answer) --> "W", my_number(N), {length(Solutions,L), (N>0, N= nth1(N,Solutions,Tuple), Answer=wrong(Tuple) ; write_error_log(['Invalid tuple number. It must be between 1 and ',L]), Answer=error ) }. display_definition(_RelationName,table) :- !. display_definition(RelationName,view) :- get_sql_view_definition(RelationName,SQLst), display_sql(SQLst, 2). display_sql_buggy_nodes(ViewNames) :- development(off), !, filter_temporary_views(ViewNames,UserViewNames), my_remove_duplicates_sort(UserViewNames,OrdUserViewNames), display_all_sql_buggy_nodes(OrdUserViewNames). display_sql_buggy_nodes(ViewNames) :- my_remove_duplicates_sort(ViewNames,OrdViewNames), display_all_sql_buggy_nodes(OrdViewNames). filter_temporary_views([],[]). filter_temporary_views([ViewName|ViewNames],UserViewNames) :- is_temporary_view(ViewName), !, filter_temporary_views(ViewNames,UserViewNames). filter_temporary_views([ViewName|ViewNames],[ViewName|UserViewNames]) :- filter_temporary_views(ViewNames,UserViewNames). display_all_sql_buggy_nodes([BuggyViewName]) :- sql_node_type(BuggyViewName/_A,NodeType), write_info_log(['Buggy ',NodeType,' found: ',BuggyViewName]), !. display_all_sql_buggy_nodes(BuggyViewNames) :- write_info_log(['Buggy relations: ',BuggyViewNames]). % Clean up temporary views created along debugging (those containing '_slice' in their names) clean_up_temporary_views :- findall( V, (my_view(_,V,_,_,_,_,_,_,_), is_temporary_view(V) ), Vs), drop_viewname_k_list(Vs), clear_et. is_temporary_view(ViewName) :- atom_codes(ViewName,StrViewName), is_temporary_view_name(StrViewName,""). is_temporary_view_name --> my_chars(_), "_slice", my_chars(_). % is_temporary_view_name --> % buggyAux is not included anymore % my_chars(_), % "Aux_", % my_positive_integer(_). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 1 debug(V,A) % Input: V view name, A answer % Output: Buggy view names debug_sql_code1(ViewName,Answer,BuggyViewNames,Options,Abort,Error) :- initial_set_of_clauses(ViewName,Answer,Program), debug_sql_code1_loop(Program,BuggyViewNames,Options,Abort,Error). debug_sql_code1_loop(Program,BuggyViewNames,Options,Abort,Error) :- get_buggy(Program,CurrentBuggyViewNames), (CurrentBuggyViewNames==[] -> get_unsolved_questions(Program,UnsolvedQuestions), (UnsolvedQuestions==[] -> Error=true ; choose_question(UnsolvedQuestions,Question), ask_oracle(Question,Options,'y',NewAnswer,_NewQuestion), (NewAnswer==abort -> Abort=true ; process_answer(Question,NewAnswer,Program,NewProgram), exec_if_development_on( write_info_log(['Current logic program:']), display_rules_list(NewProgram,2), nl_compact_log), debug_sql_code1_loop(NewProgram,BuggyViewNames,Options,Abort,Error) ) ) ; BuggyViewNames=CurrentBuggyViewNames, exec_if_development_on( write_info_log(['Final logic program:']), display_rules_list(Program,2), nl_compact_log) ). get_buggy(Program,BuggyViewNames) :- retractall(buggy(_)), retractall(state(_,_)), my_apply(assertz,Program), findall(Question,buggy(Question),Questions), questions_buggy_view_names(Questions,BuggyViewNames). questions_buggy_view_names(X,X). get_unsolved_questions(Program,UnsolvedQuestions) :- findall(Question, (member(':-'(_H,B),Program), my_list_to_tuple(Bs,B), member(state(Question,_BAnswer),Bs), \+ member(state(Question,_FAnswer),Program)), DupUnsolvedQuestions), my_remove_duplicates_sort(DupUnsolvedQuestions,UnsolvedQuestions). choose_question([Question],Question) :- !. choose_question([Question|Questions],ChosenQuestion) :- question_cardinality(Question,QuestionCardinality), choose_question(Questions,QuestionCardinality,Question,ChosenQuestion). choose_question([],_Cardinality,Question,Question). choose_question([Question|Questions],Cardinality,_CurrentBestQuestion,BestQuestion) :- question_cardinality(Question,QuestionCardinality), Cardinality>QuestionCardinality, !, choose_question(Questions,QuestionCardinality,Question,BestQuestion). choose_question([_Question|Questions],Cardinality,CurrentBestQuestion,BestQuestion) :- choose_question(Questions,Cardinality,CurrentBestQuestion,BestQuestion). question_cardinality(in(_Tuple,_RelationName),1). question_cardinality(subset(RelationName,_Rel),QuestionCardinality) :- relation_answer_cardinality(RelationName,QuestionCardinality). question_cardinality(all(RelationName),QuestionCardinality) :- relation_answer_cardinality(RelationName,QuestionCardinality). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 2 initialSetOfClauses % Input: V view name, A answer % Output: A list of clauses initial_set_of_clauses(ViewName,Answer,Program) :- initialize_debug_logic_program(ViewName,[],InitializedProgram), process_answer(all(ViewName),Answer,InitializedProgram,Program), exec_if_development_on( write_info_log(['Initial logic program:']), display_rules_list(Program,2)). initialize_debug_logic_program(ViewName,ProgramIn,ProgramOut) :- create_buggy_clause(ViewName,Clause), get_view_relations(ViewName,Relations), add_to_program([Clause],ProgramIn,ProgramIn1), initialize_debug_logic_program_list(Relations,ProgramIn1,ProgramOut). initialize_debug_logic_program_list([],Program,Program). initialize_debug_logic_program_list([Relation|Relations],ProgramIn,ProgramOut) :- initialize_debug_logic_program(Relation,ProgramIn,ProgramIn1), initialize_debug_logic_program_list(Relations,ProgramIn1,ProgramOut). create_buggy_clause(ViewName,Clause) :- get_view_relations(ViewName,Relations), build_relation_state_body(Relations,BodyList), my_list_to_tuple([state(all(ViewName),nonvalid)|BodyList],Body), Clause = ':-'(buggy(ViewName),Body). build_relation_state_body([],[]). build_relation_state_body([Relation|Relations],[state(all(Relation),valid)|Goals]) :- build_relation_state_body(Relations,Goals). get_view_relations(ViewName,Relations) :- get_sql_view_definition(ViewName,SQL), !, get_view_relations_from_sql(SQL,Relations). get_view_relations(_ViewName,[]). get_view_relations_from_sql(SQL,Relations) :- SQL = select(_D,_T,_PL,_F,_W,_GB,_H,_OB), !, get_basic_view_relations_from_sql(SQL,Relations). get_view_relations_from_sql(SQL,Relations) :- (SQL = union(_,(SQL1,_),(SQL2,_)) ; SQL = except(_,(SQL1,_),(SQL2,_)) ; SQL = intersect(_,(SQL1,_),(SQL2,_))), !, get_view_relations_from_sql(SQL1,Relations1), get_view_relations_from_sql(SQL2,Relations2), concat_lists([Relations1,Relations2],DupRelations), my_remove_duplicates_sort(DupRelations,Relations). get_view_relations_from_sql(SQL,Relations) :- SQL = with(SQL,SQLs), get_view_relations_from_sql(SQL,SQLRelations), get_view_relations_from_sql_list(SQLs,SQLsRelations), append(SQLRelations,SQLsRelations,DupRelations), my_remove_duplicates_sort(DupRelations,Relations). get_view_relations_from_sql_list(SQLs,SQLsRelations) :- get_view_relations_from_sql_list(SQLs,[],SQLsRelations). get_view_relations_from_sql_list([],Rs,Rs). get_view_relations_from_sql_list([SQL|SQLs],Rsi,Rso) :- get_view_relations_from_sql(SQL,Rsi,Rs1), get_view_relations_from_sql_list(SQLs,Rs1,Rso). get_basic_view_relations_from_sql(SQL,Relations) :- SQL = select(_D,_T,_PL,From,Where,_GB,_H,_OB), get_from_relations(From,FromRelations), get_where_relations(Where,WhereRelations), concat_lists([FromRelations,WhereRelations],DupRelations), my_remove_duplicates_sort(DupRelations,Relations). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 3 processAnswer % Input: Q question, A answer % Output: Logic program process_answer(Question,Answer,Program,NewProgram) :- exec_if_development_on(write_info_log(['processAnswer(',Question,',',Answer,')'])), ((Answer==valid ; Answer==nonvalid) -> Clauses=[state(Question,Answer)] ; (Answer=missing(_) ; Answer=wrong(_)) -> Clauses=[state(Question,nonvalid)] ; Clauses=[] ), add_to_program(Program,Clauses,Program1), process_question(Question,Answer,Program1,NewProgram). process_question(in(Tuple,RelationName),Answer,Program,NewProgram) :- (tuple_in_SQL_relation(Tuple,RelationName), Answer == nonvalid -> process_answer(all(RelationName),wrong(Tuple),Program,NewProgram) ; (\+ tuple_in_SQL_relation(Tuple,RelationName), Answer == valid -> process_answer(all(RelationName),missing(Tuple),Program,NewProgram) ; NewProgram = Program ) ), !. process_question(subset(_ViewName,RelationName),wrong(Tuple),Program,NewProgram) :- !, process_answer(all(RelationName),wrong(Tuple),Program,NewProgram). process_question(all(ViewName),Answer,Program,NewProgram) :- (Answer = wrong(Tuple) ; Answer = missing(Tuple) ), !, get_sql_view_definition(ViewName,Question), slice(ViewName,Question,Answer,Clauses), add_to_program(Program,Clauses,NewProgram). process_question(_Query,_Answer,Program,Program). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 4 slice % Input: V: view, Q query, A answer % Output: Logic program slice(ViewName,Query,Answer,NewProgram) :- Program1=[], % Missing tuple (Answer=missing(Tuple) -> (get_intersection_query(Query,Query1,Query2) -> (\+ tuple_in_SQL_query(Tuple,Query1) -> slice(ViewName,Query1,Answer,NewClauses1), add_to_program(Program1,NewClauses1,Program2) ; Program2=Program1 ), (\+ tuple_in_SQL_query(Tuple,Query2) -> slice(ViewName,Query2,Answer,NewClauses2), add_to_program(Program2,NewClauses2,NewProgram) ; NewProgram=Program2 ) ; (get_difference_query(Query,Query1,Query2) -> (\+ tuple_in_SQL_query(Tuple,Query1) -> slice(ViewName,Query1,Answer,NewClauses1), add_to_program(Program1,NewClauses1,NewProgram) ; (tuple_in_SQL_query(Tuple,Query2), total_tuple(Tuple) -> slice(ViewName,Query2,wrong(Tuple),NewClauses1), add_to_program(Program1,NewClauses1,NewProgram) ; NewProgram=Program1 ) ) ; (get_basic_query(Query) -> missing_basic(Program1,ViewName,Query,Tuple,NewProgram) ; NewProgram=Program1 % Nothing else added for missing(Tuple) ) ) ) ; % Wrong tuple (Answer=wrong(Tuple) -> (get_union_query(Query,Query1,Query2) -> (tuple_in_SQL_query(Tuple,Query1) -> slice(ViewName,Query1,Answer,NewClauses1), add_to_program(Program1,NewClauses1,Program2) ; Program2=Program1 ), (tuple_in_SQL_query(Tuple,Query2) -> slice(ViewName,Query2,Answer,NewClauses2), add_to_program(Program2,NewClauses2,NewProgram) ; NewProgram=Program2 ) ; (get_basic_query(Query) -> wrong_basic(Program1,ViewName,Query,Tuple,NewProgram) ; NewProgram=Program1 % Nothing else added for wrong(Tuple) ) ) ; NewProgram=Program1 % Nothing else added for valid/nonvalid ) ). % Intersection query get_intersection_query( select(D,T,PL,F,where(and(LC,RC)),GB,H,OB), select(D,T,PL,F,where(LC),GB,H,OB), select(D,T,PL,F,where(RC),GB,H,OB)). get_intersection_query( intersect(_D,(LR,_),(RR,_)), % WARNING: distinct LR, RR). view_name_from_question(all(Name),Name). view_name_from_question(in(_Tuple,Name),Name). view_name_from_question(subset(_Name,Name),Name). % Difference query get_difference_query( except(_D,(LR,_),(RR,_)), % WARNING: distinct LR, RR). % Basic query get_basic_query(Query) :- Query = select(_D,_T,_PL,_F,_W,group_by([]),_H,_OB). % Union query get_union_query( select(D,T,PL,F,where(or(LC,RC)),GB,H,OB), select(D,T,PL,F,where(LC),GB,H,OB), select(D,T,PL,F,where(RC),GB,H,OB)). get_union_query( union(_D,(LR,_),(RR,_)), % WARNING: distinct LR, RR). % Tuple in SQL query tuple_in_SQL_query(Tuple,Query) :- solve_des_sql_query_k(sql,(Query,_),_Schema,_ColTypes,_TableRen,DLQuery,_DLsts,_Undefined), !, Tuple=..[_Relation|Args], DLQuery=..[_Answer|Args], et(_,DLQuery,_). % Tuple in SQL relation tuple_in_SQL_relation(Tuple,RelationName) :- get_tuples_in_relation(RelationName,Tuples), my_member_chk(Tuple,Tuples). is_empty_sql_answer(Query) :- solve_des_sql_query_k((Query,_),_Schema,_ColTypes,_TableRen,DLQuery,_DLsts,_Undefined), !, \+ et(_,DLQuery,_). % Answer cardinality for a relation relation_answer_cardinality(RelationName,Cardinality) :- get_tuples_in_relation(RelationName,Tuples), length(Tuples,Cardinality). % Get tuples in a relation as its solutions get_tuples_in_relation(RelationName,Tuples) :- my_table(_,RelationName,Arity), functor(Query,RelationName,Arity), compute_datalog(Query), get_solutions(Query,Tuples). % Total tuple total_tuple(Tuple) :- my_ground(Tuple). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 5 missingBasic % Input: L logic program, V view name, Q query, T tuple % Output: Logic program missing_basic(Program,ViewName,Query,Tuple,NewProgram) :- Query = select(D,T,PL,F,where(_C),GB,H,OB), UnfilteredQuery = select(D,T,PL,F,where(true),GB,H,OB), (\+ tuple_in_SQL_query(Tuple,UnfilteredQuery) -> get_from_relations(F,Relations), add_missing_basic_list(Relations,ViewName,Query,Tuple,Program,Program1) ; Program1=Program ), NewProgram=Program1. add_missing_basic_list([],_ViewName,_Query,_Tuple,Program,Program). add_missing_basic_list([Relation|Relations],ViewName,Query,Tuple,Program,NewProgram) :- add_missing_basic(Relation,ViewName,Query,Tuple,Program,Program1), add_missing_basic_list(Relations,ViewName,Query,Tuple,Program1,NewProgram). % This version is more efficient than the one in the FLOPS paper add_missing_basic(Relation,ViewName,Query,Tuple,Program,NewProgram) :- generate_undefined(Relation,STuple), fill_missing_tuple(STuple,Relation,Query,Tuple), generate_condition(STuple,Relation,Condition), FilteredQuery = select(all,top(all),'*',from([(Relation,_Renaming)]),where(Condition),group_by([]),having(true),order_by([],[])), (is_empty_sql_answer(FilteredQuery) -> add_to_program( [':-'(buggy(ViewName),state(in(STuple,Relation),nonvalid))], % ':-'(state(all(Relation),nonvalid),state(in(STuple,Relation),valid))], Program,NewProgram) ; NewProgram=Program ). % The following is as in the FLOPS paper % add_missing_basic(Relation,ViewName,Query,Tuple,Program,NewProgram) :- % generate_undefined(Relation,STuple), % fill_missing_tuple(STuple,Relation,Query,Tuple), % (\+ tuple_in_SQL_relation(STuple,ViewName) % -> % add_to_program( % [':-'(buggy(ViewName),state(in(STuple,Relation),nonvalid)), % ':-'(state(all(Relation),nonvalid),state(in(STuple,Relation),valid))], % Program,NewProgram) % ; % NewProgram=Program % ). % Fill missing tuple fill_missing_tuple(STuple,Relation,Query,Tuple) :- get_projection_list(Query,PL), Tuple =.. [_|TArgs], fill_missing_tuple_arg_list(PL,Relation,TArgs,STuple). % Get projection list as a list with either attr(Rel,Attr,Renaming) or other expressions get_projection_list(Query,PL) :- sql_to_ra((Query,_Schema),(RA,_RASchema),[],TableRen), RA=pi(_D,_T,RPL,_,_,_,_), apply_renamings(RPL,TableRen,PL). apply_renamings([],_TableRen,[]). apply_renamings([attr(R,A,AS)|Args],TableRen,[attr(UR,A,AS)|UArgs]) :- member((UR,R),TableRen), !, apply_renamings(Args,TableRen,UArgs). apply_renamings([Arg|Args],TableRen,[Arg|UArgs]) :- apply_renamings(Args,TableRen,UArgs). fill_missing_tuple_arg_list([],_Relation,[],_). fill_missing_tuple_arg_list([attr(Relation,Attr,_Ren)|Args],Relation,[Value|TArgs],STuple) :- nonvar(Value), !, set_attr_tuple(Relation,Attr,Value,STuple), fill_missing_tuple_arg_list(Args,Relation,TArgs,STuple). fill_missing_tuple_arg_list([_Arg|Args],Relation,[_TArg|TArgs],STuple) :- fill_missing_tuple_arg_list(Args,Relation,TArgs,STuple). set_attr_tuple(Relation,Attr,Value,STuple) :- my_attribute(_,I,Relation,Attr,_Type), arg(I,STuple,Value). generate_undefined(Relation,Tuple) :- my_table(_,Relation,Arity), length(Args,Arity), Tuple =.. [Relation|Args]. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 6 generateCondition % Input: S tuple, R relation % Output: SQL Condition % Former code for missingBasic generate_condition(STuple,Relation,Condition) :- get_attributes(Relation,Attrs), STuple=..[_Relation|SValues], filter_undefined(SValues,Attrs,FSValues,FAttrs), my_zipWith('=',FAttrs,FSValues,AndConds), list_to_and_condition(AndConds,Condition). get_attributes(Relation,Attrs) :- get_table_untyped_arguments(Relation,ColNames), attr_internal_representation_list(ColNames,Attrs). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 6 wrongBasic % Input: L logic program, V view name, Q query, T tuple % Output: Logic program wrong_basic(Program,ViewName,Query,Tuple,NewProgram) :- Query = select(_D,_T,_PL,F,W,_GB,_H,_OB), get_all_from_relations(F,FromRelationsRenamings), relevant_tuples_list(FromRelationsRenamings,Query,Tuple,ViewNames,Program,Program1), get_where_relations(W,WhereRelations), my_unzip(FromRelationsRenamings,FromRelations,_FromRenamings), build_subset_answer_state_list(ViewNames,FromRelations,SubsetStates), build_all_answer_state_list(WhereRelations,AllStates), append(SubsetStates,AllStates,StateList), my_list_to_tuple(StateList,States), add_to_program([':-'(buggy(ViewName),States)],Program1,NewProgram). relevant_tuples_list([],_Query,_Tuple,[],Program,Program). relevant_tuples_list([(Relation,Renaming)|Relations],Query,Tuple,[ViewName|ViewNames],Program,NewProgram) :- new_view_name(Relation,ViewName), relevant_tuples((Relation,Renaming),ViewName,Query,Tuple), add_to_program( [], % [':-'(state(all(Relation),nonvalid),state(subset(ViewName,Relation),nonvalid))], Program,Program1), relevant_tuples_list(Relations,Query,Tuple,ViewNames,Program1,NewProgram). new_view_name(Relation,ViewName) :- atom_concat(Relation,'_slice',RelU), findall(N,(my_view(_,V,_,_,_,_,_,_,_),atom_concat(RelU,AN,V),my_atom_number(AN,N)),Ns), Ns=[_|_], !, find_max(Ns,Max), N1 is Max+1, my_atom_number(AN1,N1), atom_concat(RelU,AN1,ViewName). new_view_name(Relation,ViewName) :- atom_concat(Relation,'_slice1',ViewName). find_max([Max],Max) :- !. find_max([N|Ns],Max) :- find_max(Ns,N,Max). find_max([],Max,Max). find_max([N|Ns],CMax,Max) :- N>=CMax, !, find_max(Ns,N,Max). find_max([_|Ns],CMax,Max) :- find_max(Ns,CMax,Max). build_subset_answer_state_list([],[],[]). build_subset_answer_state_list([ViewName|ViewNames],[Relation|Relations],[state(subset(ViewName,Relation),valid)|States]) :- build_subset_answer_state_list(ViewNames,Relations,States). build_all_answer_state_list([],[]). build_all_answer_state_list([Relation|Relations],[state(all(Relation),valid)|States]) :- build_all_answer_state_list(Relations,States). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Code 7 relevantTuples % Input: R relation, V view name, Q query, T tuple % Output: Creates a new view in the database schema % WARNING: Add SQL identifier delimiters for table and column names relevant_tuples((Relation,Renaming),ViewName,Query,Tuple) :- atom_codes(Relation,StrRelation), atom_codes(ViewName,StrViewName), get_str_relation_colname_tuple(Relation,StrViewColnameTuple), get_str_select(Query,StrSelect), get_str_from(Query,StrFrom), get_str_where(Query,StrWhere), str_equal_rels(Relation,Renaming,'R',StrEqualRels), str_equal_tups_select(Query,Tuple,StrEqualTups), concat_lists( ["CREATE VIEW """, StrViewName, """(", StrViewColnameTuple, ") AS ", "( SELECT * FROM ", StrRelation, " AS R WHERE EXISTS ", "(", "SELECT ",StrSelect," FROM ",StrFrom, " ", "WHERE (",StrWhere,") AND ",StrEqualRels, " AND ", StrEqualTups, ")", ")"], StrQuery), exec_if_development_on( write_info_log(['Processing:']), %write_string_log(StrQuery), nl_log, nl_log, parse_sql_query(SQL,StrQuery,""), %write_log(SQL),nl_log,nl_log, display_sql(SQL,2)), process_sql(StrQuery). get_str_select(Query,StrSelect) :- get_projection_list(Query,PL), push_flag(pretty_print,off,PP), one_line_display_to_string(write_proj_list(PL,0),StrSelect), pop_flag(pretty_print,PP). get_str_from(Query,StrFrom) :- Query = select(_D,_T,_PL,from(Rs),_C,_GB,_H,_OB), push_flag(pretty_print,off,PP), one_line_display_to_string(write_rel_list(Rs,0),StrFrom), pop_flag(pretty_print,PP). get_str_where(Query,StrWhere) :- Query = select(_D,_T,_PL,_F,where(Cs),_GB,_H,_OB), push_flag(pretty_print,off,PP), one_line_display_to_string(write_sql_cond(Cs,0),StrWhere), pop_flag(pretty_print,PP). str_equal_rels(Relation1,Renaming1,Relation2,StrCondition) :- get_table_untyped_arguments(Relation1,Colnames), relation_dot_column_list(Renaming1,Colnames,RelDotCol1), relation_dot_column_list(Relation2,Colnames,RelDotCol2), my_zipWith('=',RelDotCol1,RelDotCol2,Equalities), str_and_condition(Equalities,StrCondition). relation_dot_column_list(_R,[],[]). relation_dot_column_list(R,[ColName|ColNames],[attr(R,ColName,ColName)|RDotColNames]) :- relation_dot_column_list(R,ColNames,RDotColNames). str_and_condition(Equalities,StrCondition) :- list_to_and_condition(Equalities,Condition), one_line_display_to_string(write_sql_cond(Condition,0),StrCondition). str_equal_tups_select(Query,Tuple,StrCondition) :- get_projection_list(Query,PL), Tuple =.. [_Relation|TArgs], filter_undefined(TArgs,PL,FTArgs,FPL), surround_with_quotes(FPL,FTArgs,QTArgs), my_zipWith('=',PL,QTArgs,Equalities), str_and_condition(Equalities,StrCondition). surround_with_quotes([],[],[]). surround_with_quotes([attr(Rel,Col,_AS)|Attrs],[Value|Values],[QValue|QValues]) :- get_attr_type(Rel,Col,Type), is_string_type(Type), !, atomic_concat_list(['''',Value,''''],QValue), surround_with_quotes(Attrs,Values,QValues). surround_with_quotes([attr(_Rel,_Col,_AS)|Attrs],[Value|Values],[QValue|QValues]) :- atom(Value), !, atomic_concat_list(['''',Value,''''],QValue), surround_with_quotes(Attrs,Values,QValues). surround_with_quotes([_|Attrs],[Value|Values],[Value|QValues]) :- surround_with_quotes(Attrs,Values,QValues). list_to_and_condition([],true). list_to_and_condition([C1],C1). list_to_and_condition([C1,C2|Cs],and(C1,ACs)) :- list_to_and_condition([C2|Cs],ACs). filter_undefined([],[],[],[]). filter_undefined([V|Vs],[_A|As],FVs,FAs) :- var(V), !, filter_undefined(Vs,As,FVs,FAs). filter_undefined([V|Vs],[A|As],[V|FVs],[A|FAs]) :- filter_undefined(Vs,As,FVs,FAs). %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % add_to_program. Add logic clauses to a logic program (in a list) % Return the new program, ordered and without duplicates add_to_program(Clauses,Program,NewProgram) :- my_sort(Clauses,OClauses), ordered_insert_list(OClauses,Program,NewProgram). ordered_insert_list(Xs,[],Xs) :- !. ordered_insert_list([],Ys,Ys). ordered_insert_list([X|Xs],[Y|Ys],[X|Zs]) :- X@