Re: [sqlite] Error message for nonsensical flags.

[Rowan Worth]

On 10 July 2017 at 00:22, Dan Ackroyd  wrote:

> Hi,
>
> I'm passing on an error report from a downstream library that uses
> SQLite -  https://bugs.php.net/bug.php?id=74883 - without having
> tested that it is an accurate bug report. Apologies in advance if it
> is a spurious bug report, however I don't currently have an
> environment to test it trivially.
>
> It seems that trying to open a database with the flags
> SQLITE3_OPEN_READONLY | SQLITE3_OPEN_CREATE set leads to a very
> non-intuitive error message of "out of memory".
>

I'm not sure the report is accurate. The very first thing that
openDatabase() does is check that the flags make sense; if the following
condition is true then SQLITE_MISUSE is returned:

((1<<(flags&7)) & 0x46)==0

The flags themselves are defined thusly:

#define SQLITE_OPEN_READONLY 0x0001
#define SQLITE_OPEN_READWRITE0x0002
#define SQLITE_OPEN_CREATE   0x0004

With SQLITE_OPEN_READONLY | SQLITE_OPEN_CREATE, (flags&7) = 5 and thus:

1 << 5 = 32 = 0x20,  0x20 & 0x46 = 0

Looking at the other cases...

SQLITE_OPEN_READONLY
1 << 1 = 0x02,  0x02 & 0x46 = 0x02

SQLITE_OPEN_READWRITE
1 << 2 = 0x04,  0x04 & 0x46 = 0x04

SQLITE_OPEN_READONLY | SQLITE_OPEN_READWRITE
1 << 3 = 0x08,  0x08 & 0x46 = 0

SQLITE_OPEN_CREATE
1 << 4 = 0x10,  0x10 & 0x46 = 0

SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE
1 << 6 = 0x40,  0x40 & 0x46 = 0x40

SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_READONLY
1 << 7 = 0x80,  0x80 & 0x46 = 0

So you should get SQLITE_MISUSE unless flags&7 is one of the three sensible
states:

SQLITE_OPEN_READONLY
SQLITE_OPEN_READWRITE
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE

I don't think sqlite is giving you SQLITE_NOMEM here. This is based on code
analysis of versions 3.8.1 and 3.18.0 (it's identical in both; I haven't
checked inbetween versions).

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

Re: [sqlite] Cannot delete a Database file?

[Rowan Worth]

On 8 July 2017 at 10:48, Simon Slavin  wrote:

> On 8 Jul 2017, at 3:33am, domonic  wrote:
>
> > No I just test the state
> > of the database once and then test the output in my if statement.
> >
> > if(sqlite3_close(...))
> > {
> >
> > }
> > else if(sqlite3_close(..))
> > {
> >
> > }
> > else if(sqlite3_close(..))
> > {
> >
> > }
>
> Glad you found a solution.  I recommend you put a one second delay between
> those attempts to give any anti-virus or indexing utility time to finish
> with the file.
>

Note that sqlite3_close on windows already tries to close the file three
times times (with a 100ms delay between each attempt). Which is not to say
that waiting a second is a bad idea if that does fail, but are you actually
hitting the second sqlite3_close?

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

Re: [sqlite] SQLITE_ERROR ("SQL error or missing database") should be split into two codes: "SQL error" and "missing database"

[Rowan Worth]

On 8 July 2017 at 03:28, Yuri  wrote:

>
> There are some other problems in error definitions. For example, what does
> SQLITE_FULL mean? How can database be full? Is it really a disk-full
> condition?
> > #define SQLITE_FULL13   /* Insertion failed because database is
> full */


Disk is full, or the database cannot hold more pages[1], or a table cannot
hold more rows (because all ROWIDs have been used).

[1] https://sqlite.org/limits.html#max_page_count


> Also, what does
>
>> #define SQLITE_IOERR_SHORT_READ(SQLITE_IOERR | (2<<8))
>>
> really mean? How is it different from the case when database is
> corrupt/truncated? But there is SQLITE_CORRUPT for that.
> Short read mean EOF, and EOF in unexpected place constitutes corrupt
> database file.
>

SQLITE_IOERR_SHORT_READ is mostly of interest to the vfs layer rather than
regular sqlite users, because sqlite specifically requires that a vfs
implementation zero the remainder of the read buffer in the short read
scenario.

Interestingly sqlite works quite differently to your expectation in the
case of unexpected EOF. Sqlite doesn't automatically assume corruption - in
fact this is the one IOERR it ignores, instead proceeding to try and use
the data which was read.

Sometimes this will cause an SQLITE_CORRUPT return - eg. if the last page
is a btree interior page then zero-filling the end of the read buffer will
almost certainly result in nonsense. But in the case of a leaf page the
zero-filled buffer can still represent a "validly" formed database page and
sqlite will proceed without reporting any error. Via this vector it is
unfortunately possible for queries to return rows containing NULL in a
column that is explicitly NOT NULL. I've mentioned this before:

http://sqlite.1065341.n5.nabble.com/Null-returned-from-NOT-NULL-column-thanks-to-undetected-corruption-td93309.html

I'm still not sure why sqlite ignores short reads; maybe something to do
with creating databases (multi-process race when the file has been created
but the first DB page hasn't been written yet?).

Anyway this is the kind of detail most users don't care (or need to care)
about. If you get _any_ kind of SQLITE_IOERR returned by sqlite it's best
to assume your connection is hosed.

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

Re: [sqlite] VALUES clause quirk or bug?

[Simon Slavin]

On 9 Jul 2017, at 9:53pm, Keith Medcalf  wrote:

> Richard has checked in fixes for this on trunk which will likely appear in 
> the next release of SQLite. 

Presumably



.  Is the behaviour of column names now consistent enough that the fix could 
include an update to



to reflect these names ?  The text current says

"The name of a result column is the value of the "AS" clause for that column, 
if there is an AS clause. If there is no AS clause then the name of the column 
is unspecified and may change from one release of SQLite to the next."

which is what I was basing my previous responses on.

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

Re: [sqlite] acf3b9cc9c3932431979995a1dceacc06c659ab400fad95ce3728ff8895a022b : Off by one on the column names

[Kees Nuyt]

On Sun, 09 Jul 2017 12:12:15 -0600, "Keith Medcalf"
 wrote:

>
>Off by one on the names:
>
>sqlite> values (1,2), (2,3);
>column1|column2
>1|2
>2|3
>
>sqlite> select * from (values (1,2), (2,3));
>column0|column1
>1|2
>2|3

Solved in
=== 2017-07-09 ===
18:55:29 [70096c505d] *CURRENT* Always make "column%d"
column-names 1-based, never 0-based. (user: drh tags: trunk)

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

Re: [sqlite] VALUES clause quirk or bug?

[Keith Medcalf]

Richard has checked in fixes for this on trunk which will likely appear in the 
next release of SQLite.  Note this is a change to the underlying SQLite engine 
(SQLite3.dll) and not the command line shell.  Column names from "values" are 
predictable and the same even in subqueries.  Note that shell simply outputs 
what is returned by executing the SQL.

Note that presently if you use a double-quote for the first value row, and that 
quoted-identified is not an identified but treated as a quoted-string, it still 
becomes the column name in the subquery.

SQLite version 3.20.0 2017-07-09 18:55:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode col
sqlite> .header on

sqlite> values (1,2), (2,3);
column1 column2
--  --
1   2
2   3

sqlite> select * from (values (1,2), (2,3));
column1 column2
--  --
1   2
2   3

sqlite> create table x as values (1,2), (3,4);
sqlite> select * from x;
column1 column2
--  --
1   2
3   4

sqlite> create table y as select * from (values (1,2), (2,3));
sqlite> select * from y;
column1 column2
--  --
1   2
2   3

sqlite> create view v as values (1,2), (2,3);
sqlite> select * from v;
column1 column2
--  --
1   2
2   3

sqlite> create view w as select * from (values (1,2), (2,3));
sqlite> select * from w;
column1 column2
--  --
1   2
2   3

sqlite> with x as (values (1,2), (2,3)) select * from x;
column1 column2
--  --
1   2
2   3

sqlite> with x as (select * from (values (1,2), (2,3))) select * from x;
column1 column2
--  --
1   2
2   3

sqlite> with
   ...> x as (values (1,2), (2,3)),
   ...> y as (values (4,5), (5,6))
   ...> select x.column1*y.column2, x.column2*y.column1 from x,y;
x.column1*y.column2  x.column2*y.column1
---  ---
58
610
10   12
12   15

sqlite> select x.column1*y.column2, x.column2*y.column1
   ...> from (values (1,2), (2,3)) as x,
   ...>  (values (4,5), (5,6)) as y;
x.column1*y.column2  x.column2*y.column1
---  ---
58
610
10   12
12   15
sqlite>

And since the values now return consistent names, you can "rename" them in the 
select more easily if you so desire.

sqlite> select column1 as idx, column2 as value from (values (1,2), (2,3));
idx value
--  --
1   2
2   3
sqlite>

However:

sqlite> values ("1", 2), ('3', 4);
column1 column2
--  --
1   2
3   4

sqlite> values ('1', 2), ("3", 4);
column1 column2
--  --
1   2
3   4
sqlite>

sqlite> select * from (values ("1", 2), ('3', 4));
1   column2
--  --
1   2
3   4

sqlite> select * from (values ('1', 2), ("3", 4));
column1 column2
--  --
1   2
3   4
sqlite>


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of petern
> Sent: Saturday, 8 July, 2017 20:51
> To: SQLite mailing list
> Subject: Re: [sqlite] VALUES clause quirk or bug?
> 
> A lot of replies.  To be clear, this is not a quirk of the shell and I'm
> not just monkeying around looking for haphazard guesses about why I'm
> "trying to change the SQL standard".  The following query sent through
> sqlite3_exec() produces the following output:
> 
> SELECT max([],[:1]) FROM (VALUES (1,2),(3,4));
> max([],[:1])
> 2
> 4
> 
> This is what SQLite 3.19.3 VALUES clause presently does independently of
> shell.c.  My question would be, is this feature going to produce stable
> column names going forward caveat the strange behavior of double quotes.
> 
> Simon, your rhetorical question asking how having an inconsistent number
> of
> columns in the VALUES list makes sense is non-sequitur.  I am not
> proposing
> some illogical bizarre syntax.  I hope you see how your remark is out of
> line and amounts to noise on this thread.
> 
> The bug here is how the VALUES logic can't have it both ways.  If double
> quotes are invalid for column value literals they should be rejected or at
> least ignored.  They should not suddenly be injected into the column
> name(s) observed by the outer scope of the VALUES clause.   That is of
> course unless there is a software easter egg here that, depending on as
> yet
> undisclosed well crafted literal values, allows the column names to be
> elegantly specified in addition to the row values.
> 
> Also sent through sqlite3_exec():
> 
> SELECT * FROM (VALUES ("1",2),(3,4));
> 1,
> 1,2
> 3,4
> 
> 
> 
> 
> 

Re: [sqlite] Concurrent reads for VTs with in-memory data structures

[Keith Medcalf]

I presume only the data structure pointer is static -- the vtab/cursors still 
need to be dynamic ..

I do not know why access to multiple separate instances of a virtual table 
would be serialized ...


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Dimitris Bil
> Sent: Tuesday, 4 July, 2017 09:19
> To: SQLite mailing list
> Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data
> structures
> 
> Keith, thanks for your answer.
> 
> 
> I tried to implement it using multiple connections (each connection from a
> different thread) from a single process, but not only all access seems to
> be serialized, but the time spent when using threads is worse than the
> total time if all queries were executed sequentially. For example, I am
> testing the same query on an 8-core machine after I have created the
> static in-memory data structures. If I use one thread, the query takes
> about 20ms, whereas with two threads the time is about 60ms and with six
> threads about 500ms. I am getting the same behavior if I just try to
> execute a read from a regular table and also if I use the same connection
> for all threads. I have tried different SQLITE_THREADSAFE values, and also
> different values for PRAGMA synchronous and PRAGMA journal_mode, as well
> as trying to open the connections in read only mode, but nothing seems to
> work.
> 
> 
> I would appreciate any idea on how to make this work properly.
> 
> 
> 
> From: sqlite-users  on
> behalf of Keith Medcalf 
> Sent: Monday, July 3, 2017 6:36 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data
> structures
> 
> 
> If it is singleton data the I suppose you could keep a static pointer to
> the data structure, a static "use" counter, and a static mutex.
> 
> Then, for each connection (xConnect), lock the mutex, if the static use
> counter is zero then build the data structure and increment the use
> counter, then set the new connection to use the shared data structure,
> then release the mutex.
> 
> For each xDisconnect, lock the mutex, release that set of virtual table
> resources, decrement the counter and if the counter is zero, release the
> static data, then release the mutex.
> 
> This assumes that the data needs "reloading" after nothing is using it.
> If this is not the case you do exactly the same thing except to never
> rebuild the data structure after the first time, all you need (inside the
> mutex) on the xConnect it to build the static data structure if it does
> not exist, and on the xDisconnect simply release the virtual table
> resources (but never the shared static data structure).
> 
> This would mean, of course, that the data would be "static" across all
> connections in a process, but different processes would have their own
> static data.  If you need to release and rebuild the data structure this
> will happen only when no connection in the process is using connected to
> the data.
> 
> By diddling with the data attributes (basically making them all extern
> references to a specially constructed data segment) you could make the
> static data structure a singleton across multiple processes.  However this
> is not the default because static data segments are "process local" not
> "library local" ...
> 
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> 
> 
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Dimitris Bil
> > Sent: Monday, 3 July, 2017 11:14
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: [sqlite] Concurrent reads for VTs with in-memory data
> structures
> >
> > I have some virtual tables that keep in-memory data structures. Data
> > loading is happening at table creation (xCreate and xConnect are the
> same)
> > and after that, during querying, only read access is needed. Queries do
> > not access any other tables. Is there a way to achieve concurrent
> > execution without having to keep multiple copies of each data structure?
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

[sqlite] acf3b9cc9c3932431979995a1dceacc06c659ab400fad95ce3728ff8895a022b : Off by one on the column names

[Keith Medcalf]

Off by one on the names:

sqlite> values (1,2), (2,3);
column1|column2
1|2
2|3

sqlite> select * from (values (1,2), (2,3));
column0|column1
1|2
2|3

---
Life should not be a journey to the grave with the intention of arriving safely 
in a pretty and well preserved body, but rather to skid in broadside in a cloud 
of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! 
What a Ride!"
 -- Hunter S. Thompson




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

[sqlite] Error message for nonsensical flags.

[Dan Ackroyd]

Hi,

I'm passing on an error report from a downstream library that uses
SQLite -  https://bugs.php.net/bug.php?id=74883 - without having
tested that it is an accurate bug report. Apologies in advance if it
is a spurious bug report, however I don't currently have an
environment to test it trivially.

It seems that trying to open a database with the flags
SQLITE3_OPEN_READONLY | SQLITE3_OPEN_CREATE set leads to a very
non-intuitive error message of "out of memory".

Those two flags are probably nonsensical to be used together, however
the alleged error message points the user in the wrong direction to
look for the error.

Would it be possible for either incompatible flags like this to be
detected, or for the error message to be improved for this scenario?

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

[sqlite] appfileformat.html

[jungle Boogie]

Hi SQLITE Committers,

I noticed a misspelling here:
https://www.sqlite.org/appfileformat.html

catagory is misspelled; it should be: category.

First committed here:
https://www.sqlite.org/docsrc/info/6d257b8d928cfd00

Thanks!


-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cannot compile sqlite3 with intel compiler (ICC) - log and hints for a possible solution

[Bob Friesenhahn]

On Sat, 8 Jul 2017, Richard Hipp wrote:


(3) I compiled SQLite on each of gcc-5.4, gcc-7.1, clang-3.5, and
icc-17.0 and compared both the size of the resulting binary and the
performance. icc gave the largest binary and the slowest performance.
Here are the actual results:

gcc-5.4: 491585 bytes, 1,124 million CPU cycles.
gcc-7.1: 487582 bytes, 1,121 million CPU cycles.
clang-3.5: 569570 bytes, 1,170 million CPU cycles
icc-17.0: 536596 bytes, 1,274 million CPU cycles

As you can see, the gcc-compiled binary of SQLite is 9% smaller and
12% faster than the icc-compiled binary. So, maybe the solution is to
just not use icc?


Sqlite3 has been cycle-optimized for GCC.  You now have 29-days to 
also cycle-optimize it for ICC.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VALUES clause quirk or bug?

[Clemens Ladisch]

petern wrote:
> I was hoping someone could shed light on what is actually going on in the
> VALUE clause.

  VALUES (a, b), (c, d) ...

is actually just a shortcut for

  SELECT a, b UNION ALL SELECT c, d ...

If you want to control the column names, you have to use the second form
with AS.


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

[sqlite] disabling quoting of newline in dump files (sqlite 3.19)

[Basile Starynkevitch]

Hello All,

(for details and motivations about my question, see also 
https://stackoverflow.com/q/44989176/841108 ...)



I'm using the latest sqlite3 3.19.3 (the package version 3.19.3-3 of 
Linux/Debian/Sid/x86-64)


I want that sqlite3 .dump outputs a dump file with newline characters 
appearing as is. But since 3.19 sqlite3 dump is using 
replace('...\n','\n',char(10)


The reason I want that is that the dump file is version controlled with 
git and I chose sqlite3 -at that time 3.18- notably because it was able 
to keep these newlines verbatim in dump files (only quoting the quote 
character).



More precisely, I have a database with

|CREATETABLEt_objects (ob_id 
VARCHAR(30)PRIMARYKEYASCNOTNULLUNIQUE,ob_mtim REAL NOTNULL,ob_content 
TEXT NOTNULL,ob_paylkind VARCHAR(30)NOTNULL,ob_paylinit TEXT 
NOTNULL,ob_paylcontent TEXT NOTNULL); and with 3.18 my dump file 
contained also (with an empty line after the $c) ||INSERTINTOt_objects VALUES('_0UsE4ep7mY5_3M6VTOn9Doe',1497676631.12,' 
///$c ','named','c','@NAMEDPROXY: __'); but since 3.19 the same INSERT 
statement for the same row content became ||INSERTINTOt_objects 
VALUES('_0UsE4ep7mY5_3M6VTOn9Doe',1497676631.118855,replace('\n///$c\n\n','\n',char(10)),'named','c','@NAMEDPROXY: 
__'); (I added the newline before the replace for readability) FYI the 
project dumping content is https://github.com/bstarynk/melt-monitor the 
dump file is for example |https://github.com/bstarynk/melt-monitor/blob/master/mom_user.sql

e.g. the commit 
https://github.com/bstarynk/melt-monitor/blob/6bc1ada0c9ace053695d669abbb944e36993ac06/mom_user.sql

and I use git precommit and postmerge hooks to dump & restore that DB when 
doing git operations.

Cheers

BTW, I could probably propose a patch to fix that (probably some .lessquote 
metacommand) but
getting an official paper from my employer (a French goverment owned 
organization CEA) is much more trouble
than coding the patch itself.

Regards
--
Basile STARYNKEVITCH   == http://starynkevitch.net/Basile
opinions are mine only - les opinions sont seulement miennes
Bourg La Reine, France

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

[sqlite] TEST: please ignore and delete

[Winfried]

I'm just checking that the list is again reachable from Nabble after an admin
changed the mail address from @sqlite.org to @mailinglists.sqlite.org

http://sqlite.1065341.n5.nabble.com/

Admins: If need be, please delete this message later.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TEST-please-ignore-and-delete-tp96561.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VALUES clause quirk or bug?

[R Smith]

On 2017/07/09 4:50 AM, petern wrote:

The bug here is how the VALUES logic can't have it both ways.  If double
quotes are invalid for column value literals they should be rejected or at
least ignored.  They should not suddenly be injected into the column
name(s) observed by the outer scope of the VALUES clause.


It's not a bug, but it is an irritation - and we've been petitioning 
since the dawn of time to have a "STRICT" mode in SQLite where these 
shenanigans are indeed not allowed. As I understood from previous 
dev-team remarks, is that it's relatively easy to change, but not easy 
to maintain "both ways" without bloating the code-base, and since this 
quirk existed from day 1, it is required to keep existing for backward 
compatibility. (And backward compatibility IS important, since it is the 
very thing you are asking future guarantees for now).


That being said, for most of us it is/was just a once-off irritation 
that one time when we used double-quotes in stead of single quotes and 
got weird results. We fixed it, and so life goes on. In your case you 
have arrived at some mechanism or exploit of the SQLite internals to 
achieve an interesting functionality, but the quotes are biting you.


As already stated, best use is to avoid the double quotes in the VALUES. 
You are however quite welcome to use them in the identifier section (in 
lieu of [ and ]), but you can use the MSSQL style square brackets too. 
As to the question of future-proofing this method, well, it's not 
documented and not set in stone, BUT, SQLite isn't in the habit of 
haphazardly changing these internals. You are very likely to have a 
stable future regime for it - just not a 100% guarantee.


My advice would be to include test code, or create a separate test 
system that tests all your syntax forms, which you verify with in future 
before incorporating any future SQLite upgrade in your projects. Also, 
try to make your code based on constants and things in a way that a 
change would be very easy should it ever be required. (That's 1st week 
Computer science 101) :)


One last option, since you seem skilled in the coding of things, SQLite 
/IS/ open source, you could maintain a fork which implements a few lines 
of code to ensure your method works, or even make it work better. The 
code is very well documented, and this would guarantee your future 
compatibility.



That is of
course unless there is a software easter egg here that, depending on as yet
undisclosed well crafted literal values, allows the column names to be
elegantly specified in addition to the row values.



None that I know of, and even if there were, it's not documented and so 
would fall under the exact same problem as your current solutions: We 
are not 100% sure it will remain in future exactly as it is now.




Best of luck!
Ryan

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