Metadata::DB::Analizer - methods to analize database metadata table entries

   use DBI;
   use Metadata::DB::Analizer;

   my $absdb = '/home/myself/metadata.db';

   my $dbh = DBI->connect("dbi:SQLite:dbname=$abs","","");

   my $a = Metadata::DB::Analizer->new({ DBH => $dbh });

   # we are storing metadata entries for how many things?
   $a->get_records_count;

   # what are all the attribute labels we are storing for all things?
   my $atts = $a->get_attributes;

   for my $att (@$atts) {

      # how many different kinds of values does this att have for all things?
      my $count = $a->attribute_all_unique_values_count($att);
      my $att_is_number = $a->attribute_type_is_number($att);

      print "Attribute $att has $count possible value options. The values are all numbers? $att_is_number\n";
   }  

   my $dbh = your_database_connection_handle();

   my $a = Metadata::DB::Analizer->new({ DBH => $dbh });

   my $i = Metadata::DB::Search::InterfaceHTML({ DBH => $dbh });

   $i->search_attributes_selected_clear;
   $i->search_attributes_selected_add('age','height','name','office');

   my @attribute_names = sort grep { !/path/ } @{ $self->get_search_attributes }; 

   $self->search_attributes_selected_clear;
   $self->search_attributes_selected_add( @attribute_names );

   for my $att_name ( grep { /client/ } @attribute_names ){

      $self->attribute_option_list_limit( $att_name => 1000 );

   }

   my $options = generate_attribute_option_list($dbh,'hair_color');

   # $options = [qw( blonde redhead brunette )];

   id mkey        mval
   1  hair_color  auburn

   my $options = generate_attribute_option_list($dbh,'first_name',1000000);

   my $limit = 
      $self->attribute_option_list_limt( $attribute_name => $number );

   my $limit_retrieved = 
      $self->attribute_option_list_limit( $attribute_name );

   my $default_limit_for_all_not_specifically_set =
      $self->attribute_option_list_limit;

   id | mkey | mval

   $object_instance->table_metadata_name('metadata_cars');
   $object_instance->table_metadata_column_name_key('attribute');

package Metadata::DB::Analizer;
use strict;
use Carp;
use warnings;
use LEOCHARRE::DEBUG;
use vars qw($VERSION);
use base 'Metadata::DB::Base';
use LEOCHARRE::Class::Accessors 
   multi => ['search_attributes_selected','_attributes'], 
   single => ['attribute_option_list_limit'];

no warnings 'redefine';
$VERSION = sprintf "%d.%02d", q$Revision: 1.3 $ =~ /(\d+)/g;



# LIST ALL ATTS AVAILABLE
*get_attributes = \&_attributes;
sub _attributes {
   my $self = shift;
   unless( $self->_attributes_count ){
      debug('_attributes_count returned none.. ');
      my $atts = $self->_distinct_attributes_arrayref;
	ref $atts eq 'ARRAY' or die('not array ref');
      debug("got atts scalar: [".scalar @$atts."]");
      for(@$atts){
         $self->_attributes_add($_);
      }
         
   }
   return $self->_attributes_arrayref;
}

sub _distinct_attributes_arrayref {
   my $self = shift;
   
   my $keys = $self->dbh->selectcol(
      sprintf
      "SELECT DISTINCT %s FROM %s",
      $self->table_metadata_column_name_key,
      $self->table_metadata_name,
      );
      debug("keys @$keys\n");
   return $keys;
}






# get ratio of attributes, how many 'age', 'name', and 'color' etc atts are there

sub get_attributes_ratios {
   my $self = shift;

   my $at = $self->get_attributes_counts;
   
   $at->{all} or croak('no atts in table ?');

   my $attr ={};

   for my $att ( keys %$at){      

      # total entries
      $attr->{$att} = 
         int (($at->{$att} * 100) / $at->{all} );      
      
   }

   delete $attr->{all};
   return $attr;
}

sub get_attributes_by_ratio {
   my $self = shift;   

   my $_att = $self->get_attributes_ratios;

   my @atts = sort { $_att->{$b} <=> $_att->{$a} } keys %$_att;
   return \@atts;
}


sub get_attributes_counts {
   my $self = shift;

   my $attr ={};
   my $_atts = $self->get_attributes;

   my $total=0;
   
   for my $att (@$_atts){      

      # total entries
      $attr->{$att} = $self->attribute_all_unique_values_count($att);      
      $total+= $attr->{$att};
   }

   # actaully we can just add all the vals, can get diff numb.. but.. whatever- not urgent.
   $attr->{ all } = $total; #$self->dbh->rows_count($self->table_metadata_name);

   return $attr;   
}


sub get_records_count {
   my $self = shift;

   my $idname = $self->table_metadata_column_name_id;
   my $tablename = $self->table_metadata_name;
   my $q = $self->dbh->prepare("SELECT count(DISTINCT $idname) FROM $tablename");
   $q->execute;

   my $count;
   $q->bind_columns(\$count);
   $q->fetch;
   $count ||=0;
   return $count;
}

# end analysis












sub search_attributes_selected {
   my $self = shift;
   
   unless( $self->search_attributes_selected_count ){
      debug("no search attributes list has been selected, we will chose all");
      my @params = sort @{ $self->get_attributes };
      debug("params are [@params]\n");
      for (@params){
         $self->search_attributes_selected_add($_);
      }
   }

   # weed out ones that have one option only? maybe not since the alternative is none..
   # because one option for an att, the alternative is still valid.
   

   return $self->search_attributes_selected_arrayref;
}




# start single att methods
# INTERFACE

# for each of the attributes, how many variations are there?
# if there are less then x, then make it a drop down box

# this is not meant to be used online, only offline, as a regeneration of query interface


# if they are more then x choices, then return false
# what we used this for is making a drop down 
sub attribute_option_list {
   my( $self, $attribute, $_limit ) = @_;   
   defined $attribute or croak('missing dbh or attribute name');
   
   # this is safe because it returns what we wet, if we set, otherwise returns anyway
   my $limit = $self->attribute_option_list_limit( $attribute, $_limit ); 
   
   
   # order it 
   my $list = $self->attribute_all_unique_values($attribute,$limit) 
      or return;

   my $sorted = _sort($list);
   
   # unshift into list a value for 'none' ?
   
   
   return $sorted;
}

sub attribute_option_list_limit {
   my($self, $att, $limit ) = @_;

   $self->{_attlimit} ||={};
   $self->{_attlimit_default} ||= 100;
   
   if( defined $att and defined $limit){
      debug("att $att, limit $limit");
      $self->{_attlimit}->{$att} = $limit;   
      return $limit;
   }

   elsif ( defined $att and ( ! defined $limit ) ){ 
   
      if( $att=~/^\d+$/ ){ # then this is to set default limit globally
         debug("setting default limit to $att");
         $self->{_attlimit_default} = $att;
         return $self->{_attlimit_default};
      }
      
      else { # we are requesting the limit value for this att
         
         
         my $specific_limit =  $self->{_attlimit}->{$att};
         unless( $specific_limit ){
            debug("att $att did not have explicit limit, returning default");
            return $self->{_attlimit_default}; 
         }
         
         debug("att $att had specific limit set to $specific_limit");
         return $specific_limit;
      }

   }

   # no args, just return the default
   debug("returning default limit of ".$self->{_attlimit_default});
   
   return $self->{_attlimit_default};   
}


sub _sort { # mostly to sort values
   my $list = shift;
   
   for(@$list){
      $_=~/^[\.\d]+$/ and next;
      
      # then we are string!      
      return [ sort { lc $a cmp lc $b } @$list ];      
   }
   
   # we are number
   return [ sort { $a <=> $b } @$list ];   
}


#just for heuristics!!! not accurate!
sub _att_uniq_vals {
   my ($self,$att) = @_;
   defined $att or croak('missing att');

   my $limit = 1000;

   # unique vals

   # this is for heuristics
	my $_sql = sprintf "SELECT DISTINCT %s FROM %s WHERE %s=? LIMIT ?",       
      $self->table_metadata_column_name_value,
      $self->table_metadata_name,
      $self->table_metadata_column_name_key;

   my $s = $self->dbh->prepare_cached( $_sql )
      or die( "statement [$_sql], ".$self->dbh->errstr );
   
   $s->execute($att,$limit) or die( $self->dbh->errstr );
   
   my $value;
   $s->bind_columns(\$value);

   my @vals;
   while($s->fetch){
      push @vals,$value;
   }
   return \@vals;
}




sub attribute_all_unique_values {
   my ($self,$attribute,$limit) = @_;
   defined $attribute or croak('missing dbh or attribute name');
   
   my $_limit;
   if(defined $limit){
      $_limit = ' LIMIT '.($limit+1);
   }
   else {
      $_limit = '';
   }

   debug("limit = $limit\n") if $limit;
   
   

   # unique vals
   my $q = sprintf "SELECT DISTINCT %s FROM %s WHERE %s='%s' $_limit",
      $self->table_metadata_column_name_value,
      $self->table_metadata_name,
      $self->table_metadata_column_name_key,
      $attribute,
   ;
   #   debug(" query: $q \n");
   
   my $r = $self->dbh->selectall_arrayref($q);
   
   my @vals = ();
   for(@$r){
      push @vals, $_->[0];
   }         

   if(scalar @vals and $limit and (scalar @vals > $limit)){
      debug("limit [$limit] exceeded, try higher limit?\n");
      return;
   }
   return \@vals;
}


# pass it one attribute name, tells how many there are (possibilities) distinct values
sub attribute_all_unique_values_count { # THIS WILL BE SLOW
   my ($self,$attribute) =@_;
   defined $attribute or confess('missing attribute arg');

   my $vals = $self->attribute_all_unique_values($attribute);
   my $count = scalar @$vals;
   return $count;
}

sub attribute_type_is_number {
   my ($self,$att) = @_;
   defined $att or croak('missing attribute name');

   my $vals = $self->_att_uniq_vals($att) or return;
   scalar @$vals or return;
   for (@$vals){
      /^\d+$/ or return 0;      
   }
   return 1;
}


# end single att methods






1;

__END__