Re: [sqlite] nested foreign keys

[David Yip]

What do your inserts look like?  This SQL will function as expected:

CREATE TABLE grandparent (id INTEGER PRIMARY KEY);
CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));
CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));

INSERT INTO grandparent VALUES (1);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (1);

You can mix up the insertion order if you defer checking (
https://www.sqlite.org/foreignkeys.html#fk_deferred); if not, you'll have
to insert parents before children.

- David

On Wed, Oct 24, 2018 at 11:44 PM Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> The statements work. Insertion fails.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
> behalf of David Yip [dw...@peach-bun.com]
> Sent: Thursday, October 25, 2018 12:37 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] nested foreign keys
>
> These statements worked for me:
>
>
> CREATE TABLE grandparent (id INTEGER PRIMARY KEY);
>
> CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));
>
> CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));
>
>
> The foreign key constraints work as you'd expect also.
>
>
> What are you doing and what error are you seeing?
>
>
> - David
>
> On Wed, Oct 24, 2018 at 11:30 PM Roman Fleysher <
> roman.fleys...@einstein.yu.edu> wrote:
>
> > Dear SQLiters,
> >
> > I am trying to set up what I would call "nested foreign keys":
> >
> > create grandParent (id PRIMARY KEY )
> > create parent (id PRIMARY KEY REFERENCES grandParent(id))
> > create child (id PRIMARY KEY REFERENCES parent(id))
> >
> > SQLite complains. Does it mean that grand children are not allowed?
> >
> > Thank you,
> >
> > Roman
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] nested foreign keys

[David Yip]

These statements worked for me:


CREATE TABLE grandparent (id INTEGER PRIMARY KEY);

CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));

CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));


The foreign key constraints work as you'd expect also.


What are you doing and what error are you seeing?


- David

On Wed, Oct 24, 2018 at 11:30 PM Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
> I am trying to set up what I would call "nested foreign keys":
>
> create grandParent (id PRIMARY KEY )
> create parent (id PRIMARY KEY REFERENCES grandParent(id))
> create child (id PRIMARY KEY REFERENCES parent(id))
>
> SQLite complains. Does it mean that grand children are not allowed?
>
> Thank you,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union + window functions = sqlite crash (version 3.25.2)

[David Yip]

I dug a little more into this with a debug build; was able to get the
same crash trace with the slightly smaller query

CREATE TABLE t(a);
SELECT 1, 1
UNION ALL
SELECT a, RANK() OVER (ORDER BY a) FROM t;

which fails the pTab!=0 assertion in sqlite3ColumnsFromExprList.

It seems like you need to have a window function and a column from t in
the second SELECT to trigger the error; e.g. having 1, RANK()... there
won't do it.

- David

On Monday, October 22, 2018 3:13:12 PM CDT Peter Ďurica wrote:
> Table with sample data:
> *create table t(a int, b int);*
> *insert into t values(1,11);*
> *insert into t values(2,12);*
> 
> now query using any window function (row_number, rank, ) after UNION or
> UNION ALL will cause sqlite.exe crash (no regular error)
> 
> for example:
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, rank() over(order by b desc) from t;*
> WILL CRASH
> 
> but single statement is fine
> 
> *select a, rank() over(order by b desc) from t;  *
> 
> and also window function just before union is fine
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, b from t;*
> 
> 
> when I used e_sqlite3.dll (https://github.com/ericsink/SQLitePCL.raw) from
> my c# app, I got error below
> Exception thrown at 0x7FFF563BF797 (e_sqlite3.dll) in WebLES.exe:
> 0xC005: Access violation reading location 0x0008.
> 
> I believe it's a bug in core sqlite, which should be fixed
> 
> Please let me know how it goes
> 
> Thanks in advacne,
> Peter Ďurica
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




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

Re: [sqlite] Bad implementation in mem5.c memsys5Roundup method in the latest version 3.25.0

[David Yip]

Do you have a benchmark demonstrating that time taken in memsys5Roundup is
a bottleneck on some compiler and platform?

gcc and clang both turn for(iFullSz=mem5.szAtom; iFullSz wrote:

> We can increase the efficiency with the following code replace:
>
>
>
>
> static int memsys5Roundup(int n){//changed by wjf
> if( n > 0x4000 ) return 0;
> int i = 0;
> while (_BlockSize[i++] return _BlockSize[i-1];
> }
> //static int memsys5Roundup(int n){
> //int iFullSz;
> //if( n > 0x4000 ) return 0;
> //for(iFullSz=mem5.szAtom; iFullSz //return iFullSz;
> //}
>
>
>
>
>
>
>
>
> /*
> ** Initialize the memory allocator.
> **
> ** This routine is not threadsafe.  The caller must be holding a mutex
> ** to prevent multiple threads from entering at the same time.
> */
> static int _BlockSize[32];//add by wjf
> static int memsys5Init(void *NotUsed){
>   int ii;/* Loop counter */
>   int nByte; /* Number of bytes of memory available to this
> allocator */
>   u8 *zByte; /* Memory usable by this allocator */
>   int nMinLog;   /* Log base 2 of minimum allocation size in bytes */
>   int iOffset;   /* An offset into mem5.aCtrl[] */
>
>
>   UNUSED_PARAMETER(NotUsed);
>
>
>   /* For the purposes of this routine, disable the mutex */
>   mem5.mutex = 0;
>
>
>   /* The size of a Mem5Link object must be a power of two.  Verify that
>   ** this is case.
>   */
>   assert( (sizeof(Mem5Link)&(sizeof(Mem5Link)-1))==0 );
>
>
>   nByte = sqlite3GlobalConfig.nHeap;
>   zByte = (u8*)sqlite3GlobalConfig.pHeap;
>   assert( zByte!=0 );  /* sqlite3_config() does not allow otherwise */
>
>
>   /* boundaries on sqlite3GlobalConfig.mnReq are enforced in
> sqlite3_config() */
>   nMinLog = memsys5Log(sqlite3GlobalConfig.mnReq);
>   mem5.szAtom = (1<   while( (int)sizeof(Mem5Link)>mem5.szAtom ){
> mem5.szAtom = mem5.szAtom << 1;
>   }
> //add by wjf
> long long size = mem5.szAtom;
> int i = 0;
> while (size < 0x4000) {
> _BlockSize[i++] = (int)size;
> size *= 2;
> }
>
>
>   mem5.nBlock = (nByte / (mem5.szAtom+sizeof(u8)));
>   mem5.zPool = zByte;
>   mem5.aCtrl = (u8 *)[mem5.nBlock*mem5.szAtom];
>
>
>   for(ii=0; ii<=LOGMAX; ii++){
> mem5.aiFreelist[ii] = -1;
>   }
>
>
>   iOffset = 0;
>   for(ii=LOGMAX; ii>=0; ii--){
> int nAlloc = (1< if( (iOffset+nAlloc)<=mem5.nBlock ){
>   mem5.aCtrl[iOffset] = ii | CTRL_FREE;
>   memsys5Link(iOffset, ii);
>   iOffset += nAlloc;
> }
> assert((iOffset+nAlloc)>mem5.nBlock);
>   }
>
>
>   /* If a mutex is required for normal operation, allocate one */
>   if( sqlite3GlobalConfig.bMemstat==0 ){
> mem5.mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MEM);
>   }
>
>
>   return SQLITE_OK;
> }
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

[David Yip]

Hi Eric,

If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you
can query the dbstat virtual table for the number of pages used per table
and index, e.g.

SELECT name, COUNT(path) AS pages FROM dbstat GROUP BY name ORDER BY
pages DESC;

This runs in ~600 ms on one of my applications, which isn't that fast, but
I've had success placing the read on a background thread.  (Out of
curiosity, how quick do you need the report to be?)

The dbstat virtual table manual (https://www.sqlite.org/dbstat.html) has
some more examples and a longer description of the contents of the dbstat
table.

- David

On Mon, Jul 30, 2018 at 1:46 AM, Eric Grange  wrote:

> Hi,
>
> Is there a quick way (as in speed) to obtain the number of database blocks
> (or kilobytes) allocated for each table and each index ?
>
> I have been using various manual approaches so far (from using length() to
> vacuum and drops), but none of them are really fast
> or can practical to automate.
>
> Eric
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Out-of-bounds read in FTS5 on 3.24.0 and 201807110327 snapshot

[David Yip]

Thank you for the quick fix!

On Wed, Jul 25, 2018 at 10:27 AM, Dan Kennedy  wrote:

> On 07/25/2018 10:50 AM, David Yip wrote:
>
>> Hi all,
>>
>> On x86-64 Linux with SQLite 3.24.0 and the 201807110327 SQLite snapshot,
>> the
>> following program causes FTS5 to do an out-of-bounds access:
>> https://gitlab.peach-bun.com/snippets/157
>>
>> Sample ASan and Valgrind outputs are here:
>> https://gitlab.peach-bun.com/snippets/158
>>
>>
> Please let me know if I can provide any additional information that would
>> help
>> with a fix.
>>
>
> Thanks for taking the time to report this and construct the demo code. Now
> fixed here:
>
>   https://www.sqlite.org/src/info/0e3de8abbb0c7ae6
>
> Dan.
>
>
>
>
>
>
>> Thanks,
>>
>> - David
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Out-of-bounds read in FTS5 on 3.24.0 and 201807110327 snapshot

[David Yip]

Hi all,

On x86-64 Linux with SQLite 3.24.0 and the 201807110327 SQLite snapshot,
the
following program causes FTS5 to do an out-of-bounds access:
https://gitlab.peach-bun.com/snippets/157

Sample ASan and Valgrind outputs are here:
https://gitlab.peach-bun.com/snippets/158

It looks like if you feed in the byte sequence E3 81 BE E3 82 8A E3 82 84
(the
UTF-8 encoding of まりや), then the loop

while( (p[n] & 0xc0)==0x80 ) n++;

in sqlite3Fts5IndexCharlenToBytelen will attempt to read past the end of
the
string when building 3-character prefixes.

I don't know what (if any) security/stability implications this
out-of-bounds read has, but it is
inconvenient to hit it when statically linking SQLite into an application
has
has ASan enabled (because it'll cause a program abort).

Please let me know if I can provide any additional information that would
help
with a fix.

Thanks,

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