OK, I wasn't clear.
I'm limited in space so when the DB is full (when sqlite3_exec() returns
SQLITE_FULL when I try to insert a new row), I remove the oldest row and
retry to insert the new one.
The data is always the same. That's why removing one row should be enough
to insert a new one.
My problem is that some times I need to remove many rows to add one new one.
This is basically my code:
main()
{
sqlite3* db;
int rc;
char *err_msg = 0;
int counter;
bool full = false;
int id;
/* --- Create DB --- */
rc = sqlite3_open("db_file.db", &db);
printf("1. rc = %d\n", rc);
rc = sqlite3_exec(db,
"CREATE TABLE IF NOT EXISTS data_table"
"(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2
INTEGER, col3 INTEGER)",
0, 0, &err_msg);
printf("2. rc = %d\n", rc);
/* --- Limit database size to 50 K --- */
rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg);
printf("3. rc = %d\n", rc);
rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg);
printf("4. rc = %d\n", rc);
rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file
printf("5. rc = %d\n", rc);
/* --- Fill DB --- */
for (int i = 0 ; i < 5000 ; i++) {
counter = 0;
do {
rc = sqlite3_exec(db,
"INSERT INTO data_table"
"(col1, col2, col3) VALUES(1, 2, 3)",
0, 0, &err_msg);
if (rc == SQLITE_FULL) {
if (!full) {
printf("%d - DB full\n", id);
full = true;
}
counter++;
// delete oldest row
int stat = sqlite3_exec(db,
"DELETE FROM data_table WHERE id IN "
"(SELECT id FROM data_table ORDER BY id LIMIT
1)",
0, 0, &err_msg);
if (stat != SQLITE_OK) {
printf("Delete error %d\n", stat);
}
} else if (rc == SQLITE_OK) {
id = sqlite3_last_insert_rowid(db);
} else /*if (rc != SQLITE_OK)*/ {
printf("Insert error %d\n", rc);
}
} while (rc == SQLITE_FULL);
if (counter > 2) {
printf("%d - %d rows was removed\n", id, counter);
}
}
printf("close -> %d\n", sqlite3_close(db));
}
Following the output:
1. rc = 0
2. rc = 0
3. rc = 0
4. rc = 0
5. rc = 0
3959 - DB full
3960 - 109 rows was removed
4044 - 92 rows was removed
4128 - 86 rows was removed
4212 - 85 rows was removed
4296 - 85 rows was removed
4380 - 84 rows was removed
4464 - 84 rows was removed
4548 - 84 rows was removed
4632 - 84 rows was removed
4716 - 84 rows was removed
4800 - 84 rows was removed
4884 - 84 rows was removed
4968 - 84 rows was removed
close -> 0
Thanks
On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel <[email protected]>
wrote:
>
> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
