+ 'export_batch' => {
+ 'columns' => [
+ 'batchnum', 'serial', '', '', '', '',
+ 'exportnum', 'int', '', '', '', '',
+ '_date', 'int', '', '', '', '',
+ 'status', 'varchar', 'NULL', 32, '', '',
+ 'statustext', 'text', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'batchnum',
+ 'unique' => [],
+ 'index' => [ [ 'exportnum' ], [ 'status' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'exportnum' ],
+ table => 'part_export',
+ references => [ 'exportnum' ]
+ },
+ ],
+ },
+
+ 'export_batch_item' => {
+ 'columns' => [
+ 'itemnum', 'serial', '', '', '', '',
+ 'batchnum', 'int', '', '', '', '',
+ 'svcnum', 'int', '', '', '', '',
+ 'action', 'varchar', '', 32, '', '',
+ 'data', 'text', 'NULL', '', '', '',
+ 'frozen', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'itemnum',
+ 'unique' => [],
+ 'index' => [ [ 'batchnum' ], [ 'svcnum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'batchnum' ],
+ table => 'export_batch',
+ references => [ 'batchnum' ]
+ },
+ ],
+ },
+
+ # lookup table for states, similar to msa and lata
+ 'state' => {
+ 'columns' => [
+ 'statenum', 'int', '', '', '', '',
+ 'country', 'char', '', 2, '', '',
+ 'state', 'char', '', $char_d, '', '',
+ 'fips', 'char', '', 3, '', '',
+ ],
+ 'primary_key' => 'statenum',
+ 'unique' => [ [ 'country', 'state' ], ],
+ 'index' => [],
+ },
+
+ # eventually link to tower/sector?
+ 'deploy_zone' => {
+ 'columns' => [
+ 'zonenum', 'serial', '', '', '', '',
+ 'description', 'char', 'NULL', $char_d, '', '',
+ 'agentnum', 'int', '', '', '', '',
+ 'censusyear', 'char', 'NULL', 4, '', '',
+ 'dbaname', 'char', 'NULL', $char_d, '', '',
+ 'zonetype', 'char', '', 1, '', '',
+ 'technology', 'int', '', '', '', '',
+ 'spectrum', 'int', 'NULL', '', '', '',
+ 'adv_speed_up', 'decimal', '', '10,3', '0', '',
+ 'adv_speed_down', 'decimal', '', '10,3', '0', '',
+ 'cir_speed_up', 'decimal', '', '10,3', '0', '',
+ 'cir_speed_down', 'decimal', '', '10,3', '0', '',
+ 'is_broadband', 'char', 'NULL', 1, '', '',
+ 'is_voice', 'char', 'NULL', 1, '', '',
+ 'is_consumer', 'char', 'NULL', 1, '', '',
+ 'is_business', 'char', 'NULL', 1, '', '',
+ 'active_date', @date_type, '', '',
+ 'expire_date', @date_type, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'zonenum',
+ 'unique' => [],
+ 'index' => [ [ 'agentnum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'agentnum' ],
+ table => 'agent',
+ references => [ 'agentnum' ],
+ },
+ ],
+ },
+
+ 'deploy_zone_block' => {
+ 'columns' => [
+ 'blocknum', 'serial', '', '', '', '',
+ 'zonenum', 'int', '', '', '', '',
+ 'censusblock', 'char', '', 15, '', '',
+ 'censusyear', 'char','NULL', 4, '', '',
+ ],
+ 'primary_key' => 'blocknum',
+ 'unique' => [],
+ 'index' => [ [ 'zonenum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'zonenum' ],
+ table => 'deploy_zone',
+ references => [ 'zonenum' ],
+ },
+ ],
+ },
+
+ 'deploy_zone_vertex' => {
+ 'columns' => [
+ 'vertexnum', 'serial', '', '', '', '',
+ 'zonenum', 'int', '', '', '', '',
+ 'latitude', 'decimal', '', '10,7', '', '',
+ 'longitude', 'decimal', '', '10,7', '', '',
+ ],
+ 'primary_key' => 'vertexnum',
+ 'unique' => [ ],
+ 'index' => [ ],
+ 'foreign_keys' => [
+ { columns => [ 'zonenum' ],
+ table => 'deploy_zone',
+ references => [ 'zonenum' ],
+ },
+ ],
+ },
+
+ 'cacti_page' => {
+ 'columns' => [
+ 'cacti_pagenum', 'serial', '', '', '', '',
+ 'exportnum', 'int', 'NULL', '', '', '',
+ 'svcnum', 'int', 'NULL', '', '', '',
+ 'graphnum', 'int', 'NULL', '', '', '',
+ 'imported', @date_type, '', '',
+ 'content', 'text', 'NULL', '', '', '',
+ 'thumbnail', 'text', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'cacti_pagenum',
+ 'unique' => [ ],
+ 'index' => [ ['svcnum'], ['imported'] ],
+ 'foreign_keys' => [
+ { columns => [ 'svcnum' ],
+ table => 'cust_svc',
+ references => [ 'svcnum' ],
+ },
+ { columns => [ 'exportnum' ],
+ table => 'part_export',
+ references => [ 'exportnum' ],
+ },
+ ],
+ },
+
+ 'report_batch' => {
+ 'columns' => [
+ 'reportbatchnum', 'serial', '', '', '', '',
+ 'reportname', 'varchar', '', 255, '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'send_date', @date_type, '', '',
+ 'sdate', @date_type, '', '',
+ 'edate', @date_type, '', '',
+ 'usernum', 'int', 'NULL', '', '', '',
+ 'msgnum', 'int', 'NULL', '', '', '',
+ # add report params here as necessary
+ ],
+ 'primary_key' => 'reportbatchnum',
+ 'unique' => [],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'agentnum' ],
+ table => 'agent',
+ references => [ 'agentnum' ],
+ },
+ { columns => [ 'usernum' ],
+ table => 'access_user',
+ references => [ 'usernum' ],
+ },
+ { columns => [ 'msgnum' ],
+ table => 'msg_template',
+ references => [ 'msgnum' ],
+ },
+ ],
+ },
+
+ 'password_history' => {
+ 'columns' => [
+ 'passwordnum', 'serial', '', '', '', '',
+ '_password', 'varchar', 'NULL', $char_d, '', '',
+ 'encryption_method', 'varchar', 'NULL', $char_d, '', '',
+ 'created', @date_type, '', '',
+ # each table that needs password history gets a column here, and
+ # an entry in foreign_keys.
+ 'svc_acct__svcnum', 'int', 'NULL', '', '', '',
+ 'svc_dsl__svcnum', 'int', 'NULL', '', '', '',
+ 'svc_alarm__svcnum', 'int', 'NULL', '', '', '',
+ 'agent__agentnum', 'int', 'NULL', '', '', '',
+ 'contact__contactnum', 'int', 'NULL', '', '', '',
+ 'access_user__usernum', 'int', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'passwordnum',
+ 'unique' => [],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'svc_acct__svcnum' ],
+ table => 'svc_acct',
+ references => [ 'svcnum' ],
+ },
+ { columns => [ 'svc_dsl__svcnum' ],
+ table => 'svc_dsl',
+ references => [ 'svcnum' ],
+ },
+ { columns => [ 'svc_alarm__svcnum' ],
+ table => 'svc_alarm',
+ references => [ 'svcnum' ],
+ },
+ { columns => [ 'agent__agentnum' ],
+ table => 'agent',
+ references => [ 'agentnum' ],
+ },
+ { columns => [ 'contact__contactnum' ],
+ table => 'contact',
+ references => [ 'contactnum' ],
+ },
+ { columns => [ 'access_user__usernum' ],
+ table => 'access_user',
+ references => [ 'usernum' ],
+ },
+ ],
+ },
+
+ 'webservice_log' => {
+ 'columns' => [
+ 'webservicelognum', 'serial', '', '', '', '', #big? hubrus
+ 'svcnum', 'int', 'NULL', '', '', '', #just in case
+ 'custnum', 'int', '', '', '', '',
+ 'method', 'varchar', '', $char_d, '', '',
+ 'quantity', 'int', '', '', '', '', #i.e. pages
+ '_date', @date_type, '', '',
+ 'status', 'varchar', 'NULL', $char_d, '', '',
+ 'rated_price', 'decimal', 'NULL', '10,2', '', '',
+ ],
+ 'primary_key' => 'webservicelognum',
+ 'unique' => [],
+ 'index' => [ ['custnum'], ['status'] ],
+ 'foreign_keys' => [
+ { columns => [ 'custnum' ],
+ table => 'cust_main',
+ },
+ #no FK on svcnum... we don't want to purge these on
+ # service deletion
+ ],
+ },
+
+ 'rt_field_charge' => {
+ 'columns' => [
+ 'rtfieldchargenum', 'serial', '', '', '', '',
+ 'pkgnum', 'int', '', '', '', '',
+ 'ticketid', 'int', '', '', '', '',
+ 'rate', @money_type, '', '',
+ 'units', 'decimal', '', '10,4', '', '',
+ 'charge', @money_type, '', '',
+ '_date', @date_type, '', '',
+ ],
+ 'primary_key' => 'rtfieldchargenum',
+ 'unique' => [],
+ 'index' => [ ['pkgnum', 'ticketid'] ],
+ 'foreign_keys' => [
+ { columns => [ 'pkgnum' ],
+ table => 'cust_pkg',
+ },
+ ],
+ },
+
+ 'commission_schedule' => {
+ 'columns' => [
+ 'schedulenum', 'serial', '', '', '', '',
+ 'schedulename', 'varchar', '', $char_d, '', '',
+ 'reasonnum', 'int', 'NULL', '', '', '',
+ 'basis', 'varchar', 'NULL', 32, '', '',
+ ],
+ 'primary_key' => 'schedulenum',
+ 'unique' => [],
+ 'index' => [],
+ },
+
+ 'commission_rate' => {
+ 'columns' => [
+ 'commissionratenum', 'serial', '', '', '', '',
+ 'schedulenum', 'int', '', '', '', '',
+ 'cycle', 'int', '', '', '', '',
+ 'amount', @money_type, '', '',
+ 'percent', 'decimal','', '7,4', '', '',
+ ],
+ 'primary_key' => 'commissionratenum',
+ 'unique' => [ [ 'schedulenum', 'cycle', ] ],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'schedulenum' ],
+ table => 'commission_schedule',
+ },
+ ],
+ },
+
+ 'saved_search' => {
+ 'columns' => [
+ 'searchnum', 'serial', '', '', '', '',
+ 'usernum', 'int', 'NULL', '', '', '',
+ 'searchname', 'varchar', '', $char_d, '', '',
+ 'path', 'varchar', '', $char_d, '', '',
+ 'params', 'text', 'NULL', '', '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ 'freq', 'varchar', 'NULL', 16, '', '',
+ 'last_sent', 'int', 'NULL', '', '', '',
+ 'format', 'varchar', 'NULL', 32, '', '',
+ ],
+ 'primary_key' => 'searchnum',
+ 'unique' => [],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'usernum' ],
+ table => 'access_user',
+ },
+ ],
+ },
+
+ 'addr_status' => {
+ 'columns' => [
+ 'addrnum', 'serial', '', '', '', '',
+ 'ip_addr', 'varchar', 'NULL', 40, '', '',
+ '_date', @date_type, '', '',
+ 'up', 'char', 'NULL', 1, '', '',
+ 'delay', 'int', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'addrnum',
+ 'unique' => [ [ 'ip_addr' ] ],
+ 'index' => [ [ '_date' ] ],
+ 'foreign_keys' => [],
+ },
+
+ 'realestate_unit' => {
+ 'columns' => [
+ 'realestatenum', 'serial', '', '', '', '',
+ 'realestatelocnum', 'int', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'unit_title', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'realestatenum',
+ 'unique' => [ ['unit_title'] ],
+ 'index' => [
+ ['agentnum'],
+ ['realestatelocnum'],
+ ['disabled'],
+ ['unit_title'],
+ ],
+ 'foreign_keys' => [
+ {columns => ['agentnum'], table => 'agent'},
+ {columns => ['realestatelocnum'] => table => 'realestate_location'},
+ ],
+ },
+
+ 'realestate_location' => {
+ 'columns' => [
+ 'realestatelocnum', 'serial', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'location_title', 'varchar', '', $char_d, '', '',
+ 'address1', 'varchar', 'NULL', $char_d, '', '',
+ 'address2', 'varchar', 'NULL', $char_d, '', '',
+ 'city', 'varchar', 'NULL', $char_d, '', '',
+ 'state', 'varchar', 'NULL', $char_d, '', '',
+ 'zip', 'char', 'NULL', 5, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'realestatelocnum',
+ 'unique' => [ ['location_title'] ],
+ 'index' => [ ['agentnum'], ['disabled'] ],
+ 'foreign_keys' => [
+ {columns => ['agentnum'], table => 'agent'},
+ ],
+ },
+
+ 'svc_realestate' => {
+ 'columns' => [
+ 'svcnum', 'serial', '', '', '', '',
+ 'realestatenum', 'int', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'svcnum',
+ 'index' => [],
+ },
+
+ 'svc_group' => {
+ 'columns' => [
+ 'svcnum', 'int', '', '', '', '',
+ 'max_accounts', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'svcnum',
+ 'unique' => [],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'svcnum' ],
+ table => 'cust_svc',
+ },
+ ],
+ },
+
+