Hello !
I'm sending here a C program that demonstrates the problem of sqlite3 in wall
mode,
in this simple example of a simulated session management for a web server
when executing
the wall log file will grow and grow till eat all our disk.
?
There is something wrong with the program or with sqlite3 ?
Cheers !
?
#include <stdio.h>
#include <stdlib.h>
#include <signal.h>
#include <unistd.h>
#include "sqlite3.h"
static const char create_sql[] = "create table if not exists sessions(id text
primary key, data text, ip_address, cdate datetime default CURRENT_TIMESTAMP,
mdate datetime);";
static const char insert_sql[] = "insert or ignore into sessions(id, data,
ip_address, mdate) values(?,?,?, CURRENT_TIMESTAMP)";
static const char update_sql[] = "update sessions set data=?,
mdate=CURRENT_TIMESTAMP where id=?";
static const char select_sql[] = "select data from sessions where id=? and
ip_address=?";
static volatile int keepRunning = 1;
void intHandler(int dummy) {
??? keepRunning = 0;
}
int main()
{
??? signal(SIGINT, intHandler);
??? printf("Starting our busy session management !\n");
??? sqlite3 *db;
??? int rc = sqlite3_open("sessions.db", &db);
??? if(rc == SQLITE_OK)
??? {
??????? sqlite3_stmt *stmt_insert, *stmt_update, *stmt_select;
??????? //rc = sqlite3_exec(db, "PRAGMA synchronous = 0;", NULL, NULL,
NULL);
??????? rc = sqlite3_exec(db, "PRAGMA journal_mode = WAL;", NULL,
NULL, NULL);
??????? //create sessions table
??????? rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
??????? //create prepared statements
??????? rc = sqlite3_prepare_v2(db, insert_sql, sizeof(insert_sql)-1,
&stmt_insert, NULL);
??????? rc = sqlite3_prepare_v2(db, update_sql, sizeof(update_sql)-1,
&stmt_update, NULL);
??????? rc = sqlite3_prepare_v2(db, select_sql, sizeof(select_sql)-1,
&stmt_select, NULL);
??????? const char session_id[] = "ABC123456789";
??????? const char ip_address[] = "127.0.0.1";
??????? const char data[] = "a kind of initial data";
??????? //insert initial data
??????? rc = sqlite3_bind_text(stmt_insert, 1, session_id,
sizeof(session_id)-1, NULL);
??????? rc = sqlite3_bind_text(stmt_insert, 2, data, sizeof(data)-1,
NULL);
??????? rc = sqlite3_bind_text(stmt_insert, 3, ip_address,
sizeof(ip_address)-1, NULL);
??????? rc = sqlite3_step(stmt_insert);
??????? rc = sqlite3_reset(stmt_insert);
??????? int count = 0;
??????? //start our busy long work
??????? while(keepRunning)
??????? {
??????????? printf("Managing session %d\n", ++count);
??????????? sleep(1);
??????????? //one client arrived, let's get it's saved data
??????????? rc = sqlite3_bind_text(stmt_select, 1, session_id,
sizeof(session_id)-1, NULL);
??????????? rc = sqlite3_bind_text(stmt_select, 2, ip_address,
sizeof(ip_address)-1, NULL);
??????????? rc = sqlite3_step(stmt_select);
??????????? const unsigned char *saved_data =
sqlite3_column_text(stmt_select, 0);
??????????? rc = sqlite3_reset(stmt_select);
??????????? sleep(1);
??????????? //ok we served our client, let's save it's data
??????????? rc = sqlite3_bind_text(stmt_update, 1, saved_data ?
"ok we have previous data" : data, -1, NULL);
??????????? rc = sqlite3_bind_text(stmt_update, 2, session_id,
sizeof(session_id)-1, NULL);
??????????? rc = sqlite3_step(stmt_update);
??????????? rc = sqlite3_reset(stmt_update);
??????? }
??????? sqlite3_finalize(stmt_insert);
??????? sqlite3_finalize(stmt_update);
??????? sqlite3_finalize(stmt_select);
??????? sqlite3_close(db);
??? }
??? return 0;
}
?
