Add cdn column to profile table
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/commit/36a4d0ea Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/tree/36a4d0ea Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/diff/36a4d0ea Branch: refs/heads/master Commit: 36a4d0ea4f721de835b0001a2459680a5519514e Parents: de60ccc Author: Jan van Doorn <jan_vando...@cable.comcast.com> Authored: Mon Dec 26 14:47:16 2016 -0700 Committer: Jan van Doorn <j...@apache.org> Committed: Fri Feb 17 17:49:10 2017 +0000 ---------------------------------------------------------------------- .../20161226000000_cdn_domain_name.sql | 26 +++++++++++++--- traffic_ops/app/lib/Schema/Result/Cdn.pm | 19 ++++++++++-- traffic_ops/app/lib/Schema/Result/Profile.pm | 32 ++++++++++++++++++-- 3 files changed, 69 insertions(+), 8 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/36a4d0ea/traffic_ops/app/db/migrations/20161226000000_cdn_domain_name.sql ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/migrations/20161226000000_cdn_domain_name.sql b/traffic_ops/app/db/migrations/20161226000000_cdn_domain_name.sql index 7b38d64..65a7b74 100644 --- a/traffic_ops/app/db/migrations/20161226000000_cdn_domain_name.sql +++ b/traffic_ops/app/db/migrations/20161226000000_cdn_domain_name.sql @@ -17,19 +17,37 @@ -- SQL in section 'Up' is executed when this migration is applied ALTER TABLE public.cdn ADD COLUMN domain_name text; - UPDATE cdn SET domain_name=domainlist.value FROM (SELECT distinct cdn_id,value FROM server,parameter WHERE type=(SELECT id FROM type WHERE name='EDGE') AND parameter.id in (select parameter from profile_parameter WHERE profile_parameter.profile=server.profile) AND parameter.name='domain_name' AND config_file='CRConfig.json') AS domainlist WHERE id = domainlist.cdn_id; - UPDATE public.cdn SET domain_name='-' WHERE name='ALL'; - ALTER TABLE public.cdn ALTER COLUMN domain_name SET NOT NULL; +ALTER TABLE public.profile ADD COLUMN cdn bigint; +ALTER TABLE public.profile + ADD CONSTRAINT fk_cdn1 FOREIGN KEY (cdn) + REFERENCES public.cdn (id) MATCH SIMPLE + ON UPDATE RESTRICT ON DELETE RESTRICT; +CREATE INDEX idx_181818_fk_cdn1 + ON public.profile + USING btree + (cdn); + +UPDATE profile set cdn=domainlist.cdn_id + FROM (SELECT distinct profile.id AS profile_id, value AS profile_domain_name, cdn.id cdn_id + FROM profile, parameter, cdn, profile_parameter + WHERE parameter.name='domain_name' + AND parameter.config_file='CRConfig.json' + AND parameter.value = cdn.domain_name + AND parameter.id in (select parameter from profile_parameter where profile=profile.id)) as domainlist +WHERE id = domainlist.profile_id; + -- +goose Down -- SQL section 'Down' is executed when this migration is rolled back -ALTER TABLE public.cdn DROP COLUMN domain_name; \ No newline at end of file +ALTER TABLE public.cdn DROP COLUMN domain_name; + +ALTER TABLE public.profile DROP COLUMN cdn; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/36a4d0ea/traffic_ops/app/lib/Schema/Result/Cdn.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/Schema/Result/Cdn.pm b/traffic_ops/app/lib/Schema/Result/Cdn.pm index 28b7f6b..bb0e520 100644 --- a/traffic_ops/app/lib/Schema/Result/Cdn.pm +++ b/traffic_ops/app/lib/Schema/Result/Cdn.pm @@ -121,6 +121,21 @@ __PACKAGE__->has_many( { cascade_copy => 0, cascade_delete => 0 }, ); +=head2 profiles + +Type: has_many + +Related object: L<Schema::Result::Profile> + +=cut + +__PACKAGE__->has_many( + "profiles", + "Schema::Result::Profile", + { "foreign.cdn" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + =head2 servers Type: has_many @@ -152,8 +167,8 @@ __PACKAGE__->might_have( ); -# Created by DBIx::Class::Schema::Loader v0.07046 @ 2016-12-26 10:44:56 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:AQZpzzOuEpgmacUrJGl+LQ +# Created by DBIx::Class::Schema::Loader v0.07046 @ 2016-12-26 14:46:31 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:QlfY6K6vVxW5C9vsHc4YqA # You can replace this text with custom code or comments, and it will be preserved on regeneration http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/36a4d0ea/traffic_ops/app/lib/Schema/Result/Profile.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/Schema/Result/Profile.pm b/traffic_ops/app/lib/Schema/Result/Profile.pm index 4ece9ed..2839364 100644 --- a/traffic_ops/app/lib/Schema/Result/Profile.pm +++ b/traffic_ops/app/lib/Schema/Result/Profile.pm @@ -47,6 +47,12 @@ __PACKAGE__->table("profile"); is_nullable: 1 original: {default_value => \"now()"} +=head2 cdn + + data_type: 'bigint' + is_foreign_key: 1 + is_nullable: 1 + =cut __PACKAGE__->add_columns( @@ -68,6 +74,8 @@ __PACKAGE__->add_columns( is_nullable => 1, original => { default_value => \"now()" }, }, + "cdn", + { data_type => "bigint", is_foreign_key => 1, is_nullable => 1 }, ); =head1 PRIMARY KEY @@ -98,6 +106,26 @@ __PACKAGE__->add_unique_constraint("idx_18384_name_unique", ["name"]); =head1 RELATIONS +=head2 cdn + +Type: belongs_to + +Related object: L<Schema::Result::Cdn> + +=cut + +__PACKAGE__->belongs_to( + "cdn", + "Schema::Result::Cdn", + { id => "cdn" }, + { + is_deferrable => 0, + join_type => "LEFT", + on_delete => "RESTRICT", + on_update => "RESTRICT", + }, +); + =head2 deliveryservices Type: has_many @@ -144,8 +172,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07046 @ 2016-12-26 10:44:56 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:lLgKBTaWgSw4T1NXk7jh/g +# Created by DBIx::Class::Schema::Loader v0.07046 @ 2016-12-26 14:46:31 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:osx+OzuftRlkWwKwb1N+hg # You can replace this text with custom code or comments, and it will be preserved on regeneration