Seperation of identifers from underlying objects [CORE5551]

[firebird-automations]

Submitted by: Howard Johnson (jambo)

Hi,

I think data base identifiers need to be independent from the underlying database. In other words, a database users should be able to easily rename things as part of development.

However I noticed that I could not rename my key field in LibreOffice. So then I submitted a bug to them (https://bugs.documentfoundation.org/show_bug.cgi?id=108068), but it turns out that Firebird lacks this important functionality.

Please read my comments in that bug, and thanks for your work on Firebird.

[firebird-automations]

Commented by: Lionel Elie Mamane (lmamane)

To be more clear, Howard would like that the rename of a column that is part of the primary key of the table, or another key, constraint, trigger, stored procedure or view would succeed.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Howard and Lionel,

Firebird is a SQL standard compliant database, as such it follows/implements the rules regarding the handling of database object names as per that standard.

The standard defines the identifier as the "name", so the simple renaming of objects is not supported.

[firebird-automations]

Commented by: @dyemanov

I agree renaming could be a handy feature, but it's not so trivial to implement.

Speaking about the SQL spec, this is what I see in the SQL2011 draft:
"A RENAME TABLE statement has been strongly desired for a very long time and any users will be expecting to see it in SQL3."
so it seems they are thinking in this direction.

Anyway, we already have CORE2486 and CORE1960, so this ticket looks like a duplicate.

[firebird-automations]

Commented by: Lionel Elie Mamane (lmamane)

CORE2486 is about renaming a whole table.
CORE1960 is about renaming an index.

This one is about renaming columns, even when other objects depend on them.

So they don't strictly look like duplicates to me, although I imagine there can be at least some overlap in the implementation of them.

I imagine very well it is not easy to implement in all cases. From a user POV, "the other DBMS allow it", at least in some (easier?) cases. E.g. the column is used only in the primary key, or only in primary key and indexes, ...

[firebird-automations]

Commented by: @AlexPeshkoff

In sql2008 I see the following:

11.12 <alter column definition>
Function Change a column and its definition.
Format <alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column action>
<alter column action> ::=
<set column default clause> |
<drop column default clause> |
<add column scope clause> |
<drop column scope clause> |
<alter column data type clause> |
<alter identity column specification>

I.e. even existing feature making it possible to rename regular column (ALTER TABLE t1 ALTER fold TO fnew) in an extension of standard. Telling true I would better wait for new standard edition - specially taking into an account that they seem to think about it.

[firebird-automations]

Commented by: Howard Johnson (jambo)

Please forget the standards for a moment. And forgive me if what I'm about to suggest you're already ahead of me on. What I am suggesting is an abstraction:

1) Every human readable text string that in some way identifies data in a database should be able to be easily renamed. This includes Table names, Field names, Index names, Query, Form and Report names etc.

2) When this renaming is done, it should be done in one, and only one place, and then all of the associations with this older name are re-directed and attached to the new name.

3) In particular, copying huge tables in order to accomplish this is not an acceptable or reasonable method, especially when we know that there is a much better way.

4) In the future the idea is that this one change can change things globally, so that extensive design rework is not required to propagate the name change.

In other words a name is nothing more than an abstraction pointing to the underlying thing in the database.

As an analogy, it would seem strange if on Monday we defined a variable in our C++ code, but then on Tuesday we couldn't change that name without some lengthy process. We'll that's what we've got at the moment, and what I'm suggesting is to use something like a dictionary of identifiers that point to the underling objects so that the name in the dictionary can be changed without disturbing the underlying database.

Let me give you one further idea. We need the ability to change a table column's name for example, and then have that change be automatically propagated through any associated Queries, Forms, Reports, etc.

The point is that databases grow and change as they mature, and they can become huge and complex. You might start with two tables and a field named ID, but then upgrade it to Product_ID to distinguish it from other IDs in additional new tables, and later upgrade it again to Product_Group_ID, etc.

This sort of change is desired often in database design, and it needs to be relatively easy to do. If you have 2 tables, with 10 records in each this is not an issue. But when you have 100 tables or more, and some of them have thousands of records in them, this becomes a real design wet blanket to have to go through a complicated process just to rename something.

Hope this makes sense. I suspect that once you get started it might be easier than you think, but yes, I'm sure it is involved to change your current code, especially if this abstraction was not allowed for when the code was originally designed.

Now back to the standards. Yes this has to fit with them, but consider that even if the standards are lacking in this area in places, if you get your underlying system to be forward thinking now, then as the standards evolve to fill in holes you are already ahead of the game.

As I'm sure you already know, good names make good clean designs where it's easy to see what is going on and thus easier to maintain, but it is not always easy to know what a name should be when you start out.

Thanks for your work on this.

[firebird-automations]

Commented by: @asfernandes

Looks like you think only in your "limited use cases".

What DBMS is doing good in this area?

There are many and many things involved. There are stored procedures/functions/packages stored in the database, there are constraints expressions stored in the database.

There are the same things stored in the filesystem, know as the database' source code versioned, if one is using good practices.

I agree that databases should not be so difficult to change. See my topic "Virtual metdata" in fb-devel. The way to do changes correctly is if one issue many DDL commands in the same transaction that maintains the database objects consistent in the commit.

Altering database objects and replicate things directly in the database is like altering executable binary in production, if possible. It will be lost on next deploy.

Let database developer tools do they job (refactor database source code), and let DBMS engine do its job (apply the changes).

[firebird-automations]

Commented by: Howard Johnson (jambo)

Adriano,

I agree that this is only part of the databases problem. But it needs to provide the renaming services to applications. The other half of the job is the application's, which is where the DDL transactions you mention need to come from.

If you think about it a minute, you'll realize that it's like the Internet structure where you have domain names and IP addresses. Keep a dictionary of names and attach them to ID numbers. Then use the ID numbers internally. If the Internet ran on names, (domain names), it would be very slow and missing the abstraction layer it would be much less flexible. But with IP addresses under the covers lots of flexibility and speed is achieved.

So this might well also make Firebird run faster, because it's much quicker to deal with one 16 bit number than a text string, and also that must suffer garbage collection from time to time.

The other advantage is that it allows greater flexibility and safety in what can be in those text strings (the identifiers). Now the text strings can easily accommodate all sorts of characters, and the parsers are fewer so less likely to have coding bugs.

So convert your guts to operate on ID's rather than text strings. And only use the text strings for the external human interface.

Microsoft access rewrote their Jet engine many years ago to get this right. Now when you rename a table or column in Access, it gets updated in your report without you doing anything further. And I'm not talking about the most recent Access. I use 2003 and I think they fixed it in the version before that one.

Thanks again for looking at this.

[firebird-automations]

Commented by: @asfernandes

Firebird already has "rename column" syntax with strong limitations, and already use ids (not for all things).

And this does not solve the full problem.

Imagine, drop column, create again with same name, will use different ids when the user just wanted the original column to be cleared.

There is two ways to solve the problem of allowing DDL changes correctly :
- Invalidate (breaking) everything that's incompatible with the change, until user fix
- Force everything to be consistent in the same transaction

I'm all for the second option.