use vars qw(@ISA @EXPORT_OK $DEBUG $setup_hack %dbdef_cache);
use subs qw(reload_dbdef);
use Exporter;
-use DBIx::DBSchema 0.30;
+use DBIx::DBSchema 0.33;
use DBIx::DBSchema::Table;
use DBIx::DBSchema::Column 0.06;
-use DBIx::DBSchema::ColGroup::Unique;
-use DBIx::DBSchema::ColGroup::Index;
-use FS::UID qw(datasrc);
+use DBIx::DBSchema::Index;
@ISA = qw(Exporter);
@EXPORT_OK = qw( dbdef dbdef_dist reload_dbdef );
sub dbdef { $dbdef; }
-=item dbdef_dist [ OPTION => VALUE ... ]
+=item dbdef_dist [ DATASRC ]
Returns the current canoical database definition as defined in this file.
+Optionally, pass a DBI data source to enable syntax specific to that database.
+Currently, this enables "TYPE=InnoDB" for MySQL databases.
+
=cut
sub dbdef_dist {
+ my $datasrc = @_ ? shift : '';
+
+ my $local_options = '';
+ if ( $datasrc =~ /^dbi:mysql/i ) {
+ $local_options = 'TYPE=InnoDB';
+ }
###
# create a dbdef object from the old data structure
#turn it into objects
my $dbdef = new DBIx::DBSchema map {
+
+ my $tablename = $_;
+ my $indexnum = 1;
+
my @columns;
- while (@{$tables_hashref->{$_}{'columns'}}) {
+ while (@{$tables_hashref->{$tablename}{'columns'}}) {
#my($name, $type, $null, $length, $default, $local) =
my @coldef =
- splice @{$tables_hashref->{$_}{'columns'}}, 0, 6;
+ splice @{$tables_hashref->{$tablename}{'columns'}}, 0, 6;
my %hash = map { $_ => shift @coldef }
qw( name type null length default local );
unless ( defined $hash{'default'} ) {
- warn "$_:\n".
+ warn "$tablename:\n".
join('', map "$_ => $hash{$_}\n", keys %hash) ;# $stop = <STDIN>;
}
push @columns, new DBIx::DBSchema::Column ( \%hash );
}
- DBIx::DBSchema::Table->new(
- $_,
- $tables_hashref->{$_}{'primary_key'},
- DBIx::DBSchema::ColGroup::Unique->new($tables_hashref->{$_}{'unique'}),
- DBIx::DBSchema::ColGroup::Index->new($tables_hashref->{$_}{'index'}),
- @columns,
- );
+
+ #false laziness w/sub indices in DBIx::DBSchema::DBD (well, sorta)
+ #and sub sql_create_table in DBIx::DBSchema::Table (slighty more?)
+ my $unique = $tables_hashref->{$tablename}{'unique'};
+ my $index = $tables_hashref->{$tablename}{'index'};
+ my @indices = ();
+ push @indices, map {
+ DBIx::DBSchema::Index->new({
+ 'name' => $tablename. $indexnum++,
+ 'unique' => 1,
+ 'columns' => $_,
+ });
+ }
+ @$unique;
+ push @indices, map {
+ DBIx::DBSchema::Index->new({
+ 'name' => $tablename. $indexnum++,
+ 'unique' => 0,
+ 'columns' => $_,
+ });
+ }
+ @$index;
+
+ DBIx::DBSchema::Table->new({
+ 'name' => $tablename,
+ 'primary_key' => $tables_hashref->{$tablename}{'primary_key'},
+ 'columns' => \@columns,
+ 'indices' => \@indices,
+ 'local_options' => $local_options,
+ });
+
} keys %$tables_hashref;
if ( $DEBUG ) {
warn "[debug]$me $_\n" foreach $dbdef->tables;
}
- my $cust_main = $dbdef->table('cust_main');
- #unless ($ship) { #remove ship_ from cust_main
- # $cust_main->delcolumn($_) foreach ( grep /^ship_/, $cust_main->columns );
- #} else { #add indices
- push @{$cust_main->index->lol_ref},
- map { [ "ship_$_" ] } qw( last company daytime night fax );
- #}
-
#add radius attributes to svc_acct
#
#my($svc_acct)=$dbdef->table('svc_acct');
) {
my $tableobj = $dbdef->table($table)
or die "unknown table $table";
-
- die "unique->lol_ref undefined for $table"
- unless defined $tableobj->unique->lol_ref;
- die "index->lol_ref undefined for $table"
- unless defined $tableobj->index->lol_ref;
-
+
+ my %indices = $tableobj->indices;
+
+ my %h_indices = map {
+ ( "h_$_" =>
+ DBIx::DBSchema::Index->new({
+ 'name' => 'h_'. $indices{$_}->name,
+ 'unique' => 0,
+ 'columns' => [ @{$indices{$_}->columns} ],
+ })
+ );
+ }
+ keys %indices;
+
+ $h_indices{"h_${table}_srckey"} = DBIx::DBSchema::Index->new({
+ 'name' => "h_${table}_srckey",
+ 'unique' => 0,
+ 'columns' => [ 'history_action', #right?
+ $tableobj->primary_key,
+ ],
+ });
+
my $h_tableobj = DBIx::DBSchema::Table->new( {
- name => "h_$table",
- primary_key => 'historynum',
- unique => DBIx::DBSchema::ColGroup::Unique->new( [] ),
- 'index' => DBIx::DBSchema::ColGroup::Index->new( [
- @{$tableobj->unique->lol_ref},
- @{$tableobj->index->lol_ref}
- ] ),
- columns => [
- DBIx::DBSchema::Column->new( {
- 'name' => 'historynum',
- 'type' => 'serial',
- 'null' => 'NOT NULL',
- 'length' => '',
- 'default' => '',
- 'local' => '',
- } ),
- DBIx::DBSchema::Column->new( {
- 'name' => 'history_date',
- 'type' => 'int',
- 'null' => 'NULL',
- 'length' => '',
- 'default' => '',
- 'local' => '',
- } ),
- DBIx::DBSchema::Column->new( {
- 'name' => 'history_user',
- 'type' => 'varchar',
- 'null' => 'NOT NULL',
- 'length' => '80',
- 'default' => '',
- 'local' => '',
- } ),
- DBIx::DBSchema::Column->new( {
- 'name' => 'history_action',
- 'type' => 'varchar',
- 'null' => 'NOT NULL',
- 'length' => '80',
- 'default' => '',
- 'local' => '',
- } ),
- map {
- my $column = $tableobj->column($_);
-
- #clone so as to not disturb the original
- $column = DBIx::DBSchema::Column->new( {
- map { $_ => $column->$_() }
- qw( name type null length default local )
- } );
-
- if ( $column->type eq 'serial' ) {
- $column->type('int');
- $column->null('NULL');
- }
- #$column->default('')
- # if $column->default =~ /^nextval\(/i;
- #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
- #$column->local($local);
- $column;
- } $tableobj->columns
- ],
+ 'name' => "h_$table",
+ 'primary_key' => 'historynum',
+ 'indices' => \%h_indices,
+ 'local_options' => $local_options,
+ 'columns' => [
+ DBIx::DBSchema::Column->new( {
+ 'name' => 'historynum',
+ 'type' => 'serial',
+ 'null' => 'NOT NULL',
+ 'length' => '',
+ 'default' => '',
+ 'local' => '',
+ } ),
+ DBIx::DBSchema::Column->new( {
+ 'name' => 'history_date',
+ 'type' => 'int',
+ 'null' => 'NULL',
+ 'length' => '',
+ 'default' => '',
+ 'local' => '',
+ } ),
+ DBIx::DBSchema::Column->new( {
+ 'name' => 'history_user',
+ 'type' => 'varchar',
+ 'null' => 'NOT NULL',
+ 'length' => '80',
+ 'default' => '',
+ 'local' => '',
+ } ),
+ DBIx::DBSchema::Column->new( {
+ 'name' => 'history_action',
+ 'type' => 'varchar',
+ 'null' => 'NOT NULL',
+ 'length' => '80',
+ 'default' => '',
+ 'local' => '',
+ } ),
+ map {
+ my $column = $tableobj->column($_);
+
+ #clone so as to not disturb the original
+ $column = DBIx::DBSchema::Column->new( {
+ map { $_ => $column->$_() }
+ qw( name type null length default local )
+ } );
+
+ if ( $column->type =~ /^(\w*)SERIAL$/i ) {
+ $column->type('int');
+ $column->null('NULL');
+ }
+ #$column->default('')
+ # if $column->default =~ /^nextval\(/i;
+ #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
+ #$column->local($local);
+ $column;
+ } $tableobj->columns
+ ],
} );
$dbdef->addtable($h_tableobj);
}
+ if ( $datasrc =~ /^dbi:mysql/i ) {
+
+ my $dup_lock_table = DBIx::DBSchema::Table->new( {
+ 'name' => 'duplicate_lock',
+ 'primary_key' => 'duplocknum',
+ 'local_options' => $local_options,
+ 'columns' => [
+ DBIx::DBSchema::Column->new( {
+ 'name' => 'duplocknum',
+ 'type' => 'serial',
+ 'null' => 'NOT NULL',
+ 'length' => '',
+ 'default' => '',
+ 'local' => '',
+ } ),
+ DBIx::DBSchema::Column->new( {
+ 'name' => 'lockname',
+ 'type' => 'varchar',
+ 'null' => 'NOT NULL',
+ 'length' => '80',
+ 'default' => '',
+ 'local' => '',
+ } ),
+ ],
+ 'indices' => { 'duplicate_lock1' =>
+ DBIx::DBSchema::Index->new({
+ 'name' => 'duplicate_lock1',
+ 'unique' => 1,
+ 'columns' => [ 'lockname' ],
+ })
+ },
+ } );
+
+ $dbdef->addtable($dup_lock_table);
+
+ }
+
$dbdef;
}
my @date_type = ( 'int', 'NULL', '' );
my @perl_type = ( 'text', 'NULL', '' );
my @money_type = ( 'decimal', '', '10,2' );
+ my @money_typen = ( 'decimal', 'NULL', '10,2' );
my $username_len = 32; #usernamemax config file
'agent' => {
'columns' => [
- 'agentnum', 'serial', '', '', '', '',
- 'agent', 'varchar', '', $char_d, '', '',
- 'typenum', 'int', '', '', '', '',
- 'freq', 'int', 'NULL', '', '', '',
- 'prog', @perl_type, '', '',
- 'disabled', 'char', 'NULL', 1, '', '',
- 'username', 'varchar', 'NULL', $char_d, '', '',
- '_password','varchar', 'NULL', $char_d, '', '',
- 'ticketing_queueid', 'int', 'NULL', '', '', '',
+ 'agentnum', 'serial', '', '', '', '',
+ 'agent', 'varchar', '', $char_d, '', '',
+ 'typenum', 'int', '', '', '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ 'ticketing_queueid', 'int', 'NULL', '', '', '',
+ 'invoice_template', 'varchar', 'NULL', $char_d, '', '',
+ 'username', 'varchar', 'NULL', $char_d, '', '', #deprecated
+ '_password', 'varchar', 'NULL', $char_d, '', '', #deprecated
+ 'freq', 'int', 'NULL', '', '', '', #deprecated (never used)
+ 'prog', @perl_type, '', '', #deprecated (never used)
+
],
'primary_key' => 'agentnum',
'unique' => [],
'index' => [ ['payby'], ['disabled'], ],
},
+ 'part_event' => {
+ 'columns' => [
+ 'eventpart', 'serial', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'event', 'varchar', '', $char_d, '', '',
+ 'eventtable', 'varchar', '', $char_d, '', '',
+ 'check_freq', 'varchar', 'NULL', $char_d, '', '',
+ 'weight', 'int', '', '', '', '',
+ 'action', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'eventpart',
+ 'unique' => [],
+ 'index' => [ ['agentnum'], ['eventtable'], ['check_freq'], ['disabled'], ],
+ },
+
+ 'part_event_option' => {
+ 'columns' => [
+ 'optionnum', 'serial', '', '', '', '',
+ 'eventpart', 'int', '', '', '', '',
+ 'optionname', 'varchar', '', $char_d, '', '',
+ 'optionvalue', 'text', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'optionnum',
+ 'unique' => [],
+ 'index' => [ [ 'eventpart' ], [ 'optionname' ] ],
+ },
+
+ 'part_event_condition' => {
+ 'columns' => [
+ 'eventconditionnum', 'serial', '', '', '', '',
+ 'eventpart', 'int', '', '', '', '',
+ 'conditionname', 'varchar', '', $char_d, '', '',
+ ],
+ 'primary_key' => 'eventconditionnum',
+ 'unique' => [],
+ 'index' => [ [ 'eventpart' ], [ 'conditionname' ] ],
+ },
+
+ 'part_event_condition_option' => {
+ 'columns' => [
+ 'optionnum', 'serial', '', '', '', '',
+ 'eventconditionnum', 'int', '', '', '', '',
+ 'optionname', 'varchar', '', $char_d, '', '',
+ 'optionvalue', 'text', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'optionnum',
+ 'unique' => [],
+ 'index' => [ [ 'eventconditionnum' ], [ 'optionname' ] ],
+ },
+
+ 'part_event_condition_option_option' => {
+ 'columns' => [
+ 'optionoptionnum', 'serial', '', '', '', '',
+ 'optionnum', 'int', '', '', '', '',
+ 'optionname', 'varchar', '', $char_d, '', '',
+ 'optionvalue', 'text', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'optionoptionnum',
+ 'unique' => [],
+ 'index' => [ [ 'optionnum' ], [ 'optionname' ] ],
+ },
+
+ 'cust_event' => {
+ 'columns' => [
+ 'eventnum', 'serial', '', '', '', '',
+ 'eventpart', 'int', '', '', '', '',
+ 'tablenum', 'int', '', '', '', '',
+ '_date', @date_type, '', '',
+ 'status', 'varchar', '', $char_d, '', '',
+ 'statustext', 'text', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'eventnum',
+ #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
+ 'unique' => [],
+ 'index' => [ ['eventpart'], ['tablenum'], ['status'] ],
+ },
+
'cust_bill_pkg' => {
'columns' => [
'billpkgnum', 'serial', '', '', '', '',
'pkgnum', 'int', '', '', '', '',
+ 'pkgpart_override', 'int', 'NULL', '', '', '',
'invnum', 'int', '', '', '', '',
'setup', @money_type, '', '',
'recur', @money_type, '', '',
'sdate', @date_type, '', '',
'edate', @date_type, '', '',
'itemdesc', 'varchar', 'NULL', $char_d, '', '',
+ 'quantity', 'int', 'NULL', '', '', '',
+ 'unitsetup', @money_typen, '', '',
+ 'unitrecur', @money_typen, '', '',
],
'primary_key' => 'billpkgnum',
'unique' => [],
'detailnum', 'serial', '', '', '', '',
'pkgnum', 'int', '', '', '', '',
'invnum', 'int', '', '', '', '',
+ 'format', 'char', 'NULL', 1, '', '',
'detail', 'varchar', '', $char_d, '', '',
],
'primary_key' => 'detailnum',
'amount', @money_type, '', '',
'otaker', 'varchar', '', 32, '', '',
'reason', 'text', 'NULL', '', '', '',
+ 'reasonnum', 'int', 'NULL', '', '', '',
'closed', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'crednum',
'unique' => [],
- 'index' => [ ['custnum'] ],
+ 'index' => [ ['custnum'], ['_date'] ],
},
'cust_credit_bill' => {
# 'middle', 'varchar', 'NULL', $char_d, '', '',
'first', 'varchar', '', $char_d, '', '',
'ss', 'varchar', 'NULL', 11, '', '',
+ 'stateid', 'varchar', 'NULL', $char_d, '', '',
+ 'stateid_state', 'varchar', 'NULL', $char_d, '', '',
'birthdate' ,@date_type, '', '',
'signupdate',@date_type, '', '',
'company', 'varchar', 'NULL', $char_d, '', '',
'paystart_year', 'int', 'NULL', '', '', '',
'payissue', 'varchar', 'NULL', 2, '', '',
'payname', 'varchar', 'NULL', $char_d, '', '',
+ 'paystate', 'varchar', 'NULL', $char_d, '', '',
+ 'paytype', 'varchar', 'NULL', $char_d, '', '',
'payip', 'varchar', 'NULL', 15, '', '',
'tax', 'char', 'NULL', 1, '', '',
'otaker', 'varchar', '', 32, '', '',
'referral_custnum', 'int', 'NULL', '', '', '',
'comments', 'text', 'NULL', '', '', '',
'spool_cdr','char', 'NULL', 1, '', '',
+ 'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
],
'primary_key' => 'custnum',
'unique' => [ [ 'agentnum', 'agent_custid' ] ],
[ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'refnum' ],
[ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ],
[ 'ship_last' ], [ 'ship_company' ],
+ [ 'ship_daytime' ], [ 'ship_night' ], [ 'ship_fax' ],
[ 'payby' ], [ 'paydate' ],
-
],
},
'index' => [ [ 'county' ], [ 'state' ], [ 'country' ] ],
},
+ 'tax_rate' => {
+ 'columns' => [
+ 'taxnum', 'serial', '', '', '', '',
+ 'geocode', 'varchar', 'NULL', $char_d, '', '',#cch provides 10 char
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '',#auto update source
+ 'location', 'varchar', 'NULL', $char_d, '', '',#provided by tax authority
+ 'taxclassnum', 'int', '', '', '', '',
+ 'effective_date', @date_type, '', '',
+ 'tax', 'real', '', '', '', '', # tax %
+ 'excessrate', 'real', 'NULL','', '', '', # second tax %
+ 'taxbase', @money_typen, '', '', # amount at first tax rate
+ 'taxmax', @money_typen, '', '', # maximum about at both rates
+ 'usetax', 'real', 'NULL', '', '', '', # tax % when non-local
+ 'useexcessrate', 'real', 'NULL', '', '', '', # second tax % when non-local
+ 'unittype', 'int', 'NULL', '', '', '', # for fee
+ 'fee', 'real', 'NULL', '', '', '', # amount tax per unit
+ 'excessfee', 'real', 'NULL', '', '', '', # second amount tax per unit
+ 'feebase', 'real', 'NULL', '', '', '', # units taxed at first rate
+ 'feemax', 'real', 'NULL', '', '', '', # maximum number of unit taxed
+ 'maxtype', 'int', 'NULL', '', '', '', # indicator of how thresholds accumulate
+ 'taxname', 'varchar', 'NULL', $char_d, '', '', # may appear on invoice
+ 'taxauth', 'int', 'NULL', '', '', '', # tax authority
+ 'basetype', 'int', 'NULL', '', '', '', # indicator of basis for tax
+ 'passtype', 'int', 'NULL', '', '', '', # indicator declaring how item should be shown
+ 'passflag', 'char', 'NULL', 1, '', '', # Y = required to list as line item, N = Prohibited
+ 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt
+ 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt
+ 'manual', 'char', 'NULL', 1, '', '', # Y = manually edited
+ ],
+ 'primary_key' => 'taxnum',
+ 'unique' => [],
+ 'index' => [ ['taxclassnum'], ['data_vendor', 'geocode'] ],
+ },
+
+ 'cust_tax_location' => {
+ 'columns' => [
+ 'custlocationnum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '', # update source
+ 'zip', 'char', '', 5, '', '',
+ 'state', 'char', '', 2, '', '',
+ 'plus4hi', 'char', '', 4, '', '',
+ 'plus4lo', 'char', '', 4, '', '',
+ 'default_location','char', 'NULL', 1, '', '', # Y = default for zip
+ 'geocode', 'varchar', '', 20, '', '',
+ ],
+ 'primary_key' => 'custlocationnum',
+ 'unique' => [],
+ 'index' => [ [ 'zip', 'plus4lo', 'plus4hi' ] ],
+ },
+
+ 'tax_class' => {
+ 'columns' => [
+ 'taxclassnum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
+ 'taxclass', 'varchar', '', $char_d, '', '',
+ 'description', 'varchar', '', 2*$char_d, '', '',
+ ],
+ 'primary_key' => 'taxclassnum',
+ 'unique' => [ [ 'data_vendor', 'taxclass' ] ],
+ 'index' => [],
+ },
+
+ 'cust_pay_pending' => {
+ 'columns' => [
+ 'paypendingnum','serial', '', '', '', '',
+ 'custnum', 'int', '', '', '', '',
+ 'paid', @money_type, '', '',
+ '_date', @date_type, '', '',
+ 'payby', 'char', '', 4, '', '', #CARD/BILL/COMP, should
+ # be index into payby
+ # table eventually
+ 'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above
+ 'paymask', 'varchar', 'NULL', $char_d, '', '',
+ 'paydate', 'varchar', 'NULL', 10, '', '',
+ #'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes.
+ 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage
+
+ 'status', 'varchar', '', $char_d, '', '',
+ 'statustext', 'text', 'NULL', '', '', '',
+ 'gatewaynum', 'int', 'NULL', '', '', '',
+ #'cust_balance', @money_type, '', '',
+ 'paynum', 'int', 'NULL', '', '', '',
+ ],
+ 'primary_key' => 'paypendingnum',
+ 'unique' => [ [ 'payunique' ] ],
+ 'index' => [ [ 'custnum' ], [ 'status' ], ],
+ },
+
'cust_pay' => {
'columns' => [
'paynum', 'serial', '', '', '', '',
- #now cust_bill_pay #'invnum', 'int', '', '', '', '',
'custnum', 'int', '', '', '', '',
- 'paid', @money_type, '', '',
'_date', @date_type, '', '',
+ 'paid', @money_type, '', '',
+ 'otaker', 'varchar', 'NULL', 32, '', '', #NULL for the upgrade so we can create & populate the field
'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should be
# index into payby table
# eventually
'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above
'paymask', 'varchar', 'NULL', $char_d, '', '',
+ 'paydate', 'varchar', 'NULL', 10, '', '',
'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes.
+ 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage
'closed', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'paynum',
- 'unique' => [],
+ #i guess not now, with cust_pay_pending, if we actually make it here, we _do_ want to record it# 'unique' => [ [ 'payunique' ] ],
'index' => [ [ 'custnum' ], [ 'paybatch' ], [ 'payby' ], [ '_date' ] ],
},
'cust_pkg' => {
'columns' => [
- 'pkgnum', 'serial', '', '', '', '',
- 'custnum', 'int', '', '', '', '',
- 'pkgpart', 'int', '', '', '', '',
- 'otaker', 'varchar', '', 32, '', '',
- 'setup', @date_type, '', '',
- 'bill', @date_type, '', '',
- 'last_bill', @date_type, '', '',
- 'susp', @date_type, '', '',
- 'cancel', @date_type, '', '',
- 'expire', @date_type, '', '',
- 'manual_flag', 'char', 'NULL', 1, '', '',
+ 'pkgnum', 'serial', '', '', '', '',
+ 'custnum', 'int', '', '', '', '',
+ 'pkgpart', 'int', '', '', '', '',
+ 'otaker', 'varchar', '', 32, '', '',
+ 'setup', @date_type, '', '',
+ 'bill', @date_type, '', '',
+ 'last_bill', @date_type, '', '',
+ 'susp', @date_type, '', '',
+ 'adjourn', @date_type, '', '',
+ 'cancel', @date_type, '', '',
+ 'expire', @date_type, '', '',
+ 'change_date', @date_type, '', '',
+ 'change_pkgnum', 'int', 'NULL', '', '', '',
+ 'change_pkgpart', 'int', 'NULL', '', '', '',
+ 'manual_flag', 'char', 'NULL', 1, '', '',
+ 'quantity', 'int', 'NULL', '', '', '',
],
'primary_key' => 'pkgnum',
'unique' => [],
- 'index' => [ ['custnum'], ['pkgpart'] ],
+ 'index' => [ ['custnum'], ['pkgpart'],
+ ['setup'], ['last_bill'], ['bill'], ['susp'], ['adjourn'],
+ ['expire'], ['cancel'],
+ ['change_date'],
+ ],
},
'cust_pkg_option' => {
'cust_refund' => {
'columns' => [
'refundnum', 'serial', '', '', '', '',
- #now cust_credit_refund #'crednum', 'int', '', '', '', '',
'custnum', 'int', '', '', '', '',
'_date', @date_type, '', '',
'refund', @money_type, '', '',
],
'primary_key' => 'refundnum',
'unique' => [],
- 'index' => [],
+ 'index' => [ ['custnum'], ['_date'] ],
},
'cust_credit_refund' => {
],
'primary_key' => 'creditrefundnum',
'unique' => [],
- 'index' => [ [ 'crednum', 'refundnum' ] ],
+ 'index' => [ ['crednum'], ['refundnum'] ],
},
'svcnum', 'serial', '', '', '', '',
'pkgnum', 'int', 'NULL', '', '', '',
'svcpart', 'int', '', '', '', '',
+ 'overlimit', @date_type, '', '',
],
'primary_key' => 'svcnum',
'unique' => [],
'disabled', 'char', 'NULL', 1, '', '',
'taxclass', 'varchar', 'NULL', $char_d, '', '',
'classnum', 'int', 'NULL', '', '', '',
+ 'taxproductnum', 'int', 'NULL', '', '', '',
'pay_weight', 'real', 'NULL', '', '', '',
'credit_weight', 'real', 'NULL', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+
],
'primary_key' => 'pkgpart',
'unique' => [],
- 'index' => [ [ 'promo_code' ], [ 'disabled' ] ],
+ 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'agentnum' ], ],
+ },
+
+ 'part_pkg_link' => {
+ 'columns' => [
+ 'pkglinknum', 'serial', '', '', '', '',
+ 'src_pkgpart', 'int', '', '', '', '',
+ 'dst_pkgpart', 'int', '', '', '', '',
+ 'link_type', 'varchar', '', $char_d, '', '',
+ ],
+ 'primary_key' => 'pkglinknum',
+ 'unique' => [ [ 'src_pkgpart', 'dst_pkgpart', 'link_type' ] ],
+ 'index' => [ [ 'src_pkgpart' ] ],
+ },
+
+ 'part_pkg_taxclass' => {
+ 'columns' => [
+ 'taxclassnum', 'serial', '', '', '', '',
+ 'taxclass', 'varchar', '', $char_d, '', '',
+ ],
+ 'primary_key' => 'taxclassnum',
+ 'unique' => [ [ 'taxclass' ] ],
+ 'index' => [],
+ },
+
+ 'part_pkg_taxproduct' => {
+ 'columns' => [
+ 'taxproductnum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
+ 'taxproduct', 'varchar', '', $char_d, '', '',
+ 'description', 'varchar', '', 2*$char_d, '', '',
+ ],
+ 'primary_key' => 'taxproductnum',
+ 'unique' => [ [ 'data_vendor', 'taxproduct' ] ],
+ 'index' => [],
+ },
+
+ 'part_pkg_taxrate' => {
+ 'columns' => [
+ 'pkgtaxratenum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '', # update source
+ 'geocode', 'varchar', 'NULL', $char_d, '', '', # cch provides 10
+ 'taxproductnum', 'int', '', '', '', '',
+ 'city', 'varchar', 'NULL', $char_d, '', '', # tax_location?
+ 'county', 'varchar', 'NULL', $char_d, '', '',
+ 'state', 'varchar', 'NULL', $char_d, '', '',
+ 'local', 'varchar', 'NULL', $char_d, '', '',
+ 'country', 'char', 'NULL', 2, '', '',
+ 'taxclassnumtaxed', 'int', 'NULL', '', '', '',
+ 'taxcattaxed', 'varchar', 'NULL', $char_d, '', '',
+ 'taxclassnum', 'int', 'NULL', '', '', '',
+ 'effdate', @date_type, '', '',
+ 'taxable', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'pkgtaxratenum',
+ 'unique' => [],
+ 'index' => [ [ 'data_vendor', 'geocode', 'taxproductnum' ] ],
+ },
+
+ 'part_pkg_taxoverride' => {
+ 'columns' => [
+ 'taxoverridenum', 'serial', '', '', '', '',
+ 'pkgpart', 'serial', '', '', '', '',
+ 'taxclassnum', 'serial', '', '', '', '',
+ ],
+ 'primary_key' => 'taxoverridenum',
+ 'unique' => [],
+ 'index' => [ [ 'pkgpart' ], [ 'taxclassnum' ] ],
},
# 'part_title' => {
],
'primary_key' => 'refnum',
'unique' => [],
- 'index' => [ ['disabled'] ],
+ 'index' => [ ['disabled'], ['agentnum'], ],
},
'part_svc' => {
'totalbytes','bigint', 'NULL', '', '', '',
'totalbytes_threshold', 'bigint', 'NULL', '', '', '',
'domsvc', 'int', '', '', '', '',
+ 'last_login', @date_type, '', '',
+ 'last_logout', @date_type, '', '',
],
'primary_key' => 'svcnum',
#'unique' => [ [ 'username', 'domsvc' ] ],
'index' => [ ['username'], ['domsvc'] ],
},
+ 'acct_rt_transaction' => {
+ 'columns' => [
+ 'svcrtid', 'int', '', '', '', '',
+ 'svcnum', 'int', '', '', '', '',
+ 'transaction_id', 'int', '', '', '', '',
+ '_date', @date_type, '', '',
+ 'seconds', 'int', '', '', '', '', #uhhhh
+ 'support', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'svcrtid',
+ 'unique' => [],
+ 'index' => [ ['svcnum', 'transaction_id'] ],
+ },
+
#'svc_charge' => {
# 'columns' => [
# 'svcnum', 'int', '', '',
'catchall', 'int', 'NULL', '', '', '',
'parent_svcnum', 'int', 'NULL', '', '', '',
'registrarnum', 'int', 'NULL', '', '', '',
- 'registrarkey', 'varchar', 'NULL', '', '', '',
+ 'registrarkey', 'varchar', 'NULL', 512, '', '',
'setup_date', @date_type, '', '',
'renewal_interval', 'int', 'NULL', '', '', '',
'expiration_date', @date_type, '', '',
'svc_www' => {
'columns' => [
- 'svcnum', 'int', '', '', '', '',
- 'recnum', 'int', '', '', '', '',
- 'usersvc', 'int', '', '', '', '',
+ 'svcnum', 'int', '', '', '', '',
+ 'recnum', 'int', '', '', '', '',
+ 'usersvc', 'int', 'NULL', '', '', '',
+ 'config', 'text', 'NULL', '', '', '',
],
'primary_key' => 'svcnum',
'unique' => [],
'index' => [ [ 'last' ] ],
},
- 'session' => {
- 'columns' => [
- 'sessionnum', 'serial', '', '', '', '',
- 'portnum', 'int', '', '', '', '',
- 'svcnum', 'int', '', '', '', '',
- 'login', @date_type, '', '',
- 'logout', @date_type, '', '',
- ],
- 'primary_key' => 'sessionnum',
- 'unique' => [],
- 'index' => [ [ 'portnum' ] ],
- },
+# 'session' => {
+# 'columns' => [
+# 'sessionnum', 'serial', '', '', '', '',
+# 'portnum', 'int', '', '', '', '',
+# 'svcnum', 'int', '', '', '', '',
+# 'login', @date_type, '', '',
+# 'logout', @date_type, '', '',
+# ],
+# 'primary_key' => 'sessionnum',
+# 'unique' => [],
+# 'index' => [ [ 'portnum' ] ],
+# },
'queue' => {
'columns' => [
'status', 'varchar', '', $char_d, '', '',
'statustext', 'text', 'NULL', '', '', '',
'svcnum', 'int', 'NULL', '', '', '',
+ 'secure', 'char', 'NULL', 1, '', '', # Y = needs to be run on machine
+ # w/private key
],
'primary_key' => 'jobnum',
'unique' => [],
'routernum', 'serial', '', '', '', '',
'routername', 'varchar', '', $char_d, '', '',
'svcnum', 'int', 'NULL', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
],
'primary_key' => 'routernum',
'unique' => [],
'routernum', 'int', '', '', '', '',
'ip_gateway', 'varchar', '', 15, '', '',
'ip_netmask', 'int', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
],
'primary_key' => 'blocknum',
'unique' => [ [ 'blocknum', 'routernum' ] ],
'part_virtual_field' => {
'columns' => [
- 'vfieldpart', 'int', '', '', '', '',
+ 'vfieldpart', 'serial', '', '', '', '',
'dbtable', 'varchar', '', 32, '', '',
'name', 'varchar', '', 32, '', '',
'check_block', 'text', 'NULL', '', '', '',
'index' => [],
},
+ 'pkg_category' => {
+ 'columns' => [
+ 'categorynum', 'serial', '', '', '', '',
+ 'categoryname', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'categorynum',
+ 'unique' => [],
+ 'index' => [ ['disabled'] ],
+ },
+
'pkg_class' => {
'columns' => [
- 'classnum', 'serial', '', '', '', '',
- 'classname', 'varchar', '', $char_d, '', '',
- 'disabled', 'char', 'NULL', 1, '', '',
+ 'classnum', 'serial', '', '', '', '',
+ 'classname', 'varchar', '', $char_d, '', '',
+ 'categorynum', 'int', 'NULL', '', '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'classnum',
'unique' => [],
###
'acctid', 'bigserial', '', '', '', '',
- 'calldate', 'TIMESTAMP with time zone', '', '', \'now()', '',
+ #'calldate', 'TIMESTAMP with time zone', '', '', \'now()', '',
+ 'calldate', 'timestamp', '', '', \'now()', '',
'clid', 'varchar', '', $char_d, \"''", '',
'src', 'varchar', '', $char_d, \"''", '',
'dst', 'varchar', '', $char_d, \"''", '',
'cdr_type' => {
'columns' => [
'cdrtypenum' => 'serial', '', '', '', '',
- 'cdrtypename' => 'varchar', '', '', '', '',
+ 'cdrtypename' => 'varchar', '', $char_d, '', '',
],
'primary_key' => 'cdrtypenum',
'unique' => [],
'cdr_carrier' => {
'columns' => [
'carrierid' => 'serial', '', '', '', '',
- 'carriername' => 'varchar', '', '', '', '',
+ 'carriername' => 'varchar', '', $char_d, '', '',
],
'primary_key' => 'carrierid',
'unique' => [],
'usernum', 'int', '', '', '', '',
'prefname', 'varchar', '', $char_d, '', '',
'prefvalue', 'text', 'NULL', '', '', '',
+ 'expiration', @date_type, '', '',
],
'primary_key' => 'prefnum',
'unique' => [],
'rightnum', 'serial', '', '', '', '',
'righttype', 'varchar', '', $char_d, '', '',
'rightobjnum', 'int', '', '', '', '',
- 'rightname', 'varchar', '', '', '', '',
+ 'rightname', 'varchar', '', $char_d, '', '',
],
'primary_key' => 'rightnum',
'unique' => [ [ 'righttype', 'rightobjnum', 'rightname' ] ],
'columns' => [
'reasonnum', 'serial', '', '', '', '',
'reason_type', 'int', '', '', '', '',
- 'reason', 'varchar', '', $char_d, '', '',
+ 'reason', 'text', '', '', '', '',
'disabled', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'reasonnum',
'confnum', 'serial', '', '', '', '',
'agentnum', 'int', 'NULL', '', '', '',
'name', 'varchar', '', $char_d, '', '',
- 'value', 'varchar', 'NULL', '', '', '', # Pg specific
+ 'value', 'text', 'NULL', '', '', '',
],
'primary_key' => 'confnum',
'unique' => [ [ 'agentnum', 'name' ]],
'index' => [],
},
+ 'pkg_referral' => {
+ 'columns' => [
+ 'pkgrefnum', 'serial', '', '', '', '',
+ 'pkgnum', 'int', '', '', '', '',
+ 'refnum', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'pkgrefnum',
+ 'unique' => [ [ 'pkgnum', 'refnum' ] ],
+ 'index' => [ [ 'pkgnum' ], [ 'refnum' ] ],
+ },
# name type nullability length default local
#'new_table' => {
projects / freeside.git / blobdiff