<!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:4F8FE418.2040205@gmail.com" type="cite">
<pre wrap="">On 19-4-2012 11:14, LacaK wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Reinier Olislagers wrote / napísal(a):
</pre>
<blockquote type="cite">
<pre wrap="">On 18-4-2012 8:27, LacaK wrote:
</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>
<pre wrap="">may be, but I think, that IsPrimary, IsUnigue, IsAscending would better
names
</pre>
</blockquote>
<pre wrap=""><!---->;) we can fight^H^H^H talk about that ;) The reason why I chose these is
that they match the %object%_%property% naming convention in the other
queries...
</pre>
</blockquote>
ok, my approach is when not implemented then be compatible with Delphi,
but also your proposal is acceptable for me ;-)<br>
<br>
<blockquote cite="mid:4F8FE418.2040205@gmail.com" type="cite">
<pre wrap="">Also a problem is that you can have both a unique constraint and a
unique index... Of course, implementing the constraint would probably be
done by the index... but an index without a constraint could also be
possible.
Is this function meant to show all indexes or only indexes meant for
constraints?</pre>
</blockquote>
As far as I can test in Delphi also indexes are returned (then column
ConstraintName is empty)<br>
<br>
<blockquote cite="mid:4F8FE418.2040205@gmail.com" type="cite">
<pre wrap=""> The presence of IsAscending would suggest it is meant for
all indexes as there is no such thing as an ascending constraint...
</pre>
<blockquote type="cite">
<blockquote 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>
<pre wrap="">we do not must add things, what nobody need ;-)
</pre>
</blockquote>
<pre wrap=""><!---->Agreed ;)
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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>
<pre wrap="">Yes IMO there is worth add only such things, which are usable/doable at
least in 2-3 sql connectors
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.
</pre>
</blockquote>
<pre wrap=""><!---->It will certainly be doable. I know enough about Sybase, MS SQL Server,
Firebird that I think it will quite likely work.
PostgreSQL, Oracle probably also. Mysql: could well be.
Also, I propose to return NULL in case a column value is not supported
by the database driver... this will allow column order to remain and
future improvements to be made
</pre>
</blockquote>
ok<br>
<br>
<blockquote cite="mid:4F8FE418.2040205@gmail.com" type="cite">
<pre wrap="">BTW, IMO, complicated queries does not matter that much as long as it is
annotated why the query was chosen (i.e. link to db reference, including
version number).
</pre>
<blockquote type="cite">
<pre wrap="">Note INFORMATION_SCHEMA are supported by:
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>
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>
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>
</pre>
</blockquote>
<pre wrap=""><!---->Yep.
Firebird, Sybase and Oracle have system catalogs/system views/system
tables that provide similar info (as the ones you mentioned probably
also have - don't know about MySQL).
Handy comparison between proprietary tables and information_schema approach:
Firebird:
<a class="moz-txt-link-freetext" href="http://www.alberton.info/firebird_sql_meta_info.html">http://www.alberton.info/firebird_sql_meta_info.html</a>
MS SQL:
<a class="moz-txt-link-freetext" href="http://www.alberton.info/sql_server_meta_info.html">http://www.alberton.info/sql_server_meta_info.html</a>
Oracle:
<a class="moz-txt-link-freetext" href="http://www.alberton.info/oracle_meta_info.html">http://www.alberton.info/oracle_meta_info.html</a>
PostgreSQL:
<a class="moz-txt-link-freetext" href="http://www.alberton.info/postgresql_meta_info.html">http://www.alberton.info/postgresql_meta_info.html</a>
One thing I noticed in the information_schema approach: you won't get
info about objects you don't have access to.
So you won't get procedures you can't execute etc.
Don't know how that works with the current implementation (i.e.
Delphi+DbExpress)? FPC Firebird connector will happily return ALL
objects, regardless of permissions, if I understand the query correctly.
</pre>
</blockquote>
I do not think, that we must care about permissions<br>
<br>
<blockquote cite="mid:4F8FE418.2040205@gmail.com" type="cite">
<pre wrap="">
I don't think this is a problem though as having info on something you
have no permissions to doesn't seem like a useful concept.
If agreed, sometime in future I'll probably rewrite the MS SQL queries
to use INFORMATION_SCHEMA... but will have to use existing approach
(with the sysobjects table) for Sybase, as it doesn't support
information_schema...
</pre>
</blockquote>
Exactly. Due to compatibilty with Sybase we must use sys* tables<br>
(drawback is that, you need CatalogName and SchemaName, which are not
in sysobjects, so you must use DB_NAME() and join to sysusers)<br>
<br>
<blockquote cite="mid:4F8FE418.2040205@gmail.com" type="cite">
<pre wrap="">
In fact, currently GetSchemaInfoSQL returns an SMetadataUnavailable
error inTSQLConnection; I might be able to write up the
information_schema approach which would be directly usable by compatible
databases... (Excepting the package stuff... I'll just return the error
there).
</pre>
</blockquote>
Yes good point.<br>
<br>
-Laco.<br>
<br>
</body>
</html>