[sqlite] FW: mutex assert_fail in sqlite3BtreeHoldsMutex in a heavily load DB access in 3.7.5 but not in 3.6.22

[ChingChang Hsiao]

There is a problem for reply in the web. So I resend again. Please neglect the 
previous one. It's 3.7.5. Journal mode is delete not WAL.

A script was running for a heavy load DB access. As you can see the sql 
statement "select * from service_table where service_no = '13';" in the log.  
There is no rows for service_no 13 or 7  in service_table when it is accessed. 
The logic is check the entry is there or not, if not then insert it. Could 
service_table be locked by previously writing(service_no 12 or 6)?

ChingChang

Version is 3.7.5
journal mode = DELETE
SELECT sqlite_source_id(); 2011-01-28 17:03:50 
ed759d5a9edb3bba5f48f243df47be29e3fe8cd7

OTHER_FLAGS=-DSQLITE_THREADSAFE=2  -DSQLITE_THREAD_OVERRIDE_LOCK=-1 
-DSQLITE_DEBUG=1
CFLAGS += -O0 -w $(OTHER_FLAGS)
in Makefile



SQLITE_PRIVATE int sqlite3BtreeHoldsMutex(Btree *p){
  assert( p->sharable==0 || p->locked==0 || p->wantToLock>0 );
  assert( p->sharable==0 || p->locked==0 || p->db==p->pBt->db );
  assert( p->sharable==0 || p->locked==0 || sqlite3_mutex_held(p->pBt->mutex) );
  assert( p->sharable==0 || p->locked==0 || sqlite3_mutex_held(p->db->mutex) );

  return (p->sharable==0 || p->locked);
}


(gdb) bt
#0  0x369d5b04 in raise () from /lib/libc.so.6
#1  0x369d72f4 in abort () from /lib/libc.so.6
#2  0x369cd2a4 in __assert_fail () from /lib/libc.so.6
#3  0x364ceb24 in sqlite3BtreeHoldsMutex (p=0x117b94f0) at sqlite3.c:45280
#4  0x36544a14 in sqlite3Prepare (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
nBytes=-1, saveSqlFlag=0, pReprepare=0x0, ppStmt=0x7e64a374,
pzTail=0x7e64a378) at sqlite3.c:87749
#5  0x365450d8 in sqlite3LockAndPrepare (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
nBytes=-1, saveSqlFlag=0, pOld=0x0, ppStmt=0x7e64a374, pzTail=0x7e64a378)
at sqlite3.c:87878
#6  0x36545404 in sqlite3_prepare (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
nBytes=-1, ppStmt=0x7e64a374, pzTail=0x7e64a378) at sqlite3.c:87941
#7  0x3653e87c in sqlite3_exec (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
xCallback=0x36551600 , pArg=0x7e64a3e8,
pzErrMsg=0x7e64a4f4) at sqlite3.c:84507
#8  0x36551ab4 in sqlite3_get_table (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
pazResult=0x7e64a5f8, pnRow=0x7e64a5fc, pnColumn=0x7e64a600,
pzErrMsg=0x7e64a4f4) at sqlite3.c:92613
#9  0x36377594 in SqlQuery::exec (this=0x36af9f44,
sql_stmt=0x7e64a80c "select * from service_table where service_no = '13';", 
context=, linenum=292215768, warnings=true)
at SqlQuery.cpp:229
#10 0x36377ec0 in SqlQuery::exec (this=0x0,
sql_stmt=0x1ede , warnings=32)
at SqlQuery.cpp:255
#11 0x1004869c in IsgServiceType (theTaskDataPtr=,
theArgArrayPtr=,
theVariableNamePtr=0x101ff180 "ip-forward",
theOutputStringPtr=0x1031b440 "",
theUserCookiePtrPtr=) at IsgService.cc:711
#12 0x101c1bcc in RcParseLine (theConnectionPtr=0x11681900)
at ./RomCLI/Sources/RcParse.c:606
#13 0x101be784 in RcFiniteStateMachine (theConnectionPtr=0x11681900)
at ./RomCLI/Sources/RcCmdLin.c:913
#14 0x101c5c44 in HandleConnectionTask (theConnectionPtr=)
at ./Engine/Sources/AsMain.c:1861
#15 AllegroMainTask (theTaskDataPtr=0x116805d0,
theHttpTasks=, theTcpTasks=0x7e64eb64)
at ./Engine/Sources/AsMain.c:1483
#16 0x101851e0 in instantiate_task ()
at ./Interfaces/LinuxUnix/SingleTask/RpTask.c:253
#17 c_main (argc=, argv=)
at ./Interfaces/LinuxUnix/SingleTask/RpTask.c:105
#18 0x10184070 in app_main (argc=1, argv=0x7e653ca4)
at ./Interfaces/LinuxUnix/SingleTask/RpStart.cpp:297
#19 0x1000d2d4 in main (argc=1, argv=0x7e653ca4)
at /mnt/local/cch/bugfix_test_11_01_02232011/isg6000/MaestrOS/mgmt-crd/linux


Program terminated with signal 6, Aborted.
#0  0x31695b04 in raise () from /lib/libc.so.6
(gdb) bt
#0  0x31695b04 in raise () from /lib/libc.so.6
#1  0x316972f4 in abort () from /lib/libc.so.6
#2  0x3168d2a4 in __assert_fail () from /lib/libc.so.6
#3  0x3116e404 in sqlite3BtreeHoldsMutex (p=0x117af8d0) at sqlite3.c:45280
#4  0x311ff2f0 in sqlite3Prepare (db=0x3123b04c,
zSql=0x797c298c "select * from service_table where service_no = '7';", 
nBytes=-1, saveSqlFlag=0,
pReprepare=, ppStmt=0x797c24e4, pzTail=0x797c24e8) at 
sqlite3.c:87749
#5  0x311ffd44 in sqlite3LockAndPrepare (db=0x11d354a8,
zSql=0x797c298c "select * from service_table where service_no = '7';", 
nBytes=-1, saveSqlFlag=0,
pOld=0x0, ppStmt=0x797c24e4, pzTail=0x797c24e8) at sqlite3.c:87878
#6  0x31200440 in sqlite3_prepare (db=0x0, zSql=0xefc , nBytes=6,
ppStmt=0xffc0, pzTail=0xf104) at sqlite3.c:87941
#7  0x31202264 in sqlite3_exec (db=0x11d354a8,
zSql=0x797c298c "select * from service_table where service_no = '7';",
xCallback=0x31189f2c , 

Re: [sqlite] mutex assert_fail in sqlite3BtreeHoldsMutex in a heavily load DB access in 3.5.7 but not in 3.6.22

[Richard Hipp]

On Tue, May 10, 2011 at 10:15 PM, ChingChang Hsiao <
chingchang.hs...@overturenetworks.com> wrote:

> A script was running for a heavy load DB access. As you can see the sql
> statement "select * from service_table where service_no = '13';" in the log.
>  There is no rows for service_no 13 or 7  in service_table when it is
> accessed. The logic is check the entry is there or not, if not then insert
> it. Could service_table be locked by previously writing(service_no 12 or 6)?
>
> ChingChang
> 
> Version is 3.5.7
>

I think you probably mean 3.7.5.



> journal mode = DELETE
> SELECT sqlite_source_id(); 2011-01-28 17:03:50
> ed759d5a9edb3bba5f48f243df47be29e3fe8cd7
>
> OTHER_FLAGS=-DSQLITE_THREADSAFE=2  -DSQLITE_THREAD_OVERRIDE_LOCK=-1
> -DSQLITE_DEBUG=1
>

Please recompile with SQLITE_THREADSAFE=1 and let us know if that help.
Thanks.


> CFLAGS += -O0 -w $(OTHER_FLAGS)
> in Makefile
>
>
>
> SQLITE_PRIVATE int sqlite3BtreeHoldsMutex(Btree *p){
>  assert( p->sharable==0 || p->locked==0 || p->wantToLock>0 );
>  assert( p->sharable==0 || p->locked==0 || p->db==p->pBt->db );
>  assert( p->sharable==0 || p->locked==0 ||
> sqlite3_mutex_held(p->pBt->mutex) );
>  assert( p->sharable==0 || p->locked==0 || sqlite3_mutex_held(p->db->mutex)
> );
>
>  return (p->sharable==0 || p->locked);
> }
>
>
> (gdb) bt
> #0  0x369d5b04 in raise () from /lib/libc.so.6
> #1  0x369d72f4 in abort () from /lib/libc.so.6
> #2  0x369cd2a4 in __assert_fail () from /lib/libc.so.6
> #3  0x364ceb24 in sqlite3BtreeHoldsMutex (p=0x117b94f0) at sqlite3.c:45280
> #4  0x36544a14 in sqlite3Prepare (db=0x11aabd58,
>zSql=0x7e64a80c "select * from service_table where service_no = '13';",
>nBytes=-1, saveSqlFlag=0, pReprepare=0x0, ppStmt=0x7e64a374,
>pzTail=0x7e64a378) at sqlite3.c:87749
> #5  0x365450d8 in sqlite3LockAndPrepare (db=0x11aabd58,
>zSql=0x7e64a80c "select * from service_table where service_no = '13';",
>nBytes=-1, saveSqlFlag=0, pOld=0x0, ppStmt=0x7e64a374,
> pzTail=0x7e64a378)
>at sqlite3.c:87878
> #6  0x36545404 in sqlite3_prepare (db=0x11aabd58,
>zSql=0x7e64a80c "select * from service_table where service_no = '13';",
>nBytes=-1, ppStmt=0x7e64a374, pzTail=0x7e64a378) at sqlite3.c:87941
> #7  0x3653e87c in sqlite3_exec (db=0x11aabd58,
>zSql=0x7e64a80c "select * from service_table where service_no = '13';",
>xCallback=0x36551600 , pArg=0x7e64a3e8,
>pzErrMsg=0x7e64a4f4) at sqlite3.c:84507
> #8  0x36551ab4 in sqlite3_get_table (db=0x11aabd58,
>zSql=0x7e64a80c "select * from service_table where service_no = '13';",
>pazResult=0x7e64a5f8, pnRow=0x7e64a5fc, pnColumn=0x7e64a600,
>pzErrMsg=0x7e64a4f4) at sqlite3.c:92613
> #9  0x36377594 in SqlQuery::exec (this=0x36af9f44,
>sql_stmt=0x7e64a80c "select * from service_table where service_no =
> '13';", context=, linenum=292215768, warnings=true)
>at SqlQuery.cpp:229
> #10 0x36377ec0 in SqlQuery::exec (this=0x0,
>sql_stmt=0x1ede , warnings=32)
>at SqlQuery.cpp:255
> #11 0x1004869c in IsgServiceType (theTaskDataPtr=,
>theArgArrayPtr=,
>theVariableNamePtr=0x101ff180 "ip-forward",
>theOutputStringPtr=0x1031b440 "",
>theUserCookiePtrPtr=) at IsgService.cc:711
> #12 0x101c1bcc in RcParseLine (theConnectionPtr=0x11681900)
>at ./RomCLI/Sources/RcParse.c:606
> #13 0x101be784 in RcFiniteStateMachine (theConnectionPtr=0x11681900)
>at ./RomCLI/Sources/RcCmdLin.c:913
> #14 0x101c5c44 in HandleConnectionTask (theConnectionPtr= out>)
>at ./Engine/Sources/AsMain.c:1861
> #15 AllegroMainTask (theTaskDataPtr=0x116805d0,
>theHttpTasks=, theTcpTasks=0x7e64eb64)
>at ./Engine/Sources/AsMain.c:1483
> #16 0x101851e0 in instantiate_task ()
>at ./Interfaces/LinuxUnix/SingleTask/RpTask.c:253
> #17 c_main (argc=, argv=)
>at ./Interfaces/LinuxUnix/SingleTask/RpTask.c:105
> #18 0x10184070 in app_main (argc=1, argv=0x7e653ca4)
>at ./Interfaces/LinuxUnix/SingleTask/RpStart.cpp:297
> #19 0x1000d2d4 in main (argc=1, argv=0x7e653ca4)
> at
> /mnt/local/cch/bugfix_test_11_01_02232011/isg6000/MaestrOS/mgmt-crd/linux
>
>
> Program terminated with signal 6, Aborted.
> #0  0x31695b04 in raise () from /lib/libc.so.6
> (gdb) bt
> #0  0x31695b04 in raise () from /lib/libc.so.6
> #1  0x316972f4 in abort () from /lib/libc.so.6
> #2  0x3168d2a4 in __assert_fail () from /lib/libc.so.6
> #3  0x3116e404 in sqlite3BtreeHoldsMutex (p=0x117af8d0) at sqlite3.c:45280
> #4  0x311ff2f0 in sqlite3Prepare (db=0x3123b04c,
>zSql=0x797c298c "select * from service_table where service_no = '7';",
> nBytes=-1, saveSqlFlag=0,
>pReprepare=, ppStmt=0x797c24e4, pzTail=0x797c24e8)
> at sqlite3.c:87749
> #5  0x311ffd44 in sqlite3LockAndPrepare (db=0x11d354a8,
>zSql=0x797c298c "select * from service_table where service_no = '7';",
> nBytes=-1, saveSqlFlag=0,
>pOld=0x0, ppStmt=0x797c24e4, pzTail=0x797c24e8) at sqlite3.c:87878
> #6  0x31200440 in sqlite3_prepare (db=0x0, 

[sqlite] mutex assert_fail in sqlite3BtreeHoldsMutex in a heavily load DB access in 3.5.7 but not in 3.6.22

[ChingChang Hsiao]

A script was running for a heavy load DB access. As you can see the sql 
statement "select * from service_table where service_no = '13';" in the log.  
There is no rows for service_no 13 or 7  in service_table when it is accessed. 
The logic is check the entry is there or not, if not then insert it. Could 
service_table be locked by previously writing(service_no 12 or 6)?

ChingChang

Version is 3.5.7
journal mode = DELETE
SELECT sqlite_source_id(); 2011-01-28 17:03:50 
ed759d5a9edb3bba5f48f243df47be29e3fe8cd7

OTHER_FLAGS=-DSQLITE_THREADSAFE=2  -DSQLITE_THREAD_OVERRIDE_LOCK=-1 
-DSQLITE_DEBUG=1
CFLAGS += -O0 -w $(OTHER_FLAGS)
in Makefile



SQLITE_PRIVATE int sqlite3BtreeHoldsMutex(Btree *p){
  assert( p->sharable==0 || p->locked==0 || p->wantToLock>0 );
  assert( p->sharable==0 || p->locked==0 || p->db==p->pBt->db );
  assert( p->sharable==0 || p->locked==0 || sqlite3_mutex_held(p->pBt->mutex) );
  assert( p->sharable==0 || p->locked==0 || sqlite3_mutex_held(p->db->mutex) );

  return (p->sharable==0 || p->locked);
}


(gdb) bt
#0  0x369d5b04 in raise () from /lib/libc.so.6
#1  0x369d72f4 in abort () from /lib/libc.so.6
#2  0x369cd2a4 in __assert_fail () from /lib/libc.so.6
#3  0x364ceb24 in sqlite3BtreeHoldsMutex (p=0x117b94f0) at sqlite3.c:45280
#4  0x36544a14 in sqlite3Prepare (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
nBytes=-1, saveSqlFlag=0, pReprepare=0x0, ppStmt=0x7e64a374,
pzTail=0x7e64a378) at sqlite3.c:87749
#5  0x365450d8 in sqlite3LockAndPrepare (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
nBytes=-1, saveSqlFlag=0, pOld=0x0, ppStmt=0x7e64a374, pzTail=0x7e64a378)
at sqlite3.c:87878
#6  0x36545404 in sqlite3_prepare (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
nBytes=-1, ppStmt=0x7e64a374, pzTail=0x7e64a378) at sqlite3.c:87941
#7  0x3653e87c in sqlite3_exec (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
xCallback=0x36551600 , pArg=0x7e64a3e8,
pzErrMsg=0x7e64a4f4) at sqlite3.c:84507
#8  0x36551ab4 in sqlite3_get_table (db=0x11aabd58,
zSql=0x7e64a80c "select * from service_table where service_no = '13';",
pazResult=0x7e64a5f8, pnRow=0x7e64a5fc, pnColumn=0x7e64a600,
pzErrMsg=0x7e64a4f4) at sqlite3.c:92613
#9  0x36377594 in SqlQuery::exec (this=0x36af9f44,
sql_stmt=0x7e64a80c "select * from service_table where service_no = '13';", 
context=, linenum=292215768, warnings=true)
at SqlQuery.cpp:229
#10 0x36377ec0 in SqlQuery::exec (this=0x0,
sql_stmt=0x1ede , warnings=32)
at SqlQuery.cpp:255
#11 0x1004869c in IsgServiceType (theTaskDataPtr=,
theArgArrayPtr=,
theVariableNamePtr=0x101ff180 "ip-forward",
theOutputStringPtr=0x1031b440 "",
theUserCookiePtrPtr=) at IsgService.cc:711
#12 0x101c1bcc in RcParseLine (theConnectionPtr=0x11681900)
at ./RomCLI/Sources/RcParse.c:606
#13 0x101be784 in RcFiniteStateMachine (theConnectionPtr=0x11681900)
at ./RomCLI/Sources/RcCmdLin.c:913
#14 0x101c5c44 in HandleConnectionTask (theConnectionPtr=)
at ./Engine/Sources/AsMain.c:1861
#15 AllegroMainTask (theTaskDataPtr=0x116805d0,
theHttpTasks=, theTcpTasks=0x7e64eb64)
at ./Engine/Sources/AsMain.c:1483
#16 0x101851e0 in instantiate_task ()
at ./Interfaces/LinuxUnix/SingleTask/RpTask.c:253
#17 c_main (argc=, argv=)
at ./Interfaces/LinuxUnix/SingleTask/RpTask.c:105
#18 0x10184070 in app_main (argc=1, argv=0x7e653ca4)
at ./Interfaces/LinuxUnix/SingleTask/RpStart.cpp:297
#19 0x1000d2d4 in main (argc=1, argv=0x7e653ca4)
at /mnt/local/cch/bugfix_test_11_01_02232011/isg6000/MaestrOS/mgmt-crd/linux


Program terminated with signal 6, Aborted.
#0  0x31695b04 in raise () from /lib/libc.so.6
(gdb) bt
#0  0x31695b04 in raise () from /lib/libc.so.6
#1  0x316972f4 in abort () from /lib/libc.so.6
#2  0x3168d2a4 in __assert_fail () from /lib/libc.so.6
#3  0x3116e404 in sqlite3BtreeHoldsMutex (p=0x117af8d0) at sqlite3.c:45280
#4  0x311ff2f0 in sqlite3Prepare (db=0x3123b04c,
zSql=0x797c298c "select * from service_table where service_no = '7';", 
nBytes=-1, saveSqlFlag=0,
pReprepare=, ppStmt=0x797c24e4, pzTail=0x797c24e8) at 
sqlite3.c:87749
#5  0x311ffd44 in sqlite3LockAndPrepare (db=0x11d354a8,
zSql=0x797c298c "select * from service_table where service_no = '7';", 
nBytes=-1, saveSqlFlag=0,
pOld=0x0, ppStmt=0x797c24e4, pzTail=0x797c24e8) at sqlite3.c:87878
#6  0x31200440 in sqlite3_prepare (db=0x0, zSql=0xefc , nBytes=6,
ppStmt=0xffc0, pzTail=0xf104) at sqlite3.c:87941
#7  0x31202264 in sqlite3_exec (db=0x11d354a8,
zSql=0x797c298c "select * from service_table where service_no = '7';",
xCallback=0x31189f2c , pArg=0x797c2550, 
pzErrMsg=0x797c2674)
at sqlite3.c:84507
#8  0x3120276c in sqlite3_get_table (db=0x11d354a8,
zSql=0x797c298c "select * from 

Re: [sqlite] Licensing and copyright info?

[Simon Slavin]

On 10 May 2011, at 8:03pm, Mr. Puneet Kishor wrote:

> On May 10, 2011, at 1:59 PM, Don Ireland wrote:
> 
>> I am writing an app and plan to embed SQLite in my app as a means to store 
>> the data.
>> 
>> What licensing/copyright statements do I need to make RE SQLite?
> 
> Nothing. sqlite, the program, is in the Public Domain

In case you need a legal page for your lawyers, the appropriate page is here:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction triggers?

[Nico Williams]

FWIW, I'm making progress.  I've got BEGIN and COMMIT triggers firing,
but there's issues related to auto-commit which imply that I need to
be able to skip -at runtime- the trigger firing that I code with each
OP_Transaction and OP_AutoCommit operation, which I think means I need
a new op, but we'll see.

Also, I've decided to change the syntax so it's AFTER BEGIN and BEFORE COMMIT.

AFTER BEGIN triggers will only fire for RESERVED and IMMEDIATE
transactions, and on the first INSERT/UPDATE/DELETE in a RESERVED
transaction.

BEFORE COMMIT triggers can fire more than once: because the commit
might fail due to, e.g., deferred constraints, and even because the
triggers themselves might RAISE(...).  Speaking of which,
RAISE(IGNORE) in these triggers should be ignored and the BEGIN/COMMIT
proceeds as usual -- I've not tested that yet, but I suspect that one
of RAISE(ABORT/FAIL) or RAISE(IGNORE) in such triggers will not work
as expected without additional code.

I'll also code up BEFORE ROLLBACK trigger firing, but only for
synchronous rollbacks (i.e., when the app executes a ROLLBACK
statement).  I don't think ROLLBACK triggers could be of any use
unless there are functions and/or virtual tables with side-effects
(think logging).

Database connect/disconnect triggers will not fire by default; a new
pragma will be needed to enable them.  Connect triggers will fire when
that pragma is enabled (or if compiled as enabled, then when the DB
handle is opened).  Disconnect triggers will fire immediately upon
entry to sqlite3_close(), and if sqlite3_close fails (e.g., due to
SQLITE_BUSY), their firing may seem spurious, just as with BEFORE
COMMIT triggers.

I've no need for AFTER ROLLBACK/COMMIT triggers (besides, those seem daunting).

My patches so far are small, believe it or not.  (But I spent a lot of
time figuring out how to create the crutch views automatically, and a
lot of time figuring out how to fire the triggers.)  Things work, with
some quirks and some missing functionality, so it's not ready yet.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Licensing and copyright info?

[Don Ireland]

Ok thanks.

Don Ireland

-Original Message-
From: "Mr. Puneet Kishor" 
To: General Discussion of SQLite Database 
Sent: Tue, 10 May 2011 2:03 PM
Subject: Re: [sqlite] Licensing and copyright info?


On May 10, 2011, at 1:59 PM, Don Ireland wrote:

> I am writing an app and plan to embed SQLite in my app as a means to store 
> the data.
> 
> What licensing/copyright statements do I need to make RE SQLite?


Nothing. sqlite, the program, is in the Public Domain. SQLite, the term, is 
trademarked. Embed sqlite in your app, don't call your app "SQLite" or 
something that can be confused with the term "SQLite," make loads of money, 
become a millionaire, and then do some public good with it. Go forth and 
multiply.


> 
> TIA!
> 
> Don Ireland
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Example/Tutorial for "extension_functions.c" in C/C++ Prog With "sqlite3.c"

[Mays, Steve]

I have searched the web and I have looked through the subjects of all
the archived posts and I cannot find any examples showing me how to use
the "extension_functions.c" code in a C/C++ program with "sqlite3.c".  I
am aware of the compilation instructions included in the
"extension_functions.c" source file and I have studied the example found
in the wiki (http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions).
Unless I am mistaken, these instructions are set up to create a *.dll
file to be used/dynamically linked by "sqlite3.c" at run time.

 

 

I use CodeGear C++ Builder and I have absolutely no trouble using
"sqlite3.c".  As huge as the amalgamation is, not even as much a single
warning is raised (I AM VERY IMPRESSED!).  Within the last two years I
wrote a C++ wrapper around "sqlite3.c", but the hard drive went up in
smoke, so my goal is to re-create the wrapper and to include support for
extended aggregate functions, such as standard deviation, hence the
desire to use "extension_functions.c".  The goal is also to avoid
dependencies.

 

 

Question 1.)  Can "exentension_functions.c" be compiled along with
"sqlite3.c" into one executable?

Question 2.)  If so, how?

Question 3.)  If "exentension_functions.c" be compiled into one
executable along with "sqlite3.c", do I need to do anything special or
will sqlite3_exec() know what to do with queries like:

"SELECT AVG(V_OUT) AS V_OUT_AVG, STDEV(V_OUT) AS V_OUT_STDEV FROM
test_data_table GROUP BY TargetInputVoltage"

 

 

Thanks,

Steve.



This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you have received this email in error please notify the system manager. This 
message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. If you are not the intended recipient you are 
notified that disclosing, copying, distributing or taking any action in 
reliance on the contents of this information is strictly prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Licensing and copyright info?

[Mr. Puneet Kishor]

On May 10, 2011, at 1:59 PM, Don Ireland wrote:

> I am writing an app and plan to embed SQLite in my app as a means to store 
> the data.
> 
> What licensing/copyright statements do I need to make RE SQLite?


Nothing. sqlite, the program, is in the Public Domain. SQLite, the term, is 
trademarked. Embed sqlite in your app, don't call your app "SQLite" or 
something that can be confused with the term "SQLite," make loads of money, 
become a millionaire, and then do some public good with it. Go forth and 
multiply.


> 
> TIA!
> 
> Don Ireland
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Licensing and copyright info?

[Don Ireland]

I am writing an app and plan to embed SQLite in my app as a means to store the 
data.

What licensing/copyright statements do I need to make RE SQLite?

TIA!

Don Ireland
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Gerry Snyder]

If this has already been suggested, I apologize.

Add an integer column with a UNIQUE ON CONFLICT REPLACE constraint.Then 
after you figure out how many entries are enough (maxcount), insert each 
row, specifying that column as mod((lastinsertrowid()+1),maxcount) or 
however you specify a modulus or remainder. That column will just wrap 
around when it hits maxcount; and you extract based on rowid, not that 
column to keep things in proper order.

And don't worry about maxing out on rowid.

HTH,

Gerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Hi Jay,

Wow, thanks for your detailed message below.much appreciated ;-)

I will try the PRAGMA and also the "msg_seq".great.

Lynton



On 10/05/2011 19:00, Jay A. Kreibich wrote:
> On Tue, May 10, 2011 at 12:42:14PM +0200, Lynton Grice scratched on the wall:
>> Hi all,
>>
>> Thanks for your comments...much appreciated..
>>
>> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
>> to say FIX the sqlite database size to say "5 MB"?
>PRAGMA max_page_count = N
>
>http://www.sqlite.org/pragma.html#pragma_max_page_count
>
>This simply fixes the total size of the database.  If you attempt to
>perform an operation (such as an INSERT) that would push it over the
>limit, you get an out-of-space error.
>
>This PRAGMA needs to be set every time the database is opened.
>
>> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will
>> happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or
>> similar?
>Yes.
>
>> I guess I am looking for a "round robin queue" here?
>I'd do something like this.  This keeps a constant number of messages
>in the log.  The "msg_id" provides a message counter, while the
>"msg_seq" is used to keep the round-robin offset.  The use of a view
>is required to enable a INSTEAD OF trigger.  There might be a way to
>do this directly against the a table, but I'm not all that
>experienced with SQLite triggers.
>
> ===
>
> -- Create storage table:
> CREATE TABLE log_t (
>msg_id INTEGER PRIMARY KEY AUTOINCREMENT,
>msg_seqINTEGER UNIQUE,
>time   TEXT DEFAULT CURRENT_TIMESTAMP,
>msgTEXT );
>
> -- Init sqlite_sequence table:
> INSERT INTO log_t ( msg_seq, msg ) VALUES ( -1, 'init' );
> DELETE FROM log_t WHERE msg_seq = -1;
>
> -- Create view:
> CREATE VIEW log AS SELECT msg_id, msg_seq, time, msg FROM log_t;
>
> CREATE TRIGGER log_seq_trg
>INSTEAD OF INSERT ON log
>FOR EACH ROW
>BEGIN
>  INSERT OR REPLACE INTO log_t ( msg_seq, msg ) VALUES (
>( SELECT seq + 1 FROM sqlite_sequence WHERE name = 'log_t' ) %
>
> -- THIS VALUE CONTROLS THE NUMBER OF MESSAGES KEPT IN THE LOG:
>10,
>
>  NEW.msg );
>END;
>
> ===
>
>To test:
>
> ===
> INSERT INTO log ( msg ) VALUES ( 'a' );
> INSERT INTO log ( msg ) VALUES ( 'b' );
> INSERT INTO log ( msg ) VALUES ( 'c' );
> INSERT INTO log ( msg ) VALUES ( 'd' );
> INSERT INTO log ( msg ) VALUES ( 'e' );
> INSERT INTO log ( msg ) VALUES ( 'f' );
> INSERT INTO log ( msg ) VALUES ( 'g' );
> INSERT INTO log ( msg ) VALUES ( 'h' );
> INSERT INTO log ( msg ) VALUES ( 'i' );
> INSERT INTO log ( msg ) VALUES ( 'j' );
> INSERT INTO log ( msg ) VALUES ( 'k' );
> INSERT INTO log ( msg ) VALUES ( 'l' );
> INSERT INTO log ( msg ) VALUES ( 'm' );
> INSERT INTO log ( msg ) VALUES ( 'n' );
> INSERT INTO log ( msg ) VALUES ( 'o' );
> INSERT INTO log ( msg ) VALUES ( 'p' );
> INSERT INTO log ( msg ) VALUES ( 'q' );
> INSERT INTO log ( msg ) VALUES ( 'r' );
> INSERT INTO log ( msg ) VALUES ( 's' );
> INSERT INTO log ( msg ) VALUES ( 't' );
> INSERT INTO log ( msg ) VALUES ( 'u' );
> INSERT INTO log ( msg ) VALUES ( 'v' );
> INSERT INTO log ( msg ) VALUES ( 'w' );
> INSERT INTO log ( msg ) VALUES ( 'x' );
> INSERT INTO log ( msg ) VALUES ( 'y' );
> INSERT INTO log ( msg ) VALUES ( 'z' );
> ===
>
> sqlite>  SELECT * FROM log;
> 18|8|2011-05-10 13:38:14|q
> 19|9|2011-05-10 13:38:14|r
> 20|0|2011-05-10 13:38:14|s
> 21|1|2011-05-10 13:38:14|t
> 22|2|2011-05-10 13:38:14|u
> 23|3|2011-05-10 13:38:14|v
> 24|4|2011-05-10 13:38:14|w
> 25|5|2011-05-10 13:38:14|x
> 26|6|2011-05-10 13:38:14|y
> 27|7|2011-05-10 13:38:14|z
>
> ===
>
>Note that because I'm grabbing the msg_seq value out of the
>sqlite_sequence table, there is some chance of minor screw-ups if the
>logging system is deeply involved in transactions and check-points
>that might invalidate a sequence number.  Don't do that.
>
>
>> While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a
>> LOGGER implementation, and the integer reaches it's limit
>It won't.  It's a 63 bit value**.  Even if you're inserting a million
>rows per second, it will take almost 300,000 years to run out of
>numbers.  The only way you'll run out is if a very high value is
>manually inserted, throwing off the sequence.
>
>** OK, technically it is a 64 bit signed value, but sequences start
>   at 1 by default, limiting them to half the number domain-- hence 63.
>
>> will the sqlite database assign "un-used
>> primary keys" (previously deleted) to any NEW inserts?
>No, 

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Nico Williams]

On Tue, May 10, 2011 at 12:00 PM, Jay A. Kreibich  wrote:
>> I guess I am looking for a "round robin queue" here?
>
>  I'd do something like this.  This keeps a constant number of messages
>  in the log.  The "msg_id" provides a message counter, while the
>  "msg_seq" is used to keep the round-robin offset.  The use of a view
>  is required to enable a INSTEAD OF trigger.  There might be a way to
>  do this directly against the a table, but I'm not all that
>  experienced with SQLite triggers.

You can get the effect of INSTEAD OF triggers on actual tables by
using a BEFORE INSERT trigger that ends with a SELECT RAISE(IGNORE):

CREATE TABLE t1(a);
CREATE TABLE t2(a);
CREATE TRIGGER t BEFORE INSERT ON t1 BEGIN
 INSERT INTO t2 SELECT NEW.a;
 SELECT RAISE(IGNORE);
END;

Thanks for the tip about the max_page_count pragma!

And, thinking about the way SQLite3 works today, re-using rowids --
using rowids as a circular list -- should help keep the page count
down, particularly if there's a maximum size for the rows being added
into that table.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Jean-Christophe Deschamps]

Without a view (but with a trigger) and certainly open to improvement 
(9 is the
MAX_ENTRIES parameter):

CREATE TABLE "log" (
   "id" INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT,
   "seq" INTEGER CONSTRAINT "ix1Seq" UNIQUE ON CONFLICT REPLACE,
   "data" CHAR);

CREATE TRIGGER "trInsLog"
AFTER INSERT
ON "log"
BEGIN
  update log set seq = id % 9 where id=(select max(id) from log);
END;

insert into log (data) values ('a');
insert into log (data) values ('b');
insert into log (data) values ('c');
insert into log (data) values ('d');
insert into log (data) values ('e');
insert into log (data) values ('f');
insert into log (data) values ('g');
insert into log (data) values ('h');
insert into log (data) values ('i');
insert into log (data) values ('j');
insert into log (data) values ('k');
insert into log (data) values ('l');
insert into log (data) values ('m');
insert into log (data) values ('o');
insert into log (data) values ('p');
insert into log (data) values ('q');
insert into log (data) values ('r');
insert into log (data) values ('s');
insert into log (data) values ('t');
insert into log (data) values ('u');
insert into log (data) values ('v');
insert into log (data) values ('w');
insert into log (data) values ('x');
insert into log (data) values ('y');
insert into log (data) values ('z');
insert into log (data) values ('Here you are!');


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Jay A. Kreibich]

On Tue, May 10, 2011 at 12:42:14PM +0200, Lynton Grice scratched on the wall:
> 
> Hi all,
> 
> Thanks for your comments...much appreciated..
> 
> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use 
> to say FIX the sqlite database size to say "5 MB"?

  PRAGMA max_page_count = N

  http://www.sqlite.org/pragma.html#pragma_max_page_count

  This simply fixes the total size of the database.  If you attempt to
  perform an operation (such as an INSERT) that would push it over the
  limit, you get an out-of-space error.

  This PRAGMA needs to be set every time the database is opened.

> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will 
> happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or 
> similar?

  Yes.

> I guess I am looking for a "round robin queue" here?

  I'd do something like this.  This keeps a constant number of messages
  in the log.  The "msg_id" provides a message counter, while the
  "msg_seq" is used to keep the round-robin offset.  The use of a view
  is required to enable a INSTEAD OF trigger.  There might be a way to
  do this directly against the a table, but I'm not all that
  experienced with SQLite triggers.

===

-- Create storage table:
CREATE TABLE log_t (
  msg_id INTEGER PRIMARY KEY AUTOINCREMENT,
  msg_seqINTEGER UNIQUE,
  time   TEXT DEFAULT CURRENT_TIMESTAMP,
  msgTEXT );

-- Init sqlite_sequence table:
INSERT INTO log_t ( msg_seq, msg ) VALUES ( -1, 'init' );
DELETE FROM log_t WHERE msg_seq = -1;

-- Create view:
CREATE VIEW log AS SELECT msg_id, msg_seq, time, msg FROM log_t;

CREATE TRIGGER log_seq_trg
  INSTEAD OF INSERT ON log
  FOR EACH ROW
  BEGIN
INSERT OR REPLACE INTO log_t ( msg_seq, msg ) VALUES ( 
  ( SELECT seq + 1 FROM sqlite_sequence WHERE name = 'log_t' ) % 

-- THIS VALUE CONTROLS THE NUMBER OF MESSAGES KEPT IN THE LOG: 
  10,

NEW.msg );
  END;

===

  To test:

===
INSERT INTO log ( msg ) VALUES ( 'a' );
INSERT INTO log ( msg ) VALUES ( 'b' );
INSERT INTO log ( msg ) VALUES ( 'c' );
INSERT INTO log ( msg ) VALUES ( 'd' );
INSERT INTO log ( msg ) VALUES ( 'e' );
INSERT INTO log ( msg ) VALUES ( 'f' );
INSERT INTO log ( msg ) VALUES ( 'g' );
INSERT INTO log ( msg ) VALUES ( 'h' );
INSERT INTO log ( msg ) VALUES ( 'i' );
INSERT INTO log ( msg ) VALUES ( 'j' );
INSERT INTO log ( msg ) VALUES ( 'k' );
INSERT INTO log ( msg ) VALUES ( 'l' );
INSERT INTO log ( msg ) VALUES ( 'm' );
INSERT INTO log ( msg ) VALUES ( 'n' );
INSERT INTO log ( msg ) VALUES ( 'o' );
INSERT INTO log ( msg ) VALUES ( 'p' );
INSERT INTO log ( msg ) VALUES ( 'q' );
INSERT INTO log ( msg ) VALUES ( 'r' );
INSERT INTO log ( msg ) VALUES ( 's' );
INSERT INTO log ( msg ) VALUES ( 't' );
INSERT INTO log ( msg ) VALUES ( 'u' );
INSERT INTO log ( msg ) VALUES ( 'v' );
INSERT INTO log ( msg ) VALUES ( 'w' );
INSERT INTO log ( msg ) VALUES ( 'x' );
INSERT INTO log ( msg ) VALUES ( 'y' );
INSERT INTO log ( msg ) VALUES ( 'z' );
===

sqlite> SELECT * FROM log;
18|8|2011-05-10 13:38:14|q
19|9|2011-05-10 13:38:14|r
20|0|2011-05-10 13:38:14|s
21|1|2011-05-10 13:38:14|t
22|2|2011-05-10 13:38:14|u
23|3|2011-05-10 13:38:14|v
24|4|2011-05-10 13:38:14|w
25|5|2011-05-10 13:38:14|x
26|6|2011-05-10 13:38:14|y
27|7|2011-05-10 13:38:14|z

===

  Note that because I'm grabbing the msg_seq value out of the
  sqlite_sequence table, there is some chance of minor screw-ups if the
  logging system is deeply involved in transactions and check-points
  that might invalidate a sequence number.  Don't do that.


> While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a 
> LOGGER implementation, and the integer reaches it's limit

  It won't.  It's a 63 bit value**.  Even if you're inserting a million
  rows per second, it will take almost 300,000 years to run out of
  numbers.  The only way you'll run out is if a very high value is
  manually inserted, throwing off the sequence.
  
  ** OK, technically it is a 64 bit signed value, but sequences start
 at 1 by default, limiting them to half the number domain-- hence 63.

> will the sqlite database assign "un-used 
> primary keys" (previously deleted) to any NEW inserts?

  No, not with an AUTOINCREMENT:  http://www.sqlite.org/autoinc.html

"If the table has previously held a row with the largest possible
 ROWID, then new INSERTs are not allowed and any attempt to insert
 a new row will fail with an SQLITE_FULL error."



  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the 

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Yup, fair enough...what I think would be better is to have s 
variable set called something like "history_retain_time" (like Nico 
said)..and perhaps a "log_check_interval" in DAYS or HOURS or 
MINUTESwhatever suits the application.

Then perhaps on each insert you get the code to get the current TIME, 
then if the time is >= "log_check_interval" then it is time to DELETE 
all records older than the "history_retain_time".

I think that would be better.

Chat later

Lynton


On 10/05/2011 16:38, Stephan Beal wrote:
> On Tue, May 10, 2011 at 4:32 PM, Lynton Grice
> wrote:
>
>> I like the ON INSERT trigger.good idea. So perhaps you have a
>> "setLogMaxSize" type function in C that allows the client program to say
>> "hey, I only want the log to hold a max of 10 000 records".and then
>> I do a select count(*) inside the ON INSERT type trigger and delete
>> entries if "num records>  max"..
>>
> The problem i see with that is that once the log has overflowed one time,
> further log calls will run a DELETE very often. Why?
>
> max_log_count=10
> current_log_count=9
>
> log("foo");
> current_log_count=10 == clean up ==
> current_log_count=9
>
> log("bar")
> current_log_count=10 == clean up ==
> current_log_count=9
> ...
> ad naseum
>
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Hey NIco,

Now this is great.in fact I was playing with an "update hook" the 
other dayand was going to put the deletion logic under the 
SQLITE_INSERT below

But your code looks better ;-) Thanks !

void update_callback( void* udp, int type, const char* db_name, const 
char* tbl_name, sqlite3_int64 rowid ){
 switch(type){
 case(SQLITE_INSERT):
 //Do deletion logic
 break;
 case(SQLITE_DELETE):
 //Do something
 break;
 };
}

On 10/05/2011 15:36, Nico Williams wrote:
> Or just a function to return the size of the current DB.  Mind you,
> automatically deleting rows from a log table isn't enough: you may
> have to periodically VACUUM the DB, or you may have to setup
> auto_vacuum (and incremental_vacuum).
>
> I have code like this in one DB:
>
> CREATE TABLE IF NOT EXISTS LogConfig
>   (opt TEXT PRIMARY KEY NOT NULL UNIQUE,
>val);
> INSERT OR IGNORE INTO LogConfig (opt, val)
> VALUES ('replication_retain_time', '7 days');
> INSERT OR IGNORE INTO LogConfig (opt, val)
> VALUES ('replication_retain_count', '1');
> INSERT OR IGNORE INTO LogConfig (opt, val)
> VALUES ('history_retain_time', '7 days');
> INSERT OR IGNORE INTO LogConfig (opt, val)
> VALUES ('history_retain_count', '1');
> ...
> DROP TRIGGER IF EXISTS EntryLog_prune;
> CREATE TRIGGER EntryLog_prune
> AFTER INSERT ON EntryLog
> FOR EACH ROW BEGIN
>   DELETE FROM EntryLog
>   WHERE mtime<  strftime('%s', 'now', '-' || (
> SELECT lc.val
> FROM LogConfig lc
> WHERE lc.opt = 'history_retain_time')) AND
>tx<  ((SELECT t.tx FROM TX t ORDER by t.tx DESC LIMIT 1) - (
>  SELECT val FROM LogConfig WHERE opt = 'history_retain_count'));
> END;
>
> It'd be simple to change the above to use a max DB size, if there was
> a function to return the current DB size.  See above note regarding
> vacuum.  Something like this:
>
> CREATE TRIGGER EntryLog_prune
> AFTER INSERT ON EntryLog
> FOR EACH ROW BEGIN
>   DELETE FROM EntryLog
>   WHERE tx<  (SELECT min(el.tx) FROM EntryLog el) - (
>  SELECT lc.val FROM LogConfig lc WHERE lc.opt = 'delete_at_once')
>   AND db_size()>  (SELECT lc.val FROM LogConfig lc WHERE lc.opt = 
> 'max_db_size');
> END;
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Stephan Beal]

On Tue, May 10, 2011 at 4:32 PM, Lynton Grice
wrote:

> I like the ON INSERT trigger.good idea. So perhaps you have a
> "setLogMaxSize" type function in C that allows the client program to say
> "hey, I only want the log to hold a max of 10 000 records".and then
> I do a select count(*) inside the ON INSERT type trigger and delete
> entries if "num records > max"..
>

The problem i see with that is that once the log has overflowed one time,
further log calls will run a DELETE very often. Why?

max_log_count=10
current_log_count=9

log("foo");
current_log_count=10 == clean up ==
current_log_count=9

log("bar")
current_log_count=10 == clean up ==
current_log_count=9
...
ad naseum


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Hi Enrico,

I like the ON INSERT trigger.good idea. So perhaps you have a 
"setLogMaxSize" type function in C that allows the client program to say 
"hey, I only want the log to hold a max of 10 000 records".and then 
I do a select count(*) inside the ON INSERT type trigger and delete 
entries if "num records > max"..

Regarding the locking, I am using a WAL mode database, so I would say we 
are talking microseconds anyway? So I am not too worried.or should I be?

Thanks

Lynton


On 10/05/2011 15:28, Enrico Thierbach wrote:
>> A round robin queue is fine.  Every so often, to kill off old records do
>>
>> SELECT max(rowid) FROM myTable
>>
>> then in your code subtract from it however many rows you want to keep, then 
>> do
>>
>> DELETE FROM myTable WHERE rowid<  firstToRetain
>>
>> It won't work perfectly but it's simple and fast.
>>
> You could even do something like that in an ON INSERT trigger. And with an 
> AUTOINCREMENT primary key (see http://www.sqlite.org/autoinc.html)
> you would not even have to SELECT max(rowid), as the max rowid is then the 
> rowid of the new record.
>
> As a side note: even when run from a separate thread, inserting the log 
> entries and deleting old entries would lock the database, thus affecting any 
> main thread. You would probably want a separate database for that.
>
> /eno
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Hi Simon,

Thanks for the feedback below, I will write some "delete logic" to run 
from time to time...;-)

Lynton

On 10/05/2011 13:34, Simon Slavin wrote:
> On 10 May 2011, at 11:42am, Lynton Grice wrote:
>
>> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
>> to say FIX the sqlite database size to say "5 MB"?
> There isn't one.  SQLite would not know which records to delete.
>
>> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will
>> happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or
>> similar? I guess I am looking for a "round robin queue" here?
> A round robin queue is fine.  Every so often, to kill off old records do
>
> SELECT max(rowid) FROM myTable
>
> then in your code subtract from it however many rows you want to keep, then do
>
> DELETE FROM myTable WHERE rowid<  firstToRetain
>
> It won't work perfectly but it's simple and fast.
>
>> While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a
>> LOGGER implementation, and the integer reaches it's limit (even though I
>> am deleting previous records), will the sqlite database assign "un-used
>> primary keys" (previously deleted) to any NEW inserts?
> A SQLite integer can get /really/ big: 2^63.  There's no way you could ever 
> write enough records to push it  over the limit.  Your hardware will 
> disintegrate first.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question: Memory-Based Databases

[Tito Ciuro]

Thanks Pavel!

-- Tito

On May 10, 2011, at 11:12 AM, Pavel Ivanov wrote:

> Until you reach limit set by 'pragma cache_size' memory usage would be
> the same for in-memory database and on-disk database. When the size of
> your database grows beyond 'pragma cache_size' in-memory database
> starts to consume more memory than on-disk one, because it has nowhere
> to push pages out of the cache.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question: Memory-Based Databases

[Pavel Ivanov]

> Is this true, or is the memory usage pretty much similar?

Until you reach limit set by 'pragma cache_size' memory usage would be
the same for in-memory database and on-disk database. When the size of
your database grows beyond 'pragma cache_size' in-memory database
starts to consume more memory than on-disk one, because it has nowhere
to push pages out of the cache.


Pavel


On Tue, May 10, 2011 at 9:18 AM, Tito Ciuro  wrote:
> Hello,
>
> I have been using memory-based databases (opened via :memory:) and 
> performance is great. However, one of the things I assumed with memory-based 
> databases was that memory usage would be higher than the temporary or 
> persistent databases stored on disk. Is this true, or is the memory usage 
> pretty much similar? I've perform a few tests with fairly small data sets and 
> I don't see a difference. Any ideas?
>
> Thanks,
>
> -- Tito
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Danny]

Why not have TWO tables?  Log_A and Log_B?

When Log_A is full, DELETE everything from Log_B and start logging to it.  When 
Lob_B is full, DELETE everything from Log_A and start logging to it again.

If you want, while logging to one, the other can be archived ...


--- On Tue, 5/10/11, Simon Slavin  wrote:

From: Simon Slavin 
Subject: Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?
To: "General Discussion of SQLite Database" 
Date: Tuesday, May 10, 2011, 7:34 AM


On 10 May 2011, at 11:42am, Lynton Grice wrote:

> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use 
> to say FIX the sqlite database size to say "5 MB"?

There isn't one.  SQLite would not know which records to delete.

> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will 
> happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or 
> similar? I guess I am looking for a "round robin queue" here?

A round robin queue is fine.  Every so often, to kill off old records do

SELECT max(rowid) FROM myTable

then in your code subtract from it however many rows you want to keep, then do

DELETE FROM myTable WHERE rowid < firstToRetain

It won't work perfectly but it's simple and fast.

> While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a 
> LOGGER implementation, and the integer reaches it's limit (even though I 
> am deleting previous records), will the sqlite database assign "un-used 
> primary keys" (previously deleted) to any NEW inserts?

A SQLite integer can get /really/ big: 2^63.  There's no way you could ever 
write enough records to push it  over the limit.  Your hardware will 
disintegrate first.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Nico Williams]

Or just a function to return the size of the current DB.  Mind you,
automatically deleting rows from a log table isn't enough: you may
have to periodically VACUUM the DB, or you may have to setup
auto_vacuum (and incremental_vacuum).

I have code like this in one DB:

CREATE TABLE IF NOT EXISTS LogConfig
 (opt TEXT PRIMARY KEY NOT NULL UNIQUE,
  val);
INSERT OR IGNORE INTO LogConfig (opt, val)
VALUES ('replication_retain_time', '7 days');
INSERT OR IGNORE INTO LogConfig (opt, val)
VALUES ('replication_retain_count', '1');
INSERT OR IGNORE INTO LogConfig (opt, val)
VALUES ('history_retain_time', '7 days');
INSERT OR IGNORE INTO LogConfig (opt, val)
VALUES ('history_retain_count', '1');
...
DROP TRIGGER IF EXISTS EntryLog_prune;
CREATE TRIGGER EntryLog_prune
AFTER INSERT ON EntryLog
FOR EACH ROW BEGIN
 DELETE FROM EntryLog
 WHERE mtime < strftime('%s', 'now', '-' || (
   SELECT lc.val
   FROM LogConfig lc
   WHERE lc.opt = 'history_retain_time')) AND
  tx < ((SELECT t.tx FROM TX t ORDER by t.tx DESC LIMIT 1) - (
SELECT val FROM LogConfig WHERE opt = 'history_retain_count'));
END;

It'd be simple to change the above to use a max DB size, if there was
a function to return the current DB size.  See above note regarding
vacuum.  Something like this:

CREATE TRIGGER EntryLog_prune
AFTER INSERT ON EntryLog
FOR EACH ROW BEGIN
 DELETE FROM EntryLog
 WHERE tx < (SELECT min(el.tx) FROM EntryLog el) - (
SELECT lc.val FROM LogConfig lc WHERE lc.opt = 'delete_at_once')
 AND db_size() > (SELECT lc.val FROM LogConfig lc WHERE lc.opt = 'max_db_size');
END;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Enrico Thierbach]

> 
> A round robin queue is fine.  Every so often, to kill off old records do
> 
> SELECT max(rowid) FROM myTable
> 
> then in your code subtract from it however many rows you want to keep, then do
> 
> DELETE FROM myTable WHERE rowid < firstToRetain
> 
> It won't work perfectly but it's simple and fast.
> 

You could even do something like that in an ON INSERT trigger. And with an 
AUTOINCREMENT primary key (see http://www.sqlite.org/autoinc.html)
you would not even have to SELECT max(rowid), as the max rowid is then the 
rowid of the new record.

As a side note: even when run from a separate thread, inserting the log entries 
and deleting old entries would lock the database, thus affecting any main 
thread. You would probably want a separate database for that.

/eno

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Adam DeVita]

Why not use INSERT OR REPLACE to your advantage?

If you set the maximum number of log entries you wanted to keep, then kept
track of your log insert statement,  you could wrap by

int this_log_entry_id=1; //initialize..  actually could be initialized by
getting the log entry id of the min date in your log at the beginning of
your program.



if (this_log_entry_id > max_log_entries){
 this_log_entry_id =1;
}
else{
   this_log_entry_id
}

call_insert_function (this_log_entry_id /*becomes the primary key that you
are inserting or replacing*/ ,  data_to_be_logged ,.



Adam


On Tue, May 10, 2011 at 9:08 AM, Simon Slavin  wrote:

>
> On 10 May 2011, at 1:57pm, Lauri Nurmi wrote:
>
> > El mar, 10-05-2011 a las 12:34 +0100, Simon Slavin escribió:
> >> On 10 May 2011, at 11:42am, Lynton Grice wrote:
> >>
> >>> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
> >>> to say FIX the sqlite database size to say "5 MB"?
> >>
> >> There isn't one.  SQLite would not know which records to delete.
> >
> > If such a pragma existed, SQLite wouldn't need to delete anything
> > necessarily, it could behave like it behaves when trying to write to a
> > full disk.
>
> Good idea.  Or introduce a new result code for 'Database has reached
> maximum allowed size'.  Presumably it would be handled as fixing the number
> of pages.  Might be useful for small platforms like cellphones, where
> running out of memory is a disaster.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question: Memory-Based Databases

[Tito Ciuro]

Hello,

I have been using memory-based databases (opened via :memory:) and performance 
is great. However, one of the things I assumed with memory-based databases was 
that memory usage would be higher than the temporary or persistent databases 
stored on disk. Is this true, or is the memory usage pretty much similar? I've 
perform a few tests with fairly small data sets and I don't see a difference. 
Any ideas?

Thanks,

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Simon Slavin]

On 10 May 2011, at 1:57pm, Lauri Nurmi wrote:

> El mar, 10-05-2011 a las 12:34 +0100, Simon Slavin escribió:
>> On 10 May 2011, at 11:42am, Lynton Grice wrote:
>> 
>>> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use 
>>> to say FIX the sqlite database size to say "5 MB"?
>> 
>> There isn't one.  SQLite would not know which records to delete.
> 
> If such a pragma existed, SQLite wouldn't need to delete anything
> necessarily, it could behave like it behaves when trying to write to a
> full disk.

Good idea.  Or introduce a new result code for 'Database has reached maximum 
allowed size'.  Presumably it would be handled as fixing the number of pages.  
Might be useful for small platforms like cellphones, where running out of 
memory is a disaster.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lauri Nurmi]

El mar, 10-05-2011 a las 12:34 +0100, Simon Slavin escribió:
> On 10 May 2011, at 11:42am, Lynton Grice wrote:
> 
> > BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use 
> > to say FIX the sqlite database size to say "5 MB"?
> 
> There isn't one.  SQLite would not know which records to delete.

If such a pragma existed, SQLite wouldn't need to delete anything
necessarily, it could behave like it behaves when trying to write to a
full disk.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak in SQlite

[Black, Michael (IS)]

#1 I don't see where you're freeing m_szErrorString (not real sure if it gets 
malloc'd on success) -- but you do need to free it on errors for sure.



And where's your Callback function?  Why are you calling SaveResultSet (which 
you also don't show)?  That should probably be done inside the Callback because 
I believe the data is destroyed coming out of the Callback.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Ian Hardingham [i...@omroth.com]
Sent: Tuesday, May 10, 2011 7:17 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Memory leak in SQlite

Hey guys.

I'm sure that this is to do with the way I am using SQLite. I do not
have time to radically change my methodology at this point, but I do
need to fix a rather severe memory leak I'm having.

(My apologies for the code)

Any help is much appreciated.

I query like this:


int SQLiteObject::ExecuteSQL(const char* sql, int hack)
{
int iResult;
sqlite_resultset* pResultSet;

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback,
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");
 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}


And I "clear a result" like this:


void SQLiteObject::ClearResultSet(int index)
{



sqlite_resultset* resultSet;
sqlite_resultrow* resultRow;
S32 rows, cols, iResultSet;

// Get the result set specified by index
resultSet = GetResultSet(index);
iResultSet = GetResultSetIndex(index);
if ((!resultSet) || (!resultSet->bValid))
{
   Con::warnf("Warning SQLiteObject::ClearResultSet(%i) failed to
retrieve specified result set.  Result set was NOT cleared.", index);
   return;
}
// Now we have the specific result set to be cleared.
// What we need to do now is iterate through each "Column" in each "Row"
// and free the strings, then delete the entries.
VectorPtr::iterator iRow;
VectorPtr::iterator iColumnName;
VectorPtr::iterator iColumnValue;

for (iRow = resultSet->vRows.begin(); iRow !=
resultSet->vRows.end(); iRow++)
{
   // Iterate through rows
   // for each row iterate through all the column values and names
   for (iColumnName = (*iRow)->vColumnNames.begin(); iColumnName !=
(*iRow)->vColumnNames.end(); iColumnName++)
   {
  // Iterate through column names.  Free the memory.
  delete[] (*iColumnName);
   }
   for (iColumnValue = (*iRow)->vColumnValues.begin(); iColumnValue
!= (*iRow)->vColumnValues.end(); iColumnValue++)
   {
  // Iterate through column values.  Free the memory.
  delete[] (*iColumnValue);
   }
   // free memory used by the row
   delete (*iRow);
}
// empty the resultset
resultSet->vRows.clear();
resultSet->bValid = false;
delete resultSet;
m_vResultSets.erase_fast(iResultSet);
}

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak in SQlite

[Stephan Beal]

On Tue, May 10, 2011 at 2:17 PM, Ian Hardingham  wrote:

> I'm sure that this is to do with the way I am using SQLite. I do not
> have time to radically change my methodology at this point, but I do
> need to fix a rather severe memory leak I'm having.
>
>
i don't see any immediate leaks in what you posted, but what you posted
relies on tons of code we can't see. Pointer-managing classes like
VectorPtr can certainly be a source of leaks.

i highly recommend running your program through valgrind and see what it
says - it will point you directly to the source of the leak. Valgrind can be
used like this:

valgrind --leak-check=full -v --show-reachable=yes --track-origins=yes
./MyProgram


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory leak in SQlite

[Ian Hardingham]

Hey guys.

I'm sure that this is to do with the way I am using SQLite. I do not 
have time to radically change my methodology at this point, but I do 
need to fix a rather severe memory leak I'm having.

(My apologies for the code)

Any help is much appreciated.

I query like this:


int SQLiteObject::ExecuteSQL(const char* sql, int hack)
{
int iResult;
sqlite_resultset* pResultSet;

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback, 
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");
 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}


And I "clear a result" like this:


void SQLiteObject::ClearResultSet(int index)
{



sqlite_resultset* resultSet;
sqlite_resultrow* resultRow;
S32 rows, cols, iResultSet;

// Get the result set specified by index
resultSet = GetResultSet(index);
iResultSet = GetResultSetIndex(index);
if ((!resultSet) || (!resultSet->bValid))
{
   Con::warnf("Warning SQLiteObject::ClearResultSet(%i) failed to 
retrieve specified result set.  Result set was NOT cleared.", index);
   return;
}
// Now we have the specific result set to be cleared.
// What we need to do now is iterate through each "Column" in each "Row"
// and free the strings, then delete the entries.
VectorPtr::iterator iRow;
VectorPtr::iterator iColumnName;
VectorPtr::iterator iColumnValue;

for (iRow = resultSet->vRows.begin(); iRow != 
resultSet->vRows.end(); iRow++)
{
   // Iterate through rows
   // for each row iterate through all the column values and names
   for (iColumnName = (*iRow)->vColumnNames.begin(); iColumnName != 
(*iRow)->vColumnNames.end(); iColumnName++)
   {
  // Iterate through column names.  Free the memory.
  delete[] (*iColumnName);
   }
   for (iColumnValue = (*iRow)->vColumnValues.begin(); iColumnValue 
!= (*iRow)->vColumnValues.end(); iColumnValue++)
   {
  // Iterate through column values.  Free the memory.
  delete[] (*iColumnValue);
   }
   // free memory used by the row
   delete (*iRow);
}
// empty the resultset
resultSet->vRows.clear();
resultSet->bValid = false;
delete resultSet;
m_vResultSets.erase_fast(iResultSet);
}

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Simon Slavin]

On 10 May 2011, at 11:42am, Lynton Grice wrote:

> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use 
> to say FIX the sqlite database size to say "5 MB"?

There isn't one.  SQLite would not know which records to delete.

> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will 
> happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or 
> similar? I guess I am looking for a "round robin queue" here?

A round robin queue is fine.  Every so often, to kill off old records do

SELECT max(rowid) FROM myTable

then in your code subtract from it however many rows you want to keep, then do

DELETE FROM myTable WHERE rowid < firstToRetain

It won't work perfectly but it's simple and fast.

> While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a 
> LOGGER implementation, and the integer reaches it's limit (even though I 
> am deleting previous records), will the sqlite database assign "un-used 
> primary keys" (previously deleted) to any NEW inserts?

A SQLite integer can get /really/ big: 2^63.  There's no way you could ever 
write enough records to push it  over the limit.  Your hardware will 
disintegrate first.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Afriza N. Arief]

On May 10, 2011 4:09 PM, "Lynton Grice"  wrote:
>
>  Hi there,
>
> how can I implement /
> mimic a type of "rotating log"?
>
> So in my mind I am thinking that perhaps I can LIMIT the size of the
> SQLIte DB to say 5 MB? And once the DB reaches that size it starts
> INSERTING new logs over the earliest records in the database?
>

What I did was to create a separate thread / process which is suspended most
of the time. Every certain interval, it wakes up and do clean up according
to some conditions.

For my case, it will DELETE records older than 60 days by using the
timestamp column I added. I used REAL for the timestamp as I read somewhere
that it will save space and be faster for sorting. It also used its own DB
connection so SQLite will deal with any concurrency issues.

DELETE FROM logs WHERE timestamp < julianday('now','-60 days')

Alternatively, you can keep the last N number of records, for example 1000.

DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY timestamp
DESC LIMIT 1000,-1)

By issuing DELETE statement, you will actually reuse the storage space for
subsequent new data. Issue VACUUM if you want to slowly reclaim unused
space.

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Stephan Beal]

On Tue, May 10, 2011 at 12:35 PM, Lynton Grice
wrote:

> Thanks for this, much appreciated. My application is written in pure C,
> so I guess I will not be able to use your C++ code?
>

It's actually just a thin coating over C, and the whole class is quite
small, so it would be easy to strip out the bits you can re-use. The log
routine, for example, internally uses vnsprintf() (which is C99, not C89, by
the way). The trim() function just prepares and executes a statement.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

Hi all,

Thanks for your comments...much appreciated..

BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use 
to say FIX the sqlite database size to say "5 MB"?

Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will 
happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or 
similar? I guess I am looking for a "round robin queue" here?

While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a 
LOGGER implementation, and the integer reaches it's limit (even though I 
am deleting previous records), will the sqlite database assign "un-used 
primary keys" (previously deleted) to any NEW inserts?

Thanks for the help ;-)

Lynton

On 10/05/2011 12:15, Enrico Thierbach wrote:
> On 10.05.2011, at 12:06, Stephan Beal wrote:
>
>> On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach  wrote:
>>
>>> I don't think sqlite (or any SQL database, for that matter) is a perfect
>>> fit for a logger, because there is a certain amount of write overhead.
>>> Why do you think you would want to do this?
>>>
>> ALL db insertions in a db are, in effect, some form of logging. In embedded
>> apps with no stdout/stderr (e.g. WinCE) using sqlite as a logging
>> destination can be quite useful (and easy to set up).
> Yes and no: logging is an (append-only) write to an already opened file or 
> network socket, and no indexes need to be updated. While inserting a document 
> into a database needs to fiddle with internal database structures, which is 
> less performant than just writing a few bytes to an already handle.
>
> Of course, constraints on an embedded device are different than, say, on a 
> Unix server, and logging to a database is easy to set up, especially if the 
> database is already there :). In other scenarios file system logging 
> generally wins, and not only performance wise, but because there are plenty 
> of tools to work with those; unless, of course, there is a specific need to 
> use a database.
>
> /eno
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Hi Stephan,

Thanks for this, much appreciated. My application is written in pure C, 
so I guess I will not be able to use your C++ code?

Chat later

Lynton

On 10/05/2011 12:06, Stephan Beal wrote:
> On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach  wrote:
>
>> I don't think sqlite (or any SQL database, for that matter) is a perfect
>> fit for a logger, because there is a certain amount of write overhead.
>> Why do you think you would want to do this?
>>
> ALL db insertions in a db are, in effect, some form of logging. In embedded
> apps with no stdout/stderr (e.g. WinCE) using sqlite as a logging
> destination can be quite useful (and easy to set up).
>
> @Lynton: i've got some C++ code implementing such a logger here:
> http://fossil.wanderinghorse.net/repos/sqlite-stuff/index.cgi/finfo?name=sq3/sq3_log_db.hpp
> http://fossil.wanderinghorse.net/repos/sqlite-stuff/index.cgi/finfo?name=sq3/sq3_log_db.cpp
>
> that might be useful as a basis (e.g. it includes a printf-like log()
> routine and a trim() function to remove all but the last N entries).
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Hi Enrico,

Well I have been looking at using a nice light weight C logger.and 
played around with LOG4C, Panthious have and also checked out using 
syslog-ng, matlog and standard syslogbut in the end decided that for 
this specific application an SQLite logger would work well.

I am already using SQLIte as a queue so the administrators are already 
comfortable using the SQLite command line ;-)

Lynton

On 10/05/2011 11:52, Enrico Thierbach wrote:
> Hi Lynton,
>
> I don't think sqlite (or any SQL database, for that matter) is a perfect fit 
> for a logger, because there is a certain amount of write overhead.
> Why do you think you would want to do this?
>
> /eno
>
> On 10.05.2011, at 10:09, Lynton Grice wrote:
>
>>   Hi there,
>>
>> SQLite is a perfect fit for a logger, the only question I have is once
>> it is in production my database will grow rapidly, how can I implement /
>> mimic a type of "rotating log"?
>>
>> So in my mind I am thinking that perhaps I can LIMIT the size of the
>> SQLIte DB to say 5 MB? And once the DB reaches that size it starts
>> INSERTING new logs over the earliest records in the database?
>>
>> Is this possible?
>>
>> Thanks for the help
>>
>> Lynton
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Enrico Thierbach]

On 10.05.2011, at 12:06, Stephan Beal wrote:

> On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach  wrote:
> 
>> I don't think sqlite (or any SQL database, for that matter) is a perfect
>> fit for a logger, because there is a certain amount of write overhead.
>> Why do you think you would want to do this?
>> 
> 
> ALL db insertions in a db are, in effect, some form of logging. In embedded
> apps with no stdout/stderr (e.g. WinCE) using sqlite as a logging
> destination can be quite useful (and easy to set up).

Yes and no: logging is an (append-only) write to an already opened file or 
network socket, and no indexes need to be updated. While inserting a document 
into a database needs to fiddle with internal database structures, which is 
less performant than just writing a few bytes to an already handle.

Of course, constraints on an embedded device are different than, say, on a Unix 
server, and logging to a database is easy to set up, especially if the database 
is already there :). In other scenarios file system logging generally wins, and 
not only performance wise, but because there are plenty of tools to work with 
those; unless, of course, there is a specific need to use a database.

/eno


 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Stephan Beal]

On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach  wrote:

> I don't think sqlite (or any SQL database, for that matter) is a perfect
> fit for a logger, because there is a certain amount of write overhead.
> Why do you think you would want to do this?
>

ALL db insertions in a db are, in effect, some form of logging. In embedded
apps with no stdout/stderr (e.g. WinCE) using sqlite as a logging
destination can be quite useful (and easy to set up).

@Lynton: i've got some C++ code implementing such a logger here:
http://fossil.wanderinghorse.net/repos/sqlite-stuff/index.cgi/finfo?name=sq3/sq3_log_db.hpp
http://fossil.wanderinghorse.net/repos/sqlite-stuff/index.cgi/finfo?name=sq3/sq3_log_db.cpp

that might be useful as a basis (e.g. it includes a printf-like log()
routine and a trim() function to remove all but the last N entries).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Enrico Thierbach]

Hi Lynton,

I don't think sqlite (or any SQL database, for that matter) is a perfect fit 
for a logger, because there is a certain amount of write overhead. 
Why do you think you would want to do this?

/eno

On 10.05.2011, at 10:09, Lynton Grice wrote:

>  Hi there,
> 
> SQLite is a perfect fit for a logger, the only question I have is once 
> it is in production my database will grow rapidly, how can I implement / 
> mimic a type of "rotating log"?
> 
> So in my mind I am thinking that perhaps I can LIMIT the size of the 
> SQLIte DB to say 5 MB? And once the DB reaches that size it starts 
> INSERTING new logs over the earliest records in the database?
> 
> Is this possible?
> 
> Thanks for the help
> 
> Lynton
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Stephan Beal]

On Tue, May 10, 2011 at 10:09 AM, Lynton Grice
wrote:

> So in my mind I am thinking that perhaps I can LIMIT the size of the
> SQLIte DB to say 5 MB? And once the DB reaches that size it starts
> INSERTING new logs over the earliest records in the database?
>
> Is this possible?


The API doesn't provide a way to do this, but i have done the following in
the past... keep a counter in the log function, and every (say) 100 calls,
run a DELETE to clean up what you want. In my case i simply got the ID of
the last-inserted log entry and ran DELETE ... WHERE ID<(the_last_id-100).

Not a perfect solution, but for simple apps it suffices.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Lynton Grice]

  Hi there,

SQLite is a perfect fit for a logger, the only question I have is once 
it is in production my database will grow rapidly, how can I implement / 
mimic a type of "rotating log"?

So in my mind I am thinking that perhaps I can LIMIT the size of the 
SQLIte DB to say 5 MB? And once the DB reaches that size it starts 
INSERTING new logs over the earliest records in the database?

Is this possible?

Thanks for the help

Lynton
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users