path: root/bin/fs-setup

#!/usr/bin/perl -Tw
#
# create database and necessary tables, etc.  DBI version.
#
# ivan@sisd.com 97-nov-8,9
#
# agent_type and type_pkgs added.
# (index need to be declared, & primary keys shoudln't have mysql syntax)
# ivan@sisd.com 97-nov-13
#
# pulled modified version back out of register.cgi ivan@sisd.com 98-feb-21
#
# removed extraneous sample data ivan@sisd.com 98-mar-23
#
# gained the big hash from dbdef.pm, dbdef.pm usage rewrite ivan@sisd.com
# 98-apr-19 - 98-may-11 plus
#
# finished up ivan@sisd.com 98-jun-1
#
# part_svc fields are all forced NULL, not the opposite
# hmm: also are forced varchar($char_d) as fixed '0' for things like
# uid is Not Good.  will this break anything else?
# ivan@sisd.com 98-jun-29
#
# ss is 11 chars ivan@sisd.com 98-jul-20
#
# setup of arbitrary radius fields ivan@sisd.com 98-aug-9
#
# ouch, removed index on company name that wasn't supposed to be there
# ivan@sisd.com 98-sep-4
#
# fix radius attributes ivan@sisd.com 98-sep-27

#to delay loading dbdef until we're ready
BEGIN { $FS::Record::setup_hack = 1; }

use strict;
use DBI;
use FS::dbdef;
use FS::UID qw(adminsuidsetup datasrc);
use FS::Record;
use FS::cust_main_county;

#needs to match FS::Record
my($dbdef_file) = "/var/spool/freeside/dbdef.". datasrc;

###

print "\nEnter the maximum username length: ";
my($username_len)=&getvalue;

print "\n\n", <<END, ":";
Freeside tracks the RADIUS attributes User-Name, Password and Framed-IP-Address
for each user.  Enter any additional RADIUS attributes you need to track for
each user, separated by whitespace.
END
my @attributes = map { s/\-/_/g; $_; } split(" ",&getvalue);

sub getvalue {
  my($x)=scalar(<STDIN>);
  chop $x;
  $x;
}

###

my($char_d) = 80; #default maxlength for text fields

#my(@date_type)  = ( 'timestamp', '', ''     );
my(@date_type)  = ( 'int', 'NULL', ''     );
my(@perl_type) = ( 'long varchar', 'NULL', ''   ); 
my(@money_type);
if (datasrc =~ m/Pg/) { #Pg can't do decimal(10,2)
  @money_type = ( 'money',   '', '' );
} else {
  @money_type = ( 'decimal',   '', '10,2' );
}

###
# create a dbdef object from the old data structure
###

my(%tables)=&tables_hash_hack;

#turn it into objects
my($dbdef) = new FS::dbdef ( map {  
  my(@columns);
  while (@{$tables{$_}{'columns'}}) {
    my($name,$type,$null,$length)=splice @{$tables{$_}{'columns'}}, 0, 4;
    push @columns, new FS::dbdef_column ( $name,$type,$null,$length );
  }
  FS::dbdef_table->new(
    $_,
    $tables{$_}{'primary_key'},
    #FS::dbdef_unique->new(@{$tables{$_}{'unique'}}),
    #FS::dbdef_index->new(@{$tables{$_}{'index'}}),
    FS::dbdef_unique->new($tables{$_}{'unique'}),
    FS::dbdef_index->new($tables{$_}{'index'}),
    @columns,
  );
} (keys %tables) );

#add radius attributes to svc_acct

my($svc_acct)=$dbdef->table('svc_acct');

my($attribute);
foreach $attribute (@attributes) {
  $svc_acct->addcolumn ( new FS::dbdef_column (
    'radius_'. $attribute,
    'varchar',
    'NULL',
    $char_d,
  ));
}

#make part_svc table (but now as object)

my($part_svc)=$dbdef->table('part_svc');

#because of svc_acct_pop
#foreach (grep /^svc_/, $dbdef->tables) { 
#foreach (qw(svc_acct svc_acct_sm svc_charge svc_domain svc_wo)) {
foreach (qw(svc_acct svc_acct_sm svc_domain)) {
  my($table)=$dbdef->table($_);
  my($col);
  foreach $col ( $table->columns ) {
    next if $col =~ /^svcnum$/;
    $part_svc->addcolumn( new FS::dbdef_column (
      $table->name. '__' . $table->column($col)->name,
      'varchar', #$table->column($col)->type, 
      'NULL',
      $char_d, #$table->column($col)->length,
    ));
    $part_svc->addcolumn ( new FS::dbdef_column (
      $table->name. '__'. $table->column($col)->name . "_flag",
      'char',
      'NULL',
      1,
    ));
  }
}

#important
$dbdef->save($dbdef_file);
FS::Record::reload_dbdef;

###
# create 'em
###

my($dbh)=adminsuidsetup;

#create tables
$|=1;

my($table);
foreach  ($dbdef->tables) {
  my($table)=$dbdef->table($_);
  print "Creating $_...";

  my($statement);

  #create table
  foreach $statement ($table->sql_create_table(datasrc)) {
    #print $statement, "\n"; 
    $dbh->do( $statement )
      or die "CREATE error: ",$dbh->errstr, "\ndoing statement: $statement";
  }

  print "\n";
}

#not really sample data (and shouldn't default to US)

#cust_main_county
foreach ( qw(
AL AK AS AZ AR CA CO CT DC DE FM FL GA GU HI ID IL IN IA KS KY LA
ME MH MD MA MI MN MS MO MT NC ND NE NH NJ NM NV NY MP OH OK OR PA PW PR RI 
SC SD TN TX TT UT VT VI VA WA WV WI WY AE AA AP
) ) {
  my($cust_main_county)=create FS::cust_main_county({
    'state' => $_,
    'tax'   => 0,
  });  
  my($error);
  $error=$cust_main_county->insert;
  die $error if $error;
}

$dbh->disconnect or die $dbh->errstr;

###
# Now it becomes an object.  much better.
###
sub tables_hash_hack {

  #note that s/(date|change)/_$1/; to avoid keyword conflict.
  #put a kludge in FS::Record to catch this or? (pry need some date-handling
  #stuff anyway also)

  my(%tables)=( #yech.}

    'agent' => {
      'columns' => [
        'agentnum', 'int',            '',     '',
        'agent',    'varchar',           '',     $char_d,
        'typenum',  'int',            '',     '',
        'freq',     'smallint',       'NULL', '',
        'prog',     @perl_type,
      ],
      'primary_key' => 'agentnum',
      'unique' => [ [] ],
      'index' => [ ['typenum'] ],
    },

    'agent_type' => {
      'columns' => [
        'typenum',   'int',  '', '',
        'atype',     'varchar', '', $char_d,
      ],
      'primary_key' => 'typenum',
      'unique' => [ [] ],
      'index' => [ [] ],
    },

    'type_pkgs' => {
      'columns' => [
        'typenum',   'int',  '', '',
        'pkgpart',   'int',  '', '',
      ],
      'primary_key' => '',
      'unique' => [ ['typenum', 'pkgpart'] ],
      'index' => [ ['typenum'] ],
    },

    'cust_bill' => {
      'columns' => [
        'invnum',    'int',  '', '',
        'custnum',   'int',  '', '',
        '_date',     @date_type,
        'charged',   @money_type,
        'owed',      @money_type,
        'printed',   'int',  '', '',
      ],
      'primary_key' => 'invnum',
      'unique' => [ [] ],
      'index' => [ ['custnum'] ],
    },

    'cust_bill_pkg' => {
      'columns' => [
        'pkgnum',  'int', '', '',
        'invnum',  'int', '', '',
        'setup',   @money_type,
        'recur',   @money_type,
        'sdate',   @date_type,
        'edate',   @date_type,
      ],
      'primary_key' => '',
      'unique' => [ ['pkgnum', 'invnum'] ],
      'index' => [ ['invnum'] ],
    },

    'cust_credit' => {
      'columns' => [
        'crednum',  'int', '', '',
        'custnum',  'int', '', '',
        '_date',    @date_type,
        'amount',   @money_type,
        'credited', @money_type,
        'otaker',   'varchar', '', 8,
        'reason',   'varchar', '', 255,
      ],
      'primary_key' => 'crednum',
      'unique' => [ [] ],
      'index' => [ ['custnum'] ],
    },

    'cust_main' => {
      'columns' => [
        'custnum',  'int',  '',     '',
        'agentnum', 'int',  '',     '',
        'last',     'varchar', '',     $char_d,
        'first',    'varchar', '',     $char_d,
        'ss',       'char', 'NULL', 11,
        'company',  'varchar', 'NULL', $char_d,
        'address1', 'varchar', '',     $char_d,
        'address2', 'varchar', 'NULL', $char_d,
        'city',     'varchar', '',     $char_d,
        'county',   'varchar', 'NULL', $char_d,
        'state',    'char', '',     2,
        'zip',      'varchar', '',     10,
        'country',  'char', '',     2,
        'daytime',  'varchar', 'NULL', 20,
        'night',    'varchar', 'NULL', 20,
        'fax',      'varchar', 'NULL', 12,
        'payby',    'char', '',     4,
        'payinfo',  'varchar', 'NULL', 16,
        'paydate',  @date_type,
        'payname',  'varchar', 'NULL', $char_d,
        'tax',      'char', 'NULL', 1,
        'otaker',   'varchar', '',     8,
        'refnum',   'int',  '',     '',
      ],
      'primary_key' => 'custnum',
      'unique' => [ [] ],
      #'index' => [ ['last'], ['company'] ],
      'index' => [ ['last'], ],
    },

    'cust_main_county' => { #county+state are checked off the cust_main_county
                            #table for validation and to provide a tax rate.
                            #add country?
      'columns' => [
        'taxnum',   'int',   '',    '',
        'state',    'char',  '',    2,  #two letters max in US... elsewhere?
        'county',   'varchar',  '',    $char_d,
        'tax',      'real',  '',    '', #tax %
      ],
      'primary_key' => 'taxnum',
      'unique' => [ [] ],
  #    'unique' => [ ['taxnum'], ['state', 'county'] ],
      'index' => [ [] ],
    },

    'cust_pay' => {
      'columns' => [
        'paynum',   'int',    '',   '',
        'invnum',   'int',    '',   '',
        'paid',     @money_type,
        '_date',    @date_type,
        'payby',    'char',   '',     4, # CARD/BILL/COMP, should be index into
                                         # payment type table.
        'payinfo',  'varchar',   'NULL', 16,  #see cust_main above
        'paybatch', 'varchar',   'NULL', $char_d, #for auditing purposes.
      ],
      'primary_key' => 'paynum',
      'unique' => [ [] ],
      'index' => [ ['invnum'] ],
    },

    'cust_pay_batch' => { #what's this used for again?  list of customers
                          #in current CARD batch? (necessarily CARD?)
      'columns' => [
        'invnum',   'int',    '',   '',
        'custnum',   'int',    '',   '',
        'last',     'varchar', '',     $char_d,
        'first',    'varchar', '',     $char_d,
        'address1', 'varchar', '',     $char_d,
        'address2', 'varchar', 'NULL', $char_d,
        'city',     'varchar', '',     $char_d,
        'state',    'char', '',     2,
        'zip',      'varchar', '',     10,
        'country',  'char', '',     2,
        'trancode', 'TINYINT', '', '',
        'cardnum',  'varchar', '',     16,
        'exp',      @date_type,
        'payname',  'varchar', 'NULL', $char_d,
        'amount',   @money_type,
      ],
      'primary_key' => '',
      'unique' => [ [] ],
      'index' => [ ['invnum'], ['custnum'] ],
    },

    'cust_pkg' => {
      'columns' => [
        'pkgnum',    'int',    '',   '',
        'custnum',   'int',    '',   '',
        'pkgpart',   'int',    '',   '',
        'otaker',    'varchar', '', 8,
        'setup',     @date_type,
        'bill',      @date_type,
        'susp',      @date_type,
        'cancel',    @date_type,
        'expire',    @date_type,
      ],
      'primary_key' => 'pkgnum',
      'unique' => [ [] ],
      'index' => [ ['custnum'] ],
    },

    'cust_refund' => {
      'columns' => [
        'refundnum',    'int',    '',   '',
        'crednum',      'int',    '',   '',
        '_date',        @date_type,
        'refund',       @money_type,
        'otaker',       'varchar',   '',   8,
        'reason',       'varchar',   '',   $char_d,
        'payby',        'char',   '',     4, # CARD/BILL/COMP, should be index
                                             # into payment type table.
        'payinfo',      'varchar',   'NULL', 16,  #see cust_main above
      ],
      'primary_key' => 'refundnum',
      'unique' => [ [] ],
      'index' => [ ['crednum'] ],
    },

    'cust_svc' => {
      'columns' => [
        'svcnum',    'int',    '',   '',
        'pkgnum',    'int',    '',   '',
        'svcpart',   'int',    '',   '',
      ],
      'primary_key' => 'svcnum',
      'unique' => [ [] ],
      'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ],
    },

    'part_pkg' => {
      'columns' => [
        'pkgpart',    'int',    '',   '',
        'pkg',        'varchar',   '',   $char_d,
        'comment',    'varchar',   '',   $char_d,
        'setup',      @perl_type,
        'freq',       'smallint', '', '',  #billing frequency (months)
        'recur',      @perl_type,
      ],
      'primary_key' => 'pkgpart',
      'unique' => [ [] ],
      'index' => [ [] ],
    },

    'pkg_svc' => {
      'columns' => [
        'pkgpart',    'int',    '',   '',
        'svcpart',    'int',    '',   '',
        'quantity',   'int',    '',   '',
      ],
      'primary_key' => '',
      'unique' => [ ['pkgpart', 'svcpart'] ],
      'index' => [ ['pkgpart'] ],
    },

    'part_referral' => {
      'columns' => [
        'refnum',   'int',    '',   '',
        'referral', 'varchar',   '',   $char_d,
      ],
      'primary_key' => 'refnum',
      'unique' => [ [] ],
      'index' => [ [] ],
    },

    'part_svc' => {
      'columns' => [
        'svcpart',    'int',    '',   '',
        'svc',        'varchar',   '',   $char_d,
        'svcdb',      'varchar',   '',   $char_d,
      ],
      'primary_key' => 'svcpart',
      'unique' => [ [] ],
      'index' => [ [] ],
    },

    #(this should be renamed to part_pop)
    'svc_acct_pop' => {
      'columns' => [
        'popnum',    'int',    '',   '',
        'city',      'varchar',   '',   $char_d,
        'state',     'char',   '',   2,
        'ac',        'char',   '',   3,
        'exch',      'char',   '',   3,
        #rest o' number?
      ],
      'primary_key' => 'popnum',
      'unique' => [ [] ],
      'index' => [ [] ],
    },

    'svc_acct' => {
      'columns' => [
        'svcnum',    'int',    '',   '',
        'username',  'varchar',   '',   $username_len, #unique (& remove dup code)
        '_password', 'varchar',   '',   25, #13 for encryped pw's plus ' *SUSPENDED*
        'popnum',    'int',    'NULL',   '',
        'uid',       'bigint', 'NULL',   '',
        'gid',       'bigint', 'NULL',   '',
        'finger',    'varchar',   'NULL',   $char_d,
        'dir',       'varchar',   'NULL',   $char_d,
        'shell',     'varchar',   'NULL',   $char_d,
        'quota',     'varchar',   'NULL',   $char_d,
        'slipip',    'varchar',   'NULL',   15, #four TINYINTs, bah.
      ],
      'primary_key' => 'svcnum',
      'unique' => [ [] ],
      'index' => [ ['username'] ],
    },

    'svc_acct_sm' => {
      'columns' => [
        'svcnum',    'int',    '',   '',
        'domsvc',    'int',    '',   '',
        'domuid',    'bigint', '',   '',
        'domuser',   'varchar',   '',   $char_d,
      ],
      'primary_key' => 'svcnum',
      'unique' => [ [] ],
      'index' => [ ['domsvc'], ['domuid'] ], 
    },

    #'svc_charge' => {
    #  'columns' => [
    #    'svcnum',    'int',    '',   '',
    #    'amount',    @money_type,
    #  ],
    #  'primary_key' => 'svcnum',
    #  'unique' => [ [] ],
    #  'index' => [ [] ],
    #},

    'svc_domain' => {
      'columns' => [
        'svcnum',    'int',    '',   '',
        'domain',    'varchar',    '',   $char_d,
      ],
      'primary_key' => 'svcnum',
      'unique' => [ ['domain'] ],
      'index' => [ [] ],
    },

    #'svc_wo' => {
    #  'columns' => [
    #    'svcnum',    'int',    '',   '',
    #    'svcnum',    'int',    '',   '',
    #    'svcnum',    'int',    '',   '',
    #    'worker',    'varchar',   '',   $char_d,
    #    '_date',     @date_type,
    #  ],
    #  'primary_key' => 'svcnum',
    #  'unique' => [ [] ],
    #  'index' => [ [] ],
    #},

  );

  %tables;

}