herding elephants: add primary keys to *all* tables for slony

11 files changed, 147 insertions, 17 deletions

diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index 6800707..3478904 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<FS::cust_bill>)
 
 =item pkgnum - package (see L<FS::cust_pkg>) 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 0b23ab5..df04cc9 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 ea52176..065ddbe 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<FS::part_pkg>)
 
 =item svcpart - Service definition (see L<FS::part_svc>)
@@ -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 313b053..7d54355 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<FS::rate>)
 
 =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 598497c..9b9a877 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<FS::reg_code>)
 
 =item pkgpart - package definition (see L<FS::part_pkg>)
@@ -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 07ad2bc..bf34e7c 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<FS::agent_type>
 
 =item pkgpart - Billing item definition, see L<FS::part_pkg>
@@ -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 38b9166..30ef205 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 53b0585..5e9b97a 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 0000000..7ae600c
--- /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 e968875..cdb59a2 100644
--- a/httemplate/docs/schema.html
+++ b/httemplate/docs/schema.html
@@ -23,6 +23,7 @@
       </ul>
     <li><a name="cust_bill" href="man/FS/cust_bill.html">cust_bill</a> - Invoices.  Declarations that a customer owes you money.  The specific charges are itemized in <a href="#cust_bill_pkg">cust_bill_pkg</a>.
       <ul>
+        <li>billpkgnum - primary_key
         <li>invnum - primary key
         <li>custnum - <a href="#cust_main">customer</a>
         <li>_date
@@ -293,6 +294,7 @@
       </ul>
     <li><a name="reg_code_pkg" href="man/FS/reg_code_pkg.html">reg_code_pkg</A> - Registration code link to package definitions
       <ul>
+        <li>codepkgnum - primary key
         <li>codenum - <a href="#reg_code">Registration code</a>
         <li>pkgpart - <a href="#part_pkg">Package definition</a>
       </ul>
@@ -321,6 +323,7 @@
       </ul>
     <li><a name="pkg_svc" href="man/FS/pkg_svc.html">pkg_svc</a>
       <ul>
+        <li>pkgsvcnum - primary key
         <li>pkgpart - <a href="#part_pkg">Package definition</a>
         <li>svcpart - <a href="#part_svc">Service definition</a>
         <li>quantity - quantity of this service that this package includes
@@ -437,6 +440,7 @@
       </ul>
     <li><a name="type_pkgs" href="man/FS/type_pkgs.html">type_pkgs</a>
       <ul>
+        <li>typepkgnum - primary key
         <li>typenum - <a href="#agent_type">agent type</a>
         <li>pkgpart - <a href="#part_pkg">Package definition</a>
       </ul>
@@ -474,6 +478,7 @@
       </ul>
     <li><a name="rate_detail" href="man/FS/rate_detail.html">rate_detail</a> - Call rate detail
       <ul>
+        <li>ratedetailnum - primary key
         <li>ratenum - <a href="#rate">rate plan</a>
         <li>orig_regionnum - call origination <a href="#rate_region">region</a>
         <li>dest_regionnum - call destination <a href="#rate_region">region</a>
diff --git a/httemplate/docs/upgrade10.html b/httemplate/docs/upgrade10.html
index 70a9aee..b81268e 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;