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.25;
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 'typepkgnum', 'serial', '', '',
347 'typenum', 'int', '', '',
348 'pkgpart', 'int', '', '',
350 'primary_key' => 'typepkgnum',
351 'unique' => [ ['typenum', 'pkgpart'] ],
352 'index' => [ ['typenum'] ],
357 'invnum', 'serial', '', '',
358 'custnum', 'int', '', '',
360 'charged', @money_type,
361 'printed', 'int', '', '',
362 'closed', 'char', 'NULL', 1,
364 'primary_key' => 'invnum',
366 'index' => [ ['custnum'], ['_date'] ],
369 'cust_bill_event' => {
371 'eventnum', 'serial', '', '',
372 'invnum', 'int', '', '',
373 'eventpart', 'int', '', '',
375 'status', 'varchar', '', $char_d,
376 'statustext', 'text', 'NULL', '',
378 'primary_key' => 'eventnum',
379 #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
381 'index' => [ ['invnum'], ['status'] ],
384 'part_bill_event' => {
386 'eventpart', 'serial', '', '',
387 'payby', 'char', '', 4,
388 'event', 'varchar', '', $char_d,
389 'eventcode', @perl_type,
390 'seconds', 'int', 'NULL', '',
391 'weight', 'int', '', '',
392 'plan', 'varchar', 'NULL', $char_d,
393 'plandata', 'text', 'NULL', '',
394 'disabled', 'char', 'NULL', 1,
396 'primary_key' => 'eventpart',
398 'index' => [ ['payby'], ['disabled'], ],
403 'billpkgnum', 'serial', '', '',
404 'pkgnum', 'int', '', '',
405 'invnum', 'int', '', '',
406 'setup', @money_type,
407 'recur', @money_type,
410 'itemdesc', 'varchar', 'NULL', $char_d,
412 'primary_key' => 'billpkgnum',
414 'index' => [ ['invnum'] ],
417 'cust_bill_pkg_detail' => {
419 'detailnum', 'serial', '', '',
420 'pkgnum', 'int', '', '',
421 'invnum', 'int', '', '',
422 'detail', 'varchar', '', $char_d,
424 'primary_key' => 'detailnum',
426 'index' => [ [ 'pkgnum', 'invnum' ] ],
431 'crednum', 'serial', '', '',
432 'custnum', 'int', '', '',
434 'amount', @money_type,
435 'otaker', 'varchar', '', 32,
436 'reason', 'text', 'NULL', '',
437 'closed', 'char', 'NULL', 1,
439 'primary_key' => 'crednum',
441 'index' => [ ['custnum'] ],
444 'cust_credit_bill' => {
446 'creditbillnum', 'serial', '', '',
447 'crednum', 'int', '', '',
448 'invnum', 'int', '', '',
450 'amount', @money_type,
452 'primary_key' => 'creditbillnum',
454 'index' => [ ['crednum'], ['invnum'] ],
459 'custnum', 'serial', '', '',
460 'agentnum', 'int', '', '',
461 # 'titlenum', 'int', 'NULL', '',
462 'last', 'varchar', '', $char_d,
463 # 'middle', 'varchar', 'NULL', $char_d,
464 'first', 'varchar', '', $char_d,
465 'ss', 'varchar', 'NULL', 11,
466 'company', 'varchar', 'NULL', $char_d,
467 'address1', 'varchar', '', $char_d,
468 'address2', 'varchar', 'NULL', $char_d,
469 'city', 'varchar', '', $char_d,
470 'county', 'varchar', 'NULL', $char_d,
471 'state', 'varchar', 'NULL', $char_d,
472 'zip', 'varchar', 'NULL', 10,
473 'country', 'char', '', 2,
474 'daytime', 'varchar', 'NULL', 20,
475 'night', 'varchar', 'NULL', 20,
476 'fax', 'varchar', 'NULL', 12,
477 'ship_last', 'varchar', 'NULL', $char_d,
478 # 'ship_middle', 'varchar', 'NULL', $char_d,
479 'ship_first', 'varchar', 'NULL', $char_d,
480 'ship_company', 'varchar', 'NULL', $char_d,
481 'ship_address1', 'varchar', 'NULL', $char_d,
482 'ship_address2', 'varchar', 'NULL', $char_d,
483 'ship_city', 'varchar', 'NULL', $char_d,
484 'ship_county', 'varchar', 'NULL', $char_d,
485 'ship_state', 'varchar', 'NULL', $char_d,
486 'ship_zip', 'varchar', 'NULL', 10,
487 'ship_country', 'char', 'NULL', 2,
488 'ship_daytime', 'varchar', 'NULL', 20,
489 'ship_night', 'varchar', 'NULL', 20,
490 'ship_fax', 'varchar', 'NULL', 12,
491 'payby', 'char', '', 4,
492 'payinfo', 'varchar', 'NULL', 512,
493 'paycvv', 'varchar', 'NULL', 512,
494 'paymask', 'varchar', 'NULL', $char_d,
495 #'paydate', @date_type,
496 'paydate', 'varchar', 'NULL', 10,
497 'payname', 'varchar', 'NULL', $char_d,
498 'tax', 'char', 'NULL', 1,
499 'otaker', 'varchar', '', 32,
500 'refnum', 'int', '', '',
501 'referral_custnum', 'int', 'NULL', '',
502 'comments', 'text', 'NULL', '',
504 'primary_key' => 'custnum',
506 #'index' => [ ['last'], ['company'] ],
507 'index' => [ ['last'], [ 'company' ], [ 'referral_custnum' ],
508 [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'refnum' ],
512 'cust_main_invoice' => {
514 'destnum', 'serial', '', '',
515 'custnum', 'int', '', '',
516 'dest', 'varchar', '', $char_d,
518 'primary_key' => 'destnum',
520 'index' => [ ['custnum'], ],
523 'cust_main_county' => { #county+state+country are checked off the
524 #cust_main_county for validation and to provide
527 'taxnum', 'serial', '', '',
528 'state', 'varchar', 'NULL', $char_d,
529 'county', 'varchar', 'NULL', $char_d,
530 'country', 'char', '', 2,
531 'taxclass', 'varchar', 'NULL', $char_d,
532 'exempt_amount', @money_type,
533 'tax', 'real', '', '', #tax %
534 'taxname', 'varchar', 'NULL', $char_d,
535 'setuptax', 'char', 'NULL', 1, # Y = setup tax exempt
536 'recurtax', 'char', 'NULL', 1, # Y = recur tax exempt
538 'primary_key' => 'taxnum',
540 # 'unique' => [ ['taxnum'], ['state', 'county'] ],
546 'paynum', 'serial', '', '',
547 #now cust_bill_pay #'invnum', 'int', '', '',
548 'custnum', 'int', '', '',
551 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
552 # payment type table.
553 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
554 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
555 'closed', 'char', 'NULL', 1,
557 'primary_key' => 'paynum',
559 'index' => [ [ 'custnum' ], [ 'paybatch' ], [ 'payby' ], [ '_date' ] ],
564 'paynum', 'int', '', '',
565 'custnum', 'int', '', '',
568 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
569 # payment type table.
570 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
571 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
572 'closed', 'char', 'NULL', 1,
573 'void_date', @date_type,
574 'reason', 'varchar', 'NULL', $char_d,
575 'otaker', 'varchar', '', 32,
577 'primary_key' => 'paynum',
579 'index' => [ [ 'custnum' ] ],
584 'billpaynum', 'serial', '', '',
585 'invnum', 'int', '', '',
586 'paynum', 'int', '', '',
587 'amount', @money_type,
590 'primary_key' => 'billpaynum',
592 'index' => [ [ 'paynum' ], [ 'invnum' ] ],
595 'cust_pay_batch' => { #what's this used for again? list of customers
596 #in current CARD batch? (necessarily CARD?)
598 'paybatchnum', 'serial', '', '',
599 'invnum', 'int', '', '',
600 'custnum', 'int', '', '',
601 'last', 'varchar', '', $char_d,
602 'first', 'varchar', '', $char_d,
603 'address1', 'varchar', '', $char_d,
604 'address2', 'varchar', 'NULL', $char_d,
605 'city', 'varchar', '', $char_d,
606 'state', 'varchar', 'NULL', $char_d,
607 'zip', 'varchar', 'NULL', 10,
608 'country', 'char', '', 2,
609 # 'trancode', 'int', '', '',
610 'cardnum', 'varchar', '', 16,
612 'exp', 'varchar', '', 11,
613 'payname', 'varchar', 'NULL', $char_d,
614 'amount', @money_type,
616 'primary_key' => 'paybatchnum',
618 'index' => [ ['invnum'], ['custnum'] ],
623 'pkgnum', 'serial', '', '',
624 'custnum', 'int', '', '',
625 'pkgpart', 'int', '', '',
626 'otaker', 'varchar', '', 32,
629 'last_bill', @date_type,
631 'cancel', @date_type,
632 'expire', @date_type,
633 'manual_flag', 'char', 'NULL', 1,
635 'primary_key' => 'pkgnum',
637 'index' => [ ['custnum'] ],
642 'refundnum', 'serial', '', '',
643 #now cust_credit_refund #'crednum', 'int', '', '',
644 'custnum', 'int', '', '',
646 'refund', @money_type,
647 'otaker', 'varchar', '', 32,
648 'reason', 'varchar', '', $char_d,
649 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index
650 # into payment type table.
651 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
652 'paybatch', 'varchar', 'NULL', $char_d,
653 'closed', 'char', 'NULL', 1,
655 'primary_key' => 'refundnum',
660 'cust_credit_refund' => {
662 'creditrefundnum', 'serial', '', '',
663 'crednum', 'int', '', '',
664 'refundnum', 'int', '', '',
665 'amount', @money_type,
668 'primary_key' => 'creditrefundnum',
670 'index' => [ [ 'crednum', 'refundnum' ] ],
676 'svcnum', 'serial', '', '',
677 'pkgnum', 'int', 'NULL', '',
678 'svcpart', 'int', '', '',
680 'primary_key' => 'svcnum',
682 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ],
687 'pkgpart', 'serial', '', '',
688 'pkg', 'varchar', '', $char_d,
689 'comment', 'varchar', '', $char_d,
690 'promo_code', 'varchar', 'NULL', $char_d,
692 'freq', 'varchar', '', $char_d, #billing frequency
694 'setuptax', 'char', 'NULL', 1,
695 'recurtax', 'char', 'NULL', 1,
696 'plan', 'varchar', 'NULL', $char_d,
697 'plandata', 'text', 'NULL', '',
698 'disabled', 'char', 'NULL', 1,
699 'taxclass', 'varchar', 'NULL', $char_d,
701 'primary_key' => 'pkgpart',
703 'index' => [ [ 'promo_code' ], [ 'disabled' ] ],
708 # 'titlenum', 'int', '', '',
709 # 'title', 'varchar', '', $char_d,
711 # 'primary_key' => 'titlenum',
712 # 'unique' => [ [] ],
718 'pkgsvcnum', 'serial', '', '',
719 'pkgpart', 'int', '', '',
720 'svcpart', 'int', '', '',
721 'quantity', 'int', '', '',
722 'primary_svc','char', 'NULL', 1,
724 'primary_key' => 'pkgsvcnum',
725 'unique' => [ ['pkgpart', 'svcpart'] ],
726 'index' => [ ['pkgpart'] ],
731 'refnum', 'serial', '', '',
732 'referral', 'varchar', '', $char_d,
733 'disabled', 'char', 'NULL', 1,
735 'primary_key' => 'refnum',
737 'index' => [ ['disabled'] ],
742 'svcpart', 'serial', '', '',
743 'svc', 'varchar', '', $char_d,
744 'svcdb', 'varchar', '', $char_d,
745 'disabled', 'char', 'NULL', 1,
747 'primary_key' => 'svcpart',
749 'index' => [ [ 'disabled' ] ],
752 'part_svc_column' => {
754 'columnnum', 'serial', '', '',
755 'svcpart', 'int', '', '',
756 'columnname', 'varchar', '', 64,
757 'columnvalue', 'varchar', 'NULL', $char_d,
758 'columnflag', 'char', 'NULL', 1,
760 'primary_key' => 'columnnum',
761 'unique' => [ [ 'svcpart', 'columnname' ] ],
762 'index' => [ [ 'svcpart' ] ],
765 #(this should be renamed to part_pop)
768 'popnum', 'serial', '', '',
769 'city', 'varchar', '', $char_d,
770 'state', 'varchar', '', $char_d,
772 'exch', 'char', '', 3,
773 'loc', 'char', 'NULL', 4, #NULL for legacy purposes
775 'primary_key' => 'popnum',
777 'index' => [ [ 'state' ] ],
780 'part_pop_local' => {
782 'localnum', 'serial', '', '',
783 'popnum', 'int', '', '',
784 'city', 'varchar', 'NULL', $char_d,
785 'state', 'char', 'NULL', 2,
786 'npa', 'char', '', 3,
787 'nxx', 'char', '', 3,
789 'primary_key' => 'localnum',
791 'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ],
796 'svcnum', 'int', '', '',
797 'username', 'varchar', '', $username_len, #unique (& remove dup code)
798 '_password', 'varchar', '', 72, #13 for encryped pw's plus ' *SUSPENDED* (md5 passwords can be 34, blowfish 60)
799 'sec_phrase', 'varchar', 'NULL', $char_d,
800 'popnum', 'int', 'NULL', '',
801 'uid', 'int', 'NULL', '',
802 'gid', 'int', 'NULL', '',
803 'finger', 'varchar', 'NULL', $char_d,
804 'dir', 'varchar', 'NULL', $char_d,
805 'shell', 'varchar', 'NULL', $char_d,
806 'quota', 'varchar', 'NULL', $char_d,
807 'slipip', 'varchar', 'NULL', 15, #four TINYINTs, bah.
808 'seconds', 'int', 'NULL', '', #uhhhh
809 'domsvc', 'int', '', '',
811 'primary_key' => 'svcnum',
812 #'unique' => [ [ 'username', 'domsvc' ] ],
814 'index' => [ ['username'], ['domsvc'] ],
819 # 'svcnum', 'int', '', '',
820 # 'amount', @money_type,
822 # 'primary_key' => 'svcnum',
823 # 'unique' => [ [] ],
829 'svcnum', 'int', '', '',
830 'domain', 'varchar', '', $char_d,
831 'catchall', 'int', 'NULL', '',
833 'primary_key' => 'svcnum',
834 'unique' => [ ['domain'] ],
840 'recnum', 'serial', '', '',
841 'svcnum', 'int', '', '',
842 #'reczone', 'varchar', '', $char_d,
843 'reczone', 'varchar', '', 255,
844 'recaf', 'char', '', 2,
845 'rectype', 'varchar', '', 5,
846 #'recdata', 'varchar', '', $char_d,
847 'recdata', 'varchar', '', 255,
849 'primary_key' => 'recnum',
851 'index' => [ ['svcnum'] ],
856 'svcnum', 'int', '', '',
857 'srcsvc', 'int', 'NULL', '',
858 'src', 'varchar', 'NULL', 255,
859 'dstsvc', 'int', 'NULL', '',
860 'dst', 'varchar', 'NULL', 255,
862 'primary_key' => 'svcnum',
864 'index' => [ ['srcsvc'], ['dstsvc'] ],
869 'svcnum', 'int', '', '',
870 'recnum', 'int', '', '',
871 'usersvc', 'int', '', '',
873 'primary_key' => 'svcnum',
880 # 'svcnum', 'int', '', '',
881 # 'svcnum', 'int', '', '',
882 # 'svcnum', 'int', '', '',
883 # 'worker', 'varchar', '', $char_d,
884 # '_date', @date_type,
886 # 'primary_key' => 'svcnum',
887 # 'unique' => [ [] ],
893 'prepaynum', 'serial', '', '',
894 'identifier', 'varchar', '', $char_d,
895 'amount', @money_type,
896 'seconds', 'int', 'NULL', '',
897 'agentnum', 'int', 'NULL', '',
899 'primary_key' => 'prepaynum',
900 'unique' => [ ['identifier'] ],
906 'portnum', 'serial', '', '',
907 'ip', 'varchar', 'NULL', 15,
908 'nasport', 'int', 'NULL', '',
909 'nasnum', 'int', '', '',
911 'primary_key' => 'portnum',
918 'nasnum', 'serial', '', '',
919 'nas', 'varchar', '', $char_d,
920 'nasip', 'varchar', '', 15,
921 'nasfqdn', 'varchar', '', $char_d,
922 'last', 'int', '', '',
924 'primary_key' => 'nasnum',
925 'unique' => [ [ 'nas' ], [ 'nasip' ] ],
926 'index' => [ [ 'last' ] ],
931 'sessionnum', 'serial', '', '',
932 'portnum', 'int', '', '',
933 'svcnum', 'int', '', '',
935 'logout', @date_type,
937 'primary_key' => 'sessionnum',
939 'index' => [ [ 'portnum' ] ],
944 'jobnum', 'serial', '', '',
945 'job', 'text', '', '',
946 '_date', 'int', '', '',
947 'status', 'varchar', '', $char_d,
948 'statustext', 'text', 'NULL', '',
949 'svcnum', 'int', 'NULL', '',
951 'primary_key' => 'jobnum',
953 'index' => [ [ 'svcnum' ], [ 'status' ] ],
958 'argnum', 'serial', '', '',
959 'jobnum', 'int', '', '',
960 'arg', 'text', 'NULL', '',
962 'primary_key' => 'argnum',
964 'index' => [ [ 'jobnum' ] ],
969 'dependnum', 'serial', '', '',
970 'jobnum', 'int', '', '',
971 'depend_jobnum', 'int', '', '',
973 'primary_key' => 'dependnum',
975 'index' => [ [ 'jobnum' ], [ 'depend_jobnum' ] ],
980 'exportsvcnum' => 'serial', '', '',
981 'exportnum' => 'int', '', '',
982 'svcpart' => 'int', '', '',
984 'primary_key' => 'exportsvcnum',
985 'unique' => [ [ 'exportnum', 'svcpart' ] ],
986 'index' => [ [ 'exportnum' ], [ 'svcpart' ] ],
991 'exportnum', 'serial', '', '',
992 #'svcpart', 'int', '', '',
993 'machine', 'varchar', '', $char_d,
994 'exporttype', 'varchar', '', $char_d,
995 'nodomain', 'char', 'NULL', 1,
997 'primary_key' => 'exportnum',
999 'index' => [ [ 'machine' ], [ 'exporttype' ] ],
1002 'part_export_option' => {
1004 'optionnum', 'serial', '', '',
1005 'exportnum', 'int', '', '',
1006 'optionname', 'varchar', '', $char_d,
1007 'optionvalue', 'text', 'NULL', '',
1009 'primary_key' => 'optionnum',
1011 'index' => [ [ 'exportnum' ], [ 'optionname' ] ],
1014 'radius_usergroup' => {
1016 'usergroupnum', 'serial', '', '',
1017 'svcnum', 'int', '', '',
1018 'groupname', 'varchar', '', $char_d,
1020 'primary_key' => 'usergroupnum',
1022 'index' => [ [ 'svcnum' ], [ 'groupname' ] ],
1027 'msgnum', 'serial', '', '',
1028 'msgcode', 'varchar', '', $char_d,
1029 'locale', 'varchar', '', 16,
1030 'msg', 'text', '', '',
1032 'primary_key' => 'msgnum',
1033 'unique' => [ [ 'msgcode', 'locale' ] ],
1037 'cust_tax_exempt' => {
1039 'exemptnum', 'serial', '', '',
1040 'custnum', 'int', '', '',
1041 'taxnum', 'int', '', '',
1042 'year', 'int', '', '',
1043 'month', 'int', '', '',
1044 'amount', @money_type,
1046 'primary_key' => 'exemptnum',
1047 'unique' => [ [ 'custnum', 'taxnum', 'year', 'month' ] ],
1053 'routernum', 'serial', '', '',
1054 'routername', 'varchar', '', $char_d,
1055 'svcnum', 'int', 'NULL', '',
1057 'primary_key' => 'routernum',
1062 'part_svc_router' => {
1064 'svcrouternum', 'serial', '', '',
1065 'svcpart', 'int', '', '',
1066 'routernum', 'int', '', '',
1068 'primary_key' => 'svcrouternum',
1075 'blocknum', 'serial', '', '',
1076 'routernum', 'int', '', '',
1077 'ip_gateway', 'varchar', '', 15,
1078 'ip_netmask', 'int', '', '',
1080 'primary_key' => 'blocknum',
1081 'unique' => [ [ 'blocknum', 'routernum' ] ],
1085 'svc_broadband' => {
1087 'svcnum', 'int', '', '',
1088 'blocknum', 'int', '', '',
1089 'speed_up', 'int', '', '',
1090 'speed_down', 'int', '', '',
1091 'ip_addr', 'varchar', '', 15,
1093 'primary_key' => 'svcnum',
1098 'part_virtual_field' => {
1100 'vfieldpart', 'int', '', '',
1101 'dbtable', 'varchar', '', 32,
1102 'name', 'varchar', '', 32,
1103 'check_block', 'text', 'NULL', '',
1104 'length', 'int', 'NULL', '',
1105 'list_source', 'text', 'NULL', '',
1106 'label', 'varchar', 'NULL', 80,
1108 'primary_key' => 'vfieldpart',
1113 'virtual_field' => {
1115 'vfieldnum', 'serial', '', '',
1116 'recnum', 'int', '', '',
1117 'vfieldpart', 'int', '', '',
1118 'value', 'varchar', '', 128,
1120 'primary_key' => 'vfieldnum',
1121 'unique' => [ [ 'vfieldpart', 'recnum' ] ],
1127 'snarfnum', 'int', '', '',
1128 'svcnum', 'int', '', '',
1129 'machine', 'varchar', '', 255,
1130 'protocol', 'varchar', '', $char_d,
1131 'username', 'varchar', '', $char_d,
1132 '_password', 'varchar', '', $char_d,
1134 'primary_key' => 'snarfnum',
1136 'index' => [ [ 'svcnum' ] ],
1141 'svcnum', 'int', '', '',
1142 'id', 'int', 'NULL', '',
1143 'title', 'varchar', 'NULL', $char_d,
1145 'primary_key' => 'svcnum',
1150 'cust_pay_refund' => {
1152 'payrefundnum', 'serial', '', '',
1153 'paynum', 'int', '', '',
1154 'refundnum', 'int', '', '',
1155 '_date', @date_type,
1156 'amount', @money_type,
1158 'primary_key' => 'payrefundnum',
1160 'index' => [ ['paynum'], ['refundnum'] ],
1163 'part_pkg_option' => {
1165 'optionnum', 'serial', '', '',
1166 'pkgpart', 'int', '', '',
1167 'optionname', 'varchar', '', $char_d,
1168 'optionvalue', 'text', 'NULL', '',
1170 'primary_key' => 'optionnum',
1172 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ],
1177 'ratenum', 'serial', '', '',
1178 'ratename', 'varchar', '', $char_d,
1180 'primary_key' => 'ratenum',
1187 'ratedetailnum', 'serial', '', '',
1188 'ratenum', 'int', '', '',
1189 'orig_regionnum', 'int', 'NULL', '',
1190 'dest_regionnum', 'int', '', '',
1191 'min_included', 'int', '', '',
1192 'min_charge', @money_type,
1193 'sec_granularity', 'int', '', '',
1194 #time period (link to table of periods)?
1196 'primary_key' => 'ratedetailnum',
1197 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
1203 'regionnum', 'serial', '', '',
1204 'regionname', 'varchar', '', $char_d,
1206 'primary_key' => 'regionnum',
1213 'prefixnum', 'serial', '', '',
1214 'regionnum', 'int', '', '',,
1215 'countrycode', 'varchar', '', 3,
1216 'npa', 'varchar', 'NULL', 6,
1217 'nxx', 'varchar', 'NULL', 3,
1219 'primary_key' => 'prefixnum',
1221 'index' => [ [ 'countrycode' ], [ 'regionnum' ] ],
1226 'codenum', 'serial', '', '',
1227 'code', 'varchar', '', $char_d,
1228 'agentnum', 'int', '', '',
1230 'primary_key' => 'codenum',
1231 'unique' => [ [ 'agentnum', 'code' ] ],
1232 'index' => [ [ 'agentnum' ] ],
1237 'codepkgnum', 'serial', '', '',
1238 'codenum', 'int', '', '',
1239 'pkgpart', 'int', '', '',
1241 'primary_key' => 'codepkgnum',
1242 'unique' => [ [ 'codenum', 'pkgpart' ] ],
1243 'index' => [ [ 'codenum' ] ],
1246 'clientapi_session' => {
1248 'sessionnum', 'serial', '', '',
1249 'sessionid', 'varchar', '', $char_d,
1250 'namespace', 'varchar', '', $char_d,
1252 'primary_key' => 'sessionnum',
1253 'unique' => [ [ 'sessionid', 'namespace' ] ],
1257 'clientapi_session_field' => {
1259 'fieldnum', 'serial', '', '',
1260 'sessionnum', 'int', '', '',
1261 'fieldname', 'varchar', '', $char_d,
1262 'fieldvalue', 'text', 'NULL', '',
1264 'primary_key' => 'fieldnum',
1265 'unique' => [ [ 'sessionnum', 'fieldname' ] ],