3 use vars qw(@ISA @EXPORT_OK $DEBUG $setup_hack %dbdef_cache);
4 use subs qw(reload_dbdef);
6 use DBIx::DBSchema 0.25;
7 use DBIx::DBSchema::Table;
8 use DBIx::DBSchema::Column;
9 use DBIx::DBSchema::ColGroup::Unique;
10 use DBIx::DBSchema::ColGroup::Index;
11 use FS::UID qw(datasrc);
14 @EXPORT_OK = qw( dbdef dbdef_dist reload_dbdef );
19 #ask FS::UID to run this stuff for us later
20 FS::UID->install_callback( sub {
21 #$conf = new FS::Conf;
22 &reload_dbdef("/usr/local/etc/freeside/dbdef.". datasrc)
23 unless $setup_hack; #$setup_hack needed now?
28 FS::Schema - Freeside database schema
32 use FS::Schema qw(dbdef dbdef_dist reload_dbdef);
34 $dbdef = reload_dbdef;
35 $dbdef = reload_dbdef "/non/standard/filename";
37 $dbdef_dist = dbdef_dist;
41 This class represents the database schema.
47 =item reload_dbdef([FILENAME])
49 Load a database definition (see L<DBIx::DBSchema>), optionally from a
50 non-default filename. This command is executed at startup unless
51 I<$FS::Schema::setup_hack> is true. Returns a DBIx::DBSchema object.
58 unless ( exists $dbdef_cache{$file} ) {
59 warn "[debug]$me loading dbdef for $file\n" if $DEBUG;
60 $dbdef_cache{$file} = DBIx::DBSchema->load( $file )
61 or die "can't load database schema from $file";
63 warn "[debug]$me re-using cached dbdef for $file\n" if $DEBUG;
65 $dbdef = $dbdef_cache{$file};
70 Returns the current database definition (represents the current database,
71 assuming it is up-to-date). See L<DBIx::DBSchema>.
77 =item dbdef_dist [ OPTION => VALUE ... ]
79 Returns the current canoical database definition as defined in this file.
86 # create a dbdef object from the old data structure
89 my $tables_hashref = tables_hashref();
92 my $dbdef = new DBIx::DBSchema map {
94 while (@{$tables_hashref->{$_}{'columns'}}) {
95 my($name, $type, $null, $length) =
96 splice @{$tables_hashref->{$_}{'columns'}}, 0, 4;
97 push @columns, new DBIx::DBSchema::Column ( $name,$type,$null,$length );
99 DBIx::DBSchema::Table->new(
101 $tables_hashref->{$_}{'primary_key'},
102 DBIx::DBSchema::ColGroup::Unique->new($tables_hashref->{$_}{'unique'}),
103 DBIx::DBSchema::ColGroup::Index->new($tables_hashref->{$_}{'index'}),
106 } keys %$tables_hashref;
109 warn "[debug]$me initial dbdef_dist created ($dbdef) with tables:\n";
110 warn "[debug]$me $_\n" foreach $dbdef->tables;
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 )
208 if ( $column->type eq 'serial' ) {
209 $column->type('int');
210 $column->null('NULL');
212 #$column->default('')
213 # if $column->default =~ /^nextval\(/i;
214 #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
215 #$column->local($local);
220 $dbdef->addtable($h_tableobj);
229 my $char_d = 80; #default maxlength for text fields
231 #my(@date_type) = ( 'timestamp', '', '' );
232 my @date_type = ( 'int', 'NULL', '' );
233 my @perl_type = ( 'text', 'NULL', '' );
234 my @money_type = ( 'decimal', '', '10,2' );
236 my $username_len = 32; #usernamemax config file
242 'agentnum', 'serial', '', '',
243 'agent', 'varchar', '', $char_d,
244 'typenum', 'int', '', '',
245 'freq', 'int', 'NULL', '',
247 'disabled', 'char', 'NULL', 1,
248 'username', 'varchar', 'NULL', $char_d,
249 '_password','varchar', 'NULL', $char_d,
250 'ticketing_queueid', 'int', 'NULL', '',
252 'primary_key' => 'agentnum',
254 'index' => [ ['typenum'], ['disabled'] ],
259 'typenum', 'serial', '', '',
260 'atype', 'varchar', '', $char_d,
262 'primary_key' => 'typenum',
269 'typepkgnum', 'serial', '', '',
270 'typenum', 'int', '', '',
271 'pkgpart', 'int', '', '',
273 'primary_key' => 'typepkgnum',
274 'unique' => [ ['typenum', 'pkgpart'] ],
275 'index' => [ ['typenum'] ],
280 'invnum', 'serial', '', '',
281 'custnum', 'int', '', '',
283 'charged', @money_type,
284 'printed', 'int', '', '',
285 'closed', 'char', 'NULL', 1,
287 'primary_key' => 'invnum',
289 'index' => [ ['custnum'], ['_date'] ],
292 'cust_bill_event' => {
294 'eventnum', 'serial', '', '',
295 'invnum', 'int', '', '',
296 'eventpart', 'int', '', '',
298 'status', 'varchar', '', $char_d,
299 'statustext', 'text', 'NULL', '',
301 'primary_key' => 'eventnum',
302 #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
304 'index' => [ ['invnum'], ['status'] ],
307 'part_bill_event' => {
309 'eventpart', 'serial', '', '',
310 'freq', 'varchar', 'NULL', $char_d,
311 'payby', 'char', '', 4,
312 'event', 'varchar', '', $char_d,
313 'eventcode', @perl_type,
314 'seconds', 'int', 'NULL', '',
315 'weight', 'int', '', '',
316 'plan', 'varchar', 'NULL', $char_d,
317 'plandata', 'text', 'NULL', '',
318 'disabled', 'char', 'NULL', 1,
320 'primary_key' => 'eventpart',
322 'index' => [ ['payby'], ['disabled'], ],
327 'billpkgnum', 'serial', '', '',
328 'pkgnum', 'int', '', '',
329 'invnum', 'int', '', '',
330 'setup', @money_type,
331 'recur', @money_type,
334 'itemdesc', 'varchar', 'NULL', $char_d,
336 'primary_key' => 'billpkgnum',
338 'index' => [ ['invnum'], [ 'pkgnum' ] ],
341 'cust_bill_pkg_detail' => {
343 'detailnum', 'serial', '', '',
344 'pkgnum', 'int', '', '',
345 'invnum', 'int', '', '',
346 'detail', 'varchar', '', $char_d,
348 'primary_key' => 'detailnum',
350 'index' => [ [ 'pkgnum', 'invnum' ] ],
355 'crednum', 'serial', '', '',
356 'custnum', 'int', '', '',
358 'amount', @money_type,
359 'otaker', 'varchar', '', 32,
360 'reason', 'text', 'NULL', '',
361 'closed', 'char', 'NULL', 1,
363 'primary_key' => 'crednum',
365 'index' => [ ['custnum'] ],
368 'cust_credit_bill' => {
370 'creditbillnum', 'serial', '', '',
371 'crednum', 'int', '', '',
372 'invnum', 'int', '', '',
374 'amount', @money_type,
376 'primary_key' => 'creditbillnum',
378 'index' => [ ['crednum'], ['invnum'] ],
383 'custnum', 'serial', '', '',
384 'agentnum', 'int', '', '',
385 # 'titlenum', 'int', 'NULL', '',
386 'last', 'varchar', '', $char_d,
387 # 'middle', 'varchar', 'NULL', $char_d,
388 'first', 'varchar', '', $char_d,
389 'ss', 'varchar', 'NULL', 11,
390 'company', 'varchar', 'NULL', $char_d,
391 'address1', 'varchar', '', $char_d,
392 'address2', 'varchar', 'NULL', $char_d,
393 'city', 'varchar', '', $char_d,
394 'county', 'varchar', 'NULL', $char_d,
395 'state', 'varchar', 'NULL', $char_d,
396 'zip', 'varchar', 'NULL', 10,
397 'country', 'char', '', 2,
398 'daytime', 'varchar', 'NULL', 20,
399 'night', 'varchar', 'NULL', 20,
400 'fax', 'varchar', 'NULL', 12,
401 'ship_last', 'varchar', 'NULL', $char_d,
402 # 'ship_middle', 'varchar', 'NULL', $char_d,
403 'ship_first', 'varchar', 'NULL', $char_d,
404 'ship_company', 'varchar', 'NULL', $char_d,
405 'ship_address1', 'varchar', 'NULL', $char_d,
406 'ship_address2', 'varchar', 'NULL', $char_d,
407 'ship_city', 'varchar', 'NULL', $char_d,
408 'ship_county', 'varchar', 'NULL', $char_d,
409 'ship_state', 'varchar', 'NULL', $char_d,
410 'ship_zip', 'varchar', 'NULL', 10,
411 'ship_country', 'char', 'NULL', 2,
412 'ship_daytime', 'varchar', 'NULL', 20,
413 'ship_night', 'varchar', 'NULL', 20,
414 'ship_fax', 'varchar', 'NULL', 12,
415 'payby', 'char', '', 4,
416 'payinfo', 'varchar', 'NULL', 512,
417 'paycvv', 'varchar', 'NULL', 512,
418 'paymask', 'varchar', 'NULL', $char_d,
419 #'paydate', @date_type,
420 'paydate', 'varchar', 'NULL', 10,
421 'paystart_month', 'int', 'NULL', '',
422 'paystart_year', 'int', 'NULL', '',
423 'payissue', 'varchar', 'NULL', 2,
424 'payname', 'varchar', 'NULL', $char_d,
425 'payip', 'varchar', 'NULL', 15,
426 'tax', 'char', 'NULL', 1,
427 'otaker', 'varchar', '', 32,
428 'refnum', 'int', '', '',
429 'referral_custnum', 'int', 'NULL', '',
430 'comments', 'text', 'NULL', '',
432 'primary_key' => 'custnum',
434 #'index' => [ ['last'], ['company'] ],
435 'index' => [ ['last'], [ 'company' ], [ 'referral_custnum' ],
436 [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'refnum' ],
437 [ 'county' ], [ 'state' ], [ 'country' ]
441 'cust_main_invoice' => {
443 'destnum', 'serial', '', '',
444 'custnum', 'int', '', '',
445 'dest', 'varchar', '', $char_d,
447 'primary_key' => 'destnum',
449 'index' => [ ['custnum'], ],
452 'cust_main_county' => { #county+state+country are checked off the
453 #cust_main_county for validation and to provide
456 'taxnum', 'serial', '', '',
457 'state', 'varchar', 'NULL', $char_d,
458 'county', 'varchar', 'NULL', $char_d,
459 'country', 'char', '', 2,
460 'taxclass', 'varchar', 'NULL', $char_d,
461 'exempt_amount', @money_type,
462 'tax', 'real', '', '', #tax %
463 'taxname', 'varchar', 'NULL', $char_d,
464 'setuptax', 'char', 'NULL', 1, # Y = setup tax exempt
465 'recurtax', 'char', 'NULL', 1, # Y = recur tax exempt
467 'primary_key' => 'taxnum',
469 # 'unique' => [ ['taxnum'], ['state', 'county'] ],
470 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ] ],
475 'paynum', 'serial', '', '',
476 #now cust_bill_pay #'invnum', 'int', '', '',
477 'custnum', 'int', '', '',
480 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
481 # payment type table.
482 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
483 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
484 'closed', 'char', 'NULL', 1,
486 'primary_key' => 'paynum',
488 'index' => [ [ 'custnum' ], [ 'paybatch' ], [ 'payby' ], [ '_date' ] ],
493 'paynum', 'int', '', '',
494 'custnum', 'int', '', '',
497 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into
498 # payment type table.
499 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
500 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes.
501 'closed', 'char', 'NULL', 1,
502 'void_date', @date_type,
503 'reason', 'varchar', 'NULL', $char_d,
504 'otaker', 'varchar', '', 32,
506 'primary_key' => 'paynum',
508 'index' => [ [ 'custnum' ] ],
513 'billpaynum', 'serial', '', '',
514 'invnum', 'int', '', '',
515 'paynum', 'int', '', '',
516 'amount', @money_type,
519 'primary_key' => 'billpaynum',
521 'index' => [ [ 'paynum' ], [ 'invnum' ] ],
524 'cust_pay_batch' => { #what's this used for again? list of customers
525 #in current CARD batch? (necessarily CARD?)
527 'paybatchnum', 'serial', '', '',
528 'invnum', 'int', '', '',
529 'custnum', 'int', '', '',
530 'last', 'varchar', '', $char_d,
531 'first', 'varchar', '', $char_d,
532 'address1', 'varchar', '', $char_d,
533 'address2', 'varchar', 'NULL', $char_d,
534 'city', 'varchar', '', $char_d,
535 'state', 'varchar', 'NULL', $char_d,
536 'zip', 'varchar', 'NULL', 10,
537 'country', 'char', '', 2,
538 # 'trancode', 'int', '', '',
539 'cardnum', 'varchar', '', 16,
541 'exp', 'varchar', '', 11,
542 'payname', 'varchar', 'NULL', $char_d,
543 'amount', @money_type,
545 'primary_key' => 'paybatchnum',
547 'index' => [ ['invnum'], ['custnum'] ],
552 'pkgnum', 'serial', '', '',
553 'custnum', 'int', '', '',
554 'pkgpart', 'int', '', '',
555 'otaker', 'varchar', '', 32,
558 'last_bill', @date_type,
560 'cancel', @date_type,
561 'expire', @date_type,
562 'manual_flag', 'char', 'NULL', 1,
564 'primary_key' => 'pkgnum',
566 'index' => [ ['custnum'], ['pkgpart'] ],
571 'refundnum', 'serial', '', '',
572 #now cust_credit_refund #'crednum', 'int', '', '',
573 'custnum', 'int', '', '',
575 'refund', @money_type,
576 'otaker', 'varchar', '', 32,
577 'reason', 'varchar', '', $char_d,
578 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index
579 # into payment type table.
580 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above
581 'paybatch', 'varchar', 'NULL', $char_d,
582 'closed', 'char', 'NULL', 1,
584 'primary_key' => 'refundnum',
589 'cust_credit_refund' => {
591 'creditrefundnum', 'serial', '', '',
592 'crednum', 'int', '', '',
593 'refundnum', 'int', '', '',
594 'amount', @money_type,
597 'primary_key' => 'creditrefundnum',
599 'index' => [ [ 'crednum', 'refundnum' ] ],
605 'svcnum', 'serial', '', '',
606 'pkgnum', 'int', 'NULL', '',
607 'svcpart', 'int', '', '',
609 'primary_key' => 'svcnum',
611 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ],
616 'pkgpart', 'serial', '', '',
617 'pkg', 'varchar', '', $char_d,
618 'comment', 'varchar', '', $char_d,
619 'promo_code', 'varchar', 'NULL', $char_d,
621 'freq', 'varchar', '', $char_d, #billing frequency
623 'setuptax', 'char', 'NULL', 1,
624 'recurtax', 'char', 'NULL', 1,
625 'plan', 'varchar', 'NULL', $char_d,
626 'plandata', 'text', 'NULL', '',
627 'disabled', 'char', 'NULL', 1,
628 'taxclass', 'varchar', 'NULL', $char_d,
629 'classnum', 'int', 'NULL', '',
631 'primary_key' => 'pkgpart',
633 'index' => [ [ 'promo_code' ], [ 'disabled' ] ],
638 # 'titlenum', 'int', '', '',
639 # 'title', 'varchar', '', $char_d,
641 # 'primary_key' => 'titlenum',
642 # 'unique' => [ [] ],
648 'pkgsvcnum', 'serial', '', '',
649 'pkgpart', 'int', '', '',
650 'svcpart', 'int', '', '',
651 'quantity', 'int', '', '',
652 'primary_svc','char', 'NULL', 1,
654 'primary_key' => 'pkgsvcnum',
655 'unique' => [ ['pkgpart', 'svcpart'] ],
656 'index' => [ ['pkgpart'] ],
661 'refnum', 'serial', '', '',
662 'referral', 'varchar', '', $char_d,
663 'disabled', 'char', 'NULL', 1,
665 'primary_key' => 'refnum',
667 'index' => [ ['disabled'] ],
672 'svcpart', 'serial', '', '',
673 'svc', 'varchar', '', $char_d,
674 'svcdb', 'varchar', '', $char_d,
675 'disabled', 'char', 'NULL', 1,
677 'primary_key' => 'svcpart',
679 'index' => [ [ 'disabled' ] ],
682 'part_svc_column' => {
684 'columnnum', 'serial', '', '',
685 'svcpart', 'int', '', '',
686 'columnname', 'varchar', '', 64,
687 'columnvalue', 'varchar', 'NULL', $char_d,
688 'columnflag', 'char', 'NULL', 1,
690 'primary_key' => 'columnnum',
691 'unique' => [ [ 'svcpart', 'columnname' ] ],
692 'index' => [ [ 'svcpart' ] ],
695 #(this should be renamed to part_pop)
698 'popnum', 'serial', '', '',
699 'city', 'varchar', '', $char_d,
700 'state', 'varchar', '', $char_d,
702 'exch', 'char', '', 3,
703 'loc', 'char', 'NULL', 4, #NULL for legacy purposes
705 'primary_key' => 'popnum',
707 'index' => [ [ 'state' ] ],
710 'part_pop_local' => {
712 'localnum', 'serial', '', '',
713 'popnum', 'int', '', '',
714 'city', 'varchar', 'NULL', $char_d,
715 'state', 'char', 'NULL', 2,
716 'npa', 'char', '', 3,
717 'nxx', 'char', '', 3,
719 'primary_key' => 'localnum',
721 'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ],
726 'svcnum', 'int', '', '',
727 'username', 'varchar', '', $username_len, #unique (& remove dup code)
728 '_password', 'varchar', '', 72, #13 for encryped pw's plus ' *SUSPENDED* (md5 passwords can be 34, blowfish 60)
729 'sec_phrase', 'varchar', 'NULL', $char_d,
730 'popnum', 'int', 'NULL', '',
731 'uid', 'int', 'NULL', '',
732 'gid', 'int', 'NULL', '',
733 'finger', 'varchar', 'NULL', $char_d,
734 'dir', 'varchar', 'NULL', $char_d,
735 'shell', 'varchar', 'NULL', $char_d,
736 'quota', 'varchar', 'NULL', $char_d,
737 'slipip', 'varchar', 'NULL', 15, #four TINYINTs, bah.
738 'seconds', 'int', 'NULL', '', #uhhhh
739 'domsvc', 'int', '', '',
741 'primary_key' => 'svcnum',
742 #'unique' => [ [ 'username', 'domsvc' ] ],
744 'index' => [ ['username'], ['domsvc'] ],
749 # 'svcnum', 'int', '', '',
750 # 'amount', @money_type,
752 # 'primary_key' => 'svcnum',
753 # 'unique' => [ [] ],
759 'svcnum', 'int', '', '',
760 'domain', 'varchar', '', $char_d,
761 'catchall', 'int', 'NULL', '',
763 'primary_key' => 'svcnum',
764 'unique' => [ ['domain'] ],
770 'recnum', 'serial', '', '',
771 'svcnum', 'int', '', '',
772 #'reczone', 'varchar', '', $char_d,
773 'reczone', 'varchar', '', 255,
774 'recaf', 'char', '', 2,
775 'rectype', 'varchar', '', 5,
776 #'recdata', 'varchar', '', $char_d,
777 'recdata', 'varchar', '', 255,
779 'primary_key' => 'recnum',
781 'index' => [ ['svcnum'] ],
786 'svcnum', 'int', '', '',
787 'srcsvc', 'int', 'NULL', '',
788 'src', 'varchar', 'NULL', 255,
789 'dstsvc', 'int', 'NULL', '',
790 'dst', 'varchar', 'NULL', 255,
792 'primary_key' => 'svcnum',
794 'index' => [ ['srcsvc'], ['dstsvc'] ],
799 'svcnum', 'int', '', '',
800 'recnum', 'int', '', '',
801 'usersvc', 'int', '', '',
803 'primary_key' => 'svcnum',
810 # 'svcnum', 'int', '', '',
811 # 'svcnum', 'int', '', '',
812 # 'svcnum', 'int', '', '',
813 # 'worker', 'varchar', '', $char_d,
814 # '_date', @date_type,
816 # 'primary_key' => 'svcnum',
817 # 'unique' => [ [] ],
823 'prepaynum', 'serial', '', '',
824 'identifier', 'varchar', '', $char_d,
825 'amount', @money_type,
826 'seconds', 'int', 'NULL', '',
827 'agentnum', 'int', 'NULL', '',
829 'primary_key' => 'prepaynum',
830 'unique' => [ ['identifier'] ],
836 'portnum', 'serial', '', '',
837 'ip', 'varchar', 'NULL', 15,
838 'nasport', 'int', 'NULL', '',
839 'nasnum', 'int', '', '',
841 'primary_key' => 'portnum',
848 'nasnum', 'serial', '', '',
849 'nas', 'varchar', '', $char_d,
850 'nasip', 'varchar', '', 15,
851 'nasfqdn', 'varchar', '', $char_d,
852 'last', 'int', '', '',
854 'primary_key' => 'nasnum',
855 'unique' => [ [ 'nas' ], [ 'nasip' ] ],
856 'index' => [ [ 'last' ] ],
861 'sessionnum', 'serial', '', '',
862 'portnum', 'int', '', '',
863 'svcnum', 'int', '', '',
865 'logout', @date_type,
867 'primary_key' => 'sessionnum',
869 'index' => [ [ 'portnum' ] ],
874 'jobnum', 'serial', '', '',
875 'job', 'text', '', '',
876 '_date', 'int', '', '',
877 'status', 'varchar', '', $char_d,
878 'statustext', 'text', 'NULL', '',
879 'svcnum', 'int', 'NULL', '',
881 'primary_key' => 'jobnum',
883 'index' => [ [ 'svcnum' ], [ 'status' ] ],
888 'argnum', 'serial', '', '',
889 'jobnum', 'int', '', '',
890 'arg', 'text', 'NULL', '',
892 'primary_key' => 'argnum',
894 'index' => [ [ 'jobnum' ] ],
899 'dependnum', 'serial', '', '',
900 'jobnum', 'int', '', '',
901 'depend_jobnum', 'int', '', '',
903 'primary_key' => 'dependnum',
905 'index' => [ [ 'jobnum' ], [ 'depend_jobnum' ] ],
910 'exportsvcnum' => 'serial', '', '',
911 'exportnum' => 'int', '', '',
912 'svcpart' => 'int', '', '',
914 'primary_key' => 'exportsvcnum',
915 'unique' => [ [ 'exportnum', 'svcpart' ] ],
916 'index' => [ [ 'exportnum' ], [ 'svcpart' ] ],
921 'exportnum', 'serial', '', '',
922 #'svcpart', 'int', '', '',
923 'machine', 'varchar', '', $char_d,
924 'exporttype', 'varchar', '', $char_d,
925 'nodomain', 'char', 'NULL', 1,
927 'primary_key' => 'exportnum',
929 'index' => [ [ 'machine' ], [ 'exporttype' ] ],
932 'part_export_option' => {
934 'optionnum', 'serial', '', '',
935 'exportnum', 'int', '', '',
936 'optionname', 'varchar', '', $char_d,
937 'optionvalue', 'text', 'NULL', '',
939 'primary_key' => 'optionnum',
941 'index' => [ [ 'exportnum' ], [ 'optionname' ] ],
944 'radius_usergroup' => {
946 'usergroupnum', 'serial', '', '',
947 'svcnum', 'int', '', '',
948 'groupname', 'varchar', '', $char_d,
950 'primary_key' => 'usergroupnum',
952 'index' => [ [ 'svcnum' ], [ 'groupname' ] ],
957 'msgnum', 'serial', '', '',
958 'msgcode', 'varchar', '', $char_d,
959 'locale', 'varchar', '', 16,
960 'msg', 'text', '', '',
962 'primary_key' => 'msgnum',
963 'unique' => [ [ 'msgcode', 'locale' ] ],
967 'cust_tax_exempt' => {
969 'exemptnum', 'serial', '', '',
970 'custnum', 'int', '', '',
971 'taxnum', 'int', '', '',
972 'year', 'int', '', '',
973 'month', 'int', '', '',
974 'amount', @money_type,
976 'primary_key' => 'exemptnum',
977 'unique' => [ [ 'custnum', 'taxnum', 'year', 'month' ] ],
981 'cust_tax_exempt_pkg' => {
983 'exemptpkgnum', 'serial', '', '',
984 #'custnum', 'int', '', '',
985 'billpkgnum', 'int', '', '',
986 'taxnum', 'int', '', '',
987 'year', 'int', '', '',
988 'month', 'int', '', '',
989 'amount', @money_type,
991 'primary_key' => 'exemptpkgnum',
993 'index' => [ [ 'taxnum', 'year', 'month' ],
1001 'routernum', 'serial', '', '',
1002 'routername', 'varchar', '', $char_d,
1003 'svcnum', 'int', 'NULL', '',
1005 'primary_key' => 'routernum',
1010 'part_svc_router' => {
1012 'svcrouternum', 'serial', '', '',
1013 'svcpart', 'int', '', '',
1014 'routernum', 'int', '', '',
1016 'primary_key' => 'svcrouternum',
1023 'blocknum', 'serial', '', '',
1024 'routernum', 'int', '', '',
1025 'ip_gateway', 'varchar', '', 15,
1026 'ip_netmask', 'int', '', '',
1028 'primary_key' => 'blocknum',
1029 'unique' => [ [ 'blocknum', 'routernum' ] ],
1033 'svc_broadband' => {
1035 'svcnum', 'int', '', '',
1036 'blocknum', 'int', '', '',
1037 'speed_up', 'int', '', '',
1038 'speed_down', 'int', '', '',
1039 'ip_addr', 'varchar', '', 15,
1041 'primary_key' => 'svcnum',
1046 'part_virtual_field' => {
1048 'vfieldpart', 'int', '', '',
1049 'dbtable', 'varchar', '', 32,
1050 'name', 'varchar', '', 32,
1051 'check_block', 'text', 'NULL', '',
1052 'length', 'int', 'NULL', '',
1053 'list_source', 'text', 'NULL', '',
1054 'label', 'varchar', 'NULL', 80,
1056 'primary_key' => 'vfieldpart',
1061 'virtual_field' => {
1063 'vfieldnum', 'serial', '', '',
1064 'recnum', 'int', '', '',
1065 'vfieldpart', 'int', '', '',
1066 'value', 'varchar', '', 128,
1068 'primary_key' => 'vfieldnum',
1069 'unique' => [ [ 'vfieldpart', 'recnum' ] ],
1075 'snarfnum', 'int', '', '',
1076 'svcnum', 'int', '', '',
1077 'machine', 'varchar', '', 255,
1078 'protocol', 'varchar', '', $char_d,
1079 'username', 'varchar', '', $char_d,
1080 '_password', 'varchar', '', $char_d,
1082 'primary_key' => 'snarfnum',
1084 'index' => [ [ 'svcnum' ] ],
1089 'svcnum', 'int', '', '',
1090 'id', 'int', 'NULL', '',
1091 'title', 'varchar', 'NULL', $char_d,
1093 'primary_key' => 'svcnum',
1098 'cust_pay_refund' => {
1100 'payrefundnum', 'serial', '', '',
1101 'paynum', 'int', '', '',
1102 'refundnum', 'int', '', '',
1103 '_date', @date_type,
1104 'amount', @money_type,
1106 'primary_key' => 'payrefundnum',
1108 'index' => [ ['paynum'], ['refundnum'] ],
1111 'part_pkg_option' => {
1113 'optionnum', 'serial', '', '',
1114 'pkgpart', 'int', '', '',
1115 'optionname', 'varchar', '', $char_d,
1116 'optionvalue', 'text', 'NULL', '',
1118 'primary_key' => 'optionnum',
1120 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ],
1125 'ratenum', 'serial', '', '',
1126 'ratename', 'varchar', '', $char_d,
1128 'primary_key' => 'ratenum',
1135 'ratedetailnum', 'serial', '', '',
1136 'ratenum', 'int', '', '',
1137 'orig_regionnum', 'int', 'NULL', '',
1138 'dest_regionnum', 'int', '', '',
1139 'min_included', 'int', '', '',
1140 'min_charge', @money_type,
1141 'sec_granularity', 'int', '', '',
1142 #time period (link to table of periods)?
1144 'primary_key' => 'ratedetailnum',
1145 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
1146 'index' => [ [ 'ratenum', 'dest_regionnum' ] ],
1151 'regionnum', 'serial', '', '',
1152 'regionname', 'varchar', '', $char_d,
1154 'primary_key' => 'regionnum',
1161 'prefixnum', 'serial', '', '',
1162 'regionnum', 'int', '', '',,
1163 'countrycode', 'varchar', '', 3,
1164 'npa', 'varchar', 'NULL', 6,
1165 'nxx', 'varchar', 'NULL', 3,
1167 'primary_key' => 'prefixnum',
1169 'index' => [ [ 'countrycode' ], [ 'regionnum' ] ],
1174 'codenum', 'serial', '', '',
1175 'code', 'varchar', '', $char_d,
1176 'agentnum', 'int', '', '',
1178 'primary_key' => 'codenum',
1179 'unique' => [ [ 'agentnum', 'code' ] ],
1180 'index' => [ [ 'agentnum' ] ],
1185 'codepkgnum', 'serial', '', '',
1186 'codenum', 'int', '', '',
1187 'pkgpart', 'int', '', '',
1189 'primary_key' => 'codepkgnum',
1190 'unique' => [ [ 'codenum', 'pkgpart' ] ],
1191 'index' => [ [ 'codenum' ] ],
1194 'clientapi_session' => {
1196 'sessionnum', 'serial', '', '',
1197 'sessionid', 'varchar', '', $char_d,
1198 'namespace', 'varchar', '', $char_d,
1200 'primary_key' => 'sessionnum',
1201 'unique' => [ [ 'sessionid', 'namespace' ] ],
1205 'clientapi_session_field' => {
1207 'fieldnum', 'serial', '', '',
1208 'sessionnum', 'int', '', '',
1209 'fieldname', 'varchar', '', $char_d,
1210 'fieldvalue', 'text', 'NULL', '',
1212 'primary_key' => 'fieldnum',
1213 'unique' => [ [ 'sessionnum', 'fieldname' ] ],
1217 'payment_gateway' => {
1219 'gatewaynum', 'serial', '', '',
1220 'gateway_module', 'varchar', '', $char_d,
1221 'gateway_username', 'varchar', 'NULL', $char_d,
1222 'gateway_password', 'varchar', 'NULL', $char_d,
1223 'gateway_action', 'varchar', 'NULL', $char_d,
1224 'disabled', 'char', 'NULL', 1,
1226 'primary_key' => 'gatewaynum',
1228 'index' => [ [ 'disabled' ] ],
1231 'payment_gateway_option' => {
1233 'optionnum', 'serial', '', '',
1234 'gatewaynum', 'int', '', '',
1235 'optionname', 'varchar', '', $char_d,
1236 'optionvalue', 'text', 'NULL', '',
1238 'primary_key' => 'optionnum',
1240 'index' => [ [ 'gatewaynum' ], [ 'optionname' ] ],
1243 'agent_payment_gateway' => {
1245 'agentgatewaynum', 'serial', '', '',
1246 'agentnum', 'int', '', '',
1247 'gatewaynum', 'int', '', '',
1248 'cardtype', 'varchar', 'NULL', $char_d,
1249 'taxclass', 'varchar', 'NULL', $char_d,
1251 'primary_key' => 'agentgatewaynum',
1253 'index' => [ [ 'agentnum', 'cardtype' ], ],
1258 'bannum', 'serial', '', '',
1259 'payby', 'char', '', 4,
1260 'payinfo', 'varchar', '', 128, #say, a 512-big digest _hex encoded
1261 #'paymask', 'varchar', 'NULL', $char_d,
1262 '_date', @date_type,
1263 'otaker', 'varchar', '', 32,
1264 'reason', 'varchar', 'NULL', $char_d,
1266 'primary_key' => 'bannum',
1267 'unique' => [ [ 'payby', 'payinfo' ] ],
1271 'cancel_reason' => {
1273 'reasonnum', 'serial', '', '',
1274 'reason', 'varchar', '', $char_d,
1275 'disabled', 'char', 'NULL', 1,
1277 'primary_key' => 'reasonnum',
1279 'index' => [ [ 'disabled' ] ],
1284 'classnum', 'serial', '', '',
1285 'classname', 'varchar', '', $char_d,
1287 'primary_key' => 'classnum',