Bug 6097

For reference:

http://github.com/pkp/ojs/blob/ojs-2_3_3-3/plugins/generic/counter/counter_monthly_log_1_1.xml

There's no postgres specific code yet, only mysql.

Colin, those ALTER TABLE statements are executed when the database descriptor is synced against the database (see dbscripts/xml/upgrade.xml); if you need to change the way the counter logs are stored, that step in the upgrade will need to be executed before the descriptor is synced or else the columns will be dropped and the data lost.

Created attachment 3319 [details]
Patch against OJS 2.3.3

Adding the counter_monthly_log_1_1.xml to dbscripts/xml/upgrade.xml and adding support for Postgres

Committed to official:

https://github.com/pkp/ojs/commit/a68cf42c204479bc0ee13e2609d2d5e88ffcc0e5

Colin, I don't think that your solution is idempotent (=can be executed several times without error always leading to the correct end result). Idempotency is important in case something goes wrong at in the update process and users have to repeat it. Users should be able to re-execute the update process without error even if it has been partially executed before.

Why don't you move your preparatory changes to a preupdate file instead so that you can use the normal schema synchronization mechanism? There are several preupdate files in OJS that you can use as samples. I'll forward an email about idempotency (if I find it) with more information.

Non-idempotency has been confirmed. Reopened until implemented.

Colin, I spotted some space-based indents in your fix here -- make sure your editor is using tabs.

Also, I've encountered OJS 2.3.0 installs that don't have a counter_monthly_log table. I'd suggest making this upgrade conditional on that table existing. (See the "condition" attribute in dbscripts/xml/upgrade.xml for that purpose.) Also, the CREATE TABLE statement shouldn't be hard-coded in XML -- use a schema.xml file instead.

Hi, 
our user are asking for COUNTER plugin, that we can not enable since there are some issues opened about it.
Do you have any news about the idempotency problem solution?

We need absolutely to enable COUNTER statistics in a few days.

Thank you for helping us.

Andrea

Hi,
we succesfully used this method with Postgres (thank to Colin):
1. Copy from the old DB the table counter_monthly_log into the new as  counter_monthly_log_backup
2. drop the counter_monthly_log table and recreate it:
CREATE TABLE counter_monthly_log
(
  "year" bigint,
  "month" bigint,
  journal_id bigint,
  count_html bigint,
  count_pdf bigint,
  count_other bigint,
  CONSTRAINT counter_monthly_log_01 PRIMARY KEY ("year", "month", journal_id)
)
WITHOUT OIDS;

3. Then we would do a SELECT from the old table and put it in the new table.
INSERT INTO counter_monthly_log
(year,month,journal_id,count_html,count_pdf,count_other)
SELECT year, 1 as month, journal_id, 0, count_jan as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 2 as month, journal_id, 0, count_feb as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 3 as month, journal_id, 0, count_mar as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 4 as month, journal_id, 0, count_apr as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 5 as month, journal_id, 0, count_may as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 6 as month, journal_id, 0, count_jun as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 7 as month, journal_id, 0, count_jul as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 8 as month, journal_id, 0, count_aug as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 9 as month, journal_id, 0, count_sep as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 10 as month, journal_id, 0, count_oct as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 11 as month, journal_id, 0, count_nov as count, 0 FROM
counter_monthly_log_backup
UNION
SELECT year, 12 as month, journal_id, 0, count_dec as count, 0 FROM
counter_monthly_log_backup
ORDER BY journal_id, year, month

As Andrea just pointed out, for this to work properly, the count* fields should be not null and default to 0.

So step 2 in the manual process should be:

CREATE TABLE counter_monthly_log
(
  "year" bigint,
  "month" bigint,
  journal_id bigint,
  count_html bigint NOT NULL DEFAULT 0,
  count_pdf bigint NOT NULL DEFAULT 0,
  count_other bigint NOT NULL DEFAULT 0,
  CONSTRAINT counter_monthly_log_01 PRIMARY KEY ("year", "month", journal_id)
)
WITHOUT OIDS;

This is already specified in the schema so no change required there.