4

In a pretty agile development of a project, the most problematic thing for frequent updates is refactoring of table and column naming.

When we work on code that is not high-risk, we can just add a column or a table in the Production database and keep on working with the real data - omitting the hassle of update scripts and updating our TestDb with the latest data from the ProductionDb.

Just add the column, add a default value to it. Nothing really scary in this case. Everything keeps working, no downtime, no hassle of Update scripts.

Problem

Sometimes we want to refactor column names, because they were not choses very well on afterthought.

I was wondering if something like this is possible:

  • Give a (semi)permanent 'second name' to a column (or table)
  • Production server talks to the OldName
  • Development talks to the NewName
  • When the new version is deployed we can remove the OldName alias

Result: The column/table name is refactored without any downtime, or update scripts.

Is this possible? We use Entity Framework and SQL Azure.

Dirk Boer
  • 8,522
  • 13
  • 63
  • 111

2 Answers2

2

A bit naive perhaps:

Alternative name for table:

create view new_table_name as 
select * 
from old_table_name

Alternative name for column:

create view new_table_name as 
select x.*, x.old_column_name as new_column_name 
from old_table_name x

The following statements are valid:

update old_table_name
set old_column_name = value
where key = key_value

update new_table_name
set old_column_name = value
where key = key_value

update new_table_name
set new_column_name = value
where key = key_value

See also: How to rename SQL table column name(s) and not break stuff

Community
  • 1
  • 1
Adrian
  • 6,013
  • 10
  • 47
  • 68
  • hmm in theorr an interesting approach. Does all statements work the same on the view? Also stuff like deleting, joining and inserting? – Dirk Boer Aug 15 '14 at 16:17
  • And I have to take a look if Entity Framework can handle this - meaning: ignore the view and treat it as a normal table. – Dirk Boer Aug 15 '14 at 16:17
  • This does **not** work with every database! It requires the datbase to support updatable views. My current problem is that I wanted to use H2 for unit testing with JPA, but it does not support updatable views (at least not in a convenient way). – SebastianH Feb 28 '16 at 18:40
0

You won't be able to create an alias for the column or table name, if you wan't to update the DB col/table name at some stage you will need to run a script.

Although in your code via EF you could just update the property name for that column or table and keep the mapping to the original DB name. This means at least your objects will have the new/better name.

swuk
  • 883
  • 6
  • 14
  • That's true. But at a certain point you have to bite the bullet and update your database :/ otherwise you'll end up with even a bigger mess. A feature where you could temporarily have two names for objects would make refactoring much more painless. – Dirk Boer Aug 15 '14 at 16:15
  • Not sure about the two names idea. I would concentrate efforts on having a test DB and look into EF migrations or do them manually, working on a production DB ends up in trouble most of the time. – swuk Aug 15 '14 at 22:54