Re: [sqlite] Stricter parsing rules
Hi Dan, I had some free time and looked into your request. Bear in mind I don't really know what I'm doing, but I managed to whip up this: http://paste.nachsoftware.com/SQLite3/BrksDfe9d421e8ed00d34f0fc50a59956af21byK Results: > ./sqlite3 SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT "cows"; Error: no such column: cows sqlite> PRAGMA dblquoted_str_lit=1; sqlite> SELECT "cows"; cows sqlite> PRAGMA dblquoted_str_lit=0; sqlite> SELECT "cows"; Error: no such column: cows I believe this is what you wanted. I imagine the naming would be improved if this code became mainline, and the ifdefs would be handled better, but I guess this is pretty much what you wanted. Note: This changes the default behavior of SQLite. A proper patch of course would keep the default behavior. Although I couldn't figure out the pragma system enough to see how to specify what default pragma settings are. From: Dan Freundel To: General Discussion of SQLite Database Sent: Thursday, November 15, 2012 7:35 PM Subject: Re: [sqlite] Stricter parsing rules I agree as well. I tested out the original poster's patch and it works great. Any way this can be made into a "PRAGMA strict_mode" so that it would be usage-specific rather than compile-specific? I asked about this on stackoverfolw as well:http://stackoverflow.com/questions/13383763/is-there-a-way-disable-lax-quoting-rules-in-sqlite/ --- On Thu, 11/15/12, John Gabriele wrote: From: John Gabriele Subject: Re: [sqlite] Stricter parsing rules To: "General Discussion of SQLite Database" Date: Thursday, November 15, 2012, 1:56 PM On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin wrote: > > 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. Me four. The OP's practice of always using double-quotes for identifiers seems like a sound one, and it would be useful if sqlite could tell me if the thing I *think* is an identifier is actually not. ---John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stricter parsing rules
Hello Everyone, I'm glad to see I'm not the only one who wants this. I'm thinking perhaps I wasn't clear enough in my first e-mail that I created a patch for the time being. You can download the patch, along with already modified source, static libraries and binaries for Linux i386, Linux AMD64, and Windows here: http://nsrt.nachsoftware.org/sqlite3-3.7.14.1-without-dblquote-string.tar.bz2 467a12059d50850f394066195d70c659 c15bc452d9db38ac47a557d06f89ff0658220861 If you're compiling yourself, add -DSQLITE_OMIT_DBLQUOTED_STRINGS to your flags to remove double quoted string literals from being supported. I really wanted to know what people think of the patch itself. @Tom >this tolerance by SQLite for misquoted identifiers allows a lot of errors. The case I'm dealing with here is actually misquoted string literals, which affects misspelled identifiers. I assume that's what you meant. While I see some wanting the ability to drop [] and `` from identifier support for the sake of purity, I don't know of a case where keeping it actually breaks any valid SQL. @Simon >Perhaps this and a couple of similar things will be fixed in SQLite4. While I hope that too, I would like a fix to become mainline in SQLite3. As is, you can play with my patch right now in the latest version of SQLite3! @Dave >The behaviour of quoting with [identifier] or `identifier` seems to do what you want, but, as the docs say, these are not standard SQL. Nice idea! Definitely one to consider without any kind of patch. However if you're trying to write cross platform SQL, that can be a bit painful. :( @Dan >I tested out the original poster's patch and it works great. Any way this can be made into a "PRAGMA strict_mode" so that it would be usage-specific rather than compile-specific? Thanks for testing it. I appreciate the feedback and glad to hear it works well for you. I don't know a lot about SQLite's source code. I just really wanted this fixed, and thank God after 15 minutes of grepping the source, I happened upon the code which seems to control double quoted string literals. I don't really know SQLite's source code, let alone the pragma system, but if I have time, I'll see what I can do. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Stricter parsing rules
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] sqlite3_column_name() contains quotes for views
In the example, I just realized something that makes matters worse.
sqlite> SELECT "id" AS "id", "name" AS "name" FROM "names";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
Despite using a view, using AS seems to remove the quotes.
From: NSRT Mail account.
To: "sqlite-users@sqlite.org"
Sent: Monday, October 29, 2012 2:33 PM
Subject: [sqlite] sqlite3_column_name() contains quotes for views
I believe I ran into a bug with SQLite, and would like to ensure the problem is
not on my end.
I created a simple table along with a view of it:
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "namesReal" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
);
INSERT INTO "namesReal" VALUES(1,'Linus');
INSERT INTO "namesReal" VALUES(2,'Bill');
INSERT INTO "namesReal" VALUES(3,'Steve');
INSERT INTO "namesReal" VALUES(4,'Richard');
INSERT INTO "namesReal" VALUES(5,'Ninjas');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('namesReal',10);
CREATE VIEW "names" AS SELECT * FROM "namesReal";
COMMIT;
-
At this point selecting from names or namesReal should generate the same data:
sqlite> .header on
sqlite> SELECT "id", "name" FROM "namesReal";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The data above is good, the column names, as well as the row values. But look
what happens when selecting from the view:
sqlite> SELECT "id", "name" FROM "names";
"id"|"name"
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The quotes are being includes in the column names unlike the prior case.
However when selecting via wildcard, this happens:
sqlite> SELECT * FROM "names";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
It appears from these examples, that SQLite mistakenly? is including the
decorations around column names as used by the query for views. Unless I'm
mistaken, column names are supposed to be quoted in SQL in order to prevent
conflict with reserved words.
When trying to query this database with the API, sqlite3_column_name() includes
the quotes around the column name in the second select statement, but not in
the first or third. So it seems the issue is with that function, and not some
quirk of the command line client.
Is this a bug? Or am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_column_name() contains quotes for views
I believe I ran into a bug with SQLite, and would like to ensure the problem is
not on my end.
I created a simple table along with a view of it:
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "namesReal" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
);
INSERT INTO "namesReal" VALUES(1,'Linus');
INSERT INTO "namesReal" VALUES(2,'Bill');
INSERT INTO "namesReal" VALUES(3,'Steve');
INSERT INTO "namesReal" VALUES(4,'Richard');
INSERT INTO "namesReal" VALUES(5,'Ninjas');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('namesReal',10);
CREATE VIEW "names" AS SELECT * FROM "namesReal";
COMMIT;
-
At this point selecting from names or namesReal should generate the same data:
sqlite> .header on
sqlite> SELECT "id", "name" FROM "namesReal";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The data above is good, the column names, as well as the row values. But look
what happens when selecting from the view:
sqlite> SELECT "id", "name" FROM "names";
"id"|"name"
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The quotes are being includes in the column names unlike the prior case.
However when selecting via wildcard, this happens:
sqlite> SELECT * FROM "names";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
It appears from these examples, that SQLite mistakenly? is including the
decorations around column names as used by the query for views. Unless I'm
mistaken, column names are supposed to be quoted in SQL in order to prevent
conflict with reserved words.
When trying to query this database with the API, sqlite3_column_name() includes
the quotes around the column name in the second select statement, but not in
the first or third. So it seems the issue is with that function, and not some
quirk of the command line client.
Is this a bug? Or am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue using ON DELETE CASCADE along with ON CONFLICTREPLACE
I would use the update if I knew the entry already existed. In my application
however, it doesn't know if the entry already exists. I was looking for
something to replace MySQL's ON DUPLICATE KEY UPDATE.
I modified my application to use two SQL statements instead.
if (!db.execute("INSERT INTO users VALUES(?, ?, ?);", user.id, user.type,
user.name))
{
db.execute("UPDATE users SET name=? WHERE id=? AND type=?;", user.name,
user.id, user.type);
}
I was hoping for a single statement, but oh well.
Thanks.
--- On Mon, 10/18/10, Igor Tandetnik wrote:
From: Igor Tandetnik
Subject: Re: [sqlite] Issue using ON DELETE CASCADE along with ON
CONFLICTREPLACE
To: sqlite-users@sqlite.org
Date: Monday, October 18, 2010, 4:36 PM
NSRT Mail account. wrote:
> The entry in meetings is now gone. Should ON DELETE CASCADE be picking up an
> UPDATE as a DELETE via INSERT INTO from ON CONFLICT
> REPLACE?
REPLACE involves deleting conflicting rows, followed by INSERT, as explained by
the documentation at http://sqlite.org/lang_conflict.html . No UPDATE is taking
place - in general, several rows may be deleted for one row inserted, in which
case update would make no sense.
> Is there perhaps a better way I should be structuring my tables so I can use
> an INSERT or UPDATE style command, yet force DELETEs
> to cascade to the other table?
What problems do you have with UPDATE? This should work:
UPDATE users set name='Joe C' where id=1 and type=4;
--
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
[sqlite] Issue using ON DELETE CASCADE along with ON CONFLICT REPLACE
I'm not sure if this is an error on my end or on SQLite's. I'm using 3.7.2.
I'm creating two tables as follows:
PRAGMA foreign_keys=ON;
CREATE TABLE 'users' (
'id' INTEGER NOT NULL,
'type' INTEGER NOT NULL,
'name' VARCHAR(64) NOT NULL,
PRIMARY KEY('id', 'type') ON CONFLICT REPLACE
);
CREATE TABLE 'meetings' (
'id' INTEGER PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT,
'userId' INTEGER NOT NULL,
'type' INTEGER NOT NULL,
'password' VARCHAR(64) NOT NULL,
FOREIGN KEY('userId', 'type') REFERENCES users('id', 'type') ON DELETE CASCADE
);
I then insert an entry into each table:
INSERT INTO 'users' VALUES(1,4,'Joe');
INSERT INTO 'meetings' ('userId', 'type', 'password') VALUES(1,4,'blah');
So far, so good. Now if I try to perform an insert which does an update on the
first table like so:
INSERT INTO 'users' VALUES(1,4,'Joe C');
The entry in meetings is now gone. Should ON DELETE CASCADE be picking up an
UPDATE as a DELETE via INSERT INTO from ON CONFLICT REPLACE?
Is there perhaps a better way I should be structuring my tables so I can use an
INSERT or UPDATE style command, yet force DELETEs to cascade to the other table?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible Bug - Return type on change functions
sqlite3_changes() and sqlite3_total_changes() both return an int. I ran a huge update on a table with 2147483685 rows in it, and when I was done, the result from sqlite3_total_changes() was negative, it had overflowed to -2147483612. The documentation doesn't seem to indicate negative values being used for any type of error conditions from these functions, so it seems to me the return types should be unsigned. On further consideration of ROWID, the documentation indicates that the highest value it can store is 9223372036854775807 and once it is full, a table full error response is returned when trying to insert another row. However, testing indicates that ROWID can be negative values as well, meaning a table has an upper limit of 18446744073709551615 rows. This limitation should probably be mentioned on the limits page. But more importantly, sqlite3_changes() should have a return type of sqlite3_uint64, as that is indeed a type that would be able to contain any response it should be able to return. sqlite3_total_changes() should share the same return type, although given enough operations, a call to sqlite3_total_changes() with whatever magnitude return type would always eventually overflow. I hope this is the right place to file this kind off error. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_open16_v2
Looking at, http://www.sqlite.org/c3ref/open.html it seems that a function sqlite3_open16_v2() is inexplicably missing. Is there a reason for this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Running UPDATE on a large table uses too much RAM
I have a database with a single table. The database is 23922826240 bytes. The table has 2147483685 rows in it. Running: UPDATE t SET v=1; makes my application start to use all available RAM, then swap, and is finally killed by the OS (I do not have 23GB of free RAM+swap). Should running an UPDATE cause the table to first be copied to RAM and modified there? Is there any way to get this UPDATE to work with meager resources (given at least 50GB free on the partition the database is stored on)? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
