X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=bin%2Ffs-setup;h=314a7c234df5dccded7f0fafe05b328a126bace4;hp=b3fc5c5a0612f368092118452599c10835d62af3;hb=1dd250b6ff2c32f0bb9b4db7606066f34721e7d9;hpb=ef834d58bfd2ce38c8bea73262c8091a5d88388f diff --git a/bin/fs-setup b/bin/fs-setup index b3fc5c5a0..314a7c234 100755 --- a/bin/fs-setup +++ b/bin/fs-setup @@ -1,6 +1,6 @@ #!/usr/bin/perl -Tw # -# create database and necessary tables, etc. DBI version. +# $Id: fs-setup,v 1.40 2001-08-11 05:53:42 ivan Exp $ # # ivan@sisd.com 97-nov-8,9 # @@ -32,7 +32,104 @@ # fix radius attributes ivan@sisd.com 98-sep-27 # # $Log: fs-setup,v $ -# Revision 1.7 1998-11-18 09:01:31 ivan +# Revision 1.40 2001-08-11 05:53:42 ivan +# add comments field +# +# Revision 1.39 2001/07/30 07:42:39 ivan +# need an DBIx::DBSchema with delcolumn +# +# Revision 1.38 2001/07/30 07:36:04 ivan +# templates!!! +# +# Revision 1.37 2001/06/03 14:16:11 ivan +# allow empty refund reasons +# +# Revision 1.36 2001/04/15 12:56:31 ivan +# s/dbdef/DBIx::DBSchema/ +# +# Revision 1.35 2001/04/15 09:36:43 ivan +# http://www.sisd.com/freeside/list-archive/msg01450.html +# +# Revision 1.34 2001/04/09 23:05:16 ivan +# Transactions Part I!!! +# +# Revision 1.33 2001/02/03 14:03:50 ivan +# time-based prepaid cards, session monitor. woop! +# +# Revision 1.32 2000/12/04 00:13:02 ivan +# fix nas.last type +# +# Revision 1.31 2000/12/01 18:34:53 ivan +# another tyop +# +# Revision 1.30 2000/12/01 18:33:32 ivan +# tyop +# +# Revision 1.29 2000/11/07 15:00:37 ivan +# session monitor +# +# Revision 1.28 2000/10/30 10:47:26 ivan +# nas.last can't be defined NULL if indexed +# +# Revision 1.26 2000/07/06 08:57:27 ivan +# support for radius check attributes (except importing). poorly documented. +# +# Revision 1.25 2000/06/29 12:00:49 ivan +# support for pre-encrypted md5 passwords. +# +# Revision 1.24 2000/03/02 07:44:07 ivan +# typo forgot closing ' +# +# Revision 1.23 2000/02/03 05:16:52 ivan +# beginning of DNS and Apache support +# +# Revision 1.22 2000/01/31 05:22:23 ivan +# prepaid "internet cards" +# +# Revision 1.21 2000/01/30 06:03:26 ivan +# postgres 6.5 finally supports decimal(10,2) +# +# Revision 1.20 2000/01/28 22:53:33 ivan +# track full phone number +# +# Revision 1.19 1999/07/29 08:50:35 ivan +# wrong type for cust_pay_batch.exp +# +# Revision 1.18 1999/04/15 22:46:30 ivan +# TT isn't a state! +# +# Revision 1.17 1999/04/14 07:58:39 ivan +# export getsecrets from FS::UID instead of calling it explicitly +# +# Revision 1.16 1999/02/28 19:44:16 ivan +# constructors s/create/new/ pointed out by "Bao C. Ha" +# +# Revision 1.15 1999/02/27 21:06:21 ivan +# cust_main.paydate should be varchar(10), not @date_type ; problem reported +# by Ben Leibig +# +# Revision 1.14 1999/02/07 09:59:14 ivan +# more mod_perl fixes, and bugfixes Peter Wemm sent via email +# +# Revision 1.13 1999/02/04 06:09:23 ivan +# add AU provences +# +# Revision 1.12 1999/02/03 10:42:27 ivan +# *** empty log message *** +# +# Revision 1.11 1999/01/17 03:11:52 ivan +# remove preliminary completehost changes +# +# Revision 1.10 1998/12/16 06:05:38 ivan +# add table cust_main_invoice +# +# Revision 1.9 1998/12/15 04:36:29 ivan +# s/croak/die/; #oops +# +# Revision 1.8 1998/12/15 04:33:27 ivan +# dies if it isn't running as the freeside user +# +# Revision 1.7 1998/11/18 09:01:31 ivan # i18n! i18n! # # Revision 1.6 1998/11/15 13:18:02 ivan @@ -53,13 +150,19 @@ BEGIN { $FS::Record::setup_hack = 1; } use strict; use DBI; -use FS::dbdef; -use FS::UID qw(adminsuidsetup datasrc); +use DBIx::DBSchema 0.18; +use DBIx::DBSchema::Table; +use DBIx::DBSchema::Column; +use DBIx::DBSchema::ColGroup::Unique; +use DBIx::DBSchema::ColGroup::Index; +use FS::UID qw(adminsuidsetup datasrc checkeuid getsecrets); use FS::Record; use FS::cust_main_county; +die "Not running uid freeside!" unless checkeuid(); + my $user = shift or die &usage; -FS::UID::getsecrets $user; +getsecrets($user); #needs to match FS::Record my($dbdef_file) = "/usr/local/etc/freeside/dbdef.". datasrc; @@ -70,18 +173,37 @@ print "\nEnter the maximum username length: "; my($username_len)=&getvalue; print "\n\n", <); chop $x; $x; } +sub _yesno { + print " [y/N]:"; + my $x = scalar(); + $x =~ /^y/i; +} + ### my($char_d) = 80; #default maxlength for text fields @@ -89,12 +211,7 @@ my($char_d) = 80; #default maxlength for text fields #my(@date_type) = ( 'timestamp', '', '' ); my(@date_type) = ( 'int', 'NULL', '' ); my(@perl_type) = ( 'varchar', 'NULL', 255 ); -my(@money_type); -if (datasrc =~ m/Pg/) { #Pg can't do decimal(10,2) - @money_type = ( 'money', '', '' ); -} else { - @money_type = ( 'decimal', '', '10,2' ); -} +my @money_type = ( 'decimal', '', '10,2' ); ### # create a dbdef object from the old data structure @@ -103,30 +220,34 @@ if (datasrc =~ m/Pg/) { #Pg can't do decimal(10,2) my(%tables)=&tables_hash_hack; #turn it into objects -my($dbdef) = new FS::dbdef ( map { +my($dbdef) = new DBIx::DBSchema ( 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 ); + push @columns, new DBIx::DBSchema::Column ( $name,$type,$null,$length ); } - FS::dbdef_table->new( + DBIx::DBSchema::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'}), + DBIx::DBSchema::ColGroup::Unique->new($tables{$_}{'unique'}), + DBIx::DBSchema::ColGroup::Index->new($tables{$_}{'index'}), @columns, ); } (keys %tables) ); +#remove ship_ from cust_main +unless ($ship) { + my $cust_main = $dbdef->table('cust_main'); + $cust_main->delcolumn($_) foreach ( grep /^ship_/, $cust_main->columns ); +} + #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 ( + $svc_acct->addcolumn ( new DBIx::DBSchema::Column ( 'radius_'. $attribute, 'varchar', 'NULL', @@ -134,6 +255,15 @@ foreach $attribute (@attributes) { )); } +foreach $attribute (@check_attributes) { + $svc_acct->addcolumn( new DBIx::DBSchema::Column ( + 'rc_'. $attribute, + 'varchar', + 'NULL', + $char_d, + )); +} + #make part_svc table (but now as object) my($part_svc)=$dbdef->table('part_svc'); @@ -141,18 +271,18 @@ 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)) { +foreach (qw(svc_acct svc_acct_sm svc_domain svc_www)) { my($table)=$dbdef->table($_); my($col); foreach $col ( $table->columns ) { next if $col =~ /^svcnum$/; - $part_svc->addcolumn( new FS::dbdef_column ( + $part_svc->addcolumn( new DBIx::DBSchema::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 ( + $part_svc->addcolumn ( new DBIx::DBSchema::Column ( $table->name. '__'. $table->column($col)->name . "_flag", 'char', 'NULL', @@ -163,7 +293,7 @@ foreach (qw(svc_acct svc_acct_sm svc_domain)) { #important $dbdef->save($dbdef_file); -FS::Record::reload_dbdef($dbdef_file); +&FS::Record::reload_dbdef($dbdef_file); ### # create 'em @@ -174,21 +304,10 @@ my($dbh)=adminsuidsetup $user; #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"; +my @sql = $dbdef->sql($dbh); +foreach my $statement ( $dbdef->sql($dbh) ) { + $dbh->do( $statement ) + or die "CREATE error: ",$dbh->errstr, "\ndoing statement: $statement"; } #not really sample data (and shouldn't default to US) @@ -199,9 +318,9 @@ foreach ($dbdef->tables) { 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 +SC SD TN TX UT VT VI VA WA WV WI WY AE AA AP ) ) { - my($cust_main_county)=create FS::cust_main_county({ + my($cust_main_county)=new FS::cust_main_county({ 'state' => $_, 'tax' => 0, 'country' => 'US', @@ -211,9 +330,23 @@ SC SD TN TX TT UT VT VI VA WA WV WI WY AE AA AP die $error if $error; } -#ISO 2-letter country codes (same as country TLDs) except US +#AU "offical" state codes ala mark.williamson@ebbs.com.au (Mark Williamson) foreach ( qw( -AF AL DZ AS AD AO AI AQ AG AR AM AW AU AT AZ BS BH BD BB BY BE BZ BJ BM BT BO +VIC NSW NT QLD TAS ACT WA SA +) ) { + my($cust_main_county)=new FS::cust_main_county({ + 'state' => $_, + 'tax' => 0, + 'country' => 'AU', + }); + my($error); + $error=$cust_main_county->insert; + die $error if $error; +} + +#ISO 2-letter country codes (same as country TLDs) except US and AU +foreach ( qw( +AF AL DZ AS AD AO AI AQ AG AR AM AW AT AZ BS BH BD BB BY BE BZ BJ BM BT BO BA BW BV BR IO BN BG BF BI KH CM CA CV KY CF TD CL CN CX CC CO KM CG CK CR CI HR CU CY CZ DK DJ DM DO TP EC EG SV GQ ER EE ET FK FO FJ FI FR FX GF PF TF GA GM GE DE GH GI GR GL GD GP GU GT GN GW GY HT HM HN HK HU IS IN ID IR IQ IE IL @@ -224,7 +357,7 @@ ST SA SN SC SL SG SK SI SB SO ZA GS ES LK SH PM SD SR SJ SZ SE CH SY TW TJ TZ TH TG TK TO TT TN TR TM TC TV UG UA AE GB UM UY UZ VU VA VE VN VG VI WF EH YE YU ZR ZM ZW ) ) { - my($cust_main_county)=create FS::cust_main_county({ + my($cust_main_county)=new FS::cust_main_county({ 'tax' => 0, 'country' => $_, }); @@ -235,6 +368,8 @@ YE YU ZR ZM ZW $dbh->disconnect or die $dbh->errstr; +print "Freeside database initialized sucessfully\n"; + sub usage { die "Usage:\n fs-setup user\n"; } @@ -289,7 +424,6 @@ sub tables_hash_hack { 'custnum', 'int', '', '', '_date', @date_type, 'charged', @money_type, - 'owed', @money_type, 'printed', 'int', '', '', ], 'primary_key' => 'invnum', @@ -317,9 +451,8 @@ sub tables_hash_hack { 'custnum', 'int', '', '', '_date', @date_type, 'amount', @money_type, - 'credited', @money_type, 'otaker', 'varchar', '', 8, - 'reason', 'varchar', '', 255, + 'reason', 'varchar', 'NULL', 255, ], 'primary_key' => 'crednum', 'unique' => [ [] ], @@ -330,9 +463,9 @@ sub tables_hash_hack { 'columns' => [ 'custnum', 'int', '', '', 'agentnum', 'int', '', '', - 'titlenum', 'int', 'NULL', '', +# 'titlenum', 'int', 'NULL', '', 'last', 'varchar', '', $char_d, - 'middle', 'varchar', 'NULL', $char_d, +# 'middle', 'varchar', 'NULL', $char_d, 'first', 'varchar', '', $char_d, 'ss', 'char', 'NULL', 11, 'company', 'varchar', 'NULL', $char_d, @@ -340,19 +473,35 @@ sub tables_hash_hack { 'address2', 'varchar', 'NULL', $char_d, 'city', 'varchar', '', $char_d, 'county', 'varchar', 'NULL', $char_d, - 'state', 'varchar', '', $char_d, + 'state', 'varchar', 'NULL', $char_d, 'zip', 'varchar', '', 10, 'country', 'char', '', 2, 'daytime', 'varchar', 'NULL', 20, 'night', 'varchar', 'NULL', 20, 'fax', 'varchar', 'NULL', 12, + 'ship_last', 'varchar', 'NULL', $char_d, +# 'ship_middle', 'varchar', 'NULL', $char_d, + 'ship_first', 'varchar', 'NULL', $char_d, + 'ship_company', 'varchar', 'NULL', $char_d, + 'ship_address1', 'varchar', 'NULL', $char_d, + 'ship_address2', 'varchar', 'NULL', $char_d, + 'ship_city', 'varchar', 'NULL', $char_d, + 'ship_county', 'varchar', 'NULL', $char_d, + 'ship_state', 'varchar', 'NULL', $char_d, + 'ship_zip', 'varchar', 'NULL', 10, + 'ship_country', 'char', 'NULL', 2, + 'ship_daytime', 'varchar', 'NULL', 20, + 'ship_night', 'varchar', 'NULL', 20, + 'ship_fax', 'varchar', 'NULL', 12, 'payby', 'char', '', 4, 'payinfo', 'varchar', 'NULL', 16, - 'paydate', @date_type, + #'paydate', @date_type, + 'paydate', 'varchar', 'NULL', 10, 'payname', 'varchar', 'NULL', $char_d, 'tax', 'char', 'NULL', 1, 'otaker', 'varchar', '', 8, 'refnum', 'int', '', '', + 'comments', 'varchar', 'NULL', '', ], 'primary_key' => 'custnum', 'unique' => [ [] ], @@ -360,12 +509,23 @@ sub tables_hash_hack { 'index' => [ ['last'], ], }, + 'cust_main_invoice' => { + 'columns' => [ + 'destnum', 'int', '', '', + 'custnum', 'int', '', '', + 'dest', 'varchar', '', $char_d, + ], + 'primary_key' => 'destnum', + 'unique' => [ [] ], + 'index' => [ ['custnum'], ], + }, + 'cust_main_county' => { #county+state+country are checked off the #cust_main_county for validation and to provide # a tax rate. 'columns' => [ 'taxnum', 'int', '', '', - 'state', 'char', 'NULL', $char_d, + 'state', 'varchar', 'NULL', $char_d, 'county', 'varchar', 'NULL', $char_d, 'country', 'char', '', 2, 'tax', 'real', '', '', #tax % @@ -402,12 +562,13 @@ sub tables_hash_hack { 'address1', 'varchar', '', $char_d, 'address2', 'varchar', 'NULL', $char_d, 'city', 'varchar', '', $char_d, - 'state', 'char', '', 2, + 'state', 'varchar', '', $char_d, 'zip', 'varchar', '', 10, 'country', 'char', '', 2, 'trancode', 'int', '', '', 'cardnum', 'varchar', '', 16, - 'exp', @date_type, + #'exp', @date_type, + 'exp', 'varchar', '', 11, 'payname', 'varchar', 'NULL', $char_d, 'amount', @money_type, ], @@ -453,7 +614,7 @@ sub tables_hash_hack { 'cust_svc' => { 'columns' => [ 'svcnum', 'int', '', '', - 'pkgnum', 'int', '', '', + 'pkgnum', 'int', 'NULL', '', 'svcpart', 'int', '', '', ], 'primary_key' => 'svcnum', @@ -475,15 +636,15 @@ sub tables_hash_hack { 'index' => [ [] ], }, - 'part_title' => { - 'columns' => [ - 'titlenum', 'int', '', '', - 'title', 'varchar', '', $char_d, - ], - 'primary_key' => 'titlenum', - 'unique' => [ [] ], - 'index' => [ [] ], - }, +# 'part_title' => { +# 'columns' => [ +# 'titlenum', 'int', '', '', +# 'title', 'varchar', '', $char_d, +# ], +# 'primary_key' => 'titlenum', +# 'unique' => [ [] ], +# 'index' => [ [] ], +# }, 'pkg_svc' => { 'columns' => [ @@ -522,10 +683,10 @@ sub tables_hash_hack { 'columns' => [ 'popnum', 'int', '', '', 'city', 'varchar', '', $char_d, - 'state', 'char', '', 2, + 'state', 'varchar', '', $char_d, 'ac', 'char', '', 3, 'exch', 'char', '', 3, - #rest o' number? + 'loc', 'char', 'NULL', 4, #NULL for legacy purposes ], 'primary_key' => 'popnum', 'unique' => [ [] ], @@ -536,7 +697,7 @@ sub tables_hash_hack { 'columns' => [ 'svcnum', 'int', '', '', 'username', 'varchar', '', $username_len, #unique (& remove dup code) - '_password', 'varchar', '', 25, #13 for encryped pw's plus ' *SUSPENDED* + '_password', 'varchar', '', 50, #13 for encryped pw's plus ' *SUSPENDED* (mp5 passwords can be 34) 'popnum', 'int', 'NULL', '', 'uid', 'int', 'NULL', '', 'gid', 'int', 'NULL', '', @@ -545,6 +706,7 @@ sub tables_hash_hack { 'shell', 'varchar', 'NULL', $char_d, 'quota', 'varchar', 'NULL', $char_d, 'slipip', 'varchar', 'NULL', 15, #four TINYINTs, bah. + 'seconds', 'int', 'NULL', '', #uhhhh ], 'primary_key' => 'svcnum', 'unique' => [ [] ], @@ -583,6 +745,31 @@ sub tables_hash_hack { 'index' => [ [] ], }, + 'domain_record' => { + 'columns' => [ + 'recnum', 'int', '', '', + 'svcnum', 'int', '', '', + 'reczone', 'varchar', '', $char_d, + 'recaf', 'char', '', 2, + 'rectype', 'char', '', 5, + 'recdata', 'varchar', '', $char_d, + ], + 'primary_key' => 'recnum', + 'unique' => [ [] ], + 'index' => [ ['svcnum'] ], + }, + + 'svc_www' => { + 'columns' => [ + 'svcnum', 'int', '', '', + 'recnum', 'int', '', '', + 'usersvc', 'int', '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [ [] ], + 'index' => [ [] ], + }, + #'svc_wo' => { # 'columns' => [ # 'svcnum', 'int', '', '', @@ -596,6 +783,56 @@ sub tables_hash_hack { # 'index' => [ [] ], #}, + 'prepay_credit' => { + 'columns' => [ + 'prepaynum', 'int', '', '', + 'identifier', 'varchar', '', $char_d, + 'amount', @money_type, + 'seconds', 'int', 'NULL', '', + ], + 'primary_key' => 'prepaynum', + 'unique' => [ ['identifier'] ], + 'index' => [ [] ], + }, + + 'port' => { + 'columns' => [ + 'portnum', 'int', '', '', + 'ip', 'varchar', 'NULL', 15, + 'nasport', 'int', 'NULL', '', + 'nasnum', 'int', '', '', + ], + 'primary_key' => 'portnum', + 'unique' => [], + 'index' => [], + }, + + 'nas' => { + 'columns' => [ + 'nasnum', 'int', '', '', + 'nas', 'varchar', '', $char_d, + 'nasip', 'varchar', '', 15, + 'nasfqdn', 'varchar', '', $char_d, + 'last', 'int', '', '', + ], + 'primary_key' => 'nasnum', + 'unique' => [ [ 'nas' ], [ 'nasip' ] ], + 'index' => [ [ 'last' ] ], + }, + + 'session' => { + 'columns' => [ + 'sessionnum', 'int', '', '', + 'portnum', 'int', '', '', + 'svcnum', 'int', '', '', + 'login', @date_type, + 'logout', @date_type, + ], + 'primary_key' => 'sessionnum', + 'unique' => [], + 'index' => [ [ 'portnum' ] ], + }, + ); %tables;