Custom fields schema
Kevin Benton
kevin.benton at amd.com
Wed Dec 15 15:34:35 UTC 2004
It seems to me that any discussion of custom fields ought to include
existing fields and handle them all in a unified approach. In my view, the
best way to do this would be to define every field in a fields table, and
specify formatting / location, etc. in the table. I would hope that there
would even be a place where that table would have a definition for certain
types of handlers (subs) associated with each field. Maybe this is an
overly complex a view, but I think that this might offer us more flexibility
than we have with TT and yet, give us the ability to handle fields we
haven't dreamed of through plugins.
---
Kevin Benton
Perl/Bugzilla Developer
Advanced Micro Devices
The opinions stated in this communication do not necessarily reflect the
view of Advanced Micro Devices and have not been reviewed by management.
This communication may contain sensitive and/or confidential and/or
proprietary information. Distribution of such information is strictly
prohibited without prior consent of Advanced Micro Devices. This
communication is for the intended recipient(s) only. If you have received
this communication in error, please notify the sender, then destroy any
remaining copies of this communication.
> -----Original Message-----
> From: developers-owner at bugzilla.org [mailto:developers-owner at bugzilla.org]
> On Behalf Of Sean McAfee
> Sent: Tuesday, December 14, 2004 10:36 PM
> To: developers at bugzilla.org
> Subject: Custom fields schema
>
> To kick things off (I hope), here are some tables from my custom fields
> schema, shorn of some of the niftier additions that have accrued over
> time.
>
> CREATE TABLE custom_fields (
> field_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
> field_name VARCHAR(100) NOT NULL,
> display_name TINYTEXT NOT NULL,
> field_type CHAR BINARY NOT NULL,
> selection_id INTEGER,
> UNIQUE INDEX (field_name)
> );
>
> This is obviously the table which enumerates all custom fields defined at
> a
> particular Bugzilla installation.
>
> field_id is your basic primary key.
>
> field_name is a unique name for the key, used in such contexts as CGI
> parameters:
>
> http://bugzilla.mycompany.com/buglist.cgi?my_custom_field=foo
>
> ...and names used in code:
>
> print "field value is ", $bug->{my_custom_field}, "\n";
>
> On ongoing but minor concern of mine has been how to guarantee that a
> custom
> field's name does not conflict with any CGI parameter, now or in the
> future.
> Adopting a naming convention, such as a short prefix followed by "_", has
> seemed to suffice.
>
> display_name is the field's name as it appears on Web pages.
> my_custom_field's display name might be "My Custom Field", for example.
>
> There has been disagreement on this mailing list in the past, since this
> approach is not internationalization-friendly. Recently it occurred to me
> that a compromise might be to use templates to look up field display
> names,
> as others have proposed (or insisted upon), but to fall back on the
> contents
> of this column if no template-based name is found.
>
> field_type obviously describes the type of the field. Valid values are:
>
> 'i' - An integer.
>
> 'd' - A date with no associated time.
>
> 't' - A date/time.
>
> 's' - A short string, 255 or fewer characters in length. No vertical
> whitespace allowed. Represented on CGI forms by a single-line text
> element.
>
> 'l' - A long string, 65535 or fewer characters in length. No restriction
> on
> contents. Represented on CGI forms by a textarea element.
>
> 'e' - A "selection" field, which takes its values from a set of valid
> strings, its "domain". Such a field's value may be either exactly
> one
> or zero of the elements of its domain.
>
> 'm' - A "multiselection" field. Like a selection field, but its value may
> consist of any number of elements of its domain.
>
> More recently, I've had a need to address custom fields and built-in
> Bugzilla fields using the same range of integers, so I added an eighth
> field
> type: 'b', for Bugzilla.
>
> selection_id is non-null only for selection, multiselection, and Bugzilla
> fields. In the first two cases, it is an index into the table
> CF_SELECTIONS (see below). In the last case, it is an index into the
> FIELDDEFS table.
>
>
> CREATE TABLE cf_membership (
> product_id INTEGER NOT NULL REFERENCES products (id),
> field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
> sortkey INTEGER NOT NULL,
> INDEX (product_id)
> );
>
> This table describes which fields belong to which products, in what should
> be a straightforward manner. (The REFERENCES clauses have no effect for
> most MySQL tables, but are useful for documentation purposes.) The sort
> order described here may be overridden or ignored outright in templates;
> it
> is a default only.
>
>
> CREATE TABLE cf_selections (
> selection_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
> selection_name VARCHAR(100) NOT NULL,
> unset_label TINYTEXT NOT NULL,
> UNIQUE INDEX (selection_name)
> );
>
> This table describes the possible domains of all selection fields in the
> installation. selection_id is the primary key. selection_name is a name
> which is presented on administrative interfaces, and may be subject to the
> same concerns as custom fields' display names. unset_label is a textual
> label which is printed when a selection field's value is null. I created
> this feature simply because it existed in my company's previous
> incident-tracking system (TeamTrack), but it has proven to be of
> remarkably
> little use, and I wouldn't be averse to ditching it.
>
> Note that distinct custom fields may share the same selection domain.
>
>
> CREATE TABLE cf_selection_labels (
> label_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
> selection_id INTEGER NOT NULL REFERENCES cf_selections (selection_id),
> label VARCHAR(100) NOT NULL,
> inactive BOOL NOT NULL,
> sortkey INTEGER NOT NULL,
> UNIQUE INDEX (selection_id, label)
> );
>
> This table describes the elements (or "labels") of all selection domains.
> label_id is the primary key. selection_id describes to which domain each
> label belongs, sorted by default in the order described by sortkey. label
> is the text of the element. inactive is a boolean flag; if true, the
> corresponding label may not be entered as a new field value. This feature
> is necessary because otherwise there would be no way to remove a selection
> element without annihilating all existing data that refers to that
> element.
>
>
> CREATE TABLE cf_integer (
> bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
> field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
> value INTEGER,
> PRIMARY KEY (bug_id, field_id),
> INDEX (field_id)
> );
>
> This table stores integer custom field data, in what again should be a
> straightforward manner. The primary key is useful for looking up field
> values for known bugs; the index on field_id is useful for querying. Note
> that an integer field may be null, indicating that it has no value.
>
> Similar comments apply to the next three tables:
>
> CREATE TABLE cf_shortstring (
> bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
> field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
> value TINYTEXT,
> PRIMARY KEY (bug_id, field_id),
> INDEX (field_id)
> );
>
> CREATE TABLE cf_longstring (
> bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
> field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
> value TEXT,
> PRIMARY KEY (bug_id, field_id),
> INDEX (field_id)
> );
>
> CREATE TABLE cf_date (
> bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
> field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
> value DATETIME,
> PRIMARY KEY (bug_id, field_id),
> INDEX (field_id)
> );
>
> Both date and date/time field values are stored in the CF_DATE table.
> Time
> components of date field values are eliminated by the interface code when
> it
> goes into or comes out of the database. Interface code will accept, as
> user
> input for date fields, any input that Date::Parse can handle.
>
>
> CREATE TABLE cf_selection (
> bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
> field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
> label_id INTEGER NOT NULL REFERENCES cf_selection_labels (label_id),
> INDEX (bug_id, field_id),
> INDEX (field_id)
> );
>
> This table is similar to the others, but since selection fields are
> multivalued, no primary key is possible. Both selection and
> multiselection
> fields are stored in the same table; the interface code must ensure that
> single-selection fields do not refer to more than one element of their
> domains.
>
> So...comments?
>
>
> --
> Sean McAfee -- etzwane at schwag.org
> -
> To view or change your list settings, click here:
> <http://bugzilla.org/cgi-bin/mj_wwwusr?user=kevin.benton@amd.com>
More information about the developers
mailing list