Changeset 454

Timestamp:

10/18/08 13:48:32 (4 years ago)

Author:

nick

Message:

Complete support for store->list_metadata_by_type, which returns a list of all the metadata values

Location:

wiki-toolkit/trunk

Files:

• Changes (modified) (1 diff)
• MANIFEST (modified) (1 diff)
• lib/Wiki/Toolkit/Store/Database.pm (modified) (2 diffs)
• t/061_list_metadata_by_type.t (added)

wiki-toolkit/trunk/Changes

@@ +1 @@
+0.77    ???
+        Complete support for store->list_metadata_by_type, which
+          returns a list of all the metadata values
+
 0.76    13 July 2008
         Really add missing prereq of DBI!

wiki-toolkit/trunk/MANIFEST

@@ -65 +65 @@
 t/052_sqlite_store.t
 t/060_recent_changes_new_only.t
+t/061_list_metadata_by_type.t
 t/100_formatting.t
 t/101_default_formatter.t

wiki-toolkit/trunk/lib/Wiki/Toolkit/Store/Database.pm

@@ -1954 +1954 @@
     List all the currently defined values of the given type of metadata.
 
-    Will only work with the latest moderated version
+    Will only return data from the latest moderated version of each node
 
     # List all of the different metadata values with the type 'category'
@@ -1964 +1964 @@
     my ($self, $type) = @_;
 
-    return 0 unless $type;
+    return undef unless $type;
+    my $dbh = $self->dbh;
+
+    # Ideally we'd do this as one big query
+    # However, this would need a temporary table on many
+    #  database engines, so we cheat and do it as two
+    my $nv_sql = 
+       "SELECT node_id, MAX(version) ".
+       "FROM content ".
+       "WHERE moderated ".
+       "GROUP BY node_id ";
+    my $sth = $dbh->prepare( $nv_sql );
+    $sth->execute();
+
+    my @nv_where;
+    while(my @results = $sth->fetchrow_array) {
+        my ($node_id, $version) = @results;
+        my $where = "(node_id=$node_id AND version=$version)";
+        push @nv_where, $where;
+    }
+
+    # Now the metadata bit
+    my $sql = 
+       "SELECT DISTINCT metadata_value ".
+       "FROM metadata ".
+       "WHERE metadata_type = ? ".
+       "AND (".
+       join(" OR ", @nv_where).
+       ")";
+    $sth = $dbh->prepare( $sql );
+    $sth->execute($type);
+
+    my $values = $sth->fetchall_arrayref([0]);
+    return ( map { $self->charset_decode( $_->[0] ) } (@$values) );
 }