2.19 upgrade script:


-- Set org unit of program instance to org unit of tracked entity instance

alter table programinstance add column organisationunitid integer;

update programinstance pi
set organisationunitid = (
  select organisationunitid from trackedentityinstance tei
  where pi.trackedentityinstanceid = tei.trackedentityinstanceid)
where pi.organisationunitid is null;


2.20 upgrade script


-- Set userid of messageconversation table based on first message

alter table messageconversation drop constraint fk_messageconversation_userid;

alter table messageconversation add column userid integer;

update messageconversation mc set userid=(
  select m.userid from message m
  inner join messageconversation_messages mcm on m.messageid = mcm.messageid
  where mcm.messageconversationid = mc.messageconversationid
  and mcm.sort_order=1
  limit 1)
where mc.userid is null;

-- Set organisation units with no opening date to 1970 and set not null

update organisationunit set openingdate='1970-01-01' where openingdate is null;
alter table organisationunit alter column openingdate set not null;

2.22
Config file now called dhis.conf

2.23 upgrade script

-- Update source type for program rules

update programrulevariable set sourcetype = 'DATAELEMENT_CURRENT_EVENT', lastupdated = current_timestamp 
where uid in (
  select prv.uid from programrulevariable prv 
  inner join program p on p.programid = prv.programid 
  where p.type = 'WITHOUT_REGISTRATION' and (
  prv.sourcetype = 'DATAELEMENT_NEWEST_EVENT_PROGRAM' or prv.sourcetype = 'DATAELEMENT_NEWEST_EVENT_PROGRAM_STAGE')
);



2.24 Upgrade
User Interface

Maintenance operations for metadata attributes, constants and option sets have been moved to the Maintenance app.
Web API

The translations solution has been rewritten, and translations are now included for each object in the API response 
instead of from the old /api/translations endpoint. Please consult the translations documentation.
Web API versioning is gradually being introduced. If you want to write your app against a specific API version 
for greater stability you can use /api/24 as base URL. An example is play.dhis2.org/demo/api/24/dataElements. 
This work will be completed in v 2.25.


-- 2.25 upgrade script

-- Add deleted column to datavalue table

-- This will be handled by DHIS 2, however running this script will be much faster in comparison for large datavalue tables

begin;

-- 1) Drop indexes and foreign keys

alter table datavalue drop constraint datavalue_pkey;
drop index in_datavalue_lastupdated;

-- 2) Add deleted column, set to false, set to not-null and create index

alter table datavalue add column deleted boolean;
update datavalue set deleted = false where deleted is null;
alter table datavalue alter column deleted set not null;
create index in_datavalue_deleted on datavalue(deleted);

-- 3) Recreate indexes and foreign keys

alter table datavalue add constraint datavalue_pkey primary key(dataelementid, periodid, sourceid, categoryoptioncomboid, attributeoptioncomboid);
create index in_datavalue_lastupdated on datavalue(lastupdated);

end;


-- 2.26 upgrade script

-- Set data value 'created' and 'lastupdated' columns to now if null and set to not null

update datavalue set created = now()::timestamp where created is null;
alter table datavalue alter column created set not null;

update datavalue set lastupdated = now()::timestamp where lastupdated is null;
alter table datavalue alter column lastupdated set not null;

-- Upgrade coordinate tracked entity data values to use brackets

update trackedentitydatavalue
set value = '[' || value || ']'
where dataelementid in (
  select dataelementid from dataelement
  where valuetype='COORDINATE')
and value is not null
and value ~ ','
and value not like '\[%\]';

-- Upgrade coordinate tracked entity attribute values to use brackets

update trackedentityattributevalue
set value = '[' || value || ']'
where trackedentityattributeid in (
  select trackedentityattributeid from trackedentityattribute
  where valuetype='COORDINATE')
and value is not null
and value ~ ','
and value not like '\[%\]';

-- Upgrade authority for messaging module

insert into userroleauthorities 
select userroleid, 'M_dhis-web-messaging'
from userroleauthorities 
where authority = 'M_dhis-web-dashboard-integration';

-- Add new event analytics authority to user roles with access to event reports/visualizer/dashboard

insert into userroleauthorities(userroleid,authority)
select distinct ura.userroleid, 'F_VIEW_EVENT_ANALYTICS' as view_event_analytics
from userroleauthorities ura
where ura.authority in ('M_dhis-web-event-reports', 'M_dhis-web-event-visualizer', 'M_dhis-web-dashboard-integration');


--2.27 upgrade script

NONE