97748 - schema for "versions" table does not enforce uniqueness

Status: RESOLVED DUPLICATE of bug 7233

(Bugzilla :: Bugzilla-General, defect)

Description

[Jeff Lawson]

The schema for the "versions" table within checksetup.pl is:

$table{versions} =
   'value tinytext,
    program varchar(64) not null';

However, it should realistically be changed to the following, so that 
the "versions.value" column matches the definition of the "bugs.version" 
column, and that a primary key enforcing uniqueness against the two column 
combinations.

$table{versions} =
   'value varchar(16) not null,
    program varchar(64) not null,
    PRIMARY KEY (program,value)';

Without the primary key to enforce uniqueness, it's possible to use the 
editversions.cgi to add duplicated versions.  Additionally, as long as the 
definition for the "versions.value" column does not match what "bugs.version" 
actually allows, it will be possible for the admin to create a version that is 
longer than what the "bugs" table will accomodate, breaking referential 
integrity.

While the schema is being fixed, why not rename "versions.program" 
to "versions.product", and rename "versions.value" to "versions.version".  That 
would improve consistency with the naming of the corresponding columns in 
the "bugs" table.

Comment 1

[Jeff Lawson]

Actually, it looks like bugs.version was recently (2001-06-15) upgraded from 
varchar(16) to varchar(64), according to Bug 9352.  So the corrected schema for 
the "versions" table should still be changed, but to the following:

$table{versions} =
   'value varchar(64) not null,
    program varchar(64) not null,
    PRIMARY KEY (program,value)';

Comment 2

[Jeff Lawson]

fyi, although it is true that editversions.cgi attempts to check whether the 
version you are attempting to add already exists before it tries to, that is 
hardly an atomic or necessarily robust action when the criteria can be easily 
enforced by the database.

Comment 3

[Dave Miller [:justdave]]

Uniqueness on the versions table is covered on bug 7233.
Changing the tables to use key number references instead of full names is on
bug 43600.

*** This bug has been marked as a duplicate of 7233 ***