4 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 <a href="http://420.am/~ivan/DBD-Pg-1.22-fixvercmp.patch">this patch</a> and commenting out the "use DBD::Pg 1.32" at the top of DBIx/DBSchema/DBD/Pg.pm)
5 install DBIx::DBSchema 0.26
7 - 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.
8 - In httpd.conf, change <Files ~ \.cgi> to <Files ~ (\.cgi|\.html)>
9 - In httpd.conf, change <b>AddHandler perl-script .cgi</b> or <b>SetHandler perl-script</b> to <b>AddHandler perl-script .cgi .html</b>
11 install NetAddr::IP, Chart::Base, Locale::SubCountry,
12 JavaScript::RPC (JavaScript::RPC::Server::CGI), Text::CSV_XS,
13 Spreadsheet::WriteExcel, IO-stringy (IO::Scalar), Frontier::RPC
14 (Frontier::RPC2) and MIME::Entity (MIME-tools)
15 <!-- and Crypt::YAPassGen-->
17 INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 20, 'svc_external-id', 'en_US', 'External ID' );
18 INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 21, 'svc_external-title', 'en_US', 'Title' );
20 CREATE TABLE cust_bill_pkg_detail (
25 PRIMARY KEY (detailnum)
27 CREATE INDEX cust_bill_pkg_detail1 ON cust_bill_pkg_detail ( pkgnum, invnum );
29 CREATE TABLE part_virtual_field (
30 vfieldpart int NOT NULL,
31 dbtable varchar(32) NOT NULL,
32 name varchar(32) NOT NULL,
37 PRIMARY KEY (vfieldpart)
40 CREATE TABLE virtual_field (
41 recnum integer NOT NULL,
42 vfieldpart integer NOT NULL,
43 value varchar(128) NOT NULL,
44 PRIMARY KEY (vfieldpart, recnum)
49 routername varchar(80),
51 PRIMARY KEY (routernum)
54 CREATE TABLE part_svc_router (
57 routernum int NOT NULL,
58 PRIMARY KEY (svcrouternum)
61 CREATE TABLE addr_block (
63 routernum int NOT NULL,
64 ip_gateway varchar(15) NOT NULL,
65 ip_netmask int NOT NULL,
66 PRIMARY KEY (blocknum)
68 CREATE UNIQUE INDEX addr_block1 ON addr_block ( blocknum, routernum );
70 CREATE TABLE svc_broadband (
72 blocknum int NOT NULL,
73 speed_up int NOT NULL,
74 speed_down int NOT NULL,
79 CREATE TABLE acct_snarf (
82 machine varchar(255) NULL,
83 protocol varchar(80) NULL,
84 username varchar(80) NULL,
85 _password varchar(80) NULL,
86 PRIMARY KEY (snarfnum)
88 CREATE INDEX acct_snarf1 ON acct_snarf ( svcnum );
90 CREATE TABLE svc_external (
98 CREATE TABLE part_pkg_temp (
99 pkgpart serial NOT NULL,
100 pkg varchar(80) NOT NULL,
101 "comment" varchar(80) NOT NULL,
103 freq varchar(80) NOT NULL,
105 setuptax char(1) NULL,
106 recurtax char(1) NULL,
107 plan varchar(80) NULL,
109 disabled char(1) NULL,
110 taxclass varchar(80) NULL,
111 PRIMARY KEY (pkgpart)
113 INSERT INTO part_pkg_temp SELECT * from part_pkg;
115 ALTER TABLE part_pkg_temp RENAME TO part_pkg;
116 CREATE INDEX part_pkg1 ON part_pkg(disabled);
119 ALTER TABLE part_pkg DROP CONSTRAINT part_pkg_temp_pkey;
120 ALTER TABLE part_pkg ADD PRIMARY KEY (pkgpart);
121 select setval('public.part_pkg_temp_pkgpart_seq', ( select max(pkgpart) from part_pkg) );
123 Or on Pg versions that don't support DROP CONSTRAINT and ADD PRIMARY KEY (tested on 7.1 and 7.2 so far):
124 DROP INDEX part_pkg_temp_pkey;
125 CREATE UNIQUE INDEX part_pkg_pkey ON part_pkg (pkgpart);
126 probably this one?: select setval('part_pkg_temp_pkgpart_seq', ( select max(pkgpart) from part_pkg) );
127 probably not this one?: select setval('part_pkg_pkgpart_seq', ( select max(pkgpart) from part_pkg) );
129 CREATE TABLE h_part_pkg_temp (
130 historynum serial NOT NULL,
132 history_user varchar(80) NOT NULL,
133 history_action varchar(80) NOT NULL,
134 pkgpart int NOT NULL,
135 pkg varchar(80) NOT NULL,
136 "comment" varchar(80) NOT NULL,
138 freq varchar(80) NOT NULL,
140 setuptax char(1) NULL,
141 recurtax char(1) NULL,
142 plan varchar(80) NULL,
144 disabled char(1) NULL,
145 taxclass varchar(80) NULL,
146 PRIMARY KEY (historynum)
148 INSERT INTO h_part_pkg_temp SELECT * from h_part_pkg;
149 DROP TABLE h_part_pkg;
150 ALTER TABLE h_part_pkg_temp RENAME TO h_part_pkg;
151 CREATE INDEX h_part_pkg1 ON h_part_pkg(disabled);
154 ALTER TABLE h_part_pkg DROP CONSTRAINT h_part_pkg_temp_pkey;
155 ALTER TABLE h_part_pkg ADD PRIMARY KEY (historynum);
156 select setval('public.h_part_pkg_temp_historynum_seq', ( select max(historynum) from h_part_pkg) );
158 Or on Pg versions that don't support DROP CONSTRAINT and ADD PRIMARY KEY (tested on 7.1 and 7.2 so far):
159 DROP INDEX h_part_pkg_temp_pkey;
160 CREATE UNIQUE INDEX h_part_pkg_pkey ON h_part_pkg (historynum);
161 probably this one?: select setval('h_part_pkg_temp_historynum_seq', ( select max(historynum) from h_part_pkg) );
162 probably not this one?: select setval('h_part_pkg_historynum_seq', ( select max(historynum) from h_part_pkg) );
163 -->ALTER TABLE part_pkg ADD COLUMN taxclass varchar(80) NULL;
164 ALTER TABLE h_part_pkg ADD COLUMN taxclass varchar(80) NULL;
166 CREATE TABLE cust_pay_refund (
167 payrefundnum serial NOT NULL,
169 refundnum int NOT NULL,
171 amount decimal(10,2) NOT NULL,
172 PRIMARY KEY (payrefundnum)
174 CREATE INDEX cust_pay_refund1 ON cust_pay_refund(paynum);
175 CREATE INDEX cust_pay_refund2 ON cust_pay_refund(refundnum);
177 CREATE TABLE cust_pay_void (
179 custnum int NOT NULL,
180 paid decimal(10,2) NOT NULL,
182 payby char(4) NOT NULL,
184 paybatch varchar(80),
188 otaker varchar(32) NOT NULL,
191 CREATE INDEX cust_pay_void1 ON cust_pay_void(custnum);
193 CREATE TABLE part_pkg_option (
195 pkgpart int not null,
196 optionname varchar(80) not null,
197 optionvalue text NULL,
198 PRIMARY KEY (optionnum)
200 CREATE INDEX part_pkg_option1 ON part_pkg_option ( pkgpart );
201 CREATE INDEX part_pkg_option2 ON part_pkg_option ( optionname );
204 ratenum serial NOT NULL,
205 ratename varchar(80) NOT NULL,
206 PRIMARY KEY (ratenum)
209 CREATE TABLE rate_detail (
210 ratedetailnum serial NOT NULL,
211 ratenum int NOT NULL,
212 orig_regionnum int NULL,
213 dest_regionnum int NOT NULL,
214 min_included int NOT NULL,
215 min_charge decimal(10,2) NOT NULL,
216 sec_granularity int NOT NULL,
217 PRIMARY KEY (ratedetailnum)
219 CREATE UNIQUE INDEX rate_detail1 ON rate_detail ( ratenum, orig_regionnum, dest_regionnum );
221 CREATE TABLE rate_region (
222 regionnum serial NOT NULL,
223 regionname varchar(80) NOT NULL,
224 PRIMARY KEY (regionnum)
227 CREATE TABLE rate_prefix (
228 prefixnum serial NOT NULL,
229 regionnum int NOT NULL,
230 countrycode varchar(3) NOT NULL,
233 PRIMARY KEY (prefixnum)
235 CREATE INDEX rate_prefix1 ON rate_prefix ( countrycode );
236 CREATE INDEX rate_prefix2 ON rate_prefix ( regionnum );
238 CREATE TABLE reg_code (
239 codenum serial NOT NULL,
240 code varchar(80) NOT NULL,
241 agentnum int NOT NULL,
242 PRIMARY KEY (codenum)
244 CREATE UNIQUE INDEX reg_code1 ON reg_code ( agentnum, code );
245 CREATE INDEX reg_code2 ON reg_code ( agentnum );
247 CREATE TABLE reg_code_pkg (
249 codenum int NOT NULL,
250 pkgpart int NOT NULL,
251 PRIMARY KEY (codepkgnum)
253 CREATE UNIQUE INDEX reg_code_pkg1 ON reg_code_pkg ( codenum, pkgpart );
254 CREATE INDEX reg_code_pkg2 ON reg_code_pkg ( codenum );
256 CREATE TABLE clientapi_session (
257 sessionnum serial NOT NULL,
258 sessionid varchar(80) NOT NULL,
259 namespace varchar(80) NOT NULL,
260 PRIMARY KEY (sessionnum)
262 CREATE UNIQUE INDEX clientapi_session1 ON clientapi_session ( sessionid, namespace );
264 CREATE TABLE clientapi_session_field (
265 fieldnum serial NOT NULL,
266 sessionnum int NOT NULL,
267 fieldname varchar(80) NOT NULL,
268 fieldvalue text NULL,
269 PRIMARY KEY (fieldnum)
271 CREATE UNIQUE INDEX clientapi_session_field1 ON clientapi_session_field ( sessionnum, fieldname );
273 DROP INDEX cust_bill_pkg1;
275 ALTER TABLE cust_bill_pkg ADD itemdesc varchar(80) NULL;
276 ALTER TABLE h_cust_bill_pkg ADD itemdesc varchar(80) NULL;
277 ALTER TABLE cust_main_county ADD taxname varchar(80) NULL;
278 ALTER TABLE h_cust_main_county ADD taxname varchar(80) NULL;
279 ALTER TABLE cust_main_county ADD setuptax char(1) NULL;
280 ALTER TABLE h_cust_main_county ADD setuptax char(1) NULL;
281 ALTER TABLE cust_main_county ADD recurtax char(1) NULL;
282 ALTER TABLE h_cust_main_county ADD recurtax char(1) NULL;
283 ALTER TABLE cust_pkg ADD last_bill int NULL;
284 ALTER TABLE h_cust_pkg ADD last_bill int NULL;
285 ALTER TABLE agent ADD disabled char(1) NULL;
286 ALTER TABLE h_agent ADD disabled char(1) NULL;
287 ALTER TABLE agent ADD username varchar(80) NULL;
288 ALTER TABLE h_agent ADD username varchar(80) NULL;
289 ALTER TABLE agent ADD _password varchar(80) NULL;
290 ALTER TABLE h_agent ADD _password varchar(80) NULL;
291 ALTER TABLE cust_main ADD paycvv varchar(512) NULL;
292 ALTER TABLE h_cust_main ADD paycvv varchar(512) NULL;
293 ALTER TABLE cust_main ADD paymask varchar(80) NULL;
294 ALTER TABLE h_cust_main ADD paymask varchar(80) NULL;
295 ALTER TABLE part_referral ADD disabled char(1) NULL;
296 ALTER TABLE h_part_referral ADD disabled char(1) NULL;
297 CREATE INDEX part_referral1 ON part_referral ( disabled );
298 ALTER TABLE pkg_svc ADD primary_svc char(1) NULL;
299 ALTER TABLE h_pkg_svc ADD primary_svc char(1) NULL;
300 ALTER TABLE svc_forward ADD src varchar(255) NULL;
301 ALTER TABLE h_svc_forward ADD src varchar(255) NULL;
302 ALTER TABLE part_pkg ADD promo_code varchar(80) NULL;
303 ALTER TABLE h_part_pkg ADD promo_code varchar(80) NULL;
304 CREATE INDEX part_pkg2 ON part_pkg ( promo_code );
305 CREATE INDEX h_part_pkg2 ON h_part_pkg ( promo_code );
306 ALTER TABLE prepay_credit ADD agentnum integer NULL;
307 ALTER TABLE h_prepay_credit ADD agentnum integer NULL;
309 ALTER TABLE type_pkgs ADD typepkgnum int;
310 ALTER TABLE type_pkgs ALTER COLUMN typepkgnum SET DEFAULT nextval('public.type_pkgs_typepkgnum_seq'::text);
311 CREATE SEQUENCE type_pkgs_typepkgnum_seq;
312 UPDATE type_pkgs SET typepkgnum = nextval('public.type_pkgs_typepkgnum_seq'::text) WHERE typepkgnum IS NULL;
313 ALTER TABLE type_pkgs ALTER typepkgnum SET NOT NULL;
314 ALTER TABLE type_pkgs ADD PRIMARY KEY (typepkgnum);
315 ALTER TABLE h_type_pkgs ADD typepkgnum int;
317 ALTER TABLE cust_bill_pkg ADD billpkgnum int;
318 ALTER TABLE cust_bill_pkg ALTER COLUMN billpkgnum SET DEFAULT nextval('public.cust_bill_pkg_billpkgnum_seq'::text);
319 CREATE SEQUENCE cust_bill_pkg_billpkgnum_seq;
320 UPDATE cust_bill_pkg SET billpkgnum = nextval('public.cust_bill_pkg_billpkgnum_seq'::text) WHERE billpkgnum IS NULL;
321 ALTER TABLE cust_bill_pkg ALTER billpkgnum SET NOT NULL;
322 ALTER TABLE cust_bill_pkg ADD PRIMARY KEY (billpkgnum);
323 ALTER TABLE h_cust_bill_pkg ADD billpkgnum int;
325 ALTER TABLE pkg_svc ADD pkgsvcnum int;
326 ALTER TABLE pkg_svc ALTER COLUMN pkgsvcnum SET DEFAULT nextval('public.pkg_svc_pkgsvcnum_seq'::text);
327 CREATE SEQUENCE pkg_svc_pkgsvcnum_seq;
328 UPDATE pkg_svc SET pkgsvcnum = nextval('public.pkg_svc_pkgsvcnum_seq'::text) WHERE pkgsvcnum IS NULL;
329 ALTER TABLE pkg_svc ALTER pkgsvcnum SET NOT NULL;
330 ALTER TABLE pkg_svc ADD PRIMARY KEY (pkgsvcnum);
331 ALTER TABLE h_pkg_svc ADD pkgsvcnum int;
333 On recent Pg versions:
335 ALTER TABLE cust_main ALTER COLUMN payinfo varchar(512) NULL;
336 ALTER TABLE h_cust_main ALTER COLUMN payinfo varchar(512) NULL;
338 Or on older Pg versions that don't support altering columns directly:
341 On recent Pg versions:
343 ALTER TABLE svc_forward ALTER COLUMN srcsvc DROP NOT NULL;
344 ALTER TABLE h_svc_forward ALTER COLUMN srcsvc DROP NOT NULL;
345 ALTER TABLE svc_forward ALTER COLUMN dstsvc DROP NOT NULL;
346 ALTER TABLE h_svc_forward ALTER COLUMN dstsvc DROP NOT NULL;
347 ALTER TABLE cust_main ALTER COLUMN zip DROP NOT NULL;
348 ALTER TABLE h_cust_main ALTER COLUMN zip DROP NOT NULL;
350 Or on Pg versions that don't support DROP NOT NULL (tested on 7.1 and 7.2 so far):
351 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' ) );
352 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' ) );
354 If you created your database with a version before 1.4.2, dump database, edit:
355 - cust_main and h_cust_main: increase otaker from 8 to 32
356 - cust_main and h_cust_main: change ss from char(11) to varchar(11) ( "character(11)" to "character varying(11)" )
357 - cust_credit and h_cust_credit: increase otaker from 8 to 32
358 - cust_pkg and h_cust_pkg: increase otaker from 8 to 32
359 - cust_refund and h_cust_refund: increase otaker from 8 to 32
360 - domain_record and h_domain_record: increase reczone from 80 to 255
361 - domain_record and h_domain_record: change rectype from char to varchar ( "character(5)" to "character varying(5)" )
362 - domain_record and h_domain_record: increase recdata from 80 to 255
367 CREATE INDEX cust_main6 ON cust_main ( daytime );
368 CREATE INDEX cust_main7 ON cust_main ( night );
369 CREATE INDEX cust_main8 ON cust_main ( fax );
370 CREATE INDEX cust_main9 ON cust_main ( ship_daytime );
371 CREATE INDEX cust_main10 ON cust_main ( ship_night );
372 CREATE INDEX cust_main11 ON cust_main ( ship_fax );
373 CREATE INDEX agent2 ON agent ( disabled );
374 CREATE INDEX part_bill_event2 ON part_bill_event ( disabled );
375 CREATE INDEX cust_pay4 ON cust_pay (_date);
381 dbdef-create username
382 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
383 dbdef-create username
385 apache - fix <Files> sections to include .html also