NAME

hd_insert_bulk.pl - Inserts records into mysql tables, using Data::HandyGen.

VERSION

This documentation refers to hd_insert_bulk.pl 0.0.5

USAGE

$ hd_insert_bulk.pl --infile mysample.json -d mydb -u myuser -p mypasswd

ARGUMENTS

General

• -d | --dbname

  (Required) A name of database

• -h | --host

  (Optional) Hostname of database

• --port

  (Optional) Port no.

• -u | --user

  (Required) User name to connect mysql

• -p | --password

  (Required) Password to connect mysql

Required only using config file

• -i | --in | --infile

  (Required) a file in which a list of records to be inserted is written.

Required only specifying table name and the number of records

• -t | --table (Required) target table name

• -n | --number (Required) the number of inserted records

• --required (Optional) a list of column names which require value. Multiple columns should be separated by comma(,), like 'col_1,col_2,col_3'. This option is useful when you want any value to be inserted to nullable columns.

DESCRIPTION

This application inserts a collection of data into tables. You don't need to specify values to every required fields. You only need to specify values what you're really interested in. If you don't want to consider foreign key constraints, nor the order of insertion (usually you would insert a referenced record at first), it's ok. This application automatically determines values for required fields in the right order.

HOW TO PREPARE INPUT FILE

[Sample table definitions]

create table item (
    id integer primary key auto_increment,
    name varchar(20)
);

create table customer (
    id integer primary key auto_increment,
    name varchar(50) not null
);

create table purchase (
    id integer primary key auto_increment,
    customer_id integer not null,
    item_id integer not null,
    constraint foreign key (customer_id) references customer(id),
    constraint foreign key (item_id) references item(id)
);

json

Currently this application accepts JSON format.

FORMAT:

{
    (table name) : {
        (ID): { (column name): (value), ... },
        ...
    },
    ....
}

EXAMPLE: Save the following as example.json

{
    "item" : {
        "1": { "name": "Apple" },
        "2": { "name": "Banana" }
    },
    "customer": {
        "1": { },
        "2": { }
    },
    "purchase" : {
        "1": { "customer_id" : "##customer.1", "item_id" : "##item.1" },
        "2": { "customer_id" : "##customer.2", "item_id" : "##item.1" },
        "3": { "customer_id" : "##customer.2", "item_id" : "##item.2" }
    }
}

* If a value references foreign key, its format is "##(table name).(ID)".

This will make

$ hd_insert_bulk.pl --infile example.json -d testdb -u myuser -p mypasswd

[item] (Assuming next auto_increment value is 101)
    +-----+--------+
    | id  | name   |
    |-----+--------|
    | 101 | Apple  |
    | 102 | Banana |
    +-----+--------+

[customer] (Assuming next auto_increment value is 50)
    +-----+---------+
    | id  | name    |
    |-----+---------|
    |  50 | name_50 |
    |  51 | name_51 |
    +-----+---------+
    * No values in table 'customer' has been specified, so required values in the table will be determined automatically.

[purchase] (Assuming next auto_increment value is 501)
    +-----+-------------+---------+
    | id  | customer_id | item_id |
    |-----+-------------+---------+
    | 501 |          50 |     101 |
    | 502 |          51 |     102 |
    | 503 |          51 |     101 |
    +-----+-------------+---------+

NOTE: ID can be omitted if it starts with 1 and is incremented one by one.

"purchase" : {
    "1": { "customer_id" : "##customer.1", "item_id" : "##item.1" },
    "2": { "customer_id" : "##customer.2", "item_id" : "##item.1" },
    "3": { "customer_id" : "##customer.2", "item_id" : "##item.2" }
}

is equivalent to:

"purchase" : [
    { "customer_id" : "##customer.1", "item_id" : "##item.1" },
    { "customer_id" : "##customer.2", "item_id" : "##item.1" },
    { "customer_id" : "##customer.2", "item_id" : "##item.2" }
]

This is especially useful when those records won't be referenced from any other tables and you don't need to care about its ID.

OUTPUT

It will output all table names and IDs to STDOUT with YAML format like the followings:

---
customer:
  - 50
  - 51
item:
  - 101
  - 102
purchase:
  - 501
  - 502
  - 503

You can use those values to delete test data. If you redirect the output to a file, you may later pass it to hd_delete_all.pl (included in this package) as an argument to delete those records.

$ hd_insert_bulk.pl --infile example.json -d testdb -u myuser -p mypasswd > inserted.yml

#  ...later

$ hd_delete_all inserted.yml --i inserted.yml -d testdb -u myuser -p mypasswd
(This will delete all records above)

TODO

• To handle also YAML, CSV and TSV formats.

BUGS AND LIMITATIONS

Please report problems to Takashi Egawa (egawa.takashi at gmail com) Patches are welcome.

SEE ALSO

Data::HandyGen::mysql hd_delete_all.pl

AUTHOR

Takashi Egawa (egawa.takashi at gmail com)

LICENCE AND COPYRIGHT

Copyright (c)2013 Takashi Egawa (egawa.takashi at gmail com). All rights reserved.

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. See perlartistic.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

cpanm Data::HandyGen::mysql

perl -MCPAN -e shell
install Data::HandyGen::mysql