X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=633e59cb67580ca88fd0e3fd1e6901b87e564775;hp=b2dddcafcf39dd29e218dab90b22851bcc3e8d12;hb=56f9dffcd409d2a10e7a2cbe4c15b397f6f3abe1;hpb=eb9d1063e1203231ee0c6922ea5638370f7b5ece diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index b2dddcafc..633e59cb6 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -187,10 +187,11 @@ sub dbdef_dist { my $tables_hashref_torrus = tables_hashref_torrus(); - #create history tables (false laziness w/create-history-tables) + #create history tables foreach my $table ( - grep { ! /^clientapi_session/ + grep { ! /^(clientapi|access_user)_session/ && ! /^h_/ + && ! /^log(_context)?$/ && ! $tables_hashref_torrus->{$_} } $dbdef->tables @@ -445,7 +446,7 @@ sub tables_hashref { my @taxrate_type = ( 'decimal', '', '14,8' ); # requires pg 8 for my @taxrate_typen = ( 'decimal', 'NULL', '14,8' ); # fs-upgrade to work - my $username_len = 32; #usernamemax config file + my $username_len = 64; #usernamemax config file # name type nullability length default local @@ -473,6 +474,18 @@ sub tables_hashref { 'index' => [ ['typenum'], ['disabled'], ['agent_custnum'] ], }, + 'agent_pkg_class' => { + 'columns' => [ + 'agentpkgclassnum', 'serial', '', '', '', '', + 'agentnum', 'int', '', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'commission_percent', 'decimal', '', '7,4', '', '', + ], + 'primary_key' => 'agentpkgclassnum', + 'unique' => [ [ 'agentnum', 'classnum' ], ], + 'index' => [], + }, + 'agent_type' => { 'columns' => [ 'typenum', 'serial', '', '', '', '', @@ -494,6 +507,18 @@ sub tables_hashref { 'index' => [ ['typenum'] ], }, + 'sales' => { + 'columns' => [ + 'salesnum', 'serial', '', '', '', '', + 'salesperson', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'salesnum', + 'unique' => [], + 'index' => [ ['salesnum'], ['disabled'] ], + }, + 'cust_attachment' => { 'columns' => [ 'attachnum', 'serial', '', '', '', '', @@ -539,6 +564,35 @@ sub tables_hashref { 'index' => [ ['custnum'], ['_date'], ['statementnum'], ['agent_invid'] ], }, + 'cust_bill_void' => { + 'columns' => [ + #regular fields + 'invnum', 'int', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'charged', @money_type, '', '', + 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', + + #customer balance info at invoice generation time + 'previous_balance', @money_typen, '', '', #eventually not nullable + 'billing_balance', @money_typen, '', '', #eventually not nullable + + #specific use cases + 'closed', 'char', 'NULL', 1, '', '', #not yet used much + 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements + 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy + 'promised_date', @date_type, '', '', + + #void fields + 'void_date', @date_type, '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', + 'void_usernum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'invnum', + 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh + 'index' => [ ['custnum'], ['_date'], ['statementnum'], ['agent_invid'], [ 'void_usernum' ] ], + }, + #for importing invoices from a legacy system for display purposes only # no effect upon balance 'legacy_cust_bill' => { @@ -718,7 +772,7 @@ sub tables_hashref { 'format', 'char', 'NULL', 1, '', '', 'classnum', 'int', 'NULL', '', '', '', 'duration', 'int', 'NULL', '', 0, '', - 'phonenum', 'varchar', 'NULL', 15, '', '', + 'phonenum', 'varchar', 'NULL', 25, '', '', 'accountcode', 'varchar', 'NULL', 20, '', '', 'startdate', @date_type, '', '', 'regionname', 'varchar', 'NULL', $char_d, '', '', @@ -751,6 +805,109 @@ sub tables_hashref { 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', 'taxtype', 'varchar', '', $char_d, '', '', + 'pkgnum', 'int', '', '', '', '', #redundant + 'locationnum', 'int', '', '', '', '', #redundant + 'amount', @money_type, '', '', + 'taxable_billpkgnum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'billpkgtaxlocationnum', + 'unique' => [], + 'index' => [ [ 'billpkgnum' ], + [ 'taxnum' ], + [ 'pkgnum' ], + [ 'locationnum' ], + [ 'taxable_billpkgnum' ], + ], + }, + + 'cust_bill_pkg_tax_rate_location' => { + 'columns' => [ + 'billpkgtaxratelocationnum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', '', $char_d, '', '', + 'locationtaxid', 'varchar', 'NULL', $char_d, '', '', + 'taxratelocationnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'taxable_billpkgnum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'billpkgtaxratelocationnum', + 'unique' => [], + 'index' => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'taxratelocationnum' ], + [ 'taxable_billpkgnum' ], ], + }, + + 'cust_bill_pkg_void' => { + 'columns' => [ + 'billpkgnum', 'int', '', '', '', '', + 'invnum', 'int', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'pkgpart_override', 'int', 'NULL', '', '', '', + 'setup', @money_type, '', '', + 'recur', @money_type, '', '', + 'sdate', @date_type, '', '', + 'edate', @date_type, '', '', + '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, '', '', + 'hidden', 'char', 'NULL', 1, '', '', + #void fields + 'void_date', @date_type, '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', + 'void_usernum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'billpkgnum', + 'unique' => [], + 'index' => [ ['invnum'], [ 'pkgnum' ], [ 'itemdesc' ], [ 'void_usernum' ], ], + }, + + 'cust_bill_pkg_detail_void' => { + 'columns' => [ + 'detailnum', 'int', '', '', '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable + 'pkgnum', 'int', 'NULL', '', '', '', # deprecated + 'invnum', 'int', 'NULL', '', '', '', # deprecated + 'amount', 'decimal', 'NULL', '10,4', '', '', + 'format', 'char', 'NULL', 1, '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'duration', 'int', 'NULL', '', 0, '', + 'phonenum', 'varchar', 'NULL', 25, '', '', + 'accountcode', 'varchar', 'NULL', 20, '', '', + 'startdate', @date_type, '', '', + 'regionname', 'varchar', 'NULL', $char_d, '', '', + 'detail', 'varchar', '', 255, '', '', + ], + 'primary_key' => 'detailnum', + 'unique' => [], + 'index' => [ [ 'billpkgnum' ], [ 'classnum' ], [ 'pkgnum', 'invnum' ] ], + }, + + 'cust_bill_pkg_display_void' => { + 'columns' => [ + 'billpkgdisplaynum', 'int', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'section', 'varchar', 'NULL', $char_d, '', '', + #'unitsetup', @money_typen, '', '', #override the linked real one? + #'unitrecur', @money_typen, '', '', #this too? + 'post_total', 'char', 'NULL', 1, '', '', + 'type', 'char', 'NULL', 1, '', '', + 'summary', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'billpkgdisplaynum', + 'unique' => [], + 'index' => [ ['billpkgnum'], ], + }, + + 'cust_bill_pkg_tax_location_void' => { + 'columns' => [ + 'billpkgtaxlocationnum', 'int', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', '', $char_d, '', '', 'pkgnum', 'int', '', '', '', '', 'locationnum', 'int', '', '', '', '', #redundant? 'amount', @money_type, '', '', @@ -760,9 +917,9 @@ sub tables_hashref { 'index' => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'pkgnum' ], [ 'locationnum' ] ], }, - 'cust_bill_pkg_tax_rate_location' => { + 'cust_bill_pkg_tax_rate_location_void' => { 'columns' => [ - 'billpkgtaxratelocationnum', 'serial', '', '', '', '', + 'billpkgtaxratelocationnum', 'int', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', 'taxtype', 'varchar', '', $char_d, '', '', @@ -789,6 +946,7 @@ sub tables_hashref { 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'eventnum', 'int', 'NULL', '', '', '', #triggering event for commission + #'commission_agentnum', 'int', 'NULL', '', '', '', # ], 'primary_key' => 'crednum', 'unique' => [], @@ -844,20 +1002,24 @@ sub tables_hashref { 'ss', 'varchar', 'NULL', 11, '', '', 'stateid', 'varchar', 'NULL', $char_d, '', '', 'stateid_state', 'varchar', 'NULL', $char_d, '', '', + 'national_id', 'varchar', 'NULL', $char_d, '', '', 'birthdate' ,@date_type, '', '', + 'spouse_birthdate' ,@date_type, '', '', + 'anniversary_date' ,@date_type, '', '', 'signupdate',@date_type, '', '', 'dundate', @date_type, '', '', 'company', 'varchar', 'NULL', $char_d, '', '', - 'address1', 'varchar', '', $char_d, '', '', + 'address1', 'varchar', 'NULL', $char_d, '', '', 'address2', 'varchar', 'NULL', $char_d, '', '', - 'city', 'varchar', '', $char_d, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', 'state', 'varchar', 'NULL', $char_d, '', '', 'zip', 'varchar', 'NULL', 10, '', '', - 'country', 'char', '', 2, '', '', + 'country', 'char', 'NULL', 2, '', '', 'latitude', 'decimal', 'NULL', '10,7', '', '', 'longitude','decimal', 'NULL', '10,7', '', '', 'coord_auto', 'char', 'NULL', 1, '', '', + 'addr_clean', 'char', 'NULL', 1, '', '', 'daytime', 'varchar', 'NULL', 20, '', '', 'night', 'varchar', 'NULL', 20, '', '', 'fax', 'varchar', 'NULL', 12, '', '', @@ -876,6 +1038,7 @@ sub tables_hashref { 'ship_latitude', 'decimal', 'NULL', '10,7', '', '', 'ship_longitude','decimal', 'NULL', '10,7', '', '', 'ship_coord_auto', 'char', 'NULL', 1, '', '', + 'ship_addr_clean', 'char', 'NULL', 1, '', '', 'ship_daytime', 'varchar', 'NULL', 20, '', '', 'ship_night', 'varchar', 'NULL', 20, '', '', 'ship_fax', 'varchar', 'NULL', 12, '', '', @@ -883,7 +1046,7 @@ sub tables_hashref { 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', 'paycvv', 'varchar', 'NULL', 512, '', '', - 'paymask', 'varchar', 'NULL', $char_d, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate', @date_type, '', '', 'paydate', 'varchar', 'NULL', 10, '', '', 'paystart_month', 'int', 'NULL', '', '', '', @@ -912,9 +1075,14 @@ sub tables_hashref { 'email_csv_cdr', 'char', 'NULL', 1, '', '', 'accountcode_cdr', 'char', 'NULL', 1, '', '', 'billday', 'int', 'NULL', '', '', '', + 'prorate_day', 'int', 'NULL', '', '', '', 'edit_subject', 'char', 'NULL', 1, '', '', 'locale', 'varchar', 'NULL', 16, '', '', 'calling_list_exempt', 'char', 'NULL', 1, '', '', + 'invoice_noemail', 'char', 'NULL', 1, '', '', + 'message_noemail', 'char', 'NULL', 1, '', '', + 'bill_locationnum', 'int', 'NULL', '', '', '', + 'ship_locationnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -925,20 +1093,10 @@ sub tables_hashref { [ 'referral_custnum' ], [ 'payby' ], [ 'paydate' ], [ 'archived' ], - #billing - [ 'last' ], [ 'company' ], - [ 'county' ], [ 'state' ], [ 'country' ], - [ 'zip' ], - [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'mobile' ], - #shipping - [ 'ship_last' ], [ 'ship_company' ], - [ 'ship_county' ], [ 'ship_state' ], [ 'ship_country' ], - [ 'ship_zip' ], - [ 'ship_daytime' ], [ 'ship_night' ], [ 'ship_fax' ], [ 'ship_mobile' ] ], }, - 'cust_recon' => { # what purpose does this serve? + 'cust_recon' => { # (some sort of not-well understood thing for OnPac) 'columns' => [ 'reconid', 'serial', '', '', '', '', 'recondate', @date_type, '', '', @@ -964,19 +1122,30 @@ sub tables_hashref { 'index' => [], }, - #eventually for cust_main too + 'contact_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + 'contact' => { 'columns' => [ 'contactnum', 'serial', '', '', '', '', 'prospectnum', 'int', 'NULL', '', '', '', 'custnum', 'int', 'NULL', '', '', '', 'locationnum', 'int', 'NULL', '', '', '', #not yet + 'classnum', 'int', 'NULL', '', '', '', # 'titlenum', 'int', 'NULL', '', '', '', #eg Mr. Mrs. Dr. Rev. 'last', 'varchar', '', $char_d, '', '', # 'middle', 'varchar', 'NULL', $char_d, '', '', 'first', 'varchar', '', $char_d, '', '', 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer - 'comment', 'varchar', 'NULL', $char_d, '', '', + 'comment', 'varchar', 'NULL', 255, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'contactnum', @@ -1037,8 +1206,52 @@ sub tables_hashref { 'index' => [ [ 'company' ], [ 'agentnum' ], [ 'disabled' ] ], }, - #eventually use for billing & ship from cust_main too - #for now, just cust_pkg locations + 'quotation' => { + 'columns' => [ + #regular fields + 'quotationnum', 'serial', '', '', '', '', + 'prospectnum', 'int', 'NULL', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', + '_date', @date_type, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'usernum', 'int', 'NULL', '', '', '', + #'total', @money_type, '', '', + #'quotation_term', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'quotationnum', + 'unique' => [], + 'index' => [ [ 'prospectnum' ], ['custnum'], ], + }, + + 'quotation_pkg' => { + 'columns' => [ + 'quotationpkgnum', 'serial', '', '', '', '', + 'quotationnum', 'int', 'NULL', '', '', '', #shouldn't be null, + # but history... + 'pkgpart', 'int', '', '', '', '', + 'locationnum', 'int', 'NULL', '', '', '', + 'start_date', @date_type, '', '', + 'contract_end', @date_type, '', '', + 'quantity', 'int', 'NULL', '', '', '', + 'waive_setup', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'quotationpkgnum', + 'unique' => [], + 'index' => [ ['pkgpart'], ], + }, + + 'quotation_pkg_discount' => { + 'columns' => [ + 'quotationpkgdiscountnum', 'serial', '', '', '', '', + 'quotationpkgnum', 'int', '', '', '', '', + 'discountnum', 'int', '', '', '', '', + #'end_date', @date_type, '', '', + ], + 'primary_key' => 'quotationpkgdiscountnum', + 'unique' => [], + 'index' => [ [ 'quotationpkgnum' ], ], #[ 'discountnum' ] ], + }, + 'cust_location' => { #'location' now that its prospects too, but... 'columns' => [ 'locationnum', 'serial', '', '', '', '', @@ -1053,9 +1266,12 @@ sub tables_hashref { 'latitude', 'decimal', 'NULL', '10,7', '', '', 'longitude', 'decimal', 'NULL', '10,7', '', '', 'coord_auto', 'char', 'NULL', 1, '', '', + 'addr_clean', 'char', 'NULL', 1, '', '', 'country', 'char', '', 2, '', '', 'geocode', 'varchar', 'NULL', 20, '', '', 'district', 'varchar', 'NULL', 20, '', '', + 'censustract', 'varchar', 'NULL', 20, '', '', + 'censusyear', 'char', 'NULL', 4, '', '', 'location_type', 'varchar', 'NULL', 20, '', '', 'location_number', 'varchar', 'NULL', 20, '', '', 'location_kind', 'char', 'NULL', 1, '', '', @@ -1065,6 +1281,7 @@ sub tables_hashref { 'unique' => [], 'index' => [ [ 'prospectnum' ], [ 'custnum' ], [ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ], + [ 'city' ], [ 'district' ] ], }, @@ -1119,10 +1336,11 @@ sub tables_hashref { 'cust_class' => { 'columns' => [ - 'classnum', 'serial', '', '', '', '', - 'classname', 'varchar', '', $char_d, '', '', - 'categorynum', 'int', 'NULL', '', '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'categorynum', 'int', 'NULL', '', '', '', + 'tax', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'classnum', 'unique' => [], @@ -1146,6 +1364,7 @@ sub tables_hashref { 'tagname', 'varchar', '', $char_d, '', '', 'tagdesc', 'varchar', 'NULL', $char_d, '', '', 'tagcolor', 'varchar', 'NULL', 6, '', '', + 'by_default', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'tagnum', @@ -1155,9 +1374,10 @@ sub tables_hashref { 'cust_main_exemption' => { 'columns' => [ - 'exemptionnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', - 'taxname', 'varchar', '', $char_d, '', '', + 'exemptionnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'taxname', 'varchar', '', $char_d, '', '', + 'exempt_number', 'varchar', 'NULL', $char_d, '', '', #start/end dates? for reporting? ], 'primary_key' => 'exemptionnum', @@ -1332,12 +1552,25 @@ sub tables_hashref { # index into payby table # eventually 'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above - 'paymask', 'varchar', 'NULL', $char_d, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paydate', 'varchar', 'NULL', 10, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + # cash/check deposit info fields + 'bank', 'varchar', 'NULL', $char_d, '', '', + 'depositor', 'varchar', 'NULL', $char_d, '', '', + 'account', 'varchar', 'NULL', 20, '', '', + 'teller', 'varchar', 'NULL', 20, '', '', + + 'batchnum', 'int', 'NULL', '', '', '', #pay_batch foreign key + + # credit card/EFT fields (formerly in paybatch) + 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK + 'processor', 'varchar', 'NULL', $char_d, '', '', # module name + 'auth', 'varchar','NULL',16, '', '', # CC auth number + 'order_number','varchar','NULL',$char_d, '', '', # transaction number ], 'primary_key' => 'paynum', #i guess not now, with cust_pay_pending, if we actually make it here, we _do_ want to record it# 'unique' => [ [ 'payunique' ] ], @@ -1348,20 +1581,35 @@ sub tables_hashref { 'columns' => [ 'paynum', 'int', '', '', '', '', 'custnum', 'int', '', '', '', '', - 'paid', @money_type, '', '', '_date', @date_type, '', '', + 'paid', @money_type, '', '', + 'otaker', 'varchar', 'NULL', 32, '', '', + 'usernum', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should be # index into payby table # eventually 'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above 'paymask', 'varchar', 'NULL', $char_d, '', '', + #'paydate' ? 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + # cash/check deposit info fields + 'bank', 'varchar', 'NULL', $char_d, '', '', + 'depositor', 'varchar', 'NULL', $char_d, '', '', + 'account', 'varchar', 'NULL', 20, '', '', + 'teller', 'varchar', 'NULL', 20, '', '', + 'batchnum', 'int', 'NULL', '', '', '', #pay_batch foreign key + + # credit card/EFT fields (formerly in paybatch) + 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK + 'processor', 'varchar', 'NULL', $char_d, '', '', # module name + 'auth', 'varchar','NULL',16, '', '', # CC auth number + 'order_number', 'varchar','NULL',$char_d, '', '', # transaction number + + #void fields 'void_date', @date_type, '', '', 'reason', 'varchar', 'NULL', $char_d, '', '', - 'otaker', 'varchar', 'NULL', 32, '', '', - 'usernum', 'int', 'NULL', '', '', '', 'void_usernum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'paynum', @@ -1417,10 +1665,11 @@ sub tables_hashref { 'columns' => [ 'batchnum', 'serial', '', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', - 'payby', 'char', '', 4, '', '', # CARD/CHEK + 'payby', 'char', '', 4, '', '', # CARD/CHEK 'status', 'char', 'NULL', 1, '', '', 'download', @date_type, '', '', 'upload', @date_type, '', '', + 'title', 'varchar', 'NULL',255, '', '', ], 'primary_key' => 'batchnum', 'unique' => [], @@ -1442,13 +1691,14 @@ sub tables_hashref { 'zip', 'varchar', 'NULL', 10, '', '', 'country', 'char', '', 2, '', '', # 'trancode', 'int', '', '', '', '' - 'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should be - 'payinfo', 'varchar', '', 512, '', '', + 'payby', 'char', '', 4, '', '', + 'payinfo', 'varchar', 'NULL', 512, '', '', #'exp', @date_type, '', '' - 'exp', 'varchar', 'NULL', 11, '', '', + 'exp', 'varchar', 'NULL', 11, '', '', 'payname', 'varchar', 'NULL', $char_d, '', '', 'amount', @money_type, '', '', - 'status', 'varchar', 'NULL', $char_d, '', '', + 'status', 'varchar', 'NULL', $char_d, '', '', + 'error_message', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'paybatchnum', 'unique' => [], @@ -1483,6 +1733,8 @@ sub tables_hashref { 'adjourn', @date_type, '', '', 'resume', @date_type, '', '', 'cancel', @date_type, '', '', + 'uncancel', @date_type, '', '', + 'uncancel_pkgnum', 'int', 'NULL', '', '', '', 'expire', @date_type, '', '', 'contract_end', @date_type, '', '', 'dundate', @date_type, '', '', @@ -1490,6 +1742,8 @@ sub tables_hashref { 'change_pkgnum', 'int', 'NULL', '', '', '', 'change_pkgpart', 'int', 'NULL', '', '', '', 'change_locationnum', 'int', 'NULL', '', '', '', + 'main_pkgnum', 'int', 'NULL', '', '', '', + 'pkglinknum', 'int', 'NULL', '', '', '', 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', @@ -1564,6 +1818,30 @@ sub tables_hashref { 'index' => [ [ 'pkgnum' ], [ 'discountnum' ], [ 'usernum' ], ], }, + 'cust_pkg_usage' => { + 'columns' => [ + 'pkgusagenum', 'serial', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'minutes', 'int', '', '', '', '', + 'pkgusagepart', 'int', '', '', '', '', + ], + 'primary_key' => 'pkgusagenum', + 'unique' => [], + 'index' => [ [ 'pkgnum' ], [ 'pkgusagepart' ] ], + }, + + 'cdr_cust_pkg_usage' => { + 'columns' => [ + 'cdrusagenum', 'bigserial', '', '', '', '', + 'acctid', 'bigint', '', '', '', '', + 'pkgusagenum', 'int', '', '', '', '', + 'minutes', 'int', '', '', '', '', + ], + 'primary_key' => 'cdrusagenum', + 'unique' => [], + 'index' => [ [ 'pkgusagenum' ], [ 'acctid' ] ], + }, + 'cust_bill_pkg_discount' => { 'columns' => [ 'billpkgdiscountnum', 'serial', '', '', '', '', @@ -1577,6 +1855,19 @@ sub tables_hashref { 'index' => [ [ 'billpkgnum' ], [ 'pkgdiscountnum' ] ], }, + 'cust_bill_pkg_discount_void' => { + 'columns' => [ + 'billpkgdiscountnum', 'int', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'pkgdiscountnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'months', 'decimal', 'NULL', '7,4', '', '', + ], + 'primary_key' => 'billpkgdiscountnum', + 'unique' => [], + 'index' => [ [ 'billpkgnum' ], [ 'pkgdiscountnum' ] ], + }, + 'discount' => { 'columns' => [ 'discountnum', 'serial', '', '', '', '', @@ -1586,7 +1877,8 @@ sub tables_hashref { 'percent', 'decimal', '', '7,4', '', '', 'months', 'decimal', 'NULL', '7,4', '', '', 'disabled', 'char', 'NULL', 1, '', '', - 'setup', 'char', 'NULL', 1, '', '', + 'setup', 'char', 'NULL', 1, '', '', + #'linked', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'discountnum', 'unique' => [], @@ -1609,6 +1901,11 @@ sub tables_hashref { 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '', 'closed', 'char', 'NULL', 1, '', '', + # credit card/EFT fields (formerly in paybatch) + 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK + 'processor', 'varchar', 'NULL', $char_d, '', '', # module name + 'auth', 'varchar','NULL',16, '', '', # CC auth number + 'order_number', 'varchar','NULL',$char_d, '', '', # transaction number ], 'primary_key' => 'refundnum', 'unique' => [], @@ -1631,14 +1928,15 @@ sub tables_hashref { 'cust_svc' => { 'columns' => [ - 'svcnum', 'serial', '', '', '', '', - 'pkgnum', 'int', 'NULL', '', '', '', - 'svcpart', 'int', '', '', '', '', - 'overlimit', @date_type, '', '', + 'svcnum', 'serial', '', '', '', '', + 'pkgnum', 'int', 'NULL', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'agent_svcid', 'int', 'NULL', '', '', '', + 'overlimit', @date_type, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], - 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ], + 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'], [ 'agent_svcid' ] ], }, 'cust_svc_option' => { @@ -1653,6 +1951,30 @@ sub tables_hashref { 'index' => [ [ 'svcnum' ], [ 'optionname' ] ], }, + 'svc_export_machine' => { + 'columns' => [ + 'svcexportmachinenum', 'serial', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'machinenum', 'int', '', '', '', '', + ], + 'primary_key' => 'svcexportmachinenum', + 'unique' => [ ['svcnum', 'exportnum'] ], + 'index' => [], + }, + + 'part_export_machine' => { + 'columns' => [ + 'machinenum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'machine', 'varchar', 'NULL', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'machinenum', + 'unique' => [ [ 'exportnum', 'machine' ] ], + 'index' => [ [ 'exportnum' ] ], + }, + 'part_pkg' => { 'columns' => [ 'pkgpart', 'serial', '', '', '', '', @@ -1675,9 +1997,12 @@ sub tables_hashref { 'credit_weight', 'real', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', 'fcc_ds0s', 'int', 'NULL', '', '', '', + 'fcc_voip_class','char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'recur_show_zero', 'char', 'NULL', 1, '', '', 'setup_show_zero', 'char', 'NULL', 1, '', '', + 'successor', 'int', 'NULL', '', '', '', + 'family_pkgpart','int', 'NULL', '', '', '', ], 'primary_key' => 'pkgpart', 'unique' => [], @@ -1813,7 +2138,9 @@ sub tables_hashref { 'disabled', 'char', 'NULL', 1, '', '', 'preserve', 'char', 'NULL', 1, '', '', 'selfservice_access', 'varchar', 'NULL', $char_d, '', '', - ], + 'classnum', 'int', 'NULL', '', '', '', + 'restrict_edit_password','char', 'NULL', 1, '', '', +], 'primary_key' => 'svcpart', 'unique' => [], 'index' => [ [ 'disabled' ] ], @@ -1821,18 +2148,29 @@ sub tables_hashref { 'part_svc_column' => { 'columns' => [ - 'columnnum', 'serial', '', '', '', '', - 'svcpart', 'int', '', '', '', '', - 'columnname', 'varchar', '', 64, '', '', + 'columnnum', 'serial', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'columnname', 'varchar', '', 64, '', '', 'columnlabel', 'varchar', 'NULL', $char_d, '', '', - 'columnvalue', 'varchar', 'NULL', $char_d, '', '', - 'columnflag', 'char', 'NULL', 1, '', '', + 'columnvalue', 'varchar', 'NULL', 512, '', '', + 'columnflag', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'columnnum', 'unique' => [ [ 'svcpart', 'columnname' ] ], 'index' => [ [ 'svcpart' ] ], }, + 'part_svc_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + #(this should be renamed to part_pop) 'svc_acct_pop' => { 'columns' => [ @@ -1907,6 +2245,9 @@ sub tables_hashref { 'shell', 'varchar', 'NULL', $char_d, '', '', 'quota', 'varchar', 'NULL', $char_d, '', '', 'slipip', 'varchar', 'NULL', 15, '', '', #four TINYINTs, bah. + # IP address mgmt + 'routernum', 'int', 'NULL', '', '', '', + 'blocknum', 'int', 'NULL', '', '', '', 'seconds', 'int', 'NULL', '', '', '', #uhhhh 'seconds_threshold', 'int', 'NULL', '', '', '', 'upbytes', 'bigint', 'NULL', '', '', '', @@ -1947,6 +2288,7 @@ sub tables_hashref { 'cgp_sendmdnmode', 'varchar', 'NULL', $char_d, '', '',#SendMDNMode #mail #XXX RPOP settings + # ], 'primary_key' => 'svcnum', #'unique' => [ [ 'username', 'domsvc' ] ], @@ -2371,11 +2713,11 @@ sub tables_hashref { 'part_export' => { 'columns' => [ - 'exportnum', 'serial', '', '', '', '', + 'exportnum', 'serial', '', '', '', '', 'exportname', 'varchar', 'NULL', $char_d, '', '', - 'machine', 'varchar', '', $char_d, '', '', - 'exporttype', 'varchar', '', $char_d, '', '', - 'nodomain', 'char', 'NULL', 1, '', '', + 'machine', 'varchar', 'NULL', $char_d, '', '', + 'exporttype', 'varchar', '', $char_d, '', '', + 'nodomain', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'exportnum', 'unique' => [], @@ -2412,6 +2754,8 @@ sub tables_hashref { 'groupname', 'varchar', '', $char_d, '', '', 'description', 'varchar', 'NULL', $char_d, '', '', 'priority', 'int', '', '', '1', '', + 'speed_up', 'int', 'NULL', '', '', '', + 'speed_down', 'int', 'NULL', '', '', '', ], 'primary_key' => 'groupnum', 'unique' => [ ['groupname'] ], @@ -2420,16 +2764,16 @@ sub tables_hashref { 'radius_attr' => { 'columns' => [ - 'attrnum', 'serial', '', '', '', '', - 'groupnum', 'int', '', '', '', '', + 'attrnum', 'serial', '', '', '', '', + 'groupnum', 'int', '', '', '', '', 'attrname', 'varchar', '', $char_d, '', '', - 'value', 'varchar', '', $char_d, '', '', - 'attrtype', 'char', '', 1, '', '', - 'op', 'char', '', 2, '', '', + 'value', 'varchar', '', 255, '', '', + 'attrtype', 'char', '', 1, '', '', + 'op', 'char', '', 2, '', '', ], 'primary_key' => 'attrnum', - 'unique' => [ ['groupnum','attrname'] ], #? - 'index' => [], + 'unique' => [], + 'index' => [ ['groupnum'], ], }, 'msgcat' => { @@ -2464,10 +2808,42 @@ sub tables_hashref { #'custnum', 'int', '', '', '', '' 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', - 'year', 'int', '', '', '', '', - 'month', 'int', '', '', '', '', + 'year', 'int', 'NULL', '', '', '', + 'month', 'int', 'NULL', '', '', '', 'creditbillpkgnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', + # exemption type flags + 'exempt_cust', 'char', 'NULL', 1, '', '', + 'exempt_setup', 'char', 'NULL', 1, '', '', + 'exempt_recur', 'char', 'NULL', 1, '', '', + 'exempt_cust_taxname', 'char', 'NULL', 1, '', '', + 'exempt_monthly', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'exemptpkgnum', + 'unique' => [], + 'index' => [ [ 'taxnum', 'year', 'month' ], + [ 'billpkgnum' ], + [ 'taxnum' ], + [ 'creditbillpkgnum' ], + ], + }, + + 'cust_tax_exempt_pkg_void' => { + 'columns' => [ + 'exemptpkgnum', 'int', '', '', '', '', + #'custnum', 'int', '', '', '', '' + 'billpkgnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'year', 'int', 'NULL', '', '', '', + 'month', 'int', 'NULL', '', '', '', + 'creditbillpkgnum', 'int', 'NULL', '', '', '', + 'amount', @money_type, '', '', + # exemption type flags + 'exempt_cust', 'char', 'NULL', 1, '', '', + 'exempt_setup', 'char', 'NULL', 1, '', '', + 'exempt_recur', 'char', 'NULL', 1, '', '', + 'exempt_cust_taxname', 'char', 'NULL', 1, '', '', + 'exempt_monthly', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'exemptpkgnum', 'unique' => [], @@ -2484,7 +2860,7 @@ sub tables_hashref { 'routername', 'varchar', '', $char_d, '', '', 'svcnum', 'int', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', - 'auto_addr', 'char', 'NULL', 1, '', '', + 'manual_addr', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'routernum', 'unique' => [], @@ -2536,7 +2912,7 @@ sub tables_hashref { 'plan_id', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [ [ 'mac_addr' ] ], + 'unique' => [ [ 'ip_addr' ], [ 'mac_addr' ] ], 'index' => [], }, @@ -2672,6 +3048,32 @@ sub tables_hashref { 'index' => [ [ 'disabled' ] ], }, + 'part_pkg_usage' => { + 'columns' => [ + 'pkgusagepart', 'serial', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', + 'minutes', 'int', '', '', '', '', + 'priority', 'int', 'NULL', '', '', '', + 'shared', 'char', 'NULL', 1, '', '', + 'rollover', 'char', 'NULL', 1, '', '', + 'description', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'pkgusagepart', + 'unique' => [], + 'index' => [ [ 'pkgpart' ] ], + }, + + 'part_pkg_usage_class' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'pkgusagepart', 'int', '', '', '', '', + 'classnum', 'int','NULL', '', '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'pkgusagepart', 'classnum' ] ], + 'index' => [], + }, + 'rate' => { 'columns' => [ 'ratenum', 'serial', '', '', '', '', @@ -2709,6 +3111,7 @@ sub tables_hashref { 'columns' => [ 'regionnum', 'serial', '', '', '', '', 'regionname', 'varchar', '', $char_d, '', '', + 'exact_match', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'regionnum', 'unique' => [], @@ -2938,7 +3341,11 @@ sub tables_hashref { 'channel', 'varchar', '', $char_d, \"''", '', 'dstchannel', 'varchar', '', $char_d, \"''", '', 'lastapp', 'varchar', '', $char_d, \"''", '', - 'lastdata', 'varchar', '', $char_d, \"''", '', + 'lastdata', 'varchar', '', 255, \"''", '', + + #currently only opensips + 'src_ip_addr', 'varchar', 'NULL', 15, '', '', + 'dst_ip_addr', 'varchar', 'NULL', 15, '', '', #these don't seem to be logged by most of the SQL cdr_* modules #except tds under sql-illegal names, so; @@ -2970,7 +3377,6 @@ sub tables_hashref { ### 'upstream_currency', 'char', 'NULL', 3, '', '', - 'upstream_price', 'decimal', 'NULL', '10,4', '', '', 'upstream_rateplanid', 'int', 'NULL', '', '', '', #? # how it was rated internally... @@ -2986,6 +3392,12 @@ sub tables_hashref { 'quantity', 'int', 'NULL', '', '', '', 'upstream_rateid', 'int', 'NULL', '', '', '', + + ### + # more fields, for GSM imports + ### + 'servicecode', 'int', 'NULL', '', '', '', + 'quantity_able', 'int', 'NULL', '', '', '', ### #and now for our own fields @@ -2994,6 +3406,11 @@ sub tables_hashref { 'cdrtypenum', 'int', 'NULL', '', '', '', 'charged_party', 'varchar', 'NULL', $char_d, '', '', + 'charged_party_imsi', 'varchar', 'NULL', $char_d, '', '', + + 'upstream_price', 'decimal', 'NULL', '10,5', '', '', + 'upstream_src_regionname', 'varchar', 'NULL', $char_d, '', '', + 'upstream_dst_regionname', 'varchar', 'NULL', $char_d, '', '', # how it was rated internally... 'rated_pretty_dst', 'varchar', 'NULL', $char_d, '', '', @@ -3006,7 +3423,7 @@ sub tables_hashref { 'rated_classnum', 'int', 'NULL', '', '', '', 'rated_ratename', 'varchar', 'NULL', $char_d, '', '', - 'carrierid', 'int', 'NULL', '', '', '', + 'carrierid', 'bigint', 'NULL', '', '', '', # service it was matched to 'svcnum', 'int', 'NULL', '', '', '', @@ -3039,6 +3456,7 @@ sub tables_hashref { [ 'sessionnum' ], [ 'subscriber' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], [ 'cdrbatchnum' ], + [ 'src_ip_addr' ], [ 'dst_ip_addr' ], ], }, @@ -3129,11 +3547,12 @@ sub tables_hashref { 'inventory_item' => { 'columns' => [ - 'itemnum', 'serial', '', '', '', '', - 'classnum', 'int', '', '', '', '', - 'agentnum', 'int', 'NULL', '', '', '', - 'item', 'varchar', '', $char_d, '', '', - 'svcnum', 'int', 'NULL', '', '', '', + 'itemnum', 'serial', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'item', 'varchar', '', $char_d, '', '', + 'svcnum', 'int', 'NULL', '', '', '', + 'svc_field', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'itemnum', 'unique' => [ [ 'classnum', 'item' ] ], @@ -3150,15 +3569,29 @@ sub tables_hashref { 'index' => [], }, + 'access_user_session' => { + 'columns' => [ + 'sessionnum', 'serial', '', '', '', '', + 'sessionkey', 'varchar', '', $char_d, '', '', + 'usernum', 'int', '', '', '', '', + 'start_date', @date_type, '', '', + 'last_date', @date_type, '', '', + ], + 'primary_key' => 'sessionnum', + 'unique' => [ [ 'sessionkey' ] ], + 'index' => [], + }, + 'access_user' => { 'columns' => [ - 'usernum', 'serial', '', '', '', '', - 'username', 'varchar', '', $char_d, '', '', - '_password', 'varchar', '', $char_d, '', '', - 'last', 'varchar', '', $char_d, '', '', - 'first', 'varchar', '', $char_d, '', '', - 'user_custnum', 'int', 'NULL', '', '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'usernum', 'serial', '', '', '', '', + 'username', 'varchar', '', $char_d, '', '', + '_password', 'varchar', 'NULL', $char_d, '', '', + '_password_encoding', 'varchar', 'NULL', $char_d, '', '', + 'last', 'varchar', 'NULL', $char_d, '', '', + 'first', 'varchar', 'NULL', $char_d, '', '', + 'user_custnum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'usernum', 'unique' => [ [ 'username' ] ], @@ -3210,6 +3643,17 @@ sub tables_hashref { 'index' => [ [ 'groupnum' ] ], }, + 'access_groupsales' => { + 'columns' => [ + 'groupsalesnum', 'serial', '', '', '', '', + 'groupnum', 'int', '', '', '', '', + 'salesnum', 'int', '', '', '', '', + ], + 'primary_key' => 'groupsalesnum', + 'unique' => [ [ 'groupnum', 'salesnum' ] ], + 'index' => [ [ 'groupnum' ] ], + }, + 'access_right' => { 'columns' => [ 'rightnum', 'serial', '', '', '', '', @@ -3226,7 +3670,8 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'countrycode', 'varchar', '', 3, '', '', - 'phonenum', 'varchar', '', 15, '', '', #12 ? + 'phonenum', 'varchar', '', 25, '', '', #12 ? + 'sim_imsi', 'varchar', 'NULL', 15, '', '', 'pin', 'varchar', 'NULL', $char_d, '', '', 'sip_password', 'varchar', 'NULL', $char_d, '', '', 'phone_name', 'varchar', 'NULL', $char_d, '', '', @@ -3393,6 +3838,8 @@ sub tables_hashref { 'reason_type', 'int', '', '', '', '', 'reason', 'text', '', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'unsuspend_pkgpart', 'int', 'NULL', '', '', '', + 'unsuspend_hold','char', 'NULL', 1, '', '', ], 'primary_key' => 'reasonnum', 'unique' => [], @@ -3602,6 +4049,63 @@ sub tables_hashref { 'index' => [], }, + 'upgrade_journal' => { + 'columns' => [ + 'upgradenum', 'serial', '', '', '', '', + '_date', 'int', '', '', '', '', + 'upgrade', 'varchar', '', $char_d, '', '', + 'status', 'varchar', '', $char_d, '', '', + 'statustext', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'upgradenum', + 'unique' => [ [ 'upgradenum' ] ], + 'index' => [ [ 'upgrade' ] ], + }, + + 'upload_target' => { + 'columns' => [ + 'targetnum', 'serial', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'protocol', 'varchar', '', 10, '', '', + 'hostname', 'varchar', '', $char_d, '', '', + 'port', 'int', 'NULL', '', '', '', + 'username', 'varchar', '', $char_d, '', '', + 'password', 'varchar', 'NULL', $char_d, '', '', + 'path', 'varchar', 'NULL', $char_d, '', '', + 'subject', 'varchar', 'NULL', '255', '', '', + 'handling', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'targetnum', + 'unique' => [ [ 'targetnum' ] ], + 'index' => [], + }, + + 'log' => { + 'columns' => [ + 'lognum', 'serial', '', '', '', '', + '_date', 'int', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'tablename', 'varchar', 'NULL', $char_d, '', '', + 'tablenum', 'int', 'NULL', '', '', '', + 'level', 'int', '', '', '', '', + 'message', 'text', '', '', '', '', + ], + 'primary_key' => 'lognum', + 'unique' => [], + 'index' => [ ['_date'], ['level'] ], + }, + + 'log_context' => { + 'columns' => [ + 'logcontextnum', 'serial', '', '', '', '', + 'lognum', 'int', '', '', '', '', + 'context', 'varchar', '', 32, '', '', + ], + 'primary_key' => 'logcontextnum', + 'unique' => [ [ 'lognum', 'context' ] ], + 'index' => [], + }, + %{ tables_hashref_torrus() }, # tables of ours for doing torrus virtual port combining