another step towards surrogate ID db objects

[mobile-ledger.git] / app / src / main / res / raw / db_59.sql

diff --git a/app/src/main/res/raw/db_59.sql b/app/src/main/res/raw/db_59.sql
index 67c9beb7a5dbcccc1a637efd4ce7ab1e4804a196..8f5d8009c7569a0008ea7ce6279c0cc840833cde 100644 (file)
--- a/app/src/main/res/raw/db_59.sql
+++ b/app/src/main/res/raw/db_59.sql
@@ -15,9 +15,18 @@
 
 -- migrate from revision 58 to revision 59
 
+-- pragmas need to be outside of transaction control
+-- foreign_keys is needed so that foreign key constraints are redirected
+
+commit transaction;
+pragma foreign_keys = on;
+
+begin transaction;
+
+-- profiles
 CREATE TABLE profiles_new (
 id INTEGER NOT NULL PRIMARY KEY,
-uuid text,
+deprecated_uuid text,
 name TEXT NOT NULL,
 url TEXT NOT NULL,
 use_authentication INTEGER NOT NULL,
@@ -37,7 +46,7 @@ detected_version_major INTEGER NOT NULL,
 detected_version_minor INTEGER NOT NULL);
 
 insert into profiles_new(
-       uuid, name, url, use_authentication, auth_user, auth_password,
+       deprecated_uuid, name, url, use_authentication, auth_user, auth_password,
        order_no, permit_posting, theme, preferred_accounts_filter, future_dates, api_version,
        show_commodity_by_default, default_commodity, show_comments_by_default, detected_version_pre_1_19,
        detected_version_major, detected_version_minor)
@@ -47,6 +56,7 @@ select uuid, name, url, use_authentication, auth_user, auth_password,
        detected_version_major, detected_version_minor
 from profiles;
 
+-- accounts
 create table accounts_new(
 id integer primary key not null,
 profile_id integer not null references profiles_new(id) on delete cascade on update restrict,
@@ -61,10 +71,100 @@ generation INTEGER NOT NULL DEFAULT 0);
 insert into accounts_new(profile_id, level, name, name_upper, parent_name, expanded, amounts_expanded, generation)
 select p.id, a.level, a.name, a.name_upper, a.parent_name, a.expanded, a.amounts_expanded, a.generation
 from profiles_new p
-join accounts a on a.profile=p.uuid;
+join accounts a on a.profile=p.deprecated_uuid;
 
 drop table accounts;
 alter table accounts_new rename to accounts;
 
 drop table profiles;
-alter table profiles_new rename to profiles;
\ No newline at end of file
+alter table profiles_new rename to profiles;
+
+create index fk_account_profile on accounts(profile_id);
+create unique index un_account_name on accounts(profile_id, name);
+
+-- options
+create table options_new(
+name text not null,
+profile_id integer not null,
+value text,
+primary key(profile_id,name));
+
+insert into options_new(name, profile_id, value)
+select o.name, p.id, o.value
+from options o
+join profiles p on p.deprecated_uuid = o.profile;
+
+drop table options;
+alter table options_new rename to options;
+
+update options
+set name='profile_id'
+  , value=(select p.id from profiles p where p.deprecated_uuid=options.value)
+where name='profile_uuid';
+
+-- account_values
+create table account_values_new(
+id integer not null primary key,
+account_id integer not null references accounts(id) on delete cascade on update restrict,
+currency text not null default '',
+value real not null,
+generation integer not null default 0);
+
+insert into account_values_new(account_id, currency, value, generation)
+select a.id, av.currency, av.value, av.generation
+from account_values av
+join profiles p on p.deprecated_uuid=av.profile
+join accounts a on a.profile_id = p.id and a.name = av.account;
+
+drop table account_values;
+alter table account_values_new rename to account_values;
+
+create index fk_account_value_acc on account_values(account_id);
+create unique index un_account_values on account_values(account_id, currency);
+
+-- transactions
+create table transactions_new(
+id integer not null primary key,
+profile_id integer not null references profiles(id) on delete cascade on update restrict,
+ledger_id integer not null,
+description text not null,
+year integer not null,
+month integer not null,
+day integer not null,
+comment text,
+data_hash text not null,
+generation integer not null);
+
+insert into transactions_new(profile_id, ledger_id, description, year, month, day, comment, data_hash, generation)
+select p.id, t.id, t.description, t.year, t.month, t.day, t.comment, t.data_hash, t.generation
+from transactions t
+join profiles p on p.deprecated_uuid = t.profile;
+
+-- transaction_accounts
+create table transaction_accounts_new(
+    id integer not null primary key,
+    transaction_id integer not null references transactions_new(id) on delete cascade on update restrict,
+    order_no integer not null,
+    account_name text not null,
+    currency text not null default '',
+    amount real not null,
+    comment text,
+    generation integer not null default 0);
+
+insert into transaction_accounts_new(transaction_id, order_no, account_name,
+    currency, amount, comment, generation)
+select ta.transaction_id, ta.order_no, ta.account_name, ta.currency, ta.amount, ta.comment, ta.generation
+from transaction_accounts ta;
+
+drop table transaction_accounts;
+alter table transaction_accounts_new rename to transaction_accounts;
+
+drop table transactions;
+alter table transactions_new rename to transactions;
+
+create index idx_transaction_description on transactions(description);
+create unique index un_transactions_ledger_id on transactions(profile_id, ledger_id);
+create index fk_transaction_profile on transactions(profile_id);
+
+create unique index un_transaction_accounts on transaction_accounts(transaction_id, order_no);
+create index fk_trans_acc_trans on transaction_accounts(transaction_id);
\ No newline at end of file