myldbsqlprocessor.pas

一个本地database引擎,支持中文T_Sql查询,兼容DELPHI标准数据库控件

 try
  // create group by AO?
  bGroupBy := (GroupByFields.ItemCount > 0);
  // check for aggregates in select list
  if (not bGroupBy) then
   if (not AllFields) then
    begin
     for i := 0 to SelectListCount-1 do
      if (SelectList[i].ValueExpr <> nil) then
       if (TMYLDBExpression(SelectList[i].ValueExpr).IsAggregated) then
        begin
         bGroupBy := True;
         break;
        end;
    end;

  // if expressions, then AOTable -> AOTableExpr?
  if (bGroupBy) or (Result is TMYLDBAOTable) then
   begin
    bAnyExpressions := False;
    for i := 0 to SelectListCount-1 do
     if (SelectList[i].ValueExpr <> nil) then
      // 5.01 fix to support "select f1,f1 from table1"
      if (not TMYLDBExpression(SelectList[i].ValueExpr).IsField) or SelectList[i].IsDuplicatedField then
        if (not TMYLDBExpression(SelectList[i].ValueExpr).IsAggregated) then
         begin
          bAnyExpressions := True;
          break;
         end;
    // create table wrapper with expression-fields?
    if (bAnyExpressions) then
      begin
        if (bGroupBy) then
          begin
            bAnyExpressions := False;
            // check if group by expression, not only by fields
            for i := 0 to SelectListCount-1 do
             if (SelectList[i].ValueExpr <> nil) then
              if (not TMYLDBExpression(SelectList[i].ValueExpr).IsField) then
                if (not TMYLDBExpression(SelectList[i].ValueExpr).IsAggregated) then
                  begin
                    for j := 0 to GroupByFields.ItemCount-1 do
                      if (AnsiUpperCase(GroupByFields.Items[j].FieldName) =
                          AnsiUpperCase(SelectList[i].Pseudonym)) then
                        begin
                          bAnyExpressions := True;
                          break;
                        end;
                  end;
            if (bAnyExpressions) then
              begin
                Result := TMYLDBAOTableExpr.Create(Result);
                Result.SetResultFields(SelectList,False);
              end;
          end
        else
          Result := TMYLDBAOTableExpr.Create(Result);
      end;
   end;

  // apply parts of WHERE clause if possible
  if (SearchCondition <> nil) then
     SearchCondition.ApplyFilterParts(Result, ParentQueryAO, ParentCursor);

  // create GroupByAO, apply having condition
  if (bGroupBy) then
   begin
    try
     Result := TMYLDBAOGroupBy.Create(Result, GroupByFields, SelectList);
     GroupByAO := Result;
    except
     // to avoid duplicated destroying
     Result := nil;
     for i := 0 to SelectListCount-1 do
      if (SelectList[i].ValueExpr <> nil) then
       begin
        SelectList[i].ValueExpr.Free;
        SelectList[i].ValueExpr := nil;
       end;
     raise;
    end;
   end;

  // prepare for setting projection
  if (Result.FHasSetResultFields) then
    Result := TMYLDBAOTableExpr.Create(Result);

  bAddTempTableAO := (not RequestLive) and (Result is TMYLDBAOTable) and (TopRowCount < 0);
  // sets projection
  Result.SetResultFields(SelectList,Distinct and (not bAddTempTableAO));

  if (bGroupBy) then
    begin
      // apply having
      if (HavingCondition <> nil) then
        begin
          // apply having with pseudonames
          HavingCondition.ApplyFilterParts(Result, ParentQueryAO, ParentCursor, false, true);
          // apply having w/o pseudonames
          HavingCondition.ApplyFilterParts(GroupByAO, ParentQueryAO, ParentCursor, true, true);
          if (not AllowNotAppliedConditions) then
            if (not HavingCondition.IsEmpty) then
               raise EMYLDBException.Create(20244, ErorrANotApplicableHavingCondition);
        end;
    end;

  // sort - order by
  ApplyOrderBy(Result);

  // apply pseudonames parts of WHERE clause if possible
  if (SearchCondition <> nil) then
   begin
     SearchCondition.ApplyFilterParts(Result, ParentQueryAO, ParentCursor);
     if (not AllowNotAppliedConditions) then
       if (not SearchCondition.IsEmpty) then
         raise EMYLDBException.Create(30144, ErorrGNotApplicableCondition);
   end;

  // TOP n?
  if (TopRowCount > -1) then
   begin
    Result := TMYLDBAOSQLTopRowCount.Create(Result);
    Result.SetTopRowCount(FirstRowNo, TopRowCount);
   end;

  // if not request live => always use temp table
  if (bAddTempTableAO) then
    begin
      Result := TMYLDBAOTableExpr.Create(Result);
      Result.SetResultFields(SelectList,Distinct and bAddTempTableAO);
    end;

 except
  if (Assigned(Result)) then
   Result.Free;
  raise;
 end;
end;//BuildAOTree


//------------------------------------------------------------------------------
// SELECT
//------------------------------------------------------------------------------
function TMYLDBSQLSelect.ParseSelectToken: Boolean;
begin
  Result := IsReservedWord(Token, rwSELECT);
  if (not Result) then
   // unsupported SQL or unexpected token
   raise EMYLDBException.Create(30156, ErrorGSQLCommandExpected,
         [Token.Text, Token.LineNum, Token.ColumnNum]);
  GetNextToken;
end;//ParseSelectToken


//------------------------------------------------------------------------------
// DISTINCT | ALL ?
//------------------------------------------------------------------------------
function TMYLDBSQLSelect.ParseSetQuantifier: Boolean;
begin
 if (IsReservedWord(Token)) then
  begin
    if (IsReservedWord(Token, rwALL)) then
     begin
      Distinct := False;
      Result := True;
     end
    else
    if (IsReservedWord(Token, rwDISTINCT)) then
     begin
      Distinct := True;
      Result := True;
     end
    else // unrecognized reserved word - give up to handle
     begin
      Distinct := False; // default value
      Result := False; // handle this token by another handler
     end;
  end
 else // not reserved-word token give up handling
  begin
    Distinct := False; // default value
    Result := False; // handle this word by another handler
  end;

 // get next token
 if (Result) then
  GetNextToken;
end;// ParseSetQuantifier


//------------------------------------------------------------------------------
// TOP n ?
//------------------------------------------------------------------------------
function TMYLDBSQLSelect.ParseTopOperator: Boolean;
begin
  if (IsReservedWord(Token, rwTOP)) then
   begin
    // suppose 'TOP n'
    GetNextToken;
    // integer?
    if (not (Token.TokenType in [tktInt,tktParameter]) ) then
     raise EMYLDBException.Create(30158, ErrorGIntegerExpected,
                             [Token.Text, Token.LineNum, Token.ColumnNum]);
    if (Token.TokenType = tktInt) then
      TopRowCount := StrToInt(Token.Text)
    else
      TopRowCount := Token.ParamValue.AsInteger;
    // get next token
    GetNextToken;
    // default value
    FirstRowNo := -1;
    // ','? => TOP n, first_row
    if (Token.Text = ',') then
     begin
       // get next token
       GetNextToken;
       // integer?
       if (not (Token.TokenType in [tktInt,tktParameter]) ) then
         raise EMYLDBException.Create(30159, ErrorGIntegerExpected,
                             [Token.Text, Token.LineNum, Token.ColumnNum]);
       if (Token.TokenType = tktInt) then
         FirstRowNo := StrToInt(Token.Text)
       else
         FirstRowNo := Token.ParamValue.AsInteger;
       // get next token
       GetNextToken;
     end;
    Result := True;
   end
  else // unrecognized reserved word - give up to handle
   begin
    TopRowCount := -1; // default value
    FirstRowNo := -1;
    Result := False; // handle this token by another handler
   end;
end;// ParseTopOperator


//------------------------------------------------------------------------------
// DuplicatedFieldExists
//------------------------------------------------------------------------------
function TMYLDBSQLSelect.DuplicatedFieldExists(const TableName, FieldName: String): Boolean;
var
  i: Integer;
begin
  Result := False;
  for i:=0 to SelectListCount-1 do
    if (not SelectList[i].IsExpression) then
      if (AnsiUpperCase(SelectList[i].TableName) = AnsiUpperCase(TableName)) and
          (AnsiUpperCase(SelectList[i].FieldName) = AnsiUpperCase(FieldName)) then
        begin
          Result := True;
          break;
        end;
end;// DuplicatedFieldExists


//------------------------------------------------------------------------------
// FieldExists
//------------------------------------------------------------------------------
function TMYLDBSQLSelect.FieldExists(const TableName, FieldName: String): Boolean;
var
  i: Integer;
begin
  Result := False;
  for i:=0 to SelectListCount-1 do
      if (AnsiUpperCase(SelectList[i].TableName) = AnsiUpperCase(TableName)) and
         ((AnsiUpperCase(SelectList[i].FieldName) = AnsiUpperCase(FieldName)) or
          (AnsiUpperCase(SelectList[i].Pseudonym) = AnsiUpperCase(FieldName)))then
        begin
          Result := True;
          break;
        end;
end;// FieldExists


//------------------------------------------------------------------------------
// GetFieldNameForDuplicatedField
//------------------------------------------------------------------------------
function TMYLDBSQLSelect.GetFieldNameForDuplicatedField(const TableName, FieldName: String): String;
var
  i: Integer;
begin
  i := 0;
  repeat
    Inc(i);
    Result := Format('%s_%d',[FieldName,i]);
  until (not FieldExists(TableName, Result));
end;// GetFieldNameForDuplicatedField


//------------------------------------------------------------------------------
// <derived column>  | <qualifier> <period> <asterisk>
//------------------------------------------------------------------------------
function TMYLDBSQLSelect.ParseSelectSubList: Boolean;
var
  FieldName, TableName, Pseudonym: string;
  AllFields: Boolean;
begin
 // handle field1 | table1.field1 | expr
  begin
   Result := True;

   // add new select list item
   inc(SelectListCount);
   SetLength(SelectList, SelectListCount);

   // parse field name | expr
   SelectList[SelectListCount-1].ValueExpr := TMYLDBExpression.Create;

   TMYLDBExpression(SelectList[SelectListCount-1].ValueExpr).InMemory :=
                                                     TMYLDBQuery(LQuery).InMemory;

   TMYLDBExpression(SelectList[SelectListCount-1].ValueExpr).ParseForValueExpression(
                                LQuery, LLex);

   if (TMYLDBExpression(SelectList[SelectListCount-1].ValueExpr).IsEmpty) then
    begin
     SelectList[SelectListCount-1].ValueExpr.Free;
     SelectList[SelectListCount-1].ValueExpr := nil;
     raise EMYLDBException.Create(30160, ErrorGExpressionExpected,
                             [Token.Text, Token.LineNum, Token.ColumnNum]);
    end;
   GetCurrentToken;

   // pseudonym is not specified yet
   Pseudonym := '';
   AllFields := False;
   // check pseudonym
   if (not AllFields) then
    // (Field1 AS F1) ?
          if (IsReservedWord(Token, rwAS)) then
           begin
             // get next token (F1?)
             GetNextToken([tktString, tktQuotedString, tktBracketedString],
                          30162, ErrorGFieldPseudonymExpected);
             // got pseudonym
             Pseudonym := Token.Text;
             // get next token
             GetNextToken;
            end
          else
          // (Field1 F1) ?
          if (Token.TokenType in
               [tktString, tktQuotedString, tktBracketedString]) then
            begin
             // got pseudonym
             Pseudonym := Token.Text;
             // look at next token
             GetNextToken;
            end;

   // expr or field?
   SelectList[SelectListCount-1].IsExpression :=
          not TMYLDBExpression(SelectList[SelectListCount-1].ValueExpr).IsField;

   // field?
   if (not SelectList[SelectListCount-1].IsExpression) then
    begin
     // get field name, table name
     TMYLDBExpression(SelectList[SelectListCount-1].ValueExpr).GetFieldInfo(TableName, FieldName);
     // treat duplicated field as expression
     if (FieldName = '*') or (not DuplicatedFieldExists(TableName, FieldName)) then
       begin
         // store TableName
         SelectList[SelectListCount-1].TableName := TableName;
         // store ValueExpr (FieldName)
         SelectList[SelectListCount-1].FieldName := FieldName;
         if (FieldName = '*') then
          AllFields := True;
         SelectList[SelectListCount-1].IsDuplicatedField := False;
       end
     else
       begin
         SelectList[SelectListCount-1].IsExpression :=True;
         SelectList[SelectListCount-1].IsDuplicatedField := True;
         if (Pseudonym = '') then
           Pseudonym := GetFieldNameForDuplicatedField(TableName, FieldName);
       end;