From 3586017f5020097a7134d21e32aed4bf3bf37eb9 Mon Sep 17 00:00:00 2001
From: Damyan Ivanov <dam+mobileledger@ktnx.net>
Date: Tue, 26 Mar 2019 00:56:43 +0200
Subject: [PATCH] single SQL file for initial db creation

applying 20 incremental revisions appears a bit too much
---
 .../java/net/ktnx/mobileledger/utils/MLDB.java  | 17 ++++++++++-------
 app/src/main/res/raw/create-db.sql              | 12 ++++++++++++
 2 files changed, 22 insertions(+), 7 deletions(-)
 create mode 100644 app/src/main/res/raw/create-db.sql

diff --git a/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java b/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
index 582e73b4..b5317ad3 100644
--- a/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
+++ b/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
@@ -212,6 +212,7 @@ public final class MLDB {
 class MobileLedgerDatabase extends SQLiteOpenHelper implements AutoCloseable {
     private static final String DB_NAME = "MoLe.db";
     private static final int LATEST_REVISION = 20;
+    private static final String CREATE_DB_SQL = "create-db.sql";
 
     private final Application mContext;
 
@@ -225,7 +226,7 @@ class MobileLedgerDatabase extends SQLiteOpenHelper implements AutoCloseable {
     @Override
     public void onCreate(SQLiteDatabase db) {
         Log.d("db", "onCreate called");
-        onUpgrade(db, -1, LATEST_REVISION);
+        applyRevisionFile(db, CREATE_DB_SQL);
     }
 
     @Override
@@ -235,15 +236,18 @@ class MobileLedgerDatabase extends SQLiteOpenHelper implements AutoCloseable {
     }
 
     private void applyRevision(SQLiteDatabase db, int rev_no) {
-        final Resources rm = mContext.getResources();
         String rev_file = String.format(Locale.US, "sql_%d", rev_no);
 
+        applyRevisionFile(db, rev_file);
+    }
+    private void applyRevisionFile(SQLiteDatabase db, String rev_file) {
+        final Resources rm = mContext.getResources();
         int res_id = rm.getIdentifier(rev_file, "raw", mContext.getPackageName());
         if (res_id == 0)
-            throw new SQLException(String.format(Locale.US, "No resource for revision %d", rev_no));
+            throw new SQLException(String.format(Locale.US, "No resource for %s", rev_file));
         db.beginTransaction();
         try (InputStream res = rm.openRawResource(res_id)) {
-            Log.d("db", "Applying revision " + String.valueOf(rev_no));
+            Log.d("db", "Applying " + rev_file);
             InputStreamReader isr = new InputStreamReader(res);
             BufferedReader reader = new BufferedReader(isr);
 
@@ -263,8 +267,7 @@ class MobileLedgerDatabase extends SQLiteOpenHelper implements AutoCloseable {
                 }
                 catch (Exception e) {
                     throw new RuntimeException(
-                            String.format("Error applying revision %d, line %d", rev_no, line_no),
-                            e);
+                            String.format("Error applying %s, line %d", rev_file, line_no), e);
                 }
                 line_no++;
             }
@@ -272,7 +275,7 @@ class MobileLedgerDatabase extends SQLiteOpenHelper implements AutoCloseable {
             db.setTransactionSuccessful();
         }
         catch (IOException e) {
-            Log.e("db", String.format("Error opening raw resource for revision %d", rev_no));
+            Log.e("db", String.format("Error opening raw resource for %s", rev_file));
             e.printStackTrace();
         }
         finally {
diff --git a/app/src/main/res/raw/create-db.sql b/app/src/main/res/raw/create-db.sql
new file mode 100644
index 00000000..7095b178
--- /dev/null
+++ b/app/src/main/res/raw/create-db.sql
@@ -0,0 +1,12 @@
+create table accounts(profile varchar not null, name varchar not null, name_upper varchar not null, hidden boolean not null default 0, keep boolean not null default 0, level integer not null, parent_name varchar, expanded default 1);
+create unique index un_accounts on accounts(profile, name);
+create table options(profile varchar not null, name varchar not null, value varchar);
+create unique index un_options on options(profile,name);
+create table account_values(profile varchar not null, account varchar not null, currency varchar not null default '', keep boolean, value decimal not null );
+create unique index un_account_values on account_values(profile,account,currency);
+create table description_history(description varchar not null primary key, keep boolean, description_upper varchar);
+create table profiles(uuid varchar not null primary key, name not null, url not null, use_authentication boolean not null, auth_user varchar, auth_password varchar, order_no integer, permit_posting boolean default 0, theme integer default -1);
+create table transactions(profile varchar not null, id integer not null, data_hash varchar not null, date varchar not null, description varchar not null, keep boolean not null default 0);
+create unique index un_transactions_id on transactions(profile,id);
+create unique index un_transactions_data_hash on transactions(profile,data_hash);
+create table transaction_accounts(profile varchar not null, transaction_id integer not null, account_name varchar not null, currency varchar not null default '', amount decimal not null, constraint fk_transaction_accounts_acc foreign key(profile,account_name) references accounts(profile,account_name), constraint fk_transaction_accounts_trn foreign key(profile, transaction_id) references transactions(profile,id));
-- 
2.39.2