/*
Create the initial, random population of size @number_to_insert
profiles
*/
create procedure up_create_initial_population
   @number_to_insert int,
/* max num of columns with non-NULL target values */
   @column_portion float, 
/* probability operator will be "=" */
   @equality_percent float(8)
as
declare
/* number of columns with non-NULL target values */
   @column_count int, 
   @inderted int,
   @inserted int,
   @this_column varchar(30),
   @this_column_value varchar(255),
   @this_column_operator char(2),
   @next_id  int
begin

   exec up_timing_start 'up_create_initial_population'

   select @next_id = max(id)+1
   from t_profile_column_values
   if @next_id is null select @next_id = 1

   select @inserted = 0

   update t_all_columns set random = -1


   /*
   Since Rand() is only evaluated once per query, we
   need to loop throught the table to assign random
   values
   */
   while (@inserted < @number_to_insert)
   begin
      --select @inserted
      /*  
      the number of columns we generate will be at most
      @column_portion*number of possible columns
      */
      select @column_count = 
        (select max(colorder) 
         from v_columns) *@column_portion * rand() + 1

      while exists (select * from t_all_columns where random = -1)
      begin
         set rowcount 1
         update t_all_columns set random = rand()
         where random = -1
	      set rowcount 0
      end

      delete from t_random_columns

      /*
      get @column_count randomly selected columns
      */
      insert into t_random_columns
      select t1.name, t1.random
      from t_all_columns t1
      where 
         ( select count(*) from t_all_columns t2
           where t2.random < t1.random) < @column_count
      order by t1.random


      /*
      Insert a clause for each column
      */
      while exists(select * from t_random_columns)
      begin
         select top 1 @this_column = name from t_random_columns
         delete from t_random_columns
         where name = @this_column
         
         exec up_insert_profile_column_row 
            @this_column = @this_column,
            @profile_id = @next_id, 
            @equality_percent = @equality_percent
      end

      select @next_id = @next_id + 1
      select @inserted = @inserted + 1 
      update t_all_columns set random = -1
   end

   exec up_timing_end 'up_create_initial_population'

end
go

