Custom Fields again

[Vitaly Fedrushkov]

Good $daytime,

> Date: Thu, 12 Dec 2002 01:18:44 +1100
> From: Bradley Baetz <bbaetz at student.usyd.edu.au>
> To: developers at bugzilla.org
> Subject: Re: Custom Fields again

> > All of this worry leads me to feel like we need a better way to
> > manage the schema.  (Others have touched on this.)  A variety of
> > schemes exist to do this.  It /shouldn't/ be a big deal for people
> > to add (or drop) fields and for the software to dynamically figure
> > it out.  Beyond primary keys and foreign keys there shouldn't be
> > that much that's sacred. Ultimately, I'd like to see the 'core'
> > schema split into the sacred and optional bits.  Then local
> > additions and choices regarding the optional core pieces can be
> > stored seperately.

> Well, we can find out whats in the schema. Its doing something
> useful with it which is the problem. If I know that I have a
> VARCHAR(x) column called 'myField', how do I know what its meant to
> contain?

Having our own metadata is inevitable, because:

o  Native DBMS data types denote nothing but storage format.
   Data types we're talking about here often denote semantics.

o  Native DBMS data types aren't portable and can't be used 
   in Bugzilla UI code.

o  Field names must not be used anywhere in UI.  At least in
   localizable world.  Values for multiple-choice fields must
   not be used either, but that's another story.

o  The few 'sacred' fields are those which (a) form database 
   schema, and (b) define bug lifecycle.  All others are all
   about entry and search, and people may want to turn them 
   off.

o  There are entities with many-to-many relation to fields
   and products, notably enable flags (to supersede has_xxxx
   configuration parameters) and fixed field values.

If implemented using three extra tables, the VARCHAR can 
be easily resolved by having 'numvalue', 'strvalue',
'datevalue' and perhaps 'blobvalue' fields in values table.
Not so savvy w.r.t. storage, but better performance as
no conversion needed.  BUT:

The problem with three extra tables is that there's almost
no method by which it can be effectively optimized.

The problem with schema changes is smaller: difficulty with
nonscalar custom fields.

> A lot of the stff in checksetup is more than just creating the
> fields - we run conversion code to move stuff arround, too. To an
> extent, teh code in checksetup is a manual version of that, which
> tests for existance of certain fields, and then does 'stuff' to them

Not having Web tools for things like schema changes is not bad.
Sometimes custom fields will require database tuning, additional
indexes, capacity planning, etc.  Here comes the Database
Administrator, hopefully armed with more powerful tools than just a
browser.

Notice about sixteen indices is not so important because anyone
with this problem will then move to, say, Oracle -- aren't we going
towards portable Bugzilla?

  Regards,
  Willy.

--
No easy hope or lies        | Vitaly "Willy the Pooh" Fedrushkov
Shall bring us to our goal, | Control Systems and Processes Division
But iron sacrifice          | LUKOIL Company, Chelyabinsk Branch
Of Body, Will and Soul.     | willy at lukoil.uu.ru  +7 3512 620367
                  R.Kipling | VVF1-RIPE