Read only Sqlite DB in Resource Folder?

  1. 2 months ago

    Karen A

    Nov 10 Pre-Release Testers

    I know it is a no-non (at least on a Mac) to use an SQLite DB file you need to write that is in the Resource folder...

    But is there a reason not to leave a small read only SQLite database in the Resource folder and use it from there on either a Mac or Windows?

    Thanks,
    Karen

    Dave is correct, connecting to SQLite with Xojo creates the extra files whether you like it or not. I have had an app rejected from the MAS because I forgot about this. It's not hard to work around. Copy the sqlite database out of the app to either app support or temporary to open it.

  2. Javier M

    Nov 10 Pre-Release Testers, Xojo Pro AprendeXojo - Europe, Spain

    You can do that without problem, as long you don’t write (on signed apps)

    Javier

  3. Dave S

    Nov 10 San Diego, California USA

    I thought SQLite created a journal file, regardless.... since it doesn't "know" that you are not going to write to it
    and that that journal file was created (ie. IT is written) at the same location as the database

  4. Karen A

    Nov 10 Pre-Release Testers

    Thanks Javier.

    I thought that was the case, but i was not sure

    - karen

  5. Javier M

    Nov 10 Pre-Release Testers, Xojo Pro AprendeXojo - Europe, Spain

    SQLite only creates additional files when WAL is activated, but I think this is not the case. Is the same when SQLite is used on iOS sandboxed apps: no WAL (multiuser) access possible.

    Javier

  6. Tim P

    Nov 10 Pre-Release Testers Answer feedback://46303

    Dave is correct, connecting to SQLite with Xojo creates the extra files whether you like it or not. I have had an app rejected from the MAS because I forgot about this. It's not hard to work around. Copy the sqlite database out of the app to either app support or temporary to open it.

  7. Karen A

    Nov 10 Pre-Release Testers
    Edited 2 months ago

    @Tim P Dave is correct, connecting to SQLite with Xojo creates the extra files whether you like it or not. I have had an app rejected from the MAS because I forgot about this. It's not hard to work around. Copy the sqlite database out of the app to either app support or temporary to open it.

    I will test it then ...

    From what I've read of the Sqlite docs, I was under the impression that it only created the extra file if you used WAL... but of course Xojo inc could change that in its plugin which is why I asked...

    If the plugin's behavior in that respect is different from that of SQLite itself, that should be documented in the language reference,

    In any case if it's not needed by SQLite, Xojo's plugin should avoid doing it if possible.

    BTW for DB's I'm going to write to (most cases), I do as you suggest... But I did not see the point of doing that for a read-only DB, as it is conceptually no different form any other resource

    - Karen

  8. Emile S

    Nov 10 Europe (France, Strasbourg)

    @Tim P connecting to SQLite with Xojo creates the extra files

    Is this why there is a crash when the db file is in a Read-Only folder ?

  9. Tim P

    Nov 10 Pre-Release Testers feedback://46303

    Sounds reasonable to me.

  10. Christian S

    Nov 10 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    You can open a SQLite database read only and than there would be no journal file as far as I know.

    At least for MBS SQL Plug-in we have a flag if I remember correctly.

  11. Tim P

    Nov 10 Pre-Release Testers feedback://46303
    Edited 2 months ago

    @ChristianSchmitz You can open a SQLite database read only and than there would be no journal file as far as I know.

    There is no way with pure Xojo though, and that's where the issue comes from.
    Good to know MBS can do it though! :D

  12. Christian S

    Nov 10 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    found it:

    // for SQLite, set flag to open database file read only:
    
    dim c as SQLDatabaseMBS // your database connection
    c.Option("SQLiteVFSFlags") = "1"

    Works for SQLite with SQLDatabaseMBS or SQLConnectionMBS class.

  13. Karen A

    Nov 10 Pre-Release Testers

    @Tim P There is no way with pure Xojo though, and that's where the issue comes from.

    As that flag is built into Sqlite there is no reason for Xojo inc not to support it... and the feedback request goes back to July 2012!

    Feedback Case #21814

    Little things like that make do make a difference in the Xojo experience... but even Apple no longer pays attention to the little things that make a better experience!

    - karen

  14. Dave S

    Nov 10 San Diego, California USA

    @Karen A but even Apple no longer pays attention to the little things that make a better experience!

    All it takes to create a whole new Operating System is to add more Emoji.... As long it it is "pretty".... no one cares.

or Sign Up to reply!