I have started using the Description and Attributes fields of Tables.

They are tremendously useful.

There are contexts where it is useful to change them while the database is in use. At present this is not possible, as they are a "tight" part of the table structure, any ALTER statement requires a total lock.

Is there any way this could be relaxed so that these elements of a table could be altered more freely.

To give a simple example I want to give users the ability to change the Description field of the table to provide them with a clear understandable description of the table. If they do this from within the App (the easy, obvious place to do it) I have to struggle with many workarounds to get them access to this field.

I realise I could just create a "TableDescriptions" table and use this, but that is not such an elegant solution.

>I realise I could just create a "TableDescriptions" table and use this, but that is not such an elegant solution.<

I personally would use a TableDescriptions table and *not allow* the user to manipulate the table definitions which usually means they need administrator rights. (Yikes!)

A TableDescriptions table would allow for different languages and a different description depending on the Role/UserName. You can also prevent Guest users from modifying the description.

Barry

Adam


I have almost finished developing a user editable help system (undocumented naturally). It uses WPTools, ElevateDB, ElTreeLite (freebie) and some of my homebrew controls.

It has three layers:
- developer
- company
- user

The idea is that the user can press F1 and it brings up help for whatever control he's on. The help info is then initially presented at the lowest level (user) for which text exists but they can toggle between all three and edit if they have the right permissions.

If you want a copy let me know and I'll email it over.


Roy Lambert

Barry

>>I personally would use a TableDescriptions table and *not allow* the
>>user to manipulate the table definitions which usually means they
>>need administrator rights. (Yikes!)

I guess exactly what I am asking for is to have some reduction in the level of admin rights / control of the DB needed to modify these parts of a table definition.

I don't want ordinary users to be able to modify the COLUMNS etc. (you are right "Yikes"!), but I think it _would_ be useful for them to be able to modify the DESCRIPTION fields.

It is just a really useful place-holder for storing user-defined text related to TABLEs & COLUMNs, as you say if a user is non-English-speaking the user can then write their own version of a Table or Column Name, I don't have to get involved as a programmer.

I can create a TableDefinitions and ColumnDefinitions table, but then when actual Table-names or Column-naes change I then have to also change the "name" fields in the TableDefinition tables, otherwise the link is lost. It is just tidier to be able to put the user-entered data right "in" the table structure to begin with.

Adam,

<< There are contexts where it is useful to change them while the database
is in use. At present this is not possible, as they are a "tight" part of
the table structure, any ALTER statement requires a total lock.

Is there any way this could be relaxed so that these elements of a table
could be altered more freely. >>

Yes, it's possible.  But, on to what Barry mentions, I'm not entirely sure
that It would be possible to relax the user security on table alterations.
That part I'll have to think about further.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,
<<Yes, it's possible.  But, on to what Barry mentions, I'm not entirely sure
that It would be possible to relax the user security on table alterations.
That part I'll have to think about further.>>

Doesn't altering the table also put a lock on all the tables in the database because it is updating the catalog? How many concurrent users can you have altering tables? I just don't see it being feasible to allow the end user to alter the table (or other objects) to update descriptions.

It is sooo much safer and efficient to have his own set of "description" tables (or just one table) and import the new table names, column names, view names etc. from the Information tables whenever the schema changes. All it takes is a table join or sub-select to find the new objects that need to be added to or removed from the Description table. This gives the developer the ability to handle different languages and even override the default description for certain companies or users.

Barry

Barry,

<< Doesn't altering the table also put a lock on all the tables in the
database because it is updating the catalog? >>

No, it simply causes an exclusive open lock on the table, and a (very quick)
catalog write lock/unlock cycle during the actual catalog update.

<< How many concurrent users can you have altering tables? I just don't see
it being feasible to allow the end user to alter the table (or other
objects) to update descriptions. >>

There are limits, yes.  But, the updates are very quick, and I don't suspect
that the descriptions, etc. are altered that often.

Tim Young
Elevate Software
www.elevatesoft.com