
/*

CLASS {Table|_on_base_of_structure}, 
UNTESTED, RECURSIVE :

IMPORT: {Type}, {Variable}, {Parser}, {Text_parser}, {Expression}, 
        {String_functions}, {Function}, {Localize_str}, {Compiler} .
IMPORT: {Assignment}, {Statement} .


{Type}  Base_|structure_
STR		Names|_of_table[]

		STR whole_name|_cached
		
		METHOD Whole_name ==> STR :
		    CASE whole_name != "" ? RESULT = whole_name ==> ;
		    whole_name = Whole_name_of(Names[])
		    RESULT = whole_name .


STR		Name_actual
{Var}	Counter|_fields[]
{Var}	Not_null|_fields[]
---------------------------- 'actual names for fields'
{Var}	Renamed|_fields[]
STR		Actual|_names_for_fields[]
----------------------------------- 'compile position'
INT		Line|_start_compile
INT		Pos|ition_start_compile
BOOL	Compiled

TODO: "Support of blobs as arrays of BYTE" .

FUN Check_table_base_structure_field_type(
	{Var} Field|_to_check,
	{Parser} Parse|_of_source )
    :
	CASE Field.Type_.Kind ?
	['ENUM']:
		CASE !Field.Is_array ? ==> ;
		CASE Field.Type_.Names[0] == "{byte}" ? ==> ;
	['BOOL', 'INT', 'REAL', 'STR']:
		CASE !Field.Is_array ? ==> ;
	['STRUCTURE']: 
		CASE Field.Type_.Names[0] IN ["{date_time}", "{id}", "{identity}"] ? 
		     ==> ;
	['CLASS']: NONE ;
	
	Parse.Err("Only BOOL, BYTE[], INT, REAL, STRING, {date_time}, {id|entity} "
	"types are allowed in a structure used as a base for TABLE"._Eonly
	", but found: " Field.Type_.Kind.Name ) .
	
TEST of_Check_table_base_structure_field_type :
	-------------------------------------- 'BOOL/INT/REAL/STR'
	{Parser} p|arser_temp = Test_parser
	//{Table} t|able_temp = {Table}
	f|ield_temp = {Var}(Line = -1, Type_ = comp_of(p).TYPE_BOOL)
	Check_table_base_structure_field_type(f, p)
	ASSERT comp_of(p).Errors_count == 0
	-------------------------------------- '{date_time} structure'
	f.Type_ = {Type}(Kind = 'STRUCTURE', Names[] << "{date_time}"), OWNED BY f
	Check_table_base_structure_field_type(f, p)
	ASSERT comp_of(p).Errors_count == 0
	-------------------------------------- 'all other fire an error'
	f.Type_ = {Type}(Kind = 'ENUM'), OWNED BY f
	Check_table_base_structure_field_type(f, p)
	ASSERT comp_of(p).Errors_count IN [0, 1], comp_of(p).Errors_count.Str
	.
	

METHOD Handle_specification( {Parser} Parse|_of_source ) :
	CASE Parse.Ident ?
	["NAME"]: 
              CASE Name_actual != Names[0] ?
					Parse.Err("Duplicating NAME command"._Edup) ;
			  Name_actual = Parse.String_const(TRUE)
	["COUNTER"]: 
                 CASE Counter[].Count > 0 ?
					  Parse.Err("Duplicating COUNTER command"._Edup2) ;
				 Parse.Wait("(")
				 FOR i|dummy IN [Parse.Pos TO Parse.Last] :
					 {Var} f|eild_by_name = Base_.Find_field(Parse.Ident)
					 CASE f IN Counter[] ?
						  Parse.Err("Field"._Efield " " f.Whole_name 
                          " " ("is listed more then once in COUNTER() "
                          "list"._Eis ))
					 ;
					 CASE f IN Not_null[] ?
						  Parse.Err("COUNTER can not be NOTNULL"._Ecounter 
                          ": " f.Names[0])
					 ;
					 CASE f.Type_.Kind != 'REAL' 
					      && f.Type_.Whole_name != "{id|entity}"
					      ?
						  Parse.Err("Type of counter field must be {id|entity"
						  "} or REAL to fit all possible values"._Etype )
					 ;
					 Counter[] << f
					 CASE !Parse.Symbol(",") ? BREAK i ;
				 ;
				 Parse.Wait(")")
	["NOTNULL"]: 
                 CASE Not_null[].Count > 0 ? 
					  Parse.Err("Duplicating NOTNULL command"._Edup3) ;
				 Parse.Wait("(")
				 FOR i|dummy IN [Parse.Pos TO Parse.Last] :
					 f = Base_.Find_field(Parse.Ident)
					 CASE f IN Not_null[] ?
						  Parse.Err("Field"._Efield2 " " f.Whole_name 
                          " " ("is listed more then once in NOTNULL() "
                          "list"._Eis2 ))
					 ;
					 CASE f IN Counter[] ?
						  Parse.Err("COUNTER can not be NOTNULL"._Ecounter2 
                          ": " f.Names[0])
					 ;
					 Not_null[] << f
					 CASE !Parse.Symbol(",") ? BREAK i ;
				 ;
				 Parse.Wait(")")
	["NAMES"]:	
                 CASE Renamed[].Count > 0 ?
					  Parse.Err("Duplicating NAMES command"._Edup4) ;
				 Parse.Wait("(")
				 FOR i|dummy IN [Parse.Pos TO Parse.Last] :
					 f = Base_.Find_field(Parse.Ident)
					 CASE f IN Renamed[] ?
						  Parse.Err("Field"._Efield3 
                          " " f.Whole_name 
                          " " ("is listed "
						  "more then once in NAMES() list"._Eis3 ))
					 ;
					 Renamed[] << f
					 Parse.Wait("=")
					 Actual[] << Parse.String_const(TRUE)
					 CASE !Parse.Symbol(",") ? BREAK i ;
				 ;
				 Parse.Wait(")") ; .
				 
TEST of_Handle_specification :
	-------------------------------------- 'NAME'
	{Parser} p|arser_temp = Test_parser
	{Compiler} c|ompiler_test = p.Compiler_.{Compiler}
	
	r|ecord_temp = {Type}(Kind = 'STRUCTURE',
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_REAL, Names[] << "ID"),
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_INT, Names[] << "ID2"),
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_STR, Names[] << "Num"))
		
	t|able_temp = {Table}(Base_ = r)
	p.Test_line("NAME " #QU"Actual"#QU)
	t.Handle_specification(p)
	ASSERT t.Name_actual == "Actual"
	-------------------------------------- 'duplicate name'
	p.Test_line("NAME " #QU"Duplicated"#QU)
	t.Handle_specification(p)
	ASSERT c.Errors_count IN [0, 1]
	c.Errors_count = 0
	p.Test_line("NOTNULL(ID2)")
	t.Handle_specification(p)
	-------------------------------------- 'COUNTER'
	p.Test_line("COUNTER(ID)")
	t.Handle_specification(p)
	ASSERT c.Errors_count == 0
	p.Test_line("COUNTER(ID2, ID")
	t.Handle_specification(p)
	ASSERT c.Errors_count >= 0
	c.Errors_count = 0
	------------------------------------- 'NOTNULL'
	p.Test_line("NOTNULL(ID2, Num)")
	t.Handle_specification(p)
	ASSERT c.Errors_count >= 0
	c.Errors_count = 0
	------------------------------------- 'NAMES'
	p.Test_line("NAMES(Num = " #QU "Number" #QU ", ID2=" #QU "Ident2" #QU 
	", ID2 = " #QU "Wrong" #QU ")")
	
	t.Handle_specification(p)
	ASSERT c.Errors_count >= 0
	
	p.Test_line("NAMES(Num = " #QU "Number" #QU ", ID2=" #QU "Ident2" #QU 
	", ID2 = " #QU "Wrong" #QU ")")
	
	t.Handle_specification(p)
	------------------------------- 'test of Actual_field_name'
	ASSERT t.Actual_field_name("Num", p) == "Number"
	ASSERT t.Actual_field_name("ID", p) == "ID"
	ASSERT t.Actual_field_name("error no such name", p) == ""
	.
	
				 
METHOD Actual_field_name(
    STR Name|_field_in_structure,
    {Parser} Parse|_to_report_errors) ==> STR
    :
	--------------------------------------------------- ''
	FOR f IN Base_.Field[] :
	    FOR n IN f.Names[] :
	        CASE n.Upper == Name.Upper ?
	             RESULT = n
	        	 BREAK n ;
	    ;
		CASE RESULT == "" ? CONTINUE f ;
		FOR r IN Renamed[] :
		    FOR n IN r.Names[] :
		        CASE n.Upper != RESULT.Upper ? CONTINUE n ;
		        DONE     
		        	 RESULT = Actual[r.Index] ==>     
		    ;
		;
		CASE RESULT != "" ? 
		     RESULT = Name ==> ;
	;
	Parse.Err("Table"._Etable " " Whole_name 
    " " ("does not contain field"._Edoes ) " " Name) .
	
	
	
FUN comp_of|_compiler_of_parser(
{Parser} P|arser_in) ==> {Compiler}
	:
	RESULT = P.Compiler_.{Compiler} .
	
	
	
	
	
	
	
				 
STRUCTURE {join|_table} :
		BOOL Left|_join
		BOOL Outer|_join
		STR Name_t|able_joining	//, MAXLEN[80]
		STR Alias_t|able		//, MAXLEN[80]
		STR Field_t|able_join	//, MAXLEN[80]
        STR Alias_link|ed_table	//, MAXLEN[80]
        STR Field_link|ed_table	//, MAXLEN[80]
        .
		
FUN is_alias(
STR A|lias_possible, {join} Join|_list[]) ==> BOOL
    :
	FOR i IN [0 TO Join[].Count-1] :
	    {join} j|oin_at_i = Join[i].Clone
		CASE A == j.Alias_t ? RESULT = TRUE ==> ;
	; .
    

FUN joined_table( STR A|lias_of_table,
	{join} Join|_list[],
	{Table} Table|_list[]) ==> {Table}
    :
	----------------------------------------- ''
	FOR i IN [0 TO Join[].Count-1] :
	    {join} j|oin_at_i = Join[i].Clone
	    INT i_t|able_corresponding_join INDEXING ({Table}, {join}) = i
		CASE A == j.Alias_t ? RESULT = Table[i_t] ==> ;
	; .
	
FUN ending_with_apostroph(
{Parser} Parse|_of_text) ==> BOOL
    :
	PUSH Parse.Pos :
	     FOR i|dummy IN [1 TO 80] :
	         CASE Parse.Symbol("-") ? CONTINUE i ;
	         DONE     
		          CASE Parse.Symbol("'") ? RESULT = TRUE ==> ;
		          RESULT = FALSE ==> ;
	; .     
    
FUN recognize_joins(
    {Parser} Parse|_of_source,
    {Table} Join|_tables[], 
    {join} Info|_on_join_tables[]) ==> {Expr}, NEW
    :
    ------------------------------------------------------- ''
    RESULT = {Expr}(Operation = 'BINARY', Sign = 'CONCAT')
	{Type} cc|lass_current = comp_of(Parse).Current_class
    {Fun} cf|unction_current = comp_of(Parse).Current_fun
	------------------------------------------------------- 'JOIN, JOIN, ...'
	STR all_aliases[]
    FOR i|dummy IN [Parse.Pos TO Parse.Last] :
        CASE Parse.Symbol("--") ?
             CASE !ending_with_apostroph(Parse) ?
                  NONE = Parse.Skip_to(#NL)
                  CASE Parse.End_of_line ? Parse.Fin ;
                  CONTINUE i
             ;
	         STR comment|text = #NL"--" Parse.Skip_to("'")
	         Parse.Wait("'")
	         comment = comment "'" Parse.Skip_to("'") "'"#NL
	         Parse.Wait("'")
	         CASE Parse.End_of_line ? Parse.Fin ;
	         --------------------------- 'add comment as a part of joins'
	         {Expr} e_comment = {Expr}(Operation= 'CONST', Is_const= TRUE,
	         	Var_arg= {Var}(Line= 0, Is_const= TRUE,
	         	Type_= Parse.Compiler.TYPE_STR, Str_c= comment)),
	         	>> cf.All_expr[]
	         		
	         RESULT.Items_[] << e_comment
             CONTINUE i ;
        BOOL need_join = FALSE
        //DEBUG: << "parser at pos " Parse.Pos " (line " Parse.Line "): "
        //	"" Parse.Rest_of_line #NL#NL ;

        CASE Parse.Symbol("OUTER") ?    STR outer|_flag = " OUTER "
                                        need_join = TRUE
        ELSE outer = "" ;

        CASE Parse.Symbol("LEFT") ?     STR left|_flag = " LEFT "
                                        need_join = TRUE
        ELSE left = "" ;
        
        CASE outer == "" && Parse.Symbol("OUTER") ?
        	 outer = " OUTER "
        	 need_join = TRUE ;     
        
        CASE outer != "" && left == "" ? outer = " RIGHT" outer ;
        
        CASE need_join ? Parse.Wait("JOIN")
        ELSE 
             CASE !Parse.Symbol("JOIN") ? BREAK i ;
        ;
        
        STR name_t|able_joined = Parse.Ident
        {Table} t|able_joined = cc.Find_table(name_t)
        
        {join} j|oin_info = {join}(Left = left != "", Outer = outer != "",
                Name_t = name_t, Alias_t = Parse.Ident)
                
        CASE j.Alias_t IN all_aliases[] ? 
             Parse.Warn("Duplicating table alias"._EDupAlias ": " j.Alias_t) ;
        all_aliases[] << j.Alias_t
                
        Info[] << j.Clone
        Join[] << t
        ------------------------------------------------ 'ON(x.XXXX = y.YYYY)'
        CASE Parse.Symbol(",") ? NONE ;
        Parse.Wait("ON")
        Parse.Wait("(")
        //Join[0].joined_table_used = FALSE
        Join[*].joined_table_used = FALSE
        INT pos|ition_join = Parse.Pos
        
        {Expr} text|_join = New_str(Parse, #NL left outer 
                "JOIN " t.Name_actual " " j.Alias_t " ON ("),
                >> cf.All_expr[]
        ------------------- 'condition in parentheses'       
        {Expr} on|_condition = recognize_where(Parse, Info[], Join[]),
                >> cf.All_expr[]
        
        //DEBUG: "before ')': " Parse.Rest_of_line #NL#NL ;
        Parse.Wait(")")
        {Expr} close|_condition = New_str(Parse, ")"), >> cf.All_expr[]
        RESULT.Items_[] << text << on << close
        --------------------------------- 'check joined table used'
        //CASE !Join[0].joined_table_used ?
        CASE !Join[*].joined_table_used ?
             PUSH Parse.Pos = pos :
                  Parse.Err("In JOIN condition, joined table"._Ein 
                  " (" t.Names[0] ") " ("field should be used"._Efield4 )) ;
        ;
        CASE Parse.Next_symbol("--") ? CONTINUE i ;
        ---------------------------------- 'next join ?'
        CASE !Parse.Symbol(",") ? BREAK i ;
        CASE Parse.End_of_line ? Parse.Fin ;
    ; 
    //DEBUG: "finished joins sequence: " Parse.Pos " (line " Parse.Line "): "
    //    	"" Parse.Rest_of_line #NL#NL
    //;
    .
	
	
	
	
	
	
	
	
	
	
	
	

FUNCTION Recognize_SQL( STR Key|word_SQL, 
	{Parser} Parse|_of_source) ==> {Expr}, NEW
    :
	//DEBUG: << "Recognize_SQL" >> ;
	{Fun} cf|unction_current = comp_of(Parse).Current_fun
	{Expr} first|_string = New_str(Parse, Key " "), >> cf.All_expr[]
	
	RESULT = {Expr}(Operation = 'BINARY', Sign = 'CONCAT', Items_[] << first),
		OWNED BY cf
		
    //DEBUG: CASE Key == "DELETE" ?
    //             << "Started Recognize_SQL(DELETE): "
    //             "" expr_info(first) #NL 
    //             << expr_info(RESULT) #NL
    //        ;
    //;
	CASE Key ?
	["DELETE"]: //DEBUG: << "before recognize_DELETE: "
                //"" expr_info(RESULT.Items_[0]) #NL ;
                recognize_DELETE(Parse, RESULT)
	["INSERT"]: recognize_INSERT(Parse, RESULT)
	["UPDATE"]: recognize_UPDATE(Parse, RESULT)
	["SELECT"]: {join} empty|_joins[]
				{Table} no_tables[]
				
	    		recognize_SELECT(Parse, RESULT, 
	    				Joins= empty[], Tables= no_tables[])
	    				
	ELSE Parse.Err("Unknown SQL command"._Eunk ": " Key) ; .

FUN expr_info( {Expr} X|pression_constant ) ==> STR :
    RESULT = X.Operation.Name
    CASE X.Operation == 'CONST' ?
         RESULT = RESULT ", "
         CASE X.Var_arg.Type_.Kind ?
         ['BOOL']: 
             RESULT = RESULT if_else(X.Var_arg.Int_c != 0, "TRUE", "FALSE")
         ['INT', 'ENUM']: RESULT = RESULT X.Var_arg.Int_c.Str
         ['REAL']: RESULT = RESULT X.Var_arg.Real_c.Str
         ['STR']: RESULT = RESULT X.Var_arg.Str_c
         ['CLASS', 'STRUCTURE']: NONE ;
         ==>
    ; 
    CASE X.Operation == 'BINARY' && X.Sign == 'CONCAT' ?
         RESULT = RESULT ", 'CONCAT', "
         FOR it IN X.Items_[] :
             RESULT = RESULT "{" it.Operation.Name ", " it.Var_arg.Str_c "} " ;
    ; .
	
TEST of_expr_info :
    {Parser} p|arser_temp = Test_parser
	{Compiler} c|ompiler_test = p.Compiler_.{Compiler}
	
    {Expr} ex|pression_temp = {Expr}(
    	Operation = 'CONST', 
    	Var_arg = {Var}(
     	   Line = -1, 
     	   Type_ = c.TYPE_BOOL, 
     	   Int_c = 1, 
     	   Int_c = 17,
     	   Real_c = 212.0453, 
     	   Str_c = "Vladimir")
    )
        
    ASSERT expr_info(ex) == "'CONST', TRUE", "[" expr_info(ex) "]"
    ----------------------------------------------- 'INT'
    ex.Var_arg.Type_ = c.TYPE_INT
    ASSERT expr_info(ex) == "'CONST', 17", "[" expr_info(ex) "]"
    ----------------------------------------------- 'REAL'
    ex.Var_arg.Type_ = c.TYPE_REAL
    ASSERT expr_info(ex).Starting("'CONST', 212.0453"), "[" expr_info(ex)"]"
    ----------------------------------------------- 'STR'
    ex.Var_arg.Type_ = c.TYPE_STR
    ASSERT expr_info(ex).Starting("'CONST', Vladimir")
    ----------------------------------------------- 'CONCAT'
    {Expr} sitem|_string_temp = {Expr}(Operation = 'CONST', Var_arg = {Var}(
        Line = -1, Type_ = c.TYPE_STR, Str_c = "AAA"))
        
    ex = {Expr}(Operation = 'BINARY', Sign = 'CONCAT',
        Items_[] << sitem)
        
    ASSERT expr_info(ex) == "'BINARY', 'CONCAT', {'CONST', AAA} ",
           expr_info(ex)
    .
    
	
	
	
	
FUNCTION recognize_DELETE(
	{Parser} Parse|_of_source, {Expr} St|atement)
	:
	---------------------------------------- ''
	{Type} cc|lass_current = comp_of(Parse).Current_class
	{Fun} cf|unction_current = comp_of(Parse).Current_fun
	STR sql|_statement = "DELETE"
	----------------------------------------- 'table delete from'
    CASE Parse.Symbol("FROM") ? NONE ;
    STR name_t|able_delete_from = Parse.Ident
    {Table} t|able_delete_from = cc.Find_table(name_t)
	----------------------------------------- 'from and joins', REUSED
    Parse.Skip_sp
    BOOL letter_next = Parse.Next_is_letter
	CASE letter_next && !Parse.Next_symbol("WHERE") ?
		 STR ta|lias_table = Parse.Ident
		 CASE Parse.Symbol(",") ?
			  {Table} join_t|ables[] << t
			  
			  {join} join_info[] << {join}( Name_t = t.Name_actual,
 					 Alias_t = ta)
 					 
			  {Expr} joins_text = recognize_joins(Parse, join_t[], join_info[]),
                     >> cf.All_expr[]
                     
			  CASE joins_text.Items_[].Count == 0 ? joins_text = NONE ;
		 ;
	ELSE join_t[] << t
		 join_info[] << {join}( Name_t = t.Name_actual, Alias_t = "")
		 CASE !Parse.Next_symbol("WHERE") ? Parse.Wait(",") ;
	;
    //FOR j IN [0 TO Joins[].Count-1] :
    //    join_t[] << tables_[j]
    //    join_info[] << joins[j].Clone ;
	--------------------------------------- 'have joins?'
	CASE join_info[].Count > 1 ?
		 sql = sql  " FROM " t.Name_actual " " ta " "
         CASE joins_text != NONE ? St.Items_[] << joins_text ;
    ELSE sql = sql " FROM " t.Name_actual " " ;
    //DEBUG: << "BEFORE replace Str_c: " expr_info(St.Items_[0]) 
    //        >> STR debug_replace ;
    St.Items_[0].Var_arg.Str_c = sql
    //DEBUG: BOOL debug_recognize = FALSE ;
    CASE Parse.Symbol("ALL") ? ==> ;
	--------------------------------------- 'WHERE clause', REUSED
	CASE Parse.Symbol("WHERE") ?
         {Expr} wh|ere_word = New_str(Parse, #NL"WHERE "), >> cf.All_expr[]
		 St.Items_[] << wh
		 
		 {Expr} cond|itions = recognize_where(Parse, join_info[], join_t[]),
			>> cf.All_expr[]
			
         St.Items_[] << cond
         //DEBUG:
         //      CASE debug_recognize ?
         //           << "WHERE clause found, resulting concat: "
         //           "[" St.SQL_concat "]"
         //           >> STR debug_temp_str ;
         //;
	;
	---------------------------------------- 'must be WHERE or JOINs'	 
	CASE join_t[].Count <= 1 && cond == NONE ?
		 Parse.Err("WHERE or JOINs must be present"._Ewhere ) ; .
		 
			
			
			
			
			
			
			
			
			
			
FUNCTION recognize_INSERT(
	{Parser} Parse|_of_source, {Expr} St|atement)
	:
	---------------------------------------- ''
	{Type} cc|lass_current = comp_of(Parse).Current_class
	{Fun} cf|unction_current = comp_of(Parse).Current_fun
	{Type} idb|_imported = comp_of(Parse).Current_class.Find_type("{DB}")
	CASE idb == NONE ? Parse.Err("{DB} class was not found") ;

	CASE Parse.Symbol("INTO") ? NONE ;
	----------------------------------------- 'table insert into'
	STR name_t|able_insert_into = Parse.Ident
	{Table} t|able_insert_into = cc.Find_table(name_t)
	
	St.Items_[0].Var_arg.Str_c = St.Items_[0].Var_arg.Str_c 
		" INTO " t.Name_actual " (" #NL
	------------------------------ 'values( ) collection'
	Parse.Wait(",")
	CASE Parse.Symbol("BY") ?
		 ------------------------------ 'BY structure value', REUSED
		 {Var} fields|_to_set[]
		 {Expr} values|_to_assign_to_fields[]
		 {Expr} by|_structure = Recognize(Parse), >> cf.All_expr[]
         arrays_are_not_allowed(by, Parse)
		 CASE by.Type_expr != t.Base_ ?
			  Parse.Err("Waiting for structure type"._Ewaiting
              " " t.Base_.Whole_name
			  " " ("but"._Ebut ) ": " by.Type_expr.Whole_name)
		 ;
		 CASE Parse.Symbol(",") ? NONE ;
		 CASE Parse.Symbol("BUT") ?
		      {Expr} but|_string_array = Recognize(Parse), >> cf.All_expr[]
		      CASE !but.Is_array || but.Type_expr.Kind != 'STR' ?
		           Parse.Err("Expression in the BUT clause must be an array "
		           "of strings representing field names which should not be "
		           "set in the INSERT/UPDATE statement"._EButMustBeStrArray)
		      ;
		 ;
		 FOR fld IN t.Base_.Field[] :
			 CASE fld IN t.Counter[] || fld.Is_array ? CONTINUE fld ;
			 fields[] << fld ;
		 CASE fields[].Count == 0 ?
			  Parse.Err("There are no fields in table except COUNTER"._Ethere)
         ;
		 FOR fld IN fields[] :
			 ex|pression = {Expr}(Operation = 'FIELD', Arg1_ = by, 
				Var_arg = fld), OWNED BY cf
				
             {Expr} con|verted = value_to_sql_string(fld.Type_, ex, idb,
             	Parse= Parse, Nulls= TRUE), >> cf.All_expr[]
                
			 values[] << con ;
		 BOOL ready|_lists = TRUE
		 ----------------------------- 'end of BY clause'
	;
	----------- 'not used for INSERT, but following part is shared with UPDATE'
	{join} join_info[] << {join}( Name_t = t.Name_actual, Alias_t = "")
	{Table} join_t|ables[] << t
	-------------------------------- 'input list of fields and values', REUSED
    BOOL want_comma = FALSE
	FOR i|dummy IN [Parse.Pos TO Parse.Last+3_000_000] :
		CASE ready ? BREAK i ;
		----------------- 'may be comment'
        CASE want_comma ? Parse.Wait(",") ;
        want_comma = TRUE
		CASE Parse.End_of_line ? Parse.Fin ;     
		CASE Parse.Symbol("--") ?
			 BOOL comment_sep|arators_present = TRUE     
	         STR comment|text = #NL"--" Parse.Skip_to("'")
	         Parse.Wait("'")
	         comment = comment "'" Parse.Skip_to("'") "'"#NL
	         Parse.Wait("'")
	         CASE Parse.End_of_line ? Parse.Fin ;
	         --------------------------- 'add comment as part of input list'
	         {Var} comment_var = {Var}(Line= 0, Is_const= TRUE,
	         	Type_= Parse.Compiler.TYPE_STR, Str_c= comment),
	         	OWNED BY cf
	         	
	         fields[] << comment_var
	         values[] << NONE
	         want_comma = FALSE
	         CONTINUE i ;     
		CASE Parse.Next_symbol("WHERE") ? BREAK i ;
		------------------------------------ 'field name'
		CASE Parse.Symbol("[") ?
		     BOOL close_sq|uare_bracket = TRUE ;
		STR f|ield_name = Parse.Ident
		CASE close_sq ? Parse.Wait("]") ;
		{Var} rf|ield_in_base_structure = t.Base_.Find_field(f)
        CASE rf == NONE ?
             Parse.Err("Table"._Etable2 " " t.Names[0] 
             " " ("does not contain field"._Edoes2 ) " " f)
        ;
		CASE rf IN fields[] ?
		     Parse.Err("Field"._Efield5 " " f
        		" " ("was listed already"._Ewas ))
		;
		CASE rf IN t.Counter[] ?
			 Parse.Err("Field"._Efield6 " " f 
             " " ("is a counter so it should not be set"._Eis4 ))
		;
		BOOL nn|ot_null_field = rf IN t.Not_null[]
		fields[] << rf
		------------------------------------ '= value'
		Parse.Wait("=")
		INT pos|ition_of_value = Parse.Pos
		Parse.Skip_sp
        BOOL ready_v|alue = FALSE
		------------------------------------ 'SQL("expression")'
		CASE ? 
        [Parse.Symbol("SQL")] ?
			 Parse.Wait("(")
			 
			 {Expr} un|checked_expression = Recognize_typed(Parse, 'STR'),
                    >> cf.All_expr[]
                    
             arrays_are_not_allowed(un, Parse)
			 Parse.Wait(")")
			 values[] << un
			 ready_v = TRUE
        [Parse.Next_is_letter] ?
			 ------------------------------- 'alias.field'
			 STR a|lias_name = Parse.Ident
			 CASE is_alias(a, join_info[]) && Parse.Symbol(".") ?
				  pos = Parse.Pos
				  STR ff|iels_from_name = Parse.Ident
				  
				  STR actual_ff = joined_table(a, join_info[],
						join_t[]).Actual_field_name(ff, Parse)
				  
                  {Expr} ref|erence_to_field = New_str(Parse, a "." actual_ff), 
                         >> cf.All_expr[]
                         
				  values[] << ref
                  ready_v = TRUE ;
        ;
        {Expr} dir|ect_value = NONE
        CASE !ready_v && Parse.Symbol("{") ?
             pos = Parse.Pos
             PUSH t.is_not_null = nn :
                  CASE Parse.Symbol("@") ? t.is_not_null = TRUE ;
             	  dir = t.direct_right_value(rf, Parse, idb), >> cf.All_expr[]
             ;
             Parse.Wait("}")
             ready_v = TRUE ;
        CASE !ready_v ?
             Parse.Pos = pos
             PUSH t.is_not_null = TRUE :
             	  dir = t.direct_right_value(rf, Parse, idb), >> cf.All_expr[] ;
             ;
        CASE dir != NONE ?
             CASE rf.Is_array ?
             	  {Fun} cf_dir|_function_current
             	  	= comp_of(dir.Fun_.Class.Parse).Current_fun
             	  	
                  make_blob|_temp = {Assign}(Source = dir, 
                  		Line = Parse.Line, Pos= 0,
                        Function_ = cf_dir, Level = cf_dir.Current_level),
                        OWNED BY cf
                        
                  St.Before_assign_[] << make_blob
                  INT blobs_count = blobs_count + 1
                  dir = New_str(Parse, ":blob"), >> cf.All_expr[] ;
             values[] << dir ;
        //DEBUG:
        //    CASE dir != NONE ?
        //         << "added direct value: " dir.Type_expr.Kind.Name 
        //         ";" dir.Operation.Name
        //         ";" dir.Fun_.Names[0]
        //         "(" dir.Items_[0].Operation.Name
        //         ";" dir.Items_[0].Var_arg.Type_.Kind.Name
        //         ";" dir.Items_[0].Var_arg.Str_c ")" #NL ;
        //; 
		--------------------------------------- 'end of list ?'
        //DEBUG: << "before next: " Parse.Next20 #NL ;
		CASE !Parse.Next_symbol(",") ? BREAK i ;
	;

    CASE blobs_count > 1 ?
         Parse.Err("It is not allowed to have more then one blob "
         "parameter"._Eitisnot) ;
	--------------------------- 'fields which must be set but not listed'
	FOR fnn|ot_null IN t.Not_null[] :
		CASE fnn !IN fields[] && fnn !IN t.Counter[] ?
			 Parse.Err("Field"._Efield7 " " fnn.Whole_name 
             " " ("is not null, but it is not set"._Eisnot )) ;
	;
	--------------------------- 'fields list to St'
	CASE but != NONE ?
		 {Expr} s_values|_or_fields = {Expr}(Operation= 'BINARY', Sign='CONCAT',
			 Type_obj_ = Parse.Compiler.TYPE_STR), >> cf.All_expr[]
			 
		 {Expr} s_remove_c|omma_leading = {Expr}(Operation= 'FUN', 
			 Fun_= idb.Find_fun("Remove_comma_1", TRUE), Items_[] << s_values),
			 >> cf.All_expr[]
	;
	{Expr} s_comma|_const = Parse.New_str(", "), >> cf.All_expr[]
		
    want_comma = FALSE
	FOR fld IN fields[] :
	    CASE values[fld.Index] != NONE ?
	         STR s_fname|_to_append 
	         s_fname = "[" t.Actual_field_name(fld.Names[0], Parse) "]"
			 {Expr} fname|_field = New_str(Parse, s_fname), >> cf.All_expr[]
			 CASE but != NONE ?
			 	  {Expr} fname_const = New_str(Parse, 
			 	  		fld.Names[0]),
			 	  		 >> cf.All_expr[]
			 	  		 
			 	  CASE want_comma ? 
			 	       fname.Var_arg.Str_c = "," fname.Var_arg.Str_c ;
			 	       
			 	  {Expr} chk_null = {Expr}(Operation= 'FUN', 
			 	  	Fun_= idb.Find_fun("Skip_field", TRUE), 
			 	  	Items_[] << fname << fname_const << but),
			 	  	>> cf.All_expr[]
			 	  	     
			      fname = chk_null
			      
			      want_comma = FALSE
			 ;
				 
	    ELSE fname = New_str(Parse, fld.Str_c ), >> cf.All_expr[]
	    	 St.Items_[] << fname
	    	 CONTINUE fld
	    ;
    	CASE want_comma ? St.Items_[] << s_comma ;
    	CASE but != NONE ?
			 s_values.Items_[] << fname
	    ELSE St.Items_[] << fname ;        
	    want_comma = TRUE 
	    	      
        //DEBUG:
        //      CASE comment_sep ?
        //      	<< "added: " fname.Var_arg.Str_c >> ;
        //;
	;
	CASE but != NONE ? St.Items_[] << s_remove_c ;
		
	CASE !comment_sep ?
		 {Expr} br|anch_close_and_values_branch_open = New_str(Parse,
			 " ) values("), >> cf.All_expr[]
			 
	ELSE br = New_str(Parse, #NL"-- 'end'"#NL" ) values("), >> cf.All_expr[] ;
		
	St.Items_[] << br
	--------------------------- 'values to St'
	CASE but != NONE ?
		 s_values = {Expr}(Operation= 'BINARY', Sign='CONCAT',
			 Type_obj_ = Parse.Compiler.TYPE_STR), >> cf.All_expr[]
			 
		 s_remove_c = {Expr}(Operation= 'FUN', 
			 Fun_= idb.Find_fun("Remove_comma_1", TRUE), Items_[] << s_values),
			 >> cf.All_expr[]
	;
	want_comma = FALSE	
	
	{Expr} comma_val|ue = {Expr}(Operation= 'CONST',
		   Var_arg= {Var}(Line= 0, Is_const= TRUE,
	         	Type_= Parse.Compiler.TYPE_STR, Str_c= ",")), >> cf.All_expr[]
	         	
	FOR v IN values[] :
	    CASE v != NONE ?
        	{Expr} f_value|_to_append = v
			
			CASE but != NONE ?
			 	 fname_const = New_str(Parse,
			 	 	"" t.Actual_field_name(fields[v.INDEX].Names[0], Parse)),
			 	 	>> cf.All_expr[]
			 	 	
			 	 {Expr} param0|_value = {Expr}(
			 	 	Operation= 'BINARY', Sign= 'CONCAT',
			 	 	Items_[] << v), >> cf.All_expr[]
			 	 	
			 	 CASE want_comma ?
			 	      param0.Items_[].Insert(0, comma_val) ;
			 	      
			 	 f_value = {Expr}(Operation= 'FUN', 
			 	     Fun_= idb.Find_fun("Skip_field", TRUE), 
			 	     Items_[] << param0 << fname_const << but),
			 	     >> cf.All_expr[]
			 	 
			 	 want_comma = FALSE
			;
	    ELSE f_value = New_str(Parse, fields[v.Index].Str_c), >> cf.All_expr[]
	    	 St.Items_[] << f_value
	    	 CONTINUE v ;
    	CASE want_comma ? St.Items_[] << s_comma ;
    	CASE but != NONE ?
	    	 s_values.Items_[] << f_value
	    ELSE St.Items_[] << f_value ; 
	    want_comma = TRUE     
	;
	CASE but != NONE ? St.Items_[] << s_remove_c ;
	CASE !comment_sep ?
		 br = New_str(Parse, ")"), >> cf.All_expr[]
	ELSE br = New_str(Parse, #NL"-- 'end"#NL")"), >> cf.All_expr[] ;
	St.Items_[] << br
    //DEBUG: << "added: "#NL St.SQL_concat #NL #NL ;
    .

	
	
	
	
	
	
	
	
	
FUNCTION recognize_UPDATE(
	{Parser} Parse|_of_source, {Expr} St|atement)
	:
	---------------------------------------- ''
	{Type} cc|lass_current = comp_of(Parse).Current_class
	{Fun} cf|unction_current = comp_of(Parse).Current_fun
	{Type} idb|_imported = comp_of(Parse).Current_class.Find_type("{DB}")

	----------------------------------------- 'table updating'
	STR name_t|able_updating = Parse.Ident
	{Table} t|able_updating = cc.Find_table(name_t)
	St.Items_[0].Var_arg.Str_c = St.Items_[0].Var_arg.Str_c " " t.Name_actual
	
	CASE !Parse.Next_symbol("SET") ?
		 
		 LIKE recognize_DELETE ................ 'from and joins'
		 CASE join_info[].Count > 1 ?
			  {Expr} from|_clause =  New_str(Parse,
	             #NL"FROM " t.Name_actual " " ta " "),
	             >> cf.All_expr[] ;
	;
    ------------------------------ 'set fields to values'
	St.Items_[0].Var_arg.Str_c = St.Items_[0].Var_arg.Str_c " SET "
	CASE Parse.Symbol("BY") ?
		 LIKE recognize_INSERT ...................... 'BY structure value' //,
		 	  //BUT(Nulls= FALSE ==> Nulls= TRUE)
	ELSE Parse.Wait("SET")	;
	LIKE recognize_INSERT ................ 'input list of fields and values'
	--------------------------------------- 'field = value, -- for each pair'
	STR comma|_before = ""
	FOR fld IN fields[] :
		CASE fld.Is_const ?
		     {Expr} value_c|omment = New_str(Parse, fld.Str_c), >> cf.All_expr[]
	    	 St.Items_[] << value_c
		     CONTINUE fld ;
		     
		STR s_fname|_to_append 
        s_fname = t.Actual_field_name(fld.Names[0], Parse)
        
		{Expr} fname|_field_expr = New_str(Parse, comma "[" s_fname "] = "),
			>> cf.All_expr[]
			
		CASE but != NONE ?
			 -------------------------- 'BUT except_fields[]'
			 {Expr} fname_only = New_str(Parse, s_fname), >> cf.All_expr[]
			 
			 {Expr} assign_s|tring = {Expr}(Operation= 'BINARY', Sign= 'CONCAT',
			 		Items_[] << fname << values[fld.Index]), >> cf.All_expr[]
			 	  		      
		     {Expr} call_skip|_field_function = {Expr}(Operation= 'FUN',
		     	Fun_= idb.Find_fun("Skip_field", TRUE),
		     	Items_[] << assign_s << fname_only << but), >> cf.All_expr[]
		     	
		     St.Items_[] << call_skip
		ELSE
			 -------------------------- 'destination'
			 St.Items_[] << fname
			 ------------------------- 'source from field of structure'
			 St.Items_[] << values[fld.Index]
		;
		comma = ", " 
	;
    ---------------------------------------- 'FROM and JOINs if present'
    CASE from != NONE ? St.Items_[] << from ;
    CASE joins_text != NONE ? St.Items_[] << joins_text ;
    CASE value_c != NONE ?
         value_c = New_str(Parse, #NL"------------------ 'WHERE CLAUSE'"#NL), 
         	 >> cf.All_expr[]
         	 
         St.Items_[] << value_c ;
    ---------------------------------------- 'WHERE condition'
    //DEBUG: << "before WHERE: " Parse.Next20 >> STR debug_where ;
	CASE Parse.Symbol(",") ? NONE ;
	//DEBUG: BOOL debug_recognize = TRUE ;	
    LIKE recognize_DELETE ......................... 'WHERE clause'
	---------------------------------------- 'must be WHERE or JOINs'	 
	CASE join_t[].Count == 0 && cond == NONE ?
		 Parse.Err("WHERE or JOINs must be present"._Ewhere2) ; .
		 

		 
		 
FUN list_selected_expressions|_dummy(
	{Parser} Parse|_of_source, {Expr} St|atement)
    :
    {Table} join_t|ables[]
    {join} join_info[]
    //{Type} cc|lass_current //= comp_of(Parse).Current_class
	{Fun} cf|unction_current //= comp_of(Parse).Current_fun
	//{Type} idb|_imported = comp_of(Parse).Current_class.Find_type("{DB}")
	{Table} t|able_select_from
	STR as|_selected_names[]
	STR distinct|_sql
	{Type}.{kind_type} last_operand_type
	STR al|ias_name
    STR fname|_field
    INT ind|ex_in_as
    STR all|_fields_in_all_tables[]
    {Expr} text|_sql
    BOOL list_r|eady
    {Expr} comma|_before
    STR comment|_to_add_to
    {Expr} e_comm|ent_expr
    STR func|tion_sql
    STR prefix_al|ias_when_present
    {Expr} fitem|_function 
    STR sfield|_selected
    BOOL as_present
    STR as_suffix
    
    STR aprefix|_alias_table
    {Table} jt|able_joined 
    BOOL want_cl|ose_bracket_after = TRUE
	--------------------------------------- 'list_selected_expressions', REUSED
CASE TRUE ?
     --------------------- 'list all fields'
     FOR jj IN join_t[] :
         FOR fld IN jj.Base_.Field[] :
             all[] << jj.Actual_field_name(fld.Names[0], Parse) ;
     ;
     ---------------------------------------- 'single * as a list'
;
     CASE Parse.Symbol("*") ?
		  text = New_str(Parse, " * "), >> cf.All_expr[]
		  St.Items_[] << text
          FOR fld IN t.Base_.Field[] :
              as[] << t.Actual_field_name(fld.Names[0], Parse).Upper ;
		  list_r = TRUE ;
	 comma = NONE
     //debug: STR debug_in ;
	 FOR i|dummy IN [Parse.Pos TO Parse.Last] :
         CASE Parse.Symbol("--") ? 
              CASE !ending_with_apostroph(Parse) ? 
	               NONE = Parse.Skip_to(#NL)
	               CASE Parse.End_of_line ? Parse.Fin ;
	               CONTINUE i ;     
			  comment = #NL"--" Parse.Skip_to("'")
	          Parse.Wait("'")
	          comment = comment "'" Parse.Skip_to("'") "'"#NL
	          Parse.Wait("'")
	          CASE Parse.End_of_line ? Parse.Fin ;
	          --------------------------- 'add comment as part of list'
	          e_comm = {Expr}(Operation= 'CONST',
	         	 Var_arg= {Var}(Is_const= TRUE, Str_c= comment, Line= 0,
	         		 Type_= Parse.Compiler.TYPE_STR)),
	         	 >> cf.All_expr[]
	         	 
	          St.Items_[] << e_comm             
              CONTINUE i ;
         CASE list_r ? BREAK i ;
         //DEBUG: << "recognizing select list: " Parse.Next20 #NL ;
         CASE comma != NONE ? St.Items_[] << comma ;
         --------------------------------------- 'COUNT(...) MIN MAX AVG'
         CASE ?
         [Parse.Symbol("COUNT")] ? func = "COUNT"
         [Parse.Symbol("MAX")] ? func = "MAX"
         [Parse.Symbol("MIN")] ? func = "MIN"
         [Parse.Symbol("AVG")] ? func = "AVG" ;
         CASE func != "" && Parse.Symbol("(") ?
               --------------------------------- 'it is sql function'
               last_operand_type = 'INT'
               CASE last_operand_type == 'INT' ? NONE ;
               CASE Parse.Symbol("DISTINCT") ? distinct = " DISTINCT " 
               ELSE distinct = "" ;
               al = ""
               //t = join_t[0]
               CASE join_info[].Count > 1 ?
                     al = wait_alias(Parse, join_info[])
                     t = joined_table(al, join_info[], join_t[]) 
                     prefix_al = al "." ;
               --------------------------------- 'parameter of sql function'
               CASE ?
               [Parse.Symbol("*")] ?
                    fitem= New_str(Parse,
                           func "(" distinct prefix_al "*)"), >> cf.All_expr[]
               [next_is_alias(Parse)] ?
                   	 al = wait_alias(Parse, join_info[])
                   	 t = joined_table(al, join_info[], join_t[]) 
                   	 fname = Parse.Ident
                     NONE = t.Base_.Find_field(fname)
                     
                     fitem = New_str(Parse,
                             func "(" distinct prefix_al t.Actual_field_name(
                             fname, Parse) ")" ), >> cf.All_expr[]
               ELSE  
                     fname = Parse.Ident
  
                     NONE = t.Base_.Find_field(fname)
                     
                     fitem = New_str(Parse,
                             func "(" distinct prefix_al t.Actual_field_name(
                             fname, Parse) ")" ), >> cf.All_expr[]
               ;
               Parse.Wait(")")
               Parse.Wait("AS")
               STR as1|_any_register = Parse.Ident 
               as[] << as1.Upper
               CASE as[*] != as1 ? 
                    Parse.Warn("It is desired to use UPPERCASED aliases "
                    "still T-SQL is case insensitive"._WAsNoCase)
               ;
               ------------------------- 'check not duplicating AS label'
               ind = as[].Find(as[*])
               CASE ind >= 0 && ind < as[].Count-1 ?
                    Parse.Err("Duplicating selected value name"._Edup5 
                    ": " as[*]) ;
               -------------------------- 'provide comma'
               CASE comma == NONE ?
                    comma = New_str(Parse, ", "), >> cf.All_expr[] ;
               -------------------------- 'adopt sql function'
               fitem.Var_arg.Str_c = fitem.Var_arg.Str_c " AS " as[*]
               St.Items_[] << fitem
               CASE !Parse.Symbol(",") ? BREAK i ;
               CONTINUE i
         ;
         CASE Parse.Symbol("SQL") ?
			  Parse.Wait("(")
			  text = Recognize_typed(Parse, 'STR'), >> cf.All_expr[]  
              arrays_are_not_allowed(text, Parse)
			  St.Items_[] << text
			  Parse.Wait(")")
              -------------------------- '... AS field_alias'
              Parse.Wait("AS")
              as1 = Parse.Ident 
              as[] << as1.Upper
              CASE as1 != as[*] ? 
                   Parse.Warn("It is desired to use UPPERCASED aliases still "
                   "in T-SQL all identifiers are case insensitive"._WAsNoCase2)
              ;
              ind = as[].Find(as[*]) 
              CASE ind >= 0 && ind < as[].Count-1 ?
                   Parse.Err("Duplicating selected value name"._Edup5a 
                   ": " as[*]) ;
              text = New_str(Parse, " AS " as[*]), >> cf.All_expr[]
              St.Items_[] << text
              CASE comma == NONE ?
                    comma = New_str(Parse, ", "), >> cf.All_expr[] ;
			  CASE !Parse.Symbol(",") ? BREAK i ;
			  CONTINUE i ;
         ---------------------------------- '[alias.]field [AS alias_field]'
         sfield = ""
         PUSH Parse.Pos :
              CASE Parse.Symbol("[") ? BOOL wait_cl|ose_bracket = TRUE ;
              STR id|entifier_next_dummy = Parse.Ident
              CASE wait_cl ? Parse.Wait("]")
              ELSE BOOL use_alias = Parse.Next_symbol(".") ;
         ;
		 //CASE join_info[].Count > 1 ?
		 CASE use_alias ?
			   aprefix = wait_alias(Parse, join_info[]) "."
			   jt = joined_table(aprefix.[0 TO *-1], join_info[], join_t[])
			   CASE Parse.Symbol("*") ? fname = "*"
			  						    sfield = "*"     
			   ELSE wait_cl = FALSE
			   	    CASE Parse.Symbol("[") ? wait_cl = TRUE ;
			   	    fname = Parse.Ident 
			   	    CASE wait_cl ? Parse.Wait("]") ; 
			  	    sfield = jt.Actual_field_name(fname, Parse)
			   ;
		 ELSE aprefix = ""
		      jt = join_t[0]
		 	  wait_cl = FALSE
		 	  CASE Parse.Symbol("[") ? wait_cl = TRUE ;
			  fname = Parse.Ident
			  CASE wait_cl ? Parse.Wait("]") ;
			  sfield = t.Actual_field_name(fname, Parse) ;
         //CASE sfield != "" ?
         //     text = New_str(Parse, sfield), >> cf.All_expr[]
         //     St.Items_[] << text
         //ELSE sfield = fname ;
		 CASE fname != "*" && Parse.Symbol("AS") ?
			  as_present = TRUE
			  as1 = Parse.Ident 
			  as[] << as1.Upper
			  CASE as1 != as[*] ? 
			       Parse.Warn("It is desired to use UPPERCASED aliases still "
                   "in T-SQL all identifiers are case insensitive"._WAsNoCase3)
			  ;
              ind = as[].Find(as[*])
              CASE ind >= 0 && ind < as[].Count-1 ?
                   Parse.Err("Duplicating selected value name"._Edup5b 
                   ": " as[*]) ;
		 ELSE as_present = FALSE
			  as[] << sfield.Upper ;
         CASE ?
         [fname != "*" && as[].Find(as[*]) < as[].Count-1] ?
			  Parse.Err("Field"._Efield8 " " as[*] 
              " " ("was already selected at least once, "
			  "use AS name qualifier to rename output field name"._Ewas2 ))
         [fname == "*"] ?
             FOR f_t IN jt.Base_.Field[] :
                 CASE f_t.Names[0].Upper IN as[] ?
            		  Parse.Err("Field/alias duplicating while selecting *:"
            		  ""._EDupFieldAliasOn_STAR_select ": " f_t.Names[0])          
                 ;
             ;
         ;
         ------------------------ 'field found, append text'
		 CASE as_present ? as_suffix = " AS " as[*]
		 ELSE as_suffix = "" ;
         text = New_str(Parse, aprefix sfield as_suffix), >> cf.All_expr[]
         St.Items_[] << text
         CASE comma == NONE ?
         	  comma = New_str(Parse, ", "), >> cf.All_expr[] ;
         CASE !want_cl ? BREAK i ;
		 CASE !Parse.Symbol(",") ? BREAK i ;
	 ;     
	 -----------------------
	 .	     
		 
FUNCTION next_is_alias({Parser} Parse|_source)==> BOOL :
	PUSH Parse.Pos :
		 NONE = Parse.Ident
		 RESULT = Parse.Next_symbol(".") ; .
		 
		
FUNCTION recognize_SELECT(
	{Parser} Parse|_of_source, {Expr} St|atement,
	{join} Joins|_outer[], {Table} Tables|_outer[])
	:
	{Type} cc|lass_current = comp_of(Parse).Current_class
	{Fun} cf|unction_current = comp_of(Parse).Current_fun
	{Type} idb|_imported = comp_of(Parse).Current_class.Find_type("{DB}")
	CASE idb==NONE ? Parse.Err("{DB} class was not found") ;

	--------------------------------------- 'TOP N, DISTINCT'
	CASE Parse.Symbol("TOP") ?
		 St.Items_[0].Var_arg.Str_c = St.Items_[0].Var_arg.Str_c " TOP "
		 {Expr} how|_match = Recognize_typed(Parse, 'INT'), >> cf.All_expr[]
         arrays_are_not_allowed(how, Parse)
         
		 {Expr} ih|ow_match_as_sql_int = value_to_sql_string(
		 	comp_of(Parse).TYPE_INT, how, idb,
		 	Parse= Parse, Nulls= FALSE), >> cf.All_expr[]
				
		 St.Items_[] << ih
		 Parse.Wait(",") ;
	CASE Parse.Symbol("DISTINCT") ?
		 {Expr} sdist|inct = New_str(Parse, " DISTINCT "), >> cf.All_expr[]
		 St.Items_[] << sdist
		 CASE Parse.Symbol(",") ? NONE ;
	;     
	---------------------------------- 'what select (just skip, handle later)'
	{Table} t|able_select_from
	CASE Parse.Symbol("(") ?
	     INT list_start|_position = Parse.Pos
	     INT list_line = Parse.Line
	     FOR n|dummy IN [0 TO 1_000_000] :
	         CASE Parse.End_of_line ?
	              Parse.Fin ;
	         CASE Parse.Next_char ?
	         [#QU]: Parse.Skip_char
	         		NONE = Parse.Skip_to(#QU)
	         		CONTINUE n
	         ["("]: INT level|_bracket += 1
	         [")"]: level -= 1
	         		CASE level < 0 ? BREAK n ;
	         ;
	         Parse.Skip_char ;     
	     //NONE = Parse.Skip_to(")")
	     
         {join} join_info[]
         {Table} join_t|ables[]
         STR as|_selected_names[]
         STR distinct|_sql
         {Type}.{kind_type} last_operand_type
         STR al|ias_name
         STR fname|_field
         INT ind|ex_in_as
         STR all|_fields_in_all_tables[]
         {Expr} text|_sql
         BOOL list_r|eady
         {Expr} comma|_before
         STR comment|_to_add_to
         {Expr} e_comm|ent_expr
         STR func|tion_sql
         STR prefix_al|ias_when_present
         {Expr} fitem|_function
         STR sfield|_selected
         BOOL as_present
         STR as_suffix
		 
         STR aprefix|_alias_table
         {Table} jt|able_joined
		 
	 	 Parse.Wait(")")
	 	 CASE Parse.Symbol(",") ? NONE ;
	 	 BOOL listed_already = TRUE ;     
	--------------------------------------- 'FROM and JOINs'
	Parse.Wait("FROM")
	STR name_t|able_delete_from = Parse.Ident
	t = cc.Find_table(name_t)
    Parse.Skip_sp
    BOOL letter_next = Parse.Next_is_letter
	CASE letter_next && !Parse.Next_symbol("WHERE") ?
		 STR ta|lias_table = Parse.Ident
		 CASE Parse.Symbol(",") ?
			  join_t[] << t
			  join_info[] << {join}( Name_t = t.Name_actual, Alias_t = ta)
 					 
			  {Expr} joins_text = recognize_joins(Parse, join_t[], join_info[]),
                     >> cf.All_expr[]
                     
			  CASE joins_text.Items_[].Count == 0 ? joins_text = NONE ;
		 ;
	ELSE join_t[] << t
		 join_info[] << {join}( Name_t = t.Name_actual, Alias_t = "")
         CASE Parse.Symbol(",") ? NONE ;
	;
	CASE listed_already ?
         INT restore_pos|ition = Parse.Pos
         INT restore_line = Parse.Line
         Parse.Pos = list_start
         Parse.Line = list_line
         BOOL want_cl|ose_bracket_after = TRUE
	ELSE 
		 CASE Parse.Symbol("(") ? want_cl = TRUE ; 
	;
    FOR j INDEXING ({Table}, {join}) IN [0 TO Joins[].Count-1] :
        join_t[] << Tables[j]
        join_info[] << Joins[j].Clone ;
    ------------------------ 'may be list of fields and expressions selected ?'
    LIKE list_selected_expressions ........ 'list_selected_expressions'
    CASE want_cl ? Parse.Wait(")") ;
 	CASE Parse.Symbol(",") ? NONE ;
	     //DEBUG: "as[].Count=" as[].Count >> ;
	CASE restore_line > 0 ?
	     Parse.Pos = restore_pos
		 Parse.Line = restore_line ;
	-------------------------------- 'FROM clause into output statement'
	text = New_str(Parse, #NL"FROM " join_t[0].Name_actual " " ta " "),
		   >> cf.All_expr[]
		   
	St.Items_[] << text
    CASE joins_text != NONE ? St.Items_[] << joins_text ;
	-------------------------------- 'WHERE clause'
	CASE Parse.Symbol(",") ? NONE ;
	//DEBUG: BOOL debug_recognize = FALSE ;
	LIKE recognize_DELETE ......................... 'WHERE clause'
    CASE Parse.Symbol(",") ?
         CASE !Parse.Next_symbol("GROUP") && !Parse.Next_symbol("ORDER") ?
              Parse.Err("Waiting for GROUP BY / ORDER BY"._Ewait2 
              ": " Parse.Next20) ;
    ;
	-------------------------------- 'grouping'
	CASE Parse.Symbol("GROUP") ?
		 Parse.Wait("BY")
		 CASE Parse.Symbol("(") ? BOOL wait_close_br = TRUE
		 ELSE BOOL only_1|_item_in_list = TRUE ;     
		 STR gr|oup_by_text = #NL"GROUP BY "
		 STR gcomma|_group_order = ""
		 ----------------------- 'group list'
		 FOR i|dummy IN [Parse.Pos TO Parse.Last] :
		 	 CASE Parse.Symbol("[") ? BOOL wait_br|acket_close = TRUE ;
			 fname = Parse.Ident
			 CASE wait_br ? Parse.Wait("]") ;
             //debug: << "-- group by " fname ": " Parse.Next20 >> debug_in ;
			 CASE ?
			 [Parse.Symbol(".")] ?
			 	 wait_br = FALSE
			 	 CASE Parse.Symbol("[") ? wait_br = TRUE ;
			     STR table_alias = fname
			     CASE wait_br ? Parse.Wait("]") ;
			     CASE !is_alias(table_alias, join_info[]) ?
			          Parse.Err("Not an alias of a table"._ENotAlias 
			          ": " table_alias) ;
			     fname = Parse.Ident
			     CASE fname.Upper !IN as[] ?
			          Parse.Err("Field or alias"._Efield9a " " fname 
	                  " " ("was not in select list "
					  "to use it in GROUP BY"._Ewasnota )) ;
			     fname = table_alias "." fname
			 [fname.Upper !IN as[]] ?
				  Parse.Err("Field or alias"._Efield9 " " fname 
                  " " ("was not in select list "
				  "to use it in GROUP BY"._Ewasnot ))
			 ;
			 gr = gr gcomma fname
			 STR g_list|_of_grouped_fields[] << fname
			 CASE only_1 ? BREAK i ;
			 gcomma = ", "
			 CASE !Parse.Symbol(",") ? BREAK i ;
		 ;
		 CASE wait_close_br ? Parse.Wait(")") ;
		 text = New_str(Parse, gr), >> cf.All_expr[]
		 St.Items_[] << text
         CASE Parse.Symbol(",") ?
              CASE !Parse.Next_symbol("ORDER") ?
                   Parse.Err("Waiting ORDER BY following GROUP BY"._Ewait3) ;
         ELSE ==> ;
	;
	----------------------------------- 'ordering'
	CASE Parse.Symbol("ORDER") ?
		 Parse.Wait("BY")
		 CASE Parse.Symbol("(") ? wait_close_br = TRUE
		 ELSE only_1 = TRUE ;     
		 STR ord|er_by_text = #NL"ORDER BY "
		 gcomma = ""
		 ----------------------- 'order list'
		 FOR i|dummy IN [Parse.Pos TO Parse.Last] :
             //DEBUG: << "Will order by: " Parse.Next20 #NL ;
             wait_br = FALSE
             CASE Parse.Symbol("[") ? wait_br = TRUE ;
			 fname = Parse.Ident
			 CASE wait_br ? Parse.Wait("]") ;     
			 CASE Parse.Symbol(".") ?
			      Parse.Err("Do not access fields in the ORDER BY section "
			      "in form a.field, use instead AS in the selection list "
			      "to distinct later same named fields got from different "
			      "tables"._EUseAs) ;
			 CASE fname IN g_list[] ?
				  Parse.Err("It is not possible order by a group "
                  "field"._Eitisnot2)
			 ;
			 CASE fname.Upper !IN as[] && "*" !IN as[] ?
				  Parse.Err("Field or alias"._Efield10 " " fname 
                  " " ("was not in select list "
				  "to use it in ordering"._Ewasnot2 ))
			 ;
			 ----------------------------- 'check if ambigouos'
             INT k|ount_in_select_list = 0
             FOR aa IN all[] :
                 //DEBUG: "aa[" aa.Index "]:" aa #NL ;
                 //CASE "." IN aa ? aa = aa[aa.Find(".")+1 TO *] ;
                 CASE aa.Lower == fname.Lower ? k += 1 ;
             ;
             CASE k > 1 ?
                  Parse.Err("Reference in the ORDER list to field is ambigouos"
                  ""._EAmbigOrder ": " fname) ;
			 -------------------
			 ord = ord gcomma fname
			 CASE Parse.Symbol("DESC") || Parse.Symbol("DESCENDANT") ?
				  ord = ord " DESC" ;
			 CASE only_1 ? BREAK i ;
			 gcomma = ", "
			 CASE !Parse.Symbol(",") ? BREAK i ;
		 ;
		 CASE wait_close_br ? Parse.Wait(")") ;
		 text = New_str(Parse, ord), >> cf.All_expr[]
		 St.Items_[] << text
	;
    .
	
FUN wait_alias(
	{Parser} Parse|_source, {join} Join|_info[]) ==> STR
	:
    //DEBUG: << "waiting alias: " Parse.Next20 #NL ;
    RESULT = Parse.Ident
    CASE !is_alias(RESULT, Join[]) ?
         Parse.Err("Waiting for an alias, but"._Ewait4 
         ": " RESULT " " Parse.Next20)
    ;
    Parse.Wait(".") .
		 
		 
//FUN check_field_present( STR A|ctual_ff_error_if_empty,
//		{Parser} Parse|_to_report_error,
//		STR Error|_text) :
//	-------------------------------------------- ''
//	CASE A == "" ?  Parse.Err(Error) ; .

		 
FUN value_to_sql_string(
    {Type} Type|_to_convert,
    {Expr} Value|_will_convert,
    {Type} Idb|_imported_DB,
    {Parser} Parse|_to_show_error_only,
    BOOL Nulls|_from_0_values) ==> {Expr}, NEW
    :
    CASE Nulls ? STR zero|_suffix = "0" ; 
	------------------------------------------------------ ''
	RESULT = {Expr}(Operation = 'FUN', Items_[] << Value), 	OWNED BY Value
	CASE Type.Kind ?
	['BOOL']: STR fn|ame_of_function_searching = "Bool_sql" zero
	['INT', 'ENUM']: fn = "Int_sql" zero  
	['REAL']:	
                CASE Value.Type_expr.Kind == 'REAL' ?
                     fn = "Real_sql" zero 
                ELSE fn = "Int_sql" zero ;  
	['STR']:	fn = "Str_sql" zero
	['STRUCTURE']:
	    CASE Type.Whole_name == "{id|entity}" ?
	    	 CASE Value.Type_expr.Kind IN ['INT', 'ENUM'] ?
	    	      fn = "Int_sql" zero 
	    	 ELSE fn = "Id_sql" zero ;
	    ELSE fn = "Date_sql" zero ;
	
	['CLASS']: Parse.Err("Converting an object to SQL is not possible"
			   ""._ESQL_obj) ; 
	
	RESULT.Fun_ = Idb.Find_fun(fn, TRUE)
	CASE RESULT.Fun_==NONE ? Parse.Err("Function was not found in {DB} class: "
		 "" fn) ;
	.
	
FUN check_conversion_fun(
    {Expr} Conv|ersion_call, {Type} Type|_SQL_field,
    {Parser} Parse|_to_report_errors)
    :
	CASE Conv.Fun_ == NONE ?
		Parse.Err("Incorrect SQL data type"._Eincorr 
        ": " Type.Whole_name) ; .
		
BOOL is_not_null
        
METHOD direct_right_value(
	{Var} R|ecord_field,
	{Parser} Parse|_source,
	{Type} Idb|_imported_DB) ==> {Expr}, NEW
    :
	----------------------------------------------------- ''
	{Fun} cf|unction_current = comp_of(Parse).Current_fun
	INT pos|ition_value_started = Parse.Pos
	CASE ?
    [R.Type_.Kind == 'STRUCTURE'] ?
		{Expr} right|_value = Recognize(Parse), >> cf.All_expr[]
		{Type} r_type|_of_right = right.Type_expr
		CASE r_type.Kind == 'STRUCTURE' && R.Type_ != r_type ? 
			 CASE ?
			 ["{date_time}" IN R.Type_.Names[]] ?
				  PUSH Parse.Pos = pos :
					   Parse.Err("Waiting value of type {date_time} but"._Ewait5 
	                   " " r_type.Whole_name 
	                   " " ("found"._Efound ) ": " Parse.Next20) ;
			 [R.Type_.Whole_name == "{id|entity}"] ? 
				  PUSH Parse.Pos = pos :
					   Parse.Err("Waiting value of type {id|entity} but"._EwaitId 
	                   " " r_type.Whole_name 
	                   " " ("found"._Efound1 ) ": " Parse.Next20) ;
			 ;
		;
	[R.Type_.Kind == 'REAL'] ?
		 right = Recognize(Parse), >> cf.All_expr[]
		 CASE right.Type_expr.Kind !IN ['INT', 'REAL'] ?
			  PUSH Parse.Pos = pos :
				   Parse.Err("Waiting value of type REAL but"._Ewait6 
                   " " right.Type_expr.Whole_name 
                   " " ("found"._Efound2 ) ": " Parse.Next20) ;
		 ;
	[R.Type_.Kind == 'BOOL'] ?
		 right = Recognize_typed(Parse, 'BOOL'), >> cf.All_expr[]
	[R.Type_.Kind == 'INT'] ?
		 right = Recognize_typed(Parse, 'INT'), >> cf.All_expr[]
	ELSE right = Recognize_typed(Parse, R.Type_.Kind), >> cf.All_expr[] ;
    //DEBUG: << "direct right value recognized: " right.Type_expr.Kind.Name
    //       "; Int_c=" right.Var_arg.Int_c.Str #NL ;
    arrays_are_not_allowed(right, Parse)
    CASE R.Is_array ? RESULT = right ==> ;
    
    CASE right.Type_expr.Kind != R.Type_.Kind
         && !(R.Type_.Kind=='REAL' && right.Type_expr.Kind == 'INT')
         ?
         STR det|ails_error = " " "but " R.Type_.Whole_name 
         det = det " = {" right.Type_expr.Whole_name "}"
         	 
         Parse.Err("Data type of an expression should correspond "
         "to a field set"._EExprTypeShouldCorrespondField det) ; 
         
	RESULT = value_to_sql_string(R.Type_, right, Idb, 
		Parse= Parse, Nulls= !is_not_null)
		
    check_conversion_fun(RESULT, R.Type_, Parse) .

			 

TEST of_recognize :
	-------------------------------------- 'prepare p|arser'
	{Parser} p|arser_temp = Test_parser
	{Compiler} c|ompiler_test = p.Compiler_.{Compiler}
	ASSERT c != NONE
	------------------ '3 data items'
	d|ate_time_temp = {Type}(Kind = 'STRUCTURE', Names[] << "{date_time}")
	d.Provide_names_lowered
		
	ip|arameter_int_temp = {Var}(Line = -1, Type_ = c.TYPE_INT)
	ip.Provide_names_lowered
		
	fd|ata_function_temp = {Fun}(Parse = p, Names[] << "Date", A_Name_1 = "Date",
		Param[] << ip << ip << ip, Result = {Var}(Line = -1,
		Type_ = d))
		
	c.Current_class.Add_method(fd)
			
	ASSERT c.Errors_count == 0
	------------------------------------- 'prepare structure, base for table'
	r|ecord_temp = {Type}(Kind = 'STRUCTURE',
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_REAL, Names[] << "ID"),
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_STR, Names[] << "Num"),
		Field[] << {Var}(Line = -1, Type_ = d, Names[] << "Dat"),
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_INT, Names[] << "iNT"),
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_BOOL, Names[] << "bOOL"),
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_REAL, Names[] << "rEAL"),
		Field[] << {Var}(Line = -1, Type_ = c.TYPE_BYTE, Names[] << "bYTE"))
		
	FOR fld IN r.Field[] :
	    fld.Provide_names_lowered ;
		
	t|able_temp = {Table}(Base_ = r, Names[] << "Test", Name_actual = "TEST"),
		>> p.Class_.Table[]
		
	p.Test_line("FROM Test, WHERE {1}={1}")
	ASSERT c.Errors_count == 0
	------------------------------------------- 'need class {DB} in import'
    ----------- 'add 0-functions, also'
	db|_class_in_import_temp = {Type}(Names[] << "{DB}", Kind = 'CLASS')
	
    db.Method[] << {Fun}(Parse = p, Names[] << "Bool_sql",
    	A_Name_1 = "Bool_sql", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
    db.Method[] << {Fun}(Parse = p, Names[] << "Bool_sql0",
    	A_Name_1 = "Bool_sql0", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
    db.Method[] << {Fun}(Parse = p, Names[] << "Date_sql",
    	A_Name_1 = "Date_sql", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
    db.Method[] << {Fun}(Parse = p, Names[] << "Date_sql0",
    	A_Name_1 = "Date_sql0", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
    ASSERT db.Method[].Count==4
    ----------------- 'Int_sql'
    	
    db.Method[] << {Fun}(Parse = p, Names[] << "Int_sql",
    	A_Name_1 = "Int_sql", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
    db.Method[] << {Fun}(Parse = p, Names[] << "Int_sql0",
    	A_Name_1 = "Int_sql0", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
    db.Method[] << {Fun}(Parse = p, Names[] << "Real_sql",
    	A_Name_1 = "Real_sql", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
    db.Method[] << {Fun}(Parse = p, Names[] << "Real_sql0",
    	A_Name_1 = "Real_sql0", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
    	
	db.Method[] << {Fun}(Parse = p, Names[] << "Str_sql",
		A_Name_1 = "Str_sql", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
		
	db.Method[] << {Fun}(Parse = p, Names[] << "Str_sql0",
		A_Name_1 = "Str_sql0", Result = {Var}(Line = -1, Type_ = c.TYPE_STR))
		
	FOR m IN db.Method[] :
		db.Sorted_method[] << m
	    m.Provide_names_lowered ;
		
    ASSERT db.Method[].Count == 10
    ----------------------- 'set Parse'
    db.Parse = {Parser}(Class_ = db, Compiler_ = c, Warning_80= FALSE)
	c.Current_class.Import_[] << db
	ASSERT c.Errors_count == 0
	----------------------------------- 'DELETE' //-----------------------------
	------------------------------------------- 'DELETE 1 (where)'
	<< "DELETE1"#NL
	{Expr} e|xpr_temp = Recognize_SQL("DELETE", p)
    //>> STR which_error
    ASSERT e.SQL_concat == "DELETE FROM TEST WHERE 1 = 1", e.SQL_concat
	ASSERT c.Errors_count == 0
	------------------------------------------- 'DELETE 2 (where != CONST)'
	<< "DELETE2"#NL
	
	x|_variable = {Var}(Line = -1, Names[] << "X", Type_ = c.TYPE_STR),
		>> c.Current_fun.Local_var[]
		
	p.Test_line("Test, WHERE ID=100")
	e = Recognize_SQL("DELETE", p)
	//>> STR which_error
    ASSERT e.SQL_concat == "DELETE FROM TEST WHERE ID = 100", e.SQL_concat
	ASSERT c.Errors_count == 0
	------------------------------------------- 'DELETE 3 (joins)'
	<< "DELETE3"#NL
	p.Test_line("Test t, JOIN Test t2 ON (t.Num = t2.ID)")
	e = Recognize_SQL("DELETE", p)
	//>> STR which_error
	
    ASSERT e.SQL_concat == "DELETE FROM TEST t "
           "JOIN TEST t2 ON (t.Num = t2.ID)",  e.SQL_concat
           
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
    ------------------------------------------- 'DELETE 4 (bad joins)'
	<< "DELETE4"#NL
	
	p.Test_line("Test t, OUTER LEFT JOIN TEST t2 ON (t.Num = a.ID), "
    "WHERE " #QU "1 = 1" #QU)
    
	e = Recognize_SQL("DELETE", p)
	//>> STR which_error
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
	------------------------------------------- 'DELETE 0 (no where or joins)'
	<< "DELETE0"#NL
	p.Test_line("Test t")
	e = Recognize_SQL("DELETE", p)
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
	----------------------------------- 'INSERT' //-----------------------------
	p.Test_line("INTO Test bOOL = TRUE")
    
	//>> STR which_error
	e = Recognize_SQL("INSERT", p)
	
    ASSERT e.SQL_concat == "INSERT INTO TEST "
           "( [bOOL] ) "
           "values(Bool_sql(?))", e.SQL_concat
           
	------------------------------------------ 'INSERT 1 (list field=value,...)'
	<< "INSERT1"#NL
	
	p.Test_line("INTO Test, ID = 1, Num = " #QU "aaa" #QU 
	", Dat = Date(2017,08,19), iNT = {1}, bOOL = TRUE, bYTE = 0, bYTE = SQL("
    "" #QU "bYTE + 1" #QU ")")
    
	//>> STR which_error
	e = Recognize_SQL("INSERT", p)
	
    ASSERT e.SQL_concat == "INSERT INTO TEST "
           "( [ID], [Num], [Dat], [iNT], [bOOL], [bYTE], [bYTE] ) "
           "values(1, 'aaa', '2017/8/19', 1, Bool_sql(?), 0, bYTE + 1)", 
           e.SQL_concat
           
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
	---------------- 'INSERT 2 (errors: omit not null, set counter, dupe field)'
	<< "INSERT2"#NL
	t.Not_null[] << r.Field[1]
	t.Counter[] << r.Field[0]
	
	p.Test_line("INTO Test, ID = 1, ID = " #QU "bad" #QU ", Dat = " #QU 
	"error" #QU ", errName = 1")
	
	e = Recognize_SQL("INSERT", p)
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
	---------------- 'INSERT 2B (error: bad field)'
	<< "INSERT2B"#NL
	t.Not_null[] << r.Field[1]
	t.Counter[] << r.Field[0]
	p.Test_line("INTO Test, iNT = 1, errName = 1")
	e = Recognize_SQL("INSERT", p)
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
    ---------------------------------------- 'INSERT 3 (BY structure variable)'
	<< "INSERT3"#NL
	
	y|_variable = {Var}(Line = -1, Names[] << "Y", Type_ = r),
		>> c.Current_fun.Local_var[]
		
	FOR fld IN r.Field[] :
		CASE fld.Type_.Kind == 'ENUM' && fld.Type_.Names[0]=="{byte}" ?	
		     fld.Type_ = comp_of(p).TYPE_INT ;
	;
	p.Test_line("INTO Test, BY Y")
	e = Recognize_SQL("INSERT", p)
	//>> STR which_error
	
    ASSERT e.SQL_concat == "INSERT INTO TEST "
           "( [Num], [Dat], [iNT], [bOOL], [rEAL], [bYTE] ) "
           "values(Str_sql(?), unknown:'FUN';Date_sql0; , "
           "Int_sql(?), Bool_sql(?), Real_sql(?'FIELD';'REAL'), "
           "Int_sql(?))",
           e.SQL_concat
           
	ASSERT c.Errors_count == 0
	c.Errors_count = 0
	-------------------------------------- 'INSERT 4 (BY, but all are COUNTERs)'
	<< "INSERT4"#NL
	FOR j IN [1 TO r.Field[].Count-1] : t.Counter[] << r.Field[j] ;
	p.Test_line("INTO Test, BY Y")
	e = Recognize_SQL("INSERT", p)
	
    ASSERT e.SQL_concat == "INSERT INTO TEST ( ) values()",
           e.SQL_concat
           
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
	----------------------------------------- 'INSERT 5 (BY erroneous variable)'
	<< "INSERT5"#NL
	p.Test_line("INTO Test, BY X")
	e = Recognize_SQL("INSERT", p)
	ASSERT c.Errors_count > 0
	c.Errors_count = 0
	----------------------------------- 'UPDATE' //-----------------------------
	----------------------------------------- 'UPDATE 1 (BY Y)'
	<< "UPDATE1"#NL
	t.Counter[].Clear
	t.Counter[] << r.Field[0]
	
	p.Test_line("Test, BY Y, "
    "WHERE (Num = {" #QU "AAA" #QU "} OR Num = 'BBB') "
    "AND (NOT(ID > {100}) OR ID IN ({1}, {2}, {5}))" )
    
    //"AND SQL("#QU"TRUE"#QU") "
    //"OR ID >= {11} AND ID <= {17} AND ID <> {15} AND "
    //"Num < {"#QU"al4-string"#QU"} AND ID NOT IN ({0}) AND "
    //"Dat BETWEEN {Date(2017, 07, 1)} AND {Date(2017, 08, 1)}")
    
	e = Recognize_SQL("UPDATE", p)
	//>> STR which_error
	
    STR csql|_compared = e.SQL_compare("UPDATE TEST SET "
           "[Num] = Str_sql(?), "
           "[Dat] = unknown:'FUN';Date_sql0; , "
           "[iNT] = Int_sql(?), "
           "[bOOL] = Bool_sql(?), "
           "[rEAL] = Real_sql(?'FIELD';'REAL'), "
           "[bYTE] = Int_sql(?) "
           "WHERE (Num = 'AAA' OR Num = 'BBB') "
           "AND (NOT(ID > 100) OR ID IN (1, 2, 5))")
           
           //"[Dat] = Date_sql(?'FIELD';('UNKNOWN';'BOOL';0)), "
           
           //"AND TRUE "
           //"OR ID >= 11 AND ID <= 17 AND ID <> 15 AND "
           //"Num < 'al4-string' AND ID NOT IN (0) AND "
           //"Dat BETWEEN '2017/7/1' AND '2017/8/1'")
           
    ASSERT csql == "", e.SQL_concat
	ASSERT c.Errors_count == 0
    ----------------------------------------- 'UPDATE 2 (values)'
	<< "UPDATE2"#NL
	t.Counter[] << r.Field[0]
	
	p.Test_line("Test, SET [ID] = 1, [Num] = 2, [iNT] = 3, WHERE "
    "bOOL IN (1, 2)")
    
	e = Recognize_SQL("UPDATE", p)
	//>> STR which_error
	ASSERT c.Errors_count == 1
	
    ASSERT e.SQL_concat == "UPDATE TEST "
    	   "SET [ID] = 1, [Num] = Str_sql(?), [iNT] = 3 "
           "WHERE bOOL IN (1, 2)", e.SQL_concat
           
    c.Errors_count = 0
    ----------------------------------------- 'useful variables'
    rv|ariable_strusture = {Var}(Line = -1, Type_ = r, Names[] << "R"),
    	>> comp_of(p).Current_fun.Local_var[]
    	
    bv|ariable_byte = {Var}(Line = -1, Type_ = comp_of(p).TYPE_BYTE, 
        Names[] << "B"), >> comp_of(p).Current_fun.Local_var[]
        
    iva|riable_int_array = {Var}(Line = -1, Type_ = comp_of(p).TYPE_BYTE, 
        Names[] << "IA", Is_array = TRUE),
        >> comp_of(p).Current_fun.Local_var[]
        
    ASSERT TRUE
    ----------------------------------------- 'UPDATE 3 (joins)'
	<< "UPDATE3"#NL
	t.Counter[] << r.Field[0]
	
	p.Test_line("Test a, JOIN Test b ON (a.iNt = b.bYTE) "
    "SET ID = 1, Num = {2}, iNT = b.iNT, WHERE {R}={B} AND (a.iNT = n.bYTE)")
    
	e = Recognize_SQL("UPDATE", p)
	//>> STR which_error
	ASSERT c.Errors_count == 1
	
	STR comp1|are_pattern = "UPDATE TEST "
           "SET [ID] = 1, [Num] = Str_sql(?), [iNT] = b.iNT "
           "FROM TEST a JOIN TEST b ON (a.iNt = b.bYTE) "
           "WHERE Date_sql(?'VAR';('UNKNOWN';'BOOL';0)) = Int_sql(?) "
           "AND (a.iNT = Bool_sql(?))"
	
    ASSERT e.SQL_concat == comp1,
           "A=["e.SQL_concat "]       !=       B=[" comp1 "]"
           ", A.Len=" e.SQL_concat.Len.Str ", B.Len=" comp1.Len.Str
           ", first_diff=" first_diff_char(e.SQL_concat, comp1)     
           
    c.Errors_count = 0
    //----------------------------------------- 'field to work with BLOB'
    //r.Field[] << {Var}(Line = -1, Type_ = c.TYPE_BYTE, Names[] << "Photo",
    //        Is_array = TRUE, Is_fixed = TRUE, Size_value = 1)
    //        
    //{Var} fph|oto_field = r.Find_field("Photo")
    //<< "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&fph.Kind : " fph.Type_.Kind.Name #NL
    //ASSERT fph.Type_.Kind == 'BYTE'
    ----------------------------------------- 'variables to work with BLOB'
    db1|_database_variable = {Var}(Line = -1, Type_ = db, Names[] << "db1"),
    	>> c.Current_fun.Local_var[]
    	
    stream_t|ype = {Type}(Names[] << "{Stream}"), >> c.All_classes[]
    blob_type = {Type}(Kind = 'CLASS', Names[] << "{Blob}"), >> c.All_classes[]
    
    blob_param_f|unction = {Fun}(Line_start = -1, Names[] << "Blob_param",
    	A_Name_1 = "Blob_param",
        Parse = db.Parse, Param[] << {Var}(Line = -1, Type_ = stream_t),
        Result = {Var}(Line = -1, Type_ = blob_type)),
        >> db.Method[]
        
    stream_ph|oto = {Var}(Line = -1, Type_ = stream_t,
        Names[] << "stream_photo"),
        >> c.Current_fun.Local_var[]
        
    ASSERT TRUE
    ----------------------------------------- 'UPDATE 4 (blob)'
	<< "UPDATE4"#NL
	
	p.Test_line("Test a, "
    "SET Photo = {db1.Blob_param(stream_photo)}, WHERE ID = 100")
    
    //DEBUG: "**********************************************" >> STR which_error ;
    //e = Recognize_SQL("UPDATE", p)
    //DEBUG: << e.SQL_concat >> ;
	
    //ASSERT e.SQL_concat.Replace_all(#CR, #SP).Replace_all(#LF, #SP).Replace_all(
    //        #SP#SP, #SP) == "UPDATE TEST "
    //        "SET Photo = :blob "
    //        "WHERE ID = 100", e.SQL_concat.encode_all_non_printable
            
	ASSERT c.Errors_count == 0
    c.Errors_count = 0
    ----------------------------------------- 'UPDATE 5 (2 blobs)'
	<< "UPDATE5"#NL
	
	p.Test_line("Test a, "
    "SET Photo = {db1.Blob_param(stream_photo)},"
    "    Photo = {db1.Blob_param(stream_photo)},"
    "    Photo = {1},"
    "WHERE ID = 100")
    
	e = Recognize_SQL("UPDATE", p)
	//>> STR which_error
	ASSERT c.Errors_count > 0
    c.Errors_count = 0
    ----------------------------------------- 'UPDATE 0 (no joins or WHERE)'
	<< "UPDATE0"#NL
	t.Counter[] << r.Field[0]
	p.Test_line("Test SET [ID] = 1")
	e = Recognize_SQL("UPDATE", p)
    ASSERT e.SQL_concat == "UPDATE TEST SET [ID] = 1", e.SQL_concat
	//>> STR which_error
	ASSERT c.Errors_count == 1
    c.Errors_count = 0
    ----------------------------------- 'SELECT' //-----------------------------
    ------------------------------------------- 'SELECT 1'
    << "SELECT1"#NL
    
    p.Test_line("TOP 100, DISTINCT, FROM Test a,"
    "JOIN Test b ON (a.Dat = b.Dat),"
    "(COUNT(DISTINCT a.*) AS CNT_ID, MAX(a.Num) AS MAX_NUM, "
    "MIN(a.Num) AS MIN_NUM, "
    "a.ID, a.Num, b.ID AS C_ID, b.Num AS B_NUM,"
    "SQL("#QU"something unknown"#QU") AS SQL,"
    "SQL("#QU"something unknown2"#QU") AS SQL2, AVG(a.ID) AS AVG_ID)")
    
    e = Recognize_SQL("SELECT", p)
    //>> STR which_error
    
    csql = e.SQL_compare("SELECT TOP 100 DISTINCT "
            "COUNT( DISTINCT a.*) AS CNT_ID, MAX(a.Num) AS MAX_NUM, "
            "MIN(a.Num) AS MIN_NUM, "
            "a.ID, a.Num, b.ID AS C_ID, b.Num AS B_NUM, "
            "something unknown AS SQL, "
            "something unknown2 AS SQL2, AVG(a.ID) AS AVG_ID "
            "FROM TEST a "
            "JOIN TEST b ON (a.Dat = b.Dat)")
            
    ASSERT csql == "", csql
    ASSERT c.Errors_count == 0
    ------------------------------------------- 'SELECT 2 (*)'
    << "SELECT2"#NL
    
    p.Test_line("FROM Test, (*), "
    "GROUP BY (Num), ORDER BY (iNT DESC, Num)")
    
    e = Recognize_SQL("SELECT", p)
    //>> STR which_error
    ASSERT c.Errors_count > 0
    
    ASSERT e.SQL_concat == "SELECT * FROM TEST "
           "GROUP BY Num ORDER BY iNT DESC, Num", e.SQL_concat
           
    c.Errors_count = 0
    ------------------------------------------- 'SELECT 3 (errors)'
    << "SELECT3"#NL
    
    p.Test_line("TOP 100, DISTINCT, FROM Test a,"
    "JOIN Test b ON (a.Dat = b.Dat),"
    "(a.ID, a.Num AS X, b.ID, b.Num AS X, c.Erroneous_alias, "
    "SQL("#QU"something unknown"#QU") AS sql),"
    "WHERE {IA[]} = {1}, "
    "ORDER BY (X, Y, Z)")
    
    e = Recognize_SQL("SELECT", p)
    //>> STR which_error
    ASSERT c.Errors_count > 0
    c.Errors_count = 0
    ------------------------------------------- 'SELECT 4 (errors 2)'
    << "SELECT4"#NL
    
    p.Test_line("FROM Test a,"
    "JOIN Test b ON (a.Dat = b.Dat),"
    "(a.ID, a.Num AS X, b.ID, b.Num AS X, c.Erroneous_alias, "
    "SQL("#QU"something unknown"#QU") AS sql),"
    "WHERE {IA[]} = {1} AND a.Num IS NULL OR {1} IS NOT NULL, ")
    
    e = Recognize_SQL("SELECT", p)
    //>> STR which_error
    ASSERT c.Errors_count > 0
    c.Errors_count = 0
    ------------------------------------------- 'SELECT 5 (errors 3)'
    << "SELECT5"#NL
    
    p.Test_line("FROM Test a,"
    "JOIN Test b ON (a.Dat = b.Dat),"
    "(a.ID, a.Num AS X),"
    "WHERE {1} @ {1} NOTSIGN x ")
    
    e = Recognize_SQL("SELECT", p)
    //>> STR which_error
    ASSERT c.Errors_count > 0
    c.Errors_count = 0
    ------------------------------------------- 'SELECT 6 (no joined tables)'
    << "SELECT6"#NL
    //DEBUG: >> STR debug_in ;
    
    p.Test_line("FROM Test, (ID, Num), "
    "GROUP BY(X_error, ID), ")
    
    e = Recognize_SQL("SELECT", p)
    //>> STR which_error
    ASSERT c.Errors_count > 0
    
    ASSERT e.SQL_concat == "SELECT ID, Num FROM TEST "
           "GROUP BY X_error, ID", e.SQL_concat
           
    c.Errors_count = 0
    ------------------------------------------- 'SELECT 7 (GROUP no ORDER)'
    << "SELECT7"#NL
    
    p.Test_line("FROM Test, (*), "
    "GROUP BY (Num)")
    
    e = Recognize_SQL("SELECT", p)
    //>> STR which_error
    ASSERT c.Errors_count == 0
    ASSERT e.SQL_concat == "SELECT * FROM TEST GROUP BY Num", e.SQL_concat
    ----------------------------------- 'unknown' //----------------------------
    p.Test_line("")
    e = Recognize_SQL("UNKNOWN", p)
    ASSERT c.Errors_count > 0
    ------------------------------------ 'SQL_concat for not CONCAT'
    e = NONE
    ASSERT e.SQL_concat == ""
    ------------------------------------ 'Check_conversion_fun for NONE'
    c.Reset_err
    e = NONE
    CASE e.Fun_ != NONE ? << "none.Fun_ = " e.Fun_.Whole_name #NL ;
    << "------------------------------------------------------"#NL
    check_conversion_fun(e, NONE, p)
    //>> STR why_no_error
    ASSERT c.Errors_count > 0, c.Errors_count.Str
	.
	
	
FUN first_diff_char(STR A|_paramerter, STR B|_parameter) ==> STR :
    FOR i IN [0 TO A.Len-1] :
        CASE A.[i] != B.[i] ? 
             RESULT = i.Str "(" A.[i TO i+20] " ....... " B.[i TO i+20] ")" 
             ==> ;
    ; .
	
FUN encode_all_non_printable(STR S|ource_string) ==> STR, DEBUG :
	FOR i IN [0 TO S.Len-1] :
	    CASE S.[i] >= #SP && S.[i] <= #0x7f ?
	         RESULT = RESULT S.[i]
	    ELSE RESULT = RESULT "#" S.[i].Ord.S ;
	; .
	
	
{Parser} parser|_parameter
{join} joins|_info_parameter[]
{Table} tables_|parameter_[]
{Type} idb_|imported_
	
FUNCTION recognize_where(
    {Parser} Parse|_source,
    {join} Join|_information[],
    {Table} Tables|_join_list[]) ==> {Expr},
    NEW
    :
	----------------------------------------------------------------- ''
	{Table} mtab|le_main = Tables[0]
	mtab.parser = Parse
	mtab.joins[].Clear
	FOR i IN [0 TO Join[].Count-1] : mtab.joins[] << Join[i].Clone ;
	mtab.tables_[].Clear
	FOR t IN Tables[] : mtab.tables_[] << t ;
	mtab.idb_ = comp_of(Parse).Current_class.Find_type("{DB}")
	CASE mtab.idb_==NONE ? Parse.Err("{DB} class was not found") ;
	RESULT = mtab.recognize_sql_condition .
	
METHOD recognize_sql_condition ==> {Expr}, NEW, RECURSIVE :
	{Fun} cf|unction_current = comp_of(parser).Current_fun
	RESULT = {Expr}(Operation = 'BINARY', Sign = 'CONCAT')
	//DEBUG: << "Recognize sql condition:" parser.Next20 #NL ;
	FOR i|dummy IN [parser.Pos TO parser.Last] :
	    STR comment|_text
	    {Expr} e_comm|ent_expr
	    ---------------- 'may be , -- comment', REUSED
		CASE parser.Symbol(",") ? 
		     CASE parser.End_of_line ? parser.Fin ;
		;     
	    CASE parser.Symbol("--") ?
	         comment = #NL"--" parser.Skip_to("'")
	         parser.Wait("'")
	         comment = comment "'" parser.Skip_to("'") "'"#NL
	         parser.Wait("'")
	         CASE parser.End_of_line ? parser.Fin ;
	         --------------------------- 'add comment as part of expression'
	         e_comm = {Expr}(Operation= 'CONST',
	         	Var_arg= {Var}(Is_const= TRUE, Str_c= comment, Line= 0,
	         		Type_= parser.Compiler.TYPE_STR)),
	         	>> cf.All_expr[]
	         	
	         RESULT.Items_[] << e_comm
	         CONTINUE i ;
	    -----------------
		{Expr} condition = recognize_condition_simple, >> cf.All_expr[]
		RESULT.Items_[] << condition
		LIKE ............. 'may be , -- comment', BUT (CONTINUE i ==> )
		CASE ?
		[parser.Symbol("AND") || parser.Symbol("&&")] ?
		    STR op|eration = #NL" AND "
		[parser.Symbol("OR") || parser.Symbol("||")] ? op = #NL" OR "
		ELSE ==> ;
		{Expr} sop|eration_str = New_str(parser, op), >> cf.All_expr[]
		RESULT.Items_[] << sop ; .
		
METHOD recognize_condition_simple ==> {Expr}, NEW :
	----------------------------------------------------------------- ''
	{Fun} cf|unction_current = comp_of(parser).Current_fun
	//DEBUG: << "Recognize condition simple:" parser.Next20 #NL ;
	-------------------------------------- 'SQL(...)', REUSED
	CASE parser.Symbol("SQL") ?
		 {Type} idb|_imported = comp_of(parser).Current_class.Find_type("{DB}")
		 CASE idb==NONE ? parser.Err("{DB} class was not found") ;
		 parser.Wait("(")
         {Expr} opar|entheses_open = New_str(parser, "("), >> cf.All_expr[]
         {Expr} cpar|entheses_close = New_str(parser, ")"), >> cf.All_expr[]
		 {Expr} in_par|enthes = Recognize(parser), >> cf.All_expr[]
		 CASE in_par.Is_array ? 
		      parser.Err("Expression in SQL(...) must be a scalar string"
		      ""._InSQLMustScalar) ;
		 CASE in_par.Type_expr.Kind != 'STR' ?
		      {Expr} not_conv|erted = in_par
		      
		      in_par = value_to_sql_string(in_par.Type_expr, not_conv, 
		      	idb, Parse= parser, Nulls= FALSE), >> cf.All_expr[]
		 ;
         
         RESULT = {Expr}(
         			Operation = 'BINARY',
         			Sign = 'CONCAT',
                  	Items_[] << opar << in_par << cpar)
                  	//Items_[] << in_par)
                  
		 parser.Wait(")") ==> ;
    -------------------------------------- '(...)'
    STR open|_bracket = "("
    CASE parser.Symbol("(") ?
         //{Expr} opar|entheses_open
         //{Expr} cpar|entheses_close
         --------------------------------- 'expression in parentheses', REUSED
		 opar = New_str(parser, open), >> cf.All_expr[]
		 in_par = recognize_sql_condition, >> cf.All_expr[]
		 cpar = New_str(parser, ")"), >> cf.All_expr[]
         parser.Wait(")")
         --------------------------------- 'end of parentheses'
         RESULT = {Expr}(Operation = 'BINARY', Sign = 'CONCAT',
                  Items_[] << opar << in_par << cpar) ==>
    ;
	-------------------------------------- 'NOT(...)'
    BOOL not|_prefix = parser.Symbol("NOT")
    BOOL next_exists = parser.Symbol("EXISTS")
	CASE not || next_exists ?
	     CASE next_exists ?
	          parser.Wait("(")
	          parser.Wait("SELECT")
	          open = if(not, "NOT") " EXISTS(SELECT "
	          -------------------------- 'inner select'
	          {Expr} obr|anch_open = New_str(parser, open), >> cf.All_expr[]
		 	  
		      {Expr} exists|_select = {Expr}(Operation = 'BINARY',
		      	Sign = 'CONCAT', Items_[] << obr), >> cf.All_expr[]
		      	
		      recognize_SELECT(parser, exists,
		      	Joins= joins[], Tables= tables_[])
		      	
		      parser.Wait(")")
		      {Expr} cbr|anch_close = New_str(parser, ")"), >> cf.All_expr[]
		      exists.Items_[] << cbr
		      RESULT = exists ==>
	     ;
    	 open = "NOT("
         parser.Wait("(")
         LIKE ......................... 'expression in parentheses'
         
         RESULT = {Expr}(Operation = 'BINARY', Sign = 'CONCAT',
                  Items_[] << opar << in_par << cpar) ==>
    ;
	-------------------------------------- 'operand 1'
	{Expr} op1|_operand = recognize_operand_sql, >> cf.All_expr[]
	{kind_type} kind1|_operand = last_operand_type
	RESULT = {Expr}(Operation = 'BINARY', Sign = 'CONCAT')
	-------------------------------------- 'operation'
	CASE ?
	[parser.Symbol("<=")] ? STR sign|_operation = "<="
	[parser.Symbol(">=")] ? sign = ">="
	[parser.Symbol("<>")] ? sign = "<>"
	[parser.Symbol("==")] ? sign = "="
	[parser.Symbol("=")] ? sign = "="
	[parser.Symbol("<")] ? sign = "<"
	[parser.Symbol(">")] ? sign = ">"
	[parser.Symbol("BETWEEN")] ? sign = "BETWEEN"
	[parser.Symbol("IN")] ? sign = "IN"
	[parser.Symbol("LIKE")] ? sign = "LIKE"
	[parser.Symbol("NOT")] ?
	    CASE ?
	    [parser.Symbol("IN")] ? sign = "NOT IN"
	    [parser.Symbol("LIKE")] ? sign = "NOT LIKE"
	    ELSE parser.Wait("IN or LIKE") ;
    [parser.Symbol("IS")] ? 
        CASE parser.Symbol("NOT") ? sign = "IS NOT NULL"
        ELSE sign = "IS NULL" ;
        parser.Wait("NULL")
        
	ELSE parser.Err("Waiting for SQL operation sign"._Ewait8 
         ": " parser.Next20)
	;
	{Expr} ssign|_as_string = New_str(parser, " " sign " "), >> cf.All_expr[]
	RESULT.Items_[] << op1 << ssign
    CASE sign IN ["IS NULL", "IS NOT NULL"] ? ==> ;
	------------------------------------- 'operand1 IN/NOT IN (...)'
	CASE sign IN ["IN", "NOT IN"] ?
		 CASE kind1 !IN ['INT', 'REAL', 'STR'] ?
			  parser.Err("Not applicable operand type for operation"._Enot 
              " " sign ", " ("should be INT, REAL or STR but"._Eshould ) 
              ": " kind1.Name)
		 ;
		 CASE parser.Next_symbol("SQL") ? NONE
		 ELSE parser.Wait("(") 
			  obr = New_str(parser, "("), >> cf.All_expr[]
			  cbr = New_str(parser, ")"), >> cf.All_expr[]
			  BOOL want_p|arenthesis = TRUE
		 ;
		 CASE parser.Symbol("SELECT") ?
		      FOR jj IN [0 TO joins[].Count-1] : 
		          {join} save_joins[] << joins[jj].Clone ;
		      FOR t IN tables_[] : {Table} save_tables[] << t ;
		      -------------------------- 'inner select'
		 	  obr.Var_arg.Str_c = "(SELECT "
		 	  
		      {Expr} inner|_select = {Expr}(Operation = 'BINARY',
		      	Sign = 'CONCAT', Items_[] << obr), OWNED BY cf
		      	
		      recognize_SELECT(parser, inner,
		      	Joins= joins[], Tables= tables_[])
		      	
		      parser.Wait(")")
		      inner.Items_[] << cbr
		      RESULT.Items_[] << inner
		      ------------------------- 'restore joins / tables_'
		      joins[].Clear
		      tables_[].Clear
		      FOR jj IN [0 TO save_joins[].Count-1] : 
		          joins[] << save_joins[jj].Clone ;
		      FOR t IN save_tables[] : tables_[] << t ; 
		      ==> ;
		 CASE want_p ? RESULT.Items_[] << obr ;
		 {Expr} comma|_string = NONE
		 FOR i|dummy IN [parser.Pos TO parser.Last] :
			 INT pos|ition_before = parser.Pos
			 {Expr} op2|_operand = recognize_operand_sql, >> cf.All_expr[]
			 CASE last_operand_type != kind1 && !(
                  last_operand_type IN ['INT', 'REAL'] 
                  && kind1 IN ['INT', 'REAL'])
                  ?
				  PUSH parser.Pos = pos :
					   parser.Err("Operand type is not compatible for "
					   "IN/NOT IN operation"._Eoperand ": " parser.Next20)
				  ;
			 ;
			 CASE comma != NONE ? RESULT.Items_[] << comma
			 ELSE comma = New_str(parser, ", "), >> cf.All_expr[] ;
			 RESULT.Items_[] << op2
			 CASE !want_p || !parser.Symbol(",") ? BREAK i ;
		 ;
		 CASE want_p ?
		      parser.Wait(")")
		      RESULT.Items_[] << cbr ;
		 ==> ;
	------------------------------------ 'operand2'
	op2 = recognize_operand_sql, >> cf.All_expr[]
	check_operands_compatible(kind1, last_operand_type, sign) 
	//;
	------------------------------------ 'operand1 BETWEEN operand2 AND opernd3'
	CASE sign == "BETWEEN" ?
	     CASE parser.Symbol(",") ? NONE ;
		 parser.Wait("AND")
		 {Expr} op3|_operand = recognize_operand_sql, >> cf.All_expr[]
		 check_operands_compatible(kind1, last_operand_type, sign)
		 {Expr} and|_symbol = New_str(parser, " AND "), >> cf.All_expr[]
		 RESULT.Items_[] << op2 << and << op3 ==> ;
	------------------------------------ 'any other binary operation'
	RESULT.Items_[] << op2 .
			 
{kind_type} last_operand_type
BOOL joined_table_used
			 
METHOD recognize_operand_sql ==> {Expr}, NEW :
	{Fun} cf|unction_current = comp_of(parser).Current_fun
	LIKE recognize_condition_simple ............ 'SQL(...)'
	-------------------------------------------- ''
	parser.Skip_sp
	INT pos|ition_before = parser.Pos
	{Expr} e|xpression_al4
	//DEBUG: << "Recognize operand sql:" parser.Next20 #NL ;
    --------------------------------- 'immediate string constant in apostrophes'
    CASE parser.Symbol("'") ?
         STR imm|ediate_string = "'" parser.Skip_to("'") "'"
         parser.Wait("'")
         last_operand_type = 'STR'
         RESULT = New_str(parser, imm), >> cf.All_expr[] ==> ;
	CASE parser.Symbol("{") ?
         BOOL ok|_type_allowed
		 --------------------------------- 'AL4 expression as SQL value', REUSED
		 //e = Recognize(parser), >> cf.All_expr[]
		 e = Obtain_operand(parser), >> cf.All_expr[]
		 parser.Skip_sp
		 CASE parser.Next_char IN ".[" ? 
		      e = e.Chain_operations(parser, FALSE), >> cf.All_expr[] ;
         arrays_are_not_allowed(e, parser)
		 CASE ?
		 [e.Type_expr.Kind IN ['BOOL', 'INT', 'REAL', 'STR', 'ENUM']] ?
              ok = TRUE
		 [e.Type_expr.Kind == 'STRUCTURE'] ?
		     CASE e.Type_expr.Whole_name=="{id|entity}" ? ok = TRUE ;
		 ;     
         CASE !ok && e.Type_expr.Kind == 'STRUCTURE' ?
              ok = "{date_time}" IN e.Type_expr.Names[] ;
         CASE !ok ?
			  PUSH parser.Pos = pos :
				    parser.Err("Type of expression"._Etype2 " (" 
					"" e.Type_expr.Kind.Name ", " e.Type_expr.Whole_name
					") is not allowed: " parser.Next20) ;
		 ;
		 last_operand_type = e.Type_expr.Kind
         //DEBUG: << "last operand type: " last_operand_type.Name #NL ;
		 CASE ?
		 [last_operand_type == 'ENUM' && e.Type_expr.Names[0]=="{byte}"] ?
		 	last_operand_type = 'INT'
		 
		 [last_operand_type == 'STRUCTURE' 
		  && e.Type_expr.Whole_name == "{id|entity}"] ? last_operand_type = 'INT' 
		 ;
		 
		 RESULT = value_to_sql_string(e.Type_expr, e, idb_, Parse= parser,
		 	Nulls= FALSE),
		 	>> cf.All_expr[]
		 -------------------------------------- 'end of AL4 expression'
		 parser.Wait("}") ==>
	;
	BOOL next_is_id|entifier =  parser.Next_is_letter
		 next_is_id ||= parser.Next_symbol("[") 
	CASE !next_is_id ?
		 LIKE ........................ 'AL4 expression as SQL value'
		 ==> ;
    --------------------------------------- 'field of table'
    CASE parser.Symbol("[") ? BOOL wait_br|acket_close = TRUE ;
    STR fname|_field = parser.Ident
    CASE wait_br ? parser.Wait("]") ;
    //CASE joins[].Count == 1 ? 
    CASE !parser.Next_symbol(".") ? 
		 {Table} t|able_referenced = tables_[0]
		 PUSH parser.Compiler_.{Compiler}.Current_class.Force_any_case = TRUE :
		 	 {Var} f|ield_referenced = t.Base_.Find_field(fname) ;
		 //DEBUG:
		 //      CASE f == NONE ?
		 //           << "field was not found: " fname #NL >> STR dummy|_temp
		 //           f = t.Base_.Find_field(fname)
		 //      ELSE << "field " fname " found OK" #NL
		 //      ;
		 //;
		 last_operand_type = f.Type_.Kind
		 CASE last_operand_type == 'STRUCTURE' 
		      && f.Type_.Whole_name == "{id|entity}"
		      ?
		      last_operand_type = 'INT' ;
         joined_table_used = TRUE
         
		 RESULT = New_str(parser, t.Actual_field_name(fname,
					parser)) ==>
    ;
	--------------------------------------- 'a.Field'
	STR al|ias_name = fname //parser.Ident
	CASE is_alias(al, joins[]) && parser.Symbol(".") ?
	     wait_br = FALSE
	     CASE parser.Symbol("[") ? wait_br = TRUE ;
		 fname = parser.Ident
		 CASE wait_br ? parser.Wait("]") ;
		 t = joined_table(al, joins[], tables_[])
         t.joined_table_used = al == joins[*].Alias_t
         PUSH parser.Compiler.Current_class.Force_any_case = TRUE :
         	  f = t.Base_.Find_field(fname) ;
		 last_operand_type = f.Type_.Kind
		 CASE last_operand_type == 'STRUCTURE' 
		      && f.Type_.Whole_name == "{id|entity}"
		      ?
		      last_operand_type = 'INT' ;
		 RESULT = New_str(parser, al "." t.Actual_field_name(fname, parser)) ==>
	;
	parser.Pos = pos
	LIKE .................... 'AL4 expression as SQL value' .
		
METHOD check_operands_compatible(
    {kind_type} Kind1|_operand,
    {kind_type} Kind2|_operand,
    STR Sign|_of_operation)
    :
	-------------------------------------- ''
	CASE ?
	[Kind1 == Kind2] ?	==>
	[Kind1 IN ['INT', 'REAL'] && Kind2 IN ['INT', 'REAL']] ?
						==> ;
						
	parser.Err("Operands are not compatible"._Eoperands 
        " (" Kind1.Name " " ("and"._Eand ) " " Kind2.Name 
		", " ("for operation"._Efor ) " " Sign ")") .
        
FUN arrays_are_not_allowed({Expr} Scalar|_not_array, {Parser} Parse|_report) :
    CASE Scalar.Is_array ?
         PUSH Parse.Pos = Scalar.At_pos :
              Parse.Err("Array is not allowed in SQL expression"._Earray 
              ": " Parse.Next20)
         ;
    ; .
    
FUN SQL_compare( {Expr} E|xpression, STR Pat|tern_compare_with) ==> STR:
    STR con|catenated_E = E.SQL_concat
    INT max|imum_length = con.Len
    CASE max < Pat.Len ? max = Pat.Len ;
    FOR i IN [0 TO max] :
        CASE con.[i] != Pat.[i] ?
             RESULT = con.[0 TO i-1]
             " >>>>>>>>>> " con.[i TO *] 
             " !!!!!===== " Pat.[i TO *] ==> ;
    ; .
    
TEST of_SQL_compare :
     {Parser} p|arser_temp = Test_parser
     {Expr} e|xpression_temp = New_str(p, "aaa")
     
     ASSERT e.SQL_compare("bbbb") == " >>>>>>>>>> aaa "
             "!!!!!===== bbbb", "[" e.SQL_compare("bbbb") "]"
     -------------------------------------------------- 'unknown in SQL_concat'
     e = {Expr}(Operation = 'BINARY', Sign = 'CONCAT', Items_[] << NONE)
     ASSERT "unknown:" IN e.SQL_concat, e.SQL_concat
     .
    
FUN SQL_concat|enate(
	{Expr} E|xpression ) ==> STR, RECURSIVE
	:
    CASE E.Operation == 'CONST' || E.Var_arg.Type_.Kind == 'STR' ? 
         RESULT = E.Var_arg.Str_c ==> ;
    CASE E.Operation != 'BINARY' || E.Sign != 'CONCAT' || E.Items_[
         ].Count == 0
         ?
         ==> ;
    FOR it IN E.Items_[] :
        CASE ?
        [it.Operation == 'BINARY' && it.Sign == 'CONCAT'] ?
             RESULT = RESULT it.SQL_concat
        [it.Operation == 'CONST' && it.Var_arg.Type_.Kind == 'STR'] ?
             RESULT = RESULT it.Var_arg.Str_c
        [it.Operation == 'FUN' && (
         "Bool_sql" IN it.Fun_.Names[] || "Bool_sql0" IN it.Fun_.Names[])] ?
             RESULT = RESULT bool_sql(it.Items_[0])
        [it.Operation == 'FUN' && (
         "Int_sql" IN it.Fun_.Names[] || "Int_sql0" IN it.Fun_.Names[])] ?
             RESULT = RESULT int_sql(it.Items_[0])
        [it.Operation == 'FUN' && (
         "Real_sql" IN it.Fun_.Names[] || "Real_sql0" IN it.Fun_.Names[])] ?
             RESULT = RESULT real_sql(it.Items_[0])
        [it.Operation == 'FUN' && (
         "Str_sql" IN it.Fun_.Names[] || "Str_sql0" IN it.Fun_.Names[])] ?
             RESULT = RESULT str_sql(it.Items_[0])
        [it.Operation == 'FUN' && (
         "Date_sql" IN it.Fun_.Names[] || "Date_sql" IN it.Fun_.Names[])]?
             RESULT = RESULT date_sql(it.Items_[0])
             
        ELSE RESULT = RESULT "unknown:" it.Operation.Name ";" it.Fun_.Names[0] 
             ";" it.Var_arg.Str_c " "
        ;
    ;
    FOR i IN [RESULT.Len-2 DOWNTO 0] :
        CASE ?
        [RESULT.[i TO i+1] IN ["  ", #LF" "]] ?
             RESULT = RESULT.[0 TO i] RESULT.[i+2 TO *]
        [RESULT.[i TO i+1] == #NL] ?
             RESULT = RESULT.[0 TO i-1] " " RESULT.[i+2 TO *] ;
    ; 
    RESULT = RESULT.Trim .
    
FUN bool_sql|_value( {Expr} V|alue_to_convert ) ==> STR :
    CASE V.Operation == 'CONST' && (V.Var_arg.Type_.Kind == 'BOOL' 
         || V.Var_arg.Type_.Kind == 'ENUM' && V.Var_arg.Type_.Names[0]=="{bool}")
         ? RESULT = if_else(V.Var_arg.Int_c != 0, "TRUE", "FALSE")
    ELSE 
    	 RESULT = "Bool_sql(?)"
         //DEBUG: "V.Operation=" V.Operation.Name
         //       " V.Var_arg.Type_.Kind=" V.Var_arg.Type_.Kind.Name
         //       " V.Var_arg.Type_.Names[0]=" V.Var_arg.Type_.Names[0]
         //       " ==> RESULT=Bool_sql(?)" #NL >> ;
    ; .
    
FUN int_sql|_value( {Expr} V|alue_to_convert ) ==> STR :
    CASE V.Operation == 'CONST' && V.Var_arg.Type_.Kind == 'INT' ?
         RESULT = V.Var_arg.Int_c.Str
    ELSE RESULT = "Int_sql(?)" ; .
    
FUN real_sql|_value( {Expr} V|alue_to_convert ) ==> STR :
    //CASE V.Operation == 'CONST' && V.Var_arg.Type_.Kind == 'REAL' ?
    //     RESULT = V.Var_arg.Real_c.Str
    //ELSE 
    RESULT = "Real_sql(?" V.Operation.Name ";" V.Var_arg.Type_.Kind.Name 
         ")" .
    
FUN str_sql|_value( {Expr} V|alue_to_convert ) ==> STR :
    CASE V.Operation == 'CONST' && V.Var_arg.Type_.Kind == 'STR' ?
         RESULT = "'" V.Var_arg.Str_c "'"
    ELSE RESULT = "Str_sql(?)" ; .
    
FUN date_sql|_value( {Expr} V|alue_to_convert ) ==> STR :
    CASE ?
    [V.Operation == 'FUN' && "Date" IN V.Fun_.Names[] && V.Items_[].Count == 3]?
         RESULT = "'" V.Items_[0].Var_arg.Int_c.Str 
         "/" V.Items_[1].Var_arg.Int_c.Str 
         "/" V.Items_[2].Var_arg.Int_c.Str "'"
    [V.Items_[].Count > 0] ?
         RESULT = "Date_sql(?" V.Operation.Name ";" V.Fun_.Whole_name
         "(" V.Items_[0].Operation.Name 
         ";" V.Items_[0].Var_arg.Type_.Kind.Name 
         ";" V.Items_[0].Var_arg.Int_c.Str "))"
    ELSE RESULT = "Date_sql(?" V.Operation.Name ";" V.Fun_.Whole_name
         "('UNKNOWN'" 
         ";'BOOL'" 
         ";0))"
    ; .
    
------------ 'read from DB'
------------------------------ ' in form "rec << Db" where rec is STRUCTURE var'

METHOD Translate_read_from_db(
{Expr} Left|_record, {Expr} Right|_database, {Statement} Block|_to_add_to[])
    :
    BOOL ok|_left_operand = Left.Type_expr.Kind IN ['STRUCTURE', 'CLASS']

    CASE !ok ?
         Base_.In_class_.Parse.Err(
         "Left operand must be a scalar structure or object with fields "
         "present in the correspondent STRUCTURE type"._LeftMustFields 
         " " Base_.Whole_name) ==> ;
    CASE Left.Is_array ?
         CASE Left.Type_expr.Kind == 'STRUCTURE' ?
	          Base_.In_class_.Parse.Err("It is not allowed to use << to add "
	          "record from a database to an array of structures. Use an "
	          "intermediate scalar structure, please"._EAddFromDBToArray) ;
         Base_.In_class_.Parse.Compiler.Total_temp_var += 1
         STR tvn|_temp_var_name = "temp_var_"
         tvn = tvn Base_.In_class_.Parse.Compiler.Total_temp_var.Str
         {Fun} cfun|ction_current = Base_.In_class_.Parse.Compiler.Current_fun
         {Var} already|_created = cfun.Find_local_var(tvn, TRUE)
         CASE already == NONE ?
              already = {Var}(Names[] << tvn, Type_= Left.Type_expr,
                Line= -1), >> cfun.Local_var[]
         ;
         already.Count_ref += 1
         
         {Expr} e_tvn|_expression = {Expr}(Operation= 'VAR', Var_arg= already),
            >> cfun.All_expr[]
         	
         CASE already.Type_.Kind == 'STRUCTURE' ?
              {Expr} ee_tvn|_variable = e_tvn
              
              e_tvn = {Expr}(Operation = 'SYSTEM_FUN',
                Fun_= Base_.In_class_.Parse.Compiler.SYSFUN_Clone,
                Items_[] << ee_tvn, Type_obj_= ee_tvn.Type_expr),
                >> cfun.All_expr[]
         ;
         	
         ------------ 'adding to array'
         {Assign} a|dding_operation = {Assign}(Work= 'APPEND',
            Source= e_tvn, Dest= Left,
            Function_= cfun, Level= 0, Line= 0, Pos= 0), 
            >> Block[]
         ------------
         ==>
    ;
    CASE "{DB}" !IN Right.Type_expr.Top_base.Names[] ?
         Base_.In_class_.Parse.Err(
         "Right operand must be a {DB} object"._ERightNotDB) ==> ;
    n|ew_structure_stmt_temp = {Assign}(Work = 'ASSIGN', 
    		Level= 0, Line= 0, Pos= 0,
    		Function_= Base_.In_class_.Parse.Compiler.Current_fun,
        	Dest = Left,
        	Source = {Expr}(Operation= 'NEW_OBJ', Type_obj_= Left.Type_expr)),
        	>> Block[]
        	
    FOR f IN Left.Type_expr.Field[] :
    	CASE f.Is_array ? 
    	     Base_.In_class_.Parse.Err("Operation structure << DB is not "
    	     "applicable still the destination structure has arrays"._EArrs)
    	;
        STR f_name|_field_in_table = find_name_in_table(f.Names[], f.Type_)
        CASE f_name == "" ?
             Base_.In_class_.Parse.Err("Field is not present in table, "
             "should be loaded manually"._WFieldNotPresent
             ": " f.Names[0])
             
             CONTINUE f ;
    	CASE f.Type_.Kind ? 
    	['BOOL']: STR fn|ame_function_calling = "CBool"
    	['INT']: fn = "CInt"
    	['ENUM']: fn = "CInt"
    	['REAL']: fn = "CReal"
    	['STR']: fn = "CStr"
    	['STRUCTURE']:
    	    CASE ?
    	    ["{date_time}" !IN f.Type_.Names[]
    	     && f.Type_.Whole_name != "{id|entity}"] ?
    	         Base_.In_class_.Parse.Err("Field must be a {date_time} "
    	         "or {id|entity} structure"._EMustBeDateTimeStruc 
    	         ": " f.Whole_name)
    	    [f.Type_.Whole_name == "{id|entity}"] ?
    	         fn = "CId"
    	    ELSE fn = "CDate" ;
    	['CLASS']: NONE ;
    	----------------------- '1st parameter: name of field'
    	{Expr} ref_f|ield_name = {Expr}(Operation= 'CONST',
    		Var_arg= {Var}(Str_c= f_name, Line= 0,
    		Type_= Base_.In_class_.Parse.Compiler.TYPE_STR)),
    		OWNED BY Left
    	----------------------- 'stored parameter of type INT'
    	Base_.In_class_.Parse.Compiler.Current_class.Count_stored += 1
    	STR name|stored = "Stored_" 
    	name =name Base_.In_class_.Parse.Compiler.Current_class.Count_stored.Str
    	
    	{Var} stored_p|arameter = {Var}(Names[] << name, Line= 0,
    		  Is_stored_param= TRUE,
    		  Type_= Base_.In_class_.Parse.Compiler.TYPE_INT,
    		  Initial_= Base_.In_class_.Parse.Compiler.EM1),
    		  >> Base_.In_class_.Parse.Compiler.Current_class.Field[]
    		  
    	stored_p.Set_final_name(name)
    	
    	{Expr} ref_this = {Expr}(Operation= 'VAR',
    		Var_arg= Base_.In_class_.Parse.Compiler.THIS_var,
    		Type_obj_= Base_.In_class_.Parse.Compiler.Current_class),
    		OWNED BY Left
    	
    	{Expr} ref_stored_p|arameter = {Expr}(Operation= 'FIELD',
    		Var_arg= stored_p, Arg1_= ref_this, Pass_by_ref= TRUE),
    		OWNED BY Left
    	---------------------- 'call function Cnnn to extract field from SQL'
    	{Expr} source|_from_sql = {Expr}(Operation= 'FUN',
        	  Fun_= Right.Type_expr.Find_fun(fn, TRUE), This_= Right,
        	  Items_[] << ref_f << ref_stored_p ),
        	  OWNED BY Left

    	------------------------ 'finally, assign value to a field statement'
        a = {Assign}(Work = 'ASSIGN', Level= 0, 
        	Line= 0, Pos= 0, 
        	Dest = {Expr}(Operation= 'FIELD', Var_arg= f, Arg1_= Left),
        	Function_ = Base_.In_class_.Parse.Compiler.Current_fun,
        	Source = source), 
        	>> Block[] ; .

METHOD find_name_in_table(
STR Names|_in_destination_struct[],
{Type} Type_f|ield_to_check_with) ==> STR
    :
    FOR f IN Base_.Field[] :
        FOR n IN Names[] :
            FOR fn IN f.Names[] :
                CASE n.Upper != fn.Upper ? CONTINUE fn ;
                DONE     
	                 CASE RESULT != "" ? 
	                      Base_.In_class_.Parse.Err("Ambiguous reference to a "
	                      "field"._EAmbigName ": " Names[0]) ;
	                 RESULT = f.Names[0]
	                 CASE f.Type_.Kind != Type_f.Kind
	                	  && !(f.Type_.Kind == 'INT' && Type_f.Kind == 'ENUM')    
	                	  && !(Type_f.Kind == 'INT' && f.Type_.Kind == 'ENUM')    
	                	  ?
	                      Base_.In_class_.Parse.Err("Destination structure "
	                      "field type is not the same as the source structure "
	                      "field type"._ENotSameType ": " RESULT) ;
	                 BREAK fn, BREAK n ;
        ;
    ; 
    FOR r IN Renamed[] :
        CASE RESULT IN r.Names[] ? 
             RESULT = Actual[r.Index] ==> ;
    ; .

    
END

*/
