-
Notifications
You must be signed in to change notification settings - Fork 44
/
populate_fk.sql
37 lines (32 loc) · 1.64 KB
/
populate_fk.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/**************
| poplate_fk : script to generate random data into a child table with
| Note that this script will require the original random data generator populate procedure(populate.sql)
| https://github.com/kedarvj/mysql-random-data-generator/blob/master/populate.sql
|
| USAGE: call populate_fk('DATABASE-NAME','CHILD-TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE);
| EXAMPLE: call populate_fk('databasename','child',100,'N');
| Developer: Kedar Vaijanapurkar
| Website: http://kedar.nitty-witty.com/
|
**************/
DELIMITER $$
DROP PROCEDURE IF EXISTS populate_fk $$
CREATE PROCEDURE populate_fk(in_db varchar(50), in_table varchar(50), in_rows int, in_debug char(1))
fk_load:BEGIN
#select CONCAT("UPDATE ",TABLE_NAME," SET ",COLUMN_NAME,"=(SELECT ",REFERENCED_COLUMN_NAME," FROM ",REFERENCED_TABLE_SCHEMA,".",REFERENCED_TABLE_NAME," ORDER BY RAND() LIMIT 1);") into @query from information_schema.key_column_usage where TABLE_NAME=in_table AND TABLE_SCHEMA=in_db AND CONSTRAINT_NAME <> 'PRIMARY';
select concat("UPDATE ",in_table," SET ", (select GROUP_CONCAT(COLUMN_NAME,"=(SELECT ",REFERENCED_COLUMN_NAME," FROM ",REFERENCED_TABLE_SCHEMA,".",REFERENCED_TABLE_NAME," ORDER BY RAND() LIMIT 1)") from information_schema.key_column_usage where TABLE_NAME=in_table AND TABLE_SCHEMA=in_db AND CONSTRAINT_NAME <> 'PRIMARY' group by table_name),";" ) into @query;
IF in_debug='Y' THEN
select @query;
END IF;
if @query is null then
select "No referential information found." as Error;
LEAVE fk_load;
end if;
set foreign_key_checks=0;
call populate(in_db,in_table,in_rows,'N');
PREPARE t_stmt FROM @query;
EXECUTE t_stmt;
set foreign_key_checks=1;
END
$$
DELIMITER ;