Hi.

In December 2012, this[1] thread has brought some ideas about Database metadata, in special the retrieving of Schema names.

This[2] wiki page summarizes what was discussed. Some further conclusion after that thread?

I agree that using Information schema, wherever is supported, is the best solution.

I propose the following types and methods:

    TSqlObjectIdenfier = class
    public
          property SchemaName: String;
          property ObjectName: String;
          //Optional / Reuse TSchemaType?
          property SqlObjectType: TSqlObjectType;
    end;
    TSqlObjectList = specialize TFPGList<TSqlObjectIdenfier>;

On TSQLConnection the new (virtual) methods bellow (similar to GetDBInfo, but public) could be added:

      function GetObjectNames(ObjectType: TSchemaType):
TSqlObjectIdenfier; overload;
      function GetObjectNames(ObjectTypes: TSchemaTypes {A new Type
Set of TSchemaType}): TSqlObjectIdenfier;  overload;

These changes would not affect Delphi compatibility, as I am proposing to add, not replace what exists today.

Ideas, Pros, cons.. ?

Thank you,

Daniel

[1] http://lists.freepascal.org/pipermail/fpc-pascal/2012-December/035844.html [2] http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization

Make this a TCollection/TCollectionItem.
Generics are not used in the DB units, and they should not be used for consistency.

Michael.

Michael Van Canneyt wrote

Make this a TCollection/TCollectionItem. Generics are not used in the DB units, and they should not be used for consistency.

But I was thinking that TSqlObjectIdenfier could be reused at Data Dictionary Units. As a TCollectionItem could be harder to reuse, as it will depend of the existence of a TCollection.

Maybe using Record instead of a class?

I don't understand what you mean with this sentence ?

1. TCollectionItem can perfectly be used without TCollection.
2. You create the collection implicitly with your generic list anyway; the exercise is pointless without it; so why not be explicit ?

But if you feel more comforable with a record and an dynamic array: feel free to use that. Though I don't see a problem with TCollectionItem/TCollection. It is used everywhere in the FCL.

Michael.

2014-06-01 11:18 GMT-03:00 Michael Van Canneyt :

... And more an advantage using TOwnedCollection/TCollection: you can show its properties in object inspector.

I was trying to say that I want to use the same
type(TSqlObjectIdenfier) in fpDatadict classes, like TDDTableDef.

Though I don't see a problem with TCollectionItem/TCollection. It is used everywhere in the FCL.

I see no problem. It was just a wrong impression that I had about TCollectionItem.

Thank You Michael.

Daniel

An Issue was created with the patch:

http://bugs.freepascal.org/view.php?id=26254

Daniel Gaspary wrote

I see no problem. It was just a wrong impression that I had about TCollectionItem.

Hi Daniel,
If I understand correctly, when you will use new GetObjectNames then you must itterate throught returned collection to obtain "schema names" + "object names" (f.e. "table names").

IMO very similar result you can get also with existing implementation, when you use:

with SQLQuery1 do begin
  SetSchemaInfo(stTables); // here you can set type of schema query like
stTables, stColumns, stProcedures, stSchemata etc.
  Open; // here is opened dataset with requested schema query
  while not Eof do begin
    // here you can use FieldByName('SCHAME_NAME').AsString ,
FieldByName('TABLE_NAME').AsString, etc.
    Next;
  end;
end;

-Laco.

LacaK wrote

IMO very similar result you can get also with existing implementation, when you use:

Yes, this is the way GetDBInfo do, but it's not public, and never return schema. What I did was to copy and modify this method.

I have sent a patch: http://bugs.freepascal.org/view.php?id=26254

Daniel Gaspary wrote / napísal(a):

Code which I have attached uses only public methods and returns "metadata dataset" which has also schema_name column (for SQLConnection which support schemas. I have used public method SetSchemaInfo not GetDBInfo)

What I did was to copy and modify this method. I have sent a patch: http://bugs.freepascal.org/view.php?id=26254

Yes I see it, but as I wrote your new method returns collection, which you must process, which is more or less what you can do with "metadata dataset"

-Laco.

While this is correct, the collection approach is much easier to use: With the metadataset, you must know which fields exist and type them correctly: FieldByName('XYZ').AsString

With collections, the IDE codetools will tell you what properties exist. That is a order of magnitude more comfortable and less error prone.

Michael.

LacaK wrote

(for SQLConnection which support schemas. I have used public method SetSchemaInfo not GetDBInfo)

It's not public in SqlConnection, it's public in SqlQuery.

Anyway, I don't see why avoid a dedicated method to do this instead of 3 steps which are not a clear.

Correct. And beyond the method, I have created the following const array:

TSchemaObjectNames: array[TSchemaType] of String = ('???', 'table_name', '???', 'procedure_name', 'column_name', 'param_name',

      'index_name', 'package_name', 'schema_name');

To avoid this need for retyping field names, over and over.

Michael Van Canneyt wrote / napísal(a):

I still feel it a bit incompatible with existing interface. If existing methods are not enough then we can return to already discussed extension:

TSchemaOption = (soIncludeSchemaName, soSystemObjects, soQuoteNames);
TSchemaOptions = set of TSchemaOption;

and

TSQLConnection.GetTableNames(List: TStrings; SchemaName : String;
Options : TSchemaOptions);
or better "new":
TSQLConnection.GetObjectNames(List: TStrings; SchemaType: TSchemaType;
Options : TSchemaOptions);

Which IMO fits better in existing frame.
-Laco.

LacaK wrote

TSQLConnection.GetTableNames(List: TStrings; SchemaName : String; Options : TSchemaOptions); Which IMO fits better in existing frame.

Using this method, to take all tables, from all schemas, the user would need to run multiples queries, one for each schema.