On gists

BC Dummy data - SQL

AW

dummy-data-sql.sql Raw #

-- dokumenty, regiony, klient, konkretni klient
SET @clientId = 10809;

INSERT INTO 

bc_stats (bc_document_id, bc_region_id, bc_client_id)

SELECT
id, 
bc_region_id,
@clientId
FROM 

bc_document 
WHERE bc_client_id = @clientId
AND bc_category_id != 0;


-- vsichni klienti
INSERT INTO 
bc_stats (bc_document_id, bc_region_id, bc_client_id)
SELECT
id, 
bc_region_id,
bc_client_id
FROM 
bc_document 
WHERE 1
AND bc_category_id != 0;



-- storage_id
update bc_stats t
    set bc_storage_id = elt(floor(rand()*12) + 1, 
	 8640,
     8641,
	 4409,
     5318,
     5338,
     6901,
     5314,
     3949,
     3332,
     3333,
     3404,
     11311
	);


-- emaily
update bc_stats t
    set email = elt(floor(rand()*15) + 1, 
	 
	 'janko@andweb.cz',
	  'stancek@andweb.cz', 
	  'netolicky@andweb.cz',
	  
	  'aaa@andweb.cz',
	  'bbb@andweb.cz', 
	  'ccc@andweb.cz',
	  
	  'ddd@andweb.cz',
	  'eee@andweb.cz', 
	  'fff@andweb.cz',
	  
	  'ggg@andweb.cz',
	  'hhh@andweb.cz', 
	  'iii@andweb.cz',
	  
	    
	  'jjj@andweb.cz',
	  'kkk@andweb.cz', 
	  'lll@andweb.cz'
	);
	

-- datumy
SET @MIN = '2017-06-29 00:53:27';
SET @MAX = '2020-04-29 13:53:27';
 
UPDATE bc_stats
SET created = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);


-- akce
update bc_stats t
    set action_type = elt(floor(rand()*7) + 1, 
	 		'login',
			'view',
			'share',
			'publish',
			'download',
			'upload',
			'comment'
	 );


 -- uzivatele
SELECT substring_index(GROUP_CONCAT(id), ',', 50) FROM bc_user 
WHERE lastname != ''
ORDER BY id ASC into @users;

SET @sql:= CONCAT('update bc_stats t set bc_user_id = elt(floor(rand()*50) + 1,', ' ', @users, ' )');

PREPARE stmt FROM @sql;
EXECUTE stmt;

-- share
update bc_stats t
    set bc_share_id = FLOOR( RAND() * (11100-11000+1) + 11000 );
	 


-- zopakovat radky
INSERT INTO bc_stats (action_type,created,bc_client_id,bc_user_id,bc_document_id,bc_region_id,bc_share_id,bc_comment_id,bc_upload_id,bc_storage_id,email) 
SELECT action_type,created,bc_client_id,bc_user_id,bc_document_id,bc_region_id,bc_share_id,bc_comment_id,bc_upload_id,bc_storage_id,email 
FROM bc_stats