fix looking up old transactions with non-ASCII names (broken in 0.18.0)

according to the documentation, SQLite's NOCASE collation affects only
ASCII characters. Unicode is explicitly mentioned as not supported.
Strangely Unicode still works on some Android versions/variants, but not
on others.

This change stops relying on NOCASE collation and puts back upper case
variant of transaction description in a new column, which is then used
when looking up previous transactions for auto-completion

diff --git a/app/build.gradle b/app/build.gradle
index b488ba2a92036b83f95ae13ab959e5667853fff6..a0be40fdbc2b18763da569c2c9810d9abc8b4c32 100644 (file)
--- a/app/build.gradle
+++ b/app/build.gradle
@@ -24,8 +24,8 @@ android {
         minSdkVersion 22
         targetSdkVersion 30
         vectorDrawables.useSupportLibrary true
-        versionCode 42
-        versionName '0.19.1'
+        versionCode 43
+        versionName '0.19.2'
         testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
         javaCompileOptions {
             annotationProcessorOptions {

diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/61.json b/app/schemas/net.ktnx.mobileledger.db.DB/61.json
new file mode 100644 (file)
index 0000000..3a92716
--- /dev/null
+++ b/app/schemas/net.ktnx.mobileledger.db.DB/61.json
@@ -0,0 +1,834 @@
+{
+  "formatVersion": 1,
+  "database": {
+    "version": 61,
+    "identityHash": "0549e893bdbb2c7eb5666c3ee81091d6",
+    "entities": [
+      {
+        "tableName": "templates",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `regular_expression` TEXT NOT NULL, `test_text` TEXT, `transaction_description` TEXT, `transaction_description_match_group` INTEGER, `transaction_comment` TEXT, `transaction_comment_match_group` INTEGER, `date_year` INTEGER, `date_year_match_group` INTEGER, `date_month` INTEGER, `date_month_match_group` INTEGER, `date_day` INTEGER, `date_day_match_group` INTEGER, `is_fallback` INTEGER NOT NULL)",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "name",
+            "columnName": "name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "regularExpression",
+            "columnName": "regular_expression",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "testText",
+            "columnName": "test_text",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionDescription",
+            "columnName": "transaction_description",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionDescriptionMatchGroup",
+            "columnName": "transaction_description_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionComment",
+            "columnName": "transaction_comment",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "transactionCommentMatchGroup",
+            "columnName": "transaction_comment_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateYear",
+            "columnName": "date_year",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateYearMatchGroup",
+            "columnName": "date_year_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateMonth",
+            "columnName": "date_month",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateMonthMatchGroup",
+            "columnName": "date_month_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateDay",
+            "columnName": "date_day",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "dateDayMatchGroup",
+            "columnName": "date_day_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "isFallback",
+            "columnName": "is_fallback",
+            "affinity": "INTEGER",
+            "notNull": true
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [],
+        "foreignKeys": []
+      },
+      {
+        "tableName": "template_accounts",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `template_id` INTEGER NOT NULL, `acc` TEXT, `position` INTEGER NOT NULL, `acc_match_group` INTEGER, `currency` INTEGER, `currency_match_group` INTEGER, `amount` REAL, `amount_match_group` INTEGER, `comment` TEXT, `comment_match_group` INTEGER, `negate_amount` INTEGER, FOREIGN KEY(`template_id`) REFERENCES `templates`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE , FOREIGN KEY(`currency`) REFERENCES `currencies`(`id`) ON UPDATE RESTRICT ON DELETE RESTRICT )",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "templateId",
+            "columnName": "template_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "accountName",
+            "columnName": "acc",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "position",
+            "columnName": "position",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "accountNameMatchGroup",
+            "columnName": "acc_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "currency",
+            "columnName": "currency",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "currencyMatchGroup",
+            "columnName": "currency_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "amount",
+            "columnName": "amount",
+            "affinity": "REAL",
+            "notNull": false
+          },
+          {
+            "fieldPath": "amountMatchGroup",
+            "columnName": "amount_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "accountComment",
+            "columnName": "comment",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "accountCommentMatchGroup",
+            "columnName": "comment_match_group",
+            "affinity": "INTEGER",
+            "notNull": false
+          },
+          {
+            "fieldPath": "negateAmount",
+            "columnName": "negate_amount",
+            "affinity": "INTEGER",
+            "notNull": false
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [
+          {
+            "name": "fk_template_accounts_template",
+            "unique": false,
+            "columnNames": [
+              "template_id"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_template_accounts_template` ON `${TABLE_NAME}` (`template_id`)"
+          },
+          {
+            "name": "fk_template_accounts_currency",
+            "unique": false,
+            "columnNames": [
+              "currency"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_template_accounts_currency` ON `${TABLE_NAME}` (`currency`)"
+          }
+        ],
+        "foreignKeys": [
+          {
+            "table": "templates",
+            "onDelete": "CASCADE",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "template_id"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          },
+          {
+            "table": "currencies",
+            "onDelete": "RESTRICT",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "currency"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          }
+        ]
+      },
+      {
+        "tableName": "currencies",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `position` TEXT NOT NULL, `has_gap` INTEGER NOT NULL)",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "name",
+            "columnName": "name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "position",
+            "columnName": "position",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "hasGap",
+            "columnName": "has_gap",
+            "affinity": "INTEGER",
+            "notNull": true
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [],
+        "foreignKeys": []
+      },
+      {
+        "tableName": "accounts",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `profile_id` INTEGER NOT NULL, `level` INTEGER NOT NULL, `name` TEXT NOT NULL, `name_upper` TEXT NOT NULL, `parent_name` TEXT, `expanded` INTEGER NOT NULL DEFAULT 1, `amounts_expanded` INTEGER NOT NULL DEFAULT 0, `generation` INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(`profile_id`) REFERENCES `profiles`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE )",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "profileId",
+            "columnName": "profile_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "level",
+            "columnName": "level",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "name",
+            "columnName": "name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "nameUpper",
+            "columnName": "name_upper",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "parentName",
+            "columnName": "parent_name",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "expanded",
+            "columnName": "expanded",
+            "affinity": "INTEGER",
+            "notNull": true,
+            "defaultValue": "1"
+          },
+          {
+            "fieldPath": "amountsExpanded",
+            "columnName": "amounts_expanded",
+            "affinity": "INTEGER",
+            "notNull": true,
+            "defaultValue": "0"
+          },
+          {
+            "fieldPath": "generation",
+            "columnName": "generation",
+            "affinity": "INTEGER",
+            "notNull": true,
+            "defaultValue": "0"
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [
+          {
+            "name": "un_account_name",
+            "unique": true,
+            "columnNames": [
+              "profile_id",
+              "name"
+            ],
+            "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_account_name` ON `${TABLE_NAME}` (`profile_id`, `name`)"
+          },
+          {
+            "name": "fk_account_profile",
+            "unique": false,
+            "columnNames": [
+              "profile_id"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_account_profile` ON `${TABLE_NAME}` (`profile_id`)"
+          }
+        ],
+        "foreignKeys": [
+          {
+            "table": "profiles",
+            "onDelete": "CASCADE",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "profile_id"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          }
+        ]
+      },
+      {
+        "tableName": "profiles",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `deprecated_uuid` TEXT, `url` TEXT NOT NULL, `use_authentication` INTEGER NOT NULL, `auth_user` TEXT, `auth_password` TEXT, `order_no` INTEGER NOT NULL, `permit_posting` INTEGER NOT NULL, `theme` INTEGER NOT NULL DEFAULT -1, `preferred_accounts_filter` TEXT, `future_dates` INTEGER NOT NULL, `api_version` INTEGER NOT NULL, `show_commodity_by_default` INTEGER NOT NULL, `default_commodity` TEXT, `show_comments_by_default` INTEGER NOT NULL DEFAULT 1, `detected_version_pre_1_19` INTEGER NOT NULL, `detected_version_major` INTEGER NOT NULL, `detected_version_minor` INTEGER NOT NULL)",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "name",
+            "columnName": "name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "deprecatedUUID",
+            "columnName": "deprecated_uuid",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "url",
+            "columnName": "url",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "useAuthentication",
+            "columnName": "use_authentication",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "authUser",
+            "columnName": "auth_user",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "authPassword",
+            "columnName": "auth_password",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "orderNo",
+            "columnName": "order_no",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "permitPosting",
+            "columnName": "permit_posting",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "theme",
+            "columnName": "theme",
+            "affinity": "INTEGER",
+            "notNull": true,
+            "defaultValue": "-1"
+          },
+          {
+            "fieldPath": "preferredAccountsFilter",
+            "columnName": "preferred_accounts_filter",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "futureDates",
+            "columnName": "future_dates",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "apiVersion",
+            "columnName": "api_version",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "showCommodityByDefault",
+            "columnName": "show_commodity_by_default",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "defaultCommodity",
+            "columnName": "default_commodity",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "showCommentsByDefault",
+            "columnName": "show_comments_by_default",
+            "affinity": "INTEGER",
+            "notNull": true,
+            "defaultValue": "1"
+          },
+          {
+            "fieldPath": "detectedVersionPre_1_19",
+            "columnName": "detected_version_pre_1_19",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "detectedVersionMajor",
+            "columnName": "detected_version_major",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "detectedVersionMinor",
+            "columnName": "detected_version_minor",
+            "affinity": "INTEGER",
+            "notNull": true
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [],
+        "foreignKeys": []
+      },
+      {
+        "tableName": "options",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`profile_id` INTEGER NOT NULL, `name` TEXT NOT NULL, `value` TEXT, PRIMARY KEY(`profile_id`, `name`))",
+        "fields": [
+          {
+            "fieldPath": "profileId",
+            "columnName": "profile_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "name",
+            "columnName": "name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "value",
+            "columnName": "value",
+            "affinity": "TEXT",
+            "notNull": false
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "profile_id",
+            "name"
+          ],
+          "autoGenerate": false
+        },
+        "indices": [],
+        "foreignKeys": []
+      },
+      {
+        "tableName": "account_values",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `account_id` INTEGER NOT NULL, `currency` TEXT NOT NULL DEFAULT '', `value` REAL NOT NULL, `generation` INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(`account_id`) REFERENCES `accounts`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE )",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "accountId",
+            "columnName": "account_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "currency",
+            "columnName": "currency",
+            "affinity": "TEXT",
+            "notNull": true,
+            "defaultValue": "''"
+          },
+          {
+            "fieldPath": "value",
+            "columnName": "value",
+            "affinity": "REAL",
+            "notNull": true
+          },
+          {
+            "fieldPath": "generation",
+            "columnName": "generation",
+            "affinity": "INTEGER",
+            "notNull": true,
+            "defaultValue": "0"
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [
+          {
+            "name": "un_account_values",
+            "unique": true,
+            "columnNames": [
+              "account_id",
+              "currency"
+            ],
+            "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_account_values` ON `${TABLE_NAME}` (`account_id`, `currency`)"
+          },
+          {
+            "name": "fk_account_value_acc",
+            "unique": false,
+            "columnNames": [
+              "account_id"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_account_value_acc` ON `${TABLE_NAME}` (`account_id`)"
+          }
+        ],
+        "foreignKeys": [
+          {
+            "table": "accounts",
+            "onDelete": "CASCADE",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "account_id"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          }
+        ]
+      },
+      {
+        "tableName": "transactions",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `ledger_id` INTEGER NOT NULL, `profile_id` INTEGER NOT NULL, `data_hash` TEXT NOT NULL, `year` INTEGER NOT NULL, `month` INTEGER NOT NULL, `day` INTEGER NOT NULL, `description` TEXT NOT NULL COLLATE NOCASE, `description_uc` TEXT NOT NULL, `comment` TEXT, `generation` INTEGER NOT NULL, FOREIGN KEY(`profile_id`) REFERENCES `profiles`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE )",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "ledgerId",
+            "columnName": "ledger_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "profileId",
+            "columnName": "profile_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "dataHash",
+            "columnName": "data_hash",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "year",
+            "columnName": "year",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "month",
+            "columnName": "month",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "day",
+            "columnName": "day",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "description",
+            "columnName": "description",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "description_uc",
+            "columnName": "description_uc",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "comment",
+            "columnName": "comment",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "generation",
+            "columnName": "generation",
+            "affinity": "INTEGER",
+            "notNull": true
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [
+          {
+            "name": "un_transactions_ledger_id",
+            "unique": true,
+            "columnNames": [
+              "profile_id",
+              "ledger_id"
+            ],
+            "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_transactions_ledger_id` ON `${TABLE_NAME}` (`profile_id`, `ledger_id`)"
+          },
+          {
+            "name": "idx_transaction_description",
+            "unique": false,
+            "columnNames": [
+              "description"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `idx_transaction_description` ON `${TABLE_NAME}` (`description`)"
+          },
+          {
+            "name": "fk_transaction_profile",
+            "unique": false,
+            "columnNames": [
+              "profile_id"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_transaction_profile` ON `${TABLE_NAME}` (`profile_id`)"
+          }
+        ],
+        "foreignKeys": [
+          {
+            "table": "profiles",
+            "onDelete": "CASCADE",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "profile_id"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          }
+        ]
+      },
+      {
+        "tableName": "transaction_accounts",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `transaction_id` INTEGER NOT NULL, `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, FOREIGN KEY(`transaction_id`) REFERENCES `transactions`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE )",
+        "fields": [
+          {
+            "fieldPath": "id",
+            "columnName": "id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "transactionId",
+            "columnName": "transaction_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "orderNo",
+            "columnName": "order_no",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
+          {
+            "fieldPath": "accountName",
+            "columnName": "account_name",
+            "affinity": "TEXT",
+            "notNull": true
+          },
+          {
+            "fieldPath": "currency",
+            "columnName": "currency",
+            "affinity": "TEXT",
+            "notNull": true,
+            "defaultValue": "''"
+          },
+          {
+            "fieldPath": "amount",
+            "columnName": "amount",
+            "affinity": "REAL",
+            "notNull": true
+          },
+          {
+            "fieldPath": "comment",
+            "columnName": "comment",
+            "affinity": "TEXT",
+            "notNull": false
+          },
+          {
+            "fieldPath": "generation",
+            "columnName": "generation",
+            "affinity": "INTEGER",
+            "notNull": true,
+            "defaultValue": "0"
+          }
+        ],
+        "primaryKey": {
+          "columnNames": [
+            "id"
+          ],
+          "autoGenerate": true
+        },
+        "indices": [
+          {
+            "name": "fk_trans_acc_trans",
+            "unique": false,
+            "columnNames": [
+              "transaction_id"
+            ],
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_trans_acc_trans` ON `${TABLE_NAME}` (`transaction_id`)"
+          },
+          {
+            "name": "un_transaction_accounts",
+            "unique": true,
+            "columnNames": [
+              "transaction_id",
+              "order_no"
+            ],
+            "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_transaction_accounts` ON `${TABLE_NAME}` (`transaction_id`, `order_no`)"
+          }
+        ],
+        "foreignKeys": [
+          {
+            "table": "transactions",
+            "onDelete": "CASCADE",
+            "onUpdate": "RESTRICT",
+            "columns": [
+              "transaction_id"
+            ],
+            "referencedColumns": [
+              "id"
+            ]
+          }
+        ]
+      }
+    ],
+    "views": [],
+    "setupQueries": [
+      "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
+      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '0549e893bdbb2c7eb5666c3ee81091d6')"
+    ]
+  }
+}
\ No newline at end of file

diff --git a/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java b/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java
index 12a27e14f36173c644e0534013fa8e302f6e4fd2..d7e8e16f3a55a14ad22b9a8b22bd20530c515c54 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java
+++ b/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java
@@ -79,11 +79,11 @@ public abstract class TransactionDAO extends BaseDAO<Transaction> {
     @Query("SELECT * FROM transactions WHERE id = :transactionId")
     public abstract TransactionWithAccounts getByIdWithAccountsSync(long transactionId);
 
-    @Query("SELECT DISTINCT description, CASE WHEN description LIKE :term||'%%' THEN 1 " +
-           "               WHEN description LIKE '%%:'||:term||'%%' THEN 2 " +
-           "               WHEN description LIKE '%% '||:term||'%%' THEN 3 " +
+    @Query("SELECT DISTINCT description, CASE WHEN description_uc LIKE :term||'%%' THEN 1 " +
+           "               WHEN description_uc LIKE '%%:'||:term||'%%' THEN 2 " +
+           "               WHEN description_uc LIKE '%% '||:term||'%%' THEN 3 " +
            "               ELSE 9 END AS ordering FROM transactions " +
-           "WHERE description LIKE '%%'||:term||'%%' ORDER BY ordering, description, rowid ")
+           "WHERE description_uc LIKE '%%'||:term||'%%' ORDER BY ordering, description_uc, rowid ")
     public abstract List<DescriptionContainer> lookupDescriptionSync(@NonNull String term);
 
     @androidx.room.Transaction

diff --git a/app/src/main/java/net/ktnx/mobileledger/db/DB.java b/app/src/main/java/net/ktnx/mobileledger/db/DB.java
index 3a236944e8969e3c3244acf869faee0035fafbcc..1e7de71da194c0c21ab0039843215e9363716e7d 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java
@@ -57,7 +57,7 @@ import static net.ktnx.mobileledger.utils.Logger.debug;
                       TransactionAccount.class
           })
 abstract public class DB extends RoomDatabase {
-    public static final int REVISION = 60;
+    public static final int REVISION = 61;
     public static final String DB_NAME = "MoLe.db";
     public static final MutableLiveData<Boolean> initComplete = new MutableLiveData<>(false);
     private static DB instance;
@@ -77,7 +77,7 @@ abstract public class DB extends RoomDatabase {
                                     multiVersionMigration(30, 32), multiVersionMigration(32, 34),
                                     multiVersionMigration(34, 40), singleVersionMigration(41),
                                     multiVersionMigration(41, 58), singleVersionMigration(59),
-                                    singleVersionMigration(60)
+                                    singleVersionMigration(60), singleVersionMigration(61)
                     })
                    .addCallback(new Callback() {
                        @Override

diff --git a/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java b/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
index fcbdd82b7fc5d289fa0ff3520c5078ea3d11d246..30395409bdc1d7a270234f46a3430d7bc893be6a 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
@@ -61,10 +61,20 @@ public class Transaction {
     @ColumnInfo(collate = ColumnInfo.NOCASE)
     @NonNull
     private String description;
+    @ColumnInfo(name = "description_uc")
+    @NonNull
+    private String descriptionUpper;
     @ColumnInfo
     private String comment;
     @ColumnInfo
     private long generation = 0;
+    @NonNull
+    public String getDescriptionUpper() {
+        return descriptionUpper;
+    }
+    public void setDescriptionUpper(@NonNull String descriptionUpper) {
+        this.descriptionUpper = descriptionUpper;
+    }
     public long getLedgerId() {
         return ledgerId;
     }
@@ -112,6 +122,7 @@ public class Transaction {
     }
     public void setDescription(String description) {
         this.description = description;
+        setDescriptionUpper(description.toUpperCase());
     }
     public String getComment() {
         return comment;

diff --git a/app/src/main/res/raw/db_61.sql b/app/src/main/res/raw/db_61.sql
new file mode 100644 (file)
index 0000000..4b9dd69
--- /dev/null
+++ b/app/src/main/res/raw/db_61.sql
@@ -0,0 +1,32 @@
+-- Copyright © 2021 Damyan Ivanov.
+-- This file is part of MoLe.
+-- MoLe is free software: you can distribute it and/or modify it
+-- under the term of the GNU General Public License as published by
+-- the Free Software Foundation, either version 3 of the License, or
+-- (at your opinion), any later version.
+--
+-- MoLe is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License terms for details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with MoLe. If not, see <https://www.gnu.org/licenses/>.
+
+-- migrate from revision 60 to revision 61
+
+-- 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;
+
+alter table transactions
+add description_uc text not null default '';
+
+update transactions
+set description_uc=upper(description);
+
+delete from options where name='last_scrape';
\ No newline at end of file