Changeset 338


Ignore:
Timestamp:
Jul 27, 2006, 4:55:41 PM (13 years ago)
Author:
nick
Message:

Add a method to let you find nodes without a given metadata (type, or type+value). Made the metadata finding code a lot more common, and fixed a long standing bug with case sensitive matching and MySQL

Location:
wiki-toolkit/trunk
Files:
5 edited

Legend:

Unmodified
Added
Removed
  • wiki-toolkit/trunk/lib/Wiki/Toolkit.pm

    r327 r338  
    399399}
    400400
     401=item B<list_nodes_by_missing_metadata>
     402Returns nodes where either the metadata doesn't exist, or is blank
     403   
     404Unlike list_nodes_by_metadata(), the metadata value is optional (the
     405metadata type is required).
     406
     407  # All nodes missing documentation
     408  my @nodes = $store->list_nodes_by_missing_metadata(
     409      metadata_type  => "category",
     410      metadata_value => "documentation",
     411      ignore_case    => 1,   # optional but recommended (see below)
     412  );
     413
     414  # All nodes which don't have a latitude defined
     415  my @nodes = $store->list_nodes_by_missing_metadata(
     416      metadata_type  => "latitude"
     417  );
     418=cut
     419sub list_nodes_by_missing_metadata {
     420    my ($self, @args) = @_;
     421    $self->store->list_nodes_by_missing_metadata( @args );
     422}
     423
    401424=item B<list_recent_changes>
    402425
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Store/Database.pm

    r336 r338  
    15911591    $sth->execute( $type, $self->charset_encode($value) );
    15921592    my @nodes;
    1593     while ( my ($node) = $sth->fetchrow_array ) {
     1593    while ( my ($id, $node) = $sth->fetchrow_array ) {
    15941594        push @nodes, $node;
    15951595    }
     1596    return @nodes;
     1597}
     1598
     1599=item B<list_nodes_by_missing_metadata>
     1600Returns nodes where either the metadata doesn't exist, or is blank
     1601
     1602Unlike list_nodes_by_metadata(), the metadata value is optional.
     1603
     1604  # All nodes missing documentation
     1605  my @nodes = $store->list_nodes_by_missing_metadata(
     1606      metadata_type  => "category",
     1607      metadata_value => "documentation",
     1608      ignore_case    => 1,   # optional but recommended (see below)
     1609  );
     1610
     1611  # All nodes which don't have a latitude defined
     1612  my @nodes = $store->list_nodes_by_missing_metadata(
     1613      metadata_type  => "latitude"
     1614  );
     1615=cut
     1616sub list_nodes_by_missing_metadata {
     1617    my ($self, %args) = @_;
     1618    my ( $type, $value ) = @args{ qw( metadata_type metadata_value ) };
     1619    return () unless $type;
     1620
     1621    my $dbh = $self->dbh;
     1622    if ( $args{ignore_case} ) {
     1623        $type  = lc( $type  );
     1624        $value = lc( $value );
     1625    }
     1626
     1627        my @nodes;
     1628
     1629        # If the don't want to match by value, then we can do it with
     1630        #  a LEFT OUTER JOIN, and either NULL or LENGTH() = 0
     1631        if( ! $value ) {
     1632                my $sql = $self->_get_list_by_missing_metadata_sql(
     1633                                                                                ignore_case => $args{ignore_case}
     1634                      );
     1635                my $sth = $dbh->prepare( $sql );
     1636                $sth->execute( $type );
     1637
     1638                while ( my ($id, $node) = $sth->fetchrow_array ) {
     1639                push @nodes, $node;
     1640                }
     1641    } else {
     1642                # To find those without the value in this case would involve
     1643                #  some seriously brain hurting SQL.
     1644                # So, cheat - find those with, and return everything else
     1645                my @with = $self->list_nodes_by_metadata(%args);
     1646                my %with_hash;
     1647                foreach my $node (@with) { $with_hash{$node} = 1; }
     1648
     1649                my @all_nodes = $self->list_all_nodes();
     1650                foreach my $node (@all_nodes) {
     1651                        unless($with_hash{$node}) {
     1652                                push @nodes, $node;
     1653                        }
     1654                }
     1655        }
     1656
    15961657    return @nodes;
    15971658}
     
    16081669    my ($self, %args) = @_;
    16091670    if ( $args{ignore_case} ) {
    1610         return "SELECT node.name "
     1671        return "SELECT node.id, node.name "
    16111672             . "FROM node "
    16121673             . "INNER JOIN metadata "
    1613              . "   ON (node.id = metadata.node_id) "
    1614              . "WHERE node.version=metadata.version "
    1615              . "AND lower(metadata.metadata_type) = ? "
    1616              . "AND lower(metadata.metadata_value) = ? ";
     1674             . "   ON (node.id = metadata.node_id "
     1675             . "       AND node.version=metadata.version) "
     1676             . "WHERE ". $self->_get_lowercase_compare_sql("metadata.metadata_type")
     1677             . " AND ". $self->_get_lowercase_compare_sql("metadata.metadata_value");
    16171678    } else {
    1618         return "SELECT node.name "
     1679        return "SELECT node.id, node.name "
    16191680             . "FROM node "
    16201681             . "INNER JOIN metadata "
    1621              . "   ON (node.id = metadata.node_id) "
    1622              . "WHERE node.version=metadata.version "
    1623              . "AND metadata.metadata_type = ? "
    1624              . "AND metadata.metadata_value = ? ";
     1682             . "   ON (node.id = metadata.node_id "
     1683             . "       AND node.version=metadata.version) "
     1684             . "WHERE ". $self->_get_casesensitive_compare_sql("metadata.metadata_type")
     1685             . " AND ". $self->_get_casesensitive_compare_sql("metadata.metadata_value");
    16251686    }
    16261687}
     
    16281689=item B<_get_list_by_missing_metadata_sql>
    16291690Return the SQL to do a match by missing metadata. Should expect the metadata
    1630 type as the first SQL parameter. If $args{with_value} is set, should expect
    1631 the metadata value as the second SQL parameter.
     1691type as the first SQL parameter.
    16321692
    16331693If possible, should take account of $args{ignore_case}
     
    16401700        my $sql = "";
    16411701    if ( $args{ignore_case} ) {
    1642         $sql = "SELECT node.name "
     1702        $sql = "SELECT node.id, node.name "
    16431703             . "FROM node "
    16441704             . "LEFT OUTER JOIN metadata "
    1645              . "   ON (node.id = metadata.node_id) "
    1646              . "WHERE node.version=metadata.version "
    1647              . "AND lower(metadata.metadata_type) = ? ";
     1705             . "   ON (node.id = metadata.node_id "
     1706             . "       AND node.version=metadata.version "
     1707             . "       AND ". $self->_get_lowercase_compare_sql("metadata.metadata_type")
     1708                         . ")";
    16481709    } else {
    1649         $sql = "SELECT node.name "
     1710        $sql = "SELECT node.id, node.name "
    16501711             . "FROM node "
    1651              . "LEFT JOIN metadata "
    1652              . "   ON (node.id = metadata.node_id) "
    1653              . "WHERE node.version=metadata.version "
    1654              . "AND metadata.metadata_type = ? ";
    1655     }
    1656 
    1657         if( $args{with_value} ) {
    1658                 if ( $args{ignore_case} ) {
    1659                 $sql .= "AND NOT lower(metadata.metadata_value) = ? ";
    1660                 } else {
    1661                 $sql .= "AND NOT metadata.metadata_value = ? ";
    1662                 }
    1663         } else {
    1664                 $sql .= "AND (metadata.metadata_value IS NULL OR LENGHT(metadata.metadata_value) == 0) ";
    1665         }
     1712             . "LEFT OUTER JOIN metadata "
     1713             . "   ON (node.id = metadata.node_id "
     1714             . "       AND node.version=metadata.version "
     1715             . "       AND ". $self->_get_casesensitive_compare_sql("metadata.metadata_type")
     1716             . ")";
     1717    }
     1718
     1719        $sql .= "WHERE (metadata.metadata_value IS NULL OR LENGTH(metadata.metadata_value) = 0) ";
    16661720        return $sql;
     1721}
     1722
     1723sub _get_lowercase_compare_sql {
     1724        my ($self, $column) = @_;
     1725        # SQL 99 version
     1726    #  Can be over-ridden by database-specific subclasses
     1727        return "lower($column) = ?";
     1728}
     1729sub _get_casesensitive_compare_sql {
     1730        my ($self, $column) = @_;
     1731        # SQL 99 version
     1732    #  Can be over-ridden by database-specific subclasses
     1733        return "$column = ?";
    16671734}
    16681735
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Store/MySQL.pm

    r335 r338  
    9494}
    9595
     96sub _get_casesensitive_compare_sql {
     97    my ($self, $column) = @_;
     98    return "BINARY $column = ?";
     99}
     100
    961011;
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Store/SQLite.pm

    r337 r338  
    9292}
    9393
    94 sub _get_list_by_metadata_sql {
    95     my ($self, %args) = @_;
    96     if ( $args{ignore_case} ) {
    97         return "SELECT node.id, node.name FROM node, metadata"
    98              . " WHERE node.id=metadata.node_id"
    99              . " AND node.version=metadata.version"
    100              . " AND metadata.metadata_type LIKE ? "
    101              . " AND metadata.metadata_value LIKE ? ";
    102     } else {
    103         return "SELECT node.id, node.name FROM node, metadata"
    104              . " WHERE node.id=metadata.node_id"
    105              . " AND node.version=metadata.version"
    106              . " AND metadata.metadata_type = ? "
    107              . " AND metadata.metadata_value = ? ";
    108     }
    109 }
    110 
    111 sub _get_list_by_missing_metadata_sql {
    112     my ($self, %args) = @_;
    113 
    114         my $sql = "";
    115     if ( $args{ignore_case} ) {
    116         $sql = "SELECT node.id, node.name "
    117                          . "FROM node "
    118                          . "LEFT OUTER JOIN metadata "
    119              . "   ON (node.id = metadata.node_id "
    120              . "       AND node.version=metadata.version "
    121              . "       AND metadata.metadata_type LIKE ?) ";
    122     } else {
    123        $sql = "SELECT node.id, node.name "
    124              . "FROM node "
    125              . "LEFT OUTER JOIN metadata "
    126              . "   ON (node.id = metadata.node_id "
    127              . "       AND node.version=metadata.version "
    128              . "       AND metadata.metadata_type = ?) ";
    129     }
    130 
    131         $sql .= "WHERE (metadata.metadata_value IS NULL OR LENGTH(metadata.metadata_value) = 0) ";
    132         return $sql;
     94sub _get_lowercase_compare_sql {
     95    my ($self, $column) = @_;
     96    return "$column LIKE ?";
    13397}
    13498
  • wiki-toolkit/trunk/t/010_metadata.t

    r209 r338  
    66    plan skip_all => "no backends configured";
    77} else {
    8     plan tests => ( 14 * scalar @Wiki::Toolkit::TestLib::wiki_info );
     8    plan tests => ( 22 * scalar @Wiki::Toolkit::TestLib::wiki_info );
    99}
    1010
     
    1515    $wiki->write_node( "Reun Thai", "A restaurant", undef,
    1616        { postcode => "W6 9PL",
    17           category => [ "Thai Food", "Restaurant", "Hammersmith" ] } );
     17          category => [ "Thai Food", "Restaurant", "Hammersmith" ],
     18          latitude => "51.911", longitude => "" } );
    1819    my %node = $wiki->retrieve_node( "Reun Thai" );
    1920    my $data = $node{metadata}{postcode};
     
    4546        metadata_type  => "category",
    4647        metadata_value => "hammersmith",
     48        ignore_case    => 0,
     49    );
     50    is_deeply( [ sort @nodes ], [ ],
     51               "ignore_case => 0 doesn't ignore case of metadata_value" );
     52    @nodes = $wiki->list_nodes_by_metadata(
     53        metadata_type  => "category",
     54        metadata_value => "hammersmith",
    4755        ignore_case    => 1,
    4856    );
     
    5664    is_deeply( [ sort @nodes ], [ "Reun Thai", "The Old Trout" ],
    5765               "...and case of metadata_type" );
     66
     67
     68    # Test list_nodes_by_missing_metadata
     69    #  Shouldn't get any if we search on category
     70    @nodes = $wiki->list_nodes_by_missing_metadata(
     71                            metadata_type => "category"
     72    );
     73    is( scalar @nodes, 0, "All have metadata category" );
     74    #  By latitude, should only get The Old Trout+The Three Cups
     75    @nodes = $wiki->list_nodes_by_missing_metadata(
     76                            metadata_type => "latitude"
     77    );
     78    is_deeply( [ sort @nodes ], [ "The Old Trout", "The Three Cups" ],
     79                    "By lat, not Reun Thai" );
     80    #  By longitude, we should get all (Reun Thai has it blank)
     81    @nodes = $wiki->list_nodes_by_missing_metadata(
     82                            metadata_type => "longitude"
     83    );
     84    is_deeply( [ sort @nodes ], [ "Reun Thai", "The Old Trout", "The Three Cups" ], "By long, get all" );
     85    #  With category=Pub, we should get only the Reun Thai
     86    @nodes = $wiki->list_nodes_by_missing_metadata(
     87                            metadata_type => "category",
     88                            metadata_value => "Pub"
     89    );
     90    is_deeply( [ sort @nodes ], [ "Reun Thai" ], "Reun Thai not a pub" );
     91    #  With Category, we should get all
     92    @nodes = $wiki->list_nodes_by_missing_metadata(
     93                            metadata_type => "Category"
     94    );
     95    is_deeply( [ sort @nodes ], [ "Reun Thai", "The Old Trout", "The Three Cups" ], "By Category, get all" );
     96    #  With category=hammersmith, we should get all
     97    @nodes = $wiki->list_nodes_by_missing_metadata(
     98                            metadata_type => "category",
     99                            metadata_value => "hammersmith"
     100    );
     101    is_deeply( [ sort @nodes ], [ "Reun Thai", "The Old Trout", "The Three Cups" ], "By category=hammersmith (case sensitive), get all" );
     102    #  But with category=hammersmith+case insensitive, shouldn't get any
     103    @nodes = $wiki->list_nodes_by_missing_metadata(
     104                            metadata_type => "category",
     105                            metadata_value => "hammersmith",
     106                            ignore_case => 1
     107    );
     108    is_deeply( [ sort @nodes ], [ "The Three Cups" ], "By category=hammersmith (ci), get all but the three cups" );
     109
    58110
    59111    %node = $wiki->retrieve_node("The Three Cups");
     
    73125    is_deeply( [ sort @nodes ], [ "The Old Trout" ],
    74126               "...as does deleting a node" );
     127
    75128
    76129    # Check that deleting a node really does clear out the metadata.
Note: See TracChangeset for help on using the changeset viewer.