b

    Bohdan

    7 months ago
    Hi all. I have a question about sqldelight on iOS side. I can add, store and display data on UI on both(Android and iOS) platforms. But it is more convenient (especially if you have more than 20 tables) to open a database file on any sqlite tool - to test queries, compare some data, visually see data etc. It’s quite easy to get and open db file from Android. I aslo would like to do same with file from ios. As i understood on ios it is generated 3 files : appDb-wal appDb-shm appDb I am able to find and get these files on emulator. While adding new rows only “appDb-wal” size changed - so my guess that data are stored in this file )) My issue that i couldn’t open this file - its requires password as db encrypted . How can i open it? or where i could find this password ? What I am doing wrong ? I am mostly working with android and don’t have much experience with iOS. Thanks in advance.
    kpgalligan

    kpgalligan

    7 months ago
    (Context, I maintain the driver under the sqldelight driver). Grab those 3 files. In theory you only need
    appDb
    and
    appDb-wal
    , though. When modifying the db, the *-wal file is updated (“wal” stands for “write ahead log”). Any tool you’re using to read the db file should be using the sqlite process under the hood, and understand how to look at the core db file and the wal.
    The way it works is, the core file
    appDb
    is where the db data lives, but db data mods are written to the
    appDb-wal
    file until a threshold is reached and they are copied into the main file. This happens for reasons related to transactions, etc. https://sqlite.org/wal.html
    I assume you’re not using sqlcipher. If you are, I assume it’s the same situation, but you need to provide the key to decrypt the data in the file.
    Yada yada, open
    appDb
    b

    Bohdan

    7 months ago
    @kpgalligan appDb is empty in my case even without schema of tables (but data present on ui). I will try to open them with some other tool that could look at both files. Thanks.
    kpgalligan

    kpgalligan

    7 months ago
    That’s weird. I guess I haven’t directly opened the sqlite file in a while. With a simulator you should be able to see the files directly on your dev machine and just point the sqlite cli at them. Let me see if I can do that quick…
    b

    Bohdan

    7 months ago
    i was able to open files with some tool - after closing all data was moved into “appDb” - but on simulator it is not working. could be that connection to db was not properly closed ?
    kpgalligan

    kpgalligan

    7 months ago
    They never really are “closed”, generally speaking, unless you do so explicitly.
    b

    Bohdan

    7 months ago
    also data was not transferred automatically from “wal” - So i need to close connection to db after transaction ? that’s also weird and not convenient. Will it be closed if user kill app on real device ? On simulator it didn’t help
    kpgalligan

    kpgalligan

    7 months ago
    So i need to close connection to db after transaction ?
    Definitely not
    Data stays in wal until sqlite decides it gets moved over.
    To be clear, it is not necessarily abnormal to have data in wal. It doesn’t get copied over when the transaction ends.
    From my terminal:
    kgalligan@Kevins-MacBook-Pro databases % ls -lA
    total 136
    -rw-r--r--  1 kgalligan  staff   4096 Feb  3 08:50 KampkitDb
    -rw-r--r--  1 kgalligan  staff  32768 Feb  3 08:50 KampkitDb-shm
    -rw-r--r--  1 kgalligan  staff  28872 Feb  3 08:50 KampkitDb-wal
    kgalligan@Kevins-MacBook-Pro databases % sqlite3 KampkitDb
    SQLite version 3.32.3 2020-06-18 14:16:19
    Enter ".help" for usage hints.
    sqlite> select * from Breed limit 2;
    1|affenpinscher|0
    2|african|0
    sqlite> .exit
    kgalligan@Kevins-MacBook-Pro databases % ls -lA           
    total 96
    -rw-r--r--  1 kgalligan  staff  16384 Feb  3 08:51 KampkitDb
    -rw-r--r--  1 kgalligan  staff  32768 Feb  3 08:51 KampkitDb-shm
    -rw-r--r--  1 kgalligan  staff      0 Feb  3 08:51 KampkitDb-wal
    kgalligan@Kevins-MacBook-Pro databases %
    This was a fresh run of “KaMPKit”. It loads data into a table on first run. You’ll notice the wal file is much larger than the db file when I do ls first (that’s after I killed the app).
    Open it with
    sqlite3 KampkitDb
    . It sees all of the data in the wal as well. It’s not weird that the data is in the wal, but you open the main db file.
    b

    Bohdan

    7 months ago
    i can’t get same behaviour in my project - i will look once more at “KaMPKit”
    kpgalligan

    kpgalligan

    7 months ago
    The sqlite3 tool does trigger the data copy from wal into the main db file at some point, but that had nothing to do with the app itself. It was the cli tool.
    b

    Bohdan

    7 months ago
    Does it depend on amount of data ?
    kpgalligan

    kpgalligan

    7 months ago
    Copying wal? I believe so. However, it should work regardless, so if something is not working in your situation I probably wouldn’t worry about wal too much. Any tool you use to open sqlite should be able to deal with wal. You can tell ios not to use wal, but I really wouldn’t.
    You can pass in a different journal mode. WAL is default. “Delete” is what should be default on android, but you would need to delete your app as I assume trying to open a wal db with a different journal mode would not work. https://github.com/touchlab/SQLiter/blob/main/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/DatabaseConfiguration.kt#L28
    I don’t know anybody that is using different journal modes (Room on Android also defaults to WAL because it’s considered the better option in all cases I can think of)
    b

    Bohdan

    7 months ago
    @kpgalligan I will play around more with it. In any case thanks a lot. In fact, I started with kmm based on “KaMPKit” - thank you for your work! 🙂
    kpgalligan

    kpgalligan

    7 months ago
    I would try the sqlite3 cli tool and verify that that doesn’t work on your db first, then maybe its the tool you’re using.
    Glad KaMP Kit helped! It’s getting a little old so we’re working on an update. Everything takes longer than you plan, though.