Hi all,
I noticed that if you add, delete, rename or change the type,
collation or precision of a composite type attribute, it deletes all
of them then adds them all back in. Obviously attribute additions,
deletions and modifications may only occur for types under PostgreSQL
9.1, but I thought it a bit extreme to actually drop all types if
you're adding a new one in, or just removing one. Also, if you rename
a type, it drops all attributes again then re-adds them, just to have
an attribute with a different name.
So I've attached a patch which "does the right thing". Take the
example of the following type:
CREATE TYPE bark AS
(one text,
two text,
three text,
four text,
five text);
Say we wanted to remove "two", change the type of three to uuid,
rename four to forty and add an extra text type of six.
Normally we'd just get:
ALTER TYPE bark DROP ATTRIBUTE one;
ALTER TYPE bark DROP ATTRIBUTE two;
ALTER TYPE bark DROP ATTRIBUTE three;
ALTER TYPE bark DROP ATTRIBUTE four;
ALTER TYPE bark DROP ATTRIBUTE five;
ALTER TYPE bark ADD ATTRIBUTE one text;
ALTER TYPE bark ADD ATTRIBUTE three text;
ALTER TYPE bark ADD ATTRIBUTE forty uuid;
ALTER TYPE bark ADD ATTRIBUTE five text;
ALTER TYPE bark ADD ATTRIBUTE six uuid;
With these changes we'd now get:
ALTER TYPE bark DROP ATTRIBUTE two;
ALTER TYPE bark RENAME ATTRIBUTE four TO forty;
ALTER TYPE bark ADD ATTRIBUTE six text;
.. except now those are also nicely indented to be more readable for
types with long schemas/type names.
e.g.
ALTER TYPE long_schema_name.quite_a_long_table_name
ADD ATTRIBUTE "suspiciously long attribute name"?
It also fixes a bug whereby if you have a precision specified, the
word COLLATE mysteriously appears after the type whether or not you
have a collation assigned because the collation condition was based on
the precision being present for some reason. And if you actually
assigned a collation for a valid data type, it wouldn't appear at all,
so that's fixed too.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/pgadmin/dlg/dlgType.cpp b/pgadmin/dlg/dlgType.cpp
index 5f3a36c..b951d7b 100644
--- a/pgadmin/dlg/dlgType.cpp
+++ b/pgadmin/dlg/dlgType.cpp
@@ -285,6 +285,7 @@ int dlgType::Go(bool modal)
memberLengths.Add(typeLength);
memberPrecisions.Add(typePrecision);
memberCollations.Add(elements.Item(i + 2));
+ memberOriginalNames.Add(elements.Item(i));
}
cbDatatype->Enable(changeok);
@@ -533,6 +534,7 @@ void dlgType::OnMemberAdd(wxCommandEvent &ev)
memberLengths.Add(length);
memberPrecisions.Add(precision);
memberCollations.Add(collation);
+ memberOriginalNames.Add(wxEmptyString);
}
CheckChange();
@@ -574,10 +576,12 @@ void dlgType::OnMemberChange(wxCommandEvent &ev)
memberLengths.Insert(length, pos);
memberPrecisions.Insert(precision, pos);
memberCollations.Insert(collation, pos);
+// memberOriginalNames.Insert(name, pos);
memberTypes.RemoveAt(pos + 1);
memberLengths.RemoveAt(pos + 1);
memberPrecisions.RemoveAt(pos + 1);
memberCollations.RemoveAt(pos + 1);
+// memberOriginalNames.RemoveAt(pos + 1);
}
}
@@ -596,6 +600,7 @@ void dlgType::OnMemberRemove(wxCommandEvent &ev)
memberLengths.RemoveAt(pos);
memberPrecisions.RemoveAt(pos);
memberCollations.RemoveAt(pos);
+ memberOriginalNames.RemoveAt(pos);
}
CheckChange();
}
@@ -833,7 +838,7 @@ wxString dlgType::GetFullTypeName(int type)
typname += wxT(",") + memberPrecisions.Item(type);
typname += wxT(")");
}
- if (!memberPrecisions.Item(type).IsEmpty() && memberPrecisions.Item(type) != wxT("pg_catalog.\"default\""))
+ if (!memberCollations.Item(type).IsEmpty() && memberCollations.Item(type) != wxT("pg_catalog.\"default\""))
typname += wxT(" COLLATE ") + memberCollations.Item(type);
return typname;
@@ -842,46 +847,78 @@ wxString dlgType::GetFullTypeName(int type)
wxString dlgType::GetSqlForTypes()
{
wxString sql = wxEmptyString;
- wxString old_name, old_type, old_collation, new_name, new_type, new_collation;
+ wxString objname, old_name, old_type, old_collation, new_name, new_type, new_collation, original_name;
wxArrayString elements = type->GetTypesArray();
- bool modified = lstMembers->GetItemCount() * 3 != (int)elements.GetCount();
- size_t i;
+ size_t newindex;
+ size_t oldindex = 0;
+ int hold = 0;
+ objname = schema->GetQuotedPrefix() + qtIdent(GetName());
- // Check if there is a change
- for (int i = 0 ; i < lstMembers->GetItemCount() && !modified; i++)
+ for (newindex = 0 ; newindex < lstMembers->GetItemCount() ; newindex = newindex + 1 - hold)
{
- old_name = elements.Item(i * 3);
- old_type = elements.Item(i * 3 + 1);
- old_collation = elements.Item(i * 3 + 2);
- new_name = lstMembers->GetItemText(i);
- new_type = GetFullTypeName(i);
- new_collation = memberCollations.Item(i);
- modified = modified || old_name != new_name
- || old_type != new_type
- || old_collation != new_collation;
- }
+ // this will decide whether we progress to the next new item,
+ // or whether we need to continue checking the old list first
+ hold = 0;
- if (modified)
- {
- // Drop all old attributes
- for (i = 0 ; i < elements.GetCount() ; i += 3)
+ // these are a copy of the list before any changes
+ if (elements.GetCount() >= (oldindex * 3) + 3)
{
- old_name = elements.Item(i);
- sql += wxT("ALTER TYPE ") + type->GetName() + wxT("\n DROP ATTRIBUTE ") + old_name + wxT(";\n");
+ old_name = elements.Item(oldindex * 3);
+ old_type = elements.Item(oldindex * 3 + 1);
+ old_collation = elements.Item(oldindex * 3 + 2);
}
+ // this is the original name of the type before editing
+ original_name = memberOriginalNames.Item(newindex);
- // Add all new attributes
- for (int i = 0 ; i < lstMembers->GetItemCount() ; i++)
+ new_name = lstMembers->GetItemText(newindex);
+ new_type = GetFullTypeName(newindex);
+ new_collation = memberCollations.Item(newindex);
+
+ if (connection->BackendMinimumVersion(9, 1) && !original_name.IsEmpty()
+ && original_name != new_name && original_name == old_name
+ && old_type == new_type && old_collation == new_collation)
{
- new_name = lstMembers->GetItemText(i);
- new_type = GetFullTypeName(i);
- new_collation = memberCollations.Item(i);
- sql += wxT("ALTER TYPE ") + type->GetName() + wxT("\n ADD ATTRIBUTE ")
- + new_name + wxT(" ") + new_type;
- if (!new_collation.IsEmpty() && new_collation != wxT("pg_catalog.\"default\""))
- sql += wxT(" COLLATE ") + new_collation;
- sql += wxT(";\n");
+ // if this was originally in the list and the name has changed then rename it
+
+ sql += wxT("ALTER TYPE ") + objname + wxT("\n RENAME ATTRIBUTE ")
+ + qtIdent(original_name) + wxT(" TO ") + qtIdent(new_name) + wxT(";\n");
}
+ else if (!original_name.IsEmpty() && original_name != old_name)
+ {
+ // the old attribute isn't in the new list so drop it
+
+ // don't move through new list yet
+ hold = 1;
+
+ sql += wxT("ALTER TYPE ") + objname + wxT("\n DROP ATTRIBUTE ")
+ + qtIdent(old_name) + wxT(";\n");
+ }
+ else if (original_name == new_name)
+ {
+ // drop then add the attribute since more than the name has changed
+ if (new_type != old_type || new_collation != old_collation)
+ {
+ sql += wxT("ALTER TYPE ") + objname + wxT("\n DROP ATTRIBUTE ")
+ + qtIdent(old_name) + wxT(";\n");
+
+ sql += wxT("ALTER TYPE ") + objname + wxT("\n ADD ATTRIBUTE ")
+ + qtIdent(new_name) + wxT(" ") + new_type + wxT(";\n");
+ }
+
+ // but do nothing if nothing has changed
+ }
+ else
+ {
+ // add new attribute as it doesn't have an original name and isn't in the old list
+
+ sql += wxT("ALTER TYPE ") + objname + wxT("\n ADD ATTRIBUTE ")
+ + qtIdent(new_name) + wxT(" ") + new_type + wxT(";\n");
+
+ // we don't need to increment the old list as this is a new item
+ continue;
+ }
+
+ oldindex++;
}
return sql;
diff --git a/pgadmin/include/dlg/dlgType.h b/pgadmin/include/dlg/dlgType.h
index d618221..8a3481b 100644
--- a/pgadmin/include/dlg/dlgType.h
+++ b/pgadmin/include/dlg/dlgType.h
@@ -54,7 +54,7 @@ private:
void showDefinition(int panel);
wxString GetFullTypeName(int type);
- wxArrayString memberTypes, memberLengths, memberPrecisions, memberCollations;
+ wxArrayString memberTypes, memberLengths, memberPrecisions, memberCollations, memberOriginalNames;
bool queriesToBeSplitted;
DECLARE_EVENT_TABLE()
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
