[wp-hackers] Taxonomy Schema Proposal

[Jamie Talbot]

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