[Request][Fix]Not get unique keys fields with oracle database

[Atlant2017]

Hi!

With current function "TZOracleDatabaseMetadata.UncachedGetPrimaryKeys" in file "src\dbc\ZDbcOracleMetadata.pas"

Code: Select all

function TZOracleDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
  const Schema: string; const Table: string): IZResultSet;
var
  SQL: string;
  OwnerCondition,TableCondition: String;

  function CreateExtraWhere: String;
  begin
    Result := '';
    If OwnerCondition <> '' then
      Result := OwnerCondition;
    If TableCondition <> '' then
      If Result <> '' then
        Result := Result + ' AND ' + TableCondition
      Else
        Result := TableCondition;
    If Result <> '' then
      Result := ' AND ' + Result;
  end;

begin
  OwnerCondition := ConstructNameCondition(Schema,'A.OWNER');
  TableCondition := ConstructNameCondition(Table,'A.TABLE_NAME');
  SQL := 'SELECT NULL AS TABLE_CAT, A.OWNER AS TABLE_SCHEM, A.TABLE_NAME,'
    + ' B.COLUMN_NAME, B.COLUMN_POSITION AS KEY_SEQ, A.INDEX_NAME AS PK_NAME'
    + ' FROM ALL_INDEXES A, ALL_IND_COLUMNS B'
    + ' WHERE A.OWNER=B.INDEX_OWNER AND A.INDEX_NAME=B.INDEX_NAME'
    + ' AND A.TABLE_OWNER=B.TABLE_OWNER AND A.TABLE_NAME=B.TABLE_NAME'
    + ' AND A.UNIQUENESS=''UNIQUE'' AND A.GENERATED=''Y'''
    + ' AND A.INDEX_NAME LIKE ''SYS_%'''
    + CreateExtraWhere
    + ' ORDER BY A.INDEX_NAME, B.COLUMN_POSITION';
  Result := CopyToVirtualResultSet(
    GetConnection.CreateStatement.ExecuteQuery(SQL),
    ConstructVirtualResultSet(PrimaryKeyColumnsDynArray));
end;

Query returns only indices generated by DB engine for internal use - names that begins with 'SYS_' and generated='Y'.
This query is not returns all other keys (unique indices) - created by user for user tables due to business logic.

My different for this function is based on ALL_IND_COLUMNS and ALL_CONSTRAINTS virtual relationships.

Code: Select all

function TZOracleDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
  const Schema: string; const Table: string): IZResultSet;
var
  SQL: string;
  OwnerCondition,TableCondition: String;

  function CreateExtraWhere: String;
  begin
    Result := '';
    If OwnerCondition <> '' then
      Result := OwnerCondition;
    If TableCondition <> '' then
      If Result <> '' then
        Result := Result + ' AND ' + TableCondition
      Else
        Result := TableCondition;
    If Result <> '' then
      Result := ' AND ' + Result;
  end;

begin
    OwnerCondition := ConstructNameCondition(Schema,'AC.OWNER');
    TableCondition := ConstructNameCondition(Table,'AC.TABLE_NAME');
    SQL:='SELECT NULL AS TABLE_CAT,AC.OWNER AS TABLE_SCHEM,AC.TABLE_NAME,'
    + 'B.COLUMN_NAME,B.COLUMN_POSITION AS KEY_SEQ,AC.INDEX_NAME AS PK_NAME '
    + 'FROM ALL_IND_COLUMNS B,ALL_CONSTRAINTS AC '
    + 'WHERE AC.INDEX_NAME=B.INDEX_NAME '
    + CreateExtraWhere
    + ' ORDER BY AC.CONSTRAINT_TYPE,B.INDEX_NAME,B.COLUMN_POSITION ';
    SQL:='SELECT * FROM('+SQL+')WHERE PK_NAME=(SELECT PK_NAME FROM('+SQL
    +')WHERE ROWNUM=1)';
  Result := CopyToVirtualResultSet(
    GetConnection.CreateStatement.ExecuteQuery(SQL),
    ConstructVirtualResultSet(PrimaryKeyColumnsDynArray));

this function is not changed in versions 7.1, 7.2 , 7.3.
Please test this changes and apply in versions 7.1 - 7.3

Alternative changes in http://zeoslib.sourceforge.net/viewtopi ... 38&t=13368 detect any index without priority primary keys.

[FMat]

Hi

I've had the same issue with the primary keys for Oracle database.

After some research, I've learned that in Oracle databases the complete set of primary keys (accessible to the current logged user) can be found joining two tables: ALL_CONSTRAINTS and ALL_CONS_COLUMNS.
You can easily find this kind of information browsing Internet.

At the end, the right query might be (Oracle 12c):

SELECT NULL AS TABLE_CAT, A.OWNER AS TABLE_SCHEM, A.TABLE_NAME, B.COLUMN_NAME, B.POSITION AS KEY_SEQ, A.INDEX_NAME AS PK_NAME
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = B.OWNER
ORDER BY A.INDEX_NAME, B.POSITION;

so You can use it in the file ZDbcOracleMetadata.pas:

  SQL := 'SELECT NULL AS TABLE_CAT, A.OWNER AS TABLE_SCHEM, A.TABLE_NAME,'
    + ' B.COLUMN_NAME, B.POSITION AS KEY_SEQ, A.INDEX_NAME AS PK_NAME'
    + ' FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B'
    + ' WHERE A.CONSTRAINT_TYPE = ''P'''
    + ' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME'
    + ' AND A.OWNER = B.OWNER'
    + CreateExtraWhere
    + ' ORDER BY A.INDEX_NAME, B.POSITION';

With these modifications a second effect is that the second sql statement proposed ( SQL:='SELECT * FROM('+SQL+')WHERE ....) should be eliminated.

The value of 'P' for the field ALL_CONSTRAINTS.CONSTRAINT_TYPE means 'Primary key' (you can see Oracle Documentation, i.e. "Oracle® Database Reference");
other values for the field are:

P - Primary key
U - Unique key
R - Referential integrity.


Note: I'm talking of the database version 12c, but I'm pretty sure it's also applicable to version 11 and 10; Unfortunately, I cannot verify.

I hope this could help to fix the software: I'm not anough expert in Delphi and ZeosLib structure library to make modifications myself.

With best regards,
Francesco

P.S.: You can also use the query below:

SELECT NULL AS TABLE_CAT, A.OWNER AS TABLE_SCHEM, A.TABLE_NAME, B.COLUMN_NAME, B.POSITION AS KEY_SEQ, A.CONSTRAINT_NAME AS FK_NAME
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B
WHERE A.CONSTRAINT_TYPE= 'R'
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = B.OWNER
ORDER BY A.INDEX_NAME, B.POSITION

in order to retrieve all the foreign keys accessible to the current logged user (and maybe create a function returning the foreign keys).

P.S.2: Let say that in the above queries the couple of tables ALL_CONSTRAINTS and ALL_CONS_COLUMNS can be substitued with DBA_CONSTRAINTS and DBA_CONS_COLUMNS (for DBA user) or USR_CONSTRAINTS and USR_CONS_COLUMNS (for generical user), it depends on the kind of user legged in;
I think that the right choise in file ZDbcOracleMetadata.pas is for the tables with the prefix 'ALL' in name.

[marsupilami]

Hello Atlant2017 and Francesco,

our Oracle guy is currently very busy with projects for his company. So please be patient.
With best regards,

Jan

[EgonHugeist]

Thanks for the advice! patch done R4228 \testing-7.2 (SVN)