X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=6fde8b1e5af0baa9cbd4df0eb8fe86d829de6a4f;hp=b48e5af8a0ac5b363b88db3242a9f32d225d26d2;hb=982ded2d929bdcdfa72efa810273f3bc753bf036;hpb=1318909d7eb16d736cbfb9f641eecccb00d4636c diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index b48e5af8a..6fde8b1e5 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -117,6 +117,7 @@ sub dbdef_dist { #false laziness w/sub indices in DBIx::DBSchema::DBD (well, sorta) #and sub sql_create_table in DBIx::DBSchema::Table (slighty more?) my $unique = $tables_hashref->{$tablename}{'unique'}; + warn "missing index for $tablename\n" unless defined $tables_hashref->{$tablename}{'index'}; my @index = @{ $tables_hashref->{$tablename}{'index'} }; # kludge to avoid avoid "BLOB/TEXT column 'statustext' used in key @@ -184,43 +185,55 @@ sub dbdef_dist { # )); #} + my $tables_hashref_torrus = tables_hashref_torrus(); + #create history tables (false laziness w/create-history-tables) foreach my $table ( - grep { ! /^clientapi_session/ } - grep { ! /^h_/ } - $dbdef->tables + grep { ! /^clientapi_session/ + && ! /^h_/ + && ! $tables_hashref_torrus->{$_} + } + $dbdef->tables ) { my $tableobj = $dbdef->table($table) or die "unknown table $table"; - my %indices = $tableobj->indices; + my %h_indices = (); + + unless ( $table eq 'cust_event' ) { #others? + + my %indices = $tableobj->indices; - my %h_indices = map { - ( "h_$_" => - DBIx::DBSchema::Index->new({ - 'name' => 'h_'. $indices{$_}->name, - 'unique' => 0, - 'columns' => [ @{$indices{$_}->columns} ], - }) - ); - } - keys %indices; - - $h_indices{"h_${table}_srckey"} = DBIx::DBSchema::Index->new({ - 'name' => "h_${table}_srckey", - 'unique' => 0, - 'columns' => [ 'history_action', #right? - $tableobj->primary_key, - ], - }); - - $h_indices{"h_${table}_srckey2"} = DBIx::DBSchema::Index->new({ - 'name' => "h_${table}_srckey2", - 'unique' => 0, - 'columns' => [ 'history_date', - $tableobj->primary_key, - ], - }); + %h_indices = map { + ( "h_$_" => + DBIx::DBSchema::Index->new({ + 'name' => 'h_'. $indices{$_}->name, + 'unique' => 0, + 'columns' => [ @{$indices{$_}->columns} ], + }) + ); + } + keys %indices; + + $h_indices{"h_${table}_srckey"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_srckey", + 'unique' => 0, + 'columns' => [ 'history_action', #right? + $tableobj->primary_key, + ], + }); + + $h_indices{"h_${table}_srckey2"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_srckey2", + 'unique' => 0, + 'columns' => [ 'history_date', + $tableobj->primary_key, + ], + }); + + } my $h_tableobj = DBIx::DBSchema::Table->new( { 'name' => "h_$table", @@ -325,6 +338,101 @@ sub dbdef_dist { } +#torrus tables http://torrus.org/reporting_setup.pod.html#create_sql_tables +sub tables_hashref_torrus { + + return { + + # Collector export table. It usually grows at several megabytes + # per month, and is updated every 5 minutes + 'srvexport' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'srv_date', 'date', '', '', '', '',#date and time of the data sample + 'srv_time', 'time', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '',#unique service ID per counter + 'value', 'double precision', '', '', '', '',#collected rate or gauge value + 'intvl', 'int', '', '', '', '', # collection interval - for counter volume calculation + ], + 'primary_key' => 'id', + 'unique' => [], + 'index' => [ ['srv_date'], ['srv_date', 'srv_time'], ['serviceid'], ], + }, + + #Tables for (currently monthly only) report contents. + #These are updated usually once per month, and read at the moment of + #rendering the report output (HTML now, PDF or XML or Excel or whatever + #in the future) + + #DBIx::Sequence backend, theplatform-independent inplementation + #of sequences + 'dbix_sequence_state' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'dataset', 'varchar', '', 50, '', '', + 'state_id', 'int', '', '', '', '', + ], + 'primary_key' => 'id', + #CONSTRAINT pk_dbix_sequence PRIMARY KEY (dataset, state_id) + 'unique' => [ [ 'dataset', 'state_id' ], ], + 'index' => [], + }, + + 'dbix_sequence_release' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'dataset', 'varchar', '', 50, '', '', + 'released_id', 'int', '', '', '', '', + ], + 'primary_key' => 'id', + #CONSTRAINT pk_dbi_release PRIMARY KEY (dataset, released_id) + 'unique' => [ [ 'dataset', 'released_id', ] ], + 'index' => [], + }, + + #Each report is characterized by name, date and time. + #Monthly reports are automatically assigned 00:00 of the 1st day + #in the month. The report contains fields for every service ID + #defined across all datasource trees. + 'reports' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'rep_date', 'date', '', '', '', '',#Start date of the report + 'rep_time', 'time', '', '', '', '',#Start time of the report + 'reportname', 'varchar', '', 64, '', '',#Report name, such as + # MonthlyUsage + 'iscomplete', 'int', '', '', '', '',#0 when the report is in + # progress, 1 when it is ready + ], + 'primary_key' => 'id', + 'unique' => [ [ qw(rep_date rep_time reportname) ] ], + 'index' => [ [ 'rep_date' ] ], + }, + + #Each report contains fields. For each service ID, + #the report may contain several fields for various statistics. + #Each field contains information about the units of the value it + #contains + 'reportfields' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'rep_id', 'int', 'NULL', '', '', '', + 'name', 'varchar', '', 64, '', '',#name of the field, + # such as AVG or MAX + 'serviceid', 'varchar', '', 64, '', '',#service ID + 'value', 'double precision', '', '', '', '',#Numeric value + 'units', 'varchar', '', 64, \"''", '',#Units, such as bytes + # or Mbps + ], + 'primary_key', => 'id', + 'unique' => [ [ qw(rep_id name serviceid) ] ], + 'index' => [], + }, + + }; + +} + sub tables_hashref { my $char_d = 80; #default maxlength for text fields @@ -592,6 +700,7 @@ sub tables_hashref { 'classnum', 'int', 'NULL', '', '', '', 'duration', 'int', 'NULL', '', 0, '', 'phonenum', 'varchar', 'NULL', 15, '', '', + 'accountcode', 'varchar', 'NULL', 20, '', '', 'regionname', 'varchar', 'NULL', $char_d, '', '', 'detail', 'varchar', '', 255, '', '', ], @@ -771,6 +880,9 @@ sub tables_hashref { 'credit_limit', @money_typen, '', '', 'archived', 'char', 'NULL', 1, '', '', 'email_csv_cdr', 'char', 'NULL', 1, '', '', + 'accountcode_cdr', 'char', 'NULL', 1, '', '', + 'billday', 'int', 'NULL', '', '', '', + 'edit_subject', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -883,32 +995,35 @@ sub tables_hashref { 'columns' => [ 'prospectnum', 'serial', '', '', '', '', 'agentnum', 'int', '', '', '', '', - 'company', 'varchar', '', $char_d, '', '', - #'disabled', 'char', 'NULL', 1, '', '', + 'company', 'varchar', 'NULL', $char_d, '', '', + 'add_date', @date_type, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'custnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'prospectnum', 'unique' => [], - 'index' => [ [ 'company' ], [ 'agentnum' ], ], + 'index' => [ [ 'company' ], [ 'agentnum' ], [ 'disabled' ] ], }, #eventually use for billing & ship from cust_main too #for now, just cust_pkg locations 'cust_location' => { #'location' now that its prospects too, but... 'columns' => [ - 'locationnum', 'serial', '', '', '', '', - 'prospectnum', 'int', 'NULL', '', '', '', - 'custnum', 'int', 'NULL', '', '', '', - 'address1', 'varchar', '', $char_d, '', '', - 'address2', 'varchar', 'NULL', $char_d, '', '', - 'city', 'varchar', '', $char_d, '', '', - 'county', 'varchar', 'NULL', $char_d, '', '', - 'state', 'varchar', 'NULL', $char_d, '', '', - 'zip', 'varchar', 'NULL', 10, '', '', - 'country', 'char', '', 2, '', '', - 'geocode', 'varchar', 'NULL', 20, '', '', - 'location_type', 'varchar', 'NULL', 20, '', '', - 'location_number', 'varchar', 'NULL', 20, '', '', - 'location_kind', 'char', 'NULL', 1, '', '', + 'locationnum', 'serial', '', '', '', '', + 'prospectnum', 'int', 'NULL', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', + 'address1', 'varchar', '', $char_d, '', '', + 'address2', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', '', $char_d, '', '', + 'county', 'varchar', 'NULL', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', + 'zip', 'varchar', 'NULL', 10, '', '', + 'country', 'char', '', 2, '', '', + 'geocode', 'varchar', 'NULL', 20, '', '', + 'location_type', 'varchar', 'NULL', 20, '', '', + 'location_number', 'varchar', 'NULL', 20, '', '', + 'location_kind', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'locationnum', 'unique' => [], @@ -1302,6 +1417,16 @@ sub tables_hashref { 'index' => [ ['batchnum'], ['invnum'], ['custnum'] ], }, + 'fcc477map' => { + 'columns' => [ + 'formkey', 'varchar', '', 255, '', '', + 'formvalue', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'formkey', + 'unique' => [], + 'index' => [], + }, + 'cust_pkg' => { 'columns' => [ 'pkgnum', 'serial', '', '', '', '', @@ -1311,6 +1436,7 @@ sub tables_hashref { 'locationnum', 'int', 'NULL', '', '', '', 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', + 'order_date', @date_type, '', '', 'start_date', @date_type, '', '', 'setup', @date_type, '', '', 'bill', @date_type, '', '', @@ -1320,6 +1446,7 @@ sub tables_hashref { 'cancel', @date_type, '', '', 'expire', @date_type, '', '', 'contract_end', @date_type, '', '', + 'dundate', @date_type, '', '', 'change_date', @date_type, '', '', 'change_pkgnum', 'int', 'NULL', '', '', '', 'change_pkgpart', 'int', 'NULL', '', '', '', @@ -1327,14 +1454,18 @@ sub tables_hashref { 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', + 'agent_pkgid', 'int', 'NULL', '', '', '', + 'waive_setup', 'char', 'NULL', 1, '', '', + 'recur_show_zero', 'char', 'NULL', 1, '', '', + 'setup_show_zero', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgnum', 'unique' => [], 'index' => [ ['custnum'], ['pkgpart'], [ 'pkgbatch' ], [ 'locationnum' ], - [ 'usernum' ], - [ 'start_date' ], ['setup'], ['last_bill'], ['bill'], - ['susp'], ['adjourn'], ['expire'], ['cancel'], - ['change_date'], + [ 'usernum' ], [ 'agent_pkgid' ], + ['order_date'], [ 'start_date' ], ['setup'], ['bill'], + ['last_bill'], ['susp'], ['adjourn'], ['cancel'], + ['expire'], ['contract_end'], ['change_date'], ['no_auto'], ], }, @@ -1416,6 +1547,7 @@ sub tables_hashref { 'percent', 'decimal', '', '', '', '', 'months', 'decimal', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'setup', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'discountnum', 'unique' => [], @@ -1482,7 +1614,7 @@ sub tables_hashref { 'index' => [ [ 'svcnum' ], [ 'optionname' ] ], }, - 'part_pkg' => { + 'part_pkg' => { 'columns' => [ 'pkgpart', 'serial', '', '', '', '', 'pkg', 'varchar', '', $char_d, '', '', @@ -1507,12 +1639,14 @@ sub tables_hashref { 'credit_weight', 'real', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', 'fcc_ds0s', 'int', 'NULL', '', '', '', - 'no_auto', 'char', 'NULL', 1, '', '', + 'no_auto', 'char', 'NULL', 1, '', '', + 'recur_show_zero', 'char', 'NULL', 1, '', '', + 'setup_show_zero', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgpart', 'unique' => [], 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'classnum' ], - [ 'agentnum' ], + [ 'agentnum' ], ['no_auto'], ], }, @@ -1641,6 +1775,7 @@ sub tables_hashref { 'svc', 'varchar', '', $char_d, '', '', 'svcdb', 'varchar', '', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'preserve', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'svcpart', 'unique' => [], @@ -1817,6 +1952,8 @@ sub tables_hashref { 'setup_date', @date_type, '', '', 'renewal_interval', 'int', 'NULL', '', '', '', 'expiration_date', @date_type, '', '', + 'au_registrant_name', 'varchar', 'NULL', $char_d, '', '', + 'au_eligibility_type', 'varchar', 'NULL', $char_d, '', '', #communigate pro fields (quota = MaxAccountSize) 'max_accounts', 'int', 'NULL', '', '', '', 'trailer', 'text', 'NULL', '', '', '', @@ -1868,6 +2005,8 @@ sub tables_hashref { '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, '', '', @@ -1895,6 +2034,66 @@ sub tables_hashref { 'index' => [ ['svcnum'] ], }, + 'svc_dish' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'acctnum', 'varchar', '', 16, '', '', + 'installdate', @date_type, '', '', + 'note', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [ ], + 'index' => [ ], + }, + + 'svc_hardware' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'typenum', 'int', '', '', '', '', + 'serial', 'varchar', 'NULL', $char_d, '', '', + 'ip_addr', 'varchar', 'NULL', 40, '', '', + 'hw_addr', 'varchar', 'NULL', 12, '', '', + 'smartcard','varchar', 'NULL', 30, '', '', + 'statusnum','int', 'NULL', '', '', '', + 'note', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [ ], + 'index' => [ ], + }, + + 'hardware_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [ ], + 'index' => [ ], + }, + + 'hardware_type' => { + 'columns' => [ + 'typenum', 'serial', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'model', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'typenum', + 'unique' => [ ], + 'index' => [ ], + }, + + 'hardware_status' => { + 'columns' => [ + 'statusnum', 'serial', '', '', '', '', + 'label' ,'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'statusnum', + 'unique' => [ ], + 'index' => [ ], + }, + 'domain_record' => { 'columns' => [ 'recnum', 'serial', '', '', '', '', @@ -2143,17 +2342,29 @@ sub tables_hashref { 'columns' => [ 'usergroupnum', 'serial', '', '', '', '', 'svcnum', 'int', '', '', '', '', - 'groupname', 'varchar', '', $char_d, '', '', + 'groupname', 'varchar', 'NULL', $char_d, '', '', + 'groupnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'usergroupnum', 'unique' => [], 'index' => [ [ 'svcnum' ], [ 'groupname' ] ], }, + + 'radius_group' => { + 'columns' => [ + 'groupnum', 'serial', '', '', '', '', + 'groupname', 'varchar', '', $char_d, '', '', + 'description', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'groupnum', + 'unique' => [ ['groupname'] ], + 'index' => [], + }, 'msgcat' => { 'columns' => [ 'msgnum', 'serial', '', '', '', '', - 'msgcode', 'varchar', '', $char_d, '', '', + 'msgcode', 'varchar', '', 255, '', '', 'locale', 'varchar', '', 16, '', '', 'msg', 'text', '', '', '', '', ], @@ -2248,6 +2459,7 @@ sub tables_hashref { 'altitude', 'decimal', 'NULL', '', '', '', 'vlan_profile', 'varchar', 'NULL', $char_d, '', '', 'performance_profile', 'varchar', 'NULL', $char_d, '', '', + 'plan_id', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [ [ 'mac_addr' ] ], @@ -2259,9 +2471,7 @@ sub tables_hashref { 'vfieldpart', 'serial', '', '', '', '', 'dbtable', 'varchar', '', 32, '', '', 'name', 'varchar', '', 32, '', '', - 'check_block', 'text', 'NULL', '', '', '', 'length', 'int', 'NULL', '', '', '', - 'list_source', 'text', 'NULL', '', '', '', 'label', 'varchar', 'NULL', 80, '', '', ], 'primary_key' => 'vfieldpart', @@ -2303,8 +2513,8 @@ sub tables_hashref { 'svc_external' => { 'columns' => [ - 'svcnum', 'int', '', '', '', '', - 'id', 'int', 'NULL', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'id', 'bigint', 'NULL', '', '', '', 'title', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', @@ -2383,6 +2593,8 @@ sub tables_hashref { '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' ] ], @@ -2406,6 +2618,9 @@ sub tables_hashref { 'countrycode', 'varchar', '', 3, '', '', 'npa', 'varchar', 'NULL', 10, '', '', #actually the whole prefix 'nxx', 'varchar', 'NULL', 3, '', '', #actually not used + 'latanum', 'int', 'NULL', '', '', '', + 'state', 'char', 'NULL', 2, '', '', + 'ocn', 'char', 'NULL', 4, '', '', ], 'primary_key' => 'prefixnum', 'unique' => [], @@ -2539,14 +2754,16 @@ sub tables_hashref { 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', '', 128, '', '', #say, a 512-big digest _hex encoded #'paymask', 'varchar', 'NULL', $char_d, '', '' - '_date', @date_type, '', '', - 'otaker', 'varchar', 'NULL', 32, '', '', - 'usernum', 'int', 'NULL', '', '', '', + '_date', @date_type, '', '', + 'end_date', @date_type, '', '', + 'otaker', 'varchar', 'NULL', 32, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'bantype', 'varchar', 'NULL', $char_d, '', '', 'reason', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'bannum', - 'unique' => [ [ 'payby', 'payinfo' ] ], - 'index' => [ [ 'usernum' ] ], + 'unique' => [], + 'index' => [ [ 'payby', 'payinfo' ], [ 'usernum' ], ], }, 'pkg_category' => { @@ -2568,6 +2785,7 @@ sub tables_hashref { 'classname', 'varchar', '', $char_d, '', '', 'categorynum', 'int', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'fcc_ds0s', 'int', 'NULL', '', '', '', ], 'primary_key' => 'classnum', 'unique' => [], @@ -2612,7 +2830,7 @@ sub tables_hashref { 'disposition', 'varchar', '', 45, \"''", '', 'amaflags', 'int', '', '', 0, '', 'accountcode', 'varchar', '', 20, \"''", '', - 'uniqueid', 'varchar', '', 32, \"''", '', + 'uniqueid', 'varchar', '', $char_d, \"''", '', 'userfield', 'varchar', '', 255, \"''", '', 'max_callers', 'int', 'NULL', '', '', '', @@ -2621,7 +2839,7 @@ sub tables_hashref { # fields for unitel/RSLCOM/convergent that don't map well to asterisk # defaults # though these are now used elsewhere: - # charged_party, upstream_price, rated_price, carrierid + # charged_party, upstream_price, rated_price, carrierid, cdrtypenum ### #cdr_type: Usage = 1, S&E = 7, OC&C = 8 @@ -2630,7 +2848,7 @@ sub tables_hashref { 'charged_party', 'varchar', 'NULL', $char_d, '', '', 'upstream_currency', 'char', 'NULL', 3, '', '', - 'upstream_price', 'decimal', 'NULL', '10,2', '', '', + 'upstream_price', 'decimal', 'NULL', '10,4', '', '', 'upstream_rateplanid', 'int', 'NULL', '', '', '', #? # how it was rated internally... @@ -2667,17 +2885,23 @@ sub tables_hashref { #an indexed place to put big numbers 'cdrid', 'bigint', 'NULL', '', '', '', + #for taqua accountcode rewriting, for starters + 'sessionnum', 'int', 'NULL', '', '', '', + 'subscriber', 'varchar', 'NULL', $char_d, '', '', + #old - 'cdrbatch', 'varchar', 'NULL', 255, '', '', + 'cdrbatch', 'varchar', 'NULL', 255, '', '', #new - 'cdrbatchnum', 'int', 'NULL', '', '', '', + 'cdrbatchnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'acctid', 'unique' => [], 'index' => [ [ 'calldate' ], [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ], - [ 'accountcode' ], [ 'carrierid' ], [ 'cdrid' ], + [ 'lastapp' ], + ['accountcode'], ['uniqueid'], ['carrierid'], ['cdrid'], + [ 'sessionnum' ], [ 'subscriber' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], [ 'cdrbatchnum' ], ], @@ -2872,6 +3096,16 @@ sub tables_hashref { 'pbxsvc', 'int', 'NULL', '', '', '', 'domsvc', 'int', 'NULL', '', '', '', 'locationnum', 'int', 'NULL', '', '', '', + 'forwarddst', 'varchar', 'NULL', 15, '', '', + 'email', 'varchar', 'NULL', 255, '', '', + 'lnp_status', 'varchar', 'NULL', $char_d, '', '', + 'portable', 'char', 'NULL', 1, '', '', + 'lrn', 'char', 'NULL', 10, '', '', + 'lnp_desired_due_date', 'int', 'NULL', '', '', '', + 'lnp_due_date', 'int', 'NULL', '', '', '', + 'lnp_other_provider', 'varchar', 'NULL', $char_d, '', '', + 'lnp_other_provider_account', 'varchar', 'NULL', $char_d, '', '', + 'lnp_reject_reason', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -2896,7 +3130,7 @@ sub tables_hashref { 'columns' => [ 'devicepart', 'serial', '', '', '', '', 'devicename', 'varchar', '', $char_d, '', '', - #'classnum', #tie to an inventory class? + 'inventory_classnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'devicepart', 'unique' => [ [ 'devicename' ] ], #? @@ -2913,6 +3147,10 @@ sub tables_hashref { 'nxx', 'char', 'NULL', 3, '', '', 'station', 'char', 'NULL', 4, '', '', 'name', 'varchar', 'NULL', $char_d, '', '', + 'rate_center_abbrev', 'varchar', 'NULL', $char_d, '', '', + 'latanum', 'int', 'NULL', '', '', '', + 'msanum', 'int', 'NULL', '', '', '', + 'ordernum', 'int', 'NULL', '', '', '', 'svcnum', 'int', 'NULL', '', '', '', 'availbatch', 'varchar', 'NULL', $char_d, '', '', ], @@ -2924,8 +3162,82 @@ sub tables_hashref { [ 'exportnum', 'countrycode', 'npa', 'nxx', 'station' ], # # [ 'svcnum' ], [ 'availbatch' ], + [ 'latanum' ], ], }, + + 'lata' => { + 'columns' => [ + 'latanum', 'int', '', '', '', '', + 'description', 'varchar', '', $char_d, '', '', + 'have_usage', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'latanum', + 'unique' => [], + 'index' => [], + }, + + 'msa' => { + 'columns' => [ + 'msanum', 'int', '', '', '', '', + 'description', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'msanum', + 'unique' => [], + 'index' => [], + }, + + 'rate_center' => { + 'columns' => [ + 'ratecenternum', 'serial', '', '', '', '', + 'description', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'ratecenternum', + 'unique' => [], + 'index' => [], + }, + + 'did_vendor' => { + 'columns' => [ + 'vendornum', 'serial', '', '', '', '', + 'vendorname', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'vendornum', + 'unique' => [], + 'index' => [], + }, + + 'did_order_item' => { + 'columns' => [ + 'orderitemnum', 'serial', '', '', '', '', + 'ordernum', 'int', '', '', '', '', + 'msanum', 'int', 'NULL', '', '', '', + 'npa', 'int', 'NULL', '', '', '', + 'latanum', 'int', 'NULL', '', '', '', + 'ratecenternum', 'int', 'NULL', '', '', '', + 'state', 'char', 'NULL', 2, '', '', + 'quantity', 'int', '', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'orderitemnum', + 'unique' => [], + 'index' => [], + }, + + 'did_order' => { + 'columns' => [ + 'ordernum', 'serial', '', '', '', '', + 'vendornum', 'int', '', '', '', '', + 'vendor_order_id', 'varchar', 'NULL', $char_d, '', '', + 'custnum', 'int', 'NULL', '', '', '', + 'submitted', 'int', '', '', '', '', + 'confirmed', 'int', 'NULL', '', '', '', + 'received', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'ordernum', + 'unique' => [ [ 'vendornum', 'vendor_order_id' ] ], + 'index' => [], + }, 'reason_type' => { 'columns' => [ @@ -3027,13 +3339,14 @@ sub tables_hashref { 'bill_batch' => { 'columns' => [ - 'batchnum', 'serial', '', '', '', '', - 'status', 'char', 'NULL','1', '', '', - 'pdf', 'blob', 'NULL', '', '', '', + 'batchnum', 'serial', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'status', 'char', 'NULL', '1', '', '', + 'pdf', 'blob', 'NULL', '', '', '', ], 'primary_key' => 'batchnum', 'unique' => [], - 'index' => [], + 'index' => [ ['agentnum'] ], }, 'cust_bill_batch' => { @@ -3076,6 +3389,24 @@ sub tables_hashref { 'index' => [ ['agentnum'], ] }, + 'cust_msg' => { + 'columns' => [ + 'custmsgnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'msgnum', 'int', 'NULL', '', '', '', + '_date', @date_type, '', '', + 'env_from', 'varchar', 'NULL', 255, '', '', + 'env_to', 'varchar', 'NULL', 255, '', '', + 'header', 'blob', 'NULL', '', '', '', + 'body', 'blob', 'NULL', '', '', '', + 'error', 'varchar', 'NULL', 255, '', '', + 'status', 'varchar', '',$char_d, '', '', + ], + 'primary_key' => 'custmsgnum', + 'unique' => [ ], + 'index' => [ ['custnum'], ], + }, + 'svc_cert' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -3097,40 +3428,52 @@ sub tables_hashref { 'index' => [], #recnum }, - 'nms_device' => { + 'svc_port' => { 'columns' => [ - 'nms_devicenum', 'serial', '', '', '', '', - #'agentnum', 'int', 'NULL', '', '', '', - 'devicename', 'varchar', '', $char_d, '', '', - 'ip', 'varchar', '', 15, '', '', - 'protocol', 'varchar', '', $char_d, '', '', -# 'last', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields ], - 'primary_key' => 'nms_devicenum', + 'primary_key' => 'svcnum', 'unique' => [], + 'index' => [], #recnum + }, + + 'areacode' => { + 'columns' => [ + 'areanum', 'serial', '', '', '', '', + 'code', 'char', '', 3, '', '', + 'country', 'char', 'NULL', 2, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'description','varchar', 'NULL', 255, '', '', + ], + 'primary_key' => 'areanum', + 'unique' => [ [ 'areanum' ] ], 'index' => [], }, - - 'nms_deviceport' => { + + %{ tables_hashref_torrus() }, + + # tables of ours for doing torrus virtual port combining + 'torrus_srvderive' => { 'columns' => [ - 'portnum', 'serial', '', '', '', '', - 'nms_devicenum', 'int', '', '', '', '', - 'deviceport', 'int', '', '', '', '', - #'ip', 'varchar', 'NULL', 15, '', '', - 'svcnum', 'int', 'NULL', '', '', '', + 'derivenum', 'serial', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields + 'last_srv_date', 'date', 'NULL', '', '', '', ], - 'primary_key' => 'portnum', - 'unique' => [ [ 'nms_devicenum', 'deviceport' ] ], - 'index' => [ [ 'svcnum' ] ], + 'primary_key' => 'derivenum', + 'unique' => [ ['serviceid'] ], + 'index' => [], }, - 'svc_port' => { + 'torrus_srvderive_component' => { 'columns' => [ - 'svcnum', 'int', '', '', '', '', + 'componentnum', 'serial', '', '', '', '', + 'derivenum', 'int', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields ], - 'primary_key' => 'svcnum', - 'unique' => [], - 'index' => [], #recnum + 'primary_key' => 'componentnum', + 'unique' => [ [ 'derivenum', 'serviceid' ], ], + 'index' => [ [ 'derivenum', ], ], },