<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Christopher Hicks wrote:
<blockquote cite="midPine.LNX.4.61.0501221050130.7454@skippy.fini.net"
type="cite">Bah. Your conclusion of what's necessary here seems to be
based on a
very selective view of the universe. Several "necessities" stick out
when considering a custom fields implemention based on abstraction at
the database level: <br>
<br>
(1) People can add as many custom fields as they want without worrying
about reaching the maximum record size of their database</blockquote>
Under my fields-as-columns (FAC) proposal, fields can live in their own
tables, and
there is no limit to the number of tables per database in MySQL, so
this isn't an issue between my proposal and Sean's fields-as-data (FAD)
proposal.<br>
<br>
<blockquote cite="midPine.LNX.4.61.0501221050130.7454@skippy.fini.net"
type="cite">(2) Code for dealing with custom fields is going to need
to have a goodly portion of the abstraction tables for keeping track of
stuff anyways.<br>
</blockquote>
I'm not sure what you mean by abstraction tables, but both proposals
will require some meta-data about fields to be stored in fielddefs,
just as we already do for standard fields, and will store lists of
possible values for some fields in separate tables, just as we
already do for standard fields like component and should be
doing for fields like op_sys.<br>
<br>
That doesn't mean we should store all meta-data as data. We should use
the right tool for the job, as we have already done with standard
fields, for which we rightly use columns.<br>
<br>
<blockquote cite="midPine.LNX.4.61.0501221050130.7454@skippy.fini.net"
type="cite">(3) Since queries that involve custom fields will now have
to be written on the fly they are less able to be optimized by using a
database that can deal with prepare() usefully.</blockquote>
How does FAC require queries to be written "on the fly" in a less
optimizable way, reducing performance on prepare()-happy databases?
Can you demonstrate this?<br>
<br>
<blockquote cite="midPine.LNX.4.61.0501221050130.7454@skippy.fini.net"
type="cite">(4) Since queries involving custom fields are going to
take a few database hits to figure out what the field names so the
query could be written you end up with cases where 1 query turns into 4
queries. If the database is across a WAN from the bugzilla instance
the effect of multiple queries where there were one will be more
noticable.</blockquote>
Sure, more queries is slower. But FAC would use
less queries overall, and simpler ones at that.<br>
<br>
Consider a simple query on a single custom field
"foo" where the user wants bugs where foo=bar. With FAC, we
search for bugs where the "foo" column contains "bar". With FAD, we
look up the field ID for "foo" and then search
for bugs where the "field_id" column contains that ID and the "value"
column contains "bar" (or do it in one query with a join).<br>
<br>
Even in the
worst case, when you couldn't infer the column name from the form field
name, FAC lookups would only be equal to, not worse than, FAD lookups.
And if these lookups mattered (which Sean claims they don't), the list
of custom fields and associated identifiers would get cached under
either proposal the same way we cache components and versions.<br>
<br>
<blockquote cite="midPine.LNX.4.61.0501221050130.7454@skippy.fini.net"
type="cite">(5) Custom fields should be able to be implemented without
the bugzilla user having database privs to alter tables.</blockquote>
FAD is even more insecure in this regard, since a compromised
Bugzilla user account that wasn't allowed to alter tables would still
be able to alter custom fields under that proposal (unless we
implemented table/column-specific privileges for that account, which
would be more work and complexity--and thus risk).<br>
<br>
Nevertheless, note that the Bugzilla user account already has such
privileges today (checksetup.pl
uses them to set up and update the database schema), and even if we
took them away, the Bugzilla administrators, to whom we
will entrust the creation of custom fields, will certainly retain
those privileges via a separate account.<br>
<br>
<blockquote cite="midPine.LNX.4.61.0501221050130.7454@skippy.fini.net"
type="cite">Myk - it sounds like you're basing the decision on what
way to go here totally based on performance and I think there's a lot
more that should go into this decision.</blockquote>
To the contrary, my proposal is based on much more than performance.
My
previous email was about performance only because that was Sean's
primary argument against it (he thought my proposal would be slower and
offered data to support his conclusion--I ran his tests myself and
found the opposite was true).<br>
<br>
I think
we should use real columns for custom fields because:<br>
<ol>
<li>that's what they're there for;<br>
<br>
Custom fields are no different from standard fields in how they're
used (queried, displayed, updated, etc.), and columns were designed for
this express purpose when database
systems were developed. Given that they've been used to represent
"fields" of all kinds for decades, and that we've used them in Bugzilla
to represent the standard fields for over five
years, they're a mature and proven technology for doing what we want
and likely to be better than any new mechanism we come up with which
represents fields as data.<br>
<br>
</li>
<li>then they work the same as standard fields;<br>
<br>
Custom fields and standard fields are both used (queried, displayed,
updated) in much the same way, and using the same technology to store
them means we can use the same code in many cases (and the same kind of
code in others) to access and manipulate them, making the source
simpler, more robust, and easier to develop.<br>
<br>
</li>
<li>it makes them significantly faster;<br>
<br>
Per my tests and standard database design theory, real columns are much
faster than data columns.<br>
</li>
</ol>
-myk<br>
<br>
</body>
</html>