Global parameters and seeds.sql

[Jeremy Mitchell <mi...@apache.org>]

IMO there seems to be an inherent flaw with global parameters and that flaw
is exposed in via seeds.sql.

Let's use an example.

There is a global parameter called tm.toolname with a seeded value of
'Traffic Ops' (
https://github.com/apache/incubator-trafficcontrol/blob/master/traffic_ops/app/db/seeds.sql#L39
)

Obviously, the intention of this parameter is that you can change it to fit
your needs. Maybe you want to change it to "Traffic Mops".

However, when seeds.sql runs again (on upgrade for example), you will end
up with a duplicate tm.toolname parameter because the unique constraint on
the parameter table is applied to name + config_file + value.

For "global" parameters, the unique constraint should really be on name +
config_file because, really, why would you want more than 1 global
parameter?

So we have a table (parameters) where some parameters the unique constraint
should be name + config_file + value (which makes perfect sense if you
think about how parameters are used with profiles) and other parameters
(global) where the unique constraint should be name + config_file...

Anyhow, after saying all of that....I think the solution (or a solution) is
to modify seeds.sql to first run a query (select count) for global
parameters to ensure that they are not already there before inserting
them....

I'll create a jira for that if there is no objections.

jeremy

Re: Global parameters and seeds.sql

[Steve Malenfant <sm...@gmail.com>]

Jeremy,

I observed the same thing when I upgraded my instance of Traffic Ops. It
had duplicated "tm.toolname" as a key. +1 on the proposal.

Steve

On Thu, Aug 24, 2017 at 12:04 PM, Jeremy Mitchell <mi...@apache.org>
wrote:

> IMO there seems to be an inherent flaw with global parameters and that flaw
> is exposed in via seeds.sql.
>
> Let's use an example.
>
> There is a global parameter called tm.toolname with a seeded value of
> 'Traffic Ops' (
> https://github.com/apache/incubator-trafficcontrol/blob/
> master/traffic_ops/app/db/seeds.sql#L39
> )
>
> Obviously, the intention of this parameter is that you can change it to fit
> your needs. Maybe you want to change it to "Traffic Mops".
>
> However, when seeds.sql runs again (on upgrade for example), you will end
> up with a duplicate tm.toolname parameter because the unique constraint on
> the parameter table is applied to name + config_file + value.
>
> For "global" parameters, the unique constraint should really be on name +
> config_file because, really, why would you want more than 1 global
> parameter?
>
> So we have a table (parameters) where some parameters the unique constraint
> should be name + config_file + value (which makes perfect sense if you
> think about how parameters are used with profiles) and other parameters
> (global) where the unique constraint should be name + config_file...
>
> Anyhow, after saying all of that....I think the solution (or a solution) is
> to modify seeds.sql to first run a query (select count) for global
> parameters to ensure that they are not already there before inserting
> them....
>
> I'll create a jira for that if there is no objections.
>
> jeremy
>