4 install DBIx::DBSchema 0.22
6 install NetAddr::IP and Chart::Base
8 CREATE TABLE cust_bill_pkg_detail (
13 PRIMARY KEY (detailnum)
15 CREATE INDEX cust_bill_pkg_detail1 ON cust_bill_pkg_detail ( pkgnum, invnum );
17 CREATE TABLE part_virtual_field (
18 vfieldpart int NOT NULL,
19 dbtable varchar(32) NOT NULL,
20 name varchar(32) NOT NULL,
25 PRIMARY KEY (vfieldpart)
28 CREATE TABLE virtual_field (
29 recnum integer NOT NULL,
30 vfieldpart integer NOT NULL,
31 value varchar(128) NOT NULL,
32 PRIMARY KEY (vfieldpart, recnum)
37 routername varchar(80),
39 PRIMARY KEY (routernum)
42 CREATE TABLE part_svc_router (
44 routernum int NOT NULL
47 CREATE TABLE addr_block (
49 routernum int NOT NULL,
50 ip_gateway varchar(15) NOT NULL,
51 ip_netmask int NOT NULL,
52 PRIMARY KEY (blocknum)
54 CREATE UNIQUE INDEX addr_block1 ON addr_block ( blocknum, routernum );
56 CREATE TABLE svc_broadband (
58 blocknum int NOT NULL,
59 speed_up int NOT NULL,
60 speed_down int NOT NULL,
65 CREATE TABLE acct_snarf (
68 machine varchar(255) NULL,
69 protocol varchar(80) NULL,
70 username varchar(80) NULL,
71 _password varchar(80) NULL,
72 PRIMARY KEY (snarfnum)
74 CREATE INDEX acct_snarf1 ON acct_snarf ( svcnum );
76 CREATE TABLE part_pkg_temp (
77 pkgpart serial NOT NULL,
78 pkg varchar(80) NOT NULL,
79 "comment" varchar(80) NOT NULL,
81 freq varchar(80) NOT NULL,
83 setuptax char(1) NULL,
84 recurtax char(1) NULL,
85 plan varchar(80) NULL,
87 disabled char(1) NULL,
88 taxclass varchar(80) NULL,
89 PRIMARY KEY (pkgpart),
91 INSERT INTO part_pkg_temp SELECT * from part_pkg;
93 ALTER TABLE part_pkg_temp RENAME TO part_pkg;
94 ALTER TABLE part_pkg DROP CONSTRAINT part_pkg_temp_pkey;
95 ALTER TABLE part_pkg ADD PRIMARY KEY (pkgpart);
96 CREATE INDEX part_pkg1 ON part_pkg(disabled);
97 select setval('public.part_pkg_temp_pkgpart_seq', ( select max(pkgpart) from part_pkg) ); #?
99 CREATE TABLE h_part_pkg_temp (
100 historynum serial NOT NULL,
102 history_user varchar(80) NOT NULL,
103 history_action varchar(80) NOT NULL,
104 pkgpart int NOT NULL,
105 pkg varchar(80) NOT NULL,
106 "comment" varchar(80) NOT NULL,
108 freq varchar(80) NOT NULL,
110 setuptax char(1) NULL,
111 recurtax char(1) NULL,
112 plan varchar(80) NULL,
114 disabled char(1) NULL,
115 taxclass varchar(80) NULL,
116 PRIMARY KEY (historynum)
118 INSERT INTO h_part_pkg_temp SELECT * from h_part_pkg;
119 DROP TABLE h_part_pkg;
120 ALTER TABLE h_part_pkg_temp RENAME TO h_part_pkg;
121 ALTER TABLE h_part_pkg DROP CONSTRAINT h_part_pkg_temp_pkey;
122 ALTER TABLE h_part_pkg ADD PRIMARY KEY (historynum);
123 CREATE INDEX h_part_pkg1 ON h_part_pkg(disabled);
124 select setval('public.h_part_pkg_temp_historynum_seq', ( select max(historynum) from h_part_pkg) );
127 DROP INDEX cust_bill_pkg1;
129 ALTER TABLE cust_bill_pkg ADD itemdesc varchar(80) NULL;
130 ALTER TABLE h_cust_bill_pkg ADD itemdesc varchar(80) NULL;
131 ALTER TABLE cust_main_county ADD taxname varchar(80) NULL;
132 ALTER TABLE h_cust_main_county ADD taxname varchar(80) NULL;
133 ALTER TABLE cust_main_county ADD setuptax char(1) NULL;
134 ALTER TABLE h_cust_main_county ADD setuptax char(1) NULL;
135 ALTER TABLE cust_main_county ADD recurtax char(1) NULL;
136 ALTER TABLE h_cust_main_county ADD recurtax char(1) NULL;
137 ALTER TABLE cust_pkg ADD last_bill int NULL;
138 ALTER TABLE h_cust_pkg ADD last_bill int NULL;
139 ALTER TABLE agent ADD disabled char(1) NULL;
140 ALTER TABLE h_agent ADD disabled char(1) NULL;
141 ALTER TABLE agent ADD username varchar(80) NULL;
142 ALTER TABLE h_agent ADD username varchar(80) NULL;
143 ALTER TABLE agent ADD _password varchar(80) NULL;
144 ALTER TABLE h_agent ADD _password varchar(80) NULL;
145 ALTER TABLE cust_main ADD paycvv varchar(4) NULL;
146 ALTER TABLE h_cust_main ADD paycvv varchar(4) NULL;
149 - cust_main: increase otaker from 8 to 32
150 - cust_main: change ss from char(11) to varchar(11)
151 - cust_credit: increase otaker from 8 to 32
152 - cust_pkg: increase otaker from 8 to 32
153 - cust_refund: increase otaker from 8 to 32
154 - domain_record: increase reczone from 80 to 255
155 - domain_record: change rectype from char to varchar
156 - domain_record: increase recdata from 80 to 255
161 CREATE INDEX cust_main6 ON cust_main ( daytime );
162 CREATE INDEX cust_main7 ON cust_main ( night );
163 CREATE INDEX cust_main8 ON cust_main ( fax );
164 CREATE INDEX cust_main9 ON cust_main ( ship_daytime );
165 CREATE INDEX cust_main10 ON cust_main ( ship_night );
166 CREATE INDEX cust_main11 ON cust_main ( ship_fax );
172 dbdef-create username
173 create-history-tables username cust_bill_pkg_detail router part_svc_router addr_block svc_broadband acct_snarf
174 dbdef-create username
176 apache - fix <Files> sections to include .html also