Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: development
Fetching contributors…

Cannot retrieve contributors at this time

file 57 lines (28 sloc) 2.082 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
/* Script to update apps.db from schema version 5 to 6 */

begin exclusive transaction;

/* Fix incorrect long name */

update country set name = 'Vietnam' where iso2 = 'VN';

/* Fix incorrect language code for China */

update country set language = 'zh-CN' where iso2 = 'CN';


/* Remove reports without dates */

delete from sale where report_id in (select id from report where from_date is null or until_date is null);

delete from report where from_date is null or until_date is null;

/* Add table for IAP */

CREATE TABLE IF NOT EXISTS InAppPurchase ('id' INTEGER PRIMARY KEY, 'title' VARCHAR, 'vendor_identifier' VARCHAR, company_name VARCHAR, parent INTEGER);

/* move existing IAP */

replace into InAppPurchase (id, title, vendor_identifier, company_name) select id, title, vendor_identifier, company_name from app where id in (select distinct app_id from sale where type_id = 101);
delete from app where id in (select id from InAppPurchase);
delete from AppAppGrouping where app_id in (select id from InAppPurchase );


/* Create Sum table per app/IAP total */

CREATE TABLE IF NOT EXISTS ProductTotals (product_id INTEGER, currency char(3), sum_units INTEGER, sum_royalties REAL, PRIMARY KEY (product_id, currency));


/* Move app grouping from own table to report table */

DROP TABLE IF EXISTS tmp_report;

CREATE TABLE tmp_report as select id, report_type_id, report_region_id, from_date, until_date, downloaded_date from report;

DROP TABLE report;

CREATE TABLE report (id INTEGER PRIMARY KEY, report_type_id INTEGER, report_region_id INTEGER, from_date DATE, until_date DATE, downloaded_date DATE, appgrouping_id INTEGER);

INSERT INTO report (id, report_type_id, report_region_id, from_date, until_date, downloaded_date, appgrouping_id) select id, report_type_id, report_region_id, from_date, until_date, downloaded_date, reportappgrouping.appgrouping_id from tmp_report left join reportappgrouping on tmp_report.id = report_id;

DROP TABLE tmp_report;

DROP TABLE ReportAppGrouping;

/* update schema_version */

update meta set schema_version = 6;


commit;
Something went wrong with that request. Please try again.