OK well your argument basically appears to be "table reflection is
dumb, nobody should ever use it" and therefore "information_schema
shouldnt care about supporting it".
regarding reflection, as it turns out i am not a huge fan of it
either, as its true constraints can be turned off in databases, an
application should have its own concept of the "logical" schema of
data, etc., but I can certainly understand the overwhelming desire
for this feature, particularly when scripting up (python *IS* a
scripting language, after all) something for testing or one-off
purposes.
anyway, this would support the notion that information_schema is just
a broken concept for the purposes of table reflection.
On Jun 6, 2006, at 2:15 PM, William K. Volkman wrote:
> On Tue, 2006-06-06 at 10:43, Michael Bayer wrote:
>> On Jun 5, 2006, at 11:50 PM, William K. Volkman wrote:
>>
>>> Users of the tables, particularly web applications, are
>>> only granted the minimum SQL privileges necessary for their
>>> tasks. The capability to execute DDL operations would
>>> specifically be prohibited. It is only in small personal
>>> type projects or, at most, initial development that that type
>>> of interaction would occur. For most deployed applications
>>> the "db.create(table)" would/should never (for security
>>> reasons) be possible.
>>
>> you understand that the issue is not involving DDL operations at all,
>> it merely involves that a user, who has select access to a particular
>> table, should be able to view information about the columns and
>> constraints that comprise that table, right ?
>
> Actually, from a security stand point, possibly/probably not.
>> From the deployed application stand point, being able to
> query table attributes also doesn't make much sense.
> Only from the generic database interface application does
> it make sense, and those types of applications tend to
> be niche ones. Once an application is designed, the tables
> and columns don't change much, why do they need to be able
> to queried? If suddenly the arglbargle column is added
> to a table would the deployed application really be able
> to do something meaningful with it?
>
>> it makes no sense that
>> information_schema will reveal the names and datatypes of a
>> particular table's columns to a user, but not its primary key
>> constraints. are you saying this is by design ?
>
> In theory the primary key and other constraints should be of
> no interest to most application programs. The fact that
> the database engine can change them at any time, even during
> the duration of a long running application, applications
> are required to not depend on that information.
> The fact that they've gone to the trouble to constrain certain
> fields to only be visible to the table owner
> indicates that they've done it on purpose. I.E. As Owner:
>
> sat=# select column_name, column_default
> from information_schema.columns
> where table_name = 'departments';
> column_name | column_default
> -----------------
> +-------------------------------------------------------
> department_id | nextval
> ('public.departments_department_id_seq'::text)
> department_name |
>
> As a non-owner:
> column_name | column_default
> -----------------+----------------
> department_id |
> department_name |
>
> One reasoning probably lies in commercial deployment of the
> database. Don't permit the users to reverse engineer the
> database or give out information that could be used to break
> it.
>
> btw. I'm playing devil's advocate here, from a agile programming
> perspective we're interested in these things. However stepping
> back from a incremental development perspective to the finished
> application one, there is no longer the need to dynamically
> query this information. Plus we have the problem of when
> to query and how often to update it.
>
> Consider this, if my application at start up retrieves the
> default values for a column and then explicitly executes
> those defaults instead of letting the DB backend do it,
> then in a long running application the DBA has lost capability.
> Perhaps the default value for a column changes based on
> time of day.
>
> Web applications exacerbate the problem, something that
> starts up, does something, then shuts down. Do we really
> want/need the overhead of fetching table and column
> information each time?
>
> Cheers,
> William.
>
>
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
