You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafficcontrol.apache.org by Jeremy Mitchell <mi...@apache.org> on 2017/04/04 19:12:16 UTC
Traffic Ops database unique constraints
I have moved all Traffic Ops seed data into ONE place -
https://github.com/apache/incubator-trafficcontrol/blob/master/traffic_ops/app/db/seeds.sql
(there used to be some in seeds.sql and others in
traffic_ops/install/data/json)
Anyhow, if you run seeds.sql multiple times (i.e. db/admin.pl upgrade),
you'll end up with duplicate data as some tables don't have unique
constraints.
I'd like to add unique constraints to the following database table /
columns:
- role.name
- status.name
- type.name
- job_status.name
In my opinion, these constraints should have been in there since day 1 but
if you have any objections, let me know.
Thanks,
Jeremy
Re: Traffic Ops database unique constraints
Posted by Jeremy Mitchell <mi...@gmail.com>.
I created a Jira - https://issues.apache.org/jira/browse/TC-222
I'll submit a PR shortly if there are no objections.
On Tue, Apr 4, 2017 at 2:18 PM, Jeremy Mitchell <mi...@gmail.com>
wrote:
> and i forgot one. i'd like to make parameter unique by
> name/config_file/value
>
> On Tue, Apr 4, 2017 at 1:55 PM, Jan van Doorn <jv...@knutsel.com> wrote:
>
>> +1
>> > On Apr 4, 2017, at 1:17 PM, Jeremy Mitchell <mi...@apache.org>
>> wrote:
>> >
>> > If I add these database constraints, you can't create 2 statuses (for
>> > example) with the same name (which I think is probably the desired
>> > effect)...so I can change my seeded data to look like:
>> >
>> > insert into status (name, description) values ('OFFLINE', 'Server is
>> > Offline. Not active in any configuration.') ON CONFLICT (name) DO
>> NOTHING;
>> >
>> > and no more duplicate seeds...
>> >
>> > On Tue, Apr 4, 2017 at 1:12 PM, Jeremy Mitchell <mitchell852@apache.org
>> >
>> > wrote:
>> >
>> >> I have moved all Traffic Ops seed data into ONE place -
>> >> https://github.com/apache/incubator-trafficcontrol/blob/
>> >> master/traffic_ops/app/db/seeds.sql
>> >>
>> >> (there used to be some in seeds.sql and others in
>> >> traffic_ops/install/data/json)
>> >>
>> >> Anyhow, if you run seeds.sql multiple times (i.e. db/admin.pl
>> upgrade),
>> >> you'll end up with duplicate data as some tables don't have unique
>> >> constraints.
>> >>
>> >> I'd like to add unique constraints to the following database table /
>> >> columns:
>> >>
>> >> - role.name
>> >> - status.name
>> >> - type.name
>> >> - job_status.name
>> >>
>> >> In my opinion, these constraints should have been in there since day 1
>> but
>> >> if you have any objections, let me know.
>> >>
>> >> Thanks,
>> >>
>> >> Jeremy
>> >>
>> >>
>> >>
>>
>>
>
Re: Traffic Ops database unique constraints
Posted by Jeremy Mitchell <mi...@gmail.com>.
and i forgot one. i'd like to make parameter unique by
name/config_file/value
On Tue, Apr 4, 2017 at 1:55 PM, Jan van Doorn <jv...@knutsel.com> wrote:
> +1
> > On Apr 4, 2017, at 1:17 PM, Jeremy Mitchell <mi...@apache.org>
> wrote:
> >
> > If I add these database constraints, you can't create 2 statuses (for
> > example) with the same name (which I think is probably the desired
> > effect)...so I can change my seeded data to look like:
> >
> > insert into status (name, description) values ('OFFLINE', 'Server is
> > Offline. Not active in any configuration.') ON CONFLICT (name) DO
> NOTHING;
> >
> > and no more duplicate seeds...
> >
> > On Tue, Apr 4, 2017 at 1:12 PM, Jeremy Mitchell <mi...@apache.org>
> > wrote:
> >
> >> I have moved all Traffic Ops seed data into ONE place -
> >> https://github.com/apache/incubator-trafficcontrol/blob/
> >> master/traffic_ops/app/db/seeds.sql
> >>
> >> (there used to be some in seeds.sql and others in
> >> traffic_ops/install/data/json)
> >>
> >> Anyhow, if you run seeds.sql multiple times (i.e. db/admin.pl upgrade),
> >> you'll end up with duplicate data as some tables don't have unique
> >> constraints.
> >>
> >> I'd like to add unique constraints to the following database table /
> >> columns:
> >>
> >> - role.name
> >> - status.name
> >> - type.name
> >> - job_status.name
> >>
> >> In my opinion, these constraints should have been in there since day 1
> but
> >> if you have any objections, let me know.
> >>
> >> Thanks,
> >>
> >> Jeremy
> >>
> >>
> >>
>
>
Re: Traffic Ops database unique constraints
Posted by Jan van Doorn <jv...@knutsel.com>.
+1
> On Apr 4, 2017, at 1:17 PM, Jeremy Mitchell <mi...@apache.org> wrote:
>
> If I add these database constraints, you can't create 2 statuses (for
> example) with the same name (which I think is probably the desired
> effect)...so I can change my seeded data to look like:
>
> insert into status (name, description) values ('OFFLINE', 'Server is
> Offline. Not active in any configuration.') ON CONFLICT (name) DO NOTHING;
>
> and no more duplicate seeds...
>
> On Tue, Apr 4, 2017 at 1:12 PM, Jeremy Mitchell <mi...@apache.org>
> wrote:
>
>> I have moved all Traffic Ops seed data into ONE place -
>> https://github.com/apache/incubator-trafficcontrol/blob/
>> master/traffic_ops/app/db/seeds.sql
>>
>> (there used to be some in seeds.sql and others in
>> traffic_ops/install/data/json)
>>
>> Anyhow, if you run seeds.sql multiple times (i.e. db/admin.pl upgrade),
>> you'll end up with duplicate data as some tables don't have unique
>> constraints.
>>
>> I'd like to add unique constraints to the following database table /
>> columns:
>>
>> - role.name
>> - status.name
>> - type.name
>> - job_status.name
>>
>> In my opinion, these constraints should have been in there since day 1 but
>> if you have any objections, let me know.
>>
>> Thanks,
>>
>> Jeremy
>>
>>
>>
Re: Traffic Ops database unique constraints
Posted by Jeremy Mitchell <mi...@apache.org>.
If I add these database constraints, you can't create 2 statuses (for
example) with the same name (which I think is probably the desired
effect)...so I can change my seeded data to look like:
insert into status (name, description) values ('OFFLINE', 'Server is
Offline. Not active in any configuration.') ON CONFLICT (name) DO NOTHING;
and no more duplicate seeds...
On Tue, Apr 4, 2017 at 1:12 PM, Jeremy Mitchell <mi...@apache.org>
wrote:
> I have moved all Traffic Ops seed data into ONE place -
> https://github.com/apache/incubator-trafficcontrol/blob/
> master/traffic_ops/app/db/seeds.sql
>
> (there used to be some in seeds.sql and others in
> traffic_ops/install/data/json)
>
> Anyhow, if you run seeds.sql multiple times (i.e. db/admin.pl upgrade),
> you'll end up with duplicate data as some tables don't have unique
> constraints.
>
> I'd like to add unique constraints to the following database table /
> columns:
>
> - role.name
> - status.name
> - type.name
> - job_status.name
>
> In my opinion, these constraints should have been in there since day 1 but
> if you have any objections, let me know.
>
> Thanks,
>
> Jeremy
>
>
>