Programatically populate MySQL Schema with arbitrary test data (using sqlInserts)

Important Note: The library the documentation has moved to www.mockneat.com. The information found on this wiki is quite outdated. Please check the new site.

---

Populating MySQL HR schema defined here.

package net.andreinc.mockneat.unit.text;

import net.andreinc.mockneat.MockNeat;
import net.andreinc.mockneat.abstraction.MockUnitString;
import net.andreinc.mockneat.unit.text.sql.SQLTable;
import org.junit.Test;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import static java.lang.Integer.parseInt;
import static java.time.format.DateTimeFormatter.BASIC_ISO_DATE;
import static net.andreinc.mockneat.types.enums.DictType.COUNTRY_ISO_CODE_2;
import static net.andreinc.mockneat.types.enums.DictType.COUNTRY_NAME;
import static net.andreinc.mockneat.types.enums.DictType.DEPARTMENTS;
import static net.andreinc.mockneat.types.enums.StringFormatType.CAPITALIZED;
import static net.andreinc.mockneat.types.enums.StringFormatType.UPPER_CASE;
import static net.andreinc.mockneat.unit.text.sql.escapers.MySQL.TEXT_BACKSLASH;

public class SQLInsertsTest {
    @Test
    public static void main(String[] args) {
        MockNeat m = MockNeat.threadLocal();

        // Manager ids
        List<Integer> managerIds = m.ints()
                                    .range(1, 1000)
                                    .list(() -> new ArrayList<>(), 20)
                                    .val();

        // ----- Regions -----
        String[] regionNames = new String[] {"Europe",
                                             "Americas",
                                             "Asia",
                                             "Middle East and Africa" };
        SQLTable regions = m.sqlInserts()
                            .tableName("regions")
                            .column("region_id",
                                    m.intSeq().start(1))
                            .column("region_name",
                                    m.seq(regionNames), TEXT_BACKSLASH)
                            .table(regionNames.length)
                            .val();
        System.out.println(regions);

        // ----- Countries -----
        int numCountries = 241;
        SQLTable countries = m.sqlInserts()
                              .tableName("countries")
                              .column("country_id",
                                      m.seq(COUNTRY_ISO_CODE_2), TEXT_BACKSLASH)
                              .column("country_name",
                                      m.seq(COUNTRY_NAME), TEXT_BACKSLASH)
                              .column("region_id",
                                      regions.fromColumn("region_id"))
                              .table(numCountries)
                              .val();
        System.out.println(countries);

        // ----- Locations -----
        int numLocations = 100;

        MockUnitString streetAddressGen = m.fmt("#{num} #{noun} #{end}")
                                           .param("num",
                                                   m.ints().range(10, 2000))
                                           .param("noun",
                                                   m.words().nouns().format(CAPITALIZED))
                                           .param("end",
                                                   m.from(new String[]{"Ave", "St", "Blvd", "Rd"}));

        MockUnitString postalCodeGen = m.fmt("#{word1} #{word2}")
                                        .param("word1",
                                                m.strings().size(3).format(UPPER_CASE))
                                        .param("word2",
                                                m.strings().size(3).format(UPPER_CASE));

        SQLTable locations = m.sqlInserts()
                              .tableName("locations")
                              .column("location_id",
                                      m.intSeq().start(1000).increment(100))
                              .column("street_address",
                                      streetAddressGen, TEXT_BACKSLASH)
                              .column("postal_code",
                                      postalCodeGen, TEXT_BACKSLASH)
                              .column("city",
                                      m.cities().us(), TEXT_BACKSLASH)
                              .column("state_province",
                                      m.cities().capitals(), TEXT_BACKSLASH)
                              .column("country_id",
                                      countries.fromColumn("country_id"), TEXT_BACKSLASH)
                              .table(numLocations)
                              .val();

        System.out.println(locations);

        // ----- Departments -----
        int depNum = 10;

        SQLTable departments = m.sqlInserts()
                                .tableName("departments")
                                .column("department_id",
                                        m.intSeq().start(0).increment(10))
                                .column("department_name",
                                        m.seq(DEPARTMENTS), TEXT_BACKSLASH)
                                .column("manager_id",
                                        m.from(managerIds))
                                .column("location_id",
                                        locations.fromColumn("location_id"))
                                .table(depNum)
                                .val();

        System.out.println(departments);

        // ----- JOBS -----
        String[] jobNames = new String[] {"Analyst",
                                          "Consultant",
                                          "Senior Consultant",
                                          "Manager",
                                          "Software Architect",
                                          "Senior Manager",
                                          "Director"};

        String[] jobIds = new String[] { "A",
                                         "C",
                                         "SC",
                                         "M",
                                         "SA",
                                         "SM",
                                         "D" };

        int numJobs = jobNames.length;
        int minSalary = 2000;
        int maxSalary = 5000;


        SQLTable jobs = m.sqlInserts()
                         .tableName("jobs")
                         .column("job_id",
                                 m.seq(jobIds), TEXT_BACKSLASH)
                         .column("job_title",
                                 m.seq(jobNames), TEXT_BACKSLASH)
                         .column("min_salary",
                                 minSalary + "")
                         .column("max_salary",
                                 maxSalary + "")
                         .table(numJobs)
                         .val();

        System.out.println(jobs);

        //  ----- EMPLOYEES -----

        int numEmployes = 1000;

        SQLTable employees = m.sqlInserts()
                              .tableName("employees")
                              .column("employee_id",
                                      m.intSeq())
                              .column("first_name",
                                      m.names().first(), TEXT_BACKSLASH)
                              .column("last_name",
                                      m.names().last(), TEXT_BACKSLASH)
                              .column("email",
                                      m.emails().domain("corp.com"), TEXT_BACKSLASH)
                              .column("phone_number",
                                      m.regex("\\+30 [0-9]{9}"), TEXT_BACKSLASH)
                              .column("hire_date",
                                      m.localDates()
                                       .past(LocalDate.of(2000, 1, 1))
                                       .display(BASIC_ISO_DATE), TEXT_BACKSLASH)
                              .column("job_id",
                                      jobs.fromColumn("job_id"), TEXT_BACKSLASH)
                              .column("salary",
                                      m.ints().range(minSalary, maxSalary))
                              .column("commission_pct",
                                      "NULL")
                              .column("manager_id",
                                      "NULL" /* TO UPDATE LATER */)
                              .column("department_id",
                                      departments.fromColumn("department_id"))
                              .table(numEmployes)
                              .val();

        // Updating employees with their manager ID
        // - One person has no manager ID - it should be a director level person
        // - Manager id and employee_id should be different

        // We update all people with a manager_id
        employees.updateAll((i, insert) -> {
            Integer employeeId = parseInt(insert.getValue("employee_id"));
            Integer managerId = Integer.MIN_VALUE;

            while(employeeId == (managerId = m.from(managerIds).val()));

            insert.setValue("manager_id", managerId + "");
        });

        // One of the directors doesn't have a manager id as he is the CO
        employees.selectFirstWhere(sqlInsert -> sqlInsert.getValue("job_id").equals("D"))
                    .get()
                    .setValue("manager_id", "NULL");

        System.out.println(employees);
    }
}