[sqlite] seeking database design advice

[Rich Rattanni]

All:

I wanted to solicit some opinions about how best to layout a database
table for a project I am working on.  I have a distributed application
that reports run time information back to a central machine.  Included
in the report are the parameters used during runtime... approx 25 or
so.  Parameters are site specific and may be changed locally by
whomever runs the application.  I have a table that records an
application instance (columns such as location, version etc), and a
table that records report instances (time, date, size).  I wanted to
be able to query at a glance the most recent parameter set in use, so
I came up with the following table design...

Design A)
primaryKey | appInstanceId | reportId | param1 | param2 | ... | param25

I spoke to a few people at work, and they all say the same thing...
"this isn't normalized'.  I get the following suggestion:

Design B)
primaryKey | appInstanceId | reportId |paramNum |paramValue
---with a table second table--
paramNum | paramDescription

The reason for Design B:
   New parameters are easily added (no schema change)
   It is "Better Design" (throwing around the word normalization a lot)

My reasons for Design A:
   1:1 record to report (not 1:25 records)
   meta data overhead per row is 1/9 (opposed to 4/1)

I'm not a database guru, so I can't fight back...but I feel I am
hearing a line straight from a textbook.  Critics of my design simply
tells me it is wrong and keep repeating the mantra "It is not
normalized".  The way I see it the column itself describes the data,
which means less data is stored... efficient and simple.
I do see the appeal of Design A by effectively 'future-proofing' the
database table.  My application performing the inserts should not have
to change, I just always insert the parameters I find in each report.

I should add that I do expect future parameters to be added (maybe
5,10 more at most, but rather infrequently).  Old and new versions
will co-exist, but eventually all application instances should be
updated.  With Design A I would add new columns and set existing
records to NULL.  I would expect 100,000 reports a month or so.  When
I select from parameters I generally will show all parameters, so my
queries become "select * from tbl where appid=x and reportId=y" with 1
result set necessary (not iterating over 25 results).

I realize design A may not be "best", but I would prefer an friendly
answer and not a canned response like I get from co-workers.

Thanks in advance,
Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] vacuum and rowids

[Rich Rattanni]

> "You can access the ROWID of an SQLite table using one the special column 
> names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column 
> to use one of those special names, then the use of that name will refer to 
> the declared column not to the internal ROWID."
>
> which suggests that referring to rowids is fine.

It does not suggest referring to ROWIDs is fine, it only says that it
can be done.  I think Pavel's point is that referencing ROWIDs is bad
practice, so that is why he says you shouldn't do it.

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

Re: [sqlite] SQLite handling of EINTR

[Rich Rattanni]

That would be it.  Thanks Roger.  Next time I will update first and
ask questions later.

--
Rich

On Thu, Apr 21, 2011 at 5:55 PM, Roger Binns <rog...@rogerbinns.com> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 04/21/2011 02:24 PM, Rich Rattanni wrote:
>> The result was errno 4, which according to
>> my flavor of Linux is EINTR.
>
> I suggest looking into the source 3.7.6 (more recent than your version) and
> find EINTR.  It looks like your issue is addressed.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk2wp7QACgkQmOOfHg372QTQFACgji9ECCmRHl/xlnTbO186GnAi
> YtMAoKAjanGmivqWAIUTRH9MpZgsDjGQ
> =CJ0O
> -END PGP SIGNATURE-
> ___
> 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] SQLite handling of EINTR

[Rich Rattanni]

I have been tracing the source of a low-occurrence anomaly in my C#
application, running on Linux 2.6 under Mono.  My application is using
Robert Simpson's SQLite .NET adapter + SQLite 3.7.5.  After I resolved
my own bug, which prevented me from seeing the exception thrown by
System.Data.SQLite, I found out that SQLite was bailing due to
SQLITE_IOERR.  I recompiled SQLite to make extended result codes on by
default (not sure if I could do this from System.Data.SQLite).  The
extended result code was consistently SQLITE_IOERR_WRITE.  I found 2
placed in the source that returned this error on the UNIX system:
fcntlSizeHint and unixWrite.  A few lines of code later, I narrowed it
down to unixWrite.  So as a quick patch I modified the return
statement from (SQLITE_IOERR_WRITE) to (SQLITE_IOERR_WRITE |
(pFile->lastErrno << 16)).  The result was errno 4, which according to
my flavor of Linux is EINTR.

So, I wanted to ask if my analysis seem correct?  If write() is
interrupted by a signal, then an SQLITE_IOERR is generated.

With that being said, would I be out of line to suggest that this is
an oversight?  Would it be acceptable to ask SQLite to detect EINTR
and retry the write?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building managed only System.Data.SQLite

[Rich Rattanni]

Shane:

I downloaded http://system.data.sqlite.org/sqlite-dotnetsrc-1006900.zip
and changed SQLite.NET.Settings.targets as per your recommendation.
It did not appear to solve my problem since my application threw an
exception stating that Sqlite.Interop was missing.  To be complete I
tried 3 permutations of the two options UseInteropDll and
UseSqliteStandard ( I did not try false, false).

All resulting System.Data.SQLite.DLLs threw exceptions due to a
missing InterOp DLL.  I pulled down a copy of System.Data.SQLite
1.0.66.0 and built the Managed Only version, and it appeared to work.

I will mess around some more with the project to see if I can build
the managed only version.  I was hoping to get my hands on the latest
so I was not fighting issues that may have been fixed by a release.
Thank you again for trying to help, rest assured it was appreciated.

--
Rich

On Tue, Apr 19, 2011 at 5:36 PM, Rich Rattanni <ratta...@gmail.com> wrote:
> Daniel:
>
> I have not tried Csharp-sqlite, it looks interesting but I do not know
> if that is right for me at this moment.
>
> Shane:
>
> I will try what you recommend tomorrow, thank you.
>
> --
> Rich
>
> On Tue, Apr 19, 2011 at 4:47 PM, Shane Harrelson
> <shane.harrel...@gmail.com> wrote:
>> The target build settings can be controlled from SQLite.NET.Settings.targets
>> - in particular, you should probably look at UseInteropDll and
>> UseSqliteStandard.
>> To override the USE_INTEROP_DLL setting, try copying
>> SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the
>> settings changes there.
>> This should work with VS2008 and VS2010.
>>
>> HTH.
>> -Shane
>>
>>
>> On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni <ratta...@gmail.com> wrote:
>>
>>> I was wondering if anyone has had any luck building the Managed-Only
>>> System.Data.SQLite .NET adapter for SQLite from the source provided at
>>> system.data.sqlite.org?  I downloaded the pre-built binaries but they
>>> appear to rely on the InterOp assembly.  My current project is running
>>> under Linux 2.6 on an ARM processor, and uses managed-only copy of the
>>> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
>>> I am looking to do some bug tracing / upgrading so I would like to
>>> build my own copy from source.
>>>
>>> --
>>> Rich
>>> ___
>>> 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] Building managed only System.Data.SQLite

[Rich Rattanni]

Daniel:

I have not tried Csharp-sqlite, it looks interesting but I do not know
if that is right for me at this moment.

Shane:

I will try what you recommend tomorrow, thank you.

--
Rich

On Tue, Apr 19, 2011 at 4:47 PM, Shane Harrelson
<shane.harrel...@gmail.com> wrote:
> The target build settings can be controlled from SQLite.NET.Settings.targets
> - in particular, you should probably look at UseInteropDll and
> UseSqliteStandard.
> To override the USE_INTEROP_DLL setting, try copying
> SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the
> settings changes there.
> This should work with VS2008 and VS2010.
>
> HTH.
> -Shane
>
>
> On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni <ratta...@gmail.com> wrote:
>
>> I was wondering if anyone has had any luck building the Managed-Only
>> System.Data.SQLite .NET adapter for SQLite from the source provided at
>> system.data.sqlite.org?  I downloaded the pre-built binaries but they
>> appear to rely on the InterOp assembly.  My current project is running
>> under Linux 2.6 on an ARM processor, and uses managed-only copy of the
>> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
>> I am looking to do some bug tracing / upgrading so I would like to
>> build my own copy from source.
>>
>> --
>> Rich
>> ___
>> 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] Building managed only System.Data.SQLite

[Rich Rattanni]

I was wondering if anyone has had any luck building the Managed-Only
System.Data.SQLite .NET adapter for SQLite from the source provided at
system.data.sqlite.org?  I downloaded the pre-built binaries but they
appear to rely on the InterOp assembly.  My current project is running
under Linux 2.6 on an ARM processor, and uses managed-only copy of the
System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
I am looking to do some bug tracing / upgrading so I would like to
build my own copy from source.

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

Re: [sqlite] Insert while select

[Rich Rattanni]

I was way off on the version.  We are using 3.3.17.  Sorry to bother
you about behavior from Apr 25, 2007.  I am simply curious to
understand this behavior.

On Wed, Jan 26, 2011 at 1:33 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Wed, Jan 26, 2011 at 1:30 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
>
>> On 1/26/2011 11:09 AM, Rich Rattanni wrote:
>> > I am helping someone write an application that utilizes SQLite.  The
>> > following code is giving us grief (sqlite lib version 3.5.ish -
>> > Windows XP):
>> >
>> > sqlite3_prepare("select some rows")
>> > while (sqlite3_step() == SQLITE_ROW)
>> > {
>> >      // Do some calc on multiple rows, and occasinally
>> >      sqlite3_exec("Insert calculated data into the same table from
>> > which we are reading")
>> > }
>>
>> If I recall correctly, this is not allowed in SQLite 3.5.*. This became
>> supported in 3.6.x (for some x I don't remember at the moment).
>>
>> > I had a look at http://www.sqlite.org/lockingv3.html.
>>
>> The documentation describes the current behavior. Older versions often
>> behave differently. 3.5 series are 2.5 years old, a lot of progress has
>> been made since then.
>>
>
> 2.5 calendar years equals 17.5 internet years, right?  :-)
>
>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Insert while select

[Rich Rattanni]

I am helping someone write an application that utilizes SQLite.  The
following code is giving us grief (sqlite lib version 3.5.ish -
Windows XP):

sqlite3_prepare("select some rows")
while (sqlite3_step() == SQLITE_ROW)
{
// Do some calc on multiple rows, and occasinally
sqlite3_exec("Insert calculated data into the same table from
which we are reading")
}
sqlite3_finalize()

I had a look at http://www.sqlite.org/lockingv3.html.  After a quick
read, I would think that the prepare or step put a SHARED lock on the
database.
Now it is sqlite3_exec's turn to run, so he may acquire at most a
PENDING lock?  I would come to this conclusion since the select is
still active
so the SHARED lock exists.  Then the sqlite3_finalize() allows the
SHARED lock to be released, which then allows the PENDING to promote
to
EXCLUSIVE and the data to finally commit?

I am kind of throwing out my analysis and looking for a yes or no.  My
final statement for which I request validation... So unless I wrap my
data
in explicit begin/commits the data is not, in fact guaranteed to be
safely on disk?  If my program were to crash before the
sqlite3_finalize then
is the data from the selects lost?

One last question:  The locking documentation says that at some time
some cache may fill up which then causes the process to wish to commit
data.
If the above while loop generated enough insert statements to fill up
this cache, would the this loop deadlock?  A better way to ask this:
Is nesting a
insert statement in a select loop dangerous?

Thanks for taking the time to read this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database unreadable after WAL conversion on system w/o shared mem support

[Rich Rattanni]

I recently tried out SQLite 3.7.0 on an ARM project board running
linux 2.6.  I did not realize that jffs2 lacked shared memory support,
so when I opened my database file using the sqlite3 utility and typed
'pragma journal_mode=wal' it appeared as though the conversion worked
(response: wal) but further commands returned "Error: disk I/O error".
 Now I am unable to open the sqlite database file.  I would call it a
nuisance that SQLite cause a database file to be unusable if said file
is residing on a file system that lacks shared memory support.
Fortunately for me I have backups.

May I put forth the suggestion that the SQLite library be modified to
gracefully recover from the lack of mmap support?  I have always
wanted to try my hand at hacking sqlite, so maybe I will take this as
the opportunity.

I cannot change my file system (at least not yet) so as a work around
if I symlink-ed (database)-shm to a filesystem that supported mmap,
then would that allow me to evaluate SQLite with WAL mode on my
project board.  Note: this is not a permanent solution, please do not
respond with 'yes but why would you want to?'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Rich Rattanni]

> That is, other than polling, .

Yep, that trick.  (Why does everyone dismiss polling...don't we have
any embedded programmers here?  Hey Windows CE guy, don't raise your
hand...)

Roger summed it up, and labeled it correctly (crappy).  But if it
works sufficiently for him, why not explore it?

Arno:  If you are still reading this, please let us know what solution
you eventually took.  I like to see closure on these threads, and
perhaps we will learn something from you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Rich Rattanni]

The creator of SQLite actually gave a talk about using an SQLite
database as a means for IPC (it was available on youtube, maybe you
can find it).  If you want an 'sqlit-ish' way, why not use that trick?

One advantage of using SQLite is that debugging / backtracing becomes
easier.  Since your messages are passed through a database, you can
easily backtrace IPC calls (by perhaps setting a 'complete' field
instead of deleting a IPC message) and you can inject IPC messages
easily by using the SQLite CLI.


On Tue, Jun 15, 2010 at 5:01 PM, Igor Tandetnik  wrote:
> arno  wrote:
>> I have two processes connecting to a database. Both can modify database. When
>> some process modify database, I want the other to be notified (so, it can
>> reload data).
>> Currently, I use unix sockets, so a process can notify all listener process
>> that something has changed. But I wonder if there's a better way to achieve
>> that.
>> I've tried to a create a custom function (with sqlite3_create_function), and
>> use triggers. But unfortunately, my trigger was executed for the modifying
>> process.
>
> SQLite is not an interprocess communication mechanism. Any notification of 
> the sort you envision must be done outside of SQLite.
> --
> Igor Tandetnik
>
>
> ___
> 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] [lcc32] "sqlite3.c:6597 Character constant taken as not signed"

[Rich Rattanni]

BloodShed Dev-C++ and ming?  My choice for windows development.

On Fri, May 21, 2010 at 11:01 AM, Richard Hipp  wrote:
> On Fri, May 21, 2010 at 7:43 AM, Gilles Ganault wrote:
>
>>
>> But when hitting "Compiler > Make" in the Wedit IDE, I get the
>> following errors:
>> =
>> Error c:\lcc\projects\sqlite\sqlite3.c 12462 Compiler error (trap).
>> Stopping compilation
>> =
>>
>
> This appears to be a bug in your compiler.  The compiler is segfaulting.
> Can you use a different compiler?
>
> --
> -
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] journaling across multiple databases on read only filesystems

[Rich Rattanni]

I got a quick question.  I have two databases, one resides on a read
only file system, the other is on a writable file system.  I routinely
copy from the read only copy to the other.  I did not realize, until
today, that I do not have the protection of transaction support since
one database is read only.  So, besides moving the database to a
read-write partition, is there anything I can do via the API or a
pragma to operate WITH transaction support within my current file
layout?

My (pseudo)code... (app is programmed in C)

Open(read-write db)
attach (read only db)
execute(begin transaction)

loop
   execute(stuff)
end loop

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

Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key

[Rich Rattanni]

Understood.  Thank you for discussing this with me.  Your help is
greatly appreciated.

--
Rich



On Thu, Dec 3, 2009 at 5:03 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> Can I ask a follow up question?  "from foo,
>> bar"... is this behaving like a join?  I saw this before and didn't
>> quite understand it, I just realized it permuted all rows of the each
>> table together (which sounds like a join with no conditions).
>
> Yes, it is join. What you seem to talk about is a cartesian product
> and this would be it if it wasn't WHERE clause. With WHERE clause it
> is inner join - SQLite gets smaller of two tables (foo and bar) and
> for each row in it scans another table for rows satisfying WHERE
> condition. And this behavior is the same as for your query if your foo
> table is smaller than bar. But in case if you create index on (col2,
> col3) in biggest of the tables foo or bar then my query will work an
> order of magnitude faster than yours.
>
>
> Pavel
>
> On Thu, Dec 3, 2009 at 4:54 PM, Rich Rattanni <ratta...@gmail.com> wrote:
>> Awesome!  Thank you.  Can I ask a follow up question?  "from foo,
>> bar"... is this behaving like a join?  I saw this before and didn't
>> quite understand it, I just realized it permuted all rows of the each
>> table together (which sounds like a join with no conditions).
>>
>> Second, I came up with my own solution:
>>
>> select * from foo
>> where col1 || col2 in (select col1 || col2 from bar)
>>
>> This also works, but from an efficiency standpoint I assume this is
>> horrible since it would basically be a series of string compare
>> operations.  However, if the number of rows in each table is VERY
>> LARGE (lets say 50,000) would my solution maybe outperform the first
>> (on the surface seems like n^2 vs n*S where S is concat string length
>> (which will always be < 50)).
>>
>> On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>>> Maybe
>>>
>>> select distinct bar.* from foo, bar
>>> where foo.col2 = bar.col2 and foo.col3 = bar.col3
>>>
>>> It's not clear from your requirements written below whether you need
>>> 'distinct' here or not but I've added it just in case...
>>>
>>>
>>> Pavel
>>>
>>> On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni <ratta...@gmail.com> wrote:
>>>> Suppose I have the following two tables
>>>>
>>>> foo:
>>>> 10 | A  | A
>>>> 20 | B  | B
>>>> 30 | C  | C
>>>>
>>>> and
>>>>
>>>> bar:
>>>> 1 | A | X
>>>> 2 | B | C
>>>> 3 | A | A
>>>> 4 | C | A
>>>> 5 | B | B
>>>>
>>>>
>>>> I want to select all the rows in table bar where the second and third
>>>> column match an entry found in foo (that is to say, I want my result
>>>> to be 3 | A | A and 5 | B | B.
>>>>
>>>> My attempt of:
>>>> select * from bar
>>>> where col2 in (select col2 from foo where bar.col2 = col2)
>>>>        and col3 in (select col3 from foo where bar.col3 = col3)
>>>>
>>>> Does not work, and I understand that is should not (it returns 2 | B |
>>>> C, 3 | A | A, 4 | C | A, 5 | B | B).
>>>>
>>>> Could someone give me a hand?
>>>>
>>>> --
>>>> Rich
>>>> ___
>>>> 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-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] Selecting unique entries from one table with multiple columns forming a unique key

[Rich Rattanni]

Awesome!  Thank you.  Can I ask a follow up question?  "from foo,
bar"... is this behaving like a join?  I saw this before and didn't
quite understand it, I just realized it permuted all rows of the each
table together (which sounds like a join with no conditions).

Second, I came up with my own solution:

select * from foo
where col1 || col2 in (select col1 || col2 from bar)

This also works, but from an efficiency standpoint I assume this is
horrible since it would basically be a series of string compare
operations.  However, if the number of rows in each table is VERY
LARGE (lets say 50,000) would my solution maybe outperform the first
(on the surface seems like n^2 vs n*S where S is concat string length
(which will always be < 50)).

On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> Maybe
>
> select distinct bar.* from foo, bar
> where foo.col2 = bar.col2 and foo.col3 = bar.col3
>
> It's not clear from your requirements written below whether you need
> 'distinct' here or not but I've added it just in case...
>
>
> Pavel
>
> On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni <ratta...@gmail.com> wrote:
>> Suppose I have the following two tables
>>
>> foo:
>> 10 | A  | A
>> 20 | B  | B
>> 30 | C  | C
>>
>> and
>>
>> bar:
>> 1 | A | X
>> 2 | B | C
>> 3 | A | A
>> 4 | C | A
>> 5 | B | B
>>
>>
>> I want to select all the rows in table bar where the second and third
>> column match an entry found in foo (that is to say, I want my result
>> to be 3 | A | A and 5 | B | B.
>>
>> My attempt of:
>> select * from bar
>> where col2 in (select col2 from foo where bar.col2 = col2)
>>        and col3 in (select col3 from foo where bar.col3 = col3)
>>
>> Does not work, and I understand that is should not (it returns 2 | B |
>> C, 3 | A | A, 4 | C | A, 5 | B | B).
>>
>> Could someone give me a hand?
>>
>> --
>> Rich
>> ___
>> 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] Selecting unique entries from one table with multiple columns forming a unique key

[Rich Rattanni]

Suppose I have the following two tables

foo:
10 | A  | A
20 | B  | B
30 | C  | C

and

bar:
1 | A | X
2 | B | C
3 | A | A
4 | C | A
5 | B | B


I want to select all the rows in table bar where the second and third
column match an entry found in foo (that is to say, I want my result
to be 3 | A | A and 5 | B | B.

My attempt of:
select * from bar
where col2 in (select col2 from foo where bar.col2 = col2)
and col3 in (select col3 from foo where bar.col3 = col3)

Does not work, and I understand that is should not (it returns 2 | B |
C, 3 | A | A, 4 | C | A, 5 | B | B).

Could someone give me a hand?

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

[sqlite] accessing bound variables in trigger

[Rich Rattanni]

I have an application that performs the following SQL action:

UPDATE table SET value = date('now', ?1, 'localtime') WHERE id = ?2

In my code I bind an integer to ?1.  This results in null.  I never intended
to code it that way, it is an honest to goodness bug.  I wanted to bind
'+1 days' or something similar.  However, I wanted
to know if I could cheat.

Can a trigger reference the bound variables?  I figure the answer is NO.
I realize exposing bound variables to a trigger is silly for many reasons,
but I figured I would ask the experts.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupting sqlite

[Rich Rattanni]

Would forking another process as a worker process be acceptable, then
in your main message loop wait for some IPC signal saying it is done?
Unless you are doing this on some extremely lightweight OS / monitor
that doesn't implement the concept of time-sharing, I can't see how
this would be hard to do.

On Mon, Jun 22, 2009 at 8:23 PM, João Eiras wrote:
> On , Dan  wrote:
>
>>> My doubt is the following: if from the progress callback (set with
>>> sqlite3_progress_handler) I return non 0 and therefore I get
>>> SQLITE_INTERRUPT from the call to sqlite3_step, is the sqlite3_stmt
>>> object still in a valid state and will the query resume normally if I
>>> pass the same sqlite3_stmt object back to sqlite3_step again ?
>>
>> No. Next call on the statement handle should be sqlite3_reset() or
>> sqlite3_finalize().
>>
>
> I have tested it and I clearly confirm your comment :)
>
> On , Roger Binns  wrote:
>
>> Why don't you call the main message loop from within the progress
>> handler callback?
>>
>
> Unfortunately, it's not that simple. The message loop loops both UI messages 
> and customs messages internal to the application. For instance, I keep track 
> of a sql statement in a object. The statement evolves after that object being 
> affected a few times by execution messages. This way everything has their 
> share of CPU and the ui works, while not relying on threading, because 
> threading would require architectural and design changes in the application.
> However, this is also one of the downfalls. I can have an arbitrary amount of 
> sqlite databases open, and if I run the message loop inside the progress 
> handler, I can theoretically have infinite reentrancy, where one progress 
> handler executes something on a different database, being in practice limited 
> by stack size, which would cause a crash.
> Also, requesting the message loop to run has its performance penalty, so it 
> lags the query a bit more than what's ideal.
>
> Thank you for those suggestions, but I have though of many other options, 
> those included.
>
>
> So, today I was looking around the sqlite code and try to make this a 
> reality, which is after interrupting a statement with a progress callback, 
> leaving the sqlite3_stmt object in a state that can be resumed with another 
> sqlite3_step call.
> It turned out a few lines of code fix, but however it had a side effect. 
> Internally, sqlite uses sqlite3_exec function and editing the code around 
> those calls to store state to allow sqlite3_exec to be suspendable as well is 
> not trivial nor simple. But I'm not using sqlite3_exec on my application, so 
> I just editied this function on my local tree to set the progress handler to 
> null and restore it in the end.
>
> I code I needed to change to allow sqlite3_step to resume was just the 
> following in file vdbe.c around line 5285, in function sqlite3VdbeExec:
>
>   vdbe_error_halt:
>     assert( rc );
>     p->rc = rc;
> +   if (rc != SQLITE_INTERRUPT){
>       sqlite3VdbeHalt(p);
> -
> +   }
> +   else
> +   {
> +       p->nCallback++;
> +       p->pc = pc;
> +   }
>     if( rc==SQLITE_IOERR_NOMEM ) db->mallocFailed = 1;
>     rc = SQLITE_ERROR;
>
>
> This is probably not enough, and could even had some side effects, none of 
> which I encountered, because I have my own test suite for my own cases.
>
> The question then becomes, would sqlite be able to support such thing in the 
> future (suspending/resuming) ?
>
> Thank you for your attention.
> ___
> 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] Simple example for dummy user writing C code

[Rich Rattanni]

Oops, hit send to soon.  Your second question has been discussed in
the thread "IP from number with SQL" started on Sun, Mar 15, 2009 at
4:10 PM.  Of course with the knowledge that IPv6 is just IPv4 with
more bits thrown at it, you can tweak the discussion to suit your
needs.

On Thu, Apr 2, 2009 at 8:54 AM, Rich Rattanni <ratta...@gmail.com> wrote:
> Igor, be careful your not solving someone's homework
>
> On Thu, Apr 2, 2009 at 7:39 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
>> "My Name" <mylistuser1...@gmail.com>
>> wrote in message
>> news:ee8102080904012149h3b8d64d9u8b972b1e6fbbf...@mail.gmail.com
>>> I'm having hard time to store and retrieve data with SQLite. Let's
>>> assume I have this structure in my C code to hold my data
>>>
>>> struct foo {
>>> long a;
>>> float b;
>>> char c[1024];
>>> int d;
>>> }
>>>
>>> so the SQL definition would be
>>>
>>> CREATE TABLE foo
>>> (
>>> a LONG;
>>> b FLOAT;
>>> c VARCHAR(1024);
>>> d INT;
>>> );
>>>
>>> In real life c[1024] does not hold a printable string but variable
>>> length binary data and d tells the data length.
>>
>> You probably want to store it as a BLOB then, not as text. You don't
>> need a separate column for d - a BLOB column knows its length (and so
>> does text, so you don't need extra column either way).
>>
>>> Let's also assume I
>>> have N records where some of the fields can be same.
>>>
>>> { 1, 1.0, "data1", 5 }
>>> { 1, 2.0, "data2", 5 }
>>> { 2, 1.0, "data3", 5 }
>>> { 2, 2.0, "data4", 5 }
>>> { 5, 6.0, "data5", 5 }
>>>
>>> And here's the "dummy user" part, how should I read from and write to
>>> the database? I want to execute
>>>
>>> DELETE FROM foo WHERE b < ...
>>> INSERT INTO foo VALUE (..)
>>> SELECT * FROM foo WHERE a=... AND b=...
>>> SELECT c,d FROM foo WHERE a=... AND b=...
>>
>> sqlite3* db = NULL;
>> sqlite3_open("myfile.db", );
>>
>> sqlite3_stmt* stmt = NULL;
>> sqlite3_prepare_v2(db, "select * from foo where a=? and b=?;", ,
>> NULL);
>>
>> sqlite3_bind_int(stmt, 1, 42);
>> sqlite3_bind_double(stmt, 2, 4.2);
>>
>> while (sqlite3_step(stmt) == SQLITE_ROW) {
>>  foo row;
>>  row.a = sqlite3_column_int(stmt, 0);
>>  row.b = sqlite3_column_double(stmt, 1);
>>  row.d = sqlite3_column_bytes(stmt, 2);
>>  assert(row.d <= sizeof(row.c));
>>  memcpy(row.c, sqlite3_column_blob(stmt, 2), row.d);
>>
>>  // do something with row
>> }
>>
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>>
>>
>> DELETE and INSERT are left as an exercise for the reader. They work the
>> same way, except that you only need to call sqlite3_step once, and of
>> course there are no column values to retrieve.
>>
>> Igor Tandetnik
>>
>>
>>
>> ___
>> 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] Simple example for dummy user writing C code

[Rich Rattanni]

Igor, be careful your not solving someone's homework

On Thu, Apr 2, 2009 at 7:39 AM, Igor Tandetnik  wrote:
> "My Name" 
> wrote in message
> news:ee8102080904012149h3b8d64d9u8b972b1e6fbbf...@mail.gmail.com
>> I'm having hard time to store and retrieve data with SQLite. Let's
>> assume I have this structure in my C code to hold my data
>>
>> struct foo {
>> long a;
>> float b;
>> char c[1024];
>> int d;
>> }
>>
>> so the SQL definition would be
>>
>> CREATE TABLE foo
>> (
>> a LONG;
>> b FLOAT;
>> c VARCHAR(1024);
>> d INT;
>> );
>>
>> In real life c[1024] does not hold a printable string but variable
>> length binary data and d tells the data length.
>
> You probably want to store it as a BLOB then, not as text. You don't
> need a separate column for d - a BLOB column knows its length (and so
> does text, so you don't need extra column either way).
>
>> Let's also assume I
>> have N records where some of the fields can be same.
>>
>> { 1, 1.0, "data1", 5 }
>> { 1, 2.0, "data2", 5 }
>> { 2, 1.0, "data3", 5 }
>> { 2, 2.0, "data4", 5 }
>> { 5, 6.0, "data5", 5 }
>>
>> And here's the "dummy user" part, how should I read from and write to
>> the database? I want to execute
>>
>> DELETE FROM foo WHERE b < ...
>> INSERT INTO foo VALUE (..)
>> SELECT * FROM foo WHERE a=... AND b=...
>> SELECT c,d FROM foo WHERE a=... AND b=...
>
> sqlite3* db = NULL;
> sqlite3_open("myfile.db", );
>
> sqlite3_stmt* stmt = NULL;
> sqlite3_prepare_v2(db, "select * from foo where a=? and b=?;", ,
> NULL);
>
> sqlite3_bind_int(stmt, 1, 42);
> sqlite3_bind_double(stmt, 2, 4.2);
>
> while (sqlite3_step(stmt) == SQLITE_ROW) {
>  foo row;
>  row.a = sqlite3_column_int(stmt, 0);
>  row.b = sqlite3_column_double(stmt, 1);
>  row.d = sqlite3_column_bytes(stmt, 2);
>  assert(row.d <= sizeof(row.c));
>  memcpy(row.c, sqlite3_column_blob(stmt, 2), row.d);
>
>  // do something with row
> }
>
> sqlite3_finalize(stmt);
> sqlite3_close(db);
>
>
> DELETE and INSERT are left as an exercise for the reader. They work the
> same way, except that you only need to call sqlite3_step once, and of
> course there are no column values to retrieve.
>
> Igor Tandetnik
>
>
>
> ___
> 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] How to size and position a scrollbar within a virtual listview

[Rich Rattanni]

I thought Puneet's reply was good.  When you make statements like
query the internal btree table to request at least the internal
pagenr w... I think you are asking too much of sqlite, right?  The
purpose of a database engine is to abstract the gory details and make
your life easier, just as is with any library API you may use.  Unless
this is a extremely resource limited or real-time project, why make
your life complicated?

On Wed, Feb 25, 2009 at 4:51 AM, Mail.sqlite  wrote:
> query the internal btree table to request at least the internal pagenr w
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Google Chrome and SQLite3

[Rich Rattanni]

So after playing around in my application data directory for google
chrome, I noticed file called something-journal.  Of course, I knew
what that was.  So I began opening all kinds of SQLite databases in
use by Chrome (had to close chrome due to locks on a few of them).
Interesting the things chrome tracks.  For instance it actually
records, for each site you go to, how many times you manually type it
in (or so I assume).  I wanted to ask anyone if they have done any
cool data mining / reports on their surfing habits, or any neat hacks
to Chrome with respect to sqlite?

Interestingly enough, Chrome, to the best of my knowledge, was sitting
there idle and yet I had a journal file.  I know Chrome brags about
being so gosh-darned impervious to two different web sessions
interfering with one another's CPU time and memory resources, but it
hangs A LOT for me (forget reading a pdf).  The presence of a journal
file makes me wonder if sqlite may be a bottle neck. (Stop right
there... I am not saying the bottle neck is sqlite's fault... No need
to flame me please)

I'm just excited to spot sqlite in the field.  I searched for any
mention of "chrome" in the mailing list and didn't see any, so I just
wanted to maybe start a small symposium on sqlite and it's integration
into chrome.

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

Re: [sqlite] validate SQL Statement

[Rich Rattanni]

>From the manual, doesnt sqlite3_prepare do the following: "To execute
an SQL query, it must first be compiled into a byte-code program using
one of these routines."  If you are really paranoid, what about taking
the input SQL statement x and then verifying it by issuing:
sqlite3_prepare("EXPLAIN x")?  That way I just tried "EXPLAIN SELECT
id1 FROM myTable" where table 'myTable' contains no column 'id1' and
it informed me of my error.

On Thu, Jul 3, 2008 at 10:23 AM, Umaa Krishnan <[EMAIL PROTECTED]> wrote:
> Well, I assume SQLPrepare allocates and locks appropriate resources. I need 
> to only check the sanity of the statement, and then discard.
>
> So I was wondering if there was a way to do it, instead of prepare statement
>
> --- On Thu, 7/3/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> From: D. Richard Hipp <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] validate SQL Statement
> To: "General Discussion of SQLite Database" 
> Date: Thursday, July 3, 2008, 2:10 AM
>
> On Jul 2, 2008, at 11:03 PM, Umaa Krishnan wrote:
>
>> I was wondering if there a way in sqlite, wherein I could validate
>> the SQL statement (for correct grammar, resource name - column name,
>> table name etc), w/o having to do prepare.
>
>
> You speak as if sqlite3_prepare() were a huge burden - something worth
> avoiding. In practice it is usually very fast. What problem are you
> trying to solve?
>
> No, there is no other way to validate an SQL statement.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] A question about the use of localtime

[Rich Rattanni]

Matthew:
I simply called tzset() after I extract the timezone file.  It worked
like a charm.  Thank you very much!  I was having a heck of a time
information online.

On Thu, Jun 19, 2008 at 4:53 PM, Matthew L. Creech <[EMAIL PROTECTED]> wrote:
> On Thu, Jun 19, 2008 at 4:35 PM, Matthew L. Creech <[EMAIL PROTECTED]> wrote:
>>
>> I think this behavior is probably due to the way localtime() works in
>> glibc.  From what I've seen (at least on my embedded ARM-Linux board),
>> localtime() only invokes tzset() the first time it's run by an
>> application.  So if your app starts and calls localtime() before the
>> timezone is changed, you'll get times formatted according to the old
>> timezone.
>>
>
> Correction - that's what happens when localtime_r() is called;
> localtime() is guaranteed to call tzset() on each invocation.
>
> So one option here is to just disable use of localtime_r(), since
> presumably the configure script detects it and defines
> HAVE_LOCALTIME_R in config.h.
>
> --
> Matthew L. Creech
> ___
> 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] A question about the use of localtime

[Rich Rattanni]

All:

I noticed the following when using sqlite to timestamp flags in an
embedded system.  I will lay out the tests performed and the results I
got.

First, an overview.  I am working with a linux 2.6.26 kernel and
sqlite v3.5.0.  For the handling of timezones, I use the zoneinfo
files.  /etc/localtime
is a symbolic link to /var/tmp/localtime.  At boot the proper timezone
file is copied to /var/tmp/localtime (the reason for this setup is
/etc resides
in a read only partition.  I timestamp each flag using 'Insert into
flags (timestamp, FLAGDATA...) values (datetime('now','localtime),
FLAGDATA)'.
Now on to my issue.

--Scenario A--
Boot System
Update /var/localtime with correct zoneinfo file
Write Flag(s)
--
Result All time stamps match my localtime.

--Scenario B--
Boot System
Write 1 Flag
Update /var/localtime with correct zoneinfo file
Write a few flags (5-10)
Some time elapses
Write some more flags
---
Result...
The first flag ,written before /var/localtime, is written in UTC.  I
expect this since the symbolic link does not yet point to a valid
zoneinfo file.
The next few flags (5-10 depending on what the system is doing) are
also in UTC, these flags are the same flags written
in Scenario A after the zoneinfo update.
The system may briefly stop writing flags, then write some more due to
a button press or something.
Eventually the flags receive the correct timestamp for my timezone
settings.  I think, but cant say for certain, that the
timestamp becomes 'correct' after a small amount of time elapses.

So this email isn't blaming SQLite for my improper timestamps even
after I set the correct zoneinfo file.  I am just trying to
understand what is going on.  Should I assume that my observation of
'Set zoneinfo first, then use datetime functions' is
a valid fix?  Or am I fooling myself?
I just wanted to know the mechanism at work here.  Of course I do
fully acknowledge this is could be an artifact in the
GLIBC library.  If someone knows the particular reason this is
happening, even if it is GLIBC fault, I would love to know.

My last bit of information which may be relevant is all flags are
written in individual transactions.

Thanks in advance,
Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] integrity_check: Beating the system

[Rich Rattanni]

> Couldn't you go ahead and do your quick check on startup and then do the
> integrity check later when the database is otherwise unoccupied?

I was thinking of doing a hybrid as you suggested.

> If your database is smaller than system memory then there is also value
> in just reading the entire file so that it is cached by the OS which
> will cause initial queries to be a lot quicker because they won't have
> to page in things from disk.

Yea, I have heard of the old 'cat myfile > /dev/null' to get pages
cached in the OS.
I didn't include the numbers, but I tested that as well and this takes
about 9 seconds
(reading the whole file to dev/null).
I then performed both tests above and the time was slightly more than
just flat out
issuing the command.

> If you look at your timing figures you can see that the integrity check
> and md5sum are spending all their time in system - ie reading the file
> into memory.  You may find a more effective way of reading into memory
> using appropriate block sizes, asynchronous I/O etc.  You can also tweak
> SQLite block sizes to match OS block sizes.

I will look into that.  Thank you for your feedback!

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

[sqlite] integrity_check: Beating the system

[Rich Rattanni]

All:
I need to check a database for readability before my application
starts.  I was originally going to keep an MD5 on the database and
check it each time at powerup.  This seems to take a great deal of
time so instead I thought abou having the database do an integrity
check at powerup, however this too takes a great deal of time.  My
last idea was to issue a set of simple select statements against the
database and check if they are successful.  I realize this doesnt
'guarantee' my data is fine like an MD5 would, nor does it really
validate the integrity of the database like an integrity check
would but can I assume to some degree of comfort that if these
select statements succeed then I can access these tables in the
database error free?

I ran some test cases (I know you guys and gals like actual numbers
and not theory), and came up with the following
sqlite3 'pragma integrity_check'
real0m 11.20s
user0m 1.85s
sys 0m 8.70s

md5sum -c ...
real0m 10.07s
user0m 1.32s
sys 0m 8.16s

sqlite3 'SELECT STATEMENTS FROM IMPORTANT TABLES'
real0m 2.34s
user0m 0.19s
sys 0m 1.74s


Now for clarity The database contains AV metadata and data.  I
have tables such as ImageData, ImageDescription, AudioData, and so on.
 I also have tables that are not 'important' (suffice it to say, I
wont access them during runtime under normal conditions).  I care
about the AV data being accessible I am optimistically assuming
that if the data is corrupt, the image will still display (with
perhaps some bad pixels) and / or the audio has some unwanted clicks
or pops.


The big question Using a group of select statements to check the
database for accessibility seem reasonable?  Or am I taking a large
gamble doing so?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing fast database rotation

[Rich Rattanni]

Self replies sorry its kinda lame huh?  Could you add a column to
your schema such as "LOG #" or so, and do all your work in the same
table.  So if your data max limit is 3 you would have...

rowiddata   logNum
1  x   1
2  y   1
3  z   1
4  a   2
5  b   2

Just thinking out of my finger tips.



On Sun, Jun 15, 2008 at 10:20 PM, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> I am working with SQLite in an embedded environment.  With synchronous
> = full, I can say large inserts are abysmal (of course I need the
> protection that full synchronous offers).  Of course, as always what I
> call large may not be what you call large.  Keep in mind that sqlite
> will make a journal file equal to roughly the size of the data you
> will be moving.  Instead of moving the data to a backup, could you
> create a new table and start dumping data there?  You know, in your
> program remember the current table (DataLogX).  When it comes time to
> roll over the log  "CREATE TABLE DataLog(X+1) .Just one man's
> opinion.
>
>
> On Fri, Jun 13, 2008 at 5:25 AM, Al <[EMAIL PROTECTED]> wrote:
>> Hello,
>>
>> I'm using sqlite to implement a fast logging system in an embbeded system. 
>> For
>> mainly space but also performance reason, I need to rotate the databases.
>>
>> The database is queried regularly and I need to keep at least $min rows in 
>> it.
>>
>> What I plan, is inside my logging loop, to do something like this.
>>
>> while(1) {
>>read_informations_from_several_sources();
>>INSERT(informations);
>>
>>if(count > max) {
>>   /* I want to move all oldest rows in another database */
>>   BEGIN;
>>   INSERT INTO logs_backup
>>SELECT * FROM logs order by rowid limit ($max - $min);
>>
>>   DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY rowid
>>LIMIT ($max - $min));
>>   COMMIT;
>>}
>> }
>>
>> rowid is an autoincremented field.
>> I am not an sql expert, and would like to find the fastest solution to move 
>> the
>> oldest rows into another database. Am I doing silly things ? Can it be 
>> improved ?
>>
>> Thanks in advance.
>>
>> ___
>> 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] Implementing fast database rotation

[Rich Rattanni]

I am working with SQLite in an embedded environment.  With synchronous
= full, I can say large inserts are abysmal (of course I need the
protection that full synchronous offers).  Of course, as always what I
call large may not be what you call large.  Keep in mind that sqlite
will make a journal file equal to roughly the size of the data you
will be moving.  Instead of moving the data to a backup, could you
create a new table and start dumping data there?  You know, in your
program remember the current table (DataLogX).  When it comes time to
roll over the log  "CREATE TABLE DataLog(X+1) .Just one man's
opinion.


On Fri, Jun 13, 2008 at 5:25 AM, Al <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I'm using sqlite to implement a fast logging system in an embbeded system. For
> mainly space but also performance reason, I need to rotate the databases.
>
> The database is queried regularly and I need to keep at least $min rows in it.
>
> What I plan, is inside my logging loop, to do something like this.
>
> while(1) {
>read_informations_from_several_sources();
>INSERT(informations);
>
>if(count > max) {
>   /* I want to move all oldest rows in another database */
>   BEGIN;
>   INSERT INTO logs_backup
>SELECT * FROM logs order by rowid limit ($max - $min);
>
>   DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY rowid
>LIMIT ($max - $min));
>   COMMIT;
>}
> }
>
> rowid is an autoincremented field.
> I am not an sql expert, and would like to find the fastest solution to move 
> the
> oldest rows into another database. Am I doing silly things ? Can it be 
> improved ?
>
> Thanks in advance.
>
> ___
> 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] design question / discussion

[Rich Rattanni]

Adolfo:
I can't tell you how many times I felt a flat file approach would be
better.  However, 2 years ago when the design began there was a
thought of 'Having the ability to mine data on the device would be an
invaluable tool'.  SQLite has proven superb for some aspects of the
system, but not for storing simple flag data I believe someone
name Occam had something to say about this?  Ah the benefits of
hindsight  (sorry for the sarcasm, its the only thing that keeps a
smile on my face).

Ken:
>How do you decide which 20% to clear incase of space treshold?
Oldest 20% is cleared once a max size is reached.  Its kinda
arbitrary... I just figured it was better to clear a large swath of
flags than a delete one, insert one approach.
>Is the downloaded data always deleted once successful?
Yes

Woody:
Good to know, thank you :-).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] design question / discussion

[Rich Rattanni]

> It seems unclear to me what your requirements are trying to attempt.
>
> Do you need to keep any of this data, if so for how long?
   I have to keep all data until a download.  Downloads can fail too
so I cannot delete data until a download succeeds.
> Do you need to be able to read the older data?
   The device supports viewing the flag information via a webpage.
Not to mention, I only want the device to store a fixed amount (say
5000) flags, and if this limit is reached I will clear some amount
(say 20%) to make room for new data.

> Do you need to be able to subset the data?
   No


>Main.db  = contains download.db and is an attachment point for ancillary db's.
>wrtdb_###.db  = Always write to this location.

>When a download is needed simply close the current wrtdb_###. Create a new 
>wrrtdb_###.db and Incrementing new wrtdb table in the >main.db

Are you saying that when I want to do a download, I copy the data from
the wrtdb_###.db to main?  Then download main?  If so I thought about
that, but then I have to reserve space for 2X the size of wrtdb_###,
because during the copy the data will exist on the unit in duplicate.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] design question / discussion

[Rich Rattanni]

> Perhaps i've missed something or don't understand it well. Your
> databases is all on the same file or do you have 2 separate sqlite
> sessions to 2 different databases files? In the first scenario you
> must be very fast and in the second you can switch from one database
> to the other, unbind (close) the sqlite, do ftp or what ever you want
> and delete database file.
>
Yes in my code, I was thinking of having two database files on the filesystem
x and x+1.
During the download process I was going to drop any data generated during
the download process into x+1 (that is to say the system continues running
normally while a download is in progress).

> You attach x+1 to x. Why do you need it? If you delete old records on
> x after the ftp you can trash x, work with x+1 and recreate a void x.

I can see where I may not need it. I was just thinking of when the unit powers
back up I need to know which database is the 'main' database and which
database is the 'overflow'.  I would use the rule that x is the main and x+1
is overflow data. Strickly policy.  Incase it is unclear x and x+1 refer to
the actual filename of the database on disk, so I would have
flags.sqlite.0  <- Main
flags.sqlite.1 <- Overflow
***After download and next power up***
flags.sqlite.1 <- Main
flags.sqlite.2 <- Overflow


> I think you only need 2 databases and while you add data to A, you
> copy and delete B. Then switch A and B. Perhaps you need 3 databases,
> and separate the download and . On the other side you can attach the
> databases and reconstruct one big database.
>
Ah the design process I thought I had a good reason for my switching
policy but as I look back perhaps it is overly complex.  My original design
was a two database scheme, but as mentioned I thought the filename
was a slick way of determining which database was the primary (of course
a simple table in each database could do the same, that I join to and
update who is Main and Overflow).

Oh thats right, I actually remember now why I implemented this the way
I did.  The system has file size constraints on the amount of data
stored in the database, and downloads may be interrupted.  In the
event of a cancel I wanted all data to be in one database, hence the
copy of data from X+1 back into X.  I figured this works well because
when I move data from X+1 to X, I can check if storage constriants
have been violated and clear old data if necessary.

Also, I wanted to save the deletion and recreation of databases for
the next powerup, because the device is battery powered.  I have
a backup battery that allows me to run briefly after power is
removed, but this time is limited.  I figured doing this operation at
powerup is the safest bet (in the worst case, the power is removed
and I am back to relying on the backup battery, but on average the
battery is not removed immediately after insertion).

At the heart of the matter is the fact that vacuum's are too costly
(time wise) and while the device is not 'real time' per se, I must
services requests from another processor fairly quickly (<1 sec).

> If you need compression you can check any lzp, lzo or lzrh
> algorithms, they are very fast, and compress the files "on the fly".
> This compression algorithms works well with text data and bad with
> binary data. Take care because sqlite does already compress data in
> the databases files.

I cant reveal the nature of the data I am compressing, but on average, with
gzip, I see a reduction of 50 -> 70% in size.


Thanks for your reply, I implemented something similar to this but I end up
with corrupt databases if a download is performed, and power is removed,
and the sun and the stars alignblah blah blah.   In a word, its buggy.
I think violating sqlite and moving databases around using OS calls is
what is getting me.  I am up against a wall to design a solution
thatworks.  Stupid input specs!  Anyways, thats why I posted to the
list and I really do apprecaite your input
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] design question / discussion

[Rich Rattanni]

Actually my reason for writing into a seperate database is more...
well crude.  I tar several databases together then encrypt using
openSSL. Then an FTP like program transmits the data a central server.
 I must suspend writing into the database for the duration of the tar
operation since tar does not abide by sqlites file locking rules.

Thanks for your input, any and all help is appreciated!

--
Rich

On Mon, May 19, 2008 at 11:50 AM, Ken <[EMAIL PROTECTED]> wrote:
> Rich,
>
> From your design it appears you are writing to a seperate db while a 
> "download" is happening? I'm guessing that is to prevent a read/write lock 
> contention correct?
>
> It seems to me that any new data coming in will need to write and you are 
> simply looking to read during a download operation and trying to avoid lock 
> contention and delays correct?
>
> DownloadInfo table is used to keep track of the point where the last download 
> completed successfully.
>
> data to download = last successful to max rowid. (ie a subset).
>
> One thought I had to avoid the contention is if this is a threaded 
> application? you could enable the shared cache and  read_uncommitted 
> isolation. It might be a bit tricky in that you'll probably have to get the 
> "committed" data in a txn, then set the uncomitted mode to read to avoid 
> waiting for locks.
>
> Ken
>
> Rich Rattanni <[EMAIL PROTECTED]> wrote: Hi I have a general design question. 
>  I have the following senario...
>
> In an embedded system running linux 2.6.2x I have a sqlite database
> constantly being updated with data acquired by the system.  I cant
> lose data (hence why I am using sqlite in the first place).  However
> periodically I have download the data contain within the database to a
> central server.  The system cannot stall during the download and must
> continue to record data.  Also, after the download I need to shrink
> the database size, simply because if the database is allowed to grow
> to its max size (~50MB) then every download thereafter would be 50MB,
> which is unacceptable.  I would simply vacuum the database, but this
> takes too much time and stalls the system.
>
> My solution is the following (still roughed out on scraps of paper and
> gray matter).
>
> have two databases on the system at all times (data.sqlite.(x) and
> data.sqlite.(x+1))
> All data written into x.
> When a download is requested...
> Mark highest rowid in each table in database (x) in a table
> called DownloadInfo
> Begin logging data to (x+1)
> Download done (success or failure - downloads may be cancelled or timeout)
>Attach x+1 to x
>Begin transaction
>delete all data in x from tables equal to <= rowid saved in DownloadInfo
>move any data stored in x+1 to x
>if download was successful...
>mark in x that a download was successful in DownloadInfo
>
> At next powerup...
> Scan x.DownloadInfo, see if a download was successful...
>Yes
>Attach x+1 to x
>attach x+2 to x
>begin transaction
>Build new database x+2
>Move data from x to x+1
>Mark database has been deleted in DownloadInfo
>commit.
>delete (using os, unlink perhaps)
>   No
>Do nothing.
>
>
> So its kinda complicated, but I think such things are necessary.  For
> instance, a vacuum is out of the question, it just takes too long.
> Thats why  the double database scheme works good for deleting old
> databases.  I guess i want to stop here and leave some info out.  That
> way I don't suppress any good ideas.
>
> And as always I really appreciate any help i can get.  I tried to
> implement something similar, but I was copying an already prepared
> sqlite database which was not very reliable.  Guess another question,
> maybe one that solves this one. has any improvements on
> auto-vacuum been made?  Does anyone trust it or can anyone attest to
> its fault tolerance.
> ___
> 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] design question / discussion

[Rich Rattanni]

Thanks for your reply.  I have done some quick timing tests on my
system; a vacuum can take 5 or more minutes (synchronous full), and a
delete and recreate is rougly 3 seconds.  I think I did such a test
with a 30MB database.  The database resides on a jffs2 file system
(compression off), which seems to have a constant time for deletions.

I should have included I am using sqlite 3.4.0.


On Sun, May 18, 2008 at 4:45 AM,  <[EMAIL PROTECTED]> wrote:
>> Hi I have a general design question.  I have the following senario...
>
> IMHO your design sound reasonable. In relation with the vacuum question
> I suggest try to delete and re-create each table and watch both timings.
>
> HTH
>
> Adolfo
> ___
> 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] design question / discussion

[Rich Rattanni]

Hi I have a general design question.  I have the following senario...

In an embedded system running linux 2.6.2x I have a sqlite database
constantly being updated with data acquired by the system.  I cant
lose data (hence why I am using sqlite in the first place).  However
periodically I have download the data contain within the database to a
central server.  The system cannot stall during the download and must
continue to record data.  Also, after the download I need to shrink
the database size, simply because if the database is allowed to grow
to its max size (~50MB) then every download thereafter would be 50MB,
which is unacceptable.  I would simply vacuum the database, but this
takes too much time and stalls the system.

My solution is the following (still roughed out on scraps of paper and
gray matter).

have two databases on the system at all times (data.sqlite.(x) and
data.sqlite.(x+1))
All data written into x.
When a download is requested...
 Mark highest rowid in each table in database (x) in a table
called DownloadInfo
 Begin logging data to (x+1)
Download done (success or failure - downloads may be cancelled or timeout)
Attach x+1 to x
Begin transaction
delete all data in x from tables equal to <= rowid saved in DownloadInfo
move any data stored in x+1 to x
if download was successful...
mark in x that a download was successful in DownloadInfo

At next powerup...
Scan x.DownloadInfo, see if a download was successful...
Yes
Attach x+1 to x
attach x+2 to x
begin transaction
Build new database x+2
Move data from x to x+1
Mark database has been deleted in DownloadInfo
commit.
delete (using os, unlink perhaps)
   No
Do nothing.


So its kinda complicated, but I think such things are necessary.  For
instance, a vacuum is out of the question, it just takes too long.
Thats why  the double database scheme works good for deleting old
databases.  I guess i want to stop here and leave some info out.  That
way I don't suppress any good ideas.

And as always I really appreciate any help i can get.  I tried to
implement something similar, but I was copying an already prepared
sqlite database which was not very reliable.  Guess another question,
maybe one that solves this one. has any improvements on
auto-vacuum been made?  Does anyone trust it or can anyone attest to
its fault tolerance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Meaning of the following code

[Rich Rattanni]

All:
I am able to consistently cause the following message during a integrity check

Page xxx is never used

This seems non-critical, since a vacuum clears this up.  If someone
has the time could you explain the meaning (besides the obvious),
causes, and dangers of receiving this message during a integrity
check?

--
Thanks in advance,
Rich Rattanni
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does or will Sqlite provide a DUMP mechanism?

[Rich Rattanni]

It should be easy to write your own dump feature.  The table create
statements are saved in sqlite_master, and likewise for the schema.
Without looking at the code for sqlite3 (the command line utility) or
tksqlite, I would bet that is how they implement their dump feature.


On Mon, Mar 3, 2008 at 11:53 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Abshagen, Martin RD-AS2 wrote:
>  > Can a backup mechanism be implemented by means of the current Sqlite-API?
>
>  Well, no, but the database is a single file, so you can back it up by
>  copying the file.
>
>  If you are concerned about other processes accessing the database while
>  you are copying it, have your program start an exclusive transaction
>  before the copy,and roll it back after the copy.
>
>  HTH
>  Dennis Cote
>
>
> ___
>  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] Use of SUM() causes Alignment Traps on ARM arch

[Rich Rattanni]

This is not necessarily a bug, but I thought I might point out that
use of the SUM() function causes my program to generate alignment
traps on an ARM system.  They seem to be proportional to the amount of
data in the table.  While no harm is caused (I have the kernel set to
fix these problems and recover), it does slow the execution of any SQL
statements with SUM() considerably.

Just I thought I would just throw it out there.

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

[sqlite] Reducing size of timestamps

[Rich Rattanni]

All:

I was wondering if there was any way to reduce the 'cost' of storing a
timestamp on entries in a SQLite database.  I performed a hexdump of
the file and it showed me the timestamp is stored as a 19-byte ASCII
string.  One quick thing I thought of was to store the unix timestamp
in each field, then when I wanted an actual date use
datetime(mytimeField,'unixepoch','localtime') to convert it back.
This would save me 9 bytes per record, but I would (greedily) like to
save more... any thoughts?

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

[sqlite] Storing / Archiving of SQLite database in vcs

[Rich Rattanni]

All:
I have several sqlite databases that I want to store in my version
control system.  I was wondering if instead of storing them as binary
files, would make sense to store a SQL dump in version control.  When
I create a root file system for my development board I will create the
databases from the SQL dump.

I had two reasons for thinking this was a good idea
1) If I upgrade sqlite and build a new root file system, then my
databases will be created with the same version of sqlite.
2) This is essentially the same (or better?) as a vacuum, since the
database should be completely free of any wasted space.
2) They may be a slight possibility of using version control faculties
(diff, blame, etc) while I am developing.

However I thought of one con for doing this
1) It may be stupid to do this.


I guess I am more concerned with wether or not I can guarantee that
the sqlite engine is deterministic such that for a given sequence of
SQL input each time I wind up with the same database (of course, using
sqlite3 to dump the sql may not guarantee me the same sequence of SQL
as was used to create the database, huh?).


Feedback? (please be nice)


--
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Help with an unusual query

[Rich Rattanni]

All:
I have the following table


key  data
1  'Version 1'
1  'Version 2'
1  'Version 3'
2  'Version 4'
2  'Version 5'

(obviously key is not primary)

I want to write a query that returns

key data
1 'Version 1 Version 2 Version 3'
2 ' Version 4 Version 5'


Basically I want a row returned for each unique key, but i want the
data column for each key (string data) concatenated together (and
seperated with spaces if possible :) ).

Thanks,
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Compact statement for table to table copy

[Rich Rattanni]

I am not changing the ordering, but i do not want to copy the primary
key field since it is auto increment (I neglected too mention that
before, sorry).
On 10/16/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:
> Well, you don't have to list the columns if you're not changing the
> ordering.
>
> INSERT INTO table1 SELECT * FROM table2;
>
> Rich Rattanni wrote:
> > I have two tables in a database with exactly the same schema
> > (idNum PRIMARY KEY, data1, data2)
> >
> > I want to copy all the records in table 2 to table 1, currently I am
> > using the following statement:
> > INSERT INTO table1 (data1, data2) SELECT data1, data2 FROM table2.
> >
> > Now this is just a simplified illustration, in my case I am copying
> > about 10 columns over.  I was wondering if there was a compact way to
> > write the SQL statement, that copied the  data over from one table to
> > the other ignoring the primary key fields.  I suspect there is not,
> > but I figured it wouldnt hurt to ask.
> >
> > --
> > Thanks,
> > Richard Rattanni
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Compact statement for table to table copy

[Rich Rattanni]

I have two tables in a database with exactly the same schema
(idNum PRIMARY KEY, data1, data2)

I want to copy all the records in table 2 to table 1, currently I am
using the following statement:
INSERT INTO table1 (data1, data2) SELECT data1, data2 FROM table2.

Now this is just a simplified illustration, in my case I am copying
about 10 columns over.  I was wondering if there was a compact way to
write the SQL statement, that copied the  data over from one table to
the other ignoring the primary key fields.  I suspect there is not,
but I figured it wouldnt hurt to ask.

--
Thanks,
Richard Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite, pthread and daemon

[Rich Rattanni]

Just read this today, after doing some other research.  Does this help any?

http://www.sqlite.org/faq.html#q6

It says, in a nutshell, don't use a database across forks.

On 10/3/07, John Stanton <[EMAIL PROTECTED]> wrote:
> How do you know that when your process forks that you are looking at the
> child, not the parent?
>
> Sabyasachi Ruj wrote:
> > Hi,
> >
> > I am writing an application which will continue to execute as a 'daemon' in
> > Linux.
> > The application is multi threaded.
> > And once the daemon is created, it will create few threads to perform some
> > tasks.
> >
> >>From here, I'll refer the 'process before creating the daemon' as 'PARENT
> > PROCESS',
> > and 'process after creating the daemon' as 'CHILD PROCESS'.
> >
> > So the threads are being created in CHILD PROCESS.
> >
> > I need to get the process id (PID) in those threads.
> >
> > Here is my problem!
> >
> > If I have called 'sqlite3_open' BEFORE creating the daemon,
> > then the PID I am getting inside the thread, is the PID of the
> > 'PARENT PROCESS', not the 'CHILD PROCESS'!
> >
> > But as after creating the daemon, the PARENT PROCESS gets killed,
> > those are invalid PIDs.
> >
> > This happens ONLY if I am calling the 'sqlite3_open' before creating the
> > daemon!
> >
> >
> > I am not getting if this is the expected behavior or not.
> >
> >
> > Herewith I am attaching one sample test project, that will depict the issue.
> > It is having one Makefile. The executable will be named as: pidtest
> >
> > To execute that process as a daemon use the '-d' command line argument.
> >
> > For example:-
> > ./pidtest -d
> >
> > This process will create one log file called 'log.txt' in the current
> > directory.
> > which will show the output.
> >
> > For limitation on size of the mail,I have removed SQLite source code('
> > sqlite3.c' and 'sqlite3.h').
> > I am using amalgamated code, version 3.4.0.
> > Checked with the latest 3.4.2. The problem is same.
> >
> >
> >
> > 
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] malloc failure in sqlite3_prepare

[Rich Rattanni]

All:
I am writing a program that reads large blob data (~15 MB) from a
sqlite database, then writes the data out to the sound card.  After
this time, some results are calculated and inserted into the same
database.  In my code, I have 1 database handle from which I do a
prepare to extract the waveform data, then I use the same handle to do
the result insert...
I get the following error results from a backtrace.

I copy the data I get back from sqlite to my own private buffer.   The
odd thing is that some runs this works just fine, and some runs it
does not... However it is the same blob data each time.  Any
assistance would be appreciated, I can supply more information upon
request.


(gdb) bt
#0  0xb7d4899f in ?? ()
  from /lib/tls/i686/cmov/libc.so.6
#1  0x0001 in ?? ()
#2  0xb7f94ea3 in sqlite3MallocFailed ()
  from /home/enguser/libsqlite3.so.0
#3  0xb7d4ae38 in ?? ()
  from /lib/tls/i686/cmov/libc.so.6
#4  0x0963cf85 in ?? ()
#5  0xb7fae418 in ?? () from /lib/ld-linux.so.2
#6  0xbfdcc834 in ?? ()
#7  0xb7fb6b79 in ?? () from /lib/ld-linux.so.2
#8  0xb7d4c60e in malloc ()
  from /lib/tls/i686/cmov/libc.so.6
#9  0xb7f8437f in sqlite3GenericMalloc ()
  from /home/enguser/libsqlite3.so.0
#10 0xb7f94f98 in sqlite3MallocRaw ()
  from /home/enguser/libsqlite3.so.0
#11 0xb7f950fe in sqlite3Malloc ()
  from /home/enguser/libsqlite3.so.0
#12 0xb7f951cc in sqlite3MallocX ()
  from /home/enguser/libsqlite3.so.0
#13 0xb7f88785 in sqlite3ParserAlloc ()
  from /home/enguser/libsqlite3.so.0
#14 0xb7f923c8 in sqlite3RunParser ()
  from /home/enguser/libsqlite3.so.0
#15 0xb7f8c087 in sqlite3Prepare ()
  from /home/enguser/libsqlite3.so.0
#16 0xb7f8c42b in sqlite3_prepare_v2 ()

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Seg Fault when using sqlite3_bind_blob

[Rich Rattanni]

I wish I could run valgrind, but this project is running on an ARM
chip and there is no port for the ARM last I checked.  Thanks for
checking my code.

--
Rich Rattanni

On 7/2/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Rich Rattanni" <[EMAIL PROTECTED]> wrote:
>
> sqlite3_exec(db, "BEGIN IMMEDIATE", NULL, NULL, NULL);
> -For each blob...
> sqlite3_prepare(db, "INSERT INTO table1 (blobData) VALUES (?1)", -1,
> , NULL);
> sqlite3_bind_blob(stmt, 1, blobData, blobSize, SQLITE_TRANSIENT);
> sqlite3_step(stmt);  // with appropriate error checking
> sqlite3_finalize(stmt);
> -end for each.
> sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
>

The above is correct and should work fine.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Seg Fault when using sqlite3_bind_blob

[Rich Rattanni]

I think I am incorrectly using the API, which may be leading to my
segmentation fault...

I have a variable amount (22k -> 1MB) of data, split across several
blobs to insert into a database.  I transactify the process to save
some time (alot by my tests).

sqlite3_exec(db, "BEGIN IMMEDIATE", NULL, NULL, NULL);
-For each blob...
sqlite3_prepare(db, "INSERT INTO table1 (blobData) VALUES (?1)", -1,
, NULL);
sqlite3_bind_blob(stmt, 1, blobData, blobSize, SQLITE_TRANSIENT);
sqlite3_step(stmt);  // with appropriate error checking
sqlite3_finalize(stmt);
-end for each.
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

Is this incorrect?  What I feel may be incorrect is the fact that I am
finalizing the stmt before the commit.  I programmed this way because
I did not want to have a separate stmt for each INSERT.  But I was
wondering if the finalize is destroying copy of the blob data sqlite
made during the call to sqlite3_bind_blob().

--
Rich Rattanni

On 7/1/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:

I stand corrected, thank you Andrew.  I seriuosly doubt it is a bug in
SQlite, but I have had a hell of a time with sqlite and binding
dynamically allocated text and binary data.

On 7/1/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> On 7/1/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> >
> > I was trying to look through the SQLITE source code to see how the
> > sqlite3_bind_blob routine worked.
> >
> > sqlite3_bind_blob passes the data pointer to bindText
> > bindText passes the data pointer to sqlite3VdbeMemSetStr
> > sqlite3VdbeMemSetStr then does...
> > ...
> > pMem->z = (char *)z;
> >   if( xDel==SQLITE_STATIC ){
> > pMem->flags = MEM_Static;
> >   }else if( xDel==SQLITE_TRANSIENT ){
> > pMem->flags = MEM_Ephem;
> >   }else{
> > pMem->flags = MEM_Dyn;
> > pMem->xDel = xDel;
> >   }
> > ...
> >
> > I dont see anywhere where sqlite3 copies data to a private buffer, I
> > just see where sqlite3 saves a copy of the user pointer.
> >
>
>
> Further down in that function, after setting MEM_Ephem, there are these
> lines of code:
>
>  if( pMem->flags_Ephem ){
>return sqlite3VdbeMemMakeWriteable(pMem);
>  }
>
> which does the memory copy when SQLITE_TRANSIENT is used as a side-effect of
> making it "writable".
>
> In your original outline you issued sqlite3_step before freeing the memory.
> If you leave it that way, you can get away with SQLITE_STATIC when binding
> the blob... which might indicate something by whether/where the crash still
> occurs.
>
> --andy
>  just a sqlite user, not really a knower-of-code
>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Seg Fault when using sqlite3_bind_blob

[Rich Rattanni]

I stand corrected, thank you Andrew.  I seriuosly doubt it is a bug in
SQlite, but I have had a hell of a time with sqlite and binding
dynamically allocated text and binary data.

On 7/1/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:

On 7/1/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:
>
> I was trying to look through the SQLITE source code to see how the
> sqlite3_bind_blob routine worked.
>
> sqlite3_bind_blob passes the data pointer to bindText
> bindText passes the data pointer to sqlite3VdbeMemSetStr
> sqlite3VdbeMemSetStr then does...
> ...
> pMem->z = (char *)z;
>   if( xDel==SQLITE_STATIC ){
> pMem->flags = MEM_Static;
>   }else if( xDel==SQLITE_TRANSIENT ){
> pMem->flags = MEM_Ephem;
>   }else{
> pMem->flags = MEM_Dyn;
> pMem->xDel = xDel;
>   }
> ...
>
> I dont see anywhere where sqlite3 copies data to a private buffer, I
> just see where sqlite3 saves a copy of the user pointer.
>


Further down in that function, after setting MEM_Ephem, there are these
lines of code:

 if( pMem->flags_Ephem ){
   return sqlite3VdbeMemMakeWriteable(pMem);
 }

which does the memory copy when SQLITE_TRANSIENT is used as a side-effect of
making it "writable".

In your original outline you issued sqlite3_step before freeing the memory.
If you leave it that way, you can get away with SQLITE_STATIC when binding
the blob... which might indicate something by whether/where the crash still
occurs.

--andy
 just a sqlite user, not really a knower-of-code



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Seg Fault when using sqlite3_bind_blob

[Rich Rattanni]

On 7/1/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:

On 7/1/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Rich Rattanni" <[EMAIL PROTECTED]> wrote:
> > All:
> > I am using SQlite to store several rows of binary data into a
> > database.  In my code I have a several character arrays containing
> > data.  I then do the following...
> >
> > 1.Begin Transaction
> > 2.For each blob...
> > 2a.sqlite3_prepare("Insert statement...")
> > 2b.call sqlite3_bind_blob(stmt, col#, dataPointer, sizeOfData, 
SQLITE_TRANSIENT)
> > 2c.sqlite3_step()
> > 2d.end
> > 3.Free data pointers.
> > 4.Commit Transaction
> >
> > This code segfaults.   Now If i move the free data pointers to outside
> > the commit, everything is fine.  According to the API documentation,
> > "If the fifth argument has the value SQLITE_TRANSIENT, then SQLite
> > makes its own private copy of the data immediately, before the
> > sqlite3_bind_*() routine returns."  I may be misinterperting the
> > documentation, or perhaps this is a bug in sqlite (course I am
> > assuming the former is true).
> >
> > Could anyone shed some light on my mystery?  I am wondering if I need
> > to enable (when sqlite is compiled) support for SQLITE_TRANSIENT?
> >
>
> The documentation is correct - SQLITE_TRANSIENT causes SQLite
> to make a copy of the data before sqlite3_bind_blob() returns.
> You should be able to free the dataPointer prior to the sqlite3_step().
> No special compile-time optimizations are required.
>
> If you have a reproducible test case, we will look into the problem.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

Sir:
Thanks for the quick reply.  I will try to work up a test case in
my spare time  I had GDB running when this problem occured, and it
said the offending function was sqlite3pager_get().  I am not sure if
this helps in any way.  Again thanks for your response.

--
Rich Rattanni



Sir:

I was trying to look through the SQLITE source code to see how the
sqlite3_bind_blob routine worked.

sqlite3_bind_blob passes the data pointer to bindText
bindText passes the data pointer to sqlite3VdbeMemSetStr
sqlite3VdbeMemSetStr then does...
...
pMem->z = (char *)z;
 if( xDel==SQLITE_STATIC ){
   pMem->flags = MEM_Static;
 }else if( xDel==SQLITE_TRANSIENT ){
   pMem->flags = MEM_Ephem;
 }else{
   pMem->flags = MEM_Dyn;
   pMem->xDel = xDel;
 }
...

I dont see anywhere where sqlite3 copies data to a private buffer, I
just see where sqlite3 saves a copy of the user pointer.

I see that sqlite3VdbeMemDynamicify memcpy's from the private data to
the vdbe object.  Should the if ...else if ... else then look like
this?
if( xDel==SQLITE_STATIC ){
   pMem->flags = MEM_Static;
 }else if( xDel==SQLITE_TRANSIENT ){
   pMem->flags = MEM_Ephem;
   sqlite3VdbeMemDynamicify(pMem);
 }else{
   pMem->flags = MEM_Dyn;
   pMem->xDel = xDel;
 }

Just trying to help if I can...  Am I looking at this right?

--
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Seg Fault when using sqlite3_bind_blob

[Rich Rattanni]

On 7/1/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Rich Rattanni" <[EMAIL PROTECTED]> wrote:
> All:
> I am using SQlite to store several rows of binary data into a
> database.  In my code I have a several character arrays containing
> data.  I then do the following...
>
> 1.Begin Transaction
> 2.For each blob...
> 2a.sqlite3_prepare("Insert statement...")
> 2b.call sqlite3_bind_blob(stmt, col#, dataPointer, sizeOfData, 
SQLITE_TRANSIENT)
> 2c.sqlite3_step()
> 2d.end
> 3.Free data pointers.
> 4.Commit Transaction
>
> This code segfaults.   Now If i move the free data pointers to outside
> the commit, everything is fine.  According to the API documentation,
> "If the fifth argument has the value SQLITE_TRANSIENT, then SQLite
> makes its own private copy of the data immediately, before the
> sqlite3_bind_*() routine returns."  I may be misinterperting the
> documentation, or perhaps this is a bug in sqlite (course I am
> assuming the former is true).
>
> Could anyone shed some light on my mystery?  I am wondering if I need
> to enable (when sqlite is compiled) support for SQLITE_TRANSIENT?
>

The documentation is correct - SQLITE_TRANSIENT causes SQLite
to make a copy of the data before sqlite3_bind_blob() returns.
You should be able to free the dataPointer prior to the sqlite3_step().
No special compile-time optimizations are required.

If you have a reproducible test case, we will look into the problem.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




Sir:
Thanks for the quick reply.  I will try to work up a test case in
my spare time  I had GDB running when this problem occured, and it
said the offending function was sqlite3pager_get().  I am not sure if
this helps in any way.  Again thanks for your response.

--
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Question about triggers

[Rich Rattanni]

Hello all:
   I was reading through a couple of sqlite tutorials and I noticed
examples of timestamping database entries by using triggers.  I wanted
to ask people's opinion about using triggers to timestamp records in a
database.

Which is 'better': using a trigger to timestamp records or explicity
adding the timestamp in the sql statement?

In my own analysis (brief < 5 min), I have thought of the following
pro's and con's.

1) Faster to do the timestamp in the sql statement, since it does not
require activation of the trigger.
2) Query looks 'cleaner', not having an explicit datetime() inside
3) Can switch the timestamp between UTC and localtime by modifying the
trigger as opposed to changing and recompiling the application.


Of course this was just a quick thought on my way home from work.  I
am sure I am forgetting several important things.  I would love to
hear any other reasons for or against using triggers for automatic
timestamping.

--
Thanks,
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Proper way to transfer a live sqlite database

[Rich Rattanni]

Thank you Igor and Christian.  I appreciate your help.


On 6/18/07, Christian Smith <[EMAIL PROTECTED]> wrote:

Rich Rattanni uttered:

> The databases will be in flux, and I didnt necessairly want to suspend
> the application that is performs reads and writes into the database.
> A simple copy worries me because it seems like messing with SQLITE on
> the file level is dangerous since you circumvent all the protection
> mechanisms that provide fault tolerance.  I didnt want to have to
> worry about if the database has a journal file that needs copied, or
> any other situation like that.  I figured using the SQLITE API to do
> the copy would award me some protection against corruption.


You're right to be cautious. Never copy an in use database if that
database could possibly be updated.

If you open the database, and obtain a SQLite read lock on it, you can be
sure it is not going to be modified, and can be safely copied at the OS
level.

Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the
database file, then executing a 'ROLLBACK' to end the transaction.

To limit the time the database is locked, I suggest copying the file to a
local filesystem first, then transferring across the network after the
lock is released.

Christian



--
 /"\
 \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
  X   - AGAINST MS ATTACHMENTS
 / \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Proper way to transfer a live sqlite database

[Rich Rattanni]

The databases will be in flux, and I didnt necessairly want to suspend
the application that is performs reads and writes into the database.
A simple copy worries me because it seems like messing with SQLITE on
the file level is dangerous since you circumvent all the protection
mechanisms that provide fault tolerance.  I didnt want to have to
worry about if the database has a journal file that needs copied, or
any other situation like that.  I figured using the SQLITE API to do
the copy would award me some protection against corruption.

On 6/18/07, Fred Williams <[EMAIL PROTECTED]> wrote:

It would most likely be much quicker (and simpler) just to utilize the
OS's file coping feature to copy the table.  What would be gained with
the attaching databases approach over just a straight file copy?

Fred

> -Original Message-
> From: Rich Rattanni [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 18, 2007 10:20 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Proper way to transfer a live sqlite database
>
>
> I was writing to ask some opinions on how to perform a download of a
> live sqlite database.  Basically I have a device, which stores all
> manner of data in a sqlite database.  Periodically I want to download
> the data to a central server for viewing.  I discussed it with my
> colleagues, and they felt that I should just copy the file to the
> server.  However I was thinking of having a blank database with
> identical schema to the database I am copying.  Then when the download
> occurs, I would ATTACH the live database to the blank database, and
> query the data from one to the other.  Then I would close the cloned
> version and offload that to the server.
>
> The standard questions now follow...
> Is this an acceptable way?
> Is there a better/best way?
>
> Thanks for any input,
> Rich Rattanni
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Proper way to transfer a live sqlite database

[Rich Rattanni]

I was writing to ask some opinions on how to perform a download of a
live sqlite database.  Basically I have a device, which stores all
manner of data in a sqlite database.  Periodically I want to download
the data to a central server for viewing.  I discussed it with my
colleagues, and they felt that I should just copy the file to the
server.  However I was thinking of having a blank database with
identical schema to the database I am copying.  Then when the download
occurs, I would ATTACH the live database to the blank database, and
query the data from one to the other.  Then I would close the cloned
version and offload that to the server.

The standard questions now follow...
Is this an acceptable way?
Is there a better/best way?

Thanks for any input,
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Re: One statement column to column copy

[Rich Rattanni]

What you tried made no sense. You can only use a table name in a
statement if it's the "primary" table of the statement (for UPDATE and
DELETE) or was explicitly introduced into the statement by FROM clause
(for SELECT). You can't just throw in any odd table, because you can't
then specify which row or rows of this table you want to use.


I mean't when I tried this...
update Parameters set value = (select value from WorkingParameters wp);
When you execute this, it works, but it takes the first result row
from value and copies it into all the rows of parameters.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: One statement column to column copy

[Rich Rattanni]

update Parameters set value =
   (select value from WorkingParameters wp
where wp.id = Parameters.id);


Hi Igor,
  That worked fine.  I am curious why it does work?  According to the
sqlite syntax guide, it says that...
"When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression. If the SELECT yields
more than one result row, all rows after the first are ignored."

I am not arguing with you, but I just wanted to be educated as to why
this way is correct.  I am guess it has something to do with the WHERE
clause, since I tried something similar, but did not have the WHERE
clause specified.  Thanks for the info.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: One statement column to column copy

[Rich Rattanni]

On 4/19/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:

All:
  I have a main table (Parameters) which contains system parameters
to which I clone the table to a temporary database...

CREATE TEMPORARY TABLE WorkingParameters AS SELECT * from Parameters;

I modify the parameters in the temporary table, and occasionally I may
want to save them.  Reading the sqlite documentation, I thought I
would be valid to execute

UPDATE Parameters SET value = temp.WorkingParameters.value;  But I get
an error stating that temp.workingparameters.value does not exist.

I thought I was correctly following the sql syntax ... this was my
reasoning, but I think I am misunderstanding something.

sql-statement ::= UPDATE table-name SET assignment
assignment ::= column-name = expr
expr ::= database-name . table-name . column-name

Could someone please help me with my query?

Thanks,
Rich Rattanni



I just came across REPLACE INTO.  This may work for my situation, but
I would use an UPDATE statement, so I can use a WHERE clause.
Basically the main parameter table has several parameter types, which
I wanted to extract into several tables (each table consisting of one
type).  Then when I put them back into the master table I would use a
WHERE to accomplish this.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] One statement column to column copy

[Rich Rattanni]

All:
  I have a main table (Parameters) which contains system parameters
to which I clone the table to a temporary database...

CREATE TEMPORARY TABLE WorkingParameters AS SELECT * from Parameters;

I modify the parameters in the temporary table, and occasionally I may
want to save them.  Reading the sqlite documentation, I thought I
would be valid to execute

UPDATE Parameters SET value = temp.WorkingParameters.value;  But I get
an error stating that temp.workingparameters.value does not exist.

I thought I was correctly following the sql syntax ... this was my
reasoning, but I think I am misunderstanding something.

sql-statement ::= UPDATE table-name SET assignment
assignment ::= column-name = expr
expr ::= database-name . table-name . column-name

Could someone please help me with my query?

Thanks,
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite database on a read only media

[Rich Rattanni]

All:
   Is it possible to have an sqlite database on a read only media?  I
will only be issuing SELECT queries on this database.  Are there any
pragma's you need to issue to let SQLITE know it is not acceptable to
write to the database file.

--
Rich Rattanni

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Default sqlite memory management policy

[Rich Rattanni]

On 4/5/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Rich Rattanni" <[EMAIL PROTECTED]> wrote:
>
> I am just wondering should I take matters into my own hands,
> and compile and make use of sqlite3_soft_heap_limit(), or can I trust
> SQLITE to manage it for me.

If you do not set sqlite3_soft_heap_limit(), SQLite assumes
you are on a workstation with essentially unlimited memory
and will use as much memory as it needs to run most efficiently.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Thank you sir.  I shall recompile and use this option.

--
Rich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Default sqlite memory management policy

[Rich Rattanni]

On 4/4/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Rich Rattanni" <[EMAIL PROTECTED]> wrote:
> On 4/3/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> > All:
> >I was wondering exactly how SQLite handles freeing memory used
> > from the heap when compiled without SQLITE_ENABLE_MEMORY_MANAGEMENT?
> >
> >Will absolutely no memory be freed, or is there some default
> > memory management scheme used?
> >
>
> Let me explain my question better...
> SQLITE on my system has been compiled without the
> SQLITE_ENABLE_MEMORY_MANAGEMENT define.  I have a thread that writes a
> hundred or so samples into a database every 30 seconds.  I do not open
> and close the database pointer each time, but I do prepare and
> finalize a statement for each transaction.  I have notices that the
> memory used by my program slowly but surely increases as the program
> runs (about 24 hrs sees a 40%+ increase in system memory).  I have
> checked, and rechecked the program for any memory leaks on my part,
> but I have no found any obvious ones.  I just wanted to know if
> sqlite, as compiled for my system, will use as much memory as it sees
> fit in a particular thread if compiled without
> SQLITE_ENABLE_MEMORY_MANAGEMENT?
>
> Can anyone answer this for me?
>

SQLite does not leak memory, if that what you are asking.
You do not need to compile with SQLITE_ENABLE_MEMORY_MANAGEMENT
in order to prevent memory leaks.

The SQLITE_ENABLE_MEMORY_MANAGEMENT macro is used to enable
two APIs used for tighter control of memory on resource
limited embedded devices:

sqlite3_release_memory()
sqlite3_soft_heap_limit()

I recommend you run your program in valgrind to find out where
the memory is leaking.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




Sorry I was not implying that SQLite leaked memory, but I would
consider it ok behavior if SQLite was allowed to use some reasonably
large amount of system memory as long as you kept a pointer to the
database open (for caching, etc).   And then closing the pointer
freeded that memory.



The SQLITE_ENABLE_MEMORY_MANAGEMENT macro is used to enable
two APIs used for tighter control of memory on resource
limited embedded devices:


Compiled without SQLITE_ENABLE_MEMORY_MANAGEMENT, what policy does
SQLite use regarding heap memory usage?  (this is a limited embedded
device).  I am just wondering should I take matters into my own hands,
and compile and make use of sqlite3_soft_heap_limit(), or can I trust
SQLITE to manage it for me.

Also, Valgrind is not an option, since there is no ARM port yet, but
they are hastly working on it.

Thank you for your reply,
Rich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Default sqlite memory management policy

[Rich Rattanni]

On 4/3/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:

All:
   I was wondering exactly how SQLite handles freeing memory used
from the heap when compiled without SQLITE_ENABLE_MEMORY_MANAGEMENT?

   Will absolutely no memory be freed, or is there some default
memory management scheme used?



Let me explain my question better...
SQLITE on my system has been compiled without the
SQLITE_ENABLE_MEMORY_MANAGEMENT define.  I have a thread that writes a
hundred or so samples into a database every 30 seconds.  I do not open
and close the database pointer each time, but I do prepare and
finalize a statement for each transaction.  I have notices that the
memory used by my program slowly but surely increases as the program
runs (about 24 hrs sees a 40%+ increase in system memory).  I have
checked, and rechecked the program for any memory leaks on my part,
but I have no found any obvious ones.  I just wanted to know if
sqlite, as compiled for my system, will use as much memory as it sees
fit in a particular thread if compiled without
SQLITE_ENABLE_MEMORY_MANAGEMENT?

Can anyone answer this for me?

--
Thanks in advance,
Rich

P.S. Sorry to repost, if there are no takers I will not repost again,
I just thought my first message was lacking any real question so I
wanted to clarify the original.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Default sqlite memory management policy

[Rich Rattanni]

All:
   I was wondering exactly how SQLite handles freeing memory used
from the heap when compiled without SQLITE_ENABLE_MEMORY_MANAGEMENT?

   Will absolutely no memory be freed, or is there some default
memory management scheme used?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] using SQLite with flash file systems

[Rich Rattanni]

On 3/25/07, John Fisher <[EMAIL PROTECTED]> wrote:

I note from the home page that "Transactions are atomic, consistent,
isolated, and durable (ACID) even after system crashes and power failures".
Is this still the case if the database is stored on a flash file system, say
JFFS2 with the linux kernel?


I am running SQLITE on a JFFS2 filesystem, and the above statement
seems to hold just fine.  Just make sure SYNCHRONOUS = FULL.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite segfault using libc library

[Rich Rattanni]

On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote:

Andrew Finkenstadt wrote:
> On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote:
>
>>
>> Compilers do not terminate strings, library functions do.
>
>
>
> You are guaranteed by the C standard that the string referred to by
>
>>> const char message[] = "this string";
>
>
> is null-terminated by the compiler.
>
Of course you are correct.  However this string is read only and if you
use GNU C and try to write to it you get a kick in the head.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Yes, and I am not writing to it... I am binding a string like
message[] to a sqlite query (using sqlite3_bind_text(stmt, bIndex,
value, -1, SQLITE_TRANSIENT);
) requesting that SQL count the length of the string, and SQLite
apparently calls strlen().  One of the calls to strlen segfaulted.  I
was wondering if anyone every had this same error.   I figure either
sqlite made a mistake, or there is a problem with the libc library
(this is an ARM arch).  If the consensus is that this is libc's
problem I will move this discussion to that list.

Of course, I guess I could determine if it is libc's fault by
replacing the -1 with strlen(message) and seeing if I see the same
problem.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite segfault using libc library

[Rich Rattanni]

Just because you have a pointer assigned to the string does not ensure
that it is terminated.  It only finds the start of the string.  You need
a null character at the end.


I understand that John, but these strings I am writing to the database
are declared as follows...

const char message[] = "some message";

So the compiler will be null terminating these.  Thats why I am
confused, it is no like I am building a string manually and forgetting
the \0 at the end.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite segfault using libc library

[Rich Rattanni]

Maybe using an invalid UTF-8 string as input to SQLite?
Maybe your wrapper uses strlen to find the end of the string and that
is different from the SQL string length function of SQLite, which may
be diferent on UTF-8 strings?


My wrapper class never uses strlen().  Instead I bind the string to
the statement, and I indicate the stringlength of -1 (which I assume
means sqlite runs strlen() internally).  And I do not change the
strings while the program runs.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite segfault using libc library

[Rich Rattanni]

I agree, but all my strings are const char *, and I do not modify them
during program execution.

On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote:

It looks like you might have an unterminated string.

Rich Rattanni wrote:
> All:
>I am writing an application that heavily logs all activity to a
> sqlite3 database.  Last night, while running some extended testing, I
> caught a segmentation fault.  The core dump isnt of much help...
>
> (gdb) bt
> #0  0x403d2934 in strlen () from /lib/libc.so.6
> #1  0x401add60 in ?? () from /usr/lib/libsqlite3.so.0
> (gdb)
>
> I do pass strings into sqlite to be logged, but these strings do not
> change while the program is running.  In fact all of these strings are
> declared as const char * in most of the header files.
>
> I bind all my text to the sqlite statements I execute (I wrote a
> simple sqlite3 wrapper class).  The bind statement looks like so...
> sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT);
>
> I use SQLITE_TRANSIENT because since this is a generic wrapper, I may
> from time to time write a character string that is lives on some
> functions stack and I didnt want to take the chance.
>
> I realize there isnt much to go on here, but any suggestions or
> guidance would be appreicated.
>
> --
> Rich
>
> -
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite segfault using libc library

[Rich Rattanni]

All:
   I am writing an application that heavily logs all activity to a
sqlite3 database.  Last night, while running some extended testing, I
caught a segmentation fault.  The core dump isnt of much help...

(gdb) bt
#0  0x403d2934 in strlen () from /lib/libc.so.6
#1  0x401add60 in ?? () from /usr/lib/libsqlite3.so.0
(gdb)

I do pass strings into sqlite to be logged, but these strings do not
change while the program is running.  In fact all of these strings are
declared as const char * in most of the header files.

I bind all my text to the sqlite statements I execute (I wrote a
simple sqlite3 wrapper class).  The bind statement looks like so...
sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT);

I use SQLITE_TRANSIENT because since this is a generic wrapper, I may
from time to time write a character string that is lives on some
functions stack and I didnt want to take the chance.

I realize there isnt much to go on here, but any suggestions or
guidance would be appreicated.

--
Rich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Re: Re: Question about multithreading

[Rich Rattanni]

On 3/22/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

Voxen <[EMAIL PROTECTED]> wrote:
>> There's no "like" - you do use connection pointer directly from
>> thread B. The fact that the piece of code thread B currently
>> executes is a method of an object that happened to be created by
>> thread A is immaterial.
>
> That clears things and it shows me I need to open/close the database
> locally when a method is called by several threads.

Well, you can have each thread open a connection, then pass it along as
a parameter to whatever function the thread needs to call. This way, the
method would always operate on a connection associated with whatever
thread were calling it, and you won't have to keep opening and closing
connections all the time.

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




Just be careful with this, because if you have multiple threads
requesting this shared connection you will have to use some kind of
mutual exclusion technique to allow only one thread to access the
connection at a time.

--
Rich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Question about multithreading

[Rich Rattanni]

On 3/22/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

Gil Delavous <[EMAIL PROTECTED]>
wrote:
> However, what happens when a thread calls a method from the main
> thread, this one using its own sqlite connection?

The question doesn't make any sense to me, sorry. Methods don't belong
to threads. Any function in a program can, in principle, be executed by
any thread at any time, including by multiple threads simultaneously.

> For example:
>
> Main thread:
> void main::query_something() {
> // query something using main thread's sqlite connection
> }
>
> Print thread:
> void print::print_result() {
> int value = main->query_something();
> }

What makes you think these classes are somehow affine to a particular
thread? They are not. When you call query_something from print_result,
the same thread that executed print_result now executes query_something,
whether it's a "main" thread (whatever that means) or otherwise.

If it's your intention that all methods from class main be called on one
thread, and all methods of print be called on another, it's up to your
program to ensure that. You need some kind of inter-thread communication
mechanism, e.g. a producer/consumer queue.

> As my main thread has tons of utility methods called from other
> threads

You seem to say "thread" when you mean "class", and this lies at the
heart of your confusion. Realize that the two are entirely different,
largely unrelated concepts.

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Gil:
  In you threads, dont declare sqlite3 *db private or public, instead
make it a local variable in each method.  Then if one class calls a
method from another, a seperate database pointer will exist (on each
threads local stack) and you will have no problems.  Let me know how
it works.

--
Rich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: multithread problem

[Rich Rattanni]

On 3/20/07, Jakub Ladman <[EMAIL PROTECTED]> wrote:

Dne úterý 20 březen 2007 12:42 Igor Tandetnik napsal(a):
> Rafi Cohen <[EMAIL PROTECTED]> wrote:
> > 1. Should I open the database explicitly in the amin part and also in
> > the thread?
>
> In my experience, SQLite works best when every thread opens its own
> connection.

How should I understand it?
It is faster? Much secure or what?

I have multithreaded program, where some  threads are inserting data into
tables (in random moments), two of them are retrieving subsets of data to
send it via udp protocol over internet in short data length, and one thread
operates as terminal for human users, where (single) user can fed sql
statements and retrieve data in human readable format.
The architecture looks like this: Single thread locks a common mutex just
before and then calls an sqlite api, when it receives excepted data, the
mutex is unlocked.

Do you think that is there a better method?

Thank You

Jakub Ladman

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




SQLite can support multiple readers to the same database, so the
common mutex will be a bottle neck if two separate threads want to
simply do reads from the database.  In an application I am writing, I
was doing the same approach as you, then I switch to using a separate
database pointer per thread, and I noticed that my code just looked
cleaner (since when I needed to open a database, I just created an
instance of an sqlite wrapper class I wrote, and did not have to worry
about sharing the mutex or any other synchronization problems).

Note, that if one thread performs a write the database will be locked,
so other threads may stall, if you chose to use multiple SQLITE db
pointers, make sure you check your sqlite_steps/exec's for the return
condition SQLITE_BUSY (and/or SQLITE_LOCKED).

In general if a library multithreads well (which in my opinion sqlite
3 does) then why not take advantage of it... Instead of trying to
serialize concurrent processes in your code.

Let me know how it works.

Re: [sqlite] Avoiding use of temporary files

[Rich Rattanni]

On 3/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

Siebe Warners <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I'm using sqlite on an embedded system with flash EEPROM as the medium.
> Performance of the flash is not too good, so i'm trying to avoid file
> system access where possible without running risk of database corruption.
> So the database file and the journal files need to be written to the
> flash, but i also noticed temp files being written to disk. I'm using
> sqlite 3.2.7.
>
> When i perform a sequence of commands like:
> BEGIN;
> UPDATE t SET col="val1" WHERE key="key1";
> UPDATE t SET col="val2" WHERE key="key2";
> UPDATE t SET col="val3" WHERE key="key3";
> .
> UPDATE t SET col="valx" WHERE key="keyx";
> COMMIT;
>
> Using strace i observe:
> - the journal file is created at the start of the transaction
> - a temp file is created at the start of the transaction
> - the journal file is written at the start of the sequence and some more
> data is appended somewhere halfway
> - the temp file is written at every UPDATE
> - at the commit the journal file is written, the database file is
> updated, and journal file and temp file are removed.
>

You must have a UNIQUE or CHECK constraint on your "t" table.
The extra file being opened is a rollback journal for each
particular UPDATE statement.  This extra rollback journal is
needed in case you hit a UNIQUE or CHECK constraint half way
through the update and have to go back and undo those parts of
the UPDATE that are already done.

You can avoid this extra journal file by using UPDATE OR FAIL
instead of UPDATE.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




Would it also be acceptable (assuming you want the protection offered
by the temporary tables) to do ... "PRAGMA temp_store = MEMORY"  on
the database?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Segmentation fault using sqlite

[Rich Rattanni]

I am wondering why my program crashes with a segmentation fault.  This
has only occured once, but it seems to be something with sqlite.  Is
this a known issue or is there anything I can do to further track this
down?


Program terminated with signal 11, Segmentation fault.
#0  0x403cdcb4 in _int_malloc () from /lib/libc.so.6
(gdb) bt
#0  0x403cdcb4 in _int_malloc () from /lib/libc.so.6
#1  0x403cedfc in malloc () from /lib/libc.so.6
#2  0x401c4418 in sqlite3MallocRaw () from /usr/lib/libsqlite3.so.0
#3  0x401c450c in sqlite3StrNDup () from /usr/lib/libsqlite3.so.0
#4  0x401cc070 in sqlite3VdbeChangeP3 () from /usr/lib/libsqlite3.so.0
#5  0x401cc0ac in sqlite3VdbeOp3 () from /usr/lib/libsqlite3.so.0
#6  0x401ac010 in sqlite3CodeSubselect () from /usr/lib/libsqlite3.so.0
#7  0x401ab4b0 in sqlite3ExprCode () from /usr/lib/libsqlite3.so.0
#8  0x401abd00 in sqlite3ExprIfFalse () from /usr/lib/libsqlite3.so.0
#9  0x401d02cc in sqlite3WhereBegin () from /usr/lib/libsqlite3.so.0
#10 0x401bf13c in sqlite3Select () from /usr/lib/libsqlite3.so.0
#11 0x401b6978 in sqlite3Parser () from /usr/lib/libsqlite3.so.0
#12 0x401c12e0 in sqlite3RunParser () from /usr/lib/libsqlite3.so.0
#13 0x47de97e0 in ?? ()
Cannot access memory at address 0x30

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Difference between DATETIME datatype and TIMESTAMP

[Rich Rattanni]

I was wondering what the differences were (storage, efficiency, usefulness)
between the DATETIME field and TIMESTAMP field in sqlite?

Re: [sqlite] SQLite on ARM9 running Linux

[Rich Rattanni]

My co-worker did a lot of work on OpenEmbedded, I know he just pushed the
latest patch for the latest SQLite snapshot into OE.  If you hardware is
supported by OE I would suggest you take a look.  I am writing an
application to run on this platform and everything is working wonderfully.

On 3/2/07, Korey Calmettes <[EMAIL PROTECTED]> wrote:


I am planning an embedded project using SQLite an a Cirrus Logic
EP9301.  I am curious of any difficulties that have been found in
either compiling and/or using SQLite in this environment.

Thanks for any input that you can provide,

Korey


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] use of sqlite in a multithread C application

[Rich Rattanni]

Rafi,
  Do not forget that if you do prepare and perform an execute in different
threads, that you should not attempt to execute multiple statements compiled
against the same sqlite3 database pointer concurrently.  Trust me, I made
this mistake.  If you are preparing multiple statements, make sure they are
executed in a serial fashion.

On 3/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


Rafi Cohen wrote:
> Another question: may I prepare all statements tha need to be prepared
> in one thread, but for part of them apply the execution process
> (bind-step-reset) I n the second thread or whould it be best to prepare
> statements in the same thread I intend to execute them later?
>
>
Rafi,

It don't think it matters. Either way should work.

From a design point of view it may be better to decouple the two
threads by having each prepare its own statements.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-