Re: [sqlite] UNION

[Hick Gunter]

You are literally mixing apples and oranges without creating a superclass 
"fruit" that contains a field to tell them apart and a set of attributes 
(=fields) merged from the attributes of the component tables.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von do...@mail.com
Gesendet: Mittwoch, 01. März 2017 02:40
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] UNION

# SELECT * FROM processors UNION SELECT * FROM storage;
Error: SELECTs to the left and right do not have the same number of result 
columns.

All tables that I created in my database have differing column names, values, 
and amounts of columns with the noted exception of the one column which is 
common (board). I've no idea what to do now.

Why is this an error?

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] foreign key cardinality

[Hick Gunter]

I was not asking for SQL statements but that you think about what you are 
trying to achieve.

Without a UNIQUE constraint there can be more than one row in the parent table 
that is the "parent row" of at least one row of the child table. To delete rows 
from the parent while still satisfying the EXISTS relation, you can delete all 
rows except the "last one" for each distinct (=UNIQUE) foreign key combination 
in the child table. This is not a set operation, there is no "last row" in a 
set. Likewise ON DELETE CASCADE needs tob e triggered when the "last row" for a 
distinct (=UNIQUE) foreign key combination is deleted from the parent table. 
Again, there is no "last row" in a set.

Not requiring UNIQUE means that FK enforcement relies on sets exhibiting an 
order, when they are clearly not allowed to have one by the relational model. 
Thus UNIQUE is required. (Proof by "reductio ad absurdum")

SQL for non-unique foreign key ON DELETE CASCADE:

-- assumes FK constraints are DEFERRED
BEGIN;
-- get the set of foreign key expressions touched by the delete
CREATE TEMP TABLE TBD_C AS SELECT DISTINCT  FROM P WHERE ;
-- remove the rows from parent table
DELETE FROM P WHERE ;
-- remove foreign key expressions still fulfilled by parent table
DELETE FROM TBD_C WHERE EXISTS (SELECT 1 FROM P WHERE );
-- remove "unparented" rows from child table
DELETE FROM C WHERE  IN (SELECT * FROM TBD_C);
--
COMMIT;


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von James K. Lowden
Gesendet: Dienstag, 28. Februar 2017 22:41
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 17:13:30 +
Hick Gunter  wrote:

> Let's assume you have a parent table P (color, shape, style, ...) that
> is unique on the three named fields. Let's assume you have a child
> table C (id, ...,color, shape, ...) that references P
> (color,shape)

As far as SQL goes, Kees provided the relevant text, so we know SQLite conforms 
to the standard in this regard.  Thank you, Kees.

To answer your questions, supposing UNIQUE were not required for a FK referent:

> Which, if any, of the P rows can you safely delete without losing
> referential integrity?

delete from P where not exists (
select 1 from C where color = P.color and shape = P.shape );

> Assuming ON DELETE CASCADE, when do you delete the rows from C?

delete from C where exists (
select 1 from P where color = C.color and shape = C.shape
and /* ... P criteria ... */
);

From a theoretical standpoint, for relations

P{A,B}
and
C{C,B} with C{B} referencing P{B}

without loss of information we may add

B{B}

and constraints

P{B} references B{B} and C{B} references B{B}

But, having added B, we have not added any information.  We have merely added 
another relation that is the projection of P{B}.

It is *usually* true that B will have other attributes, in which case of course 
a B table would be needed to hold them.  Maybe that, or practical 
considerations, or both, motivated the SQL rule.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] foreign key cardinality

[Keith Medcalf]

> > Let's assume you have a parent table P (color, shape, style, ...)
> > that is unique on the three named fields. Let's assume you have a
> > child table C (id, ...,color, shape, ...) that references P
> > (color,shape)
> 
> As far as SQL goes, Kees provided the relevant text, so we know SQLite
> conforms to the standard in this regard.  Thank you, Kees.
> 
> To answer your questions, supposing UNIQUE were not required for a
> FK referent:
> 
> > Which, if any, of the P rows can you safely delete without losing
> > referential integrity?
> 
> delete from P where not exists (
>   select 1 from C where color = P.color and shape = P.shape );
> 
> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
> 
> delete from C where exists (
>   select 1 from P where color = C.color and shape = C.shape
>   and /* ... P criteria ... */
> );
> 
> From a theoretical standpoint, for relations
> 
>   P{A,B}
> and
>   C{C,B} with C{B} referencing P{B}
> 
> without loss of information we may add
> 
>   B{B}
> 
> and constraints
> 
>   P{B} references B{B} and C{B} references B{B}
> 
> But, having added B, we have not added any information.  We have
> merely added another relation that is the projection of P{B}.
> 
> It is *usually* true that B will have other attributes, in which case
> of course a B table would be needed to hold them.  Maybe that, or
> practical considerations, or both, motivated the SQL rule.

It has always been the case in SQL (and relational databases in general) that 
Parents must be unique.  It was even the case in Hierarchical databases that 
Parents of a Set must be unique -- that is, identify a 1:N relationship.  N:M 
relationship modelling requires a connecting N:M table where the relationships 
between table A -> C <- B are one parent in A identifies N records in C, each 
of which identifies one record in B (that is, A -> C is 1:N and B -> C is 1:M 
with the resulting A <-> B relationship being N:M).  Only Network Model (I 
forget whether it has to be Network Extended -- it has been a long time since I 
used one) Hierarchical databases model N:M sets directly.

It has always been a requirement that FK relationships are 1:N mappings, 
otherwise update anomalies will occur.  If you have a relational database that 
does not "naturally" have all FK relationships as 1:N, then you have not 
normalized the data properly (or sufficiently).




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

Re: [sqlite] UNION

[J Decker]

On Tue, Feb 28, 2017 at 5:40 PM,  wrote:

> # SELECT * FROM processors UNION SELECT * FROM storage;
> Error: SELECTs to the left and right do not have the same number of
> result columns.
>
> All tables that I created in my database have differing column names,
> values, and amounts of columns with the noted exception of the one
> column which is common (board). I've no idea what to do now.
>
> Why is this an error?
>

1   6770
2  3770

1  samsung 960   250G
2  seagate5T
3

what should it use for fields, if the query first encounters 2 columns, and
starts allocating rows for it, then runs into data with 3, then it shouln't
just drop some arbitrary data, so it throws an error.
If you want them really to merge... on the first select *,"nothing" from X
untion select * from Y
(I think sqlite allows * as first or last argument)
or sepcifically name some number from X and the same number from Y and
it'll put them together
Whether that actually has any meaning is up to the application





>
> Thank you,
> 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

Re: [sqlite] foreign key cardinality

[J Decker]

On Tue, Feb 28, 2017 at 6:01 PM, J Decker  wrote:

>
>
> On Tue, Feb 28, 2017 at 1:41 PM, James K. Lowden  > wrote:
>
>> On Tue, 28 Feb 2017 17:13:30 +
>> Hick Gunter  wrote:
>>
>> > Let's assume you have a parent table P (color, shape, style, ...)
>> > that is unique on the three named fields. Let's assume you have a
>> > child table C (id, ...,color, shape, ...) that references P
>> > (color,shape)
>>
>> As far as SQL goes, Kees provided the relevant text, so we know SQLite
>> conforms to the standard in this regard.  Thank you, Kees.
>>
>> To answer your questions, supposing UNIQUE were not required for a
>> FK referent:
>>
>> > Which, if any, of the P rows can you safely delete without losing
>> > referential integrity?
>>
>
> Then how would you properly find the children?  Or find any information
> about their parent, the child would have to in turn be deleted.
>
> foreign keys are as good as pointers.  If the parent structure pointing at
> a child is no longer, how is the child valid for anything other than
> garbage collection?
>
> okay but I guess that's what on delete SETNULL is for... so you can keep
the child as a special 'NULL' record for later searching just by file


> If the unique; (memory address) key contains multiple parts, then you have
> to reference all aprts.  If there was a part that was itself unique then
> you wouldn't really need the second part; otherwise you do in order to
> differentiate (1,1) from (1,2)
>
> perhaps what you intend is to use a graph database, where a single 'child'
> might be referred to from mulitple parents (links) ; but then your model
> should be something more self recursive like...
> (pseudoSQL)
> create table inode( iNode char PRIMARY KEY, moreColumns char,  )
> create table inodeLinks( someInode char, someOtherInode char, FK(someinode
> references inode.iNode), FK(someOtheriNode references inode.iNode) )
>
> create table inodeFileData( iNode char , fileDataIfAny char,  )
> create table inodeNameData( iNode char, theNameOfThisPoint char,  )
> create table inodeFileData( iNode char, attributes char,  )
>
> Such that any directory might also contain some data, but any file can
> contain other files since each thing is named separatly from it's key.
>
>
>
>
> Instead of having the file records themselves having their directory, why
> not just make a separate table
> ( directory char , hasFiles char );
>
> CTE queries can make this even tolerable for linear queries
> 
> Sorry I wandered into an alternative solution instead of the other
> representation...
> in .NET you have DataSet, whichi contains DataTables, and between DTs are
> Relation(s).  When you fill a dataset with all properly keyed things, the
> row in one table (files).getChildRows( "filesInDirectory" ) (returns more
> rows)
>
> And it's really a direct reference, it doesn't have to go additionally
> search through the index in the child table to get to the rows by looking
> up a key, it just points to it.  Much like working with a graph, your
> master records just point at the children, and you never have to search for
> anything...
>
>
>
>> delete from P where not exists (
>> select 1 from C where color = P.color and shape = P.shape );
>>
>> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>>
>> delete from C where exists (
>> select 1 from P where color = C.color and shape = C.shape
>> and /* ... P criteria ... */
>> );
>>
>> From a theoretical standpoint, for relations
>>
>> P{A,B}
>> and
>> C{C,B} with C{B} referencing P{B}
>>
>> without loss of information we may add
>>
>> B{B}
>>
>> and constraints
>>
>> P{B} references B{B} and C{B} references B{B}
>>
>> But, having added B, we have not added any information.  We have
>> merely added another relation that is the projection of P{B}.
>>
>> It is *usually* true that B will have other attributes, in which case
>> of course a B table would be needed to hold them.  Maybe that, or
>> practical considerations, or both, motivated the SQL rule.
>>
>> --jkl
>> ___
>> 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] foreign key cardinality

[J Decker]

On Tue, Feb 28, 2017 at 1:41 PM, James K. Lowden 
wrote:

> On Tue, 28 Feb 2017 17:13:30 +
> Hick Gunter  wrote:
>
> > Let's assume you have a parent table P (color, shape, style, ...)
> > that is unique on the three named fields. Let's assume you have a
> > child table C (id, ...,color, shape, ...) that references P
> > (color,shape)
>
> As far as SQL goes, Kees provided the relevant text, so we know SQLite
> conforms to the standard in this regard.  Thank you, Kees.
>
> To answer your questions, supposing UNIQUE were not required for a
> FK referent:
>
> > Which, if any, of the P rows can you safely delete without losing
> > referential integrity?
>

Then how would you properly find the children?  Or find any information
about their parent, the child would have to in turn be deleted.

foreign keys are as good as pointers.  If the parent structure pointing at
a child is no longer, how is the child valid for anything other than
garbage collection?

If the unique; (memory address) key contains multiple parts, then you have
to reference all aprts.  If there was a part that was itself unique then
you wouldn't really need the second part; otherwise you do in order to
differentiate (1,1) from (1,2)

perhaps what you intend is to use a graph database, where a single 'child'
might be referred to from mulitple parents (links) ; but then your model
should be something more self recursive like...
(pseudoSQL)
create table inode( iNode char PRIMARY KEY, moreColumns char,  )
create table inodeLinks( someInode char, someOtherInode char, FK(someinode
references inode.iNode), FK(someOtheriNode references inode.iNode) )

create table inodeFileData( iNode char , fileDataIfAny char,  )
create table inodeNameData( iNode char, theNameOfThisPoint char,  )
create table inodeFileData( iNode char, attributes char,  )

Such that any directory might also contain some data, but any file can
contain other files since each thing is named separatly from it's key.




Instead of having the file records themselves having their directory, why
not just make a separate table
( directory char , hasFiles char );

CTE queries can make this even tolerable for linear queries

Sorry I wandered into an alternative solution instead of the other
representation...
in .NET you have DataSet, whichi contains DataTables, and between DTs are
Relation(s).  When you fill a dataset with all properly keyed things, the
row in one table (files).getChildRows( "filesInDirectory" ) (returns more
rows)

And it's really a direct reference, it doesn't have to go additionally
search through the index in the child table to get to the rows by looking
up a key, it just points to it.  Much like working with a graph, your
master records just point at the children, and you never have to search for
anything...



> delete from P where not exists (
> select 1 from C where color = P.color and shape = P.shape );
>
> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>
> delete from C where exists (
> select 1 from P where color = C.color and shape = C.shape
> and /* ... P criteria ... */
> );
>
> From a theoretical standpoint, for relations
>
> P{A,B}
> and
> C{C,B} with C{B} referencing P{B}
>
> without loss of information we may add
>
> B{B}
>
> and constraints
>
> P{B} references B{B} and C{B} references B{B}
>
> But, having added B, we have not added any information.  We have
> merely added another relation that is the projection of P{B}.
>
> It is *usually* true that B will have other attributes, in which case
> of course a B table would be needed to hold them.  Maybe that, or
> practical considerations, or both, motivated the SQL rule.
>
> --jkl
> ___
> 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

[Stephen Chrzanowski]

The error says it.  You need to have the same number of columns/fields.
For example, this would throw an error if processors had 3 fields and
storage had 4.  I don't think the names of the fields are important, as
it'll use whatever is defined in the first SELECT.

On Tue, Feb 28, 2017 at 8:40 PM,  wrote:

> # SELECT * FROM processors UNION SELECT * FROM storage;
> Error: SELECTs to the left and right do not have the same number of
> result columns.
>
> All tables that I created in my database have differing column names,
> values, and amounts of columns with the noted exception of the one
> column which is common (board). I've no idea what to do now.
>
> Why is this an error?
>
> Thank you,
> 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] UNION

[doark]

# SELECT * FROM processors UNION SELECT * FROM storage;
Error: SELECTs to the left and right do not have the same number of
result columns.

All tables that I created in my database have differing column names,
values, and amounts of columns with the noted exception of the one
column which is common (board). I've no idea what to do now.

Why is this an error?

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

Re: [sqlite] SQLite 3 locking

[Domingo Alvarez Duarte]

Hello !

On this snippet you can play with the parameters and find a value that 
can give a good result for a workload:


https://gist.github.com/mingodad/79225c88f8dce0f174f5

I did it to test sqlite3 wall mode but it also work without it and with 
disk/memory databases.


Cheers !


On 28/02/17 21:56, Matthew Ceroni wrote:

Appreciate it. Have a much better picture now.

Thanks

On Feb 28, 2017 4:48 PM, "Simon Slavin"  wrote:


On 1 Mar 2017, at 12:40am, Matthew Ceroni  wrote:


Once PENDING is obtained, what time out value controls how long to wait

to

get EXCLUSIVE?

There is only the one timeout value for each connection: the one you set.
Each attempt to escalate the lock level can take up to the timeout value
before it returns an error code.  I think.  You’re at the limits of my
knowledge here.

Simon.
___
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] SQLite 3 locking

[Matthew Ceroni]

Appreciate it. Have a much better picture now.

Thanks

On Feb 28, 2017 4:48 PM, "Simon Slavin"  wrote:

>
> On 1 Mar 2017, at 12:40am, Matthew Ceroni  wrote:
>
> > Once PENDING is obtained, what time out value controls how long to wait
> to
> > get EXCLUSIVE?
>
> There is only the one timeout value for each connection: the one you set.
> Each attempt to escalate the lock level can take up to the timeout value
> before it returns an error code.  I think.  You’re at the limits of my
> knowledge here.
>
> Simon.
> ___
> 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] SQLite 3 locking

[Simon Slavin]

On 1 Mar 2017, at 12:40am, Matthew Ceroni  wrote:

> Once PENDING is obtained, what time out value controls how long to wait to
> get EXCLUSIVE?

There is only the one timeout value for each connection: the one you set.  Each 
attempt to escalate the lock level can take up to the timeout value before it 
returns an error code.  I think.  You’re at the limits of my knowledge here.

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

Re: [sqlite] SQLite 3 locking

[Matthew Ceroni]

Once PENDING is obtained, what time out value controls how long to wait to
get EXCLUSIVE?

On Feb 28, 2017 4:33 PM, "Simon Slavin"  wrote:

>
> On 1 Mar 2017, at 12:29am, Matthew Ceroni  wrote:
>
> > Appreciate the reply. I just found it odd that busy_timeout is set to 0
> (so
> > fail immediately) considering the intermediary PENDING stage. That stage
> is
> > essentially useless with the default busy timeout of 0 no?
>
> Only if its first attempt at grabbing the lock fails.  Which, even when
> you have many threads/processes accessing the database is rare.
>
> But yes, a value of 0 for that setting does look a little strange.  But I
> can’t think of a default which would work well for all setups, and having
> the default be zero forces programmers to learn about the setting and think
> about what the best value would be for their setup.
>
> Simon.
> ___
> 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] SQLite 3 locking

[Simon Slavin]

On 1 Mar 2017, at 12:29am, Matthew Ceroni  wrote:

> Appreciate the reply. I just found it odd that busy_timeout is set to 0 (so
> fail immediately) considering the intermediary PENDING stage. That stage is
> essentially useless with the default busy timeout of 0 no?

Only if its first attempt at grabbing the lock fails.  Which, even when you 
have many threads/processes accessing the database is rare.

But yes, a value of 0 for that setting does look a little strange.  But I can’t 
think of a default which would work well for all setups, and having the default 
be zero forces programmers to learn about the setting and think about what the 
best value would be for their setup.

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

Re: [sqlite] SQLite 3 locking

[Matthew Ceroni]

Appreciate the reply. I just found it odd that busy_timeout is set to 0 (so
fail immediately) considering the intermediary PENDING stage. That stage is
essentially useless with the default busy timeout of 0 no?

I will read up on WAL.

On Tue, Feb 28, 2017 at 4:22 PM, Simon Slavin  wrote:

>
> On 28 Feb 2017, at 6:39pm, Matthew Ceroni  wrote:
>
> > After a bit of reading and troubleshooting I stumbled across the
> > busy_timeout option (which defaults to 0). Prior to execute the create
> > table statement I set PRAGMA busy_timeout=1000 (1 s) and the writes
> always
> > succeeded.
>
> You need to set the busy timeout at every connection to the database,
> including those which only read and never write.  It’s common to see it
> done immediately after the sqlite3_open() command.  It is not stored in the
> database and new connections to the database do not know what it was set to
> before.
>
> If that doesn’t fix things, as a diagnostic measure, try setting
> busy_timeout to 30 seconds instead of just 1 second.  Learning whether this
> does or does not fix the problem will tell you a lot about what’s going on.
>
> Are you using WAL mode ?  If not, you should try it.  Locking works
> differently in WAL files, as described in
>
> 
>
> > So since busy_timeout defaults to 0, all write attempts if a lock can't
> be
> > obtained will return SQLITE_BUSY immediately. Where does the PENDING lock
> > come into play here? I thought the PENDING was meant to be an
> intermediary
> > step before EXCLUSIVE. Does the busy_timeout impact the writers attempt
> to
> > obtain PENDING? Or does the busy_timeout trigger after X amount of ms
> > between obtaining PENDING and trying to move to EXCLUSIVE?
>
> There are two kinds of locks: shared and exclusive.  A pending lock means
> you have shared, not exclusive, but that you want exclusive.
>
> timeout applies when obtaining both shared and exclusive locks.  Actually,
> hold on, I’m not sure about this.  Better to try what I wrote already then
> get an expert to explain things.
>
> Simon.
> ___
> 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] SQLite 3 locking

[Simon Slavin]

On 28 Feb 2017, at 6:39pm, Matthew Ceroni  wrote:

> After a bit of reading and troubleshooting I stumbled across the
> busy_timeout option (which defaults to 0). Prior to execute the create
> table statement I set PRAGMA busy_timeout=1000 (1 s) and the writes always
> succeeded.

You need to set the busy timeout at every connection to the database, including 
those which only read and never write.  It’s common to see it done immediately 
after the sqlite3_open() command.  It is not stored in the database and new 
connections to the database do not know what it was set to before.

If that doesn’t fix things, as a diagnostic measure, try setting busy_timeout 
to 30 seconds instead of just 1 second.  Learning whether this does or does not 
fix the problem will tell you a lot about what’s going on.

Are you using WAL mode ?  If not, you should try it.  Locking works differently 
in WAL files, as described in



> So since busy_timeout defaults to 0, all write attempts if a lock can't be
> obtained will return SQLITE_BUSY immediately. Where does the PENDING lock
> come into play here? I thought the PENDING was meant to be an intermediary
> step before EXCLUSIVE. Does the busy_timeout impact the writers attempt to
> obtain PENDING? Or does the busy_timeout trigger after X amount of ms
> between obtaining PENDING and trying to move to EXCLUSIVE?

There are two kinds of locks: shared and exclusive.  A pending lock means you 
have shared, not exclusive, but that you want exclusive.

timeout applies when obtaining both shared and exclusive locks.  Actually, hold 
on, I’m not sure about this.  Better to try what I wrote already then get an 
expert to explain things.

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

[sqlite] SQLite 3 locking

[Matthew Ceroni]

https://www.sqlite.org/lockingv3.html

I have read through the attached link but what is outlined here doesn't
seem to match with what I am seeing in a production SQLite setup we have.
Specifically the writer starvation part of the document.

We have a DB that is heavily read. Writes happen very infrequently. We were
finding that writes were failing with "Database locked" (SQLITE_BUSY I
believe) from time to time.

To reproduce I execute continuous reads in a tight loop. Then I tried to
execute a write operation (create table) and received database locked.

After a bit of reading and troubleshooting I stumbled across the
busy_timeout option (which defaults to 0). Prior to execute the create
table statement I set PRAGMA busy_timeout=1000 (1 s) and the writes always
succeeded.

So since busy_timeout defaults to 0, all write attempts if a lock can't be
obtained will return SQLITE_BUSY immediately. Where does the PENDING lock
come into play here? I thought the PENDING was meant to be an intermediary
step before EXCLUSIVE. Does the busy_timeout impact the writers attempt to
obtain PENDING? Or does the busy_timeout trigger after X amount of ms
between obtaining PENDING and trying to move to EXCLUSIVE?

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

Re: [sqlite] foreign key cardinality

[Kees Nuyt]

On Tue, 28 Feb 2017 11:42:23 -0500, "James K. Lowden"
 wrote:

> I have always thought af a foreign key as an existence test.  Looking
> around, I see that other implementations also often require the
> referent to be unique.  I'm not convinced that's justified
> theoretically, but at the moment I can't check against my usual
> resources.  

Here you go:

Information Technology - Database Language SQL
(Proposed revised text of DIS 9075)
(Second Informal Review Draft) ISO/IEC 9075:1992, 
Database Language SQL- July 30, 1992
which is pretty close to the final text.

X3H2-92-154/DBL CBR-002  (page 274 or thereabout)

11.8  

Function

Specify a referential constraint.

Format

 ::=
 FOREIGN KEY   
   

 ::=
 REFERENCES 
   [ MATCH  ]
   [  ]

 ::=
   FULL
 | PARTIAL

 ::=
 

 ::=
  []

 ::= 

 ::=
[  ]
 |  [  ]

 ::= ON UPDATE 

 ::= ON DELETE 

 ::=
   CASCADE
 | SET NULL
 | SET DEFAULT
 | NO ACTION


Syntax Rules

1) Let referencing table be the table identified by the containing
or . Let referenced
   table be the table identified by the  in the . Let referencing columns be the column
   or columns identified by the  in the
and let referencing column be one such
   column.

2) Case:

   a) If the  specifies a , then the set of column names of that  shall be equal to the set of column names
 in the unique columns of a unique constraint of the refer-
 enced table. Let referenced columns be the column or columns
 identified by that  and let refer-
 enced column be one such column. Each referenced column shall
 identify a column of the referenced table and the same column
 shall not be identified more than once.

   b) If the  does not specify a
 , then the table descriptor of the
 referenced table shall include a unique constraint that spec-
 ifies PRIMARY KEY. Let referenced columns be the column or
 columns identified by the unique columns in that unique con-
 straint and let referenced column be one such column. The
  shall be considered to implic-
 itly specify a  that is identical to
 that .

3) The table constraint descriptor describing the  whose  identifies the
   referenced columns shall indicate that the unique constraint is
   not deferrable.


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] foreign key cardinality

[James K. Lowden]

On Tue, 28 Feb 2017 08:48:02 +
Hick Gunter  wrote:

> "If they are not the primary key, then the parent key columns must
> be collectively subject to a UNIQUE constraint or have a UNIQUE
> index."

Thank you, Hick.  

I have always thought af a foreign key as an existence test.  Looking
around, I see that other implementations also often require the
referent to be unique.  I'm not convinced that's justified
theoretically, but at the moment I can't check against my usual
resources.  

I now see how to solve the conundrum I faced that motivated my
complaint.  It requires more columns and UNIQUE constraints than I
think are strictly necessary, but it can be made to work.  

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

Re: [sqlite] Crash on Android

[Hick Gunter]

Looks like attempting to dereference a NULL pointer. Without a backtrace of the 
call stack this is not of much use. Most commonly this is caused by an 
uninitialized variable on the stack, accessing a structure that has been 
freed/garbage collected or memory being clobbered by a rogue write operation 
(too long, wrong place e.g. dangling pointer).

Attaching a debugger and/or adding log messages may change the stack layout in 
a way that makes the problem appear to have gone away (it is just affecting 
"something else", which may or may not mind).

In the context of sqlite3_column_text16(), make sure that the last call to 
sqlite3_step() returned SQLITE_ROW and that sqlite3_step(), sqlite3_finalize) 
or sqlite3_reset() are not called on the statement (even from another thread) 
and that you are not using an object returned from sqlite3_column_value() or 
using pointers returned from prior sqlite3_column functions and calling an 
sqlite3_column function that requires a conversion to take place.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jeff Archer
Gesendet: Montag, 27. Februar 2017 17:28
An: SQLite mailing list 
Betreff: [sqlite] Crash on Android

Hi All,
Any thoughts on this will be greatly appreciated.

I am having an issue only on a specific tablet when it is running Android 
4.2.2.  When Android is upgraded to 4.4.2 problems appears to be gone.  I still 
want to understand root cause to know if problem is really gone.

My environment is a little unusual.  I am running SQLite amalgamation wrapped 
by JDBC driver.  I was running 3.13.0 when issue was initially found but have 
updated to 3.17.0 and no change.

Problem always occurs while doing same operation but unable to predict when it 
will occur.  Problem occurs while reading in a specific table and a call to 
sqlite3_column_text16() results in the following error being logged:
A/libc(5225): Fatal signal 11 (SIGSEGV) at 0x (code=128), thread
5225 (saltillo.chatpc)
and of course kills the process.

Also, I find that attaching a debugger or writing too many log messages seems 
to make the problems disappear also.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] foreign key cardinality

[Hick Gunter]

Your assumption does not correspond with the documentation, see 
http://sqlite.org/foreignkeys.html :

"The parent key is the column or set of columns in the parent table that the 
foreign key constraint refers to. This is normally, but not always, the primary 
key of the parent table. The parent key must be a named column or columns in 
the parent table, not the rowid."

" If they are not the primary key, then the parent key columns must be 
collectively subject to a UNIQUE constraint or have a UNIQUE index."

"Cardinality" refers to the number of fields in the constraint. As in "(x,y,z) 
of child table must match (a,b,c) of parent table" is valid, while "(x,y) of 
child must match (a) of parent table" is invalid, just like "(x,y) of chid 
table must match (a,b,c) of parent table".


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von James K. Lowden
Gesendet: Montag, 27. Februar 2017 21:42
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] foreign key cardinality

I would like to illustrate a problem with SQLite's foreign key enforcement 
policy.  SQLite requires that foreign keys refer to primary keys.  That rule 
has no relational foundation, and prevents the use of foreign keys that are 
perfectly valid.

I have these tables (non-key columns omitted for brevity):

Directory ( name primary key )
File ( name, dname references Directory(name), primary key (name, 
dname) ) Program ( name primary key )

Note that a filename is not unique except within a directory.

Now I have authorization tables that constrain what directories and files a 
program can access, and in what order.

ProgDir (  pname references Program(name),
dname references Directory(name),
ord,
primary key (pname, ord) )

ProgFile ( pname, pord, fname, ord,
   primary key (pname, pord, fname),
   foreign key (pname, pord) references ProgDir (pname, 
ord), foreign key (fname) references File(name) ) -- ouch

A program can access a directory and that directory's files.  The file access 
order depends on which directory we're referring to, and requires that the 
program be permitted to use that directory.

It's not necessary to carry dname in ProgFile; it can be derived from 
ProgDir.dname.  But it would be nice to know that the ProgFile.fname exists in 
File.name.

If I added Progfile.dname, so that I could declare a foreign key to File, it 
would be nice to also add it to the FK declaration referring to ProgDir:

foreign key (pname, pord, dname) references ProgDir(pname, ord,
dname)

because that reflects the rule that permission to use a file requires 
permission to use the file's directory.

In both cases, I'm stuck.  SQLite requires a FK declaration to match the 
referrenced table's PK, else you get a "foreign key mismatch"
error.  (At least, that's my understanding of, "Parent and child keys must have 
the same cardinality." If ProgFile has no dname, it can't have a foreign key to 
File. If it does have dname, it can't include it in its reference to ProgDir.

The relational model doesn't recognize the concept of "primary key", and the 
rule that a foreign key must refer to a primary key is not part of the SQL 
standard.  The rule is unnecessary, and interferes with accurate foreign key 
reference declarations.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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