X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=ecfb0e4d270264b2e8166d43b7d6b84ee3b9ee92;hp=ea270a5eb84faf945ae00ab17cd977031e24ac42;hb=e3a2ca0a7ef6e0931283136321ba8c1494b37c19;hpb=38532144b08af1ae9e577c32ad1924e7c891b8c6 diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index ea270a5eb..ecfb0e4d2 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -184,11 +184,15 @@ 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"; @@ -325,6 +329,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 @@ -1329,11 +1428,12 @@ sub tables_hashref { 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', + 'agent_pkgid', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgnum', 'unique' => [], 'index' => [ ['custnum'], ['pkgpart'], [ 'pkgbatch' ], [ 'locationnum' ], - [ 'usernum' ], + [ 'usernum' ], [ 'agent_pkgid' ], [ 'start_date' ], ['setup'], ['last_bill'], ['bill'], ['susp'], ['adjourn'], ['expire'], ['cancel'], ['change_date'], ['order_date'], @@ -1819,6 +1919,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', '', '', '', @@ -1870,6 +1972,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, '', '', @@ -2250,6 +2354,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' ] ], @@ -2385,6 +2490,7 @@ sub tables_hashref { 'sec_granularity', 'int', '', '', '', '', 'ratetimenum', 'int', 'NULL', '', '', '', 'classnum', 'int', 'NULL', '', '', '', + 'cdrtypenum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], @@ -2874,6 +2980,8 @@ 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, '', '', @@ -2906,7 +3014,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' ] ], #? @@ -2923,6 +3031,8 @@ sub tables_hashref { 'nxx', 'char', 'NULL', 3, '', '', 'station', 'char', 'NULL', 4, '', '', 'name', 'varchar', 'NULL', $char_d, '', '', + 'rate_center_abbrev', 'varchar', 'NULL', $char_d, '', '', + 'ordernum', 'int', 'NULL', '', '', '', 'svcnum', 'int', 'NULL', '', '', '', 'availbatch', 'varchar', 'NULL', $char_d, '', '', ], @@ -2936,6 +3046,45 @@ sub tables_hashref { [ 'availbatch' ], ], }, + + 'lata' => { + 'columns' => [ + 'latanum', 'int', '', '', '', '', + 'description', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'latanum', + 'unique' => [], + 'index' => [], + }, + + 'did_vendor' => { + 'columns' => [ + 'vendornum', 'serial', '', '', '', '', + 'vendorname', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'vendornum', + 'unique' => [], + 'index' => [], + }, + + 'did_order' => { + 'columns' => [ + 'ordernum', 'serial', '', '', '', '', + 'vendornum', 'int', '', '', '', '', + 'vendor_order_id', 'varchar', '', $char_d, '', '', + 'msa', 'varchar', 'NULL', $char_d, '', '', + 'latanum', 'int', 'NULL', '', '', '', + 'rate_center', 'varchar', 'NULL', $char_d, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'quantity', 'int', '', '', '', '', + 'submitted', 'int', '', '', '', '', + 'confirmed', 'int', 'NULL', '', '', '', + 'received', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'ordernum', + 'unique' => [ [ 'vendornum', 'vendor_order_id' ] ], + 'index' => [], + }, 'reason_type' => { 'columns' => [ @@ -3107,40 +3256,51 @@ 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' => [ + 'code', 'char', '', 3, '', '', + 'country', 'char', 'NULL', 2, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'description','varchar', 'NULL', 255, '', '', + ], + 'primary_key' => 'code', 'unique' => [], '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', ], ], },