Accidentally deleted tax_result column

[hdoupe]

I accidentally deleted the tax_result column in the TaxBrain database for runs occurring before 11/22/2017. Heroku only saves database history for the previous week. Fortunately, I pulled the production and test databases down on 11/09/2017 and still have them on my laptop. So, I should be able to save the old tax runs. However, there was a period from 11/09/2017 to 11/22/2017 for which the tax results are gone.

When I created a decorator for the tax_result attribute in PR #738 I added a field _tax_result to the Django model. However, when I did the migration, instead of changing the name from tax_result to _tax_result, I removed tax_result and added a new field _tax_result.

[hdoupe]

The test database was updated with the following work flow:

# pull down test db
heroku pg:pull HEROKU_POSTGRESQL_OLIVE pb_test_0125_1 -a ospc-taxes7

# duplicate database ; pb_test_fixed will be pushed to app at the end
createdb -T pb_test_0125_1 pb_test_fixed;
# duplicate back up db
createdb -T pb_test_1109 pb_test_1109_copy2;

# duplicate table
# bash psql
\c pb_test_01109_copy2
create table taxbrain_taxsaveinputs_1109 as table taxbrain_taxsaveinputs;

# move back up taxbrain_taxsaveinputs table into app database
pg_dump -U henrydoupe -d pb_test_1109_copy2 -t taxbrain_taxsaveinputs_1109 | psql -U henrydoupe -d pb_test_fixed

# join and update values
# bash psql
\c pb_test_fixed

# duplicate everything
create table taxbrain_taxsaveinputs_copy as table taxbrain_taxsaveinputs;

create table taxbrain_taxsaveinputs_fixed as
    select * from taxbrain_taxsaveinputs as new
        left join (
            select id as old_id, tax_result as old_tax_result from taxbrain_taxsaveinputs_1109
        ) as old
        on new.id = old.old_id;


# update tax_result column with tax_result from back up table
update taxbrain_taxsaveinputs_fixed
set tax_result=old_tax_result
where tax_result is null;

# check work; should be equal to number of archived old results
select count(1)
from taxbrain_taxsaveinputs_fixed
where tax_result = old_tax_result and old_tax_result is not null;

# drop intermediate variables
alter table taxbrain_taxsaveinputs_fixed
drop column old_tax_result;
alter table taxbrain_taxsaveinputs_fixed
drop column old_id;

# update tax_result on real table
update taxbrain_taxsaveinputs
set tax_result = fixed.tax_result
from taxbrain_taxsaveinputs_fixed fixed
where fixed.id = taxbrain_taxsaveinputs.id;

# check work; is equal to count(old results) + count(new results)
select count(*) from taxbrain_taxsaveinputs as fin 
inner join (
    select id as fix_id, tax_result as fix_tax_result from taxbrain_taxsaveinputs_fixed
) as check_1 
on fin.id = check_1.fix_id
where fin.tax_result = check_1.fix_tax_result;


# drop tables
drop table taxbrain_taxsaveinputs_copy;
drop table taxbrain_taxsaveinputs_1109;
drop table taxbrain_taxsaveinputs_fixed;


heroku pg:push pb_test_fixed HEROKU_POSTGRESQL_COPPER -a ospc-taxes7

The update was successful. If anyone has SQL experience I would appreciate feedback on the SQL queries used. Note that we use PostgreSQL 9.4

[hdoupe]

The production database has been updated using the same work flow as described above.

Results 23000 through 24105 were deleted. Anything after 24105 is fine. 22456 and before are not working, but #814 should resolve that.

[hdoupe]

Above I said:

The production database has been updated using the same work flow as described above.

Results 23000 through 24105 were deleted. Anything after 24105 is fine. 22456 and before are not working, but #814 should resolve that.

CORRECTION: Results 20999 through 22413 were deleted. 17910 and before do not work, but #814 resolves that.