Deleting a record from SqLite Browse

Question

Does deleting a record from sq lite browser manually affect the app database ? I have not created any delete database query in my Android studio

I have created a login and registration page in Android studio using sq lite but after I delete the record from the SqLite browser I am still able to login with the same user id and password

I am trying to delete the records manually from the Sqlite browser

I tried deleting the record from sq lite browser and then clicking on write changes but I am still able to login with the same ID and password

Answer 1

Does deleting a record from sq lite browser manually affect the app database ?

NO. SQLite is not a server based database, it is an embedded database. That is every device (including the device running SQLite Browser) will have it's own copy of the database (which is just a file (or files as below)).

If using an SQLite tool to create or edit a database used in an App you have to have a means of transferring the database to/from the App.

The database itself is just a file (or if not properly closed 3 files if using WAL mode (W Ahead Logging)).

Typically, you would create the database in whatever tool, populate the database. Save the database, ensuring that it is a single file

• you can ignore a file that is the same as the database but suffixed with -journal.
  • this indicates that the database is in JOURNAL mode, in which case, changes made to the database are written to the database file and a record of those changes are made in the journal, allowing for a rollback.
  • if there is a file, the same name as the database file but suffixed with -wal and the -wal is not empty then the -wal file is part of the database.
    • you should revisit the database and save it again and close the tool (some tools require this to actually fully close the connection)
    • alternately you can consider the database all 3 files (-shm isn't as important, but it is best to also treat this as pert of the database).

To introduce the pre-populated database (i.e. the database file), typically this would be copied into the assets as part of the project.

You then need a means of copying the asset(s) from the asset to the where the database is expected to be (typically in the data.data/<the_package_name>/databases folder/directory). Typically this would be when the database does not exist.

If you are changing the database, as opposed to creating it, in a tool, then you need a means of indicating and detecting those changes so that the logic of the App can replace the database from the asset. Typically you would utilise the database version (SQLite's USER_VERSION) along with the expected version that is compiled as part of the App.

Detection of such a change, would be to open the database and extract the USER_VERSION. The you would have logic that:-

• in the case that there is no database file; copy the database file from the asset to the required location (aka a new Install of the App)
• in the case that the database exists but it's version is different (typically lower) than the expected compiled version, replace the database with the database from the assets (note the database file in the asset should have it's user version set)
• in the case that the database exists and it's version is the same as the expected compiled version, then do nothing as the database is the latest version.

You may wish to consider the following which Can't copy pre-created db from assets