1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
|
NOTE: Version numbering has been simplified. 1.5.7 is the version after
1.5.0pre6. It is still a development version - releases with odd numbered
middle parts (NN in x.NN.x) are development versions, like Perl or Linux.
install DBIx::DBSchema 0.26
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
);
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 );
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 cust_main ALTER COLUMN zip DROP NOT NULL;
ALTER TABLE h_cust_main ALTER COLUMN zip DROP NOT NULL;
ALTER TABLE prepay_credit ADD agentnum integer NULL;
ALTER TABLE h_prepay_credit ADD agentnum integer NULL;
On current (7.3? definitely 7.4+) Pg:
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;
ALTER TABLE part_svc_router ADD svcrouternum int;
ALTER TABLE part_svc_router ALTER COLUMN svcrouternum SET DEFAULT nextval('public.part_svc_router_svcrouternum_seq'::text);
CREATE SEQUENCE part_svc_router_svcrouternum_seq;
UPDATE part_svc_router SET svcrouternum = nextval('public.part_svc_router_svcrouternum_seq'::text) WHERE svcrouternum IS NULL;
ALTER TABLE part_svc_router ALTER svcrouternum SET NOT NULL;
ALTER TABLE part_svc_router ADD PRIMARY KEY (svcrouternum);
ALTER TABLE h_part_svc_router ADD svcrouternum int;
Or on very old Pg (7.2 and earlier (eek), 7.3?):
ALTER TABLE type_pkgs ADD typepkgnum int;
ALTER TABLE type_pkgs ALTER COLUMN typepkgnum SET DEFAULT nextval('type_pkgs_typepkgnum_seq'::text);
CREATE SEQUENCE type_pkgs_typepkgnum_seq;
UPDATE type_pkgs SET typepkgnum = nextval('type_pkgs_typepkgnum_seq'::text) WHERE typepkgnum IS NULL;
UPDATE pg_attribute SET attnotnull = TRUE WHERE attname = 'typepkgnum' AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'type_pkgs');
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('cust_bill_pkg_billpkgnum_seq'::text);
CREATE SEQUENCE cust_bill_pkg_billpkgnum_seq;
UPDATE cust_bill_pkg SET billpkgnum = nextval('cust_bill_pkg_billpkgnum_seq'::text) WHERE billpkgnum IS NULL;
UPDATE pg_attribute SET attnotnull = TRUE WHERE attname = 'billpkgnum' AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'cust_bill_pkg');
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('pkg_svc_pkgsvcnum_seq'::text);
CREATE SEQUENCE pkg_svc_pkgsvcnum_seq;
UPDATE pkg_svc SET pkgsvcnum = nextval('pkg_svc_pkgsvcnum_seq'::text) WHERE pkgsvcnum IS NULL;
UPDATE pg_attribute SET attnotnull = TRUE WHERE attname = 'pkgsvcnum' AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'pkg_svc');
ALTER TABLE pkg_svc ADD PRIMARY KEY (pkgsvcnum);
ALTER TABLE h_pkg_svc ADD pkgsvcnum int;
ALTER TABLE part_svc_router ADD svcrouternum int;
ALTER TABLE part_svc_router ALTER COLUMN svcrouternum SET DEFAULT nextval('part_svc_router_svcrouternum_seq'::text);
CREATE SEQUENCE part_svc_router_svcrouternum_seq;
UPDATE part_svc_router SET svcrouternum = nextval('part_svc_router_svcrouternum_seq'::text) WHERE svcrouternum IS NULL;
UPDATE pg_attribute SET attnotnull = TRUE WHERE attname = 'svcrouternum' AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'part_svc_router');
ALTER TABLE part_svc_router ADD PRIMARY KEY (svcrouternum);
ALTER TABLE h_part_svc_router ADD svcrouternum int;
Installs w/integrated RT:
CREATE SEQUENCE attributes_id_seq;
CREATE TABLE Attributes (
id INTEGER DEFAULT nextval('attributes_id_seq'),
Name varchar(255) NOT NULL ,
Description varchar(255) NULL ,
Content text,
ContentType varchar(16),
ObjectType varchar(64),
ObjectId integer, -- foreign key to anything
Creator integer NOT NULL DEFAULT 0 ,
Created TIMESTAMP NULL ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
LastUpdated TIMESTAMP NULL ,
PRIMARY KEY (id)
);
CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
Add these lines to /opt/rt3/etc/RT_SiteConfig.pm (before the "1;"):
$RT::URI::freeside::IntegrationType = 'Internal';
$RT::URI::freeside::URL = 'http://path/to/your/freeside/';
Set($DatabaseHost , '');
(End of Installs w/integrated RT)
(make sure you have upgraded DBIx::DBSchema to 0.26)
dbdef-create username
create-history-tables username rate rate_detail rate_region rate_prefix reg_code reg_code_pkg
dbdef-create username
install Text::CSV_XS, Spreadsheet::WriteExcel, IO-stringy (IO::Scalar),
Frontier::RPC (Frontier::RPC2), MIME::Entity (MIME-tools) and IPC::Run3
afterwords (for installs w/integrated RT):
install HTML::Scrubber, Text::Quoted and Tree::Simple
make configure-rt
make deploy
/opt/rt3/sbin/rt-setup-database --action insert --datadir etc/upgrade/3.1.15
/opt/rt3/sbin/rt-setup-database --action insert --datadir etc/upgrade/3.1.17
|
