X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fdocs%2Fupgrade10.html;h=38e1591ded82db83190087efdbb3985ae1925d11;hb=65886f8e35ddd4a618760d7771c6163521cb43b5;hp=080528bac8c0e6b912f5a181fdee9c1adecd3fd7;hpb=f63c0e821610c885f9f49d301eeccf804e1ca6d3;p=freeside.git diff --git a/httemplate/docs/upgrade10.html b/httemplate/docs/upgrade10.html index 080528bac..38e1591de 100644 --- a/httemplate/docs/upgrade10.html +++ b/httemplate/docs/upgrade10.html @@ -1,9 +1,21 @@
this is incomplete -install DBIx::DBSchema 0.22 +install DBD::Pg 1.32, 1.41 or later (not 1.40) (or, if you're using a Perl version before 5.6, you could try installing DBD::Pg 1.22 with this patch and commenting out the "use DBD::Pg 1.32" at the top of DBIx/DBSchema/DBD/Pg.pm) +install DBIx::DBSchema 0.26 +install Net::SSH 0.08 +- If using Apache::ASP, add PerlSetVar RequestBinaryRead Off and PerlSetVar IncludesDir /your/freeside/document/root/ to your Apache configuration and make sure you are using Apache::ASP minimum version 2.55. +- In httpd.conf, change <Files ~ \.cgi> to <Files ~ (\.cgi|\.html)> +- In httpd.conf, change AddHandler perl-script .cgi or SetHandler perl-script to AddHandler perl-script .cgi .html -install NetAddr::IP and Chart::Base +install NetAddr::IP, Chart::Base, Locale::SubCountry, +JavaScript::RPC (JavaScript::RPC::Server::CGI), Text::CSV_XS, +Spreadsheet::WriteExcel, IO-stringy (IO::Scalar), Frontier::RPC +(Frontier::RPC2) and MIME::Entity (MIME-tools) + + +INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 20, 'svc_external-id', 'en_US', 'External ID' ); +INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 21, 'svc_external-title', 'en_US', 'Title' ); CREATE TABLE cust_bill_pkg_detail ( detailnum serial, @@ -40,8 +52,10 @@ CREATE TABLE router ( ); CREATE TABLE part_svc_router ( + svcrouternum serial, svcpart int NOT NULL, - routernum int NOT NULL + routernum int NOT NULL, + PRIMARY KEY (svcrouternum) ); CREATE TABLE addr_block ( @@ -73,6 +87,14 @@ CREATE TABLE acct_snarf ( ); CREATE INDEX acct_snarf1 ON acct_snarf ( svcnum ); +CREATE TABLE svc_external ( + svcnum int NOT NULL, + id int, + title varchar(80), + PRIMARY KEY (svcnum) +); + +ALTER TABLE part_pkg ADD COLUMN taxclass varchar(80) NULL; +ALTER TABLE h_part_pkg ADD COLUMN taxclass varchar(80) NULL; + +CREATE TABLE cust_pay_refund ( + payrefundnum serial NOT NULL, + paynum int NOT NULL, + refundnum int NOT NULL, + _date int NOT NULL, + amount decimal(10,2) NOT NULL, + PRIMARY KEY (payrefundnum) +); +CREATE INDEX cust_pay_refund1 ON cust_pay_refund(paynum); +CREATE INDEX cust_pay_refund2 ON cust_pay_refund(refundnum); + +CREATE TABLE cust_pay_void ( + paynum int NOT NULL, + custnum int NOT NULL, + paid decimal(10,2) NOT NULL, + _date int, + payby char(4) NOT NULL, + payinfo varchar(80), + paybatch varchar(80), + closed char(1), + void_date int, + reason varchar(80), + otaker varchar(32) NOT NULL, + PRIMARY KEY (paynum) +); +CREATE INDEX cust_pay_void1 ON cust_pay_void(custnum); + +CREATE TABLE part_pkg_option ( + optionnum serial, + pkgpart int not null, + optionname varchar(80) not null, + optionvalue text NULL, + PRIMARY KEY (optionnum) +); +CREATE INDEX part_pkg_option1 ON part_pkg_option ( pkgpart ); +CREATE INDEX part_pkg_option2 ON part_pkg_option ( optionname ); + +CREATE TABLE rate ( + ratenum serial NOT NULL, + ratename varchar(80) NOT NULL, + PRIMARY KEY (ratenum) +); + +CREATE TABLE rate_detail ( + ratedetailnum serial NOT NULL, + ratenum int NOT NULL, + orig_regionnum int NULL, + dest_regionnum int NOT NULL, + min_included int NOT NULL, + min_charge decimal(10,2) NOT NULL, + sec_granularity int NOT NULL, + PRIMARY KEY (ratedetailnum) +); +CREATE UNIQUE INDEX rate_detail1 ON rate_detail ( ratenum, orig_regionnum, dest_regionnum ); + +CREATE TABLE rate_region ( + regionnum serial NOT NULL, + regionname varchar(80) NOT NULL, + PRIMARY KEY (regionnum) +); + +CREATE TABLE rate_prefix ( + prefixnum serial NOT NULL, + regionnum int NOT NULL, + countrycode varchar(3) NOT NULL, + npa varchar(6) NULL, + nxx varchar(3) NULL, + PRIMARY KEY (prefixnum) +); +CREATE INDEX rate_prefix1 ON rate_prefix ( countrycode ); +CREATE INDEX rate_prefix2 ON rate_prefix ( regionnum ); + +CREATE TABLE reg_code ( + codenum serial NOT NULL, + code varchar(80) NOT NULL, + agentnum int NOT NULL, + PRIMARY KEY (codenum) +); +CREATE UNIQUE INDEX reg_code1 ON reg_code ( agentnum, code ); +CREATE INDEX reg_code2 ON reg_code ( agentnum ); + +CREATE TABLE reg_code_pkg ( + codepkgnum serial, + codenum int NOT NULL, + pkgpart int NOT NULL, + PRIMARY KEY (codepkgnum) +); +CREATE UNIQUE INDEX reg_code_pkg1 ON reg_code_pkg ( codenum, pkgpart ); +CREATE INDEX reg_code_pkg2 ON reg_code_pkg ( codenum ); + +CREATE TABLE clientapi_session ( + sessionnum serial NOT NULL, + sessionid varchar(80) NOT NULL, + namespace varchar(80) NOT NULL, + PRIMARY KEY (sessionnum) +); +CREATE UNIQUE INDEX clientapi_session1 ON clientapi_session ( sessionid, namespace ); + +CREATE TABLE clientapi_session_field ( + fieldnum serial NOT NULL, + sessionnum int NOT NULL, + fieldname varchar(80) NOT NULL, + fieldvalue text NULL, + PRIMARY KEY (fieldnum) +); +CREATE UNIQUE INDEX clientapi_session_field1 ON clientapi_session_field ( sessionnum, fieldname ); DROP INDEX cust_bill_pkg1; @@ -142,18 +288,78 @@ ALTER TABLE agent ADD username varchar(80) NULL; ALTER TABLE h_agent ADD username varchar(80) NULL; ALTER TABLE agent ADD _password varchar(80) NULL; ALTER TABLE h_agent ADD _password varchar(80) NULL; -ALTER TABLE cust_main ADD paycvv varchar(4) NULL; -ALTER TABLE h_cust_main ADD paycvv varchar(4) NULL; - -dump database, edit: -- cust_main: increase otaker from 8 to 32 -- cust_main: change ss from char(11) to varchar(11) -- cust_credit: increase otaker from 8 to 32 -- cust_pkg: increase otaker from 8 to 32 -- cust_refund: increase otaker from 8 to 32 -- domain_record: increase reczone from 80 to 255 -- domain_record: change rectype from char to varchar -- domain_record: increase recdata from 80 to 255 +ALTER TABLE cust_main ADD paycvv varchar(512) NULL; +ALTER TABLE h_cust_main ADD paycvv varchar(512) NULL; +ALTER TABLE cust_main ADD paymask varchar(80) NULL; +ALTER TABLE h_cust_main ADD paymask varchar(80) NULL; +ALTER TABLE part_referral ADD disabled char(1) NULL; +ALTER TABLE h_part_referral ADD disabled char(1) NULL; +CREATE INDEX part_referral1 ON part_referral ( disabled ); +ALTER TABLE pkg_svc ADD primary_svc char(1) NULL; +ALTER TABLE h_pkg_svc ADD primary_svc char(1) NULL; +ALTER TABLE svc_forward ADD src varchar(255) NULL; +ALTER TABLE h_svc_forward ADD src varchar(255) NULL; +ALTER TABLE part_pkg ADD promo_code varchar(80) NULL; +ALTER TABLE h_part_pkg ADD promo_code varchar(80) NULL; +CREATE INDEX part_pkg2 ON part_pkg ( promo_code ); +CREATE INDEX h_part_pkg2 ON h_part_pkg ( promo_code ); +ALTER TABLE prepay_credit ADD agentnum integer NULL; +ALTER TABLE h_prepay_credit ADD agentnum integer NULL; + +ALTER TABLE type_pkgs ADD typepkgnum int; +ALTER TABLE type_pkgs ALTER COLUMN typepkgnum SET DEFAULT nextval('public.type_pkgs_typepkgnum_seq'::text); +CREATE SEQUENCE type_pkgs_typepkgnum_seq; +UPDATE type_pkgs SET typepkgnum = nextval('public.type_pkgs_typepkgnum_seq'::text) WHERE typepkgnum IS NULL; +ALTER TABLE type_pkgs ALTER typepkgnum SET NOT NULL; +ALTER TABLE type_pkgs ADD PRIMARY KEY (typepkgnum); +ALTER TABLE h_type_pkgs ADD typepkgnum int; + +ALTER TABLE cust_bill_pkg ADD billpkgnum int; +ALTER TABLE cust_bill_pkg ALTER COLUMN billpkgnum SET DEFAULT nextval('public.cust_bill_pkg_billpkgnum_seq'::text); +CREATE SEQUENCE cust_bill_pkg_billpkgnum_seq; +UPDATE cust_bill_pkg SET billpkgnum = nextval('public.cust_bill_pkg_billpkgnum_seq'::text) WHERE billpkgnum IS NULL; +ALTER TABLE cust_bill_pkg ALTER billpkgnum SET NOT NULL; +ALTER TABLE cust_bill_pkg ADD PRIMARY KEY (billpkgnum); +ALTER TABLE h_cust_bill_pkg ADD billpkgnum int; + +ALTER TABLE pkg_svc ADD pkgsvcnum int; +ALTER TABLE pkg_svc ALTER COLUMN pkgsvcnum SET DEFAULT nextval('public.pkg_svc_pkgsvcnum_seq'::text); +CREATE SEQUENCE pkg_svc_pkgsvcnum_seq; +UPDATE pkg_svc SET pkgsvcnum = nextval('public.pkg_svc_pkgsvcnum_seq'::text) WHERE pkgsvcnum IS NULL; +ALTER TABLE pkg_svc ALTER pkgsvcnum SET NOT NULL; +ALTER TABLE pkg_svc ADD PRIMARY KEY (pkgsvcnum); +ALTER TABLE h_pkg_svc ADD pkgsvcnum int; + +On recent Pg versions: + +ALTER TABLE cust_main ALTER COLUMN payinfo varchar(512) NULL; +ALTER TABLE h_cust_main ALTER COLUMN payinfo varchar(512) NULL; + +Or on older Pg versions that don't support altering columns directly: + + +On recent Pg versions: + +ALTER TABLE svc_forward ALTER COLUMN srcsvc DROP NOT NULL; +ALTER TABLE h_svc_forward ALTER COLUMN srcsvc DROP NOT NULL; +ALTER TABLE svc_forward ALTER COLUMN dstsvc DROP NOT NULL; +ALTER TABLE h_svc_forward ALTER COLUMN dstsvc DROP NOT NULL; +ALTER TABLE cust_main ALTER COLUMN zip DROP NOT NULL; +ALTER TABLE h_cust_main ALTER COLUMN zip DROP NOT NULL; + +Or on Pg versions that don't support DROP NOT NULL (tested on 7.1 and 7.2 so far): +UPDATE pg_attribute SET attnotnull = FALSE WHERE ( attname = 'srcsvc' OR attname = 'dstsvc' ) AND ( attrelid = ( SELECT oid FROM pg_class WHERE relname = 'svc_forward' ) OR attrelid = ( SELECT oid FROM pg_class WHERE relname = 'h_svc_forward' ) ); +UPDATE pg_attribute SET attnotnull = FALSE WHERE ( attname = 'zip' ) AND ( attrelid = ( SELECT oid FROM pg_class WHERE relname = 'cust_main' ) OR attrelid = ( SELECT oid FROM pg_class WHERE relname = 'h_cust_main' ) ); + +If you created your database with a version before 1.4.2, dump database, edit: +- cust_main and h_cust_main: increase otaker from 8 to 32 +- cust_main and h_cust_main: change ss from char(11) to varchar(11) ( "character(11)" to "character varying(11)" ) +- cust_credit and h_cust_credit: increase otaker from 8 to 32 +- cust_pkg and h_cust_pkg: increase otaker from 8 to 32 +- cust_refund and h_cust_refund: increase otaker from 8 to 32 +- domain_record and h_domain_record: increase reczone from 80 to 255 +- domain_record and h_domain_record: change rectype from char to varchar ( "character(5)" to "character varying(5)" ) +- domain_record and h_domain_record: increase recdata from 80 to 255 then reload optionally: @@ -164,13 +370,16 @@ optionally: CREATE INDEX cust_main9 ON cust_main ( ship_daytime ); CREATE INDEX cust_main10 ON cust_main ( ship_night ); CREATE INDEX cust_main11 ON cust_main ( ship_fax ); + CREATE INDEX agent2 ON agent ( disabled ); + CREATE INDEX part_bill_event2 ON part_bill_event ( disabled ); + CREATE INDEX cust_pay4 ON cust_pay (_date); serial columns mandatory again: dbdef-create username -create-history-tables username cust_bill_pkg_detail router part_svc_router addr_block svc_broadband acct_snarf +create-history-tables username cust_bill_pkg_detail router part_svc_router addr_block svc_broadband acct_snarf svc_external cust_pay_refund cust_pay_void part_pkg_option rate rate_detail rate_region rate_prefix reg_code reg_code_pkg dbdef-create username apache - fixsections to include .html also