Re: [sqlite] DatabaseError: database disk image is malformed

[Larry Knibb]

Just wanted to say thanks to everyone for the help... I've decided to
use MySQL for this particular setup rather than go down the route of
custom builds. SQLite remains my preference for exclusive scenarios
and it's only because I have to support scaling to simultaneous
connections that I'm compromising this time.

Always a pleasure to feel the high-engagement of this list. Thanks again.

Cheers,
Larry


On 16 October 2012 17:10, Clemens Ladisch  wrote:
> Keith Medcalf wrote:
>> Note that according to the Microsoft documentation opportunistic
>> locking is only used when overlapped I/O is enabled.
>
> That applies only to oplocks that are requested manually by
> an application through FSCTL_ control codes:
> http://msdn.microsoft.com/en-us/library/windows/desktop/aa365438(v=vs.85).aspx
>
> Windows can also request oplocks automatically, and this happens for
> both synchronous and asynchronous I/O.  (Internally, even synchronous
> operations are implemented using overlapped I/O:
> .)
>
>
> On OSes before Vista/Server 2008, oplocks were incompatible with byte
> range locks (which SQLite uses), but this is unlikely to happen
> nowadays.
>
>
> Regards,
> Clemens
> ___
> 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] Creating a view

[Keith Medcalf]

I believe that unless you have constrained nR1 nR2 nR3 ... nR6 as NOT NULL then

select
   (select RefItem from REFTABLE where id=nR1),
   (select RefItem from REFTABLE where id=nR2),
   (select RefItem from REFTABLE where id=nR3),
   (select RefItem from REFTABLE where id=nR4),
   (select RefItem from REFTABLE where id=nR5),
   (select RefItem from REFTABLE where id=nR6)
from TBL;

written in join syntax needs to be expressed thusly:

select ref1.RetItem, ref2.RetItem, ref3.RetItem, ref4.RetItem, 
ref5.RetItem, ref6.RetItem
  from TBL 
outer join REFTABLE ref1 on (nR1=ref1.id)
outer join REFTABLE ref2 on (nR2=ref2.id)
outer join REFTABLE ref3 on (nR3=ref3.id)
outer join REFTABLE ref4 on (nR4=ref4.id)
outer join REFTABLE ref5 on (nR5=ref5.id)
outer join REFTABLE ref6 on (nR6=ref6.id);

in order to obtain the comparable result sets where there is a NULL value.  

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> On 10/17/2012 4:23 PM, Gert Van Assche wrote:
> > I don't know how to do something very simple like this. I have two table
> > and I would like to see the value of one table as it is expressed in the
> > other.
> >
> > CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> > [RefItem] CHAR);
> > INSERT INTO [REFTABLE]([RefItem]) VALUES('One');
> > INSERT INTO [REFTABLE]([RefItem]) VALUES('Two');
> > INSERT INTO [REFTABLE]([RefItem]) VALUES('Three');
> >
> > CREATE TABLE [TBL] (
> >id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> >[nR1] CONSTRAINT [r1] REFERENCES [REFTABLE]([id]),
> >[nR2] CONSTRAINT [r2] REFERENCES [REFTABLE]([id]),
> >[nR3] CONSTRAINT [r3] REFERENCES [REFTABLE]([id]),
> >[nR4] CONSTRAINT [r4] REFERENCES [REFTABLE]([id]),
> >[nR5] CONSTRAINT [r5] REFERENCES [REFTABLE]([id]),
> >[nR6] CONSTRAINT [r6] REFERENCES [REFTABLE]([id])
> >);
> >
> > INSERT INTO [TBL]([nR1], [nR2], [nR3], [nR4], [nR5], [nR6]) VALUES('1',
> > '2', '2', '3', '3', '3');
> >
> > I would like to do a select from TBL where I would see this:
> > 'One', 'Two', 'Two', 'Three', 'Three', 'Three'
> 
> select
>(select RefItem from REFTABLE where id=nR1),
>(select RefItem from REFTABLE where id=nR2),
>(select RefItem from REFTABLE where id=nR3),
>(select RefItem from REFTABLE where id=nR4),
>(select RefItem from REFTABLE where id=nR5),
>(select RefItem from REFTABLE where id=nR6)
> from TBL;
> 
> -- or
> 
> select ref1.RetItem, ref2.RetItem, ref3.RetItem, ref4.RetItem,
> ref5.RetItem, ref6.RetItem
> from TBL join REFTABLE ref1 on (nR1=ref1.id)
>join REFTABLE ref2 on (nR2=ref2.id)
>join REFTABLE ref3 on (nR3=ref3.id)
>join REFTABLE ref4 on (nR4=ref4.id)
>join REFTABLE ref5 on (nR5=ref5.id)
>join REFTABLE ref6 on (nR6=ref6.id);
> 
> --
> Igor Tandetnik




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

Re: [sqlite] System.Data.Sqlite writing chunks to a BLOB

[Simon Slavin]

On 17 Oct 2012, at 11:59pm, Mike King  wrote:

> I'm using the latest System.Data.Sqlite with c# and .Net 4.
> 
> Is there any method of writing to a BLOB in byte array chunks rather
> than in one big lump? (I can see how using SQLiteDataReader GetBytes I
> can read a blob back in chunks).

SQLite proves the equivalent routines:

http://www.sqlite.org/c3ref/blob_open.html
http://www.sqlite.org/c3ref/blob_write.html
http://www.sqlite.org/c3ref/blob_close.html

I don't know whether System.Data.Sqlite can call them but the underlying engine 
supports what you want.

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

[sqlite] System.Data.Sqlite writing chunks to a BLOB

[Mike King]

Hi All,

I'm using the latest System.Data.Sqlite with c# and .Net 4.

Is there any method of writing to a BLOB in byte array chunks rather
than in one big lump? (I can see how using SQLiteDataReader GetBytes I
can read a blob back in chunks).

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

Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Imanuel]

No, the performance stays the same.
I have also tried using a big cache_size, but that didn't change
anything, too.

Yes, I can share the database - it is currently uploading, I will mail
the link tomorrow.

Imanuel


Am 17.10.2012 22:08, schrieb Dan Kennedy:
> On 10/18/2012 01:32 AM, Imanuel wrote:
>> Hello
>>
>> I tested this on an SSD with a database with one single table with
>> 5,553,534 entries:
>> CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang TEXT,
>> name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll INTEGER
>> DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7)
>>
>> When running this command:
>> CREATE INDEX idx_namen_name ON Namen(name)
>>
>> Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36
>> seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds.
>> Indexing the column "geonameid" makes 24 vs. 312 seconds.
>> Neither of the both columns are presorted.
>
> If you set "PRAGMA temp_store = memory" in 3.7.14.1 is the
> performance the same as in 3.6.22?
>
> Are you able to share the database?
>
> ___
> 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] Fixing some valgrind issues

[Alfred Sawaya]

calloc is C89 and C99. Is there any problem using it in sqlite ?

2012/10/17 Richard Hipp 

> On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya  wrote:
>
> > Hello,
> >
> >
> > Sqlite has some minor valgrind issues (some memory area point to
> > unitialized bytes).
> > You can easily avoid this by replacing malloc by calloc in src/mem1.c:84
> > and src/mem2.c:255
> >
> > Is it possible to integrate those improvements into the mainline of
> Sqlite
> > ?
> >
>
> Thanks for the data and the suggestion.  But I think we'll omit this change
> from SQLite for now
>
>
>
> >
> > Thank you,
> >
> > Alfred.
> >
> > --
> >
> > http://alfred.sawaya.tel
> >
> > .''`.
> >: :'  :   .:: Alfred Sawaya ::.
> >`. `'`
> >  `-
> > ___
> > 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
>



-- 

http://alfred.sawaya.tel

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

Re: [sqlite] Fixing some valgrind issues

[Richard Hipp]

On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya  wrote:

> Hello,
>
>
> Sqlite has some minor valgrind issues (some memory area point to
> unitialized bytes).
> You can easily avoid this by replacing malloc by calloc in src/mem1.c:84
> and src/mem2.c:255
>
> Is it possible to integrate those improvements into the mainline of Sqlite
> ?
>

Thanks for the data and the suggestion.  But I think we'll omit this change
from SQLite for now



>
> Thank you,
>
> Alfred.
>
> --
>
> http://alfred.sawaya.tel
>
> .''`.
>: :'  :   .:: Alfred Sawaya ::.
>`. `'`
>  `-
> ___
> 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

Re: [sqlite] Fixing some valgrind issues

[Alfred Sawaya]

It is not related to a particular SQL request. And the errors are corrected
by using calloc instead of malloc in mem1.c and mem2.c

Maybe sqlite team prefer to let the caller memset the allocated area ?

Anyway,

For this one :

==32575== Conditional jump or move depends on uninitialised value(s)
==32575==at 0x17F93C: sqlite3PagerSharedLock (in main.exe)
==32575==by 0x1887D0: lockBtree (in main.exe)
==32575==by 0x188FB1: sqlite3BtreeBeginTrans (in main.exe)
==32575==by 0x1A0723: sqlite3VdbeExec (in main.exe)
==32575==by 0x19A76A: sqlite3Step (in main.exe)
==32575==by 0x19A93F: sqlite3_step (in main.exe)
==32575==by 0x1C6863: sqlite3_exec (in main.exe)
==32575==by 0x13764E: main (main.c:341)
==32575==  Uninitialised value was created by a heap allocation
==32575==at 0x4C2779D: malloc (in
/usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==32575==by 0x16DAE4: sqlite3MemMalloc (in main.exe)
==32575==by 0x16E4DF: mallocWithAlarm (in main.exe)
==32575==by 0x16E57A: sqlite3Malloc (in main.exe)
==32575==by 0x168503: sqlcipher_malloc (in main.exe)
==32575==by 0x16905E: sqlcipher_codec_ctx_set_pagesize (in main.exe)
==32575==by 0x1691DD: sqlcipher_codec_ctx_init (in main.exe)
==32575==by 0x167F96: sqlite3CodecAttach (in main.exe)
==32575==by 0x168096: sqlite3_key (in main.exe)
==32575==by 0x137191: main (main.c:255)

The SQL request is :


SELECT cast(CPU_ID as integer)||\",\"||STRFTIME(\"%Y-%m-%d %H:%M:00\",
TMS_LOAD)||\",\"||case when avg(CPU_PRC_PROCESS_TIME) < -1 then \"-1\"
else avg(CPU_PRC_PROCESS_TIME)end||\",\"||case when avg(CPU_PRC_IDLE)
< -1 then \"-1\" else avg(CPU_PRC_IDLE)end||\",\"||case when
avg(CPU_PRC_IOWAIT) < -1 then \"-1\" else avg(CPU_PRC_IOWAIT) end FROM
METR_CPU JOIN PRM_LOAD ON PK_LOAD_ID = FK_LOAD_ID  GROUP BY CPU_ID,
STRFTIME(\"%Y-%m-%d %H:%M:00\", TMS_LOAD);"


2012/10/17 Richard Hipp 

> On Wed, Oct 17, 2012 at 5:18 PM, Alfred Sawaya  wrote:
>
> > The list block big messages...
> >
> > Here is a pastebin : http://pastebin.com/raw.php?i=QjN18m4h
> >
>
> Can you show us what SQL you are running in order to get these errors?
>
>
> >
> > 2012/10/17 Richard Hipp 
> >
> > > On Wed, Oct 17, 2012 at 4:56 PM, Alfred Sawaya  wrote:
> > >
> > > > I send you the valgrind report, in attached file.
> > > >
> > >
> > > This mailing list deletes attachments.  Please include the valgrind
> > report
> > > inline.  Thanks.
> > >
> > >
> > > >
> > > > I use sqlite with sqlcipher but it is not a sqlcipher related issue I
> > > think
> > > > (please see the sqlcipher team reply :
> > > > https://github.com/sqlcipher/sqlcipher/issues/33 ).
> > > >
> > > > Thank you.
> > > >
> > > > 2012/10/17 Richard Hipp 
> > > >
> > > > > On Wed, Oct 17, 2012 at 4:36 PM, Richard Hipp 
> > wrote:
> > > > >
> > > > > >
> > > > > >
> > > > > > On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya 
> > > wrote:
> > > > > >
> > > > > >> Hello,
> > > > > >>
> > > > > >>
> > > > > >> Sqlite has some minor valgrind issues (some memory area point to
> > > > > >> unitialized bytes).
> > > > > >>
> > > > > >
> > > > > > Really?  We run many of our test cases here through valgrind and
> > > don't
> > > > > see
> > > > > > any problems.  Can you be more specific?
> > > > > >
> > > > >
> > > > > See checklist items 13f and 13g:
> > > > > http://www.sqlite.org/checklists/3071400#c13
> > > > >
> > > > >
> > > > > >
> > > > > >
> > > > > >> You can easily avoid this by replacing malloc by calloc in
> > > > src/mem1.c:84
> > > > > >> and src/mem2.c:255
> > > > > >>
> > > > > >> Is it possible to integrate those improvements into the mainline
> > of
> > > > > >> Sqlite ?
> > > > > >>
> > > > > >> Thank you,
> > > > > >>
> > > > > >> Alfred.
> > > > > >>
> > > > > >> --
> > > > > >>
> > > > > >> http://alfred.sawaya.tel
> > > > > >>
> > > > > >> .''`.
> > > > > >>: :'  :   .:: Alfred Sawaya ::.
> > > > > >>`. `'`
> > > > > >>  `-
> > > > > >> ___
> > > > > >> sqlite-users mailing list
> > > > > >> sqlite-users@sqlite.org
> > > > > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > D. Richard Hipp
> > > > > > d...@sqlite.org
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > D. Richard Hipp
> > > > > d...@sqlite.org
> > > > > ___
> > > > > sqlite-users mailing list
> > > > > sqlite-users@sqlite.org
> > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > > http://alfred.sawaya.tel
> > > >
> > > > .''`.
> > > >: :'  :   .:: Alfred Sawaya ::.
> > > >`. `'`
> > > >  `-
> > > >
> > > > ___
> > > > sqlite-users mailing list
> > > > 

Re: [sqlite] Fixing some valgrind issues

[Richard Hipp]

On Wed, Oct 17, 2012 at 5:18 PM, Alfred Sawaya  wrote:

> The list block big messages...
>
> Here is a pastebin : http://pastebin.com/raw.php?i=QjN18m4h
>

Can you show us what SQL you are running in order to get these errors?


>
> 2012/10/17 Richard Hipp 
>
> > On Wed, Oct 17, 2012 at 4:56 PM, Alfred Sawaya  wrote:
> >
> > > I send you the valgrind report, in attached file.
> > >
> >
> > This mailing list deletes attachments.  Please include the valgrind
> report
> > inline.  Thanks.
> >
> >
> > >
> > > I use sqlite with sqlcipher but it is not a sqlcipher related issue I
> > think
> > > (please see the sqlcipher team reply :
> > > https://github.com/sqlcipher/sqlcipher/issues/33 ).
> > >
> > > Thank you.
> > >
> > > 2012/10/17 Richard Hipp 
> > >
> > > > On Wed, Oct 17, 2012 at 4:36 PM, Richard Hipp 
> wrote:
> > > >
> > > > >
> > > > >
> > > > > On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya 
> > wrote:
> > > > >
> > > > >> Hello,
> > > > >>
> > > > >>
> > > > >> Sqlite has some minor valgrind issues (some memory area point to
> > > > >> unitialized bytes).
> > > > >>
> > > > >
> > > > > Really?  We run many of our test cases here through valgrind and
> > don't
> > > > see
> > > > > any problems.  Can you be more specific?
> > > > >
> > > >
> > > > See checklist items 13f and 13g:
> > > > http://www.sqlite.org/checklists/3071400#c13
> > > >
> > > >
> > > > >
> > > > >
> > > > >> You can easily avoid this by replacing malloc by calloc in
> > > src/mem1.c:84
> > > > >> and src/mem2.c:255
> > > > >>
> > > > >> Is it possible to integrate those improvements into the mainline
> of
> > > > >> Sqlite ?
> > > > >>
> > > > >> Thank you,
> > > > >>
> > > > >> Alfred.
> > > > >>
> > > > >> --
> > > > >>
> > > > >> http://alfred.sawaya.tel
> > > > >>
> > > > >> .''`.
> > > > >>: :'  :   .:: Alfred Sawaya ::.
> > > > >>`. `'`
> > > > >>  `-
> > > > >> ___
> > > > >> sqlite-users mailing list
> > > > >> sqlite-users@sqlite.org
> > > > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > >>
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > D. Richard Hipp
> > > > > d...@sqlite.org
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > D. Richard Hipp
> > > > d...@sqlite.org
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > >
> > > http://alfred.sawaya.tel
> > >
> > > .''`.
> > >: :'  :   .:: Alfred Sawaya ::.
> > >`. `'`
> > >  `-
> > >
> > > ___
> > > 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
> >
>
>
>
> --
>
> http://alfred.sawaya.tel
>
> .''`.
>: :'  :   .:: Alfred Sawaya ::.
>`. `'`
>  `-
> ___
> 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

Re: [sqlite] Fixing some valgrind issues

[Alfred Sawaya]

The list block big messages...

Here is a pastebin : http://pastebin.com/raw.php?i=QjN18m4h

2012/10/17 Richard Hipp 

> On Wed, Oct 17, 2012 at 4:56 PM, Alfred Sawaya  wrote:
>
> > I send you the valgrind report, in attached file.
> >
>
> This mailing list deletes attachments.  Please include the valgrind report
> inline.  Thanks.
>
>
> >
> > I use sqlite with sqlcipher but it is not a sqlcipher related issue I
> think
> > (please see the sqlcipher team reply :
> > https://github.com/sqlcipher/sqlcipher/issues/33 ).
> >
> > Thank you.
> >
> > 2012/10/17 Richard Hipp 
> >
> > > On Wed, Oct 17, 2012 at 4:36 PM, Richard Hipp  wrote:
> > >
> > > >
> > > >
> > > > On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya 
> wrote:
> > > >
> > > >> Hello,
> > > >>
> > > >>
> > > >> Sqlite has some minor valgrind issues (some memory area point to
> > > >> unitialized bytes).
> > > >>
> > > >
> > > > Really?  We run many of our test cases here through valgrind and
> don't
> > > see
> > > > any problems.  Can you be more specific?
> > > >
> > >
> > > See checklist items 13f and 13g:
> > > http://www.sqlite.org/checklists/3071400#c13
> > >
> > >
> > > >
> > > >
> > > >> You can easily avoid this by replacing malloc by calloc in
> > src/mem1.c:84
> > > >> and src/mem2.c:255
> > > >>
> > > >> Is it possible to integrate those improvements into the mainline of
> > > >> Sqlite ?
> > > >>
> > > >> Thank you,
> > > >>
> > > >> Alfred.
> > > >>
> > > >> --
> > > >>
> > > >> http://alfred.sawaya.tel
> > > >>
> > > >> .''`.
> > > >>: :'  :   .:: Alfred Sawaya ::.
> > > >>`. `'`
> > > >>  `-
> > > >> ___
> > > >> sqlite-users mailing list
> > > >> sqlite-users@sqlite.org
> > > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >>
> > > >
> > > >
> > > >
> > > > --
> > > > D. Richard Hipp
> > > > d...@sqlite.org
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> >
> > http://alfred.sawaya.tel
> >
> > .''`.
> >: :'  :   .:: Alfred Sawaya ::.
> >`. `'`
> >  `-
> >
> > ___
> > 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
>



-- 

http://alfred.sawaya.tel

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

Re: [sqlite] Fixing some valgrind issues

[Richard Hipp]

On Wed, Oct 17, 2012 at 4:56 PM, Alfred Sawaya  wrote:

> I send you the valgrind report, in attached file.
>

This mailing list deletes attachments.  Please include the valgrind report
inline.  Thanks.


>
> I use sqlite with sqlcipher but it is not a sqlcipher related issue I think
> (please see the sqlcipher team reply :
> https://github.com/sqlcipher/sqlcipher/issues/33 ).
>
> Thank you.
>
> 2012/10/17 Richard Hipp 
>
> > On Wed, Oct 17, 2012 at 4:36 PM, Richard Hipp  wrote:
> >
> > >
> > >
> > > On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya  wrote:
> > >
> > >> Hello,
> > >>
> > >>
> > >> Sqlite has some minor valgrind issues (some memory area point to
> > >> unitialized bytes).
> > >>
> > >
> > > Really?  We run many of our test cases here through valgrind and don't
> > see
> > > any problems.  Can you be more specific?
> > >
> >
> > See checklist items 13f and 13g:
> > http://www.sqlite.org/checklists/3071400#c13
> >
> >
> > >
> > >
> > >> You can easily avoid this by replacing malloc by calloc in
> src/mem1.c:84
> > >> and src/mem2.c:255
> > >>
> > >> Is it possible to integrate those improvements into the mainline of
> > >> Sqlite ?
> > >>
> > >> Thank you,
> > >>
> > >> Alfred.
> > >>
> > >> --
> > >>
> > >> http://alfred.sawaya.tel
> > >>
> > >> .''`.
> > >>: :'  :   .:: Alfred Sawaya ::.
> > >>`. `'`
> > >>  `-
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users@sqlite.org
> > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
>
> http://alfred.sawaya.tel
>
> .''`.
>: :'  :   .:: Alfred Sawaya ::.
>`. `'`
>  `-
>
> ___
> 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

Re: [sqlite] Creating a view

[Igor Tandetnik]

On 10/17/2012 4:23 PM, Gert Van Assche wrote:

I don't know how to do something very simple like this. I have two table
and I would like to see the value of one table as it is expressed in the
other.

CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[RefItem] CHAR);
INSERT INTO [REFTABLE]([RefItem]) VALUES('One');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Two');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Three');

CREATE TABLE [TBL] (
   id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
   [nR1] CONSTRAINT [r1] REFERENCES [REFTABLE]([id]),
   [nR2] CONSTRAINT [r2] REFERENCES [REFTABLE]([id]),
   [nR3] CONSTRAINT [r3] REFERENCES [REFTABLE]([id]),
   [nR4] CONSTRAINT [r4] REFERENCES [REFTABLE]([id]),
   [nR5] CONSTRAINT [r5] REFERENCES [REFTABLE]([id]),
   [nR6] CONSTRAINT [r6] REFERENCES [REFTABLE]([id])
   );

INSERT INTO [TBL]([nR1], [nR2], [nR3], [nR4], [nR5], [nR6]) VALUES('1',
'2', '2', '3', '3', '3');

I would like to do a select from TBL where I would see this:
'One', 'Two', 'Two', 'Three', 'Three', 'Three'


select
  (select RefItem from REFTABLE where id=nR1),
  (select RefItem from REFTABLE where id=nR2),
  (select RefItem from REFTABLE where id=nR3),
  (select RefItem from REFTABLE where id=nR4),
  (select RefItem from REFTABLE where id=nR5),
  (select RefItem from REFTABLE where id=nR6)
from TBL;

-- or

select ref1.RetItem, ref2.RetItem, ref3.RetItem, ref4.RetItem, 
ref5.RetItem, ref6.RetItem

from TBL join REFTABLE ref1 on (nR1=ref1.id)
  join REFTABLE ref2 on (nR2=ref2.id)
  join REFTABLE ref3 on (nR3=ref3.id)
  join REFTABLE ref4 on (nR4=ref4.id)
  join REFTABLE ref5 on (nR5=ref5.id)
  join REFTABLE ref6 on (nR6=ref6.id);

--
Igor Tandetnik

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

Re: [sqlite] Fixing some valgrind issues

[Alfred Sawaya]

I send you the valgrind report, in attached file.

I use sqlite with sqlcipher but it is not a sqlcipher related issue I think
(please see the sqlcipher team reply :
https://github.com/sqlcipher/sqlcipher/issues/33 ).

Thank you.

2012/10/17 Richard Hipp 

> On Wed, Oct 17, 2012 at 4:36 PM, Richard Hipp  wrote:
>
> >
> >
> > On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya  wrote:
> >
> >> Hello,
> >>
> >>
> >> Sqlite has some minor valgrind issues (some memory area point to
> >> unitialized bytes).
> >>
> >
> > Really?  We run many of our test cases here through valgrind and don't
> see
> > any problems.  Can you be more specific?
> >
>
> See checklist items 13f and 13g:
> http://www.sqlite.org/checklists/3071400#c13
>
>
> >
> >
> >> You can easily avoid this by replacing malloc by calloc in src/mem1.c:84
> >> and src/mem2.c:255
> >>
> >> Is it possible to integrate those improvements into the mainline of
> >> Sqlite ?
> >>
> >> Thank you,
> >>
> >> Alfred.
> >>
> >> --
> >>
> >> http://alfred.sawaya.tel
> >>
> >> .''`.
> >>: :'  :   .:: Alfred Sawaya ::.
> >>`. `'`
> >>  `-
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

http://alfred.sawaya.tel

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

Re: [sqlite] Fixing some valgrind issues

[Richard Hipp]

On Wed, Oct 17, 2012 at 4:36 PM, Richard Hipp  wrote:

>
>
> On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya  wrote:
>
>> Hello,
>>
>>
>> Sqlite has some minor valgrind issues (some memory area point to
>> unitialized bytes).
>>
>
> Really?  We run many of our test cases here through valgrind and don't see
> any problems.  Can you be more specific?
>

See checklist items 13f and 13g:
http://www.sqlite.org/checklists/3071400#c13


>
>
>> You can easily avoid this by replacing malloc by calloc in src/mem1.c:84
>> and src/mem2.c:255
>>
>> Is it possible to integrate those improvements into the mainline of
>> Sqlite ?
>>
>> Thank you,
>>
>> Alfred.
>>
>> --
>>
>> http://alfred.sawaya.tel
>>
>> .''`.
>>: :'  :   .:: Alfred Sawaya ::.
>>`. `'`
>>  `-
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



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

Re: [sqlite] Fixing some valgrind issues

[Richard Hipp]

On Wed, Oct 17, 2012 at 4:33 PM, Alfred Sawaya  wrote:

> Hello,
>
>
> Sqlite has some minor valgrind issues (some memory area point to
> unitialized bytes).
>

Really?  We run many of our test cases here through valgrind and don't see
any problems.  Can you be more specific?


> You can easily avoid this by replacing malloc by calloc in src/mem1.c:84
> and src/mem2.c:255
>
> Is it possible to integrate those improvements into the mainline of Sqlite
> ?
>
> Thank you,
>
> Alfred.
>
> --
>
> http://alfred.sawaya.tel
>
> .''`.
>: :'  :   .:: Alfred Sawaya ::.
>`. `'`
>  `-
> ___
> 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] Fixing some valgrind issues

[Alfred Sawaya]

Hello,


Sqlite has some minor valgrind issues (some memory area point to
unitialized bytes).
You can easily avoid this by replacing malloc by calloc in src/mem1.c:84
and src/mem2.c:255

Is it possible to integrate those improvements into the mainline of Sqlite ?

Thank you,

Alfred.

-- 

http://alfred.sawaya.tel

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

[sqlite] Creating a view

[Gert Van Assche]

All,

I don't know how to do something very simple like this. I have two table
and I would like to see the value of one table as it is expressed in the
other.

CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[RefItem] CHAR);
INSERT INTO [REFTABLE]([RefItem]) VALUES('One');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Two');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Three');

CREATE TABLE [TBL] (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  [nR1] CONSTRAINT [r1] REFERENCES [REFTABLE]([id]),
  [nR2] CONSTRAINT [r2] REFERENCES [REFTABLE]([id]),
  [nR3] CONSTRAINT [r3] REFERENCES [REFTABLE]([id]),
  [nR4] CONSTRAINT [r4] REFERENCES [REFTABLE]([id]),
  [nR5] CONSTRAINT [r5] REFERENCES [REFTABLE]([id]),
  [nR6] CONSTRAINT [r6] REFERENCES [REFTABLE]([id])
  );

INSERT INTO [TBL]([nR1], [nR2], [nR3], [nR4], [nR5], [nR6]) VALUES('1',
'2', '2', '3', '3', '3');

I would like to do a select from TBL where I would see this:
'One', 'Two', 'Two', 'Three', 'Three', 'Three'

How should I do this? If there is just one field, I can use a JOIN, but
with several fields I don't see how to do this. Maybe I'm also doing this
completely wrong...

thanks

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

Re: [sqlite] Bug report: Out-Of-Memory error when doing DELETE from a table with 150 million records

[Richard Hipp]

On Wed, Oct 17, 2012 at 11:58 AM, Ivan P  wrote:

> Hello!
>
> I've got Out-Of-Memory error when delete records from a table that has
> about 150,000,000 records.
>
> The table is created as:
> CREATE TABLE differential_parts_temp (plan_id TEXT, [version_id] INT NOT
> NULL, [page_index] INT NOT NULL, [md5low] INT64 NOT NULL, [md5high] INT64
> NOT NULL);
> CREATE INDEX differentialpartstemp_versionid ON differential_parts_temp
> (version_id, plan_id);
>
> It has approx 150,000,000 records.
> The VERY MOST (if not all) of those records have
> plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'
>
> The following request leads to "Out of Memory" error (the application eats
> all the free memory up to 3.5 Gb , and then fails)
> DELETE FROM differential_parts_temp WHERE
> plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'
>

In order to do a DELETE, SQLite first identifies every row that needs
deleting.  It remembers the rowid of each such row in memory.  Normally
this works fine, but it can give problems when you are trying to delete
150M rows, apparently.

One possible work-around:

CREATE TABLE new_dpt AS SELECT * FROM differential_parts_temp WHERE
plan_id<>bf43c9ae-d681-4f2a-be19-0e0426db2b43';
DROP TABLE differential_parts_temp;
ALTER TABLE new_dpt RENAME AS differential_parts_temp;

Another possible work-around:

DELETE FROM differential_parts_temp WHERE rowid IN (SELECT rowid FROM
differential_parts_temp WHERE
plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43' LIMIT 100);
-- repeat the previous statement 150 times, or until sqlite3_changes()
returns zero.



>
> I tried different SQLite clients including the following precompiled
> binary:
> http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip
>
> Why the DELETE statement can eat so much memory?
>
> The total database size is 20 GBytes.
> SQLite Expert shows the following DB properties:
> auto_vacuum=none
> automatic_index=on
> cache_size=2000
> case_sensitive_like=off
> collation_list=[NOCASE], [RTRIM], [BINARY]
> count_changes=off
> default_cache_size=2000
> empty_result_callbacks=off
> encoding=UTF-8
> foreign_keys=on
> freelist_count=0
> full_column_names=off
> fullfsync=off
> journal_mode=delete
> journal_size_limit=-1
> legacy_file_format=off
> locking_mode=normal
> max_page_count=1073741823
> page_count=20719252
> page_size=1024
> read_uncommitted=off
> recursive_triggers=off
> reverse_unordered_selects=off
> schema_version=27
> secure_delete=off
> short_column_names=on
> synchronous=full
> temp_store=default
> temp_store_directory=""
> user_version=0
> wal_autocheckpoint=1000
>
> Thanks,
> IP
> ___
> 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

Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Dan Kennedy]

On 10/18/2012 01:32 AM, Imanuel wrote:

Hello

I tested this on an SSD with a database with one single table with
5,553,534 entries:
CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang TEXT,
name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll INTEGER
DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7)

When running this command:
CREATE INDEX idx_namen_name ON Namen(name)

Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36
seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds.
Indexing the column "geonameid" makes 24 vs. 312 seconds.
Neither of the both columns are presorted.


If you set "PRAGMA temp_store = memory" in 3.7.14.1 is the
performance the same as in 3.6.22?

Are you able to share the database?

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

[sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Imanuel]

Hello

I tested this on an SSD with a database with one single table with
5,553,534 entries:
CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang TEXT,
name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll INTEGER
DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7)

When running this command:
CREATE INDEX idx_namen_name ON Namen(name)

Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36
seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds.
Indexing the column "geonameid" makes 24 vs. 312 seconds.
Neither of the both columns are presorted.

When I stumbled upon this for the first time, I experienced values of 18
vs 380 seconds but I can't reproduce them now because I don't have that
version of the database any more.

Is there anything I can change?

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

Re: [sqlite] Bug report: Out-Of-Memory error when doing DELETE from a table with 150 million records

[Simon Slavin]

On 17 Oct 2012, at 4:58pm, Ivan P  wrote:

> Why the DELETE statement can eat so much memory?

Because it doesn't delete each one row singly, doing all the file updates that 
are needed to delete that row, then move on to the next row.  If it did it 
would take an extremely long time to operate.  Nevertheless it is definitely 
not meant to ever crash in the way you're seeing.  It is meant to realise when 
its available memory is full, flush changes to disk, then carry on.

> SQLite Expert shows the following DB properties:

Some of these (e.g foreign_keys) are not properties of the database, but of the 
connection that SQLite Expert has opened to it, so they are not relevant to 
anything you've been doing in the shell tool.  So instead we ask the following 
questions:

Which version of Windows are you running ?  Did you specify any PRAGMAs in the 
shell tool ?  Are you opening the file on a local disk or across a file sharing 
connection ?

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

Re: [sqlite] pragma synchronous=off and journal_mode=wal still syncs to disk

[Bob Price]

strace is a nice tool.

Once I figured out that it needed the -f option (for threads maybe as I don't 
fork other processes) I got good output, and, no, it does not contain any fsync 
or any other *sync* calls.

So that answers my question.  Testing this out on a different Linux box I did 
not see the cpu and disk I/O patterns which led me to think that a sync was 
being done, so it must be some issue specific to the first host unrelated to 
Sqlite.


Thanks for your response!

Bob




 From: Richard Hipp 
To: Bob Price ; General Discussion of SQLite Database 
 
Sent: Wednesday, October 17, 2012 11:32 AM
Subject: Re: [sqlite] pragma synchronous=off and journal_mode=wal still syncs 
to disk
 




On Wed, Oct 17, 2012 at 11:04 AM, Bob Price  wrote:

It appears that regardless of a "pragma synchronous=off" that there are still 
syncs to disk done at key points in WAL mode such as in a "pragma 
wal_checkpoint(RESTART)".  I think that this is true based on the application 
cpu and disk I/O patterns I observe when logging shows the wal checkpoint to be 
in progress, while running on a Linux host with tons of free memory available 
for its file cache.
>

I ran test cases using "strace" and they show no fsyncs occurring when 
synchronous=off.  Do you have a script that shows an fsync (or fdatasync) using 
strace?
 

>Is this true?  Is there any way to configure a connection so that these syncs 
>do not happen in WAL mode?
>
>Thanks,
>Bob
>___
>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] Bug report: Out-Of-Memory error when doing DELETE from a table with 150 million records

[Ivan P]

Hello!

I've got Out-Of-Memory error when delete records from a table that has
about 150,000,000 records.

The table is created as:
CREATE TABLE differential_parts_temp (plan_id TEXT, [version_id] INT NOT
NULL, [page_index] INT NOT NULL, [md5low] INT64 NOT NULL, [md5high] INT64
NOT NULL);
CREATE INDEX differentialpartstemp_versionid ON differential_parts_temp
(version_id, plan_id);

It has approx 150,000,000 records.
The VERY MOST (if not all) of those records have
plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'

The following request leads to "Out of Memory" error (the application eats
all the free memory up to 3.5 Gb , and then fails)
DELETE FROM differential_parts_temp WHERE
plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'

I tried different SQLite clients including the following precompiled binary:
http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip

Why the DELETE statement can eat so much memory?

The total database size is 20 GBytes.
SQLite Expert shows the following DB properties:
auto_vacuum=none
automatic_index=on
cache_size=2000
case_sensitive_like=off
collation_list=[NOCASE], [RTRIM], [BINARY]
count_changes=off
default_cache_size=2000
empty_result_callbacks=off
encoding=UTF-8
foreign_keys=on
freelist_count=0
full_column_names=off
fullfsync=off
journal_mode=delete
journal_size_limit=-1
legacy_file_format=off
locking_mode=normal
max_page_count=1073741823
page_count=20719252
page_size=1024
read_uncommitted=off
recursive_triggers=off
reverse_unordered_selects=off
schema_version=27
secure_delete=off
short_column_names=on
synchronous=full
temp_store=default
temp_store_directory=""
user_version=0
wal_autocheckpoint=1000

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

Re: [sqlite] pragma synchronous=off and journal_mode=wal still syncs to disk

[Richard Hipp]

On Wed, Oct 17, 2012 at 11:04 AM, Bob Price  wrote:

> It appears that regardless of a "pragma synchronous=off" that there are
> still syncs to disk done at key points in WAL mode such as in a "pragma
> wal_checkpoint(RESTART)".  I think that this is true based on the
> application cpu and disk I/O patterns I observe when logging shows the wal
> checkpoint to be in progress, while running on a Linux host with tons of
> free memory available for its file cache.
>

I ran test cases using "strace" and they show no fsyncs occurring when
synchronous=off.  Do you have a script that shows an fsync (or fdatasync)
using strace?


>
> Is this true?  Is there any way to configure a connection so that these
> syncs do not happen in WAL mode?
>
> Thanks,
> Bob
> ___
> 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] pragma synchronous=off and journal_mode=wal still syncs to disk

[Bob Price]

It appears that regardless of a "pragma synchronous=off" that there are still 
syncs to disk done at key points in WAL mode such as in a "pragma 
wal_checkpoint(RESTART)".  I think that this is true based on the application 
cpu and disk I/O patterns I observe when logging shows the wal checkpoint to be 
in progress, while running on a Linux host with tons of free memory available 
for its file cache.

Is this true?  Is there any way to configure a connection so that these syncs 
do not happen in WAL mode?

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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Pavel Ivanov]

The problem is you are starting read-only transaction by executing
SELECT and then try to convert this transaction into writing one by
executing BEGIN IMMEDIATE. If in such situation SQLITE_BUSY is
returned you have to finish the transaction and start it again. In
your code solution is easy: finalize SELECT statement before executing
BEGIN IMMEDIATE.

Pavel

On Wed, Oct 17, 2012 at 6:12 AM, Daniel Polski  wrote:
> Hello again,
> Attached is a test application which replicates the problem.
>
> I expected the transactions to block each other exactly like they do in the
> beginning (one connection successfully begins and the other receives
> SQLITE_BUSY), but I didn't expect the blocked connection to never get
> unlocked in the end. What's holding the lock on the database so that the
> "begin" can't proceed?
>
> Sample console output:
>
> sqlite3_libversion: 3.7.13
> sqlite3_sourceid: 2012-06-11 02:05:22
> f5b5a13f7394dc143aa136f1d4faba6839eaa6dc
> sqlite3_libversion_number: 3007013
> sqlite3_threadsafe: 1
> Creating thread 0
> Creating thread 1
>
> conn addrstatusquery
> ----
> 0x6a6278SuccessPRAGMA journal_mode=wal;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6bd678SuccessPRAGMA journal_mode=wal;
> 0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
> 0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
> (... And so on)
>
>
> Thank you in advance,
> Daniel
>
> ___
> 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] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Daniel Polski]

Hello again,
Attached is a test application which replicates the problem.

I expected the transactions to block each other exactly like they do in 
the beginning (one connection successfully begins and the other receives 
SQLITE_BUSY), but I didn't expect the blocked connection to never get 
unlocked in the end. What's holding the lock on the database so that the 
"begin" can't proceed?


Sample console output:

sqlite3_libversion: 3.7.13
sqlite3_sourceid: 2012-06-11 02:05:22 
f5b5a13f7394dc143aa136f1d4faba6839eaa6dc

sqlite3_libversion_number: 3007013
sqlite3_threadsafe: 1
Creating thread 0
Creating thread 1

conn addrstatusquery
----
0x6a6278SuccessPRAGMA journal_mode=wal;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6bd678SuccessPRAGMA journal_mode=wal;
0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
(... And so on)

Thank you in advance,
Daniel
#include 
#include 
#include 
#include 
#include 

#include "sqlite3.h"

#define DATABASE_PATH "/tmp/test.db"
#define THREAD_COUNT2

struct thread_data
{
int thread_id;
};

struct thread_data thread_data_array[THREAD_COUNT];

void execSQL(sqlite3* db, std::string query){
bool keep_trying = true;
while(keep_trying){
char* szError=0;
int return_code = sqlite3_exec(db, query.c_str(), 0, 0, 
);
if(return_code == SQLITE_OK){
std::cout<

Re: [sqlite] SQLite flush on disk to calc db file hash,how?

[Black, Michael (IS)]

Are you maybe using WAL mode?

Do you have any other files alongside your database like *.db-shm or *.db-wal?

If so, you can just cat all the files together and pipe through md5sum or such.


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of lebron james [lebron.m...@gmail.com]
Sent: Wednesday, October 17, 2012 4:51 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] SQLite flush on disk to calc db file hash,how?

I have program which with some period insert row in sqlite database. I need
calc hash sum of database file after each insert without close connection.
I have some problem with that, after insert database file hash sum are same
with they have before insert. Only after closing connection hash sum are
changed. How i can solve this problem?
platform are windows, and i dont have open transaction, just default opened
sqlite db file and simple insert, if i do COMMIT after INSERT query i have
exception "you dont have open transaction"
___
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] inserting record datinto mutliple tables with a composite primary key

[Keith Medcalf]

On Tuesday, 16 October, 2012, 11:51, LMHmedchem said:

You could always create a table JUST for for the compsite key which gives you 
JUST the single-key which you then use to look up data in the other tables.  If 
you specifically name the rowid columns, then you can use simple and efficient 
joins to retrieve the data from the subtables:

Create table masterkey(
rowkey integer primary key autoincrement, 
k1 integer not null, 
k2 integer not null, 
k3 integer not null, 
k4 integer not null,
unique (k1, k2, k3, k4));

create table sub1(
rowkey integer primary key references masterkey(rowkey),
...);
...

You then join the masterkey table to whatever subs you want to do the 
retrieval, and when storing data you store the masterkey first and use the 
rowkey (last_insert_rowid) for the inserts in the subtables.

Select * from masterkey, sub1 where k1=? And k2=? And k3=? And k4=? And 
masterkey.rowkey=sub1.rowkey;

If you are careful about your column names you could also just use a NATURAL 
JOIN to join the rowkey fields and not need the explicit equijoins on the 
rowkey in the select.

On all inserts you would first do the same operation against the masterkey 
table to get the appropriate rowkey -- first a lookup then an insert of the key 
if required.

> Hello Igor, thank you for the information, it is a big help.
> 
> > If you have a one-to-one relationship between two tables, is there a
> > reason why you don't simply combine the two into a single, wider table?
> 
> They way I think about a database is that you subdivide the data based
> on how you may want to retrieve it later. The main table is structure,
> and all of it's fields are mandatory not null (each record is a chemical
> structure). The other tables contain other data (about the chemical
> structure), such as available vendors, prices, experimentally measured
> values, and computer generated data. These fields may be null. Some of
> the other tables are fairly large (500-2500 cols), so I thought it would
> help make the query process more efficient if you could just search on
> the tables with the data you need and ignore others. If I am incorrect
> in thinking about the setup in this way, I would appreciate knowing
> about that.
> 
> > but the notion of having multiple primary keys doesn't seem quite right.
> 
> > Why is that? Basically, every table needs a primary key, whether
> > composite or otherwise. If this tuple of integers is the natural key for
> > your data, I don't see a problem.
> 
> I guess what I was thinking was that tables should not have independent
> primary keys if there is a 1:1 relationship in the data between the
> tables. The way I was thinking about this is that the primary key value
> assigned to a record when it was inserted to the first table would be
> copied and used to insert into the second table, etc. In my spreadsheet
> way of thinking, that is having a single primary key that is used in
> multiple tables. I'm trying to learn to think "database" and not
> "spreadsheet".
> 
> > There is - see http://sqlite.org/autoinc.html . Change your table to
> >
> > create table Structure (
> > id integer primary key,
> > i1 integer not null,
> > i2 integer not null,
> > i3 integer not null,
> > i4 integer not null,
> > ...
> > unique (i1, i2, i3, i4)
> > );
> >
> > Now, you can insert a record while leaving 'id' column out, and it will
> > be automatically assigned a unique integer value, which you can retrieve
> > with sqlite3_last_insert_rowid. You can then use that ID when inserting
> > records into your "satellite" tables.
> 
> Later on, when I need to lookup data from a record using the 4 key
> values, there would have to be a way to retrieve the unique integer
> value ROWID that corresponds to the 4 keys (was assigned by
> AUTOINCREMENT). If I use unique like above, how would the lookup on the
> 4 keys work?
> 
> LMHmedchem
> 
---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

[Simon Slavin]

On 16 Oct 2012, at 6:50pm, LMHmedchem  wrote:

> Some of 
> the other tables are fairly large (500-2500 cols)

A table with 500 columns is itself a bad sign.  You should be able to think 
about the entire table makeup in your head without needing to refer to written 
documentation.  Having hundreds of numbered columns, especially, is a sign that 
you didn't think your schema through.

> In my spreadsheet 
> way of thinking, that is having a single primary key that is used in 
> multiple tables. I'm trying to learn to think "database" and not 
> "spreadsheet".


Right.  Your database is not a spreadsheet.  You don't have to lay out 
everything into one rectangular grid, you can have multiple tables.

Refactor your database to make this table into a narrower set of data, either 
by splitting it into attributes or by implementing the hierarchy that it 
probably represents.  Once you've done this your problem with zeroblobs will 
vanish.  You will probably find that you don't need those rows at all, or that 
all the zeroblobs are all inherently at the end of rows.

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

Re: [sqlite] subscribe to mailing list

[Richard Hipp]

On Tue, Oct 16, 2012 at 2:03 PM, LMHmedchem wrote:

> I'm not having much luck with this.
>
> When I try to go to,
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> I get a "failed to connect",
> "The connection was refused when attempting to contact sqlite.org:8080."
>

My guess is that you are behind a restrictive corporate firewall that
prevents you from accessing HTTP on port 8080.  Talk to your IT staff.  Or,
take your laptop over to Starbucks and try it from there.


>
> I tried in both seamonkey and ie and get the same message.
>
> LMHmedchem
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/subscribe-to-mailing-list-tp64888p64903.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] SQLite flush on disk to calc db file hash,how?

[lebron james]

I have program which with some period insert row in sqlite database. I need
calc hash sum of database file after each insert without close connection.
I have some problem with that, after insert database file hash sum are same
with they have before insert. Only after closing connection hash sum are
changed. How i can solve this problem?
platform are windows, and i dont have open transaction, just default opened
sqlite db file and simple insert, if i do COMMIT after INSERT query i have
exception "you dont have open transaction"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Potential corruption on VACUUM crash when SQLITE_OMIT_AUTOVACUUM is defined

[Danny Couture]

I found a bug in latest (3.7.14.1) with a very specific #define that can causes 
a database corruption after truncation because of missing backup pages.

If you specify this define:
#define SQLITE_OMIT_AUTOVACUUM

And then execute a VACUUM operation that shrinks the database, due to the 
#ifndef at sqlite3.c:42830

The code that would otherwise proceed to backup pages before truncation will 
not be executed.

Even the comment is wrong, it CAN and WILL happen for a normal vacuum too, not 
just in auto vacuum.

/* If this transaction has made the database smaller, then all pages
  ** being discarded by the truncation must be written to the journal
  ** file. This can only happen in auto-vacuum mode.
  **
  ** Before reading the pages with page numbers larger than the
  ** current value of Pager.dbSize, set dbSize back to the value
  ** that it took at the start of the transaction. Otherwise, the
  ** calls to sqlite3PagerGet() return zeroed pages instead of
  ** reading data from the database file.
  */

So here is my fix... (just removed the #ifndef)

/* If this transaction has made the database smaller, then all pages
  ** being discarded by the truncation must be written to the journal
  ** file. This can happen in auto-vacuum mode and during a normal
  ** vacuum operation.
  **
  ** Before reading the pages with page numbers larger than the
  ** current value of Pager.dbSize, set dbSize back to the value
  ** that it took at the start of the transaction. Otherwise, the
  ** calls to sqlite3PagerGet() return zeroed pages instead of
  ** reading data from the database file.
  */
  if( pPager->dbSizedbOrigSize
   && pPager->journalMode!=PAGER_JOURNALMODE_OFF
  ){
Pgno i;   /* Iterator variable */
const Pgno iSkip = PAGER_MJ_PGNO(pPager); /* Pending lock page */
const Pgno dbSize = pPager->dbSize;   /* Database image size */
pPager->dbSize = pPager->dbOrigSize;
for( i=dbSize+1; i<=pPager->dbOrigSize; i++ ){
  if( !sqlite3BitvecTest(pPager->pInJournal, i) && i!=iSkip ){
PgHdr *pPage; /* Page to journal */
rc = sqlite3PagerGet(pPager, i, );
if( rc!=SQLITE_OK ) goto commit_phase_one_exit;
rc = sqlite3PagerWrite(pPage);
sqlite3PagerUnref(pPage);
if( rc!=SQLITE_OK ) goto commit_phase_one_exit;
  }
}
pPager->dbSize = dbSize;
  }


You can test it with a really simple application that I included in 
attachment...

You just have to put a breakpoint at sqlite3.c:56747 and step over it and then 
restart the app right there.

The next integrity_check will fail completely :)

Thanks

Danny Couture
Technical Architect
Ubisoft Montreal


#include "stdafx.h"

//DON'T FORGET TO COMPILE SQLITE WITH #define SQLITE_OMIT_AUTOVACUUM
#include "sqlite3.h"

int callback(void *, int argc, char ** argv, char ** x)
{
for (int i = 0; i < argc; ++i)
printf("%s, ", argv[i]);

printf("\n");

return 0;
}

int _tmain(int argc, _TCHAR* argv[])
{
sqlite3 * db;
sqlite3_open("test.db", );

char * errorMsg;

sqlite3_exec(db, "PRAGMA integrity_check", callback, 0, );

sqlite3_exec(db, "CREATE TABLE test (Key INT, Test DOUBLE, Text 
VARCHAR(1024))", callback, 0, );

sqlite3_exec(db, "BEGIN", callback, 0, );

//add some stuff
char query[1024];
for (int i = 0; i < 10; ++i)
{
sprintf_s(query, "INSERT INTO test VALUES(%d, %d.5, \"%d\")", i, i, i);
sqlite3_exec(db, query, callback, 0, );
}

sqlite3_exec(db, "COMMIT", callback, 0, );

//remove some of the stuff so the vacuum shrinks the DB
sqlite3_exec(db, "DELETE FROM test WHERE Key > 5000", callback, 0, 
);

//add a breakpoint at sqlite3.c:58390 and restart the application right 
there.
//the next integrity_check will fail completely...
sqlite3_exec(db, "VACUUM", callback, 0, );

return 0;
}

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

Re: [sqlite] subscribe to mailing list

[LMHmedchem]

I'm not having much luck with this.

When I try to go to,
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

I get a "failed to connect",
"The connection was refused when attempting to contact sqlite.org:8080."

I tried in both seamonkey and ie and get the same message.

LMHmedchem



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/subscribe-to-mailing-list-tp64888p64903.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

[LMHmedchem]

Hello Igor, thank you for the information, it is a big help.

> If you have a one-to-one relationship between two tables, is there a
> reason why you don't simply combine the two into a single, wider table?

They way I think about a database is that you subdivide the data based 
on how you may want to retrieve it later. The main table is structure, 
and all of it's fields are mandatory not null (each record is a chemical 
structure). The other tables contain other data (about the chemical 
structure), such as available vendors, prices, experimentally measured 
values, and computer generated data. These fields may be null. Some of 
the other tables are fairly large (500-2500 cols), so I thought it would 
help make the query process more efficient if you could just search on 
the tables with the data you need and ignore others. If I am incorrect 
in thinking about the setup in this way, I would appreciate knowing 
about that.

> but the notion of having multiple primary keys doesn't seem quite right.

> Why is that? Basically, every table needs a primary key, whether
> composite or otherwise. If this tuple of integers is the natural key for
> your data, I don't see a problem.

I guess what I was thinking was that tables should not have independent 
primary keys if there is a 1:1 relationship in the data between the 
tables. The way I was thinking about this is that the primary key value 
assigned to a record when it was inserted to the first table would be 
copied and used to insert into the second table, etc. In my spreadsheet 
way of thinking, that is having a single primary key that is used in 
multiple tables. I'm trying to learn to think "database" and not 
"spreadsheet".

> There is - see http://sqlite.org/autoinc.html . Change your table to
>
> create table Structure (
> id integer primary key,
> i1 integer not null,
> i2 integer not null,
> i3 integer not null,
> i4 integer not null,
> ...
> unique (i1, i2, i3, i4)
> );
>
> Now, you can insert a record while leaving 'id' column out, and it will
> be automatically assigned a unique integer value, which you can retrieve
> with sqlite3_last_insert_rowid. You can then use that ID when inserting
> records into your "satellite" tables.

Later on, when I need to lookup data from a record using the 4 key 
values, there would have to be a way to retrieve the unique integer 
value ROWID that corresponds to the 4 keys (was assigned by 
AUTOINCREMENT). If I use unique like above, how would the lookup on the 
4 keys work?

LMHmedchem




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p64902.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Zeroblob initialization memory allocation issue

[Dmitry Tsinin]

Hello Richard,

Thank You for the answer.
We think it would make sense to reflect this rule for zeroblobs in the 
documentation,
so other teams would save time not spending it when trying to fix bugs that 
don't really exist.
Probably, we just missed this mentioning in the documentation, so if we did, 
please point us to this.
Anyway, thanks a lot.

Dmitry Tsinin
OS33

From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp
Sent: Tuesday, October 16, 2012 4:24 PM
To: General Discussion of SQLite Database
Cc: Dmitry Tsinin; Dennis Davydov; Alex Osipov; Alexander Anikin
Subject: Re: [sqlite] Zeroblob initialization memory allocation issue


On Mon, Oct 15, 2012 at 4:15 PM, Petr Kovalev 
> wrote:
Hello worldwide Sqlite team!

We've found an issue caused by zeroblob initialization. Depending on the table 
columns creation order, zeroblob initialization causes huge memory allocation 
or not.

Attached sample project is for Mono, but we've tried the same scenario with 
native methods. In first case no additional memory is allocated, but in 
ZeroblobFail method approx 100Mb of memory is allocated for a quick period of 
zeroblob creation.

Could you please help to explain the behavior and provide possible workarounds?

Here is quote:
http://www.sqlite.org/c3ref/bind_blob.html
http://www.sqlite.org/c3ref/blob_open.html> routines>

In order for zeroblobs to work as above (using a fixed amount of memory no 
matter how big they are) all zeroblobs must be at the end of the row.  In other 
words, the columns of the table that are receiving the zeroblobs must be the 
last columns in the table.  If any non-zero content follows the zeroblob, then 
the zeroblob is expanded into a literal sequence of zero bytes, meaning memory 
must be allocated for the entire zeroblob.



Any advice would be great.

Thanks.

Petr.

___
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

Re: [sqlite] sqlite3.dll no longer operative

[OBones]

Actually, the 32 bits version should go to c:\windows\syswow64 as 
c:\windows\system32 is for 64 bits DLLs on a 64bits Windows.


Stephen Chrzanowski wrote:

This "smells" like a program that is either installed by you, or, from your
CD when you reloaded, is looking for the sqlite3.dll but can't find it.
Download the Win32 version from the SQLite site and extract the DLL to the
c:\windows\system32 directory (You'll need administrative rights) and you
should be alright after a reboot.

On Tue, Oct 16, 2012 at 12:56 PM, Navaneeth.K.N wrote:


On Oct 11, 2012 5:58 PM, "L. Dale Rohl"  wrote:

I am using 64bit Window 7 on my stand alone Toshiba Computer. Recently I
lost a Hard Drive and had it replaced. All is well but during activation

of

the computer a pop-up is on the screen that says that "sqlite3.dll" has

been

lost and I need to download and replace the file.



I have looked and looked and finally found you and need your assistance
about how to download this file and cure the problem.

AFAIK, there is no pre built 64 bit version dll available to dowload. You
might have to download amalgamation and compile the dll yourself.




Thanks for your help.



Regards,



Dale



L. Dale Rohl, President

ROHL MORTGAGE CAPITAL CORPORATION

602 SE 131st Court

Vancouver, WA 98683-4001

Telephone: 360-944-1440

Mobile: 360-921-6610

FAX: 360-892-4632



___
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] sqlite3.dll no longer operative

[Stephen Chrzanowski]

This "smells" like a program that is either installed by you, or, from your
CD when you reloaded, is looking for the sqlite3.dll but can't find it.
Download the Win32 version from the SQLite site and extract the DLL to the
c:\windows\system32 directory (You'll need administrative rights) and you
should be alright after a reboot.

On Tue, Oct 16, 2012 at 12:56 PM, Navaneeth.K.N wrote:

> On Oct 11, 2012 5:58 PM, "L. Dale Rohl"  wrote:
> >
> > I am using 64bit Window 7 on my stand alone Toshiba Computer. Recently I
> > lost a Hard Drive and had it replaced. All is well but during activation
> of
> > the computer a pop-up is on the screen that says that "sqlite3.dll" has
> been
> > lost and I need to download and replace the file.
> >
> >
> >
> > I have looked and looked and finally found you and need your assistance
> > about how to download this file and cure the problem.
>
> AFAIK, there is no pre built 64 bit version dll available to dowload. You
> might have to download amalgamation and compile the dll yourself.
>
> >
> >
> >
> > Thanks for your help.
> >
> >
> >
> > Regards,
> >
> >
> >
> > Dale
> >
> >
> >
> > L. Dale Rohl, President
> >
> > ROHL MORTGAGE CAPITAL CORPORATION
> >
> > 602 SE 131st Court
> >
> > Vancouver, WA 98683-4001
> >
> > Telephone: 360-944-1440
> >
> > Mobile: 360-921-6610
> >
> > FAX: 360-892-4632
> >
> >
> >
> > ___
> > 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] find sequential groups

[Bart Smissaert]

Thanks.
Have a feeling I made this same mistake before and posted to this
forum as well ...

RBS



On Wed, Oct 17, 2012 at 4:13 AM, Igor Tandetnik  wrote:
> Bart Smissaert  wrote:
>> To do with the same, what is wrong with this update SQL?
>>
>> update final2
>> set group_count =
>> (select count(*)
>> from
>> final2
>> group by
>> group_marker)
>>
>> It makes group_count always 1
>
> The subquery does not depend on the values in the row being updated. It 
> always produces the same resultset. Now, the value of the expression of the 
> form "(select ...)" is the value of the first column of the first row of the 
> resultset. In your case, it just happens to be 1.
>
> You are probably looking for something like this:
>
> update final2 set group_count =
> (select count(*) from final2 t2 where t2.group_marker = final2.group_marker);
>
> Here, the condition of the subquery mentions a value from the outer table, so 
> it's evaluated anew for every row being updated. See also:
>
> http://en.wikipedia.org/wiki/Correlated_subquery
>
> --
> 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