oraservicesuni.pas

来自「CrLab UniDAC 1.0 include sources」· PAS 代码 · 共 1,991 行 · 第 1/5 页

        end;

      if Filter = '' then
        exit;

      SQL := 'SELECT owner, table_name, column_name';
      if FDataSet.Options.DefaultValues then
        SQL := SQL + ', data_default, data_type';
      SQL := SQL + ' FROM all_tab_columns' + AddDBLink(DBLink) +
        ' WHERE ' + Filter +
        ' ORDER BY owner, table_name, column_id';

      Query.SQL.Text := SQL;
      try
        Query.Open;
      except
        //Probably no DBLink
      end;

      p := 0;
      while p < Columns.Count do begin
        ColumnInfo := TColumnInfo(Columns[p]);
        if (ColumnInfo.TableIndex = -1) and (ColumnInfo.Table <> '') or
          not FDataSet.Options.FieldsOrigin and (ColumnInfo.TableIndex <> -1)
          and (ColumnInfo.TableIndex <> FUpdatingTableInfoIdx)
        then
          ColumnInfo.Described := True;

        if ColumnInfo.Described then begin
          Inc(p);
          continue;
        end;

        if ColumnInfo.Name = '*' then begin
          if ColumnInfo.TableIndex <> -1 then begin
            FieldTableName := Tables[ColumnInfo.TableIndex].TableName;
            FieldOwner := Tables[ColumnInfo.TableIndex].Schema;
          end;
          NewColumnInfo := nil;
          Query.First;
          while not Query.EOF do begin
            CurOwner := Query.Fields[0].AsString;
            CurTableName := Query.Fields[1].AsString;
            if (ColumnInfo.TableIndex = -1) or
              (CurOwner = FieldOwner) and (CurTableName = FieldTableName)
            then begin
              Inc(p);
              NewColumnInfo := TColumnInfo.Create;
              Columns.Insert(p, NewColumnInfo);
              NewColumnInfo.Used := False;
              NewColumnInfo.Described := True;
              NewColumnInfo.Table := ColumnInfo.Table;
              NewColumnInfo.TableIndex := ColumnInfo.TableIndex;
              NewColumnInfo.Name := Query.Fields[2].AsString;
              NewColumnInfo.Alias := NewColumnInfo.Name;

              if NewColumnInfo.TableIndex = -1 then begin
                for j := 0 to High(Tables) do
                  if (Tables[j].Schema = CurOwner) and (Tables[j].TableName = CurTableName) then begin
                    NewColumnInfo.TableIndex := j;
                    break;
                  end;
              end;
              if NewColumnInfo.TableIndex <> -1 then
                Tables[NewColumnInfo.TableIndex].Flag := 0;

              if FDataSet.Options.DefaultValues and (NewColumnInfo.TableIndex = FUpdatingTableInfoIdx) then
                NewColumnInfo.Expr := GetDefExpr(Query.Fields[3].AsString, Query.Fields[4].AsString);
            end
            else
              if (NewColumnInfo <> nil) and (ColumnInfo.TableIndex <> -1) then
                break;
            Query.Next;
          end;
          if (NewColumnInfo <> nil) and (ColumnInfo.TableIndex <> -1) then
            ColumnInfo.Described := True;
        end
        else
          if (ColumnInfo.Name <> '') and (ColumnInfo.Name <> 'ROWID') then begin
            if ColumnInfo.TableIndex <> -1 then begin
              if FDataSet.Options.DefaultValues and (ColumnInfo.TableIndex = FUpdatingTableInfoIdx) then
                Located := Query.Locate('owner;table_name;column_name',
                  VarArrayOf([Tables[ColumnInfo.TableIndex].Schema, Tables[ColumnInfo.TableIndex].TableName, ColumnInfo.Name]), [])
              else begin
                ColumnInfo.Described := True;
                Located := False;
              end;
            end
            else
              Located := Query.Locate('column_name', ColumnInfo.Name, []);

            if Located then begin
              ColumnInfo.Described := True;
              if FDataSet.Options.DefaultValues then
                ColumnInfo.Expr := GetDefExpr(Query.Fields[3].AsString, Query.Fields[4].AsString);

              if ColumnInfo.TableIndex = -1 then begin
                CurOwner := Query.Fields[0].AsString;
                CurTableName := Query.Fields[1].AsString;
                for j := 0 to High(Tables) do
                  if (Tables[j].Schema = CurOwner) and (Tables[j].TableName = CurTableName) then begin
                    ColumnInfo.TableIndex := j;
                    break;
                  end;
              end;
              if ColumnInfo.TableIndex <> -1 then
                Tables[ColumnInfo.TableIndex].Flag := 0;
            end;
          end
          else
            ColumnInfo.Described := True;

        inc(p);
      end;

      for j := 0 to High(Tables) do
        if (Tables[j].Flag and 1 <> 0) and (Tables[j].DBLink = DBLink) then begin
          if Query.Locate('owner;table_name',
            VarArrayOf([Tables[j].Schema, Tables[j].TableName]), [])
          then
            Tables[j].Flag := 0
          else
            NeedDescribeSynonyms := True;
        end;

    until not MoreDBLinks;

    if NeedDescribeSynonyms then begin
      NeedDescribeSynonyms := False;
      for j := 0 to Columns.Count - 1 do
        if not TColumnInfo(Columns[j]).Described then begin
          NeedDescribeSynonyms := True;
          break;
        end;
    end;
  end;

  procedure GetDBLinkSchema;
  var
    SQL: string;
    i: integer;
  begin
    for i := 0 to High(Tables) do
      if (Tables[i].Flag and 1 <> 0) and
        (Tables[i].DBLink <> '') and (Tables[i].Schema = '')
      then
        SQL := SQL + ' AND db_link = ''' + Tables[i].DBLink + '''';

    if SQL = '' then
      Exit;

    SQL := 'SELECT username, db_link FROM all_db_links' +
           ' WHERE owner IN (''' + GetOwner + ''', ''PUBLIC'') ' +
           SQL +
           ' ORDER BY decode(owner, ''PUBLIC'', 1, 0) ';

    Query.SQL.Text := SQL;
    Query.Open;

    for i := 0 to High(Tables) do
      if (Tables[i].Flag and 1 <> 0) and
        (Tables[i].DBLink <> '') and (Tables[i].Schema = '')
      then
        if Query.Locate('db_link', Tables[i].DBLink, []) then
          Tables[i].Schema := Query.Fields[0].AsString;

    Query.Close;

    for i := 0 to High(Tables) do
      if (Tables[i].Flag and 1 <> 0) and
        (Tables[i].DBLink <> '') and
        ((Tables[i].Schema = '') or (Tables[i].Schema = 'CURRENT_USER'))
      then begin
        if UserName = '' then begin
          UserName := UsedConnection.Username;
          UserName := TOCITableInfo.NormalizeName(UserName, False, True);
          if UserName = '' then begin
            Query.SQL.Text := 'SELECT sys_context(''USERENV'', ''SESSION_USER'') FROM dual';
            Query.Open;
            UserName := Query.Fields[0].AsString;
            Query.Close;
          end;
        end;
        Tables[i].Schema := UserName;
      end;
  end;

  procedure DescribeSynonyms;
  var
    j: integer;
    SQL, Filter, DBLink: string;
    MoreDBLinks: boolean;
  begin
    for j := 0 to High(Tables) do
      Tables[j].Flag := Tables[j].Flag and not 2;
    repeat
      Filter := '';
      MoreDBLinks := False;
      for j := 0 to High(Tables) do
        if (Tables[j].Flag and 1 <> 0) and (Tables[j].Flag and 2 = 0) then begin
          if Filter = '' then
            DBLink := Tables[j].DBLink
          else begin
            if Tables[j].DBLink <> DBLink then begin
              MoreDBLinks := True;
              continue;
            end;
            Tables[j].Flag := Tables[j].Flag or 2;
            Filter := Filter + ' OR';
          end;
          Filter := Filter + ' (synonym_name = ''' + Tables[j].TableName + '''';
          if Tables[j].Flag and 8 = 0 then
            Filter := Filter + ' AND owner IN (''' + Tables[j].Schema + ''',''PUBLIC'') )'
          else
            Filter := Filter + ' AND owner = ''' + Tables[j].Schema + ''')';
        end;

      if Filter = '' then
        Exit;

      SQL := 'SELECT synonym_name, table_owner, table_name, db_link ' +
        'FROM all_synonyms' + AddDBLink(DBLink) +
        ' WHERE ' + Filter +
        ' ORDER BY decode(owner, ''PUBLIC'', 1, 0)';

      Query.SQL.Text := SQL;
      Query.Open;

      for j := 0 to High(Tables) do
        if (Tables[j].Flag and 1 <> 0) and (Tables[j].DBLink = DBLink) then
          if Query.Locate('synonym_name', Tables[j].TableName, []) then begin
            if (DBLink <> '') and (Query.Fields[3].AsString <> '') then
              Tables[j].Flag := 0
            else begin
              Tables[j].Synonym := Tables[j].TableName;
              Tables[j].TableName := Query.Fields[2].AsString;
              Tables[j].DBLink := Query.Fields[3].AsString;
              Tables[j].Schema := Query.Fields[1].AsString;
              Tables[j].Flag := Tables[j].Flag or 8;
            end
          end
          else
            Tables[j].Flag := 0;

    until not MoreDBLinks;

    GetDBLinkSchema;
  end;

  procedure GetTablesFields;
  var
    i: integer;
    NeedDescribeSynonyms: boolean;
  begin
    for i := 0 to High(Tables) do
      with Tables[i] do begin
        if Schema = '' then
          Schema := GetOwner;
        if FDataSet.Options.FieldsOrigin or (i = FUpdatingTableInfoIdx) then
          Flag := 1
        else
          Flag := 0; // don't query fields for this table
      end;

    Query := UsedConnection.CreateDataSet;
    try
      TDBAccessUtils.SetLockDebug(Query, True);
      repeat
        GetFieldsInfoServer(NeedDescribeSynonyms);

        if NeedDescribeSynonyms then
          DescribeSynonyms;
      until not NeedDescribeSynonyms;
    finally
      Query.Free;
    end;
  end;

var
  FieldDesc: TCRFieldDesc;
  ReadFieldsFromServer: boolean;
  Origin, TableName, FieldName: string;
  ColumnsInfo: TColumnsInfo;
  ColumnCount, AsteriskCount, DefaultTable: integer;
  ColumnInfo: TColumnInfo;
  St: string;
  i, j: integer;
  TablesInfo: TCRTablesInfo;
begin
  if FFieldsInfoRequested then
    Exit;

  TablesInfo := GetTablesInfo;

  // for the case when we can not determine tables list
  // select from (select from)
  if TablesInfo.Count = 0 then begin
    FFieldsInfoRequested := True;
    exit;
  end;

  SetLength(Tables, TablesInfo.Count);
  for i := 0 to High(Tables) do
    with Tables[i] do begin
      TableName := TOCITableInfo.NormalizeName(TablesInfo[i].TableName, False, True);
      if not (TOCITableInfo.IsQuoted(TablesInfo[i].TableName) and
        (TOCITableInfo.UnQuote(TablesInfo[i].TableName) <> TableName))
      then begin
        j := pos('@', TableName);
        if j > 0 then begin
          DBLink := Copy(TableName, j + 1, Length(TableName));
          TableName := Copy(TableName, 1, j - 1);
        end;
        j := pos('.', TableName);
        if j > 0 then begin
          Schema := Copy(TableName, 1, j - 1);
          TableName := Copy(TableName, j + 1, Length(TableName));
        end;
      end;
    end;

  St := FDataSet.FinalSQL;
  ColumnCount := GetColumnsInfo(St, ColumnsInfo);
  try
   AsteriskCount := 0;
   DefaultTable := -1; // table index for fields from '*'
   ReadFieldsFromServer := False;
   Columns := TList.Create;
   try
     for i := 0 to ColumnCount - 1 do begin
       ColumnInfo := TColumnInfo.Create;
       Columns.Add(ColumnInfo);
       ColumnInfo.Used := False;
       ColumnInfo.Described := False;
       ColumnInfo.Name := ColumnsInfo[i].Name;
       ColumnInfo.TableIndex := -1;
       ColumnInfo.Table := ColumnsInfo[i].Table;
       if ColumnInfo.Table <> '' then
         for j := 0 to TablesInfo.Count - 1 do begin
           if TablesInfo[j].TableAlias <> '' then
             TableName := TOCITableInfo.NormalizeName(TablesInfo[j].TableAlias, False, True)
           else
             TableName := TOCITableInfo.NormalizeName(TablesInfo[j].TableName, False, True);
           if (ColumnInfo.Table = TableName) or
             // table name without schema and dblink
             (TablesInfo[j].TableAlias = '') and (ColumnInfo.Table = Tables[j].TableName)
           then begin
             ColumnInfo.TableIndex := j;
             break;
           end;
         end;

       // We use Expr to set Alias.
       // Currently Expr is not properly normalized by GetColumnsInfo.
       // Moreover Expr is returned without spaces. So Alias will not always correct.
       ColumnInfo.Expr := AnsiUpperCase(ColumnsInfo[i].Expr);
       ColumnInfo.Alias := ColumnsInfo[i].Alias;

       if (ColumnInfo.Alias = '') and (ColumnInfo.Name <> '*') then begin
         if ColumnInfo.Name <> '' then
           ColumnInfo.Alias := ColumnInfo.Name
         else
         // We need correct Alias do find corresponding FieldDesc.
         // Otherwise expression can be treated as field from '*'.
           ColumnInfo.Alias := ColumnInfo.Expr;
       end;

       if ColumnInfo.Name = '*' then begin
         Inc(AsteriskCount);
         // If Table <> '', we use this ColumnInfo.TableIndex for all FieldDescs that does not
         // have corresponding ColumnInfo.
         // If tables count > 1 and Table = '' or there are several '*' with different Table,
         // we cannot determine FieldDesc's table without query to ALL_TAB_COLUMNS.
         // There can be several tables even if TableInfo.Count = 1:
         // select * from t1, (select a from b) t2
         if (ColumnInfo.Table = '') or
           (AsteriskCount > 1) and (ColumnInfo.TableIndex <> DefaultTable)
         then
           ReadFieldsFromServer := True;

         // ReadFieldsFromServer will be reset to False if ExtendedFieldsInfo = False.
         // So DefaultTable must be set.
         // When ExtendedFieldsInfo = False we suggest that all fields w/o ColumnInfo
         // belong to Table from first '*' in query
         if AsteriskCount = 1 then begin // for first '*'
           if ColumnInfo.Table = '' then
             DefaultTable := 0
           else
             DefaultTable := ColumnInfo.TableIndex; // can be -1 for table that is not present in TablesInfo
         end;
       end
       else
       if ColumnInfo.Name = 'ROWID' then begin
         if ColumnInfo.Table = '' then
           ColumnInfo.TableIndex := 0;
       end
       else begin
         if (ColumnInfo.Table = '') and (ColumnInfo.Name <> '') then
            // Possibly it is a value or system variable