Re: [sqlite] parameters in a view, disallowed? (docs issue)

[Larry Brasfield]

On 12/20/2012 4:10 PM, Larry Brasfield wrote:
> Igor Tandetnik wrote:
 [regarding where parameters allowed, "where literals are"]
>> >
>> > How did you discern this?
>>
>> I know from experience where parameters work (SELECT, INSERT and
>> similar; also ATTACH as one of my projects happens to use it this way),
>> and where they don't (all forms of CREATE; I haven't tried ALTER but I'm
>> 99% sure it won't work there either). I've just tested PRAGMA. I briefly
>> scanned the rest at http://sqlite.org/lang.html to confirm that their
>> syntax doesn't involve expressions.
>
> I do not mean to be argumentative here, but I think the documentation on
> this leads to a different result.  If you examine the syntax diagram for
> "create view"

Why should I? I never made any claim about CREATE VIEW's syntax. In my
explanation, "All forms of CREATE", which includes CREATE VIEW, fall
under "know from experience" bucket, not "syntax doesn't involve
expressions" bucket.


You assert (believably) that SQLite has not allowed parameters in views 
for awhile, and that you know this from experience rather than the 
documentation.  I assert (without refutation) that the documentation 
clearly allows parameters in a view.  We have no disagreement, but it 
does appear that SQLite's operation differs from what its documentation 
clearly implies.  Hence my revision of the topic.


I will not suggest that you should be interested in this discrepancy, 
but others might be.


Best regards,
--
Larry Brasfield

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

Re: [sqlite] How to verify referential integrity of SQLite database

[Richard Hipp]

On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
lhask...@bloomberg.net> wrote:

> Tested, works perfectly. Thanks!
>
> Two questions/observation:
>
> 1. Should there be a way to determine the parent key w/o looking at or
> parsing the schema DDL commands?
>
> For example:
> SQLite version 3.7.16 2012-12-20 01:15:20
>
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table p(a, b, primary key(a, b), unique(b, a));
> sqlite> create table c(x, y, foreign key(x, y) references p);
> sqlite> insert into p values (1, 2);
> sqlite> insert into c values (1, 2), (2, 1);
> sqlite> pragma foreign_key_check(c);
> c|2|p|0
>

The fourth column is the foreign_key_id.  If you look at the output of
PRAGMA foreign_key_list(c), you'll find all the information about parent
table and the columns that map between parent and child, for that id.


>
> Now I know that the second record is in violation but I don't know what
> key/index the foreign key actually refers to (and no other combination of
> existing pragmas will tell me).
>
> 2. While I do like your API far better than what I originally proposed, I
> found that returning no result in case of success may lead to confusion
> since unknown pragmas behave the same way. So if I run "pragma
> foreign_key_check;" and get empty result it can mean any of the following:
>
> 1. There are no foreign key violations - good!
> 2. My version of SQLite does not support this pragma yet
> 3. (In case of using the shell) I made a typo in the pragma name
>

The pragma throws an error if you enter the name of a table that does not
exist.  That handles case 3.  To verify 2, that the version of SQLite you
are using support foreign_key_check, simply use the name of a table that
does not exist and verify that you get an error back:

  PRAGMA foreign_key_check('no-such-table');  --- expect an error





>
> While I don't have a better suggestion now, I just wanted to point it out
> to you.
>
> Again thanks a lot!
> - Levi
>
> - Original Message -
> From: d...@sqlite.org
> To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org
> At: Dec 19 2012 21:10:52
>
>
>
> On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
> lhask...@bloomberg.net> wrote:
>
>> My suggestion would be to have check_integrity command verify referential
>> integrity as well only if it's executed while the foreign key enforcement
>> is enabled on the connection.
>>
>
> The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code
> that is destined to become 3.7.16) has a new pragma:
>
> PRAGMA foreign_key_check;
> PRAGMA foreign_key_check(TABLE);
>
> The second from checks all of the REFERENCES clauses in TABLE.  The first
> form checks the keys on all tables in the database.
>
> The result of the pragma is a table, with one row per mismatched key.  The
> row contains the name of the child table, the rowid of the child table, the
> name of the parent table, and the "foreign key index" which is an integer
> that describes the foreign key in PRAGMA foreign_key_list(CHILD).  If the
> foreign_key_check pragma returns an empty set, that means that all of the
> keys are correct.
>
> PRAGMA foreign_key_check works regardless of whether or not foreign keys
> are currently enabled or disabled.
>
>
> --
> 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] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

[Alem Biscan]

Thank you guys very much for answering,

Igor, i belive you are right. I think it is a typed dataset .NET bug. There
exists an option to use optimistic concurency. If it's on then it uses rows
affected to check if row was modified or not. The stupidity of it is even
if it is turned off ( checkbox unchecked ), it still throws the exception.
I am hoping that someone else working with System.Data.Sqlite and Typed
dataSets  encountered the same problem, and perhaps knows the solution.
Well, at least i have reduced the problem to typed DS, and eliminated
SQLite connector.

Alem

On Fri, Dec 21, 2012 at 1:48 AM, Igor Tandetnik  wrote:

> On 12/20/2012 7:21 PM, BareFeetWare wrote:
>
>> The changes are in fact made, but those avenues for checking don't work.
>> I'm tempted to label this as a bug in SQLite, since I see no reason for the
>> limitation.
>>
>
> If the trigger changes 20 records across 5 tables, which results in 63
> rows in the view visibly affected, which value should sqlite3_changes
> return?
> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

[Igor Tandetnik]

On 12/20/2012 7:21 PM, BareFeetWare wrote:

The changes are in fact made, but those avenues for checking don't work. I'm 
tempted to label this as a bug in SQLite, since I see no reason for the 
limitation.


If the trigger changes 20 records across 5 tables, which results in 63 
rows in the view visibly affected, which value should sqlite3_changes 
return?

--
Igor Tandetnik

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

Re: [sqlite] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

[BareFeetWare]

> *Throws DBconcurrency violation. Affected rows 0 instead of 1.*


I use updatable views all the time, via "instead of" triggers, as you 
described. They work well.

I think your problem may be that SQLite doesn't acknowledge changes via the 
changes() SQL function and sqlite3_changes() C function, which I suspect your 
wrapper is using.

On the SQLite web site:
http://www.sqlite.org/lang_corefunc.html

>> changes()The changes() function returns the number of database rows that 
>> were changed or inserted or deleted by the most recently completed INSERT, 
>> DELETE, or UPDATE statement, exclusive of statements in lower-level 
>> triggers. The changes() SQL function is a wrapper around the 
>> sqlite3_changes() C/C++ function and hence follows the same rules for 
>> counting changes.

http://www.sqlite.org/c3ref/changes.html

>> Changes to a view that are simulated by an INSTEAD OF trigger are not 
>> counted. Only real table changes are counted.


The changes are in fact made, but those avenues for checking don't work. I'm 
tempted to label this as a bug in SQLite, since I see no reason for the 
limitation.

I hope this helps,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

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

[sqlite] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

[Alem Biscan]

Hello,

I am developing an application using Visual Studio 2008 ( C# .NET 3.5SP1 )
and sqlite ( System.Data.Sqlite ).
Whole database interaction is written using strongly typed datasets. All
was fine while working with tables, but when i tried to
update views, it was brick wall. I was hoping to use sqlite view just like
any table trough the designer. Issue commands, and let instead of trigger
to do the logic.

 try
 {
 this.katalogvBindingSource.RemoveCurrent();
 }
 catch (Exception exception)
 {
 }

try
{
this.katalogvTableAdapter.Update(fkasaDataSet.katalogv);
}
catch (SqliteException ex)
{
}

*Throws DBconcurrency violation. Affected rows 0 instead of 1.*

Even though i have turned optimistic concurrency off.

Delete command works fine. Executed the command trough VS dataset designer,
and everything was fine, instead of trigger did all buisness logic it was
designed for. Since i ' ve had the same problem testing on postgreSQL, i am
thinking this is probably typed dataset bug, and not sqlite problem. Since
no one replied to my question on official .NET forums, i am desperatly
trying here. Maybe someone helps. If not , oh well i will manualy code it.
But would like to keep coding style consistency.

Maybe someone knows how to bypass this.

Thank you in advance,
Alem Bišćan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters in a view, disallowed?

[Igor Tandetnik]

On 12/20/2012 5:20 PM, Simon Slavin wrote:

I've understood that the optimizer can be usefully used on prepared statements 
before the parameters are known.  An implication from this is that table names 
cannot be parameterized.


Table names cannot be parameterized for the simple reason that, 
syntactically, they are not literals. The syntax only allows parameter 
placeholders where a literal (like 123 or 'xyz') may legitimately 
appear. That is not at all in contention.

--
Igor Tandetnik

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

Re: [sqlite] parameters in a view, disallowed?

[Simon Slavin]

On 20 Dec 2012, at 8:26pm, Igor Tandetnik  wrote:

> I know from experience where parameters work (SELECT, INSERT and similar; 
> also ATTACH as one of my projects happens to use it this way), and where they 
> don't (all forms of CREATE; I haven't tried ALTER but I'm 99% sure it won't 
> work there either).

I've understood that the optimizer can be usefully used on prepared statements 
before the parameters are known.  An implication from this is that table names 
cannot be parameterized.

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

Re: [sqlite] parameters in a view, disallowed? (docs issue)

[Igor Tandetnik]

On 12/20/2012 4:10 PM, Larry Brasfield wrote:

Igor Tandetnik wrote:

[regarding where parameters allowed, "where literals are"]

>
> How did you discern this?

I know from experience where parameters work (SELECT, INSERT and
similar; also ATTACH as one of my projects happens to use it this way),
and where they don't (all forms of CREATE; I haven't tried ALTER but I'm
99% sure it won't work there either). I've just tested PRAGMA. I briefly
scanned the rest at http://sqlite.org/lang.html to confirm that their
syntax doesn't involve expressions.


I do not mean to be argumentative here, but I think the documentation on
this leads to a different result.  If you examine the syntax diagram for
"create view"


Why should I? I never made any claim about CREATE VIEW's syntax. In my 
explanation, "All forms of CREATE", which includes CREATE VIEW, fall 
under "know from experience" bucket, not "syntax doesn't involve 
expressions" bucket.

--
Igor Tandetnik

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

Re: [sqlite] parameters in a view, disallowed? (docs issue)

[Larry Brasfield]

Igor Tandetnik wrote:

[regarding where parameters allowed, "where literals are"]

>
> How did you discern this?

I know from experience where parameters work (SELECT, INSERT and
similar; also ATTACH as one of my projects happens to use it this way),
and where they don't (all forms of CREATE; I haven't tried ALTER but I'm
99% sure it won't work there either). I've just tested PRAGMA. I briefly
scanned the rest at http://sqlite.org/lang.html to confirm that their
syntax doesn't involve expressions.


I do not mean to be argumentative here, but I think the documentation on 
this leads to a different result.  If you examine the syntax diagram for 
"create view", it refers to the construct 'select-stmt', which in turn 
refers to the construct 'expr'.  (See 
http://sqlite.org/lang_createview.html , 
http://sqlite.org/lang_select.html , http://sqlite.org/lang_expr.html ) 
 There, one alternative for expr is given as 'bind-parameter', which 
seems to not be defined under that name.  However, the same page on 
'expr' defines parameters.  Since using parameters in a view appears to 
be allowed by the syntax diagrams, and since I saw nothing to the 
contrary where it logically should appear (as a semantic exception to 
the syntax), I went ahead and tried to use them in a view.


Best regards,
--
Larry Brasfield

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

Re: [sqlite] parameters in a view, disallowed?

[Igor Tandetnik]

On 12/20/2012 2:30 PM, Larry Brasfield wrote:

I believe it's pretty simple. Parameters are allowed everywhere a
literal may appear, except in schema definition statements, namely
various CREATE statements as well as ALTER TABLE; and also PRAGMA (not
sure why). Currently, this leaves SELECT, INSERT, UPDATE, DELETE,
REPLACE, ATTACH (the file name is an expression which allows parameters)
and EXPLAIN (when the query being explained itself allows parameters).
None of the remaining statements allow for literals in their syntax, so
the issue is moot for them.


How did you discern this?


I know from experience where parameters work (SELECT, INSERT and 
similar; also ATTACH as one of my projects happens to use it this way), 
and where they don't (all forms of CREATE; I haven't tried ALTER but I'm 
99% sure it won't work there either). I've just tested PRAGMA. I briefly 
scanned the rest at http://sqlite.org/lang.html to confirm that their 
syntax doesn't involve expressions.

--
Igor Tandetnik

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

Re: [sqlite] How to verify referential integrity of SQLite database

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

Tested, works perfectly. Thanks!


Two questions/observation:


1. Should there be a way to determine the parent key w/o looking at or parsing 
the schema DDL commands?


For example:
SQLite version 3.7.16 2012-12-20 01:15:20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table p(a, b, primary key(a, b), unique(b, a));
sqlite> create table c(x, y, foreign key(x, y) references p);
sqlite> insert into p values (1, 2);
sqlite> insert into c values (1, 2), (2, 1);
sqlite> pragma foreign_key_check(c);
c|2|p|0



Now I know that the second record is in violation but I don't know what 
key/index the foreign key actually refers to (and no other combination of 
existing pragmas will tell me).


2. While I do like your API far better than what I originally proposed, I found 
that returning no result in case of success may lead to confusion since unknown 
pragmas behave the same way. So if I run "pragma foreign_key_check;" and get 
empty result it can mean any of the following:


1. There are no foreign key violations - good!
2. My version of SQLite does not support this pragma yet
3. (In case of using the shell) I made a typo in the pragma name


While I don't have a better suggestion now, I just wanted to point it out to 
you.


Again thanks a lot!
 - Levi


- Original Message -
From: d...@sqlite.org

To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org

At: Dec 19 2012 21:10:52





On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) 
 wrote:


My suggestion would be to have check_integrity command verify referential 
integrity as well only if it's executed while the foreign key enforcement is 
enabled on the connection.




The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that 
is destined to become 3.7.16) has a new pragma:

PRAGMA foreign_key_check;
PRAGMA foreign_key_check(TABLE);



The second from checks all of the REFERENCES clauses in TABLE.  The first form 
checks the keys on all tables in the database.

The result of the pragma is a table, with one row per mismatched key.  The row 
contains the name of the child table, the rowid of the child table, the name of 
the parent table, and the "foreign key index" which is an integer that 
describes the foreign key in PRAGMA foreign_key_list(CHILD).  If the 
foreign_key_check pragma returns an empty set, that means that all of the keys 
are correct.



PRAGMA foreign_key_check works regardless of whether or not foreign keys are 
currently enabled or disabled.


-- 
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] parameters in a view, disallowed?

[Larry Brasfield]

Igor Tandetnik wrote:

On 12/20/2012 1:27 PM, Larry Brasfield wrote:
> With recent versions of SQLite, a prepare call fails when there are
> parameters in the SQL for a 'create view' statement.

Did it ever work with any version of SQLite? Parameters in DDL
statements don't make sense.


Agreed with respect to DDL.  However, creating a view is not quite fully 
DDL.  A view, at least in read-only usage, can be considered a deferred 
or encapsulated query (or subquery).



>  I stumbled into
> this for two reasons: The documentation for parameters and ..._prepare
> does not contra-indicate such usage; it seemed perfectly sensible; and
> it was useful in my application.  (I have a complex view setup which I
> had wanted to vary, parametrically, when the view was queried.)

By what mechanism did you plan to feed parameter values into the view
while SELECTing against it? What would the (hypothetical) syntax be like
for such a beast?


In whatever query finally uses the view, the parameters would have to 
substituted just as when they appear directly in a query.



> Would there be any downside to allowing named parameters to remain in a
> view's SQL?

Ah, I think I see what you have in mind. If you do "select * from
MyView", this query, even though it doesn't on the face of it appear to
contain any placeholders, would be deemed to sort of incorporate by
reference parameter placeholders from MyView.


Yes.


Specifying such a feature would sure be fun. Say, if I select against
two views that each have parameter named :X, should the query be
considered to have one parameter (so that a single bound value applies
to both views), or two (and then how would you refer to them?) What to
do with a parameter like ?1 - where do you start counting?


If somebody were such a masochist as to use placement-identified 
parameters, they would have to be counted as if appearing where the view 
is expanded.  This seems harmless except to those who invite the 
potential for confusion by using nameless parameters.



> I am tempted to offer a paragraph with which to enhance the doc page on
> parameters, explaining where they are allowed and where not.  But I can
> only guess what it should say without a lot of experimentation or study
> of the code.

I believe it's pretty simple. Parameters are allowed everywhere a
literal may appear, except in schema definition statements, namely
various CREATE statements as well as ALTER TABLE; and also PRAGMA (not
sure why). Currently, this leaves SELECT, INSERT, UPDATE, DELETE,
REPLACE, ATTACH (the file name is an expression which allows parameters)
and EXPLAIN (when the query being explained itself allows parameters).
None of the remaining statements allow for literals in their syntax, so
the issue is moot for them.


How did you discern this?  If it is in the docs, it is scattered and not 
tied to the sections that are primarily about parameters.


By the way, thanks for your help elsewhere.

Best regards,
--
Larry Brasfield

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

Re: [sqlite] parameters in a view, disallowed?

[Igor Tandetnik]

On 12/20/2012 1:27 PM, Larry Brasfield wrote:

With recent versions of SQLite, a prepare call fails when there are
parameters in the SQL for a 'create view' statement.


Did it ever work with any version of SQLite? Parameters in DDL 
statements don't make sense.



 I stumbled into
this for two reasons: The documentation for parameters and ..._prepare
does not contra-indicate such usage; it seemed perfectly sensible; and
it was useful in my application.  (I have a complex view setup which I
had wanted to vary, parametrically, when the view was queried.)


By what mechanism did you plan to feed parameter values into the view 
while SELECTing against it? What would the (hypothetical) syntax be like 
for such a beast?



Would there be any downside to allowing named parameters to remain in a
view's SQL?


Ah, I think I see what you have in mind. If you do "select * from 
MyView", this query, even though it doesn't on the face of it appear to 
contain any placeholders, would be deemed to sort of incorporate by 
reference parameter placeholders from MyView.


Specifying such a feature would sure be fun. Say, if I select against 
two views that each have parameter named :X, should the query be 
considered to have one parameter (so that a single bound value applies 
to both views), or two (and then how would you refer to them?) What to 
do with a parameter like ?1 - where do you start counting?



I am tempted to offer a paragraph with which to enhance the doc page on
parameters, explaining where they are allowed and where not.  But I can
only guess what it should say without a lot of experimentation or study
of the code.


I believe it's pretty simple. Parameters are allowed everywhere a 
literal may appear, except in schema definition statements, namely 
various CREATE statements as well as ALTER TABLE; and also PRAGMA (not 
sure why). Currently, this leaves SELECT, INSERT, UPDATE, DELETE, 
REPLACE, ATTACH (the file name is an expression which allows parameters) 
and EXPLAIN (when the query being explained itself allows parameters). 
None of the remaining statements allow for literals in their syntax, so 
the issue is moot for them.

--
Igor Tandetnik

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

[sqlite] parameters in a view, disallowed?

[Larry Brasfield]

With recent versions of SQLite, a prepare call fails when there are 
parameters in the SQL for a 'create view' statement.  I stumbled into 
this for two reasons: The documentation for parameters and ..._prepare 
does not contra-indicate such usage; it seemed perfectly sensible; and 
it was useful in my application.  (I have a complex view setup which I 
had wanted to vary, parametrically, when the view was queried.)


Upon thinking over how parameter substitution must be implemented, I can 
see that there may be difficulty with parameters in a view since its 
select appears to be stored as SQL, not subject to substitution at the 
time the view is created.  However, that seems like a feature rather 
than a bug.


Would there be any downside to allowing named parameters to remain in a 
view's SQL?


I am tempted to offer a paragraph with which to enhance the doc page on 
parameters, explaining where they are allowed and where not.  But I can 
only guess what it should say without a lot of experimentation or study 
of the code.


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

Re: [sqlite] SQLite Version 3.7.15.1

[Dan Kennedy]

On 12/20/2012 01:26 PM, Patrik Nilsson wrote:

Thank you for the release!

I can't find sqlite-shell-linux-x86-3071501.zip and sqlite-doc-3071501.zip.


Thanks. They are there now.

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