my $username_len = 64; #usernamemax config file
- # name type nullability length default local
+ # Return 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 if 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,
+ # ...
+ #
+ # ],
+ # name type nullability length default local
return {
'agent' => {
'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, '', '',
],
'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, '', '',
'classnum', 'int', 'NULL', '', '', '',
'comment', 'varchar', 'NULL', 255, '', '',
'selfservice_access', 'char', 'NULL', 1, '', '',
- 'invoice_dest', 'char', 'NULL', 1, '', '',
+ 'invoice_dest', 'char', 'NULL', 1, '', '', # Y or NULL
+ 'message_dest', 'char', 'NULL', 1, '', '', # Y or NULL
],
'primary_key' => 'custcontactnum',
'unique' => [ [ 'custnum', 'contactnum' ], ],
'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, '', '',
'ocn', 'char', 'NULL', 4, '', '',
],
'primary_key' => 'prefixnum',
- 'unique' => [],
+ 'unique' => [ [ 'countrycode', 'npa', 'nxx' ] ],
'index' => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ],
'foreign_keys' => [
{ columns => [ 'regionnum' ],
],
},
+ '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', '', '', '', '',
'access_group' => {
'columns' => [
- 'groupnum', 'serial', '', '', '', '',
- 'groupname', 'varchar', '', $char_d, '', '',
+ 'groupnum', 'serial', '', '', '', '',
+ 'groupname', 'varchar', '', $char_d, '', '',
+ 'session_timeout', 'int', 'NULL', '', '', '',
],
'primary_key' => 'groupnum',
'unique' => [ [ 'groupname' ] ],
'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' => [],
'foreign_keys' => [],
},
+ 'realestate_unit' => {
+ 'columns' => [
+ 'realestatenum', 'serial', '', '', '', '',
+ 'realestatelocnum', 'int', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'unit_title', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'realestatenum',
+ 'unique' => [ ['unit_title'] ],
+ 'index' => [
+ ['agentnum'],
+ ['realestatelocnum'],
+ ['disabled'],
+ ['unit_title'],
+ ],
+ 'foreign_keys' => [
+ {columns => ['agentnum'], table => 'agent'},
+ {columns => ['realestatelocnum'] => table => 'realestate_location'},
+ ],
+ },
+
+ realestate_location => {
+ 'columns' => [
+ 'realestatelocnum', 'serial', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'location_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', 'NULL', 1, '', '',
+ ],
+ primary_key => 'realestatelocnum',
+ 'unique' => [ ['location_title'] ],
+ 'index' => [ ['agentnum'], ['disabled'] ],
+ 'foreign_keys' => [
+ {columns => ['agentnum'], table => 'agent'},
+ ],
+ },
+
+ svc_realestate => {
+ columns => [
+ 'svcnum', 'serial', '', '', '', '',
+ 'realestatenum', 'int', 'NULL', '', '', '',
+ ],
+ primary_key => 'svcnum',
+ index => [],
+ },
+
# name type nullability length default local
#'new_table' => {
=cut
1;
-