Tables naturally group into the three categories of security, subscription, and order transactions, with tables from the latter two categories having foreign key constraints depending on security-related tables.
In order to create or recreate the dbms, tables are first dropped in top-down order by foreign key dependency, the reverse order of creation. Then create table statements for securities, subscriptions, and orders are source'd in creation order, that is bottom-up by key dependency. (The GraphViz dot program and the dbms-related dot files in ../dot may be used to generate foreign key dependency graphs). Table loads are finally performed via a mixture of literal and source'd insert statements, the load files for the latter case named according to the table name, e.g., for a given table TabName, the load file if it exists is named TabName.sql, and found in one of the directories req or mod (table load files in opt are not used here).
To populate an empty dbms and load initial data, run this script once from the mysql interpreter to create and load tables, and then rerun a second time to ensure that a fix-point has been reached, that is to check that all tables are conditionally dropped before creation.
Since recreating the dbms tables also loads tables with symbols, contracts, and orders, this script is one means by which to add new tuples for dbms relations that provide input to the shim; edit the <table-name>.sql insert statement in the appropriate load file, halt all instances of the shim that might read the dbms, and rerun this script. Realize that all history and state information stored in the dbms will be lost, unless it is first extracted via mysqldump, added to load files, and source'd as necessary.
All persistent tables have the engine type InnoDb, to provide foreign key dependency checking. This engine type *must not* be changed to some other type, e.g., MyISAM, else the shim die due to subscript-out-of-range errors.
For the Contract and SubRequest tables, the SecType and SubType attributes, respectively, tag an otherwise untyped numerical index, the actual type of which is a union of foreign key types, even though not explicitly declareable as such. Since the derived type data members for some objects in the shim are defined via these tagged variants, the underlying tables must be loaded with the related values, else there be an index-out-of-range error, so that for the Contract and SubRequest relations, in addition to the explicitly declared foreign key dependencies, there are also these implicit dependency constraints on the underlying Detail or Filter tables.
