3 #to delay loading dbdef until we're ready
4 BEGIN { $FS::Record::setup_hack = 1; }
10 use Locale::SubCountry;
12 use DBIx::DBSchema 0.21;
13 use DBIx::DBSchema::Table;
14 use DBIx::DBSchema::Column;
15 use DBIx::DBSchema::ColGroup::Unique;
16 use DBIx::DBSchema::ColGroup::Index;
17 use FS::UID qw(adminsuidsetup datasrc checkeuid getsecrets);
19 use FS::cust_main_county;
21 use FS::part_bill_event;
23 die "Not running uid freeside!" unless checkeuid();
26 map { lc($FS::raddb::attrib{$_}) => $_ } keys %FS::raddb::attrib;
29 my $user = shift or die &usage;
32 #needs to match FS::Record
33 my($dbdef_file) = "/usr/local/etc/freeside/dbdef.". datasrc;
37 #print "\nEnter the maximum username length: ";
38 #my($username_len)=&getvalue;
39 my $username_len = 32; #usernamemax config file
41 #print "\n\n", <<END, ":";
42 #Freeside tracks the RADIUS User-Name, check attribute Password and
43 #reply attribute Framed-IP-Address for each user. You can specify additional
44 #check and reply attributes (or you can add them later with the
45 #fs-radius-add-check and fs-radius-add-reply programs).
47 #First enter any additional RADIUS check attributes you need to track for each
48 #user, separated by whitespace.
50 #my @check_attributes = map { $attrib2db{lc($_)} or die "unknown attribute $_"; }
51 # split(" ",&getvalue);
53 #print "\n\n", <<END, ":";
54 #Now enter any additional reply attributes you need to track for each user,
55 #separated by whitespace.
57 #my @attributes = map { $attrib2db{lc($_)} or die "unknown attribute $_"; }
58 # split(" ",&getvalue);
60 #print "\n\n", <<END, ":";
61 #Do you wish to enable the tracking of a second, separate shipping/service
67 # my($x)=scalar(<STDIN>);
74 # my $x = scalar(<STDIN>);
78 my @check_attributes = (); #add later
79 my @attributes = (); #add later
84 my($char_d) = 80; #default maxlength for text fields
86 #my(@date_type) = ( 'timestamp', '', '' );
87 my(@date_type) = ( 'int', 'NULL', '' );
88 my(@perl_type) = ( 'text', 'NULL', '' );
89 my @money_type = ( 'decimal', '', '10,2' );
92 # create a dbdef object from the old data structure
95 my(%tables)=&tables_hash_hack;
98 my($dbdef) = new DBIx::DBSchema ( map {
100 while (@{$tables{$_}{'columns'}}) {
101 my($name,$type,$null,$length)=splice @{$tables{$_}{'columns'}}, 0, 4;
102 push @columns, new DBIx::DBSchema::Column ( $name,$type,$null,$length );
104 DBIx::DBSchema::Table->new(
106 $tables{$_}{'primary_key'},
107 DBIx::DBSchema::ColGroup::Unique->new($tables{$_}{'unique'}),
108 DBIx::DBSchema::ColGroup::Index->new($tables{$_}{'index'}),
113 my $cust_main = $dbdef->table('cust_main');
114 unless ($ship) { #remove ship_ from cust_main
115 $cust_main->delcolumn($_) foreach ( grep /^ship_/, $cust_main->columns );
116 } else { #add indices
117 push @{$cust_main->index->lol_ref},
118 map { [ "ship_$_" ] } qw( last company daytime night fax );
121 #add radius attributes to svc_acct
123 my($svc_acct)=$dbdef->table('svc_acct');
126 foreach $attribute (@attributes) {
127 $svc_acct->addcolumn ( new DBIx::DBSchema::Column (
128 'radius_'. $attribute,
135 foreach $attribute (@check_attributes) {
136 $svc_acct->addcolumn( new DBIx::DBSchema::Column (
144 #create history tables (false laziness w/create-history-tables)
146 grep { ! /^clientapi_session/ }
150 my $tableobj = $dbdef->table($table)
151 or die "unknown table $table";
153 die "unique->lol_ref undefined for $table"
154 unless defined $tableobj->unique->lol_ref;
155 die "index->lol_ref undefined for $table"
156 unless defined $tableobj->index->lol_ref;
158 my $h_tableobj = DBIx::DBSchema::Table->new( {
160 primary_key => 'historynum',
161 unique => DBIx::DBSchema::ColGroup::Unique->new( [] ),
162 'index' => DBIx::DBSchema::ColGroup::Index->new( [
163 @{$tableobj->unique->lol_ref},
164 @{$tableobj->index->lol_ref}
167 DBIx::DBSchema::Column->new( {
168 'name' => 'historynum',
170 'null' => 'NOT NULL',
175 DBIx::DBSchema::Column->new( {
176 'name' => 'history_date',
183 DBIx::DBSchema::Column->new( {
184 'name' => 'history_user',
186 'null' => 'NOT NULL',
191 DBIx::DBSchema::Column->new( {
192 'name' => 'history_action',
194 'null' => 'NOT NULL',
200 my $column = $tableobj->column($_);
202 #clone so as to not disturb the original
203 $column = DBIx::DBSchema::Column->new( {
204 map { $_ => $column->$_() }
205 qw( name type null length default local )
209 if $column->type eq 'serial';
210 #$column->default('')
211 # if $column->default =~ /^nextval\(/i;
212 #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
213 #$column->local($local);
218 $dbdef->addtable($h_tableobj);
222 $dbdef->save($dbdef_file);
223 &FS::Record::reload_dbdef($dbdef_file);
229 my($dbh)=adminsuidsetup $user;
234 foreach my $statement ( $dbdef->sql($dbh) ) {
235 $dbh->do( $statement )
236 or die "CREATE error: ". $dbh->errstr. "\ndoing statement: $statement";
240 foreach my $country ( sort map uc($_), all_country_codes ) {
242 my $subcountry = eval { new Locale::SubCountry($country) };
243 my @states = $subcountry ? $subcountry->all_codes : undef;
245 if ( !scalar(@states) || ( scalar(@states) == 1 && !defined($states[0]) ) ) {
247 my $cust_main_county = new FS::cust_main_county({
249 'country' => $country,
251 my $error = $cust_main_county->insert;
252 die $error if $error;
256 if ( $states[0] =~ /^(\d+|\w)$/ ) {
257 @states = map $subcountry->full_name($_), @states
260 foreach my $state ( @states ) {
262 my $cust_main_county = new FS::cust_main_county({
265 'country' => $country,
267 my $error = $cust_main_county->insert;
268 die $error if $error;
277 #[ 'COMP', 'Comp invoice', '$cust_bill->comp();', 30, 'comp' ],
278 [ 'CARD', 'Batch card', '$cust_bill->batch_card();', 40, 'batch-card' ],
279 [ 'BILL', 'Send invoice', '$cust_bill->send();', 50, 'send' ],
280 [ 'DCRD', 'Send invoice', '$cust_bill->send();', 50, 'send' ],
281 [ 'DCHK', 'Send invoice', '$cust_bill->send();', 50, 'send' ],
284 my $part_bill_event = new FS::part_bill_event({
285 'payby' => $aref->[0],
286 'event' => $aref->[1],
287 'eventcode' => $aref->[2],
289 'weight' => $aref->[3],
290 'plan' => $aref->[4],
293 $error=$part_bill_event->insert;
294 die $error if $error;
298 $dbh->commit or die $dbh->errstr;
299 $dbh->disconnect or die $dbh->errstr;
301 #print "Freeside database initialized sucessfully\n";
304 die "Usage:\n freeside-setup [ -s ] user\n";
308 # Now it becomes an object. much better.
310 sub tables_hash_hack {
312 #note that s/(date|change)/_$1/; to avoid keyword conflict.
313 #put a kludge in FS::Record to catch this or? (pry need some date-handling
316 my(%tables)=( #yech.}
320 'agentnum', 'serial', '', '',
321 'agent', 'varchar', '', $char_d,
322 'typenum', 'int', '', '',
323 'freq', 'int', 'NULL', '',
325 'disabled', 'char', 'NULL', 1,
326 'username', 'varchar', 'NULL', $char_d,
327 '_password','varchar', 'NULL', $char_d,
329 'primary_key' => 'agentnum',
331 'index' => [ ['typenum'], ['disabled'] ],
336 'typenum', 'serial', '', '',
337 'atype', 'varchar', '', $char_d,
339 'primary_key' => 'typenum',
346 'typenum', 'int', '', '',
347 'pkgpart', 'int', '', '',
350 'unique' => [ ['typenum', 'pkgpart'] ],
351 'index' => [ ['typenum'] ],
356 'invnum', 'serial', '', '',
357 'custnum', 'int', '', '',
359 'charged', @money_type,
360 'printed', 'int', '', '',
361 'closed', 'char', 'NULL', 1,
363 'primary_key' => 'invnum',
365 'index' => [ ['custnum'], ['_date'] ],
368 'cust_bill_event' => {
370 'eventnum', 'serial', '', '',
371 'invnum', 'int', '', '',
372 'eventpart', 'int', '', '',
374 'status', 'varchar', '', $char_d,
375 'statustext', 'text', 'NULL', '',
377 'primary_key' => 'eventnum',
378 #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
380 'index' => [ ['invnum'], ['status'] ],
383 'part_bill_event' => {
385 'eventpart', 'serial', '', '',
386 'payby', 'char', '', 4,
387 'event', 'varchar', '', $char_d,
388 'eventcode', @perl_type,
389 'seconds', 'int', 'NULL', '',
390 'weight', 'int', '', '',
391 'plan', 'varchar', 'NULL', $char_d,
392 'plandata', 'text', 'NULL', '',
393 'disabled', 'char', 'NULL', 1,
395 'primary_key' => 'eventpart',
397 'index' => [ ['payby'], ['disabled'], ],
402 'pkgnum', 'int', '', '',
403 'invnum', 'int', '', '',
404 'setup', @money_type,
405 'recur', @money_type,
408 'itemdesc', 'varchar', 'NULL', $char_d,
412 'index' => [ ['invnum'] ],
415 'cust_bill_pkg_detail' => {
417 'detailnum', 'serial', '', '',
418 'pkgnum', 'int', '', '',
419 'invnum', 'int', '', '',
420 'detail', 'varchar', '', $char_d,
422 'primary_key' => 'detailnum',
424 'index' => [ [ 'pkgnum', 'invnum' ] ],
429 'crednum', 'serial', '', '',
430 'custnum', 'int', '', '',
432 'amount', @money_type,
433 'otaker', 'varchar', '', 32,
434 'reason', 'text', 'NULL', '',
435 'closed', 'char', 'NULL', 1,
437 'primary_key' => 'crednum',
439 'index' => [ ['custnum'] ],
442 'cust_credit_bill' => {
444 'creditbillnum', 'serial', '', '',
445 'crednum', 'int', '', '',
446 'invnum', 'int', '', '',
448 'amount', @money_type,
450 'primary_key' => 'creditbillnum',
452 'index' => [ ['crednum'], ['invnum'] ],
457 'custnum', 'serial', '', '',
458 'agentnum', 'int', '', '',
459 # 'titlenum', 'int', 'NULL', '',
460 'last', 'varchar', '', $char_d,
461 # 'middle', 'varchar', 'NULL', $char_d,
462 'first', 'varchar', '', $char_d,
463 'ss', 'varchar', 'NULL', 11,
464 'company', 'varchar', 'NULL', $char_d,
465 'address1', 'varchar', '', $char_d,
466 'address2', 'varchar', 'NULL', $char_d,
467 'city', 'varchar', '', $char_d,
468 'county', 'varchar', 'NULL', $char_d,
469 'state', 'varchar', 'NULL', $char_d,
470 'zip', 'varchar', 'NULL', 10,
471 'country', 'char', '', 2,
472 'daytime', 'varchar', 'NULL', 20,
473 'night', 'varchar', 'NULL', 20,
474 'fax', 'varchar', 'NULL', 12,
475 'ship_last', 'varchar', 'NULL', $char_d,
476 # 'ship_middle', 'varchar', 'NULL', $char_d,
477 'ship_first', 'varchar', 'NULL', $char_d,
478 'ship_company', 'varchar', 'NULL', $char_d,
479 'ship_address1', 'varchar', 'NULL', $char_d,
480 'ship_address2', 'varchar', 'NULL', $char_d,
481 'ship_city', 'varchar', 'NULL', $char_d,
482 'ship_county', 'varchar', 'NULL', $char_d,
483 'ship_state', 'varchar', 'NULL', $char_d,
484 'ship_zip', 'varchar', 'NULL', 10,
485 'ship_country', 'char', 'NULL', 2,
486 'ship_daytime', 'varchar', 'NULL', 20,
487 'ship_night', 'varchar', 'NULL', 20,
488 'ship_fax', 'varchar', 'NULL', 12,
489 'payby', 'char', '', 4,
490 'payinfo', 'varchar', 'NULL', $char_d,
491 'paycvv', 'varchar', 'NULL', 4,
492 #'paydate', @date_type,
493 'paydate', 'varchar', 'NULL', 10,
494 'payname', 'varchar', 'NULL', $char_d,
495 'tax', 'char', 'NULL', 1,
496 'otaker', 'varchar', '', 32,
497 'refnum', 'int', '', '',
498 'referral_custnum', 'int', 'NULL', '',
499 'comments', 'text', 'NULL', '',
501 'primary_key' => 'custnum',
503 #'index' => [ ['last'], ['company'] ],
504 'index' => [ ['last'], [ 'company' ], [ 'referral_custnum' ],
505 [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'refnum' ],
509 'cust_main_invoice' => {
511 'destnum', 'serial', '', '',
512 'custnum', 'int', '', '',
513 'dest', 'varchar', '', $char_d,
515 'primary_key' => 'destnum',
517 'index' => [ ['custnum'], ],
520 'cust_main_county' => { #county+state+country are checked off the
521 #cust_main_county for validation and to provide
524 'taxnum', 'serial', '', '',
525 'state', 'varchar', 'NULL', $char_d,
526 'county', 'varchar', 'NULL', $char_d,
527 'country', 'char', '', 2,
528 'taxclass', 'varchar', 'NULL', $char_d,
529 'exempt_amount', @money_type,
530 'tax', 'real', '', '', #tax %
531 'taxname', 'varchar', 'NULL', $char_d,
532 'setuptax', 'char', 'NULL', 1, # Y = setup tax exempt
533 'recurtax', 'char', 'NULL', 1, # Y = recur tax exempt
535 'primary_key' => 'taxnum',
537 # 'unique' => [ ['taxnum'], ['state', 'county'] ],
543 'paynum', 'serial', '', '',
544 #now cust_bill_pay #'invnum', 'int', '', '',
545 'custnum', 'int', '', '',
548 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
549 # payment type table.
550 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
551 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
552 'closed', 'char', 'NULL', 1,
554 'primary_key' => 'paynum',
556 'index' => [ [ 'custnum' ], [ 'paybatch' ], [ 'payby' ], [ '_date' ] ],
561 'paynum', 'int', '', '',
562 'custnum', 'int', '', '',
565 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
566 # payment type table.
567 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
568 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
569 'closed', 'char', 'NULL', 1,
570 'void_date', @date_type,
571 'reason', 'varchar', 'NULL', $char_d,
572 'otaker', 'varchar', '', 32,
574 'primary_key' => 'paynum',
576 'index' => [ [ 'custnum' ] ],
581 'billpaynum', 'serial', '', '',
582 'invnum', 'int', '', '',
583 'paynum', 'int', '', '',
584 'amount', @money_type,
587 'primary_key' => 'billpaynum',
589 'index' => [ [ 'paynum' ], [ 'invnum' ] ],
592 'cust_pay_batch' => { #what's this used for again? list of customers
593 #in current CARD batch? (necessarily CARD?)
595 'paybatchnum', 'serial', '', '',
596 'invnum', 'int', '', '',
597 'custnum', 'int', '', '',
598 'last', 'varchar', '', $char_d,
599 'first', 'varchar', '', $char_d,
600 'address1', 'varchar', '', $char_d,
601 'address2', 'varchar', 'NULL', $char_d,
602 'city', 'varchar', '', $char_d,
603 'state', 'varchar', 'NULL', $char_d,
604 'zip', 'varchar', 'NULL', 10,
605 'country', 'char', '', 2,
606 # 'trancode', 'int', '', '',
607 'cardnum', 'varchar', '', 16,
609 'exp', 'varchar', '', 11,
610 'payname', 'varchar', 'NULL', $char_d,
611 'amount', @money_type,
613 'primary_key' => 'paybatchnum',
615 'index' => [ ['invnum'], ['custnum'] ],
620 'pkgnum', 'serial', '', '',
621 'custnum', 'int', '', '',
622 'pkgpart', 'int', '', '',
623 'otaker', 'varchar', '', 32,
626 'last_bill', @date_type,
628 'cancel', @date_type,
629 'expire', @date_type,
630 'manual_flag', 'char', 'NULL', 1,
632 'primary_key' => 'pkgnum',
634 'index' => [ ['custnum'] ],
639 'refundnum', 'serial', '', '',
640 #now cust_credit_refund #'crednum', 'int', '', '',
641 'custnum', 'int', '', '',
643 'refund', @money_type,
644 'otaker', 'varchar', '', 32,
645 'reason', 'varchar', '', $char_d,
646 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index
647 # into payment type table.
648 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
649 'paybatch', 'varchar', 'NULL', $char_d,
650 'closed', 'char', 'NULL', 1,
652 'primary_key' => 'refundnum',
657 'cust_credit_refund' => {
659 'creditrefundnum', 'serial', '', '',
660 'crednum', 'int', '', '',
661 'refundnum', 'int', '', '',
662 'amount', @money_type,
665 'primary_key' => 'creditrefundnum',
667 'index' => [ [ 'crednum', 'refundnum' ] ],
673 'svcnum', 'serial', '', '',
674 'pkgnum', 'int', 'NULL', '',
675 'svcpart', 'int', '', '',
677 'primary_key' => 'svcnum',
679 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ],
684 'pkgpart', 'serial', '', '',
685 'pkg', 'varchar', '', $char_d,
686 'comment', 'varchar', '', $char_d,
687 'promo_code', 'varchar', 'NULL', $char_d,
689 'freq', 'varchar', '', $char_d, #billing frequency
691 'setuptax', 'char', 'NULL', 1,
692 'recurtax', 'char', 'NULL', 1,
693 'plan', 'varchar', 'NULL', $char_d,
694 'plandata', 'text', 'NULL', '',
695 'disabled', 'char', 'NULL', 1,
696 'taxclass', 'varchar', 'NULL', $char_d,
698 'primary_key' => 'pkgpart',
700 'index' => [ [ 'promo_code' ], [ 'disabled' ] ],
705 # 'titlenum', 'int', '', '',
706 # 'title', 'varchar', '', $char_d,
708 # 'primary_key' => 'titlenum',
709 # 'unique' => [ [] ],
715 'pkgpart', 'int', '', '',
716 'svcpart', 'int', '', '',
717 'quantity', 'int', '', '',
718 'primary_svc','char', 'NULL', 1,
721 'unique' => [ ['pkgpart', 'svcpart'] ],
722 'index' => [ ['pkgpart'] ],
727 'refnum', 'serial', '', '',
728 'referral', 'varchar', '', $char_d,
729 'disabled', 'char', 'NULL', 1,
731 'primary_key' => 'refnum',
733 'index' => [ ['disabled'] ],
738 'svcpart', 'serial', '', '',
739 'svc', 'varchar', '', $char_d,
740 'svcdb', 'varchar', '', $char_d,
741 'disabled', 'char', 'NULL', 1,
743 'primary_key' => 'svcpart',
745 'index' => [ [ 'disabled' ] ],
748 'part_svc_column' => {
750 'columnnum', 'serial', '', '',
751 'svcpart', 'int', '', '',
752 'columnname', 'varchar', '', 64,
753 'columnvalue', 'varchar', 'NULL', $char_d,
754 'columnflag', 'char', 'NULL', 1,
756 'primary_key' => 'columnnum',
757 'unique' => [ [ 'svcpart', 'columnname' ] ],
758 'index' => [ [ 'svcpart' ] ],
761 #(this should be renamed to part_pop)
764 'popnum', 'serial', '', '',
765 'city', 'varchar', '', $char_d,
766 'state', 'varchar', '', $char_d,
768 'exch', 'char', '', 3,
769 'loc', 'char', 'NULL', 4, #NULL for legacy purposes
771 'primary_key' => 'popnum',
773 'index' => [ [ 'state' ] ],
776 'part_pop_local' => {
778 'localnum', 'serial', '', '',
779 'popnum', 'int', '', '',
780 'city', 'varchar', 'NULL', $char_d,
781 'state', 'char', 'NULL', 2,
782 'npa', 'char', '', 3,
783 'nxx', 'char', '', 3,
785 'primary_key' => 'localnum',
787 'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ],
792 'svcnum', 'int', '', '',
793 'username', 'varchar', '', $username_len, #unique (& remove dup code)
794 '_password', 'varchar', '', 72, #13 for encryped pw's plus ' *SUSPENDED* (md5 passwords can be 34, blowfish 60)
795 'sec_phrase', 'varchar', 'NULL', $char_d,
796 'popnum', 'int', 'NULL', '',
797 'uid', 'int', 'NULL', '',
798 'gid', 'int', 'NULL', '',
799 'finger', 'varchar', 'NULL', $char_d,
800 'dir', 'varchar', 'NULL', $char_d,
801 'shell', 'varchar', 'NULL', $char_d,
802 'quota', 'varchar', 'NULL', $char_d,
803 'slipip', 'varchar', 'NULL', 15, #four TINYINTs, bah.
804 'seconds', 'int', 'NULL', '', #uhhhh
805 'domsvc', 'int', '', '',
807 'primary_key' => 'svcnum',
808 #'unique' => [ [ 'username', 'domsvc' ] ],
810 'index' => [ ['username'], ['domsvc'] ],
815 # 'svcnum', 'int', '', '',
816 # 'amount', @money_type,
818 # 'primary_key' => 'svcnum',
819 # 'unique' => [ [] ],
825 'svcnum', 'int', '', '',
826 'domain', 'varchar', '', $char_d,
827 'catchall', 'int', 'NULL', '',
829 'primary_key' => 'svcnum',
830 'unique' => [ ['domain'] ],
836 'recnum', 'serial', '', '',
837 'svcnum', 'int', '', '',
838 #'reczone', 'varchar', '', $char_d,
839 'reczone', 'varchar', '', 255,
840 'recaf', 'char', '', 2,
841 'rectype', 'varchar', '', 5,
842 #'recdata', 'varchar', '', $char_d,
843 'recdata', 'varchar', '', 255,
845 'primary_key' => 'recnum',
847 'index' => [ ['svcnum'] ],
852 'svcnum', 'int', '', '',
853 'srcsvc', 'int', 'NULL', '',
854 'src', 'varchar', 'NULL', 255,
855 'dstsvc', 'int', 'NULL', '',
856 'dst', 'varchar', 'NULL', 255,
858 'primary_key' => 'svcnum',
860 'index' => [ ['srcsvc'], ['dstsvc'] ],
865 'svcnum', 'int', '', '',
866 'recnum', 'int', '', '',
867 'usersvc', 'int', '', '',
869 'primary_key' => 'svcnum',
876 # 'svcnum', 'int', '', '',
877 # 'svcnum', 'int', '', '',
878 # 'svcnum', 'int', '', '',
879 # 'worker', 'varchar', '', $char_d,
880 # '_date', @date_type,
882 # 'primary_key' => 'svcnum',
883 # 'unique' => [ [] ],
889 'prepaynum', 'serial', '', '',
890 'identifier', 'varchar', '', $char_d,
891 'amount', @money_type,
892 'seconds', 'int', 'NULL', '',
893 'agentnum', 'int', 'NULL', '',
895 'primary_key' => 'prepaynum',
896 'unique' => [ ['identifier'] ],
902 'portnum', 'serial', '', '',
903 'ip', 'varchar', 'NULL', 15,
904 'nasport', 'int', 'NULL', '',
905 'nasnum', 'int', '', '',
907 'primary_key' => 'portnum',
914 'nasnum', 'serial', '', '',
915 'nas', 'varchar', '', $char_d,
916 'nasip', 'varchar', '', 15,
917 'nasfqdn', 'varchar', '', $char_d,
918 'last', 'int', '', '',
920 'primary_key' => 'nasnum',
921 'unique' => [ [ 'nas' ], [ 'nasip' ] ],
922 'index' => [ [ 'last' ] ],
927 'sessionnum', 'serial', '', '',
928 'portnum', 'int', '', '',
929 'svcnum', 'int', '', '',
931 'logout', @date_type,
933 'primary_key' => 'sessionnum',
935 'index' => [ [ 'portnum' ] ],
940 'jobnum', 'serial', '', '',
941 'job', 'text', '', '',
942 '_date', 'int', '', '',
943 'status', 'varchar', '', $char_d,
944 'statustext', 'text', 'NULL', '',
945 'svcnum', 'int', 'NULL', '',
947 'primary_key' => 'jobnum',
949 'index' => [ [ 'svcnum' ], [ 'status' ] ],
954 'argnum', 'serial', '', '',
955 'jobnum', 'int', '', '',
956 'arg', 'text', 'NULL', '',
958 'primary_key' => 'argnum',
960 'index' => [ [ 'jobnum' ] ],
965 'dependnum', 'serial', '', '',
966 'jobnum', 'int', '', '',
967 'depend_jobnum', 'int', '', '',
969 'primary_key' => 'dependnum',
971 'index' => [ [ 'jobnum' ], [ 'depend_jobnum' ] ],
976 'exportsvcnum' => 'serial', '', '',
977 'exportnum' => 'int', '', '',
978 'svcpart' => 'int', '', '',
980 'primary_key' => 'exportsvcnum',
981 'unique' => [ [ 'exportnum', 'svcpart' ] ],
982 'index' => [ [ 'exportnum' ], [ 'svcpart' ] ],
987 'exportnum', 'serial', '', '',
988 #'svcpart', 'int', '', '',
989 'machine', 'varchar', '', $char_d,
990 'exporttype', 'varchar', '', $char_d,
991 'nodomain', 'char', 'NULL', 1,
993 'primary_key' => 'exportnum',
995 'index' => [ [ 'machine' ], [ 'exporttype' ] ],
998 'part_export_option' => {
1000 'optionnum', 'serial', '', '',
1001 'exportnum', 'int', '', '',
1002 'optionname', 'varchar', '', $char_d,
1003 'optionvalue', 'text', 'NULL', '',
1005 'primary_key' => 'optionnum',
1007 'index' => [ [ 'exportnum' ], [ 'optionname' ] ],
1010 'radius_usergroup' => {
1012 'usergroupnum', 'serial', '', '',
1013 'svcnum', 'int', '', '',
1014 'groupname', 'varchar', '', $char_d,
1016 'primary_key' => 'usergroupnum',
1018 'index' => [ [ 'svcnum' ], [ 'groupname' ] ],
1023 'msgnum', 'serial', '', '',
1024 'msgcode', 'varchar', '', $char_d,
1025 'locale', 'varchar', '', 16,
1026 'msg', 'text', '', '',
1028 'primary_key' => 'msgnum',
1029 'unique' => [ [ 'msgcode', 'locale' ] ],
1033 'cust_tax_exempt' => {
1035 'exemptnum', 'serial', '', '',
1036 'custnum', 'int', '', '',
1037 'taxnum', 'int', '', '',
1038 'year', 'int', '', '',
1039 'month', 'int', '', '',
1040 'amount', @money_type,
1042 'primary_key' => 'exemptnum',
1043 'unique' => [ [ 'custnum', 'taxnum', 'year', 'month' ] ],
1049 'routernum', 'serial', '', '',
1050 'routername', 'varchar', '', $char_d,
1051 'svcnum', 'int', 'NULL', '',
1053 'primary_key' => 'routernum',
1058 'part_svc_router' => {
1060 'svcpart', 'int', '', '',
1061 'routernum', 'int', '', '',
1063 'primary_key' => '',
1070 'blocknum', 'serial', '', '',
1071 'routernum', 'int', '', '',
1072 'ip_gateway', 'varchar', '', 15,
1073 'ip_netmask', 'int', '', '',
1075 'primary_key' => 'blocknum',
1076 'unique' => [ [ 'blocknum', 'routernum' ] ],
1080 'svc_broadband' => {
1082 'svcnum', 'int', '', '',
1083 'blocknum', 'int', '', '',
1084 'speed_up', 'int', '', '',
1085 'speed_down', 'int', '', '',
1086 'ip_addr', 'varchar', '', 15,
1088 'primary_key' => 'svcnum',
1093 'part_virtual_field' => {
1095 'vfieldpart', 'int', '', '',
1096 'dbtable', 'varchar', '', 32,
1097 'name', 'varchar', '', 32,
1098 'check_block', 'text', 'NULL', '',
1099 'length', 'int', 'NULL', '',
1100 'list_source', 'text', 'NULL', '',
1101 'label', 'varchar', 'NULL', 80,
1103 'primary_key' => 'vfieldpart',
1108 'virtual_field' => {
1110 'recnum', 'int', '', '',
1111 'vfieldpart', 'int', '', '',
1112 'value', 'varchar', '', 128,
1114 'primary_key' => '',
1115 'unique' => [ [ 'vfieldpart', 'recnum' ] ],
1121 'snarfnum', 'int', '', '',
1122 'svcnum', 'int', '', '',
1123 'machine', 'varchar', '', 255,
1124 'protocol', 'varchar', '', $char_d,
1125 'username', 'varchar', '', $char_d,
1126 '_password', 'varchar', '', $char_d,
1128 'primary_key' => 'snarfnum',
1130 'index' => [ [ 'svcnum' ] ],
1135 'svcnum', 'int', '', '',
1136 'id', 'int', 'NULL', '',
1137 'title', 'varchar', 'NULL', $char_d,
1139 'primary_key' => 'svcnum',
1144 'cust_pay_refund' => {
1146 'payrefundnum', 'serial', '', '',
1147 'paynum', 'int', '', '',
1148 'refundnum', 'int', '', '',
1149 '_date', @date_type,
1150 'amount', @money_type,
1152 'primary_key' => 'payrefundnum',
1154 'index' => [ ['paynum'], ['refundnum'] ],
1157 'part_pkg_option' => {
1159 'optionnum', 'serial', '', '',
1160 'pkgpart', 'int', '', '',
1161 'optionname', 'varchar', '', $char_d,
1162 'optionvalue', 'text', 'NULL', '',
1164 'primary_key' => 'optionnum',
1166 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ],
1171 'ratenum', 'serial', '', '',
1172 'ratename', 'varchar', '', $char_d,
1174 'primary_key' => 'ratenum',
1181 'ratenum', 'int', '', '',
1182 'orig_regionnum', 'int', 'NULL', '',
1183 'dest_regionnum', 'int', '', '',
1184 'min_included', 'int', '', '',
1185 'min_charge', @money_type,
1186 'sec_granularity', 'int', '', '',
1187 #time period (link to table of periods)?
1189 'primary_key' => '',
1190 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
1196 'regionnum', 'serial', '', '',
1197 'regionname', 'varchar', '', $char_d,
1199 'primary_key' => 'regionnum',
1206 'prefixnum', 'serial', '', '',
1207 'regionnum', 'int', '', '',,
1208 'countrycode', 'varchar', '', 3,
1209 'npa', 'varchar', 'NULL', 6,
1210 'nxx', 'varchar', 'NULL', 3,
1212 'primary_key' => 'prefixnum',
1214 'index' => [ [ 'countrycode' ], [ 'regionnum' ] ],
1219 'codenum', 'serial', '', '',
1220 'code', 'varchar', '', $char_d,
1221 'agentnum', 'int', '', '',
1223 'primary_key' => 'codenum',
1224 'unique' => [ [ 'agentnum', 'code' ] ],
1225 'index' => [ [ 'agentnum' ] ],
1230 'codenum', 'int', '', '',
1231 'pkgpart', 'int', '', '',
1233 'primary_key' => '',
1234 'unique' => [ [ 'codenum', 'pkgpart' ] ],
1235 'index' => [ [ 'codenum' ] ],
1238 'clientapi_session' => {
1240 'sessionnum', 'serial', '', '',
1241 'sessionid', 'varchar', '', $char_d,
1242 'namespace', 'varchar', '', $char_d,
1244 'primary_key' => 'sessionnum',
1245 'unique' => [ [ 'sessionid', 'namespace' ] ],
1249 'clientapi_session_field' => {
1251 'fieldnum', 'serial', '', '',
1252 'sessionnum', 'int', '', '',
1253 'fieldname', 'varchar', '', $char_d,
1254 'fieldvalue', 'text', 'NULL', '',
1256 'primary_key' => 'fieldnum',
1257 'unique' => [ [ 'sessionnum', 'fieldname' ] ],