Using the setup.sql Script to Create the Databases

More precisely, and given that you accept the naming and security choices in the setup.sql script, do the following:

1. Obtain the mysql root account password; and note that the mysql root account is distinct from the linux OS root account. Login access for the mysql root account is often restricted to prevent network logins, and in that case you will also need login access to the machine where the database server runs as well, although here an ordinary user account should work just fine.
2. Ensure that the shim tar ball has been unpacked on the machine from which you intend to login to the mysql database, or else copy the subdirectory sql with all its contents and subdirectories to that machine. For reasons already noted in (1), the machine you use may well be the one that the mysql server itself runs on.
3. From the directory sql, and given read access to the sql directory, its subdirectories, and files, start the mysql interpreter as the [mysql] root user:

   mysql -u root -p
   

   The -p option will cause the mysql program to prompt you for the mysql root password, which you must have obtained previously, in (1).

4. You should see text approximately like the following, indicating that you have obtained a mysql interpreter prompt:

   mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 928 to server version: 5.0.27
   
   Type 'help;' or '\h' for help. Type '\c' to clear
   the buffer.
   
   mysql>
   

5. Enter the following command in order to create user accounts, the testing and trading databases, create the tables for those databases, and populate them with initial values. You should see many status messages fly by (on the order of a couple of hundred; in the dialogue below, the ellipses indicate many rows scrolling past), and ending with the mysql prompt:

   mysql> source setup.sql
   
   ... ... ... ... ... ... ... ... ... ...
   
   Query OK, 12 rows affected (0.00 sec)
   Records: 12  Duplicates: 0  Warnings: 0
   
   Query OK, 4 rows affected (0.00 sec)
   Records: 4  Duplicates: 0  Warnings: 0
   
   Query OK, 4 rows affected (0.00 sec)
   Records: 4  Duplicates: 0  Warnings: 0
   
   Query OK, 0 rows affected (0.00 sec)
   
   mysql>
   

6. To verify that the testing and trading databases have been created, you may list the databases (note the semicolon):

   mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | lost+found         |
   | mysql              |
   | testing            |
   | trading            |
   +--------------------+
   5 rows in set (0.00 sec)
   
   mysql>
   

7. To verify that tables have been created, you may set the database to testing, and list the tables (again, ellipses indicate elided material):

   mysql> use testing
   Database changed
   mysql> show tables;
   
   +-------------------+
   | Tables_in_testing |
   +-------------------+
   | AtomTag           |
   | BarSize           |
   | Bool              |
   
   ...  ... ... ...  ...
   
   | Underlying        |
   | Version           |
   | Volatility        |
   | WatchSets         |
   +-------------------+
   50 rows in set (0.00 sec)
   
   mysql>
   

8. Type quit at the prompt to end the mysql session:

   mysql> quit
   Bye
   sql$
   

At this point the shim databases have been created, tables for those databases created and populated with default values, and user accounts created for the four roles of shim program operation, downstream data collection, downstream orders, and offline maintenance and programming; the account names are listed in Table 3.1.

Table 3.1: Default user accounts

Name	User	Intended Role
shim	program	database connection by the trading-shim program
data	program	downstream programs that run the shim in data mode
risk	program	downstream programs that run the shim in risk mode
code	person	interactive access and database maintenance

Please understand that although the testing database has default order information, so that the regression scripts can demonstrate simple reversing orders, the trading database has not yet been populated with the order lineitems referred to by downstream order commands, about which more in Chapter 4. Although databases have been set up at this point, you should not yet be trying to submit orders from downstream programs to the shim for transmission through the api to the IB tws.