adding an sqlite data source is showing old data

[simonmichael]

I might be confused, but I seem to be seeing a strange bug in Ultorg 1.26 [on arm mac]. I have two sqlite dbs, containing records for 2022 and 2023 respectively. I confirmed this with sqlite, eg:

$ sqlite3 2022.db 'select date1 from postings' | grep 2023
$ sqlite3 2023.db 'select date1 from postings' | grep 2022

When I add 2022.db to Ultorg, I see only 2022 records as expected. But when I add 2023.db to Ultorg, Ultorg shows it containing all of the 2022 & 2023 records, from both 2022.db and 2023.db. This also happens when 2022.db has not been added as a data source, and even if I delete the 2022.db file. It's as if 2023.db might have at one time included all records and Ultorg is seeing only an old cached version of it.

[simonmichael]

And renaming the file, eg to 2023b.db, fixes the problem.

[eirikbakke]

Hmmm, curious. Is this a problem you see when just opening a table of data, with no joins or anything?

Did there ever exist a SQLite file containing both years in one table?

[simonmichael]

Yes, just opening the table and viewing it. Yes it's possible the file might once have contained both years.

[eirikbakke]

Odd. Ultorg should not be doing any caching in this case.

Did you ever use the Custom Join feature to try to do joins across two different data sources?

There's a caching mechanism in Ultorg that is used when doing joins between different data sources, or when working with CSV/Excel/Access/GSheets files. In these cases data will be stored in a "Generated Extracts" folder that will appear in the Folders sidebar; you can inspect or delete this folder at any time. But this system shouldn't be active in this case, especially when just looking at a plain SQLite table.

You can also push the "Refresh" button in the toolbar (keyboard shortcut F5 or Ctrl+R) to try to refresh the result of an open perspective.

[simonmichael]

Did you ever use the Custom Join feature to try to do joins across two different data sources?

I don't believe so, as I didn't know that was possible. Joins across tables within one data source, I have tried.

I deleted the Generated Extracts folder in the UI yesterday (as I didn't know what it was and it was annoying me). I see it has returned with a .csv item, but it doesn't seem to be a factor when working with sqlite dbs.

I copied 2023b.db to 2023.db again, reopened it, and clicked the refresh button - no change, when opened with that filename it still shows 2022+2023 data.

[simonmichael]

But, after emptying the recycle bin within Ultorg's UI, opening the file correctly showed the latest data (2023 records only). :)

[eirikbakke]

I'm not sure where the stale data could come from. As long as Generated Extracts and Recycle Bin is empty, there is nowhere Ultorg could be storing it.

I'd rather try to verify that Ultorg is querying the right SQLite file. If you hover over the database icon, it should show a tooltip with the path to the file being used. If you click Internals->Services, you will also see a list of the actual database connections currently being used, also with the file path shown for SQLite databases.

[simonmichael]

The problem was fixed by emptying Ultorg's recycle bin. I believe it is prioritising old data from there instead of what's on disk.