Pool Layout DB

Introduction

This is a page describing the pool layout solution database created in 2012-08 attempting to implement the "algorithm" or approach presented in Static-Hashed-Tournament_Scheduling_Algorithm. The idea is that by ordering site counts in the pool (i.e.: if site A has 3 teams in pool, site B has 1 and site C has 2, we create an ordered hash string "3 2 1") Once a solution has been generated for this combination, any other pool using that combination can use the same solution.

In addition to the solution, I am adding columns for:

• # of courts - This may get included in the hash at some point. While usually the courts are set to floor(#teams/2), there are exceptions (ie: a celebrity visitor signing volleyballs, want more byes to allow teams to get signatures).
• games - # of games played
• # of collisions
• # of duplicates
• quality - not used yet, but this can be another flag to sort on to order preference.
• byes - I dont think this one will be needed

Did not add this, but we may need to....

Create

This is the sqlite3 create command to create the database...

CREATE TABLE poollayout(rowid integer primary key,hash text,courts int,games int,colls int, dups int,byes int, qual int, solution text);

Insert Data

customTournamentEditor.php now has an extra button labeled as Save Solution To DB (or something like that). The button doesn't actually save to the db yet (as of 2012-08-18), but produces an insert screen (like below) to copy and paste onto the command line prompt (sqlite3 pool layout.sqlite3).

insert into poollayout ('hash','courts','games','dups','colls','solution' ) values ('6 3 2','5','4','0','0','1-7+2-8+3-9+4-10+5-11+6|1-8+2-7+3-10+4-9+6-11+5|1-9+2-10+3-11+5-7+6-8+4|1-10+2-9+4-11+5-8+6-7+3');

Implementation

Calling tree we are concerned with is:

• usyvlTourn::output()
  • usyvlTourn::outputXML()
    • usyvlPool::outputXML.
      • usyvlPool::setPoolLayout()
        • Both of the following calls are global functions defined in usyvlPoolLayout.php which is included from usyvl.php, so are available everywhere.
        • $this->poolLayout = getPoolLayout(count($this->teams),count($acv),$this->teamsites);
        • $this->poolLayoutStr = encodePoolLayoutStr($this->poolLayout);

But, need to have a populated db in place before we do that.

Considerations

Because this is so different than the current system, implementation could be tricky. Several of the pdf creation routines need alternate pool solutions and call getPoolLayout in situations where we wont have a site hash.

Will want some routines to translate from poolLayout solutions string to an indexed array (and vice versa)... Have one of those in encodePoolLayoutStr($this->poolLayout); which takes the indexed array and converts it to a solution string.

The current team distributions into the pools was left alone. The current system has the order of the queues "rolled" for subsequent tournaments. That distribution was left intact. The new system merely comes in at the end of the process and unshuffles the teams so that teams from the same site are lumped together with the team/site order corresponding to the sitehash order (teams from the site with the most teams are the first into the pool). The unshuffling process maintains the order that the teams from the same site are encountered (i.e.: the "rolling" for subsequent tournaments should be preserved... would like to verify that though).

Will have to look at the pdfs to see how the larger pool sizes affect the grid layout. Probably OK for standard sheets, but will likely need to work on the summary sheet.

Alternate pools. Alternate pools may actually be more predictable now... ie: if we have a 4 3 2 configuration, our alternate pools would be 3 3 2, 4 2 2 and 4 3 1 (drop each term by one). This assumes that each site would lose no more than one team (which may not be reasonable). Regardless of that, with the larger pools, we will have trouble finding room to cram all the possible alternate pool layouts into. This would be something for our mobile app!!!

Will need to add in db import options for the customTournamentEditor. This will allow us to modify a given tournament parameters (drop a team) and then search the db to see if we have that solutions already in place.

Comparisons/Checking

With original code and 2-7 team pool (2 to 7 teams in a pool) range we get

all tourn summary, mode: Minimize Collisions : 1189 collisions in 3755 games, 
all tourn summary, mode: Minimize Collisions : 395 duplications in 3755 games,

Using poollayout db with 2-7 team pool range we get... Dropped 129 collisions but increased duplications by 29 collisions

all tourn summary, mode: Minimize Collisions : 1060 collisions in 3755 games, 
all tourn summary, mode: Minimize Collisions : 424 duplications in 3755 games,

Using poollayout db but bumping up tournament team pool range to 2-12 we get the following

all tourn summary, mode: Minimize Collisions : 885 collisions in 3755 games, 
all tourn summary, mode: Minimize Collisions : 220 duplications in 3755 games,

Data Entry

2012-08-18

Went through and came up with solutions to handle the 2-7 team pool sites for the Spring 2012 input files. That accounts for the first 27 rules (below) (there are a couple duplicates I need to remove).

The remaining rules are the solutions required to handle the 2-12 team pool range for the Spring 2012 input files. That range change is currently a define statement in usyvlPoolLayout.php. It may have been required for the old method. The new system may handle this as a setting more gracefully. Will have to test that at some point.

The data entry process was to run runTS.php and look at the db miss errors generated during the run. From there, I would note which xml files (date, sites, pool #). I would then click "Select XML to Edit" (launchXMLEditor.php script). From that page, I would select the XML file I had noted before and click "Edit" which brings up the customTournEditor.php. There were three cases for most of the larger numbered:

• Import Solution produced a workable solution - easiest case
• Lower the # of courts by 1 to see if I could then import a solution.
  • If so, bump # of courts back up and finish the job
• No Import Solution available
  • Take the teams from the site with fewer teams, distribute those teams (always higher numbered teams) across the courts into the second menu for each match.
  • Take the teams from the larger site and distribute those against those courts, trying to keep the numbers balanced.
  • Figure out the collisions for the remaining courts for the teams from the larger site.

2012-08-18 Database contents

1|4 2|3|4|4|0|||1-3+2-6+4-5|1-4+2-5+3-6|1-5+2-3+4-6|1-6+2-4+3-5
2|2 2|2|4|2|2|||1-2+3-4|1-3+2-4|1-4+2-3|1-3+2-4
3|4 3|3|4|0|0|||1-5+2-6+3-7+4|1-6+2-5+4-7+3|1-7+3-5+4-6+2|2-7+3-6+4-5+1
4|2 1|1|4|1|1|||1-3+2|2-3+1|1-2+3|1-3+2
5|2|1|4|4|3|||1-2|1-2|1-2|1-2
6|4 2|3|4|4|0|||1-3+2-6+4-5|1-4+2-5+3-6|1-5+2-3+4-6|1-6+2-4+3-5
7|4 3|3|4|0|0|||1-5+2-6+3-7+4|1-6+2-5+4-7+3|1-7+3-5+4-6+2|2-7+3-6+4-5+1
8|2 2 1|2|4|0|0|||1-3+4-5+2|1-4+2-5+3|1-5+2-3+4|2-4+3-5+1
9|2 2 2|3|4|0|0|||1-3+2-6+4-5|1-4+2-5+3-6|1-5+2-3+4-6|1-6+2-4+3-5
10|3 2 1|3|4|2|0|||1-4+2-5+3-6|1-5+2-6+3-4|5-6+2-4+1-3|4-6+3-5+1-2
11|3 2 2|3|4|0|0|||1-4+2-5+3-6+7|1-5+2-4+3-7+6|1-6+2-7+3-4+5|1-7+4-6+3-5+2
12|3 2|2|4|2|0|||1-3+2-5+4|1-4+2-3+5|1-5+3-4+2|2-4+3-5+1
13|2 1 1|2|4|1|2|||1-3+2-4|1-4+2-3|1-2+3-4|1-4+2-3
14|3 2 2|3|4|0|0|||1-4+2-5+3-6+7|1-5+2-4+3-7+6|1-6+2-7+3-4+5|1-7+4-6+3-5+2
15|4 1|2|4|4|0|||1-3+4-5+2|1-4+2-5+3|2-3+1-5+4|2-4+3-5+1
16|3 1|2|4|4|2|||1-2+3-4|1-3+2-4|1-4+2-3|1-2+3-4
17|5 2|3|4|4|0|||1-7+3-5+2-6+4|1-6+2-7+4-5+3|3-7+1-5+4-6+2|5-7+3-6+2-4+1
18|5 1|3|4|8|0|||1-3+2-6+4-5|1-4+2-5+3-6|1-5+2-3+4-6|1-6+2-4+3-5
19|3 3|3|4|0|3|||1-4+2-5+3-6|1-5+2-6+3-4|1-6+2-4+3-5|1-6+2-4+3-5
20|3 3|3|4|2|1|||1-4+2-5+3-6|1-5+2-6+3-4|1-6+2-4+3-5|1-2+3-4+5-6
21|4|2|4|8|2|||1-2+3-4|1-3+2-4|1-4+2-3|1-2+3-4
22|1 1|1|4|0|3|||1-2|1-2|1-2|1-2
23|3|1|4|4|1|||1-2+3|2-3+1|1-3+2|2-3+1
24|6 1|3|4|8|0|||1-2+4-7+5-6+3|1-3+2-7+4-5+6|1-7+2-3+4-6+5|1-5+2-6+3-7+4
25|4 2 1|3|4|0|0|||1-5+2-6+3-7+4|1-6+2-5+4-7+3|1-7+3-5+4-6+2|2-7+3-6+4-5+1
26|4 1 1|3|4|4|0|||1-3+2-6+4-5|1-4+2-5+3-6|2-3+1-5+4-6|2-4+1-6+3-5
27|4 1 1|3|4|4|0|||1-5+2-6+3-4|1-6+4-5+2-3|2-5+3-6+1-4|4-6+3-5+1-2
28|4 4 4|6|4|0|0|||1-5+2-6+3-9+4-10+7-11+8-12|1-6+2-5+3-10+4-9+7-12+8-11|1-7+2-8+3-11+4-12+5-9+6-10|1-8+2-7+3-12+4-11+5-10+6-9
29|4 4 2|5|4|0|0|||1-5+2-6+3-7+4-9+8-10|1-6+2-5+3-8+4-10+7-9|1-7+2-8+3-9+4-5+6-10|1-8+2-7+3-10+4-6+5-9
30|4 2 2|4|4|0|0|||1-5+2-6+3-7+4-8|1-6+2-5+3-8+4-7|1-7+2-8+3-5+4-6|1-8+2-7+3-6+4-5
31|4 4 3|5|4|0|0|||1-5+2-6+3-7+4-9+8-10+11|1-6+2-5+3-8+4-11+7-9+10|1-7+2-8+3-5+4-10+6-11+9|1-9+2-7+3-10+4-6+5-11+8
32|4 3 1|4|4|0|0|||1-5+2-6+3-7+4-8|1-6+2-5+3-8+4-7|1-7+2-8+3-5+4-6|1-8+2-7+3-6+4-5
33|5 4|4|4|0|0|||1-6+2-7+3-8+4-9+5|1-7+2-6+3-9+5-8+4|1-8+2-9+4-6+5-7+3|1-9+3-7+4-8+5-6+2
34|7 3|5|4|8|0|||1-8+4-9+6-10+2-5+3-7|2-9+7-10+3-8+1-6+4-5|2-10+7-8+3-9+1-4+5-6|5-10+1-9+4-8+2-7+3-6
35|3 3 2|4|4|0|0|||1-4+2-5+3-7+6-8|1-5+2-4+3-8+6-7|1-6+2-7+3-4+5-8|1-7+2-6+3-5+4-8
36|6 4|5|4|4|0|||1-7+2-8+3-9+4-10+5-6|1-8+2-7+5-9+6-10+3-4|3-7+4-8+5-10+6-9+1-2|1-9+2-10+6-8+4-7+3-5
37|6 5|5|4|0|0|||1-7+2-8+3-9+4-10+5-11+6|1-8+2-7+3-10+4-9+6-11+5|1-9+2-10+3-11+5-7+6-8+4|1-10+2-9+4-11+5-8+6-7+3
38|6 3|4|4|4|0|||1-7+2-8+3-9+4-5+6|4-7+5-8+6-9+1-2+3|1-8+2-9+3-7+4-6+5|4-8+5-9+6-7+2-3+1
39|8 2|5|4|12|0|||1-9+3-10+2-4+5-7+6-8|6-10+4-9+1-5+3-8+2-7|2-9+7-10+1-3+4-8+5-6|8-10+5-9+1-2+3-4+6-7
40|5 5|5|4|0|0|||1-6+2-7+3-8+4-9+5-10|1-7+2-6+3-9+4-10+5-8|1-8+2-9+3-10+4-6+5-7|1-9+2-10+3-7+4-8+5-6
41|6 4|5|4|4|0|||1-7+2-8+3-9+4-10+5-6|1-8+2-7+5-9+6-10+3-4|3-7+4-8+5-10+6-9+1-2|1-9+2-10+3-8+6-7+4-5
42|8 4|6|4|8|0|||1-9+2-10+3-11+4-12+5-6+7-8|5-9+6-10+7-11+8-12+1-2+3-4|1-10+2-9+3-12+4-11+5-7+6-8|5-10+6-9+7-12+8-11+1-3+2-4
43|6 2|4|4|8|0|||1-7+2-8+3-6+4-5|3-7+4-8+1-5+2-6|5-7+6-8+1-2+3-4|2-7+3-8+1-4+5-6
44|5 3|4|4|4|0|||1-6+2-7+3-8+4-5|1-7+4-6+5-8+2-3|2-6+3-7+4-8+1-5|5-6+4-7+2-8+1-3
45|8 3|5|4|8|0|||1-9+2-10+3-11+4-5+6-7+8|4-9+5-10+6-11+2-7+3-8+1|7-9+8-10+1-11+2-3+4-6+5|2-9+3-10+4-11+1-7+5-8+6
46|4 4|4|4|0|0|||1-5+2-6+3-7+4-8|1-6+2-5+3-8+4-7|1-7+2-8+3-5+4-6|1-8+2-7+3-6+4-5
47|6 6|6|4|0|0|||1-7+2-8+3-9+4-10+5-11+6-12|1-8+2-7+3-10+4-9+5-12+6-11|1-9+2-10+3-11+4-12+5-7+6-8|1-10+2-9+3-12+4-11+5-8+6-7
48|7 4|5|4|4|0|||1-8+2-9+3-10+4-11+5-6+7|1-9+5-8+6-10+7-11+2-3+4|2-8+3-9+4-10+5-11+6-7+1|1-10+2-11+6-8+7-9+3-4+5
49|4 3 2|4|4|0|0|||1-5+2-6+3-7+4-8+9|1-6+2-5+3-9+4-7+8|1-8+2-9+3-5+4-6+7|1-7+2-8+3-6+5-9+4
50|5 4 2|5|4|0|0|||1-6+2-7+3-8+4-9+5-10+11|1-7+2-6+3-9+4-8+5-11+10|1-8+2-10+3-6+4-11+5-7+9|1-9+2-8+3-7+4-10+6-11+5
51|6 3 2|5|4|0|0|||1-7+2-8+3-9+4-10+5-11+6|1-8+2-7+3-10+4-9+6-11+5|1-9+2-10+3-11+5-7+6-8+4|1-10+2-9+4-11+5-8+6-7+3
sqlite> .schema
CREATE TABLE poollayout(rowid integer primary key,hash text,courts int,games int,colls int, dups int,byes int, qual int, solution text);

Here is the ordered hashes:

sqlite> select hash from poollayout order  by hash;
1 1
2
2 1
2 1 1
2 2
2 2 1
2 2 2
3
3 1
3 2
3 2 1
3 2 2
3 2 2
3 3
3 3
3 3 2
4
4 1
4 1 1
4 1 1
4 2
4 2
4 2 1
4 2 2
4 3
4 3
4 3 1
4 3 2
4 4
4 4 2
4 4 3
4 4 4
5 1
5 2
5 3
5 4
5 4 2
5 5
6 1
6 2
6 3
6 3 2
6 4
6 4
6 5
6 6
7 3
7 4
8 2
8 3
8 4