PostgreSQL defines several system columns for each table. These include oid, tableoid, xmin, cmin, xmax, cmax, and ctid. These column names cannot be used in a create table statement. There happens to be one table, collection_tracker, that uses 'ctId' as a column name; this should be renamed, as otherwise DBIC won't be able to add that table to a Pg database.
Created attachment 23434 [details] [review] Bug 11384: rename the collections_tracking.ctId column 'ctId' as a column name conflicts with one of the system columns that PostgreSQL uses for each table, and consequently needs to be renamed to enable deploying the schema to a Pg database. This patch makes this change. To test: [1] Apply the patch and run the SQL specified in the database updated. [2] Verify that the collections_tracking table no longer has a ctId column, but now has collections_tracking_id. [3] Verify that prove -v t/db_dependent/RotatingCollections.t passes. [4] Verify that installer/data/mysql/kohastructure.sql runs cleanly in an empty database. This patch does not affect user-visible behavior given the fact that the rotating collections feature is currently disabled. Signed-off-by: Galen Charlton <gmc@esilibrary.com>
Created attachment 23435 [details] [review] Bug 11384: (follow-up) update DBIC schema classes This patch updates the DBIC schema class for CollectionTracking to reflect the new name of its primary key column. To test: The CollectionTracking class is not currently used, but if you *really* want to test this, take a look at the following branch: http://git.librarypolice.com/?p=koha-galen.git;a=shortlog;h=refs/heads/pg Then, set up a PostgreSQL database, update koha-conf.xml to point to it, then run pg/deploy and verify that the collections_tracking table is created in the Pg database. Signed-off-by: Galen Charlton <gmc@esilibrary.com>
Created attachment 23769 [details] [review] Bug 11384: rename the collections_tracking.ctId column 'ctId' as a column name conflicts with one of the system columns that PostgreSQL uses for each table, and consequently needs to be renamed to enable deploying the schema to a Pg database. This patch makes this change. To test: [1] Apply the patch and run the SQL specified in the database updated. [2] Verify that the collections_tracking table no longer has a ctId column, but now has collections_tracking_id. [3] Verify that prove -v t/db_dependent/RotatingCollections.t passes. [4] Verify that installer/data/mysql/kohastructure.sql runs cleanly in an empty database. This patch does not affect user-visible behavior given the fact that the rotating collections feature is currently disabled. Signed-off-by: Galen Charlton <gmc@esilibrary.com> Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Created attachment 23770 [details] [review] Bug 11384: (follow-up) update DBIC schema classes This patch updates the DBIC schema class for CollectionTracking to reflect the new name of its primary key column. To test: The CollectionTracking class is not currently used, but if you *really* want to test this, take a look at the following branch: http://git.librarypolice.com/?p=koha-galen.git;a=shortlog;h=refs/heads/pg Then, set up a PostgreSQL database, update koha-conf.xml to point to it, then run pg/deploy and verify that the collections_tracking table is created in the Pg database. Signed-off-by: Galen Charlton <gmc@esilibrary.com> Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Created attachment 23776 [details] [review] Bug 11384: rename the collections_tracking.ctId column 'ctId' as a column name conflicts with one of the system columns that PostgreSQL uses for each table, and consequently needs to be renamed to enable deploying the schema to a Pg database. This patch makes this change. To test: [1] Apply the patch and run the SQL specified in the database updated. [2] Verify that the collections_tracking table no longer has a ctId column, but now has collections_tracking_id. [3] Verify that prove -v t/db_dependent/RotatingCollections.t passes. [4] Verify that installer/data/mysql/kohastructure.sql runs cleanly in an empty database. This patch does not affect user-visible behavior given the fact that the rotating collections feature is currently disabled. Signed-off-by: Galen Charlton <gmc@esilibrary.com> Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz> Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Created attachment 23777 [details] [review] Bug 11384: (follow-up) update DBIC schema classes This patch updates the DBIC schema class for CollectionTracking to reflect the new name of its primary key column. To test: The CollectionTracking class is not currently used, but if you *really* want to test this, take a look at the following branch: http://git.librarypolice.com/?p=koha-galen.git;a=shortlog;h=refs/heads/pg Then, set up a PostgreSQL database, update koha-conf.xml to point to it, then run pg/deploy and verify that the collections_tracking table is created in the Pg database. Signed-off-by: Galen Charlton <gmc@esilibrary.com> Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz> Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Pushed to master.
Patches pushed to 3.14.x, will be in 3.14.2. (created version 3.14.01.002)
FWIW, upgrading koha-common from 3.14.01 to 3.14.03 threw the following: Upgrade to 3.14.01.001 done (Bug 10970 - Update MARC21 frameworks to Update Nr. 17 - DB update) DBD::mysql::db do failed: Table 'koha_biblios.collections_tracking' doesn't exist at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 7830. It is probably a good idea to trap such errors if they are expected by testing for the existence of the table perior to operating on it.
Created attachment 25154 [details] [review] [3.14.x]Bug 11384: (follow-up) test if table exists in update database Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>
(In reply to Chris Nighswonger from comment #9) > It is probably a good idea to trap such errors if they are expected by > testing for the existence of the table perior to operating on it. I've pushed a patch that tests table existence into 3.14.x branch.
While testing for the existance of the table is good - it worries me a bit, that the table is not there. I got the table on my installations.
(In reply to Katrin Fischer from comment #12) > While testing for the existance of the table is good - it worries me a bit, > that the table is not there. I got the table on my installations. Indeed, this table exists since 3.2
(In reply to Fridolin SOMERS from comment #13) > (In reply to Katrin Fischer from comment #12) > > While testing for the existance of the table is good - it worries me a bit, > > that the table is not there. I got the table on my installations. > Indeed, this table exists since 3.2 Interesting and a bit disconcerning since this DB began with a 3.0 installation and has moved along ever since. It provokes a few not-necessarily-new thoughts: 1. We should probably "stamp" a new database when it is created with the original rev number it was created under. This field would never change and could be used to somehow "verify" that the current DB structure at any point in time was, indeed, current. 2. There should definitely be some mechanism in place to veryify the existence of tables prior to "working" on them during upgrades. 3. When dealing with essential tables, there should be a mechanism to throw a rather large warning to the user when one of them is found missing, even if there is no "easy" way to fix it because what you don't know can, indeed, hurt you. But we have beat around this bush before, so I just list them here for posterity's sake. :-)