Use one connection. Use one thread. As follows:
sqlite *db;
sqlite_stmt *beginstatement;
sqlite_stmt *commitstatement;
sqlite_stmt *insertstatement;
sqlite_stmt *deletestatement;
int i = 0;
sqlite_open_v2('database.db', &db, ...);
sqlite_prepare_v2(db, "begin immediate", &beginstatement, ...);
sqlite_prepare_v2(db, "insert into blah (_rowid_, data) values (?, ?);",
&insertstatement, ...);
sqlite_prepare_v2(db, "delete from blah where _rowid_ < ?;", &deletestatement
... );
sqlite_prepare_v2(db, "commit;", &commitstatement, ...);
while 1:
char *data = WaitForDataToArrive()
if (!data) break;
int64 ts = GetTheTimeInMSsinceTheUnixEpochAsInt64()
if (!i) {
sqlite_step(beginstatement);
sqlite_reset(beginstatement);
}
sqlite_bind_int64(deletestatement, 0, ts -
(numberofmillisecondsofdatatoretainindatabase));
sqlite_step(deletestatement);
sqlite_reset(deletestatement);
sqlite_bind_int64(insertstatement, 0, ts);
sqlite_bind_blob(insertstatement, 1, data);
sqlite_step(insertstatement);
sqlite_reset(insertstatement);
i++;
if (i>NumberOfRecordsInEachBatch) {
sqlite_step(commitstatement);
sqlite_reset(commitstatement);
}
}
if (i) {
sqlite_step(commitstatement);
sqlite_reset(commitstatement);
}
sqlite_finalize(beginstatement);
sqlite_finalize(commitstatement);
sqlite_finalize(insertstatement);
sqlite_finalize(deletestatement);
sqlite_close_v2(db);
Use a separate connection for the data reading on its own thread/process. Make
sure the database is in WAL mode. Don't bother with auto vacuum (not needed).
Space freed will be re-used automatically. Assuming that the data rate is
constant you will eventually end up with a stable database size. Reading will
not interfere with writing. Make sure you do your reads in a deferred
transaction if they require a REPEATABLE READ across multiple statements. Be
aware that your rowid (the timestamp) will overflow in a couple of hundred
million years, so you will have to devise a different timestamp calculation
method before then. You are probably fine with a NumberOfRecordsInEachBatch
being 1, but you might want to set it larger, particularly if your persistent
storage is old and slow.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Zhu, Liang
>[AUTOSOL/ASSY/US]
>Sent: Monday, 15 July, 2019 15:11
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] Re: how to delete BLOB object from
>the data base
>
>We do the increment of 1000, when the record reaches number which
>dividable by 1000, we delete the record.
>
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Robert Hairgrove
>Sent: Monday, July 15, 2019 4:00 PM
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [EXTERNAL] Re: [sqlite] how to delete BLOB object from the
>data base
>
>On 15.07.19 21:38, Zhu, Liang [AUTOSOL/ASSY/US] wrote:
>> ... I am inserting to the BLOB data into the table at every 250ms,
>I
>> delete the oldest row at every 600ms, also I am reading the data
>from
>> the database at every 10ms...
>
>How do you determine the "oldest" row? I believe the timestamps
>generated by SQLite are only accurate to the nearest second.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-
>3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-
>2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1Z
>hFy9bpH-
>wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=JgVnpDzYgWWXrCfLyoOP5F3fhNn1Gk3rk2t
>6Ak5G8Tw&s=9h4tFOoFd335e5M_jT34B0HuiTqMKR2HMqh_E0wcp1Y&e=
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
