4 install DBD::Pg 1.32 (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.23
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) <!-- and Crypt::YAPassGen-->
14 INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 20, 'svc_external-id', 'en_US', 'External ID' );
15 INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 21, 'svc_external-title', 'en_US', 'Title' );
17 CREATE TABLE cust_bill_pkg_detail (
22 PRIMARY KEY (detailnum)
24 CREATE INDEX cust_bill_pkg_detail1 ON cust_bill_pkg_detail ( pkgnum, invnum );
26 CREATE TABLE part_virtual_field (
27 vfieldpart int NOT NULL,
28 dbtable varchar(32) NOT NULL,
29 name varchar(32) NOT NULL,
34 PRIMARY KEY (vfieldpart)
37 CREATE TABLE virtual_field (
38 recnum integer NOT NULL,
39 vfieldpart integer NOT NULL,
40 value varchar(128) NOT NULL,
41 PRIMARY KEY (vfieldpart, recnum)
46 routername varchar(80),
48 PRIMARY KEY (routernum)
51 CREATE TABLE part_svc_router (
53 routernum int NOT NULL
56 CREATE TABLE addr_block (
58 routernum int NOT NULL,
59 ip_gateway varchar(15) NOT NULL,
60 ip_netmask int NOT NULL,
61 PRIMARY KEY (blocknum)
63 CREATE UNIQUE INDEX addr_block1 ON addr_block ( blocknum, routernum );
65 CREATE TABLE svc_broadband (
67 blocknum int NOT NULL,
68 speed_up int NOT NULL,
69 speed_down int NOT NULL,
74 CREATE TABLE acct_snarf (
77 machine varchar(255) NULL,
78 protocol varchar(80) NULL,
79 username varchar(80) NULL,
80 _password varchar(80) NULL,
81 PRIMARY KEY (snarfnum)
83 CREATE INDEX acct_snarf1 ON acct_snarf ( svcnum );
85 CREATE TABLE svc_external (
92 CREATE TABLE part_pkg_temp (
93 pkgpart serial NOT NULL,
94 pkg varchar(80) NOT NULL,
95 "comment" varchar(80) NOT NULL,
97 freq varchar(80) NOT NULL,
99 setuptax char(1) NULL,
100 recurtax char(1) NULL,
101 plan varchar(80) NULL,
103 disabled char(1) NULL,
104 taxclass varchar(80) NULL,
105 PRIMARY KEY (pkgpart)
107 INSERT INTO part_pkg_temp SELECT * from part_pkg;
109 ALTER TABLE part_pkg_temp RENAME TO part_pkg;
110 CREATE INDEX part_pkg1 ON part_pkg(disabled);
113 ALTER TABLE part_pkg DROP CONSTRAINT part_pkg_temp_pkey;
114 ALTER TABLE part_pkg ADD PRIMARY KEY (pkgpart);
115 select setval('public.part_pkg_temp_pkgpart_seq', ( select max(pkgpart) from part_pkg) );
117 Or on Pg versions that don't support DROP CONSTRAINT and ADD PRIMARY KEY (tested on 7.1 and 7.2 so far):
118 DROP INDEX part_pkg_temp_pkey;
119 CREATE UNIQUE INDEX part_pkg_pkey ON part_pkg (pkgpart);
120 probably this one?: select setval('part_pkg_temp_pkgpart_seq', ( select max(pkgpart) from part_pkg) );
121 probably not this one?: select setval('part_pkg_pkgpart_seq', ( select max(pkgpart) from part_pkg) );
123 CREATE TABLE h_part_pkg_temp (
124 historynum serial NOT NULL,
126 history_user varchar(80) NOT NULL,
127 history_action varchar(80) NOT NULL,
128 pkgpart int NOT NULL,
129 pkg varchar(80) NOT NULL,
130 "comment" varchar(80) NOT NULL,
132 freq varchar(80) NOT NULL,
134 setuptax char(1) NULL,
135 recurtax char(1) NULL,
136 plan varchar(80) NULL,
138 disabled char(1) NULL,
139 taxclass varchar(80) NULL,
140 PRIMARY KEY (historynum)
142 INSERT INTO h_part_pkg_temp SELECT * from h_part_pkg;
143 DROP TABLE h_part_pkg;
144 ALTER TABLE h_part_pkg_temp RENAME TO h_part_pkg;
145 CREATE INDEX h_part_pkg1 ON h_part_pkg(disabled);
148 ALTER TABLE h_part_pkg DROP CONSTRAINT h_part_pkg_temp_pkey;
149 ALTER TABLE h_part_pkg ADD PRIMARY KEY (historynum);
150 select setval('public.h_part_pkg_temp_historynum_seq', ( select max(historynum) from h_part_pkg) );
152 Or on Pg versions that don't support DROP CONSTRAINT and ADD PRIMARY KEY (tested on 7.1 and 7.2 so far):
153 DROP INDEX h_part_pkg_temp_pkey;
154 CREATE UNIQUE INDEX h_part_pkg_pkey ON h_part_pkg (historynum);
155 probably this one?: select setval('h_part_pkg_temp_historynum_seq', ( select max(historynum) from h_part_pkg) );
156 probably not this one?: select setval('h_part_pkg_historynum_seq', ( select max(historynum) from h_part_pkg) );
158 CREATE TABLE cust_pay_refund (
159 payrefundnum serial NOT NULL,
161 refundnum int NOT NULL,
163 amount decimal(10,2) NOT NULL,
164 PRIMARY KEY (payrefundnum)
166 CREATE INDEX cust_pay_refund1 ON cust_pay_refund(paynum);
167 CREATE INDEX cust_pay_refund2 ON cust_pay_refund(refundnum);
169 CREATE TABLE cust_pay_void (
171 custnum int NOT NULL,
172 paid decimal(10,2) NOT NULL,
174 payby char(4) NOT NULL,
176 paybatch varchar(80),
180 otaker varchar(32) NOT NULL,
183 CREATE INDEX cust_pay_void1 ON cust_pay_void(custnum);
185 CREATE TABLE part_pkg_option (
186 optionnum int primary key,
187 pkgpart int not null,
188 optionname varchar(80) not null,
189 optionvalue text NULL
191 CREATE INDEX part_pkg_option1 ON part_export_option ( pkgpart );
192 CREATE INDEX part_pkg_option2 ON part_export_option ( optionname );
195 ratenum serial NOT NULL,
196 reatename varchar(80) NOT NULL,
197 PRIMARY KEY (ratenum)
200 CREATE TABLE rate_detail (
201 ratenum int NOT NULL,
202 orig_regionnum int NULL,
203 dest_regionnum int NOT NULL,
204 min_included int NOT NULL,
205 min_charge decimal(10,2) NOT NULL,
206 sec_granularity int NOT NULL
208 CREATE UNIQUE INDEX rate_detail1 ON rate_detail ( ratenum, orig_regionnum, dest_regionnum );
210 CREATE TABLE rate_region (
211 regionnum serial NOT NULL,
212 regionname varchar(80) NOT NULL,
213 PRIMARY KEY (regionnum)
216 CREATE TABLE rate_prefix (
217 prefixnum serial NOT NULL,
218 regionnum int NOT NULL,
219 countrycode varchar(3) NOT NULL,
222 PRIMARY KEY (prefixnum)
224 CREATE INDEX rate_prefix1 ON rate_prefix ( countrycode );
225 CREATE INDEX rate_prefix2 ON rate_prefix ( regionnum );
227 CREATE TABLE reg_code (
228 codenum serial NOT NULL,
229 code varchar(80) NOT NULL,
230 agentnum int NOT NULL,
231 PRIMARY KEY (codenum)
233 CREATE UNIQUE INDEX reg_code1 ON reg_code ( agentnum, code );
234 CREATE INDEX reg_code2 ON reg_code ( agentnum );
236 CREATE TABLE reg_code_pkg (
237 codenum int NOT NULL,
240 CREATE UNIQUE INDEX reg_code_pkg1 ON reg_code_pkg ( codenum, pkgpart );
241 CREATE INDEX reg_code_pkg2 ON reg_code_pkg ( codenum );
243 CREATE TABLE clientapi_session (
244 sessionnum serial NOT NULL,
245 sessionid varchar(80) NOT NULL,
246 namespace varchar(80) NOT NULL,
247 PRIMARY KEY (sessionnum)
249 CREATE UNIQUE INDEX clientapi_session1 ON clientapi_session ( sessionid, namespace );
251 CREATE TABLE clientapi_session_field (
252 fieldnum serial NOT NULL,
253 sessionnum int NOT NULL,
254 fieldname varchar(80) NOT NULL,
255 fieldvalue text NULL,
256 PRIMARY KEY (fieldnum)
258 CREATE UNIQUE INDEX clientapi_session_field1 ON clientapi_session_field ( sessionnum, fieldname );
260 DROP INDEX cust_bill_pkg1;
262 ALTER TABLE cust_bill_pkg ADD itemdesc varchar(80) NULL;
263 ALTER TABLE h_cust_bill_pkg ADD itemdesc varchar(80) NULL;
264 ALTER TABLE cust_main_county ADD taxname varchar(80) NULL;
265 ALTER TABLE h_cust_main_county ADD taxname varchar(80) NULL;
266 ALTER TABLE cust_main_county ADD setuptax char(1) NULL;
267 ALTER TABLE h_cust_main_county ADD setuptax char(1) NULL;
268 ALTER TABLE cust_main_county ADD recurtax char(1) NULL;
269 ALTER TABLE h_cust_main_county ADD recurtax char(1) NULL;
270 ALTER TABLE cust_pkg ADD last_bill int NULL;
271 ALTER TABLE h_cust_pkg ADD last_bill int NULL;
272 ALTER TABLE agent ADD disabled char(1) NULL;
273 ALTER TABLE h_agent ADD disabled char(1) NULL;
274 ALTER TABLE agent ADD username varchar(80) NULL;
275 ALTER TABLE h_agent ADD username varchar(80) NULL;
276 ALTER TABLE agent ADD _password varchar(80) NULL;
277 ALTER TABLE h_agent ADD _password varchar(80) NULL;
278 ALTER TABLE cust_main ADD paycvv varchar(4) NULL;
279 ALTER TABLE h_cust_main ADD paycvv varchar(4) NULL;
280 ALTER TABLE part_referral ADD disabled char(1) NULL;
281 ALTER TABLE h_part_referral ADD disabled char(1) NULL;
282 CREATE INDEX part_referral1 ON part_referral ( disabled );
283 ALTER TABLE pkg_svc ADD primary_svc char(1) NULL;
284 ALTER TABLE h_pkg_svc ADD primary_svc char(1) NULL;
285 ALTER TABLE svc_forward ADD src varchar(255) NULL;
286 ALTER TABLE h_svc_forward ADD src varchar(255) NULL;
287 ALTER TABLE part_pkg ADD promo_code varchar(80) NULL;
288 ALTER TABLE h_part_pkg ADD promo_code varchar(80) NULL;
289 CREATE INDEX part_pkg2 ON part_pkg ( promo_code );
290 CREATE INDEX h_part_pkg2 ON h_part_pkg ( promo_code );
292 On recent Pg versions:
294 ALTER TABLE svc_forward ALTER COLUMN srcsvc DROP NOT NULL;
295 ALTER TABLE h_svc_forward ALTER COLUMN srcsvc DROP NOT NULL;
296 ALTER TABLE svc_forward ALTER COLUMN dstsvc DROP NOT NULL;
297 ALTER TABLE h_svc_forward ALTER COLUMN dstsvc DROP NOT NULL;
298 ALTER TABLE cust_main ALTER COLUMN zip DROP NOT NULL;
299 ALTER TABLE h_cust_main ALTER COLUMN zip DROP NOT NULL;
301 Or on Pg versions that don't support DROP NOT NULL (tested on 7.1 and 7.2 so far):
302 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' ) );
303 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' ) );
305 If you created your database with a version before 1.4.2, dump database, edit:
306 - cust_main and h_cust_main: increase otaker from 8 to 32
307 - cust_main and h_cust_main: change ss from char(11) to varchar(11) ( "character(11)" to "character varying(11)" )
308 - cust_credit and h_cust_credit: increase otaker from 8 to 32
309 - cust_pkg and h_cust_pkg: increase otaker from 8 to 32
310 - cust_refund and h_cust_refund: increase otaker from 8 to 32
311 - domain_record and h_domain_record: increase reczone from 80 to 255
312 - domain_record and h_domain_record: change rectype from char to varchar ( "character(5)" to "character varying(5)" )
313 - domain_record and h_domain_record: increase recdata from 80 to 255
318 CREATE INDEX cust_main6 ON cust_main ( daytime );
319 CREATE INDEX cust_main7 ON cust_main ( night );
320 CREATE INDEX cust_main8 ON cust_main ( fax );
321 CREATE INDEX cust_main9 ON cust_main ( ship_daytime );
322 CREATE INDEX cust_main10 ON cust_main ( ship_night );
323 CREATE INDEX cust_main11 ON cust_main ( ship_fax );
324 CREATE INDEX agent2 ON agent ( disabled );
325 CREATE INDEX part_bill_event2 ON part_bill_event ( disabled );
326 CREATE INDEX cust_pay4 ON cust_pay (_date);
332 dbdef-create username
333 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
334 dbdef-create username
336 apache - fix <Files> sections to include .html also