Re: [sqlite] Stricter parsing rules

[Simon Slavin]

On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:

> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
> errors. I'd really like to see the rules tightened to remove ambiguous input.

Me three.  Perhaps this and a couple of similar things will be fixed in SQLite4.

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

Re: [sqlite] Stricter parsing rules

[BareFeetWare]

I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
errors. I'd really like to see the rules tightened to remove ambiguous input.

Thanks,
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

On 15/11/2012, at 7:37 AM, NSRT Mail account.  wrote:

> However, we've been bitten a few times by SQLite's compatibility features 
> which relax rules, and create ambiguity.
> 
> When listing columns to retrieve in SELECT, or as part of a WHERE clause, we 
> always enclose our identifiers in double quotes. However, if a developer 
> accidentally misspells a column name, instead of failing with "Error: no such 
> column: xx", SQLite reinterprets the identifier as a string, and carries 
> on as if nothing happened. Sometimes such bugs go unnoticed for a while.
> 
> Is there any way to make SQLite's parsing stricter?


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

[sqlite] Stricter parsing rules

[NSRT Mail account.]

In accordance with SQL standards and SQLite developing guidelines, my entire 
team always uses double quotes around identifiers, and single quotes around 
string literals.

Some relevant documentation is here: http://www.sqlite.org/lang_keywords.html
"SQLite adds new keywords from time to time when it takes on new features.
So to prevent your code from being broken by future enhancements, you should
normally quote any identifier that is an English language word, even if
you do not have to." 


However, we've been bitten a few times by SQLite's compatibility features which 
relax rules, and create ambiguity.

When listing columns to retrieve in SELECT, or as part of a WHERE clause, we 
always enclose our identifiers in double quotes. However, if a developer 
accidentally misspells a column name, instead of failing with "Error: no such 
column: xx", SQLite reinterprets the identifier as a string, and carries on 
as if nothing happened. Sometimes such bugs go unnoticed for a while.

Is there any way to make SQLite's parsing stricter? Or are the only options to 
have code which fails silently, or unquoted identifiers which may break with an 
SQLite upgrade?
I could not find any compiling options or pragmas for this, so I looked through 
the SQLite source code to see if I could fix this myself.

In resolve.c, (which in the amalgamation for 3.7.14.1 at line 72340) I found 
this bit of code with a comment:
  /*
  ** If X and Y are NULL (in other words if only the column name Z is
  ** supplied) and the value of Z is enclosed in double-quotes, then
  ** Z is a string literal if it doesn't match any column names.  In that
  ** case, we need to return right away and not make any changes to
  ** pExpr.
  **
  ** Because no reference was made to outer contexts, the pNC->nRef
  ** fields are not changed in any context.
  */
  if( cnt==0 && zTab==0 && ExprHasProperty(pExpr,EP_DblQuoted) ){
    pExpr->op = TK_STRING;
    pExpr->pTab = 0;
    return WRC_Prune;
  }

I wrapped this in a define which omits double quotes strings by default and 
tested it.

Before:
sqlite> SELECT 'cows';
cows

sqlite> SELECT "cows";

cows

After:
sqlite> SELECT 'cows';
cows 
sqlite> SELECT "cows";
Error: no such column: cows

I wanted to know if
A) There is some existing options that I overlooked?
B) If not, is my "fix" correct, or will it break something else?
C) Can a stricter parsing option become part of the mainline codebase?

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

Re: [sqlite] Trigger logic with INSERT OR REPLACE

[Simon Slavin]

On 14 Nov 2012, at 3:17pm, kyan  wrote:

> PS: Because of this the INSERT OR REPLACE statement cannot be
> considered logically equivalent to MSSQL or ORACLE's MERGE statement
> (http://en.wikipedia.org/wiki/Merge_%28SQL%29)  which IMHO should be
> added to SQLite at some point.

The two are sufficiently different that I would never confuse them.  It's also 
worth knowing about

UPDATE OR REPLACE

which is similarly not MERGE, but has more MERGE-like behaviour.  Though what 
it does I had to figure out myself since the documentation on the 'UPDATE' page 
doesn't describe it.

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

Re: [sqlite] Trigger logic with INSERT OR REPLACE

[kyan]

> No.  INSERT OR REPLACE does an INSERT, possibly after doing a DELETE.  It 
> never does an UPDATE.

http://www.sqlite.org/lang_conflict.html

Similarly any detail rows that are linked with foreign keys to the
affected row will be handled according to the FK's ON DELETE clause.
For instance if the FK is ON DELETE CASCADE detail records will be
deleted even if the affected row's key's value is not changed by the
INSERT or REPLACE statement.

create table master (id int primary key, descr text);
create table detail (id int, parentid int, descr text, foreign key
(parentid) references master on delete cascade);
insert into master values (1, '1');
insert into detail values (1, 1, '1');
insert or replace into master values (1, '2');
select * from detail;
> 0 records

I mention this because it was a cause of grief for me, not having paid
proper attention to the behaviour of INSERT OR REPLACE.

PS: Because of this the INSERT OR REPLACE statement cannot be
considered logically equivalent to MSSQL or ORACLE's MERGE statement
(http://en.wikipedia.org/wiki/Merge_%28SQL%29)  which IMHO should be
added to SQLite at some point.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger logic with INSERT OR REPLACE

[Daniel Polski]

Ok thanks!


Simon Slavin skrev 2012-11-14 16:04:

On 14 Nov 2012, at 2:58pm, Daniel Polski  wrote:


If I have a value in a table, and use INSERT OR REPLACE to update the value, a trigger 
created with AFTER UPDATE ON won't fire. Shouldn't an INSERT OR REPLACE to a table which 
already contain data which are beeing replaced be considered an "update" of the 
table?

No.  INSERT OR REPLACE does an INSERT, possibly after doing a DELETE.  It never 
does an UPDATE.

Simon.
___
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] Trigger logic with INSERT OR REPLACE

[Simon Slavin]

On 14 Nov 2012, at 2:58pm, Daniel Polski  wrote:

> If I have a value in a table, and use INSERT OR REPLACE to update the value, 
> a trigger created with AFTER UPDATE ON won't fire. Shouldn't an INSERT OR 
> REPLACE to a table which already contain data which are beeing replaced be 
> considered an "update" of the table?

No.  INSERT OR REPLACE does an INSERT, possibly after doing a DELETE.  It never 
does an UPDATE.

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

[sqlite] Trigger logic with INSERT OR REPLACE

[Daniel Polski]

Hello,
If I have a value in a table, and use INSERT OR REPLACE to update the 
value, a trigger created with AFTER UPDATE ON won't fire. Shouldn't an 
INSERT OR REPLACE to a table which already contain data which are beeing 
replaced be considered an "update" of the table?


CREATE table t1 (
value INT
);

CREATE table t2 (
text VARCHAR(30)
);

CREATE trigger trigger_1 AFTER INSERT on t1
BEGIN
INSERT INTO t2 values("trigger 1 fired");
END;

CREATE trigger trigger_2 AFTER UPDATE on t1
BEGIN
INSERT INTO t2 values("trigger 2 fired");
END;

-- fires trigger 1 as expected
INSERT OR REPLACE INTO t1 VALUES("1");

--this will also fire trigger 1, even though the action updates an 
already existing value in t1

INSERT OR REPLACE INTO t1 VALUES("2");


Best Regards,
Daniel

___
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

[Igor Tandetnik]

LMHmedchem  wrote:
> As it happens, some of the text strings that will be added to the database
> have single quotes,
> 
> N,N'-dimethylethylenediamine
> 
> do I need to do anything different for these

Normally, your program shouldn't use string literals directly, but instead uses 
parameterized queries: http://sqlite.org/c3ref/bind_blob.html . Then you don't 
need to worry about single quotes. Obligatory: http://xkcd.com/327/

If for some reason you insist on embedding them directly into queries as 
literals, then, as Simon explained, you should double up each apostrophe, as in 
'N,N''-dimethylethylenediamine'. Doesn't matter how many of them in a row, or 
whether they are in the beginning, end or middle of the string. E.g. if you 
want to insert a three-character string 'x' (including apostrophes), the 
correct string literal is '''x''' (three apostrophes on either side).

SQLite provides a helper function sqlite3_mprintf 
(http://sqlite.org/c3ref/mprintf.html) , which is similar to regular sprintf() 
except that a) it allocates memory for the result, so you don't need to worry 
about sufficient buffer size, and b) it recognizes %q and %Q format specifiers, 
which are like %s but escape single quotes appropriately.
-- 
Igor Tandetnik

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

Re: [sqlite] added new db with other journal mode. Any issue?

[Durga D]

this the sequence of sqlite statements:
--

sqlite3_open
sqlite3_key
sqlite3_prepare_v2
sqlite3_step
sqlite3_finalize
sqlite3_prepare_v2 --- //my application crashed here




On Wed, Nov 14, 2012 at 5:14 PM, Durga D  wrote:

> sqlite_source_id(): 2012-03-20 11:35:50
> 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
>
> I will send the sequence of sql stmts.
>
>
> On Wed, Nov 14, 2012 at 4:51 PM, Richard Hipp  wrote:
>
>> On Wed, Nov 14, 2012 at 3:41 AM, Durga D  wrote:
>>
>> > I got the below error:
>> > ---
>> > The database disk image is malformed.
>> >
>>
>> Please send:
>>
>> (1) The version of SQLite you are running - the output of "SELECT
>> sqlite_source_id();".
>> (2) The exact sequence of SQL statements and API calls you used to create
>> the error above.  Be specific, please.
>>
>> Thanks.
>>
>>
>> >
>> > I am able to open. It's throwing above error when I try to set journal
>> mode
>> > as truncate after db open before tables creation. How to solve this?
>> >
>> >
>> > On Wed, Nov 14, 2012 at 11:59 AM, Simon Slavin 
>> > wrote:
>> >
>> > >
>> > > On 14 Nov 2012, at 7:35am, Durga D  wrote:
>> > >
>> > > > I have 8 sqlite databases with TRUNCATE journal mode. I have added
>> new
>> > > > database with WAL journal mode. Now, 9 databases in my application.
>> > Will
>> > > it
>> > > > give any database corruption?
>> > >
>> > > You can mix different modes for different databases without problems.
>> > >
>> > > Simon.
>> > > ___
>> > > 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
>> >
>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 13 Nov 2012, at 5:41pm, LMHmedchem  wrote:

> Thanks for the clarification. So my proper syntax for inserts with
> AUTOINCREMENT is one of,
> 
> INSERT INTO Structure
> VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
> 
> or
> 
> INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw)
> VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
> 
> or
> 
> INSERT INTO Structure(name, filePath, iH1, iH2, iH3, iH4, formula, fw)
> VALUES('phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);

Yes, all three will work (unless my old eyes missed something) as will several 
other formulations.  See the diagram on this page:



> As it happens, some of the text strings that will be added to the database
> have single quotes,
> 
> N,N'-dimethylethylenediamine
> 
> do I need to do anything different for these, such as to escape the single
> single-quote in some way? There will never be double quotes, but there could
> be any number of single quotes.
> 
> N,N',N''-trimethylbis(hexamethylene)triamine

Yes, if you are passing entire SQL commands in as text, you absolutely do need 
to worry about this.  You can write your own code which will double the quote:



As an example, the following is a valid way to insert the above formulation:

INSERT INTO Structure(name) 
VALUES('N,N'',N-trimethylbis(hexamethylene)triamine');

Doubling single quotes is the only thing you should need to do to a 
conventional C string.  You could write a standard routine to do it and call it 
for all string values you are concatenating into a SQL command.  However, if 
instead of passing entire commands you are using binding to bind a string to a 
parameter of the INSERT command, then you do not need to escape the string 
you're passing.

By the way, can I ask what iH2, iH3, iH4 are ?  I think I've figured out iH1.

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

Re: [sqlite] added new db with other journal mode. Any issue?

[Durga D]

sqlite_source_id(): 2012-03-20 11:35:50
00bb9c9ce4f465e6ac321ced2a9d0062dc364669

I will send the sequence of sql stmts.


On Wed, Nov 14, 2012 at 4:51 PM, Richard Hipp  wrote:

> On Wed, Nov 14, 2012 at 3:41 AM, Durga D  wrote:
>
> > I got the below error:
> > ---
> > The database disk image is malformed.
> >
>
> Please send:
>
> (1) The version of SQLite you are running - the output of "SELECT
> sqlite_source_id();".
> (2) The exact sequence of SQL statements and API calls you used to create
> the error above.  Be specific, please.
>
> Thanks.
>
>
> >
> > I am able to open. It's throwing above error when I try to set journal
> mode
> > as truncate after db open before tables creation. How to solve this?
> >
> >
> > On Wed, Nov 14, 2012 at 11:59 AM, Simon Slavin 
> > wrote:
> >
> > >
> > > On 14 Nov 2012, at 7:35am, Durga D  wrote:
> > >
> > > > I have 8 sqlite databases with TRUNCATE journal mode. I have added
> new
> > > > database with WAL journal mode. Now, 9 databases in my application.
> > Will
> > > it
> > > > give any database corruption?
> > >
> > > You can mix different modes for different databases without problems.
> > >
> > > Simon.
> > > ___
> > > 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
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[LMHmedchem]

Thanks for the clarification. So my proper syntax for inserts with
AUTOINCREMENT is one of,

INSERT INTO Structure
VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);

or

INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw)
VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);

or

INSERT INTO Structure(name, filePath, iH1, iH2, iH3, iH4, formula, fw)
VALUES('phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);

As it happens, some of the text strings that will be added to the database
have single quotes,

N,N'-dimethylethylenediamine

do I need to do anything different for these, such as to escape the single
single-quote in some way? There will never be double quotes, but there could
be any number of single quotes.

N,N',N''-trimethylbis(hexamethylene)triamine

These single-quotes will never appear as the first or last character, so
possibly nothing needs to be done?

*LMHmedchem*




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65524.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] added new db with other journal mode. Any issue?

[Richard Hipp]

On Wed, Nov 14, 2012 at 3:41 AM, Durga D  wrote:

> I got the below error:
> ---
> The database disk image is malformed.
>

Please send:

(1) The version of SQLite you are running - the output of "SELECT
sqlite_source_id();".
(2) The exact sequence of SQL statements and API calls you used to create
the error above.  Be specific, please.

Thanks.


>
> I am able to open. It's throwing above error when I try to set journal mode
> as truncate after db open before tables creation. How to solve this?
>
>
> On Wed, Nov 14, 2012 at 11:59 AM, Simon Slavin 
> wrote:
>
> >
> > On 14 Nov 2012, at 7:35am, Durga D  wrote:
> >
> > > I have 8 sqlite databases with TRUNCATE journal mode. I have added new
> > > database with WAL journal mode. Now, 9 databases in my application.
> Will
> > it
> > > give any database corruption?
> >
> > You can mix different modes for different databases without problems.
> >
> > Simon.
> > ___
> > 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
>



-- 
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] Minor documentation error

[Richard Hipp]

On Wed, Nov 14, 2012 at 5:26 AM, Kevin Benson wrote:

> On Oct 29, 2012; 9:09am Simon Slavin wrote:
>
> The page
>
> 
>
> says "As long a connection is using a shared-memory wal-index" near the
> bottom. I believe that this should be "As long as a connection is using a
> shared-memory wal-index".
>


http://www.sqlite.org/docsrc/info/64593a771c

The websites will get updated the next time we rebuild them - probably at
the next release.


>
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> 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] added new db with other journal mode. Any issue?

[Durga D]

I got the below error:
---
The database disk image is malformed.

I am able to open. It's throwing above error when I try to set journal mode
as truncate after db open before tables creation. How to solve this?


On Wed, Nov 14, 2012 at 11:59 AM, Simon Slavin  wrote:

>
> On 14 Nov 2012, at 7:35am, Durga D  wrote:
>
> > I have 8 sqlite databases with TRUNCATE journal mode. I have added new
> > database with WAL journal mode. Now, 9 databases in my application. Will
> it
> > give any database corruption?
>
> You can mix different modes for different databases without problems.
>
> Simon.
> ___
> 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] added new db with other journal mode. Any issue?

[Simon Slavin]

On 14 Nov 2012, at 7:35am, Durga D  wrote:

> I have 8 sqlite databases with TRUNCATE journal mode. I have added new
> database with WAL journal mode. Now, 9 databases in my application. Will it
> give any database corruption?

You can mix different modes for different databases without problems.

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