X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=b3ef4095a762ab9daee9384a91d9d05f367c2a5f;hb=79a7bf5a7dc369181849e4a0771b0c4d2d722d1c;hp=3b1bc0e05d4d9537f23469fd1be77a1382d6da88;hpb=595998d86ea3e75efa16eba5d7f1319394cf62b2;p=freeside.git diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 3b1bc0e05..b3ef4095a 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -201,11 +201,9 @@ sub dbdef_dist { grep { ! /^(clientapi|access_user)_session/ && ! /^h_/ && ! /^log(_context)?$/ - && ! /^legacy_cust_history$/ + && ! /^(legacy_cust_history|cacti_page|template_image|access_user_log)$/ && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} ) && ! $tables_hashref_torrus->{$_} - && ! /^cacti_page$/ - && ! /^template_image$/ } $dbdef->tables ) { @@ -925,12 +923,13 @@ sub tables_hashref { '_date', @date_type, '', '', 'status', 'varchar', '', $char_d, '', '', 'statustext', 'text', 'NULL', '', '', '', + 'no_action', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'eventnum', #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ], 'unique' => [], 'index' => [ ['eventpart'], ['tablenum'], ['status'], - ['statustext'], ['_date'], + ['statustext'], ['_date'], ['no_action'], ], 'foreign_keys' => [ { columns => [ 'eventpart' ], @@ -1624,6 +1623,9 @@ sub tables_hashref { 'paystate', 'varchar', 'NULL', $char_d, '', '', 'paytype', 'varchar', 'NULL', $char_d, '', '', 'payip', 'varchar', 'NULL', 15, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', + + 'is_tokenized', 'char', 'NULL', '1', '', '', 'geocode', 'varchar', 'NULL', 20, '', '', 'censustract', 'varchar', 'NULL', 20, '', '', # 7 to save space? @@ -1646,6 +1648,7 @@ sub tables_hashref { 'accountcode_cdr', 'char', 'NULL', 1, '', '', 'billday', 'int', 'NULL', '', '', '', 'prorate_day', 'int', 'NULL', '', '', '', + 'force_prorate_day', 'char', 'NULL', 1, '', '', 'edit_subject', 'char', 'NULL', 1, '', '', 'locale', 'varchar', 'NULL', 16, '', '', 'calling_list_exempt', 'char', 'NULL', 1, '', '', @@ -1671,6 +1674,8 @@ sub tables_hashref { [ 'archived' ], [ 'ship_locationnum' ], [ 'bill_locationnum' ], + [ 'paycardtype' ], + [ 'is_tokenized' ], ], 'foreign_keys' => [ { columns => [ 'agentnum' ], @@ -1710,7 +1715,7 @@ sub tables_hashref { 'weight', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', - 'cardtype', 'varchar', 'NULL', $char_d, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', 'paycvv', 'varchar', 'NULL', 512, '', '', 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate', @date_type, '', '', @@ -1723,10 +1728,11 @@ sub tables_hashref { 'paytype', 'varchar', 'NULL', $char_d, '', '', 'payip', 'varchar', 'NULL', 15, '', '', 'locationnum', 'int', 'NULL', '', '', '', + 'is_tokenized', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custpaybynum', 'unique' => [], - 'index' => [ [ 'custnum' ] ], + 'index' => [ [ 'custnum' ], [ 'is_tokenized' ] ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -1919,14 +1925,15 @@ sub tables_hashref { 'quotation' => { 'columns' => [ #regular fields - 'quotationnum', 'serial', '', '', '', '', - 'prospectnum', 'int', 'NULL', '', '', '', - 'custnum', 'int', 'NULL', '', '', '', - '_date', @date_type, '', '', - 'disabled', 'char', 'NULL', 1, '', '', - 'usernum', 'int', 'NULL', '', '', '', - 'close_date', @date_type, '', '', - 'confidence', 'int', 'NULL', '', '', '', + 'quotationnum', 'serial', '', '', '', '', + 'prospectnum', 'int', 'NULL', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', + '_date', @date_type, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'close_date', @date_type, '', '', + 'confidence', 'int', 'NULL', '', '', '', + 'quotation_description', 'varchar', 'NULL', '50', '', '', #'total', @money_type, '', '', #'quotation_term', 'varchar', 'NULL', $char_d, '', '', ], @@ -2056,9 +2063,12 @@ sub tables_hashref { 'district', 'varchar', 'NULL', 20, '', '', 'censustract', 'varchar', 'NULL', 20, '', '', 'censusyear', 'char', 'NULL', 4, '', '', + + #used only in... DSL qualification? .CA DSL qualification? 'location_type', 'varchar', 'NULL', 20, '', '', 'location_number', 'varchar', 'NULL', 20, '', '', 'location_kind', 'char', 'NULL', 1, '', '', + 'incorporated', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], @@ -2393,7 +2403,7 @@ sub tables_hashref { 'cust_pay_pending' => { 'columns' => [ 'paypendingnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', 'paid', @money_type, '', '', 'currency', 'char', 'NULL', 3, '', '', '_date', @date_type, '', '', @@ -2417,11 +2427,13 @@ sub tables_hashref { 'manual', 'char', 'NULL', 1, '', '', 'discount_term','int', 'NULL', '', '', '', 'failure_status','varchar','NULL', 16, '', '', + 'is_tokenized', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'paypendingnum', 'unique' => [ [ 'payunique' ] ], 'index' => [ [ 'custnum' ], [ 'status' ], ['paynum'], ['void_paynum'], ['jobnum'], ['invnum'], + [ 'is_tokenized' ], ], 'foreign_keys' => [ { columns => [ 'custnum' ], @@ -2460,6 +2472,7 @@ sub tables_hashref { 'usernum', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paydate', 'varchar', 'NULL', 10, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '',#for auditing purposes @@ -2467,7 +2480,8 @@ sub tables_hashref { 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'no_auto_apply', 'char', 'NULL', 1, '', '', - + 'is_tokenized', 'char', 'NULL', 1, '', '', + # cash/check deposit info fields 'bank', 'varchar', 'NULL', $char_d, '', '', 'depositor', 'varchar', 'NULL', $char_d, '', '', @@ -2480,12 +2494,12 @@ sub tables_hashref { 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK 'processor', 'varchar', 'NULL', $char_d, '', '', # module name 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number - 'order_number','varchar', 'NULL', $char_d, '', '', # transaction number + 'order_number','varchar', 'NULL', 256, '', '', # transaction number ], 'primary_key' => 'paynum', #i guess not now, with cust_pay_pending, if we actually make it here, we _do_ want to record it# 'unique' => [ [ 'payunique' ] ], 'index' => [ ['custnum'], ['paybatch'], ['payby'], ['_date'], - ['usernum'], + ['usernum'], ['is_tokenized'], ], 'foreign_keys' => [ { columns => [ 'custnum' ], @@ -2517,11 +2531,13 @@ sub tables_hashref { 'usernum', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', - 'paymask', 'varchar', 'NULL', $char_d, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate' ? 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + 'is_tokenized', 'char', 'NULL', 1, '', '', # cash/check deposit info fields 'bank', 'varchar', 'NULL', $char_d, '', '', @@ -2534,7 +2550,7 @@ sub tables_hashref { 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK 'processor', 'varchar', 'NULL', $char_d, '', '', # module name 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number - 'order_number','varchar', 'NULL', $char_d, '', '', # transaction number + 'order_number','varchar', 'NULL', 256, '', '', # transaction number #void fields 'void_date', @date_type, '', '', @@ -2544,7 +2560,9 @@ sub tables_hashref { ], 'primary_key' => 'paynum', 'unique' => [], - 'index' => [ ['custnum'], ['usernum'], ['void_usernum'] ], + 'index' => [ ['custnum'], ['usernum'], ['void_usernum'], + ['is_tokenized'], + ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -3077,7 +3095,8 @@ sub tables_hashref { # be index into payby # table eventually 'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above - 'paymask', 'varchar', 'NULL', $char_d, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '', 'closed', 'char', 'NULL', 1, '', '', 'source_paynum', 'int', 'NULL', '', '', '', # link to cust_payby, to prevent unapply of gateway-generated refunds @@ -3086,10 +3105,11 @@ sub tables_hashref { 'processor', 'varchar', 'NULL', $char_d, '', '', # module name 'auth', 'varchar','NULL',16, '', '', # CC auth number 'order_number', 'varchar','NULL',$char_d, '', '', # transaction number + 'is_tokenized', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'refundnum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'], [ 'usernum' ], ], + 'index' => [ ['custnum'], ['_date'], [ 'usernum' ], ['is_tokenized'] ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -3609,10 +3629,11 @@ sub tables_hashref { 'refnum', 'serial', '', '', '', '', 'referral', 'varchar', '', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', - 'agentnum', 'int', 'NULL', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'refnum', - 'unique' => [], + 'unique' => [ ['agentnum', 'title'] ], 'index' => [ ['disabled'], ['agentnum'], ], 'foreign_keys' => [ { columns => [ 'agentnum' ], @@ -3699,6 +3720,24 @@ sub tables_hashref { ], }, + 'part_svc_msgcat' => { + 'columns' => [ + 'svcpartmsgnum', 'serial', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'locale', 'varchar', '', 16, '', '', + 'svc', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'svcpartmsgnum', + 'unique' => [ [ 'svcpart', 'locale' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'svcpart' ], + table => 'part_svc', + }, + ], + }, + + #(this should be renamed to part_pop) 'svc_acct_pop' => { 'columns' => [ @@ -3887,9 +3926,12 @@ sub tables_hashref { 'unique' => [], 'index' => [ ['svcnum', 'transaction_id'] ], 'foreign_keys' => [ - { columns => [ 'svcnum' ], - table => 'svc_acct', #'cust_svc', - }, + # problems w/deleted services, and as per below, this + # is our internal hack, not a customer-facing feature + #{ columns => [ 'svcnum' ], + # table => 'svc_acct', #'cust_svc', + #}, + # 1. RT tables aren't part of our data structure, so # we can't make sure Queue is created already # 2. This is our internal hack for time tracking, not @@ -4472,6 +4514,26 @@ sub tables_hashref { ], }, + 'export_cust_svc' => { + 'columns' => [ + 'exportcustsvcnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'remoteid', 'varchar', '', 512, '', '', + ], + 'primary_key' => 'exportcustsvcnum', + 'unique' => [ [ 'exportnum', 'svcnum' ] ], + 'index' => [ [ 'exportnum', 'svcnum' ] ], + 'foreign_keys' => [ + { columns => [ 'exportnum' ], + table => 'part_export', + }, + { columns => [ 'svcnum' ], + table => 'cust_svc', + }, + ], + }, + 'export_device' => { 'columns' => [ 'exportdevicenum' => 'serial', '', '', '', '', @@ -4842,9 +4904,16 @@ sub tables_hashref { 'freq_mhz', 'int', 'NULL', '', '', '', 'direction', 'int', 'NULL', '', '', '', 'width', 'int', 'NULL', '', '', '', - #downtilt etc? rfpath has profile files for devices/antennas you upload? 'sector_range', 'decimal', 'NULL', '', '', '', #? - ], + 'downtilt', 'decimal', 'NULL', '', '', '', + 'v_width', 'int', 'NULL', '', '', '', + 'margin', 'decimal', 'NULL', '', '', '', + 'image', 'blob', 'NULL', '', '', '', + 'west', 'decimal', 'NULL', '10,7', '', '', + 'east', 'decimal', 'NULL', '10,7', '', '', + 'south', 'decimal', 'NULL', '10,7', '', '', + 'north', 'decimal', 'NULL', '10,7', '', '', + ], 'primary_key' => 'sectornum', 'unique' => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ], 'index' => [ [ 'towernum' ] ], @@ -5135,7 +5204,7 @@ sub tables_hashref { 'ocn', 'char', 'NULL', 4, '', '', ], 'primary_key' => 'prefixnum', - 'unique' => [], + 'unique' => [ [ 'countrycode', 'npa', 'nxx' ] ], 'index' => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ], 'foreign_keys' => [ { columns => [ 'regionnum' ], @@ -5503,6 +5572,10 @@ sub tables_hashref { 'rated_ratename', 'varchar', 'NULL', $char_d, '', '', 'rated_cost', 'decimal', 'NULL', '10,4', '', '', + # real endpoints of the call + 'src_lrn', 'varchar', 'NULL', '15', '', '', + 'dst_lrn', 'varchar', 'NULL', '15', '', '', + 'carrierid', 'bigint', 'NULL', '', '', '', # service it was matched to @@ -5534,6 +5607,8 @@ sub tables_hashref { 'unique' => [], 'index' => [ [ 'calldate' ], [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ], + [ 'startdate' ], [ 'duration' ], + [ 'cdrtypenum' ], [ 'calltypenum' ], [ 'lastapp' ], ['accountcode'], ['uniqueid'], ['carrierid'], ['cdrid'], [ 'sessionnum' ], [ 'subscriber' ], @@ -5565,6 +5640,7 @@ sub tables_hashref { 'rated_price', 'decimal', 'NULL', '10,4', '', '', 'rated_seconds', 'int', 'NULL', '', '', '', 'rated_minutes', 'double precision', 'NULL', '', '', '', + 'rated_granularity','int', 'NULL', '', '', '', 'status', 'varchar', 'NULL', 32, '', '', 'svcnum', 'int', 'NULL', '', '', '', ], @@ -5811,6 +5887,26 @@ sub tables_hashref { 'index' => [ ['usernum'], ['path'], ['_date'] ], }, + 'access_user_page_pref' => { + 'columns' => [ + 'prefnum' => 'serial', '', '', '', '', + 'usernum' => 'int', '', '', '', '', + 'path' => 'text', '', '', '', '', + 'tablenum' => 'int', 'NULL', '', '', '', + '_date' => @date_type, '', '', + 'prefname' => 'varchar', '', $char_d, '', '', + 'prefvalue' => 'text', '', '', '', '', + ], + 'primary_key' => 'prefnum', + 'unique' => [ [ 'usernum', 'path', 'tablenum', 'prefname' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'usernum' ], + table => 'access_user' + }, + ], + }, + 'sched_item' => { 'columns' => [ 'itemnum', 'serial', '', '', '', '', @@ -5872,6 +5968,9 @@ sub tables_hashref { 'lnp_other_provider', 'varchar', 'NULL', $char_d, '', '', 'lnp_other_provider_account', 'varchar', 'NULL', $char_d, '', '', 'lnp_reject_reason', 'varchar', 'NULL', $char_d, '', '', + 'lnp_portid', 'varchar', 'NULL', $char_d, '', '', + 'lnp_signature', 'char', 'NULL', 1, '', '', + 'lnp_bill', 'char', 'NULL', 1, '', '', 'sms_carrierid', 'int', 'NULL', '', '', '', 'sms_account', 'varchar', 'NULL', $char_d, '', '', 'max_simultaneous', 'int', 'NULL', '', '', '', @@ -6602,6 +6701,7 @@ sub tables_hashref { 'min_level', 'int', 'NULL', '', '', '', 'msgnum', 'int', '', '', '', '', 'to_addr', 'varchar', 'NULL', 255, '', '', + 'context_height', 'int', 'NULL', '', '', '', ], 'primary_key' => 'logemailnum', 'unique' => [], @@ -6868,6 +6968,7 @@ sub tables_hashref { 'vendor_order_status', 'varchar', 'NULL', $char_d, '', '', 'endpoint_ip_addr', 'varchar', 'NULL', 40, '', '', 'endpoint_mac_addr', 'varchar', 'NULL', 12, '', '', + 'internal_circuit_id', 'varchar', '', 64, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -7247,6 +7348,7 @@ sub tables_hashref { 'graphnum', 'int', 'NULL', '', '', '', 'imported', @date_type, '', '', 'content', 'text', 'NULL', '', '', '', + 'thumbnail', 'text', 'NULL', '', '', '', ], 'primary_key' => 'cacti_pagenum', 'unique' => [ ], @@ -7340,6 +7442,71 @@ sub tables_hashref { ], }, + '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', + }, + ], + }, + + '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', + }, + ], + }, + # name type nullability length default local #'new_table' => {