X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=c635ea0a00e8e3843773539df60f709fa42e0635;hb=3317a32f572a2e3605071410e7ddd8082e0b03ee;hp=4f547725d06ea51e2e4cb5543724765812faabfe;hpb=7bfd0175b05d52b648e283fca4343aef79e851d7;p=freeside.git diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 4f547725d..c635ea0a0 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -200,7 +200,7 @@ sub dbdef_dist { grep { ! /^(clientapi|access_user)_session/ && ! /^h_/ && ! /^log(_context)?$/ - && ( ! /^queue(_arg)?$/ || ! $opt->{'queue-no_history'} ) + && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} ) && ! $tables_hashref_torrus->{$_} } $dbdef->tables @@ -243,6 +243,23 @@ sub dbdef_dist { ], }); + #necessary for queries that want to look at *who* made changes + $h_indices{"h_${table}_usernum"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_usernum", + 'unique' => 0, + 'columns' => [ 'history_usernum'], + }); + + # necessary because of the evil OR username for older data, be really nice if everything was just migrated to usernum and we could drop username + # This will not be helpful to mysql, but postgres smartly does a bitmap across both indexes, mysql will just use one + + $h_indices{"h_${table}_username"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_username", + 'unique' => 0, + 'columns' => [ 'history_username'], + }); } my $primary_key_col = $tableobj->column($tableobj->primary_key) @@ -662,6 +679,7 @@ sub tables_hashref { 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', #customer balance info at invoice generation time + #(deprecated) 'previous_balance', @money_typen, '', '', #eventually not nullable 'billing_balance', @money_typen, '', '', #eventually not nullable @@ -1008,7 +1026,7 @@ sub tables_hashref { 'cust_bill_pkg_detail' => { 'columns' => [ - 'detailnum', 'serial', '', '', '', '', + 'detailnum', 'serial', '', '', '', '', 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable 'pkgnum', 'int', 'NULL', '', '', '', # deprecated 'invnum', 'int', 'NULL', '', '', '', # deprecated @@ -1617,6 +1635,8 @@ sub tables_hashref { [ 'referral_custnum' ], [ 'payby' ], [ 'paydate' ], [ 'archived' ], + [ 'ship_locationnum' ], + [ 'bill_locationnum' ], ], 'foreign_keys' => [ { columns => [ 'agentnum' ], @@ -2232,7 +2252,7 @@ sub tables_hashref { 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'status', 'varchar', '', $char_d, '', '', - 'session_id', 'varchar', 'NULL', $char_d, '', '', #only need 32 + 'session_id', 'varchar', 'NULL', 1024, '', '', # SHA-512-hex 'statustext', 'text', 'NULL', '', '', '', 'gatewaynum', 'int', 'NULL', '', '', '', #'cust_balance', @money_type, '', '', @@ -2573,7 +2593,7 @@ sub tables_hashref { 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', - 'agent_pkgid', 'int', 'NULL', '', '', '', + 'agent_pkgid', 'varchar', 'NULL', $char_d, '', '', 'waive_setup', 'char', 'NULL', 1, '', '', 'recur_show_zero', 'char', 'NULL', 1, '', '', 'setup_show_zero', 'char', 'NULL', 1, '', '', @@ -2584,9 +2604,17 @@ sub tables_hashref { 'index' => [ ['custnum'], ['pkgpart'], ['pkgbatch'], ['locationnum'], ['usernum'], ['agent_pkgid'], ['order_date'], [ 'start_date' ], ['setup'], ['bill'], - ['last_bill'], ['susp'], ['adjourn'], ['cancel'], - ['expire'], ['contract_end'], ['change_date'], + ['last_bill'], ['susp'], ['adjourn'], ['resume'], + ['cancel'], ['expire'], ['contract_end'], + ['change_date'], ['no_auto'], + #['contactnum'], + ['salesnum'], + #['uncancel_pkgnum'], + #['change_pkgnum'], ['change_locationnum'], + #['change_custnum'], + ['main_pkgnum'], + #['pkglinknum'], ['change_to_pkgnum'], ], 'foreign_keys' => [ { columns => [ 'custnum' ], @@ -2733,7 +2761,7 @@ sub tables_hashref { 'columns' => [ 'pkgusagenum', 'serial', '', '', '', '', 'pkgnum', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', 'pkgusagepart', 'int', '', '', '', '', ], 'primary_key' => 'pkgusagenum', @@ -2754,7 +2782,7 @@ sub tables_hashref { 'cdrusagenum', 'bigserial', '', '', '', '', 'acctid', 'bigint', '', '', '', '', 'pkgusagenum', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', ], 'primary_key' => 'cdrusagenum', 'unique' => [], @@ -4032,7 +4060,7 @@ sub tables_hashref { ], 'primary_key' => 'prepaynum', 'unique' => [ ['identifier'] ], - 'index' => [], + 'index' => [ ['agentnum'] ], 'foreign_keys' => [ { columns => [ 'agentnum' ], table => 'agent', @@ -4166,6 +4194,21 @@ sub tables_hashref { ], }, + 'queue_stat' => { + 'columns' => [ + 'statnum', 'bigserial', '', '', '', '', + 'jobnum', 'bigint', '', '', '', '', + 'job', 'varchar', '', 512, '', '', + 'custnum', 'int', 'NULL', '', '', '', + 'insert_date', @date_type, '', '', + 'start_date', @date_type, '', '', + 'end_date', @date_type, '', '', + ], + 'primary_key' => 'statnum', + 'unique' => [], #[ ['jobnum'] ], + 'index' => [], + }, + 'export_svc' => { 'columns' => [ 'exportsvcnum' => 'serial', '', '', '', '', @@ -4500,6 +4543,7 @@ sub tables_hashref { 'rssi', 'int', 'NULL', '', '', '', 'suid', 'int', 'NULL', '', '', '', 'shared_svcnum', 'int', 'NULL', '', '', '', + 'serviceid', 'varchar', 'NULL', 64, '', '',#srvexport/reportfields ], 'primary_key' => 'svcnum', 'unique' => [ [ 'ip_addr' ], [ 'mac_addr' ] ], @@ -4545,16 +4589,16 @@ sub tables_hashref { 'tower_sector' => { 'columns' => [ - 'sectornum', 'serial', '', '', '', '', - 'towernum', 'int', '', '', '', '', - 'sectorname', 'varchar', '', $char_d, '', '', - 'ip_addr', 'varchar', 'NULL', 15, '', '', - 'height', 'decimal', 'NULL', '', '', '', - 'freq_mhz', 'int', 'NULL', '', '', '', - 'direction', 'int', 'NULL', '', '', '', - 'width', 'int', 'NULL', '', '', '', + 'sectornum', 'serial', '', '', '', '', + 'towernum', 'int', '', '', '', '', + 'sectorname', 'varchar', '', $char_d, '', '', + 'ip_addr', 'varchar', 'NULL', 15, '', '', + 'height', 'decimal', 'NULL', '', '', '', + 'freq_mhz', 'int', 'NULL', '', '', '', + 'direction', 'int', 'NULL', '', '', '', + 'width', 'int', 'NULL', '', '', '', #downtilt etc? rfpath has profile files for devices/antennas you upload? - 'range', 'decimal', 'NULL', '', '', '', #? + 'sector_range', 'decimal', 'NULL', '', '', '', #? ], 'primary_key' => 'sectornum', 'unique' => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ], @@ -4710,7 +4754,7 @@ sub tables_hashref { 'columns' => [ 'pkgusagepart', 'serial', '', '', '', '', 'pkgpart', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', 'priority', 'int', 'NULL', '', '', '', 'shared', 'char', 'NULL', 1, '', '', 'rollover', 'char', 'NULL', 1, '', '', @@ -4745,14 +4789,32 @@ sub tables_hashref { ], }, + 'part_pkg_fcc_option' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'fccoptionname', 'varchar', '', $char_d, '', '', + 'pkgpart', 'int', '', '', '', '', + 'optionvalue', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'fccoptionname', 'pkgpart' ] ], + 'index' => [], + }, + 'rate' => { 'columns' => [ - 'ratenum', 'serial', '', '', '', '', - 'ratename', 'varchar', '', $char_d, '', '', + 'ratenum', 'serial', '', '', '', '', + 'ratename', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'ratenum', 'unique' => [], 'index' => [], + 'foreign_keys' => [ + { columns => [ 'agentnum' ], + table => 'agent', + }, + ], }, 'rate_detail' => { @@ -4763,13 +4825,15 @@ sub tables_hashref { 'dest_regionnum', 'int', '', '', '', '', 'min_included', 'int', '', '', '', '', 'conn_charge', 'decimal', '', '10,4', '0.0000', '', + 'conn_cost', 'decimal', '', '10,4', '0.0000', '', 'conn_sec', 'int', '', '', '0', '', 'min_charge', 'decimal', '', '10,5', '', '', + 'min_cost', 'decimal', '', '10,5','0.00000', '', 'sec_granularity', 'int', '', '', '', '', 'ratetimenum', 'int', 'NULL', '', '', '', 'classnum', 'int', 'NULL', '', '', '', 'cdrtypenum', 'int', 'NULL', '', '', '', - 'region_group', 'char', 'NULL', 1, '', '', + 'region_group', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], @@ -5049,11 +5113,12 @@ sub tables_hashref { 'pkg_category' => { 'columns' => [ - 'categorynum', 'serial', '', '', '', '', - 'categoryname', 'varchar', '', $char_d, '', '', - 'weight', 'int', 'NULL', '', '', '', - 'condense', 'char', 'NULL', 1, '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'categorynum', 'serial', '', '', '', '', + 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', + 'ticketing_queueid', 'int', 'NULL', '', '', '', + 'condense', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'categorynum', 'unique' => [], @@ -5479,6 +5544,44 @@ sub tables_hashref { 'index' => [], }, + 'sched_item' => { + 'columns' => [ + 'itemnum', 'serial', '', '', '', '', + 'usernum', 'int', 'NULL', '', '', '', + #'itemname', 'varchar', $char_d, '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'itemnum', + 'unique' => [ [ 'usernum' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'usernum' ], + table => 'access_user', + }, + ], + }, + + #'sched_item_class' + + 'sched_avail' => { + 'columns' => [ + 'availnum', 'serial', '', '', '', '', + 'itemnum', 'int', '', '', '', '', + 'wday', 'int', '', '', '', '', + 'stime', 'int', '', '', '', '', + 'etime', 'int', '', '', '', '', + 'override_date', @date_type, '', '', + ], + 'primary_key' => 'availnum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'itemnum' ], + table => 'sched_item', + }, + ], + }, + 'svc_phone' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -5504,6 +5607,8 @@ sub tables_hashref { 'sms_carrierid', 'int', 'NULL', '', '', '', 'sms_account', 'varchar', 'NULL', $char_d, '', '', 'max_simultaneous', 'int', 'NULL', '', '', '', + 'e911_class', 'char', 'NULL', 1, '', '', + 'e911_type', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'svcnum', 'unique' => [ [ 'sms_carrierid', 'sms_account'] ], @@ -6160,7 +6265,7 @@ sub tables_hashref { 'columns' => [ 'logcontextnum', 'serial', '', '', '', '', 'lognum', 'int', '', '', '', '', - 'context', 'varchar', '', 32, '', '', + 'context', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'logcontextnum', 'unique' => [ [ 'lognum', 'context' ] ], @@ -6264,7 +6369,7 @@ sub tables_hashref { 'mac_addr', 'varchar', 'NULL', 12, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [], + 'unique' => [ ['serialnum'] , ['mac_addr'] ], 'index' => [], 'foreign_keys' => [ { columns => [ 'svcnum' ], @@ -6405,7 +6510,8 @@ sub tables_hashref { 'columns' => [ 'vendbillnum', 'serial', '', '', '', '', 'vendnum', 'int', '', '', '', '', - '_date', @date_type, '', '', + #'_date', @date_type, '', '', + '_date', 'int', '', '', '', '', 'charged', @money_type, '', '', ], 'primary_key' => 'vendbillnum', @@ -6422,7 +6528,8 @@ sub tables_hashref { 'columns' => [ 'vendpaynum', 'serial', '', '', '', '', 'vendnum', 'int', '', '', '', '', - '_date', @date_type, '', '', + #'_date', @date_type, '', '', + '_date', 'int', '', '', '', '', 'paid', @money_type, '', '', ], 'primary_key' => 'vendpaynum', @@ -6531,6 +6638,130 @@ sub tables_hashref { ], }, + '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', '', '', '', '', + '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, '', '', + ], + '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', '', 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' ], + }, + ], + }, + + + + + # name type nullability length default local #'new_table' => {