>-----Original Message----- >From: Keith Medcalf [mailto:kmedc...@dessus.com] >Sent: Monday, 15 July, 2019 19:13 >To: 'SQLite mailing list' >Subject: RE: [sqlite] [EXTERNAL] Re: how to delete BLOB object from >the data base > > >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); i = 0; /* Add this cuz you are going to start another batch */ > } >} >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=4Y1 >Z >>hFy9bpH- >>wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=JgVnpDzYgWWXrCfLyoOP5F3fhNn1Gk3rk2 >t >>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
