editorquery.pas

DBDesigner 4 is a database design system that integrates database design, modelling, creation and ma

  FromClause,
  SetStart: integer;
  AliasNotAssigned: Boolean;
begin
  theEERModel:=TEERModel(theTable.Parent);

  FromClause:=0;

  theClauses:=TStringList.Create;
  theTableAliases:=TStringList.Create;
  theInnerJoinTables:=TStringList.Create;
  theTableList:=TList.Create;
  try
    //Select Command
    if(SQLCmdType=SQLctSELECT)then
    begin
      //Build the Clauses
      theClauses.Add('SELECT=');
      theClauses.Add('FROM=');
      theClauses.Add('WHERE=');
      theClauses.Add('GROUP BY=');
      theClauses.Add('HAVING=');
      theClauses.Add('ORDER BY=');
      theClauses.Add('LIMIT=');

      GetClauses(GetSQLMemoText, theClauses);

      FromClause:=1;

      //Analyze and build SQL command

      //Split into lines
      s:=theClauses.ValueFromIndex[FromClause];
      s:=DMMain.ReplaceText(s, ',', #13#10);
      s:=DMMain.ReplaceText(s, 'CROSS JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'INNER JOIN', #13#10);
      theInnerJoinTables.Text:=s;
      for i:=0 to theInnerJoinTables.Count-1 do
        theInnerJoinTables[i]:=Trim(theInnerJoinTables[i]);

      s:=DMMain.ReplaceText(s, 'STRAIGHT_JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'LEFT JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'LEFT OUTER JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'NATURAL JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'NATURAL LEFT JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'NATURAL LEFT OUTER JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'RIGHT JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'RIGHT OUTER JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'NATURAL JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'NATURAL RIGHT JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'NATURAL RIGHT OUTER JOIN', #13#10);
      s:=DMMain.ReplaceText(s, 'JOIN', #13#10);
      theTableAliases.Text:=s;
      //Store Count of tables existing in FROM Clause
      TablesInFromClauseCount:=theTableAliases.Count;


      for i:=0 to theTableAliases.Count-1 do
      begin
        //Set Tablename=Alias
        theTableAliases[i]:=DMMain.ReplaceText(Trim(theTableAliases[i]), ' AS ', '=');
        theTableAliases[i]:=DMMain.ReplaceText(theTableAliases[i], ' ', '=');

        //Add Table to TableList
        theTableList.Add(theEERModel.GetEERObjectByName(EERTable, theTableAliases.Names[i]));
      end;

      //---------------------------------------------------
      //Get all selected tables
      if(theTable.Selected)then
        for i:=0 to theEERModel.ComponentCount-1 do
          if(theEERModel.Components[i].ClassName='TEERTable')then
            if(TEERTable(theEERModel.Components[i]).Selected)then
            begin
              theTableAliases.Add(TEERTable(theEERModel.Components[i]).ObjName+'=');

              theTableList.Add(theEERModel.Components[i]);
            end;

      //If there is no selected Table, add dragged table
      if(theTableList.Count=TablesInFromClauseCount)then
      begin
        theTableAliases.Add(theTable.ObjName+'=');

        theTableList.Add(theTable);
      end;

      //Get Table Aliases for new tables
      for i:=TablesInFromClauseCount to theTableAliases.Count-1 do
      begin
        //if the table is already in FROM clause, take same Alias+Number
        if(theTableAliases.IndexOfName(TEERTable(theTableList[i]).ObjName)>-1)and
          (theTableAliases.IndexOfName(TEERTable(theTableList[i]).ObjName)<TablesInFromClauseCount)then
        begin
          j:=2;
          while(theTableAliases.IndexOf(TEERTable(theTableList[i]).ObjName+'='+
            theTableAliases.Values[TEERTable(theTableList[i]).ObjName]+IntToStr(j))>-1)do
            inc(j);

          theTableAliases[i]:=TEERTable(theTableList[i]).ObjName+'='+theTableAliases.Values[TEERTable(theTableList[i]).ObjName]+IntToStr(j);
        end
        else
        begin
          j:=0;
          AliasNotAssigned:=False;
          while(Not(AliasNotAssigned))do
          begin
            inc(j);

            AliasNotAssigned:=True;
            for k:=0 to i-1 do
              if(Copy(theTableAliases[k], Pos('=', theTableAliases[k])+1, Length(theTableAliases[k]))=
                Copy(TEERTable(theTableList[i]).ObjName, 1, j))then
              begin
                AliasNotAssigned:=False;
                break;
              end;

            if(AliasNotAssigned)then
              theTableAliases[i]:=TEERTable(theTableList[i]).ObjName+'='+Copy(TEERTable(theTableList[i]).ObjName, 1, j);
          end;
        end;
      end;

      //---------------------------------------------------
      //Modify Clauses

      //Add * to SELECT clause if it's empty
      if(theClauses.ValueFromIndex[0]='')then
        theClauses.ValueFromIndex[0]:='*';

      //Add new tables to FROM Clause
      for i:=TablesInFromClauseCount to theTableAliases.Count-1 do
      begin
        if(theClauses.ValueFromIndex[FromClause]='')then
          theClauses.ValueFromIndex[FromClause]:=DMMain.ReplaceText(theTableAliases[i], '=', ' ')
        else
          theClauses.ValueFromIndex[FromClause]:=Trim(theClauses.ValueFromIndex[FromClause])+', '+
            DMMain.ReplaceText(theTableAliases[i], '=', ' ');
      end;

      //make joins
      if(SQLCmdSelectJoinType<>SQLjtNONE)then
      begin
        for i:=TablesInFromClauseCount to theTableList.Count-1 do
        begin
          //Check RelStart
          for j:=0 to TEERTable(theTableList[i]).RelStart.Count-1 do
          begin
            destTblNr:=theTableList.IndexOf(TEERRel(TEERTable(theTableList[i]).RelStart[j]).DestTbl);
            if(destTblNr>-1)and(destTblNr<TablesInFromClauseCount)then
            begin
              for k:=0 to TEERRel(TEERTable(theTableList[i]).RelStart[j]).FKFields.Count-1 do
              begin
                s:='';
                if(theClauses.ValueFromIndex[2]<>'')then
                  s:=s+' AND ';
                s:=s+theTableAliases.ValueFromIndex[destTblNr]+'.'+
                  TEERRel(TEERTable(theTableList[i]).RelStart[j]).FKFields.ValueFromIndex[k]+'='+
                  theTableAliases.ValueFromIndex[i]+'.'+
                  TEERRel(TEERTable(theTableList[i]).RelStart[j]).FKFields.Names[k];

                theClauses.ValueFromIndex[2]:=theClauses.ValueFromIndex[2]+s;
              end;
            end;
          end;

          //Check RelEnd
          for j:=0 to TEERTable(theTableList[i]).RelEnd.Count-1 do
          begin
            destTblNr:=theTableList.IndexOf(TEERRel(TEERTable(theTableList[i]).RelEnd[j]).SrcTbl);
            if(destTblNr>-1)and(destTblNr<TablesInFromClauseCount)then
            begin
              for k:=0 to TEERRel(TEERTable(theTableList[i]).RelEnd[j]).FKFields.Count-1 do
              begin
                s:='';
                if(theClauses.ValueFromIndex[2]<>'')then
                  s:=s+' AND ';
                s:=s+theTableAliases.ValueFromIndex[destTblNr]+'.'+
                  TEERRel(TEERTable(theTableList[i]).RelEnd[j]).FKFields.ValueFromIndex[k]+'='+
                  theTableAliases.ValueFromIndex[i]+'.'+
                  TEERRel(TEERTable(theTableList[i]).RelEnd[j]).FKFields.Names[k];

                theClauses.ValueFromIndex[2]:=theClauses.ValueFromIndex[2]+s;
              end;
            end;
          end;
        end;
      end;
    end
    //UPDATE Command
    else if(SQLCmdType=SQLctUPDATE)then
    begin
      //Build the Clauses
      theClauses.Add('UPDATE=');
      theClauses.Add('SET=');
      theClauses.Add('WHERE=');
      theClauses.Add('LIMIT=');

      GetClauses(GetSQLMemoText, theClauses);

      FromClause:=0;

      //Analyze and build SQL command
      theClauses.ValueFromIndex[0]:=theTable.ObjName;
    end
    //UPDATE Command
    else if(SQLCmdType=SQLctDELETE)then
    begin
      //Build the Clauses
      theClauses.Add('DELETE FROM=');
      theClauses.Add('WHERE=');
      theClauses.Add('LIMIT=');

      GetClauses(GetSQLMemoText, theClauses);

      FromClause:=0;

      //Analyze and build SQL command
      theClauses.ValueFromIndex[0]:=theTable.ObjName;
    end
    //INSERT Command
    else if(SQLCmdType=SQLctINSERT)then
    begin
      //Build the Clauses
      theClauses.Add('INSERT INTO=');
      theClauses.Add('VALUES=');
      theClauses.Add('SELECT=');

      GetClauses(GetSQLMemoText, theClauses);

      //Set Cursor to Values...
      FromClause:=-1;

      //Analyze and build SQL command
      theClauses.ValueFromIndex[0]:=theTable.ObjName+'(';

      for i:=0 to theTable.Columns.Count-1 do
      begin
        theClauses.ValueFromIndex[0]:=
          theClauses.ValueFromIndex[0]+TEERColumn(theTable.Columns[i]).ColName;

        if(i<theTable.Columns.Count-1)then
          theClauses.ValueFromIndex[0]:=
            theClauses.ValueFromIndex[0]+', '
        else
          theClauses.ValueFromIndex[0]:=
            theClauses.ValueFromIndex[0]+')';
      end;

      theClauses.ValueFromIndex[1]:='(';

    end;

    //Build SQLStr from Clauses
    SQLStr:='';
    SetStart:=0;
    for i:=0 to theClauses.Count-1 do
      if(theClauses.ValueFromIndex[i]<>'')then
      begin
        SQLStr:=SQLStr+theClauses.Names[i]+' '+
          Trim(theClauses.ValueFromIndex[i])+#13#10;
        if(FromClause=i)then
          SetStart:=Length(SQLStr)-2;
      end;

    SetSQLMemoText(SQLStr);
  finally
    theClauses.Free;
    theTableAliases.Free;
    theInnerJoinTables.Free;
    theTableList.Free;
  end;

{$IFDEF USE_SYNEDIT}
  if(DMGUI.UseSQLSyntaxHighlighting)then
  begin
    if(SQLSynEdit.Visible)then
      SQLSynEdit.SetFocus;
    SQLSynEdit.SelLength:=0;
    if(SetStart>0)then
      SQLSynEdit.SelStart:=SetStart
    else
      SQLSynEdit.SelStart:=Length(SQLSynEdit.Text);
  end
  else
  begin
{$ENDIF}
    if(SQLPnl.Visible)then
      SQLMemo.SetFocus;
    SQLMemo.SelLength:=0;
    if(SetStart>0)then
      SQLMemo.SelStart:=SetStart
    else
      SQLMemo.SelStart:=Length(SQLMemo.Text);
{$IFDEF USE_SYNEDIT}
  end;
{$ENDIF}
end;

procedure TEditorQueryForm.AddColumnToSQLCommand(columnPos: integer; col: TEERColumn);
var i, SetStart: integer;
  SQLStr, s, TableAlias: string;
  theClauses: TStringList;
begin
  if(Not(Assigned(theEERModel)))then
    Exit;

  theClauses:=TStringList.Create;
  try
    //Select Command
    if(Copy(GetSQLMemoText, 1, 6)='SELECT')then
    begin
      //Build the Clauses
      theClauses.Add('SELECT=');
      theClauses.Add('FROM=');
      theClauses.Add('WHERE=');
      theClauses.Add('GROUP BY=');
      theClauses.Add('HAVING=');
      theClauses.Add('ORDER BY=');
      theClauses.Add('LIMIT=');

      GetClauses(GetSQLMemoText, theClauses);

      //Get columns Alias
      TableAlias:='';

      //Check if column's table is in where clause
      theEERTable:=theEERModel.GetEERTableByColumnID(col.Obj_id);
      if(theEERTable<>nil)then
      begin
        i:=Pos(theEERTable.ObjName+' ', theClauses.ValueFromIndex[1]);
        if(i>0)then
        begin
          s:=Copy(theClauses.ValueFromIndex[1], i+Length(theEERTable.ObjName)+1, Length(theClauses.ValueFromIndex[1]));

          if(Pos(',', s)=0)then
            TableAlias:=Trim(s)+'.'
          else
            TableAlias:=Trim(Copy(s, 1, Pos(',', s)-1))+'.';
        end
        else
        begin
          //Add table to FROM clause
          AddTableToSQLCommand(theEERTable, SQLctSELECT, SQLjtINNER);

          AddColumnToSQLCommand(columnPos, col);

          Exit;
        end;
      end;

      case columnPos of
        cpSelectClause, cpGroupClause, cpOrderClause:
        begin
            //Check if there is only a SELECT *, and replace the *
            //With the current column
            if(theClauses.ValueFromIndex[columnPos]='*')or
              (theClauses.ValueFromIndex[columnPos]='')then
              theClauses.ValueFromIndex[columnPos]:=TableAlias+col.ColName
            //If there is already a , after the last column
            else if(Copy(Trim(theClauses.ValueFromIndex[columnPos]),
              Length(Trim(theClauses.ValueFromIndex[columnPos])), 1)=',')then
              theClauses.ValueFromIndex[columnPos]:=
                Trim(theClauses.ValueFromIndex[columnPos])+' '+TableAlias+col.ColName
            //Add column to col-list
            else
              theClauses.ValueFromIndex[columnPos]:=
                Trim(theClauses.ValueFromIndex[columnPos])+', '+TableAlias+col.ColName;
        end;
        cpWhereClause, cpHavingClause:
        begin
          //if the last char is =, then simply add the col name
          if(Copy(Trim(theClauses.ValueFromIndex[columnPos]),
            Length(Trim(theClauses.ValueFromIndex[columnPos])), 1)='=')then
            theClauses.ValueFromIndex[columnPos]:=
              Trim(theClauses.ValueFromIndex[columnPos])+TableAlias+col.ColName
          //if there already is a WHERE condition, add AND
          else if(Trim(theClauses.ValueFromIndex[columnPos])<>'')then
            theClauses.ValueFromIndex[columnPos]:=
              Trim(theClauses.ValueFromIndex[columnPos])+' AND '+TableAlias+col.ColName+'='
          else
            theClauses.ValueFromIndex[columnPos]:=TableAlias+col.ColName+'=';
        end;
      end;
    end
    //UPDATE Command
    else if(Copy(GetSQLMemoText, 1, 6)='UPDATE')then
    begin
      //Build the Clauses
      theClauses.Add('UPDATE=');
      theClauses.Add('SET=');
      theClauses.Add('WHERE=');
      theClauses.Add('LIMIT=');

      GetClauses(GetSQLMemoText, theClauses);

      case columnPos of
        cpSetClause, cpWhereClause:
        begin
          //if the last char is =, then simply add the col name
          if(Copy(Trim(theClauses.ValueFromIndex[columnPos]),
            Length(Trim(theClauses.ValueFromIndex[columnPos])), 1)='=')then
            theClauses.ValueFromIndex[columnPos]:=
              Trim(theClauses.ValueFromIndex[columnPos])+TableAlias+col.ColName
          //if there already is a WHERE condition, add AND (or ,)
          else if(Trim(theClauses.ValueFromIndex[columnPos])<>'')then
            if(columnPos=cpWhereClause)then
              theClauses.ValueFromIndex[columnPos]:=
                Trim(theClauses.ValueFromIndex[columnPos])+' AND '+TableAlias+col.ColName+'='
            else
              theClauses.ValueFromIndex[columnPos]:=
                Trim(theClauses.ValueFromIndex[columnPos])+', '+TableAlias+col.ColName+'='