
!!!  PRIOR TO  ANY FURTHER CHANGES  -> MAKE a backup copy of 'quexs' database ! and working QUEXS  folder on server !!!
 - in case smth will go wrong you may loose your data!!!
 
 next steps should be done manually (used phpMyAdmin for sql management) , sql querries are for original 'quexs' sql DB structure


-->> 1st create new table columns:

/* insert var_id  + type  into sample_import_var_restrict table, fill var_id with continuous numbers (unique)*/
ALTER TABLE  `sample_import_var_restrict` ADD  `var_id` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT AFTER  `sample_import_id`;
 ALTER TABLE  `sample_import_var_restrict` ADD  `type` SMALLINT( 10 ) UNSIGNED NOT NULL AFTER  `var`;
 ALTER TABLE `sample_import_var_restrict` DROP PRIMARY KEY, ADD PRIMARY KEY(`var_id`)
 
 
/* insert var_id into sample_var table */
ALTER TABLE  `sample_var` ADD  `var_id` BIGINT( 20 ) UNSIGNED NOT NULL AFTER  `sample_id`


-->> 2nd fill created columns with corresponding data

/* update sample_var table */

UPDATE `sample_var` as sv ,`sample`as s, `sample_import_var_restrict` as sivr  SET sv.`var_id`=sivr.`var_id`  WHERE sv.`var` LIKE sivr.`var`  AND sv.`sample_id`=s.`sample_id` AND   s.`import_id`=sivr.`sample_import_id` 


/* update type @ sample_import_var_restrict table, insert corresponding values from sample_var */
UPDATE  `sample_import_var_restrict` ,
`sample_var` SET  `sample_import_var_restrict`.`type` =  `sample_var`.`type` WHERE  `sample_import_var_restrict`.`var_id` =  `sample_var`.`var_id`


--> 3d   some cleanup 
 
/*--> check if there're   unmatched strings for previously "de-identified" samples*/
SELECT * FROM  `sample`  LEFT JOIN `sample_var` ON (`sample`.sample_id = `sample_var`.sample_id) WHERE `sample_var`.sample_id IS NULL
 
/* --> delete `Time_zone_name` and `phone` data from `sample` table for previously "de-identified" samples from `sample_var` table*/
UPDATE `sample`  LEFT JOIN `sample_var` ON (`sample`.sample_id = `sample_var`.sample_id) SET `Time_zone_name`= '',`phone`= '' WHERE `sample_var`.sample_id IS NULL

 
/* delete records for deidentified sample strings*/
DELETE FROM  `sample_import_var_restrict` WHERE  `type` =0

--> !!!  VERY Important !!!  check that collations and  character sets are the same for all tables in DB (f.i.  utf8_unicode_ci )

		probably need some php and sql querry here...


--> !!!!QUOTA Row SHOULD BE FINISHED AND CLEARED FOR ALL SAMPLES AND QUESTtionnaires!!!! 
--> (could influence quota calculation and behaviour because now switched to sample.var_id instead of sample.var )

/* add column for var_id*/
ALTER TABLE  `questionnaire_sample_quota_row` ADD  `exclude_var_id` BIGINT( 20 ) NOT NULL AFTER  `completions`

/*fill `exclude_var_id` with relevant data for `exclude_var_id` = `var_id`*/
UPDATE  `questionnaire_sample_quota_row` as qsqr, `sample_import_var_restrict` as sivr SET  qsqr.exclude_var_id =  sivr.var_id WHERE  qsqr.sample_import_id =  sivr.sample_import_id and qsqr.exclude_var = sivr.var


--> 4th  update *.php files in 'quexs' folder according to the branch revisions

-->  Finish list

CHECK program is working OK : 
	ADMIN - > samplesearch, samplelist, dataoutput, superviser, quotas , lime(export data)
	operator panel -> displaying respondent data as required



update file admin/functions.import.php - > remove sample_var.`var`, sample_var.`type` from importing data 

delete columns `var` and `type`  from `sample_var` table


THE END

*enjoy sample freedom*