myldbrelationalalgebra.pas

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

    if (FFieldLinks[i].IsExpression) then
      if (FFieldLinks[i].Expr <> nil) then
        TMYLDBExpression(FFieldLinks[i].Expr).Free;

  FFieldLinks := nil;
  if (FResultDataset <> nil) then
    begin
      FResultDataset.Close;
      try
        if (TMYLDBTable(FResultDataset).Temporary) then
          TMYLDBTable(FResultDataset).DeleteTable;
      except
      end;
      FResultDataset.Free;
    end;
  if (FResultFieldsOrder <> nil) then
    FResultFieldsOrder.Free;
  if (FLeftAO <> nil) then
    FLeftAO.Free;
  if (FRightAO <> nil) then
    FRightAO.Free;
  if (FFilterExpr <> nil) then
    TMYLDBExpression(FFilterExpr).Free;
  FreeResultIndexLists;
  FValue.Free;
  inherited Destroy;
end; // Destroy


//------------------------------------------------------------------------------
// getting all result records
//------------------------------------------------------------------------------
procedure TMYLDBAO.Execute(IsRootAO: Boolean; ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor);
var
  p: Pointer;
  IsCreated: Boolean;
begin
  FIsRootAO := IsRootAO;

  // filter
  if (not FIsAOTable) then
   if (FFilterExpr <> nil) then
     TMYLDBExpression(FFilterExpr).AssignAO(Self, ParentQueryAO, ParentCursor);

  if (FLeftAO <> nil) then
    FLeftAO.Execute(False, ParentQueryAO, ParentCursor);
  if (FRightAO <> nil) then
    FRightAO.Execute(False, ParentQueryAO, ParentCursor);

  // optimize join

  if ((FLeftAO <> nil) and (FRightAO <> nil)) then
    if (Self is TMYLDBAOJoin) then
      if (TMYLDBAOJoin(Self).FJoinType in [ajtInner,ajtFullOuter]) then
      // bug-fix: 4.84 - speed up join with filtered table when LeftAO has much more records than RightAO
//        if (TMYLDBTable(FLeftAO.ResultDataset).Handle.SQLFilterExpression = nil) and
//           (TMYLDBTable(FRightAO.ResultDataset).Handle.SQLFilterExpression = nil) then
          if (FRightAO.ResultDataset.RecordCount < FLeftAO.ResultDataset.RecordCount) then
            begin
              p := FLeftAO;
              FLeftAO := FRightAO;
              FRightAO := p;
              p := TMYLDBAOJoin(Self).FFields1;
              TMYLDBAOJoin(Self).FFields1 := TMYLDBAOJoin(Self).FFields2;
              TMYLDBAOJoin(Self).FFields2 := p;
            end;

  if (FIsAOGroupBy) then
   if (not TMYLDBAOGroupBy(Self).FAllFields) then
     if (TMYLDBAOGroupBy(Self).FTempDataset = nil) and (FLeftAO <> nil) then
      begin
       TMYLDBAOGroupBy(Self).FTempDataset := TMYLDBTable.Create(nil);
       TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).DatabaseName :=
        TMYLDBTable(FLeftAO.FResultDataset).DatabaseName;
       TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).SessionName :=
        TMYLDBTable(FLeftAO.FResultDataset).SessionName;
       TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).TableName :=
        TMYLDBTable(FLeftAO.FResultDataset).TableName;
       TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).InMemory :=
        TMYLDBTable(FLeftAO.FResultDataset).InMemory;
       TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).Temporary :=
        TMYLDBTable(FLeftAO.FResultDataset).Temporary;
       TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).ReadOnly := True;

       try
        TMYLDBAOGroupBy(Self).FTempDataset.Open;
       except
        raise EMYLDBException.Create(10304,ErrorLCannotOpenTable,
          [TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).TableName,
           Word(TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).InMemory)]);
       end;

       if (TMYLDBTable(FLeftAO.FResultDataset).IsDistinctApplied) then
         TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).ApplyDistinct(TMYLDBTable(FLeftAO.FResultDataset))
       else
         TMYLDBTable(TMYLDBAOGroupBy(Self).FTempDataset).IndexName :=
           TMYLDBTable(FLeftAo.FResultDataset).IndexName;
      end; // FIsAOGroupBy

 if (FTopRowCount > 0) and (FResultIndexFieldsList <> nil) and (FLeftAO <> nil) then
  begin
   TMYLDBTable(FLeftAo.FResultDataset).IndexName :=
     TMYLDBTable(FLeftAo.FResultDataset).FindOrCreateIndex(
      FResultIndexFieldsList,FResultIndexAscDescFieldsList,FResultIndexCaseInsFieldsList,IsCreated)
  end;

 if ((FLeftAO <> nil) or (FRightAO <> nil)) then
  DoMaterialize;
 if (FResultIndexFieldsList <> nil) then
  InternalSetIndex(True);
end;// Execute


//------------------------------------------------------------------------------
// LockTables
//------------------------------------------------------------------------------
function TMYLDBAO.LockTables: Boolean;
begin
  if (FIsAOTable) then
    begin
      if (ResultDataset.Active) then
        Result := TMYLDBTable(ResultDataset).Handle.LockTable(ltS,
                      SelectTrialTableLockRetries, SelectTrialTableLockDelay)
      else
        Result := False;
      FIsLocked := Result;
    end
  else
    begin
      Result := True;
      if (FLeftAO <> nil) then
        Result := Result and FLeftAO.LockTables;
      if (FRightAO <> nil) then
       if (Result) then
         Result := Result and FRightAO.LockTables;
    end;
end;// LockTables


//------------------------------------------------------------------------------
// UnlockTables
//------------------------------------------------------------------------------
function TMYLDBAO.UnlockTables: Boolean;
begin
  if (FIsAOTable) then
    begin
      if (FIsLocked) then
        if (ResultDataset.Active) then
           begin
             Result := TMYLDBTable(ResultDataset).Handle.UnlockTable(ltS, True);
             FIsLocked := not Result;
           end
        else
          Result := False
      else
        Result := True;
    end
  else
    begin
      Result := True;
      if (FLeftAO <> nil) then
        Result := Result and FLeftAO.UnlockTables;
      if (FRightAO <> nil) then
        Result := Result and FRightAO.UnlockTables;
    end;
end;// UnlockTables


//------------------------------------------------------------------------------
// OpenTables
//------------------------------------------------------------------------------
function TMYLDBAO.OpenTables: Boolean;
begin
  if (FIsAOTable) then
    begin
      try
        ResultDataset.Open;
        Result := True;
      except
        ResultDataset.Close;
        Result := False;
      end;
    end
  else
    begin
      Result := True;
      if (FLeftAO <> nil) then
        Result := Result and FLeftAO.OpenTables;
      if (FRightAO <> nil) then
        Result := Result and FRightAO.OpenTables;
    end;
end;// OpenTables


//------------------------------------------------------------------------------
// CloseTables
//------------------------------------------------------------------------------
function TMYLDBAO.CloseTables: Boolean;
begin
  if (FIsAOTable) then
    begin
      try
        ResultDataset.Close;
        Result := True;
      except
        Result := False;
      end;
    end
  else
    begin
      Result := True;
      if (FLeftAO <> nil) then
        Result := Result and FLeftAO.CloseTables;
      if (FRightAO <> nil) then
        Result := Result and FRightAO.CloseTables;
    end;
end;// CloseTables


//------------------------------------------------------------------------------
// set filter
//------------------------------------------------------------------------------
procedure TMYLDBAO.SetFilter(FilterExpr: TObject);
begin
 if (FFilterExpr = nil) then
   FFilterExpr := FilterExpr
 else
   begin
     TMYLDBExpression(FFilterExpr).AddAndMoveNodesFromExpression(TMYLDBExpression(FilterExpr));
     FilterExpr.Free;
   end;
end; // SetFilter


//------------------------------------------------------------------------------
// for SELECT INTO optimization
//------------------------------------------------------------------------------
procedure TMYLDBAO.SetResultTable(InMemory, Immediate: Boolean; TableName: String; DatabaseName: String);
begin
  FResultInMemory := InMemory;
  FResultImmediate := Immediate;
  FResultTableName := TableName;
  FResultDatabaseName := DatabaseName;
end; // SetResultTable


//------------------------------------------------------------------------------
// sets Top row count
//------------------------------------------------------------------------------
procedure TMYLDBAO.SetTopRowCount(FirstRowNo, TopRowCount: integer);
begin
  if (not FDistinctApplied) and
     ((FResultIndexFieldsList = nil) or
      ((FResultIndexFieldsList <> nil) and (FResultIndexFieldsList.Count = 0))) then
    begin
      FTopRowCount := TopRowCount;
      if (FirstRowNo < 0) then
        FFirstRowNo := 1
      else
        FFirstRowNo := FirstRowNo;
    end;
end;// SetTopRowCount


//------------------------------------------------------------------------------
// applies distinct
//------------------------------------------------------------------------------
procedure TMYLDBAO.ApplyDistinct(DistinctFields: String);
var
  DistinctFieldList: TStringList;

procedure ApplyDistinctToChild(AO: TMYLDBAO);
var
  ChildDistinctFields: String;
  i: integer;
begin
  if not (AO is TMYLDBAOTable) then
    begin
      ChildDistinctFields := '';
      for i := 0 to FFieldCount-1 do
        if (FFieldLinks[i].AO = AO) and (not FFieldLinks[i].IsHidden) then
          begin
            if (ChildDistinctFields <> '') then
              ChildDistinctFields := ChildDistinctFields+';';
            ChildDistinctFields := ChildDistinctFields +
              TMYLDBAO(FFieldLinks[i].AO).FFieldLinks[FFieldLinks[i].FieldNo].FieldName;
          end;
      if (ChildDistinctFields <> '') then
        AO.ApplyDistinct(ChildDistinctFields);
    end;
end;

procedure FillDistinctFieldsMap;
var
  i,j: integer;
begin
  SetLength(FDistinctFieldsMap, DistinctFieldList.Count);
  for i := 0 to DistinctFieldList.Count-1 do
    for j := 0 to FFieldCount-1 do
      if (AnsiUpperCase(FFieldLinks[j].FieldName) = AnsiUpperCase(DistinctFieldList[i])) then
        begin
          FDistinctFieldsMap[i] := j;
          break;
        end;
end;

begin
  DistinctFieldList := TStringList.Create;
  try
    FillFieldNames(DistinctFieldList,DistinctFields);
    FDistinctApplied := (DistinctFieldList.Count > 0);
    if (FDistinctApplied) then
      begin
        FDistinctFields := DistinctFields;
        FDistinctFieldCount := DistinctFieldList.Count;
        FillDistinctFieldsMap;
        // bug-fix 4.85 - incorrect when condition on hidden join fields
        if (FFilterExpr = nil) then
          if (Self is TMYLDBAOJoin) then
            begin
              if (FLeftAO <> nil) then
                ApplyDistinctToChild(FLeftAO);
              if (FRightAO <> nil) then
                ApplyDistinctToChild(FRightAO);
            end;
      end;
  finally
    DistinctFieldList.Free;
  end;
end; // ApplyDistinct


//------------------------------------------------------------------------------
// sets projection for other TMYLDBAO
//------------------------------------------------------------------------------
procedure TMYLDBAO.SetResultFields(var FieldRefs: array of TMYLDBSelectListItem;
                                  bDistinct: Boolean);
var i,j,k,x,res: integer;
    fname,tname: string;
    aDistinctFields: String;
begin
 aDistinctFields := '';
 j := Length(FieldRefs);
 if (j <= 0) then
  begin
   FieldExists('*','',true,FResultFieldsOrder);
  end
 else
  for i := 0 to j-1 do
   begin
    if (FieldRefs[i].AllFields) then
     fname := '*'
    else
     fname := FieldRefs[i].FieldName;
    tname := FieldRefs[i].TableName;
    if (FieldRefs[i].IsExpression) then
      if ((not TMYLDBExpression(FieldRefs[i].ValueExpr).IsAggregated) or (FIsAOGroupBy)) then
       begin
        inc(FFieldCount);
        SetLength(FFieldLinks,FFieldCount);
        FFieldLinks[FFieldCount-1].Expr := FieldRefs[i].ValueExpr;
        TMYLDBExpression(FFieldLinks[FFieldCount-1].Expr).AssignAO(self, nil, nil);
        // create new field for this expression
        if (FieldRefs[i].Pseudonym <> '') then
         // pseudonim specified
         FFieldLinks[FFieldCount-1].FieldName := FieldRefs[i].Pseudonym
        else
         // random name
         FFieldLinks[FFieldCount-1].FieldName := GetTemporaryName(MYLDBExpressionFieldName);
        FFieldLinks[FFieldCount-1].DisplayName := FFieldLinks[FFieldCount-1].FieldName;
        // get field type
        FFieldLinks[FFieldCount-1].FieldType :=
          TMYLDBExpression(FieldRefs[i].ValueExpr).GetDataType;
        // get field size
        FFieldLinks[FFieldCount-1].FieldSize :=
          TMYLDBExpression(FieldRefs[i].ValueExpr).GetDataSize;
        // get field precision
        FFieldLinks[FFieldCount-1].FieldPrecision :=