I searched but found no answer… Is it possible (and how) to create SQLite in memory only? In sqlite manual it says tu use sqlite3_open(":memory:", &db) but do not know how to implement that in Kodular and sqlite extension.
I have app where I want to temporary store data as fast as possible and saving to SD just reduce that speed and reduce also an SD card life. I know I could revrite app and work with a list but that would mean rewrite most of the app. If I can just switch to memory it would be the best solution.
that feature is neither offered by the sqlite component nor by any sqlite extension…
did you already try the sqlite extension? did you get permormance issues? how did you setup your table? do you use indices? how many records are we talking about?
Yes I use SQlite extension bought from you :). My app reads from BT serial port, then first writes that in db up to 30 records per second. I do have indice on the big integer column used to store time stamp in unix format which is also the main index. Then when web is present (not always) upload that data to web server from oldest to newest and if succes I do an update in status column at just uploaded unix time. So I do have 3 accesses that happen for every record (insert, read and update). I could rewrite all to upload in batches instead each record which would have impact on overall performance, or simply change everything to work with a lists (or disctionaries) to keep all in memory and avoid constant file access. But my original thought was to simply switch to in memory db… If that is not possible than I need to rewrite the app… which at the end is not such a problem.
Thanks for your info about sqlite in memory.
There are How many records in your table ?
You have 1 database on the web and another on your smartphone, right?
Are the updates (Insert into) of many records? Or are only fields changed?
Yes, local sqlite and remote mysql accesed via back end. I log my car data. Here is the final result on the web:
Table on phone is simple as server do the data parsing:
CREATE TABLE log (url text NOT NULL,elmTS bigint NOT NULL,file int default 0,web int default 0)
then there is index:
CREATE UNIQUE INDEX IF NOT EXISTS
I can get up to 30 data per s from OBD to insert with:
insert into log (url,elmTS) values (?,?)
then when It is send to webserver I do update web @ specific elmTS if there is also log to file enabled and delete if it only web upload or it was saved to file already.
If I turn of web upload then I can log up to 17 records per second. With live web upload up to 13. If I also store CSV file then it logical drop a little more (but I actualy never use that option) . If only file log is enabled then record is deleted from db if also web is selected and not yet sent then file is updated.
Usualy only either web or file is selected so db should not get to big. But for example when I went to holidays I turned off web and file, and only store to db. over milion records were in before I upload them to web.
Normaly It will always upload in real time or at the end of jurney on wifi. so I could move db in memory to avoid storing to be the slowest part of the system.
Best regards, Alex