[wp-trac] Re: [WordPress Trac] #5183: General Meta-Data Table
WordPress Trac
wp-trac at lists.automattic.com
Tue Jun 16 22:33:25 GMT 2009
#5183: General Meta-Data Table
-----------------------------------------+----------------------------------
Reporter: filosofo | Owner: anonymous
Type: enhancement | Status: reopened
Priority: normal | Milestone:
Component: General | Version: 2.3
Severity: normal | Resolution:
Keywords: needs-patch meta-table meta |
-----------------------------------------+----------------------------------
Comment(by Denis-de-Bernardy):
More reference, since IRC logs are broken:
{{{
well, I don't think there's anything about the wp_terms table structure
that prevents different terms from having the same name. that's an api
issue
11:53 PM ddebernardy
no no, it's a db issue underneath
11:53 PM filosofo
The foreign key thing could be solved with another table, wp_objects
11:54 PM ddebernardy
that's even worse than our current implementation
11:54 PM
and it won't allow the foreign key
11:54 PM filosofo
?
11:55 PM ddebernardy
well, unless you're meaning wp_objects is merging posts and links (heck,
why not...), it's really not a good idea, because you'd merely add an
extra table in the lot that doesn't fix the foreign key constraint problem
11:56 PM filosofo
right, that's what I mean. all objects in one table
11:56 PM ddebernardy
FWIW, I've been playing with that idea fo the past 10 years or so
11:56 PM
the only implementation I found worthy of implementing was like this:
11:56 PM
table nodes (id)
11:56 PM
table foo (id references nodes(id))
11:57 PM
that way you're working on native structures
11:57 PM
and you still have native indexes
11:57 PM
but it actually comes down to lackings in database engines
11:57 PM
because what the above really should be is:
11:57 PM
table nodes (id)
11:57 PM
table foo inherits nodes
11:58 PM
with id shared across the board
11:58 PM
you can do that with a uuid field, basically. but your meta, e.g. created,
modified, etc. then needs to be redefined in each table
11:59 PM ruslany__ has left IRC ("Leaving")
11:59 PM ddebernardy
in pgsql, inherit won't enforce unique indexes. and I'm not even aware of
a similar keyword in mysql
12:01 AM filosofo
I was going to say, I didn't think you could do that in MySQL. I don't
think foreign keys are supported with all mysql storage engines.
12:01 AM ddebernardy
no, they're not. only in innodb, ndb, and a few more (but not myisam)
12:02 AM rodrigo_sampaio1 has left IRC ("Leaving.")
12:02 AM ddebernardy
still, it's generally good to be able to enforce them. it means the db is
well designed if you can, and I'd like to make it possible to enforce
transactions in 2.9 or 3.0.
12:04 AM filosofo
what about the db structure prevents different terms from having the same
name?
12:04 AM ddebernardy
UNIQUE KEY slug (slug),
12:04 AM
in wp_terms
12:04 AM filosofo
ok, missed that. So change that.
12:05 AM ddebernardy
no no, you want more than that, really
12:05 AM
by the same token, then other fields would want to be in wp_term_taxonomy
12:05 AM
with a unique index on slug/parent/taxonomy
12:05 AM
and the term table then disappears
12:06 AM
for the sake of marking the change, I'd suggest renaming the table as
wp_taxonomies, so that old plugins end up miserably failing (or then, we'd
add a pseudoview to make it backwards compat)
12:07 AM
I'd be curious to have the thoughts of rboren on all of this, though
12:11 AM sorich87 has joined the channel.
12:13 AM filosofo
so how about 3 tables: terms, taxonomies, objects
12:13 AM
we could move taxonomy definition into the db
12:13 AM ddebernardy
why terms and taxonomies?
12:14 AM
if you've an index on slug, held in taxonomies, then you can join that
table on itself using ( slug ) where t1.id <> t2.id, and you've the same
thing as terms
12:14 AM
the ultimate difference is, with three tables, you've three joins, with
two tables, you've only two :-)
12:14 AM
and when you join tax join tax join tax as I and others do in a few
plugins, it can make a huge difference
12:15 AM
query optimizers are generally lost after 8-10 joins and end up using a
(slow) genetic algorithm to optimize query plans
12:15 AM filosofo
ok, but wouldn't there be a performance benefit?
12:15 AM sivel[a] is now known as sivel
12:16 AM filosofo
currently, to query a taxonomy you have to do a text search on varchar
12:16 AM
with a taxonomy table, you could make that an integer
12:16 AM ddebernardy
oh, that's what you mean
12:16 AM filosofo
db size would be smaller, too
12:17 AM ddebernardy
in this case yeah, agreeing with you, but the general trend in WP is to
use php arrays for this kind of stuff
12:17 AM
I mean, we're using a serialized option to store roles :D
12:18 AM filosofo
I think everyone would like to move away from that
12:18 AM
the roles, I mean
12:18 AM ddebernardy
yeah, I hope I'll find enough time to get this done in 2.9
12:19 AM
but there are still a couple of things we'll want in varchar fields even
if we do that, for the same of making thing pluginable
12:19 AM
take roles, for instance
12:19 AM filosofo
so a role is taxonomy
12:19 AM ddebernardy
by the same token as the taxo table you're describing, we'd want roles and
caps defined in tables with ids references in a user2role table
12:20 AM
trouble is, this makes heavy queries
12:20 AM
in practice, it's better to have an enum field to store roles, and caps
12:20 AM
enum is really an int with another name
12:20 AM
now, to make the thing extendable, we actually want a varchar in there
12:20 AM
and the same holds for taxonomies/terms
12:21 AM
whatever we do, we'll end up with a varchar field to make things
extendable
12:21 AM
and end up with the thingy containing post_tag, category, link_category
(and cities, countries, and whatever plugin authors want it to contain)
12:22 AM filosofo
problem with role as enum is that it doesn't allow easy custom roles,
right?
12:22 AM ddebernardy
exactly
12:22 AM
hence the need for a varchar in WP, even if it performs more poorly
12:23 AM filosofo
hmm, but see what I meant before is that in your wp_taxonomies table...
12:23 AM ddebernardy
yes?
12:23 AM filosofo
you'd have to query over zillions of varchars, instead of just a few in a
taxonomy table.
12:24 AM ddebernardy
no no, you'd still have a single one - to fetch post_tag, or category, or
link_category, or...
12:24 AM
picture it as the current term_taxonomy and terms combined
12:24 AM filosofo
right, if we combine present terms and taxonomy....
12:25 AM ddebernardy
and renamed for the sake of making things backwards compatible (we'd make
subqueries or views available for plugins that dig straight into it)
12:25 AM filosofo
then if we want to query for the "post_tag" "my term", we have to query
over the large terms table looking for "post_tag"
12:26 AM ddebernardy
it would contain the same number of rows as the current term_taxonomy
table
12:26 AM
it would merely have a few extra fields to play with, avoiding a join, and
adding for the possibility of extra indexes
12:26 AM filosofo
yes, that's why I think we need a taxonomy table.
12:27 AM ddebernardy
issue with a taxonomy table (as you're thinking, referencing what really
ends up being an array in WP, or so I'm understanding), has to do with the
register_taxonomy function
12:27 AM
I suspect
12:28 AM filosofo
just make register_taxonomy like add_rewrite_rule
12:28 AM
a one-time thing
12:28 AM ddebernardy
works too, but we end up breaking plugins in the process, if terms becomes
a different animal
12:29 AM
filosofo
And this would improve the query_posts queries,
12:30 AM
because you could do one query
12:31 AM ddebernardy
correct
12:31 AM
hence the need for rboren feedback
12:31 AM
I mean, I'd be all +1 to break existing plugins and change the semantics
of wp_terms
12:32 AM
but one of the core devs may end up wanting to keep backwards compat
}}}
--
Ticket URL: <http://core.trac.wordpress.org/ticket/5183#comment:16>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software
More information about the wp-trac
mailing list