Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Ability to rename a table [CORE2486] #2899

Open
firebird-automations opened this issue May 31, 2009 · 25 comments
Open

Ability to rename a table [CORE2486] #2899

firebird-automations opened this issue May 31, 2009 · 25 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Ido Kanner (ik_5)

Depends on CORE1015
Is duplicated by CORE3040

Votes: 15

At the moment you can not rename a table in Firebird without starting to play with system tables.

Most databases have a proper syntax for renaming a table name.

I suggest something such as:

ALTER TABLE <table> RENAME TABLE <new name>

All capitals are part of the syntax, and it continue the way you rename columns today in Firebird.
The command should change the table name and all of the constrains that store it's name such as FK.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

issuetype: Improvement [ 4 ] => New Feature [ 2 ]

description: At the moment you can not rename a table in Firebird without starting to play with system tables.

Most databases have a proper syntax for renaming a table name.

I suggest something such as:

ALTER TABLE <table> RENAME TABLE <new name>

All capitals are part of the syntax, and it continue the way you rename columns today in Firebird.
The command should change the table name and al of the constrains that store it's name such as FK.

=>

At the moment you can not rename a table in Firebird without starting to play with system tables.

Most databases have a proper syntax for renaming a table name.

I suggest something such as:

ALTER TABLE <table> RENAME TABLE <new name>

All capitals are part of the syntax, and it continue the way you rename columns today in Firebird.
The command should change the table name and all of the constrains that store it's name such as FK.

summary: Rename table name => Ability to rename a table

environment: all supported systems =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

I was always afraid of posting this myself, but whenever I refactor a db I wishful-think about it :)

I think you also need invalidation of stored procs and triggers in order to have this (there's a request for that too), otherwise the implementation of RENAME would have to change the stored sql code, which is somehting I know firebird doesn't do by principle.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

1. The only conditions under which a table could be renamed is if no Trigger, Procedure or Computed Column were dependent on it, since they contain table references in the source text.

Accordingly, at that point it would very simple to define a new table and the copy the data. So, it this really required?

2. What other database engines support this feature? How is it implemented?

3. Unless the SQL Standard supports such a feature, and in the absence of a compelling list of other databases engine which support same, I do not believe that Firebird should implement such a non-conformant feature

@firebird-automations
Copy link
Collaborator Author

Commented by: Ido Kanner (ik_5)

1. Well partly agree, Yes you would require to rename it regardless, but coping data is an extra task IMHO that can take a lot more time then renaming a table even today.

2. Let's start from the database list:

A. PostgreSQL - http://www.postgresql.org/docs/8.3/static/sql-altertable.html
B. MySQL - http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
C. SQLite - http://www.sqlite.org/lang_altertable.html
D. HSQLDB - http://hsqldb.org/doc/guide/ch09.html

3. There is no SQL standard for it as far as I know, but it sure help a lot.

4. Why does the Firebird developers are so against it ? If there is an answer about it, I sure it will also help a lot more to understand this. Even to add it to firebirdsql can solve a lot of head acks regarding the issue.

Anyway, thank you for your answers.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Ido,

Thanks for your detailed reply, very appreciated.

Please do not take my comments about the case as being any suggestion that "the Firebird developers are so against it". They are simply my personal comments/observations. Until such time as this case is closed with an equivalent "won't fix" status, the comments are simply that, comments.

I find it interesting, that while the documentation references does describe the command syntax:
- the PostgreSQL, MySQL and HSSQL documentation doesn't make reference to the *impact* of the rename on existing triggers/procedures.
- the SQLite documentation suggests that existing triggers/procedures are silently "orphaned" -- not the most desiable way to handle such an operation.

While I can understand why a "rename" syntax might be a good thing, the impact of such an operation on the related/dependent structures/SQL is much too complex to be done in any of the ways described above.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

Sean,

1) If stored proc/trigger/check/computed-by invalidation would be implemented, it would only be just a matter of invalidating those, just like Oracle does. A wider perspective over a particular problem doesn't hurt sometimes.

2) It doesn't affect the validity of the request in the bit.

3) I struggled but ultimately failed to detect even the slightest trace of logic in that statement, unless, of course, alienating users is your primary goal in this project -- then you might be on to something.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue depends on CORE1015 [ CORE1015 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Cosmin"

"1) If stored proc/trigger/check/computed-by invalidation would be implemented, it would only be just a matter of invalidating those, just like Oracle does. A wider perspective over a particular problem doesn't hurt sometimes. "

- The original post makes no mention of metadata "invalidation", which Firebird doesn't currently support. It simply describes

"2) It doesn't affect the validity of the request in the bit."

- I didn't say that it did, I was looking for some details of other engines which supported it.

- whether other databases implement the feature and how their design compares to Firebird does matter, in so far as understanding how they might have; implemented the feature, constrained its application and whether the engine is a simple DBMS with a SQL front-end or a true engine.

"3) I struggled but ultimately failed to detect even the slightest trace of logic in that statement, unless, of course, alienating users is your primary goal in this project -- then you might be on to something. "

If that was my goal, I would simply follow your example. Did you read my 2nd reply to Ido?

I meant that a SQL standard syntax would help a lot in terms of worrying about implementation and possible future changes. The fact there is no SQL standard simply means that the implementation is the feature would be "non-compliant", which means that the syntax needs to be carefully considered -- the Project has spent a lot of time working on bringing the engine into compliance and into adopting compliant feature. This isn't a "home project" that invents things on a "whim", we need to have a broader review/consideration.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

Since it's too late for water, how'bout more gas: your message got out of my lexer like this:

You do not believe that firebird should implement such a non-conformant feature, unless 1) someone presents you with a compelling list (??) of other database engines which supports the feature (??), and/or 2) the standard specifies it.

Now, I strongly believe that:
1) this is your standard answer to most requests on the tracker and it usually comes with the agility I learned to respect.
2) a non-standard feature doesn't make a database any less standards-compliant -- it's the standard features that have to deal with that.
3) the usefulness of a feature has nothing to do with whether it is present or not in other engines or standards on this universe.
4) you have good intentions.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ido Kanner (ik_5)

Please do not fight over this.

I do wish to better understand why such feature should not be implemented.

The cons that I can think of are:
A. You break your old code, and it's not an easy task to refactor a database.
B. It's not a standard complaint -> hence if tomorrow there will be SQL 2020 (for example) it will break such feature.
C. Design a database is a very strict task and should be made only after careful understanding of numerous things variant from the needs.

The pros that I can think of are:
A. It's easier then creating a new table and copy data (you still need to change the name of the table in everything that uses it)
B. More compliance with most used DBMS that exists today (Oracle, MySQL, PostgreSQL, SQLight ...)
C. It can start a new set of tools for better administrating the database.
D. You can still do this task today, but it will only make it easier to achieve the same task.

If there are other pros and cons it is a good idea to add them.
I really do not know if adding such feature is a good practice to a database, however at the end it makes things easier to handle, at least in the way it feels.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Cosmin:

"...your message got out of my lexer like this:"

You need to update your lexer.

"Now, I strongly believe that:
1) this is your standard answer to most requests on the tracker and it usually comes with the agility I learned to respect.
2) a non-standard feature doesn't make a database any less standards-compliant -- it's the standard features that have to deal with that.
3) the usefulness of a feature has nothing to do with whether it is present or not in other engines or standards on this universe."

1 - most cases/feature requests are "off the top of my head, this seems to be a good thing" type of submissions, which are rooted in concepts fostered by file based databases with simple SQL front-ends, and lack a sense of what referential integrity is about and it's implications for databases.

I ask for "justification" because the requests need to be thought through... and the "requester" needs to be part of that process.

2 - I never said that a non-standard feature makes the database non-compliant -- again, update your lexer.

3 - True, but the usefullness of a feature has everything to do with context. The fact that another engine which dooesn't have triggers/stored procedures have implemented the feature would mean absolutely nothing, since the engine has none of the feature which makes Firebird, Firebird.

"4) you have good intentions."

As do you.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Ido,

"I do wish to better understand why such feature should not be implemented."

I have not said that it shouldn't be implemented, just that there are issues to be carefully considered.

This feature could only be implemented after support for "invalidating" metadata objects was added to the Firebird engine, since attempting to replace all table references in the source 'code' of the dependent objects would be much too risky to consider.

(This is how Oracle and Postgres have implemented the feature -- they mark all existing objects as invalid and require the developer to correct the references themselves.)

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

If that wasn't what you said, I'm ready to eat a Futurama movie popcorn action figurine. I'll even update my lexer, compiler, and other parts of my brain at your choosing :) ... but before I do, I find your 1 point to be an arrogant underestimation of your users... but again, an update of my lexer may (or may not) invalidate the above statement. we'll see after the operation.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Cosmin,

"I find your 1 point to be an arrogant underestimation of your users"

I have read and reviewed (edited many as well) *each and every entry* that has been logged to the project trackers, so my comment is not so much arogant but rather based on experience.

Further, I think it entirely appropriate to "play devil's advocate" and challenge feature requests.

@firebird-automations
Copy link
Collaborator Author

Commented by: Claudio Valderrama C. (robocop)

isql has nothing to do with this crazy request.

@firebird-automations
Copy link
Collaborator Author

Modified by: Claudio Valderrama C. (robocop)

Component: ISQL [ 10003 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE3040 [ CORE3040 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: F.D.Castel (fdcastel)

"it would only be just a matter of invalidating those, just like Oracle does"

Yes. Exactly as to make my car fly "it would only be just a matter of" attach two wings on it, "just like" a 747 does.

Please, let's keep Firebird free from that hell. Everyone who asks for this "feature" doesn't have any idea of the troubles it causes.

** NEVER DO allow any DDL command to put the database in a "invalid" state. NEVER! **

This is a wonderful thing of Firebird. From someone who already have enough of Oracle's "invalid metadata" headaches in production databases.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

>> ** NEVER DO allow any DDL command to put the database in a "invalid" state. NEVER! **

Firebird already does this by allowing NOT NULL columns with NULL values (which btw it's far more problematic), so judging by this standard alone it's not as wonderful as you think it is.

Now, I'm sorry that Oracle gives you headaches by invalidating metadata, but I'm sure it doesn't do that all by itself, but only as a reaction to you using the ALTER TABLE command to rename things. If you don't like the feature, why don't you just stop using it? Do you really need that feature out of Oracle because you just can't help using it? If you use it, how can you complain about it?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The argument re. NOT NULL vs NULL is not a good excuse, FWIW. And it's no longer valid starting with FB3.

@firebird-automations
Copy link
Collaborator Author

Commented by: F.D.Castel (fdcastel)

@cosmin: while I agree with you that NULL values on NOT NULL fields do left the DB on a "somewhat inconsistent" state, they at least doesn't throw exceptions on end users face. And, on worst case, they would only cause a problem on next backup/restore cycle.

There are dozens of ways to leave an Oracle database with invalid metadata, not only renaming tables. And, with Oracle, many times you are not the only one developing on it. Think about it. The "headaches" never were from things *I* have done...

Finally, I didn't ask to remove any feature from Oracle. I'm pleading to not bring a plethora of Oracle problems to Firebird.

@dmitry: great news from FB3 front! Thank you!

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

>> The "headaches" never were from things *I* have done...

Ah, but your use case then calls for a different feature. What you really want is the ability to GRANT renaming of tables to other users, so that you can remove this feature from the hands of irresponsible programmers -- which is basically what you just said: *you* yourself can happily leave with this feature, is *others* that can't handle it. Which is fine, that's what security features are for. But you can't justify depriving all Firebird programmers of a useful feature because of your particular situation, which, even if not an uncommon one, it's not universal either.

@firebird-automations
Copy link
Collaborator Author

Commented by: Pablo Carbonell (pablocar80)

Hello, the workaround suggested in the FAQ is to create a new table and copy all rows. This workaround doesn't consider that all foreign keys need to be adjusted to point into the new table. Renaming a table is very basic functionality that every developer expects to have available in a database engine. This issue currently has a status of trivial (misspelled word / cosmetic) and is however a critical missing functionality.

@krilbe
Copy link

krilbe commented Aug 17, 2022

Are there ANY plans to implement this feature?

I am now in a situation where I need to rename a table containing about 30 million records and three indices. As you can imagine this does take "a while" using the create + insert/select from + drop workaround. And yes, the rename is required because of real world business model changes, not sloppy DB design.

Apart from all the rather non-constructive discussions above, what is really the problem with implementing this feature? Are there technical complications in the Firebird codebase that make it very difficult, risky or otherwise unreasonable?

@aafemt
Copy link
Contributor

aafemt commented Aug 17, 2022

In stored BLR tables are referenced by name which makes renaming impossible in the most cases because of dependencies.

Even if table naming is really a part of business logic (which I doubt) the creation of a view with desired name is a simple workaround.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants