Changeset 406

Show
Ignore:
Timestamp:
02/08/08 02:44:39 (10 months ago)
Author:
eevee
Message:

Database refactoring. Renamed columns and tables to be more consistent and more readable. (#58)

Location:
veekun/trunk
Files:
1 removed
78 modified
3 moved

Legend:

Unmodified
Added
Removed

  • veekun/trunk/lib/Vee/Authorization.pm

    r385 r406  
    3434    return 0 unless $c->user; 
    3535 
    36     my @usergroups = $c->model('UserGroups')->search({ userid => [ 0, $c->user->obj->id ] })->get_column('groupid')->all; 
     36    my @usergroups = $c->model('UserGroups')->search({ user_id => [ 0, $c->user->obj->id ] })->get_column('group_id')->all; 
    3737#    my %seen; 
    3838 
    3939    # TODO: get group parents! 
    4040    my @groupperms = $c->model('GroupPermissions')->search({ 
    41         groupid => { -in => \@usergroups }, 
     41        group_id => { -in => \@usergroups }, 
    4242        permission => [ $permission, 'splat' ], 
    4343        scope => $scope, 

  • veekun/trunk/lib/Vee/Bot.pm

    r262 r406  
    7373        my $ability = $schema->resultset('Abilities')->find($hashref->{id}); 
    7474        return sprintf "%s: %s", 
    75             $ability->name, $ability->effect; 
     75            $ability->name, $ability->description; 
    7676 
    7777    } else { 
    … …  
    204204    } 
    205205 
    206     my @pm = $schema->resultset('PokeMoves')->search({ 
    207         pokeid => $pokemon_id, 
    208         moveid => $move_id, 
    209         -nest  => \ "FIND_IN_SET('$ver', version)", 
     206    my @pm = $schema->resultset('PokemonMoves')->search({ 
     207        pokemon_id => $pokemon_id, 
     208        move_id    => $move_id, 
     209        -nest      => \ "FIND_IN_SET('$ver', versions)", 
    210210    }); 
    211211 

  • veekun/trunk/lib/Vee/Controller/Dex.pm

    r400 r406  
    281281        group_by  => 'me.id', 
    282282        join      => 'pokemon_abilities', 
    283         '+select' => 'COUNT(DISTINCT pokemon_abilities.pokeid)', 
     283        '+select' => 'COUNT(DISTINCT pokemon_abilities.pokemon_id)', 
    284284        '+as'     => 'pokemon_count', 
    285285    }); 
    … …  
    353353     
    354354    # check to see if there is only one move, ever, that corresponds to this TM 
    355     my %set = map { $_->moveid => 1 } @tms; 
     355    my %set = map { $_->move_id => 1 } @tms; 
    356356 
    357357    if (!@tms) { 
    … …  
    359359    } elsif (1 == scalar keys %set) { 
    360360        # TODO: remove this? 
    361         $c->res->redirect( $c->uri('Dex', 'moves', lc $MoveData[ $tms[0]->moveid ]->name) ); 
     361        $c->res->redirect( $c->uri('Dex', 'moves', lc $MoveData[ $tms[0]->move_id ]->name) ); 
    362362    } 
    363363 
    … …  
    453453        %query, 
    454454    }, { 
    455         join     => { evchain => 'pokemon' }, 
     455        join     => { evo_chain => 'pokemon' }, 
    456456        prefetch => { pokemon_abilities => 'ability' }, 
    457         group_by => [ 'me.id', 'pokemon_abilities.abilityid' ], 
     457        group_by => [ 'me.id', 'pokemon_abilities.ability_id' ], 
    458458        order_by => [ @extra_order, "pokemon.id ASC", "FIND_IN_SET('baby', me.flags) DESC", 'me.id ASC' ], 
    459459    }); 
    … …  
    482482    delete $c->session->{last_pokemon_view}; 
    483483 
    484     $s->{page_title}  = $row->name . ' - Pokémon #' . $row->real_id; 
     484    $s->{page_title}  = $row->name . ' - Pokémon #' . $row->real_pokemon_id; 
    485485    $s->{page_header} = $row->name; 
    486486    $s->{extra_js}    = ['dexutils']; 
    … …  
    507507    $s->{this}       = $row; 
    508508    $s->{pid}        = Vee::Utils::pad($row->id, 3); 
    509     $s->{generation} = my $generation = ( grep { $Generations[$_]{maxid} >= $row->real_id } 0 .. $#Generations )[0]; 
     509    $s->{generation} = my $generation = ( grep { $Generations[$_]{maxid} >= $row->real_pokemon_id } 0 .. $#Generations )[0]; 
    510510 
    511511    # alt forms 
    … …  
    513513        my @alts; 
    514514        my $alt_rs = $c->model('DBIC::Pokemon')->search({ 
    515             real_id  => $row->real_id, 
     515            real_pokemon_id  => $row->real_pokemon_id, 
    516516        }, { 
    517517            columns  => ['alt_form'], 
    … …  
    525525         
    526526    # evolution chain 
    527     my @family = $row->evchain->pokemon( \'id = real_id', { columns => [qw/id evparent evmethod evparam/] } ); 
     527    my @family = $row->evo_chain->pokemon( \'id = real_pokemon_id', { columns => [qw/id evo_parent_id evo_method evo_param/] } ); 
    528528    my %evtrees; 
    529     $evtrees{$_->id} = { id => $_->id, parent => $_->evparent, method => $_->evmethod, param => $_->evparam, children => [] } for @family; 
     529    $evtrees{$_->id} = { id => $_->id, parent => $_->evo_parent_id, method => $_->evo_method, param => $_->evo_param, children => [] } for @family; 
    530530    push @{ $evtrees{ $_->{parent} }{children} }, $_ for sort { $a->{id} <=> $b->{id} } grep { $_->{parent} } values %evtrees; 
    531     for my $node (values %evtrees) { add_evolution_desc($node, $row->evchain) } 
     531    for my $node (values %evtrees) { add_evolution_desc($node, $row->evo_chain) } 
    532532    $s->{evtree} = ( grep { !$_->{parent} } values %evtrees )[0];  # should only be one with no parent: the root 
    533533    calculate_tree_width($s->{evtree}); 
    … …  
    547547    $s->{compatibility}{all}  = $compat_rs->count; 
    548548    # TODO: this is actually wrong; fix when bug #88 is fixed please 
    549     $s->{compatibility}{base} = $compat_rs->search({ evparent => 0 })->count; 
     549    $s->{compatibility}{base} = $compat_rs->search({ evo_parent_id => 0 })->count; 
    550550 
    551551    # held items 
    … …  
    563563    # moves from here down 
    564564    # slurp everything this Pokemon can learn, complete with egg moves if necessary 
    565     my $moves_rs = $c->model('DBIC::PokeMoves')->search( { 
    566         pokeid => $row->id, 
    567     }, { 
    568         columns => [qw/moveid method level version/] 
     565    my $moves_rs = $c->model('DBIC::PokemonMoves')->search( { 
     566        pokemon_id => $row->id, 
    569567    } ); 
    570568     
    … …  
    578576    while (my $move_row = $moves_rs->next) { 
    579577        my $table_row = { 
    580             moveid => $move_row->moveid, 
     578            move_id => $move_row->move_id, 
    581579            level => $move_row->level, 
    582             versions => { map { $_ => $move_row->level || 1 } split /,/, $move_row->version }, 
     580            versions => { map { $_ => $move_row->level || 1 } split /,/, $move_row->versions }, 
    583581        }; 
    584582 
    … …  
    595593    @{ $moves{$_} } = sort { 
    596594        $a->{level} <=> $b->{level} or 
    597         $MoveData[ $a->{moveid} ]->name cmp $MoveData[ $b->{moveid} ]->name 
     595        $MoveData[ $a->{move_id} ]->name cmp $MoveData[ $b->{move_id} ]->name 
    598596    } @{ $moves{$_} } for qw/ level egg machine /; 
    599597     
    … …  
    633631        my $i = -1; 
    634632        while (++$i <= $#$lev_moves) {  # shouldn't use a for since the size of the array changes 
    635             # find the next row index with the same moveid 
    636             my $next_idx = first { $lev_moves->[$_]{moveid} == $lev_moves->[$i]{moveid} } $i + 1 .. $#$lev_moves; 
     633            # find the next row index with the same move id 
     634            my $next_idx = first { $lev_moves->[$_]{move_id} == $lev_moves->[$i]{move_id} } $i + 1 .. $#$lev_moves; 
    637635            next if not defined $next_idx; 
    638636 
    … …  
    673671            } 
    674672            if (scalar keys %{ $from->{versions} } == 0) {   
    675                 # only moveid left, so delete this row and redo to hit the next one 
     673                # only move id left, so delete this row and redo to hit the next one 
    676674                splice @$lev_moves, $from_idx, 1; 
    677675                redo; 
    … …  
    704702        map { $_->generation => $_->text } $row->flavors 
    705703    }; 
    706     $s->{generation} = ( grep { $Generations[$_]{maxid} >= $row->real_id } 0 .. $#Generations )[0]; 
     704    $s->{generation} = ( grep { $Generations[$_]{maxid} >= $row->real_pokemon_id } 0 .. $#Generations )[0]; 
    707705 
    708706    $s->{page_title} = $row->name . ' - Flavor Text and Images'; 
    … …  
    785783        ? Vee::Utils::round( $c->model('DBIC::Moves')->count({ power => { '!=' => undef, '<' => $row->power } }) / $DamagingMoveCount * 100, 1 ) 
    786784        :'n/a'; 
    787     $s->{tm_info} = { map { $_->generation => $_ } $c->model('DBIC::Machines')->search({ moveid => $row->id }) }; 
     785    $s->{tm_info} = { map { $_->generation => $_ } $c->model('DBIC::Machines')->search({ move_id => $row->id }) }; 
    788786 
    789787    # status effect 
    … …  
    805803 
    806804    # contest stuffs 
    807     my $contest_family_rs = $c->model('DBIC::Moves')->search({ coneffect => $row->coneffect, id => { '!=', $row->id } }, { columns => [qw/id name contype/], order_by => 'name ASC' }); 
     805    my $contest_family_rs = $c->model('DBIC::Moves')->search({ contest_effect_id => $row->contest_effect_id, id => { '!=', $row->id } }, { columns => [qw/id name contest_type/], order_by => 'name ASC' }); 
    808806    my %contest_family; 
    809807    $s->{contest_family_count} = 0; 
    810808    while (my $move = $contest_family_rs->next) { 
    811         push @{ $contest_family{ $move->contype } }, $move; 
     809        push @{ $contest_family{ $move->contest_type } }, $move; 
    812810        $s->{contest_family_count}++; 
    813811    } 
    … …  
    818816    my %pokemon_count; 
    819817    my %valid_methods = map { $_ => 1 } qw/level egg tutor machine/; 
    820     my $pokemoves_rs = $c->model('DBIC::PokeMoves')->search({ moveid => $row->id }); 
     818    my $pokemoves_rs = $c->model('DBIC::PokemonMoves')->search({ move_id => $row->id }); 
    821819    my %pokemon_hash;  # method => pokemon id => version => level 
    822820    while (my $pm = $pokemoves_rs->next) { 
    … …  
    829827            @versions = ('all'); 
    830828        } else { 
    831             @versions = split /,/, $pm->version; 
     829            @versions = split /,/, $pm->versions; 
    832830        } 
    833831 
    834832        for my $ver (@versions) { 
    835             $pokemon_hash{$method}{$pm->pokeid}{pokemon} ||= $pm->pokemon; 
    836             $pokemon_hash{$method}{$pm->pokeid}{$ver} = $pm->level || 1; 
    837         } 
    838         $pokemon_count{ $pm->pokeid } = 1; 
     833            $pokemon_hash{$method}{$pm->pokemon_id}{pokemon} ||= $pm->pokemon; 
     834            $pokemon_hash{$method}{$pm->pokemon_id}{$ver} = $pm->level || 1; 
     835        } 
     836        $pokemon_count{ $pm->pokemon_id } = 1; 
    839837    } 
    840838 
    … …  
    903901    $c->forward('/cache', [ $row->id ]); 
    904902 
    905     $s->{hid}         = $row->internalid + 1; 
     903    $s->{hid}         = $row->internal_id + 1; 
    906904    $s->{generation}  = ($row->name eq 'dark' || $row->name eq 'steel') ? 1 : 0; 
    907905 
    … …  
    11901188    my @extra; 
    11911189    # n.b.: can ONLY prefetch one has_many here; DBIx::Class will refuse more due to cross-product effect 
    1192     if ($table eq 'Pokemon') { @extra = ( prefetch => [qw/evchain pokemoves/], order_by => 'pokemoves.moveid ASC' ) } 
    1193     elsif ($table eq 'Moves') { @extra = ( prefetch => 'pokemoves', order_by => 'pokemoves.pokeid ASC' ) } 
     1190    if ($table eq 'Pokemon') { @extra = ( prefetch => [qw/evo_chain pokemon_moves/], order_by => 'pokemon_moves.move_id ASC' ) } 
     1191    elsif ($table eq 'Moves') { @extra = ( prefetch => 'pokemon_moves', order_by => 'pokemon_moves.pokemon_id ASC' ) } 
    11941192     
    11951193    if ($id eq 'random') { 

  • veekun/trunk/lib/Vee/Controller/Dex/Search.pm

    r348 r406  
    4949    breed       => { type => 'select', options => [ [ 0 => 'n/a' ], map { [ $_ => ($_ ? "$_: " : '') . $BreedingGroups[$_] ] } 1 .. $#BreedingGroups ], count => 2 }, 
    5050    breed_mode  => { type => 'select', options => [ [ and => 'exactly' ], [ or => 'either of' ] ], default => 'or' }, 
    51     gender      => { type => 'select', options => [ [ any => 'anything' ], [ 255 => 'no gender' ], [ not255 => 'any gender' ], map { [ $_ => lc gender_text($_) ] } qw/0 31 63 127 191 254/ ] }, 
     51    gender_rate => { type => 'select', options => [ [ any => 'anything' ], [ 255 => 'no gender' ], [ not255 => 'any gender' ], map { [ $_ => lc gender_text($_) ] } qw/0 31 63 127 191 254/ ] }, 
    5252    ability     => { type => 'text', size => 20, title => 'Enter the name or number of an ability' }, 
    5353    color       => { type => 'select', options => [qw/any black blue brown gray green pink purple red white yellow/], title => 'I have no explanation for why this is here' }, 
    … …  
    123123            if ($ability) { 
    124124                $joins{pokemon_abilities} = 1; 
    125                 $criteria{'pokemon_abilities.abilityid'} = $ability->id; 
     125                $criteria{'pokemon_abilities.ability_id'} = $ability->id; 
    126126            } 
    127127        } 
    … …  
    142142 
    143143        # BREEDING AND STUFF 
    144         if (defined $p->{gender} and $p->{gender} ne 'any') { 
    145             if ($p->{gender} eq 'not255') { 
    146                 $criteria{'me.gender'} = { '!=' => 255 }; 
     144        if (defined $p->{gender_rate} and $p->{gender_rate} ne 'any') { 
     145            if ($p->{gender_rate} eq 'not255') { 
     146                $criteria{'me.gender_rate'} = { '!=' => 255 }; 
    147147            } else { 
    148                 $criteria{'me.gender'} = $p->{gender}; 
     148                $criteria{'me.gender_rate'} = $p->{gender_rate}; 
    149149            } 
    150150        } 
    … …  
    154154            $criteria{'breeds.breed'} = \@breeds; 
    155155            if ($p->{breed_mode} eq 'and') { 
    156                 $clauses{having}{'COUNT(DISTINCT breeds.pokeid, breeds.breed)'} = scalar @breeds; 
     156                $clauses{having}{'COUNT(DISTINCT breeds.pokemon_id, breeds.breed)'} = scalar @breeds; 
    157157 
    158158                if (@breeds == 1) { 
    … …  
    192192            for my $move (@{ $p->{move} }) { 
    193193                # XXX: show some message if a move is invalid? 
    194                 my $moveid = get_move($move); 
    195                 push @moveids, $moveid if defined $moveid; 
     194                my $move_id = get_move($move); 
     195                push @moveids, $move_id if defined $move_id; 
    196196            } 
    197197            if (@moveids) { 
    198                 $joins{pokemoves} = 1; 
    199                 $criteria{'pokemoves.moveid'} = \@moveids; 
    200                 $clauses{having}{'COUNT(DISTINCT pokemoves.pokeid, pokemoves.moveid)'} = scalar @moveids; 
     198                $joins{pokemon_moves} = 1; 
     199                $criteria{'pokemon_moves.move_id'} = \@moveids; 
     200                $clauses{having}{'COUNT(DISTINCT pokemon_moves.pokemon_id, pokemon_moves.move_id)'} = scalar @moveids; 
    201201 
    202202                if ($p->{move_method}) { 
    203                     $criteria{'pokemoves.method'} = $p->{move_method}; 
     203                    $criteria{'pokemon_moves.method'} = $p->{move_method}; 
    204204                } 
    205205                if ($p->{move_version}) { 
    206                     push @{$criteria{'-and'}}, { '-or', [ map { \ "FIND_IN_SET('$_', pokemoves.version)" } Vee::Utils::array($p->{move_version}) ] }; 
     206                    push @{$criteria{'-and'}}, { '-or', [ map { \ "FIND_IN_SET('$_', pokemon_moves.versions)" } Vee::Utils::array($p->{move_version}) ] }; 
    207207                } 
    208208            } 
    … …  
    237237 
    238238            if ($evo_stages{base}) { 
    239                 $criteria{'me.evparent'} = 0; 
     239                $criteria{'me.evo_parent_id'} = 0; 
    240240            } 
    241241 
    … …  
    308308}; 
    309309 
    310 for my $col (qw/power acc pp effect_chance priority/) { 
     310for my $col (qw/power accuracy pp effect_chance priority/) { 
    311311    for my $endpoint (keys %endpoints) { 
    312312        $move_search_fields->{ $col . '_' . $endpoint } = { type => 'text', size => 4, maxlength => 3, title => "\u$endpoints{$endpoint}{english} $col" }; 
    … …  
    366366            } 
    367367            if (@pokemonids) { 
    368                 $joins{pokemoves} = 1; 
    369                 $criteria{'pokemoves.pokeid'} = \@pokemonids; 
    370                 $clauses{having}{'COUNT(DISTINCT pokemoves.pokeid, pokemoves.moveid)'} = scalar @pokemonids; 
     368                $joins{pokemon_moves} = 1; 
     369                $criteria{'pokemon_moves.pokemon_id'} = \@pokemonids; 
     370                $clauses{having}{'COUNT(DISTINCT pokemon_moves.pokemon_id, pokemon_moves.move_id)'} = scalar @pokemonids; 
    371371 
    372372                if ($p->{move_method}) { 
    373                     $criteria{'pokemoves.method'} = $p->{move_method}; 
     373                    $criteria{'pokemon_moves.method'} = $p->{move_method}; 
    374374                } 
    375375                if ($p->{move_version}) { 
    376                     push @{$criteria{'-and'}}, { '-or', [ map { \ "FIND_IN_SET('$_', pokemoves.version)" } Vee::Utils::array($p->{move_version}) ] }; 
     376                    push @{$criteria{'-and'}}, { '-or', [ map { \ "FIND_IN_SET('$_', pokemon_moves.versions)" } Vee::Utils::array($p->{move_version}) ] }; 
    377377                } 
    378378            } 
    … …  
    380380 
    381381        # NUMBERS 
    382         for my $numbar (qw/power acc pp effect_chance priority/) { 
     382        for my $numbar (qw/power accuracy pp effect_chance priority/) { 
    383383            for my $endpoint (keys %endpoints) { 
    384384                my $value = $p->{$numbar . '_' . $endpoint}; 

  • veekun/trunk/lib/Vee/Controller/Dex/Utils.pm

    r371 r406  
    102102    my @pokemon; 
    103103 
    104     my (@rows, @pokemoves); 
     104    my (@rows, @pokemon_moves); 
    105105    # only do this if there are actually correct Pokemon to look up! 
    106106    if (@pokemon_ids) { 
    … …  
    116116         
    117117        # get moves 
    118         my $move_rs = $c->model('DBIC::PokeMoves')->search({ 
    119             pokeid => [ keys %pokemon_order ], 
    120             method => [qw/level machine egg tutor/], 
    121             -nest => \ "FIND_IN_SET('$version', version)", 
     118        my $move_rs = $c->model('DBIC::PokemonMoves')->search({ 
     119            pokemon_id => [ keys %pokemon_order ], 
     120            method     => [qw/level machine egg tutor/], 
     121            -nest      => \ "FIND_IN_SET('$version', versions)", 
    122122        }); 
    123123        while (my $row = $move_rs->next) { 
    124             push @{ $pokemoves[ $_ ]{ $row->method } }, $row for @{ $pokemon_order{$row->pokeid} } 
    125         } 
    126     } 
    127  
    128     $s->{pokemon}     = \@pokemon; 
    129     $s->{pokemon_raw} = \@pokemon_raw; 
    130     $s->{pokemoves}   = \@pokemoves; 
     124            push @{ $pokemon_moves[ $_ ]{ $row->method } }, $row for @{ $pokemon_order{$row->pokemon_id} } 
     125        } 
     126    } 
     127 
     128    $s->{pokemon}       = \@pokemon; 
     129    $s->{pokemon_raw}   = \@pokemon_raw; 
     130    $s->{pokemon_moves} = \@pokemon_moves; 
    131131 
    132132    $s->{template} = 'dex/utils/compare-results.tt'; 
    … …  
    173173    # ensure this move is actually inheritable 
    174174    # TODO: should I error if the move is learnable normally? 
    175     my $inheritable_ct = $c->model('DBIC::PokeMoves')->count({ 
    176         pokeid => $pokemon->id, 
    177         moveid => $move->id, 
    178         method => [qw/ egg machine /], 
    179         -nest  => \ "FIND_IN_SET('$gen', version)", 
     175    my $inheritable_ct = $c->model('DBIC::PokemonMoves')->count({ 
     176        pokemon_id => $pokemon->id, 
     177        move_id    => $move->id, 
     178        method     => [qw/ egg machine /], 
     179        -nest      => \ "FIND_IN_SET('$gen', versions)", 
    180180    }); 
    181181    $c->vee_stop('', $pokemon->name, " can't inherit ", $move->name, '.') unless $inheritable_ct; 
    182182     
    183183    my $gender_restriction; 
    184     if ($pokemon->gender == 255) { 
     184    if ($pokemon->gender_rate == 255) { 
    185185        # must also be genderless, i.e. bred with Ditto 
    186186        $gender_restriction = 255; 
    … …  
    192192    ### grab the methods by which any Pokemon learn the move 
    193193 
    194     my $methods_rs = $c->model('DBIC::PokeMoves')->search({ 
    195         moveid => $move->id, 
    196         method => [qw[ level egg machine ]], 
    197         -nest => \ "FIND_IN_SET('$gen', version)", 
    198         'pokemon.gender' => $gender_restriction, 
     194    my $methods_rs = $c->model('DBIC::PokemonMoves')->search({ 
     195        move_id => $move->id, 
     196        method  => [qw[ level egg machine ]], 
     197        -nest   => \ "FIND_IN_SET('$gen', versions)", 
     198        'pokemon.gender_rate' => $gender_restriction,