rt# 74031 Update database schema

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

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index b6d0f07..6d88204 100644 (file)
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -495,7 +495,42 @@ sub tables_hashref {
 
   my $username_len = 64; #usernamemax config file
 
-    # name type nullability length default local
+  # Returns a hashref defining the entire application database schema
+  # Each key of the hashref contains a structure describing a database table
+  #
+  # table_name => {
+  #   columns      => [...],
+  #   primary_key  => 'column',
+  #   unique       => [column,column,...],
+  #   index        => [[column],[column,column],...],
+  #   foreign_keys => [{...},{...},...],
+  # }
+  #
+  #
+  # columns => [
+  #
+  #   'column_name',
+  #
+  #   'column_type',
+  #
+  #   'NULL' or '',   # 'NULL' : Allow null values
+  #                   # ''     : Disallow null values
+  #
+  #   'length',       # Column size value.  eg:
+  #                   # 40     : VARCHAR(40)
+  #                   # '10,2' : FLOAT(10,2)
+  #
+  #   'default',      # Default column value for a new record
+  #                   # (Unclear is setting this to '' results in a default
+  #                   #  value of NULL or empty string)
+  #
+  #   '',             # local ?
+  #
+  #   name, type, nullability, length, default, local,
+  #   name, type, nullability, length, default, local,
+  #   ...
+  #
+  # ],
 
   return {
 
@@ -695,6 +730,7 @@ sub tables_hashref {
         'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
         'agent_invid',  'int', 'NULL', '', '', '', #(varchar?) importing legacy
         'promised_date', @date_type,       '', '',
+        'taxengine_request', 'text', 'NULL', '', '', '',
         
         'pending',     'char', 'NULL',  1, '', '',
       ],
@@ -732,6 +768,7 @@ sub tables_hashref {
         'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
         'agent_invid',  'int', 'NULL', '', '', '', #(varchar?) importing legacy
         'promised_date', @date_type,       '', '',
+        'taxengine_request', 'text', 'NULL', '', '', '',
 
         #void fields
         'void_date', @date_type, '', '', 
@@ -1918,14 +1955,15 @@ sub tables_hashref {
     'quotation' => {
       'columns' => [
         #regular fields
-        'quotationnum',   'serial',     '', '', '', '', 
-        'prospectnum',       'int', 'NULL', '', '', '',
-        'custnum',           'int', 'NULL', '', '', '',
-        '_date',        @date_type,             '', '', 
-        'disabled',         'char', 'NULL',  1, '', '', 
-        'usernum',           'int', 'NULL', '', '', '',
-        'close_date',   @date_type,             '', '', 
-        'confidence',        'int', 'NULL', '', '', '',
+        'quotationnum',           'serial',     '', '',   '', '', 
+        'prospectnum',               'int', 'NULL', '',   '', '',
+        'custnum',                   'int', 'NULL', '',   '', '',
+        '_date',                @date_type,               '', '', 
+        'disabled',                 'char', 'NULL',  1,   '', '', 
+        'usernum',                   'int', 'NULL', '',   '', '',
+        'close_date',           @date_type,               '', '', 
+        'confidence',                'int', 'NULL', '',   '', '',
+        'quotation_description', 'varchar', 'NULL', '50', '', '',
         #'total',      @money_type,       '', '', 
         #'quotation_term', 'varchar', 'NULL', $char_d, '', '',
       ],
@@ -2334,6 +2372,7 @@ sub tables_hashref {
         'taxratelocationnum', 'serial',  '',     '', '', '', 
         'data_vendor',        'varchar', 'NULL', $char_d, '', '',
         'geocode',            'varchar', '',     20,      '', '', 
+        'district',           'varchar', 'NULL', $char_d, '', '',
         'city',               'varchar', 'NULL', $char_d, '', '',
         'county',             'varchar', 'NULL', $char_d, '', '',
         'state',              'char',    'NULL',       2, '', '', 
@@ -2704,6 +2743,7 @@ sub tables_hashref {
         'status',        'varchar', 'NULL', $char_d, '', '', 
         'failure_status','varchar', 'NULL',      16, '', '',
         'error_message', 'varchar', 'NULL', $char_d, '', '',
+        'paycode',       'varchar', 'NULL', $char_d, '', '',
       ],
       'primary_key'  => 'paybatchnum',
       'unique'       => [],
@@ -5229,7 +5269,7 @@ sub tables_hashref {
         'ocn',         'char',    'NULL',       4, '', '', 
       ],
       'primary_key'  => 'prefixnum',
-      'unique'       => [],
+      'unique'       => [ [ 'countrycode', 'npa', 'nxx' ] ],
       'index'        => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ],
       'foreign_keys' => [
                           { columns    => [ 'regionnum' ],
@@ -5794,6 +5834,25 @@ sub tables_hashref {
                         ],
     },
 
+    'access_user_session_log' => {
+      'columns' => [
+        'sessionlognum', 'serial', '',      '', '', '',
+        'usernum',          'int', '',      '', '', '',
+        'start_date',  @date_type,              '', '',
+        'last_date',   @date_type,              '', '',
+        'logout_date', @date_type,              '', '',
+        'logout_type',  'varchar', '', $char_d, '', '',
+      ],
+      'primary_key' => 'sessionlognum',
+      'unique'       => [],
+      'index'        => [],
+      'foreign_keys' => [
+                          { columns    => [ 'usernum' ],
+                            table      => 'access_user',
+                          },
+                        ],
+    },
+
     'access_user' => {
       'columns' => [
         'usernum',             'serial',     '',      '', '', '',
@@ -5841,8 +5900,9 @@ sub tables_hashref {
 
     'access_group' => {
       'columns' => [
-        'groupnum',   'serial', '',      '', '', '',
-        'groupname', 'varchar', '', $char_d, '', '',
+        'groupnum',        'serial',     '',      '', '', '',
+        'groupname',      'varchar',     '', $char_d, '', '',
+        'session_timeout',    'int', 'NULL',      '', '', '',
       ],
       'primary_key' => 'groupnum',
       'unique' => [ [ 'groupname' ] ],
@@ -5993,6 +6053,9 @@ sub tables_hashref {
         'lnp_other_provider',         'varchar', 'NULL', $char_d, '', '',
         'lnp_other_provider_account', 'varchar', 'NULL', $char_d, '', '',
         'lnp_reject_reason',          'varchar', 'NULL', $char_d, '', '',
+        'lnp_portid',                 'varchar', 'NULL', $char_d, '', '',
+        'lnp_signature',                 'char', 'NULL',       1, '', '',
+        'lnp_bill',                      'char', 'NULL',       1, '', '',
         'sms_carrierid',                  'int', 'NULL',      '', '', '',
         'sms_account',                'varchar', 'NULL', $char_d, '', '',
         'max_simultaneous',               'int', 'NULL',      '', '', '',
@@ -6990,6 +7053,7 @@ sub tables_hashref {
         'vendor_order_status',  'varchar', 'NULL', $char_d,  '', '',
         'endpoint_ip_addr',     'varchar', 'NULL', 40, '', '',
         'endpoint_mac_addr',    'varchar', 'NULL', 12, '', '',
+        'internal_circuit_id',  'varchar', 'NULL', 64, '', '',
       ],
       'primary_key' => 'svcnum',
       'unique'      => [],
@@ -7572,6 +7636,41 @@ sub tables_hashref {
       'foreign_keys'  => [],
     },
 
+    'realestate_property' => {
+      'columns' => [
+        'propnum',  'serial',  '',     '',      '',  '',
+        'agentnum', 'int',     'NULL', '',      '',  '',
+        'title',    'varchar', '',     $char_d, '',  '',
+        'address1', 'varchar', 'NULL', $char_d, '',  '',
+        'address2', 'varchar', 'NULL', $char_d, '',  '',
+        'city',     'varchar', 'NULL', $char_d, '',  '',
+        'state',    'varchar', 'NULL', $char_d, '',  '',
+        'zip',      'char',    'NULL', 5,       '',  '',
+        'disabled', 'char',    '',     1,       '0', '',
+      ],
+      'primary_key'  => 'propnum',
+      'unique'       => [],
+      'index'        => [ ['agentnum'] ],
+      'foreign_keys' => [
+        {columns => ['agentnum'], table => 'agent'}
+      ],
+    },
+
+    'realestate_subproperty' => {
+      'columns' => [
+        'spropnum', 'serial',  '', '',      '',  '',
+        'propnum',  'int',     '', '',      '',  '',
+        'subtitle', 'varchar', '', $char_d, '',  '',
+        'disabled', 'char',    '', 1,       '0', '',
+      ],
+      'primary_key'  => 'spropnum',
+      'unique'       => [],
+      'index'        => [ ['propnum'] ],
+      'foreign_keys' => [
+        {columns => ['propnum'], table => 'realestate_property'}
+      ],
+    },
+
     # name type nullability length default local
 
     #'new_table' => {
@@ -7598,4 +7697,3 @@ L<DBIx::DBSchema>
 =cut
 
 1;
-