@@ -0,0 +1,1864 @@
+package uk.gov.migration;%0A%0Aimport org.flywaydb.core.api.migration.MigrationChecksumProvider;%0Aimport org.flywaydb.core.api.migration.jdbc.BaseJdbcMigration;%0A%0Aimport java.sql.Connection;%0Aimport java.sql.Statement;%0A%0Apublic class V__16_AddOrderToBlobs extends BaseJdbcMigration implements MigrationChecksumProvider %7B%0A    @Override%0A    public void migrate(Connection connection) throws Exception %7B%0A        Statement stmt = connection.createStatement();%0A%0A        try %7B%0A            stmt.execute(%22alter table item add column item_order serial%22);%0A%0A            // Work out the order of an item using the entries and system entries that reference it%0A            // since the two logs are independent, smoosh them together and treat the system entries%0A            // as coming first.%0A            stmt.execute(%22%22 +%0A                    %22with all_entry as (%5Cn%22 +%0A                    %22  select * from entry union all select * from entry_system%5Cn%22 +%0A                    %22)%5Cn%22 +%0A                    %22, combined_log as (%5Cn%22 +%0A                    %22  select sha256hex, row_number() over(order by type::text,entry_number) as combined_entry_number, row_number() over(partition by sha256hex order by type::text, entry_number) as item_entry_number from all_entry%5Cn%22 +%0A                    %22)%5Cn%22 +%0A                    %22, first_snapshot as (%5Cn%22 +%0A                    %22  select sha256hex, combined_entry_number from combined_log where item_entry_number=1%5Cn%22 +%0A                    %22)%5Cn%22 +%0A                    %22%5Cn%22 +%0A                    %22update item set item_order=first_snapshot.combined_entry_number from first_snapshot where item.sha256hex=first_snapshot.sha256hex%22);%0A%0A            stmt.execute(%22alter table item alter column item_order set not null%22);%0A%0A        %7D finally %7B%0A            stmt.close();%0A        %7D%0A    %7D%0A%0A%0A    @Override%0A    public Integer getChecksum() %7B%0A        return 1;%0A    %7D%0A%7D%0A

Add an order field to the items table