opensips provisioning, RT10993

[freeside.git] / FS / FS / Schema.pm

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 253eb66..26252a7 100644 (file)
--- 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'], ['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
@@ -1312,6 +1411,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,             '', '', 
@@ -1335,7 +1435,7 @@ sub tables_hashref {
                    [ 'usernum' ],
                    [ 'start_date' ], ['setup'], ['last_bill'], ['bill'],
                    ['susp'], ['adjourn'], ['expire'], ['cancel'],
-                   ['change_date'],
+                   ['change_date'], ['order_date'],
                  ],
     },
 
@@ -2873,6 +2973,15 @@ sub tables_hashref {
         'pbxsvc',           'int', 'NULL',      '', '', '',
         'domsvc',           'int', 'NULL',      '', '', '', 
         'locationnum',      'int', 'NULL', '', '', '',
+        'route',   'varchar', 'NULL', $char_d, '', '',
+        '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' => [],
@@ -3134,6 +3243,8 @@ sub tables_hashref {
       'index'  => [], #recnum
     },
 
+    %{ tables_hashref_torrus() },
+
 
     # name type nullability length default local