X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=0a70eb6bc0a699bb428f8c482f69f55a7f731794;hb=ec863ef089ed20e21caeea235924de5b5d0e6fd7;hp=76186115b33c23913c6488c1d5b394e7059cd522;hpb=cffe61b65c1304e5cbb34fba65673c8448113a5f;p=freeside.git diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 76186115b..0a70eb6bc 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -3,9 +3,9 @@ package FS::Schema; use vars qw(@ISA @EXPORT_OK $DEBUG $setup_hack %dbdef_cache); use subs qw(reload_dbdef); use Exporter; -use DBIx::DBSchema 0.33; +use DBIx::DBSchema 0.40; #0.40 for mysql upgrade fixes use DBIx::DBSchema::Table; -use DBIx::DBSchema::Column 0.06; +use DBIx::DBSchema::Column; use DBIx::DBSchema::Index; @ISA = qw(Exporter); @@ -70,7 +70,7 @@ sub dbdef { $dbdef; } Returns the current canoical database definition as defined in this file. Optionally, pass a DBI data source to enable syntax specific to that database. -Currently, this enables "TYPE=InnoDB" for MySQL databases. +Currently, this enables "ENGINE=InnoDB" for MySQL databases. =cut @@ -79,7 +79,7 @@ sub dbdef_dist { my $local_options = ''; if ( $datasrc =~ /^dbi:mysql/i ) { - $local_options = 'TYPE=InnoDB'; + $local_options = 'ENGINE=InnoDB'; } ### @@ -191,6 +191,7 @@ sub dbdef_dist { foreach my $table ( grep { ! /^clientapi_session/ && ! /^h_/ + && ! /^log(_context)?$/ && ! $tables_hashref_torrus->{$_} } $dbdef->tables @@ -235,6 +236,12 @@ sub dbdef_dist { } + my $historynum_type = ( $tableobj->column($tableobj->primary_key)->type + =~ /^(bigserial|bigint|int8)$/i + ? 'bigserial' + : 'serial' + ); + my $h_tableobj = DBIx::DBSchema::Table->new( { 'name' => "h_$table", 'primary_key' => 'historynum', @@ -243,7 +250,7 @@ sub dbdef_dist { 'columns' => [ DBIx::DBSchema::Column->new( { 'name' => 'historynum', - 'type' => 'serial', + 'type' => $historynum_type, 'null' => 'NOT NULL', 'length' => '', 'default' => '', @@ -473,6 +480,18 @@ sub tables_hashref { 'index' => [ ['typenum'], ['disabled'], ['agent_custnum'] ], }, + 'agent_pkg_class' => { + 'columns' => [ + 'agentpkgclassnum', 'serial', '', '', '', '', + 'agentnum', 'int', '', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'commission_percent', 'decimal', '', '7,4', '', '', + ], + 'primary_key' => 'agentpkgclassnum', + 'unique' => [ [ 'agentnum', 'classnum' ], ], + 'index' => [], + }, + 'agent_type' => { 'columns' => [ 'typenum', 'serial', '', '', '', '', @@ -494,6 +513,18 @@ sub tables_hashref { 'index' => [ ['typenum'] ], }, + 'sales' => { + 'columns' => [ + 'salesnum', 'serial', '', '', '', '', + 'salesperson', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'salesnum', + 'unique' => [], + 'index' => [ ['salesnum'], ['disabled'] ], + }, + 'cust_attachment' => { 'columns' => [ 'attachnum', 'serial', '', '', '', '', @@ -532,12 +563,31 @@ sub tables_hashref { 'closed', 'char', 'NULL', 1, '', '', #not yet used much 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy + 'promised_date', @date_type, '', '', ], 'primary_key' => 'invnum', 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh 'index' => [ ['custnum'], ['_date'], ['statementnum'], ['agent_invid'] ], }, + #for importing invoices from a legacy system for display purposes only + # no effect upon balance + 'legacy_cust_bill' => { + 'columns' => [ + 'legacyinvnum', 'serial', '', '', '', '', + 'legacyid', 'varchar', 'NULL', $char_d, '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'charged', @money_type, '', '', + 'content_pdf', 'blob', 'NULL', '', '', '', + 'content_html', 'text', 'NULL', '', '', '', + 'locale', 'varchar', 'NULL', 16, '', '', + ], + 'primary_key' => 'legacyinvnum', + 'unique' => [], + 'index' => [ ['legacyid', 'custnum', 'locale' ], ], + }, + 'cust_statement' => { 'columns' => [ 'statementnum', 'serial', '', '', '', '', @@ -699,7 +749,7 @@ sub tables_hashref { 'format', 'char', 'NULL', 1, '', '', 'classnum', 'int', 'NULL', '', '', '', 'duration', 'int', 'NULL', '', 0, '', - 'phonenum', 'varchar', 'NULL', 15, '', '', + 'phonenum', 'varchar', 'NULL', 25, '', '', 'accountcode', 'varchar', 'NULL', 20, '', '', 'startdate', @date_type, '', '', 'regionname', 'varchar', 'NULL', $char_d, '', '', @@ -770,10 +820,13 @@ sub tables_hashref { 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'eventnum', 'int', 'NULL', '', '', '', #triggering event for commission + 'credbatch', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'crednum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'] ], + 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'], + ['credbatch'], + ], }, 'cust_credit_bill' => { @@ -825,7 +878,10 @@ sub tables_hashref { 'ss', 'varchar', 'NULL', 11, '', '', 'stateid', 'varchar', 'NULL', $char_d, '', '', 'stateid_state', 'varchar', 'NULL', $char_d, '', '', + 'national_id', 'varchar', 'NULL', $char_d, '', '', 'birthdate' ,@date_type, '', '', + 'spouse_birthdate' ,@date_type, '', '', + 'anniversary_date' ,@date_type, '', '', 'signupdate',@date_type, '', '', 'dundate', @date_type, '', '', 'company', 'varchar', 'NULL', $char_d, '', '', @@ -836,9 +892,13 @@ sub tables_hashref { 'state', 'varchar', 'NULL', $char_d, '', '', 'zip', 'varchar', 'NULL', 10, '', '', 'country', 'char', '', 2, '', '', + 'latitude', 'decimal', 'NULL', '10,7', '', '', + 'longitude','decimal', 'NULL', '10,7', '', '', + 'coord_auto', 'char', 'NULL', 1, '', '', 'daytime', 'varchar', 'NULL', 20, '', '', 'night', 'varchar', 'NULL', 20, '', '', 'fax', 'varchar', 'NULL', 12, '', '', + 'mobile', 'varchar', 'NULL', 12, '', '', 'ship_last', 'varchar', 'NULL', 2*$char_d, '', '', # 'ship_middle', 'varchar', 'NULL', $char_d, '', '', 'ship_first', 'varchar', 'NULL', $char_d, '', '', @@ -850,9 +910,13 @@ sub tables_hashref { 'ship_state', 'varchar', 'NULL', $char_d, '', '', 'ship_zip', 'varchar', 'NULL', 10, '', '', 'ship_country', 'char', 'NULL', 2, '', '', + 'ship_latitude', 'decimal', 'NULL', '10,7', '', '', + 'ship_longitude','decimal', 'NULL', '10,7', '', '', + 'ship_coord_auto', 'char', 'NULL', 1, '', '', 'ship_daytime', 'varchar', 'NULL', 20, '', '', 'ship_night', 'varchar', 'NULL', 20, '', '', 'ship_fax', 'varchar', 'NULL', 12, '', '', + 'ship_mobile', 'varchar', 'NULL', 12, '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', 'paycvv', 'varchar', 'NULL', 512, '', '', @@ -868,6 +932,8 @@ sub tables_hashref { 'payip', 'varchar', 'NULL', 15, '', '', 'geocode', 'varchar', 'NULL', 20, '', '', 'censustract', 'varchar', 'NULL', 20, '', '', # 7 to save space? + 'censusyear', 'char', 'NULL', 4, '', '', + 'district', 'varchar', 'NULL', 20, '', '', 'tax', 'char', 'NULL', 1, '', '', 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', @@ -876,15 +942,18 @@ sub tables_hashref { 'comments', 'text', 'NULL', '', '', '', 'spool_cdr','char', 'NULL', 1, '', '', 'squelch_cdr','char', 'NULL', 1, '', '', - 'cdr_termination_percentage', 'decimal', 'NULL', '', '', '', + 'cdr_termination_percentage', 'decimal', 'NULL', '7,4', '', '', 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', 'credit_limit', @money_typen, '', '', 'archived', 'char', 'NULL', 1, '', '', 'email_csv_cdr', 'char', 'NULL', 1, '', '', 'accountcode_cdr', 'char', 'NULL', 1, '', '', 'billday', 'int', 'NULL', '', '', '', + 'prorate_day', 'int', 'NULL', '', '', '', 'edit_subject', 'char', 'NULL', 1, '', '', 'locale', 'varchar', 'NULL', 16, '', '', + 'calling_list_exempt', 'char', 'NULL', 1, '', '', + 'invoice_noemail', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -899,12 +968,12 @@ sub tables_hashref { [ 'last' ], [ 'company' ], [ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ], - [ 'daytime' ], [ 'night' ], [ 'fax' ], + [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'mobile' ], #shipping [ 'ship_last' ], [ 'ship_company' ], [ 'ship_county' ], [ 'ship_state' ], [ 'ship_country' ], [ 'ship_zip' ], - [ 'ship_daytime' ], [ 'ship_night' ], [ 'ship_fax' ], + [ 'ship_daytime' ], [ 'ship_night' ], [ 'ship_fax' ], [ 'ship_mobile' ] ], }, @@ -1020,8 +1089,12 @@ sub tables_hashref { 'county', 'varchar', 'NULL', $char_d, '', '', 'state', 'varchar', 'NULL', $char_d, '', '', 'zip', 'varchar', 'NULL', 10, '', '', + 'latitude', 'decimal', 'NULL', '10,7', '', '', + 'longitude', 'decimal', 'NULL', '10,7', '', '', + 'coord_auto', 'char', 'NULL', 1, '', '', 'country', 'char', '', 2, '', '', 'geocode', 'varchar', 'NULL', 20, '', '', + 'district', 'varchar', 'NULL', 20, '', '', 'location_type', 'varchar', 'NULL', 20, '', '', 'location_number', 'varchar', 'NULL', 20, '', '', 'location_kind', 'char', 'NULL', 1, '', '', @@ -1112,6 +1185,7 @@ sub tables_hashref { 'tagname', 'varchar', '', $char_d, '', '', 'tagdesc', 'varchar', 'NULL', $char_d, '', '', 'tagcolor', 'varchar', 'NULL', 6, '', '', + 'by_default', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'tagnum', @@ -1121,9 +1195,10 @@ sub tables_hashref { 'cust_main_exemption' => { 'columns' => [ - 'exemptionnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', - 'taxname', 'varchar', '', $char_d, '', '', + 'exemptionnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'taxname', 'varchar', '', $char_d, '', '', + 'exempt_number', 'varchar', 'NULL', $char_d, '', '', #start/end dates? for reporting? ], 'primary_key' => 'exemptionnum', @@ -1146,11 +1221,12 @@ sub tables_hashref { 'index' => [ [ 'custnum' ], [ 'billpkgnum' ] ], }, - 'cust_main_county' => { #county+state+country are checked off the - #cust_main_county for validation and to provide - # a tax rate. + 'cust_main_county' => { #district+city+county+state+country are checked + #off the cust_main_county for validation and to + #provide a tax rate. 'columns' => [ 'taxnum', 'serial', '', '', '', '', + 'district', 'varchar', 'NULL', 20, '', '', 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', 'state', 'varchar', 'NULL', $char_d, '', '', @@ -1165,7 +1241,8 @@ sub tables_hashref { 'primary_key' => 'taxnum', 'unique' => [], # 'unique' => [ ['taxnum'], ['state', 'county'] ], - 'index' => [ [ 'city' ], [ 'county' ], [ 'state' ], [ 'country' ], + 'index' => [ [ 'district' ], [ 'city' ], [ 'county' ], [ 'state' ], + [ 'country' ], [ 'taxclass' ], ], }, @@ -1277,7 +1354,7 @@ sub tables_hashref { 'gatewaynum', 'int', 'NULL', '', '', '', #'cust_balance', @money_type, '', '', 'paynum', 'int', 'NULL', '', '', '', - 'jobnum', 'int', 'NULL', '', '', '', + 'jobnum', 'bigint', 'NULL', '', '', '', ], 'primary_key' => 'paypendingnum', 'unique' => [ [ 'payunique' ] ], @@ -1296,12 +1373,18 @@ sub tables_hashref { # index into payby table # eventually 'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above - 'paymask', 'varchar', 'NULL', $char_d, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paydate', 'varchar', 'NULL', 10, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + # cash/check deposit info fields + 'bank', 'varchar', 'NULL', $char_d, '', '', + 'depositor', 'varchar', 'NULL', $char_d, '', '', + 'account', 'varchar', 'NULL', 20, '', '', + 'teller', 'varchar', 'NULL', 20, '', '', + 'batchnum', 'int', 'NULL', '', '', '', #pay_batch foreign key ], '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' ] ], @@ -1379,19 +1462,19 @@ sub tables_hashref { 'pay_batch' => { #batches of payments to an external processor 'columns' => [ - 'batchnum', 'serial', '', '', '', '', - 'payby', 'char', '', 4, '', '', # CARD/CHEK - 'status', 'char', 'NULL', 1, '', '', - 'download', @date_type, '', '', - 'upload', @date_type, '', '', + 'batchnum', 'serial', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'payby', 'char', '', 4, '', '', # CARD/CHEK + 'status', 'char', 'NULL', 1, '', '', + 'download', @date_type, '', '', + 'upload', @date_type, '', '', ], 'primary_key' => 'batchnum', 'unique' => [], 'index' => [], }, - 'cust_pay_batch' => { #what's this used for again? list of customers - #in current CARD batch? (necessarily CARD?) + 'cust_pay_batch' => { #list of customers in current CARD/CHEK batch 'columns' => [ 'paybatchnum', 'serial', '', '', '', '', 'batchnum', 'int', '', '', '', '', @@ -1406,13 +1489,14 @@ sub tables_hashref { 'zip', 'varchar', 'NULL', 10, '', '', 'country', 'char', '', 2, '', '', # 'trancode', 'int', '', '', '', '' - 'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should be - 'payinfo', 'varchar', '', 512, '', '', + 'payby', 'char', '', 4, '', '', + 'payinfo', 'varchar', 'NULL', 512, '', '', #'exp', @date_type, '', '' - 'exp', 'varchar', 'NULL', 11, '', '', + 'exp', 'varchar', 'NULL', 11, '', '', 'payname', 'varchar', 'NULL', $char_d, '', '', 'amount', @money_type, '', '', - 'status', 'varchar', 'NULL', $char_d, '', '', + 'status', 'varchar', 'NULL', $char_d, '', '', + 'error_message', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'paybatchnum', 'unique' => [], @@ -1445,7 +1529,10 @@ sub tables_hashref { 'last_bill', @date_type, '', '', 'susp', @date_type, '', '', 'adjourn', @date_type, '', '', + 'resume', @date_type, '', '', 'cancel', @date_type, '', '', + 'uncancel', @date_type, '', '', + 'uncancel_pkgnum', 'int', 'NULL', '', '', '', 'expire', @date_type, '', '', 'contract_end', @date_type, '', '', 'dundate', @date_type, '', '', @@ -1513,14 +1600,14 @@ sub tables_hashref { 'cust_pkg_discount' => { 'columns' => [ - 'pkgdiscountnum', 'serial', '', '', '', '', - 'pkgnum', 'int', '', '', '', '', - 'discountnum', 'int', '', '', '', '', - 'months_used', 'decimal', 'NULL', '', '', '', - 'end_date', @date_type, '', '', - 'otaker', 'varchar', 'NULL', 32, '', '', - 'usernum', 'int', 'NULL', '', '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'pkgdiscountnum', 'serial', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'discountnum', 'int', '', '', '', '', + 'months_used', 'decimal', 'NULL', '7,4', '', '', + 'end_date', @date_type, '', '', + 'otaker', 'varchar', 'NULL', 32, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgdiscountnum', 'unique' => [], @@ -1529,11 +1616,11 @@ sub tables_hashref { 'cust_bill_pkg_discount' => { 'columns' => [ - 'billpkgdiscountnum', 'serial', '', '', '', '', - 'billpkgnum', 'int', '', '', '', '', - 'pkgdiscountnum', 'int', '', '', '', '', - 'amount', @money_type, '', '', - 'months', 'decimal', 'NULL', '', '', '', + 'billpkgdiscountnum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'pkgdiscountnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'months', 'decimal', 'NULL', '7,4', '', '', ], 'primary_key' => 'billpkgdiscountnum', 'unique' => [], @@ -1546,8 +1633,8 @@ sub tables_hashref { #'agentnum', 'int', 'NULL', '', '', '', 'name', 'varchar', 'NULL', $char_d, '', '', 'amount', @money_type, '', '', - 'percent', 'decimal', '', '', '', '', - 'months', 'decimal', 'NULL', '', '', '', + 'percent', 'decimal', '', '7,4', '', '', + 'months', 'decimal', 'NULL', '7,4', '', '', 'disabled', 'char', 'NULL', 1, '', '', 'setup', 'char', 'NULL', 1, '', '', ], @@ -1594,14 +1681,15 @@ sub tables_hashref { 'cust_svc' => { 'columns' => [ - 'svcnum', 'serial', '', '', '', '', - 'pkgnum', 'int', 'NULL', '', '', '', - 'svcpart', 'int', '', '', '', '', - 'overlimit', @date_type, '', '', + 'svcnum', 'serial', '', '', '', '', + 'pkgnum', 'int', 'NULL', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'agent_svcid', 'int', 'NULL', '', '', '', + 'overlimit', @date_type, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], - 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ], + 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'], [ 'agent_svcid' ] ], }, 'cust_svc_option' => { @@ -1641,9 +1729,11 @@ sub tables_hashref { 'credit_weight', 'real', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', 'fcc_ds0s', 'int', 'NULL', '', '', '', + 'fcc_voip_class','char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'recur_show_zero', 'char', 'NULL', 1, '', '', 'setup_show_zero', 'char', 'NULL', 1, '', '', + 'delay_start', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgpart', 'unique' => [], @@ -1773,11 +1863,13 @@ sub tables_hashref { 'part_svc' => { 'columns' => [ - 'svcpart', 'serial', '', '', '', '', - 'svc', 'varchar', '', $char_d, '', '', - 'svcdb', 'varchar', '', $char_d, '', '', - 'disabled', 'char', 'NULL', 1, '', '', - 'preserve', 'char', 'NULL', 1, '', '', + 'svcpart', 'serial', '', '', '', '', + 'svc', 'varchar', '', $char_d, '', '', + 'svcdb', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'preserve', 'char', 'NULL', 1, '', '', + 'selfservice_access', 'varchar', 'NULL', $char_d, '', '', + 'restrict_edit_password','char', 'NULL', 1, '', '', ], 'primary_key' => 'svcpart', 'unique' => [], @@ -1786,12 +1878,12 @@ sub tables_hashref { 'part_svc_column' => { 'columns' => [ - 'columnnum', 'serial', '', '', '', '', - 'svcpart', 'int', '', '', '', '', - 'columnname', 'varchar', '', 64, '', '', + 'columnnum', 'serial', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'columnname', 'varchar', '', 64, '', '', 'columnlabel', 'varchar', 'NULL', $char_d, '', '', - 'columnvalue', 'varchar', 'NULL', $char_d, '', '', - 'columnflag', 'char', 'NULL', 1, '', '', + 'columnvalue', 'varchar', 'NULL', 512, '', '', + 'columnflag', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'columnnum', 'unique' => [ [ 'svcpart', 'columnname' ] ], @@ -1864,6 +1956,7 @@ sub tables_hashref { '_password_encoding', 'varchar', 'NULL', $char_d, '', '', 'sec_phrase', 'varchar', 'NULL', $char_d, '', '', 'popnum', 'int', 'NULL', '', '', '', + 'sectornum', 'int', 'NULL', '', '', '', 'uid', 'int', 'NULL', '', '', '', 'gid', 'int', 'NULL', '', '', '', 'finger', 'varchar', 'NULL', 2*$char_d, '', '', @@ -1911,6 +2004,7 @@ sub tables_hashref { 'cgp_sendmdnmode', 'varchar', 'NULL', $char_d, '', '',#SendMDNMode #mail #XXX RPOP settings + # ], 'primary_key' => 'svcnum', #'unique' => [ [ 'username', 'domsvc' ] ], @@ -1991,36 +2085,50 @@ sub tables_hashref { 'svc_dsl' => { 'columns' => [ - 'svcnum', 'int', '', '', '', '', - 'pushed', 'int', 'NULL', '', '', '', - 'desired_due_date', 'int', 'NULL', '', '', '', - 'due_date', 'int', 'NULL', '', '', '', - 'vendor_order_id', 'varchar', 'NULL', $char_d, '', '', - 'vendor_qual_id', 'varchar', 'NULL', $char_d, '', '', - 'vendor_order_type', 'varchar', 'NULL', $char_d, '', '', + 'svcnum', 'int', '', '', '', '', + 'pushed', 'int', 'NULL', '', '', '', + 'desired_due_date', 'int', 'NULL', '', '', '', + 'due_date', 'int', 'NULL', '', '', '', + 'vendor_order_id', 'varchar', 'NULL', $char_d, '', '', + 'vendor_qual_id', 'varchar', 'NULL', $char_d, '', '', + 'vendor_order_type', 'varchar', 'NULL', $char_d, '', '', 'vendor_order_status', 'varchar', 'NULL', $char_d, '', '', - 'first', 'varchar', 'NULL', $char_d, '', '', - 'last', 'varchar', 'NULL', $char_d, '', '', - 'company', 'varchar', 'NULL', $char_d, '', '', - 'phonenum', 'varchar', 'NULL', 24, '', '', - 'loop_type', 'char', 'NULL', 1, '', '', - 'local_voice_provider', 'varchar', 'NULL', $char_d, '', '', - 'circuitnum', 'varchar', 'NULL', $char_d, '', '', - 'rate_band', 'varchar', 'NULL', $char_d, '', '', - 'vpi', 'int', 'NULL', '', '', '', - 'vci', 'int', 'NULL', '', '', '', - 'isp_chg', 'char', 'NULL', 1, '', '', - 'isp_prev', 'varchar', 'NULL', $char_d, '', '', - 'username', 'varchar', 'NULL', $char_d, '', '', - 'password', 'varchar', 'NULL', $char_d, '', '', - 'staticips', 'text', 'NULL', '', '', '', - 'monitored', 'char', 'NULL', 1, '', '', - 'last_pull', 'int', 'NULL', '', '', '', + 'first', 'varchar', 'NULL', $char_d, '', '', + 'last', 'varchar', 'NULL', $char_d, '', '', + 'company', 'varchar', 'NULL', $char_d, '', '', + 'phonenum', 'varchar', 'NULL', 24, '', '', + 'gateway_access_number', 'varchar', 'NULL', 24, '', '', + 'loop_type', 'char', 'NULL', 1, '', '', + 'local_voice_provider', 'varchar', 'NULL', $char_d, '', '', + 'circuitnum', 'varchar', 'NULL', $char_d, '', '', + 'rate_band', 'varchar', 'NULL', $char_d, '', '', + 'vpi', 'int', 'NULL', '', '', '', + 'vci', 'int', 'NULL', '', '', '', + 'isp_chg', 'char', 'NULL', 1, '', '', + 'isp_prev', 'varchar', 'NULL', $char_d, '', '', + 'username', 'varchar', 'NULL', $char_d, '', '', + 'password', 'varchar', 'NULL', $char_d, '', '', + 'staticips', 'text', 'NULL', '', '', '', + 'monitored', 'char', 'NULL', 1, '', '', + 'last_pull', 'int', 'NULL', '', '', '', ], 'primary_key' => 'svcnum', 'unique' => [ ], 'index' => [ ['phonenum'], ['vendor_order_id'] ], }, + + 'dsl_device' => { + 'columns' => [ + 'devicenum', 'serial', '', '', '', '', + #part_device? or our own part_dsl_device? + #'devicepart', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'mac_addr', 'varchar', '', 12, '', '', + ], + 'primary_key' => 'devicenum', + 'unique' => [ [ 'mac_addr' ], ], + 'index' => [ [ 'svcnum' ], ], # [ 'devicepart' ] ], + }, 'dsl_note' => { 'columns' => [ @@ -2076,12 +2184,13 @@ sub tables_hashref { 'hardware_type' => { 'columns' => [ - 'typenum', 'serial', '', '', '', '', - 'classnum', 'int', '', '', '', '', - 'model', 'varchar', '', $char_d, '', '', + 'typenum', 'serial', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'model', 'varchar', '', $char_d, '', '', + 'revision','varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'typenum', - 'unique' => [ ], + 'unique' => [ [ 'classnum', 'model', 'revision' ] ], 'index' => [ ], }, @@ -2227,33 +2336,36 @@ sub tables_hashref { 'nas' => { 'columns' => [ - 'nasnum', 'serial', '', '', '', '', - 'nas', 'varchar', '', $char_d, '', '', - 'nasip', 'varchar', '', 15, '', '', - 'nasfqdn', 'varchar', '', $char_d, '', '', - 'last', 'int', '', '', '', '', + 'nasnum', 'serial', '', '', '', '', + 'nasname', 'varchar', '', 128, '', '', + 'shortname', 'varchar', 'NULL', 32, '', '', + 'type', 'varchar', '', 30, 'other', '', + 'ports', 'int', 'NULL', '', '', '', + 'secret', 'varchar', '', 60, 'secret', '', + 'server', 'varchar', 'NULL', 64, '', '', + 'community', 'varchar', 'NULL', 50, '', '', + 'description', 'varchar', '', 200, 'RADIUS Client', '', + 'svcnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'nasnum', - 'unique' => [ [ 'nas' ], [ 'nasip' ] ], - 'index' => [ [ 'last' ] ], + 'unique' => [ [ 'nasname' ], ], + 'index' => [], }, -# 'session' => { -# 'columns' => [ -# 'sessionnum', 'serial', '', '', '', '', -# 'portnum', 'int', '', '', '', '', -# 'svcnum', 'int', '', '', '', '', -# 'login', @date_type, '', '', -# 'logout', @date_type, '', '', -# ], -# 'primary_key' => 'sessionnum', -# 'unique' => [], -# 'index' => [ [ 'portnum' ] ], -# }, + 'export_nas' => { + 'columns' => [ + 'exportnasnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'nasnum', 'int', '', '', '', '', + ], + 'primary_key' => 'exportnasnum', + 'unique' => [ [ 'exportnum', 'nasnum' ] ], + 'index' => [ [ 'exportnum' ], [ 'nasnum' ] ], + }, 'queue' => { 'columns' => [ - 'jobnum', 'serial', '', '', '', '', + 'jobnum', 'bigserial', '', '', '', '', 'job', 'varchar', '', 512, '', '', '_date', 'int', '', '', '', '', 'status', 'varchar', '', $char_d, '', '', @@ -2272,10 +2384,10 @@ sub tables_hashref { 'queue_arg' => { 'columns' => [ - 'argnum', 'serial', '', '', '', '', - 'jobnum', 'int', '', '', '', '', - 'frozen', 'char', 'NULL', 1, '', '', - 'arg', 'text', 'NULL', '', '', '', + 'argnum', 'bigserial', '', '', '', '', + 'jobnum', 'bigint', '', '', '', '', + 'frozen', 'char', 'NULL', 1, '', '', + 'arg', 'text', 'NULL', '', '', '', ], 'primary_key' => 'argnum', 'unique' => [], @@ -2284,9 +2396,9 @@ sub tables_hashref { 'queue_depend' => { 'columns' => [ - 'dependnum', 'serial', '', '', '', '', - 'jobnum', 'int', '', '', '', '', - 'depend_jobnum', 'int', '', '', '', '', + 'dependnum', 'bigserial', '', '', '', '', + 'jobnum', 'bigint', '', '', '', '', + 'depend_jobnum', 'bigint', '', '', '', '', ], 'primary_key' => 'dependnum', 'unique' => [], @@ -2357,12 +2469,29 @@ sub tables_hashref { 'groupnum', 'serial', '', '', '', '', 'groupname', 'varchar', '', $char_d, '', '', 'description', 'varchar', 'NULL', $char_d, '', '', + 'priority', 'int', '', '', '1', '', + 'speed_up', 'int', 'NULL', '', '', '', + 'speed_down', 'int', 'NULL', '', '', '', ], 'primary_key' => 'groupnum', 'unique' => [ ['groupname'] ], 'index' => [], }, + 'radius_attr' => { + 'columns' => [ + 'attrnum', 'serial', '', '', '', '', + 'groupnum', 'int', '', '', '', '', + 'attrname', 'varchar', '', $char_d, '', '', + 'value', 'varchar', '', 255, '', '', + 'attrtype', 'char', '', 1, '', '', + 'op', 'char', '', 2, '', '', + ], + 'primary_key' => 'attrnum', + 'unique' => [], + 'index' => [ ['groupnum'], ], + }, + 'msgcat' => { 'columns' => [ 'msgnum', 'serial', '', '', '', '', @@ -2415,6 +2544,7 @@ sub tables_hashref { 'routername', 'varchar', '', $char_d, '', '', 'svcnum', 'int', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', + 'manual_addr', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'routernum', 'unique' => [], @@ -2448,26 +2578,56 @@ sub tables_hashref { 'svc_broadband' => { 'columns' => [ - 'svcnum', 'int', '', '', '', '', - 'description', 'varchar', 'NULL', $char_d, '', '', - 'blocknum', 'int', 'NULL', '', '', '', - 'speed_up', 'int', '', '', '', '', - 'speed_down', 'int', '', '', '', '', - 'ip_addr', 'varchar', 'NULL', 15, '', '', - 'mac_addr', 'varchar', 'NULL', 12, '', '', - 'authkey', 'varchar', 'NULL', 32, '', '', - 'latitude', 'decimal', 'NULL', '', '', '', - 'longitude', 'decimal', 'NULL', '', '', '', - 'altitude', 'decimal', 'NULL', '', '', '', - 'vlan_profile', 'varchar', 'NULL', $char_d, '', '', + 'svcnum', 'int', '', '', '', '', + 'description', 'varchar', 'NULL', $char_d, '', '', + 'routernum', 'int', 'NULL', '', '', '', + 'blocknum', 'int', 'NULL', '', '', '', + 'sectornum', 'int', 'NULL', '', '', '', + 'speed_up', 'int', 'NULL', '', '', '', + 'speed_down', 'int', 'NULL', '', '', '', + 'ip_addr', 'varchar', 'NULL', 15, '', '', + 'mac_addr', 'varchar', 'NULL', 12, '', '', + 'authkey', 'varchar', 'NULL', 32, '', '', + 'latitude', 'decimal', 'NULL', '10,7', '', '', + 'longitude', 'decimal', 'NULL', '10,7', '', '', + 'altitude', 'decimal', 'NULL', '', '', '', + 'vlan_profile', 'varchar', 'NULL', $char_d, '', '', 'performance_profile', 'varchar', 'NULL', $char_d, '', '', - 'plan_id', 'varchar', 'NULL', $char_d, '', '', + 'plan_id', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [ [ 'mac_addr' ] ], + 'unique' => [ [ 'ip_addr' ], [ 'mac_addr' ] ], + 'index' => [], + }, + + 'tower' => { + 'columns' => [ + 'towernum', 'serial', '', '', '', '', + #'agentnum', 'int', 'NULL', '', '', '', + 'towername', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'latitude', 'decimal', 'NULL', '10,7', '', '', + 'longitude','decimal', 'NULL', '10,7', '', '', + 'altitude', 'decimal', 'NULL', '', '', '', + 'coord_auto', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'towernum', + 'unique' => [ [ 'towername' ] ], # , 'agentnum' ] ], 'index' => [], }, + 'tower_sector' => { + 'columns' => [ + 'sectornum', 'serial', '', '', '', '', + 'towernum', 'int', '', '', '', '', + 'sectorname', 'varchar', '', $char_d, '', '', + 'ip_addr', 'varchar', 'NULL', 15, '', '', + ], + 'primary_key' => 'sectornum', + 'unique' => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ], + 'index' => [ [ 'towernum' ] ], + }, + 'part_virtual_field' => { 'columns' => [ 'vfieldpart', 'serial', '', '', '', '', @@ -2584,23 +2744,25 @@ sub tables_hashref { 'rate_detail' => { 'columns' => [ - 'ratedetailnum', 'serial', '', '', '', '', - 'ratenum', 'int', '', '', '', '', - 'orig_regionnum', 'int', 'NULL', '', '', '', - 'dest_regionnum', 'int', '', '', '', '', - 'min_included', 'int', '', '', '', '', - 'conn_charge', 'decimal', '', '10,4', '0', '', - 'conn_sec', 'int', '', '', '0', '', - 'min_charge', 'decimal', '', '10,5', '', '', #@money_type, '', '', - 'sec_granularity', 'int', '', '', '', '', - 'ratetimenum', 'int', 'NULL', '', '', '', - 'classnum', 'int', 'NULL', '', '', '', - 'cdrtypenum', 'int', 'NULL', '', '', '', - 'region_group', 'char', 'NULL', 1, '', '', + 'ratedetailnum', 'serial', '', '', '', '', + 'ratenum', 'int', '', '', '', '', + 'orig_regionnum', 'int', 'NULL', '', '', '', + 'dest_regionnum', 'int', '', '', '', '', + 'min_included', 'int', '', '', '', '', + 'conn_charge', 'decimal', '', '10,4', '0.0000', '', + 'conn_sec', 'int', '', '', '0', '', + 'min_charge', 'decimal', '', '10,5', '', '', + 'sec_granularity', 'int', '', '', '', '', + 'ratetimenum', 'int', 'NULL', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'cdrtypenum', 'int', 'NULL', '', '', '', + 'region_group', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], - 'index' => [ [ 'ratenum', 'dest_regionnum' ] ], + 'index' => [ [ 'ratenum', 'dest_regionnum' ], + [ 'ratenum', 'ratetimenum' ] + ], }, 'rate_region' => { @@ -2651,6 +2813,30 @@ sub tables_hashref { 'index' => [], }, + #not really part of the above rate_ stuff (used with flat rate rather than + # rated billing), but could be eventually, and its a rate + 'rate_tier' => { + 'columns' => [ + 'tiernum', 'serial', '', '', '', '', + 'tiername', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'tiernum', + 'unique' => [ [ 'tiername'], ], + 'index' => [], + }, + + 'rate_tier_detail' => { + 'columns' => [ + 'tierdetailnum', 'serial', '', '', '', '', + 'tiernum', 'int', '', '', '', '', + 'min_quan', 'int', '', '', '', '', + 'min_charge', 'decimal', '', '10,4', '', '', + ], + 'primary_key' => 'tierdetailnum', + 'unique' => [], + 'index' => [ ['tiernum'], ], + }, + 'usage_class' => { 'columns' => [ 'classnum', 'serial', '', '', '', '', @@ -2838,24 +3024,16 @@ sub tables_hashref { 'max_callers', 'int', 'NULL', '', '', '', ### - # fields for unitel/RSLCOM/convergent that don't map well to asterisk - # defaults - # though these are now used elsewhere: + # old fields for unitel/RSLCOM/convergent that don't map to asterisk + # ones we adoped moved to "own fields" section below # charged_party, upstream_price, rated_price, carrierid, cdrtypenum ### - #cdr_type: Usage = 1, S&E = 7, OC&C = 8 - 'cdrtypenum', 'int', 'NULL', '', '', '', - - 'charged_party', 'varchar', 'NULL', $char_d, '', '', - 'upstream_currency', 'char', 'NULL', 3, '', '', - 'upstream_price', 'decimal', 'NULL', '10,4', '', '', 'upstream_rateplanid', 'int', 'NULL', '', '', '', #? # how it was rated internally... 'ratedetailnum', 'int', 'NULL', '', '', '', - 'rated_price', 'decimal', 'NULL', '10,4', '', '', 'distance', 'decimal', 'NULL', '', '', '', 'islocal', 'int', 'NULL', '', '', '', # '', '', 0, '' instead? @@ -2866,16 +3044,28 @@ sub tables_hashref { 'description', 'varchar', 'NULL', $char_d, '', '', 'quantity', 'int', 'NULL', '', '', '', - #cdr_carrier: Telstra =1, Optus = 2, RSL COM = 3 - 'carrierid', 'int', 'NULL', '', '', '', - 'upstream_rateid', 'int', 'NULL', '', '', '', ### #and now for our own fields ### - # a svcnum... right..? + 'cdrtypenum', 'int', 'NULL', '', '', '', + + 'charged_party', 'varchar', 'NULL', $char_d, '', '', + + 'upstream_price', 'decimal', 'NULL', '10,4', '', '', + 'upstream_src_regionname', 'varchar', 'NULL', $char_d, '', '', + 'upstream_dst_regionname', 'varchar', 'NULL', $char_d, '', '', + + # how it was rated internally... + 'rated_price', 'decimal', 'NULL', '10,4', '', '', + 'rated_seconds', 'int', 'NULL', '', '', '', + 'rated_minutes', 'double precision', 'NULL', '', '', '', + + 'carrierid', 'int', 'NULL', '', '', '', + + # service it was matched to 'svcnum', 'int', 'NULL', '', '', '', #NULL, done (or something) @@ -2926,6 +3116,8 @@ sub tables_hashref { 'acctid', 'bigint', '', '', '', '', 'termpart', 'int', '', '', '', '',#future use see below 'rated_price', 'decimal', 'NULL', '10,4', '', '', + 'rated_seconds', 'int', 'NULL', '', '', '', + 'rated_minutes', 'double precision', 'NULL', '', '', '', 'status', 'varchar', 'NULL', 32, '', '', 'svcnum', 'int', 'NULL', '', '', '', ], @@ -2994,11 +3186,12 @@ sub tables_hashref { 'inventory_item' => { 'columns' => [ - 'itemnum', 'serial', '', '', '', '', - 'classnum', 'int', '', '', '', '', - 'agentnum', 'int', 'NULL', '', '', '', - 'item', 'varchar', '', $char_d, '', '', - 'svcnum', 'int', 'NULL', '', '', '', + 'itemnum', 'serial', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'item', 'varchar', '', $char_d, '', '', + 'svcnum', 'int', 'NULL', '', '', '', + 'svc_field', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'itemnum', 'unique' => [ [ 'classnum', 'item' ] ], @@ -3091,7 +3284,7 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'countrycode', 'varchar', '', 3, '', '', - 'phonenum', 'varchar', '', 15, '', '', #12 ? + 'phonenum', 'varchar', '', 25, '', '', #12 ? 'pin', 'varchar', 'NULL', $char_d, '', '', 'sip_password', 'varchar', 'NULL', $char_d, '', '', 'phone_name', 'varchar', 'NULL', $char_d, '', '', @@ -3258,6 +3451,8 @@ sub tables_hashref { 'reason_type', 'int', '', '', '', '', 'reason', 'text', '', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'unsuspend_pkgpart', 'int', 'NULL', '', '', '', + 'unsuspend_hold','char', 'NULL', 1, '', '', ], 'primary_key' => 'reasonnum', 'unique' => [], @@ -3467,6 +3662,81 @@ sub tables_hashref { 'index' => [], }, + 'upgrade_journal' => { + 'columns' => [ + 'upgradenum', 'serial', '', '', '', '', + '_date', 'int', '', '', '', '', + 'upgrade', 'varchar', '', $char_d, '', '', + 'status', 'varchar', '', $char_d, '', '', + 'statustext', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'upgradenum', + 'unique' => [], + 'index' => [ [ 'upgrade' ] ], + }, + + 'log' => { + 'columns' => [ + 'lognum', 'serial', '', '', '', '', + '_date', 'int', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'tablename', 'varchar', 'NULL', $char_d, '', '', + 'tablenum', 'int', 'NULL', '', '', '', + 'level', 'int', '', '', '', '', + 'message', 'text', '', '', '', '', + ], + 'primary_key' => 'lognum', + 'unique' => [], + 'index' => [ ['_date'], ['level'] ], + }, + + 'log_context' => { + 'columns' => [ + 'logcontextnum', 'serial', '', '', '', '', + 'lognum', 'int', '', '', '', '', + 'context', 'varchar', '', 32, '', '', + ], + 'primary_key' => 'logcontextnum', + 'unique' => [ [ 'lognum', 'context' ] ], + 'index' => [], + }, + + 'svc_cable' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'providernum', 'int', 'NULL', '', '', '', + 'ordernum', 'varchar', 'NULL', $char_d, '', '', + 'modelnum', 'int', 'NULL', '', '', '', + 'serialnum', 'varchar', 'NULL', $char_d, '', '', + 'mac_addr', 'varchar', 'NULL', 12, '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [], + 'index' => [], + }, + + 'cable_model' => { + 'columns' => [ + 'modelnum', 'serial', '', '', '', '', + 'model_name', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'modelnum', + 'unique' => [ [ 'model_name' ], ], + 'index' => [], + }, + + 'cable_provider' => { + 'columns' => [ + 'providernum', 'serial', '', '', '', '', + 'provider', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'providernum', + 'unique' => [ [ 'provider' ], ], + 'index' => [], + }, + %{ tables_hashref_torrus() }, # tables of ours for doing torrus virtual port combining