Taxonomy Schema Proposal
|
View:
New views
20 Messages
—
Rating Filter:
Alert me
|
| < Prev | 1 - 2 - 3 | Next > |
 |
Re: Taxonomy Schema Proposal
by Steve Lewis-6
::
Rate this Message:
On 4/18/07, Jamie Talbot <wphackers@...> wrote:
> > > We'll still have the category base and tag base, right? In which case, > won't they just work as they > do now? anything site.tld/category/my_term/ would be queried as a > post_category, anything > site.tld/tag/my_term/ would be queried as a post_tag. At least, that's > how I understood Ryan's > suggestion. Why should we? If we are storing these things in one table, I think the term_slug ought to be a UNIQUE key. If I can have a distinct 'cocktails' tag and 'cocktails' category, then I stubbornly insist we stop storing them in the same table. :) -- SteveL _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Matt Mullenweg
::
Rate this Message:
Steve Lewis wrote:
> Why should we? If we are storing these things in one table, I think the > term_slug ought to be a UNIQUE key. If I can have a distinct 'cocktails' > tag and 'cocktails' category, then I stubbornly insist we stop storing them > in the same table. :) Check out the new schema, they're completely different and effectively in different tables. However, this type of argument is what made the discussion on trac so difficult and what led me to just put some code out there. -- Matt Mullenweg http://photomatt.net | http://wordpress.org http://automattic.com | http://akismet.com _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Steve Lewis-6
::
Rate this Message:
On 4/18/07, Matt Mullenweg <m@...> wrote:
> > Steve Lewis wrote: > > Why should we? If we are storing these things in one table, I think the > > term_slug ought to be a UNIQUE key. If I can have a distinct > 'cocktails' > > tag and 'cocktails' category, then I stubbornly insist we stop storing > them > > in the same table. :) > > Check out the new schema, they're completely different and effectively > in different tables. However, this type of argument is what made the > discussion on trac so difficult and what led me to just put some code > out there. > Color me confused. The new proposed schema on-list? As in the one that discusses 'terms' perhaps? The one I am replying to? Categories are not in a distinct table from Tags because they are both in wp_terms in the proposed schema I am replying to. I must have missed something. -- SteveL _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Matt Mullenweg
::
Rate this Message:
Steve Lewis wrote:
> Categories are not in a distinct table from Tags because they are both in > wp_terms in the proposed schema I am replying to. But terms are neither categories or tags, the definition is stored in a separate table so you can have both a category and tag named "cocktails." -- Matt Mullenweg http://photomatt.net | http://wordpress.org http://automattic.com | http://akismet.com _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
RE: Taxonomy Schema Proposal
by Computer Guru
::
Rate this Message:
> Steve Lewis wrote:
> > Categories are not in a distinct table from Tags because they are > both in > > wp_terms in the proposed schema I am replying to. > > But terms are neither categories or tags, the definition is stored in a > separate table so you can have both a category and tag named > "cocktails." Yep, I believe that ties in with BCNF db schema.. It's just a bit "weird" since WP hasn't been using this route - but this would be a huge step towards a proper db schema :) Hopefuly by using JOIN queries here we might be setting a precedent to further optimize the rest of WP's db-calls. It would be just _awesome_ to have WP use a fully BCNF db... though I'm not holding my breath for two or three years though :P Computer Guru NeoSmart Technologies http://neosmart.net/blog/ _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Matt Mullenweg
::
Rate this Message:
Computer Guru wrote:
> Yep, I believe that ties in with BCNF db schema.. It's just a bit "weird" > since WP hasn't been using this route - but this would be a huge step > towards a proper db schema :) For those unfamiliar with the terminology: http://en.wikipedia.org/wiki/Boyce-Codd_Normal_Form Is this actual BCNF though? > Hopefuly by using JOIN queries here we might be setting a precedent to > further optimize the rest of WP's db-calls. You have an interesting assumption that JOINs are more efficient. ;) Ultimately speed is going to be a big factor in any implementation, I hope that the 3 additional tables and related queries don't hit us too hard on the performance side. -- Matt Mullenweg http://photomatt.net | http://wordpress.org http://automattic.com | http://akismet.com _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Mark Jaquith
::
Rate this Message:
On Apr 18, 2007, at 5:04 PM, Matt Mullenweg wrote:
> Ultimately speed is going to be a big factor in any implementation, > I hope that the 3 additional tables and related queries don't hit > us too hard on the performance side. If the API for updating/adding/removing tags and post categories is solid (i.e. we won't expect people to have a reason to circumvent it), we can cache cats/tags in postmeta whenever they are updated. That would actually REMOVE a query from the existing system (as postmeta is already queried). The system could be fail-safe, so that if there is no appropriate _wp_categories postmeta entry, it can do the big JOIN'd query (and at that moment, populate the postmeta cache). That doesn't help us for Tag/Category views (which posts have X taxonomy?), but it helps us when querying tags/categories for a given set of posts. I'd assume that would be faster than doing a three-way JOIN'd query... it certainly sped UTW up. -- Mark Jaquith http://markjaquith.com/ Covered Web Services http://coveredwebservices.com/ _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Steve Lewis-6
::
Rate this Message:
On 4/18/07, Matt Mullenweg <m@...> wrote:
> > Steve Lewis wrote: > > Categories are not in a distinct table from Tags because they are both > in > > wp_terms in the proposed schema I am replying to. > > But terms are neither categories or tags, the definition is stored in a > separate table so you can have both a category and tag named "cocktails." > Sorry, I'm not buying that. Terms have a taxonomy which may be Category or Tag or some other. That means Categories and Tags are Terms. That suggests I shouldn't care which taxonomy is associated with the Term. I think it will be both easier to use for authors and easier to support for the community if *or* is considered to be *xor* in implementation. Hopefully I made that clear. -- SteveL _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Jamie Talbot
::
Rate this Message:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 Matt Mullenweg wrote: > Computer Guru wrote: >> Yep, I believe that ties in with BCNF db schema.. It's just a bit "weird" >> since WP hasn't been using this route - but this would be a huge step >> towards a proper db schema :) > > For those unfamiliar with the terminology: > > http://en.wikipedia.org/wiki/Boyce-Codd_Normal_Form > > Is this actual BCNF though? > It isn't actually. BCNF would be having a separate taxonomy lookup table. An even more normalised form would be having separate tags, categories and link cat tables, as well as the associated post2cat, post2tag etc. While initially I thought that would be the best way to go about it, Ryan's proposal actually makes much more sense in terms of being mostly normalised while future proofing without lots of future db alterations. A nice compromise. Cheers, Jamie. - -- http://jamietalbot.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGJq0arovxfShShFARAu0IAJ9eWJlul5J8p5mJH77pyt4XiuyoXgCfb/ds 6lEsg9ZiYtekP8EGV+4mrXY= =M/F8 -----END PGP SIGNATURE----- _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
|
|
|
Re: Taxonomy Schema Proposal
by Jamie Talbot
::
Rate this Message:
Ryan Boren wrote:
> I caught a bug that's laying me low at the moment. The stuff you are > proposing sounds pretty good, but my head isn't clear enough to think > it all the way through. Would you mind taking the proposed schema, > modifying it with your amendments, sprinkling in a few comments, and > submitting back to the list so we can see it as a whole? > > Ryan So here are my initial thoughts. The schema is almost identical to your proposed one, with the changes I suggested for an integer join, as well as alias stuff from elsewhere on this thread: <?php // terms contains the actual categories/tags/terms/classifiers/whatevers. It stores ID, name, and slug and alias group. CREATE TABLE $wpdb->terms ( term_id bigint(20) NOT NULL auto_increment, term_name varchar(55) NOT NULL default '', term_slug varchar(200) NOT NULL default '', term_group bigint(10) NOT NULL default 0 PRIMARY KEY (term_ID), UNIQUE KEY term_slug (term_slug) ); // term_taxonomy puts a term in the context of a taxonomy (link category, post category, or tag). Hierarchy is put here as well as counts. CREATE TABLE $wpdb->term_taxonomy ( term_taxonomy_id bigint(20) NOT NULL auto_increment, term_id bigint(20) NOT NULL default 0, taxonomy varchar(20) NOT NULL default 0, term_description longtext NOT NULL, parent bigint(20) NOT NULL default 0, count bigint(20) NOT NULL default 0, PRIMARY KEY (term_taxonomy_id), KEY (term_idtaxonomy) ); // term_relationships relates a term to a post or link or undeclared future object thingy. The relationship is placed within the context of a given taxonomy. CREATE TABLE $wpdb->term_relationships ( object_id bigint(20) NOT NULL default 0, term_taxonomy_id bigint(20) NOT NULL default 0, PRIMARY KEY (object_ID), KEY (term_taxonomy_id) ); // Some default definitions that might be useful. define ('TAXONOMY_POST_CATEGORY', 1); define ('TAXONOMY_LINK_CATEGORY', 2); define ('TAXONOMY_POST_TAG', 4); // Very basic example API. This is half pseudocode, mostly real. Not by any means final. class WPTaxonomy { static var taxonomies = array( TAXONOMY_POST_CATEGORY => 'post_category', TAXONOMY_LINK_CATEGORY => 'link_category', TAXONOMY_POST_TAG => 'post_tag' ); // Adds a new term, also adds alias relationship if necessary. Handles numeric or slug-based aliases. function add_term($term, $alias_of = '') { global $wpdb; $term_slug = sanitize($term); if ($alias_of) { // ctype_digit is faster than is_numeric, with the caveat that the arg must be in a string. $clause = (ctype_digit("$alias_of")) ? "term_id = $alias_of" : "term_slug = '$alias_of'"; $alias = $wpdb->fetch_row("SELECT term_id, term_group FROM $wpdb->terms WHERE $clause"); if ($alias->term_group) // The alias we want is already in a group, so let's use that one. $term_group = $alias->term_group; } else { // The alias isn't in a group, so let's create a new one and firstly add the alias term to it. $term_group = $wpdb->get_var("SELECT MAX() term_group FROM $wpdb->terms GROUP BY term_group") + 1; $wpdb->query("UPDATE $wpdb->terms SET term_group = $term_group WHERE term_id = $alias->term_id"); } } else { $term_group = 0; } // Because we have a unique key on term_slug, this will update or insert. This means we can use the same function // just to make an alias relationship between existing terms. The term group has already been set above. $wpdb->query("REPLACE INTO $wpdb->terms (term_name, term_slug, term_group) VALUES ($term, $term_slug, $term_group)"); } // Adds or increments a term taxonomy entry (for example, 'city' as a post_tag). // Accepts either term_id or term_slug, for completeness... function add_term_taxonomy($term, $taxonomy, $description = '', $parent = 0) { $clause = (ctype_digit("$term")) ? "t.term_id = $term" : "t.term_slug = '$term'"; if ($count = $wpdb->query("SELECT tt.count FROM $wpdb->term_taxonomy AS tt INNER JOIN $wpdb->terms AS t ON tt.term_id = t.term_id WHERE tt.taxonomy = '$taxonomy' AND $clause")) { // We'll be updating whatever's there, and incrementing the count. Not a huge fan of // storing counts by the way, but accept that there might be valid optimisation reasons for doing so. $count++; $wpdb->query("UPDATE $wpdb->term_taxonomy SET count = $count"); } else { // There was no existing entry for this term and taxonomy type, so add a new one, using the supplied // description and parent, with a count of 1. $wpdb->query("INSERT INTO $wpdb->term_taxonomy (term_id, taxonomy, term_description, parent, count) SELECT term_id, '$taxonomy', '$description', $parent, 1 FROM $wpdb->terms AS t1 WHERE $clause"); } } // Accepts either term_id or term_slug. function get_aliases($term) { global $wpdb; $clause = (ctype_digit("$term")) ? "t1.term_id = $term" : "t1.term_slug = '$term'"; return $wpdb->fetch("SELECT t2.* FROM $wpdb->terms AS t1 INNER JOIN $wpdb->terms AS t2 ON t1.term_group = t2.term_group WHERE t1.term_group != 0 AND $clause"); } // Gets the counts of supplied term, for specified taxonomies. // Could be extended to accept an array of terms. // Returns an object with members for each taxonomy if more than one is specified. // This example has taxonomies specified as strings, but you could also do it based on integers, either from the default defines, or from a taxonomy index added by a plugin using add_taxonomy(); function get_count($term, $taxonomies) { global $wpdb; if (ctype_digit("$term")) { $clause = "term_id = $term"; } else { $join = "INNER JOIN $wpdb->terms AS t ON t.term_id = tt.term_id"; $clause = "t.term_slug = '$term'"; } if (is_array($taxonomies)) { $taxonomies = "'" implode("','", $taxonomies) . "'"; if (!$counts = $wpdb->fetch("SELECT taxonomy, count FROM $wpdb->term_taxonomy AS tt $join WHERE tt.taxonomy IN ($taxonomies) AND $clause")) { // Does $wpdb->fetch return an empty array, when there are no results? $counts = array(); } foreach ($counts as $count) { // Note that for this to work, taxonomies must use underscores, not hyphens, in their names... $return->{$count->taxonomy} = $count->count; } } else { $return = $wpdb->get_var("SELECT count FROM $wpdb->term_taxonomy AS tt $join WHERE tt.taxonomy = '$taxonomies' AND $clause LIMIT 1"); } return $return; } // Adds a new taxonomy and returns the index it was added at. static function add_taxonomy($taxonomy) { $index = last(array_flip(WPTaxonomy::taxonomies)) * 2; WPTaxonomy::taxonomies[$index] = $taxonomy; return $index; } } Plugins can say <?php $my_taxonomy_index = add_taxonomy('my_awesome_taxonomy'); ?> then use that index, or 'my_awesome_taxonomy' to refer to it later on. Pretty flexible, nice and extensible, mostly normalised and with the opportunity to flesh out a full API. For a more classical OO approach, rather than just using a class as a container, something like class WPTerm {} might be nice, so you could do: $term = new WPTerm('city'); $term->get_aliases(); $counts = $term->get_count(TAXONOMY_LINK_CATEGORY | TAXONOMY_POST_TAG); // counts->link_category = 5; // counts->post_tag = 8; $term->find_ancestors(); Again where the WPTerm constructor would accept either an term_id or a term_slug. Not sure what people would think of that? I'll leave it there for now :) Cheers, Jamie. ?> -- http://jamietalbot.com _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Jamie Talbot
::
Rate this Message:
Steve Lewis wrote:
> On 4/18/07, Matt Mullenweg <m@...> wrote: >> >> Steve Lewis wrote: >> > Categories are not in a distinct table from Tags because they are both >> in >> > wp_terms in the proposed schema I am replying to. >> >> But terms are neither categories or tags, the definition is stored in a >> separate table so you can have both a category and tag named "cocktails." >> > > Sorry, I'm not buying that. Terms have a taxonomy which may be Category or > Tag or some other. That means Categories and Tags are Terms. That > suggests I shouldn't care which taxonomy is associated with the Term. That doesn't necessarily follow. I could want a post_category called news, and want to tag certain posts with news. In one context, I want to retrieve all posts that have the category news. In the other, I only want those individually tagged with news. These are not necessarily the same subsets. Now granted, you wouldn't be able to differentiate those in a URL without a category and tag base, but we do have those and as far as I can tell, they're not going away (though they are becoming customisable perhaps). > I think it > will be both easier to use for authors and easier to support for the > community if *or* is considered to be *xor* in implementation. Hopefully I > made that clear. Isn't that exactly what we *don't* want? If I understand you correctly, you want to a term to be either a post_category, or a post_tag, but not both? What happens when you add a link_category called 'friends'? That means under your proposal you'd never be able to tag anything as 'friends' or put posts in a category called 'friends'. As I understand it, this was a big complaint about the integration of link and post categories and exactly the thing we're trying to avoid here. And after all this, there is a unique key on the proposed terms table term_slug anyway, as well as on the term taxonomy table for the (term_id, taxonomy) tuple. Or if there isn't I just missed it off and there should be. Cheers, Jamie. -- http://jamietalbot.com _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Bas Bosman
::
Rate this Message:
> CREATE TABLE $wpdb->terms (
> term_id bigint(20) NOT NULL auto_increment, > term_name varchar(55) NOT NULL default '', > term_slug varchar(200) NOT NULL default '', > term_group bigint(10) NOT NULL default 0 > PRIMARY KEY (term_ID), > UNIQUE KEY term_slug (term_slug) > ); I think it's just a typo, but in every iteration of the scheme i've seen on this list there is a field called term_id and a primary key called term_ID? The same also applies to object_id in one of the other tables. Bas Bosman (Nazgul) _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Steve Lewis-6
::
Rate this Message:
On 4/19/07, Jamie Talbot <wphackers@...> wrote:
> > > Sorry, I'm not buying that. Terms have a taxonomy which may be Category > or > > Tag or some other. That means Categories and Tags are Terms. That > > suggests I shouldn't care which taxonomy is associated with the Term. > > That doesn't necessarily follow. I could want a post_category called > news, and want to tag certain It doesn't have to. The example you provided is understood. > I think it > > will be both easier to use for authors and easier to support for the > > community if *or* is considered to be *xor* in > implementation. Hopefully I > > made that clear. > > Isn't that exactly what we *don't* want? If I understand you correctly, > you want to a term to be > either a post_category, or a post_tag, but not both? We want different things. I don't want to share tables across different taxonomies unless the terms are exclusive. As I understand it, this was a big complaint about the > integration of link and post categories and exactly the thing we're trying > to avoid here. Our understanding of the complaints re: link and post categories is different. The complaints I am familiar with were that the release that merged them broke APIs without sufficient warning. My recollections are most likely the least reliable in the room because they were personal and emotional when my stuff got broken and I had to roll back. If you want that functionality, it's not clear what exactly you are buying by having everything in one table. Maybe I was clear on some value in doing that before, but I forgot. Either way, that argument seems to be going nowhere. After grousing as much as I have, i should take a moment to review the php you outlined--and actually contribute something useful[gdr]. I haven't read it yet, and will try to make some time to soon, but if you do put a UNIQUE on the term_slug then you might find it difficult to create a 'friends' tag if you already have a 'friends' category, no? To do what you want, you should put a compound UNIQUE on the taxonomy table: UNIQUE(taxonomy, term_id). -- SteveL _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Ryan Boren
::
Rate this Message:
On 4/19/07, Jamie Talbot <wphackers@...> wrote:
> // terms contains the actual categories/tags/terms/classifiers/whatevers. It stores ID, name, and > slug and alias group. > CREATE TABLE $wpdb->terms ( > term_id bigint(20) NOT NULL auto_increment, > term_name varchar(55) NOT NULL default '', > term_slug varchar(200) NOT NULL default '', > term_group bigint(10) NOT NULL default 0 > PRIMARY KEY (term_ID), > UNIQUE KEY term_slug (term_slug) > ); > > // term_taxonomy puts a term in the context of a taxonomy (link category, post category, or tag). > Hierarchy is put here as well as counts. > CREATE TABLE $wpdb->term_taxonomy ( > term_taxonomy_id bigint(20) NOT NULL auto_increment, > term_id bigint(20) NOT NULL default 0, > taxonomy varchar(20) NOT NULL default 0, > term_description longtext NOT NULL, > parent bigint(20) NOT NULL default 0, > count bigint(20) NOT NULL default 0, > PRIMARY KEY (term_taxonomy_id), > KEY (term_idtaxonomy) > ); > > // term_relationships relates a term to a post or link or undeclared future object thingy. The > relationship is placed within the context of a given taxonomy. > CREATE TABLE $wpdb->term_relationships ( > object_id bigint(20) NOT NULL default 0, > term_taxonomy_id bigint(20) NOT NULL default 0, > PRIMARY KEY (object_ID), > KEY (term_taxonomy_id) > ); So each term_taxonomy entry has its own unique id stored in term_taxonomy_id? Given an object_id, how does one find all relationships in a specific taxonomy since there is no taxonomy field in term_relationships? > // Adds a new term, also adds alias relationship if necessary. Handles numeric or slug-based aliases. > function add_term($term, $alias_of = '') { > global $wpdb; > $term_slug = sanitize($term); > if ($alias_of) { > // ctype_digit is faster than is_numeric, with the caveat that the arg must be in a string. > $clause = (ctype_digit("$alias_of")) ? "term_id = $alias_of" : "term_slug = '$alias_of'"; Using a year as a category/tag is pretty common. "2007" would be misidentified as term ID. > // Adds a new taxonomy and returns the index it was added at. > static function add_taxonomy($taxonomy) { > $index = last(array_flip(WPTaxonomy::taxonomies)) * 2; > WPTaxonomy::taxonomies[$index] = $taxonomy; > return $index; > } > } > > Plugins can say <?php $my_taxonomy_index = add_taxonomy('my_awesome_taxonomy'); ?> then use that > index, or 'my_awesome_taxonomy' to refer to it later on. Pretty flexible, nice and extensible, > mostly normalised and with the opportunity to flesh out a full API. We have to be careful about the id changing depending on plugin load order, but we can work that out. > For a more classical OO approach, rather than just using a class as a container, something like > class WPTerm {} might be nice, so you could do: > > $term = new WPTerm('city'); > $term->get_aliases(); > $counts = $term->get_count(TAXONOMY_LINK_CATEGORY | TAXONOMY_POST_TAG); > // counts->link_category = 5; > // counts->post_tag = 8; > $term->find_ancestors(); > > Again where the WPTerm constructor would accept either an term_id or a term_slug. Not sure what > people would think of that? Fine by me. Ryan _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
by Steve Lewis-6
::
Rate this Message:
Poor form, replying to self...
On 4/19/07, Steve Lewis <stevelle@...> wrote: > > but if you do put a UNIQUE on the term_slug then you might find it > difficult to create a 'friends' tag if you already have a 'friends' > category, no? To do what you want, you should put a compound UNIQUE on the > taxonomy table: UNIQUE(taxonomy, term_id). > Scratch that, you want the UNIQUE on the term_slug. The terms to taxonomy relation isn't 1:1. I believe you still want your KEY (taxonomy, term_id) to be a UNIQUE, however. -- SteveL _______________________________________________ wp-hackers mailing list wp-hackers@... http://lists.automattic.com/mailman/listinfo/wp-hackers |
|
|
Re: Taxonomy Schema Proposal
|
