From: ivan Date: Sat, 2 Apr 2005 22:46:44 +0000 (+0000) Subject: herding elephants: add primary keys to *all* tables for slony X-Git-Tag: BEFORE_FINAL_MASONIZE~605 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=f583418a23dfb001978d3abf8476670d5adb96af herding elephants: add primary keys to *all* tables for slony --- diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm index 6800707fe..3478904ec 100644 --- a/FS/FS/cust_bill_pkg.pm +++ b/FS/FS/cust_bill_pkg.pm @@ -36,6 +36,8 @@ supported: =over 4 +=item billpkgnum - primary key + =item invnum - invoice (see L) =item pkgnum - package (see L) or 0 for the special virtual sales tax package @@ -153,7 +155,8 @@ sub check { my $self = shift; my $error = - $self->ut_number('pkgnum') + $self->ut_numbern('billpkgnum') + || $self->ut_number('pkgnum') || $self->ut_number('invnum') || $self->ut_money('setup') || $self->ut_money('recur') diff --git a/FS/FS/part_svc_router.pm b/FS/FS/part_svc_router.pm index 0b23ab580..df04cc9fb 100755 --- a/FS/FS/part_svc_router.pm +++ b/FS/FS/part_svc_router.pm @@ -13,7 +13,8 @@ sub table { 'part_svc_router'; } sub check { my $self = shift; my $error = - $self->ut_foreign_key('svcpart', 'part_svc', 'svcpart') + $self->ut_numbern('svcrouternum') + || $self->ut_foreign_key('svcpart', 'part_svc', 'svcpart') || $self->ut_foreign_key('routernum', 'router', 'routernum'); return $error if $error; ''; #no error diff --git a/FS/FS/pkg_svc.pm b/FS/FS/pkg_svc.pm index ea52176cb..065ddbe51 100644 --- a/FS/FS/pkg_svc.pm +++ b/FS/FS/pkg_svc.pm @@ -39,6 +39,8 @@ FS::Record. The following fields are currently supported: =over 4 +=item pkgsvcnum - primary key + =item pkgpart - Billing item definition (see L) =item svcpart - Service definition (see L) @@ -101,7 +103,8 @@ sub check { my $error; $error = - $self->ut_number('pkgpart') + $self->ut_numbern('pkgsvcnum') + || $self->ut_number('pkgpart') || $self->ut_number('svcpart') || $self->ut_number('quantity') ; diff --git a/FS/FS/rate_detail.pm b/FS/FS/rate_detail.pm index 313b0533d..7d54355fb 100644 --- a/FS/FS/rate_detail.pm +++ b/FS/FS/rate_detail.pm @@ -34,6 +34,8 @@ inherits from FS::Record. The following fields are currently supported: =over 4 +=item ratedetailnum - primary key + =item ratenum - rate plan (see L) =item orig_regionnum - call origination region @@ -106,7 +108,8 @@ sub check { my $self = shift; my $error = - $self->ut_foreign_key('ratenum', 'rate', 'ratenum') + $self->ut_numbern('ratedetailnum') + || $self->ut_foreign_key('ratenum', 'rate', 'ratenum') || $self->ut_foreign_keyn('orig_regionnum', 'rate_region', 'regionnum' ) || $self->ut_foreign_key('dest_regionnum', 'rate_region', 'regionnum' ) || $self->ut_number('min_included') diff --git a/FS/FS/reg_code_pkg.pm b/FS/FS/reg_code_pkg.pm index 598497c46..9b9a87712 100644 --- a/FS/FS/reg_code_pkg.pm +++ b/FS/FS/reg_code_pkg.pm @@ -35,6 +35,8 @@ supported: =over 4 +=item codepkgnum - primary key + =item codenum - registration code (see L) =item pkgpart - package definition (see L) @@ -99,7 +101,8 @@ sub check { my $self = shift; my $error = - $self->ut_foreign_key('codenum', 'reg_code', 'codenum') + $self->ut_numbern('codepkgnum') + || $self->ut_foreign_key('codenum', 'reg_code', 'codenum') || $self->ut_foreign_key('pkgpart', 'part_pkg', 'pkgpart') ; return $error if $error; diff --git a/FS/FS/type_pkgs.pm b/FS/FS/type_pkgs.pm index 07ad2bc2b..bf34e7cda 100644 --- a/FS/FS/type_pkgs.pm +++ b/FS/FS/type_pkgs.pm @@ -35,6 +35,8 @@ FS::Record. The following fields are currently supported: =over 4 +=item typepkgnum - primary key + =item typenum - Agent type, see L =item pkgpart - Billing item definition, see L @@ -80,7 +82,8 @@ sub check { my $self = shift; my $error = - $self->ut_number('typenum') + $self->ut_numbern('typepkgnum') + || $self->ut_number('typenum') || $self->ut_number('pkgpart') ; return $error if $error; diff --git a/FS/bin/freeside-setup b/FS/bin/freeside-setup index 38b916613..30ef2051c 100755 --- a/FS/bin/freeside-setup +++ b/FS/bin/freeside-setup @@ -343,10 +343,11 @@ sub tables_hash_hack { 'type_pkgs' => { 'columns' => [ + 'typepkgnum', 'serial', '', '', 'typenum', 'int', '', '', 'pkgpart', 'int', '', '', ], - 'primary_key' => '', + 'primary_key' => 'typepkgnum', 'unique' => [ ['typenum', 'pkgpart'] ], 'index' => [ ['typenum'] ], }, @@ -399,6 +400,7 @@ sub tables_hash_hack { 'cust_bill_pkg' => { 'columns' => [ + 'billpkgnum', 'serial', '', '', 'pkgnum', 'int', '', '', 'invnum', 'int', '', '', 'setup', @money_type, @@ -407,7 +409,7 @@ sub tables_hash_hack { 'edate', @date_type, 'itemdesc', 'varchar', 'NULL', $char_d, ], - 'primary_key' => '', + 'primary_key' => 'billpkgnum', 'unique' => [], 'index' => [ ['invnum'] ], }, @@ -713,12 +715,13 @@ sub tables_hash_hack { 'pkg_svc' => { 'columns' => [ + 'pkgsvcnum', 'serial', '', '', 'pkgpart', 'int', '', '', 'svcpart', 'int', '', '', 'quantity', 'int', '', '', 'primary_svc','char', 'NULL', 1, ], - 'primary_key' => '', + 'primary_key' => 'pkgsvcnum', 'unique' => [ ['pkgpart', 'svcpart'] ], 'index' => [ ['pkgpart'] ], }, @@ -1058,10 +1061,11 @@ sub tables_hash_hack { 'part_svc_router' => { 'columns' => [ + 'svcrouternum', 'serial', '', '', 'svcpart', 'int', '', '', 'routernum', 'int', '', '', ], - 'primary_key' => '', + 'primary_key' => 'svcrouternum', 'unique' => [], 'index' => [], }, @@ -1108,11 +1112,12 @@ sub tables_hash_hack { 'virtual_field' => { 'columns' => [ + 'vfieldnum', 'serial', '', '', 'recnum', 'int', '', '', 'vfieldpart', 'int', '', '', 'value', 'varchar', '', 128, ], - 'primary_key' => '', + 'primary_key' => 'vfieldnum', 'unique' => [ [ 'vfieldpart', 'recnum' ] ], 'index' => [], }, @@ -1179,6 +1184,7 @@ sub tables_hash_hack { 'rate_detail' => { 'columns' => [ + 'ratedetailnum', 'serial', '', '', 'ratenum', 'int', '', '', 'orig_regionnum', 'int', 'NULL', '', 'dest_regionnum', 'int', '', '', @@ -1187,7 +1193,7 @@ sub tables_hash_hack { 'sec_granularity', 'int', '', '', #time period (link to table of periods)? ], - 'primary_key' => '', + 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], 'index' => [], }, @@ -1228,10 +1234,11 @@ sub tables_hash_hack { 'reg_code_pkg' => { 'columns' => [ + 'codepkgnum', 'serial', '', '', 'codenum', 'int', '', '', 'pkgpart', 'int', '', '', ], - 'primary_key' => '', + 'primary_key' => 'codepkgnum', 'unique' => [ [ 'codenum', 'pkgpart' ] ], 'index' => [ [ 'codenum' ] ], }, diff --git a/README.1.5.7 b/README.1.5.7 index 53b0585ab..5e9b97a9e 100644 --- a/README.1.5.7 +++ b/README.1.5.7 @@ -11,6 +11,7 @@ CREATE TABLE rate ( ); CREATE TABLE rate_detail ( + ratedetailnum serial NOT NULL, ratenum int NOT NULL, orig_regionnum int NULL, dest_regionnum int NOT NULL, @@ -47,8 +48,10 @@ 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 + 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 ); @@ -81,6 +84,30 @@ 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; +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; +ALTER TABLE type_pkgs ALTER typepkgnum SET NOT NULL; +ALTER TABLE type_pkgs ADD PRIMARY KEY (typepkgnum); + +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; +ALTER TABLE cust_bill_pkg ALTER billpkgnum SET NOT NULL; +ALTER TABLE cust_bill_pkg ADD PRIMARY KEY (billpkgnum); + +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; +ALTER TABLE pkg_svc ALTER pkgsvcnum SET NOT NULL; +ALTER TABLE pkg_svc ADD PRIMARY KEY (pkgsvcnum); + +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; +ALTER TABLE part_svc_router ALTER svcrouternum SET NOT NULL; +ALTER TABLE part_svc_router ADD PRIMARY KEY (svcrouternum); + Installs w/integrated RT: CREATE SEQUENCE attributes_id_seq; diff --git a/README.1.5.7.lastbit b/README.1.5.7.lastbit new file mode 100644 index 000000000..7ae600c38 --- /dev/null +++ b/README.1.5.7.lastbit @@ -0,0 +1,50 @@ +this is ONLY for people upgrading from CVS snapshots after march 12th who +have most of the changes in README.1.5.7 already. + +if you're upgrading from 1.5.0pre6, see README.1.5.7 instead. + +if you're upgrading from 1.4.x, see httemplate/docs/upgrade10.html instead. + + +ALTER TABLE rate_detail ADD COLUMN ratedetailnum int; +ALTER TABLE rate_detail ALTER COLUMN ratedetailnum SET DEFAULT nextval('public.rate_detail_ratedetailnum_seq'::text); +CREATE SEQUENCE rate_detail_ratedetailnum_seq; +ALTER TABLE rate_detail ALTER ratedetailnum SET NOT NULL; +ALTER TABLE rate_detail ADD PRIMARY KEY (ratedetailnum); + +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; +ALTER TABLE type_pkgs ALTER typepkgnum SET NOT NULL; +ALTER TABLE type_pkgs ADD PRIMARY KEY (typepkgnum); + +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; +ALTER TABLE cust_bill_pkg ALTER billpkgnum SET NOT NULL; +ALTER TABLE cust_bill_pkg ADD PRIMARY KEY (billpkgnum); + +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; +ALTER TABLE pkg_svc ALTER pkgsvcnum SET NOT NULL; +ALTER TABLE pkg_svc ADD PRIMARY KEY (pkgsvcnum); + +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; +ALTER TABLE part_svc_router ALTER svcrouternum SET NOT NULL; +ALTER TABLE part_svc_router ADD PRIMARY KEY (svcrouternum); + +ALTER TABLE reg_code_pkg ADD codepkgnum int; +ALTER TABLE reg_code_pkg ALTER COLUMN codepkgnum SET DEFAULT nextval('public.reg_code_pkg_codepkgnum_seq'::text); +CREATE SEQUENCE reg_code_pkg_codepkgnum_seq; +ALTER TABLE reg_code_pkg ALTER codepkgnum SET NOT NULL; +ALTER TABLE reg_code_pkg ADD PRIMARY KEY (codepkgnum); + +ALTER TABLE virtual_field ADD vfieldnum int; +ALTER TABLE virtual_field ALTER COLUMN vfieldnum SET DEFAULT nextval('public.virtual_field_vfieldnum_seq'::text); +CREATE SEQUENCE virtual_field_vfieldnum_seq; +ALTER TABLE virtual_field ALTER vfieldnum SET NOT NULL; +ALTER TABLE virtual_field ADD PRIMARY KEY (vfieldnum); + diff --git a/httemplate/docs/schema.html b/httemplate/docs/schema.html index e9688756b..cdb59a2e9 100644 --- a/httemplate/docs/schema.html +++ b/httemplate/docs/schema.html @@ -23,6 +23,7 @@
  • cust_bill - Invoices. Declarations that a customer owes you money. The specific charges are itemized in cust_bill_pkg.
      +
    • billpkgnum - primary_key
    • invnum - primary key
    • custnum - customer
    • _date @@ -293,6 +294,7 @@
  • reg_code_pkg - Registration code link to package definitions @@ -321,6 +323,7 @@
  • pkg_svc
  • type_pkgs @@ -474,6 +478,7 @@
  • rate_detail - Call rate detail
      +
    • ratedetailnum - primary key
    • ratenum - rate plan
    • orig_regionnum - call origination region
    • dest_regionnum - call destination region diff --git a/httemplate/docs/upgrade10.html b/httemplate/docs/upgrade10.html index 70a9aeee3..b81268e46 100644 --- a/httemplate/docs/upgrade10.html +++ b/httemplate/docs/upgrade10.html @@ -52,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 ( @@ -186,10 +188,11 @@ CREATE TABLE cust_pay_void ( CREATE INDEX cust_pay_void1 ON cust_pay_void(custnum); CREATE TABLE part_pkg_option ( - optionnum int primary key, + 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 ); @@ -201,12 +204,14 @@ CREATE TABLE rate ( ); 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 + sec_granularity int NOT NULL, + PRIMARY KEY (ratedetailnum) ); CREATE UNIQUE INDEX rate_detail1 ON rate_detail ( ratenum, orig_regionnum, dest_regionnum ); @@ -237,8 +242,10 @@ 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 ); @@ -298,6 +305,24 @@ 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; +ALTER TABLE type_pkgs ALTER typepkgnum SET NOT NULL; +ALTER TABLE type_pkgs ADD PRIMARY KEY (typepkgnum); + +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; +ALTER TABLE cust_bill_pkg ALTER billpkgnum SET NOT NULL; +ALTER TABLE cust_bill_pkg ADD PRIMARY KEY (billpkgnum); + +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; +ALTER TABLE pkg_svc ALTER pkgsvcnum SET NOT NULL; +ALTER TABLE pkg_svc ADD PRIMARY KEY (pkgsvcnum); + On recent Pg versions: ALTER TABLE svc_forward ALTER COLUMN srcsvc DROP NOT NULL;