<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Reinier Olislagers wrote / napísal(a):
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap="">On 18-4-2012 8:27, LacaK wrote:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">I don't have Delphi with dbExpress here, so can't test.
</pre>
</blockquote>
<pre wrap="">I did some tests in Delphi XE with DBExpress and MySQL:
for stTables column names are: CatalogName, SchemaName, TableName,
TableType ('TABLE')
</pre>
</blockquote>
<pre wrap=""><!---->So difference with FPC: the names (catalog_name, schema_name,
table_name, table_type). The FPC names seem to match the ISO SQL*) names
more (ISO has them capitalized, no problem if no quotes are used).
In contrast to dbExpress (and probably the standard), FPC also has recno
(internal database object ID? Could be very handy.)
Other difference: TableType ('TABLE'): I think Delphi+dbExpress better
match ISO SQL*) here (though 'BASE TABLE', 'VIEW') instead of the 0 that
FPC returns - at least in the Firebird, PostgreSQL connectors that I
checked.
*) at least an SQL2008 draft version I found some time ago...
The PostgreSQL documentation is also very nice:
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/information-schema.html">http://www.postgresql.org/docs/current/static/information-schema.html</a>
As is the Mimer (link to old documentation; newer is inside a frame):
<a class="moz-txt-link-freetext" href="http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html">http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html</a>
</pre>
</blockquote>
<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap="">
I'd prefer changing TABLE_TYPE to the ISO way of doing things, but of
course it could break existing applications if any of them use this
functionality... Still, FPC always returns 0 so I suppose it would have
been useless anyway, so no harm in changing it.
</pre>
</blockquote>
ok also MS SQL Server:
<a class="moz-txt-link-freetext" href="http://msdn.microsoft.com/en-us/library/ms186224.aspx">http://msdn.microsoft.com/en-us/library/ms186224.aspx</a><br>
<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap=""></pre>
<blockquote type="cite">
<pre wrap="">for stProcedures : CatalogName, SchemaName, ProcedureName, ProcedureType
('PROCEDURE')
(in FPC (for IBConnection) we have catalog_name, schema_name, proc_name,
proc_type and others)
</pre>
</blockquote>
<pre wrap=""><!---->Yep, in_params and out_params: number of in and out parameters apparently...
Once again, proc_type returns always 0 (Firebird - or is not implemented
- PostgreSQL); changing it to varchar returning 'FUNCTION' or
'PROCEDURE' would make sense IMO.
For using FUNCTION or PROCEDURE: see e.g. the IBM DB2 for iSeries/AS/400
documentation via
<a class="moz-txt-link-freetext" href="http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogans.htm#catroutines">http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogans.htm#catroutines</a>
</pre>
</blockquote>
also MS SQL <a class="moz-txt-link-freetext" href="http://msdn.microsoft.com/en-us/library/ms188757.aspx">http://msdn.microsoft.com/en-us/library/ms188757.aspx</a><br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap=""></pre>
<blockquote type="cite">
<pre wrap="">for stColumns : CatalogName, SchemaName, TableName, ColumnName, TypeName
('char', 'integer' etc.), Precision, Scale, Ordinal, DefaultValue,
IsNullable, IsAutoincrement, and others.
</pre>
</blockquote>
<pre wrap=""><!---->FPC Firebird does not have Ordinal but column_position - probably the
same meaning.
</pre>
</blockquote>
yes<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap="">Apart from naming issues: FPC does not have DefaultValue, or
IsAutoIncrement.
Furthermore, apart from column_name, column_position and the table info,
none of the columns (e.g. column_type) return any useful data, always 0
or an empty string.
</pre>
<blockquote type="cite">
<pre wrap="">for stIndexes : CatalogName, SchemaName, TableName, IndexName,
ConstraintName, IsPrimary, IsUnique, IsAscending
(in list are included also PRIMARY KEYs and UNIQUE constraints)
</pre>
</blockquote>
<pre wrap=""><!---->Got it, so both constraints and indexes... which of course overlap to a
large extent.
Suggest recno (object identifier: integer), catalog_name, schema_name,
table_name, index_name, constraint_name, constraint_primary (boolean),
constraint_unique (boolean), index_ascending (boolean)
</pre>
</blockquote>
may be, but I think, that IsPrimary, IsUnigue, IsAscending would better
names<br>
<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap="">... we could add index_unique and constraint_check later/when needed
</pre>
<blockquote type="cite">
<pre wrap="">for stUserNames : CatalogName, SchemaName
(used by GetSchemaNames)
</pre>
</blockquote>
<pre wrap=""><!---->Ok, could be added; suggest recno (object identifier: integer),
catalog_name and schema_name as column names for consistency with the
existing code.
</pre>
</blockquote>
ok<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap="">
>From Michael's post:
</pre>
<blockquote type="cite">
<pre wrap="">stPRocedureParams: get the parameters of a stored procedure
</pre>
</blockquote>
<pre wrap=""><!---->Suggestion: hijack/adapt PostgreSQL's method - probably quite close to ISO:
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/infoschema-parameters.html">http://www.postgresql.org/docs/current/static/infoschema-parameters.html</a>
(cross checked with IBM iSeries/AS400 DB2 at
<a class="moz-txt-link-freetext" href="http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogans.htm#catparameters">http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogans.htm#catparameters</a>)
recno (object identifier: integer), catalog_name, schema_name,
ordinal_position (integer), parameter_mode ('IN'/'OUT'/'INOUT'),
parameter_name, data_type (varchar; probably db dependent for blobs etc?)
</pre>
</blockquote>
ok<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap="">.... we might add CHARACTER_MAXIMUM_LENGTH and/or CHARACTER_OCTET_LENGTH
and/or NUMERIC_PRECISION, and character set details, but I think that
might be going too far...
</pre>
</blockquote>
we do not must add things, what nobody need ;-)<br>
<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">stPackages: list packages (Oracle and Firebird)
</pre>
</blockquote>
<pre wrap=""><!---->Oracle info:
adapted from:
<a class="moz-txt-link-freetext" href="http://www.oracleappsqueries.com/list-all-invalid-packages/">http://www.oracleappsqueries.com/list-all-invalid-packages/</a>
select object_id
,object_name
from all_objects
where object_type='PACKAGE' -- original also had PACKAGE BODY, which
will get us duplicates?
... also available is owner etc.
Perhaps something like
recno,catalog_name,schema_name,package_name
?
I'll leave that to the Oracle experts..
IIRC, Firebird packages are planned for Firebird 3.0; haven't seen any
documentation on it yet.
Plans
=====
I'll focus on getting lazdatadesktop/datadict support for MSSQL/Sybase
running first; afterwards we can look at the things we can add for other
databases</pre>
</blockquote>
Yes IMO there is worth add only such things, which are usable/doable at
least in 2-3 sql connectors<br>
So please check if your changes can be done also in any other 2
connectors and if it will not lead to very complicated queries against
system catalogs.<br>
<br>
Note INFORMATION_SCHEMA are supported by:<br>
MSSQL: <a class="moz-txt-link-freetext" href="http://msdn.microsoft.com/en-us/library/ms186778.aspx">http://msdn.microsoft.com/en-us/library/ms186778.aspx</a><br>
MySQL: <a class="moz-txt-link-freetext" href="http://dev.mysql.com/doc/refman/5.0/en/information-schema.html">http://dev.mysql.com/doc/refman/5.0/en/information-schema.html</a><br>
PostgreSQL:
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/9.0/interactive/information-schema.html">http://www.postgresql.org/docs/9.0/interactive/information-schema.html</a><br>
<br>
<blockquote cite="mid:4F8FBF39.50502@gmail.com" type="cite">
<pre wrap=""> and functionality (e.g. just getting a list of tables instead
of always having to run queries might be nice functionality).
Because documentation is lacking, I propose annotating sqldb.pp to the
effect that the Interbase/Firebird implementation is the reference
implementation.
Then in the GetSchemaInfoSQL function in ibconnection.pp, indicate what
the queries do, and what they return (refer to column names being
similar or the same as information_schema in SQL ISO standard, but has
deviations).
Also indicate rec_no refers to a unique database-specific identifier -
if available - that can be used in further querying the metadata (e.g.
object_id in MS SQL server, or the id columns in Firebird rdb$....
system tables).
Of course, further comments/suggestions/flames welcome ;)
Thanks a lot Laco & Michael,
</pre>
</blockquote>
-Laco.<br>
<br>
</body>
</html>