X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=5d453ac271b12d219ff4614acf91ae1196d6e81e;hp=ca54d7b798a6b19c2ca0b6b044ba16a21acffd6b;hb=91fd4ea0e91ee9e6717e0b12e01eebb8f8b21322;hpb=3a201934245c2e72b167804a6d512e0e6d435f65

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index ca54d7b79..5d453ac27 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
@@ -352,8 +451,8 @@ sub tables_hashref {
         'invoice_template', 'varchar', 'NULL', $char_d, '', '',
         'agent_custnum',        'int', 'NULL',      '', '', '',
         'disabled',            'char', 'NULL',       1, '', '', 
-        'username',         'varchar', 'NULL', $char_d, '', '', #deprecated
-        '_password',        'varchar', 'NULL', $char_d, '', '', #deprecated
+        'username',         'varchar', 'NULL', $char_d, '', '',
+        '_password',        'varchar', 'NULL', $char_d, '', '',
         'freq',              'int', 'NULL', '', '', '', #deprecated (never used)
         'prog',                     @perl_type, '', '', #deprecated (never used)
       ],
@@ -570,6 +669,7 @@ sub tables_hashref {
         'itemdesc',         'varchar', 'NULL', $char_d, '', '', 
         'itemcomment',      'varchar', 'NULL', $char_d, '', '', 
         'section',          'varchar', 'NULL', $char_d, '', '', 
+        'freq',             'varchar', 'NULL', $char_d, '', '',
         'quantity',             'int', 'NULL',      '', '', '',
         'unitsetup',           @money_typen,            '', '', 
         'unitrecur',           @money_typen,            '', '', 
@@ -591,6 +691,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, '', '', 
       ],
@@ -767,8 +868,11 @@ sub tables_hashref {
         'squelch_cdr','char', 'NULL', 1, '', '', 
         'cdr_termination_percentage', 'decimal', 'NULL', '', '', '',
         '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', '', '', '',
       ],
       'primary_key' => 'custnum',
       'unique' => [ [ 'agentnum', 'agent_custid' ] ],
@@ -829,8 +933,8 @@ sub tables_hashref {
         'last',      'varchar',     '', $char_d, '', '', 
 #        'middle',    'varchar', 'NULL', $char_d, '', '', 
         'first',     'varchar',     '', $char_d, '', '', 
-        'title',     'varchar',     '', $char_d, '', '', #eg Head Bottle Washer
-        'comment',   'varchar',     '', $char_d, '', '', 
+        'title',     'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer
+        'comment',   'varchar', 'NULL', $char_d, '', '', 
         'disabled',     'char', 'NULL',       1, '', '', 
       ],
       'primary_key' => 'contactnum',
@@ -842,13 +946,13 @@ sub tables_hashref {
 
     'contact_phone' => {
       'columns' => [
-        'contactphonenum', 'serial', '', '', '', '',
-        'contactnum',         'int', '', '', '', '',
-        'phonetypenum',       'int', '', '', '', '',
-        'countrycode',    'varchar', '',  3, '', '', 
-        'phonenum',       'varchar', '', 14, '', '', 
-        'extension',      'varchar', '',  7, '', '',
-        #?#'comment',   'varchar',     '', $char_d, '', '', 
+        'contactphonenum', 'serial',     '', '', '', '',
+        'contactnum',         'int',     '', '', '', '',
+        'phonetypenum',       'int',     '', '', '', '',
+        'countrycode',    'varchar',     '',  3, '', '', 
+        'phonenum',       'varchar',     '', 14, '', '', 
+        'extension',      'varchar', 'NULL',  7, '', '',
+        #?#'comment',        'varchar',     '', $char_d, '', '', 
       ],
       'primary_key' => 'contactphonenum',
       'unique'      => [],
@@ -881,29 +985,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, '', '',
+        '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'      => [],
@@ -927,6 +1037,7 @@ sub tables_hashref {
       'columns' => [
         'notenum',  'serial',  '',     '', '', '', 
         'custnum',  'int',  '',     '', '', '', 
+        'classnum',      'int',     'NULL', '', '', '', 
         '_date',    @date_type, '', '', 
         'otaker',   'varchar', 'NULL',    32, '', '', 
         'usernum',   'int', 'NULL', '', '', '',
@@ -936,6 +1047,17 @@ sub tables_hashref {
       'unique' => [],
       'index' => [ [ 'custnum' ], [ '_date' ], [ 'usernum' ], ],
     },
+    
+    'cust_note_class' => {
+      'columns' => [
+        'classnum',    'serial',   '',      '', '', '', 
+        'classname',   'varchar',  '', $char_d, '', '', 
+        'disabled',    'char', 'NULL',       1, '', '', 
+      ],
+      'primary_key' => 'classnum',
+      'unique' => [],
+      'index' => [ ['disabled'] ],
+    },
 
     'cust_category' => {
       'columns' => [
@@ -960,6 +1082,30 @@ sub tables_hashref {
       'unique' => [],
       'index' => [ ['disabled'] ],
     },
+ 
+    'cust_tag' => {
+      'columns' => [
+        'custtagnum', 'serial', '', '', '', '',
+        'custnum',       'int', '', '', '', '',
+        'tagnum',        'int', '', '', '', '',
+      ],
+      'primary_key' => 'custtagnum',
+      'unique'      => [ [ 'custnum', 'tagnum' ] ],
+      'index'       => [ [ 'custnum' ] ],
+    },
+
+    'part_tag' => {
+      'columns' => [
+        'tagnum',    'serial',     '',      '', '', '',
+        'tagname',  'varchar',     '', $char_d, '', '',
+        'tagdesc',  'varchar', 'NULL', $char_d, '', '',
+        'tagcolor', 'varchar', 'NULL',       6, '', '',
+        'disabled',    'char', 'NULL',       1, '', '', 
+      ],
+      'primary_key' => 'tagnum',
+      'unique'      => [], #[ [ 'tagname' ] ], #?
+      'index'       => [ [ 'disabled' ] ],
+    },
 
     'cust_main_exemption' => {
       'columns' => [
@@ -1168,10 +1314,11 @@ sub tables_hashref {
         'reason',    'varchar',   'NULL', $char_d, '', '', 
         'otaker',   'varchar', 'NULL', 32, '', '', 
         'usernum',   'int', 'NULL', '', '', '',
+        'void_usernum',   'int', 'NULL', '', '', '',
       ],
       'primary_key' => 'paynum',
       'unique' => [],
-      'index' => [ [ 'custnum' ], [ 'usernum' ], ],
+      'index' => [ [ 'custnum' ], [ 'usernum' ], [ 'void_usernum' ] ],
     },
 
     'cust_bill_pay' => {
@@ -1260,14 +1407,26 @@ sub tables_hashref {
       'index' => [ ['batchnum'], ['invnum'], ['custnum'] ],
     },
 
+    'fcc477map' => {
+      'columns' => [
+        'formkey', 'varchar', '', '', '', '', 
+        'formvalue', 'text', 'NULL', '', '', '', 
+      ],
+      'primary_key' => 'formkey',
+      'unique'      => [],
+      'index'       => [],
+    },
+
     'cust_pkg' => {
       'columns' => [
         'pkgnum',           'serial',     '', '', '', '', 
         'custnum',             'int',     '', '', '', '', 
         'pkgpart',             'int',     '', '', '', '', 
+        'pkgbatch',        'varchar', 'NULL', $char_d, '', '',
         'locationnum',         'int', 'NULL', '', '', '',
         'otaker',          'varchar', 'NULL', 32, '', '', 
         'usernum',             'int', 'NULL', '', '', '',
+        'order_date',     @date_type,             '', '', 
         'start_date',     @date_type,             '', '', 
         'setup',          @date_type,             '', '', 
         'bill',           @date_type,             '', '', 
@@ -1276,6 +1435,7 @@ sub tables_hashref {
         'adjourn',        @date_type,             '', '', 
         'cancel',         @date_type,             '', '', 
         'expire',         @date_type,             '', '', 
+        'contract_end',   @date_type,             '', '',
         'change_date',    @date_type,             '', '',
         'change_pkgnum',       'int', 'NULL', '', '', '',
         'change_pkgpart',      'int', 'NULL', '', '', '',
@@ -1283,13 +1443,16 @@ 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, '', '', 
       ],
       'primary_key' => 'pkgnum',
       'unique' => [],
-      'index' => [ ['custnum'], ['pkgpart'], [ 'locationnum' ], [ 'usernum' ],
-                   [ 'start_date' ], ['setup'], ['last_bill'], ['bill'],
-                   ['susp'], ['adjourn'], ['expire'], ['cancel'],
-                   ['change_date'],
+      'index' => [ ['custnum'], ['pkgpart'], [ 'pkgbatch' ], [ 'locationnum' ],
+                   [ 'usernum' ], [ 'agent_pkgid' ],
+                   ['order_date'], [ 'start_date' ], ['setup'], ['bill'],
+                   ['last_bill'], ['susp'], ['adjourn'], ['cancel'],
+                   ['expire'], ['contract_end'], ['change_date'],
                  ],
     },
 
@@ -1437,7 +1600,7 @@ sub tables_hashref {
       'index'       => [ [ 'svcnum' ], [ 'optionname' ] ],
     },
 
-    'part_pkg' => {
+   'part_pkg' => {
       'columns' => [
         'pkgpart',       'serial',    '',   '', '', '', 
         'pkg',           'varchar',   '',   $char_d, '', '', 
@@ -1461,6 +1624,7 @@ sub tables_hashref {
         'pay_weight',    'real',    'NULL', '', '', '',
         'credit_weight', 'real',    'NULL', '', '', '',
         'agentnum',      'int',     'NULL', '', '', '', 
+        'fcc_ds0s',      'int',     'NULL', '', '', '', 
         'no_auto',      'char',     'NULL',  1, '', '', 
       ],
       'primary_key' => 'pkgpart',
@@ -1485,6 +1649,17 @@ sub tables_hashref {
     # XXX somewhat borked unique: we don't really want a hidden and unhidden
     # it turns out we'd prefer to use svc, bill, and invisibill (or something)
 
+    'part_pkg_discount' => {
+      'columns' => [
+        'pkgdiscountnum', 'serial',   '',      '', '', '',
+        'pkgpart',        'int',      '',      '', '', '',
+        'discountnum',    'int',      '',      '', '', '', 
+      ],
+      'primary_key' => 'pkgdiscountnum',
+      'unique' => [ [ 'pkgpart', 'discountnum' ] ],
+      'index'  => [],
+    },
+
     'part_pkg_taxclass' => {
       'columns' => [
         'taxclassnum',  'serial', '',       '', '', '',
@@ -1559,6 +1734,7 @@ sub tables_hashref {
         'svcpart',    'int',    '',   '', '', '', 
         'quantity',   'int',    '',   '', '', '', 
         'primary_svc','char', 'NULL',  1, '', '', 
+        'hidden',     'char', 'NULL',  1, '', '',
       ],
       'primary_key' => 'pkgsvcnum',
       'unique' => [ ['pkgpart', 'svcpart'] ],
@@ -1583,6 +1759,7 @@ sub tables_hashref {
         'svc',        'varchar',   '',   $char_d, '', '', 
         'svcdb',      'varchar',   '',   $char_d, '', '', 
         'disabled',   'char',  'NULL',   1, '', '', 
+        'preserve',   'char', 'NULL',  1, '', '',
       ],
       'primary_key' => 'svcpart',
       'unique' => [],
@@ -1631,6 +1808,35 @@ sub tables_hashref {
       'unique' => [],
       'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ],
     },
+    
+    'qual' => {
+      'columns' => [
+        'qualnum',  'serial',     '',     '', '', '', 
+        'custnum',    'int',     'NULL',     '', '', '',
+        'prospectnum',    'int',     'NULL',     '', '', '',
+        'locationnum',    'int',     'NULL',     '', '', '',
+	'phonenum',     'varchar', 'NULL',       24, '', '',
+        'exportnum',      'int', 'NULL', '', '', '', 
+        'vendor_qual_id',      'varchar', 'NULL', $char_d, '', '', 
+        'status',      'char', '', 1, '', '', 
+      ],
+      'primary_key' => 'qualnum',
+      'unique' => [],
+      'index' => [ [ 'locationnum' ], ['custnum'], ['prospectnum'],
+		    ['phonenum'], ['vendor_qual_id'] ],
+    },
+    
+    'qual_option' => {
+      'columns' => [
+        'optionnum', 'serial', '', '', '', '', 
+        'qualnum',  'int',     '',     '', '', '', 
+        'optionname', 'varchar', '', $char_d, '', '', 
+        'optionvalue', 'text', 'NULL', '', '', '', 
+      ],
+      'primary_key' => 'optionnum',
+      'unique' => [],
+      'index' => [],
+    },
 
     'svc_acct' => {
       'columns' => [
@@ -1659,6 +1865,8 @@ sub tables_hashref {
         'pbxsvc',    'int', 'NULL', '', '', '',
         'last_login',  @date_type, '', '', 
         'last_logout', @date_type, '', '', 
+        #cardfortress field(s)
+        'cf_privatekey',      'text', 'NULL',      '', '', '',
         #communigate pro fields (quota = MaxAccountSize)
         'cgp_aliases',     'varchar', 'NULL',     255, '', '',
         #settings
@@ -1673,7 +1881,8 @@ sub tables_hashref {
         'cgp_rpopallowed',    'char', 'NULL',       1, '', '', #RPOPAllowed
         'cgp_mailtoall',      'char', 'NULL',       1, '', '', #MailToAll
         'cgp_addmailtrailer', 'char', 'NULL',       1, '', '', #AddMailTrailer
-        #XXX archive messages, mailing lists
+        'cgp_archiveafter',    'int', 'NULL',      '', '', '', #ArchiveMessagesAfter
+        #XXX mailing lists
         #preferences
         'cgp_deletemode',     'varchar', 'NULL', $char_d, '', '',#DeleteMode
         'cgp_emptytrash',     'varchar', 'NULL', $char_d, '', '',#EmptyTrash
@@ -1683,7 +1892,6 @@ sub tables_hashref {
         'cgp_prontoskinname', 'varchar', 'NULL', $char_d, '', '',#ProntoSkinName
         'cgp_sendmdnmode',    'varchar', 'NULL', $char_d, '', '',#SendMDNMode
         #mail
-#vacation message, redirect all mail, mail rules
         #XXX RPOP settings
       ],
       'primary_key' => 'svcnum',
@@ -1728,11 +1936,14 @@ 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',       '', '', '',
         'cgp_aliases',  'varchar', 'NULL',      255, '', '',
         'cgp_accessmodes','varchar','NULL',     255, '', '', #DomainAccessModes
+        'cgp_certificatetype','varchar','NULL', $char_d, '', '',
         #settings
         'acct_def_password_selfchange',   'char', 'NULL',       1,  '', '', 
         'acct_def_password_recover',      'char', 'NULL',       1, 'Y', '', 
@@ -1745,7 +1956,7 @@ sub tables_hashref {
         'acct_def_cgp_rpopallowed',       'char', 'NULL',       1,  '', '', 
         'acct_def_cgp_mailtoall',         'char', 'NULL',       1,  '', '', 
         'acct_def_cgp_addmailtrailer',    'char', 'NULL',       1,  '', '', 
-        #XXX archive messages
+        'acct_def_cgp_archiveafter',       'int', 'NULL',      '',  '', '',
         #preferences
         'acct_def_cgp_deletemode',     'varchar', 'NULL', $char_d,  '', '',
         'acct_def_cgp_emptytrash',     'varchar', 'NULL', $char_d,  '', '',
@@ -1754,13 +1965,118 @@ sub tables_hashref {
         'acct_def_cgp_skinname',       'varchar', 'NULL', $char_d,  '', '',
         'acct_def_cgp_prontoskinname', 'varchar', 'NULL', $char_d,  '', '',
         'acct_def_cgp_sendmdnmode',    'varchar', 'NULL', $char_d,  '', '',
-        #mail
-        #XXX rules, archive rule, spam foldering rule(s)
       ],
       'primary_key' => 'svcnum',
       'unique' => [ ],
       'index' => [ ['domain'] ],
     },
+    
+    '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,  '', '',
+        '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',       '', '', '',
+      ],
+      'primary_key' => 'svcnum',
+      'unique' => [ ],
+      'index' => [ ['phonenum'], ['vendor_order_id'] ],
+    },
+    
+    'dsl_note' => {
+      'columns' => [
+        'notenum',           'serial',    '',        '', '', '',
+	'svcnum',     'int', '',       '', '', '',
+        'author',     'varchar', 'NULL', $char_d,  '', '',
+        'priority',   'char', 'NULL',       1,  '', '', 
+	'_date',     'int', 'NULL',       '', '', '',
+	'note',     'text', '',       '', '', '',
+      ],
+      'primary_key' => 'notenum',
+      'unique' => [ ],
+      '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' => [
@@ -1770,6 +2086,7 @@ sub tables_hashref {
         'recaf',     'char',    '',  2, '', '', 
         'rectype',   'varchar',    '',  5, '', '', 
         'recdata',   'varchar', '',  255, '', '', 
+        'ttl',       'int',     'NULL', '', '', '',
       ],
       'primary_key' => 'recnum',
       'unique'      => [],
@@ -1788,40 +2105,40 @@ sub tables_hashref {
 
     'cgp_rule' => {
       'columns' => [
-        'rulenum',  'serial', '',      '', '', '',
-        'name',    'varchar', '', $char_d, '', '',
-        'comment', 'varchar', '', $char_d, '', '',
-        'svcnum',      'int', '',      '', '', '',
-        'priority',    'int', '',      '', '', '',
+        'rulenum',  'serial',     '',      '', '', '',
+        'name',    'varchar',     '', $char_d, '', '',
+        'comment', 'varchar', 'NULL', $char_d, '', '',
+        'svcnum',      'int',     '',      '', '', '',
+        'priority',    'int',     '',      '', '', '',
       ],
       'primary_key' => 'rulenum',
-      'unique'      => [],
-      'index '      => [ [ 'svcnum' ] ],
+      'unique'      => [ [ 'svcnum', 'name' ] ],
+      'index'       => [ [ 'svcnum' ] ],
     },
 
     'cgp_rule_condition' => {
       'columns' => [
-        'ruleconditionnum',  'serial', '',      '', '', '',
-        'condition',        'varchar', '', $char_d, '', '',
-        'op',               'varchar', '', $char_d, '', '',
-        'params',           'varchar', '',     255, '', '',
-        'rulenum',              'int', '',      '', '', '',
+        'ruleconditionnum',  'serial',     '',      '', '', '',
+        'conditionname',    'varchar',     '', $char_d, '', '',
+        'op',               'varchar', 'NULL', $char_d, '', '',
+        'params',           'varchar', 'NULL',     255, '', '',
+        'rulenum',              'int',     '',      '', '', '',
       ],
       'primary_key' => 'ruleconditionnum',
       'unique'      => [],
-      'index '      => [ [ 'rulenum' ] ],
+      'index'       => [ [ 'rulenum' ] ],
     },
 
     'cgp_rule_action' => {
        'columns' => [
-        'ruleactionnum',  'serial', '',      '', '', '',
-        'action',        'varchar', '', $char_d, '', '',
-        'params',        'varchar', '',     255, '', '',
-        'rulenum',           'int', '',      '', '', '',
+        'ruleactionnum',  'serial',     '',      '', '', '',
+        'action',        'varchar',     '', $char_d, '', '',
+        'params',        'varchar', 'NULL',     255, '', '',
+        'rulenum',           'int',     '',      '', '', '',
       ],
       'primary_key' => 'ruleactionnum',
       'unique'      => [],
-      'index '      => [ [ 'rulenum' ] ],
+      'index'       => [ [ 'rulenum' ] ],
    },
 
     'svc_forward' => {
@@ -1983,6 +2300,7 @@ sub tables_hashref {
     'part_export' => {
       'columns' => [
         'exportnum', 'serial', '', '', '', '', 
+        'exportname', 'varchar', 'NULL', $char_d, '', '',
         'machine', 'varchar', '', $char_d, '', '', 
         'exporttype', 'varchar', '', $char_d, '', '', 
         'nodomain',     'char', 'NULL', 1, '', '', 
@@ -2113,6 +2431,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' ] ],
@@ -2148,12 +2467,18 @@ sub tables_hashref {
 
     'acct_snarf' => {
       'columns' => [
-        'snarfnum',  'int', '', '', '', '', 
-        'svcnum',    'int', '', '', '', '', 
-        'machine',   'varchar', '', 255, '', '', 
-        'protocol',  'varchar', '', $char_d, '', '', 
-        'username',  'varchar', '', $char_d, '', '', 
-        '_password', 'varchar', '', $char_d, '', '', 
+        'snarfnum',    'serial',     '',      '', '', '', 
+        'snarfname',  'varchar', 'NULL', $char_d, '', '', 
+        'svcnum',         'int',     '',      '', '', '', 
+        'machine',    'varchar',     '',     255, '', '', 
+        'protocol',   'varchar',     '', $char_d, '', '', 
+        'username',   'varchar',     '', $char_d, '', '', 
+        '_password',  'varchar',     '', $char_d, '', '', 
+        'check_freq',     'int', 'NULL',      '', '', '', 
+        'leavemail',     'char', 'NULL',       1, '', '', 
+        'apop',          'char', 'NULL',       1, '', '', 
+        'tls',           'char', 'NULL',       1, '', '', 
+        'mailbox',    'varchar', 'NULL', $char_d, '', '', 
       ],
       'primary_key' => 'snarfnum',
       'unique' => [],
@@ -2162,8 +2487,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',
@@ -2196,6 +2521,18 @@ sub tables_hashref {
       'index'       => [ [ 'pkgpart' ], [ 'optionname' ] ],
     },
 
+    'part_pkg_vendor' => {
+      'columns' => [
+        'num', 'serial', '', '', '', '', 
+        'pkgpart', 'int', '', '', '', '', 
+        'exportnum', 'int', '', '', '', '', 
+        'vendor_pkg_id', 'varchar', '', $char_d, '', '', 
+      ],
+      'primary_key' => 'num',
+      'unique' => [ [ 'pkgpart', 'exportnum' ] ],
+      'index'       => [ [ 'pkgpart' ] ],
+    },
+    
     'part_pkg_report_option' => {
       'columns' => [
         'num',      'serial',   '',      '', '', '', 
@@ -2224,12 +2561,14 @@ sub tables_hashref {
         'orig_regionnum',  'int', 'NULL',     '', '', '', 
         'dest_regionnum',  'int',     '',     '', '', '', 
         'min_included',    'int',     '',     '', '', '', 
-        'conn_charge',     @money_type, '0', '', #'decimal','','10,5','0','',
+        'conn_charge',     'decimal', '', '10,4', '0', '',
         'conn_sec',        'int',     '',     '', '0', '',
         'min_charge',      'decimal', '', '10,5', '', '', #@money_type, '', '', 
         'sec_granularity', 'int',     '',     '', '', '', 
-        #time period (link to table of periods)?
+        'ratetimenum',     'int', 'NULL',     '', '', '',
         'classnum',        'int', 'NULL',     '', '', '', 
+        'cdrtypenum',      'int', 'NULL',     '', '', '',
+        'region_group', 'char', 'NULL',       1, '', '', 
       ],
       'primary_key' => 'ratedetailnum',
       'unique'      => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
@@ -2259,6 +2598,28 @@ sub tables_hashref {
       'index'       => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ],
     },
 
+    'rate_time' => {
+      'columns' => [
+        'ratetimenum', 'serial',      '',      '', '', '',
+        'ratetimename',   'varchar',      '', $char_d, '', '',
+      ],
+      'primary_key' => 'ratetimenum',
+      'unique'      => [],
+      'index'       => [],
+    },
+
+    'rate_time_interval' => {
+      'columns' => [
+        'intervalnum', 'serial', '', '', '', '',
+        'stime',          'int', '', '', '', '',
+        'etime',          'int', '', '', '', '',
+        'ratetimenum',    'int', '', '', '', '',
+      ],
+      'primary_key' => 'intervalnum',
+      'unique'      => [],
+      'index'       => [],
+    },
+
     'usage_class' => {
       'columns' => [
         'classnum',    'serial',      '',      '', '', '', 
@@ -2437,14 +2798,16 @@ 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',      '',    '', '',
+
         ###
         # 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
@@ -2490,10 +2853,14 @@ 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',
@@ -2501,6 +2868,7 @@ sub tables_hashref {
       'index' => [ [ 'calldate' ],
                    [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ],
                    [ 'accountcode' ], [ 'carrierid' ], [ 'cdrid' ],
+                   [ 'sessionnum' ], [ 'subscriber' ],
                    [ 'freesidestatus' ], [ 'freesiderewritestatus' ],
                    [ 'cdrbatch' ], [ 'cdrbatchnum' ],
                  ],
@@ -2524,6 +2892,7 @@ sub tables_hashref {
         'termpart',         'int',     '',      '', '', '',#future use see below
         'rated_price',  'decimal', 'NULL',  '10,4', '', '',
         'status',       'varchar', 'NULL',      32, '', '',
+        'svcnum',           'int', 'NULL',      '', '', '',
       ],
       'primary_key' => 'cdrtermnum',
       'unique'      => [ [ 'acctid', 'termpart' ] ],
@@ -2592,12 +2961,13 @@ sub tables_hashref {
       'columns' => [
         'itemnum',  'serial',      '',      '', '', '',
         'classnum', 'int',         '',      '', '', '',
+        'agentnum', 'int',     'NULL',      '', '', '',
         'item',     'varchar',     '', $char_d, '', '',
         'svcnum',   'int',     'NULL',      '', '', '',
       ],
       'primary_key' => 'itemnum',
       'unique' => [ [ 'classnum', 'item' ] ],
-      'index'  => [ [ 'classnum' ], [ 'svcnum' ] ],
+      'index'  => [ [ 'classnum' ], [ 'agentnum' ], [ 'svcnum' ] ],
     },
 
     'inventory_class' => {
@@ -2693,6 +3063,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' => [],
@@ -2717,7 +3097,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' ] ], #?
@@ -2734,6 +3114,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, '', '',
       ],
@@ -2747,6 +3131,77 @@ sub tables_hashref {
                     [ 'availbatch' ],
                   ],
     },
+    
+    'lata' => {
+      'columns' => [
+        'latanum',    'int',      '',      '', '', '', 
+        'description',   'varchar',    '',      $char_d, '', '', 
+      ],
+      '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',     '',      '', '', '',
+      ],
+      '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' => [
@@ -2796,10 +3251,11 @@ sub tables_hashref {
 
     'svc_pbx' => {
       'columns' => [
-        'svcnum',         'int',     '',      '', '', '', 
-        'id',             'int', 'NULL',      '', '', '', 
-        'title',      'varchar', 'NULL', $char_d, '', '', 
-        'max_extensions', 'int', 'NULL',      '', '', '',
+        'svcnum',           'int',     '',      '', '', '', 
+        'id',               'int', 'NULL',      '', '', '', 
+        'title',        'varchar', 'NULL', $char_d, '', '', 
+        'max_extensions',   'int', 'NULL',      '', '', '',
+        'max_simultaneous', 'int', 'NULL',      '', '', '',
       ],
       'primary_key' => 'svcnum',
       'unique' => [],
@@ -2845,6 +3301,126 @@ sub tables_hashref {
       'index'       => [['listnum'],['svcnum'],['contactemailnum'],['email']],
     },
 
+    'bill_batch' => {
+      'columns' => [
+        'batchnum',         'serial',     '', '', '', '',
+        'status',             'char', 'NULL','1', '', '',
+        'pdf',                'blob', 'NULL', '', '', '',
+      ],
+      'primary_key' => 'batchnum',
+      'unique'      => [],
+      'index'       => [],
+    },
+
+    'cust_bill_batch' => {
+      'columns' => [
+        'billbatchnum',     'serial',     '', '', '', '',
+        'batchnum',            'int',     '', '', '', '',
+        'invnum',              'int',     '', '', '', '',
+      ],
+      'primary_key' => 'billbatchnum',
+      'unique'      => [],
+      'index'       => [ [ 'batchnum' ], [ 'invnum' ] ],
+    },
+
+    'cust_bill_batch_option' => {
+      'columns' => [
+        'optionnum', 'serial', '', '', '', '', 
+        'billbatchnum', 'int', '', '', '', '', 
+        'optionname', 'varchar', '', $char_d, '', '', 
+        'optionvalue', 'text', 'NULL', '', '', '', 
+      ],
+      'primary_key' => 'optionnum',
+      'unique'      => [],
+      'index'       => [ [ 'billbatchnum' ], [ 'optionname' ] ],
+    },
+
+    'msg_template' => {
+      'columns' => [
+        'msgnum',     'serial',     '',      '', '', '',
+        'msgname',   'varchar',     '', $char_d, '', '',
+        'agentnum',      'int', 'NULL',      '', '', '',
+        'subject',   'varchar', 'NULL',     512, '', '',
+        'mime_type', 'varchar',     '', $char_d, '', '',
+        'body',         'blob', 'NULL',      '', '', '',
+        'disabled',     'char', 'NULL',       1, '', '', 
+        'from_addr', 'varchar', 'NULL',     255, '', '',
+        'bcc_addr',  'varchar', 'NULL',     255, '', '',
+      ],
+      'primary_key' => 'msgnum',
+      'unique'      => [ ['msgname', 'mime_type'] ],
+      'index'       => [ ['agentnum'], ]
+    },
+
+    'svc_cert' => {
+      'columns' => [
+        'svcnum',                'int',     '',      '', '', '', 
+        'recnum',                'int', 'NULL',      '', '', '',
+        'privatekey',           'text', 'NULL',      '', '', '',
+        'csr',                  'text', 'NULL',      '', '', '',
+        'certificate',          'text', 'NULL',      '', '', '',
+        'cacert',               'text', 'NULL',      '', '', '',
+        'common_name',       'varchar', 'NULL', $char_d, '', '',
+        'organization',      'varchar', 'NULL', $char_d, '', '',
+        'organization_unit', 'varchar', 'NULL', $char_d, '', '',
+        'city',              'varchar', 'NULL', $char_d, '', '',
+        'state',             'varchar', 'NULL', $char_d, '', '',
+        'country',              'char', 'NULL',       2, '', '',
+        'cert_contact',      'varchar', 'NULL', $char_d, '', '',
+      ],
+      'primary_key' => 'svcnum',
+      'unique' => [],
+      'index'  => [], #recnum
+    },
+
+    'svc_port' => {
+      'columns' => [
+        'svcnum',                'int',     '',      '', '', '', 
+        'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields
+      ],
+      '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'  => [],
+    },
+
+    %{ tables_hashref_torrus() },
+
+    # tables of ours for doing torrus virtual port combining
+    'torrus_srvderive' => {
+      'columns' => [
+        'derivenum',     'serial',     '', '', '', '',
+        'serviceid',    'varchar',     '', 64, '', '', #srvexport / reportfields
+        'last_srv_date',   'date', 'NULL', '', '', '',
+      ],
+      'primary_key' => 'derivenum',
+      'unique' => [ ['serviceid'] ],
+      'index'  => [],
+    },
+
+    'torrus_srvderive_component' => {
+      'columns' => [
+        'componentnum', 'serial', '', '', '', '',
+        'derivenum',       'int', '', '', '', '',
+        'serviceid',   'varchar', '', 64, '', '', #srvexport / reportfields
+      ],
+      'primary_key' => 'componentnum',
+      'unique'      => [ [ 'derivenum', 'serviceid' ], ],
+      'index'       => [ [ 'derivenum', ], ],
+    },
+
 
     # name type nullability length default local