Hi everyone,
I want to ask if we can delete the current local database in the mobile after the user logout?The context is we would like to re-initial the database creation whenever a new user login. We faced a bit of an issue while using the action DeleteAll<Entity> to delete all the records of the entities, since it deleted everything but kept the auto-id persisted. If a new user log in, some data will be saved to db and will keep increasing from the last id deleted (expected that it should be from 0).
Right now, the workaround is to ask the user to clear the storage data of the app (App's setting), which is inconvenient for the users. I want to find some way to clear or delete the entire database (so that new users come in just like a freshly installed app)
Any idea/comment will be appreciated.
Thanks all!
P/S: Our's platform is on OS 11
To drop the database you can use the sqllistePlugin that is by default available in a OutSystems mobile application. To use this method you need the name of the local database, you can get the name of the database by getting it from the System entities.
The database name consist out of the value of the ServiceCenter siteproperty ServiceCenterUID and the current Application key. To get those values we create a serveraction with 2 aggregates that gets the values from the system entities. (see this post)
(local storage) Databasename =
GetSitePropertyShareds.List.Current.Site_Property_Shared.Property_Value + "_" +
GetApplicationsByEntryESpaceId.List.Current.Application.Key
After you get the name of the database, you can drop and recreate the local database by using the following Javascript:
window.sqlitePlugin.deleteDatabase(
{ name: $parameters.DatabaseName
, location: 'default'}
);
window.sqlitePlugin.openDatabase(
{
name: $parameters.DatabaseName
this will delete the local Database and recreate an empty database with the same name (so that new users come in just like a freshly installed app), so also the system entities ApplicationVersion and LocalUser are cleaned, you need to restart the application to recreate the entities and repopulate the system entities.
To restart the application and get the ApplicationVersion use the JavaScript:
window.location.reload();
Attached a module with the described client and server actions (I used this methods to recreate the localstorage database after removing the Ciphered Localstorage plugin, will write a article about the whole procedure later).
After restarting the login screen will be prompted, because the user will be logged out (the LocalUser entity holds no record)
Happy lowcoding!
Matthias
@Matthias Preuter : Thanks for sharing this information.
Hi, thanks for sharing the details of the solution. I really appreciate that!
@Loc Nguyen Bao Vinh hallo ,
https://www.outsystems.com/forums/discussion/70634/how-to-reset-table-id/ as per this post and as i try also can not use TRUNCATE TABLE {Tasks} for local storage
but as work around can you can try the next
1. make pk is auto number false
2. create client action get max id and add 1 ( MaxID + 1 )
3. on create check in case create new 1 will get the max from your client action
Thanks @Kerollos Adel for the workaround, unfortunately, we cannot adjust our current database schema or setting because of the high number of local entities and the logic/data relationship has been built upfront. Really appreciate your comment!
Hi,
if you remove autoincreament, and add manual increament may not good idea, if you have bulk request, it may try to insert with same ids, and ofcourse will have error, i see this possibility.
Thanks,
Balasubramanian
Tootalty agree work around not perfect all times
Hi @Loc Nguyen Bao Vinh,
Can you tell me why you need to reset the primary key of the localstorage entity? so based on this we can help you different suggestion.
The problem I would like to solve here is that the user can log in with multiple of accounts, and we have the synchronization background to download the data locally. The id is built with the auto number, If we only call the DeleteAll<Entity> action inside of each entity, it will only delete the record but not reset the auto ID incrementally. The next time user login with another account, the synced id will be increased wrongly.
To not affect the logic, and data upload to the server, we want to clear (reset) the db so it will always be a fresh new app and users can load their data.
Because of limited time and effort, we cannot adjust the local schema and logic for the sync.
Regards,
Loc
IndexedDB is used in the PWA, and SQLite is used in the native app to store local data. In IndexedDB, you can't reset the ID to 0 without recreating the table. You can clear the whole database with JavaScript, but that's not recommended. For APKs, it’s not possible to access SQLite on the device and manually perform operations, based on my findings.
My suggestion is to either accept the limitation as is or consider implementing one of the suggestions provided by other members.
Hi Siya,
Yes, you're right. We're using native, which is SQLite db as a local db.
We know the limitation, but we just want to find a way or some forge component to help us clean up the app data instead of asking the user to go to the app setting and clear it by themself.
Another way is to reset the auto-id of the entity, but I cannot find any forge that supports us in writing a kind of reset query like this.
It is possible to execute the query using JavaScript through the SQLite Cordova Plugin (bundled in the APK). However, we need to know which SQLite file to open. Unfortunately, there is no way to determine the SQLite file name created by OutSystems behind the scenes. Additionally, there is no method to list or enumerate the .db files.
Why is it relevant that the Id should start numbering from 0 again. The Identifier should be used as a meaningless unique identifier. If you need to have a counter attribute that always starts at 1 when the entity is empty, it is better to add it as an additional attribute to the local entity, and have your code responsible for the sequential numbering.
Daniel