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', 512,
491 'paycvv', 'varchar', 'NULL', 512,
492 'paymask', 'varchar', 'NULL', $char_d,
493 #'paydate', @date_type,
494 'paydate', 'varchar', 'NULL', 10,
495 'payname', 'varchar', 'NULL', $char_d,
496 'tax', 'char', 'NULL', 1,
497 'otaker', 'varchar', '', 32,
498 'refnum', 'int', '', '',
499 'referral_custnum', 'int', 'NULL', '',
500 'comments', 'text', 'NULL', '',
502 'primary_key' => 'custnum',
504 #'index' => [ ['last'], ['company'] ],
505 'index' => [ ['last'], [ 'company' ], [ 'referral_custnum' ],
506 [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'refnum' ],
510 'cust_main_invoice' => {
512 'destnum', 'serial', '', '',
513 'custnum', 'int', '', '',
514 'dest', 'varchar', '', $char_d,
516 'primary_key' => 'destnum',
518 'index' => [ ['custnum'], ],
521 'cust_main_county' => { #county+state+country are checked off the
522 #cust_main_county for validation and to provide
525 'taxnum', 'serial', '', '',
526 'state', 'varchar', 'NULL', $char_d,
527 'county', 'varchar', 'NULL', $char_d,
528 'country', 'char', '', 2,
529 'taxclass', 'varchar', 'NULL', $char_d,
530 'exempt_amount', @money_type,
531 'tax', 'real', '', '', #tax %
532 'taxname', 'varchar', 'NULL', $char_d,
533 'setuptax', 'char', 'NULL', 1, # Y = setup tax exempt
534 'recurtax', 'char', 'NULL', 1, # Y = recur tax exempt
536 'primary_key' => 'taxnum',
538 # 'unique' => [ ['taxnum'], ['state', 'county'] ],
544 'paynum', 'serial', '', '',
545 #now cust_bill_pay #'invnum', 'int', '', '',
546 'custnum', 'int', '', '',
549 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
550 # payment type table.
551 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
552 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
553 'closed', 'char', 'NULL', 1,
555 'primary_key' => 'paynum',
557 'index' => [ [ 'custnum' ], [ 'paybatch' ], [ 'payby' ], [ '_date' ] ],
562 'paynum', 'int', '', '',
563 'custnum', 'int', '', '',
566 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
567 # payment type table.
568 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
569 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
570 'closed', 'char', 'NULL', 1,
571 'void_date', @date_type,
572 'reason', 'varchar', 'NULL', $char_d,
573 'otaker', 'varchar', '', 32,
575 'primary_key' => 'paynum',
577 'index' => [ [ 'custnum' ] ],
582 'billpaynum', 'serial', '', '',
583 'invnum', 'int', '', '',
584 'paynum', 'int', '', '',
585 'amount', @money_type,
588 'primary_key' => 'billpaynum',
590 'index' => [ [ 'paynum' ], [ 'invnum' ] ],
593 'cust_pay_batch' => { #what's this used for again? list of customers
594 #in current CARD batch? (necessarily CARD?)
596 'paybatchnum', 'serial', '', '',
597 'invnum', 'int', '', '',
598 'custnum', 'int', '', '',
599 'last', 'varchar', '', $char_d,
600 'first', 'varchar', '', $char_d,
601 'address1', 'varchar', '', $char_d,
602 'address2', 'varchar', 'NULL', $char_d,
603 'city', 'varchar', '', $char_d,
604 'state', 'varchar', 'NULL', $char_d,
605 'zip', 'varchar', 'NULL', 10,
606 'country', 'char', '', 2,
607 # 'trancode', 'int', '', '',
608 'cardnum', 'varchar', '', 16,
610 'exp', 'varchar', '', 11,
611 'payname', 'varchar', 'NULL', $char_d,
612 'amount', @money_type,
614 'primary_key' => 'paybatchnum',
616 'index' => [ ['invnum'], ['custnum'] ],
621 'pkgnum', 'serial', '', '',
622 'custnum', 'int', '', '',
623 'pkgpart', 'int', '', '',
624 'otaker', 'varchar', '', 32,
627 'last_bill', @date_type,
629 'cancel', @date_type,
630 'expire', @date_type,
631 'manual_flag', 'char', 'NULL', 1,
633 'primary_key' => 'pkgnum',
635 'index' => [ ['custnum'] ],
640 'refundnum', 'serial', '', '',
641 #now cust_credit_refund #'crednum', 'int', '', '',
642 'custnum', 'int', '', '',
644 'refund', @money_type,
645 'otaker', 'varchar', '', 32,
646 'reason', 'varchar', '', $char_d,
647 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index
648 # into payment type table.
649 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
650 'paybatch', 'varchar', 'NULL', $char_d,
651 'closed', 'char', 'NULL', 1,
653 'primary_key' => 'refundnum',
658 'cust_credit_refund' => {
660 'creditrefundnum', 'serial', '', '',
661 'crednum', 'int', '', '',
662 'refundnum', 'int', '', '',
663 'amount', @money_type,
666 'primary_key' => 'creditrefundnum',
668 'index' => [ [ 'crednum', 'refundnum' ] ],
674 'svcnum', 'serial', '', '',
675 'pkgnum', 'int', 'NULL', '',
676 'svcpart', 'int', '', '',
678 'primary_key' => 'svcnum',
680 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ],
685 'pkgpart', 'serial', '', '',
686 'pkg', 'varchar', '', $char_d,
687 'comment', 'varchar', '', $char_d,
688 'promo_code', 'varchar', 'NULL', $char_d,
690 'freq', 'varchar', '', $char_d, #billing frequency
692 'setuptax', 'char', 'NULL', 1,
693 'recurtax', 'char', 'NULL', 1,
694 'plan', 'varchar', 'NULL', $char_d,
695 'plandata', 'text', 'NULL', '',
696 'disabled', 'char', 'NULL', 1,
697 'taxclass', 'varchar', 'NULL', $char_d,
699 'primary_key' => 'pkgpart',
701 'index' => [ [ 'promo_code' ], [ 'disabled' ] ],
706 # 'titlenum', 'int', '', '',
707 # 'title', 'varchar', '', $char_d,
709 # 'primary_key' => 'titlenum',
710 # 'unique' => [ [] ],
716 'pkgpart', 'int', '', '',
717 'svcpart', 'int', '', '',
718 'quantity', 'int', '', '',
719 'primary_svc','char', 'NULL', 1,
722 'unique' => [ ['pkgpart', 'svcpart'] ],
723 'index' => [ ['pkgpart'] ],
728 'refnum', 'serial', '', '',
729 'referral', 'varchar', '', $char_d,
730 'disabled', 'char', 'NULL', 1,
732 'primary_key' => 'refnum',
734 'index' => [ ['disabled'] ],
739 'svcpart', 'serial', '', '',
740 'svc', 'varchar', '', $char_d,
741 'svcdb', 'varchar', '', $char_d,
742 'disabled', 'char', 'NULL', 1,
744 'primary_key' => 'svcpart',
746 'index' => [ [ 'disabled' ] ],
749 'part_svc_column' => {
751 'columnnum', 'serial', '', '',
752 'svcpart', 'int', '', '',
753 'columnname', 'varchar', '', 64,
754 'columnvalue', 'varchar', 'NULL', $char_d,
755 'columnflag', 'char', 'NULL', 1,
757 'primary_key' => 'columnnum',
758 'unique' => [ [ 'svcpart', 'columnname' ] ],
759 'index' => [ [ 'svcpart' ] ],
762 #(this should be renamed to part_pop)
765 'popnum', 'serial', '', '',
766 'city', 'varchar', '', $char_d,
767 'state', 'varchar', '', $char_d,
769 'exch', 'char', '', 3,
770 'loc', 'char', 'NULL', 4, #NULL for legacy purposes
772 'primary_key' => 'popnum',
774 'index' => [ [ 'state' ] ],
777 'part_pop_local' => {
779 'localnum', 'serial', '', '',
780 'popnum', 'int', '', '',
781 'city', 'varchar', 'NULL', $char_d,
782 'state', 'char', 'NULL', 2,
783 'npa', 'char', '', 3,
784 'nxx', 'char', '', 3,
786 'primary_key' => 'localnum',
788 'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ],
793 'svcnum', 'int', '', '',
794 'username', 'varchar', '', $username_len, #unique (& remove dup code)
795 '_password', 'varchar', '', 72, #13 for encryped pw's plus ' *SUSPENDED* (md5 passwords can be 34, blowfish 60)
796 'sec_phrase', 'varchar', 'NULL', $char_d,
797 'popnum', 'int', 'NULL', '',
798 'uid', 'int', 'NULL', '',
799 'gid', 'int', 'NULL', '',
800 'finger', 'varchar', 'NULL', $char_d,
801 'dir', 'varchar', 'NULL', $char_d,
802 'shell', 'varchar', 'NULL', $char_d,
803 'quota', 'varchar', 'NULL', $char_d,
804 'slipip', 'varchar', 'NULL', 15, #four TINYINTs, bah.
805 'seconds', 'int', 'NULL', '', #uhhhh
806 'domsvc', 'int', '', '',
808 'primary_key' => 'svcnum',
809 #'unique' => [ [ 'username', 'domsvc' ] ],
811 'index' => [ ['username'], ['domsvc'] ],
816 # 'svcnum', 'int', '', '',
817 # 'amount', @money_type,
819 # 'primary_key' => 'svcnum',
820 # 'unique' => [ [] ],
826 'svcnum', 'int', '', '',
827 'domain', 'varchar', '', $char_d,
828 'catchall', 'int', 'NULL', '',
830 'primary_key' => 'svcnum',
831 'unique' => [ ['domain'] ],
837 'recnum', 'serial', '', '',
838 'svcnum', 'int', '', '',
839 #'reczone', 'varchar', '', $char_d,
840 'reczone', 'varchar', '', 255,
841 'recaf', 'char', '', 2,
842 'rectype', 'varchar', '', 5,
843 #'recdata', 'varchar', '', $char_d,
844 'recdata', 'varchar', '', 255,
846 'primary_key' => 'recnum',
848 'index' => [ ['svcnum'] ],
853 'svcnum', 'int', '', '',
854 'srcsvc', 'int', 'NULL', '',
855 'src', 'varchar', 'NULL', 255,
856 'dstsvc', 'int', 'NULL', '',
857 'dst', 'varchar', 'NULL', 255,
859 'primary_key' => 'svcnum',
861 'index' => [ ['srcsvc'], ['dstsvc'] ],
866 'svcnum', 'int', '', '',
867 'recnum', 'int', '', '',
868 'usersvc', 'int', '', '',
870 'primary_key' => 'svcnum',
877 # 'svcnum', 'int', '', '',
878 # 'svcnum', 'int', '', '',
879 # 'svcnum', 'int', '', '',
880 # 'worker', 'varchar', '', $char_d,
881 # '_date', @date_type,
883 # 'primary_key' => 'svcnum',
884 # 'unique' => [ [] ],
890 'prepaynum', 'serial', '', '',
891 'identifier', 'varchar', '', $char_d,
892 'amount', @money_type,
893 'seconds', 'int', 'NULL', '',
894 'agentnum', 'int', 'NULL', '',
896 'primary_key' => 'prepaynum',
897 'unique' => [ ['identifier'] ],
903 'portnum', 'serial', '', '',
904 'ip', 'varchar', 'NULL', 15,
905 'nasport', 'int', 'NULL', '',
906 'nasnum', 'int', '', '',
908 'primary_key' => 'portnum',
915 'nasnum', 'serial', '', '',
916 'nas', 'varchar', '', $char_d,
917 'nasip', 'varchar', '', 15,
918 'nasfqdn', 'varchar', '', $char_d,
919 'last', 'int', '', '',
921 'primary_key' => 'nasnum',
922 'unique' => [ [ 'nas' ], [ 'nasip' ] ],
923 'index' => [ [ 'last' ] ],
928 'sessionnum', 'serial', '', '',
929 'portnum', 'int', '', '',
930 'svcnum', 'int', '', '',
932 'logout', @date_type,
934 'primary_key' => 'sessionnum',
936 'index' => [ [ 'portnum' ] ],
941 'jobnum', 'serial', '', '',
942 'job', 'text', '', '',
943 '_date', 'int', '', '',
944 'status', 'varchar', '', $char_d,
945 'statustext', 'text', 'NULL', '',
946 'svcnum', 'int', 'NULL', '',
948 'primary_key' => 'jobnum',
950 'index' => [ [ 'svcnum' ], [ 'status' ] ],
955 'argnum', 'serial', '', '',
956 'jobnum', 'int', '', '',
957 'arg', 'text', 'NULL', '',
959 'primary_key' => 'argnum',
961 'index' => [ [ 'jobnum' ] ],
966 'dependnum', 'serial', '', '',
967 'jobnum', 'int', '', '',
968 'depend_jobnum', 'int', '', '',
970 'primary_key' => 'dependnum',
972 'index' => [ [ 'jobnum' ], [ 'depend_jobnum' ] ],
977 'exportsvcnum' => 'serial', '', '',
978 'exportnum' => 'int', '', '',
979 'svcpart' => 'int', '', '',
981 'primary_key' => 'exportsvcnum',
982 'unique' => [ [ 'exportnum', 'svcpart' ] ],
983 'index' => [ [ 'exportnum' ], [ 'svcpart' ] ],
988 'exportnum', 'serial', '', '',
989 #'svcpart', 'int', '', '',
990 'machine', 'varchar', '', $char_d,
991 'exporttype', 'varchar', '', $char_d,
992 'nodomain', 'char', 'NULL', 1,
994 'primary_key' => 'exportnum',
996 'index' => [ [ 'machine' ], [ 'exporttype' ] ],
999 'part_export_option' => {
1001 'optionnum', 'serial', '', '',
1002 'exportnum', 'int', '', '',
1003 'optionname', 'varchar', '', $char_d,
1004 'optionvalue', 'text', 'NULL', '',
1006 'primary_key' => 'optionnum',
1008 'index' => [ [ 'exportnum' ], [ 'optionname' ] ],
1011 'radius_usergroup' => {
1013 'usergroupnum', 'serial', '', '',
1014 'svcnum', 'int', '', '',
1015 'groupname', 'varchar', '', $char_d,
1017 'primary_key' => 'usergroupnum',
1019 'index' => [ [ 'svcnum' ], [ 'groupname' ] ],
1024 'msgnum', 'serial', '', '',
1025 'msgcode', 'varchar', '', $char_d,
1026 'locale', 'varchar', '', 16,
1027 'msg', 'text', '', '',
1029 'primary_key' => 'msgnum',
1030 'unique' => [ [ 'msgcode', 'locale' ] ],
1034 'cust_tax_exempt' => {
1036 'exemptnum', 'serial', '', '',
1037 'custnum', 'int', '', '',
1038 'taxnum', 'int', '', '',
1039 'year', 'int', '', '',
1040 'month', 'int', '', '',
1041 'amount', @money_type,
1043 'primary_key' => 'exemptnum',
1044 'unique' => [ [ 'custnum', 'taxnum', 'year', 'month' ] ],
1050 'routernum', 'serial', '', '',
1051 'routername', 'varchar', '', $char_d,
1052 'svcnum', 'int', 'NULL', '',
1054 'primary_key' => 'routernum',
1059 'part_svc_router' => {
1061 'svcpart', 'int', '', '',
1062 'routernum', 'int', '', '',
1064 'primary_key' => '',
1071 'blocknum', 'serial', '', '',
1072 'routernum', 'int', '', '',
1073 'ip_gateway', 'varchar', '', 15,
1074 'ip_netmask', 'int', '', '',
1076 'primary_key' => 'blocknum',
1077 'unique' => [ [ 'blocknum', 'routernum' ] ],
1081 'svc_broadband' => {
1083 'svcnum', 'int', '', '',
1084 'blocknum', 'int', '', '',
1085 'speed_up', 'int', '', '',
1086 'speed_down', 'int', '', '',
1087 'ip_addr', 'varchar', '', 15,
1089 'primary_key' => 'svcnum',
1094 'part_virtual_field' => {
1096 'vfieldpart', 'int', '', '',
1097 'dbtable', 'varchar', '', 32,
1098 'name', 'varchar', '', 32,
1099 'check_block', 'text', 'NULL', '',
1100 'length', 'int', 'NULL', '',
1101 'list_source', 'text', 'NULL', '',
1102 'label', 'varchar', 'NULL', 80,
1104 'primary_key' => 'vfieldpart',
1109 'virtual_field' => {
1111 'recnum', 'int', '', '',
1112 'vfieldpart', 'int', '', '',
1113 'value', 'varchar', '', 128,
1115 'primary_key' => '',
1116 'unique' => [ [ 'vfieldpart', 'recnum' ] ],
1122 'snarfnum', 'int', '', '',
1123 'svcnum', 'int', '', '',
1124 'machine', 'varchar', '', 255,
1125 'protocol', 'varchar', '', $char_d,
1126 'username', 'varchar', '', $char_d,
1127 '_password', 'varchar', '', $char_d,
1129 'primary_key' => 'snarfnum',
1131 'index' => [ [ 'svcnum' ] ],
1136 'svcnum', 'int', '', '',
1137 'id', 'int', 'NULL', '',
1138 'title', 'varchar', 'NULL', $char_d,
1140 'primary_key' => 'svcnum',
1145 'cust_pay_refund' => {
1147 'payrefundnum', 'serial', '', '',
1148 'paynum', 'int', '', '',
1149 'refundnum', 'int', '', '',
1150 '_date', @date_type,
1151 'amount', @money_type,
1153 'primary_key' => 'payrefundnum',
1155 'index' => [ ['paynum'], ['refundnum'] ],
1158 'part_pkg_option' => {
1160 'optionnum', 'serial', '', '',
1161 'pkgpart', 'int', '', '',
1162 'optionname', 'varchar', '', $char_d,
1163 'optionvalue', 'text', 'NULL', '',
1165 'primary_key' => 'optionnum',
1167 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ],
1172 'ratenum', 'serial', '', '',
1173 'ratename', 'varchar', '', $char_d,
1175 'primary_key' => 'ratenum',
1182 'ratenum', 'int', '', '',
1183 'orig_regionnum', 'int', 'NULL', '',
1184 'dest_regionnum', 'int', '', '',
1185 'min_included', 'int', '', '',
1186 'min_charge', @money_type,
1187 'sec_granularity', 'int', '', '',
1188 #time period (link to table of periods)?
1190 'primary_key' => '',
1191 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
1197 'regionnum', 'serial', '', '',
1198 'regionname', 'varchar', '', $char_d,
1200 'primary_key' => 'regionnum',
1207 'prefixnum', 'serial', '', '',
1208 'regionnum', 'int', '', '',,
1209 'countrycode', 'varchar', '', 3,
1210 'npa', 'varchar', 'NULL', 6,
1211 'nxx', 'varchar', 'NULL', 3,
1213 'primary_key' => 'prefixnum',
1215 'index' => [ [ 'countrycode' ], [ 'regionnum' ] ],
1220 'codenum', 'serial', '', '',
1221 'code', 'varchar', '', $char_d,
1222 'agentnum', 'int', '', '',
1224 'primary_key' => 'codenum',
1225 'unique' => [ [ 'agentnum', 'code' ] ],
1226 'index' => [ [ 'agentnum' ] ],
1231 'codenum', 'int', '', '',
1232 'pkgpart', 'int', '', '',
1234 'primary_key' => '',
1235 'unique' => [ [ 'codenum', 'pkgpart' ] ],
1236 'index' => [ [ 'codenum' ] ],
1239 'clientapi_session' => {
1241 'sessionnum', 'serial', '', '',
1242 'sessionid', 'varchar', '', $char_d,
1243 'namespace', 'varchar', '', $char_d,
1245 'primary_key' => 'sessionnum',
1246 'unique' => [ [ 'sessionid', 'namespace' ] ],
1250 'clientapi_session_field' => {
1252 'fieldnum', 'serial', '', '',
1253 'sessionnum', 'int', '', '',
1254 'fieldname', 'varchar', '', $char_d,
1255 'fieldvalue', 'text', 'NULL', '',
1257 'primary_key' => 'fieldnum',
1258 'unique' => [ [ 'sessionnum', 'fieldname' ] ],