Creating the Database

only for RuBoard - do not distribute or recompile

As with Listing 6.1, this section will detail the initial construction of the commish database and the initial data load procedures. In this case, the data format will be slightly different. The tables to be loaded will be comma delimited with the first row containing the field names (as opposed to the tab-delimited files from the SESI Application in Chapters 6 and 7). It is important to note that the data tables being imported for this application have already been scrubbed. For example, in a comma-delimited file, the customer name Smak Moduler, Inc. will screw up a MySQL LOAD DATA statement because LOAD DATA will take the comma to mean that Smak Moduler is one field and Inc. is another, when in fact they are one field in the data. Although the double quotation mark character can be used as the field delimiter , trouble can arise if someone has included a double quotation mark character in the data, either accidentally or on purpose. Data quality is everything, and in this case the data has already been cleansed (prior to its inclusion in this book) so that the import procedures go smoothly.

There will be two separate procedures: one to import the revenue table (tbl_revenue) which is the file to be imported monthly, and the other to load the tables that will determine how the data in tbl_revenue will be processed . Listing 8.1 is the data load procedure for loading tbl_revenue, and Listing 8.2 is for the initial load of the rest of the tables.

First, create the database by launching mysql as root (or equivalent). Then create the database:

 mysql> create database commish;

Exit mysql and run Listing 8.1 with this command:

 %mysql -t < listing.8.1

Or run it with whatever the name of the file is. Refer to Listing 6.1 and its surrounding text for a detailed explanation of the -t option and the peculiarities of directing a file to mysql in this manner.

Listing 8.1 Loading the Revenue Data to tbl_revenue

 use commish;  /* Listing 8.1   *   * This file loads the data from the tbl_revenue.txt file   * into  tbl_revenue  in the commish database.   *   * usage:   *   * % mysql -t < listing.8.1   */  drop table if exists tbl_revenue;  create table tbl_revenue  (line_number       INT PRIMARY KEY AUTO_INCREMENT,          customer_number   varchar(50),          customer_name     varchar(50),          salesperson       varchar(50),          country           varchar(50),          city              varchar(50),          state             char(2),          zip               varchar(50),          ship_date         varchar(50),          item_number       varchar(50),          order_quantity    smallint,          unit_price        decimal(10,2),          net_value         decimal(10,2),          invoice_number    varchar(50),          invoice_date      varchar(50),          invoice_quantity  smallint,          invoice_value     decimal(10,2));  LOAD DATA INFILE "/mnt/DOS_hda2/newriders/book/ch8/tbl_revenue.txt"       INTO TABLE tbl_revenue       FIELDS TERMINATED BY ','       IGNORE 1 LINES         (customer_number,          customer_name,          salesperson,          country,          city,          state,          zip,          ship_date,          item_number,          order_quantity,          unit_price,          net_value,          invoice_number,          invoice_date,          invoice_quantity ,          invoice_value);  select count(*) from tbl_revenue;

Listing 8.2 creates and loads the rest of the database, including the security and event tables.

Listing 8.2 Loading the Remaining Data to the Commish Database

 use commish;  /* This file loads all tables except  tbl_revenue  (which   * is loaded by Listing 8.1).   *   * usage:   *   * % mysql -t < listing.8.2   */   /* The following table lists the salespeople that are being paid   * or have the potential to be paid a commission.   */  drop table if exists tbl_people;  create table tbl_people  (line_number      SMALLINT     PRIMARY KEY    AUTO_INCREMENT,        salesperson      char(4),        last_name        varchar(50),        first_name       varchar(35),        country          varchar(50),        commission       decimal(6,4));  /* In the  LOAD DATA  statement that follows, the last part   * of the statementthe part in parenthesesis the   * column-list. It tells MySQL which columns to put   * the data in. In this case, it serves the purpose of   * allowing the line_number field to be first in the table   * structure without trying to load a value   * from the text file into it.   */  LOAD DATA INFILE "/mnt/DOS_hda2/newriders/book/ch8/tbl_people.txt"      INTO TABLE tbl_people      FIELDS TERMINATED BY ','      IGNORE 1 LINES      (salesperson,       last_name,       first_name,       country,       commission);  /* tbl_people.txt should look something like the following (which   * is actually the first three lines from the file):   *  salesperson_num,last_name,first_name,country,commission  *  3565,Regreb,Jenny,Germany,0.0028  *  4101,Facile,Francis,South Africa,0.00513  *   * The following table holds the currency exchange rates   * for various countries.   */  drop table if exists tbl_exchange_rates;  create table tbl_exchange_rates  (line_number SMALLINT    PRIMARY KEY    AUTO_INCREMENT,        country     varchar(50),        rate        decimal(8,2));  LOAD DATA INFILE "/mnt/DOS_hda2/newriders/book/ch8/tbl_exchange_rates.txt"      INTO TABLE tbl_exchange_rates      FIELDS TERMINATED BY ','      IGNORE 1 LINES      (country,       rate);  /* The following table lists those salespeople who are paid for sales   * in a certain state and which state that is.   */  drop table if exists tbl_people_paid_on_state;  create table tbl_people_paid_on_state  (line_number SMALLINT    PRIMARY KEY    AUTO_INCREMENT,        salesperson varchar(50),        state       varchar(20));  LOAD DATA INFILE "/mnt/DOS_hda2/newriders/book/ch8/tbl_people_paid_on_state.txt"      INTO TABLE tbl_people_paid_on_state      FIELDS TERMINATED BY ','      IGNORE 1 LINES      (salesperson,       state);  /* The following table lists those salespeople who are paid for   * sales in a certain ZIP code and which ZIP_code that is.   */  drop table if exists tbl_people_paid_on_zipcode;  create table tbl_people_paid_on_zipcode  (line_number SMALLINT    PRIMARY KEY    AUTO_INCREMENT,        salesperson varchar(50),        zipcode     varchar(20));  LOAD DATA INFILE "/mnt/DOS_hda2/newriders/book/ch8/tbl_people_paid_on_zipcode.txt"      INTO TABLE tbl_people_paid_on_zipcode      FIELDS TERMINATED BY ','      IGNORE 1 LINES      (salesperson,       zipcode);  /* That represents the last of the data files to be loaded.   *   * The remaining tables in the database are shown below.   *   * For those that have only a few rows of data, or where   * no data file was available for import, individual   * insert statements have been created.   *   * The next table to be created lists those people who   * are paid for all sales worldwide.   */  drop table if exists tbl_people_paid_on_worldwide;  create table tbl_people_paid_on_worldwide  (line_number   SMALLINT    PRIMARY KEY    AUTO_INCREMENT,        salesperson   varchar(4));    insert into tbl_people_paid_on_worldwide(salesperson)              values('4838'), ('5383');  /* Next is the table of people who are paid on   * everything sold in a certain country.   */  drop table if exists tbl_people_paid_on_country;  create table tbl_people_paid_on_country  (line_number   SMALLINT    PRIMARY KEY    AUTO_INCREMENT,        salesperson   varchar(4),        country       varchar(50));  insert into tbl_people_paid_on_country(salesperson, country)         values ('4070', 'United States'),                ('4515', 'United States'),                ('4191', 'United States'),                ('5646', 'United States'),                ('4333', 'China'),                ('3686', 'Germany'),                ('5151', 'Poland'),                ('5919', 'Honduras');  /* Next is the commissions table. Each month,   * the application fills this table   * and the one that is archived at the   * start of the monthly processing.   *   * The software will perform a series of steps to fill   * this table each month. The data from this table   * will be used for reporting to the salespeople and   * the country managers.   */  drop table if exists tbl_commissions;  /* Notice that the line number field in this table is   *  INT  instead of  SMALLINT  . That is because   * this table is expected to surpass 32767 within the first   * month or two of use. Although the  MEDIUMINT  * data type could also be used, it has a maximum value of 8388607.   * But the  INT  data type has a maximum value of over two billion.   * That should be more than enough.   */  create table tbl_commissions  (line_number   INT    PRIMARY KEY    AUTO_INCREMENT,       salesperson   varchar(4),       invoice_num   varchar(10),       commission    decimal(8,2));  /* Finally, create the security and administration tables.   *   * You'll create three tables here. One will hold the access   * permissionsthat is, who can view which tables in the database.   * Another will hold logins and passwords.   * The third is the application log, which records events.   */  drop table if exists tbl_permissions;  create table tbl_permissions  (login        varchar(35),       form         varchar(35));  insert into tbl_permissions         values ('processor', 'process'),                ('processor', 'tbl_exchange_rates'),                ('processor', 'tbl_people'),                ('processor', 'tbl_people_paid_on_country'),                ('processor', 'tbl_people_paid_on_worldwide'),                ('processor', 'tbl_people_paid_on_state'),                ('processor', 'tbl_people_paid_on_zipcode'),                ('processor', 'tbl_revenue'),                ('processor', 'tbl_commissions'),                ('analyst1', 'tbl_people_paid_on_worldwide'),                ('analyst1', 'tbl_revenue'),                ('analyst1', 'tbl_commissions'),                ('boss1', 'tbl_exchange_rates'),                ('boss1', 'tbl_people'),                ('boss1', 'tbl_people_paid_on_country'),                ('boss1', 'tbl_people_paid_on_worldwide'),                ('boss1', 'tbl_people_paid_on_state'),                ('boss1', 'tbl_people_paid_on_zipcode'),                ('boss1', 'tbl_revenue'),                ('boss1', 'tbl_commissions');  drop table if exists tbl_security;  /* In the following table, rather than use "login"   * and "password" as the field names,, we have chosen to   * use names that will prevent problemseven though   * mysql would accept "login" and "password" as   * field names.   */  create table tbl_security  (user_login    varchar(35),        pass_word     varchar(35));  /* The  password()  function encrypts the given text   * in a one-way operation. In the application,   * the  password()  function will be used on the value   * entered in entry_password and then compared against   * the database.   */  insert into tbl_security         values ('processor', password('smile')),                ('analyst1' , password('salsa')),                ('boss1' , password('sippycup'));  drop table if exists tbl_logged_events;  /* In the following create table statement, the  TIMESTAMP  * data type not only creates a date-time column, but   * also sets the default value to the current date and time.   */  create table tbl_logged_events  (time_stamp      TIMESTAMP NOT NULL,        user_login      varchar(35),        event           varchar(255));  insert into tbl_logged_events (user_login, event)         values ('administrator', 'database created and loaded');  select * from tbl_logged_events;

only for RuBoard - do not distribute or recompile